一、子查询 in
1：子查询是将一个查询语句嵌套在另一个查询语句中。
2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
4：还可以包含比较运算符：= 、 !=、> 、
1 带IN关键字的子查询
子查询的思路
select * from emp where dep_id in
(select id from dep where name="技术" or name="销售");
链表的思路
select * from emp inner join dep
on emp.dep_id = dep.id
where dep.name in ("技术","销售");
not in 无法处理null的值，即子查询中如果存在null的值，not in将无法处理
插入一条dep_id为空的记录
mysql> insert into emp values(7,'lili','female',48,null);
Query OK, 1 row affected (0.03 sec)
mysql> select * from emp
-> ;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
| 7 | lili | female | 48 | NULL |
+----+------------+--------+------+--------+
7 rows in set (0.00 sec)
查询出有员工的部门，
select * from dep where id in
(select distinct dep_id from emp);
查询出没有员工的部门，
select * from dep where id not in
(select distinct dep_id from emp);
解决方案如下
select * from dep where id not in
(select distinct dep_id from emp where dep_id is not null);
2、带any关键字的子查询
在SQL中 any 和some 是同义词，用法和功能和any一样
any后也跟子查询语句，与in不一样的地方在哪里
in (子查询语句)
in (值1,值2,值3)
而any只能跟子查询语句
any必须跟比较运算符配合使用
ANY 必须和其他的比较运算符共同使用，而且ANY必须将比较运算符放在 ANY 关键字之前，
所比较的值需要匹配子查询中的任意一个值，这也就是 ANY 在英文中所表示的意义
select * from emp where dep_id in
(select id from dep where name in ("技术","人力资源"));
select * from emp where dep_id = any
(select id from dep where name in ("技术","人力资源"));
select * from emp where dep_id not in
(select id from dep where name in ("技术","人力资源"));
select * from emp where ! (dep_id = any(select id from dep where name in ("技术","人力资源")));
使用 IN 和使用 ANY运算符得到的结果是一致的
也就是说“=ANY”等价于 IN 运算符，而“<>ANY”则等价于 NOT IN 运算符
3 带ALL关键字的子查询
all同any类似，只不过all表示的是所有，any表示任一
查询出那些薪资比所有部门的平均薪资都高的员工=》薪资在所有部门平均线以上的狗币资本家
select * from employee where salary > all
(select avg(salary) from employee where depart_id is not null group by depart_id);
查询出那些薪资比所有部门的平均薪资都低的员工=》薪资在所有部门平均线以下的无产阶级劳苦大众
select * from employee where salary < all
(select avg(salary) from employee where depart_id is not null group by depart_id);
查询出那些薪资比任意一个部门的平均薪资高的员工=》薪资在任一部门平均线以上的员工
select * from employee where salary > any
(select avg(salary) from employee where depart_id is not null group by depart_id);
查询出那些薪资比任意一个部门的平均薪资低的员工=》薪资在任一部门平均线以下的员工
select * from employee where salary < any
(select avg(salary) from employee where depart_id is not null group by depart_id);
4 带比较运算符的子查询
比较运算符：=、!=、>、>=、
查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from emp where age > (select avg(age) from emp);
+---------+------+
| name | age |
+---------+------+
| alex | 48 |
| wupeiqi | 38 |
+---------+------+
2 rows in set (0.00 sec)
查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from emp t1
inner join
(select dep_id,avg(age) avg_age from emp group by dep_id) t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age;
5 带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
语法
select * from 表1 where exists (select * from 表2);
5.1 in与exists
！！！！！！当in和exists在查询效率上比较时，in查询的效率快于exists的查询效率！！！！！！
exists
exists后面一般都是子查询，后面的子查询被称做相关子查询(即与主语句相关)，当子查询返回行数时，exists条件返回true，
否则返回false，exists是不返回列表的值的，exists只在乎括号里的数据能不能查找出来，是否存在这样的记录。
例
查询出那些班级里有学生的班级
select * from class where exists (select * from stu where stu.cid=class.id)
exists的执行原理为：
1、依次执行外部查询：即select * from class
2、然后为外部查询返回的每一行分别执行一次子查询：即(select * from stu where stu.cid=class.cid)
3、子查询如果返回行，则exists条件成立，条件成立则输出外部查询取出的那条记录
in
in后跟的都是子查询，in()后面的子查询 是返回结果集的
例
查询和所有女生年龄相同的男生
select * from stu where sex='男' and age in(select age from stu where sex='女')
in的执行原理为：
in()的执行次序和exists()不一样,in()的子查询会先产生结果集,
然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出.
例如：查询有员工的部门=》
select * from dep where exists (select * from emp where dep.id=emp.dep_id);
5.2 not in与 not exists
not exists的效果 高于 not in
not in()子查询的执行顺序是：
为了证明not in成立，即找不到，需要一条一条地查询表，符合要求才返回子查询的结果集，
不符合的就继续查询下一条记录，直到把表中的记录查询完，只能查询全部记录才能证明，并没有用到索引
not exists：对结果取反，没有返回值才为真
就是对exists完全取反，下面的循环语句中全部满足才为真，有一个不满足就是假
select * from dep where not exists (select * from emp where 203=emp.dep_id);
例：查询选修了所有课程的学生id、name:
实现方式一：选修了三门课程的学生就是选修了所有课程的学生
select s.id,s.name from student as s inner join student2course as sc
on s.id = sc.sid
group by sc.sid
having count(sc.cid) = (select count(id) from course);
实现方式二：找到这样的学生，该学生不存在没有选修过的课程
select * from student as s where not exists (
select * from course as c not exists (
select * from student2course as sc where sc.sid = s.id and sc.cid = c.id
)
);
select * from student as s where not exists (
select * from course as c where not exists (
select * from student2course as sc where sc.sid = s.id and sc.cid = c.id
)
);
学生记录可以过滤出来，一定是子查询内没有记录
for 学生: # s.id=2
for 课程: # c.id=1
for 学生2课程: # sc.sid = 2 and sc.cid = 1
pass
==================================
for sid in [1,2,3,4]:
for cid in [1,2,3]:
(sid,cid)
最外层循环一次
# (1,1)
# (1,2)
# (1,3)
最外层循环二次
# (2,1)
# (2,2)
# (2,3)
最外层循环三次
# (3,1)
# (3,2)
# (3,3)
最外层循环四次
# (4,1)
# (4,2)
# (4,3)
===================================
例2、查询没有选择所有课程的学生，即没有全选的学生。=》找出这样的学生，存在没有选修过的课程
select * from student as s where exists (
select * from course as c where not exists (
select * from student2course as sc where sc.sid = s.id and sc.cid = c.id
)
);
例3、查询一门课也没有选的学生=》找出这样的学生，不存在选修过的课程
select * from student as s where not exists (
select * from course as c where exists (
select * from student2course as sc where sc.sid = s.id and sc.cid = c.id
)
);
例4、查询至少选修了一门课程的学生=》找出这样的学生，存在选修过课程
select * from student as s where exists (
select * from course as c where exists (
select * from student2course as sc where sc.sid = s.id and sc.cid = c.id
)
);
