目錄
1.基本查詢回顧
準備測試表：
?下面給出三張表，分別是員工表（emp）、部門表（dept）和工資等級表（）。
?后續(xù)所要進行的查詢操作都將以這三張表作為數(shù)據(jù)源，包括基本查詢和復合查詢。
員工表（emp）中包含如下字段
部門表（dept）中包含如下字段
工資等級表（）中包含如下字段
?雇員編號（empno）
?雇員姓名（ename）
?雇員職位（job）
?雇員領(lǐng)導編號（mgr）
?雇傭時間（）
?工資月薪（sal）
?獎金（comm）
?部門編號（deptno）
?部門編號（deptno）
?部門名稱（dname）
?部門所在地點（loc）
?等級（grade）
?此等級最低工資（losal）
?此等級最高工資（hisal）
員工表（emp）中的內(nèi)容如下：
部門表（dept）中的內(nèi)容如下：
工資等級表（）中的內(nèi)容如下：
查詢工資高于500或崗位為MANAGER的員工，同時要求員工姓名的首字母為大寫的J：
在where子句中指明篩選條件為工資高于500或崗位為MANAGER，并且通過模糊匹配指明員工姓名的首字母為大寫的J，在select的column列表中指明要查詢的列為姓名、工資和崗位。如下：
查詢員工信息，按部門號升序而員工工資降序顯示：
在select的column列表中指明要查詢的列為姓名、部門號和工資，在order by子句中依次指明按部門號排升序和按員工工資排降序，即不同部門的員工按照部門號排升序，而同一部門的員工按員工工資排降序。如下：
查詢員工信息，按年薪降序顯示：
在select的column列表中指明要查詢的列為姓名和年薪，在order by子句中指明按年薪進行降序排序。如下：
說明一下：
?由于NULL與任何值做計算得到的結(jié)果都是NULL，因此在計算年薪時不能直接用月薪的12倍加上每個員工的獎金，這樣可能導致得到的年薪為NULL值。
?在計算每個員工的年薪時，應該通過ifnull函數(shù)判斷員工的獎金是否為NULL，如果不為NULL則ifnull函數(shù)返回員工的獎金，如果為NULL則ifnull函數(shù)返回0，避免讓NULL值參與計算。
查詢工資最高的員工的姓名和崗位：
解決該問題需要進行兩次查詢，先對員工表進行一次查詢得到最高工資，然后再根據(jù)最高工資對員工表進行一次查詢，得到工資等于最高工資的員工的姓名和崗位。如下：
此外，這種問題還可以使用子查詢，將第一次查詢的SQL語句用括號括起來，作為最高工資直接在第二次查詢的SQL語句中使用。如下：
查詢工資高于平均工資的員工信息：
解決該問題也需要進行兩次查詢，先對員工表進行一次查詢得到平均工資，然后再根據(jù)平均工資對員工表進行一次查詢，篩選出工資高于平均工資的員工信息，該問題同樣可以使用子查詢。如下：
查詢每個部門的平均工資和最高工資：
在group by子句中指明按照部門號進行分組，在select語句中使用avg函數(shù)和max函數(shù)，分別查詢每個部門的平均工資和最高工資。如下：
查詢平均工資低于2000的部門號和它的平均工資：
在group by子句中指明按照部門號進行分組，在select語句中使用avg函數(shù)查詢每個部門的平均工資，在having子句中指明篩選條件為平均工資小于2000。如下：
查詢每種崗位的雇員總數(shù)和平均工資：
在group by子句中指明按照崗位進行分組，在select語句中使用count函數(shù)和avg函數(shù)，分別查詢每種崗位的雇員總數(shù)和平均工資。如下：
2.多表查詢
多表查詢：
?上面的基礎(chǔ)查詢都是在一張表的基礎(chǔ)上進行的查詢，而實際開發(fā)中往往需要將多張表關(guān)聯(lián)起來進行查詢，這就叫做多表查詢。
?在進行多表查詢時，只需要將多張表的表名依次放到from子句之后，用逗號隔開即可，這時MySQL將會對給定的這多張表取笛卡爾積，作為多表查詢的初始數(shù)據(jù)源。
?多表查詢的本質(zhì)，就是對給定的多張表取笛卡爾積，然后在笛卡爾積中進行查詢。
所謂的對多張表取笛卡爾積，就是得到這多張表的記錄的所有可能有序?qū)M成的集合，比如下面對員工表和部門表進行多表查詢，由于查詢語句中沒有指明篩選條件，因此最終得到的結(jié)果便是員工表和部門表的笛卡爾積。
說明一下：
?員工表和部門表的笛卡爾積由兩部分組成，前半部分是員工表的列信息，后半部分是部門表的列信息。
?對員工表和部門表取笛卡爾積時，會先從員工表中選出一條記錄與部門表中的所有記錄進行組合，然后再從員工表中選出一條記錄與部門表中的所有記錄進行組合，以此類推，最終得到的就是這兩張表的笛卡爾積。
笛卡爾積的初步過濾：
需要注意的是，對多張表取笛卡爾積后得到的數(shù)據(jù)并不都是有意義的，比如對員工表和部門表取笛卡爾積時，員工表中的每一個員工信息都會和部門表中的每一個部門信息進行組合，而實際一個員工只有和自己所在的部門信息進行組合才是有意義的，因此需要從笛卡爾積中篩選出員工的部門號和部門的編號相等記錄。如下：
注：進行笛卡爾積的多張表中可能會存在相同的列名，這時在選中列名時需要通過 表名.列名 的方式進行指明。
解決多表查詢的思路：
1.根據(jù)需求，確定和哪些表有關(guān)。
2.通過笛卡爾積和初步過濾，“無腦”組合成一張表（多張表轉(zhuǎn)成一張表）。
3.將多表查詢看作成為一張表的查詢。
顯示部門號為10的部門名、員工名和員工工資：
由于部門名只有部門表中才有，而員工名和員工工資只有員工表中才有，因此需要同時使用員工表和部門表進行多表查詢，在where子句中指明篩選條件為員工的部門號等于部門編號，并且部門號為10的記錄。如下：
注：第一個篩選條件已經(jīng)篩選出員工的部門號和部門編號相等的記錄，因此在篩選部門號等于10的部門時，可以使用員工表中的部門號，也可以使用部門表中的部門編號。
顯示各個員工的姓名、工資和工資級別：
由于員工名和工資只有員工表中才有，而工資級別只有工資等級表中才有，因此需要同時使用員工表和工資等級表進行多表查詢，在where子句中指明篩選條件為員工的工資在losal和hisal之間的記錄。如下：
注：
1.員工表和工資等級表的笛卡爾積中，將每一個員工的信息和每一個工資等級的信息都進行了組合，而實際一個員工只有和自己的工資對應的工資等級信息進行組合才是有意義的。
2.因此需要根據(jù)各個工資等級的最低工資和最高工資判斷一個員工是否屬于該工資等級，進而篩選出有意義的記錄。
3.自連接
自連接：
?自連接是指在同一張表進行連接查詢，也就是說我們不僅可以取不同表的笛卡爾積，也可以對同一張表取笛卡爾積。
?如果一張表中的某個字段能夠?qū)⒈碇械亩鄺l記錄關(guān)聯(lián)起來，那么就可以通過自連接將表中通過該字段關(guān)聯(lián)的記錄組合起來。
?表也是可以取別名的，自鏈接的時候至少要給其中一張表取別名，否則無法區(qū)分這兩張表中的列。
顯示員工FORD的上級領(lǐng)導的編號和姓名：
解決該問題可以使用子查詢，先對員工表進行查詢得到FORD的領(lǐng)導的編號，然后再根據(jù)領(lǐng)導的編號對員工表進行查詢得到FORD領(lǐng)導的姓名。如下：
此外，解決該問題也可以使用自連接，因為員工表中的mgr字段能夠?qū)⒈碇袉T工的信息和員工領(lǐng)導的信息關(guān)聯(lián)起來。如下：
對員工表進行自連接后，在where子句中指明篩選條件為員工的領(lǐng)導編號等于領(lǐng)導的編號，這時就能篩選出每個員工信息與其領(lǐng)導信息組合形成的記錄，進一步指明篩選條件為員工的姓名為FORD，這時便能篩選出員工FORD的信息和他的領(lǐng)導的信息組成的記錄。如下：
注：由于自連接是對同一張表取笛卡爾積，因此在自連接時至少需要給一張表取別名，否則無法區(qū)分這兩張表中的列。
4.子查詢
子查詢：
?子查詢是指嵌入在其他SQL語句中的查詢語句，也叫嵌套查詢。
?子查詢可分為單行子查詢、多行子查詢、多列子查詢，以及在from子句中使用的子查詢。
4.1.單行子查詢
?單行子查詢，是指返回單行單列數(shù)據(jù)的子查詢。
顯示SMITH同一部門的員工：
在子查詢中查詢SMITH所在的部門號，在where子句中指明篩選條件為員工部門號等于子查詢返回的部門號，并且員工的姓名不為SMITH。如下：
此外，解決該問題也可以使用自連接，因為和SMITH同一部門的員工的信息也在員工表當中，因此對員工表進行自連接后，在where子句中指明表1的員工姓名為SMITH，并且表1和表2的部門號必須相等，并且表2的員工姓名不為SMITH，這樣也能篩選出和SMITH同一部門的員工信息。如下：
4.2.多行子查詢
?多行子查詢，是指返回多行單列數(shù)據(jù)的子查詢
4.2.1.in關(guān)鍵字
顯示和10號部門的工作崗位相同的員工的名字、崗位、工資和部門號，但是不包含10號部門的員工：
先查詢10號部門有哪些工作崗位，在查詢時最好對結(jié)果進行去重，因為10號部門的某些員工的工作崗位可能是相同的。如下：
然后將上述查詢作為子查詢，在查詢員工表時在where子句中使用in關(guān)鍵字，判斷員工的工作崗位是子查詢得到的若干崗位中的一個，如果是則符合篩選條件，由于要求篩選出來的員工不包含10號部門的，因此還需要在where子句中指明篩選條件為部門號不等于10。如下：
4.2.2.all關(guān)鍵字
顯示工資比30號部門的所有員工的工資高的員工的姓名、工資和部門號：
先查詢30號部門員工的工資，在查詢時最好對結(jié)果進行去重，因為30號部門的某些員工的工資可能是相同的。如下：
然后將上述查詢作為子查詢，在查詢員工表時在where子句中使用all關(guān)鍵字，判斷員工的工資是否高于子查詢得到的所有工資，如果是則符合篩選條件。如下：
但實際這道題也等價于找到工資高于30號部門的最高工資的員工，因此也可以使用單行子查詢得到30號部門的最高工資，然后判斷員工的工資是否高于子查詢得到的最高工資即可。如下：
4.2.3.any關(guān)鍵字
顯示工資比30號部門的任意員工的工資高的員工的姓名、工資和部門號，包含30號部門的員工：
解決該題目也需要先查詢30號部門員工的工資，然后在查詢員工表時在where子句中使用any關(guān)鍵字，判斷員工的工資是否高于子查詢的得到的工資中的某一個，如果是則符合篩選條件。如下：
但實際這道題也等價于找到工資高于30號部門的最低工資的員工，因此也可以使用單行子查詢得到30號部門的最低工資，然后判斷員工的工資是否高于子查詢得到的最低工資即可，由于要求篩選出來的員工包含30號部門的，因此不需要再對部門號進行過濾。如下：
4.3.多列子查詢
?多列子查詢，是指返回單行多列數(shù)據(jù)的子查詢。
顯示和SMITH的部門和崗位完全相同的員工，不包含SMITH本人：
先查詢SMITH所在部門的部門號和他的崗位。如下：
然后將上述查詢作為子查詢，在查詢員工表時在where子句中，指明篩選條件為部門號和崗位等于子查詢得到的部門號和崗位，并且員工的姓名不為SMITH即可。如下：
注：
1.多列子查詢得到的結(jié)果是多列數(shù)據(jù)，在比較多列數(shù)據(jù)時需要將待比較的多個列用圓括號括起來。
2.多列子查詢返回的如果是多行數(shù)據(jù)，在篩選數(shù)據(jù)時也可以使用in、all和any關(guān)鍵字。
4.4.在from子句中使用子查詢
mysql表的大一統(tǒng)思想：
?在我們看來mysql永遠會有一張表。
?如果只有一張表，要進行各種select查詢，所有select查出來的結(jié)果都是一張表。
?如果有多張表，進行查詢之前可以進行select整合，整合查出來的結(jié)果也是一張表。
?子查詢語句不僅可以出現(xiàn)在where子句中，也可以出現(xiàn)在from子句中。
?子查詢語句出現(xiàn)from子句中，其查詢結(jié)果將會被當作一個臨時表使用。
?多行子查詢解決的是多行單列數(shù)據(jù)的子查詢，多列子查詢解決的是單行多列數(shù)據(jù)的子查詢，而對于多行多列數(shù)據(jù)的子查詢，我們可以使用在from子句中使用子查詢的方法解決。
顯示每個高于自己部門平均工資的員工的姓名、部門、工資和部門的平均工資：
首先查詢每個部門的平均工資。如下：
由于顯示信息中包含部門的平均工資，需要同時使用員工表和上述的查詢結(jié)果進行多表查詢，這時可以將上述查詢作為子查詢放在from子句中，然后對員工表和臨時表取笛卡爾積，在where子句中指明篩選條件為員工的部門號等于臨時表中的部門號，并且員工的工資大于臨時表中的平均工資。如下：
注：在from子句中使用子查詢時，必須給子查詢得到的臨時表取一個別名，否則查詢將會出錯。
顯示每個部門工資最高的員工的姓名、工資、部門和部門的最高工資：
先查詢每個部門的最高工資。如下：
將上述查詢作為子查詢放在from子句中，然后對員工表和臨時表取笛卡爾積，在where子句中指明篩選條件為員工的部門號等于臨時表中的部門號，并且員工的工資等于臨時表中的最高工資。如下：
顯示每個部門的部門名、部門編號、所在地址和人員數(shù)量：
在group by子句中指明按照部門號進行分組，分別查詢每個部門的人員數(shù)量。如下：
將上述查詢作為子查詢放在from子句中，然后對員工表和臨時表取笛卡爾積，在where子句中指明篩選條件為員工的部門號等于臨時表中的部門號即可。如下：
此外，除了上述子查詢+多表查詢的方式外，也可以只使用多表查詢解決該問題。
?先對員工表和部門表取笛卡爾積。
?在where子句中指明篩選條件為員工的部門號等于部門的編號，篩選出有意義的記錄。
?在order by子句中指明按照部門號進行分組，分別統(tǒng)計出每個部門的人數(shù)。
如下：
但由于題目同時要求顯示每個部門的部門名和所在地址，因此在group by子句中需要添加按照部門名和地址進行分組。如下：
說明一下：
?因為在select語句中新增了要顯示部門名和所在地址，因此需要在group by子句中也添加這兩個字段，表明當部門號相同時按照部門名進行分組，當部門名也相同時繼續(xù)按照所在地址進行分組。
?但實際在上述場景中部門號相同的記錄，它們的部門名和所在地址也一定是相同的，因此在我們看來group by中繼續(xù)添加這兩個字段沒什么意義，但MySQL語句要求我們必須添加。
5.合并查詢
合并查詢，是指將多個查詢結(jié)果進行合并，可使用的操作符有union和union all。
?union用于取得兩個查詢結(jié)果的并集，union會自動去掉結(jié)果集中的重復行。
?union all也用于取得兩個查詢結(jié)果的并集，但union all不會去掉結(jié)果集中的重復行。
顯示工資大于2500或職位是MANAGER的員工：
查詢工資大于2500的員工的SQL如下：
查詢職位是MANAGER的員工的SQL如下：
要查詢工資大于2500或職位是MANAGER的員工，可以使用or操作符將where子句中的兩個條件關(guān)聯(lián)起來。如下：
在合并查詢這里，可以使用union操作符將上述的兩條查詢SQL連接起來，這時將會得到兩次查詢結(jié)果的并集，并且會對合并后的結(jié)果進行去重。如下：
此外，也可以使用union all操作符將上述的兩條查詢SQL連接起來，這時將也會得到兩次查詢結(jié)果的并集，但不會對合并后的結(jié)果進行去重。如下：
說明一下：
?待合并的兩個查詢結(jié)果的列的數(shù)量必須一致，否則無法合并。
?待合并的兩個查詢結(jié)果對應的列屬性可以不一樣，但不建議這樣做。