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

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

©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 205,386评论 6 479
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 87,939评论 2 381
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 151,851评论 0 341
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 54,953评论 1 278
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 63,971评论 5 369
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,784评论 1 283
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 38,126评论 3 399
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,765评论 0 258
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 43,148评论 1 300
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,744评论 2 323
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,858评论 1 333
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,479评论 4 322
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 39,080评论 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 30,053评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,278评论 1 260
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 45,245评论 2 352
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,590评论 2 343