MySQL（八）—— 联合查询和子查询

目录

联合查询

基本语法

意义

Order by使用

子查询

子查询分类

标量子查询

列子查询

行子查询

表子查询

Exists子查询

---

联合查询

联合查询: 将多次查询(多条select语句), 在记录上进行拼接(字段不会增加)

基本语法

多条select语句构成: 每一条select语句获取的字段数必须严格一致(但是字段类型无关)

Select 语句1

Union [union选项]

Select语句2...

Union选项: 与select选项一样有两个

All: 保留所有(不管重复)

Distinct: 去重(整个重复): 默认的

-- 联合查询 默认去重
select * from my_class
union -- 默认去重
select * from my_class;

-- 不去重
select * from my_class
union all -- 不去重
select * from my_class;

联合查询只要求字段一样, 跟数据类型无关

select id,c_name,room from my_class
union all -- 不去重
select name,number,id from my_student;

意义

联合查询的意义分为两种:

1. 查询同一张表,但是需求不同: 如查询学生信息, 男生身高升序, 女生身高降序.

2. 多表查询: 多张表的结构是完全一样的,保存的数据(结构)也是一样的.

 

Order by使用

在联合查询中: order by不能直接使用,需要对查询语句使用括号才行

-- 需求: 男生升序,女生降序(年龄)
(select * from my_student where sex = '男' order by age asc)
union 
(select * from my_student where sex = '女' order by age desc);

若要orderby生效: 必须搭配limit: limit使用限定的最大数即可.

-- 需求: 男生升序,女生降序(年龄)
(select * from my_student where sex = '男' order by age asc limit 9999999)
union 
(select * from my_student where sex = '女' order by age desc limit 9999999);

 

子查询

子查询: sub query, 查询是在某个查询结果之上进行的.(一条select语句内部包含了另外一条select语句).

子查询分类

子查询有两种分类方式: 按位置分类; 按结果分类

按位置分类: 子查询(select语句)在外部查询(select语句)中出现的位置

From子查询: 子查询跟在from之后

Where子查询: 子查询出现where条件中

Exists子查询: 子查询出现在exists里面

按结果分类: 根据子查询得到的数据进行分类(理论上讲任何一个查询得到的结果都可以理解为二维表)

标量子查询: 子查询得到的结果是一行一列

列子查询: 子查询得到的结果是一列多行

行子查询: 子查询得到的结果是多列一行(多行多列)

上面几个出现的位置都是在where之后

表子查询: 子查询得到的结果是多行多列(出现的位置是在from之后)

标量子查询

需求: 知道班级名字为PHP0710,想获取该班的所有学生.

1. 确定数据源: 获取所有的学生

Select * from my_student where c_id = ?;

1. 获取班级ID: 可以通过班级名字确定

Select id from my_class where c_name = ‘PHP0710’; -- id一定只有一个值(一行一列)

标量子查询实现

-- 标量子查询
select * from my_student where c_id = (select id from my_class where c_name = 'PHP0710');

列子查询

需求: 查询所有在读班级的学生(班级表中存在的班级)

1. 确定数据源: 学生

Select * from my_student where c_id in (?);

1. 确定有效班级的id: 所有班级id

Select id from my_class;

列子查询

-- 列子查询
select * from my_student where c_id in(select id from my_class);

列子查询返回的结果会比较: 一列多行, 需要使用in作为条件匹配: 其实在mysql中有还有几个类似的条件: all, some, any

Any  ====  in; -- 其中一个即可

Any ====== some; -- any跟some是一样

all    ==== 为全部

肯定结果

-- any,some,all
select * from my_student where c_id =any(select id from my_class);
select * from my_student where c_id =some(select id from my_class);
select * from my_student where c_id =all(select id from my_class);

否定结果

select * from my_student where c_id !=any(select id from my_class); -- 所有结果(null除外)
select * from my_student where c_id !=some(select id from my_class); -- 所有结果(null除外)
select * from my_student where c_id !=all(select id from my_class); -- 2(null除外)

行子查询

行子查询: 返回的结果可以是多行多列(一行多列)

需求: 要求查询整个学生中,年龄最大且身高是最高的学生.

1. 确定数据源

Select * from my_student where age = ? And height = ?;

1. 确定最大的年龄和最高的身高;

Select max(age),max(height) from my_student;

行子查询: 需要构造行元素: 行元素由多个字段构成

-- 行子查询
select * from my_student where 
-- (age,height)称之为行元素
(age,height) = (select max(age),max(height) from my_student);

 

表子查询

表子查询: 子查询返回的结果是多行多列的二维表: 子查询返回的结果是当做二维表来使用

需求: 找出每一个班最高的一个学生.

1. 确定数据源: 先将学生按照身高进行降序排序

Select * from my_student order by height desc;

1. 从每个班选出第一个学生

Select * from my_student group by c_id; -- 每个班选出第一个学生

表子查询: from子查询: 得到的结果作为from的数据源

-- 表子查询
select * from (select * from my_student order by height desc) as student group by c_id;

Exists子查询

Exists: 是否存在的意思, exists子查询就是用来判断某些条件是否满足(跨表), exists是接在where之后: exists返回的结果只有0和1.

需求: 查询所有的学生: 前提条件是班级存在

1. 确定数据源

Select * from my_student where ?;

1. 确定条件是否满足

Exists(Select * from my_class); -- 是否成立

Exists子查询

-- exists子查询
select * from my_student where 
exists(select * from my_class where id = 1);

select * from my_student where 
exists(select * from my_class where id = 2);