《MySQL必知必會》學習筆記七(復雜查詢)------掌握部分

2019-11-08 20:42:53

字體：大中小

來源：轉載

供稿：網友

MySQL必知必會知識預覽第一章——了解SQL 第二章——MySQL簡介第三章——使用MySQL 第四章——檢索數據第五章——排序檢索數據第六章——過濾數據第七章——數據過濾第八章——用通配符進行過濾第九章——用正則表達式進行搜索第十章——創建計算字段第十一章——使用數據處理函數第十二章——匯總數據第十三章——分組數據第十四章——使用子查詢第十五章——聯結表第十六章——創建高級聯結第十七章——組合查詢第十八章——全文本搜索第十九章——插入數據第二十章——更新和刪除數據第二十一章——創建和操縱表第二十二章——使用視圖第二十三章——使用儲存過程第二十四章——使用游標第二十五章——使用觸發器第二十六章——管理事務處理第二十七章——全球化和本地化第二十八章——安全管理第二十九章——數據庫維護第三十章——改善性能

————————————– 華麗的分隔符 ————————————————–

第十四章子查詢

子查詢版本要求：MySQL4.1引入了對子查詢的支持。

查詢：任何SQL都是查詢。但是此術語一般指select語句。 子查詢：嵌套在其他查詢中的查詢。

利用子查詢進行過濾

思考：假如需要列出訂購物品TNT2的所有客戶，應該怎么檢索？

————————————–————————————–————————————–————————————–

1）檢索包含TNT2的訂單編號； 2）檢索具有第一步驟列出的訂單編號的所有客戶的ID ； 3）檢索前一步驟返回的所有客戶ID的客戶信息；

上述每一個步驟都可以進行單獨查詢，可以把一條select語句返回的結果用于另外一條select語句的where子句。 select order_num from orderitems where PRod_id = ‘tnt2’; select cust_id from orders where order_num IN(20005,20007); select cust_name,cust_contact from customers where cust_id IN(10001,10004); // 屬于硬編碼ID 這里寫圖片描述

可以使用子查詢把3個查詢組合成一條語句

將1）2）聯合起來 select cust_id from orders where order_num IN(select order_num from orderitems where prod_id = ‘tnt2’);

將1）2）3）聯合起來 select cust_name,cust_contact from customers where cust_id IN(select cust_id from orders where order_num IN(select order_num from orderitems where prod_id = ‘tnt2’)); 這里寫圖片描述

子查詢總是從內向外處理。為了執行上述子查詢，MySQL實際上必須執行3條select語句，最里面的子查詢返回訂單號列表，此列表用于其外面的子查詢的where子句。外面的子查詢返回客戶ID列表，此客戶ID列表用于最外層查詢的where子句。最外層查詢確實返回所需要的數據。

在where子句中使用子查詢，應該保證select語句具有where子句中相同數目的列。通常，子查詢將返回單個列并且與單個列匹配，但如果需要也可以使用多個列。

使用子查詢，一般與IN操作符結合使用，但可以用于測試相等(=)、不相等(<>)等。

作為計算字段使用子查詢

思考：檢索customers表中每個客戶的訂單總數。訂單與相應的客戶ID存儲在orders表中。

————————————–————————————–————————————–————————————– 1）從customers表中檢索客戶列表。 2）對于檢索出的每個客戶，統計其在orders表中的訂單數目。

select cust_name,cust_state,(select count(*) from orders where orders.cust_id = customers.cust_id ) as orders from customers order by cust_name; 這里寫圖片描述

select語句對customers表中每個客戶返回3列，cust_name,cust_state，orders ，orders是一個計算字段，它是由圓括號中的子查詢建立的。該子查詢對檢索出的每個客戶執行一次。在此例子中，該子查詢執行了5次，因為檢索出5個客戶。

在子查詢中，一般使用完全限定名，必須注意有歧義的列名。

用子查詢測試和調試很有技巧性，特別是這些語句的復雜性不斷增加的情況下更是如此，用子查詢建立(和測試)查詢的最可靠的方法就是逐漸進行，這與MySQL處理他們的方法非常相同，首先，建立和測試最內層的查詢，然后，用硬編碼數據建立和測試外層查詢，并且僅在確定它完全正常后才嵌入子查詢。

第十五章聯結表

等值聯結

外鍵：外鍵為某個表中的一列，它包含另一個表中的主鍵值，定義了兩個表之間的關系。

創建聯結非常簡單：只需要規定要聯結的表以及他們如何關聯即可。

select vend_name,prod_name,prod_price from vendors,products **where vendors.vend_id = products.vend_id **order by vend_name,prod_name;

select語句與前面的所有語句一樣，指定要檢索的列，這里，最大的差別是所指定的兩個列(prod_name和prod_price)在一個表中，而另一個列(vend_name)在另外一個表中。

黑體部分則是給出了select語句要聯結的兩個表的名字，用where子句正常聯結。where子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id。 完全限定名：在肯能引起二義性時，必須使用完全限定名

聯結表中where子句的重要性 在一條select語句中，要聯結幾個表時，相應的關系時在運行中進行構造的，在數據庫表的定義中不存在能指示MYSQL如何對表進行聯結的東西。你必須自己做這件事情，在聯結兩個表時，你實際上要做的是將第一個表中的每一行與第二個表中的每一行進行配對。where子句作為過濾條件，他只包含哪些匹配給條件(這里是聯結條件)的行。沒有where子句，第一個表中的每一行將第二個表中的每個行配對，而不管他們邏輯上是否可以配在一起。這也是笛卡兒積產生的原因。 笛卡兒積 由沒有聯結條件的表關系返回的結果為笛卡兒積，檢索的行數將是第一個表中的行乘以第二個表中的行數

不要忘記where子句 應該保證所有的聯結都有where子句，否則MySQL將返回比所需要的多得多的數據，同時，也應該保證where子句的正確性，不正確的過濾條件，將導致MySQL返回不正確的數據。

叉聯結返回稱為叉聯結的笛卡兒積的聯結類型。

內部聯結

內部聯結與等值聯結 等值聯結是基于兩個表之間的相等測試，這種聯結也稱為內部聯結。但是，對于這種聯結，我們可以使用稍微不同的語法進行來明確指定聯結的類型。

select vend_name,prod_name,prod_price from vendors inner join products on vendors.vend_id = products.vend_id order by vend_name; 查詢結果同上，利用inner join on子句代替where子句查詢，此效率遠遠大于上面查詢。注意：下面兩個圖片每次都是重新進行連接數據庫下的測試。這里寫圖片描述

注意：首選Inner Join語法。盡管where子句定義聯結比較簡單，但是使用明確的聯結語法能夠保證不會忘記聯結條件，而且前者的性能也是比較好的。

聯結多表

select vend_name,prod_name,prod_price,quantity from vendors,products,orde ritems where vendors.vend_id = products.vend_id and orderitems.prod_id = product s.prod_id and order_num=20005 order by vend_name;

將子查詢轉化為聯結查詢 select cust_name,cust_contact from customers where cust_id IN(select cust_id from orders where order_num IN(select order_num from orderitems where prod_id = ‘tnt2’));// 最開始的例子

select cust_name,cust_contact from customers ，orders ，orderitems where customers.cust_id =orders.cust_id and orders.order_num =orderitems.order_num and orderitems.prod_id=’tnt2’

這里寫圖片描述