mysql中子查詢學習筆記

當一個查詢是另一個查詢的條件時，稱之為子查詢。子查詢可以使用幾個簡單命令構造功能強大的復合命令。

子查詢最常用於SELECT-SQL命令的WHERE子句中。
子查詢是一個 SELECT 語句，它嵌套在一個 SELECT、SELECT...INTO 語句、INSERT...INTO 語句、DELETE 語句、或 UPDATE 語句或嵌套在另一子查詢中

子查詢可以寫在WHERE子句、HAVING子句、FROM子句中。
子查詢分為多行子查詢和單行子查詢。

如果能夠保證返回的行數小於等於1行的，則是單行子查詢。
使用單行比較操作符：=、>、>=、<、<=、<>。
否則是多行子查詢。
使用多行比較符：IN、> ANY 、> ALL、< ANY、< ALL。 下面我們測試下子查詢的效果，還是用之前的TEST_T表。表結構查詢一般情況下返回一個列的值（單列子查詢），也可以返回多個列的值（多列子查詢），但要成對匹配。
單列子查詢可以在DECODE和CASE語句，還有SELECT中除GROUP BY子句以外的所有子句中使用。 多列子查詢例子：

select t1.id 序號, t1.no 班級, t1.s_name 學生名字, t1.score 分數 from test_t t1 where (t1.id,t1.score) in (select t2.id, t2.score from test_t t2);

這裡子查詢返回兩個列，但外層查詢也要同時匹配這兩個列。

單列子查詢例子：
--查找表中分數最高的學生的名字和班級

select no 班級, s_name 學生名字 from test_t where score in(select max(score) from test_t);


班級 學生名字 2 李四 3 小C

關聯子查詢：
關聯子查詢按照一行接一行的順序執行，主查詢的每一行都執行一次子查詢。
--查找每個班級中分數最高的學生的班級、名字和分數 為了測試的准確性，再插入一條記錄：

INSERT INTO TEST_T (ID, NO, T_NAME, S_NAME, SCORE) VALUES ('10', '2', 'Mary', 'AAA', '70');

思路一（使用關聯子查詢）：

select t1.id 序號, t1.no 班級, t1.s_name 學生名字, t1.score 分數 from test_t t1 where t1.score = (select max(t2.score) from test_t t2 where t1.no = t2.no);

查詢結果：

序號 班級 學生名字 分數 2 2 李四 99 7 1 Zhang 70 6 3 小C 99

該句使用了關聯子查詢，內層查詢用t1.no = t2.no和外層查詢的字段做匹配。 關聯子查詢的執行過程（猜測）：
1）先從外層查詢的test_t t1表取出一行

ID NO T_NAME S_NAME SCORE 1 1 Peter 張三 60

2）到內層查詢中t2表篩選t1.no = t2.no（把同一個班級的記錄都篩選出來，返回了這個班級的最高分）

ID NO T_NAME S_NAME SCORE 1 1 Peter 張三 60 4 1 Peter 小A 59 7 1 Peter Zhang 70

返回了70這個值 3）外層比較score是否和t2.score相等，這裡是不等，於是返回空 4）外層查詢繼續取下一條記錄，繼續循環 如果t1.no = t2.no判斷條件放到外層查詢中：

select t1.id 序號, t1.no 班級, t1.s_name 學生名字, t1.score 分數 from test_t t1 where t1.score = (select max(t2.score) from test_t t2) and t1.no = t2.no;

就會報錯ORA-00904: "T2"."NO": 標識符無效 說明對於非關聯子查詢，外層查詢看不到內層查詢使用的表，因為此時內層查詢已經返回結果了。

思路二（使用group by）：

select id 序號, no 班級, s_name 學生名字, score 分數 from test_t where (no,score) in(select no 班級, max(score) 分數 from test_t group by no);

使用多列子查詢，先在內層查詢用班級號分組，算出最高分，返回給外層查詢

 

exists和not exists關鍵字 exists操作符檢查在子查詢中是否存在滿足條件的行。exists只返回true或false。 exists和關聯子查詢：
1、如果在子查詢中存在滿足條件的行：
如果能找到一行，對於這個子查詢來說，不繼續查找，exists返回true。
繼續下一個子查詢。 2、如果在子查詢中不存在滿足條件的行：
如果沒有一行滿足子查詢條件，子查詢的查詢結果為空，exists返回false。
繼續下一個子查詢。 因為exists只返回true或false，所以在子查詢中select後面只要寫成select 1就行了。

TEST_T表。

select * from test_t t1 where exists (select 1 from test_t t2 where t2.score < 60 and t1.id = t2.id);


ID NO T_NAME S_NAME SCORE 4 1 Peter 小A 59

1）從主查詢裡取出一行，到子查詢查找滿足score < 60 and t1.id = t2.id的行
2）子查詢只要找到一條滿足條件的行，exists返回true，輸出主查詢的這一行
3）如果子查詢結果為空，exists返回false，則不輸出主查詢的這一行 not exists關鍵字
沒有滿足條件的行，返回true。
有滿足條件的行，返回false。
如果子查詢的結果都為空，那麼會返回主查詢的所有行。

select * from test_t t1 where not exists (select 1 from test_t t2 where t2.score < 90 and t1.id = t2.id);


ID NO T_NAME S_NAME SCORE 6 3 Tom 小C 99 2 2 Mary 李四 99 9 3 Tom Li 90