10 mysql高级查询：联合查询union，内连接inner join，外连接left join，子查询
1 联合查询：合并结果集-不推荐
概念
联合查询就是： 将多个查询的结果合并到一起（纵向合并）：字段不变，多个查询的记录数合并（产生的结果是笛卡尔积）
等同于将一个表追加到另一个表，从而实现将两个表的查询组合到一起的效果，使用关键字 union 和 union all
语法
select 语句
union [union 选项]
select 语句;
-- union选项
distinct：去掉完全重复的数据（默认）
all：保存所有的结果
在联合查询中，如果要使用 order by，那么对应的select 语句 必须使用括号括起来，同时必须结合limit使用
limit的数量：通常可以使用一个较大的值，大于对应的表的记录数
应用场景
1、 将同一张表中的不同结果（需要对应多条查询语句来实现），合并到一起展示数据
-- 获取男生成绩降序 女生的成绩升序的结果集
(SELECT * from student WHERE gender='男' ORDER BY score desc)
UNION
(SELECT * from student WHERE gender='女' ORDER BY score);
2、 常用--在数据量很大的时候，通常会进行分表操作，需要对每张表进行部分数据的统计，就需要使用联合查询将结果合并到一起展示
2 连接查询--推荐 内连接join
概念
连接查询：将多张表连接在一起进行查询：会导致字段列表和记录数都发生变化。
在关系型数据库设计的过程中，实体(表)和实体之间存在很多联系：一对一、多对一、多对多，通常在实际操作过程中，我们需要利用这层关系，来保证数据的完整性
2.1 内连接(inner join)：on 匹配条件--推荐
内连接：inner join，从一张表中取出所有的记录去另外一张表中进行有条件的匹配，符合条件的数据保留
注意：
内连接必须要有匹配条件 on，否则么有任何意义
表1和表2之间，必须要有关联
原理
- 1 从一张表取出一条记录，然后去另外一张表中进行匹配
- 2 利用匹配条件进行匹配
- 2.1 匹配到数据：保留此条数据，继续向下匹配
- 2.2 没有匹配到数据：向下继续匹配，直到全表匹配完毕
语法
-- inner 可以省略
表1 [inner] join 表2 on 匹配条件;
-- 查询学生所在班级及其所在班级的名称
-- 因为表的设计通常会容易产生同名字段，为了保证数据的唯一性，可以使用 表名.字段名 来确保唯一性
-- 如果条件中使用的表名比较长，一般会使用表别名来替换
SELECT stu.name '姓名',c.name '班级' from student stu join class c on stu.class_id=c.id;
应用场景
内连接通常是在对数据有精确要求的地方使用：必须保证两表中都可以匹配到数据
2.2 外连接(outer join)：on 匹配条件
外连接：一张表作为主表（主表中所有记录都保留），根据条件去连接另外一张表（从表），得到目标数据
外连接主要分为左外连接(left join)和右外连接(right join)
左连接：左侧的表是主表
右连接：右侧的表是主表
外连接中主表的数据记录一定会保留，连接后不会出现记录数小于主表记录数的情况（内连接会，因为内连接会把不符合条件的过滤掉）
原理
1、 确定连接主表：左连接：左侧的表是主表；右连接：右侧的表是主表
2、 拿主表的每一条记录去匹配从表的每一条记录
3、 匹配条件满足，保留记录；否则不保留记录
4、 如果主表记录在从表中一条记录都没有匹配成功，那么也要保留该记录，只不过从表对应的字段值都是NULL
语法
-- 左连接：
主表 left join 从表 on 条件
-- 右连接：
从表 left join 主表 on 条件
-- 左连接对应的主表数据在左边
-- 右连接对应的主表数据在右边
-- 班级信息表是主表，保留所有记录，学生信息表示从表，如果没有匹配到数据，从表对应的数值都是null
SELECT * from class c left join student stu on stu.class_id=c.id;
2.3 连接查询-using关键字--少用
主要是在连接查询中，用来替换 on 关键字进行条件匹配的
使用using的时候，对应2张表中连接的字段必须是同名的
原理
1、 在连接查询的时候，使用on的地方使用 using 替换
2、 使用using的前提是：两张对应关联的表连接的字段必须是同名的
3、 使用using关键字后，对应的同名字段只会保留一个
语法
表1 [inner,left,roght] join 表2 using(同名字段)
2.4 交叉连接(cross join)-不推荐
交叉连接：将一张表的数据与另一张表的数据进行彼此交叉
交叉连接产生的结果是笛卡尔积，会产生较大的结果集，没有实际应用
原理
1、 从第一张表依次取出每一条记录
2、 取出每一条记录后，与另外一个表的全部记录逐个匹配
3、没有任何匹配条件，所有的结果都会进行保留
4、记录数=第一张表的记录数 * 第二张表的记录数，字段数=第一张表的字段数+第二张表的字段数
语法
表1 cross join 表2;
-- 连接查询：学生信息表 和 班级信息表
SELECT * from student CROSS JOIN (SELECT * from class) as class;
子查询
子查询是sql查询语言中嵌套查询下层的程序模块。就是当一个查询是另外一个查询的条件时，这个查询就是子查询
子查询：在一条select查询语句中，嵌入了另外一条select语句，那么被嵌入的这个select语句是子查询语句
主查询
主查询：主要的查询对象，第一条select语句，确定用户所获取的数据模板（数据源）
与子查询的关系：
- 子查询是嵌入到主查询中的
- 子查询主要是辅助主查询的
- 子查询也是可以独立存在的，因为子查询是一条完整的select语句
子查询的分类
按照功能分类
-
标量子查询：子查询返回的结果是一个数据（一行一列）
-
列子查询：子查询返回的结果是一列数据（一列多行，同一个字段不同的值）
-
行子查询：子查询返回的结果是一行数据（一行多列，就是完整的一条记录）
-
表子查询：子查询返回的结果是多行多列表
-
exists子查询：返回的结果是1或0（类似布尔操作）
按照位置分类
where子查询（较多）：子查询出现的位置在where条件中
from子查询：子查询出现的位置在from后面，作为数据源使用
标量子查询--where子查询
标量子查询：子查询得到的结果是一个数据（一行一列）
-- 语法：
-- 通常使用 = 或 <> 关键字，获取一个数据
-- 子查询select 字段名 from 数据源 where 条件 得到的结果只有一个值
select * from 数据源 where 条件 =或者<> (select 字段名 from 数据源 where 条件);
-- 查询 张三所在班级的名称
-- 标量子查询语句：SELECT class_id from student WHERE name='张三'; 返回一个数据
SELECT name from class WHERE id=(SELECT class_id from student WHERE name='张三');
列子查询--where子查询
列子查询：子查询返回的结果是一列数据（一列多行，同一个字段不同的值）
-- 语法
-- 通常使用 in 关键字
主查询 where in(列子查询)
SELECT * from student WHERE class_id in(SELECT id from class);
行子查询--where子查询
行子查询：子查询返回的结果是一行数据（一行多列，就是完整的一条记录）
行元素：字段元素是指一个字段对应的值。行元素就是对应多个字段合起来作为一个元素参与运算。
-- 语法
主查询 where 条件(构造一个行元素)=(行子查询)
-- 获取班级中，年龄最小且分数最高的学生
-- 1 查询 年龄最小的
-- 2 查询 分数最高的
-- 3 查询 龄最小且分数最高的学生
SELECT * from student WHERE(score,age)=(SELECT max(score),min(age) from student);
--等价与下面的：
SELECT * from student WHERE age=(SELECT min(age) from student) and score=(SELECT max(score) from student);
-- 行子查询 结果
SELECT max(score),min(age) from student;
表子查询--from子查询
表子查询：子查询返回的结果是多行多列
表子查询与行子查询类似，只是行子查询会产生行元素，表子查询没有行元素
行子查询主要用于where条件判断：where子查询
表子查询主要用于from数据源：from子查询
-- 语法：
select 字段列表 from (表子查询) as 别名;
-- 获取每个班级中分数最高的学生
-- 1 获取每班成绩最高的分数
SELECT class_id,max(score) as score from student GROUP BY class_id;
-- 2 针对1种结果 进行合并查询
-- 查询每个班级的最高成绩学生信息
SELECT *
FROM student s1
JOIN (
SELECT class_id, MAX(score) as score
FROM student
GROUP BY class_id
) s2 ON s1.class_id = s2.class_id AND s1.score = s2.score
ORDER BY s1.class_id, s1.score DESC;
exists 子查询
exists 子查询：查询返回的结果只有1或0
-- 语法：
-- exists根据查询得到的结果进行判断，如果结果存在，返回1；否则返回0
where exists(查询语句);
子查询中的特殊关键字
in--常用
主查询 where 条件 in(列子查询);
any
-- 任意一个，此时等价与 in
=any(列子查询)：条件在查询结果中任意一个满足即可
<>any(列子查询)：条件在查询结果中不等于任意一个
some
some与any一样
