MySql基础

操作语句

DDL（Data Definition Language）数据定义语言

DDL命令用于定义和管理数据库对象，如表、索引、视图等。

DML（Data Manipulation Language）数据操作语言

DML命令用于对数据库中的数据进行增、删、改操作。

DQL（Data Query Language）数据查询语言

DQL命令用于查询数据库中的数据。

排序方式

1. 按单列排序

   • 升序排序（默认）：使用 ORDER BY column_name ASC，也可以省略 ASC。

   • 降序排序：使用 ORDER BY column_name DESC。

1
2

SELECT * FROM employees ORDER BY salary ASC;
SELECT * FROM employees ORDER BY salary DESC;

2. 按多列排序
   • 按多列排序：可以指定多个列，依次进行排序。如果第一列有相同的值，则按照第二列排序，依此类推。

1

SELECT * FROM employees ORDER BY department_id ASC, salary DESC;

3. 按表达式排序
   • 按计算表达式排序：可以使用计算表达式进行排序，例如根据某列的计算结果排序。

1

SELECT * FROM employees ORDER BY salary * 1.1 DESC;

4. 按字段位置排序
   • 按字段位置排序：使用字段在 SELECT 子句中的位置进行排序。位置从1开始。

1

SELECT name, salary, department_id FROM employees ORDER BY 2 DESC;

5. 按函数结果排序
   • 按函数返回值排序：可以使用函数返回的值进行排序。

1

SELECT * FROM employees ORDER BY LENGTH(name) ASC;

6. 自定义排序
   • 使用 FIELD() 函数进行自定义排序：可以根据指定的顺序对数据进行排序。

1

SELECT * FROM employees ORDER BY FIELD(department_id, 3, 2, 1);

7. 随机排序
   • 使用 RAND() 函数进行随机排序：通常用于需要随机抽取数据的场景。

1

SELECT * FROM employees ORDER BY RAND();

8. 空值排序
   • NULLS FIRST 或 NULLS LAST（MySQL 8.0及更高版本）：可以控制 NULL 值在排序时是排在前面还是后面。

1
2

SELECT * FROM employees ORDER BY salary ASC NULLS LAST;
SELECT * FROM employees ORDER BY salary DESC NULLS FIRST;

执行顺序

1. FROM

• 从指定的表或视图中检索数据。如果涉及多个表，还会在此步骤执行连接操作（如 JOIN）。
• 例子：FROM employees

2. JOIN

• 处理表之间的连接。如果有多个表，MySQL 会根据连接条件将它们组合成一个结果集。
• 例子：JOIN departments ON employees.department_id = departments.id

3. WHERE

• 过滤数据，只有满足 WHERE 条件的行才会被保留在结果集中。
• 例子：WHERE salary > 50000

4. GROUP BY

• 将结果集中的行按照指定的列分组。通常用于汇总数据。
• 例子：GROUP BY department_id

5. HAVING

• 对分组后的数据进行过滤。类似于 WHERE，但作用于 GROUP BY 之后的结果集。
• 例子：HAVING COUNT(*) > 10

6. SELECT

• 选择要返回的列或计算结果。包括对数据的投影（选择列）和计算（如使用聚合函数）。
• 例子：SELECT department_id, COUNT(*)

7. DISTINCT

• 从结果集中去除重复行。通常和 SELECT 一起使用。
• 例子：SELECT DISTINCT department_id

8. ORDER BY

• 对结果集进行排序。可以按一列或多列进行升序或降序排序。
• 例子：ORDER BY salary DESC

9. LIMIT / OFFSET

• 限制返回的行数。用于分页或限制结果集的大小。
• 例子：LIMIT 10 OFFSET 20

完整的执行顺序

1. FROM：确定数据来源，包括表和视图，并处理 JOIN。
2. WHERE：根据条件过滤数据，减少需要处理的数据量。
3. GROUP BY：将数据分组，以便进行汇总计算。
4. HAVING：对分组后的数据再次过滤。
5. SELECT：选择和计算要返回的数据列。
6. DISTINCT：去除重复行。
7. ORDER BY：对结果集进行排序。
8. LIMIT / OFFSET：限制返回的行数，进行分页。

示例 SQL 语句

1
2
3
4
5
6
7

