MySQL_SQL

发表于2022-05-17更新于2025-07-30阅读次数

内容

SQL(Structure Query Language, 结构化查询语言)
SQL划分为3个类别，DD(Definition)L、DM(Manipulation)L、DC(Control)L
库操作
表操作
CRUD
SQL语句和索引的关系

SQL

SQL即Structure Query Language，结构化查询语言。它是关系型数据库的通用语言。

SQL可划分为以下三种类别：

DDL(Data Definition Language)
- 数据定义语言，与数据库、表、列、索引等数据库对象的定义有关。
- 常用的语句关键字主要包括create、drop、alter等。
DML(Data Manipulation Language)
- 数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。
- 常用的语句关键字主要包括insert、delete、update、select等。
DCL(Data Control Language)
- 数据控制语句，用于控制不同的许可和访问级别的语句。与数据库、表、字段、用户的访问权限和安全级别有关。
- 主要的语句关键字包括grant、revoke。

库操作

查询数据库：show databases;

创建数据库：create database dbname;

删除数据库：drop database dbname;

选择数据库：use dbname;

我们下面以学校的数据库为例，创建库表。

1 2	CREATE DATABASE school; USE school;

表操作

表的设计要素：

字段的数据类型
完整性约束条件的规范
一对一/一对多/多对多实体关系对应的表设计原则

创建表

CREATE TABLE user(
    id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) UNIQUE NOT NULL,
    age TINTINT NOT NULL,
    sex ENUM('M', 'W') NOT NULL
)ENGINE=INNODB DEFAULT CHARSET=utf8;

对于存储引擎以及默认字符集的配置文件，
在windows下为mysql安装目录下的my.ini；
linux下为/etc/my.cnf。
配置字段为[mysqld]

查看表

1	show tables;

查看表的描述信息

1 2	DESC user; describe user; # 全拼

表已创建后，查看表的创建语句

1 2	SHOW CREATE TABLE user; SHOW CREATE TABLE user\G # \G结尾和;结尾的区别在于显示方式不同

删除表 - drop (不光是表的内容，整个表的结构都没了)

1	DROP TABLE user;

不常用的操作

View（视图）

在数据库中，左边栏，存在一个view，可以存放一个表中的多个字段的查询结果，也可以存放多个表连接之后的临时结果。

实际用途中，视图可以用于屏蔽与实际数据库的差异，比如数据库的实际字段可能不是用户请求的字段，视图起了个别名。
也可以用于隐藏一些细节。把实际数据库中的内容提取出来的不敏感的部分放到视图里，建立虚拟的字段，供用户使用。

存储过程

类似于批处理脚本。可以没有返回值。

函数

封装操作过程，可以定义变量，可以有控制语句、循环语句等。必须有返回值。

触发器

触发器的作用：比如插入、删除一条记录等等操作，可以进行相应的回调。
触发器是基于行的，即基于记录的。当操作很多行时，不建议使用触发器。

总结

存储过程和函数一般是专门操作数据库的后端人员负责的。实际上，存储过程和函数的操作，完全可以在上层服务用高级语言（C++等）完成。数据库可以只负责简单的计算。

CRUD

Create - 插入

1
2

INSERT INTO user VALUES(1, 'zhangsan', 20, 'M'); #如果不指明插入的属性，则必须按照字段的顺序全部填入
INSERT INTO user(name,age,sex) VALUES('lisi', 22, 'W'); #指明插入的属性，由于id设置为自增性，所以不用填写。

每次添加一个

INSERT INTO user(name,age,sex) VALUES('zhangsan', 20, 'M');
INSERT INTO user(name,age,sex) VALUES('gaoyang', 22, 'W');
INSERT INTO user(name,age,sex) VALUES('chenwei', 20, 'M');
INSERT INTO user(name,age,sex) VALUES('zhangfan', 21, 'W');
INSERT INTO user(name,age,sex) VALUES('zhanglan', 21, 'W');

批量增加

INSERT INTO user(name,age,sex) VALUES
('zhangsan', 20, 'M'),
('gaoyang', 22, 'W'),
('chenwei', 20, 'M'),
('zhangfan', 21, 'W'),
('zhanglan', 21, 'W');

最终表中存放的数据是一致的。有啥区别？

我们使用sql，相当于mysql server的一个client。

每次添加一个：

client和server进行tcp三次握手，建立通信链路；
client发送sql语句到server上接收并处理，返回处理结果；
server和client断开连接，tcp四次挥手，释放通信链路。

