MySQL数据库——SQL语法

发布时间：2023年12月20日

Structured Query Language（结构化查询语言），简称SQL，是用于操作关系型数据库的标准编程语言。SQL提供了一种与数据库交互的方式，可以用于查询、插入、更新和删除数据库中的数据。

1. SQL通用语法

  1. SQL语句可以写在一行或者多行，以分号（;）结尾。这意味着可以将一个SQL语句分成多行来提高可读性，或者使用多行来书写一个复杂的查询。
-- 单行SQL语句
SELECT * FROM table_name;

-- 多行SQL语句
SELECT column1, 
       column2, 
       column3 
FROM table_name 
WHERE condition;

  1. SQL语句中可以使用空格和缩进来增加语句的可读性。

空格和缩进不会影响SQL语句的执行，但可以使代码更加整洁和易于理解。

  1. 在MySQL数据库中，SQL语句是不区分大小写的。然而，建议使用大写字母来书写关键字，以便更容易区分关键字和其他部分。这不是必需的，但是是一种通用的编码约定。

  1. SQL语句中可以使用注释来解释代码的目的或者提供其他信息。在SQL中，有两种类型的注释：
    • 单行注释：使用两个连续的破折号（--）或者井号（#）来标记单行注释。这种注释在一行中的后续内容将被视为注释。
sql -- 这是一个单行注释
SELECT * FROM table_name;

# 这也是一个单行注释
SELECT * FROM table_name;
    • 多行注释：使用 / 和 / 来标记多行注释的开始和结束。在这种注释中间的内容将被视为注释，可以跨越多行。
sql /*
这是一个
多行注释
*/
SELECT * FROM table_name;

2. SQL分类

SQL语句根据其功能主要分为四类：

DDL（数据定义语言）、DML（数据操纵语言）、DQL（数据查询语言）、DCL（数据控制语言）。

分类

全称

说明

DDL

Data Definition Language

数据定义语言，用来定义数据库对象(数据库，表，字段)

DML

Data Manipulation Language

数据操作语言，用来对数据库表中的数据进行增删改

DQL

Data Query Language

数据查询语言，用来查询数据库中表的记录

DCL

Data Control Language

数据控制语言，用来创建数据库用户、控制数据库的访问权限

下面的只是示意，到后面会具体详细讲解

  1. DDL（Data Definition Language，数据定义语言）：用于定义数据库对象，如创建、修改和删除数据库、表、索引等。常见的DDL语句包括CREATE、ALTER、DROP等。

示例：

* 创建一个表：
```
sql`CREATE TABLE table_name (column1 datatype, column2 datatype, ...);`
```
* 删除一个表：
```
sql`DROP TABLE table_name;`
```

  1. DML（Data Manipulation Language，数据操纵语言）：用于操纵数据库中的数据，包括插入、更新和删除数据。常见的DML语句包括INSERT、UPDATE、DELETE等。

示例：

* 插入数据：
```
sql`INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);`
```
* 更新数据：
```
sql`UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;`
```

  1. DQL（Data Query Language，数据查询语言）：用于查询数据库中的数据。常见的DQL语句是SELECT。

示例：

* 查询数据：
```
sql`SELECT column1, column2 FROM table_name WHERE condition;`
```

  1. DCL（Data Control Language，数据控制语言）：用于控制和管理数据库的访问权限和安全性。常见的DCL语句包括GRANT、REVOKE等。

示例：

* 授予权限：
```
sql`GRANT privilege TO user;`
```
* 撤销权限：
```
sql`REVOKE privilege FROM user;`
```

3. DDL

Data Definition Language，数据定义语言，用来定义数据库对象(数据库，表，字段) 。

3.1. 数据库操作

3.1.1. 查询所有数据库

show databases ;

3.1.2. 查询当前数据库

查询当前选中的是哪个数据库

select database() ;

3.1.3. 创建数据库

create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序规则 ] ;

CREATE DATABASE：这是基本的命令，表示要创建一个新的数据库。

[IF NOT EXISTS]：这是一个可选的子句。如果您加上它，那么如果数据库已经存在，命令将不会产生错误。反之，如果不加这个子句，而数据库已经存在，命令会报错。

