首页/函數

MySQL數據庫連接，《MYSQL必知必會》— 14~17.子查詢、聯結、高級聯結、組合查詢

 2023-12-09 阅读 26 评论 0

摘要：文章目錄1.如何使用子查詢1.1 什么是子查詢1.2 利用子查詢進行過濾—WHERE子句的IN操作符1.3 作為計算字段使用子查詢1.4 相關子查詢—限制有歧義的列名2.如何編寫使用聯結的SELECT語句2.1 聯結2.1.1 關系表2.1.2 為什么使用聯接2.2 創建聯結—WHERE2.2.1 笛卡爾積2.2.2 內部

文章目錄

      • 1.如何使用子查詢
        • 1.1 什么是子查詢
        • 1.2 利用子查詢進行過濾—WHERE子句的IN操作符
        • 1.3 作為計算字段使用子查詢
        • 1.4 相關子查詢—限制有歧義的列名
      • 2.如何編寫使用聯結的SELECT語句
        • 2.1 聯結
          • 2.1.1 關系表
          • 2.1.2 為什么使用聯接
        • 2.2 創建聯結—WHERE
          • 2.2.1 笛卡爾積
          • 2.2.2 內部聯結—INNER JOIN與ON
          • 2.2.3 聯結多個表
      • 3.如何對被聯結的表使用表別名和聚集函數以及創建高級聯結
        • 3.1 使用表別名—AS
        • 3.2 使用不同類型的聯結
          • 3.2.1 自聯結
          • 3.2.2 自然聯結
          • 3.2.3 外部聯結—LEFT OUTER J0IN
        • 3.3 使用帶聚集函數的聯結
        • 3.4 使用聯結及聯結條件
      • 4.組合查詢—UNION操作符
        • 4.1 組合查詢
        • 4.2 創建組合查詢
          • 4.2.1 UNION規則
          • 4.2.2 包含或取消重復的行
          • 4.2.3 對組合查詢結果排序

1.如何使用子查詢

1.1 什么是子查詢

??SELECT語句是SQL的查詢。所有SELECT語句都是簡單查詢，即從單個數據庫表中檢索數據的單條語句。SQL還允許創建子查詢(subquery)，即嵌套在其他查詢中的查詢。

1.2 利用子查詢進行過濾—WHERE子句的IN操作符

??本書所有章中使用的數據庫表都是關系表。訂單存儲在兩個表中。對于包含訂單號、客戶ID、訂單日期的每個訂單，orders表存儲一行。各訂單的物品存儲在相關的orderitems表中。orders表不存儲客戶信息。它只存儲客戶的ID。實際的客戶信息存儲在customers表中。假如需要列出訂購物品TNT2的所有客戶的客戶信息，應該怎樣檢索?

  1. MySQL數據庫連接？檢索包含物品TNT2的所有訂單的編號。

    SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2';

    在這里插入圖片描述

  2. 檢索具有前一步驟列出的訂單編號的所有客戶的ID

    SELECT cust_id
FROM orders
WHERE order_num IN (20005,20007);

    在這里插入圖片描述

  3. 檢索前一步驟返回的所有客戶ID的客戶信息。

    SELECT cust_name,cust_contact
FROM customers
WHERE cust_id IN (10001,10004);

    數據庫內連接查詢語句，在這里插入圖片描述

可以使用子查詢來把3個查詢組合成一條語句
在SELECT語句中，子查詢總是從內向外處理。

SELECT cust_name,cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_idFROM ordersWHERE order_num IN (SELECT order_numFROM orderitemsWHERE prod_id ='TNT2 '));

在這里插入圖片描述
為了執行上述SELECT語句，MySQL實際上必須執行3條SELECT語句。最里邊的子查詢返回訂單號列表,此列表用于其外面的子查詢的WHERE子句。外面的子查詢返回客戶ID列表，此客戶ID列表用于最外層查詢的WHERE子句。最外層查詢確實返回所需的數據。
??雖然子查詢一般與IN操作符結合使用，但也可以用于測試等于(=)、不等于(<>)等。

1.3 作為計算字段使用子查詢