sql复制代码SELECT department_id, COUNT(*)
FROM employees
WHERE salary > 50000
GROUP BY department_id
HAVING COUNT(*) > 10
ORDER BY COUNT(*) DESC
LIMIT 5;

执行顺序

1. FROM employees
2. WHERE salary > 50000
3. GROUP BY department_id
4. HAVING COUNT(*) > 10
5. SELECT department_id, COUNT(*)
6. ORDER BY COUNT(*) DESC
7. LIMIT 5

DCL（Data Control Language）数据控制语言

DCL命令用于控制数据库的访问权限。

其他常见命令

除了上述常见的分类外，还有一些常用命令：

函数

1. 字符串函数

2. 数值函数

3. 日期和时间函数

4. 流程控制函数

范式

1. 第一范式（1NF）

第一范式要求数据库中的每个表格的每一列都应该是不可分割的原子值，也就是说，每列的数据都是单一值，而不能是集合、数组或其他表格。

例子： 假设有一个学生表Students：

上述表格未满足第一范式，因为电话号码字段包含了多个值。为了满足1NF，我们可以将电话号码拆分成单独的行：

2. 第二范式（2NF）

第二范式在满足第一范式的基础上，还要求每个非主属性都必须完全依赖于候选键（即，不能存在部分依赖）。

例子： 假设有一个订单表Orders：

在这个例子中，产品名称和单价依赖于产品ID，而不完全依赖于订单ID。为了符合第二范式，我们可以将表拆分为两个表：

Orders表：

Products表：

3. 第三范式（3NF）

第三范式要求在满足第二范式的基础上，表中的非主属性之间不应该存在传递依赖（即，非主属性不能依赖于另一个非主属性）。

例子： 假设有一个员工表Employees：

在这个例子中，部门名称依赖于部门ID，而部门ID又依赖于员工ID，这违反了第三范式。为了满足第三范式，我们可以将表拆分为两个表：

Employees表：

Departments表：

4. BCNF（Boyce-Codd范式）

BCNF是第三范式的一个更严格的版本。它要求表中的每一个决定因素（candidate key的一部分）都是候选键。

例子： 假设有一个课程表Course：

在这个例子中，学生ID和课程ID共同决定了教师，但教师也依赖于课程ID，而课程ID并不是候选键。为了符合BCNF，我们可以将表拆分为两个表：

CourseTeacher表：

StudentCourse表：

5. 第四范式（4NF）

第四范式要求在BCNF的基础上，表中不能存在多值依赖（即，不能有一个属性依赖于另一个属性的多值）。

例子： 假设有一个项目表Project：

在这个例子中，员工和技能之间存在多值依赖。为了满足第四范式，我们可以将表拆分为两个表：

ProjectEmployee表：

ProjectSkill表：

总结

范式的目的是通过减少冗余数据来提高数据库的设计质量。实际应用中，有时为了提高性能，可能会适当进行反范式化（如适当的冗余设计）。

数据库键的概念

1. 候选键（Candidate Key）

• 定义：候选键是表中能够唯一标识记录的一个或多个列的组合。每一个候选键都可以作为主键，因为它们都能够唯一确定表中的每一行。
• 特点
  • 一个表可以有多个候选键。
  • 每个候选键都不允许包含重复的值。
  • 不允许包含 NULL 值。
• 例子：在一个“员工”表中，employee_id 和 social_security_number（社会保障号码）都可以唯一标识每一名员工，因此它们都是候选键。

2. 主键（Primary Key）

• 定义：主键是从候选键中选出的用来唯一标识表中每一行的键。一个表只能有一个主键。
• 特点
  • 主键列中的值必须是唯一的，且不能为 NULL。
  • 主键通常是一个列，但也可以由多个列组合而成，这种情况下称为复合主键。
  • 在数据库中，主键会自动创建索引，用于加快查询速度。
• 例子：在“员工”表中，employee_id 可以作为主键，因为它唯一标识每一个员工。

1
2
3
4
5

sql复制代码CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    social_security_number CHAR(11)
);

3. 候选键与主键的区别

• 候选键是潜在的主键，一个表可以有多个候选键，但只能选择其中一个作为主键。
• 主键是从候选键中选出的，用来唯一标识表中的记录。

4. 超键（Super Key）

• 定义：超键是指能够唯一标识表中记录的一个或多个列的集合。所有的候选键和主键都是超键。
• 特点
  • 超键可以包含冗余的列，只要这个组合能够唯一标识记录。
