一、子查询 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
    )
);

2020-09-08MySQL多表联合查询之子查询