-- 创建数据库并切换
create database db_itheima;
use db_itheima;
-- 创建班级表
drop table if exists classes;
create table classes(cls_id int, cls_name varchar(20));
insert into classes values (1, 'UI'), (2, 'Java'), (3, 'Python'), (4, 'Shell');
-- 创建学生表
drop table if exists students;
create table students(id int, name varchar(20), age int, gender enum('male', 'female'), score decimal(11,2), cls_id int);
insert into students values
(1, '刘备', 18, 'male', 100.00, 2),
(2, '貂蝉', 18, 'female', 99.00, 1),
(3, '赵云', 18, 'male', 98.00, 3),
(4, '关羽', 18, 'male', 96.00, 3),
(5, '大乔', 18, 'female', 97.00, 1),
(6, '小乔', 18, 'female', 97.00, 5); -- cls_id=5 不存在
三、内连接（INNER JOIN）
说明：返回两表中满足条件的交集记录，不匹配的行不会出现。
语法：
select 字段 from 表A inner join 表B on 关联条件;
-- inner 可省略：select 字段 from 表A join 表B on 关联条件;
案例1：查询学生及其班级
-- 内连接：查询学生和班级信息
select * from students inner join classes on students.cls_id = classes.cls_id;
-- 给表起别名
select stu.*,cls.cls_name from students as stu inner join classes as cls on stu.cls_id = cls.cls_id;
id
name
age
gender
score
cls_id
cls_id
cls_name
1
刘备
18
male
100.00
2
2
Java
2
貂蝉
18
female
99.00
1
1
UI
3
赵云
18
male
98.00
3
3
Python
4
关羽
18
male
96.00
3
3
Python
5
大乔
18
female
97.00
1
1
UI
小乔（cls_id=5）不匹配，不会出现。
案例2：指定字段 + WHERE 条件
-- 查询分数>=97的学生姓名、分数和班级
select stu.id,stu.name,stu.score,cls.cls_name from students as stu inner join classes as cls
on stu.cls_id = cls.cls_id
where stu.score>=97;
id
name
score
cls_name
1
刘备
100.00
Java
2
貂蝉
99.00
UI
3
赵云
98.00
Python
5
大乔
97.00
UI
四、左外连接（LEFT JOIN）
说明：以左表为基准，左表数据全部显示，右表无匹配时用 NULL 填充。
语法：
select 字段 from 左表 left join 右表 on 关联条件;
案例：查询所有班级及其学生
-- 左外连接：所有班级都要显示，没有学生的班级学生信息为NULL
select stu.id,stu.name,stu.score,cls.cls_name from classes as cls left join students as stu
on stu.cls_id = cls.cls_id;
id
name
score
cls_name
5
大乔
97.00
UI
2
貂蝉
99.00
UI
1
刘备
100.00
Java
4
关羽
96.00
Python
3
赵云
98.00
Python
NULL
NULL
NULL
Shell
Shell班没有学生，但左连接保证左表（classes）全部显示。
五、右外连接（RIGHT JOIN）
说明：以右表为基准，右表数据全部显示，左表无匹配时用 NULL 填充。
语法：
select 字段 from 左表 right join 右表 on 关联条件;
案例：查询所有学生及其班级
-- 右外连接：所有学生都要显示，没有匹配班级的显示NULL
select stu.id,stu.name,stu.score,cls.cls_name from classes as cls right join students as stu
on stu.cls_id = cls.cls_id;
-- 子查询：查询数学成绩大于平均分的学生
select * from tb_score where math > (select avg(math) from tb_score);
执行过程：子查询先算出平均分 84.6，主查询再过滤 math > 84.6 的记录。
id
name
math
english
1
刘备
90
85
4
赵云
95
88
5
貂蝉
88
92
案例2：SELECT 列表中的子查询
-- 子查询：查询每人成绩及全班最高分
select name,math,(select max(math) from tb_score) as max_math from tb_score;
name
math
max_math
刘备
90
95
关羽
80
95
张飞
70
95
赵云
95
95
貂蝉
88
95
七、连接查询对比表
特性
内连接 (INNER JOIN)
左外连接 (LEFT JOIN)
右外连接 (RIGHT JOIN)
关键字
inner join / join
left join
right join
结果集
两表交集
左表全部 + 右表匹配
右表全部 + 左表匹配
NULL 填充
不填充
右表无匹配填 NULL
左表无匹配填 NULL
语法
on 关联条件
on 关联条件
on 关联条件
使用场景
查共有数据
左表全量展示
右表全量展示
第二部分：MySQL 体系结构
一、MySQL四层架构简介
层级
名称
主要功能
第一层
连接层
管理连接、身份认证、连接池
第二层
服务层
SQL解析、优化、缓存
第三层
引擎层
数据存储和提取的实现
第四层
存储层
与操作系统交互，数据持久化
二、连接层核心参数
参数
说明
默认值
建议值
max_connections
最大连接数
151
根据业务调整（100-1000）
wait_timeout
连接闲置等待时间（秒）
28800
600-1800
thread_cache_size
线程缓存池大小
9
CPU核心数×2
进程与线程区别
概念
特点
适用场景
进程
资源分配基本单位，资源开销大，数据不共享
CPU密集型
线程
CPU调度最小单位，依赖进程，资源共享
IO密集型
三、存储引擎
什么是存储引擎？
存储引擎是管理操作数据的方法和机制
MySQL推荐使用InnoDB引擎
一个库中不同的表可以使用不同的引擎
查看存储引擎
-- 查看支持的存储引擎种类
show engines;
-- 查看默认存储引擎
select @@default_storage_engine;
-- 结果：InnoDB
四、MyISAM vs InnoDB对比表
特性
MyISAM
InnoDB
事务支持
不支持
支持（ACID特性）
外键支持
不支持
支持
锁机制
表级锁
行级锁
适用场景
读多写少
高并发写操作
崩溃恢复
数据损坏风险高
自动恢复，双写机制
存储结构
每表3个文件(.sdi/.MYI/.MYD)
每表1个.ibd文件
性能
查询性能较好
写操作性能较高
五、事务处理核心语法
-- 开启事务
start transaction;
-- 执行SQL操作（如转账）
update bank set money=money-1000 where name='itwu';
update bank set money=money+1000 where name='我';
-- 提交事务（操作全部成功时）
commit;
-- 回滚事务（操作失败时）
rollback;
EXPLAIN SELECT * FROM orders WHERE user_id = 123; -- 分析查询执行计划
核心字段说明
字段
说明
重要程度
type
连接类型，反映查询效率
最重要
key
实际使用的索引（NULL=没用索引）
很重要
rows
估算扫描行数（越少越好）
很重要
possible_keys
可能使用的索引
重要
Extra
额外信息
重要
type 效率对比表（从高到低）
type
速度
说明
const
最快
主键/唯一索引等值查询
ref
快
普通索引等值查询
range
较快
索引范围查询（BETWEEN、>、<）
index
较慢
全扫描索引树
ALL
最慢
全表扫描（必须优化！）
三、索引类型简介
索引类型
命令
特点
主键索引
PRIMARY KEY
唯一且不为NULL
唯一索引
UNIQUE INDEX
值不重复，允许NULL
普通索引
INDEX
允许重复值
联合索引
INDEX(col1, col2)
多字段组合，遵循最左前缀原则
索引管理命令
-- 查看索引
SHOW INDEX FROM 表名;
-- 添加唯一索引
ALTER TABLE 表名 ADD UNIQUE uk_name (name);
-- 添加普通索引
ALTER TABLE 表名 ADD INDEX idx_name (name);
-- 添加联合索引
ALTER TABLE 表名 ADD INDEX idx_name_age (name, age);
-- 删除索引
DROP INDEX 索引名 ON 表名;
四、加索引前后对比案例
未加索引
EXPLAIN SELECT * FROM tb_people WHERE id = 1990000;
+----+------+------+------+---------+----------+-------------+
| id | type | key | rows | filtered | Extra |
+----+------+------+------+---------+----------+-------------+
| 1 | ALL | NULL | 2M | 100.00 | Using where |
+----+------+------+------+---------+----------+-------------+
添加主键索引
ALTER TABLE tb_people ADD PRIMARY KEY(id);
加索引后
EXPLAIN SELECT * FROM tb_people WHERE id = 1990000;
+----+-------+---------+------+----------+-------+
| id | type | key | rows | filtered | Extra |
+----+-------+---------+------+----------+-------+
| 1 | const | PRIMARY | 1 | 100.00 | |
+----+-------+---------+------+----------+-------+
对比： type 从 ALL → const，rows 从 200万 → 1，性能提升约 200 万倍。