• 例子：在“员工”表中，(employee_id, name) 也是一个超键，但它不是候选键，因为它包含了冗余的 name 列。

5. 替代键（Alternate Key）

• 定义：替代键是指那些没有被选为主键的候选键。
• 特点
  • 替代键可以用于唯一标识表中的记录，但它不是主键。
• 例子：在“员工”表中，如果 employee_id 是主键，那么 social_security_number 就是替代键。

6. 外键（Foreign Key）

• 定义：外键是表中的一列或多列，其值必须是另一张表中主键或候选键的值。外键用于建立表之间的关系。
• 特点
  • 外键用于确保数据的一致性和完整性。
  • 外键列中的值可以重复，且可以为 NULL（除非设置了 NOT NULL 约束）。
• 例子：在一个“订单”表中，customer_id 可以是外键，引用“客户”表中的 customer_id，确保每个订单都关联到一个有效的客户。

1
2
3
4
5
6

sql复制代码CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

7. 复合键（Composite Key）

• 定义：复合键是由两个或多个列组成的主键，用于唯一标识表中的记录。
• 特点
  • 复合键中的每一列组合起来才能唯一标识记录。
• 例子：在一个“课程注册”表中，student_id 和 course_id 共同组成主键，以确保每个学生只能注册一次某一门课程。

1
2
3
4
5

CREATE TABLE course_registration (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

总结

• 候选键：所有可以唯一标识表中记录的键。
• 主键：从候选键中选出的用于唯一标识记录的键。
• 超键：可以包含冗余列的唯一标识键。
• 替代键：没有被选为主键的候选键。
• 外键：建立表与表之间关系的键。
• 复合键：由多个列组合成的主键。

约束

1. 主键约束（PRIMARY KEY）

• 作用：保证表中某列或组合列的值在每一行都是唯一的，并且不能为空值（NOT NULL）。一个表只能有一个主键。

• 示例

  1 2 3 4

  CREATE TABLE students ( student_id INT PRIMARY KEY, name VARCHAR(50) );

2. 外键约束（FOREIGN KEY）

• 作用：保证表中的一个列或一组列的值必须存在于另一张表的主键或候选键列中，用于维护表之间的引用完整性。

• 示例

  1 2 3 4 5

  CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );

  外键约束的作用

  1. 维持数据的引用完整性
  • 作用：外键约束保证了表与表之间的关系是有效的。当你在一个表（称为子表或从表）中插入或更新数据时，外键约束会检查这些数据是否在另一个表（称为父表或主表）中存在，从而维护表之间的引用完整性。
  • 示例：在订单表中，一个 customer_id 字段被定义为外键，引用了客户表中的 customer_id。这就保证了订单中每一个 customer_id 都必须在客户表中存在，否则不能插入或更新。
  2. 防止无效数据插入
  • 作用：外键约束可以防止在子表中插入没有对应父表记录的无效数据。例如，如果你尝试在订单表中插入一个不存在的 customer_id，数据库将拒绝这个插入操作。
  • 示例：如果客户表中没有 customer_id = 123 的记录，那么订单表中不能插入 customer_id = 123 的订单。
  3. 级联操作（CASCADE）

  • 作用：外键约束可以指定当父表中的记录发生变化（如删除或更新）时，子表中相关联的记录应该如何处理。MySQL 支持以下级联操作：

    行为	说明
    CASCADE	级联操作：当父表中的记录被删除或更新时，子表中所有引用这些记录的行会自动被删除或更新。
    SET NULL	设置为空：当父表中的记录被删除或更新时，子表中所有引用这些记录的外键字段将被设置为 NULL。要求子表中的外键字段允许 NULL 值。
    RESTRICT	限制操作：如果子表中存在引用了父表中记录的行，则禁止删除或更新父表中的记录。这是最严格的行为，任何与之关联的子表记录存在时，父表记录都不能被修改或删除。
    NO ACTION	无操作：与 RESTRICT 类似，阻止删除或更新父表记录，但在事务提交前不会立即检查外键约束，通常用于支持更复杂的约束逻辑。
    SET DEFAULT	设置默认值：当父表中的记录被删除或更新时，子表中所有引用这些记录的外键字段将被设置为一个预定义的默认值。MySQL 不直接支持此行为。
    NO REFERENCE	无引用：没有直接指定的行为（与 NO ACTION 类似，通常使用 RESTRICT 行为）。

  • 示例：在删除客户表中的一条记录时，如果定义了 ON DELETE CASCADE，则会自动删除订单表中所有与该客户关联的记录。

  1 2 3 4 5 6

  CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE CASCADE );

  4. 防止孤儿记录
  • 作用：通过外键约束，MySQL 可以防止子表中留下没有父表记录关联的孤儿记录。例如，如果父表中的某条记录被删除，但该记录在子表中仍有引用，外键约束会阻止这条父表记录的删除，除非设置了适当的级联操作。
  5. 增强数据一致性
  • 作用：外键约束确保了数据的一致性，避免了手动管理表之间关系的复杂性。例如，自动处理删除和更新操作，防止不一致的数据状态。

