學生基本資訊表

學生成績表

分組查詢-group by

 SELECT subject, AVG(score) FROM student_score GROUP BY subject;

這個查詢的執行過程就是按照subject中的值將所有的記錄分成兩組，然後分別對每個分組中記錄的score列呼叫AVG函式進行資料統計。

在使用分組的時候必須要意識到，分組的存在僅僅是為了方便我們分別統計各個分組中的資訊，所以我們只把分組列和聚集函式放到查詢列表處就好！
如果非分組列出現在查詢列表中會出現什麼情況呢？比如下邊這個查詢：

可以看到出現了錯誤。為啥會錯誤呢？回想一下我們使用GROUP BY子句的初衷，我們只是想把記錄分為若干組，然後再對各個組分別呼叫聚集函式去做一些統計工作。本例中的查詢列表處放置了既非分組列、又非聚集函式的number列，那我們想表達啥意思呢？從各個分組中的記錄中取一條記錄的number列？該取分組中的哪條記錄為好呢？比如對於數學這個分組而言，該分組有三條記錄，number=1，2，3，number該取哪一個呢？不知道。也就是說把非分組列放到查詢列表中會引起爭議，導致結果不確定,就會報錯。

帶有WHERE子句的分組查詢

將記錄先進行過濾然後分組

作用於分組的過濾條件-HAVING

其實這裡所謂的作用分組的條件一般是指下邊這兩種：

1、分組列
也就是說我們可以把用於分組的列放到HAVING子句的條件列中，比如這樣：

2、作用於分組的聚集函式

當然，並不是HAVING子句中只能放置在查詢列表出現的那些聚集函式，只要是針對這個分組進行統計的聚集函式都可以，比方說老師想查詢最高分大於98分的課程的平均分，可以這麼寫：

 SELECT subject, AVG(score) FROM student_score GROUP BY subject HAVING MAX(score) > 98;

其中的MAX(score)這個聚集函式並沒有出現在查詢列表中，但仍然可以作為HAVING子句中表示式的一部分。

分組和排序

如果我們想對各個分組查詢出來的統計資料進行排序，需要為查詢列表中有聚集函式的表示式新增別名，比如想按照各個學科的平均分從大到小降序排序，可以這麼寫：

巢狀分組

先按department分組，然後在department組再分major。

使用分組注意事項

要隨時提防有坑的地方：

如果分組列中含有NULL值，那麼NULL也會作為一個獨立的分組存在。

如果存在多個分組列，也就是巢狀分組，聚集函式將作用在最後的那個分組列上。

如果查詢語句中存在WHERE子句和ORDER BY子句，執行順序：WHERE–GROUP BY–ORDER BY

非分組列不能單獨出現在搜尋列表中(可以被放到聚集函式中)。（有待考證）

GROUP BY子句後也可以跟隨表示式(但不能是聚集函式)。

簡單查詢語句中各子句的順序

我們上邊介紹了查詢語句的各個子句，但是除了SELECT之外，其他的子句全都是可以省略的。如果在一個查詢語句中出現了多個子句，那麼它們之間的順序是不能亂放的，順序如下所示：

SELECT [DISTINCT] 查詢列表
[FROM 表名]                     # 表名都可以省
[WHERE 布林表示式]
[GROUP BY 分組列表 ]
[HAVING 分組過濾條件]
[ORDER BY 排序列表]
[LIMIT 開始行, 限制條數]

子查詢

小括號中的查詢語句也被稱為子查詢或者內層查詢，使用內層查詢的結果作為搜尋條件的運算元的查詢稱為外層查詢

標量子查詢

上面的查詢返回值只有一個（杜的學號20180102），這種子查詢稱之為標量子查詢。正因為標量子查詢單純的代表一個值，所以它可以作為表示式的運算元來參與運算，它除了用在外層查詢的搜尋條件中以外，也可以被放到查詢列表處，比如這樣：

列子查詢

很顯然第一條查詢語句的結果集中並不是一個單獨的值，而是一個列（本例中第一條查詢語句的結果集中該列包含2個值，分別是：20180101和20180102），所以它對應的子查詢也被稱之為列子查詢。因為列子查詢得到的結果是多個值，相當於一個列表。