数据库名：这是您想要创建的数据库的名称。您需要用双引号或方括号括起来，特别是当数据库名称包含特殊字符或与SQL关键字冲突时。
[DEFAULT CHARSET 字符集]：这也是一个可选的子句。您可以用它来指定数据库的默认字符集。

[COLLATE 排序规则]：这是另一个可选的子句。它用于指定数据库的默认排序规则。例如，如果您想要使用 utf8_general_ci 排序规则，您可以这样写：CREATE DATABASE mydatabase COLLATE utf8_general_ci;

3.1.4. 切换数据库

我们要操作某一个数据库下的表时，就需要通过该指令，切换到对应的数据库下，否则是不能操作的。

use 数据库名 ;

3.1.5. 删除数据库

drop database [ if exists ] 数据库名 ;

3.2. 数据表操作

3.2.1. 表操作-查询创建

3.2.1.1. 查询当前数据库所有表
show tables;

切换到company数据库，再显示该数据库下的所有表

3.2.1.2. 查看指定表结构*

通过这条指令，我们可以查看到指定表的字段，字段的类型、是否可以为NULL，是否存在默认值等信息。

desc 数据表名 ;

3.2.1.3. 查询指定表的建表语句*
show create table 表名 ;

通过这条指令，主要是用来查看建表语句的，而有部分参数我们在创建表的时候，并未指定也会查询到，因为这部分是数据库的默认值，如：存储引擎、字符集等。

3.2.1.4. 创建表结构
CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ],
字段2 字段2类型 [COMMENT 字段2注释 ],
字段3 字段3类型 [COMMENT 字段3注释 ],
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) [ COMMENT 表注释 ] ;

[...] 内为可选参数，最后一个字段后面没有逗号

  1. CREATE TABLE 表名：
    • CREATE TABLE是SQL命令，用于创建一个新的表。
    • 表名是为新表选择的名称。
  1. 字段1 字段1类型 [ COMMENT 字段1注释 ], ... 字段n 字段n类型 [COMMENT 字段n注释 ]：
    • 这些是表的列定义。
    • 字段1, ... 字段n是列的名称。
    • 字段1类型, ... 字段n类型定义了列的数据类型。例如：INT, VARCHAR(255), DATE等。
    • [ COMMENT 字段1注释 ], ... [ COMMENT 字段n注释 ]是可选的，用于为每列添加注释或描述。
  1. [ COMMENT 表注释 ]：
    • 这是可选的，用于为整个表添加注释或描述。
  1. [...] 内为可选参数：
    • 这表示在CREATE TABLE语句中，有很多其他的可选参数和功能，如索引、外键、默认值等，但它们不是每个命令都需要的一部分。
  1. 最后一个字段后面没有逗号：
    • 在定义表的列时，最后一个列后面不应该有逗号。这是SQL语法的要求。

比如，我们创建一张表 tb_user ，对应的结构如下，那么建表语句为：

create table tb_user(
id int comment '编号',
name varchar(50) comment '姓名',
age int comment '年龄',
gender varchar(1) comment '性别'
) comment '用户表';

3.2.2. 表操作-数据类型*

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

3.2.2.1. 数值类型

MySQL中的数值类型主要有以下几种：

  1. 整数类型：

类型

大小

有符号(SIGNED)范围

无符号(UNSIGNED)范围

描述

TINYINT

1byte

-128到127

0到255

一个非常小的整数

SMALLINT

2bytes

-32768到32767

0到65535

一个小整数

MEDIUMINT

3bytes

-8388608到8388607

0到16777215

一个中等大小的整数

INT 或 INTEGER

4bytes

-2147483648到2147483647

0到4294967295

一个标准的整数

BIGINT

8bytes

-9223372036854775808到9223372036854775807

0到18446744073709551615

一个大整数

  1. 浮点类型

类型

大小

有符号(SIGNED)范围

无符号(UNSIGNED)范围

描述

FLOAT

4bytes

(-3.402823466 E+38， 3.402823466351 E+38)

0 和 (1.175494351 E38，3.402823466 E+38)

单精度浮点数