??使用子查詢的另一方法是創建計算字段。假如需要顯示customers表中每個客戶的訂單總數,訂單與相應的客戶ID存儲在orders表中。需要如下步驟：

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

為了對每個客戶執行COUNT(*)計算，應該將COUNT (*)作為一個子查詢。

SELECT cust_name,cust _state,(SELECT COUNT(*) FROM ordersWHERE 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個客戶。

1.4 相關子查詢—限制有歧義的列名

??相關子查詢(correlated subquery)：涉及外部查詢的子查詢
WHERE orders.cust_id = customers.cust_id使用了完全限定列名，如果不使用限定列名會發生什么情況呢

SELECT cust_name,cust_state,(SELECT COUNT(*) FROM ordersWHERE cust_id = cust_id) AS orders
FROM customers
ORDER BY cust_name;

在這里插入圖片描述
顯然,返回的結果不正確(請比較前面的結果)，那么，為什么會這樣呢?有兩個cust_id列，一個在customers中，另一個在orders中，需要比較這兩個列以正確地把訂單與它們相應的顧客匹配。如果不完全限定列名，MySQL將假定你是對orders表中的cust_id進行自身比較。而SELECT COUNT(* )FROM orders WHERE cust_id= cust_id;總是返回orders表中的訂單總數（因為MySQL查看每個訂單的cust id是否與本身匹配，當然,它們總是匹配的)。

2.如何編寫使用聯結的SELECT語句

2.1 聯結

??SOL最強大的功能之一就是能在數據檢索查詢的執行中聯結（join)表。聯結是利用SQL的SELECT能執行的最重要的操作

2.1.1 關系表

??關系表的設計就是要保證把信息分解成多個表，一類數據一個表。各表通過某些常用的值(即關系設計中的關系 (relational))互相關聯。

