MySQL 入门

本文对 MySQL 的基础知识和常用操作进行介绍，是阅读《MySQL是怎样使用的：从零蛋开始学习MySQL》的笔记。

MySQL 数据类型

整数类型

类型	占用的存储空间	无符号数取值范围	有符号数取值范围	含义
`TINYINT`	1 字节	0 ~ 2⁸-1	-2⁷ ~ 2⁷-1	非常小的整数
`SMALLINT`	2 字节	0 ~ 2¹⁶-1	-2¹⁵ ~ 2¹⁵-1	小的整数
`MEDIUMINT`	3 字节	0 ~ 2²⁴-1	-2²³ ~ 2²³-1	中等大小的整数
`INT`	4 字节	0 ~ 2³²-1	-2³¹ ~ 2³¹-1	标准的整数
`BIGINT`	8 字节	0 ~ 2⁶⁴-1	-2⁶³ ~ 2⁶³-1	大整数

默认情况下，整数类型表示的是有符号整数。如果想表示无符号整数，需要在类型后加上单词UNSIGEND，例如TINYINT UNSIGEND。

浮点数类型

类型	占用的存储空间	绝对值最小非 0 值	绝对值最大值	含义
`FLOAT`	4 字节	±1.175494351E-38	±3.402823466E+38	单精度浮点数
`DOUBLE`	8 字节	±2.2250738585072014E-308	±1.7976931348623157E+308	双精度浮点数

同样，如果想表示无符号浮点数，在类型后加上单词UNSIGEND，例如FLOAT UNSIGEND。但是，无符号浮点数并不能增加所能表示的正数范围，所以不推荐使用。

定点数类型

浮点数表示小数可能会有不精确的情况，比如说 0.3，它转换成浮点数存储的是 0.29999....，是一个无限小数。定点数类型能提供更精确的小数存储：

类型	占用的存储空间（单位：字节）	取值范围
`DECIMAL(M, D)`	取决于 M 和 D	取决于 M 和 D

M：小数最多需要的十进制有效数字个数，例如：2.3 的有效数字个数为 2，0.9 的有效数字个数为 1。
D：小数的小数点后面的十进制有效数字个数，例如：2.3 和 0.9 的小数点后面的有效数字都为 1。

以DEMCIMAL(16, 4)为例，该定点数在小数点左边能存储的十进制位数是 12 位，小数点右边的能存储的十进制位数是 4 位，如图所示：

从小数点位置出发，每隔 9 个十进制位划分为 1 组，不足 9 个单独划分为 1 组，具体如下：

针对每个组中的十进制数字，将其转换为二进制数字进行存储，根据组中包含的十进制数字位数不同，所需的存储空间大小也不同：

组中包含的十进制位数	占用存储空间大小
1 或 2	1 字节
3 或 4	2 字节
5 或 6	3 字节
7 或 8 或 9	4 字节

因此，DECIMAL(16, 4)需要 8 个字节的存储空间。对于定点数类型DECIMAL(M, D)来说，M 的默认值为 10，取值范围为 1~65，D 的默认值是 0，取值范围为 0~30：

DECIMAL = DECIMAL(10) = DECIMAL(10, 0)
DECIMAL(n) = DECIMAL(n, 0)

如果想要表示无符号定点数类型，同样在类型后加上单词UNSIGEND，即DECIMAL UNSIGNED。但是，无符号定点数也不能增加所能表示的正数范围，所以不推荐使用。

日期和时间类型

类型	占用的存储空间	取值范围	含义
`YEAR`	1 字节	1901~2155	年份值
`DATE`	3 字节	`1000-01-01` ~ `9999-12-31`	日期值
`TIME`	3 字节 + 小数秒的存储空间	`-838:59:59[.000000]`~`838:59:59[.000000]`	时间值
`DATETIME`	5 字节 + 小数秒的存储空间	`1000-01-01 00:00:00[.000000]`～`9999-12-31 23:59:59[.999999]`	日期加时间值
`TIMESTAMP`	4 字节 + 小数秒的存储空间	`1970-01-01 00:00:01[.000000]`～`2038-01-19 03:14:07[.999999]`	时间戳

默认情况下，TIME、DATETIME、TIMESTAMP这几种类型精确到秒，如果想让它们支持小数秒并精确到具体位数，需要进行如下表示：

-- 小数秒位数可以在 0-6 中选择，例如：DATATIME(3) 表示精确到毫秒，DATATIME(6) 表示精确到微秒
类型(小数秒位数)

小数秒的保留增加了额外的存储空间，具体如下：

保留的小数秒位数	额外需要的存储空间
0	0 字节
1 或 2	1 字节
3 或 4	2 字节
5 或 6	3 字节

字符串类型

类型	最大长度	存储空间要求	含义
`CHAR(M)`	M 个字符	M×W 个字节	固定长度的字符串
`VARCHAR(M)`	M 个字符	L+1 或 L+2 个字节	可变长度的字符串
`TINYTEXT`	2⁸-1 个字节	L+1 个字节	非常小型的字符串
`TEXT`	2¹⁶-1 个字节	L+2 个字节	小型的字符串
`MEDIUMTEXT`	2²⁴-1 个字节	L+3 个字节	中等大小的字符串
`LONGTEXT`	2³²-1 个字节	L+4 个字节	大型的字符串

M 表示该数据类型最多能存储的字符数量，W 表示在特定字符集下编码一个字符最多需要的字节数，L 代表实际向该类型的列中存储的字符串在特定字符集下所占的字节数。

字符串还有两种特殊的类型：ENUM和SET。如果遇到需要选取使用固定字符串时，例如学生性别一列只能填男或女，就可以使用ENUM类型，它表示在给定的字符串列表里选择一个：

ENUM('str1', 'str2', 'str3' ⋯)
ENUM('男', '女')  -- 性别只能在 '男' 或 '女' 选择一个

有的时候某一列的值可以在给定的字符串列表中挑选多个，例如学生兴趣，就可以使用SET类型，它表示可以在给定的字符串列表里选择一个或多个：

SET('str1', 'str2', 'str3' ⋯)
SET('打球', '画画', '扯犊子', '玩游戏')  -- 兴趣可以挑选一个或多个

二进制类型

☕️ BIT类型

类型	字节数	含义
`BIT(M)`	近似为`(M+7)/8`	存储 M 个比特位的值

BIT类型用于存储一个或者多个二进制位。其中，M 的取值范围为1~64，默认值为 1，即BIT(1)和BIT等价。

⭐️ BINARY(M)与VARBINARY(M)

BINARY(M)和VARBINARY(M)分别存储是固定长度和可变长度的二进制数据，对应前文字符串类型中的CHAR(M)和VARCHAR(M)，只不过这里的 M 表示最多能存放的字节数量。

✏️ BLOB类型

TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB用于存储可变长度的二进制数据，对应前文字符串类型中的TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT。

数据库的基本操作

展示数据库

☕️ 语法：

SHOW DATABASES;

⭐️ 例子：

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.02 sec)

创建数据库

☕️ 语法：

CREATE DATABASE 数据库名;

-- 如果需要创建的数据库已经存在，上述语句执行会报错，为了避免这种错误发生，可以执行以下语句
CREATE DATABASE IF NOT EXISTS 数据库名;

⭐️ 例子：

mysql> CREATE DATABASE learning;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE DATABASE IF NOT EXISTS learning;
Query OK, 1 row affected (0.01 sec)

切换当前数据库

☕️ 语法：

USE 数据库名称;

⭐️ 例子：

mysql> USE learning;
Database changed

删除数据库

☕️ 语法：

DROP DATABASE 数据库名;

-- 如果需要删除的数据库已经不存在，上述语句执行会报错，为了避免这种错误发生，可以执行以下语句
DROP DATABASE IF EXISTS 数据库名;

⭐️ 例子：

mysql> DROP DATABASE learning;
Query OK, 0 rows affected (0.01 sec)

mysql> DROP DATABASE IF EXISTS learning;
Query OK, 0 rows affected (0.01 sec)

表的基本操作

展示数据库中的表

☕️ 语法：

SHOW TABLES FROM 数据库名;

-- 如果已经选择了默认的数据库，可以直接使用 SHOW TABLES 语句
SHOW TABLES;

⭐️ 例子：

mysql> SHOW TABLES;
Empty set

创建表

☕️ 语法：

-- 如果定义的表名或者列名与 MySQL 中保留字重复，为避免歧义，可以将它们放在反引号 `` 中
-- 命名如果有多个单词的话，单词之间最好使用下划线连接起来，首字母全部小写，比如 first_column
CREATE TABLE 表名 (
    列名1 数据类型 [列的属性],
    列名2 数据类型 [列的属性],
     ...
    列名n 数据类型 [列的属性]
);

-- 可以在建表语句中为表或者列添加注释
CREATE TABLE 表名 (
    列名1 数据类型 [列的属性] COMMENT '列相关注释',
    列名2 数据类型 [列的属性] COMMENT '列相关注释',
     ...
    列名n 数据类型 [列的属性] COMMENT '列相关注释'
) COMMENT '表相关注释';

-- 如果需要创建的表已经存在，上述语句执行会报错，为了避免这种错误发生，可以执行以下语句
CREATE TABLE IF NOT EXISTS 表名 (
    各个列的信息 ...
);

⭐️ 例子：

CREATE TABLE first_table (
    first_column INT,
    second_column VARCHAR(100)
);

CREATE TABLE student_info (
    number INT COMMENT '学号',   
    name VARCHAR(5) COMMENT '姓名',
    sex ENUM('男', '女') COMMENT '性别',
    id_number CHAR(18) COMMENT '身份证号',
    department VARCHAR(30) COMMENT '学院',
    major VARCHAR(30) COMMENT '专业',
    enrollment_time DATE COMMENT '入学时间'
) COMMENT '学生基本信息表';

CREATE TABLE IF NOT EXISTS student_score (
    number INT COMMENT '学号',
    subject VARCHAR(30) COMMENT '科目',
    score TINYINT COMMENT '成绩'
) COMMENT '学生成绩表';

删除表

☕️ 语法：

DROP TABLE 表1, 表2, ..., 表n;

-- 如果需要删除的表已经不存在，上述语句执行会报错，为了避免这种错误发生，可以执行以下语句
DROP TABLE IF EXISTS 表1, 表2, ..., 表n;

⭐️ 例子：

mysql> DROP TABLE first_table;
Query OK, 0 rows affected (0.01 sec)

mysql> DROP TABLE IF EXISTS first_table;
Query OK, 0 rows affected (0.00 sec)

查看表结构

☕️ 语法：

-- 以表格方式展示表中各个列信息
DESC 表名;

-- 查看建表语句
SHOW CREATE TABLE 表名;

⭐️ 例子：

