mysql SELECT查詢
一、單表查詢
1、一般查詢。2、聚合函數、排序 3、別名。4、分組。5、分組過濾。6、限制顯示條目。7、雜項。
二、多表查詢
1、聯結查詢。2、子查詢。3、聯合查詢。
數據庫版本：5.5.46-MariaDB
說明一下這幾張表，這是在上馬哥課程的時候給的生成表的sql備份文件。
在文章最後我把它放到附件中。
注意：在linux上表名是區分大小寫的。
如果搞不清語句順序請看：help select
一、單表查詢
1、一般查詢
MariaDB [hellodb]> SELECT * FROM students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.05 sec)
MariaDB [hellodb]> SELECT StuID,Name,Age FROM students WHERE Age > 25;
+-------+--------------+-----+
| StuID | Name | Age |
+-------+--------------+-----+
| 3 | Xie Yanke | 53 |
| 4 | Ding Dian | 32 |
| 5 | Yu Yutong | 26 |
| 6 | Shi Qing | 46 |
| 13 | Tian Boguang | 33 |
| 24 | Xu Xian | 27 |
| 25 | Sun Dasheng | 100 |
+-------+--------------+-----+
7 rows in set (0.02 sec)
SELECT中的WHERE子句就是一個布爾條件表達式，來判斷行是否區配表達式。只要返回的為真，也就是不為0，則WHERE子句就為真，就會顯示匹配的行。
布爾條件表達式操作符：
= 等於，用於數值或字符都可以。
<=> 也是等值比較，不過不會跟空產生意外情況。是跟空值比較的安全方式。
<> 不等於，這個就只能用於數值了。
<
<=
>
>=
空字符跟空是不一樣的。 空字符也是一種字符串，也是有自己的ASCII碼和值的。
IS NULL 判斷是否為空
IS NOT NULL 判斷是否為不空
LIKE
模糊匹配，支持通配符，% 百分號表示任意個任意字符。_ 下劃線任意單個字符。在能用等值比較或不等值比較的情況下不要用LIKE，性能差的多。
RLIKE，REGEXP
支持使用正則表達式。性能更低。LIKE, RLIKE只能用來做字符的比較。
也可以完整的匹配數值，不過也沒有意義。
IN
判斷指定的字段的值是否在給定的列表中， IN （‘abc','cc')
BETWEEN AND
判斷指定的字段是否在給定的范圍之間。
如 x>=20 AND x<=40 這種，可以用 X BETWEEN 20 AND 40
組合條件：
NOT ，!
AND ,，&&
OR，||
注意： 在mysql中只要是字符型的在使用的時候都要加引號，而如果是數值型的，一定不能加引號。
例1：IS NULL， 判斷ClassID字段為空的記錄。只顯示Name,Age,ClassID.
MariaDB [hellodb]> SELECT Name,Age,ClassID FROM students WHERE ClassID IS NULL;
+-------------+-----+---------+
| Name | Age | ClassID |
+-------------+-----+---------+
| Xu Xian | 27 | NULL |
| Sun Dasheng | 100 | NULL |
+-------------+-----+---------+
2 rows in set (0.00 sec)
例2：用LIKE來模糊匹配Name字段所有以X開頭的行。%通配任意個任意字符。
MariaDB [hellodb]> SELECT Name FROM students WHERE Name LIKE 'X%';
+-------------+
| Name |
+-------------+
| Xie Yanke |
| Xi Ren |
| Xu Zhu |
| Xue Baochai |
| Xiao Qiao |
| Xu Xian |
+-------------+
6 rows in set (0.00 sec)
下面的效果跟上面的相同。這裡是用正則表達式匹配的。
1
MariaDB [hellodb]> SELECT Name FROM students WHERE Name RLIKE '^X.*';
例3：IN。下面是查找ClassID是1或3或5的記錄。只顯示Name和ClassID字段。
MariaDB [hellodb]> SELECT Name,ClassID FROM students WHERE ClassID IN (1,3,5);
+--------------+---------+
| Name | ClassID |
+--------------+---------+
| Shi Potian | 1 |
| Yu Yutong | 3 |
| Shi Qing | 5 |
| Xi Ren | 3 |
| Yue Lingshan | 3 |
| Wen Qingqing | 1 |
| Lu Wushuang | 3 |
| Xu Zhu | 1 |
| Xiao Qiao | 1 |
+--------------+---------+
9 rows in set (0.00 sec)
MariaDB [hellodb]>
例4：BETWEEN AND ，匹配一個范圍。年齡在30到50之間。
MariaDB [hellodb]> SELECT Name,Age FROM students WHERE Age BETWEEN 30 AND 50;
+--------------+-----+
| Name | Age |
+--------------+-----+
| Ding Dian | 32 |
| Shi Qing | 46 |
| Tian Boguang | 33 |
+--------------+-----+
3 rows in set (0.00 sec)
MariaDB [hellodb]>
例5：組合AND，gender為m，並且，Age大於30或等於20。這個括號是一定要有的，不然就變成“gender為M並且Age大於30，或者Age等於20。
MariaDB [hellodb]> SELECT * FROM students WHERE gender='M' AND (Age > 30 OR Age = 20);
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+--------------+-----+--------+---------+-----------+
5 rows in set (0.01 sec)
想以年齡排序。可以用
ORDER BY [ASC|DESC|字段]
ASC表示升序， DESC表示降序。 默認是ASC
以年齡降序排列。
MariaDB [hellodb]> SELECT * FROM students WHERE gender='M' AND (Age > 30 OR Age = 20) ORDER BY Age DESC;
+-------+--------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+--------------+-----+--------+---------+-----------+
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 |
+-------+--------------+-----+--------+---------+-----------+
5 rows in set (0.01 sec)
MariaDB [hellodb]>
這些只是一般的查詢，如果要統計數據，就要用聚合函數了。
2、聚合函數。
話說想統計下一共有多少人，或者女的有多少，男的有多少，平均年齡，最小最大年齡。
SUM(), AVG(), MAX(), MIN(), COUNT()
分別是求和、平均值、最大、最小、統計個數。這幾個是常用到的。
例6：SUM()，全體同學年齡總和。可以用WHERE加上條件，如男同學的年齡總和。
MariaDB [hellodb]> SELECT SUM(Age) FROM students;
+----------+
| SUM(Age) |
+----------+
| 685 |
+----------+
1 row in set (0.00 sec)
也可以顯示其它字段，不過也只是一行。
例7：AVG()，全體同學的年齡平均值。
MariaDB [hellodb]> SELECT AVG(Age) FROM students;
+----------+
| AVG(Age) |
+----------+
| 27.4000 |
+----------+
1 row in set (0.00 sec)
例8：COUNT(), 統計一共多少學生。COUNT後面有的會使用*。COUNT(*)，這樣也可以，不過性能差點。
MariaDB [hellodb]> SELECT COUNT(Name) FROM students;
+-------------+
| COUNT(Name) |
+-------------+
| 25 |
+-------------+
1 row in set (0.00 sec)
我們也可以不讓它顯示上面的字段名稱，給它換一個名稱。
3、AS 別名。
MariaDB [hellodb]> SELECT COUNT(Name) AS CC FROM students;
+----+
| CC |
+----+
| 25 |
+----+
1 row in set (0.00 sec)
還有表也可以有別名，在多表查詢的時候再來說說。
下面男同學的最小年齡，並用別名顯示。
MariaDB [hellodb]> SELECT MIN(Age) AS Min_M FROM students WHERE gender = 'M';
+-------+
| Min_M |
+-------+
| 19 |
+-------+
1 row in set (0.00 sec)
可不可以一次性男女分開顯示各自的最小年齡。那就要用分組了。可以按性別gender來分組。這樣函數就會分別計算各組的數據。
4、分組。
GROUP BY 字段名
以字段的值分組。同一個值一個組。然後再通過用聚合函數來統計不同組中的信息。
現在以gender分組，也就是兩組。函數分別計算兩個組。
不過下面這個有點缺陷，不知道哪是女的，哪個是男的。
MariaDB [hellodb]> SELECT MIN(Age) FROM students GROUP BY gender;
+----------+
| MIN(Age) |
+----------+
| 17 |
| 19 |
+----------+
2 rows in set (0.00 sec)
MariaDB [hellodb]>
下面再顯示出來性別字段。
MariaDB [hellodb]> SELECT MIN(Age),gender FROM students GROUP BY gender;
+----------+--------+
| MIN(Age) | gender |
+----------+--------+
| 17 | F |
| 19 | M |
+----------+--------+
2 rows in set (0.00 sec)
MariaDB [hellodb]>
例：顯示不同班級的學生個數
MariaDB [hellodb]> SELECT Count(Name),ClassID FROM students GROUP BY classID;
+-------------+---------+
| Count(Name) | ClassID |
+-------------+---------+
| 2 | NULL |
| 4 | 1 |
| 3 | 2 |
| 4 | 3 |
| 4 | 4 |
| 1 | 5 |
| 4 | 6 |
| 3 | 7 |
+-------------+---------+
8 rows in set (0.00 sec)
MariaDB [hellodb]>
不顯示沒有班級的。WHERE在分組之前先進行過濾，然後把數據再給GROUP BY來進行分組。
MariaDB [hellodb]> SELECT Count(Name),ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY classID;
+-------------+---------+
| Count(Name) | ClassID |
+-------------+---------+
| 4 | 1 |
| 3 | 2 |
| 4 | 3 |
| 4 | 4 |
| 1 | 5 |
| 4 | 6 |
| 3 | 7 |
+-------------+---------+
7 rows in set (0.00 sec)
MariaDB [hellodb]>
例9：各個班級的平均年齡。
MariaDB [hellodb]> SELECT AVG(age),ClassID FROM students GROUP BY ClassID;
+----------+---------+
| AVG(age) | ClassID |
+----------+---------+
| 63.5000 | NULL |
| 20.5000 | 1 |
| 36.0000 | 2 |
| 20.2500 | 3 |
| 24.7500 | 4 |
| 46.0000 | 5 |
| 20.7500 | 6 |
| 19.6667 | 7 |
+----------+---------+
8 rows in set (0.00 sec)
MariaDB [hellodb]>
加上排序呢：
MariaDB [hellodb]> SELECT AVG(age),ClassID FROM students GROUP BY ClassID ORDER BY AVG(age);
+----------+---------+
| AVG(age) | ClassID |
+----------+---------+
| 19.6667 | 7 |
| 20.2500 | 3 |
| 20.5000 | 1 |
| 20.7500 | 6 |
| 24.7500 | 4 |
| 36.0000 | 2 |
| 46.0000 | 5 |
| 63.5000 | NULL |
+----------+---------+
8 rows in set (0.00 sec)
意思就是在分組之後，把各個分組重新排序了。以各個組的age字段的平均值來排序。
回來看分組：如果不想顯示平均年齡小於等於25的，怎麼辦呢。
5、分組過濾。
HAVING 用於對分組做條件過濾。
普及：WHERE是對表中的每一行做過濾，單位是行。 而HAVING是對每一個組做過濾，單位是組。
如：
MariaDB [hellodb]> SELECT AVG(age),ClassID FROM students GROUP BY ClassID HAVING AVG(age)>25;
+----------+---------+
| AVG(age) | ClassID |
+----------+---------+
| 63.5000 | NULL |
| 36.0000 | 2 |
| 46.0000 | 5 |
+----------+---------+
3 rows in set (0.00 sec)
MariaDB [hellodb]>
HAVING拿到手的都是一組一組的數據，所以也要求下平均值。然後不匹配的組，就刷掉。到了SELECT那裡，它求一下平均值是為了顯示。這是兩個不同的部分。
如果想找age小於AVG(age)之類的結果，這裡是查不出來的。在子查詢部分。
例10：顯示最少有3個同學的班級和該班級的人數。
MariaDB [hellodb]> SELECT ClassID,Count(Name) FROM students GROUP BY ClassID HAVING Count(Name) >= 3;
+---------+-------------+
| ClassID | Count(Name) |
+---------+-------------+
| 1 | 4 |
| 2 | 3 |
| 3 | 4 |
| 4 | 4 |
| 6 | 4 |
| 7 | 3 |
+---------+-------------+
6 rows in set (0.00 sec)
MariaDB [hellodb]>
這個表小，這樣顯示還可以，但是如果有上千上萬個的條目，一下子顯示出來就有點誇張了，占網絡帶寬不說，一下子出來這麼多，也看不完啊。
6、限制顯示條目的數量。
LIMIT
只顯示3行。在最後加上limit 3就可以了。
MariaDB [hellodb]> SELECT * FROM students LIMIT 3;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
這是從頭開始，顯示3行。如果想從中間開始。下面這個是從第5行開始，顯示3行。
MariaDB [hellodb]> SELECT * FROM students LIMIT 5,3;
+-------+-----------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-----------+-----+--------+---------+-----------+
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
+-------+-----------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
7、雜項。
DISTINCT : 指定的結果相同的只顯示一次。在SELECT 語句後面。
SQL_CACHE : 緩存此條語句至查詢緩存中。
SQL_NO_CACHE: 說明不緩存此條語句。
簡單的例子說明下第一個吧。如果顯示都有哪些年齡的同學，除了用分組外。還可以用DISTINCT。只不過只能顯示一個字段。
age字段重復的就不顯示，並且排序。 不排序也沒有關系。
MariaDB [hellodb]> SELECT DISTINCT age FROM students ORDER BY age;
+-----+
| age |
+-----+
| 17 |
| 18 |
| 19 |
| 20 |
| 21 |
| 22 |
| 23 |
| 25 |
| 26 |
| 27 |
| 32 |
| 33 |
| 46 |
| 53 |
| 100 |
+-----+
15 rows in set (0.00 sec)
到這裡單表查詢就完了。我們來看看這麼多語句它的執行流程。
SELECT語句的執行流程：
FROM --> WHERE --> GROUP BY --> HAVING --> ORDER BY --> SELECT --> LIMIT
首先是FROM獲取表數據，然後WHERE篩選，再然後GROUP BY來分組，再然後HAVING給組再來一下過濾，再然後就是ORDER BY給剩下的組或是整張表的行排序，再然後才是SELECT把最終整理好的數據計算或者直接顯示出來，當然到達客戶端還要經過LIMIT限制。
二、多表查詢。
我們知道關系型數據庫就是為了降低冗余，所以都是把內容記錄到多張表中，我們在查詢的時候要把多張表連起來才能查到所有數據。
說明一下，因為表的內容都貼出來的話就太多了，所以這裡就只舉例子了，具體的表內容，朋友們自己下載看吧。
MariaDB [hellodb]> SHOW TABLES;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.00 sec)
1、聯結查詢
聯結查詢： 先將幾張表join起來， 然後再根據join以後所產生的表，來進行查詢。
有：
交叉聯結、自然聯結、外聯結、自聯結。
交叉聯結：
就是各個表的各字段的值相乘的關系。各種連結，各種交叉。這裡也只是提一下。
直接FROM表就是了。
1
MariaDB [hellodb]> SELECT * FROM students,coc,classes;
自然聯結：
又叫內聯結或等值聯結，兩張表要有相同的字段可以建立聯結。用WHERE 聯結條件。一般情況下都是使用自然聯結。
舉例子先，上面的students表中有學生信息，而classes表中有班級的名稱。現在想顯示學生的名子和所對應的班級名稱。
那麼就需要student和classes建立結結。正好它們都有classID班級編號。所以：
MariaDB [hellodb]> SELECT students.Name,classes.Class FROM students,classes WHERE students.ClassID = classes.ClassID;
+---------------+----------------+
| Name | Class |
+---------------+----------------+
| Shi Zhongyu | Emei Pai |
| Shi Potian | Shaolin Pai |
| Xie Yanke | Emei Pai |
| Ding Dian | Wudang Pai |
*
*
23 rows in set (0.00 sec)
中間省略了，不然太多。 上面用WHERE來做兩個表的等值條件。
把字段全部顯示出來看看：
MariaDB [hellodb]> SELECT * FROM students,classes WHERE students.ClassID = classes.ClassID;
+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+
| StuID | Name | Age | Gender | ClassID | TeacherID | ClassID | Class | NumOfStu |
+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 2 | Emei Pai | 7 |
| 2 | Shi Potian | 22 | M | 1 | 7 | 1 | Shaolin Pai | 10 |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 2 | Emei Pai | 7 |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Wudang Pai | 12 |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 3 | QingCheng Pai | 11 |
| 6 | Shi Qing | 46 | M | 5 | NULL | 5 | Riyue Shenjiao | 31 |
| 7 | Xi Ren | 19 | F | 3 | NULL | 3 | QingCheng Pai | 11 |
ClassID都是相等的。這裡還有一個問題就是，在多表連結的時候會有多個字段一樣的，所以在寫的時候要把表名也給寫上，就是這種格式students.ClassID之類的。但是有的表名又很長，這個時候就可以用別名了。在FROM後面的表名後面使用AS。FROM 表名 AS 別名
MariaDB [hellodb]> SELECT * FROM students AS STU,classes AS CLA WHERE STU.ClassID = CLA.ClassID;
+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+
| StuID | Name | Age | Gender | ClassID | TeacherID | ClassID | Class | NumOfStu |
+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 2 | Emei Pai | 7 |
| 2 | Shi Potian | 22 | M | 1 | 7 | 1 | Shaolin Pai | 10 |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 2 | Emei Pai | 7 |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Wudang Pai | 12 |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 3 | QingCheng Pai | 11 |
| 6 | Shi Qing | 46 | M | 5 | NULL | 5 | Riyue Shenjiao | 31 |
多表連結也簡單，就是表多了以後會暈乎。所以主要問題就是要熟悉自己的各種表。
有內連結，自然就有外連結。內連結把表的字段的數值與另一張表連接起來，但是並不是所有記錄都可以連接起來，比如上面的students表中還有兩個人沒有顯示出來，因為他們沒有班級。
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
如這兩位仁兄。那麼如果我也想把這兩個顯示出來。就要用到外連接了。
外聯結：外聯結又分為“左外連結和右外連結”。其實意思都一樣，就是以哪個為主，主表所有的都顯示出來，別一張表如果對不上就為NULL。
左外聯結 以左表為基准，右表沒有的為NULL.
left_tb LEFT JOIN right_tb ON 連接條件
右外聯結 以右表為基准，左表沒有的為NULL.
left_tb RIGHT JOIN right_tb ON 連接條件
全外聯結 以兩個表為基准，哪個沒有哪個為NULL. mysql中沒有。
MariaDB [hellodb]> SELECT * FROM students LEFT JOIN classes ON students.ClassID=classes.ClassID;
+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+
| StuID | Name | Age | Gender | ClassID | TeacherID | ClassID | Class | NumOfStu |
+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+
| 18 | Hua Rong | 23 | M | 7 | NULL | 7 | Ming Jiao | 27 |
| 19 | Xue Baochai | 18 | F | 6 | NULL | 6 | Lianshan Pai | 27 |
| 20 | Diao Chan | 19 | F | 7 | NULL | 7 | Ming Jiao | 27 |
| 21 | Huang Yueying | 22 | F | 6 | NULL | 6 | Lianshan Pai | 27 |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | 1 | Shaolin Pai | 10 |
| 23 | Ma Chao | 23 | M | 4 | NULL | 4 | Wudang Pai | 12 |
| 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | NULL | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+---------+----------------+----------+
就是這樣的了。打個比方，如果classes的表有額外的ClassID,如8，9，10之類的，而students表中沒有，那麼也是不會顯示出來的。因為左外連接是以左表為准，管你右邊的表有什麼。而右外連接就是以右邊的表為准了。當然在寫表的時候把classes表寫左邊也是一樣的。
這些表還可以作三個表甚至四個表連接的操作。比如加上成績。大家就自己試試吧。
2、子查詢：
在查詢中嵌套的查詢。
用於WHERE中的子查詢
1、用於比較表達式中的子查詢。子查詢的返回值只能有一個
2、用於EXISTS中的子查詢,判斷存在與否。
3、用於IN中的子查詢,判斷存在於指定的列表中。
4、用於FROM中的子查詢，SELECT * FROM (SELECT clause) AS alias。這裡一定要用別名。
5、在SELECT中也可以用子語句的值來作為一個字段。
先解決查詢age>AVG(age)的問題。為什麼在上面那裡不能用，因為這種寫法就是錯的。一行還是一組呢。
1、放到WHERE後，WHERE語句的數據是一行一行的，age是可以表示當前行的age值。但是AVG(age)就有問題了，它只能放在GROUP BY後面來計算組的平均值，或是SELECT後面全表的平均值。
2、放到HAVING後面，同樣的問題。是一組數據。
如果要查詢就要用子查詢先計算平均值。
查詢所有同學年齡大於平均年齡的。
MariaDB [hellodb]> SELECT Name,Age FROM students WHERE age > (SELECT AVG(age) FROM students);
+--------------+-----+
| Name | Age |
+--------------+-----+
| Xie Yanke | 53 |
| Ding Dian | 32 |
| Shi Qing | 46 |
| Tian Boguang | 33 |
| Sun Dasheng | 100 |
+--------------+-----+
5 rows in set (0.00 sec)
延伸一下：顯示平均年齡：
MariaDB [hellodb]> SELECT Name,Age,(SELECT AVG(age) FROM students) AS avg_age FROM students WHERE age > (SELECT AVG(age) FROM students);
+--------------+-----+---------+
| Name | Age | avg_age |
+--------------+-----+---------+
| Xie Yanke | 53 | 27.4000 |
| Ding Dian | 32 | 27.4000 |
| Shi Qing | 46 | 27.4000 |
| Tian Boguang | 33 | 27.4000 |
| Sun Dasheng | 100 | 27.4000 |
+--------------+-----+---------+
5 rows in set (0.00 sec)
那麼再延伸一下，顯示在各個班級內同學，大於班級內年齡平均值的。
有點復雜，我這裡是這樣作的。
第一步：求出各個班內的平均年齡。
MariaDB [hellodb]> SELECT AVG(age),ClassID FROM students GROUP BY ClassID;
+----------+---------+
| AVG(age) | ClassID |
+----------+---------+
| 63.5000 | NULL |
| 20.5000 | 1 |
| 36.0000 | 2 |
| 20.2500 | 3 |
| 24.7500 | 4 |
| 46.0000 | 5 |
| 20.7500 | 6 |
| 19.6667 | 7 |
+----------+---------+
8 rows in set (0.00 sec)
第二步：以上面這個結果與students表建立連接。
MariaDB [hellodb]> SELECT * FROM students,(SELECT AVG(age),ClassID FROM students GROUP BY ClassID) AS avg_age WHERE students.ClassID=avg_age.ClassID;
+-------+---------------+-----+--------+---------+-----------+----------+---------+
| StuID | Name | Age | Gender | ClassID | TeacherID | AVG(age) | ClassID |
+-------+---------------+-----+--------+---------+-----------+----------+---------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 36.0000 | 2 |
| 2 | Shi Potian | 22 | M | 1 | 7 | 20.5000 | 1 |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 36.0000 | 2 |
| 4 | Ding Dian | 32 | M | 4 | 4 | 24.7500 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 20.2500 | 3 |
| 6 | Shi Qing | 46 | M | 5 | NULL | 46.0000 | 5 |
| 7 | Xi Ren | 19 | F | 3 | NULL | 20.2500 | 3 |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | 19.6667 | 7 |
| 9 | Ren Yingying | 20 | F | 6 | NULL | 20.7500 | 6 |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | 20.2500 | 3 |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 20.7500 | 6 |
| 12 | Wen Qingqing | 19 | F | 1 | NULL | 20.5000 | 1 |
| 13 | Tian Boguang | 33 | M | 2 | NULL | 36.0000 | 2 |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | 20.2500 | 3 |
| 15 | Duan Yu | 19 | M | 4 | NULL | 24.7500 | 4 |
| 16 | Xu Zhu | 21 | M | 1 | NULL | 20.5000 | 1 |
| 17 | Lin Chong | 25 | M | 4 | NULL | 24.7500 | 4 |
| 18 | Hua Rong | 23 | M | 7 | NULL | 19.6667 | 7 |
| 19 | Xue Baochai | 18 | F | 6 | NULL | 20.7500 | 6 |
| 20 | Diao Chan | 19 | F | 7 | NULL | 19.6667 | 7 |
| 21 | Huang Yueying | 22 | F | 6 | NULL | 20.7500 | 6 |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | 20.5000 | 1 |
| 23 | Ma Chao | 23 | M | 4 | NULL | 24.7500 | 4 |
+-------+---------------+-----+--------+---------+-----------+----------+---------+
23 rows in set (0.00 sec)
第三步：這裡就直接作判斷就可以了。
MariaDB [hellodb]> SELECT * FROM students,(SELECT AVG(age) AS avg_age_col,ClassID FROM students GROUP BY ClassID) AS avg_age_tab WHERE students.ClassID=avg_age_tab.ClassID AND Age > avg_age_col ORDER BY students.ClassID;
+-------+---------------+-----+--------+---------+-----------+-------------+---------+
| StuID | Name | Age | Gender | ClassID | TeacherID | avg_age_col | ClassID |
+-------+---------------+-----+--------+---------+-----------+-------------+---------+
| 2 | Shi Potian | 22 | M | 1 | 7 | 20.5000 | 1 |
| 16 | Xu Zhu | 21 | M | 1 | NULL | 20.5000 | 1 |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 36.0000 | 2 |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 20.2500 | 3 |
| 17 | Lin Chong | 25 | M | 4 | NULL | 24.7500 | 4 |
| 4 | Ding Dian | 32 | M | 4 | 4 | 24.7500 | 4 |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 20.7500 | 6 |
| 21 | Huang Yueying | 22 | F | 6 | NULL | 20.7500 | 6 |
| 18 | Hua Rong | 23 | M | 7 | NULL | 19.6667 | 7 |
+-------+---------------+-----+--------+---------+-----------+-------------+---------+
9 rows in set (0.00 sec)
MariaDB [hellodb]>
再來一個：要瘋了。這個我這樣寫總覺得有點復雜了。不知道大家有沒有簡略點的。
如何顯示其成員數最少為3個的班級的同學中年齡大於同班同學平均年齡的同學？
MariaDB [hellodb]> SELECT SQL_NO_CACHE * FROM (SELECT AVG(age) AS A,ClassID FROM students WHERE ClassID IN (SELEct ClassID FROM students GROUP BY ClassID HAVING COUNT(*) >= 3) GROUP BY ClassID) AS s,students WHERE students.ClassID=s.ClassID AND age > A;
+---------+---------+-------+---------------+-----+--------+---------+-----------+
| A | ClassID | StuID | Name | Age | Gender | ClassID | TeacherID |
+---------+---------+-------+---------------+-----+--------+---------+-----------+
| 20.5000 | 1 | 2 | Shi Potian | 22 | M | 1 | 7 |
| 36.0000 | 2 | 3 | Xie Yanke | 53 | M | 2 | 16 |
| 24.7500 | 4 | 4 | Ding Dian | 32 | M | 4 | 4 |
| 20.2500 | 3 | 5 | Yu Yutong | 26 | M | 3 | 1 |
| 20.7500 | 6 | 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 20.5000 | 1 | 16 | Xu Zhu | 21 | M | 1 | NULL |
| 24.7500 | 4 | 17 | Lin Chong | 25 | M | 4 | NULL |
| 19.6667 | 7 | 18 | Hua Rong | 23 | M | 7 | NULL |
| 20.7500 | 6 | 21 | Huang Yueying | 22 | F | 6 | NULL |
+---------+---------+-------+---------------+-----+--------+---------+-----------+
9 rows in set (0.00 sec)
MariaDB [hellodb]>
有人說mysql中對子查詢的優化不好，所以子查詢也要少用。
3、聯合查詢：
把兩個或多個查詢語句的結果合並起來。UNION
這個簡單，就是一個結果附加在了另一個結果的下面。疊加起來了。
SELECT Name,Age FROM teachers UNION SELECT Name,Age FROM students;
把後面的語句結果連接在前面結果的下面。
UNION 可以有多個，可以連接多個查詢結果。
各個查詢結果的字段數要相同。