3. 唯一约束（UNIQUE）

• 作用：保证表中某列或组合列的值在每一行中是唯一的，但与主键不同的是，表中可以有多个唯一约束，并且唯一约束的列可以为空值。

• 示例

  1 2 3 4

  CREATE TABLE users ( user_id INT PRIMARY KEY, email VARCHAR(100) UNIQUE );

4. 非空约束（NOT NULL）

• 作用：保证某列不能包含 NULL 值，必须插入有效的数据。

• 示例

  1 2 3 4

  CREATE TABLE employees ( employee_id INT PRIMARY KEY, name VARCHAR(50) NOT NULL );

5. 检查约束（CHECK）

• 作用：保证表中的数据符合某个特定的条件或范围。MySQL 从 8.0.16 版本开始支持 CHECK 约束。

• 示例

  1 2 3 4

  CREATE TABLE products ( product_id INT PRIMARY KEY, price DECIMAL(10, 2) CHECK (price > 0) );

6. 默认值约束（DEFAULT）

• 作用：在没有明确提供值时，为列设置默认值。

• 示例

  1 2 3 4

  CREATE TABLE orders ( order_id INT PRIMARY KEY, order_date DATE DEFAULT CURRENT_DATE );

总结

• 主键约束（PRIMARY KEY）：确保唯一性和非空性。
• 外键约束（FOREIGN KEY）：维持表之间的引用完整性。
• 唯一约束（UNIQUE）：确保唯一性，但允许空值。
• 非空约束（NOT NULL）：防止空值插入。
• 检查约束（CHECK）：确保数据满足特定条件。
• 默认值约束（DEFAULT）：为列设置默认值。

多表

多表关系

多表关系指的是数据库中不同表之间的关联，通常反映了现实世界中实体之间的关系。常见的多表关系有三种：

1. 一对一关系（One-to-One）

   • 定义：表 A 中的每一行数据与表 B 中的每一行数据之间存在唯一的对应关系。通常通过在一个表中创建外键，引用另一个表的主键来实现。
   • 示例：用户表（users）和用户详细信息表（user_details），每个用户在用户表中有一条记录，而用户详细信息表中对应一个用户详细信息。
   • 实现：在 user_details 表中，使用 user_id 作为外键指向 users 表的 id。

   1 2 3 4 5 6 7 8 9 10 11

   CREATE TABLE users ( id INT PRIMARY KEY, username VARCHAR(50) NOT NULL ); CREATE TABLE user_details ( id INT PRIMARY KEY, user_id INT UNIQUE, address VARCHAR(255), FOREIGN KEY (user_id) REFERENCES users(id) );

2. 一对多关系（One-to-Many）

   • 定义：表 A 中的一行可以对应表 B 中的多行数据，而表 B 中的每一行数据只能与表 A 中的一行数据相关联。常见于主表与从表之间的关系。
   • 示例：部门表（departments）和员工表（employees），一个部门可以有多个员工，而每个员工只属于一个部门。
   • 实现：在 employees 表中，使用 department_id 作为外键指向 departments 表的 id。

   1 2 3 4 5 6 7 8 9 10 11

   CREATE TABLE departments ( id INT PRIMARY KEY, department_name VARCHAR(50) NOT NULL ); CREATE TABLE employees ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, department_id INT, FOREIGN KEY (department_id) REFERENCES departments(id) );