mysql> DESC student_score;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| number  | int(11)     | YES  |     | NULL    |       |
| subject | varchar(30) | YES  |     | NULL    |       |
| score   | tinyint(4)  | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> SHOW CREATE TABLE student_score;
CREATE TABLE `student_score` (
  `number` int(11) DEFAULT NULL COMMENT '学号',
  `subject` varchar(30) DEFAULT NULL COMMENT '科目',
  `score` tinyint(4) DEFAULT NULL COMMENT '成绩'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='学生成绩表';

修改表名

☕️ 语法：

-- 方式一：
ALTER TABLE 旧表名 RENAME TO 新表名;

-- 方式二，相对于方式一，它可以在一条语句中修改多个表的名称
RENAME TABLE 旧表名1 TO 新表名1, 旧表名2 TO 新表名2, ... 旧表名n TO 新表名n;

⭐️ 例子：

mysql> ALTER TABLE first_table RENAME TO first_table1;
Query OK, 0 rows affected (0.06 sec)

mysql> RENAME TABLE first_table1 TO first_table;
Query OK, 0 rows affected (0.07 sec)

注意：表的修改操作语法开头都是ALTER TABLE 表名，下面的所有表修改操作的语法开头都是这样。

增加列

☕️ 语法：

ALTER TABLE 表名 ADD COLUMN 列名 数据类型 [列的属性];

-- 添加到第一列
ALTER TABLE 表名 ADD COLUMN 列名 列的类型 [列的属性] FIRST;

-- 添加到指定列的后边
ALTER TABLE 表名 ADD COLUMN 列名 列的类型 [列的属性] AFTER 指定列名;

⭐️ 例子：

-- 把名为 third_column 的列添加到 first_table 表
mysql> ALTER TABLE first_table ADD COLUMN third_column CHAR(4);
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 把名为 fourth_column 的列添加到 first_table 表的第一列
mysql> ALTER TABLE first_table ADD COLUMN fourth_column CHAR(4) FIRST;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 把名为 fifth_column 的列添加到 first_table 表的 first_column 列之后
mysql> ALTER TABLE first_table ADD COLUMN fifth_column CHAR(4) AFTER first_column;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> DESC first_table;
+---------------+--------------+------+-----+---------+-------+
| Field         | Type         | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+-------+
| fourth_column | char(4)      | YES  |     | NULL    |       |
| first_column  | int(11)      | YES  |     | NULL    |       |
| fifth_column  | char(4)      | YES  |     | NULL    |       |
| second_column | varchar(100) | YES  |     | NULL    |       |
| third_column  | char(4)      | YES  |     | NULL    |       |
+---------------+--------------+------+-----+---------+-------+
5 rows in set (0.05 sec)

删除列

☕️ 语法：

ALTER TABLE 表名 DROP COLUMN 列名;

⭐️ 例子：

mysql> ALTER TABLE first_table DROP COLUMN fifth_column;
Query OK, 0 rows affected (0.16 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改列信息

☕️ 语法：

-- 方式一
ALTER TABLE 表名 MODIFY 列名 新数据类型 [新属性];

-- 方式二，相对于方式一，不但可以修改数据类型和属性，还可以修改列名
ALTER TABLE 表名 CHANGE 旧列名 新列名 新数据类型 [新属性];

-- 可以使用方式一修改列的排序顺序
-- 将列设为表的第一列
ALTER TABLE 表名 MODITY 列名 列的类型 列的属性 FIRST;
-- 将列放到指定列的后边
ALTER TABLE 表名 MODITY 列名 列的类型 列的属性 AFTER 指定列名;

⭐️ 例子：

-- 修改 first_table 表中的 second_column 列的数据类型为 VARCHAR(2)
mysql> ALTER TABLE first_table MODIFY second_column VARCHAR(2);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 修改 first_table 表中的 second_column 列名为 second_column1，数据类型为 VARCHAR(2)
mysql> ALTER TABLE first_table CHANGE second_column second_column1 VARCHAR(2);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 将 second_column1 列放在 first_table 表的第一列
mysql> ALTER TABLE first_table MODIFY second_column1 VARCHAR(2) FIRST;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 将 second_column1 列放在 first_table 表的 first_column 列后面
mysql> ALTER TABLE first_table MODIFY second_column1 VARCHAR(2) AFTER first_column;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

列的属性

DEFAULT

☕️ 语法：

-- 设置列的默认值
列名 列的类型 DEFAULT 默认值

⭐️ 例子：

DROP TABLE IF EXISTS first_table;
CREATE TABLE first_table ( 
    first_column INT DEFAULT NULL, 
    second_column VARCHAR (100) DEFAULT 'abc' 
);

-- 插入一条记录，second_column 列的值被赋予默认值 abc
mysql> INSERT INTO first_table(first_column) VALUES(1);
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | abc           |
+--------------+---------------+
1 row in set (0.01 sec)

NOT NULL

☕️ 语法：

-- 设置列中必须有值，不能为 NULL
列名 列的类型 NOT NULL

⭐️ 例子：

DROP TABLE IF EXISTS first_table;
CREATE TABLE first_table (
    first_column INT NOT NULL,
    second_column VARCHAR(100) DEFAULT 'abc'
);

-- 插入一条 first_column 列值为 NULL 的记录，会报错
mysql> INSERT INTO first_table(first_column, second_column) VALUES(NULL, 'aaa');
1048 - Column 'first_column' cannot be null

主键

☕️ 语法：

-- 能够唯一确定一条记录的某个列或者某些列称为候选键，一个表可以有多个候选键，但只有一个候选键能作为表的主键
-- 主键列默认是有 NOT NULL 属性和唯一性，所以不能给主键赋 NULL 值或者已存在的键值

-- 方式一，在列后面声明 PRIMARY KEY 指定主键
列名 列的类型 PRIMARY KEY

-- 方式二，把主键的声明单独提取出来指定主键。如果声明的是联合主键，只能使用该方式
PRIMARY KEY (列名1, 列名2, ...)

⭐️ 例子：

-- 使用方式一指定主键
DROP TABLE IF EXISTS student_info;
CREATE TABLE student_info (
    number INT PRIMARY KEY,
    name VARCHAR(5),
    sex ENUM('男', '女'),
    id_number CHAR(18),
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE
);

-- 使用方式二声明联合主键
DROP TABLE IF EXISTS student_score;
CREATE TABLE student_score (
    number INT,
    subject VARCHAR(30),
    score TINYINT,
    PRIMARY KEY (number, subject)
);

UNIQUE

☕️ 语法：

-- 除主键外，其它某个列或者列组合中存储的值也希望具有唯一性，可以使用 UNIQUE 约束

-- 方式一，列后面声明 UNIQUE 或者 UNIQUE KEY
列名 列的类型 UNIQUE [KEY]

-- 方式二，把 UNIQE 属性的声明单独提取出来要求不能重复。如果声明的是列组合的唯一性，只能使用该方式
-- 每一个约束都具有名称，主键约束的名称是 PRIMARY（不能修改），UNIQE 的约束名称可以自定义
UNIQUE [KEY] [约束名称] (列名1, 列名2, ...)

⭐️ 例子：

-- 使用方式一对 id_number 列进行 UNIQUE 约束
DROP TABLE IF EXISTS student_info;
CREATE TABLE student_info (
    number INT PRIMARY KEY,
    name VARCHAR(5),
    sex ENUM('男', '女'),
    id_number CHAR(18) UNIQUE,
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE
);

-- 使用方式二对 id_number 列进行 UNIQUE 约束，并命名为 uk_id_number
DROP TABLE IF EXISTS student_info;
CREATE TABLE student_info (
    number INT PRIMARY KEY,
    name VARCHAR(5),
    sex ENUM('男', '女'),
    id_number CHAR(18),
    department VARCHAR(30),
    major VARCHAR(30),
    enrollment_time DATE,
    UNIQUE KEY uk_id_number (id_number)
);

✏️ 主键和UNIQUE约束的对比：

一张表中只能定义一个主键，却可以定义多个UNIQUE约束。
主键列不允许存放NULL，而声明了UNIQUE属性的列可以存放NULL，而且NULL可以重复地出现在多条记录中。NULL其实并不是一个值，它代表不确定，我们平常说某个列的值为NULL，意味着这一列的值尚未被填入，所以声明了UNQIUE属性的列可以重复存放NULL。
如果没有给表定义主键，第一个声明为NOT NULL且具有UNIQUE约束的列或者列组合会自动被定义为主键。

外键

☕️ 语法：

-- 如果 A 表中的某个列或者某些列依赖于 B 表中的某个列或者某些列，那么就称 A 表为子表，B 表为父表
-- 子表和父表是使用外键进行关联，外键定义在子表中。定义外键之后，子表插入数据时会检查父表中是否存在数据
-- 父表中被子表依赖的列或者列组合必须建立索引，如果该列或者列组合已经是主键或者有 UNIQUE 属性，那么它们也就被默认建立了索引
CONSTRAINT [外键名称] FOREIGN KEY(列1, 列2, ...) REFERENCES 父表名(父列1, 父列2, ...);

⭐️ 例子：

DROP TABLE IF EXISTS student_score;
CREATE TABLE student_score (
    number INT,
    subject VARCHAR(30),
    score TINYINT,
    PRIMARY KEY (number, subject),
    CONSTRAINT FOREIGN KEY(number) REFERENCES student_info(number)
);

AUTO_INCREMENT

☕️ 语法：

-- 如果一个表中的某个列的数据类型是整数类型或者浮点数类型，并被设置了 AUTO_INCREMENT 属性
-- 当插入新记录时不指定该列值，或者将该列值显式地指定为 NULL 或者 0，最终该列值会被设置为该列最大值加 1
列名 列的类型 AUTO_INCREMENT

⭐️ 例子：

DROP TABLE IF EXISTS first_table;
CREATE TABLE first_table (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_column INT,
    second_column VARCHAR(100) DEFAULT 'abc'
);

-- AUTO_INCREMENT 默认是从 1 开始递增
INSERT INTO first_table(first_column, second_column) VALUES(1, 'aaa');
INSERT INTO first_table(id, first_column, second_column) VALUES(NULL, 1, 'aaa');
INSERT INTO first_table(id, first_column, second_column) VALUES(0, 1, 'aaa');

mysql> SELECT * FROM first_table;
+----+--------------+---------------+
| id | first_column | second_column |
+----+--------------+---------------+
|  1 |            1 | aaa           |
|  2 |            1 | aaa           |
|  3 |            1 | aaa           |
+----+--------------+---------------+
3 rows in set (0.03 sec)

✏️ 注意事项：

一个表中最多有一个具有AUTO_INCREMENT属性的列。
具有AUTO_INCREMENT属性的列必须建立索引，主键和具有UNIQUE属性的列会被自动建立索引。
拥有AUTO_INCREMENT属性的列就不能再通过指定DEFAULT属性来指定默认值。
一般拥有AUTO_INCREMENT属性的列都是作为主键的属性，来自动生成唯一标识一条记录的主键值。

COMMENT

-- 在语句的末尾通过添加 COMMENT 来对表或者列的含义进行注释
DROP TABLE IF EXISTS first_table;
CREATE TABLE first_table (
    id int UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '自增主键',
    first_column INT COMMENT '第一列',
    second_column VARCHAR(100) DEFAULT 'abc' COMMENT '第二列'
) COMMENT '第一个表';

简单查询

-- 前期准备
DROP TABLE IF EXISTS student_score;
DROP TABLE IF EXISTS student_info;

CREATE TABLE student_info (
  number INT PRIMARY KEY COMMENT '学号',
  name VARCHAR(5) COMMENT '姓名',
  sex ENUM('男','女') COMMENT '性别',
  id_number CHAR(18) COMMENT '身份证号',
  department VARCHAR(30) COMMENT '学院',
  major VARCHAR(30) COMMENT '专业',
  enrollment_time DATE COMMENT '入学时间',
  UNIQUE KEY (id_number)
) COMMENT='学生基本信息表';

CREATE TABLE student_score (
    number INT COMMENT '学号',
    subject VARCHAR(30) COMMENT '科目',
    score TINYINT COMMENT '成绩',
    PRIMARY KEY (number, subject),
    CONSTRAINT FOREIGN KEY(number) REFERENCES student_info(number)
) COMMENT '学生成绩表';

INSERT INTO student_info (number, NAME, sex, id_number, department, major, enrollment_time) VALUES
    (20210101, 'Alice', '男', '158177199901044792', '计算机学院', '计算机科学与工程', '2021-09-01'),
    (20210102, 'Bob', '男', '151008199801178529', '计算机学院', '计算机科学与工程', '2021-09-01'),
    (20210103, 'Lee', '女', '17156319980116959X', '计算机学院', '软件工程', '2021-09-01'),
    (20210104, 'Bella', '男', '141992199701078600', '计算机学院', '软件工程', '2021-09-01'),
    (20210105, 'Ray', '女', '181048199308156368', '航天学院', '飞行器设计', '2021-09-01'),
    (20210106, 'Tina', '男', '197995199501078445', '航天学院', '电子信息', '2021-09-01');
    
INSERT INTO student_score (number, SUBJECT, score) VALUES
    (20210101, '数据结构', 78 ),
    (20210101, '操作系统', 88 ),
    (20210102, '数据结构', 100 ),
    (20210102, '操作系统', 98 ),
    (20210103, '数据结构', 59 ),
    (20210103, '操作系统', 61 ),
    (20210104, '数据结构', 55 ),
    (20210104, '操作系统', 46 );

查询单个列

☕️ 语法：

SELECT 列名 FROM 表名;

⭐️ 例子：

mysql> SELECT number FROM student_info;
+----------+
| number   |
+----------+
| 20210104 |
| 20210102 |
| 20210101 |
| 20210103 |
| 20210105 |
| 20210106 |
+----------+
6 rows in set (0.04 sec)

列的别名

☕️ 语法：

-- 可以为查询结果中的列定义一个别名
SELECT 列名 [AS] 列的别名 FROM 表名;

⭐️ 例子：

mysql> SELECT number AS 学号 FROM student_info;
+----------+
| 学号     |
+----------+
| 20210104 |
| 20210102 |
| 20210101 |
| 20210103 |
| 20210105 |
| 20210106 |
+----------+
6 rows in set (0.04 sec)

查询多个列

☕️ 语法：

SELECT 列名1, 列名2, ... 列名n FROM 表名;

⭐️ 例子：

mysql> SELECT number, name, id_number, major FROM student_info;
+----------+-------+--------------------+------------------+
| number   | name  | id_number          | major            |
+----------+-------+--------------------+------------------+
| 20210101 | Alice | 158177199901044792 | 计算机科学与工程 |
| 20210102 | Bob   | 151008199801178529 | 计算机科学与工程 |
| 20210103 | Lee   | 17156319980116959X | 软件工程         |
| 20210104 | Bella | 141992199701078600 | 软件工程         |
| 20210105 | Ray   | 181048199308156368 | 飞行器设计       |
| 20210106 | Tina  | 197995199501078445 | 电子信息         |
+----------+-------+--------------------+------------------+
6 rows in set (0.04 sec)

查询全部列

☕️ 语法：

SELECT * FROM 表名;

⭐️ 例子：

mysql> SELECT * FROM student_info;
+----------+-------+-----+--------------------+------------+------------------+-----------------+
| number   | name  | sex | id_number          | department | major            | enrollment_time |
+----------+-------+-----+--------------------+------------+------------------+-----------------+
| 20210101 | Alice | 男  | 158177199901044792 | 计算机学院 | 计算机科学与工程 | 2021-09-01      |
| 20210102 | Bob   | 男  | 151008199801178529 | 计算机学院 | 计算机科学与工程 | 2021-09-01      |
| 20210103 | Lee   | 女  | 17156319980116959X | 计算机学院 | 软件工程         | 2021-09-01      |
| 20210104 | Bella | 男  | 141992199701078600 | 计算机学院 | 软件工程         | 2021-09-01      |
| 20210105 | Ray   | 女  | 181048199308156368 | 航天学院   | 飞行器设计       | 2021-09-01      |
| 20210106 | Tina  | 男  | 197995199501078445 | 航天学院   | 电子信息         | 2021-09-01      |
+----------+-------+-----+--------------------+------------+------------------+-----------------+
6 rows in set (0.05 sec)

查询结果去重

☕️ 语法：

-- 去除单列的重复结果
SELECT DISTINCT 列名 FROM 表名;

-- 去除多列的重复结果
SELECT DISTINCT 列名1, 列名2, ... 列名n  FROM 表名;

⭐️ 例子：

mysql> SELECT department FROM student_info;
+------------+
| department |
+------------+
| 计算机学院 |
| 计算机学院 |
| 计算机学院 |
| 计算机学院 |
| 航天学院   |
| 航天学院   |
+------------+
6 rows in set (0.05 sec)

mysql> SELECT DISTINCT department FROM student_info;
+------------+
| department |
+------------+
| 计算机学院 |
| 航天学院   |
+------------+
2 rows in set (0.06 sec)

mysql> SELECT department, major FROM student_info;
+------------+------------------+
| department | major            |
+------------+------------------+
| 计算机学院 | 计算机科学与工程 |
| 计算机学院 | 计算机科学与工程 |
| 计算机学院 | 软件工程         |
| 计算机学院 | 软件工程         |
| 航天学院   | 飞行器设计       |
| 航天学院   | 电子信息         |
+------------+------------------+
6 rows in set (0.04 sec)

mysql> SELECT DISTINCT department, major FROM student_info;
+------------+------------------+
| department | major            |
+------------+------------------+
| 计算机学院 | 计算机科学与工程 |
| 计算机学院 | 软件工程         |
| 航天学院   | 飞行器设计       |
| 航天学院   | 电子信息         |
+------------+------------------+
4 rows in set (0.05 sec)

限制结果集记录条数

☕️ 语法：

-- 如果不声明偏移量，默认为 0。结果集中的第 1 条记录的偏移量是 0，第 2 条记录的偏移量是 1 
LIMIT 限制条数;
LIMIT 偏移量, 限制条数;

⭐️ 例子：

mysql> SELECT number, name, id_number, major FROM student_info LIMIT 2, 2;
+----------+-------+--------------------+----------+
| number   | name  | id_number          | major    |
+----------+-------+--------------------+----------+
| 20210103 | Lee   | 17156319980116959X | 软件工程 |
| 20210104 | Bella | 141992199701078600 | 软件工程 |
+----------+-------+--------------------+----------+
2 rows in set (0.06 sec)

对查询结果排序

☕️ 语法：

-- ASC 是从小到大排序（升序），DESC 是从大到小排序（降序），默认是升序
-- 按照单个列的值进行排序
ORDER BY 列名 [ASC|DESC]

-- 按照多个列的值进行排序，先按照列1排序，对于列1中相同的值，再按照列2排序...
ORDER BY 列1 [ASC|DESC], 列2 [ASC|DESC], ...

⭐️ 例子：

-- 按照 score 升序排序
mysql> SELECT * FROM student_score ORDER BY score;
+----------+----------+-------+
| number   | subject  | score |
+----------+----------+-------+
| 20210104 | 操作系统 |    46 |
| 20210104 | 数据结构 |    55 |
| 20210103 | 数据结构 |    59 |
| 20210103 | 操作系统 |    61 |
| 20210101 | 数据结构 |    78 |
| 20210101 | 操作系统 |    88 |
| 20210102 | 操作系统 |    98 |
| 20210102 | 数据结构 |   100 |
+----------+----------+-------+
8 rows in set (0.04 sec)

-- 先按照 subject 升序排序，对于 subject 列值相同的记录，再按照 score 降序排序
mysql> SELECT * FROM student_score ORDER BY subject, score DESC;
+----------+----------+-------+
| number   | subject  | score |
+----------+----------+-------+
| 20210102 | 操作系统 |    98 |
| 20210101 | 操作系统 |    88 |
| 20210103 | 操作系统 |    61 |
| 20210104 | 操作系统 |    46 |
| 20210102 | 数据结构 |   100 |
| 20210101 | 数据结构 |    78 |
| 20210103 | 数据结构 |    59 |
| 20210104 | 数据结构 |    55 |
+----------+----------+-------+
8 rows in set (0.04 sec)

带搜索条件的查询

简单搜索条件

SQL 把搜索条件放在WHERE子句中，通过运算符构造，常用的运算符如下：

运算符	示例	描述
`=`	`a = b`	a 等于 b
`<>`或者`!=`	`a <> b`	a 不等于 b
`<`	`a < b`	a 小于 b
`<=`	`a <= b`	a 小于或等于 b
`>`	`a > b`	a 大于 b
`>=`	`a >= b`	a 大于或等于 b
`BETWEEN`	`a BETWEEN b AND c`	a 的值必须满足 b <= a <= c
`NOT BETWEEN`	`a NOT BETWEEN b AND c`	a 的值必须不满足 b <= a <= c

☕️ 例子：

-- 查询 name 为 Alice 的学生记录
mysql> SELECT number, name, id_number, major FROM student_info WHERE name = 'Alice';
+----------+-------+--------------------+------------------+
| number   | name  | id_number          | major            |
+----------+-------+--------------------+------------------+
| 20210101 | Alice | 158177199901044792 | 计算机科学与工程 |
+----------+-------+--------------------+------------------+
1 row in set (0.05 sec)

-- 查询学号在 20210102~20210104 间的学生信息
mysql> SELECT number, name, id_number, major FROM student_info WHERE number BETWEEN 20210102 AND 20210104;
+----------+-------+--------------------+------------------+
| number   | name  | id_number          | major            |
+----------+-------+--------------------+------------------+
| 20210102 | Bob   | 151008199801178529 | 计算机科学与工程 |
| 20210103 | Lee   | 17156319980116959X | 软件工程         |
| 20210104 | Bella | 141992199701078600 | 软件工程         |
+----------+-------+--------------------+------------------+
3 rows in set (0.04 sec)

-- 查询学号不在 20210102~20210104 间的学生信息
mysql> SELECT number, name, id_number, major FROM student_info WHERE number NOT BETWEEN 20210102 AND 20210104;
+----------+-------+--------------------+------------------+
| number   | name  | id_number          | major            |
+----------+-------+--------------------+------------------+
| 20210101 | Alice | 158177199901044792 | 计算机科学与工程 |
| 20210105 | Ray   | 181048199308156368 | 飞行器设计       |
| 20210106 | Tina  | 197995199501078445 | 电子信息         |
+----------+-------+--------------------+------------------+
3 rows in set (0.04 sec)

匹配列表中的元素

MySQL 使用IN和NOT IN两个运算符来匹配列表中的元素：

运算符	示例	描述
`IN`	`a IN (b1,b2,...)`	a 是`b1,b2,...`中的某一个
`NOT IN`	`a NOT IN (b1,b2,...)`	a 不是`b1,b2,...`中的任意一个

☕️ 例子：

-- 查询专业为软件工程和飞行器设计的学生
mysql> SELECT number, name, id_number, major FROM student_info WHERE major IN ('软件工程', '飞行器设计');
+----------+-------+--------------------+------------+
| number   | name  | id_number          | major      |
+----------+-------+--------------------+------------+
| 20210103 | Lee   | 17156319980116959X | 软件工程   |
| 20210104 | Bella | 141992199701078600 | 软件工程   |
| 20210105 | Ray   | 181048199308156368 | 飞行器设计 |
+----------+-------+--------------------+------------+
3 rows in set (0.04 sec)

-- 查询专业不是为软件工程和飞行器设计的学生
mysql> SELECT number, name, id_number, major FROM student_info WHERE major NOT IN ('软件工程', '飞行器设计');
+----------+-------+--------------------+------------------+
| number   | name  | id_number          | major            |
+----------+-------+--------------------+------------------+
| 20210101 | Alice | 158177199901044792 | 计算机科学与工程 |
| 20210102 | Bob   | 151008199801178529 | 计算机科学与工程 |
| 20210106 | Tina  | 197995199501078445 | 电子信息         |
+----------+-------+--------------------+------------------+
3 rows in set (0.04 sec)

匹配 NULL

某个列的值为NULL，意味着该值尚未确定。在判断某一列的值为NULL时，不能使用=或!=运算符，而是必须使用专门的运算符IS NULL或IS NOT NULL。

运算符	示例	描述
`IS NULL`	`a IS NULL`	a 的值是 NULL
`IS NOT NULL`	`a IS NOT NULL`	a 的值不是 NULL

☕️ 例子：

mysql> SELECT number, name, id_number, major FROM student_info WHERE name is NOT NULL;
+----------+-------+--------------------+------------------+
| number   | name  | id_number          | major            |
+----------+-------+--------------------+------------------+
| 20210101 | Alice | 158177199901044792 | 计算机科学与工程 |
| 20210102 | Bob   | 151008199801178529 | 计算机科学与工程 |
| 20210103 | Lee   | 17156319980116959X | 软件工程         |
| 20210104 | Bella | 141992199701078600 | 软件工程         |
| 20210105 | Ray   | 181048199308156368 | 飞行器设计       |
| 20210106 | Tina  | 197995199501078445 | 电子信息         |
+----------+-------+--------------------+------------------+
6 rows in set (0.05 sec)

多个搜索条件

在一个查询语句中可以指定多个搜索条件，通过AND运算符或者OR运算符进行连接。

-- 查询 subject 为数据结构且 score 大于 75 分的记录 
mysql> SELECT * FROM student_score WHERE subject = '数据结构' AND score > 75;
+----------+----------+-------+
| number   | subject  | score |
+----------+----------+-------+
| 20210101 | 数据结构 |    78 |
| 20210102 | 数据结构 |   100 |
+----------+----------+-------+
2 rows in set (0.06 sec)

-- 查询 score 大于 95 分或者小于 55 分的记录
mysql> SELECT * FROM student_score WHERE score > 95 OR score < 55;
+----------+----------+-------+
| number   | subject  | score |
+----------+----------+-------+
| 20210102 | 操作系统 |    98 |
| 20210102 | 数据结构 |   100 |
| 20210104 | 操作系统 |    46 |
+----------+----------+-------+
3 rows in set (0.04 sec)

-- AND 的优先级高于 OR，下面两条语句是等价的
SELECT * FROM student_score WHERE score > 95 OR score < 55 AND subject = '数据结构';
SELECT * FROM student_score WHERE score > 95 OR (score < 55 AND subject = '数据结构');

通配符

MySQL 使用LIKE和NOT LIKE两个运算符来支持模糊查询：

运算符	示例	描述
`LIKE`	`a LIKE b`	a 匹配 b
`NOT LIKE`	`a NOT LIKE b`	a 不匹配 b

MySQL 支持以下两个通配符：

%：代表任意数量的字符，0 个字符也可以
_：代表任意一个字符

☕️ 例子：

-- 查询 id_number 中包含 '1998' 的记录
mysql> SELECT number, name, id_number, major FROM student_info WHERE id_number LIKE '%1998%';
+----------+------+--------------------+------------------+
| number   | name | id_number          | major            |
+----------+------+--------------------+------------------+
| 20210102 | Bob  | 151008199801178529 | 计算机科学与工程 |
| 20210103 | Lee  | 17156319980116959X | 软件工程         |
+----------+------+--------------------+------------------+
2 rows in set (0.04 sec)

-- 查询 id_number 前 17 位为 '17156319980116959' 的记录
mysql> SELECT number, name, id_number, major FROM student_info WHERE id_number LIKE '17156319980116959_';
+----------+------+--------------------+----------+
| number   | name | id_number          | major    |
+----------+------+--------------------+----------+
| 20210103 | Lee  | 17156319980116959X | 软件工程 |
+----------+------+--------------------+----------+
1 row in set (0.04 sec)

-- 如果待匹配的字符串中本身就包含字符 '%' 或者 '_'，可以通过转义来区分是普通字符还是通配符
-- '\%' 代表普通字符 '%'，'\_' 代表普通字符 '_'
SELECT number, name, id_number, major FROM student_info WHERE id_number LIKE '\%1998\%';

表达式和函数

常用运算符

☕️ 算术运算符

运算符	示例	描述
`+`	`a + b`	加法
`-`	`a - b`	减法
`*`	`a * b`	乘法
`/`	`a / b`	除法
`DIV`	`a DIV b`	取模，取商的整数部分
`%`	`a % b`	取余
`-`	`-a`	负号

算术运算符中的DIV和/都是除法，但是DIV只会取商的整数部分，而/会保留商的小数部分。

⭐️ 比较运算符

运算符	示例	描述
`=`	`a = b`	a 等于 b
`<>`或者`!=`	`a <> b`	a 不等于 b
`<`	`a < b`	a 小于 b
`<=`	`a <= b`	a 小于或等于 b
`>`	`a > b`	a 大于 b
`>=`	`a >= b`	a 大于或等于 b
`BETWEEN`	`a BETWEEN b AND c`	满足 b <= a <= c
`NOT BETWEEN`	`a NOT BETWEEN b AND c`	不满足 b <= a <= c
`IN`	`a IN (b1,b2,...)`	a 是`b1,b2,...`中的某一个
`NOT IN`	`a NOT IN (b1,b2,...)`	a 不是`b1,b2,...`中的任意一个
`IS NULL`	`a IS NULL`	a 的值是`NULL`
`IS NOT NULL`	`a IS NOT NULL`	a 的值不是`NULL`
`LIKE`	`a LIKE b`	a 匹配 b
`NOT LIKE`	`a NOT LIKE b`	a 不匹配 b

✏️ 逻辑操作符

操作符	示例	描述
`NOT`（也可写作`!`）	`NOT a`	对 a 的结果取反
`AND`（也可写作`&&`）	`a AND b`	只有 a 和 b 同时为真，表达式才为真
`OR`（也可写作`\|\|`）	`a OR b`	只要 a 或 b 有任意一个为真，表达式就为真
`XOR`	`a XOR b`	a 和 b 有且只有一个为真，表达式为真

字符串处理函数

名称	调用示例	示例结果	描述
`LEFT`	`LEFT('abc123', 3)`	`abc`	给定字符串从左边取指定长度的子串
`RIGHT`	`RIGHT('abc123', 3)`	`123`	给定字符串从右边取指定长度的子串
`LENGTH`	`LENGTH('abc')`	`3`	给定字符串占用的字节数量
`LOWER`	`LOWER('ABC')`	`abc`	给定字符串的小写格式
`UPPER`	`UPPER('abc')`	`ABC`	给定字符串的大写格式
`LTRIM`	`LTRIM(' abc')`	`abc`	给定字符串左边空格去除后的格式
`RTRIM`	`RTRIM('abc ')`	`abc`	给定字符串右边空格去除后的格式
`SUBSTRING`	`SUBSTRING('abc123', 2, 3)`	`bc1`	给定字符串从指定位置截取指定长度的子串
`CONCAT`	`CONCAT('abc', '123', 'xyz')`	`abc123xyz`	将给定的各个字符串拼接成一个新字符串
`CHAR_LENGTH`	`CHAR_LENGTH('abc')`	`3`	给定字符串的字符数量
`SUBSTRING_INDEX`	`SUBSTRING_INDEX('www.wiki.com', '.', 1)`	`www`	截取字符串（后文进一步说明）

☕️ 例子：

-- 从字符串 'abc123' 的第 2 个字符开始，向后获取 3 个字符的子串
mysql> SELECT SUBSTRING('abc123', 2, 3);
+---------------------------+
| SUBSTRING('abc123', 2, 3) |
+---------------------------+
| bc1                       |
+---------------------------+
1 row in set (0.04 sec)

-- 拼接字符串
mysql> SELECT CONCAT('学号为', number, '的学生在《', subject, '》课程的成绩是：', score) AS 成绩描述 FROM student_score;
+-----------------------------------------------------+
| 成绩描述                                            |
+-----------------------------------------------------+
| 学号为20210101的学生在《操作系统》课程的成绩是：88  |
| 学号为20210101的学生在《数据结构》课程的成绩是：78  |
| 学号为20210102的学生在《操作系统》课程的成绩是：98  |
| 学号为20210102的学生在《数据结构》课程的成绩是：100 |
| 学号为20210103的学生在《操作系统》课程的成绩是：61  |
| 学号为20210103的学生在《数据结构》课程的成绩是：59  |
| 学号为20210104的学生在《操作系统》课程的成绩是：46  |
| 学号为20210104的学生在《数据结构》课程的成绩是：55  |
+-----------------------------------------------------+
8 rows in set (0.05 sec)

⭐️ 例子：

-- SUBSTRING_INDEX 函数是用于截取字符串，此处对其进一步说明
-- str: 要处理的字符串；delim：分隔符；count：计数 
-- 如果 count 是正数，那么从左往右数，返回第 N 个分隔符的左边的全部内容
-- 如果 count 是负数，那么从右往左数，返回第 N 个分隔符的右边的全部内容
SUBSTRING_INDEX(str, delim, count)

-- www
SELECT SUBSTRING_INDEX('www.wiki.com', '.', 1);

-- www.wiki
SELECT SUBSTRING_INDEX('www.wiki.com', '.', 2);

-- com
SELECT SUBSTRING_INDEX('www.wiki.com', '.', -1);

-- wiki.com
SELECT SUBSTRING_INDEX('www.wiki.com', '.', -2);

日期和时间处理函数

名称	调用示例	示例结果	描述
`NOW`	`NOW()`	`2019-08-16 17:10:43`	返回当前日期和时间
`CURDATE`	`CURDATE()`	`2019-08-16`	返回当前日期
`CURTIME`	`CURTIME()`	`17:10:43`	返回当前时间
`DATE`	`DATE('2019-08-16 17:10:43')`	`2019-08-16`	将给定日期和时间值的日期提取出来
`DATE_ADD`	`DATE_ADD('2019-08-16 17:10:43', INTERVAL 2 DAY)`	`2019-08-18 17:10:43`	将给定的日期和时间值添加指定的时间间隔（后文进一步说明）
`DATE_SUB`	`DATE_SUB('2019-08-16 17:10:43', INTERVAL 2 DAY)`	`2019-08-14 17:10:43`	将给定的日期和时间值减去指定的时间间隔（后文进一步说明）
`DATEDIFF`	`DATEDIFF('2019-08-16', '2019-08-17')`	`-1`	返回两个日期之间的天数（负数代表前一个参数代表的日期比较小）
`TIMESTAMPDIFF`	`TIMESTAMPDIFF(DAY, '2019-08-16', '2019-08-17');`	`1`	返回两个时间戳之间的时间差（后文进一步说明）
`DATE_FORMAT`	`DATE_FORMAT(NOW(),'%m-%d-%Y')`	`08-16-2019`	用给定的格式显示日期和时间（后文进一步说明）
`YEAR`	`YEAR('2019-08-16 17:10:43')`	`2019`	提取年份
`MONTH`	`MONTH('2019-08-16 17:10:43')`	`8`	提取月份
`DAY`	`DAY('2019-08-16 17:10:43')`	`16`	提取日
`HOUR`	`HOUR('2019-08-16 17:10:43')`	`17`	提取小时
`MINUTE`	`MINUTE('2019-08-16 17:10:43')`	`10`	提取分钟
`SECOND`	`SECOND('2019-08-16 17:10:43')`	`43`	提取秒

☕️ 例子：

在使用DATE_ADD和DATE_SUB这两个函数时，增加或减去的时间间隔单位如下：

时间单位	描述
`MICROSECOND`	毫秒
`SECOND`	秒
`MINUTE`	分钟
`HOUR`	小时
`DAY`	天
`WEEK`	星期
`MONTH`	月
`QUARTER`	季度
`YEAR`	年

-- 让 2020-05-09 20:18:58 这个时间点增加 2 分钟：
mysql> SELECT DATE_ADD('2020-05-09 20:18:58', INTERVAL 2 MINUTE);
+----------------------------------------------------+
| DATE_ADD('2020-05-09 20:18:58', INTERVAL 2 MINUTE) |
+----------------------------------------------------+
| 2020-05-09 20:20:58                                |
+----------------------------------------------------+
1 row in set (0.04 sec)

⭐️ 例子：

-- TIMESTAMPDIFF 函数用来返回两个时间戳 begin 和 end 之间的时间差 end - end
-- unit 为返回的时间差单位，支持的单位有:
--    MICROSECOND（毫秒）/SECOND（秒）/MINUTE（分钟）/HOUR（小时）
--    DAY（天）/WEEK（周）/MONTH（月）/QUARTER（季）/YEAR（年）
TIMESTAMPDIFF(unit，begin，end)

-- 1，相差一年
SELECT TIMESTAMPDIFF(YEAR, '2018-01-01 15:15:16', '2019-08-23 15:15:16');
-- -1，也是相差一年
SELECT TIMESTAMPDIFF(YEAR, '2019-08-22 15:15:19', '2018-08-22 15:15:16');
-- 0，相差不到一年
SELECT TIMESTAMPDIFF(YEAR, '2019-08-22 15:15:19', '2018-12-23 15:15:16');

-- 0，相差不到一个月
SELECT TIMESTAMPDIFF(MONTH, '2018-08-22 15:15:16', '2018-07-23 15:15:16');
-- -1，相差一个月
SELECT TIMESTAMPDIFF(MONTH, '2018-08-22 15:15:16', '2018-07-01 15:15:16');
-- 1，相差一个月
SELECT TIMESTAMPDIFF(MONTH, '2018-08-22 15:15:19', '2018-09-23 15:15:16');

✏️ 例子：

在使用DATE_FORMAT函数时，日期和时间的显示格式的格式符如下：

格式符	描述
`%b`	简写的月份名称（Jan、Feb、...、Dec)
`%D`	带有英文后缀的月份中的日期（0th、1st、2nd、...、31st)）
`%d`	数字格式的月份中的日期(00、01、02、...、31)
`%f`	微秒（000000-999999）
`%H`	二十四小时制的小时 (00-23)
`%h`	十二小时制的小时 (01-12)
`%i`	数值格式的分钟(00-59)
`%M`	月份名（January、February、...、December）
`%m`	数值形式的月份(00-12)
`%p`	上午或下午（AM 代表上午、PM 代表下午）
`%S`	秒(00-59)
`%s`	秒(00-59)
`%W`	星期名（Sunday、Monday、...、Saturday）
`%w`	周内第几天（0=星期日、1=星期一、 6=星期六）
`%Y`	4 位数字形式的年（例如 2019）
`%y`	2 位数字形式的年（例如 19）