把每次添加一条记录的操作抽象为3次步骤。则总共需要3*n个步骤。

而如果是批量添加：

client和server进行tcp三次握手，建立通信链路；
client发送sql语句到server上接收并处理，返回处理结果；
server和client断开连接，tcp四次挥手，释放通信链路。

则总共仅需3个步骤，即可全部插入。

Delete - 删除

1 2	DELETE FROM user; #删除表中所有数据 DELETE FROM user WHERE id=1; #只删除id为1的记录

Update - 更新

1	UPDATE user SET age=age+1 WHERE name='gaoyang'; #调正gaoyang的年龄+1

Retrieve - 查询

在这里我们示例一些简单的查询语句。

无条件查询全部字段

1	SELECT * FROM user; #不推荐，最好还是老老实实地写出所有字段名称，因为表的结构之后可能会变更，可能会影响业务代码。

条件查询指定字段

1
2
3

SELECT name,age,sex FROM user WHERE age>=21;
SELECT name,age,sex FROM user WHERE age>20 AND age<22;
SELECT name,age,sex FROM user WHERE BETWEEN 20 AND 22;#闭区间

1
2
3

# 以下两个sql语句效果一致。
SELECT name,age,sex FROM user WHERE age=20 OR age=21;
SELECT name,age,sex FROM user WHERE age IN (20,21);

使用通配符查询

1	SELECT name,age,sex FROM user WHERE name LIKE "zhang%";#必须用LIKE，不能用=等号，否则会把%通配符看作实际字符。

判断为空不用=，而用IS NULL

1 2	SELECT name,age,sex FROM user WHERE name IS NULL; SELECT name,age,sex FROM user WHERE name IS NOT NULL;

去重

有两种去重的方式

distinct - 简单地去重
group by - 功能强大，耗时

1	SELECT DISTINCT age FROM user;

合并查询 - union

格式如下

SELECT expression1, expression2, ..., expression
FROM table1 [WHERE conditions]
UNION [ALL | DISTINCT(default)]
SELECT expression1, expression2, ..., expression
FROM table2 [WHERE conditions];
# union默认为distinct去重，all表示显示重复的记录项

SELECT name,age,sex
FROM user WHERE age>=21
UNION ALL
SELECT name,age,sex
FROM user WHERE sex='M';

SQL语句和索引的关系

1	SELECT name,age,sex FROM user WHERE age=20 OR age=21;

通常都说带逻辑与的SQL语句会用到索引，带逻辑或的SQL语句不会用到索引。比如上面这句带逻辑或的SQL语句不会用到索引吗？错误。

如果仅仅是对于这个SQL语句的表象来说，是不会用到索引的。但是MySQL实际运行过程中是会对用户提交的SQL语句进行优化的。

OR的语义是：要么..., 要么...。对于逻辑或语句，MySQL有可能将其转化为union合并查询，即前后分别执行两个不同的不带逻辑或的SQL语句，这样的SQL语句是完全可以用不到索引的。

类似地，带in的SQL语句，表面上是能用到索引；带not in的SQL语句，表面上用不到索引。带仅仅限于MySQL不做优化的情况。

所以，会不会用到索引不能只看表面的SQL语句，而要看MySQL实际如何优化。

连接查询

表1：Employees

EmployeeID	Name	DepartmentID
1	张三	101
2	李四	102
3	王五	102
4	赵六	103
5	钱七	NULL

表2：Departments

DepartmentID	DepartmentName
101	人事部
102	技术部
104	财务部

笛卡尔积（交叉查询）

SELECT
    e.EmployeeID AS EmpID,
    e.Name AS EmpName,
    e.DepartmentID AS A_DeptID,
    d.DepartmentID AS B_DeptID,
    d.DepartmentName AS DeptName
FROM Employees e -- 给表起别名 'e'
CROSS JOIN Departments d; -- 给表起别名 'd'
-- 没有 ON 子句！

EmpID	EmpName	A_DeptID	B_DeptID	DeptName
1	张三	101	101	人事部
1	张三	101	102	技术部
1	张三	101	104	财务部
2	李四	102	101	人事部
2	李四	102	102	技术部
2	李四	102	104	财务部
3	王五	102	101	人事部
3	王五	102	102	技术部
3	王五	102	104	财务部
4	赵六	103	101	人事部
4	赵六	103	102	技术部
4	赵六	103	104	财务部
5	钱七	NULL	101	人事部
5	钱七	NULL	102	技术部
5	钱七	NULL	104	财务部

内连接 (INNER JOIN / JOIN)

