01｜SQL 语法

1. count 主键和 count 非主键结果会不同吗？

#

分析

count() 函数是返回表中某个列的非 NULL 值的数量。

• 由于主键的列不能有 NULL 值，所以 count(主键) 返回的结果，可以表示数据库表中所有行数据的数量。
• 由于非主键的列可以有 NULL 值，那么 count(非主键) 返回表中非主键列的非 NULL 值的数量。

回答

主键是不能存 NULL 值的，所以 count(主键) 代表统计表中所有行数据的数量。

而非主键是可以存 NULL 值的，所以 count(非主键) 统计的是表中这个列的非 NULL 值的数量。

2. MySQL 内连接、外连接有什么区别？

#

分析

内连接（INNER JOIN）：内连接返回两个表中匹配的行，即只返回两个表中共有的数据。

外连接（OUTER JOIN）：外连接则返回两个表中匹配和不匹配的行。MySQL 外连接主要有左外连接（LEFT JOIN）、右外连接（RIGHT JOIN）两种。

• 左连接（LEFT JOIN）：

SELECT * FROM A LEFT JOIN B ON A.A_id = B.B_id;

将返回左表 A 中的所有行和右表 B 中与之匹配的行。如果 B 表中没有匹配的行，则 B 表相关的列使用 NULL 值填充。

• 右连接（RIGHT JOIN）：

SELECT * FROM A RIGHT JOIN B ON A.A_id = B.B_id;

将返回右表 B 中的所有行和左表 A 中与之匹配的行。如果 A 表中没有匹配的行，则 A 表相关的列使用 NULL 值填充。

回答

内连接和外连接都是用于连表查询。

内连接是只返回两个表匹配的数据行；外连接可以返回两个表匹配和不匹配的数据行，外连接主要分为左连接和右连接。

• 左连接返回左表中的所有行和右表中匹配的行，如果右表中没有匹配的行，则用 NULL 值填充。
• 右连接返回右表中的所有行和左表中匹配的行，如果左表中没有匹配的行，则用 NULL 值填充。

3. 外连接时 on 和 where 过滤条件区别？

#

分析

• 对于内连接（INNER JOIN）查询，WHERE 和 ON 中的过滤条件等效。
• 对于外连接（OUTER JOIN）查询，ON 中的过滤条件在连接时进行，WHERE 中的过滤条件在连接操作之后执行。

回答

外连接中，ON 和 WHERE 的过滤条件区别在于：

• ON 用于指定连接两个表的条件，通常用于指定两个表之间的关联条件，即连接条件，在连接时进行过滤。
• WHERE 用于指定过滤条件，对连接后的结果集进行进一步筛选。

4. having 与 where 的区别？

#

分析

WHERE 与 HAVING 的根本区别在于：

• WHERE 子句在 GROUP BY 分组和聚合函数之前对数据行进行过滤，WHERE 子句无法使用聚合函数。
• HAVING 子句对 GROUP BY 分组和聚合函数之后的数据行进行过滤，HAVING 子句可以使用聚合函数。

回答

在 GROUP BY 分组查询过程中，WHERE 工作在 GROUP BY 之前，是对分组之前的数据进行筛选，无法使用聚合函数；HAVING 工作在 GROUP BY 之后，主要对分组之后的数据进行筛选，可以使用聚合函数。

5. EXISTS 和 IN 的区别是什么？

#

分析

IN 和 EXISTS 一般用于子查询，语法如下：

SELECT * FROM A WHERE id IN (SELECT id FROM B);

SELECT * FROM A WHERE EXISTS (SELECT 1 FROM B WHERE A.id = B.id);

性能区别：

• 当 A 表（外表）数据与 B 表（内表）数据一样大时，IN 与 EXISTS 效率差不多，可任选一个使用。
• IN 适合子外表大而内表小的情况，EXISTS 适合子外表小而内表大的情况。

IN 的工作原理：

IN() 里的查询只会执行一次，它查出 B 表中的所有 id 字段并缓存起来。之后检查 A 表的 id 是否与 B 表中的 id 相等，在内存中判断，如果相等则将 A 表的记录加入结果集中，直到遍历完 A 表的所有记录。

它的查询过程类似如下：

List resultSet = {};