3. 多对多关系（Many-to-Many）

   • 定义：表 A 中的每一行可以与表 B 中的多行数据相关联，同时表 B 中的每一行也可以与表 A 中的多行数据相关联。这种关系需要通过一个中间表（关联表）来实现。
   • 示例：学生表（students）和课程表（courses），一个学生可以选修多门课程，而一门课程可以被多个学生选修。
   • 实现：使用一个中间表 student_courses，其中包含 student_id 和 course_id，分别引用 students 表和 courses 表。

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

   CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL ); CREATE TABLE courses ( id INT PRIMARY KEY, course_name VARCHAR(50) NOT NULL ); CREATE TABLE student_courses ( student_id INT, course_id INT, PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES students(id), FOREIGN KEY (course_id) REFERENCES courses(id) );

多表查询

1. INNER JOIN（内连接）

   • 定义：返回两个表中匹配的行。只有两个表中都存在匹配关系的行才会被返回。
   • 示例：查询所有员工及其所在的部门。

   1 2 3

   SELECT employees.name, departments.department_name FROM employees INNER JOIN departments ON employees.department_id = departments.id;

2. LEFT JOIN（左连接）

   • 定义：返回左表的所有行，即使右表中没有匹配的行，未匹配的右表列会显示为 NULL。
   • 示例：查询所有部门及其员工（包括没有员工的部门）。

   1 2 3

   SELECT departments.department_name, employees.name FROM departments LEFT JOIN employees ON departments.id = employees.department_id;

3. RIGHT JOIN（右连接）

   • 定义：返回右表的所有行，即使左表中没有匹配的行，未匹配的左表列会显示为 NULL。
   • 示例：查询所有员工及其所属的部门（包括没有分配部门的员工）。

   1 2 3

   SELECT employees.name, departments.department_name FROM employees RIGHT JOIN departments ON employees.department_id = departments.id;

4. FULL OUTER JOIN（全连接）

   • 定义：返回两个表中所有匹配和不匹配的行。MySQL 不直接支持 FULL OUTER JOIN，可以通过 UNION 实现。
   • 示例：查询所有部门及其员工（包括没有员工的部门和没有分配部门的员工）。

   1 2 3 4 5 6 7

   SELECT employees.name, departments.department_name FROM employees LEFT JOIN departments ON employees.department_id = departments.id UNION SELECT employees.name, departments.department_name FROM employees RIGHT JOIN departments ON employees.department_id = departments.id;

5. CROSS JOIN（交叉连接）

   • 定义：返回两个表的笛卡尔积，即将每一行左表的每一行与右表的每一行进行组合。
   • 示例：获取所有员工和所有项目的组合（不考虑关联关系）。

   1 2 3

   SELECT employees.name, projects.project_name FROM employees CROSS JOIN projects;

6. SELF JOIN（自连接）

   • 定义：一个表与自身进行连接，通常用于处理表内关系，例如层次结构数据。
   • 示例：查询员工及其经理（假设经理也是员工表中的一员）。

   1 2 3

   SELECT e1.name AS Employee, e2.name AS Manager FROM employees e1 INNER JOIN employees e2 ON e1.manager_id = e2.id;

笛卡尔积的概念

1. 定义：

   • 笛卡尔积是两个集合之间的操作。对于两个表 A 和 B，笛卡尔积会将表 A 中的每一行与表 B 中的每一行组合，生成一个新的结果集。结果集的行数等于表 A 的行数乘以表 B 的行数。
   • 如果表 A 有 m 行，表 B 有 n 行，则笛卡尔积的结果集将包含 m * n 行。

2. 笛卡尔积在 SQL 中的体现：

   • 在 SQL 查询中，笛卡尔积通常由 CROSS JOIN 操作生成，但如果使用 JOIN 语句时没有指定 ON 条件或 WHERE 条件，也会产生笛卡尔积。
   • CROSS JOIN 是最直接生成笛卡尔积的方法。

   1 2 3

   SELECT * FROM table1 CROSS JOIN table2;

   或者：

   1 2	sql复制代码SELECT * FROM table1, table2;

3. 笛卡尔积的使用场景：

   • 笛卡尔积通常不常用于实际的业务查询中，因为它生成的结果集往往非常大，且包含大量不必要的组合。不过，它在某些情况下是有用的，例如在需要生成所有可能的组合或者配对时。

示例

假设有两个表，students 和 courses：