通俗解释：只保留“两头都能配上对”的数据。 两边都有记录的才显示出来。

SELECT Employees.EmployeeID, Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

结果：

EmployeeID	Name	DepartmentName
1	张三	人事部
2	李四	技术部
3	王五	技术部

赵六(员工ID4) 的部门ID是103，而部门表里没有103号部门，所以被过滤掉了。
财务部(部门ID 104) 没有任何员工，也没出现。

左连接 (LEFT JOIN / LEFT OUTER JOIN)

通俗解释：“左边表全保，右边表尽量配”。 左表所有记录都显示，右表有匹配的也显示出来，没匹配的补空值（NULL）。

场景： 列出所有员工（不管有没有部门），同时如果员工有部门，就显示部门名称（没有部门的员工，部门名称显示为空）。

SELECT Employees.EmployeeID, Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

结果：

EmployeeID	Name	DepartmentName	DepartmentID
1	张三	人事部	101
2	李四	技术部	102
3	王五	技术部	102
4	赵六	NULL	103
5	钱七	NULL	NULL

左边员工表 (Employees) 的 5 条记录全部保留。
张三、李四、王五 在部门表里找到了匹配 (101, 102)，所以显示部门名称。
赵六(部门ID 103) 在部门表里没找到对应的名字，所以部门名称显示为 NULL。
钱七（员工ID 5）的部门 ID 本来就是 NULL，所以无法查询，显示 NULL。
部门表中孤立的 财务部(104) 没出现。

如何筛选出A表中外键为NULL的记录

如此查询，便能筛选出钱七。
这个图的意思是：在外面的红色部分，与B没有交集，意思就是说，外面这部分没有记录来自B的外键信息，与B无关联。

右连接 (RIGHT JOIN / RIGHT OUTER JOIN)

（其实相当于左连接的镜像，左连接时，A和B表交换书写位置就是他们的右连接）

通俗解释：“右边表全保，左边表尽量配”。 右表所有记录都显示，左表有匹配的也显示出来，没匹配的补空值（NULL）。（和左连接方向相反）

场景： 列出所有部门（不管有没有员工），同时如果部门下有员工，就列出员工姓名；没人的部门，员工信息显示为空。

SELECT Employees.EmployeeID, Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

结果：

EmployeeID	Name	DepartmentName
1	张三	人事部（101）
2	李四	技术部（102）
3	王五	技术部（102）
NULL	NULL	财务部（104）

右边部门表 (Departments) 的 3 条记录(101,102,104)全部保留。
人事部(101)、技术部(102) 找到了员工 (张三 / 李四, 王五)，显示姓名。
财务部(104) 在员工表里没任何员工，所以员工ID和姓名都是 NULL。
员工 赵六 (部门ID 103)、钱七（员工ID 5，部门ID NULL） 没出现（因为它的部门103在部门表里不存在，不属于右表保留范围）。

全连接 (FULL JOIN / FULL OUTER JOIN)

通俗解释：“两个表我都全要！管你配不配得上！” 两个表的所有记录都保留。能配上对的就合并成一行显示。配不上对的，各自那边的空缺就用 NULL 填充。

场景： 想做一个完整的组织结构/花名册快照，既要看到所有员工（包括没部门的），也要看到所有部门（包括没员工的）。信息齐全！

SELECT Employees.EmployeeID, Employees.Name, Departments.DepartmentName
FROM Employees
FULL JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;

结果：

EmployeeID	Name	DepartmentName	DepartmentID
1	张三	人事部	101
2	李四	技术部	102
3	王五	技术部	102
4	赵六	NULL	103
5	钱七	NULL	NULL
NULL	NULL	财务部	104

全部 6 种情况都展示出来了：
1. 有员工有部门 (张三-人事部, 李四-技术部, 王五-技术部)。
2. 有员工没(匹配的)部门 (赵六、钱七)。
3. 有部门没(匹配的)员工 (财务部)。

如何筛选出A表或B表的外键为NULL的记录

如此查询，便能筛选出钱七。

由于本例中B表没有外键，因此无法体现B表中没有外键的记录。

连接查询（Join）和联合查询（Union）的区别

联合查询（Union Query）： 这个概念容易和连接混淆。

通俗解释： 把两张结构_相似_的表 上下拼起来。就像把《番茄供应商名单》和《鸡蛋供应商名单》摞在一起，变成《食材供应商总名单》。注意：是垂直叠加！
核心要求：
- 两张表查询结果的列数必须一样。
- 对应列的数据类型要兼容（比如数字对数字，字符串对字符串）。

