阅读前提醒⚠️⚠️⚠️

本文适合于有一定的SQL基础再进行阅读，并不适合新手入门，本文更多是基本特性的简短介绍，更加注重让读者更好的巩固SQL知识。

SELECT

1. SELECT 语句的正确语义应是 FROM table_name SELECT columns_list，即：从某个表检索某几列数据。MySQL 解析 SELECT 语句的时候，会首先评估 FROM 子句，再评估 SELECT 子句。
2. 一个结果 SELECT 语句被称为结果集，因为它是一组行从查询结果。
3. 你可能要问， **SELECT *** 与 SELECT column_name 有什么区别呢？就一般而言，两者的性能差不多。就区别而言，有如下不同：

• 写出明确的字段，更容易理解你的 SQL 的查询意图

• 某些大字段不适合直接查询出来，因为直接查询会占用更多的开销?

• **SELECT *** 更适合在命令行或者测试场景下使用

• SELECT 后面可以直接跟表达式，这种情况下可以省略 FROM。

• dual 表是一个虚拟表，可以让没有 FROM 的语句满足 SELECT … FROM 语句的结构。
  

AND

如果两个操作数都不为 0 (FALSE) 并且不为 NULL 时，则 AND 运算的结果为 1。
如果有一个操作数为 0 (FALSE)，则 AND 运算的结果为 0。
如果两个操作数中有一个为 NULL，且另一个不为 0 (FALSE)，则返回 NULL。

OR

:::info

• 如果两个操作数中至少一个操作数为 1 (TURE)，则 OR 运算的结果为 1。
• 如果两个操作数都为 0 (FALSE)，则 OR 运算的结果为 0。
• 如果两个操作数有一个为 NULL，另一个为 0 (FALSE) 或者 NULL，则 OR 运算的结果为 NULL。
• AND 运算符的优先级高于 OR。使用括号更改计算顺序。
  :::
  

IN

IN 运算符用来检查一个字段或值是否包含在一个集合中，如果值包含在集合中返回 1，否则返回 0。

1
2
3
4
5
6

SELECT
*
FROM
actor
WHERE
last_name IN ('ALLEN', 'DAVIS');

NOT IN 和 IN 几乎一样，只是在 IN 前面添加一个 NOT 关键字
**SELECT * FROM language WHERE name NOT IN ('French' , 'German');**

BETWEEN

1
2
3
4
5
6

SELECT
    film_id, title, replacement_cost
FROM
    film
WHERE
    replacement_cost BETWEEN 1 AND 10;

LIKE

在 MySQL 中， LIKE 运算符可以根据指定的模式过滤数据。LIKE 运算符一般用于模糊匹配字符数据。
:::tips
MySQL 字符串模式支持两个通配符： % 和 **_**。

• % 匹配零或多个任意字符。
• _ 匹配单个任意字符。
• 如果需要匹配通配符，则需要使用 ** 转义字符，如 % 和 **_**。
• 使用通配符匹配文本时，不区分字母大小写。

比如：

• a% 匹配以字符 a 开头的任意长度的字符串。
• %a 匹配以字符 a 结尾的任意长度的字符串。
• %a% 匹配包含字符 a 的任意长度的字符串。
• %a%b% 匹配同时包含字符 a 和 b 且 a 在 b 前面的任意长度的字符串。
• a_ 匹配以字符 a 开头长度为 2 字符串。
• _a 匹配以字符 a 结尾长度为 2 字符串。
  :::
  SELECT * FROM actor WHERE first_name LIKE 'P%';
SELECT * FROM actor WHERE first_name LIKE '_AY';

IS NULL

IS NULL 和 IS NOT NULL 可以用在 SELECT 子句或者 WHERE 子句中。

1
2
3
4
5
6

SELECT
    first_name, last_name, password
FROM
    staff
WHERE
    password IS NULL;

EXISTS（有点懵）

在 MySQL 中，EXISTS 操作符用来判断一个子查询是否返回数据行。如果一个子查询返回了至少一个数据行，则 EXISTS 的计算结果为 TRUE，否则计算结果为 FALSE。

1
2
3
4

SELECT column_name
FROM table_name
WHERE EXISTS(subquery);

EXISTS 使用说明：

• EXISTS 一般用在 WHERE 子句中。
• EXISTS 是一个单目操作符，它需要一个子查询 subquery 作为参数。
• 如果子查询 subquery 返回了至少一个数据行，则 EXISTS 的计算结果为 TRUE，否则计算结果为 FALSE。
• EXISTS 运算时，一旦子查询找到一个匹配的行，EXISTS 运算就会返回。这对提高查询新能很有帮助。
• EXISTS 不关心子查询中的列的数量或者名称，它只在乎子查询是否返回数据行。所以在 EXISTS 的子查询中，无论你是使用 SELECT 1 还是 **SELECT ***，亦或是 SELECT column_list，都不影响 EXISTS 运算的结果。
• NOT EXISTS 则是 EXISTS 的否定操作。
• 大多数情况下，使用 EXISTS 的语句的性能比对应的使用 IN 的语句要好。

下面的实例查询 language 表的一些语种，该语种在 film 表中存在相关语种的影片。

1
2
3
4
5
6
7

SELECT *
FROM language
WHERE EXISTS(
    SELECT *
    FROM film
    WHERE film.language_id = language.language_id
  );

1
2
3
4
5
6

+-------------+---------+---------------------+
| language_id | name    | last_update         |
+-------------+---------+---------------------+
|           1 | English | 2006-02-15 05:02:19 |
+-------------+---------+---------------------+
1 row in set (0.01 sec)

在上面的例子中，我们在 WHERE 子句中使用了 EXISTS 操作符。在 EXISTS 的子查询中，我们从 film 表中检索记录，检索的条件是 film.language_id = language.language_id。
在检索 language 表的每一行时，只要 film 表存在一行数据具有相同的 language_id， EXISTS 就返回 TRUE。然后进入 language 表的下一行，直到所有行都检索完成，然后返回 language 表中所有匹配成功的行。
最终的结果告诉我们，在 language 表中，只有语种 English 拥有影片。
EXISTS 不关心子查询中的列的数量或者名称，它只在乎子查询是否返回数据行。

ORDER BY

我们可以通过 ORDER BY 子句指定排序的字段以及升序排序还是降序排序。
:::success
说明：

• ORDER BY 子句可以指定一个或多个字段。

• [ASC|DESC] 代表排序是升序还是降序，这是可选的。

• ASC 代表升序，DESC 代表降序。

• 未指定 [ASC|DESC] 时，默认值是 ASC。即，默认是按指定的字段升序排序。

• 当指定多个列时，首先按照前面的字段排序，其次按照后面的字段排序。

• ORDER BY column ASC;

此 ORDER BY 子句对结果集按 column 字段的值升序排序。

• ORDER BY column DESC;

此 ORDER BY 子句对结果集按 column 字段的值降序排序。

• ORDER BY column;

此 ORDER BY 子句对结果集按 column 字段的值升序排序。这个语句等效于： **ORDER BY column ASC;**。

• ORDER BY column1, column2;

此 ORDER BY 子句对结果集先按 column1 字段的值升序排序，然后再按 column2 字段的值升序排序。也就是说主排序按 column1 字段升序排序，在主排序的基础上，对 column1 字段相同的行，再按 column2 字段升序排序。

• ORDER BY column1 DESC, column2;

此 ORDER BY 子句对结果集先按 column1 字段的值降序排序，然后再按按 column2 字段的值升序排序。也就是说主排序按 column1 字段降序排序，在主排序的基础上，对 column1 字段相同的行，再按 column2 字段升序排序。
:::

自定义排序

我们需要按照电影分级 ‘G’, ‘PG’, ‘PG-13’, ‘R’, ‘NC-17’ 的顺序对影片进行排序。
对于这样的需求，它可以理解为按照列表中元素的索引位置进行排序。我们分别使用 CASE 子句或 FIELD() 函数实现它。
使用CASE实现