MySQL子查詢？假如有一個包含產品目錄的數據庫表，其中每種類別的物品占一行。對于每種物品要存儲的信息包括產品描述和價格，以及生產該產品的供應商信息。假如有由同一供應商生產的多種物品，那么在何處存儲供應商信息（如供應商名、地址、聯系方法等）呢?
答：在這個例子中，可建立兩個表，一個存儲供應商信息，另一個存儲產品信息。vendors表包含所有供應商信息，每個供應商占一行，每個供應商具有唯一的標識。此標識稱為主鍵( primary key）可以是供應商ID或任何其他唯一值。products表只存儲產品信息，它除了存儲供應商ID (vendors表的主鍵)外不存儲其他供應商信息。vendors表的主鍵又叫作products的外鍵，它將vendors表與>products表關聯,利用供應商ID能從vendors表中找出相應供應商的詳細信息。
這樣做的好處如下:

  1. 供應商信息不重復，從而不浪費時間和空間;
  2. 如果供應商信息變動，可以只更新vendors表中的單個記錄，相關表中的數據不用改動;
  3. 由于數據無重復，顯然數據是一致的，這使得處理數據更簡單。

總之，關系數據可以有效地存儲和方便地處理。因此，關系數據庫的可伸縮性遠比非關系數據庫要好。

  1. 外鍵(foreign key):外鍵為某個表中的一列，它包含另一個表的主鍵值，定義了兩個表之間的關系。
  2. 可伸縮性(scale):能夠適應不斷增加的工作量而不失敗
2.1.2 為什么使用聯接

??如果數據存儲在多個表中，怎樣用單條SELECT語句檢索出數據?答案是使用聯結。聯結是用來在一條SELECT語句中關聯表。使用特殊的語法，可以聯結多個表返回一組輸出

維護引用完整性

  1. 聯結在實際的數據庫表中不存在。聯結由MySQL根據需要建立,它存在于查詢的執行當中。
  2. 在使用關系表時，僅在關系列中插入合法的數據非常重要。如果在products表中插入擁有非法供應商ID(即沒有在vendors表中出現)的供應商生產的產品，則這些產品是不可訪問的,因為它們沒有關聯到某個供應商。為防止這種情況發生,可指示MySQL只允許在products表的供應商ID列中出現合法值(即出現在vendors表中的供應商)。這就是維護引用完整性,它是通過在表的定義中指定主鍵和外鍵來實現的。

2.2 創建聯結—WHERE

SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend name, prod_name;

在這里插入圖片描述
在這里插入圖片描述
FROM子句列出了兩個表，分別是vendors和products。它們就是這條SELECT吾句聯結的兩個表的名字。這兩個表用WHERE子句正確聯結，WHERE子句旨示MySQL匹配vendors表中的vend_id和products表中的vend_id。這里需要這種完全限定列名vendors.vend_id = products.vend_id

2.2.1 笛卡爾積

連接查詢與子查詢的區別與聯系，??笛卡兒積:(cartesian product)由沒有聯結條件的表關系返回的結果為笛卡兒積。檢索出的行的數目將是第一個表中的行數乘以第二個表中的行數。

SELECT vend_name,prod_name,prod_price
FROM vendors,products
ORDER BY vend_name,prod_name;

這里返回的數據用每個供應商匹配了每個產品，它包括了供應商不正確的產品。

2.2.2 內部聯結—INNER JOIN與ON

??內部聯結：基于兩個表之間的相等測試。下面使用不同的語法來明確指定聯結的類型

SELECT vend_name,prod_name,prod_price
FROM vendors INNER J0IN products
ON vendors.vend_id = products.vend_id;

這里,兩個表之間的關系是FROM子句的組成部分，以INNER JOIN指定。在使用這種語法時，聯結條件用特定的ON子句而不是WHERE子句給出。傳遞給ON的實際條件與傳遞給WHERE的相同。

2.2.3 聯結多個表

??SQL對一條SELECT語句中可以聯結的表的數目沒有限制。創建聯結的基本規則：首先列出所有表，然后定義表之間的關系。

SELECT prod_name,vend_name,prod_price，quantity
FROM orderitems,products, vendors
WHERE products.vend_id = vendors.vend_id AND orderitems.prod_id = products.prod_id AND order_num = 20005;

子查詢和多表查詢？在這里插入圖片描述
此例子顯示編號為20005的訂單中的物品。訂單物品存儲在orderitems表中。每個產品按其產品ID存儲，它引用products表中的產品。這些產品通過供應商ID聯結到vendors表中相應的供應商，供應商ID存儲在每個產品的記錄中。這里的FROM子句列出了3個表，而WHERE子句定義了這兩個聯結條件，而第三個聯結條件用來過濾出訂單20005中的物品。

SELECT cust_name, cust_contact
FROM customers, orders,orderitems
WHERE customers.cust_id = orders.cust_id AND orderitems.order_num = orders.order num AND prod_id= 'TNT2';

在這里插入圖片描述
這里有3個WHERE子句條件。前兩個關聯聯結中的表，后一個過濾產品TNT2的數據。實現之前需要使用子查詢才能實現的操作

3.如何對被聯結的表使用表別名和聚集函數以及創建高級聯結

3.1 使用表別名—AS

??別名除了用于列名和計算字段外，SQL還允許給表名起別名。這樣做有兩個主要理由:

  1. 縮短SQL語句
  2. 允許在單條SELECT語句中多次使用相同的表
SELECT cust_name, cust_contact
FROM customers AS c,orders AS o,orderitems AS oi
WHERE c.cust_id = o.cust_id AND oi .order_num = o.order_num AND prod_id = 'TNT2';

表別名不僅能用于WHERE子句，它還可以用于SELECT的列表、ORDER BY子句以及語句的其他部分。應該注意，表別名只在查詢執行中使用。與列別名不一樣，表別名不返回到客戶機。

3.2 使用不同類型的聯結

??下面介紹三種聯結：自聯結、自然聯結和外部聯結

3.2.1 自聯結

Mysql查詢。??假如你發現某物品(其ID為DTNTR)存在問題，因此想知道生產該物品的供應商生產的其他物品是否也存在這些問題。此查詢要求首先找到生產D為DTNTR的物品的供應商，然后找出這個供應商生產的其他物品。

SELECT p1. prod_id,p1.prod_name
FROM products AS p1,products AS p2
WHERE p1.vend_id = p2. vend_id AND p2.prod_id = 'DTNTR';

在這里插入圖片描述
WHERE（通過匹配p1中的vend id和p2中的vend_id）首先聯結兩個表，然后按第二個表中的prod_id過濾數據，返回所需的數據。與以下子查詢返回的結果一致

SELECT prod_id,prod_name
FROM products
WHERE vend_id = (SELECT vend_idFROM productsWHERE prod_id ='DTNTR ');

自聯結通常作為外部語句用來替代從相同表中檢索數據時使用的子查詢語句。雖然最終的結果是相同的,但有時候處理聯結遠比處理子查詢快得多。所以用自聯結而不用子查詢

3.2.2 自然聯結

??無論何時對表進行聯結，應該至少有一個列出現在不止一個表中(被聯結的列)。內部聯結返回所有數據，甚至相同的列多次出現。自然聯結排除多次出現，使每個列只返回一次。這一般是通過對表使用通配符SELECT *，對所有其他表的列使用明確的子集來完成的。

SELECT c.*，o.order_num,o.order_date,oi.prod_id,oi.quantity，o1.item_price
FROM customers AS c,orders AS o,orderitems AS oi
WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = 'FB';

在這個例子中，通配符只對第一個表使用。所有其他列明確列出,所以沒有重復的列被檢索出來。

3.2.3 外部聯結—LEFT OUTER J0IN

??聯結包含了那些在相關表中沒有關聯行的行，這種類型的聯結稱為外部聯結。

SELECT customers.cust_id,orders.order_num 
FROM customers LEFT OUTER J0IN orders
ON customers.cust_id = orders.cust_id;

在這里插入圖片描述
在使用OUTER JOIN語法時，必須使用RIGHT或LEFT關鍵字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右邊的表,而LEFT指出的是OUTER JOIN左邊的表)。上面的例子使用LEFT OUTER JOIN從FROM子句的左邊表(customers表)中選擇所有行。為了從右邊的表中選擇所有行，應該使用RIGHT OUTER JOIN