-- 将当前时间格式化输出
mysql> SELECT DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p');
+----------------------------------------+
| DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p') |
+----------------------------------------+
| May 09 2020 08:19 PM                   |
+----------------------------------------+
1 row in set (0.04 sec)

数值处理函数

名称	调用示例	示例结果	描述
`ABS`	`ABS(-1)`	`1`	取绝对值
`Pi`	`PI()`	`3.141593`	返回圆周率
`COS`	`COS(PI())`	`-1`	返回一个角度的余弦
`SIN`	`SIN(PI()/2)`	`1`	返回一个角度的正弦
`TAN`	`TAN(0)`	`0`	返回一个角度的正切
`POW`	`POW(2,2)`	`4`	返回某个数的指定次幂
`SQRT`	`SQRT(9)`	`3`	返回一个数的平方根
`MOD`	`MOD(5,2)`	`1`	返回除法的余数
`RAND`	`RAND()`	`0.7537...`	返回一个随机数
`CEIL`	`CEIL(2.3)`	`3`	返回不小于给定值的最小整数
`FLOOR`	`FLOOR(2.3)`	`2`	返回不小于给定值的最大整数
`ROUND`	`ROUND(1123.26723,2)`	`1123.27`	把数值字段舍入为指定的小数位数

对于ROUND函数，需要进一步说明。该函数用于数据的四舍五入，它有两种形式：