行子查詢

只要子查詢的結果集中最多隻包含一條記錄，而且這條記錄中有超過一個列的資料（如果該條記錄只包含一個列的話，該子查詢就成了標量子查詢），那麼這個子查詢就可以被稱之為行子查詢，比如這樣：

SELECT * FROM student_score WHERE (number, subject) = (SELECT number, '母豬的產後護理' FROM student_info LIMIT 1);

小貼士： 在想要得到標量子查詢或者行子查詢，但又不能保證子查詢的結果集只有一條記錄時，應該使用LIMIT 1子句來限制記錄數量。

表子查詢

如果子查詢結果集中包含多行多列，那麼這個子查詢也可以被稱之為表子查詢，比如這樣：

SELECT * FROM student_score WHERE (number, subject) IN (SELECT number, '母豬的產後護理' FROM student_info WHERE major = '電腦科學與工程');

EXISTS和NOT EXISTS子查詢

有時候外層查詢並不關心子查詢中的結果是什麼，而只關心子查詢的結果集是不是為空集，這時可以用到下邊這兩個操作符：

舉例：

其中子查詢的意思是在student_info表中查詢學號為20180108的學生資訊，很顯然並沒有學號為20180108的學生，所以子查詢的結果集是一個空集，於是EXISTS表示式的結果為FALSE，所以外層查詢也就不查了，直接返回了一個Empty set，表示沒有結果

不相關子查詢和相關子查詢

前邊介紹的子查詢和外層查詢都沒有依賴關係，也就是說子查詢可以獨立執行併產生結果之後，再拿結果作為外層查詢的條件去執行外層查詢，這種子查詢稱為不相關子查詢。

而有時候我們需要在子查詢的語句中引用到外層查詢的值，這樣的話子查詢就不能當作一個獨立的語句去執行，這種子查詢被稱為相關子查詢。
比方說我們想檢視一些學生的基本資訊，但是前提是這些學生在student_score表中有成績記錄，那可以這麼寫：

SELECT number, name, id_number, major FROM student_info WHERE EXISTS 
(SELECT * FROM student_score WHERE student_score.number = student_info.number);

注： student_info和student_score表裡都有number列，所以在子查詢的WHERE語句中書寫number = number會造成二義性，也就是讓伺服器懵逼，不知道這個number列到底是哪個表的，所以為了區分，在列名前邊加上了表名，並用點.連線起來，這種顯式的將列所屬的表名書寫出來的名稱稱為該列的全限定名。所以上邊子查詢的WHERE語句中用了列的全限定名：student_score.number = student_info.number。

這條查詢語句可以分成這麼兩部分來理解：

• 我們要查詢學生的一些基本資訊。

• 這些學生必須符合這樣的條件：必須有成績記錄儲存在student_score表中。

所以這個例子中的相關子查詢的查詢過程是這樣的：

• 先執行外層查詢獲得到student_info表的第一條記錄，發現它的number值是20180101。把20180101當作引數傳入到子查詢，此時子查詢的意思是判斷student_score表的number欄位是否有20180101這個值存在，子查詢的結果是該值存在，所以整個EXISTS表示式的值為TRUE，那麼student_info表的第一條記錄可以被加入到結果集。

• 再執行外層查詢獲得到student_info表的第二條記錄，發現它的number值是20180102，與上邊的步驟相同，student_info表的第二條記錄也可以被加入到結果集。

• 與上邊類似，student_info表的第三條記錄也可以被加入到結果集。

• 與上邊類似，student_info表的第四條記錄也可以被加入到結果集。

• 再執行外層查詢獲得到student_info表的第五條記錄，發現它的number值是20180105，把20180105當作引數傳入到它的子查詢，此時子查詢的意思是判斷student_score表的number欄位是否有20180105這個值存在，子查詢的結果是該值不存在，所以整個EXISTS表示式的值為FALSE，那麼student_info表的第五條記錄就不被加入結果集中。

