MySQL子查询

子查询

sub query：一条select语句内部包含了另外一条select语句

create table student(
	id int(5) auto_increment primary key,
	name varchar(20) not null,
	age int(2) not null,
	height int(3) not null,
	c_id int(3)
)auto_increment=20201;

select * from student;
drop table student;

insert into student values (null,'绣花',18,167,null),(null,'绣花',19,190,405),(null,'张三',20,172,401),(null,'李四',23,185,403),(null,'王五',19,172,402),(null,'孙六',18,179,401),(null,'米粉',20,189,402),(null,'团宠',23,190,402),(null,'猛男',18,150,403),(null,'绣花',19,180,402);

create table clazz(
	c_id int(3) auto_increment primary key,
	c_name varchar(20) not null
)auto_increment = 401;

insert into clazz values(null,'Web'),(null,'Java'),(null,'Spring'),(null,'Docker');

select * from clazz;

分类

方式：位置、结果

位置

子查询（select语句）在外部查询（select语句）中的位置

​ from子查询：子查询跟在from之后

​ where子查询：子查询出现where条件中

​ exists子查询：子查询出现在exists中

结果

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

​ 标量子查询：子查询的结果是一行一列

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

​ 行子查询：子查询得到的结果是多列一行（多行多列）

​ 以上都是where子查询

​ 表子查询：子查询得到的结果是多行多列（出现的位置是from之后）

标量子查询

需求：知道班级名字为Java，查询该班级的所有学生

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

   select * from student where c_id =?;

2. 获取班级ID，可以通过班级名字确定

   select c_id from clazz where c_name = ‘Java’;

3. 整合

   select * from student where c_id =（select c_id from clazz where c_name = 'Java'）；
   

列子查询

需求：查询在读班级的所有学生【班级表中存在的班级】

1. 确定数据源:学生

   select * from student where c_id in(?);

2. 确定现有班级

   select c_id from clazz;

3. 整合

   select * from student where c_id in(select c_id from clazz);
   

   一列多行，需要使用in作为条件匹配，在mysql中还有类似的条件 all any some

   1. 需要加比较操作符
   2. any 和some 在肯定语句中一样，在否定句中，some和not some 一样，any和not any不一样。

   select * from student where c_id =all(select c_id from clazz);
   select * from student where c_id =any(select c_id from clazz);
   select * from student where c_id =some(select c_id from clazz);
   -- null不参与比较
   select * from student where c_id !=all(select c_id from clazz);
   select * from student where c_id !=any(select c_id from clazz);
   select * from student where c_id !=some(select c_id from clazz);
   
   不需要纠结，开发中没见到谁用，但是尽可能去用，用in去解决这些问题
   

行子查询

一行多列，多行多列

需求：查询出年龄最大，身高最高的学生

1. 确定数据源：学生

   select *from student where age = ? and height = ?;

2. 确定最大年龄和最高身高

   select max(age),max(height) from student;

-- 整合【两个标量子查询实现】
select * from student where age = (select max(age) from student)
and height = (select max(height) from student);

-- 行子查询，需要构建行元素
select * from student where (age,height) = (select max(age),max(height) from student);

-- 当前情况的另一种办法
select * from student order by age desc,height desc limit 1;
-- 缺点：不同时满足最大和最高，数据是乱的，数据量级，同时满足的多条数据。

表子查询【from子查询】

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

标量子查询：一行一列，当做一个 变量来用

列子查询：一列多行，当做集合来用

行子查询：一行多列，一行一行来用，当做map来用，构造行元素

需求：查询每一个班中最高的一个学生

1. 确定数据源

   select * from student group by c_id order by height desc;-- 先分组，再排序，错误的

   -- 正确：先将学生按照身高进行降序排序
   select * from student order by height desc;
   

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

   select * from (select * from student order by height desc limit 0,9) as students group by c_id 
   -- 5.7中表子查询中，order by 需要跟limit，否则无效
   

exists子查询【考点】

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

1. 确定数据源

   select * from student where ?

2. 确定条件是否满足

   exists(select * from clazz)

   -- 整合
   select * from student where exists (select * from clazz)
   

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False
EXISTS 指定一个子查询，检测 行 的存在。

语法： EXISTS subquery
参数： subquery 是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)。
结果类型： Boolean 如果子查询包含行，则返回 TRUE ，否则返回 FLASE 。

结论：
EXISTS(包括 NOT EXISTS )子句的返回值是一个BOOL值。 EXISTS内部有一个子查询语句(SELECT … FROM…)， 我将其称为EXIST的内查询语句。其内查询语句返回一个结果集。 EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。

一种通俗的可以理解为：将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行可作为外查询的结果行，否则不能作为结果。

在插入记录前，需要检查这条记录是否已经存在，只有当记录不存在时才执行插入操作，可以通过使用 EXISTS 条件句防止插入重复记录。
INSERT INTO TableIn (ANAME,ASEX)
SELECT top 1 ‘张三’, ‘男’ FROM TableIn
WHERE not exists (select * from TableIn where TableIn.AID = 7)

EXISTS与IN的使用效率的问题，通常情况下采用exists要比in效率高，因为IN不走索引，但要看实际情况具体使用：
IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

当in和exists在查询效率上比较时，in查询的效率快于exists的查询效率

执行顺序如下：
1.首先执行一次外部查询
2.对于外部查询中的每一行分别执行一次子查询，而且每次执行子查询时都会引用外部查询中当前行的值。
3.使用子查询的结果来确定外部查询的结果集。
如果外部查询返回100行，SQL 就将执行101次查询，一次执行外部查询，然后为外部查询返回的每一行执行一次子查询。

not in的执行顺序是：是在表中一条记录一条记录的查询(查询每条记录）符合要求的就返回结果集，不符合的就继续查询下一条记录，直到把表中的记录查询完。也就是说为了证明找不到，所以只能查询全部记录才能证明。并没有用到索引。

not exists的执行顺序是：在表中查询，是根据索引查询的，如果存在就返回true，如果不存在就返回false，不会每条记录都去查询。
之所以要多用not exists，而不用not in，也就是not exists查询的效率远远高与not in查询的效率。