1
2
3
4
5
6
7
8
9
10
11

SELECT
    film_id, title, rating
FROM
    film
ORDER BY CASE rating
    WHEN 'G' THEN 1
    WHEN 'PG' THEN 2
    WHEN 'PG-13' THEN 3
    WHEN 'R' THEN 4
    WHEN 'NC-17' THEN 5
END;

使用FIELD实现

1
2
3
4
5

SELECT
    *
FROM
    film
ORDER BY FIELD(rating, 'G', 'PG', 'PG-13', 'R', 'NC-17');

• 升序排序时， NULL 在非 NULL 值之前；降序排序时，NULL 在非 NULL 值之后。

LIMIT

该 LIMIT 子句可用于限制 SELECT 语句返回的行数。 LIMIT 接受一个或两个非负数正数作为参数。
LIMIT 子句的语法如下：

1

LIMIT [offset,] row_count;

或者

1

LIMIT row_count OFFSET offset;

:::warning
说明：

• 上述两种语法的结果是等效的，只是写法略有不同。
• offset 指定要返回的第一行的偏移量。偏移量是相对于未使用 LIMIT 语句时的原始结果集而言的。offset 可理解为在原始结果集的基础上跳过的行数。
• row_count 执行要返回的最大行数。
• offset 是可选的。当未指定 offset 时，默认的值 offset 为 0。
• LIMIT 一般位于 SELECT 语句的最后。

例如：

• LIMIT 5最多返回 5 个记录行，等效于 LIMIT 0 5
• LIMIT 2 5在原始结果集中，跳过 2 个记录行，并从 第 3 个记录行开始，最多返回 5 个记录行。
  :::
  
  SELECT 语句中， MySQL 会先按照 ORDER BY 指定的规则对排序结果集，再返回 LIMIT 子句指定的行数返回结果集。

要查询第二页需要先跳过第一页的 10 行数据并限制最多行数为 10 行数据，整个 SQL 如下：

1

SELECT film_id, title FROM film LIMIT 10, 10;

DISTINCT

SELECT DISTINCT last_name FROM actor;

• DISTINCT 清除 SELECT 结果集中的重复记录行。
• DISTINCT 可以用一个字段或者多个字段，也可以用 *****。
• DISTINCT 多个 NULL 值只保留一个 NULL 值。

←

多表查询

JOIN

MySQL 支持以下类型的连接：

• 内部联接 (INNER JOIN)
• 左连接 (LEFT JOIN)
• 右连接 (RIGHT JOIN)
• 交叉连接 (CROSS JOIN)
  

交叉连接

显式的交叉连接 student 和 student_score 表：

1
2
3
4
5

SELECT
  student.*,
  student_score.*
FROM
  student CROSS JOIN student_score;

隐式的交叉连接 student 和 student_score 表：

1
2
3
4
5

SELECT
  student.*,
  student_score.*
FROM
  student, student_score;

结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

+------------+------+------------+---------+-------+
| student_id | name | student_id | subject | score |
+------------+------+------------+---------+-------+
|          3 | Lucy |          1 | English |    90 |
|          2 | Jim  |          1 | English |    90 |
|          1 | Tim  |          1 | English |    90 |
|          3 | Lucy |          1 | Math    |    80 |
|          2 | Jim  |          1 | Math    |    80 |
|          1 | Tim  |          1 | Math    |    80 |
|          3 | Lucy |          2 | English |    85 |
|          2 | Jim  |          2 | English |    85 |
|          1 | Tim  |          2 | English |    85 |
|          3 | Lucy |          2 | Math    |    88 |
|          2 | Jim  |          2 | Math    |    88 |
|          1 | Tim  |          2 | Math    |    88 |
|          3 | Lucy |          5 | English |    92 |
|          2 | Jim  |          5 | English |    92 |
|          1 | Tim  |          5 | English |    92 |
+------------+------+------------+---------+-------+
15 rows in set (0.00 sec)

内连接

1
2
3
4
5
6
7

SELECT
  student.*,
  student_score.*
FROM
  student
  INNER JOIN student_score
  ON student.student_id = student_score.student_id;

1
2
3
4
5
6

SELECT
  student.*,
  student_score.*
FROM
  student, student_score
  WHERE student.student_id = student_score.student_id;

where student.student_id = student_score.student_id;等价于 USING(student_id)

1
2
3
4
5
6

SELECT
  student.*,
  student_score.*
FROM
  student
  INNER JOIN student_score USING(student_id);

外连接

左外连接

以下 SQL 语句将 student 表和 student_score 表左连接：

1
2
3
4
5
6
7

SELECT
  student.*,
  student_score.*
FROM
  student
  LEFT JOIN student_score
  ON student.student_id = student_score.student_id;

1
2
3
4
5
6
7
8
9
10

+------------+------+------------+---------+-------+
| student_id | name | student_id | subject | score |
+------------+------+------------+---------+-------+
|          1 | Tim  |          1 | Math    |    80 |
|          1 | Tim  |          1 | English |    90 |
|          2 | Jim  |          2 | Math    |    88 |
|          2 | Jim  |          2 | English |    85 |
|          3 | Lucy |       NULL | NULL    |  NULL |
+------------+------+------------+---------+-------+
5 rows in set (0.00 sec)

:::danger
注意：

1. 结果集中包含了 student 表的所有记录行。
2. student_score 表中不包含 student_id = 3 的记录行，因此结果几种最后一行中来自 student_score 的列的内容为 NULL。
3. student_score 表存在多条 student_id 为 1 和 2 的记录，因此 student 表也产生了多行数据。
   :::
   

右连接

UNION

UNION 操作符用来合并两个 SELECT 语句的结果集。UNION 操作符的语法如下

说明：

• UNION 双目操作符，需要两个 SELECT 语句作为操作数。
• UNION 中的 SELECT 语句中的列数、列顺序必须相同。
• UNION 运算包括 UNION DISTINCT 和 UNION ALL 两种算法，其中 UNION DISTINCT 可以简写为 UNION。
• UNION DISTINCT 或 UNION 将过滤掉结果集中重复记录。
• UNION ALL 将返回结果集中的所有记录。
• 当对两个结果集进行 UNION 运算的时候，要保证每个结果集具有相同的列数。否则就会产生错误。
• UNION 运算取第一个参与运算的结果集的列名作为最终的列名。
• 可以使用 ORDER BY 对 UNION 运算的结果进行排序。

1
2
3

SELECT * FROM a
UNION
SELECT * FROM b;

1
2
3
4
5
6
7
8
9

+------+
| v    |
+------+
|    1 |
|    2 |
| NULL |
|    3 |
+------+
4 rows in set (0.00 sec)

UNION ALL

1
2
3

SELECT * FROM a
UNION ALL
SELECT * FROM b;

1
2
3
4
5
6
7
8
9
10
11
12

+------+
| v    |
+------+
|    1 |
|    2 |
| NULL |
| NULL |
|    2 |
|    2 |
| NULL |
+------+
7 rows in set (0.00 sec)

那么，如果我们想自定义列名，只需要为第一个结果集的列设定一个别名即可。请看下面的实例：

1
2
3

SELECT 2 AS c
UNION
SELECT 1;

1
2
3
4
5
6
7

+---+
| c |
+---+
| 2 |
| 1 |
+---+
2 rows in set (0.00 sec)

满全连接

用一个UNION来连接

1
2
3

SELECT * FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno
UNION
SELECT * FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;

表别名

1
2
3
4
5
6
7

SELECT
		first_name `First Name`,
		last_name `Last Name`,
		CONCAT(first_name, ', ', last_name) `Full Name`
FROM
		actor
LIMIT 5;

1
2
3
4
5
6
7
8
9
10