Array A = (SELECT * FROM A);
Array B = (SELECT id FROM B);

for (int i = 0; i < A.length; i++) {
    for (int j = 0; j < B.length; j++) {
        if (A[i].id == B[j].id) {
            resultSet.add(A[i]);
            break;
        }
    }
}

return resultSet;

可以看出，当 B 表数据较大时不适合使用 IN()，因为它会把 B 表数据全部遍历一次。

• 例 1：A 表有 10000 条记录（小表），B 表有 1000000 条记录（大表），那么最多有可能遍历 10000 * 1000000 次，效率很差。
• 例 2：A 表有 10000 条记录（大表），B 表有 100 条记录（小表），那么最多有可能遍历 10000 * 100 次，遍历次数大大减少，效率大大提升。

EXISTS 的工作原理：

EXISTS() 会执行 A.length 次，它并不缓存 EXISTS() 结果集，因为 EXISTS() 结果集的内容并不重要，重要的是其内查询语句的结果集空或者非空，空则返回 false，非空则返回 true。

它的查询过程类似如下：

List resultSet = {};
Array A = (SELECT * FROM A);

for (int i = 0; i < A.length; i++) {
    if (exists(A[i].id)) {
        // 执行 SELECT 1 FROM B WHERE B.id = A.id，是否有记录返回
        resultSet.add(A[i]);
    }
}

return resultSet;

当 B 表（大表）比 A 表（小表）数据大时适合使用 EXISTS()，因为它没有那么多遍历操作，只需要再执行一次查询就行。

• 例 1：A 表有 10000 条记录，B 表有 1000000 条记录，那么 EXISTS() 会执行 10000 次去判断 A 表中的 id 是否与 B 表中的 id 相等。
• 例 2：A 表有 10000 条记录，B 表有 100000000 条记录，那么 EXISTS() 还是执行 10000 次，因为它只执行 A.length 次，可见 B 表数据越多，越适合 EXISTS() 发挥效果。
• 例 3：A 表有 10000 条记录，B 表有 100 条记录，那么 EXISTS() 还是执行 10000 次，还不如使用 IN() 遍历 10000 * 100 次。因为 IN() 是在内存里遍历比较，而 EXISTS() 需要查询数据库，查询数据库所消耗的性能更高，而内存比较很快。

回答

• 内部工作原理区别：
  • IN 是先执行内表，会把查询到的内表数据缓存起来，然后会进行双重 for 循环，外层的 for 循环是遍历外表记录，内层的 for 循环是遍历内表记录，最后每一次 for 循环时在内存判断内表的记录与外表记录是否一致。
  • EXISTS 会遍历外表的记录，每一次循环都会进行一次内查询来判断数据是否匹配。
• 性能区别：
  • 如果查询的两个表大小相当，那么 IN 和 EXISTS 性能差别不大。
  • 如果查询的两个表中一个是小表，一个是大表，IN 适合子外表大而内表小的情况，EXISTS 适合子外表小而内表大的情况。

6. MySQL 的约束有哪些？

#

分析

当我们创建数据表的时候，还会对字段进行约束，约束的目的在于保证数据库里面数据的准确性和一致性。

主要有六大约束：

• 主键约束（PRIMARY KEY）：主键起的作用是唯一标识一条记录，不能重复，不能为空，即 UNIQUE + NOT NULL。一个数据表的主键只能有一个，主键可以是一个字段，也可以由多个字段复合组成。一般我们会把数据库表中 id 字段设置为主键，每个表中只能有一个 PRIMARY KEY 约束，但是可以有多个 UNIQUE 约束。
• 外键约束（FOREIGN KEY）：外键确保了表与表之间引用的完整性。一个表中的外键对应另一张表的主键。外键可以是重复的，也可以为空。
• 唯一性约束（UNIQUE）：唯一性约束表明字段在表中的数值是唯一的。即使我们已经有了主键，还可以对其他字段进行唯一性约束。比如 player 表中给 player_name 设置唯一性约束，就表明任何两个球员的姓名不能相同。需要注意的是，唯一性约束和普通索引（NORMAL INDEX）之间是有区别的。唯一性约束相当于创建了一个约束和普通索引，目的是保证字段的正确性；普通索引只是提升数据检索的速度，并不对字段的唯一性进行约束。
• 非空约束（NOT NULL）：对字段定义了 NOT NULL，即表明该字段不能为空，必须有取值。
• 默认约束（DEFAULT）：表明字段的默认值。如果插入数据的时候，这个字段没有取值，就设置为默认值。比如把价格身高 height 字段的取值默认设置为 0.00，即 DEFAULT 0.00。
• 检查约束（CHECK）：用来检查特定字段取值范围的有效性，CHECK 约束的结果不能为 FALSE。比如可以对身高 height 的数值进行 CHECK 约束，必须大于等于 0 且小于 3，即 CHECK (height >= 0 AND height < 3)。

