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. 数据库操作

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. 表操作

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 —— 增删改

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. 完整语法顺序

SELECT 字段列表
FROM 表名
[WHERE 条件]
[GROUP BY 分组字段 HAVING 分组后条件]
[ORDER BY 排序字段 ASC|DESC]
[LIMIT 起始索引, 每页条数]

2. 基础查询

SELECT name, age FROM student;
SELECT DISTINCT gender FROM student;           -- 去重
SELECT name AS 姓名, age * 2 FROM student;     -- 别名

3. 条件查询

WHERE age >= 18 AND age <= 25
WHERE age BETWEEN 18 AND 25
WHERE gender IN ('男', '女')
WHERE name LIKE '张%'        -- 张开头
WHERE name LIKE '_三'        -- 第二个字是三
WHERE age IS NULL

4. 排序

ORDER BY age DESC, name ASC;     -- 多字段

5. 聚合函数

COUNT(*) / COUNT(字段)   -- 个数（字段会忽略 null）
MAX / MIN / SUM / AVG

6. 分组

SELECT gender, COUNT(*), AVG(score)
FROM student
GROUP BY gender
HAVING COUNT(*) > 2;          -- 分组后再过滤

WHERE 在分组前过滤行，HAVING 在分组后过滤组。

7. 分页

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	关联其他表

外键

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. 连接查询

-- 内连接（交集）
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;

2. 子查询

-- 标量子查询（一行一列）
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. 经典案例：找各部门工资最高的人

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. 基本操作

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 + 间隙锁解决了幻读。

SELECT @@transaction_isolation;                    -- 查看隔离级别
SET GLOBAL transaction_isolation = 'READ-COMMITTED';

练习建议

1. 设计「学生-课程-成绩」三张表，建立外键关系。
2. 完成资料中的 SQL 练习题（共 4 套）。
3. 模拟转账事务，故意制造一个错误观察回滚。
4. 用左连接查询"没有员工的部门"。