+------------+--------------+----------------------+
| First Name | Last Name    | Full Name            |
+------------+--------------+----------------------+
| PENELOPE   | GUINESS      | PENELOPE, GUINESS    |
| NICK       | WAHLBERG     | NICK, WAHLBERG       |
| ED         | CHASE        | ED, CHASE            |
| JENNIFER   | DAVIS        | JENNIFER, DAVIS      |
| JOHNNY     | LOLLOBRIGIDA | JOHNNY, LOLLOBRIGIDA |
+------------+--------------+----------------------+
5 rows in set (0.00 sec)

INSERT

INSERT 语句的插入单行数据语法：

1
2

INSERT INTO table_name (column_1, column_2, ...)
VALUES (value_1, value_2, ...);

INSERT 语句的插入多行数据语法：

1
2
3
4

INSERT INTO table_name (column_1, column_2, ...)
VALUES (value_11, value_12, ...),
       (value_21, value_22, ...)
       ...;

说明：

• INSERT INTO 和 VALUES 都是关键字。
• INSERT INTO 后跟表名 table_name。
• 表名 table_name 后跟要插入数据的列名列表。列名放在小括号中，多个列表使用逗号分隔。
• VALUES 关键字之后的小括号中是值列表。值的数量要和字段的数量相同。值的位置和列的位置一一对应。
• 当插入多行数据时，多个值列表之间使用逗号分隔。
• INSERT 语句返回插入的行数。

INSERT 修饰符

在 MySQL 中， INSERT 语句支持 4 个修饰符:

• LOW_PRIORITY: 如果你指定了 LOW_PRIORITY 修饰符，MySQL 服务器将延迟执行 INSERT 操作直到没有客户端对表进行读操作。LOW_PRIORITY 修饰符影响那些只支持表级锁的存储引擎，比如： MyISAM, MEMORY, 和 MERGE。
• HIGH_PRIORITY: 如果你指定了 HIGH_PRIORITY 修饰符，它会覆盖掉服务器启动时的 –low-priority-updates 选项。HIGH_PRIORITY 修饰符影响那些只支持表级锁的存储引擎，比如： MyISAM, MEMORY, 和 MERGE。
• IGNORE: 如果你指定了 IGNORE 修饰符，MySQL 服务器会在执行 INSERT 操作期间忽略那些可忽略的错误。这些错误最终会作为 WARNING 返回。
• DELAYED: 这个修饰符已经在 MySQL 5.6 版本中弃用，将来会被删除。在 MySQL 8.0 中，这个修饰符可用但会被忽略。
  

DELETE

如果要从数据库表中删除记录行，请使用 DELETE 语句。 DELETE 语句单表删除语法如下：

1
2
3
4

DELETE FROM table_name
[WHERE clause]
[ORDER BY ...]
[LIMIT row_count]

:::tips
说明：

• DELETE FROM 后跟的是要从中删除数据的表。
• WHERE 子句用来过滤需要删除的行。满足条件的行会被删除。
• WHERE 子句是可选的。没有 WHERE 子句时，DELETE 语句将删除表中的所有行。
• ORDER BY 子句用来指定删除行的顺序。它是可选的。
• LIMIT 子句用来指定删除的最大行数。它是可选的。
• DELETE 语句返回删除的行数。
  :::
  

限制删除的最大行数

例如考虑以下这些需求：

• 删除排名最靠后的 5 个成绩
• 删除最新注册的 10 名用户

这时，我们可以结合使用 ORDER BY 和 LIMIT 子句。
以下语句用来删除 actor_copy 中 actor_id 最大的 10 行：

1
2
3

DELETE FROM actor_copy
ORDER BY actor_id DESC
LIMIT 10;

1

Query OK, 10 rows affected (0.01 sec)

如果单独使用 LIMIT 子句，删除的顺序是不明确的。大多数情况下， DELETE 语句中的 LIMIT 子句都应该和 ORDER BY 子句一起使用。

如果你只是想清空表，可以使用 TRUNCATE TABLE 语句以获得更好的性能。如下：

1

TRUNCATE actor_copy;

DELETE 多表删除

我们也可以在一个 DELETE 语句中指定多个表，以便在一个或多个表中删除符合 WHERE 子句中的条件的行。

• 以下语句删除 t1 和 t2 表中满足条件的行：

  1 2 3

  DELETE t1, t2 FROM t1 INNER JOIN t2 WHERE t1.id = t2.id;

• 以下语句删除 t1 表中满足条件的行：

  1 2 3

  DELETE t1 FROM t1 INNER JOIN t2 WHERE t1.id = t2.id;

• 以下语句在删除时使用 LEFT JOIN：

  1 2 3 4

  DELETE t1 FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL;

  只要是 SELECT 语句中允许使用的 JOIN 类型，多表删除语句都可以使用。
  多表删除语句中不能使用 LIMIT 子句和 ORDER BY 子句。
  

UPDATE

UPDATE 语句可以更新表中的一行或者多行数据，可以更新表中的一个或者多个字段（列）。 以下是 UPDATE 语句的基本语法：

1
2
3
4
5
6

UPDATE [IGNORE] table_name
SET
    column_name1 = value1,
    column_name2 = value2,
    ...
[WHERE clause];

用法说明：

• UPDATE 关键字后指定要更新数据的表名。
• 使用 SET 子句设置字段的新值。多个字段使用逗号分隔。字段的值可以是普通的字面值，也可以是表达式运算，还可以是子查询。
• 使用 WHERE 子句指定要更新的行。只有符合 WHERE 条件的行才会被更新。
• WHERE 子句是可选的。如果不指定 WHERE 子句，则更新表中的所有行。

UPDATE 语句中的 WHERE 子句非常重要。除非您特意，否则不要省略 WHERE 子句。

使用子查询更新

下面实例展示了如何为没有绑定商店的客户绑定一个随机商店。

1
2
3
4
5
6
7
8

UPDATE customer
SET store_id = (
    SELECT store_id
    FROM store
    ORDER BY RAND()
    LIMIT 1
  )
WHERE store_id IS NULL;

GROUP BY

GROUP BY 子句用于将结果集根据指定的字段或者表达式进行分组。

有时候，我们需要将结果集按照某个维度进行汇总。这在统计数据的时候经常用到，考虑以下的场景：

• 按班级求取平均成绩。
• 按学生汇总某个人的总分。
• 按年或者月份统计销售额。
• 按国家或者地区统计用户数量。

这些正是 GROUP BY 子句发挥作用的地方。

在使用GROUP BY时，SELECT 后的字段必须是分组字段中的字段。
**在SELECT列表中所有未包含在组函数中的列都应该包含在 GROUP BY子句中 **
举个例子：
需求：查询各个部门的平均工资
错误写法❌(这种写法会将整个表的平均工资求出来)：

1
2

SELECT AVG(salary)
FROM employee;

正确写法✅：

1
2
3

SELECT department_id, AVG(salary)
FROM employee
GROUP BY department_id;

GROUP BY 与聚合函数实例

我们使用 GROUP BY 子句和聚合函数 COUNT() 查看 actor 表中的姓氏列表以及每个姓氏的次数。

1
2
3
4

SELECT last_name, COUNT(*)
FROM actor
GROUP BY last_name
ORDER BY COUNT(*) DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13

+--------------+----------+
| last_name    | COUNT(*) |
+--------------+----------+
| KILMER       |        5 |
| NOLTE        |        4 |
| TEMPLE       |        4 |
| AKROYD       |        3 |
| ALLEN        |        3 |
| BERRY        |        3 |
...
| WRAY         |        1 |
+--------------+----------+
121 rows in set (0.00 sec)

本例中，执行的顺序如下：

1. 首先使用 GROUP BY 子句按照 last_name 字段对数据进行分组。
2. 然后使用聚合函数 COUNT(*) 汇总每个姓氏的行数。
3. 最后使用 ORDER BY 子句按照 COUNT(*) 降序排列。

这样，数量最多的姓氏排在最前面。