• 與上一步驟類似，student_info表的第六條記錄也不被加入結果集中。

• student_info表沒有更多的記錄了，結束查詢。

對同一個表的子查詢

其實不只是在涉及多個表查詢的時候會用到子查詢，在只涉及單個表的查詢中有時也會用到子查詢。比方說我們想看看在student_score表的’母豬的產後護理’這門課的成績中，有哪些超過了平均分的記錄，腦子中第一印象是這麼寫：

很抱歉，報錯了。為啥呢？因為聚集函式是用來對分組做資料統計的（如果沒有GROUP BY語句那麼意味著只有一個分組），而WHERE子句是以記錄為單位來執行過濾操作的，在WHERE子句執行完成之後才會得到分組，也就是說：聚集函式不能放到WHERE子句中！！！ 如果我們想實現上邊的需求，就需要搞一個student_score表的副本，就相當於有了兩個student_score表，在一個表上使用聚集函式統計，統計完了之後拿著統計結果再到另一個表中進行過濾，這個過程可以這麼寫：

SELECT * FROM student_score WHERE subject = '母豬的產後護理' AND 
score > (SELECT AVG(score) FROM student_score WHERE subject = '母豬的產後護理');

連線查詢-join

我們之前一直使用student_info和student_score兩個表來分別儲存學生的基本資訊和學生的成績資訊，其實合併成一張表也不是不可以，假設將兩張表合併後的新表名稱為student_merge，那它應該長這樣：

student_merge表

有了這個合併後的表，我們就可以在一個查詢語句中既查詢到學生的基本資訊，也查詢到學生的成績資訊，比如這個查詢語句：

SELECT number, name, major, subject, score FROM student_merge;

但是別忘了一個學生可能會有很多門學科的成績資訊，也就是說每當我們想為一個學生增加一門學科的成績資訊時，我們必須把他的基本資訊再抄一遍，這種同一個學生的基本資訊被冗餘儲存會帶來下邊的問題：

問題一：浪費儲存空間。

問題二：當修改某個學生的基本資訊時必須修改多處，很容易造成資訊的不一致，增大維護的困難。

所以為了儘可能少的儲存冗餘資訊，一開始我們就把這個所謂的student_merge表拆分成了student_info和student_score表，但是這兩張表之間有某種關係作為紐帶，這裡的某種關係指的就是兩個表都擁有的number列。

連線過程簡介

下邊我們就要看一下攜帶過濾條件的連線查詢的大致執行過程了，比方說下邊這個查詢語句：

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

在這個查詢中我們指明瞭這三個過濾條件：

t1.m1 > 1

t1.m1 = t2.m2

t2.n2 < 'd'

那麼這個連線查詢的大致執行過程如下：

1、首先確定第一個需要查詢的表，這個表稱之為驅動表。此處假設使用t1作為驅動表，那麼就需要到t1表中找滿足t1.m1 > 1的記錄，符合這個條件的t1表記錄如下所示：

2、上一步驟中從驅動表每獲取到一條記錄，都需要到t2表中查詢匹配的記錄，所謂匹配的記錄，指的是符合過濾條件的記錄。因為是根據t1表中的記錄去找t2表中的記錄，所以t2表也可以被稱之為被驅動表。上一步驟從驅動表中得到了2條記錄，也就意味著需要查詢2次t2表。此時涉及兩個表的列的過濾條件t1.m1 = t2.m2就派上用場了：

• 對於從t1表種查詢得到的第一條記錄，也就是當t1.m1 = 2, t1.n1 = 'b’時，過濾條件t1.m1 = t2.m2就相當於t2.m2 = 2，所以此時t2表相當於有了t2.m2 = 2、t2.n2 < 'd’這兩個過濾條件，然後到t2表中執行單表查詢，將得到的記錄和從t1表中查詢得到的第一條記錄相組合得到下邊的結果：
  