DOUBLE

8bytes

(-1.7976931348623157 E+308， 1.7976931348623157 E+308)

0 和 (2.2250738585072014 E-308， 1.7976931348623157 E+308)

双精度浮点数

DECIMAL

依赖于M(精度)和D(标度) 的值

依赖于M(精度)和D(标度)的 值

小数值(精确定点数)

3.2.2.2. 字符串类型

类型

大小

描述

CHAR

0-255 bytes

定长字符串(需要指定长度)

VARCHAR

0-65535 bytes

变长字符串(需要指定长度)

TINYBLOB

0-255 bytes

不超过255个字符的二进制数据

TINYTEXT

0-255 bytes

短文本字符串

BLOB

0-65535 bytes

二进制形式的长文本数据

TEXT

0-65535 bytes

长文本数据

MEDIUMBLOB

0-16777215 bytes

二进制形式的中等长度文本数据

MEDIUMTEXT

0-16777215 bytes

中等长度文本数据

LONGBLOB

0-4294967295 bytes

二进制形式的极大文本数据

LONGTEXT

0-4294967295 bytes

极大文本数据

CHAR 和 VARCHAR 是两种不同的字符串数据类型，它们在存储和性能上有一些关键的区别。

  1. CHAR(M)：
    • 这是一个固定长度的字符串。
    • 当你定义一个 CHAR 字段并指定一个长度（例如 CHAR(10)），无论你实际存储的字符串有多短，它都会占用10个字符的空间。
    • 例如，如果你存储字符串 "abc" 在一个 CHAR(10) 字段中，那么它后面会有8个空格被填充，总共占据10个字符的空间。
    • 如果存储的字符串长度超过定义的长度，它会被截断。
  1. VARCHAR(M)：
    • 这是一个可变长度的字符串。
    • 与 CHAR 不同，VARCHAR 的长度是根据实际存储的字符串来决定的。
    • 例如，如果你存储字符串 "abc" 在一个 VARCHAR(10) 字段中，它只会占用3个字符的空间（加上字符串结束的null字符，总共是4个字节）。
    • 如果存储的字符串长度超过定义的长度（例如 VARCHAR(10)），它会被截断，但不会像 CHAR 那样在后面填充空格。

性能考虑：

  • 对于经常变化长度的字段，使用 VARCHAR 可以节省空间，因为它只存储实际的数据，而不是额外的空格或截断的字符。
  • 对于长度始终不变或变化不大的字段，使用 CHAR 可能更高效，因为数据库可以更快速地访问固定长度的数据。
3.2.2.3. 日期时间类型

类型

大小

范围

格式

描述

DATE

3

1000-01-01 至 9999-12-31

YYYY-MM-DD

日期值

TIME

3

-838:59:59 至 838:59:59

HH:MM:SS

时间值或持续时间

YEAR

1

1901 至 2155

YYYY

年份值

DATETIME

8

1000-01-01 00:00:00 至 9999-12-31 23:59:59

YYYY-MM-DD HH:MM:SS

混合日期和时间值

TIMESTAMP

4

1970-01-01 00:00:01 至 2038-01-19 03:14:07

YYYY-MM-DD HH:MM:SS

混合日期和时间值，时间戳

时间戳：

时间戳是一个用于标识事件发生时间的数字或字符串。它通常表示自某个固定起始点（如协调世界时(UTC)的起始时间）到特定时间的经过的秒数或毫秒数。

时间戳可以用于记录事件发生的顺序、计算时间间隔或对数据进行时间排序等。例如，在日志文件中，时间戳用于记录每条日志的时间；在数据库中，时间戳用于追踪数据的修改时间；在通信系统中，时间戳用于确定消息的顺序和时序。

3.2.3. 表操作-练习

需求1：

设计一张员工信息表，要求如下：

1. 编号（纯数字）

2. 员工工号 (字符串类型，长度不超过10位)

3. 员工姓名（字符串类型，长度不超过10位）

4. 性别（男/女，存储一个汉字）

5. 年龄（正常人年龄，不可能存储负数）

6. 身份证号（二代身份证号均为18位，身份证中有X这样的字符）