GROUP BY, LIMIT, 聚合函数实例

以下实例使用 GROUP BY 子句，LIMIT 子句和聚合函数 SUM() 返回总消费金额排名前 10 位的客户。

1
2
3
4
5

SELECT customer_id, SUM(amount) total
FROM payment
GROUP BY customer_id
ORDER BY total DESC
LIMIT 10;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

+-------------+--------+
| customer_id | total  |
+-------------+--------+
|         526 | 221.55 |
|         148 | 216.54 |
|         144 | 195.58 |
|         137 | 194.61 |
|         178 | 194.61 |
|         459 | 186.62 |
|         469 | 177.60 |
|         468 | 175.61 |
|         236 | 175.58 |
|         181 | 174.66 |
+-------------+--------+
10 rows in set (0.02 sec)

本例中，执行的顺序如下：

1. 首先使用 GROUP BY 子句按照 customer_id 字段对数据进行分组。
2. 然后使用聚合函数 SUM(amount) 汇总每个客户的 amount 字段，并使用 total 作为别名。
3. 然后使用 ORDER BY 子句按照 total 降序排列。
4. 最后使用 LIMIT 10 子句返回前 10 个记录行。

HAVING

HAVING+聚合函数注意事项：

1. 如果过滤条件中使用了聚合函数，则必须使用HAVING来替换WHERE。否则，报错。
2. HAVING 必须声明在 GROUP BY 后面。
3. 开发中我们使用HAVING的前提是SQL中使用了GROUP BY。

错误实例：

1
2
3
4

select department_id, MAX(salary)
FROM employees
WHERE MAX(salary) > 100000 #这句出错
GROUP BY department_id;

正确写法：

1
2
3
4

select department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 100000;

练习：查询部门id为10，20， 30， 40这4个部门中最高工资比10000高的部门信息
方式1: 推荐 执行效率更高！！！

1
2
3
4
5

select department_id, MAX(salary)
FROM employees
WHERE department_id IN (10, 20, 30, 40)
GROUP BY department_id
HAVING MAX(salary) > 100000;

方式2:

1
2
3
4

select department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 100000 AND department_id IN (10, 20, 30, 40);

结论：

• 当过滤条件有聚合函数时，则此过滤条件必须声明在HAVING中。
• 当过滤条件中没有聚合函数时，则此过滤条件声明在WHERE中或HAVING中均可。但是，建议声明在WHERE中。

WHERE 与 HAVING 对比

• 适用范围：HAVING适用范围更广
• 过滤条件🈚️聚合函数，这种情况下，WHERE执行效率高于HAVING
• **如果需要通过连接从关联表中获取需要的数据，WHERE 是先筛选后连接，而 HAVING 是先连接 后筛选。 **这一点，就决定了在关联查询中，WHERE 比 HAVING 更高效。因为 WHERE 可以先筛选，用一 个筛选后的较小数据集和关联表进行连接，这样占用的资源比较少，执行效率也比较高。HAVING 则需要 先把结果集准备好，也就是用未被筛选的数据集进行关联，然后对这个大的数据集进行筛选，这样占用 的资源就比较多，执行效率也较低。

SQL底层执行原理

SELECT语句完整结构

1
2
3
4
5
6
7

SELECT ... , ...(存在聚合函数)
FROM ... , ... , ...
WHERE 多表的连接条件 AND 不包含聚合函数的过滤条件
GROUP BY ... , ...
HAVING 包含聚合函数的过滤条件
ORDER BY ... , ... (ASC / DESC)
LIMIT ... , ...

1
2
3
4
5
6
7
8

SELECT ... , ...(存在聚合函数)
FROM ...(LEFT / RIGHT) JOIN ... ON 多表的连接条件
JOIN ... ON
WHERE 不包含聚合函数的过滤条件
GROUP BY ... , ...
HAVING 包含聚合函数的过滤条件
ORDER BY ... , ... (ASC / DESC)
LIMIT ... , ...

SQL语句执行过程

1
2
3
4
5

FROM ...(LEFT / RIGHT) JOIN ... ON 多表的连接条件
JOIN ... ON
WHERE 不包含聚合函数的过滤条件
GROUP BY ... , ...
HAVING 包含聚合函数的过滤条件

1

SELECT ... , ...(存在聚合函数)

1
2

ORDER BY ... , ... (ASC / DESC)
LIMIT ... , ...

1
2
3

FROM ...,... -> ON -> (LEFT / RIGHT JOIN)
-> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT
-> ORDER BY -> LIMIT

SELECT 之前可以理解为表行数上的过滤，而SELECT是对我们列上的过滤

执行步骤详解：

子查询

子查询与 IN

在这个例子中，我们使用来自 Sakila 示例数据库中的 language 和 film 表作为演示。

1
2
3
4
5
6

SELECT *
FROM language
WHERE language_id IN (
    SELECT DISTINCT language_id
    FROM film
  );

1
2
3
4
5
6

+-------------+---------+---------------------+
| language_id | name    | last_update         |
+-------------+---------+---------------------+
|           1 | English | 2006-02-15 05:02:19 |
+-------------+---------+---------------------+
1 row in set (0.01 sec)

派生表

当一个子查询位于 FORM 子句中时，这个子查询被称为派生表。
让我们看一下下面这个语句：

1
2
3
4
5
6
7
8

SELECT *
FROM (
    SELECT last_name,
      COUNT(*) count
    FROM actor
    GROUP BY last_name
  ) t
WHERE t.last_name LIKE 'A%';

1
2
3
4
5
6
7
8

+-----------+-------+
| last_name | count |
+-----------+-------+
| AKROYD    |     3 |
| ALLEN     |     3 |
| ASTAIRE   |     1 |
+-----------+-------+
3 rows in set (0.00 sec)

请注意下面的语句：

1
2
3
4

SELECT last_name,
  COUNT(*) count
FROM actor
GROUP BY last_name

这是一个派生表，并且它有一个别名 t。派生表必须使用别名，因为 MySQL 规定，任何 FORM 子句中的表必须具有一个名字。
请注意，派生表不是临时表。
派生表遵循以下规则：

• 派生表必须具有别名。
• 派生表的列名必须是唯一的。

数据类型

VARCHAR

在 MySQL 中， VARCHAR 是可变长度的字符串类型，这与 CHAR数据类型有所不同。当一个列的类型定义为 VARCHAR 类型后，列中的内容的长度就是内容本身的长度（字符数）。

MySQL VARCHAR 语法

当我们使用 VARCHAR 数据类型时，我们需要指定一个最大的长度。其语法如下：

1

VARCHAR(max_length)

其中 max_length 是一个数值，它指示了此列的最大字符数。如果我们不指定此值，则默认值是 255。也就是说 VARCHAR 等同于 **VARCHAR(255)**。
MySQL 存储 VARCHAR 数值时，会将最前的 1 或者 2 个字节存储为实际字符串内容的长度。如果列的值少于 255 个字节，则长度前缀为 1 个字节，否则为 2 个字节。
VARCHAR 允许的最大长度为 65535 个字节，这也是 MySQL 中的行大小的限制。

VARCHAR 截断内容

当插入的内容超过 VARCHAR 列定义的长度时，MySQL 会采用如下策略：

• 如果超过的部分只包含空格，则多余的空格会被截断。
• 如果超过的部分不只是空格，则给出错误提示。

让我们先创建一个测试表，只有一个字段 v，且定义为 **VARCHAR(2)**：

1
2

CREATE TABLE test_varchar (v VARCHAR(2))
CHARACTER SET 'latin1';

让我们插入一个值 **’A ‘**，它包含 2 个空格，总长度为 3。

1
2

INSERT INTO test_varchar
VALUES ('A  ')

1

1 row(s) affected, 1 warning(s): 1265 Data truncated for column 'v' at row 1

此时插入成功，但是 MySQL 给出了截断提示。
让我们再一个值 **’ABC’**，它的长度为 3。

