Mysql中子查询,用法与注意点

Mysql中的子查询

子查询

什么是子查询:

# 1: 子查询就是将查询语句嵌套在另外一个语句中.
# 2: 内层查询语句的查询结果,可以为外层查询语句提供查询条件.
# 3: 子查询可以包含: IN,NOT IN, ANY ,ALL,EXISTS和 NOT EXISTS等关键字
# 4: 还可以包含比较运算符: = , != , > , <等等

1 带in关键字的子查询

#查询平均年龄在25岁以上的部门名
select id,name from dep 
where id in 
(select dep_id from emp group by dep_id having avg(age)>25);

#查看技术部员工姓名
select name from emp where
dep_id in
(select id from dep where name='jishu');

#查看员工数小于等于一人的部门名
select id,name from dep
where id in 
(select dep_id from emp group by dep_id having count(id)<=1);

2 带比较运算符的子查询

#比较运算符：=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
select name ,age from emp where age>(select avg(age) from emp);
+---------+------+
| name    | age  |
+---------+------+
| alex    |   48 |
| wupeiqi |   38 |
+---------+------+

#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age,t1.dep_id from emp as t1 
inner join 
(select dep_id,avg(age) as avg_age from emp group by dep_id) as t2
on t1.dep_id = t2.dep_id
where t1.age > t2.avg_age; 
+------+------+--------+
| name | age  | dep_id |
+------+------+--------+
| alex |   48 |    201 |
+------+------+--------+

select name,age,dep_id from emp group by dep_id having age>avg(age);

3 带ANY关键字的子查询

# any 和 in运算符不同之处1
any 必须和其他的比较运算符共同使用，而且any必须将比较运算符放在 any 关键字之前，所比较的值需要匹配子查询的任意一个值。
例如：使用 in 和 使用 any 运算符得到的结果是一致的
SELECT * FROM emp WHERE salary = ANY
(SELECT MAX(salary) FROM emp GROUP BY post);

SELECT * FROM emp WHERE salary IN
(SELECT MAX(salary) FROM emp GROUP BY post);

结论：也就是说“=any”等价于 in 运算符，而"<>any"则等价于 not in 运算符

# ANY和 IN 运算符不同之处2
ANY 运算符不能与固定的集合相匹配，比如下面的 SQL 语句是错误的
SELECT * FROM emp WHERE salary < ANY(10000,12000,20000);

4 带ALL关键字的子查询

# all 同 any 类似，只不过 all 表示的是所有， any 表示任一
查询出那些薪资比所有部门的平均薪资都高的员工（比all语句中最高薪资要高即可）
SELECT name FROM emp WHERE salary> ALL
(SELECT AVG(salary) FROM emp GROUP BY post);


同样将all换上any，则表示比任何一个员工平均薪资高即可满足条件(比any语句中的最低薪资高即可)
SELECT name FROM emp WHERE salary> ANY
(SELECT AVG(salary) FROM emp GROUP BY post);

查询出那些薪资比所有部门的平均薪资都低的员工（比all语句中最低薪资要低即可）
SELECT name FROM emp WHERE salary< ALL
(SELECT AVG(salary) FROM emp GROUP BY post);

同样将all换上any，则表示比任何一个员工平均薪资低即可满足条件(比any语句中的最高薪资低即可)
SELECT name FROM emp WHERE salary< ANY
(SELECT AVG(salary) FROM emp GROUP BY post);

5 带EXISTS关键字的子查询

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

#department表中存在dept_id=203，Ture
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=200);
+----+------------+--------+------+--------+
| 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 |
+----+------------+--------+------+--------+

#department表中存在dept_id=205，False
mysql> select * from employee
    ->     where exists
    ->         (select id from department where id=204);
Empty set (0.00 sec)

5.1 in与exists

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

#exists
exists后面一般都是子查询，后面的子查询被称作相关子查询（即与主语句相关），当子查询返回行数时，exists条件返回true，否则返回false，exists是不返回列表的值的，exists只在乎括号内的数据能不能查找出来，是否存在这样的记录

#例
查询出那些班级里有学生的班级（只要学生列表中有一个学生存在该班级的id即可）
SELECT * FROM class where EXISTS
(SELECT * FROM student where class.cid=class_id);

