数据库：别看我只是一张表，我装得下整个世界

第一部分：数据库核心概念 (文字笔记)

1. 什么是数据库？

• 定义：数据库是按照数据结构来组织、存储和管理数据的仓库，是一个长期存储在计算机内的、有组织的、可共享的数据集合。

• 核心作用：数据持久化存储、数据高效检索、数据完整性保证、多用户并发访问控制。

2. 关系型数据库 (SQL) vs. 非关系型数据库 (NoSQL)

特性	关系型数据库 (SQL)	非关系型数据库 (NoSQL)
数据模型	二维表格（行与列），强结构化。	键值对、文档、列族、图结构。
模式	固定模式（Schema），需要预先定义表结构。	动态模式（无Schema或弱Schema），适合灵活迭代。
事务	严格支持ACID（原子性、一致性、隔离性、持久性）。	大多支持CAP（一致性、可用性、分区容错性），弱事务。
扩展性	主要垂直扩展（提升单机硬件性能）。	主要水平扩展（增加更多廉价服务器）。
典型产品	MySQL, PostgreSQL, Oracle, SQL Server	MongoDB, Redis, Cassandra, Elasticsearch

3. SQL (结构化查询语言) 分类

• DDL (数据定义语言)：定义数据库结构。CREATE, ALTER, DROP

• DML (数据操作语言)：增删改数据。INSERT, UPDATE, DELETE

• DQL (数据查询语言)：查询数据。SELECT

• DCL (数据控制语言)：权限管理。GRANT, REVOKE

• TCL (事务控制语言)：事务管理。COMMIT, ROLLBACK

4. 数据库三大范式 (规范设计)

• 第一范式 (1NF)：所有列都是不可分割的原子数据。

• 第二范式 (2NF)：在1NF基础上，非主键列必须完全依赖于主键（不能依赖主键的一部分，针对联合主键）。

• 第三范式 (3NF)：在2NF基础上，非主键列不能传递依赖于主键（即非主键列之间不能有依赖关系）。

---

第二部分：SQL实战代码 (可执行示例)

以下代码基于 MySQL 语法，请在你的测试库中运行。

1. DDL - 数据库与表的操作

sql

2. -- 创建一个名为 school 的数据库（如果不存在）
3. CREATE DATABASE IF NOT EXISTS school DEFAULT CHARACTER SET utf8mb4;
5. -- 使用该数据库
6. USE school;
8. -- 创建学生表 (包含主键、约束、默认值)
9. CREATE TABLE IF NOT EXISTS students (
10. id INT AUTO_INCREMENT COMMENT '学生ID，自增主键',
11. name VARCHAR(50) NOT NULL COMMENT '姓名，不能为空',
12. gender CHAR(1) DEFAULT 'M' COMMENT '性别，默认M',
13. age INT CHECK (age >= 0 AND age <= 120) COMMENT '年龄，检查约束',
14. email VARCHAR(100) UNIQUE COMMENT '邮箱，唯一约束',
15. created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
16. PRIMARY KEY (id)
17. ) ENGINE=InnoDB COMMENT='学生信息表';
19. -- 修改表结构：添加一个新列
20. ALTER TABLE students ADD COLUMN phone VARCHAR(20) AFTER email;
22. -- 修改表结构：修改列属性
23. ALTER TABLE students MODIFY COLUMN phone VARCHAR(15);
25. -- 删除表（慎用）
26. -- DROP TABLE IF EXISTS students;

复制代码

2. DML - 数据的增删改

sql

2. -- 插入数据 (INSERT)
3. -- 插入完整数据
4. INSERT INTO students (name, gender, age, email, phone)
5. VALUES ('张三', 'M', 20, 'zhangsan@test.com', '13800138001');
7. -- 插入多条数据
8. INSERT INTO students (name, gender, age, email, phone) VALUES
9. ('李四', 'M', 22, 'lisi@test.com', '13800138002'),
10. ('王五', 'F', 21, 'wangwu@test.com', '13800138003'),
11. ('赵六', 'F', 19, 'zhaoliu@test.com', '13800138004');
13. -- 更新数据 (UPDATE) - 务必加上WHERE条件！
14. UPDATE students SET age = 23, phone = '13900139001' WHERE name = '李四';
16. -- 删除数据 (DELETE) - 务必加上WHERE条件！
17. DELETE FROM students WHERE id = 4; -- 删除赵六
19. -- 如果你想清空全表，TRUNCATE比DELETE快，但不触发触发器，且无法回滚
20. -- TRUNCATE TABLE students;

复制代码

3. DQL - 数据查询 (核心)

sql

