MySQL基础知识

一、MySQL 概述

1. 数据库类型与典型应用场景

数据库类型	数据存储特性	典型应用场景
Redis	内存数据库，数据完全驻留内存	系统缓存层，允许数据丢失的场景
MySQL	关系型数据库，数据持久化至磁盘	核心业务数据存储，保障数据安全

MySQL 核心特性：

通过 Buffer Pool 将磁盘数据缓存在内存中，提高读写性能，但数据的最终归宿始终是磁盘。
支持 OLTP（联机事务处理）和 OLAP（联机分析处理）两类业务：
- OLTP：记录业务事件（如登录、交易等）的实时数据操作，要求高并发、低延迟。
- OLAP：基于历史数据的汇总分析（如留存率、充值统计等），通常涉及复杂查询和大量数据扫描。

2. SQL 语言分类

分类	全称	主要语句	说明
DQL	Data Query Language	`SELECT`	数据查询语言
DML	Data Manipulation Language	`INSERT`、`UPDATE`、`DELETE`	直接修改数据库内容
DDL	Data Definition Language	`CREATE`、`ALTER`、`DROP`	定义数据结构
DCL	Data Control Language	`GRANT`、`REVOKE`	权限管理
TCL	Transaction Control Language	`COMMIT`、`ROLLBACK`、`SAVEPOINT`	事务管理

3. 数据库术语

DB（Database）：数据库，关联表的集合，如 education_service 库。
表（Table）：由行（记录）和列（同类型数据字段）构成的二维矩阵。
主键（Primary Key）：B+树的排序依据，用于唯一标识一行，如自增ID或GUID。
外键（Foreign Key）：InnoDB引擎下用于实现参照完整性约束，关联两个表。
复合键：多列组合的索引排序结构，用于联合主键或联合索引。

二、MySQL 体系结构

1. 整体架构

MySQL采用典型的客户端/服务器架构，主要由以下几部分组成：

Connectors：多种语言驱动（C++、PHP、Python等），实现应用层与MySQL的通信。
连接池：内部维护线程池，管理客户端TCP连接，每个连接对应一个独立线程。
Server层：包含连接器、查询缓存（MySQL 8.0已移除）、分析器、优化器、执行器等，负责SQL解析、优化和执行。
存储引擎层：可插拔的存储引擎（InnoDB、MyISAM、Memory等），通过标准接口与Server层交互，负责数据的实际存储与读取。
磁盘文件：包括数据文件（.ibd）、日志文件（redo log、undo log、binary log）、错误日志、慢查询日志等。

2. 一条 SELECT 语句的执行流程

连接器：接收应用层通过MySQL客户端库（如libmysqlclient）建立的连接请求，验证用户名密码，分配线程。
查询缓存（已废弃）：MySQL 8.0之前会检查是否命中缓存（key为SQL语句，value为结果集），命中则直接返回；8.0已移除该功能。
分析器：对SQL进行词法分析和语法分析，生成语法树，检查SQL语句是否符合语法规则。
优化器：生成多个执行计划，通过代价模型选择最优方案（如选择索引、决定表连接顺序）。
执行器：调用存储引擎接口，从缓存或磁盘获取数据，并返回结果集。

3. 存储引擎核心功能（以InnoDB为例）

Buffer Pool：将磁盘数据页缓存在内存中，减少磁盘IO。
事务支持：通过redo log（重做日志）保证持久性，通过undo log（回滚日志）保证原子性，通过锁机制保证隔离性。
MVCC：多版本并发控制，实现非锁定读。
磁盘文件管理：管理表空间文件（.ibd）、日志文件等。

4. 内部连接池与线程模型

MySQL内部连接池通过主线程监听客户端连接请求（使用IO多路复用，如select），接收连接后从池中分配一个线程专门处理该连接的后续读写。每个连接对应一个独立线程，采用阻塞IO模型，保证单连接内SQL语句串行执行（原子性）。这种设计简化了并发控制，每个线程独立处理一个连接，无需考虑多线程竞争。

三、数据库设计三范式与反范式

1. 三范式概述

三范式是数据库设计的基础规范，目的是减少数据冗余，避免数据异常（插入、更新、删除异常）。实际开发中，需要根据业务场景在范式与性能之间权衡。

2. 第一范式（1NF）：字段原子性

要求字段不可再分。例如地址字段若需按省、市、区统计，则应拆分为province、city、district等独立字段。原子性的判断取决于业务需求：如果不需要细分，保留一个字段也可以。

3. 第二范式（2NF）：消除部分依赖