ROUND(x,d)：x为要处理的数，d为保留的小数位数；d 可以为负数或者0，这时是指定小数点左边的d位整数位为0，同时小数位均为0。
ROUND(x)：其实就是ROUND(x,0)，也就是默认d为0。

-- 1123.27
SELECT ROUND(1123.26723, 2);

-- 1123.2
SELECT ROUND(1123.26723, 1);

-- 1123
SELECT ROUND(1123.26723, 0);

-- 1120
SELECT ROUND(1123.26723, -1);

-- 1100
SELECT ROUND(1123.26723, -2);

-- 1123
SELECT ROUND(1123.26723);

流程控制表达式和函数

☕️ CASE表达式

-- 当表达式1为 TRUE 时，整个 CASE 表达式的值是结果1；当表达式2为 TRUE 时，整个 CASE 表达式的值是结果2
-- 依此类推，当表达式n 为 TRUE 时，整个 CASE 表达式的值就是结果n
-- 如果所有的表达式都不为 TRUE，那整个 CASE 表达式的值就是 ELSE 之后的默认结果
CASE WHEN 表达式1 THEN 结果1 [WHEN 表达式2 THEN 结果2...] [ELSE 默认结果] END

-- 当待比较表达式的值和表达式1的值相同时，整个 CASE 表达式的值就是结果1
-- 当待比较表达式的值和表达式2的值相同时，整个 CASE 表达式的值就是结果2
-- 依此类推，当待比较表达式的值和表达式1、n 的值相同时，整个 CASE 表达式的值就是结果n
-- 如果所有都不相等，那整个 CASE 表达式的值就是 ELSE 之后的默认结果
CASE 待比较表达式 WHEN 表达式1 THEN 结果1 [WHEN 表达式2 THEN 结果2...] [ELSE 默认结果] END