• students 表：

• courses 表：

如果对这两个表进行笛卡尔积操作：

1
2
3

sql复制代码SELECT *
FROM students
CROSS JOIN courses;

结果集将包含每个学生和每门课程的所有可能组合：

在这个例子中，students 表有 2 行，courses 表也有 2 行，所以笛卡尔积的结果集包含 2 * 2 = 4 行。

重要提示

• 性能问题：笛卡尔积的结果集可能会非常大，尤其是在表行数较多时。因此，在没有明确需求的情况下，应避免生成笛卡尔积。
• 过滤条件：通常，我们会通过 JOIN 语句结合 ON 或 WHERE 子句来避免笛卡尔积，只获取相关的匹配数据。

Union联合查询

UNION 是 SQL 中的一种用于合并两个或多个 SELECT 查询结果的操作符。它将多个查询的结果组合成一个结果集，且默认去除重复的行。UNION 操作可以用于合并来自不同表或相同表中的查询结果。

UNION 的使用

基本语法

1
2
3
4
5
6
7
8
9

sql复制代码SELECT column1, column2, ...
FROM table1
WHERE condition

UNION

SELECT column1, column2, ...
FROM table2
WHERE condition;

• 每个 SELECT 查询必须具有相同数量的列，并且列的数据类型必须兼容。
• 列的顺序在每个查询中必须相同。
• 默认情况下，UNION 会去除重复的行。如果要保留重复的行，可以使用 UNION ALL。

UNION 和 UNION ALL

• UNION：合并多个查询的结果，并去除结果集中重复的行。
• UNION ALL：合并多个查询的结果，并保留所有重复的行。

1
2
3
4
5
6
7
8
9

sql复制代码-- 使用 UNION
SELECT name FROM employees WHERE department = 'HR'
UNION
SELECT name FROM managers WHERE department = 'HR';

-- 使用 UNION ALL
SELECT name FROM employees WHERE department = 'HR'
UNION ALL
SELECT name FROM managers WHERE department = 'HR';

UNION 示例

假设有两个表，students 和 teachers，都包含 name 和 age 两列：

• students 表：

• teachers 表：

使用 UNION 将这两个表的 name 和 age 合并：

1
2
3

sql复制代码SELECT name, age FROM students
UNION
SELECT name, age FROM teachers;

结果：

如果使用 UNION ALL，则会保留重复的行（如果有）：

1
2
3

sql复制代码SELECT name, age FROM students
UNION ALL
SELECT name, age FROM teachers;

总结

• UNION 用于合并两个或多个查询结果，并默认去除重复行。
• UNION ALL 用于合并两个或多个查询结果，并保留所有重复行。
• 适用于合并来自不同表或相同表的查询结果，需要保证每个查询的列数相同且数据类型兼容。

子查询（Subquery）

子查询 是嵌套在另一个 SQL 查询中的查询。子查询可以在 SELECT、FROM、WHERE 和 HAVING 子句中使用，返回单个值或一组值，供外部查询使用。

常见的子查询类型

1. 标量子查询：

   • 返回单个值（即一行一列），通常用于 WHERE 或 HAVING 子句中。

   1 2 3

   SELECT name, salary FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

2. 行子查询：

   • 返回一行多列，可以与主查询中的多列比较。

   1 2 3 4 5 6 7

   SELECT name, department_id, salary FROM employees WHERE (department_id, salary) IN ( SELECT department_id, MAX(salary) FROM employees GROUP BY department_id );

3. 表子查询：

   • 返回多行多列，通常用在 FROM 子句中，作为一个临时表。

   1 2 3 4 5 6 7

   SELECT e.name, d.avg_salary FROM employees e INNER JOIN ( SELECT department_id, AVG(salary) AS avg_salary FROM employees GROUP BY department_id ) d ON e.department_id = d.department_id;

4. 相关子查询（Correlated Subquery）：

   • 子查询依赖于外部查询中的值，因此每次执行外部查询的每一行时，子查询都会重新执行。

   1 2 3 4 5 6 7

   SELECT name, salary FROM employees e1 WHERE salary > ( SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id );

联合查询 vs 子查询

• 联合查询（JOIN）：
  • 更适合获取来自多个表的关联数据，尤其是在表之间有明确的外键关系时。它的执行效率通常比子查询高，因为数据库优化器可以更好地优化 JOIN 操作。