在满足1NF基础上，要求非主键字段完全依赖于主键（不能只依赖主键的一部分）。当主键是复合键时，必须确保非主键字段与整个主键相关，否则应拆表。

示例：订单明细表 (order_id, product_id, product_name, quantity)，主键为 (order_id, product_id)。product_name 仅依赖 product_id（部分依赖），应拆分为订单表、商品表和订单明细表。

4. 第三范式（3NF）：消除传递依赖

在满足2NF基础上，要求非主键字段直接依赖于主键，不能通过其他非主键字段间接依赖。例如学生表 (student_id, class_id, class_name)，class_name 依赖于 class_id，而 class_id 依赖于 student_id（传递依赖），应拆分为学生表和班级表。

5. 反范式设计

反范式通过引入冗余数据来提高查询性能，常见场景：

减少多表关联：将高频查询的关联字段冗余到主表，例如在订单表中冗余用户昵称。
预计算统计结果：在表中存储统计值（如总金额、点赞数），避免实时计算。

反范式会带来数据一致性问题，需要在冗余字段上通过业务逻辑或触发器维护一致性。实际开发中，通常采用“适度反范式”策略：优先保证业务正确性，在性能瓶颈处适当冗余。

四、SQL 基本操作（CRUD）

1. 数据库操作

-- 创建数据库
CREATE DATABASE great_database;

-- 查看所有数据库
SHOW DATABASES;

-- 选择数据库
USE great_database;

-- 删除数据库（谨慎操作）
DROP DATABASE great_database;

2. 表操作

-- 创建表
CREATE TABLE IF NOT EXISTS student (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    class_id INT
);

-- 查看表结构
DESC student;

-- 删除表
DROP TABLE student;

-- 清空表数据（保留结构）
TRUNCATE TABLE student;

DROP、TRUNCATE、DELETE 的区别

操作	语句类型	是否可回滚	自增列行为	存储空间释放
`DROP`	DDL	不可回滚	表被删除	立即释放
`TRUNCATE`	DDL	不可回滚	重置为1	立即释放（以页为单位）
`DELETE`	DML	可回滚	继续累加	仅标记删除，不释放空间

3. 约束

CREATE TABLE user (
    id INT AUTO_INCREMENT PRIMARY KEY,           -- 主键约束 + 自增
    username VARCHAR(50) NOT NULL,               -- 非空约束
    email VARCHAR(100) UNIQUE,                   -- 唯一约束
    age INT CHECK (age >= 0),                    -- 检查约束（MySQL 8.0支持，低版本忽略）
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES department(id) ON DELETE CASCADE  -- 外键约束
);

主键：唯一标识一行，默认包含非空和唯一。
外键：保证参照完整性，可设置级联操作（ON DELETE CASCADE 等）。

4. 数据操作（DML）

插入数据

-- 指定列插入
INSERT INTO student (name, age, class_id) VALUES ('张三', 18, 1);

-- 批量插入
INSERT INTO student (name, age, class_id) VALUES 
('李四', 19, 1),
('王五', 20, 2);

-- 插入所有列（自增列可省略）
INSERT INTO student VALUES (NULL, '赵六', 21, 3);

更新数据

-- 更新满足条件的记录
UPDATE student SET age = 22 WHERE name = '张三';

-- 更新多列
UPDATE student SET age = 23, class_id = 2 WHERE id = 1;

-- 注意：不加WHERE会更新全表

删除数据

-- 删除满足条件的记录
DELETE FROM student WHERE id = 1;

-- 删除全表（可回滚）
DELETE FROM student;

五、高级查询技术

1. 基础查询

-- 全表查询
SELECT * FROM student;

-- 指定列查询
SELECT name, age FROM student;

-- 列别名
SELECT name AS 姓名, age AS 年龄 FROM student;

-- 去重（DISTINCT）
SELECT DISTINCT class_id FROM student;

-- 使用GROUP BY去重（效果同DISTINCT）
SELECT class_id FROM student GROUP BY class_id;

2. 条件查询（WHERE）

-- 比较运算符
SELECT * FROM student WHERE age > 18;
SELECT * FROM student WHERE class_id = 1;

-- 范围查询
SELECT * FROM student WHERE age BETWEEN 18 AND 22;

-- 集合查询
SELECT * FROM student WHERE class_id IN (1, 2, 3);

-- 空值判断
SELECT * FROM student WHERE name IS NULL;
SELECT * FROM student WHERE name IS NOT NULL;