• 對於從t1表種查詢得到的第二條記錄，也就是當t1.m1 = 3, t1.n1 = 'c’時，過濾條件t1.m1 = t2.m2就相當於t2.m2 = 3，所以此時t2表相當於有了t2.m2 = 3、t2.n2 < 'd’這兩個過濾條件，然後到t2表中執行單表查詢，將得到的記錄和從t1表中查詢得到的第二條記錄相組合得到下邊的結果：
  
  所以整個連線查詢的執行最後得到的結果集就是這樣：
  
  從上邊兩個步驟可以看出來，我們上邊嘮叨的這個兩表連線查詢共需要查詢1次t1表，2次t2表。當然這是在特定的過濾條件下的結果，如果我們把t1.m1 > 1這個條件去掉，那麼從t1表中查出的記錄就有3條，就需要查詢3次t2表了。也就是說在兩表連線查詢中，驅動表只需要查詢一次，被驅動表可能會被查詢多次。

內連線和外連線

1、對於內連線的兩個表，驅動表中的記錄在被驅動表中找不到匹配的記錄，該記錄不會加入到最後的結果集，我們上邊提到的連線都是所謂的內連線。

2、對於外連線的兩個表，驅動表中的記錄即使在被驅動表中沒有匹配的記錄，也仍然需要加入到結果集。

在MySQL中，根據選取驅動表的不同，外連線仍然可以細分為2種：

• 左外連線：選取左側的表為驅動表。

• 右外連線： 選取右側的表為驅動表。

可是這樣仍然存在問題，即使對於外連線來說，有時候我們也並不想把驅動表的全部記錄都加入到最後的結果集。這就犯難了，有時候匹配失敗要加入結果集，有時候又不要加入結果集，這咋辦？
把過濾條件分為兩種不就解決了這個問題了麼，所以放在不同地方的過濾條件是有不同語義的：

• WHERE子句中的過濾條件

WHERE子句中的過濾條件就是我們平時見的那種，不論是內連線還是外連線，凡是不符合WHERE子句中的過濾條件的記錄都不會被加入最後的結果集。

• ON子句中的過濾條件

對於外連線的驅動表的記錄來說，如果無法在被驅動表中找到匹配ON子句中的過濾條件的記錄，那麼該記錄仍然會被加入到結果集中，對應的被驅動表記錄的各個欄位使用NULL值填充。

注意：
這個ON子句是專門為外連線驅動表中的記錄在被驅動表找不到匹配記錄時應不應該把該記錄加入結果集這個場景下提出的，所以如果把ON子句放到內連線中，MySQL會把它和WHERE子句一樣對待，也就是說：內連線中的WHERE子句和ON子句是等價的。

一般情況下，我們都把只涉及單表的過濾條件放到WHERE子句中，把涉及兩表的過濾條件都放到ON子句中，我們也一般把放到ON子句中的過濾條件也稱之為連線條件。

左連線的語法

SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 連線條件 [WHERE 普通過濾條件];

對於左（外）連線和右（外）連線來說，必須使用ON子句來指出連線條件。

右（外）連線的語法

SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 連線條件 [WHERE 普通過濾條件];

內連線的語法

內連線和外連線的根本區別就是在驅動表中的記錄不符合ON子句中的連線條件時不會把該記錄加入到最後的結果集，我們最開始嘮叨的那些連線查詢的型別都是內連線。不過之前僅僅提到了一種最簡單的內連線語法，就是直接把需要連線的多個表都放到FROM子句後邊。其實針對內連線，MySQL提供了好多不同的語法，我們以t1和t2表為例瞅瞅：

SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 連線條件] [WHERE 普通過濾條件];

下邊這幾種內連線的寫法都是等價的：推薦INNER JOIN的形式書寫內連線

SELECT * FROM t1 JOIN t2;

SELECT * FROM t1 INNER JOIN t2;

SELECT * FROM t1 CROSS JOIN t2;
SELECT  *  FROM t1, t2;

由於在內連線中ON子句和WHERE子句是等價的，所以內連線中不要求強制寫明ON子句。

