01-MySQL 基础（SQL 与约束）
对应原始资料：
数据库_day01~02
一、数据库与 MySQL 概述
1. 基本概念
- 数据库（DB）：存储数据的仓库。
- 数据库管理系统（DBMS）：管理数据库的软件，如 MySQL、Oracle、SQL Server。
- SQL：操作关系型数据库的统一语言。
2. MySQL 安装与连接
- 服务：
mysqld；客户端：mysql -u root -p。 - 可视化工具：DataGrip / Navicat / SQLyog。
3. 数据模型
关系型数据库 = 多张二维表 + 表之间关系。 每个数据库对应一个文件夹，每张表对应一个 .frm（结构）+ .ibd（数据+索引）。
二、SQL 分类
| 分类 | 全称 | 说明 |
|---|---|---|
| DDL | Data Definition | 定义库、表、字段 |
| DML | Data Manipulation | 增删改数据 |
| DQL | Data Query | 查询数据 |
| DCL | Data Control | 用户权限 |
SQL 通用语法
- 语句以
;结尾，不区分大小写（关键字建议大写）。 -- 单行注释，/* 多行 */。- 字符串用单引号
'abc'。
三、DDL —— 数据定义
1. 数据库操作
sql
CREATE DATABASE IF NOT EXISTS db1 DEFAULT CHARSET utf8mb4;
SHOW DATABASES;
USE db1;
DROP DATABASE db1;2. 数据类型
| 类型 | 说明 |
|---|---|
INT / BIGINT | 整数 |
DECIMAL(10,2) | 定点小数（金额） |
CHAR(10) | 定长字符串 |
VARCHAR(255) | 变长字符串（最常用） |
TEXT | 长文本 |
DATE / TIME / DATETIME | 日期时间 |
TIMESTAMP | 时间戳 |
char vs varchar：char 固定长度（不足补空格），varchar 按实际长度 + 1 字节存。
3. 表操作
sql
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
age INT,
score DOUBLE(5,2)
);
DESC student; -- 查看表结构
SHOW TABLES;
ALTER TABLE student ADD COLUMN gender CHAR(1);
ALTER TABLE student MODIFY name VARCHAR(30);
ALTER TABLE student CHANGE name stu_name VARCHAR(30);
ALTER TABLE student DROP COLUMN gender;
DROP TABLE student;四、DML —— 增删改
sql
INSERT INTO student(name, age) VALUES ('张三', 18), ('李四', 20);
UPDATE student SET age = 19 WHERE name = '张三';
DELETE FROM student WHERE id = 1; -- 按条件删，可回滚
TRUNCATE TABLE student; -- 清空表，自增重置，不可回滚，更快五、DQL —— 查询（重点）
1. 完整语法顺序
sql
SELECT 字段列表
FROM 表名
[WHERE 条件]
[GROUP BY 分组字段 HAVING 分组后条件]
[ORDER BY 排序字段 ASC|DESC]
[LIMIT 起始索引, 每页条数]2. 基础查询
sql
SELECT name, age FROM student;
SELECT DISTINCT gender FROM student; -- 去重
SELECT name AS 姓名, age * 2 FROM student; -- 别名3. 条件查询
sql
WHERE age >= 18 AND age <= 25
WHERE age BETWEEN 18 AND 25
WHERE gender IN ('男', '女')
WHERE name LIKE '张%' -- 张开头
WHERE name LIKE '_三' -- 第二个字是三
WHERE age IS NULL4. 排序
sql
ORDER BY age DESC, name ASC; -- 多字段5. 聚合函数
sql
COUNT(*) / COUNT(字段) -- 个数（字段会忽略 null）
MAX / MIN / SUM / AVG6. 分组
sql
SELECT gender, COUNT(*), AVG(score)
FROM student
GROUP BY gender
HAVING COUNT(*) > 2; -- 分组后再过滤WHERE 在分组前过滤行，HAVING 在分组后过滤组。
测试数据（student 表）：
| id | gender | score |
|---|---|---|
| 1 | 男 | 80 |
| 2 | 男 | 90 |
| 3 | 男 | 70 |
| 4 | 女 | 85 |
| 5 | 女 | 95 |
| 6 | 保密 | 60 |
执行 SELECT gender, COUNT(*), AVG(score) FROM student GROUP BY gender HAVING COUNT(*) > 2;：
| gender | COUNT(*) | AVG(score) |
|---|---|---|
| 男 | 3 | 80.0000 |
男生 3 条满足 COUNT(*) > 2 保留；女生 2 条、保密 1 条被 HAVING 过滤。
WHERE + GROUP BY + HAVING 完整例子：先剔除不及格的学生（原始行过滤），再分组，再过滤人数太少的分组：
sql
SELECT gender, COUNT(*), AVG(score)
FROM student
WHERE score >= 60 -- 分组前：过滤原始行
GROUP BY gender
HAVING COUNT(*) > 2 -- 分组后：过滤分组
;7. 分页
sql
LIMIT 0, 10; -- 第 1 页，每页 10
LIMIT 10, 10; -- 第 2 页：起始索引 = (页码-1) * 条数六、约束
保证表中数据的正确性、有效性、完整性。
| 约束 | 关键字 | 作用 |
|---|---|---|
| 非空 | NOT NULL | 不能为 null |
| 唯一 | UNIQUE | 值不能重复 |
| 主键 | PRIMARY KEY | 非空且唯一 |
| 自增 | AUTO_INCREMENT | 自动 +1 |
| 默认 | DEFAULT | 默认值 |
| 检查（8.0+） | CHECK | 自定义条件 |
| 外键 | FOREIGN KEY | 关联其他表 |
外键
sql
CREATE TABLE score (
id INT PRIMARY KEY AUTO_INCREMENT,
stu_id INT,
score DOUBLE,
CONSTRAINT fk_stu FOREIGN KEY (stu_id) REFERENCES student(id)
);外键影响性能，互联网公司通常不在数据库层面加外键，而是在代码层保证。
七、多表设计
1. 表关系
- 一对多：在"多"方加外键（如部门-员工）。
- 多对多：建中间表（如学生-课程）。
- 一对一：外键 + 唯一约束（如用户-详情）。
2. 数据库设计范式
- 1NF：每个字段不可再分。
- 2NF：在 1NF 基础上，非主键完全依赖主键（消除部分依赖）。
- 3NF：在 2NF 基础上，非主键直接依赖主键（消除传递依赖）。
实际开发为了性能，有时会适度反范式（冗余字段）。
八、多表查询（重点）
1. 连接查询
sql
-- 内连接（交集）
SELECT * FROM emp e INNER JOIN dept d ON e.dept_id = d.id;
SELECT * FROM emp e, dept d WHERE e.dept_id = d.id; -- 隐式
-- 左外连接：左表全 + 交集
SELECT * FROM emp e LEFT JOIN dept d ON e.dept_id = d.id;
-- 右外连接：右表全 + 交集
SELECT * FROM emp e RIGHT JOIN dept d ON e.dept_id = d.id;一句话总结：
- INNER JOIN：只取两边匹配上的（交集），显式/隐式写法等价（隐式可读性差，不推荐）
- LEFT JOIN：左表全部保留，匹配不上的右表字段补
NULL - RIGHT JOIN：右表全部保留，匹配不上的左表字段补
NULL
测试数据：
emp 员工表
| id | name | dept_id | 备注 |
|---|---|---|---|
| 1 | 张三 | 10 | |
| 2 | 李四 | 20 | |
| 3 | 王五 | 30 | 部门不存在 |
| 4 | 赵六 | 99 | 部门不存在 |
dept 部门表
| id | dept_name | 备注 |
|---|---|---|
| 10 | 研发部 | |
| 20 | 产品部 | |
| 50 | 财务部 | 没有员工 |
三种连接的结果对比：
INNER JOIN —— 只有张三、李四（王五、赵六、财务部都消失）：
| id | name | dept_id | id | dept_name |
|---|---|---|---|---|
| 1 | 张三 | 10 | 10 | 研发部 |
| 2 | 李四 | 20 | 20 | 产品部 |
LEFT JOIN —— emp 全保留，王五（无 30）、赵六（无 99）的 dept 字段补 NULL：
| id | name | dept_id | id | dept_name |
|---|---|---|---|---|
| 1 | 张三 | 10 | 10 | 研发部 |
| 2 | 李四 | 20 | 20 | 产品部 |
| 3 | 王五 | 30 | NULL | NULL |
| 4 | 赵六 | 99 | NULL | NULL |
RIGHT JOIN —— dept 全保留，财务部（无员工）的 emp 字段补 NULL：
| id | name | dept_id | id | dept_name |
|---|---|---|---|---|
| 1 | 张三 | 10 | 10 | 研发部 |
| 2 | 李四 | 20 | 20 | 产品部 |
| NULL | NULL | NULL | 50 | 财务部 |
坑点（面试常问）：
sql
-- 1. LEFT JOIN 条件写 ON 和 WHERE 不一样！
-- ON 后面：只影响匹配，不过滤左表数据
SELECT * FROM emp e LEFT JOIN dept d ON e.dept_id = d.id AND d.dept_name = '研发部';
-- WHERE 后面：连接完成后再过滤，LEFT JOIN 降级成内连接
SELECT * FROM emp e LEFT JOIN dept d ON e.dept_id = d.id WHERE d.dept_name = '研发部';
-- 2. MySQL 没有 FULL OUTER JOIN，用 UNION 拼 left + right
SELECT * FROM emp e LEFT JOIN dept d ON e.dept_id = d.id
UNION
SELECT * FROM emp e RIGHT JOIN dept d ON e.dept_id = d.id;
-- 3. 禁止不加连接条件：笛卡尔积，行数相乘爆炸
SELECT * FROM emp, dept;2. 子查询
sql
-- 标量子查询（一行一列）
SELECT * FROM emp WHERE salary > (SELECT AVG(salary) FROM emp);
-- 列子查询（一列多行）
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE name = '销售部');
-- 行子查询（一行多列）
SELECT * FROM emp WHERE (salary, mgr) = (SELECT MAX(salary), MIN(mgr) FROM emp);
-- 表子查询（作为临时表）
SELECT * FROM (SELECT * FROM emp ORDER BY salary DESC LIMIT 10) t;3. 经典案例：找各部门工资最高的人
sql
SELECT e.* FROM emp e
INNER JOIN (
SELECT dept_id, MAX(salary) ms FROM emp GROUP BY dept_id
) t ON e.dept_id = t.dept_id AND e.salary = t.ms;九、事务
把一组操作当作一个整体，要么都成功，要么都失败。
1. 基本操作
sql
START TRANSACTION;
-- 或 BEGIN;
UPDATE account SET money = money - 100 WHERE name = 'A';
UPDATE account SET money = money + 100 WHERE name = 'B';
COMMIT; -- 提交
-- ROLLBACK; -- 回滚2. 四大特性（ACID）
- A 原子性：不可分割。
- C 一致性：从一个一致状态到另一个。
- I 隔离性：多事务互不干扰。
- D 持久性：提交后永久保存。
3. 隔离级别与并发问题
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| READ UNCOMMITTED 读未提交 | √ | √ | √ |
| READ COMMITTED 读已提交（Oracle 默认） | × | √ | √ |
| REPEATABLE READ 可重复读（MySQL 默认） | × | × | √ |
| SERIALIZABLE 串行化 | × | × | × |
MySQL 的 RR 级别用 MVCC + 间隙锁解决了幻读。
sql
SELECT @@transaction_isolation; -- 查看隔离级别
SET GLOBAL transaction_isolation = 'READ-COMMITTED';练习建议
- 设计「学生-课程-成绩」三张表，建立外键关系。
- 完成资料中的 SQL 练习题（共 4 套）。
- 模拟转账事务，故意制造一个错误观察回滚。
- 用左连接查询"没有员工的部门"。