MySQL數據查詢之子查詢的示例分析

這篇文章主要介紹了MySQL數據查詢之子查詢的示例分析，具有一定借鑒價值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

子查詢是指一個查詢語句嵌套在另一個查詢語句內部的查詢。在select子句中先計算子查詢，子查詢結果作為外層另一個查詢的過濾條件，查詢可以基于一個表或者多個表。

子查詢中常用的操作符有any(some)、all、in、exists。子查詢可以添加到select、update和delete語句中，而且可以進行多層嵌套。子查詢中也可以使用比較運算符，如"<"，"<="，">"，">=“和”!="。

(1)帶any、some關鍵字的子查詢
(2)帶all關鍵字的子查詢
(3)帶exists關鍵字的子查詢
(4)帶in關鍵字的子查詢
(5)帶比較運算符的子查詢

（免費學習推薦：mysql視頻教程）

any和some關鍵字是同義詞，表示滿足其中任一條件，允許創建一個表達式對子查詢的返回值列表進行比較，只要滿足內層子查詢中的任何一個比較條件，就返回一個結果作為外層查詢的條件。

下面定義兩個表tbl1和tbl2，并向兩個表中插入數據：

mysql> create table tbl1( num1 int not null);Query OK, 0 rows affected (0.13 sec)mysql> create table tbl2(num2 int not null);Query OK, 0 rows affected (0.10 sec)mysql> insert into tbl1 values(1),(5),(13),(27);Query OK, 4 rows affected (0.05 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> insert into tbl2 values(6),(14),(11),(20);Query OK, 4 rows affected (0.06 sec)Records: 4  Duplicates: 0  Warnings: 0

any關鍵字接在一個比較操作符后面，表示若與子查詢返回的任何職比較為true，則返回true。

【例】返回tbl2表的所有num2列，然后將tbl1中的num1的值與之進行比較，只要大于num2的任何一個值，即為符合條件的結果。

mysql> select num1 from tbl1 where num1 > any(select num2 from tbl2);+------+| num1 |+------+|   13 ||   27 |+------+2 rows in set (0.00 sec)

• all關鍵字需要同時滿足所有內層查詢的條件。

• all關鍵字接在一個比較操作符后面，表示與子查詢返回的所有值比較為true，則返回true。

【例】返回tbl1表中比tbl2表num2列所有值都大的值，SQL語句如下：

mysql> select num1 from tbl1 where num1 > all(select num2 from tbl2);+------+| num1 |+------+|   27 |+------+1 row in set (0.00 sec)

• exists關鍵字后面的參數是一個任意的子查詢，系統對子查詢進行運算以判斷它是否返回行，如果至少返回一行，那么exists的結果為true，此時外層查詢語句將進行查詢；如果子查詢沒有返回任何行，那么exists返回的結果是false，此時外層語句將不進行查詢。

【例1】查詢suppliers表中是否存在s_id=107的的供應商，如果存在，則查詢fruits表中的記錄，SQL語句如下：

mysql> select * from fruits    -> where exists
    -> (select s_name from suppliers where s_id = 107);+------+------+------------+---------+| f_id | s_id | f_name     | f_price |+------+------+------------+---------+| 12   |  104 | lemon      |    6.40 || a1   |  101 | apple      |    5.20 || a2   |  103 | apricot    |    2.20 || b1   |  101 | blackberry |   10.20 || b2   |  104 | berry      |    7.60 || b5   |  107 | xxxx       |    3.60 || bs1  |  102 | orange     |   11.20 || bs2  |  105 | melon      |    8.20 || c0   |  101 | cherry     |    3.20 || m1   |  106 | mango      |   15.70 || m2   |  105 | xbabay     |    2.60 || m3   |  105 | xxtt       |   11.60 || o2   |  103 | coconut    |    9.20 || t1   |  102 | banana     |   10.30 || t2   |  102 | grape      |    5.30 || t4   |  107 | xbabay     |    3.60 |+------+------+------------+---------+16 rows in set (0.00 sec)

由結果可知，內層查詢結果表明suppliers表中存在s_id=107的記錄，因此exists表達式返回true；外層查詢語句接收true之后對表fruits進行查詢，返回所有記錄。

【例2】查詢suppliers表中是否存在s_id=107的供應商，如果存在，則查詢fruits表中的f_price大于10.20的記錄,SQL語句如下:

mysql> select * from fruits    -> where f_price > 10.20 and exists
    -> (select s_name from suppliers where s_id = 107);+------+------+--------+---------+| f_id | s_id | f_name | f_price |+------+------+--------+---------+| bs1  |  102 | orange |   11.20 || m1   |  106 | mango  |   15.70 || m3   |  105 | xxtt   |   11.60 || t1   |  102 | banana |   10.30 |+------+------+--------+---------+4 rows in set (0.00 sec)

可以看到,內層查詢表名suppliers表中存在s_id=107的記錄，因此exists表達式返回true；外層查詢語句接收true之后根據查詢條件f_price>10.20對fruits表進行查詢，返回結果為4條f_price大于10.20的記錄。

• not exists與exists使用方法相同，返回的結果相反。子查詢如果至少返回一行，那么not exists的結果為false，此時外層查詢語句將不進行查詢；如果子查詢沒有返回任何行，那么not exists返回的結果就是true，此時外層語句將進行查詢。

【例3】查詢suppliers表中是否存在s_id =107的供應商，如果不存在就查詢fruits表中的記錄，SQL語句如下：

mysql> select * from fruits    -> where not exists
    -> (select s_name from suppliers where s_id = 107);Empty set (0.00 sec)

可以看到，內層查詢返回結果為false，外層表達式接收false將不再查詢fruits表中的記錄。

注意：exists 和 not exists的結果只取決于是否會返回行，而不取決于這些行的內容，所以這個子查詢輸入列表通常是無關緊要的。

• in關鍵字進行子查詢時，內層查詢語句僅僅返回一個數據列，這個數據列里的值將提供給外層查詢語句進行比較操作。

【例1】在orderitems表中查詢f_id為c0的訂單號，并根據訂單號查詢具有訂單號的客戶c_id，SQL語句如下：

mysql> select c_id from orders where o_num in
    -> (select o_num from orderitems where f_id = 'c0');+-------+| c_id  |+-------+| 10004 || 10001 |+-------+2 rows in set (0.00 sec)

上面的語句是下面查詢方式的簡寫：

mysql> select o_num from orderitems where f_id = 'c0';+-------+| o_num |+-------+| 30003 || 30005 |+-------+2 rows in set (0.00 sec)mysql> select c_id from orders where o_num in (30003,30005);+-------+| c_id  |+-------+| 10004 || 10001 |+-------+2 rows in set (0.00 sec)

下面介紹與in相反的not in關鍵字：

【例2】與例1類似，但select語句中使用not in 關鍵字。SQL語句如下:

mysql> select c_id from orders where o_num not in
    -> (select o_num from orderitems where f_id ='c0');+-------+| c_id  |+-------+| 10001 || 10003 || 10005 |+-------+3 rows in set (0.00 sec)

可以看到返回了三個表,查看orders中的記錄可知,c_id等于10001的客戶的訂單不止一個:

mysql> select * from orders;+-------+---------------------+-------+| o_num | o_date              | c_id  |+-------+---------------------+-------+| 30001 | 2008-09-01 00:00:00 | 10001 || 30002 | 2008-09-12 00:00:00 | 10003 || 30003 | 2008-09-30 00:00:00 | 10004 || 30004 | 2008-10-03 00:00:00 | 10005 || 30005 | 2008-10-08 00:00:00 | 10001 |+-------+---------------------+-------+5 rows in set (0.00 sec)

結果只是排除了訂單號，但仍然有可能選擇同一個客戶。

子查詢的功能也可以通過連接查詢完成，但子查詢使得MySQL代碼更容易閱讀和編寫。

【例1】在suppliers表中查詢s_city等于"Tianjin"的供應商s_id，然后在fruits表中查詢所有該供應商提供的水果的種類，SQL語句如下：

mysql> select s_id,f_name from fruits    -> where s_id =
    -> (select s1.s_id from suppliers as s1 where s1.s_city = 'Tianjin');+------+------------+| s_id | f_name     |+------+------------+|  101 | apple      ||  101 | blackberry ||  101 | cherry     |+------+------------+3 rows in set (0.00 sec)

【例2】在suppliers表中查詢s_city等于"Tianjin"的供應商，s_id，然后在fruits表中查詢所有非該供應商提供的水果的種類，SQL語句如下：

mysql> select s_id , f_name from fruits    -> where s_id <>
    -> (select s1.s_id from suppliers as s1 where s1.s_city ='Tianjin');+------+---------+| s_id | f_name  |+------+---------+|  104 | lemon   ||  103 | apricot ||  104 | berry   ||  107 | xxxx    ||  102 | orange  ||  105 | melon   ||  106 | mango   ||  105 | xbabay  ||  105 | xxtt    ||  103 | coconut ||  102 | banana  ||  102 | grape   ||  107 | xbabay  |+------+---------+13 rows in set (0.00 sec)

感謝你能夠認真閱讀完這篇文章，希望小編分享的“MySQL數據查詢之子查詢的示例分析”這篇文章對大家有幫助，同時也希望大家多多支持億速云，關注億速云行業資訊頻道，更多相關知識等著你來學習!