1
2

INSERT INTO test_varchar
VALUES ('ABC')

1

Error Code: 1406. Data too long for column 'v' at row 1

这里，插入数据失败。

CHAR

在 MySQL 中， CHAR 是固定长度的字符串类型，这与 VARCHAR数据类型有所不同。当一个列的类型定义为 CHAR 类型后，列中的内容的长度就是字段定义的长度（字符数）。

MySQL CHAR 语法

当我们使用 CHAR 数据类型时，我们需要指定一个长度。其语法如下：

1

CHAR(length)

:::success
这里：

• length 是一个数值，它指示了此列的字符数。
• length 可以是 0 到 255 之间的任何值。
• 如果我们不指定此值，则默认值是 1。也就是说 CHAR 等同于 **CHAR(1)**。

如果您写入 CHAR 列中的字符串的长度小于指定的字符长度，MySQL 会在源字符串后填充空格一直到指定的长度。当您读取 CHAR 列中的值时，MySQL 会删除后面的空格。
由于 CHAR 数据类型采用固定的长度进行存储，因此 CHAR 的性能要比 VARCHAR 更好。
:::

后缀空格问题

现在，让我们插入一个前后带空格的值 ‘ Tom ‘ 到列中：

1

INSERT INTO test_char VALUES(' Tom ');

让我们再看一下结果：

1
2
3
4
5
6
7

+-------+--------------+
| name  | LENGTH(name) |
+-------+--------------+
| Harve |            5 |
| Tim   |            3 |
|  Tom  |            4 |
+-------+--------------+

这里，**’ Tom ‘** 变成了 **’ Tom’**。这是因为 MySQL 会删除 CHAR 值的后面的空格，不论这个空格是否是原来的。
也就是说 MySQL 会忽略字符串尾部的空格。比如 ‘Tom’ 是等同于 ‘Tom ‘ 或者 ‘Tom ‘ 的。
当一个 CHAR 列为唯一列时，这可能会带来问题。

插入超过长度的值

让我们插入一个超过指定长度的 CHAR 值：

1

INSERT INTO test_char VALUES('Steven');

MySQL 不会自动截断字符串，它将会返回如下的错误：

1

ERROR 1406 (22001): Data too long for column 'name' at row 1

INT

MySQL INT 语法

MySQL INT 类型使用起来简单，如下：

1

INT [UNSIGNED]

这里： UNSIGNED 属性标识了此数据类型为无符号整数。

MySQL INT 数据类型实例

INT 数据类型列用来存储整数，比如年龄，数量等，它也可以结合 AUTO_INCREMENT 作为主键列。

定义 INT 列并插入数据

让我们看一下简单的整数列的例子。首先我们创建一个演示表：

1
2
3
4

CREATE TABLE test_int(
    name char(30) NOT NULL,
    age INT NOT NULL
);

上面 SQL 中的 INT 也可以使用 INTEGER 代替。
让我们插入两条数据：

1
2

INSERT INTO test_int (name, age)
VALUES ('Tom', '23'), ('Lucy', 20);

使用 INT 列作为自增列

通常，一个表的主键列使用 INT 数据类型列结合 AUTO_INCREMENT 属性作为主键。请看下面的 SQL：

1
2
3
4
5

CREATE TABLE test_int_pk(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name char(30) NOT NULL,
    age INT NOT NULL
);

这里，id 列是主键列。它的类型是 INT，并且使用了 AUTO_INCREMENT 属性。
拓展链接：数字类型：避免自增踩坑

DEMICAL

MySQL DECIMAL 语法

为了存储精确的数值，我们需要为 DECIMAL 数据类型指定总位数和小数位数。这里是 DECIMAL 数据类型的语法：

1

DECIMAL[(M[,D])] [UNSIGNED] [ZEROFILL]

这里：

• M 是总的位数，不包含小数点和正负号。
• D 是小数部分的位数。如果 D 为 0 则表示没有小数部分。当 D 省略时，默认值为 0。
• UNSIGNED 属性表示数值是无符号的。无符号的数字不能是负数。
• ZEROFILL 属性表示当整数部分位数不足时，用整数的左侧用 0 填充。带有 ZEROFILL 的列将自动具有 UNSIGNED 属性。这和 INT 数据类型一样。

比如，我们定义了如下一个列：

1

amount DECIMAL(9, 4);

那么 amount 列的值的范围是从 -99999.9999 到 99999.9999。
比如，我们定义了如下一个列：

1

amount DECIMAL(9);

那么 amount 列的值的范围是从 -999999999 到 999999999。

MySQL DECIMAL 实例

首先，创建一个表 customer_balance，这个表有 3 列：id、name 和 balance。

1
2
3
4
5

CREATE TABLE customer_balance (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  balance DECIMAL(14, 4) NOT NULL
);

然后，插入数据到 materials 表中。

1
2
3
4

INSERT INTO customer_balance(name, balance)
VALUES ('Tim', 1500.04),
       ('Jim', 10000),
       ('Tom', 333333.4);

现在，让我们从 customer_balance 表中查询数据。

1

SELECT * FROM customer_balance;

1
2
3
4
5
6
7

+----+------+-------------+
| id | name | balance     |
+----+------+-------------+
|  1 | Tim  |   1500.0400 |
|  2 | Jim  |  10000.0000 |
|  3 | Tom  | 333333.4000 |
+----+------+-------------+

BIT

在 MySQL 中 BIT 数据类型被用来存储二进制的位值。我们通常使用 BIT 数据类型的列存储状态值，比如布尔值等。 BIT 数据类型也是易于扩展的。

MySQL BIT 语法

当我们使用 BIT 数据类型时，需要指定一个位数参数。这是语法：

1

BIT(M)

这里:

• BIT(M) 允许存储 M 位值。 M 的取值范围是 1 到 64。
• 如果不指定 M，那么它的默认值为 1。 BIT 等效于 **BIT(1)**。
  

BIT 字面值

当我们需要向 BIT 列中插入 BIT 值时，需要使用 BIT 字面量。 BIT 字面量可以使用如下格式：

• b’val’
• B’val’
• 0bval

这里 val 是二进制值，它仅包含 0 和 1。例如：

1
2
3

b'01'
B'01'
0b01

注意，前导 b 和 B 是等效的。但是 0b 是正确的前导符号，而 0B 不是正确的前导符号。下面是几个不正确的 BIT 字面值：

1
2

b'2'    -- 2 不是正确的 2 进制数
0B01    -- 不能用 0B 前导，应使用 0b

BIT 列非常适合用来存储状态值。在下例子中，我们将创建一个订单状态表 order_state，表中包含了一个用来存储订单状态的 state 列。我们先预设订单有如下状态：

二进制值最大位数是 3 位，那么 state 列的数据类型可以使用 **state BIT(3)**。
我们使用下面的 sql 语句创建表：

1
2
3
4

CREATE TABLE order_state (
  order_id INT NOT NULL PRIMARY KEY,
  state BIT(3) NOT NULL
);

插入 BIT 值

现在，让我我们插入一条状态为 3 的行，我们可以使用 3, b’011’, B’011’, 0b011 其中的任意一个。如下：

1
2
3
4
5

INSERT INTO order_state (order_id, state)
VALUES (1, 3),
       (2, b'011'),
       (3, B'011'),
       (4, 0b011);

现在让我通过以下 SQL 查询 order_state 表中的所有行：

1

SELECT * FROM order_state;

1
2
3
4
5
6
7
8

+----------+--------------+
| order_id | state        |
+----------+--------------+
|        1 | 0x03         |
|        2 | 0x03         |
|        3 | 0x03         |
|        4 | 0x03         |
+----------+--------------+

DATA