1
2
3

SELECT EmployeeID, Name FROM Employees -- 查员工ID和名字
UNION -- 或者 UNION ALL (包含重复行)
SELECT DepartmentID, DepartmentName FROM Departments; -- 查部门ID和名字

EmployeeID/DepartmentID	Name/DepartmentName
1	张三
2	李四
3	王五
4	赵六
5	钱七
101	人事部
102	技术部
104	财务部
注意：这个例子不常用，只是为了展示UNION效果。UNION更常用于合并同类型数据，比如不同年份的销售记录表

`SELECT ... FROM A, B WHERE ...`和`SELECT ... FROM A JOIN B ON ...`的区别

这两者功能上对于内连接(INNER JOIN)是等效的，但存在关键区别：语法标准、可读性、维护性以及错误预防能力。

特性	`SELECT ... FROM A, B WHERE ...` (隐式连接)	`SELECT ... FROM A JOIN B ON ...` (显式连接)
语法本质	老式SQL标准，FROM子句用逗号分隔表，WHERE定义条件和连接	新式SQL-92标准，用JOIN关键字明确指定连接类型和条件
连接类型	默认是交叉连接(CROSS JOIN)，需要WHERE过滤才能变成内连接	明确指出连接类型 (INNER/LEFT/RIGHT/FULL JOIN)
连接条件位置	和过滤条件混在WHERE子句中	连接条件在独立的ON子句，过滤条件在WHERE（逻辑分离）
可读性 & 维护性	差（复杂查询时条件混乱）	优（清晰区分连接条件和过滤逻辑）
防止笛卡尔积错误	弱（忘记WHERE会导致全组合）	强（JOIN必须配ON或USING，否则语法错误）
支持外连接	部分数据库不支持（如Oracle老语法用`(+)`）	所有数据库都支持标准外连接写法(LEFT/RIGHT/FULL JOIN)
现代推荐度	⚠️ 不推荐（尤其复杂查询）	✅ 强烈推荐

练习

查询平均成绩大于60分的同学的学号和平均成绩。

select s_id, avg(sc_score)
from score
group by s_id
having avg(sc_score)>60

查询所有同学的学号、姓名、选课总数、总成绩。

1	select s_id, s_name, count(c_id), sum(sc_score) from student left join score on student.s_id = score.s_id;

也可以这么写，嵌套select：

select student.s_id, student.s_name, count_id as XKS, sum_id as ZCJ
from student
left join
(select s_id, count(c_id), as count_id, sum(sc_score) as sum_id from score group by s_id) as ss
on student.s_id = ss.s_id;

查询没有选全部课的同学的学号、姓名。

select student.s_id, student.s_name from student, score
where student.s_id = score.s_id
group by student.s_id
having count(score.c_id) < (select count(c_id) from course);

先让score表补充一列学生的姓名。
再让此临时表，按学号（确定1人）分组，每一组的s_id数就是这个人选的课程数。

结果

删除Kim老师课的Score表记录
为了看到效果，临时增加一条成绩。

1
2
3

delete from score
where c_id in
(select course.c_id from course inner join teacher on course.t_id = teacher.t_id where teacher.t_name = 'Kim');

后面的select，先让course表补充一列老师的名字，之后筛选出老师名字是Kim的course；
in用于批量处理。

结果：

MySQL_查询专题

发表于2022-05-17更新于2025-09-28阅读次数

内容

简单的查询语句
分页查询 - limit
排序、分组
连接查询（多表查询）

存储过程

为了模拟实际中的大数据场景，我们创建一个t_user数据库，t_user表，向其中添加200万条记录。

表的创建：

create table t_user(
    id int(11) NOT NULL AUTO_INCREMENT,
    email varchar(255) DEFAULT NULL,
    password varchar(255) DEFAULT NULL,
    PRIMARY KEY(id)
)engine=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8;

需要用到存储过程。

首先改变分隔符delimiter $
创建存储过程

Create Procedure add_t_user(IN n INT)
BEGIN
DECLARE i INT;
SET i=0;

WHILE i<n DO
INSERT INTO t_user VALUES(NULL, CONCAT(i+1,'@xcg.com'),i+1);
SET i=i+1;
END WHILE;
END$

创建好后，可以show create procedure add_t_user\G查看此存储过程。

改回分隔符delimiter ;
调用存储过程call add_t_user(2000000);，括号内的参数为要添加的记录数。

插入数据成功后，可以对其测试

1	SELECT COUNT(*) FROM t_user; #应为2000000

1	SELECT * FROM t_user LIMIT 10;