7. 入职时间（取值年月日即可）

建表语句：

create table emp(
	id int comment '编号',
	workno varchar(10) comment '工号',
	name varchar(10) comment '姓名',
	gender char(1) comment '性别',
	age tinyint unsigned comment '年龄',
	idcard char(18) comment '身份证号',
	entrydate date comment '入职时间'
) comment '员工表';

3.2.4. 表操作修改

3.2.4.1. 添加字段
ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ] ;
  1. ALTER TABLE：这是一个SQL命令的开始，表示你要对一个表进行操作。
  2. 表名：这是你要修改的表的名称。你需要用实际的表名替换它。
  3. ADD：这个关键词表示你要添加一个新的字段。
  4. 字段名：这是你要添加的新字段的名称。你需要用实际的字段名替换它。
  5. 类型 (长度)：这部分定义了新字段的数据类型和长度。例如，如果字段是整数类型，你可能写为 INT(11)，其中11是该整数的最大长度。如果字段是字符串类型，你可能写为 VARCHAR(255)，其中255是该字符串的最大长度。
  6. [ COMMENT 注释 ]：这部分是可选的，它允许你为新字段添加一个注释。注释可以帮助其他人理解字段的含义或者用途。例如，你可以写 COMMENT '这是新添加的字段'。
  7. [ 约束 ]：这部分也是可选的，它允许你为新字段添加约束条件。约束可以限制该字段的输入格式或值。例如，你可以添加 NOT NULL 约束来确保这个字段不能为空。

需求：

为emp表增加一个新的字段”昵称”为nickname，类型为varchar(20)

ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';

3.2.4.2. 修改数据类型
 ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度) ;
  1. ALTER TABLE：这是一个SQL命令的开始，表示你要对一个表进行操作。
  2. 表名：这是你要修改的表的名称。你需要用实际的表名替换它。
  3. MODIFY：这个关键词表示你要修改一个现有的字段。
  4. 字段名：这是你要修改的字段的名称。你需要用实际的字段名替换它。
  5. 新数据类型 (长度)：这部分定义了字段的新数据类型和长度。例如，如果字段是整数类型，你可能写为 INT(11)，其中11是该整数的最大长度。如果字段是字符串类型，你可能写为 VARCHAR(255)，其中255是该字符串的最大长度。

3.2.4.3. 修改字段名和数据类型
 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];

需求：

将emp表的nickname字段修改为username，类型为varchar(30)

AlTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';

3.2.4.4. 删除字段
ALTER TABLE 表名 DROP 字段名 ;

需求：

将emp表的字段username删除

ALTER TABLE emp DROP username ;

3.2.4.5. 修改表名
ALTER TABLE 表名 RENAME TO 新表名;

需求：

将emp表的表名修改为 employee

ALTER TABLE emp RENAME to employee;

3.2.5. 表操作-删除*

3.2.5.1. 删除表
 DROP TABLE [ IF EXISTS ] 表名;

可选项 IF EXISTS 代表，只有表名存在时才会删除该表，表名不存在，则不执行删除操作(如果不加该参数项，删除一张不存在的表，执行将会报错)。

3.2.5.2. *删除指定表并重新创建表
TRUNCATE TABLE 表名;

注意: 在删除表的时候，表中的全部数据也都会被删除。

4. DML

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作。

4.1. 添加数据

4.1.1. 给指定字段添加数据

INSERT INTO 表名 (字段名1, 字段名2, ...) 
VALUES (值1, 值2, ...);

需求：

给employee表所有的字段添加数据

insert into employee(id,workno,name,gender,age,idcard,entrydate)
values(1,'1','Tom','男',10,'123456789012345678','2000-01-01');

4.1.2. 给全部字段添加数据

INSERT INTO 表名 VALUES (值1, 值2, ...);

需求：

插入数据到employee表

insert into employee
values(2,'2','Jack','男',10,'156789012345678','2002-01-01');

4.1.3. 批量添加数据

  • 给指定字段批量添加数据
INSERT INTO 表名 (字段名1, 字段名2, ...) 
VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;

  • 给全部字段添加数据
INSERT INTO 表名 
VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;