mysql> SELECT number, subject, score,
    ->    CASE WHEN score < 60 THEN '不及格'
    ->         WHEN score < 90 THEN '及格'
    ->         ELSE '优秀' END AS level
    -> FROM student_score;
+----------+----------+-------+--------+
| number   | subject  | score | level  |
+----------+----------+-------+--------+
| 20210101 | 操作系统 |    88 | 及格   |
| 20210101 | 数据结构 |    78 | 及格   |
| 20210102 | 操作系统 |    98 | 优秀   |
| 20210102 | 数据结构 |   100 | 优秀   |
| 20210103 | 操作系统 |    61 | 及格   |
| 20210103 | 数据结构 |    59 | 不及格 |
| 20210104 | 操作系统 |    46 | 不及格 |
| 20210104 | 数据结构 |    55 | 不及格 |
+----------+----------+-------+--------+
8 rows in set (0.09 sec)

mysql> SELECT name, department,
    ->    CASE department WHEN '计算机学院' THEN '1级学科'
    ->                    WHEN '航天学院' THEN '2级学科' END AS 学院类别
    -> FROM student_info;
+-------+------------+----------+
| name  | department | 学院类别 |
+-------+------------+----------+
| Alice | 计算机学院 | 1级学科  |
| Bob   | 计算机学院 | 1级学科  |
| Lee   | 计算机学院 | 1级学科  |
| Bella | 计算机学院 | 1级学科  |
| Ray   | 航天学院   | 2级学科  |
| Tina  | 航天学院   | 2级学科  |
+-------+------------+----------+
6 rows in set (0.09 sec)

⭐️ IF函数

-- 如果表达式1为 TRUE，函数返回表达式2，否则返回表达式3
IF(表达式1,表达式2,表达式3)

mysql> SELECT IF(1 > 2, 3, 4);
+-----------------+
| IF(1 > 2, 3, 4) |
+-----------------+
|               4 |
+-----------------+
1 row in set (0.07 sec)

✏️ IFNULL函数

-- 如果表达式1 为 NULL，函数返回表达式2，否则返回表达式1
IFNULL(表达式1,表达式2)

mysql> SELECT IFNULL(NULL,5);
+----------------+
| IFNULL(NULL,5) |
+----------------+
|              5 |
+----------------+
1 row in set (0.05 sec)

mysql> SELECT IFNULL(3,5);
+-------------+
| IFNULL(3,5) |
+-------------+
|           3 |
+-------------+
1 row in set (0.05 sec)

📚 NULLIF函数

-- 当表达式1的值和表达式2的值相同时，函数返回 NULL，否则返回表达式1
NULLIF(表达式1，表达式2)

mysql> SELECT NULLIF(2,3);
+--------------+
| NULLIF(2, 3) |
+--------------+
|            2 |
+--------------+
1 row in set (0.05 sec)

mysql> SELECT NULLIF(2,2);
+--------------+
| NULLIF(2, 2) |
+--------------+
| NULL         |
+--------------+
1 row in set (0.04 sec)

聚集函数

☕️ MAX/MIN函数

-- 返回表达式对应列的最大值
MAX(表达式)

-- 返回表达式对应列的最小值
MIN(表达式)

-- 获取操作系统成绩的最高分
mysql> SELECT MAX(score) FROM student_score WHERE subject = '操作系统';
+------------+
| MAX(score) |
+------------+
|         98 |
+------------+
1 row in set (0.06 sec)

-- 获取操作系统成绩的最低分
mysql> SELECT MIN(score) FROM student_score WHERE subject = '操作系统';
+------------+
| MIN(score) |
+------------+
|         46 |
+------------+
1 row in set (0.06 sec)

⭐️ SUM函数

-- 计算表达式对应列的总和
SUM(表达式)

-- 计算操作系统成绩的总分
mysql> SELECT SUM(score) FROM student_score WHERE subject = '操作系统';
+------------+
| SUM(score) |
+------------+
| 293        |
+------------+
1 row in set (0.05 sec)

✏️ AVG函数

-- 计算表达式对应列的平均数
AVG(表达式)

-- 计算操作系统成绩的平均分
mysql> SELECT AVG(score) FROM student_score WHERE subject = '操作系统';
+------------+
| AVG(score) |
+------------+
| 73.2500    |
+------------+
1 row in set (0.07 sec)

