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():有一個匹對失敗,條件就成立
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)