2. -- 基础查询：查询特定列
3. SELECT id, name, age FROM students;
5. -- 条件查询 (WHERE)
6. SELECT * FROM students WHERE gender = 'M' AND age > 20;
8. -- 模糊查询 (LIKE) % 代表任意字符，_ 代表一个字符
9. SELECT * FROM students WHERE name LIKE '张%'; -- 查询姓张的同学
11. -- 排序 (ORDER BY) 默认ASC升序，DESC降序
12. SELECT * FROM students ORDER BY age DESC, id ASC;
14. -- 分组与聚合 (GROUP BY + 聚合函数)
15. -- 常用聚合函数: COUNT, SUM, AVG, MAX, MIN
16. SELECT gender, COUNT(*) AS count, AVG(age) AS avg_age
17. FROM students
18. GROUP BY gender;
20. -- 分组后筛选 (HAVING) - 在分组结果上过滤
21. SELECT gender, COUNT(*) AS count
22. FROM students
23. GROUP BY gender
24. HAVING count > 1;
26. -- 分页查询 (LIMIT offset, row_count)
27. -- 查第2页，每页2条 (第一页是0,2)
28. SELECT * FROM students LIMIT 2, 2;

复制代码

4. 高级查询 - 多表连接 (JOIN)

首先创建一个成绩表，演示连接：

sql

2. CREATE TABLE IF NOT EXISTS scores (
3. id INT AUTO_INCREMENT PRIMARY KEY,
4. student_id INT NOT NULL,
5. subject VARCHAR(50) NOT NULL,
6. score INT NOT NULL,
7. FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE
8. );
10. INSERT INTO scores (student_id, subject, score) VALUES
11. (1, '数学', 90),
12. (1, '英语', 85),
13. (2, '数学', 78),
14. (3, '英语', 92);
16. -- INNER JOIN (内连接): 只返回匹配的行
17. SELECT s.name, sc.subject, sc.score
18. FROM students s
19. INNER JOIN scores sc ON s.id = sc.student_id;
21. -- LEFT JOIN (左外连接): 返回左表所有行，右表无匹配则显示NULL
22. SELECT s.name, sc.subject, sc.score
23. FROM students s
24. LEFT JOIN scores sc ON s.id = sc.student_id;
26. -- 子查询 (Subquery): 查询分数大于平均分的学生
27. SELECT name, age FROM students
28. WHERE id IN (
29. SELECT student_id FROM scores WHERE score > (SELECT AVG(score) FROM scores)
30. );

复制代码

5. 事务控制 (TCL) - 保证数据一致性

sql

2. -- 开始事务
3. START TRANSACTION;
5. -- 执行操作：张三账户扣100，李四账户加100
6. UPDATE accounts SET balance = balance - 100 WHERE user = '张三';
7. UPDATE accounts SET balance = balance + 100 WHERE user = '李四';
9. -- 检查无误，提交事务（持久化）
10. COMMIT;
12. -- 如果出错，回滚事务（撤销所有更改）
13. -- ROLLBACK;

复制代码

6. 索引 (INDEX) - 提升查询性能

sql

2. -- 创建普通索引
3. CREATE INDEX idx_students_name ON students(name);
5. -- 创建唯一索引 (确保列值唯一)
6. CREATE UNIQUE INDEX idx_students_email ON students(email);
8. -- 查看SQL执行计划，判断是否使用了索引
9. EXPLAIN SELECT * FROM students WHERE name = '张三';
11. -- 删除索引
12. DROP INDEX idx_students_name ON students;

复制代码

---

第三部分：总结与面试高频点

1. 事务的ACID特性：

   • 原子性：事务中的操作要么全部成功，要么全部失败。

   • 一致性：事务前后，数据库的完整性约束没有被破坏。

   • 隔离性：多个事务并发执行时，相互隔离，互不干扰。

   • 持久性：事务提交后，数据的更改是永久的。

2. 索引的底层结构：最常见的为 B+树。

   • 优势：大大减少磁盘I/O次数，查询效率高。

   • 劣势：占用物理空间，更新表时（INSERT/UPDATE/DELETE）需要维护索引，会降低写速度。

   • 使用原则：在频繁查询的列、有序的列、重复度低的列上建索引。

3. SQL优化思路：

   • 避免 SELECT *，只查询需要的字段。

   • 避免在 WHERE 子句中对字段进行函数操作或计算（会导致索引失效）。

   • 使用 EXPLAIN 分析执行计划，关注 type (连接类型) 和 rows (扫描行数)。

   • 对于大数据量的分页查询，避免使用 LIMIT offset, size，改用子查询或 JOIN 方式优化。