📚 COUNT函数

-- 统计表达式对应列中非 NULL 值的数量
COUNT(表达式)

CREATE TABLE count_demo (
    c int
);

INSERT INTO count_demo VALUES(1), (NULL), (2), (NULL);

-- 统计列 c 中非 NULL 值的数量
mysql> SELECT COUNT(c) FROM count_demo;
+----------+
| COUNT(c) |
+----------+
|        2 |
+----------+
1 row in set (0.04 sec)

-- 统计表中的总记录数，不关心某个列中是否存储 NULL
mysql> SELECT COUNT(*) FROM count_demo;
+----------+
| COUNT(*) |
+----------+
|        4 |
+----------+
1 row in set (0.05 sec)

-- 使用 COUNT(1) 也可以统计表中的总记录数，等价于 COUNT(*)
-- COUNT(1) 相当于统计 SELECT 1 FROM count_demo 的结果集中的记录条数
mysql> SELECT COUNT(1) FROM count_demo;
+----------+
| COUNT(1) |
+----------+
|        4 |
+----------+
1 row in set (0.01 sec)

-- 使用 DISTINCT 过滤掉重复数据，然后进行统计
mysql> SELECT COUNT(DISTINCT major) FROM student_info;
+-----------------------+
| COUNT(DISTINCT major) |
+-----------------------+
|                     4 |
+-----------------------+
1 row in set (0.02 sec)

-- 多个聚聚函数放在同一个查询列表中
mysql> SELECT COUNT(*) AS 成绩记录总数, MAX(score) AS 最高成绩, MIN(score) AS 最低成绩, AVG(score) AS 平均成绩 FROM student_score;
+--------------+----------+----------+----------+
| 成绩记录总数 | 最高成绩 | 最低成绩 | 平均成绩 |
+--------------+----------+----------+----------+
|            8 |      100 |       46 | 73.1250  |
+--------------+----------+----------+----------+
1 row in set (0.04 sec)

隐式类型转换

-- 字符串类型的表达式与其它类型的表达式进行算术运算、比较大小以及逻辑判断时，都会尽可能转换为 DOUBLE 类型
-- 字符串会被强制转换为数值类型时，会截断字符串开头的数字，如果字符串的开头没有包含数字，会被转换为 0
mysql> SELECT 1 + 2, '1' + 2, '1' + '2', 1 + '23sfd', 1 + 'sfd';
+-------+---------+-----------+-------------+-----------+
| 1 + 2 | '1' + 2 | '1' + '2' | 1 + '23sfd' | 1 + 'sfd' |
+-------+---------+-----------+-------------+-----------+
|     3 |       3 |         3 |          24 |         1 |
+-------+---------+-----------+-------------+-----------+
1 row in set (0.03 sec)

mysql> SELECT 1 > 'a', 1 AND 'a', 1 AND '2a';
+---------+-----------+------------+
| 1 > 'a' | 1 AND 'a' | 1 AND '2a' |
+---------+-----------+------------+
|       1 |         0 |          1 |
+---------+-----------+------------+
1 row in set (0.02 sec)

-- 函数传入的参数会转换为该函数期望的类型。例如，CONCAT 函数传入的参数会强制转换为字符串类型
mysql> SELECT CONCAT('1', '2'), CONCAT('1', 2), CONCAT(1, 2);
+------------------+----------------+--------------+
| CONCAT('1', '2') | CONCAT('1', 2) | CONCAT(1, 2) |
+------------------+----------------+--------------+
| 12               | 12             | 12           |
+------------------+----------------+--------------+
1 row in set (0.02 sec)

-- 存储数据时，会把某个值转换为某个列需要的类型
CREATE TABLE type_conversion_demo (
    i1 TINYINT,
    i2 TINYINT,
    s VARCHAR(100)
);
INSERT INTO type_conversion_demo(i1, i2, s) VALUES('100', '100', 200);
-- 字符串类型转换为 DOUBLE 时不能发生发生截断，否则会报错
INSERT INTO type_conversion_demo(i1, i2, s) VALUES('sfd', 'sdf', 'aaa');

mysql> SELECT * FROM type_conversion_demo;
+-----+-----+-----+
| i1  | i2  | s   |
+-----+-----+-----+
| 100 | 100 | 200 |
+-----+-----+-----+
1 row in set (0.06 sec)

分组查询

创建分组

-- 使用 GROUP BY 子句完成分组的过程，然后通过聚集函数统计各分组信息。例如，统计各科目的平均成绩：
mysql> SELECT subject, AVG(score) FROM student_score GROUP BY subject;
+----------+------------+
| subject  | AVG(score) |
+----------+------------+
| 操作系统 | 73.2500    |
| 数据结构 | 73.0000    |
+----------+------------+
2 rows in set (0.02 sec)

-- 分组的存在是为了方便统计各分组的信息，如果查询列表中有一条既不是分组也不是聚集函数的列，那么该值是随机的
-- number 既不是分组也不是聚集函数的列，它的值是分组中随机取出的一条记录的数据
mysql> SELECT number, subject, AVG(score) FROM student_score GROUP BY subject;
+----------+----------+------------+
| number   | subject  | AVG(score) |
+----------+----------+------------+
| 20210101 | 操作系统 | 73.2500    |
| 20210101 | 数据结构 | 73.0000    |
+----------+----------+------------+
2 rows in set (0.02 sec)

注意：如果分组列中含有NULL，那么NULL也会作为一个独立的分组存在。

带有 WHERE 子句的分组

-- 如果想在划分分组之前就将某些记录过滤掉，这时可以使用 WHERE 子句
-- 将低于 60 分的记录去掉之后，再统计平均成绩
mysql> SELECT subject, AVG(score) FROM student_score WHERE score >= 60 GROUP BY subject;
+----------+------------+
| subject  | AVG(score) |
+----------+------------+
| 操作系统 | 82.3333    |
| 数据结构 | 89.0000    |
+----------+------------+
2 rows in set (0.03 sec)

作用于分组的过滤条件

-- 如果想对分组进行过滤，可以使用 HAVING 子句
-- 使用与分组列有关的条件进行分组过滤，也可以直接将该条件放在 WHERE 子句中，效果是一样的
-- 统计数据结构科目的平均成绩
mysql> SELECT subject, AVG(score) FROM student_score GROUP BY subject HAVING subject = '数据结构';
+----------+------------+
| subject  | AVG(score) |
+----------+------------+
| 数据结构 | 73.0000    |
+----------+------------+
1 row in set (0.04 sec)

-- 使用作用于分组的聚集函数有关的条件进行分组过滤，该条件不能出现在 WHERE 子句中，否则会报错
-- 统计最高分大于 98 分的科目的平均成绩
mysql> SELECT subject, AVG(score) FROM student_score GROUP BY subject HAVING MAX(score) > 98;
+----------+------------+
| subject  | AVG(score) |
+----------+------------+
| 数据结构 | 73.0000    |
+----------+------------+
1 row in set (0.05 sec)

分组和排序

-- 分组查询的结果也可以进行排序，按照从大到小的顺序对各个学科的平均分进行排序
mysql> SELECT subject, AVG(score) FROM student_score GROUP BY subject ORDER BY AVG(score) DESC;
+----------+------------+
| subject  | AVG(score) |
+----------+------------+
| 操作系统 | 73.2500    |
| 数据结构 | 73.0000    |
+----------+------------+
2 rows in set (0.01 sec)

嵌套分组

-- 可以将一个分组继续划分成更小的分组，只需要在 GROUP BY 子句中把各个分组列依次写上，以逗号分隔
-- 先按照 department 进行分组，对于同一分组，再按照 major 划分为更小的分组
mysql> SELECT department, major, COUNT(*) FROM student_info GROUP BY department, major;
+------------+------------------+----------+
| department | major            | COUNT(*) |
+------------+------------------+----------+
| 计算机学院 | 计算机科学与工程 |        2 |
| 计算机学院 | 软件工程         |        2 |
| 航天学院   | 飞行器设计       |        1 |
| 航天学院   | 电子信息         |        1 |
+------------+------------------+----------+
4 rows in set (0.03 sec)

查询语句中各子句顺序

SELECT [DISTINCT] 查询列表
[FROM 表名]
[WHERE 布尔表达式]
[GROUP BY 分组列表]
[HAVING 分组过滤条件]
[ORDER BY 排序列表]
[LIMIT 偏移量, 限制条数]

WHERE子句是在分组前进行过滤，作用于每一条记录，不符合WHERE子句的记录不会参与分组；而HAVING子句在将记录进行分组后进行过滤，作用于整个分组。

子查询

标量子查询

-- 如果子查询结果只有一个值，这种子查询叫做标量子查询。例如：查询 Alice 的所有学科成绩
mysql> SELECT * FROM student_score WHERE number = (SELECT number FROM student_info WHERE name = 'Alice');
+----------+----------+-------+
| number   | subject  | score |
+----------+----------+-------+
| 20210101 | 操作系统 |    88 |
| 20210101 | 数据结构 |    78 |
+----------+----------+-------+
2 rows in set (0.03 sec)

列子查询

-- 如果子查询结果不是一个单独的值，而是一个列，这种子查询叫做列子查询。例如：计算机学院学生的所有学科成绩
mysql> SELECT * FROM student_score WHERE number IN (SELECT number FROM student_info WHERE major = '计算机科学与工程');
+----------+----------+-------+
| number   | subject  | score |
+----------+----------+-------+
| 20210101 | 操作系统 |    88 |
| 20210101 | 数据结构 |    78 |
| 20210102 | 操作系统 |    98 |
| 20210102 | 数据结构 |   100 |
+----------+----------+-------+
4 rows in set (0.05 sec)

行子查询

-- 如果子查询结果是一条记录，这种子查询叫做行子查询
mysql> SELECT * FROM student_score WHERE (number, subject) = (SELECT number, '操作系统' FROM student_info LIMIT 1);
+----------+----------+-------+
| number   | subject  | score |
+----------+----------+-------+
| 20210104 | 操作系统 |    46 |
+----------+----------+-------+
1 row in set (0.02 sec)

表子查询

-- 如果子查询结果是多条记录，这种子查询叫做表子查询
mysql> SELECT * FROM student_score WHERE (number, subject) IN (SELECT number, '操作系统' FROM student_info WHERE major = '计算机科学与工程');
+----------+----------+-------+
| number   | subject  | score |
+----------+----------+-------+
| 20210101 | 操作系统 |    88 |
| 20210102 | 操作系统 |    98 |
+----------+----------+-------+
2 rows in set (0.05 sec)

EXISTS 和 NOT EXISTS 子查询

有时候外层查询并不关心子查询中的结果是什么，而只关心子查询的结果集是不是为空集：

操作符	示例	描述
`EXISTS`	`EXISTS (SELECT ...)`	当子查询结果集不是空集时表达式为真
`NOT EXISTS`	`NOT EXISTS (SELECT ...)`	当子查询结果集是空集时表达式为真

☕️ 例子：

-- student_info 表中没有学号为 20210108 的学生，所以子查询的结果集是一个空集
-- 外层查询的 EXISTS 表达式的结果为 FALSE，最终返回的结果集为 Empty set，表示没有结果
mysql> SELECT * FROM student_score WHERE EXISTS (SELECT * FROM student_info WHERE number = 20210108);
Empty set

