M6: 复合查询、子查询、正则表达式

6.10 复合查询与子查询

6.10.1 复合查询

定义： 所谓复合查询，就是联表查询时，where多写几个条件。

例子：

select * from student as s,
sbuject as sub
where s.subject_id=sub.id and s.sex='男';

上述语句可以查询出所有的男学生的个人信息,以及专业信息。

6.10.2 子查询

定义： 所谓子查询，就是嵌套多个select，但它本身并不叫嵌套子查询，把一个select的查询结果，作为另一个select的查询条件的范围。

带in关键字的子查询：

语法：

selece * from 表名 where 字段 in 集合;

例子：
select * from student
where subject_id in
(select id from subject);

上面语句也等同于：
select * from student as s
inner join subject as sub
on s.subject_id=sub.id;

注意： 虽然in也可以实现连接的功能，但是，连接效率更高。

---

带比较运算符的子查询：

语法：

select * from 表名 where 字段 比较运算符 值;

例子：
select * from student
where subject_id=(select id from where `name`='财务管理');

---

带exists关键字的查询：

定义：使用exists关键字，内层查询不返回结果集，而返回布尔值。子查询语句查询到满足条件的记录，则返回true,此时外层查询将会进行；如果内层子句为查询到满足条件的记录，则返回false。外层查询则不进行。

语法：

select * from 表名 where exists 布尔值;

在实际使用过程中，发现了一些问题

假设有如下，两张表：

表1：student表

name	subject
张三	1
李四	3
王五	2

表2：subject表

id	sub_name
1	软件工程
2	通信工程

执行语句1

select * from student where exists (select * from `subject` where id =1);

返回值：

name	subject
张三	1
李四	3
王五	2

执行语句2

select * from student as s where exists (select * from `subject` as sub where s.`subject`=sub.id);

返回结果：

name	subject
张三	1
王五	2

分析：

​ 根据exists关键字的定义来说，只要子查询为真，那么外层循环一定会返回所有值，那么两个执行结果，都应该是语句1的结果。但是，从执行结果看来，语句1与语句2的执行结果并不一样。这里要引入一个知识点，叫做相关子查询与非相关子查询。

定义：非相关子查询就是指，子查询可以自己独立运行，不依赖外层查询，也叫做嵌套子查询。而相关子查询则是需要依赖外层查询。

相关子查询执行步骤：

​ 1.从外层查询的表里，得到第一条记录。

​ 2.将第一条记录放入子查询内，辅助子查询进行。

​ 3.子查询完毕，返回外层查询中，并与where进行判断，满足曾保留，反之排除。

​ 4.从外层查询的表里，得到第二条记录，重复以上过程。

由此可见，上述语句2是相关子查询，所以才会少了一条数据，并且相关子查询并不局限于exists关键字。

---

带ANY关键字的子查询：

定义： 表示满足内层返回结果的其中一个条件即可，通常与比较运算符联合使用。

例子：现有两张成绩表class3与class2，请查询出3班中成绩比2班最低分高的所有学生。

select * from class3 where score >any(select * from class2);

---

带all关键字的子查询

定义： 表示满足内层返回结果的所有值，通常与比较运算符联合使用。

例子：现有两张成绩表class3与class2，请查询出3班中成绩比2班最高分高的所有学生。

select * from class3 where score > all(select score from class2);

6.11 合并查询

6.11.1 简介

​ 所谓合并查询，实则是将几个select的结果集，合并在一起。有两个关键字：Union、Union all,二者的区别在于前者会去掉重复记录，但是后者不会。

6.11.2 语法

Union的使用

select * from 表名
Union
select * from 表名
Union
...


例子：
select * from grade
Union
select * from subject;
此语句可以查询出所有的专业以及年级信息。

Union all的使用：

​ 与Union语法完全一致。

注意： Union与Union all在合并结果集时，多个结果集的列数要一致，否则sql语句将会执行失败。

6.12 正则表达式

6.12.1 语法

select * from 表名 where 字段 regexp '正则表达式';

6.12.2 正则表达式的模式字符

字符	含义	语句	备注
^	匹配以特定字符/字符串开头的记录	select * from student where name regexp ‘^张’	张三、张天佑、张
$	匹配以特定字符/字符串结尾的记录	select * from student where name regexp ‘西$’	陈冠西、刘山西、密西西
.	匹配任意单个字符，包括换行与回车	select * from student where name regexp ‘a.c’	abc、adc、aac
[]	匹配集合中的任意一个字符	select * from student where name REGEXP ‘[123]’	1111，2bghm3,3dsfd
[^]	匹配除集合之中的任意一个字符	select * from student where name REGEXP ‘[^李四南]’	除李四南意外的所有人
s1|s2|s3	匹配其中任意一个字符	select * from student where name REGEXP ‘李|王’	李四南、王伟、刘李浩
*	匹配多个字符之前的字符，保包括0与1	select * from student where name REGEXP ‘四*南’	李四南、王张四南懿
+	匹配多个字符之前的字符，保包括1	select * from student where name REGEXP ‘四+南’	李四南、王张四南懿
{n}	匹配字符串连续出现至少N次	select * from student where pwd REGEXP ‘1{2}’	211，1112
{n,m}	匹配字符串连续出现[n,m]次	select * from student where pwd REGEXP ‘1{3,6}’	21111、311111

注意：

​ 1）“.”对中文的支持很不好， “a.c” 可以查出abc、agc，但是**“刘.轩”却查不出 “刘子轩” ，不过“刘.”可以查出刘子轩与刘浩**。

​ 2）“[]” 对中文支持更不友好，检索失败。

以上原因未知，数据库版本5.7。

6.12.3 验证邮箱非法

select * from user
where email not regexp '^[a-zA-Z0-9_-]+@[a-zA-Z0-9_-]+(\.[a-zA-Z0-9_-]+)+$'

注意： 这是某资料上的写法，但是，这个写法有待改进。

---

2021.07.25