MySQL | 高级查询

1、多表查询

1.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;

1.2、外连接查询

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

1.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; 

1.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;

1.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;

2、子查询

2.1、使用子查询的时机

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

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

2.2.1、IN子查询

IN子查询判断一个给定值是否存在于子查询的结果集中
语法格式：

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

语法说明：

• <表达式>：用于指定表达式。当表达式与子查询返回的结果集中的某个值相等时，返回TRUE，否则返回FALSE；若使用关键字NOT，则返回的值正好相反。
• <子查询>：用于指定子查询。对于比较复杂的查询要求，可以使用SELECT语句实现子查询的嵌套。

2.2.2、比较运算符子查询

比较运算符主要用于对表达式的值和子查询返回的值进行比较运算
语法格式：

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

语法说明：

• <子查询>：用于指定子查询
• <表达式>：用于指定要进行比较的表达式
• ALL、SOME、ANY：可选项。用于指定对比较运算的限制。

2.2.3、EXIST子查询

EXIST子查询主要用于判断子查询的结果集是否为空，如果子查询的结果集不为空，则返回TRUE，否则返回FALSE。
语法格式：

EXIST <子查询>

2.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");

案例：在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");

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

SELECT * FROM student WHERE EXISTS (SELECT id FROM class WHERE name='java');
# EXISTS判断集合是否为空集

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

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

3、分组查询

3.1、使用子查询的时机

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

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

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

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

SELECT COUNT(*) FROM student GROUP BY class_id;

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

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

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

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

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

4、使用正则表达式查询

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

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

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

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

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

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