不相关和相关子查询

-- 如果子查询可以独立运行并产生结果，之后再拿结果作为外层查询的条件去执行，这种子查询叫做不相关子查询
mysql> SELECT * FROM student_score WHERE number = (SELECT number FROM student_info WHERE name = 'Alice');
+----------+----------+-------+
| number   | subject  | score |
+----------+----------+-------+
| 20210101 | 操作系统 |    88 |
| 20210101 | 数据结构 |    78 |
+----------+----------+-------+
2 rows in set (0.08 sec)

-- 如果子查询的语句中引用到外层查询的值，也就是子查询的语句不能独立执行，这种子查询被称为相关子查询
mysql> SELECT number, name, id_number, major FROM student_info WHERE EXISTS (SELECT * FROM student_score WHERE student_info.number = student_score.number);
+----------+-------+--------------------+------------------+
| number   | name  | id_number          | major            |
+----------+-------+--------------------+------------------+
| 20210101 | Alice | 158177199901044792 | 计算机科学与工程 |
| 20210102 | Bob   | 151008199801178529 | 计算机科学与工程 |
| 20210103 | Lee   | 17156319980116959X | 软件工程         |
| 20210104 | Bella | 141992199701078600 | 软件工程         |
+----------+-------+--------------------+------------------+
4 rows in set (0.02 sec)

上面的相关子查询用到外层查询的student_info表中的记录。执行过程如下：

先从外层查询中取得student_info表的第一条记录，发现它的 number 列值是 20210101，并将该值传入到子查询中，子查询相当于SELECT * FROM student_score WHERE student_score.number=20210101。这个子查询的结果集不为空集，所以整个EXISTS表达式的值为TRUE，第一条记录加入外层查询的结果集。
再从外层查询中取得student_info表的第二条记录、第三条记录...执行上述过程，直至student_info表中没有更多的记录。

对同一个表的子查询

-- 获取数据结构科目高于平均成绩的成绩记录
mysql> SELECT * FROM student_score WHERE subject = '数据结构' AND score > (SELECT AVG(score) FROM student_score WHERE subject = '数据结构');
+----------+----------+-------+
| number   | subject  | score |
+----------+----------+-------+
| 20210101 | 数据结构 |    78 |
| 20210102 | 数据结构 |   100 |
+----------+----------+-------+
2 rows in set (0.03 sec)

连接查询

连接的概念

-- 前期准备
CREATE TABLE t1 (
    m1 INT,
    n1 CHAR (1)
);
CREATE TABLE t2 (
    m2 INT,
    n2 CHAR(1)
);
INSERT INTO t1 VALUES(1, 'a'), (2, 'b'), (3, 'c');
INSERT INTO t2 VALUES(2, 'b'), (3, 'c'), (4, 'd');

-- 连接查询的本质是把各个表中的记录取出来进行组合，组成新的更大的记录，然后将满足条件的记录加入结果集
-- 将 t1 和 t2 表进行连接查询，返回全部记录，下面几种写法等价
-- 写法1：* 代表读取 FROM 子句中每个表的所有列
SELECT * FROM t1, t2;

-- 写法2：将 t1、t2 表中的列名都显式的写出来，也就是使用了列的全限定名
SELECT t1.m1, t1.n1, t2.m2, t2.n2 FROM t1, t2;

-- 写法3：由于 t1、t2 表中的列名并不重复，没有二义性，可以不用加限定表名
SELECT m1, n1, m2, n2 FROM t1, t2;

-- 写法4：这种写法意思就是查询 t1 表的全部的列，t2 表的全部的列
SELECT t1.*, t2.* FROM t1, t2;

查询过程如下图所示：

将t1表的每一条记录与t2表中的每一条记录相互匹配进行组合，上面的组合方式返回的结果集称为笛卡尔积。

连接查询过滤条件

-- 在实际使用中，不可能把多表连接的结果集全部返回，在连接的时候需要过滤掉特定记录组合
-- 连接查询中的过滤条件一般分为两种：涉及单表的条件和涉及多表的条件
-- 下述语句中，t1.m1 > 1 和 t2.n2 < 'd' 是单表条件，t1.m1 = t2.m2 是多表条件
SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

上述连接查询的大致执行过程如下：

首先确定第一个需要查询的表，这个表称为驱动表。这里假设t1作为驱动表，那么就需要在t1表中查找满足t1.m1 > 1的记录，具体如下所示：
```
mysql> SELECT * FROM t1 WHERE t1.m1 > 1;
+----+----+
| m1 | n1 |
+----+----+
|  2 | b  |
|  3 | c  |
+----+----+
2 rows in set (0.04 sec)
```
针对满足t1.m1 > 1 条件的驱动表中的每条记录，都需要到 t2 表中查找能进行匹配的记录，t2表也被称为被驱动表。以从驱动表中查询出的第一条记录t1.m1 = 2, t1.n1 = 'b'为例，此时t2表的过滤条件相当于t2.m2 = 2 AND t2.n2 < 'd'，使用该条件执行单表查询，并将查询结果与驱动表中第一条记录进行组合：
```
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
+------+------+------+------+
```

所以执行完整个连接查询，最后得到的结果集如下：

+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+

也就是说在两表连接查询中，驱动表只需要查询一次，而被驱动表可能会被查询多次。

内连接和外连接

内连接：对于内连接的两个表，如果驱动表中的记录在被驱动表中找不到匹配的记录，则该记录不会加入到最后的结果集中。
外连接：对于外连接的两个表，即使驱动表中的记录在被驱动表中没有匹配的记录，也仍然需要加入到结果集中。

-- 对于内连接来说，ON 子句和 WHERE 子句是等价的
-- 内连接查询一般是哪张表的数据较少，哪张表为驱动表
SELECT * FROM t1, t2 [WHERE 普通过滤条件];
SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 连接条件] [WHERE 普通过滤条件];

-- 以下 SQL 语句是等效的
SELECT * FROM t1 JOIN t2;
SELECT * FROM t1 INNER JOIN t2;
SELECT * FROM t1 CROSS JOIN t2;
SELECT * FROM t1, t2;

mysql> SELECT student_info.number, name, major, subject, score FROM student_info, student_score WHERE student_info.number = student_score.number;
+----------+-------+------------------+----------+-------+
| number   | name  | major            | subject  | score |
+----------+-------+------------------+----------+-------+
| 20210101 | Alice | 计算机科学与工程 | 操作系统 |    88 |
| 20210101 | Alice | 计算机科学与工程 | 数据结构 |    78 |
| 20210102 | Bob   | 计算机科学与工程 | 操作系统 |    98 |
| 20210102 | Bob   | 计算机科学与工程 | 数据结构 |   100 |
| 20210103 | Lee   | 软件工程         | 操作系统 |    61 |
| 20210103 | Lee   | 软件工程         | 数据结构 |    59 |
| 20210104 | Bella | 软件工程         | 操作系统 |    46 |
| 20210104 | Bella | 软件工程         | 数据结构 |    55 |
+----------+-------+------------------+----------+-------+
8 rows in set (0.05 sec)

-- 对于外连接来说，ON 子句后接两表连接条件，WHERE 子句后接过滤记录条件
-- 一般将涉及单表的过滤条件放到 WHERE 子句中，把涉及两表的过滤条件或者说连接条件都放到 ON 子句中
-- 左（外）连接，对于左（外）连接来说，LEFT JOIN 左侧的表是驱动表
SELECT * FROM t1 LEFT JOIN t2 ON 过滤条件 [WHERE 普通过滤条件]

-- 右（外）连接，对于右（外）连接来说，RIGHT JOIN 右侧的表是驱动表
SELECT * FROM t2 RIGHT JOIN t2 ON 过滤条件 [WHERE 普通过滤条件]

mysql> SELECT student_info.number, name, major, subject, score FROM student_info LEFT JOIN student_score ON student_info.number = student_score.number;
+----------+-------+------------------+----------+-------+
| number   | name  | major            | subject  | score |
+----------+-------+------------------+----------+-------+
| 20210101 | Alice | 计算机科学与工程 | 操作系统 |    88 |
| 20210101 | Alice | 计算机科学与工程 | 数据结构 |    78 |
| 20210102 | Bob   | 计算机科学与工程 | 操作系统 |    98 |
| 20210102 | Bob   | 计算机科学与工程 | 数据结构 |   100 |
| 20210103 | Lee   | 软件工程         | 操作系统 |    61 |
| 20210103 | Lee   | 软件工程         | 数据结构 |    59 |
| 20210104 | Bella | 软件工程         | 操作系统 |    46 |
| 20210104 | Bella | 软件工程         | 数据结构 |    55 |
| 20210105 | Ray   | 飞行器设计       | NULL     | NULL  |
| 20210106 | Tina  | 电子信息         | NULL     | NULL  |
+----------+-------+------------------+----------+-------+
10 rows in set (0.05 sec)

-- 多种连接的对比
-- 内连接
mysql> SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
+------+------+------+------+
2 rows in set (0.00 sec)

-- 左外连接
mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
|    1 | a    | NULL | NULL |
+------+------+------+------+
3 rows in set (0.00 sec)

-- 右外连接
mysql> SELECT * FROM t1 RIGHT JOIN t2 ON t1.m1 = t2.m2;
+------+------+------+------+
| m1   | n1   | m2   | n2   |
+------+------+------+------+
|    2 | b    |    2 | b    |
|    3 | c    |    3 | c    |
| NULL | NULL |    4 | d    |
+------+------+------+------+
3 rows in set (0.00 sec)

多表连接

-- 前期准备
CREATE TABLE t3 (
    m3 INT,
    n3 CHAR (1)
);
INSERT INTO t3 VALUES(3, 'c'), (4, 'd'), (5, 'e');

-- 三表连接，无论是多少张表的连接，本质上都是各个表的记录在满足过滤条件下的自由组合
mysql> SELECT * FROM t1, t2, t3 WHERE t1.m1 = t2.m2 AND t1.m1 = t3.m3;
+----+----+----+----+----+----+
| m1 | n1 | m2 | n2 | m3 | n3 |
+----+----+----+----+----+----+
|  3 | c  |  3 | c  |  3 | c  |
+----+----+----+----+----+----+
1 row in set (0.05 sec)

这个查询的执行过程用伪代码如下：

for each row in t1 {
    for each row in t2 which satisfies t1.m1 = t2.m2 {
        for each row in t3 which satisfies t1.m1 = t3.m3 {
            send to client;
        }
    }
}

自连接

-- FROM 子句中不能出现相同的表名，对于同一个表的自连接，需要为表定义一下别名
mysql> SELECT * FROM t1, t1;
1066 - Not unique table/alias: 't1'

mysql> SELECT * FROM t1 AS table1, t1 AS table2;
+----+----+----+----+
| m1 | n1 | m1 | n1 |
+----+----+----+----+
|  1 | a  |  1 | a  |
|  2 | b  |  1 | a  |
|  3 | c  |  1 | a  |
|  1 | a  |  2 | b  |
|  2 | b  |  2 | b  |
|  3 | c  |  2 | b  |
|  1 | a  |  3 | c  |
|  2 | b  |  3 | c  |
|  3 | c  |  3 | c  |
+----+----+----+----+
9 rows in set (0.03 sec)