1 2	SELECT * FROM t_user WHERE email='1@xcg.com'; EXPLAIN SELECT * FROM t_user WHERE email='1@xcg.com';

简单的查询语句

在这里我们示例一些简单的查询语句。

无条件查询全部字段

1	SELECT * FROM user; #不推荐，最好还是老老实实地写出所有字段名称，因为表的结构之后可能会变更，可能会影响业务代码。

条件查询指定字段

1
2
3

SELECT name,age,sex FROM user WHERE age>=21;
SELECT name,age,sex FROM user WHERE age>20 AND age<22;
SELECT name,age,sex FROM user WHERE BETWEEN 20 AND 22;#闭区间

1
2
3

# 以下两个sql语句效果一致。
SELECT name,age,sex FROM user WHERE age=20 OR age=21;
SELECT name,age,sex FROM user WHERE age IN (20,21);

使用通配符查询

1	SELECT name,age,sex FROM user WHERE name LIKE "zhang%";#必须用LIKE，不能用=等号，否则会把%通配符看作实际字符。

判断为空不用=，而用IS NULL

1 2	SELECT name,age,sex FROM user WHERE name IS NULL; SELECT name,age,sex FROM user WHERE name IS NOT NULL;

去重

有两种去重的方式

distinct - 简单地去重
group by - 功能强大，耗时

1	SELECT DISTINCT age FROM user;

合并查询 - union

格式如下

SELECT expression1, expression2, ..., expression
FROM table1 [WHERE conditions]
UNION [ALL | DISTINCT(default)]
SELECT expression1, expression2, ..., expression
FROM table2 [WHERE conditions];
# union默认为distinct去重，all表示显示重复的记录项

SELECT name,age,sex
FROM user WHERE age>=21
UNION ALL
SELECT name,age,sex
FROM user WHERE sex='M';

分页查询 - limit

需要掌握的内容：

limit分页查询的语法
explain可以查看SQL语句大致的执行计划
limit在何种情况下能够提升查询效率，使用limit需要注意哪些
项目中用到分页处理时，如何编写相应的SQL语句

语法

limit M,N - limit N

1
2
3

SELECT * FROM user LIMIT N;		#表示取user表第0~N条记录的所有字段，相当于取前N条
SELECT * FROM user LIMIT M,N;	#表示取user表第M~(M+N)条记录的所有字段，相当于先偏移M条，再取N条。
SELECT * FROM user LIMIT N OFFSET M;	#同LIMIT M,N

limit只是改变了数据显示的效果吗？对查询的效率有没有提升呢？先引入一个关键字explain。

explain是一个关键字，是定位SQL、索引性能的有利工具。

使用它加载SQL语句开头，可以查看SQL语句的执行计划。

explain展示的字段：

id: 编号

select_type: 如SIMPLE，表示查询语句的复杂程度类型

table: 如user，当前表

partitions: NULL

type: const

possible_keys: name，可能用到的索引

key: name，实际用到的索引

key_len: 152

ref: const

rows: 1，查询语句可能需要扫描的行数。

filtered: 100.00

Extra: NULL

1	EXPLAIN SELECT * FROM user WHERE name='zhangsan';

我们发现，搜索"zhangsan"只扫了1行。可能你会猜测是因为他在表的第一行，那么试试查找其他人扫描几行呢？

1 2	EXPLAIN SELECT * FROM user WHERE name='chenwei'; EXPLAIN SELECT * FROM user WHERE name='zhanglan';

发现仍是row=1。

这是因为name有索引。

我们再试试与age有关的查询语句。

1	EXPLAIN SELECT * FROM user WHERE age=20;

我们发现现在的row为5。相当于把user表进行了全表搜索。

相比于与name有关的查询语句，由于age没有索引，查询的效率就很低。

怎么尽可能提高效率呢？

如果我们只想得到第一个满足条件的记录，那么可以用limit。

1	SELECT * FROM user WHERE age=20 LIMIT 1;

理论上，EXPLAIN此语句的rows应较小，但实际上rows还是5。

1	EXPLAIN SELECT * FROM user WHERE age=20 LIMIT 1;

说明EXPLAIN不能看到limit的作用。

我们现在对拥有200w条记录的t_user表进行测试。

1
2
3

SELECT * FROM t_user WHERE email='1@xcg.com';		#约0.44s
EXPLAIN SELECT * FROM t_user WHERE email='1@xcg.com';
SELECT * FROM t_user WHERE email='1@xcg.com' LIMIT 1;#约0.00s