對於內連線來說，驅動表和被驅動表是可以互換的，並不會影響最後的查詢結果。但是對於外連線來說，由於驅動表中的記錄即使在被驅動表中找不到符合ON子句連線條件的記錄也會被加入結果集，所以此時驅動表和被驅動表的關係就很重要了，也就是說左外連線和右外連線的驅動表和被驅動表不能輕易互換。

多表連線

SELECT * FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.m1 = t2.m2 AND t1.m1 = t3.m3;
等價於：
SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2 INNER JOIN t3 ON t1.m1 = t3.m3;

這個查詢的執行過程用虛擬碼表示一下就是這樣：

for each row in t1 {

    for each row in t2 which satisfies t1.m1 = t2.m2 {
        
        for each row in t3 which satisfies t1.m1 = t3.m3 {
            send to client;
        }
    }
}

其實不管是多少個表的連線，本質上就是各個表的記錄在符合過濾條件下的自由組合。

自連線

我們上邊說的都是多個不同的表之間的連線，其實同一個表也可以進行連線。比方說我們可以對兩個t1表來生成笛卡爾積，就像這樣：

mysql> SELECT * FROM t1, t1;
ERROR 1066 (42000): Not unique table/alias: 't1'

咦，報了個錯，這是因為設計MySQL的大叔不允許FROM子句中出現相同的表名。我們這裡需要的是兩張一模一樣的t1表進行連線，為了把兩個一樣的表區分一下，需要為表定義別名。比如這樣：

 SELECT * FROM t1 AS table1, t1 AS table2;

這裡相當於我們為t1表定義了兩個副本，一個是table1，另一個是table2，這裡的連線過程就不贅述了，大家把它們認為是不同的表就好了。由於被連線的表其實是源自同一個表，所以這種連線也稱為自連線。我們看一下這個自連線的現實意義，比方說我們想檢視與’史珍香’相同專業的學生有哪些，可以這麼寫：

SELECT s2.number, s2.name, s2.major FROM student_info AS s1 INNER JOIN student_info AS s2 
WHERE s1.major = s2.major AND s1.name = '史珍香' ;

連線查詢與子查詢的轉換

有的查詢需求既可以使用連線查詢解決，也可以使用子查詢解決，比如

SELECT * FROM student_score WHERE number IN (SELECT number FROM student_info WHERE major = '電腦科學與工程');

這個子查詢就可以被替換：

SELECT s2.* FROM student_info AS s1 INNER JOIN student_score AS s2 WHERE s1.number = s2.number AND s1.major = '電腦科學與工程';

大家在實際使用時可以按照自己的習慣來書寫查詢語句。

小貼士： MySQL伺服器在內部可能將子查詢轉換為連線查詢來處理，當然也可能用別的方式來處理，不過對於我們剛入門的小白來說，這些都不重要，知道這個語句會把哪些資訊查出來就好了！

組合查詢-Union

組合查詢通常用在涉及不同表的查詢語句中，比方說下邊這兩個查詢：

第一個查詢是從t1表中查詢m1, n1這兩個列的資料，第二個查詢是從t2表中查詢m2, n2這兩個列的資料。我們可以使用UNION直接將這兩個查詢語句拼接到一起：

包含或去除重複的行

我們看下邊這兩個查詢：

很顯然，t1表裡有3條記錄，t2表裡有3條記錄，我們使用UNION把它們合併起來看一下：

為什麼合併後的結果只剩下了4條記錄呢？因為使用UNION來合併多個查詢的記錄會預設過濾掉重複的記錄。由於t1表和t2表都有(2, b)、(3, c)這兩條記錄，所以合併後的結果集就把他倆去重了。如果我們想要保留重複記錄，可以使用UNION ALL來連線多個查詢：

組合查詢中的ORDER BY和LIMIT子句

組合查詢會把各個查詢的結果彙總到一塊，如果我們相對最終的結果集進行排序或者只保留幾行的話，可以在組合查詢的語句末尾加上ORDER BY和LIMIT子句，就像這樣：

這裡需要注意的一點是，由於最後的結果集展示的列名是第一個查詢中給定的列名，所以ORDER BY子句中指定的排序列也必須是第一個查詢中給定的列名（別名也可以）。