MySQL基础内容

一、SQL语言分类

SQL (Structured Query Language) 主要分为四类：

1. DDL (Data Definition Language)

• 全称：数据定义语言。
• 作用：定义数据库对象（数据库、表、字段）。即操作表结构。
• 关键字：CREATE, DROP, ALTER, TRUNCATE。
• 注意：TRUNCATE（清空表）属于 DDL，因为它涉及重新创建表结构，且无法回滚。

2. DML (Data Manipulation Language)

• 全称：数据操作语言。
• 作用：对数据库中表的数据进行增、删、改。
• 关键字：INSERT, UPDATE, DELETE。
• 注意：DML 操作支持事务（Transaction），可以手动提交或回滚。

3. DQL (Data Query Language)

• 全称：数据查询语言。
• 作用：查询数据库中表的记录。
• 关键字：SELECT。
• 地位：数据库中最核心、使用频率最高的功能。

4. DCL (Data Control Language)

• 全称：数据控制语言。
• 作用：定义数据库的访问权限和安全级别，创建用户。
• 关键字：GRANT, REVOKE。

二、DQL 核心解析

1. 编写顺序 vs 执行顺序

MySQL 解析 SQL 语句的顺序与编写顺序并不一致，理解执行顺序有助于优化 SQL 及排查错误（如别名无效问题）。

A. 编写顺序 (Writing Order)

SELECT
    字段列表
FROM
    表名列表
WHERE
    条件列表
GROUP BY
    分组字段列表
HAVING
    分组后条件列表
ORDER BY
    排序字段列表
LIMIT
    分页参数

B. 执行顺序 (Execution Order)

1. FROM：指定查询的数据表。
2. WHERE：对每一行数据进行条件过滤。
3. GROUP BY：将过滤后的数据进行分组。
4. HAVING：对分组后的结果进行条件过滤。
5. SELECT：选择需要显示的字段（此时字段别名才产生）。
6. ORDER BY：对结果集进行排序（可以使用别名）。
7. LIMIT：进行分页处理。

2. WHERE 与 HAVING 的区别

• 执行时机：
  • WHERE 在分组之前执行。
  • HAVING 在分组之后执行。
• 聚合函数：
  • WHERE 不允许使用聚合函数（如 COUNT, SUM, AVG）。
  • HAVING 允许使用聚合函数。
• 逻辑关系：
  • WHERE 过滤掉的数据不会参与分组。

三、函数 (Functions)

MySQL 提供了大量内置函数，主要分为四类。此处仅列举每类中最典型的用法。

1. 字符串函数

• CONCAT(s1, s2, ...)：字符串拼接。
  • 例：SELECT CONCAT('My', 'SQL'); -> 'MySQL'
• SUBSTRING(str, start, len)：截取字符串。
  • 例：SELECT SUBSTRING('Hello', 1, 2); -> 'He' (注意：索引从1开始)

2. 数值函数

• ROUND(x, n)：四舍五入，保留 n 位小数。
  • 例：SELECT ROUND(3.14159, 2); -> 3.14
• RAND()：返回 0~1 之间的随机数。

3. 日期函数

• NOW()：返回当前系统日期和时间。
  • 例：SELECT NOW(); -> 2023-10-01 12:00:00
• DATEDIFF(date1, date2)：返回两个日期之间的天数差 (date1 - date2)。

4. 流程控制函数 (重点，复杂 SQL 常用)

• IFNULL(value, default)：如果 value 为 null，则返回 default，否则返回 value。
• CASE WHEN：

  CASE WHEN [条件1] THEN [结果1]
       WHEN [条件2] THEN [结果2]
       ELSE [默认结果]
  END
  

四、约束 (Constraints)

约束是作用于表中字段上的规则，用于限制存储在表中的数据。 目的：保证数据库中数据的正确性、有效性和完整性。

1. 常用约束分类

2. 外键约束 (Foreign Key) 详解

外键用来关联两张表：主表 (父表) 和 从表 (子表)。

• 添加外键语法：

  ALTER TABLE 表名 ADD CONSTRAINT 外键名称 
  FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ;
  

• 删除外键语法：

  ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
  

3. 外键删除/更新行为 (重要)

当主表中的数据被删除或更新时，从表对应的数据该如何反应？需在创建外键时指定 ON UPDATE 或 ON DELETE 行为。

• 语法示例 (级联更新与删除)：

  ALTER TABLE 表名 ADD CONSTRAINT 外键名称 
  FOREIGN KEY (外键字段) REFERENCES 主表 (主键) 
  ON UPDATE CASCADE ON DELETE CASCADE;
  

五、多表查询 (Multi-Table Query)

1. 概述与笛卡尔积

• 笛卡尔积：两个集合 A 和 B 的所有组合情况（行数 = A的行数 × B的行数）。
• 核心原则：多表查询必须包含连接条件，从而消除无效的笛卡尔积。

2. 连接查询分类

3. 内连接 (Inner Join)

只返回两张表中连接字段匹配的行。

• 隐式内连接：使用 WHERE 指定连接条件。

  SELECT 字段列表 FROM 表1, 表2 WHERE 表1.字段 = 表2.字段;
  

• 显式内连接：使用 INNER JOIN ... ON (推荐，语义更清晰)。

  SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件;
  