• 子查询（Subquery）：
  • 适合在查询中需要动态条件时使用，尤其是当主查询的条件依赖于子查询的结果时。子查询可以使查询结构更清晰，但在某些情况下可能会导致性能问题，特别是相关子查询。

总结

• 联合查询：适合表间有明确关系的情况，通常性能更好。
• 子查询：适合需要动态条件或更复杂查询逻辑的情况，代码可读性较高。

事务

MySQL 事务（Transaction）是一组由一个或多个 SQL 语句组成的操作单元，这些操作要么全部成功，要么全部失败，事务可以确保数据的一致性和完整性。事务在数据库中尤为重要，尤其是在涉及到多个操作的复杂系统中。

事务的基本操作

MySQL 中事务的基本操作包括：

1. 开始事务 (START TRANSACTION)：

   • 显式地开始一个事务。

   1	START TRANSACTION;

2. 提交事务 (COMMIT)：

   • 将事务中的所有操作永久保存到数据库中。

   1

   COMMIT;

3. 回滚事务 (ROLLBACK)：

   • 撤销事务中的所有操作，恢复到事务开始前的状态。

   1

   ROLLBACK;

4. 保存点 (SAVEPOINT)：

   • 设置一个事务中的保存点，可以在回滚时回到这个保存点。

   1	SAVEPOINT savepoint_name;

5. 回滚到保存点 (ROLLBACK TO SAVEPOINT)：

   • 将事务回滚到指定的保存点，而不回滚整个事务。

   1	ROLLBACK TO SAVEPOINT savepoint_name;

6. 设置自动提交模式 (SET AUTOCOMMIT)：

   • MySQL 默认每个语句都会自动提交。如果要在一个事务中执行多个语句，可以关闭自动提交模式。

   1 2	SET AUTOCOMMIT = 0; -- 关闭自动提交 SET AUTOCOMMIT = 1; -- 开启自动提交

事务的四大特性 (ACID)

事务必须满足以下四个特性，简称 ACID：

1. 原子性（Atomicity）：
   • 事务是一个不可分割的操作单元，其中的操作要么全部成功，要么全部失败。如果事务中的某个操作失败，整个事务将回滚到开始前的状态。
2. 一致性（Consistency）：
   • 事务的执行必须使数据库从一个一致状态转换到另一个一致状态。在事务开始和结束时，数据库都必须处于一致的状态。
3. 隔离性（Isolation）：
   • 一个事务的执行不应受到其他事务的干扰，各并发事务之间应该是隔离的，即一个事务不应该看到其他事务的中间状态。
   • MySQL 提供多种隔离级别（下文详细介绍）。
4. 持久性（Durability）：
   • 事务一旦提交，其结果应当永久保存到数据库中，即使系统崩溃，事务的结果也不会丢失。

事务隔离级别

事务的隔离性保证了多个事务并发执行时的数据安全性，但不同的应用场景可能对隔离性要求不同，因此数据库提供了不同的隔离级别。MySQL 支持以下四种隔离级别：

1. 读未提交（Read Uncommitted）：

   • 允许一个事务读取另一个事务尚未提交的数据。这种级别提供最低的隔离性，可能会导致脏读问题。

   1	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

2. 读已提交（Read Committed）：

   • 只允许一个事务读取另一个事务已提交的数据，这种级别防止了脏读，但可能会出现不可重复读问题。

   1	SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

3. 可重复读（Repeatable Read）：

   • 确保在同一个事务中多次读取同样的数据结果是一致的，避免了不可重复读问题，但可能会出现幻读问题。MySQL 默认的隔离级别。

   1	SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

4. 串行化（Serializable）：

   • 提供最高的隔离级别，通过强制事务顺序执行来避免所有并发问题，包括脏读、不可重复读和幻读。性能较低，一般只在需要完全隔离的情况下使用。

   1	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

各隔离级别

事务的使用场景

事务通常用于需要确保多个操作要么全部成功要么全部失败的场景，如：

• 银行转账：确保从一个账户扣钱的同时，另一个账户能够成功加钱，否则交易回滚。
• 订单处理：确保在多个表中插入订单数据时，要么所有插入成功，要么所有回滚。
• 库存管理：确保减少库存和增加销售记录同时成功，否则回滚。