MySQL DATE 使用 yyyy-mm-dd 格式来存储日期值。如果您想以其他的日期格式显示，比如 mm-dd-yyyy，您可以使用 DATE_FORMAT 函数将日期格式化为您需要的格式。
MySQL DATE 类型值的范围从 1000-01-01 到 9999-12-31。
当您向 DATE 列中插入值时，您可以使用 yyyy-mm-dd 或者 yy-mm-dd 格式。
在严格模式下，您不能插入无效日期，比如：2018-08-32。否则 MySQL 会给出错误。在非严格模式下，MySQL 会将无效日期转为 0000-00-00。

MySQL DATE 语法

以下是创建 DATE 列的语法：

1

column_name DATE;

这里， 我们无需为 DATE 指定任何属性。

MySQL 日期实例

上面我们说过，您可以将 yyyy-mm-dd 或者 yy-mm-dd 格式的日期值插入到 DATE 列中。
如果您使用两位数的年份值，MySQL 仍然按照以下规则将它转为四位数的年份值：

• 00-69 范围内的年份值将转换为 2000-2069。
• 70-99 范围内的年份值将转换为 1970-1999。

虽然规则时明确的，但是，具有两位数的日期值会给读取您代码的人带来困惑，因此应该避免使用两位数的年份。

MySQL DATE 函数

:::warning
MySQL 提供了许多有用的日期函数，允许您有效地操作日期。以下列出了常用的日期函数：

• NOW(): 获取当前日期和时间
• CURDATE(): 获取当前日期
• DATE(): 获取日期部分
• DATE_FORMAT(): 格式化输出日期
• DATEDIFF(): 计算两个日期之间的天数
• DATE_ADD(): 在给定日期上增加给定的时间间隔
• DATE_SUB(): 在给定日期上减少给定的时间间隔
• DAY(): 返回日期中天
• MONTH(): 返回月份
• QUARTER(): 返回季节
• YEAR(): 返回年份
• WEEK(): 函数返回给定日期是一年周的第几周
• WEEKDAY(): 函数返回工作日索引
• WEEKOFYEAR(): 函数返回日历周
  :::

DATE_FORMAT()

要格式化日期值，请使用 DATE_FORMAT() 函数。以下语句使用日期格式模式 %m/%d/%Y 格式化当前日期：

1

SELECT DATE_FORMAT(CURDATE(), '%m/%d/%Y') today;

1
2
3
4
5

+------------+
| today      |
+------------+
| 03/25/2022 |
+------------+

DATETIME

在 MySQL 中，您使用 DATETIME 来存储包含日期和时间的值。当您从 DATETIME 列中查询数据 时，MySQL DATETIME 列的值以下格式显示：

1

YYYY-MM-DD HH:MM:SS

默认情况下， DATETIME 值范围从 1000-01-01 00:00:00 到 9999-12-31 23:59:59 。
一个 DATETIME 值使用 5 个字节进行存储。

MySQL 日期时间与时间戳

MySQL 提供了另一种类似于 DATETIME 的 时间数据类型 TIMESTAMP 。
TIMESTAMP 需要 4 个字节，而 DATETIME 需要 5 个字节。TIMESTAMP 和 DATETIME 二者都需要额外字节存储小数秒。
TIMESTAMP 值范围从 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC 。如果要存储超过 2038 年的时间值，则应使用 DATETIME 代替 TIMESTAMP 。
MySQL TIMESTAMP 以 UTC 值存储。但是，MySQL 将 DATETIME 值按原样存储，没有时区。

1
2
3
4

SET time_zone = '+03:00';

SELECT ts, dt
FROM timestamp_n_datetime;

1
2
3
4
5

+---------------------+---------------------+
| ts                  | dt                  |
+---------------------+---------------------+
| 2022-03-28 05:52:15 | 2022-03-28 02:52:15 |
+---------------------+---------------------+

TIMESTAMP 列中的值不同。这是因为 TIMESTAMP 当我们更改时区时， TIMESTAMP 列以 UTC 存储日期和时间值，该列的值会根据新时区进行调整。
这意味着如果您使用 TIMESTAMP 数据来存储日期和时间值，则在将数据库移动到位于不同时区的服务器时应该认真考虑。

YEAR

在 MySQL 中， YEAR 数据类型用来存储年份值。
YEAR 数据类型占用 1 个字节，YEAR 值的范围为从 1901 到 2155, 还有 0000。
定义为 YEAR 数据类型的列可以接受多种输入格式，包括：

• 4 位数字的年份值，从 1901 to 2155。
• 4 位数字的年份值的字符串形式，从 ‘1901’ 到 **’2155’**。
• 2 位数字的年份值，从 0 到 99，并按如下规则转换为 4 位数年份：
  • 1 到 69 转换为 2001 到 2069。
  • 70 到 99 转换为 1970 到 1999。
  • 0 转换为 0000。
• 2 位数字的年份值的字符串形式，从 ‘0’ 到 **’99’**，并按如下规则转换为 4 位数年份：
  • ‘0’ 到 ‘69’ 转换为 2000 到 2069。
  • ‘70’ 到 ‘99’ 转换为 1970 到 1999。

若未启用严格 SQL 模式，MySQL 会将无效的 YEAR 值转为 0000。在严格 SQL 模式下，插入无效的 YEAR 值时会产生错误。

MySQL YEAR 实例

让我通过一个实例看一下 YEAR 的用法。
首先，让我们创建表 test_year， 它有包含一个 YEAR 类型的列 created_year。

1
2
3
4

CREATE TABLE test_year(
    id INT AUTO_INCREMENT PRIMARY KEY,
    created_year YEAR NOT NULL
);

接着，让我们使用 4 位数年份插入一行：

1
2

INSERT INTO test_year (created_year)
VALUES (2022);

然后，让我们使用 2 位数年份插入 2 行：

1
2

INSERT INTO test_year (created_year)
VALUES (10), (98);

让我们再插入一个 0 值：

1
2

INSERT INTO test_year (created_year)
VALUES (0), ('0');

最后，让我们通过查询表中的数据，验证一下是否插入正确：

1

SELECT * FROM test_year;

1
2
3
4
5
6
7
8
9

+----+--------------+
| id | created_year |
+----+--------------+
|  1 |         2022 |
|  2 |         2010 |
|  3 |         1998 |
|  4 |         0000 |
|  5 |         2000 |
+----+--------------+

这里，我们可以看出，数字 0 转换为了 0000，而字符串 ‘0’ 转换为了 2000。

在 MySQL 中，一个 ENUM 是一个字符串的列表，它定义了一个列中允许的值，列的值只能是创建列时定义的允许值列表中的的一个。
MySQL ENUM 数据类型列适合存储状态和标识等有限数量的固定值的数据。
MySQL ENUM 数据类型具有以下优点：

• 列值的可读性更强。
• 紧凑的数据存储。MySQL 存储 ENUM 时只存储枚举值对应的数字索引 (1, 2, 3, …)。
  

MySQL ENUM 语法

要是使用 ENUM 数据类型，这是它的语法：

1

ENUM ('v1', 'v2', ..., 'vn')

这里，

• ENUM 是一个关键字，用来声明一个枚举类型。
• v1 到 vn 是此 ENUM 类型的可选项列表，使用 ENUM 类型的列的值只能是上面值中的其中一个。
• 枚举值只能是字符串。

要定义 ENUM 列，请使用以下语法：

1
2

CREATE TABLE table_name
(col_name ENUM ('v1','v2', ..., 'vn'));

在 ENUM 数据类型中，您可以拥有多个枚举值。但是，将枚举值的数量保持在 20 以下是一种很好的做法。

插入 MySQL ENUM 值

要将插入数据到 ENUM 列中，请使用预定义列表中的枚举值。否则，MySQL 会给出错误。 例如，以下语句向 orders 表中插入一个新行。

1
2

INSERT INTO orders(title, state)
VALUES ('Apples', 'Paid');

除了枚举值，您还可以使用枚举成员的数字索引将数据插入到 ENUM 列中。例如：

