一、多表查询

1、内连接查询

内连接通过在查询中设置连接条件的方式，来移除查询结果集中某些数据行后的交叉连接。简单说，就是利用条件表达式来消除交叉连接的某些数据行。在FROM子句中使用关键字INNER JSON连接两张表，并使用ON子句来设置连接条件。

SELECT <列名1，列名2，...>
FROM <表名1> INNER JOIN <表名2> [ON 子句]

# 语法说明
 <列名1，列名2>：需要检索的列名
 <表名1>，<表名2>：进程内连接的两张表的表名

内连接是系统默认的表连接，所以在FROM子句后可以省略INNER关键字，只用关键字JOIN。在FROM子句中可以在多个表之间连续使用INNER JOIN或JOIN，这样可以实现多个表的内连接。

示例：表student与class都包含相同数据类型的字段class_id，在两个表之间使用内连接查询

SELECT s.id,s.name,s.sex,c.name FROM student AS s INNER JOIN class AS c ON s.class_id=c.id;
SELECT s.id,s.name,s.sex,c.name FROM student AS s INNER JOIN class AS c WHERE s.class_id=c.id;

内连接查询.png

2、外连接查询

内连接是在交叉连接的结果集上返回满足条件的记录，而外连接是先将连接的表分为基表和参考表，再以基表为依据返回满足和不满足条件的数据。
外连接按照连接表的顺序，可以分为左外连接和右外连接。

2.1、左外连接--)LEFT JOIN

左外连接又称为左连接，在FROM子句中使用关键字LEFT OUTER JOIN或LEFT JOIN用于接收该关键字左表(基表)的所有行，并用这些行与该行关键字右表(参考表)中的行进行匹配，即匹配左表中的每一行以及右表中符合条件的行。
在左外连接的结果集中，对于左表中有但右表中不匹配的行，从右表中选择的列的值被设置为NULL，即左外连接的结果集中的NULL值表示右表中没有找到与左表相符合的数据行。

示例：在student与class表中查询所有学生，包括没有学院的学生

SELECT s.id,s.name,s.sex,c.name FROM student AS s LEFT OUTER JOIN class AS c ON s.class_id=c.id; 

左外连接.png

2.2、右外连接--)RIGHT JOIN

右外连接又称为右连接，在FROM子句中使用RIGHT OUTER JOIN或者RIGHT JOIN。与左外连接相反，右外连接以右表为基表，左表为参考表。
在右外连接的结果集中，除了匹配的行外，还包括右表中有但在左表中不匹配的行，对于这样的行，从左表中选择的值被设置为NULL。

示例：在student与class表中查询所有学生，包括没有学生的学院

SELECT s.name AS '学生',c.id,c.name AS '学院' FROM student AS s RIGHT OUTER JOIN class AS c ON s.class_id=c.id;

右外连接.png

3、自连接查询

自连接是将一个表与自身进行连接，也是内连接的一种，同样适用INNER JOIN或者JOIN关键字进行连接。
如果需要在一个表中查找具有相同列值的行，就可以考虑用自连接。在使用自连接时，需要为表指定两个不同的别名，且对所有查询列的引用必须使用表别名的限定。

示例：查询id为1的学生所在学院的其他学生的信息

SELECT s1.id,s1.name,s1.class_id FROM student AS s1,student AS s2 WHERE s1.class_id=s2.class_id AND s2.id=1;

自连接查询.png

二、子查询

1、使用子查询的时机

子查询指一个查询语句嵌套在另一个查询语句内部的查询。在SELECT子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。
子查询中常用的操作符有ANY(SOME)、ALL、IN和EXISTS。子查询添加到SELECT、UPDATE和DELETE语句中。而且可以进行多层嵌套。子查询也可以使用比较运算符。

2、子查询中常用的运算符

2.1、IN子查询

<表达式> [NOT] IN <子查询>

# 判断一个给定值是否存在于子查询的结果集中