SELECT customers.cust_id,orders.order_num
FROM customers RIGHT OUTER JOIN orders
ON orders.cust_id = customers.cust_id;

3.3 使用帶聚集函數的聯結

??聚集函數可以與聯結一起使用

SELECT customers.cust_name,customers. cust_id,COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
ON customers.cust_id = orders. cust_id
GROUP BY customers.cust_id;

在這里插入圖片描述
此SELECT語句使用INNER JOIN將customers和orders表互相關聯。GROUP BY子句按客戶分組數據，因此，函數調用COUNT(orders.order_num)對每個客戶的訂單計數，將它作為num_ord返回。

SELECT customers.cust_name,customers.cust_id,COUNT(orders.order_num) AS num_ord
FROM customers LEFT OUTER J0IN orders
ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;

在這里插入圖片描述
在這里插入圖片描述
這個例子使用左外部聯結來包含所有客戶，甚至包含那些沒有任何下訂單的客戶。結果顯示也包含了客戶Mouse House，它有0個訂單。

3.4 使用聯結及聯結條件

  1. 注意所使用的聯結類型。一般我們使用內部聯結，但使用外部聯結也是有效的。
  2. 保證使用正確的聯結條件,否則將返回不正確的數據。應該總是提供聯結條件，否則會得出笛卡兒積。
  3. 在一個聯結中可以包含多個表，甚至對于每個聯結可以采用不同的聯結類型。雖然這樣做是合法的，一般也很有用，但應該在一起測試它們前,分別測試每個聯結。這將使故障排除更為簡單。

4.組合查詢—UNION操作符

4.1 組合查詢

??多數SQL查詢都只包含從一個或多個表中返回數據的單條SELECT語句。MySQL也允許執行多個查詢(多條SELECT語句)，并將結果作為單個查詢結果集返回。這些組合查詢通常稱為并（union)或復合查詢。有兩種基本情況需要使用組合查詢:

  1. 在單個查詢中從不同的表返回類似結構的數據
  2. 對單個表執行多個查詢，按單個查詢返回數據