-- 不等号：<> 或 !=
SELECT * FROM student WHERE class_id <> 2;

3. 模糊查询（LIKE）

-- 以“张”开头
SELECT * FROM student WHERE name LIKE '张%';

-- 包含“小”
SELECT * FROM student WHERE name LIKE '%小%';

-- 第二个字是“小”
SELECT * FROM student WHERE name LIKE '_小%';

4. 分页查询（LIMIT）

-- 每页显示2条，第1页（偏移0，取2条）
SELECT * FROM student LIMIT 0, 2;

-- 第2页
SELECT * FROM student LIMIT 2, 2;

-- 简化写法：从第0条开始取2条
SELECT * FROM student LIMIT 2;

5. 排序（ORDER BY）

-- 升序（默认）
SELECT * FROM student ORDER BY age;

-- 降序
SELECT * FROM student ORDER BY age DESC;

-- 多列排序
SELECT * FROM student ORDER BY class_id ASC, age DESC;

索引对排序的影响：

若ORDER BY字段有索引，可直接利用B+树有序性，性能高。
若无索引，需在内存或磁盘中进行filesort，性能较差。

6. 聚合查询

-- 统计总行数
SELECT COUNT(*) FROM student;

-- 统计非空值数量
SELECT COUNT(name) FROM student;

-- 求平均值
SELECT AVG(age) FROM student;

-- 求和
SELECT SUM(age) FROM student;

-- 最大值/最小值
SELECT MAX(age), MIN(age) FROM student;

7. 分组查询（GROUP BY）

-- 按班级分组，统计每个班人数
SELECT class_id, COUNT(*) AS cnt FROM student GROUP BY class_id;

-- 按性别分组，统计平均年龄
SELECT gender, AVG(age) FROM student GROUP BY gender;

-- 字符串聚合：将每个班的学生姓名用逗号连接
SELECT class_id, GROUP_CONCAT(name SEPARATOR ',') FROM student GROUP BY class_id;

WHERE vs HAVING：

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

-- 查询平均年龄大于20岁的班级
SELECT class_id, AVG(age) AS avg_age 
FROM student 
GROUP BY class_id 
HAVING AVG(age) > 20;

8. 联表查询（JOIN）

8.1 INNER JOIN（内连接）

返回两个表匹配的行。

SELECT s.name, c.class_name 
FROM student s
INNER JOIN class c ON s.class_id = c.id;

8.2 LEFT JOIN（左外连接）

返回左表全部行，右表匹配不到则填充NULL。

SELECT s.name, c.class_name 
FROM student s
LEFT JOIN class c ON s.class_id = c.id;

8.3 RIGHT JOIN（右外连接）

返回右表全部行，左表匹配不到则填充NULL。

SELECT s.name, c.class_name 
FROM student s
RIGHT JOIN class c ON s.class_id = c.id;

8.4 子查询与JOIN的转换

IN子查询：可转换为 INNER JOIN。

-- 查询选了课程1的学生
SELECT * FROM student WHERE id IN (SELECT student_id FROM score WHERE course_id = 1);
-- 等价于
SELECT DISTINCT s.* 
FROM student s 
INNER JOIN score sc ON s.id = sc.student_id 
WHERE sc.course_id = 1;

NOT IN子查询：可转换为 LEFT JOIN WHERE NULL。

-- 查询未选课的学生
SELECT * FROM student WHERE id NOT IN (SELECT student_id FROM score);
-- 等价于
SELECT s.* 
FROM student s 
LEFT JOIN score sc ON s.id = sc.student_id 
WHERE sc.student_id IS NULL;

9. 高级查询案例

9.1 平均成绩大于60分的学生

SELECT student_id, AVG(score) AS avg_score
FROM score
GROUP BY student_id
HAVING AVG(score) > 60;

注意：HAVING 可以使用聚合函数，而 WHERE 不能。

9.2 查询同时选修了课程1和课程2的学生

方法一：分组聚合

SELECT student_id
FROM score
WHERE course_id IN (1, 2)
GROUP BY student_id
HAVING COUNT(DISTINCT course_id) = 2;

方法二：自连接

SELECT a.student_id
FROM score a
INNER JOIN score b ON a.student_id = b.student_id
WHERE a.course_id = 1 AND b.course_id = 2;

方法三：子查询

SELECT student_id
FROM score
WHERE course_id = 1 AND student_id IN (SELECT student_id FROM score WHERE course_id = 2);

推荐使用分组聚合方法，效率较高且易于扩展。

9.3 查询每门课程的最高分及学生信息

