子查询

一、子查询的定义

子查询 sub query 查询是建立在某个查询结果的基础之上（一条select 语句内部包含了一条select语句）

二、 子查询分类

分为两种：
按位置分类， 按结果分类

• 按位置分类：
  按子查询（select语句）在外部查询（select语句）中出现的位置分类
  （1）from 子查询，子查询语句跟在from之后
  （2）where 子查询，子查询语句出现在where 条件中
  （3）exists 子查询，子查询语句出现在exists里面
• 按结果分类：
  根据子查询得到的数据进行分类：
  （1）标量子查询：子查询得到的结果是一行一列
  （2）列子查询：子查询得到结果是一列多行
  （3）行子查询：子查询得到的结果是一行多列（或者多行多列）
  （4）表子查询：子查询得到的结果是多行多列（出现的位置是在from之后，上面的（1）（2）（3）是出现在where之后）

2.1 标量子查询

示例：
班级表

 CREATE TABLE c_class(
      id int  PRIMARY KEY AUTO_INCREMENT,
      c_name VARCHAR(20) not NULL,
      room VARCHAR (20) not null   
                       ) charset utf8;

插入数据：

INSERT INTO  c_class VALUES(1,'PHP0710','A203'),(2,'PHP0710','A204');

创建学生表

 CREATE TABLE c_student(
     id int  PRIMARY KEY AUTO_INCREMENT,
     number VARCHAR (10) not null,
     name VARCHAR (20) not null,
     sex VARCHAR (2) not null,
     age TINYINT NOT null,
     c_id int not null
     
                       ) charset utf8;

插入表

INSERT INTO c_student VALUES
(5,'it001','张云','男','18',1),
(2,'it002','李四','男','17',2),
(3,'it003','张扬','男','18',3),
(4,'it004','王梅','女','18',2);

屏幕快照 2017-08-11 下午5.52.20.png 屏幕快照 2017-08-11 下午5.52.55.png

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

步骤一：确定数据源，获取所有的学生
select * from c_student where c_id = ?
步骤二：获取班级id ,可以通过名字确定
select id from c_class where c_name = 'PHP0710'

步骤三：组合

SELECT * from c_student where c_id = (SELECT id from c_class WHERE c_name = 'PHP0710');

屏幕快照 2017-08-11 下午5.57.37.png

结果，一行一列，满足标量子查询

2.2 列子查询

需求：查询所有班级在读学生

步骤一：确定数据源：学生
select * from c_student where c_id in(?)
步骤二：确定所有有效班级的班级id
select id from c_class;
步骤三：组合
SELECT * from c_student where c_id in (SELECT id from c_class)

屏幕快照 2017-08-11 下午6.17.16.png

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

• =any <===> in

SELECT * from c_student where c_id =any (SELECT id from c_class)

• any<===>some
  =all -- 全部

2.4 行子查询

返回的结果可以是一行多列也可以是多行多列
需求：要求查处学生中年龄最大且身高最大的学生

步骤一：确定数据源
select * from c_student where age = ? and height = ?;
步骤二：确定最大的年龄，确定最大的身高
select max(age),max(height) from c_student;

步骤三：合并

select * from c_student 
where age = (SELECT max(age) from c_student) 
and 
height = (SELECT  max(height) FROM c_student);

屏幕快照 2017-08-11 下午6.51.15.png 屏幕快照 2017-08-11 下午6.50.49.png

注意，做如上的语句操作时，须保证年龄和升高最大值出现在同一个学生身上，才能查到.

行子查询：构造行元素，由多个行元素构成

select * from c_student where 
-- (age,height) 称之为行元素
(age,height) = (SELECT max(age),MAX(height) from c_student);

屏幕快照 2017-08-11 下午7.00.25.png

2.5表子查询

表子查询返回的结果是多行多列的二维表，子查询返回的结果当多二维表来使用
需求：找出每个班身高最高的学生
步骤一：
确定数据源，先将学生按照身高降序排列

select * from c_student orderby height desc;

步骤二：
从每个班选出第一个学生
select * from c_student group by c_id;
步骤三：
第一步查询的结果作为步骤二的数据源进行处理
from 子查询

select * from (select * from c_student ORDER BY  height desc) as student GROUP  BY c_id;

屏幕快照 2017-08-12 上午11.25.35.png 屏幕快照 2017-08-12 上午11.25.26.png

2.6 exsits 子查询

用来判断某些条件是否满足，往往是跨表。exists 是接在where之后。
exists返回的结果只有两个（不是boolean类型）0和1
需求：查看所有学生，前提是班级存在的学生
步骤一：确定数据源：
select * from c_student where ?;
步骤二：条件是否满足
exists(select * from c_class);--是否成立
步骤三：
合并

select * from c_student where 
exists
(select * from c_class);

select * from c_student where 
exists
(select * from c_class where `c_id` =5);

屏幕快照 2017-08-12 上午11.40.05.png