1
2
3

SELECT * FROM t_user LIMIT 1000000, 1;#约0.20s，说明偏移也很费时
SELECT * FROM t_user WHERE email='1000001@xcg.com';	#约0.44s
SELECT * FROM t_user WHERE email='1000001@xcg.com' LIMIT 1;	#约0.23s

分页需要注意的事情

首先需要明白分页的参数pagenum=20，表示每页显示的记录数为20；pageno表示当前的页码。

那么，根据LIMIT的M,N规则，计算出下面的语句公式：

1	SELECT * FROM user LIMIT (pageno-1)*pagenum, pagenum;

这样就实现了每一页显示对应位置的20条记录。

但是，有个很现实的性能问题，我们总不能让越往后的页数查出来的时间越久吧？因为LIMIT M,N默认是全表顺序查询，查后面的数据时，会在前面查很多冗余的信息，浪费了时间。

1 2	SELECT * FROM t_user LIMIT 0, 20; #0.00s SELECT * FROM t_user LIMIT 1000000, 20; #0.23s

可以用一个思想来优化性能——过滤。

我们知道，其主键id是有规律的，可根据对id的限制来进行过滤。

1	SELECT * FROM t_user WHERE id>2000000 LIMIT 0, 20;#0.00s

上面的语句和SELECT * FROM t_user LIMIT 1000000, 20;查询出来的结果一样。但是性能却大大提升。

即

1	SELECT * FROM user WHERE id>上一页最后一条数据的id值 LIMIT 0, 20;

但是，不是每个分页都通过id来解决，具体问题具体分析，选择合适的索引去过滤，不一定非要选id。

LIMIT可以很好地帮助我们在没有索引的情况下，以相应的列作为过滤条件时，提高效率。

连接查询

分为两种

内连接查询
外连接查询
1. left连接查询
2. right连接查询

内连接

场景分析：学生、课程、考试结果

student: uid、name、age、sex

course: cid、cname、credit

exam: uid、cid、time、score

CREATE TABLE student(
    uid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age TINYINT UNSIGNED NOT NULL,
    sex ENUM('M', 'W') NOT NULL
);
CREATE TABLE course(
    cid INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
    cname VARCHAR(50) NOT NULL,
    credit TINYINT UNSIGNED NOT NULL
);
CREATE TABLE exam(
    uid INT UNSIGNED NOT NULL,
    cid INT UNSIGNED NOT NULL,
    time DATE NOT NULL,
    score FLOAT NOT NULL
    PRIMARY KEY(uid,cid) #联合主键
);
DESC student;
DESC course;
DESC exam;
INSERT INTO student(name,age,sex) VALUES
('zhangsan', 18, 'M'),
('gaoyang', 20, 'W'),
('chenwei', 22, 'M'),
('liufeng', 21, 'W'),
('liuxiang', 19, 'W');
INSERT INTO course(cname,credit) VALUES
('C++基础课程', 5),
('C++高级课程', 10),
('C++项目开发', 8),
('C++算法课程', 12),
INSERT INTO exam(uid,cid,time,score) VALUES
(1, 1, '2022-04-09', 99.0),
(1, 2, '2022-04-10', 90.0),
(2, 2, '2022-04-10', 90.0),
(2, 3, '2022-04-12', 85.0),
(3, 1, '2022-04-09', 56.0),
(3, 2, '2022-04-10', 93.0),
(3, 3, '2022-04-12', 89.0),
(3, 4, '2022-04-11', 100.0),
(4, 4, '2022-04-11', 99.0),
(5, 2, '2022-04-10', 59.0),
(5, 3, '2022-04-12', 94.0),
(5, 4, '2022-04-11', 95.0);

zhangsan同学某门课的考试成绩，假设知道预置条件：学号uid: 1、课程号cid: 2

1	SELECT score FROM exam WHERE uid=1 AND cid=2;

按上面这样查，只能查出score: 80。其他的信息没有，这样给出的信息过少。

怎么进行内连接查询，以获取更多信息呢？

先查学生表和考试创建表：由于学生中要根据uid，考试成绩中也是要根据uid，所以属于公有的内容，属于内连接。

下面是两个表单独的SQL：

1 2	SELECT uid,name,age,sex FROM student WHERE uid=1; SELECT score FROM exam WHERE uid=1 AND cid=2;

我们把它想办法合并：

各表中可能有重复的字段名，所以需要给表起别名

1
2
3

SELECT S.uid,S.name,S.age,S.sex FROM student S WHERE S.uid=1;

