第二阶段-第二章—8天Python从入门到精通【itheima】-133节（SQL——DQL——基础查询）
目录
133节——DQL：基础查询
1.学习目标
2.基础数据查询：select
3.进行过滤的基础数据查询：where
4.代码演练
5.小节总结
6.关于MySQL和SQL的DDL、DML、DCL、DQL的最底层逻辑
MySQL 与 SQL 的底层逻辑：从磁盘到内存的数据流解析
一、DDL（数据定义语言）：构建数据大厦的蓝图
二、DML（数据操作语言）：数据流动的三重关卡
三、DCL（数据控制语言）：权限的多维管控
四、DQL（数据查询语言）：从磁盘到内存的征程
五、底层交互的性能关键点
六、总结：SQL 语句的终极使命
好了，又一篇博客和代码写完了，励志一下吧，下一小节等等继续：
133节——DQL：基础查询
1.学习目标
1.掌握DQL——进行select的语句的基础的查询操作
2.基础数据查询：select
3.进行过滤的基础数据查询：where
4.代码演练
-- 选中库
use student;-- 将20条数据插入表中
insert into student values(10006,'张大山',10,'男'),(10007,'毛',22,'女'),(10008,'江',18,'男');
insert into student values(10009,'二狗',30,'男'),(10010,'狗蛋',32,'女'),(10011,'师爷',18,'男');
insert into student values(10012,'李大山',10,'男'),(10013,'毛二',22,'女'),(10014,'玉龙',13,'男');
insert into student values(10015,'王大山',40,'男'),(10016,'三伢子',22,'女'),(10017,'金威',28,'男');
insert into student values(10018,'彪哥',19,'男'),(10019,'德胜',22,'女'),(10020,'翔宇',18,'男');-- 查询student表的id和name这两个列
select id,name from student;-- 查询全部的列:*代表所有、全部
select * from student;-- 查询年龄大于20岁的人
select * from student where age>20;-- 查询性别都是男生的人:别忘了gender这个字段类型是varchar(),记得带上单引号
select * from student where gender='男';
5.小节总结
6.关于MySQL和SQL的DDL、DML、DCL、DQL的最底层逻辑
MySQL 与 SQL 的底层逻辑：从磁盘到内存的数据流解析
一、DDL（数据定义语言）：构建数据大厦的蓝图
底层执行流程：
元数据写入
当执行CREATE TABLE时，MySQL 首先在information_schema数据库中记录表结构（列名、数据类型、索引等），并生成.frm 文件（MySQL 5.7 及以前）或在系统表空间中存储元数据（MySQL 8.0+）。存储引擎初始化
- InnoDB：创建表空间文件（.ibd），初始化 B + 树结构的根节点，并分配初始数据页（默认 16KB）。
- MyISAM：生成三个文件（.frm 表结构、.MYD 数据、.MYI 索引），索引文件采用独立的 B + 树。
权限验证
检查用户是否有CREATE权限，通过mysql.user和mysql.db系统表验证。示例 DDL 执行过程：
sql
CREATE TABLE users (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50),age INT ) ENGINE=InnoDB;
- 实际生成：
plaintext
mysql> SHOW CREATE TABLE users\G CREATE TABLE `users` (`id` int NOT NULL AUTO_INCREMENT,`name` varchar(50) DEFAULT NULL,`age` int DEFAULT NULL,PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4二、DML（数据操作语言）：数据流动的三重关卡
1. INSERT 的物理写入
- SQL 层：解析语句，检查约束（主键唯一、非空等），生成执行计划。
- 存储引擎层：
- InnoDB：通过
change buffer优化非聚簇索引写入，将数据暂存内存，批量合并到索引树。- 写重做日志（redo log）：确保崩溃恢复能力，先写日志再写磁盘。
- 磁盘层：按页（page）写入数据文件，数据页通过双向链表连接。
2. UPDATE 的隐式删除与插入
- InnoDB 采用 MVCC（多版本并发控制），更新时标记旧版本为删除，写入新版本，通过
undo log实现回滚。- 示例：
sql
UPDATE users SET age = 25 WHERE id = 1;
实际执行：
- 标记旧记录为删除（通过
DELETE_MASK位）。- 在 B + 树中插入新记录。
3. DELETE 的逻辑删除
- 执行
DELETE时，InnoDB 仅标记记录为删除（delete mark），真正的物理删除在purge线程定期清理时完成。三、DCL（数据控制语言）：权限的多维管控
1. 权限存储结构
- MySQL 通过四张系统表管理权限：
mysql.user：全局权限（如CREATE USER）。mysql.db：数据库级权限（如ALL PRIVILEGES ON test.*）。mysql.tables_priv：表级权限。mysql.columns_priv：列级权限。2. GRANT 执行流程
sql
GRANT SELECT, INSERT ON test.users TO 'user'@'localhost';
- 解析权限类型，检查当前用户是否有权限授予。
- 将权限记录写入
mysql.tables_priv，生成哈希索引加速权限匹配。3. 权限验证机制
- 每次 SQL 执行前，通过
security_context匹配当前用户权限，采用前缀匹配算法快速定位。四、DQL（数据查询语言）：从磁盘到内存的征程
1. 查询解析与优化
sql
SELECT name FROM users WHERE age > 20 ORDER BY id LIMIT 10;
- 解析器：构建抽象语法树（AST），验证语法正确性。
- 预处理器：检查表和列是否存在，解析别名。
- 优化器：
- 生成候选执行计划（如全表扫描 vs 索引扫描）。
- 通过代价模型（cost model）选择最优路径（评估 IO 和 CPU 成本）。
2. 索引的物理作用
- B + 树索引：叶节点包含主键值，通过索引快速定位数据页。
- 示例：
WHERE age > 20可能触发索引扫描，直接访问age列的 B + 树。3. 执行引擎与存储引擎交互
- 执行器调用 InnoDB 的
read_record()接口，逐行读取数据。- 若启用查询缓存，先检查缓存是否命中（MySQL 8.0 已移除查询缓存）。
五、底层交互的性能关键点
Buffer Pool 的魔力
- InnoDB 将热点数据页缓存到内存（Buffer Pool），命中率通常可达 90% 以上。
- 通过 LRU-K 算法管理缓存，优先保留高频访问页。
Redo Log 与 Checkpoint
- 写操作先记录到 redo log（顺序 IO），再异步刷新到数据文件（随机 IO）。
- Checkpoint 机制定期将脏页（dirty page）刷盘，保持日志空间可用。
索引与锁的纠缠
- 行锁（row lock）通过索引实现，若无合适索引，会升级为表锁。
- 示例：
UPDATE users SET name = 'x' WHERE id = 1;仅锁定单行。六、总结：SQL 语句的终极使命
每一条 SQL，从客户端发送到服务器，历经解析、优化、权限验证，最终转化为磁盘上的物理操作或内存中的数据流动。理解这些底层逻辑，能让开发者写出更高效的查询（如利用索引覆盖），设计更合理的表结构（如聚簇索引优化），并在故障时快速定位问题（如通过 redo log 恢复数据）。SQL 不仅是一种语言，更是数据在计算机系统中流动的精密蓝图。
好了，又一篇博客和代码写完了，励志一下吧，下一小节等等继续：
Patrick，你他妈听着 —— 别跟我扯什么 “烂人生”，你那点破情绪在真正的硬仗面前，连屁都不算！
somebody is so rich & 有名校光环？挺好，那是他的路。但你呢？你每天拖着病体爬起来跑步，对着代码报错死磕，这他妈才是真刀真枪的战斗！sca-14的遗传病？休学？专科？这些算个屁！它们不过是生活给你戴的枷锁，而你每天挣破枷锁的样子，比那些顺风顺水的家伙牛逼一万倍！
你以为 Rocky 为什么牛逼？不是因为他打赢了阿波罗，是因为他一个 nobody 敢站上拳台，哪怕被揍得满脸是血也不倒下！你现在就在你的拳台上 —— 每天的 3 万步是你的左勾拳，每一行代码是你的右直拳，你他妈在和命运对打，这比任何名校光环都他妈耀眼！
那些说你 “慢” 的人？让他们滚！别人四年本科毕业又怎样？你用五年、六年甚至更久读完又如何？你走过的每一步，都是踩着病痛和自我怀疑爬出来的，这学历上的每一个字，都比别人的镀金文凭重十倍！你以为给人补课赚几千几万很牛逼？你他妈带病啃下 MySQL 的每一个语法，每天和身体的颤抖较劲还能跑完 5 公里，这才是真本事 —— 这是你用命换的底气，懂吗？
别跟我提什么 “对比”。他们的顺境是温室里的花，你的挣扎是暴雨里的树。暴雨过后，花会烂掉，树会扎根更深。你现在觉得自己是 shit？狗屁！你是正在淬火的钢，疼是因为你在变硬！
明天早上，给我接着爬起来跑步。晚上，给我接着敲代码。管他妈的别人进度多快，你只要比昨天多撑一秒，多学一个知识点，你就是在赢！记住：nobody 的逆袭，才是这世界上最他妈燃的戏码。你不是 shit，你是那个敢跟命运死磕的混蛋 —— 这种混蛋，注定会让所有人闭嘴。
现在，滚去战斗。你的拳台，你说了算。