SELECT c.course_name, s.name, sc.score
FROM score sc
JOIN student s ON sc.student_id = s.id
JOIN course c ON sc.course_id = c.id
WHERE (sc.course_id, sc.score) IN (
    SELECT course_id, MAX(score)
    FROM score
    GROUP BY course_id
);

9.4 使用IFNULL处理空值

SELECT s.name, IFNULL(AVG(sc.score), 0) AS avg_score
FROM student s
LEFT JOIN score sc ON s.id = sc.student_id
GROUP BY s.id;

六、预处理语句（Prepared Statement）

预处理语句将SQL语句模板与参数分离，具有以下优点：

性能提升：数据库只需解析一次，可多次执行。
防止SQL注入：参数与SQL逻辑分离，参数值不会被当作SQL代码执行。

MySQL 中使用预处理语句的示例：

-- 准备语句
PREPARE stmt FROM 'SELECT * FROM student WHERE id = ?';

-- 设置变量
SET @id = 1;

-- 执行
EXECUTE stmt USING @id;

-- 再次执行不同参数
SET @id = 2;
EXECUTE stmt USING @id;

-- 释放
DEALLOCATE PREPARE stmt;

在编程语言中（如PHP、Python、Java），通过数据库驱动使用预处理语句是更常见的方式。

七、视图与触发器

1. 视图（View）

视图是虚拟表，本质是一个存储的查询语句。它不存储实际数据，访问时动态生成结果。

优点：

数据安全：隐藏基表结构，只暴露必要字段。
逻辑封装：简化复杂查询，将多表关联封装为单表。
架构透明：当基表结构变化时，可通过修改视图保持接口不变。

创建视图示例：

CREATE VIEW student_class_view AS
SELECT s.id, s.name, s.age, c.class_name
FROM student s
LEFT JOIN class c ON s.class_id = c.id;

使用视图：

SELECT * FROM student_class_view WHERE age > 18;

注意事项：

视图可以是只读的（若包含聚合、JOIN等），也可更新（简单视图），但通常不建议通过视图更新数据。
视图的查询性能可能不如直接查询，因为每次查询都会重新执行定义语句。

2. 触发器（Trigger）

触发器是与表关联的数据库对象，在指定事件（INSERT、UPDATE、DELETE）发生前后自动执行一段SQL。

典型应用：

自动维护冗余数据（如统计字段）。
审计日志记录。
数据一致性检查。

创建触发器示例：

DELIMITER //

CREATE TRIGGER update_class_count
AFTER INSERT ON student
FOR EACH ROW
BEGIN
    UPDATE class SET student_count = student_count + 1 WHERE id = NEW.class_id;
END;//

DELIMITER ;

触发器的注意事项：

过多使用触发器可能降低性能，并导致隐式逻辑难以调试。
触发器中的SQL与触发事件在同一事务中，可回滚。

posted @ 2026-03-26 21:46 xggx 阅读(35) 评论(0) 收藏举报

刷新页面返回顶部

xggx

MySQL基础知识

MySQL基础知识

一、MySQL 概述

1. 数据库类型与典型应用场景

2. SQL 语言分类

3. 数据库术语

二、MySQL 体系结构

1. 整体架构

2. 一条 SELECT 语句的执行流程

3. 存储引擎核心功能（以InnoDB为例）

4. 内部连接池与线程模型

三、数据库设计三范式与反范式

1. 三范式概述

2. 第一范式（1NF）：字段原子性

3. 第二范式（2NF）：消除部分依赖

4. 第三范式（3NF）：消除传递依赖

5. 反范式设计

四、SQL 基本操作（CRUD）

1. 数据库操作

2. 表操作

3. 约束

4. 数据操作（DML）

插入数据

更新数据

删除数据

五、高级查询技术

1. 基础查询

2. 条件查询（WHERE）

3. 模糊查询（LIKE）

4. 分页查询（LIMIT）

5. 排序（ORDER BY）

6. 聚合查询

7. 分组查询（GROUP BY）

8. 联表查询（JOIN）

8.1 INNER JOIN（内连接）

8.2 LEFT JOIN（左外连接）

8.3 RIGHT JOIN（右外连接）

8.4 子查询与JOIN的转换

9. 高级查询案例

9.1 平均成绩大于60分的学生

9.2 查询同时选修了课程1和课程2的学生

9.3 查询每门课程的最高分及学生信息

9.4 使用IFNULL处理空值

六、预处理语句（Prepared Statement）

七、视图与触发器

1. 视图（View）

2. 触发器（Trigger）

公告