1
2

INSERT INTO orders(title, state)
VALUES ('Bananas', 2);

在这个例子中，我们没有使用 Paid 枚举值，而是使用了值 2。由于 Paid 的索引是 2，所以是可以接受的。
因为我们定义了 state 一个 NOT NULL 列，当你插入一个新行而不指定state 列的值时，MySQL 将使用第一个枚举成员作为默认值。请执行以下 SQL 语句：

1

INSERT INTO orders(title) VALUES('Oranges');

这里，我们没有 state 列指定值，MySQL 将默认插入第一个枚举成员 Unpaid。

MySQL ENUM 的缺点

MySQL ENUM 带来一些好处，比如可读性和存储效率，但是它也有以下缺点：

1. 更改枚举成员需要使用ALTER TABLE 语句重建整个表，这在资源和时间方面都是昂贵的。

2. 获取完整的枚举列表很复杂，因为您需要访问 information_schema 数据库：

   1 2 3 4

   SELECT column_type FROM information_schema.COLUMNS WHERE TABLE_NAME = 'orders' AND COLUMN_NAME = 'state';

3. 因为 ENUM 它不是 SQL 标准的，因此，移植到其他 RDBMS 可能是一个问题。

4. 枚举列表是不可重用的。例如，上面 orders 表中的状态枚举值，不能重用到新建的其他表上。

5. 枚举值是字符串，不能包含更多的信息。比如我们需要在每个订单状态上添加超时属性。
   

MySQL事务

事务的四个特性(ACID)【面试常考项】

:::danger
原子性（Atomicity）：指事务是一个不可分割的最小工作单位，事务中的操作只有都发生和都不发生两种情况
一致性（Consistency）：事务必须使数据库从一个一致状态变换到另外一个一致状态，举一个栗子，李二给王五转账50元，其事务就是让李二账户上减去50元，王五账户上加上50元；一致性是指其他事务看到的情况是要么李二还没有给王五转账的状态，要么王五已经成功接收到李二的50元转账。而对于李二少了50元，王五还没加上50元这个中间状态是不可见的。
隔离性（Isolation）：一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（Durability）：一个事务一旦提交成功，它对数据库中数据的改变将是永久性的，接下来的其他操作或故障不应对其有任何影响。
:::

脏读

在事务A修改数据之后提交数据之前，这时另一个事务B来读取数据，如果不加控制，事务B读取到A修改过数据，之后A又对数据做了修改再提交，则B读到的数据是脏数据，此过程称为脏读Dirty Read。

不可重复读

一个事务内在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了变更、或者某些记录已经被删除了。

幻读

幻读是指在一个事务中执行了某个查询操作，然后在随后的操作中，另一个事务插入了一些满足前一个查询条件的新数据，这时再次执行相同的查询，就会发现多出了一些之前不存在的数据行。
幻读问题的根本原因在于，事务在查询数据的时候，其他事务可能会对数据做出插入、删除等操作，从而导致事务在隔离期间看到的数据发生变化。

在您的示例中，事务 A 在执行第一次 SELECT 操作后，事务 B 插入了一些新的数据行。由于事务 A 还没有提交，因此它可以看到事务 B 插入的数据行。当事务 A 再次执行 SELECT 操作时，就会发现多出了一些之前不存在的数据行。

不可重复读与幻读有什么区别？

• 不可重复读的重点是修改：在同一事务中，同样的条件，第一次读的数据和第二次读的「数据不一样」。（因为中间有其他事务提交了修改）
• 幻读的重点在于新增或者删除：在同一事务中，同样的条件，第一次和第二次读出来的「记录数不一样」。（因为中间有其他事务提交了插入/删除）

隔离级别

什么是自动提交？

MySQL默认采用自动提交AUTOCOMMIT模式。也就是说，如果不是显式地开始一个事务，则每个查询都被当作一个事务执行提交操作。
对于MyISAM或者内存表这些事务型的表，修改AUTOCOMMIT不会有任何影响。对这类表来说，没有COMMIT或者ROLLBACK的概念，也可以说是相当于一直处于AUTOCOMMIT启用的模式。

主键

在 MySQL 中，主键需要遵循以下规则：

• 主键是定义在表上的。一个表不强制定义主键，但最多只能定义一个主键。
• 主键可以包含一个列或者多个列。
• 主键列的值必须是唯一的。如果主键包含多个列，则这些列的值组合起来必须是唯一的。
• 主键列中不能包含 NULL 值。

如果不遵循上面的规则，则可能会引发以下的错误。

• 如果定义了多个主键，会返回错误：ERROR 1068 (42000): Multiple primary key defined。
• 如果插入或者更新时有重复的主键值，则会返回类似的错误：**ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘user.PRIMARY’**。
• 如果插入了 NULL 值，则会返回类似的错误：ERROR 1048 (23000): Column ‘id’ cannot be null。

定义主键

我们可以在创建表时定义主键。如下：

1
2
3
4

CREATE TABLE user (
  id INT PRIMARY KEY,
  name VARCHAR(45)
);

这里定义了 id 列为主键。
上面这种方式适合只有一列作为主键的情况，如果主键包含多列，请使用下面的方式:

1
2
3
4
5
6

CREATE TABLE user (
  com_id INT,
  user_number INT,
  name VARCHAR(45),
  PRIMARY KEY(com_id, user_number)
);

这里定义一个主键，它包含 com_id 和 user_number 2 列。

添加主键

如果我们创建表的时候没有设置主键，而现在我们想添加一个主键，请使用下面的方式：

1
2

ALTER TABLE user
ADD PRIMARY KEY(id);

这里为 user 表添加了主键，该主键包括 id 列。
注意，当你向一个有数据的表中添加主键时，由于主键要求列的值是唯一的并且不能为 NULL，如果该列中有重复的值或者 NULL 值，则会返回类似如下的错误：
ERROR 1062 (23000): Duplicate entry ‘1’ for key ‘user.PRIMARY’

删除主键

如果我们想删除一个表上主键，请使用下面的方式：

1
2

ALTER TABLE user
DROP PRIMARY KEY;

这里，我们删除了 user 表上的主键。

如何产生主键值

通常在业务系统中，我们不使用业务字段作为主键，虽然它们也是唯一的。我们一般使用单独的字段作为主键，这主要是出于以下两方面的原因：

1. 保密业务数据
2. 方便这些业务字段的修改

为了生成唯一的主键值，我们通常采用以下方法：

1. 将主键字段设置为 AUTO_INCREMENT。声明为 AUTO_INCREMENT 的字段会自动生成连续的整数值。

2. 使用 UUID() 函数。UUID() 函数产生一个长度为 36 个字符的字符串，并且永不重复。如下：

   1	SELECT UUID();

   1 2 3 4 5 6

   +--------------------------------------+ | UUID() | +--------------------------------------+ | 523efe70-29ac-11ec-a78b-6dba5fac4247 | +--------------------------------------+ 1 row in set (0.00 sec)

   UUID() 适合用在集群环境下。这样即使一个表被分区在多个服务器上，也不会产生相同的主键的记录。

3. 使用 UUID_SHORT() 函数。UUID_SHORT() 函数返回一个 64 位无符号整数并全局唯一。如下：

   1	SELECT UUID_SHORT();

   1 2 3 4 5 6

   +-------------------+ | UUID_SHORT() | +-------------------+ | 99469056502923283 | +-------------------+ 1 row in set (0.00 sec)

外键

外键相对于主键而言，用来引用其他表。外键通过子表的一个或多个列对应到父表的主键或唯一键值，将子表的行和父表行建立起关联关系。
让我们看一下 Sakila 示例数据库中的 country 表和 city 表。下面是它们的关系图：

country 和 city 关系
country 表和 city 表是一对多的关系。一个国家中可以有多个城市，一个城市只能位于一个国家。
如果一个国家已经有了城市，那么这个你就不能轻易的从 country 表删除国家，否则就会造成这个城市数据的不完整。你也不能为一个城市设定一个不存在的 country_id，否则这个城市数据就是错误的。
外键约束能保证数据的完整和正确。