需求：

批量插入数据到employee表

  • 给指定字段批量添加数据

insert into employee(id,workno,name,gender,age,idcard,entrydate)
values
(3,'3','ASC','男',10,'123456789012345678','2000-01-01'),
(4,'4','ADC','男',10,'121235678934d5678','2004-06-15'),
(5,'5','AUT','男',10,'12344512354435678','2000-01-01');

  • 给全部字段添加数据
insert into employee
values
(6,'6','YDC','男',10,'123456789012345678','2000-01-01'),
(7,'7','UYC','男',10,'121235678934d5678','2004-06-15'),
(8,'8','IUT','男',10,'12344512354435678','2000-01-01');

注意事项：

? 插入数据时，指定的字段顺序需要与值的顺序是一一对应的，不然会有语法错误。

? 字符串和日期型数据应该包含在引号中。

? 插入的数据大小，应该在字段的规定范围内。

4.2. 修改数据

具体语句如下：

UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ;

注意：

修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

需求：

在employee表中，修改id为1的数据，将name修改为Jerry

update employee set name = Jerry where id = 1;

在employee表中，将所有的员工入职日期修改为 2008-01-01

update employee set entrydate = '2008-01-01';

4.3. 删除数据

具体语句如下：

DELETE FROM 表名 [ WHERE 条件 ] ;

注意：

删除语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。

DELETE 语句不能删除某一个字段的值，可以使用UPDATE，将该字段值置为NULL即可

需求：

在employee表中，删除 gender = '女' 的员工数据

delete from employee where gender = '女';

删除employee表中所有的数据（即删除该表中所有列数据）

delete from employee;

5. DQL

DQL，全称为Data Query Language，是一种专门用于查询数据库中数据的语言。它的主要目的是从数据库中检索信息，而不会对数据进行任何修改或删除操作。在日常的业务系统中，查询操作的使用频率通常远远高于插入、删除和更新操作。

"SELECT"是DQL中最关键的命令，它用于指定想要从数据库中获取哪些数据。例如，你可以使用SELECT语句来请求特定表中的某些列或者所有列的记录。

在实际的业务场景中，如企业官网或电商网站，当我们浏览页面时，所看到的诸如产品信息、用户评论、订单状态等数据，都是通过DQL查询语句从数据库中获取的。这些查询可能涉及到复杂的逻辑和操作，包括但不限于：

1. 条件查询：通过WHERE子句来指定筛选记录的条件，例如查询价格大于100元的产品或者状态为“已发货”的订单。

2. 排序查询：使用ORDER BY子句来指定结果集的排序方式，例如按照产品名称的字母顺序排序或者按照销量从高到低排序。

3. 分页查询：在处理大量数据时，为了提高性能和用户体验，常常需要通过LIMIT和OFFSET（或者特定数据库系统的等价语法）来实现分页显示，只取出当前页面需要展示的数据。

数据准备工作：

drop table if exists employee;

create table emp
(
    id          int comment '编号',
    workno      varchar(10) comment '工号',
    name        varchar(10) comment '姓名',
    gender      char(1) comment '性别',
    age         tinyint unsigned comment '年龄',
    idcard      char(18) comment '身份证号',
    workaddress varchar(50) comment '工作地址',
    entrydate   date comment '入职时间'
) comment '员工表';

INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (1, '00001', '张三', '男', 20, '123456789012345678', '北京', '2000-01-01');

INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (2, '00002', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01');

INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (3, '00003', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01');

INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (4, '00004', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01');

INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01');

INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (6, '00006', '杨逍', '男', 28, '12345678931234567X', '北京', '2006-01-01');

INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (7, '00007', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01');

INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (8, '00008', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01');

INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (9, '00009', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01');

INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (10, '00010', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01');

INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (11, '00011', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01');

INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01');

INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (13, '00013', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01');

INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (14, '00014', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01');

INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (15, '00015', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01');

INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (16, '00016', '周芷若', '女', 18, null, '北京', '2012-06-01');

5.1. 基本语法

DQL 查询语句，语法结构如下：

SELECT
字段列表

FROM
表名列表

WHERE
条件列表

GROUP BY
分组字段列表

HAVING
分组后条件列表

ORDER BY
排序字段列表

LIMIT
分页参数

基本查询（不带任何条件）

条件查询（WHERE）

聚合函数（count、max、min、avg、sum）

分组查询（group by）

排序查询（order by）

分页查询（limit）

这些具体的关键字后面一个一个讲

5.2. 基础查询

在基本查询的DQL语句中，不带任何的查询条件

  1. 查询多个字段：
SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;
SELECT * FROM 表名 ;

注意 : * 号代表查询所有字段。

  1. 查询时设置别名
SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;
SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;

使用时AS可以省略

  1. 去除重复记录*
SELECT DISTINCT 字段列表 FROM 表名;

需求：

在emp表中，查询指定字段 name, workno, age并返回

select name, workno, age from emp;

查询返回所有字段

select select id ,workno,name,gender,age,idcard,workaddress,entrydate from emp;

select * from emp;

查询所有员工的工作地址,起别名

select name as '姓名', workaddress as '工作地址' from emp;

select name '姓名', workaddress '工作地址' from emp;

查询公司员工的上班地址有哪些(不要重复)

select distinct workaddress from emp;

5.3. 条件查询

基本语法如下：

SELECT 字段列表 FROM 表名 WHERE 条件列表 ;

条件列表：

比较运算符

功能

>

大于

>=

大于等于

<

小于

<=

小于等于

=

等于

<> 或 !=

不等于

BETWEEN ... AND ...

在某个范围之内(含最小、最大值)

IN(...)

在in之后的列表中的值，多选一

LIKE 占位符

模糊匹配( _匹配单个字符, %匹配任意个字符)

IS NULL

是NULL

注意：

  • 在MySQL中，用于表示相等条件的运算符是单个等号=。也就是说，在编写SQL查询时，你应该使用=来比较两个值是否相等，而不是==。==是某些编程语言（比如Java）中用于比较相等性的运算符，但在SQL（包括MySQL）中，只有=用于表示相等。
  • 在MySQL中，<>和!=都可以用来表示不等于。这两个运算符的作用是相同的，都用于比较两个值是否不相等。但在实际使用中，<>可能较少见，更多的人倾向于使用!=来表示不等于。

逻辑运算符：

逻辑运算符

功能

AND 或 &&

并且 (多个条件同时成立)

OR 或 ||

或者 (多个条件任意一个成立)

NOT 或 !

非 , 不是

需求：

在emp表中，所有年龄等于 88 的员工信息

select * from emp where age = 88;

在emp表中，所有年龄小于 20 的员工信息

select * from emp where age < 20;

在emp表中，查询年龄小于等于 20 的员工信息

select * from emp where age <= 20;

在emp表中，查询没有身份证号的员工信息

select * from emp where idcard is null;

在emp表中，查询有身份证号的员工信息

select * from emp where idcard is not null;

在emp表中，查询年龄不等于 88 的员工信息

select * from emp where age != 88;
select * from emp where age <> 88;

在emp表中，查询年龄在15岁(包含) 到 20岁(包含)之间的员工信息

select * from emp where age >= 15 && age <= 20;
select * from emp where age >= 15 and age <= 20;
select * from emp where age between 15 and 20;

在emp表中，查询年龄等于18 或 20 或 40 的员工信息

select * from emp where age = 18 or age = 20 or age =40;
select * from emp where age in(18,20,40);

在emp表中，查询姓名为两个字的员工信息 _ %

select * from emp where name like '__';

这里是 __ 两个 '_'

在emp表中，查询身份证号最后一位是X的员工信息

select * from emp where idcard like '%X';
select * from emp where idcard like '_________________X';

5.4. 聚合函数

将一列数据作为一个整体，进行纵向计算

常见的聚合函数：

函数

功能

count

统计数量

max

最大值

min

最小值

avg

平均值

sum

求和

基本语法：

SELECT 聚合函数(字段列表) FROM 表名 ;

注意：NULL值是不参与所有聚合函数运算的

需求：

统计该企业员工数量

select count(*) from emp; -- 统计的是总记录数

select count(idcard) from emp; -- 统计的是idcard字段不为null的记录数

统计该企业员工的平均年龄

select avg(age) from emp;

统计该企业员工的最大年龄

select max(age) from emp;

统计该企业员工的最小年龄

select min(age) from emp;

E. 统计西安地区员工的年龄之和

select sum(age) from emp where workaddress = '西安';

5.5. 分组查询

基本语法：

SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后过滤条件 ];

GROUP BY用法：

GROUP BY语句通常与SELECT语句一起使用，用于指定需要分组的列。其基本语法如下：

SELECT 列1, 列2, ... FROM 表名 GROUP BY 列1, 列2, ...

其中，列1、列2等表示需要分组的列名，表名表示要查询的表名。

GROUP BY语句可以按照单个列、多个列或者表达式进行分组。同时，GROUP BY语句还可以与聚合函数一起使用，用于对每个分组进行计算。常用的聚合函数包括sum、avg、count、max、min等。通过在select语句中使用聚合函数，可以对每个分组的数据进行求和、平均值、计数、最大值、最小值等操作。

例如，要计算每个地区的销售总额，可以使用以下语句：

SELECT 地区, SUM(销售额) FROM 销售表 GROUP BY 地区

这个查询将返回每个地区的销售总额。

HAVING用法：

HAVING语句在SQL查询中用于对聚合函数的结果进行过滤。它通常与GROUP BY子句一起使用，以对分组后的数据进行过滤。

HAVING语句的基本语法如下：

SELECT 列1, 列2, ... FROM 表名 GROUP BY 列1, 列2, ... HAVING 条件

其中，条件是一个布尔表达式，用于对分组后的数据进行过滤。只有满足条件的分组才会被包括在查询结果中。

HAVING语句可以与聚合函数一起使用，以对聚合函数的结果进行过滤。例如，要筛选出销售总额超过1000的地区，可以使用以下查询：

SELECT 地区, SUM(销售额) FROM 销售表 GROUP BY 地区 HAVING SUM(销售额) > 1000

这个查询将返回销售总额超过1000的地区以及对应的销售总额。

需要注意的是，HAVING语句的执行时机是在GROUP BY分组后，对已经分组的记录进行过滤。因此，在使用HAVING语句时，必须先使用GROUP BY子句进行分组。

GROUP BY 和 HAVING：

两者通常一起使用，它们用于对数据进行分组和过滤

  • GROUP BY 用于将数据按照一个或多个字段进行分组。例如，如果你有一个包含员工信息的表，你可以按照部门（department）字段进行分组，这样每个部门的数据都会被聚合在一起。
  • HAVING 用于对分组后的数据进行过滤。它允许你根据聚合函数的结果来过滤数据。例如，你可以使用 HAVING 子句来过滤那些员工数量超过一定数量的部门。

WHERE、GROUP BY 和 HAVING 的执行时机：

  • WHERE：在查询中，WHERE 条件是首先执行的。它用于过滤出满足指定条件的记录，不满足条件的记录将被排除。在处理查询之前，WHERE 子句会先对数据进行过滤，然后再进行分组和聚合。
  • GROUP BY：GROUP BY 子句用于将数据按照指定的字段进行分组。它的执行时机是在 WHERE 条件过滤后，对满足条件的记录进行分组。一旦数据被分组，就可以对每个组进行聚合操作，如计数、求和等。
  • HAVING：HAVING 子句用于对分组后的数据进行过滤。它的执行时机是在 GROUP BY 分组后，对已经分组的记录进行过滤。与 WHERE 子句不同，HAVING 子句是对分组后的数据进行过滤，而不是对单个记录进行过滤。

需求：

根据性别分组 , 统计男性员工 和 女性员工的数量

select gender, count(*) from emp group by gender ;

根据性别分组 , 统计男性员工 和 女性员工的平均年龄

select gender, avg(age) from emp group by gender ;

查询年龄小于45的员工 , 并根据工作地址分组 , 获取员工数量大于等于3的工作地址

select workaddress, count(*) as address_count
from emp
where age < 45
group by
workaddress
having address_count >= 3;

统计各个工作地址上班的男性及女性员工的数量

select workaddress, gender, count(*) '数量' from emp group by gender , workaddress;

5.6. 排序查询

排序在日常开发中是非常常见的一个操作，有升序排序，也有降序排序。

基本语法：

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;

排序方式：

  • ASC : 升序(默认值，什么都不写就是升序排序)
  • DESC: 降序

注意事项：

  • 如果是升序, 可以不指定排序方式ASC
  • 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

需求：

根据年龄对公司的员工进行升序排序

select * from emp order by age asc;
select * from emp order by age;

根据入职时间, 对员工进行降序排序

select * from emp order by entrydate desc;

根据年龄对公司的员工进行升序排序 , 年龄相同 , 再按照入职时间进行降序排序

select * from emp order by age asc, entrydate desc;
select * from emp order by age, entrydate desc;

5.7. 分页查询

分页操作在业务系统开发时，也是非常常见的一个功能，我们在网站中看到的各种各样的分页条，后台都需要借助于数据库的分页操作。

基本语法：

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ;

注意事项:

  • 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
  • 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
  • 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

需求：

查询第1页员工数据, 每页展示10条记录

select * from emp limit 0,10;
select * from emp limit 10;

查询第2页员工数据, 每页展示10条记录 --------> (页码-1)*页展示记录数

select * from emp limit 10,10;

5.8. 执行顺序

DQL（数据查询语言）语句的执行顺序并不是按照我们书写的顺序，而是按照特定的逻辑顺序执行的。

语法书写是这样的：

SELECT
字段列表

FROM
表名列表

WHERE
条件列表

GROUP BY
分组字段列表

HAVING
分组后条件列表

ORDER BY
排序字段列表

LIMIT
分页参数

DQL语句在数据库中的实际执行顺序为：

  1. FROM：首先确定要从哪个表中检索数据。
  2. WHERE：接着对从表中选择的数据进行过滤。
  3. GROUP BY：然后根据指定的字段对数据进行分组。
  4. HAVING：对分组后的数据进行过滤。
  5. SELECT：选择要返回的字段。
  6. ORDER BY：对结果集进行排序。
  7. LIMIT：最后，根据指定的参数返回结果集的一部分。

这样的执行顺序是为了优化查询性能，首先确定需要哪些数据，然后对这些数据进行分组、过滤和排序，最后选择需要返回的字段。

值得注意的是，虽然SQL语法中GROUP BY通常写在WHERE之后，但实际上在执行时GROUP BY会在WHERE之前执行，这样可以尽早进行数据的聚合操作，提高查询效率。

6. DCL

DCL英文全称是Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访问权限。

6.1. 管理用户

6.1.1. 查询用户

select * from mysql.user;

mysql是MySQL的内置数据库，它包含了关于MySQL服务器自身的信息。其中，user 表存储了关于MySQL服务器上所有用户的信息，如用户名、密码、权限等。其中 Host代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问，是不可以远程访问的。

6.1.2. 创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

6.1.3. 修改用户密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ;

6.1.4. 删除用户

DROP USER '用户名'@'主机名' ;

注意事项:

? 在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户。

? 主机名可以使用 % 通配。

? 这类SQL开发人员操作的比较少，主要是DBA（ Database Administrator 数据库管理员）使用。

6.2. 权限控制

MySQL中定义了很多种权限，但是常用的就以下几种：

权限

说明

ALL, ALL PRIVILEGES

所有权限

SELECT

查询数据

INSERT

插入数据

UPDATE

修改数据

DELETE

删除数据

ALTER

修改表

DROP

删除数据库/表/视图

CREATE

创建数据库/表

上述只是简单罗列了常见的几种权限描述，其他权限描述及含义，建议直接参考官方文档。

6.2.1. 查询权限

SHOW GRANTS FOR '用户名'@'主机名' ;

6.2.2. 授予权限

GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

6.2.3. 撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注意事项：

  • 多个权限之间，使用逗号分隔
  • 授权时， 数据库名和表名可以使用 * 进行通配，代表所有。
文章来源:https://blog.csdn.net/Sakurapaid/article/details/135094901
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
最新文章