SELECT E.score FROM exam E WHERE E.uid=1 AND E.cid=2;

我们发现，两个SQL语句的共同点是uid为1。则是按uid来进行关联的。

SELECT S.uid,S.name,S.age,S.sex,E.score FROM student S
INNER JOIN exam E
ON S.uid=E.uid	#拿什么来关联
WHERE E.uid=1 AND E.cid=2;	#过滤的条件，即1、查的是某个人；2、查的是某门课

连接查询的性能分析：

on S.uid=E.uid按照表中记录的数目来区分大表和小表，小表永远是整表扫描，然后去大表搜索。即从student小表中取出所有的S.uid，然后拿着这些uid去exam大表中搜索。

继续：现在只有学生信息、分数，但是没有课程信息。

现在我们发现，如果基于select ... from student的话，无法与course表进行关联。因为student表中无cid。而如果基于eaxm查的话，就很顺利，因为exam表既有uid又有cid，既可与student表关联，又可与course表关联。

于是我们就需要改造select语句的第一句中的FROM ...。把FROM student改为FROM exam。

SELECT S.uid,S.name,S.age,S.sex,E.score FROM eaxm E
INNER JOIN student S
ON S.uid=E.uid
...

然后，我们接下来要做的就是再去内连接course表，以产生exam与其的关联。先写出要查course表信息的单独的SQL语句：

1	SELECT cid,cname,credit FROM course C WHERE C.cid=2;

然后再加入到内连接语句中去：

SELECT  S.uid,S.name,S.age,S.sex,
		C.cid,C.cname,C.credit,
		E.score FROM eaxm E
INNER JOIN student S ON E.uid=S.uid
INNER JOIN course C ON E.cid=C.cid
WHERE E.uid=1 AND E.cid=2;

以上就是三表内连接查询的标准写法。

内连接的应用 - 与分页结合提升性能

前面我们研究过了，如果直接按LIMIT M,N进行先偏移后查询的方法来进行的话，越往后性能越差。

分页 - 第一个方案，最差：

同时我们也发现，查询的效率也与查询的字段个数有关系，比如只查id的话，性能就会有所提升。

分页 - 第二个方案，适中，但是信息少：

最极致性能的办法，但是常常不通用——先过滤id，直接从过滤完的位置LIMIT查询N条记录。但是这要求我们知道当前的id值及其条件，通常情况下不能实现，我们只清楚目前偏移到了多少位置，一次取多少条。

分页 - 第三个方案，极致，但是往往不能实现：

我们再把目光转到第二个方案 - 即只查id。

为了尽可能提高性能，我们只查出了id这一列。目前的信息量过少。但是我们却可以利用查出的id数据，与自己的原表进行内连接。这是基本思想。

1 2	SELECT U.id,U.email,U.password FROM t_user U INNER JOIN (SELECT id FROM t_user LIMIT 1500000,10) temp ON U.id=temp.id;

如此不仅仅可以得到更多信息，而且效率和只查id这一列一致。

explain此语句的结果：

总结 - 内连接的过程

在做实验之前，先声明一下表中的最新内容

1 2	SELECT S.,E. FROM student S INNER JOIN exam E ON S.uid=E.uid;

1
2
3

EXPLAIN
SELECT S.*,E.* FROM student S
INNER JOIN exam E ON S.uid=E.uid;

如果我们通过where过滤条件使Exam从大表变小表呢？

1
2
3

SELECT S.*,E.* FROM student S
INNER JOIN exam E ON S.uid=E.uid
WHERE E.cid=3;

我们看到，在E表进行内连接时，需要先过滤，即E需要进行全表扫描，然后E成为小表（相比于S表），然后，再拿剩下的记录与S表比对。

E表没有用到cid的索引，因为uid和cid是联合索引，只有先用uid索引，cid才有效。

再看一个 - 把where的过滤条件改到on中。和上面有区别吗？

1 2	SELECT S.,E. FROM student S INNER JOIN exam E ON S.uid=E.uid and E.cid=3;

发现，对于inner join内连接，过滤条件不管写在where的后面和on连接条件里面，效果是一样的。而且extra里写明第一个是Using where，说明mysql为我们做了优化，改成了where过滤，因为where过滤后再查询的效率会更好。

外连接

格式：

#左连接查询
#显示left表所有数据，右表中如果不存在相应数据则显示NULL
SELECT a.属性名列表, b.属性名列表 FROM table_a a
LEFT [OUTER] JOIN table_b b ON a.id=b.id;