#exists的执行原理：
1.依次执行外部查询：即select * from class
2.然后为外部查询返回每一行分别执行一次子查询
通俗的讲，在外部class表中拿一个字段下的记录class.cid到内部student表中与class_id下的每一个记录比对，比对成功则返回结果true,对比失败返回false（就是class.cid配对了student表class_id下的所有的记录都失败了）
3.子查询如果返回行，则exists条件成立，条件成立则输出外部查询取出的那条记录


# in
in后跟的都是子查询，in()后面的子查询 是返回结果集的
# 例
查询和所有女生年龄相同的男生
select * from student where sex='男' and age in(select age from stu where sex='女')

# in的执行原理为：
in()的执行次序和exists()不一样,in()的子查询会先产生结果集,
然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出.

相比之下exists每一次字段的访问都要执行一遍exists语句中的指令，
而in则是先执行完in里面的指令集并产生硬性的结果，然后再主查询的每一次字段访问的比对。

erists:
主查询执行调用一个字段下的记录去访问======>运行exists中的语句相应字段下的记录与主查询的记录进行配比直到成功或是指令运行完毕才结束=========>返回结果给子查询，判断该字段TRUE或FALSE(即该记录会不会出现在表中)

in:
先执行in中的语句产生一个结果集======>运行主查询，主查询调用与结果集相对应字段下的一个记录去与结果集进行配比=========>成功则返回，那该记录成功写入字段下，失败则相反。

5.2 not in 与 not exists

not exists 查询的效率远远高于not in查询效率

#not in
为了证明not in成立，即找不动，需要一条一条的查询表，符合要求才返回子查询的结果，不符合的就继续查询下一条记录，直到把表中的记录查询完，只能查询全部记录才能证明，并没有用到索引。


#not exists
如果主查询表中的记录少，子查询中的记录多，并有索引。
例如：查询那些班级中没有学生的班级
SELECT * FROM class where not EXISTS
(SELECT * FROM student where class.cid=class_id);

not exists的执行顺序是：
在表中查询，是根据索引查询的，如果存在就返回true，如果不存在就返回false，不会每条记录都去查询。

前戏

create database dbtest;

use dbtset;

create table student(
    id int primary key auto_increment,
    name varchar(16)
);

create table course(
    id int primary key auto_increment,
    name varchar(16),
    comment varchar(20)
);

create table student2course(
    id int primary key auto_increment,
    sid int,
    cid int,
    foreign key(sid) references student(id),
    foreign key(cid) references course(id)
);


insert into student(name) values
("egon"),
("lili"),
("jack"),
("tom");

insert into course(name,comment) values
("数据库","数据仓库"),
("数学","根本学不会"),
("英语","鸟语花香");


insert into student2course(sid,cid) values
(1,1),
(1,2),
(1,3),
(2,1),
(2,2),
(3,2);

示例

# 1、查询选修了所有课程的学生id、name:（即该学生根本就不存在一门他没有选的课程。）

#不存在一门都没有选修的即是选修了所有门
SELECT * FROM student WHERE  not EXISTS
(SELECT * FROM course WHERE NOT EXISTS  #结果为一门都没有选修到返回上层并被取反即是都选修到
(SELECT * FROM student2course WHERE sid=student.id and cid=course.id)); 
#底层结果为真，上层有一行为假，上层每一行为假，则上上层有一行为真

# 2、查询没有选择所有课程的学生，即没有全选的学生。（存在这样的一个学生，他至少有一门课没有选）
SELECT * FROM student WHERE   EXISTS
(SELECT * FROM course WHERE NOT EXISTS  
(SELECT * FROM student2course WHERE sid=student.id and cid=course.id)); 

# 3、查询一门课也没有选的学生。（不存这样的一个学生，他至少选修一门课程）
	SELECT * FROM student WHERE   not EXISTS
	(SELECT * FROM course WHERE  EXISTS  
	(SELECT * FROM student2course WHERE sid=student.id and cid=course.id)); 


# 4、查询至少选修了一门课程的学生。
SELECT * FROM student WHERE   EXISTS
(SELECT * FROM course WHERE  EXISTS  
(SELECT * FROM student2course WHERE sid=student.id and cid=course.id)); 


#注意：
exists(): 有一个匹对成功,条件就成立
not exists():有一个匹对失败,条件就成立