4. 外连接 (Outer Join)

• 左外连接 (Left Outer Join) —— 最常用
  • 语法：SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
  • 结果：查询表1 (左表) 的所有数据，以及表2 中匹配的数据。如果表2 中没匹配到，显示为 NULL。
• 右外连接 (Right Outer Join)
  • 语法：SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
  • 结果：查询表2 (右表) 的所有数据，以及表1 中匹配的数据。
  • 注：右连接通常可以转换为左连接，开发中习惯统一步调使用左连接。

5. 自连接 (Self Join)

一张表自己连接自己。通常用于处理层级关系（如：查询员工及其直属领导，员工和领导都在同一张员工表）。

• 语法：

  SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 别名A.字段 = 别名B.字段;
  

• 注意：自连接必须给表起别名，否则数据库无法区分引用的是哪一次的表。
• 场景区别：自连接可以是内连接（查不到顶级领导），也可以是外连接（能查到顶级领导，领导字段为NULL）。

6. 联合查询 (Union)

把多次查询的结果合并起来，形成一个新的结果集。

• 语法：

  SELECT 字段列表 FROM 表A ...
  UNION [ALL]
  SELECT 字段列表 FROM 表B ...;
  

• 条件：
  1. 多张表的列数必须保持一致。
  2. 多张表的字段类型需要保持一致。
• 区别：
  • UNION ALL：直接合并，保留重复记录（效率高）。
  • UNION：合并后去重（效率较低）。

7. 子查询 (Subquery)

SQL 语句中嵌套 SELECT 语句，称为嵌套查询或子查询。 外部语句可以是 INSERT / UPDATE / DELETE / SELECT。

根据子查询结果的不同，分为四类：

A. 标量子查询 (Scalar Subquery)

• 结果：单个值 (数字、字符串、日期)。
• 常用操作符：=, <>, >, >=, <, <=。
• 例：查询 "销售部" 的所有员工信息。

  SELECT * FROM emp WHERE dept_id = (SELECT id FROM dept WHERE name = '销售部');
  

B. 列子查询 (Column Subquery)

• 结果：一列 (多行)。
• 常用操作符：
  • IN：在指定的集合范围之内。
  • NOT IN：不在指定的集合范围之内。
  • ANY：子查询返回列表中，有任意一个满足即可。
  • ALL：子查询返回列表的所有值都必须满足。
• 例：查询 "销售部" 和 "市场部" 的所有员工信息。

  SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE name = '销售部' OR name = '市场部');
  

C. 行子查询 (Row Subquery)

• 结果：一行 (多列)。
• 常用操作符：=, <>, IN, NOT IN。
• 例：查询与 "张三" 的薪资和直属领导相同的员工信息。

  SELECT * FROM emp WHERE (salary, manager_id) = (SELECT salary, manager_id FROM emp WHERE name = '张三');
  

D. 表子查询 (Table Subquery)

• 结果：多行多列 (相当于一张临时表)。

• 常用操作符：IN。

• 用法：通常出现在 FROM 子句之后，作为临时表进行再次查询。

• 例：查询入职时间是 "2000-01-01" 之后的员工信息及其部门信息。

  SELECT * FROM (SELECT * FROM emp WHERE entrydate > '2000-01-01') AS t 
  LEFT JOIN dept d ON t.dept_id = d.id;
  

六、事务 (Transactions)

1. 简介

事务是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

• 典型场景：银行转账（张三给李四转 1000 元）。
  • 第一步：张三余额 -1000。
  • 第二步：李四余额 +1000。
  • 异常情况：如果第一步成功，第二步抛出异常，如果没有事务，钱就平白消失了。事务保证这两步要么都成，要么都回滚。

2. 四大特性 (ACID) - 面试必背

3. 并发事务问题

当多个事务同时操作同一份数据时，会引发以下问题：

• 脏读 (Dirty Read)：
  • 一个事务读到了另外一个事务还没有提交的数据。
• 不可重复读 (Non-Repeatable Read)：
  • 一个事务先后读取同一条记录，但两次读取的数据不一致。
  • 原因：在两次读取之间，另一个事务修改 (Update) 了该记录并提交。
• 幻读 (Phantom Read)：
  • 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”。
  • 原因：在查询之后，另一个事务插入 (Insert) 或 删除 (Delete) 了符合该条件的数据并提交。

4. 事务隔离级别 (Isolation Levels)

为了解决并发问题，数据库规定了 4 种隔离级别。级别越高，安全性越高，但性能越低。

面试避坑 (MySQL 特性)： 虽然标准 SQL 规范中 Repeatable Read 无法解决幻读，但 MySQL 的 InnoDB 引擎通过 MVCC (多版本并发控制) 和 Next-Key Lock (临键锁)，在很大程度上解决了幻读问题。

5. 事务操作语法

• 查看/设置事务提交方式：
  • SELECT @@autocommit; (1为自动提交，0为手动)。
  • SET @@autocommit = 0; (设置为手动提交)。
• 开启事务：
  • START TRANSACTION 或 BEGIN;
• 提交事务：
  • COMMIT; (业务逻辑正常执行完后)。
• 回滚事务：
  • ROLLBACK; (捕获到异常后)。