MySql基礎(chǔ)-子查詢

一、子查詢定義

定義：

子查詢允許把一個查詢嵌套在另一個查詢當(dāng)中其监。
子查詢用()括起來
子查詢，又叫內(nèi)部查詢限匣，相對于內(nèi)部查詢抖苦，包含內(nèi)部查詢的就稱為外部查詢。
子查詢可以包含普通select可以包括的任何子句米死，比如：distinct锌历、 group by、order by峦筒、limit究西、join和union等；但是對應(yīng)的外部查詢必須是以下語句之一：select物喷、insert卤材、update、delete峦失、set或者do扇丛。

子查詢的位置：

select后(標(biāo)量子查詢)、from 后(表子查詢)宠进、where或having條件中(標(biāo)量,行,列子查詢) 晕拆、exists后面（相關(guān)子查詢,表子查詢）
group by 和order by 中無實用意義。

二材蹬、子查詢分類:

按結(jié)果集的行列數(shù)分類：

標(biāo)量子查詢：返回單一值的標(biāo)量实幕，最簡單的形式。
列子查詢：返回的結(jié)果集是 N 行一列堤器。
行子查詢：返回的結(jié)果集是一行 N 列昆庇。
表子查詢：返回的結(jié)果集是 N 行 N 列。

可以使用的操作符：= > < >= <= <> ANY IN SOME ALL EXISTS

按子查詢返回值的數(shù)量分類：

標(biāo)量子查詢：會返回一個值闸溃，一般搭配著< >= <= = <>使用
多值子查詢：會返回一列整吆、一行或者一個表拱撵，它們組成一個集合。
我們一般使用的any表蝙、in拴测、all、some等詞府蛇，將外部查詢與子查詢的結(jié)果進(jìn)行判斷集索、不排除特殊情況比如()=()。
如果將any汇跨、in务荆、all、some等詞與標(biāo)量子查詢穷遂，就會得到空的結(jié)果函匕。

按子查詢位置分類：

where型子查詢：把內(nèi)層查詢結(jié)果當(dāng)作外層查詢的比較條件）
from型子查詢：把內(nèi)層的查詢結(jié)果供外層再次查詢
exists型子查詢：把外層查詢結(jié)果拿到內(nèi)層，看內(nèi)層的查詢是否成立

按子查詢與外部查詢的依賴關(guān)系分類：

獨立子查詢：不依賴外部查詢而運行的子查詢
相關(guān)子查詢：是指引用了外部查詢列的子查詢蚪黑，即子查詢會對外部查詢的每行進(jìn)行一次計算盅惜。

1、標(biāo)量子查詢：

是指子查詢返回的是單一值的標(biāo)量忌穿，如一個數(shù)字或一個字符串酷窥，也是子查詢中最簡單的返回形式
可以使用 = > < >= <= <> 這些操作符對子查詢的標(biāo)量結(jié)果進(jìn)行比較，通常子查詢的位置在比較式的右側(cè)

#查詢大于平均年齡的學(xué)生
select * from stu age>(select avg(age) from stu)
#查詢王昭君的成績伴网，并顯示成績
select * from scores where s_no=(select sid from stu where name='王昭君')

注意子查詢結(jié)果只能是一個值

2蓬推、列級子查詢

指子查詢返回的結(jié)果集是一列N 行
因為子查詢返回的結(jié)果是多個值，不是一個結(jié)果澡腾，
所以不能直接使用 = > < >= <= <> 這些比較標(biāo)量結(jié)果的操作符
需要IN沸伏、ANY、SOME 动分、ALL 操作符毅糟，搭配= > < >= <= <> 使用

#查詢18歲的學(xué)生成績，顯示成績
select * from score where stuid in (select stuid from stu where age=18)
select * from score where stuid > any (select stuid from stu where age=18)
select * from score where stuid > all (select stuid from stu where age=18)

注意：對于子查詢中的表是空表的情況澜公，語句均返回 NULL姆另；

3、行級子查詢

指子查詢返回的結(jié)果集是一行 N 列坟乾，該子查詢的結(jié)果通常是對表的某行數(shù)據(jù)進(jìn)行查詢而返回的結(jié)果集

select * from stu where sex='男' and age=26 
#可以寫成
select * from stu where (sex,age)=('男',26)
#因此 ('男',26)里面兩個值可以寫成語句
select * from stu where (sex,age)=
(select sex,age from stu where sex='男' order by age limit 1)
#()與()字段個數(shù)要對應(yīng)

4迹辐、表子查詢

