MySQL_查询专题

发表于2022-05-17更新于2025-11-04阅读次数

内容

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

存储过程

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

表的创建：

1
2
3
4
5
6
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;

需要用到存储过程。

  1. 首先改变分隔符delimiter $
  2. 创建存储过程
1
2
3
4
5
6
7
8
9
10
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查看此存储过程。

image-20220517201206028

  1. 改回分隔符delimiter ;
  2. 调用存储过程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;

去重

有两种去重的方式

  1. distinct - 简单地去重
  2. group by - 功能强大，耗时
1
SELECT DISTINCT age FROM user;

image-20220517114906800

合并查询 - union

格式如下

1
2
3
4
5
6
SELECT expression1, expression2, ..., expression
FROM table1 [WHERE conditions]
UNION [ALL | DISTINCT(default)]
SELECT expression1, expression2, ..., expression
FROM table2 [WHERE conditions];
# union默认为distinct去重，all表示显示重复的记录项
1
2
3
4
5
SELECT name,age,sex
FROM user WHERE age>=21
UNION ALL
SELECT name,age,sex
FROM user WHERE sex='M';

image-20220517163204447

分页查询 - limit

需要掌握的内容：

  1. limit分页查询的语法
  2. explain可以查看SQL语句大致的执行计划
  3. limit在何种情况下能够提升查询效率，使用limit需要注意哪些
  4. 项目中用到分页处理时，如何编写相应的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展示的字段：

  1. id: 编号
  2. select_type: 如SIMPLE，表示查询语句的复杂程度类型
  3. table: 如user，当前表
  4. partitions: NULL
  5. type: const
  6. possible_keys: name，可能用到的索引
  7. key: name，实际用到的索引
  8. key_len: 152
  9. ref: const
  10. rows: 1，查询语句可能需要扫描的行数。
  11. filtered: 100.00
  12. Extra: NULL
1
EXPLAIN SELECT * FROM user WHERE name='zhangsan';

image-20220517192840144

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

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

image-20220517193136218

发现仍是row=1。

这是因为name有索引。

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

1
EXPLAIN SELECT * FROM user WHERE age=20;

image-20220517193519105

我们发现现在的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;

image-20220517194317756

说明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. 内连接查询
  2. 外连接查询
    1. left连接查询
    2. right连接查询

image-20220518111035278

内连接

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

student: uid、name、age、sex

course: cid、cname、credit

exam: uid、cid、time、score

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
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;

image-20220518113024474

按上面这样查，只能查出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来进行关联的。

1
2
3
4
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、查的是某门课

image-20220518144857264

连接查询的性能分析：

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。

1
2
3
4
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;

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

1
2
3
4
5
6
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进行先偏移后查询的方法来进行的话，越往后性能越差。

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

image-20220518163625400

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

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

image-20220518163818854

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

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

image-20220518164026806

我们再把目光转到第二个方案 - 即只查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这一列一致。

image-20220518164514376

explain此语句的结果：

image-20220518164909106

总结 - 内连接的过程

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

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

image-20220519083428353

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

image-20220519084255059

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

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

image-20220519090035026

我们看到，在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;

image-20220519091420259

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

外连接

格式：

1
2
3
4
5
6
7
8
9
#左连接查询
#显示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;

image-20220519094125697

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

image-20220519094314249

1
2
3
4
5
6
7
8
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;

image-20220519100936236

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

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;

image-20220519101959096

Explain查看查询流程：

image-20220519102456318

拿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;

image-20220519103001353

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

1
2
3
4
5
SELECT S.* FROM student S
LEFT JOIN exam E
ON S.uid=E.uid
WHERE E.uid=3
AND E.cid IS NULL;
1
2
3
4
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;

image-20220519103539550

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

image-20220519103820392

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

explain分析

image-20220519104015360

image-20220519104102999

这样写内连接和外连接无区别。都是先使用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;

image-20220519112753295

把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;

image-20220519113227107

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

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

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

MySQL_范式设计

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

内容

  1. 关系型数据库范式
  2. 应用范式的好处
  3. 第一范式
  4. 第二范式
  5. 第三范式
  6. BC范式
  7. 第四范式

应用范式的好处

范式和表的设计有什么关系呢？可以说是息息相关，表设计过程中需要知道的规则，在范式设计中，都会有所提及。可以说，范式是表设计原则的提炼。

表设计原则是告诉我们怎么设计表之间的关联，并且尽量减少数据冗余。

而应用范式最重要的好处也是减少数据冗余。由此带来的好处有：

  1. 消除异常（插入异常、更新异常、删除异常）
  2. 让数据组织地更加和谐