#右连接查询
#显示right表所有数据，左表中如果不存在相应数据则显示NULL
SELECT a.属性名列表, b.属性名列表 FROM table_a a
RIGHT [OUTER] JOIN table_b b ON a.id=b.id;

1 2	SELECT S.,E. FROM student S LEFT JOIN exam E ON S.uid=E.uid;

1 2	SELECT S.,E. FROM student S RIGHT JOIN exam E ON S.uid=E.uid;

EXPLAIN SELECT S.*,E.* FROM student S
INNER JOIN exam E ON S.uid=E.uid;

EXPLAIN SELECT S.*,E.* FROM student S
LEFT JOIN exam E ON S.uid=E.uid;

EXPLAIN SELECT S.*,E.* FROM student S
RIGHT JOIN exam E ON S.uid=E.uid;

实际的问题：能不能查出来谁没参加考试？

1	SELECT * FROM student WHERE uid NOT IN (SELECT DISTINCT uid FROM exam);

上面这个SQL虽然可以完成，但是NOT IN是否能够用到索引是不容易确定的。另外，子查询的结果可能会生成临时表。

用外连接来解决：

1
2
3

SELECT S.* FROM student S
LEFT JOIN exam E ON S.uid=E.uid
WHERE E.cid IS NULL;

Explain查看查询流程：

拿student表所有数据与exam表对比。exam表中的uid和cid是联合主键，因为uid是第一列索引，所以可以使用uid索引。

外连接和内连接的区别

参加过某一门考试的人

先用内连接来写：

1
2
3

SELECT S.* FROM student S
INNER JOIN exam E ON S.uid=E.uid
WHERE E.cid=3;

没有参加cid为3的考试的人

SELECT S.* FROM student S
LEFT JOIN exam E
ON S.uid=E.uid
WHERE E.uid=3
AND E.cid IS NULL;

SELECT S.* FROM student S
LEFT JOIN exam E
ON S.uid=E.uid
WHERE E.uid=3;

1
2
3

SELECT S.* FROM student S
LEFT JOIN exam E
ON S.uid=E.uid AND E.cid=3;

SELECT S.* FROM student S
LEFT JOIN exam E
ON S.uid=E.uid AND E.cid=3
WHERE E.cid IS NULL;

我们发现，where条件和on条件在内外连接上需要斟酌。

explain分析

这样写内连接和外连接无区别。都是先使用where过滤，然后exam变为小表。拿exam全部数据（主键）去student搜索。这样的外连接是不正确的。

1 2	EXPLAIN SELECT S.* FROM student S LEFT JOIN exam E ON S.uid=E.uid AND E.uid=3;

把E.uid=3放在on后面才会对student表进行全表扫描。进而去exam表中匹配。

然后我们再在这个正确外连接后的基础上，再加WHERE E.cid IS NULL，这才能正确显示未参加cid为的考试的学生。

1
2
3

SELECT S.* FROM student S
LEFT JOIN exam E ON S.uid=E.uid AND E.uid=3
WHERE E.cid IS NULL;

因此我们总结出了一条经验：外连接中，连接时的限制条件要放到ON后面，然后把NULL的过滤条件放到最后的where中。

连接查询中的索引影响性能

小表决定循环的次数，大表决定每次循环的查询时间。

内容

SQL

库操作

表操作

不常用的操作

View（视图）

存储过程

函数

触发器

总结

CRUD

Create - 插入

最终表中存放的数据是一致的。有啥区别？

Delete - 删除

Update - 更新

Retrieve - 查询

去重

合并查询 - union

SQL语句和索引的关系

连接查询

笛卡尔积（交叉查询）

内连接 (INNER JOIN / JOIN)

左连接 (LEFT JOIN / LEFT OUTER JOIN)

如何筛选出A表中外键为NULL的记录

右连接 (RIGHT JOIN / RIGHT OUTER JOIN)

全连接 (FULL JOIN / FULL OUTER JOIN)

如何筛选出A表或B表的外键为NULL的记录

连接查询（Join）和联合查询（Union）的区别

SELECT ... FROM A, B WHERE ...和SELECT ... FROM A JOIN B ON ...的区别

练习

内容

存储过程

简单的查询语句

去重

合并查询 - union

分页查询 - limit

语法

limit M,N - limit N

分页需要注意的事情

连接查询

内连接

内连接的应用 - 与分页结合提升性能

总结 - 内连接的过程

外连接

外连接和内连接的区别

连接查询中的索引影响性能

`SELECT ... FROM A, B WHERE ...`和`SELECT ... FROM A JOIN B ON ...`的区别