4.2 創建組合查詢

??可用UNION操作符來組合數條SQL查詢。利用UNION，可給出多條SELECT語句，將它們的結果組合成單個結果集。

SELECT vend_id， prod_id, prod_price
FROM products
WHERE prod_price<= 5;

在這里插入圖片描述
第一條SELECT檢索價格不高于5的所有物品。

SELECT vend_id，prod_id,prod_price
FROM products
WHERE vend_id IN (1001,1002);

在這里插入圖片描述
第二條SELECT使用IN找出供應商1001和1002生產的所有物品。

組合語句

SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price = 5
UNION
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN (1001,1002);

在這里插入圖片描述
這條語句由前面的兩條SELECT語句組成，語句中用UNION關鍵字分隔。UNION指示MySOL執行兩條SELECT語句，并把輸出組合成單個查詢結果集。

4.2.1 UNION規則

UNION是非常容易使用的。但有幾條規則需要注意:

  1. UNION必須由兩條或兩條以上的SELECT語句組成，語句之間用關鍵字UNION分隔（因此，如果組合4條SELECT語句，將要使用3個UNION關鍵字)。
  2. UNION中的每個查詢必須包含相同的列、表達式或聚集函數（不過各個列不需要以相同的次序列出)。
  3. 列數據類型必須兼容:類型不必完全相同，但必須是DBMS可以隱含地轉換的類型（例如，不同的數值類型或不同的日期類型)。
4.2.2 包含或取消重復的行

??UNION從查詢結果集中自動去除了重復的行(換句話說，它的行為與單條SELECT語句中使用多個WHERE子句條件一樣)。這是UNION的默認行為，但是如果需要，可以改變它。事實上，如果想返回所有匹配行，可使用UNION ALL而不是UNION。

SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5
UNION ALL
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN (1001,1002);

在這里插入圖片描述
使用UNION ALL,MySQL不取消重復的行。因此其中有一行出現兩次

4.2.3 對組合查詢結果排序

??SELECT語句的輸出用ORDER BY子句排序。在用UNION組合查詢時，只能使用一條ORDER BY子句，它必須出現在最后一條SELECT語句之后。對于結果集，不存在用一種方式排序一部分，而又用另一種方式排序另一部分的情況，因此不允許使用多條ORDER BY子句。

SELECT vend_id,prod_id,prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id,prod_price
FROM products
WHERE vend_id IN (1001,1002)
ORDER BY vend_id,prod_price;

在這里插入圖片描述
這條UNION在最后一條SELECT語句后使用了ORDER BY子句。雖然ORDER BY子句似乎只是最后一條SELECT語句的組成部分，但實際上MySQL將用它來排序所有SELECT語句返回的所有結果。

使用UNION的組合查詢可以組合不同的表

參考于《MYSQL必知必會》

如果對您有幫助，麻煩點贊關注，這真的對我很重要！！！如果需要互關，請評論或者私信！
在這里插入圖片描述

版权声明：本站所有资料均为网友推荐收集整理而来,仅供学习和研究交流使用。

原文链接：https://hbdhgg.com/3/193600.html

上一篇：NLP算法有哪些，nlp基礎—7.隱馬爾可夫模型(HMM算法)
下一篇：sql常用的聚合函數，《MYSQL必知必會》—10~13.創建計算字段、使用數據處理函數、使用聚集函數匯總數據，使用GROUP BY與HAVING分組數據及過濾
标签：MySQL數據庫連接數據庫內連接查詢語句子查詢和連接查詢 效率mysql子查詢連接查詢與子查詢的區別與聯系子查詢和多表查詢Mysql查詢數據庫左右內外連接詳細
MySQL數據庫連接

发表评论:

最新文章
阅读排行
猜你喜欢
热门标签

本站为非赢利网站，部分文章来源或改编自互联网及其他公众平台，主要目的在于分享信息，版权归原作者所有，内容仅供读者参考，如有侵权请联系我们删除！

Copyright © 2022 匯編語言學習筆記 Inc. 保留所有权利。

底部版权信息