2.2、比较运算符子查询

<表达式> {= | < | > |>= | <= | <=> | <> | !=}
{ALL | SOME |ANY} <子查询>

# 主要用于对表达式的值和子查询返回的值进行比较运算

2.3、EXIST子查询

EXIST <子查询>

# 主要用于判断子查询的结果集是否为空，如果子查询的结果集不为空，则返回TRUE，否则返回FALSE。

3、子查询的应用

示例：在class表中查询python学院所有学生的姓名

SELECT * FROM student WHERE class_id IN (SELECT id FROM class WHERE name="python");
SELECT * FROM student WHERE class_id=(SELECT id FROM class WHERE name="python");

子查询~1.png

示例：在class表中查询不是python学院的所有学生的姓名

SELECT * FROM student WHERE class_id <> (SELECT id FROM class WHERE name="python");
SELECT * FROM student WHERE class_id != (SELECT id FROM class WHERE name="python");

子查询~2.png

示例：判断class表中是否存在java学院，如果存在，则查询student表下的所有学生姓名

SELECT * FROM student WHERE EXISTS (SELECT id FROM class WHERE name='java');

• EXISTS判断集合是否为空集

子查询~3.png

示例：判断class表中是否存在go学院，如果存在，则查询student表下的所有学生姓名

SELECT * FROM student WHERE EXISTS (SELECT id FROM class WHERE name='go');

子查询~4.png

三、分组查询

1、使用子查询的时机

在SELECT语句中，所有GROUP BY子句，将结果集中的数据行根据选择列的值进行逻辑分组，汇总内容的子集，实现对每个组而不是对整个结果集进行整合。

GROUP BY {<列名> | <表达式> | <位置>} [ASC | DESC]

2、聚合函数在分组查询中的应用

示例：查询每个学院的学生数量

SELECT COUNT(*) FROM student GROUP BY class_id;

聚合查询~1.png

示例：查询每个学院的学生数量，并显示显示姓名

SELECT COUNT(*),GROUP_CONCAT(name) FROM student GROUP BY class_id;

聚合查询~2.png

3、使用HAVING关键字设置条件

根据class_id对student表中的数据进行分组，并显示学生数量大于1的分组信息

SELECT COUNT(*),GROUP_CONCAT(name) FROM student GROUP BY class_id HAVING COUNT(*)>1;

聚合查询~3.png

四、使用正则表达式查询

使用REGEXP关键字进行正则表达式的字符匹配。常见的正则表达式字符匹配

选项	说明
^	匹配文本的开始字符
$	匹配文本的结束字符
.	匹配任何单个字符
*	匹配0个或多个在它前面的字符
+	匹配前面的字符1次或多次
<字符集>	匹配包含指定字符的文本
[字符集合]	匹配字符集合中的任何一个字符
[^]	匹配不在括号中的任何字符
字符串{n,}	匹配前面的字符串至少n次
字符串{n,m}	匹配前面的字符串至少n次，至多m次

示例：在student表中，查询学生姓名以"J"开头的学生信息

SELECT * FROM student WHERE name REGEXP '^J';

正则表达式查询~1.png

示例：在student表中，查询学生姓名以"S"结尾的学生信息

SELECT * FROM student WHERE name REGEXP 'S$';

正则表达式查询~2.png

示例：在student表中，查询学生姓名中倒数第二位为"E"的学生信息

SELECT * FROM student WHERE name REGEXP 'E.$';

正则表达式查询~3.png

示例：在student表中，查询学生信息字段值包含字母"J"，并且"J"后面出现字母"A"的学生信息

SELECT * FROM student WHERE name REGEXP '^JA*';

正则表达式查询~4.png

示例：在student表中，查询学生信息字段值包含字母"J"，并且"J"后面出现字母"A"至少一次的学生信息

SELECT * FROM student WHERE name REGEXP '^JA+';

正则表达式查询~5.png