天天看點
傳回

SELECT中常用的子查詢操作

MySQL中的子查詢

　　是在MySQL中經常使用到的一個操作，不僅僅是用在DQL語句中，在DDL語句、DML語句中也都會常用到子查詢。

子查詢的定義：

　　子查詢是将一個查詢語句嵌套在另一個查詢語句中；

　　在特定情況下，一個查詢語句的條件需要另一個查詢語句來擷取，内層查詢（inner query）語句的查詢結果，可以為外層查詢（outer query）語句提供查詢條件。

特點（規範）：

　　①子查詢必須放在小括号中

　　②子查詢一般放在比較操作符的右邊，以增強代碼可讀性

　　③子查詢(小括号裡的内容)可出現在幾乎所有的SELECT子句中(如：SELECT子句、FROM子句、WHERE子句、ORDER BY子句、HAVING子句……)

(相關、不相關)子查詢分類：

　　①标量子查詢(scalar subquery)：傳回1行1列一個值

　　②行子查詢(row subquery)：傳回的結果集是 1 行 N 列

　　③列子查詢(column subquery)：傳回的結果集是 N 行 1列

　　④表子查詢(table subquery)：傳回的結果集是 N 行 N 列

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

注意：一個子查詢會傳回一個标量（就一個值）、一個行、一個列或一個表，這些子查詢稱之為标量、行、列和表子查詢

　　1、如果子查詢傳回一個标量值（就一個值），那麼外部查詢就可以使用：=、>、<、>=、<=和<>符号進行比較判斷；

　　2、如果子查詢傳回的不是一個标量值，而外部查詢使用了比較符和子查詢的結果進行了比較，那麼就會抛出異常。

環境搭建：employee雇員表、department部門表、TENNIS資料庫

mysql> select * from department;
+------+-----------+--------------+---------------------+
| d_id | d_name    | function     | address             |
+------+-----------+--------------+---------------------+
| 1001 | 科技部     | 研發産品       | 3号樓5層            |
| 1002 | 生産部     | 生産産品       | 5号樓1層            |
| 1003 | 銷售部     | 策劃銷售       | 1号樓銷售大廳        |
+------+-----------+--------------+---------------------+
3 rows in set (0.02 sec)

mysql> select * from employee;
+------+------+--------+------+------+--------------------+
| num  | d_id | name   | age  | sex  | homeaddr           |
+------+------+--------+------+------+--------------------+
|    1 | 1001 | 張三    |   26 | 男   | 北京市海澱區         |
|    2 | 1001 | 李四    |   24 | 女   | 上海市黃浦區         |
|    3 | 1002 | 王五    |   25 | 男   | 江西省贛州市         |
|    4 | 1004 | Aric   |   15 | 男   | England            |
+------+------+--------+------+------+--------------------+
4 rows in set (0.00 sec)      
mysql> use TENNIS
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------+
| Tables_in_TENNIS  |
+-------------------+
| COMMITTEE_MEMBERS |
| MATCHES           |
| PENALTIES         |
| PLAYERS           |
| TEAMS             |
+-------------------+
5 rows in set (0.00 sec)

一、不相關子查詢

　　不相關，主查詢和子查詢是不相關的關系。也就是意味着在子查詢中沒有使用到外部查詢的表中的任何列。

　　先執行子查詢，然後執行外部查詢

SELECT中常用的子查詢操作

1、标量子查詢(scalar subquery)：傳回1行1列一個值

　　因為是标量子查詢，結果是一個值，是以可用來進行算數運算。

　　可以使用 = > < >= <= <> 操作符對子查詢的結果進行比較：

mysql> select num,name
    -> from employee
    -> where d_id=(
    -> 　　select d_id
    -> 　　from department
    -> 　　where d_name='科技部');
+------+--------+
| num  | name   |
+------+--------+
|    1 | 張三    |
|    2 | 李四    |
+------+--------+      
mysql> select num,name
    -> from employee
    -> where d_id=(
    -> 　　select d_id
    -> 　　from department
    -> 　　where d_name='财務部');
Empty set (0.00 sec)      
注意：如果子查詢傳回空值，可能導緻外部查詢的where條件也為空，進而外部查詢的結果集為空。      
mysql> SELECT  playerno,town,sex
    -> FROM  PLAYERS
    -> WHERE (town,sex) = ((SELECT town FROM PLAYERS WHERE playerno=7),
    ->                     (SELECT sex FROM PLAYERS WHERE playerno=44));
+----------+-----------+-----+
| playerno | town      | sex |
+----------+-----------+-----+
|        2 | Stratford | M   |
|        6 | Stratford | M   |
|        7 | Stratford | M   |
|       39 | Stratford | M   |
|       57 | Stratford | M   |
|       83 | Stratford | M   |
|      100 | Stratford | M   |
+----------+-----------+-----+
7 rows in set (0.01 sec)      
注意： (列,列,…)叫做行表達式，比較時是比較列的組合。

2、行子查詢(row subquery)：傳回的結果集是 1 行 N 列

　　使用行表達式進行比較，可以使用 = > < >= <= <> in操作符

mysql> select d_id from department;
+------+
| d_id |
+------+
| 1001 |
| 1002 |
| 1003 |
+------+

mysql> select * from employee
    -> where d_id in
    -> 　　(select d_id from department);
+------+------+--------+------+------+--------------------+
| num  | d_id | name   | age  | sex  | homeaddr           |
+------+------+--------+------+------+--------------------+
|    1 | 1001 | 張三    |   26 | 男   | 北京市海澱區         |
|    2 | 1001 | 李四    |   24 | 女   | 上海市黃浦區         |
|    3 | 1002 | 王五    |   25 | 男   | 江西省贛州市         |
+------+------+--------+------+------+--------------------+