mysql> SELECT s2.number, s2.name, s2.major FROM student_info AS s1, student_info AS s2 WHERE s1.major = s2.major AND s1.name = 'Alice';
+----------+-------+------------------+
| number   | name  | major            |
+----------+-------+------------------+
| 20210101 | Alice | 计算机科学与工程 |
| 20210102 | Bob   | 计算机科学与工程 |
+----------+-------+------------------+
2 rows in set (0.06 sec)

并集查询

-- 前期准备
mysql> SELECT m1, n1 FROM t1;
+----+----+
| m1 | n1 |
+----+----+
|  1 | a  |
|  2 | b  |
|  3 | c  |
+----+----+
3 rows in set (0.03 sec)

mysql> SELECT m2, n2 FROM t2;
+----+----+
| m2 | n2 |
+----+----+
|  2 | b  |
|  3 | c  |
|  4 | d  |
+----+----+
3 rows in set (0.03 sec)

-- UNION 会将两个查询语句的结果集合合并到一个大的结果集，并且去除重复的行
-- 并集查询的结果集展示的列名是第一个查询中给定的列名
mysql> SELECT m1, n1 FROM t1 UNION SELECT m2, n2 FROM t2;
+----+----+
| m1 | n1 |
+----+----+
|  1 | a  |
|  2 | b  |
|  3 | c  |
|  4 | d  |
+----+----+
4 rows in set (0.03 sec)

-- UNION ALL 会将两个查询语句的结果集合合并到一个大的结果集，并且保留重复记录
mysql> SELECT m1, n1 FROM t1 UNION ALL SELECT m2, n2 FROM t2;
+----+----+
| m1 | n1 |
+----+----+
|  1 | a  |
|  2 | b  |
|  3 | c  |
|  2 | b  |
|  3 | c  |
|  4 | d  |
+----+----+
6 rows in set (0.03 sec)

-- 可以使用 ORDER BY 进行排序，LIMIT 限制数目；查询语句可以不用加括号，但是有括号显示更为清晰
-- 并集查询的结果集展示的列名是第一个查询中给定的列名，所以 ORDER BY 子句指定的列必须是第一个查询中给定的列名
-- 括号内的查询语句使用 ORDER BY 没有意义，并集查询不保证最终结果集中的顺序是按照各个查询的结果集中的顺序排列
mysql> (SELECT m1, n1 FROM t1) UNION (SELECT m2, n2 FROM t2) ORDER BY m1 DESC LIMIT 2;
+----+----+
| m1 | n1 |
+----+----+
|  4 | d  |
|  3 | c  |
+----+----+
2 rows in set (0.04 sec)

插入数据

INSERT

-- 前期准备
DROP TABLE IF EXISTS first_table;
CREATE TABLE first_table (
     first_column INT,
     second_column VARCHAR(100)
);

-- 插入完整的记录的语法：
INSERT INTO 表名 VALUES(列1的值, 列2的值,..., 列n的值);

-- 插入一条完整的记录
INSERT INTO first_table VALUES (1, 'aaa');
-- 如果不知道某个列填什么值，可以填入 NULL（前提是该列没有声明 NOT NULL 属性）
INSERT INTO first_table VALUES (2, NULL);
-- 如果想随意指定列的插入顺序，需要将各个列显式指定，并将各个列的顺序与各个参数的顺序一一对应
INSERT INTO first_table(second_column, first_column) VALUES ('ccc', 3);

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | aaa           |
|            2 | NULL          |
|            3 | ccc           |
+--------------+---------------+
3 rows in set (0.03 sec)

-- 只插入记录的一部分参数时，需要显式指定列，未显式指定列的值会设置为默认值（如果没有默认值，设置为 NULL）
INSERT INTO first_table(first_column) VALUES (4);

-- 批量插入记录时，直接在 VALUES 后多加几组值，各组之间用逗号分割
INSERT INTO first_table(first_column, second_column) VALUES (5, 'eee'), (6, 'fff');

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | aaa           |
|            2 | NULL          |
|            3 | ccc           |
|            4 | NULL          |
|            5 | eee           |
|            6 | fff           |
+--------------+---------------+
6 rows in set (0.03 sec)

DROP TABLE IF EXISTS second_table;
CREATE TABLE second_table (
     s VARCHAR(200),
     i INT
);

-- 可以将某个查询的结果集批量插入表中，下面语句先执行查询操作，再执行批量插入操作
INSERT INTO second_table(s, i) SELECT second_column, first_column FROM first_table WHERE first_column < 4;

mysql> SELECT * FROM second_table;
+------+---+
| s    | i |
+------+---+
| aaa  | 1 |
| NULL | 2 |
| ccc  | 3 |
+------+---+
3 rows in set (0.03 sec)

INSERT IGNORE

-- 对于那些是主键或者具有 UNIQUE 约束的列或者列组合来说，它们不允许重复值的出现
-- 如果待插入记录中的值与已有的记录重复，会报错，此时可以使用 INSERT IGNORE 语法来忽略该记录的插入操作

-- 将 first_column 列添加 UNIQUE 约束
ALTER TABLE first_table MODIFY COLUMN first_column INT UNIQUE;

-- 记录 (1, '娃哈哈') 的 first_column 列值与已有的记录重复，会被忽略插入，最终只成功插入 (7, 'ggg')
mysql> INSERT IGNORE INTO first_table VALUES (1, '娃哈哈'), (7, 'ggg');
Query OK, 1 row affected (0.02 sec)
Records: 2  Duplicates: 1  Warnings: 1

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | aaa           |
|            2 | NULL          |
|            3 | ccc           |
|            4 | NULL          |
|            5 | eee           |
|            6 | fff           |
|            7 | ggg           |
+--------------+---------------+
7 rows in set (0.03 sec)

INSERT ... ON DUPLICATE KEY UPDATE

-- 对于那些是主键或者具有 UNIQUE 约束的列或者列组合来说，也可以选择更新已有的重复记录的值
-- INSERT IGNORE 会忽略待插入的记录，而 INSERT ... ON DUPLICATE KEY UPDATE 会修改重复记录的值

-- 记录 (1, '娃哈哈') 的 first_column 列值与已有的记录重复，将重复记录上的列值进行修改
INSERT INTO first_table VALUES(1, '哇哈哈') ON DUPLICATE KEY UPDATE second_column = '雪碧';

-- 可以使用 VALUES(列名) 的形式引用待插入记录中对应列的值
INSERT INTO first_table VALUES(2, '红牛'), (3, '橙汁') ON DUPLICATE KEY UPDATE second_column = VALUES(second_column);

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | 雪碧          |
|            2 | 红牛          |
|            3 | 橙汁          |
|            4 | NULL          |
|            5 | eee           |
|            6 | fff           |
|            7 | ggg           |
+--------------+---------------+
7 rows in set (0.03 sec)

REPLACE

-- REPLACE 和 INSERT 功能类似，但 REPLACE 会按照如下方式插入数据：
-- 1. 如果发现表中已经有此行数据（根据主键或者唯一索引判断）则先删除此行数据，然后插入新的数据
-- 2. 否则，直接插入新数据

-- 使用 INSERT 插入记录 (7, 'hhh')，UNIQUE 约束会导致插入失败
mysql> INSERT INTO first_table VALUES (7, 'hhh');
1062 - Duplicate entry '7' for key 'first_column'

-- 使用 REPLACE 插入记录 (7, 'hhh')，会先将已有记录 (7, 'ggg') 删除，然后插入 (7, 'hhh')
mysql> REPLACE INTO first_table VALUES (7, 'hhh');
Query OK, 2 rows affected (0.02 sec)

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | 雪碧          |
|            2 | 红牛          |
|            3 | 橙汁          |
|            4 | NULL          |
|            5 | eee           |
|            6 | fff           |
|            7 | hhh           |
+--------------+---------------+
7 rows in set (0.04 sec)

删除数据

☕️ 语法：

-- 删除表中的记录
DELETE FROM 表名 [WHERE 表达式];

-- 删除表中的所有记录
TRUNCATE TABLE 表名;

⭐ 例子：

-- 将 first_column 列的值大于 4 的记录删除
mysql> DELETE FROM first_table WHERE first_column > 4;
Query OK, 3 rows affected (0.02 sec)

-- 可以使用 LIMIT 子句限制想要删除的记录数量，ORDER BY 子句指定符合条件的记录的删除顺序
mysql> DELETE FROM first_table ORDER BY first_column DESC LIMIT 1;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | 雪碧          |
|            2 | 红牛          |
|            3 | 橙汁          |
+--------------+---------------+
3 rows in set (0.04 sec)

✏️ DELETE FROM 表名和TRUNCATE TABLE 表名的区别：

两者都是删除表中的所有记录，但 TRUNCATE 是DDL(Data Definition Language)语句，执行后不能撤销；而 DELETE 是DML(Data Manipulation Language)语句，执行后可以撤销。
TRUNCATE 删除表中的所有记录后，自增主键会重置，而 DELETE 不会。

更新数据

☕️ 语法：

-- 更新记录的语法：
UPDATE 表名 SET 列1=值1, 列2=值2, ..., 列n=值n [WHERE 表达式];

⭐ 例子：

-- 将 first_column 值为 3 的记录进行更新
mysql> UPDATE first_table SET first_column = 4, second_column = '乳娃娃' WHERE first_column = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 可以使用 LIMIT 子句限制想要更新的记录数量，ORDER BY 子句指定符合条件的记录的更新顺序
mysql> UPDATE first_table SET second_column = '爽歪歪' ORDER BY first_column LIMIT 1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM first_table;
+--------------+---------------+
| first_column | second_column |
+--------------+---------------+
|            1 | 爽歪歪        |
|            2 | 红牛          |
|            4 | 乳娃娃        |
+--------------+---------------+
3 rows in set (0.04 sec)

参考

《MySQL 是怎样使用的——快速入门 MySQL》

datediff()函数与 timestampdiff()函数的区别及使用

posted @ 2022-05-13 13:58 呵呵233 阅读(89) 评论(0) 编辑收藏举报

刷新页面返回顶部

岁月静好

MySQL 入门

MySQL 数据类型

整数类型

浮点数类型

定点数类型

日期和时间类型

字符串类型

二进制类型

数据库的基本操作

展示数据库

创建数据库

切换当前数据库

删除数据库

表的基本操作

展示数据库中的表

创建表

删除表

查看表结构

修改表名

增加列

删除列

修改列信息

列的属性

DEFAULT

NOT NULL

主键

UNIQUE

外键

AUTO_INCREMENT

COMMENT

简单查询

查询单个列

列的别名

查询多个列

查询全部列

查询结果去重

限制结果集记录条数

对查询结果排序

带搜索条件的查询

简单搜索条件

匹配列表中的元素

匹配 NULL

多个搜索条件

通配符

表达式和函数

常用运算符

字符串处理函数

日期和时间处理函数

数值处理函数

流程控制表达式和函数

聚集函数

隐式类型转换

分组查询

创建分组

带有 WHERE 子句的分组

作用于分组的过滤条件

分组和排序

嵌套分组

查询语句中各子句顺序

子查询

标量子查询

列子查询

行子查询

表子查询

EXISTS 和 NOT EXISTS 子查询

不相关和相关子查询

对同一个表的子查询

连接查询

连接的概念

连接查询过滤条件

内连接和外连接

多表连接

自连接

并集查询

插入数据

INSERT

INSERT IGNORE

INSERT ... ON DUPLICATE KEY UPDATE

REPLACE