指子查詢返回的結(jié)果集是 N 行 N 列的一個表數(shù)據(jù)
將子查詢結(jié)果充當(dāng)一張表，要求必須起別名

select * from (select * from stu) as s
select* from article where (title,content,uid) in  (select title,content,uid from blog)

select * from 表1 
inner join (可以是查詢出來的結(jié)果當(dāng)作數(shù)據(jù)源使用) as 別名   
on 表1.字段=表2.字段
where 條件

#查詢數(shù)據(jù)庫和系統(tǒng)測試的課程成績
select * from score
inner join (select * from course where cname in ('數(shù)據(jù)庫','系統(tǒng)測試')) as c
on score.cid=c.cid
#比起
select * from score
inner join course on score.cid=c.cid
where course.cname in ('數(shù)據(jù)庫','系統(tǒng)測試')
#性能高一點

三甚侣、子查詢中特定的關(guān)鍵詞使用

1明吩、 in 、not in
in 殷费、 =any 印荔、 =some ：表示一個范圍內(nèi)
not in 低葫、 <>all：表示不在一個范圍內(nèi)

2、 any| some 任意一個仍律，只要條件滿足任意的一個嘿悬，就返回TRUE
=any、 =some水泉、 in：等于子查詢返回的結(jié)果
>any鹊漠、 >some ：大于子查詢返回的結(jié)果中任意一個值，就是大于最小值
<any 茶行、<some ：小于子查詢返回的結(jié)果中任意一個值，就是小于最大值
!=any 登钥、!=some：沒有意義畔师，就是查了所有

3、 all 所有
=all：沒有意義牧牢，一個值不可能同時等于多個值
>all：大于子查詢返回的結(jié)果中所有值看锉，就是大于最大值
<all：小于子查詢返回的結(jié)果中所有值，就是小于最小值
!=all 塔鳍、<>all 伯铣、not in：不等于子查詢返回的結(jié)果中所有值，就是非

4轮纫、 exists腔寡、not exists

用于檢查子查詢是否至少會返回一行數(shù)據(jù)，該子查詢實際上并不返回任何數(shù)據(jù)掌唾，而是返回值True或False放前，檢測行的存在
與其它謂詞和邏輯表達(dá)式不同的是，
無論輸入子查詢是否返回行糯彬，exists都不會返回unknown凭语，
對于exists來說，unknown就是false

select... fromtable where exists (select........)

將主查詢的數(shù)據(jù)撩扒，放到子查詢中做條件驗證似扔，根據(jù)驗證結(jié)果（TRUE 或 FALSE）來決定主查詢的數(shù)據(jù)結(jié)果是否得以保留

#獲得城市為hangzhou，并且存在訂單的用戶
select *
from table1
where city='hangzhou' and exists
                (select *
                from table2
                where table1.customer_id=table2.customer_id);

四搓谆、子查詢優(yōu)化

很多查詢中需要使用子查詢炒辉。使用子查詢可以一次性的完成很多邏輯上需要多個步驟才能完成的SQL操作，同時也可以避免事務(wù)或者表鎖死泉手。子查詢可以使查詢語句很靈活辆脸，但子查詢的執(zhí)行效率不高。

子查詢時螃诅，MySQL需要為內(nèi)層查詢語句的查詢結(jié)果建立一個臨時表啡氢。然后外層查詢語句再臨時表中查詢記錄状囱。查詢完畢后，MySQL需要撤銷這些臨時表倘是。因此亭枷，子查詢的速度會受到一定的影響。如果查詢的數(shù)據(jù)量比較大搀崭，這種影響就會隨之增大叨粘。

在MySQL中可以使用連接查詢來替代子查詢。連接查詢不需要建立臨時表瘤睹，其速度比子查詢要快升敲。

如

例子1：
SELECT * FROM t1
WHERE t1.a1 NOT in (SELECT a2 FROM t2 )
優(yōu)化后：
SELECT * FROM t1
LEFT JOIN t2 ON t1.a1=t2.a2
WHERE t2.a2 IS NULL  

例子2：
SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog)
優(yōu)化后：
SELECT * FROM article
inner join blog
on (article.title=blog.title AND article.content=blog.content AND article.uid=blog.uid)

練習(xí)：
1.查詢所有價格大于平均價格(保留2位小數(shù))的商品，并按價格降序排序

select * from goods where price>
(select  round(avg(price),2) from goods ) order by price desc

查詢價格大于或等于“超極本”價格的商品轰传，并按價格降序排序
子查詢返回的是一列多行

select * from goods where price >=any
(select  price from goods  where cate='超極本' ) order by price desc