一、子查詢
MySQL 4.1版本及以上支持子查詢
子查詢:嵌套在其他查詢中的查詢。
子查詢的作用:
1、進行過濾:
實例1:檢索訂購物品TNT2的所有客戶的ID
= +
一般,在WHERE子句中對于能嵌套的子查詢的數(shù)目沒有限制,不過在實際使用時由于性能的限制,不能嵌套太多的子查詢。
注意:列必須匹配 ——在WHERE子句中使用子查詢(如這里所示),應(yīng)該保證SELECT語句具有與WHERE子句中相同數(shù)目的列。通常,子查詢將返回單個列并且與單個列匹配,但如果需要也可以使用多個列。
示例2:返回訂購產(chǎn)品TNT2的客戶列表
該實例更為有效的方法是采用聯(lián)結(jié)進行查詢:
注意:具體關(guān)于聯(lián)結(jié)的內(nèi)容下文會整理到。
2、創(chuàng)建計算字段:
相關(guān)子查詢:涉及外部查詢的子查詢。當列名可能有多義性時必須使用該語法。
實例:顯示customers 表中每個客戶的訂單總數(shù)
總結(jié):
子查詢最常見的使用是在WHERE子句的IN操作符中,以及用來填充計算列
子查詢建立(和測試)查詢的最可靠的方法是逐漸進行, 這與MySQL處理它們的方法非常相同。首先,建立和測試最內(nèi)層的查詢。然后,用硬編碼數(shù)據(jù)建立和測試外層查詢,并且僅在確認它正常后才嵌入子查詢。這時,再次測試它。對于要增加的每個查詢,重復(fù)這些步驟。這樣做僅給構(gòu)造查詢增加了一點點時間,但節(jié)省了以后(找出查詢?yōu)槭裁床徽#┑拇罅繒r間,并且極大地提高了查詢一開始就正常工作的可能性。
二、聯(lián)結(jié)表
聯(lián)結(jié)表是SQL最強大的功能之一
1、一些相關(guān)的基礎(chǔ)知識儲備:
關(guān)系表:保證把信息分解成多個表,一類數(shù)據(jù)一個表。各表通過某些常用的值(即關(guān)系設(shè)計中的關(guān)系(relational))互相關(guān)聯(lián)。節(jié)省時間和存儲空間,同時方便數(shù)據(jù)的修改、更新。因此,關(guān)系數(shù)據(jù)庫的可伸縮性遠比非關(guān)系數(shù)據(jù)庫要好。
可伸縮性(scale):能夠適應(yīng)不斷增加的工作量而不失敗。設(shè)計良好的數(shù)據(jù)庫或應(yīng)用程序稱之為可伸縮性好。
聯(lián)結(jié):聯(lián)結(jié)是一種機制,用來在一條SELECT語句中關(guān)聯(lián)表,可以聯(lián)結(jié)多個表返回一組輸出。
聯(lián)結(jié)不是物理實體——它在實際的數(shù)據(jù)庫表中不存在。聯(lián)結(jié)由MySQL根據(jù)需要建立,它存在于查詢的執(zhí)行當中。
在使用關(guān)系表時,僅在關(guān)系列中插入合法的數(shù)據(jù)非常重要。為防止這種情況發(fā)生,需要維護引用完整性,它是通過在表的定義中指定主鍵和外鍵來實現(xiàn)的。
2、基礎(chǔ)聯(lián)結(jié):
實例1:
這兩個表用WHERE子句正確聯(lián)結(jié):WHERE子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id。注意:在引用的列可能出現(xiàn)二義性時,必須使用完全限定列名(用一個點分隔的表名和列名)。
在一條SELECT語句中聯(lián)結(jié)幾個表時,相應(yīng)的關(guān)系是在運行中構(gòu)造的,在數(shù)據(jù)庫表的定義中不存在能指示MySQL如何對表進行聯(lián)結(jié)的東西。在聯(lián)結(jié)兩個表時,實際上是將第一個表中的每一行與第二個表中的每一行配對。WHERE子句作為過濾條件,它只包含那些匹配給定條件(這里是聯(lián)結(jié)條件)的行。沒有WHERE子句,第一個表中的每個行將與第二個表中的每個行配對,而不管它們邏輯上是否可以配在一起。
笛卡兒積:由沒有聯(lián)結(jié)條件的表關(guān)系返回的結(jié)果。檢索出的行的數(shù)目將是第一個表中的行數(shù)乘以第二個表中的行數(shù)。有時也被稱為叉聯(lián)結(jié)。
實例2:顯示編號為20005的訂單中的物品
應(yīng)該保證所有聯(lián)結(jié)都有WHERE子句,否則MySQL將返回比想要的數(shù)據(jù)多得多的數(shù)據(jù)。
MySQL在運行時關(guān)聯(lián)指定的每個表以處理聯(lián)結(jié)。這種處理可能是非常耗費資源的,因此應(yīng)該仔細,不要聯(lián)結(jié)不必要的表。聯(lián)結(jié)的表越多,性能下降越厲害。
等值聯(lián)結(jié):基于兩個表之間的相等測試,也被稱為內(nèi)部聯(lián)結(jié)。(最經(jīng)常使用的聯(lián)結(jié)方式)
實例:
ANSI SQL規(guī)范首選INNER JOIN語法。此外,盡管使用WHERE子句定義聯(lián)結(jié)的確比較簡單,但是使用明確的聯(lián)結(jié)語法能夠確保不會忘記聯(lián)結(jié)條件,有時候這樣做也能影響性能。
3、高級聯(lián)結(jié):
實例1:給表起別名(同給列起別名用法一樣)
注意:表別名只在查詢執(zhí)行中使用。與列別名不一樣,表別名不返回到客戶機。
使用表別名的主要原因之一是能在單條SELECT語句中不 止一次引用相同的表
實例2:查詢生產(chǎn)ID為DTNTR的物品的供應(yīng)商生產(chǎn)的其他物品
上述解決方法為自聯(lián)結(jié),自聯(lián)結(jié)通常作為外部語句用來替代從相同表中檢索數(shù)據(jù)時使用的子查詢語句。該實例也可用子查詢來解決。雖然最終的結(jié)果是相同的,但有時候處理聯(lián)結(jié)遠比處理子查詢快得多。在解決問題時,可以試一下兩種方法,以確定哪一種的性能更好。
自然聯(lián)結(jié):排除多次出現(xiàn),使每個列只返回一次。一般我們用到的內(nèi)部聯(lián)結(jié)都是自然聯(lián)結(jié) 。
實例3:自然聯(lián)結(jié)
自然聯(lián)結(jié)一般是通過對表使用通配符(SELECT *),對所有其他表的列使用明確的子集來完成的。
外部聯(lián)結(jié):聯(lián)結(jié)包含了那些在相關(guān)表中沒有關(guān)聯(lián)行的行。
實例4:檢索所有客戶,包括那些沒有訂單的客戶
用法與內(nèi)部聯(lián)結(jié)相似,使用了關(guān)鍵字OUTER JOIN來指定聯(lián)結(jié)的類型。但是,與內(nèi)部聯(lián)結(jié)關(guān)聯(lián)兩個表中的行不同的是,外部聯(lián)結(jié)還包括沒有關(guān)聯(lián)行的行。
存在兩種基本的外部聯(lián)結(jié)形式:左外部聯(lián)結(jié)和右外部聯(lián)結(jié)。在使用OUTER JOIN語法時,必須使用RIGHT或LEFT關(guān)鍵字指定包括其所有行的表(RIGHT指出的是OUTER JOIN右邊的表,而LEFT 指出的是OUTER JOIN左邊的表)。上面的例子使用LEFT OUTER JOIN從FROM 子句的左邊表(customers表)中選擇所有行。
注意:MySQL不支持簡化字符*=和=*的使用,盡管這兩種操作符在其他DBMS中很流行。
實例5:檢索所有客戶及每個客戶所下的訂單數(shù)(包括沒有下任何訂單的客戶
聚集函數(shù)可以方便地與各種聯(lián)結(jié)類型一起使用
使用聯(lián)結(jié)和聯(lián)結(jié)條件:
- 1. 注意所使用的聯(lián)結(jié)類型。一般我們使用內(nèi)部聯(lián)結(jié),但使用外部聯(lián) 結(jié)也是有效的。
- 2. 保證使用正確的聯(lián)結(jié)條件,否則將返回不正確的數(shù)據(jù)。
- 3. 應(yīng)該總是提供聯(lián)結(jié)條件,否則會得出笛卡兒積。
- 4. 在一個聯(lián)結(jié)中可以包含多個表,甚至對于每個聯(lián)結(jié)可以采用不同的聯(lián)結(jié)類型。雖然這樣做是合法的,一般也很有用,但應(yīng)該在一起測試它們前,分別測試每個聯(lián)結(jié)。這將使故障排除更為簡單。
三、組合查詢
組合查詢:執(zhí)行多個查詢(多條SELECT語句),并將結(jié)果作為單個查詢結(jié)果集返回。這些組合查詢通常稱為并(union)或復(fù)合查詢。
為何需要組合查詢?
- 在單個查詢中從不同的表返回類似結(jié)構(gòu)的數(shù)據(jù);
- 對單個表執(zhí)行多個查詢,按單個查詢返回數(shù)據(jù);
- 使用組合查詢可極大地簡化復(fù)雜的WHERE子句,簡化從多個表中檢索數(shù)據(jù)的工作。
1、創(chuàng)建組合查詢
關(guān)鍵字:UNION操作符
實例1:得到價格小于等于5的所有物品的一個列表,并且包括供應(yīng)商1001和1002生產(chǎn)的所有物品(不考慮價格)。
UNION指示MySQL執(zhí)行兩條SELECT語句,并把輸出組合成單個查詢結(jié)果集。該解法與where prod_price=5 OR vend_id in(1001,1002);等效
使用并時需要注意的規(guī)則:
- 1. UNION必須由兩條或兩條以上的SELECT語句組成,語句之間用關(guān)鍵字UNION分隔(因此,如果組合4條SELECT語句,將要使用3個UNION關(guān)鍵字)。
- 2. UNION中的每個查詢必須包含相同的列、表達式或聚集函數(shù)(不過各個列不需要以相同的次序列出)。
- 3. 列數(shù)據(jù)類型必須兼容:類型不必完全相同,但必須是DBMS可以隱含地轉(zhuǎn)換的類型(例如,不同的數(shù)值類型或不同的日期類型)
- 4. 使用UNION的組合查詢可以應(yīng)用不同的表
在一些簡單的例子中,使用UNION可能比使用WHERE子句更為復(fù)雜。 但對于更復(fù)雜的過濾條件,或者從多個表(而不是單個表)中檢索數(shù)據(jù)的情形,使用UNION可能會使處理更簡單。
UNION默認從查詢結(jié)果集中自動去除重復(fù)的行,如果 想返回所有匹配行,可使用UNION ALL而不實UNION。
注意:UNION幾乎總是完成與多個WHERE條件相同的工作。UNION ALL為UNION的一種形式,它完成WHERE子句完成不了的工作。如果確實需要每個條件的匹配行全部出現(xiàn)(包括重復(fù)行),則必須使用UNION ALL而不是WHERE
實例2:對組合查詢結(jié)果排序
在用UNION組合查詢時,只能使用一條ORDER BY子句,它必須出現(xiàn)在最后一條SELECT語句之后。對于結(jié)果集,不存在用一種方式排序一部分,而又用另一種方式排序另一部分的情況,因此不允許使用多條ORDER BY子句。該ORDER BY子句對所有SELECT語句返回的所有結(jié)果進行排序。
以上所述是小編給大家介紹的MySQL子查詢(嵌套查詢)、聯(lián)結(jié)表、組合查詢詳解整合,希望對大家有所幫助,如果大家有任何疑問請給我留言,小編會及時回復(fù)大家的。在此也非常感謝大家對腳本之家網(wǎng)站的支持!
您可能感興趣的文章:- MySQL高級查詢之與Group By集合使用介紹
- MySQL修煉之聯(lián)結(jié)與集合淺析