注意：MySQL 只支持前 5 种约束，不支持检查约束。MySQL 默认也忽略 CHECK 约束并且不执行数据验证。要在 MySQL 中实现约束，可以使用触发器或视图。

回答

数据库的约束主要有 6 大约束，分别是主键约束、外键约束、唯一性约束、非空约束、默认约束、检查约束。MySQL 只支持前 5 种约束，不支持检查约束。

MySQL 支持的这些约束的作用如下：

• 主键约束的作用是唯一标识一条记录，不能重复也不能为空，一张表只能有一个主键，一般会针对 id 字段设置为主键。
• 外键约束的作用是确保表与表之间引用的完整性。
• 唯一性约束的作用是保证字段在表中的数值是唯一的，如果插入相同字段值的记录，就会报唯一性约束的错误。
• 非空约束的作用是保证字段不能为空。
• 默认约束的作用是给字段设置默认值，如果插入数据的时候，这个字段没有取值的话，就会用默认值。

7. delete、drop、truncate 有什么区别？

#

分析

• DELETE 用来删除记录。该命令其实只是把“记录的位置”或者“数据页”标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 DELETE 命令不能回收表空间。但是 DELETE 全表是很慢的，需要生成回滚日志、redo、undo、binlog。所以，从性能角度考虑，你应该优先考虑使用 TRUNCATE TABLE 或者 DROP TABLE 命令。
• DROP 可以用来删除表和表数据。每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中，那么使用 DROP TABLE 命令，系统就会直接删除这个文件，从而回收表空间大小。如果表的数据放在系统共享表空间，即使 DROP TABLE，空间也是不会回收的。
• TRUNCATE 可以用来删除表中的所有记录来实现数据清空。与 DELETE 命令不同，它不逐行删除数据。但表结构及其列、约束、索引等保持不变，并且重置 id 从 1 开始。在 InnoDB 存储引擎中，TRUNCATE 命令会释放表的表空间，但是表文件（如 .ibd 文件）不会被删除。表文件会被重用，而不是删除和重新创建。

回答

DELETE 是删除表中的数据，可以选择删除部分数据或者全部数据。DELETE 删除的数据是可以回滚的，DELETE 操作并不是真的把数据删除掉，而是给数据打上删除标记，目的是为了空间复用，所以 DELETE 删除表数据后，磁盘文件的大小是不会缩减的。

DROP 是删除表结构和表中所有的数据，TRUNCATE 是只删除表中所有的记录，表结构并不会被删除。DROP 和 TRUNCATE 删除的数据都是不可以回滚的，并且删除表会立刻释放磁盘空间。

从删除表的性能来看，DROP > TRUNCATE > DELETE。

8. 联合查询中 union 和 union all 的区别是什么？

#

分析

主要区别在于处理重复值的方式：

• UNION：UNION 操作符用于合并多个查询结果，并去除重复的行。当使用 UNION 时，查询结果中的重复行只会被包含一次。这意味着如果两个查询结果中有相同的行，则只会在最终的结果中包含一次。
• UNION ALL：UNION ALL 操作符也用于合并多个查询结果，但不会去除重复的行。当使用 UNION ALL 时，查询结果中的重复行会被全部包含。这意味着如果两个查询结果中有相同的行，则在最终的结果中会包含所有的重复行。

回答

• UNION：在合并结果集后会自动剔除重复的行。
• UNION ALL：会保留所有的重复行，不会进行去重操作。

9. 数据库三大范式是什么？范式设计是为了解决什么问题？范式设计有什么缺点？

#

分析