外键的语法

让我们看一下 city 表定义的外键约束：

1

SHOW CREATE TABLE city\G

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

*************************** 1. row ***************************
       Table: city
Create Table: CREATE TABLE `city` (
  `city_id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `city` varchar(50) NOT NULL,
  `country_id` smallint unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`city_id`),
  KEY `idx_fk_country_id` (`country_id`),
  CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`)
    REFERENCES `country` (`country_id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=601 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

注意其中的部分：

1
2
3
4

CONSTRAINT `fk_city_country` FOREIGN KEY (`country_id`)
    REFERENCES `country` (`country_id`)
    ON DELETE RESTRICT
    ON UPDATE CASCADE

这里定义了一个外键：

• 位于 CONSTRAINT 关键字之后的 fk_city_country 是外键的名字。它是可选的。
• 位于 FOREIGN KEY 关键字之后的是作为外键的列名。
• 位于 REFERENCES 关键字之后的是被引用的表和列。
• ON DELETE 和 ON UPDATE 指定了删除或更新被引用的表中的数据时要采取的约束策略。你可以使用以下 3 个策略中的一个：
  • CASCADE：如果被引用的表中的一行被删除或更新，该表中匹配行的值会自动删除或更新。
  • SET NULL：如果被引用的表中的一行被删除或更新，该表中匹配行的值设置为 NULL。
  • RESTRICT: 如果被引用的表中的一行在该表中有匹配的行，试图删除或更新被引用的表中行时会引发 MySQL 错误。这是默认的策略。

通常，外键所属的表被称作子表，被外键引用的表被称作父表。

添加外键语法

如果建表的时候没有定义外键，你也可以后来通过以下语法添加外键：

1
2
3
4
5
6
7

ALTER TABLE child_table_name
ADD [CONSTRAINT foreign_key_name]
  FOREIGN KEY (column))
  REFERENCES parent_table_name (column)
  ON UPDATE CASCADE
  ON DELETE NO ACTION
  ;

这里：

• 使用 ALTER TABLE 语句修改表的定义。
• 使用 ADD [CONSTRAINT foreign_key_name] 添加一个名为 foreign_key_name 的约束。**[CONSTRAINT foreign_key_name]** 是可选的。
• 使用 FOREIGN KEY (column)) REFERENCES parent_table_name (column) 定义了外键。
  

删除外键语法

要删除表上外键，可以采用下面的两种语法之一：

• 
  

  1 2	ALTER TABLE table_name DROP FOREIGN KEY foreign_key_name;

• 
  

  1 2	ALTER TABLE table_name DROP CONSTRAINT constraint_name;

  这里：

• 使用 ALTER TABLE 语句修改表的定义。

• DROP FOREIGN KEY 后面指定外键名，也就是约束名。

• DROP CONSTRAINT 后面指定约束名。它可以通过名字删除任何约束，并不仅仅是外键。

CASCADE 策略

如果外键的 ON DELETE 和 ON UPDATE 使用了 CASCADE 策略：

• 当父表的行被删除的时候，子表中匹配的行也会被删除。
• 当父表的行的键值更新的时候，子表中匹配的行的字段也会被更新。
  

RESTRICT 策略

如果外键的 ON DELETE 和 ON UPDATE 使用了 RESTRICT 策略：

• MySQL 禁止删除父表中与子表匹配的行。
• MySQL 禁止删除父表中与子表匹配的行的键的值。
  

SET NULL 策略

如果外键的 ON DELETE 和 ON UPDATE 使用了 SET NULL 策略：

• 当父表的行被删除的时候，子表中匹配的行的列的值被设置为 NULL。
• 当父表的行的键值被更新的时候，子表中匹配的行的列的值被设置为 NULL。

多表关系

一对多

多对多

一对一

三大范式

三大范式概念

第一范式(1NF)：每个列都不可以再拆分。
第二范式(2NF)：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
第三范式(3NF)：在第二范式的基础上，非主键列只依赖于主键，不依赖于其他非主键。

第一范式

注意__⚠️__：这个表的主键是学号 + 课程名
比如我们有一个表，以后的例子会对这个表进行三大范式的改造，然后把他变成规范的表：

进行第一范式的改造
第一范式(1NF)：每个列都不可以再拆分
我们可以看到表中有一列可以再分，那就是系，所以把他进行第一范式的改造就变成了：

第二范式

第二范式(2NF)：在第一范式的基础上，非主键列完全依赖于主键，而不能是依赖于主键的一部分。
:::tips
这第二范式不好理解，那么我们先了解几个概念：
1.函数依赖：如果通过A属性(属性组)，可以确定唯一B属性的值，那么B依赖于A。比如上图的姓名，完全依赖于学号
2.完全函数依赖：如果A是一个属性组，则B属性值的确定需要依赖于A属性组中的所有的属性值。属性组是指多个字段，那么比如我们要想知道一个分数，就必须依赖于学号和课程名称两个属性才能确定一个分数，其他的属性是不能确定某一个分数的
3.部分函数依赖：如果A是一个属性组，则B属性值的确定需要依赖A属性组的某一些字段即可，例如学号和课程名称为一个属性组，那么学生姓名其实就只需要学号就可以确定
4.传递函数依赖：如果A属性(属性组),可以确定唯一个B属性的值，再通过B属性的值又可以唯一确定C属性的值，例如一个学号确定一个系名，一个系名对应一个系主任
5.主键：在一张表中，一个属性或者属性组，被其他所有属性完全依赖，则称这个属性为该码的表，比如上图的学号和课程名称组成的属性组
:::
其实第二范式就是还可以理解为:
在第一范式的基础上消除非主键对主键的部分依赖

那么我们上图的主键为学号加课程名称组成的属性组，对于上图，我们可以看到，除了分数其他的都对主键是部分依赖，那么我们可以按照下图的方式进行改正：

经过第二范式的改造把一个表分成两个表，那么我们发现其实第二范式为我们消除了很多冗余的部分，比如改造前张无忌的姓名系名系主任在表中出现了三次，而改造后在两表才出现一次
再举一个例子：
这里的courses表的所有字段都是courses的一个属性，所以他符合第二范式。
而如果我们这里包含这门课被注册的时间(enrollment_data)，它并不是courses的一个属性，这们课可以有多个学生，每个学生有不同的注册日期，所以这列enrollment_data不属于这张表

再来一个例子：
一个ORDERS表，含有顾客名列描述了一位顾客而不是一个订单，此时这个用户订购了多张订单，他的名字就会重复，这不仅浪费空间，还会让我们更新的很痛苦

我们改进一下，这样我们修改用户名的时候只需要修改一次就好了

第三范式

第三范式：表中的列不应派生自其他的列

balance是由invoice_total列和patment_total列来决定的，这违背了第三范式。

创建和删除数据库

创建

1

CREATE DATABASE IF NOT EXISTS a_database;

删除

1

DROP DATABASE IF EXISTS a_database;

创建表

创建一张表

1
2
3
4
5
6
7

CREATE TABLE IF NOT EXISTS customers
(
customer_id INT PRIMARY KEY AUTO_INCREMENT,
first_name	VARCHAR(50) NOT NULL,
points		INT NOT NULL DEFAULT 0,
email		VARCHAR(255) NOT NULL UNIQUE
);

删除

1

DROP TABLE IF EXISTS customers;

更改表

需求：我们发现忘记添加或删除表中的数据

添加、删除、更改列

1
2
3
4
5
6

ALTER TABLE customers
ADD last_name VARCHAR(50) NOT NULL AFTER first_name,
ADD city 	    VARCHAR(50) NOT NULL,
MODIFY COLUMN first_name VARCHAR(55) DEFAULT '',
DROP points
;