解析：此處首先查詢出department表中所有d_id字段的資訊，并将結果作為條件，接着查詢employee表中以d_id為條件的所有字段資訊；NOT IN的效果與上面剛好相反。

3、列子查詢(column subquery)：傳回的結果集是 N 行 1列

　　必須使用 IN、ANY 和 ALL 操作符對子查詢傳回的結果進行比較

注意：ANY 和 ALL 操作符不能單獨使用，其前面必須加上單行比較操作符= > < >= <= <>

1）帶ANY關鍵字的子查詢：ANY關鍵字表示滿足其中任一條件

mysql> select * from employee
    -> where d_id !=any
    -> 　　(select d_id from department);
+------+------+--------+------+------+--------------------+
| num  | d_id | name   | age  | sex  | homeaddr           |
+------+------+--------+------+------+--------------------+
|    1 | 1001 | 張三    |   26 | 男   | 北京市海澱區         |
|    2 | 1001 | 李四    |   24 | 女   | 上海市黃浦區         |
|    3 | 1002 | 王五    |   25 | 男   | 江西省贛州市         |
|    4 | 1004 | Aric   |   15 | 男   | England            |
+------+------+--------+------+------+--------------------+

2）帶ALL關鍵字的子查詢：ALL關鍵字表示滿足其中所有條件

mysql> select * from employee
    -> where d_id >=all
    -> 　　(select d_id from department);
+------+------+------+------+------+----------+
| num  | d_id | name | age  | sex  | homeaddr |
+------+------+------+------+------+----------+
|    4 | 1004 | Aric |   15 | 男    | England |
+------+------+------+------+------+----------+

注意：如果子查詢的結果集中有null值，使用>ALL 和not in操作符時，必須去掉子查詢結果集中的null值，否則查詢結果錯誤

mysql> select * from department
    -> where d_id >all    #>all背後執行and操作
    -> 　　(select d_id from employee);
Empty set (0.01 sec)      
結果為空：子查詢的結果集中包含null值(子查詢結果集中沒有主查詢裡的1004行，則為空)。

4、表子查詢(table subquery)：傳回的結果集是 N 行 N 列

示例：在committee_members表中，得到任職日期和卸任日期與具有Secretary職位的一行相同的所有行

mysql> select *
    -> from COMMITTEE_MEMBERS
    -> where (begin_date,end_date) in
    -> (
    ->   select begin_date,end_date
    ->   from COMMITTEE_MEMBERS
    ->   where position='Secretary'
    -> );
+----------+------------+------------+-----------+
| PLAYERNO | BEGIN_DATE | END_DATE   | POSITION  |
+----------+------------+------------+-----------+
|        6 | 1990-01-01 | 1990-12-31 | Secretary |
|        8 | 1990-01-01 | 1990-12-31 | Treasurer |
|        8 | 1991-01-01 | 1991-12-31 | Secretary |
|       27 | 1990-01-01 | 1990-12-31 | Member    |
|       27 | 1991-01-01 | 1991-12-31 | Treasurer |
|       57 | 1992-01-01 | 1992-12-31 | Secretary |
|      112 | 1992-01-01 | 1992-12-31 | Member    |
+----------+------------+------------+-----------+
7 rows in set (0.05 sec)

二、相關子查詢(correlated subquery)

　　在子查詢中使用到了外部查詢的表中的任何列。

　　先執行外部查詢，然後執行子查詢

SELECT中常用的子查詢操作

相關子查詢的執行步驟：

　　①先執行外部查詢，得到的行叫做候選行

　　②使用某個候選行來執行子查詢

　　③使用子查詢的傳回值來決定該候選行是出現在最終的結果集中還是被丢棄

　　④重複以上步驟2和3，将所有的候選行處理完畢，得到最終的結果

SELECT中常用的子查詢操作

示例：得到項目是‘研發産品’的雇員的編号

mysql> select num
    -> from employee
    -> where '研發産品'=(
    -> 　　select function
    -> 　　from department
    -> 　　where d_id=employee.d_id);
+------+
| num  |
+------+
|    1 |
|    2 |
+------+

解析：

　　1）主查詢得到候選行，一行一行的拿去執行子查詢；

　　2）主查詢表employee的候選行的d_id和子查詢的d_id比對，傳回值進行where過濾；

　　3）符合，加入最終結果集；

　　4）不符合，将候選行丢棄，接着進行處理下一個候選行。

帶EXISTS關鍵字的相關子查詢(EXISTS存在)

專門判斷子查詢的結果集是否不為空：

　　非空空傳回true

　　空傳回false

當傳回的值為true時，外層查詢語句将進行查詢，否則不進行查詢

mysql> select * from employee
    -> where exists
    -> (select d_name from department where d_id=1004);
Empty set (0.00 sec)

此處内層循環并沒有查詢到滿足條件的結果，是以傳回false，外層查詢不執行

EXISTS關鍵字可以與其他的查詢條件一起使用，條件表達式與EXISTS關鍵字之間用AND或者OR來連接配接

mysql> select * from employee
    -> where age>24 and exists
    -> (select d_name from department where d_id=1003);
+------+------+--------+------+------+--------------------+
| num  | d_id | name   | age  | sex  | homeaddr           |
+------+------+--------+------+------+--------------------+
|    1 | 1001 | 張三    |   26 | 男   | 北京市海澱區         |
|    3 | 1002 | 王五    |   25 | 男   | 江西省贛州市         |
+------+------+--------+------+------+--------------------+

@author：http://www.cnblogs.com/geaozhang/

上一篇: MySQL常見建表選項及限制
下一篇: Linux常用運維指令整理記錄