第一范式

  1. 每一列保持原子特性
  2. 不符合第一范式则不能称作关系型数据库。比如key-value，因为key对应的value可能包含了各种杂乱的信息，例如json数据。

列都是基本数据项，不能再进行分割，否则设计成一对多的实体关系。

例如表中的地址字段，可以再细分为省、市、区等不可再分割的字段。

image-20220516163422062

上图的Employee表中的Address字段，就没有遵循第一范式。即地址信息可以细分为很多详细的地址。如果没有去规范地址的分类，则就会造成地址数据粒度太粗。如果我们要查询某一特定地址的雇员信息，还要从地址字符串中提取相关信息，过于麻烦。

image-20220516163314051

我们现在将Address抽取出来作为一个单独的表，让地址字段分成更详细的city、country、street三个字段。再让Employee表与其关联，则可解决Address粒度过粗的问题。

查询指定EmployeeID的职员的地址信息。

1
2
3
# 方法一：带in的子查询，子查询效率较低
select * from EmployeeAddress where addressID in (select AddressID from Employee where EmployeeID=1);
# 用连接查询效率高一些。

第二范式

属性完全依赖于主键 - 主要针对联合主键

非主属性（不是主键的字段）必须完全依赖主关键字（包括联合主键的每一列），如果不是完全依赖主键，应该拆分成新的实体，设计成一对多的实体关系。

例如教务系统中的选课：
选课关系表为SelectCourse(学号, 姓名, 年龄, 课程名称, 成绩, 学分)。
则(学号, 课程名称)是联合主键，
但是学分字段只与课程名称有关，与学号无关，相当于只依赖联合主键的其中一个字段 - 课程名称，不符合第二范式。
姓名、年龄只和学号有关，与课程名称无关。只依赖联合主键的其中一个字段 - 学号，不符合第二范式。

总之，学分在此表中属于冗余存储。姓名和年龄字段也属于冗余存储。

学生和课程之间，属于多对多的关系。

学生表：

学号 姓名 年龄

课程表：

课程id 课程名称 学分

选课情况（中间表）：

学号 课程id 成绩

再例如，上面的Employee表。经历了第一范式之后，仍存在问题。

该表存在联合主键(EmployeeID, departmentName)。表中Name字段明显与departmentName不存在关系。因此不符合第二范式。

则把department有关信息

image-20220516202334294

第三范式

属性不依赖于其他非主属性。

一般的关系型数据库满足第三范式就足够了。

要求一个数据表中不包含已在其他表中已包含的非主关键字信息。

示例：
学生关系表为Student(学号, 姓名, 年龄, 所在学院, 学院地点, 学院电话)，
字段学号是主键。
但是，字段学院地点、学院电话依赖于非主属性所在学院，并不依赖于主键学号。
因此该设计不符合第三范式。
应该把学院单独设计成一张表，学院和学生是一对多的关系。

再如Employee表中的问题，发现字段jobDescription并不依赖于主键EmployeeID，而是依赖非主属性job。不符合第三范式。因此把job有关的属性抽取出来单独作为job表。工作和雇员也是一对多的关系。

image-20220517082159583

BC范式

每个表中只有一个候选键

BC范式是在第三范式的基础上的一种特殊情况，即每个表中只有一个候选键（在一个数据库中，每行的值都不相同，则可称为候选键）。Employee表中可以看出，每一个员工的email都是唯一的（逻辑上来说，邮箱是单人单用），则上表不符合BC范式。

应把邮箱单独抽出作为一张表。邮箱与雇员属于一对一的关系。

image-20220517084229658

第四范式

消除表中的多值依赖

简单来说，第四范式就是要消除表中的多值依赖，也就是说可以减少数据一致性的工作。比如Employee表中的字段skill，有的人是"java, mysql"，有的人是"Java, MySQL"，这样数据就不一致了。

解决方案就是将多值属性放入一个新表。Employee表则是把skill抽出作为一张表。雇员和技能是一对多的关系。

image-20220517085547889

skill EmployeeID
C++ 1000
Java 1000
MySQL 2000
Golang 2000
Rust 1000
C++ 2000

总结

  • 范式含义
  • 好处：应用数据库范式可以带来许多好处,但是最重要的好处归结为三点：
    1. 减少数据沉余(这是最主要的好处，其他好处都是由此而附带的)
    2. 消除异常(插入异常、更新异常、删除异常
    3. 让数据组织的更加和谐
  • 缺点：但是数据库范式绝对不是越高越好，范式越高，意味着表越多，多表联合查询的机率就越大，SQL的效率就变低。