数据库三大范式是数据库设计中的规范化原则，包括：

• 第一范式（1NF）：要求关系模式中的每个属性都是原子的，即属性不可再分。确保每个属性都是不可再分的基本数据项。比如学生（学号、姓名、性别、出生年月日），如果认为最后一列还可以再分成（出生年、出生月、出生日），它就不是一范式了，否则就是。
• 第二范式（2NF）：要求关系模式必须符合第一范式，并且非主属性必须完全依赖于候选关键字，而不是部分依赖。确保每个非主属性都完全依赖于候选关键字。比如「表：学号、课程号、姓名、学分」，这个表包含了两种信息：学生信息、课程信息。由于非主键字段必须依赖主键，这里学分依赖课程号，姓名依赖学号，所以不符合二范式。符合第二范式的做法是：「学生：Student（学号、姓名）」；「课程：Course（课程号、学分）」；「选课关系：StudentCourse（学号、课程号、成绩）」。
• 第三范式（3NF）：要求关系模式必须符合第二范式，并且不存在传递依赖。即所有非主属性之间不能存在依赖关系。确保不存在非主属性对其他非主属性的传递依赖。举例来说，如果有一个关系模式 R(A, B, C)，其中 A -> B（A 决定 B）、B -> C（B 决定 C），那么存在传递依赖，因为 A 决定了 B，B 又决定了 C，从而导致 A -> C 的传递依赖。比如「表：学号、姓名、年龄、学院名称、学院电话」，表属于第二范式，因为主键由单个属性组成（学号）。但是存在依赖传递：（学号）->（学生）->（所在学院）->（学院电话）。符合第三范式的做法是：「学生：（学号、姓名、年龄、所在学院）」；「学院：（学院、学院名称、电话）」。

回答

• 一范式要求所有属性都是不可分的基本数据项。
• 二范式目的是解决部分依赖。
• 三范式目的是解决传递依赖。

在实际工程实践上没有必要严格遵循三范式要求，比如说可以通过字段冗余的设计，避免联表查询。

追问 1：范式设计是为了解决什么问题？

数据库的范式是为了解决数据冗余、数据不一致性、数据更新异常和插入异常等问题。

• 数据冗余： 数据冗余是指数据库中存储了大量重复的数据，这不仅浪费了存储空间，而且可能导致数据不一致的问题。范式通过规定数据的结构和组织方式，使得每一份数据只需要被存储一次，从而避免了数据冗余。
• 数据更新异常： 更新异常是指当我们尝试更新一份数据时，可能需要在多个地方进行修改，而如果某一处修改被遗漏，就会导致数据不一致的问题。范式通过规定数据的组织和关联方式，使得每一份数据只需要被修改一次，从而避免了更新异常。
• 插入异常： 插入异常是指当我们尝试插入一份新的数据时，可能因为数据的组织和关联方式的问题，而无法进行插入。范式通过规定数据的组织和关联方式，使得任何合法的新数据都可以被顺利插入，从而避免了插入异常。
• 保证数据的一致性和完整性： 范式化的设计通过消除数据冗余和依赖关系，使得数据的一致性和完整性得到保证。当数据只存在于一个位置时，更新和修改数据更加简单和可靠。通过实施数据库范式，开发人员能够确保数据库架构更具可维护性和可扩展性，尤其在处理大型数据集时，范式化能够确保数据的一致性，并使数据库操作更高效。

追问 2：范式设计有什么缺点？

范式化将数据分解为多个表，那么查询数据的时候，就需要进行更多的表连接操作。在应用中，进行表关联的成本是很高的，也不适合分库分表的场景，所以有时候实际应用、设计表的时候会反范式，比如可以通过字段冗余的设计，避免联表查询。

10. count(*) 性能比 count(1) 好吗？

#

分析

按照性能排序：count(*) = count(1) > count(主键字段) > count(字段)。

count(*) 其实等于 count(0)，也就是说，当你使用 count(*) 时，MySQL 会将 * 参数转化为参数 0 来处理。

所以，count(*) 执行过程跟 count(1) 执行过程基本一样，性能没有什么差异。

回答

不是的。

MySQL 会将星号参数转化为参数 0 来处理，所以 count(*) 和 count(1) 性能是一样的。