前言：
Mysql中查詢語句是日常使用最頻繁和複雜的語句，Mysql查詢有單表查詢和多表連線查詢，以下透過案例來熟悉Mysql的查詢語句。
一、單表查詢
現有hellodb資料庫和students等表
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dbtest |
| hellodb |
| mydb |
| mysql |
| performance_schema |
| sys |
| test |
| testdb |
+--------------------+
9 rows in set (0.00 sec)
mysql> USE hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql> SHOW TABLES;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| test |
| toc |
+-------------------+
8 rows in set (0.00 sec)
查詢Gender欄位並去重
mysql> SELECT DISTINCT Gender FROM students;
+--------+
| Gender |
+--------+
| M |
| F |
+--------+
2 rows in set (0.00 sec)
檢視快取命中次數
mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031832 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 34450935 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+----------+
8 rows in set (0.00 sec)
檢視查詢的次數
mysql> SHOW GLOBAL STATUS LIKE 'Com_se%';
+----------------+----------+
| Variable_name | Value |
+----------------+----------+
| Com_select | 34450938 |
| Com_set_option | 53743714 |
+----------------+----------+
2 rows in set (0.01 sec)
命中率=(命中總次數/查詢總次數)*100
查詢欄位使用別名
mysql> SELECT Name FROM students;
+---------------+
| Name |
+---------------+
| Diao Chan |
| Ding Dian |
| Duan Yu |
| Hua Rong |
| Huang Yueying |
| Jinjiao King |
| Lin Chong |
| Lin Daiyu |
| Lu Wushuang |
| Ma Chao |
| Ren Yingying |
| Shi Potian |
| Shi Qing |
| Shi Zhongyu |
| Sun Dasheng |
| Tian Boguang |
| Wen Qingqing |
| Xi Ren |
| Xiao Qiao |
| Xie Yanke |
| Xu Xian |
| Xu Zhu |
| Xue Baochai |
| Yinjiao King |
| Yu Yutong |
| Yuan Chengzhi |
| Yue Lingshan |
+---------------+
27 rows in set (0.00 sec)
mysql> SELECT Name AS StuName FROM students;
+---------------+
| StuName |
+---------------+
| Diao Chan |
| Ding Dian |
| Duan Yu |
| Hua Rong |
| Huang Yueying |
| Jinjiao King |
| Lin Chong |
| Lin Daiyu |
| Lu Wushuang |
| Ma Chao |
| Ren Yingying |
| Shi Potian |
| Shi Qing |
| Shi Zhongyu |
| Sun Dasheng |
| Tian Boguang |
| Wen Qingqing |
| Xi Ren |
| Xiao Qiao |
| Xie Yanke |
| Xu Xian |
| Xu Zhu |
| Xue Baochai |
| Yinjiao King |
| Yu Yutong |
| Yuan Chengzhi |
| Yue Lingshan |
+---------------+
27 rows in set (0.00 sec)
算數和比較運算子
mysql> SELECT Name,Age FROM students WHERE Age > 50;
+--------------+-----+
| Name | Age |
+--------------+-----+
| Xie Yanke | 53 |
| Yinjiao King | 98 |
| Sun Dasheng | 100 |
| Jinjiao King | 100 |
+--------------+-----+
4 rows in set (0.00 sec)
mysql> SELECT Name,Age FROM students WHERE Age+30 > 50;
+---------------+-----+
| Name | Age |
+---------------+-----+
| Shi Zhongyu | 22 |
| Shi Potian | 22 |
| Xie Yanke | 53 |
| Ding Dian | 32 |
| Yu Yutong | 26 |
| Shi Qing | 46 |
| Yuan Chengzhi | 23 |
| Tian Boguang | 33 |
| Xu Zhu | 21 |
| Lin Chong | 25 |
| Hua Rong | 23 |
| Huang Yueying | 22 |
| Ma Chao | 23 |
| Xu Xian | 27 |
| Sun Dasheng | 100 |
| Jinjiao King | 100 |
| Yinjiao King | 98 |
+---------------+-----+
17 rows in set (0.00 sec)
mysql> SELECT Name,Age FROM students WHERE Age != 50;
+---------------+-----+
| Name | Age |
+---------------+-----+
| Shi Zhongyu | 22 |
| Shi Potian | 22 |
| Xie Yanke | 53 |
| Ding Dian | 32 |
| Yu Yutong | 26 |
| Shi Qing | 46 |
| Xi Ren | 19 |
| Lin Daiyu | 17 |
| Ren Yingying | 20 |
| Yue Lingshan | 19 |
| Yuan Chengzhi | 23 |
| Wen Qingqing | 19 |
| Tian Boguang | 33 |
| Lu Wushuang | 17 |
| Duan Yu | 19 |
| Xu Zhu | 21 |
| Lin Chong | 25 |
| Hua Rong | 23 |
| Xue Baochai | 18 |
| Diao Chan | 19 |
| Huang Yueying | 22 |
| Xiao Qiao | 20 |
| Ma Chao | 23 |
| Xu Xian | 27 |
| Sun Dasheng | 100 |
| Jinjiao King | 100 |
| Yinjiao King | 98 |
+---------------+-----+
27 rows in set (0.00 sec)
mysql> SELECT Name,Age FROM students WHERE Age IN (18,100);
+--------------+-----+
| Name | Age |
+--------------+-----+
| Xue Baochai | 18 |
| Sun Dasheng | 100 |
| Jinjiao King | 100 |
+--------------+-----+
3 rows in set (0.00 sec)
mysql> SELECT Name,ClassID FROM students WHERE ClassID = NULL;
Empty set (0.00 sec)
mysql> SELECT Name,ClassID FROM students WHERE ClassID IS NULL;
+--------------+---------+
| Name | ClassID |
+--------------+---------+
| Xu Xian | NULL |
| Sun Dasheng | NULL |
| Jinjiao King | NULL |
| Yinjiao King | NULL |
+--------------+---------+
4 rows in set (0.00 sec)
根據性別進行分組
mysql> SELECT * FROM students GROUP BY Age;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 27 | Yinjiao King | 98 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
根據性別進行分組並且年齡做平均運算，根據性別顯示
GROUP BY：根據指定的欄位把查詢的結果進行“分組”以用於“聚合”運算；
avg(), max(), min(), sum(), count()
HAVING：對分組聚合後的結果進行條件過濾；
mysql> SELECT avg(Age),Gender FROM students GROUP BY Gender;
+----------+--------+
| avg(Age) | Gender |
+----------+--------+
| 19.0000 | F |
| 40.7647 | M |
+----------+--------+
2 rows in set (0.00 sec)
統計每個班級的人數（對班級進行分組）
mysql> SELECT count(StuID) AS NOS,ClassID FROM students GROUP BY ClassID;
+-----+---------+
| NOS | ClassID |
+-----+---------+
| 4 | NULL |
| 4 | 1 |
| 3 | 2 |
| 4 | 3 |
| 4 | 4 |
| 1 | 5 |
| 4 | 6 |
| 3 | 7 |
+-----+---------+
8 rows in set (0.00 sec)
統計每個班級的人數並且大於2的
mysql> SELECT count(StuID) AS NOS,ClassID FROM students GROUP BY ClassID HAVING NOS>2;
+-----+---------+
| NOS | ClassID |
+-----+---------+
| 4 | NULL |
| 4 | 1 |
| 3 | 2 |
| 4 | 3 |
| 4 | 4 |
| 4 | 6 |
| 3 | 7 |
+-----+---------+
7 rows in set (0.01 sec)
根據年齡排序，降序
ORDER BY：根據指定的欄位把查詢的結果進行排序；預設升序；
升序：ASC
降序：DESC
mysql> SELECT Name,Age FROM students ORDER BY Age DESC;
+---------------+-----+
| Name | Age |
+---------------+-----+
| Jinjiao King | 100 |
| Sun Dasheng | 100 |
| Yinjiao King | 98 |
| Xie Yanke | 53 |
| Shi Qing | 46 |
| Tian Boguang | 33 |
| Ding Dian | 32 |
| Xu Xian | 27 |
| Yu Yutong | 26 |
| Lin Chong | 25 |
| Ma Chao | 23 |
| Yuan Chengzhi | 23 |
| Hua Rong | 23 |
| Huang Yueying | 22 |
| Shi Potian | 22 |
| Shi Zhongyu | 22 |
| Xu Zhu | 21 |
| Xiao Qiao | 20 |
| Ren Yingying | 20 |
| Duan Yu | 19 |
| Wen Qingqing | 19 |
| Diao Chan | 19 |
| Yue Lingshan | 19 |
| Xi Ren | 19 |
| Xue Baochai | 18 |
| Lu Wushuang | 17 |
| Lin Daiyu | 17 |
+---------------+-----+
27 rows in set (0.00 sec)
查詢之後顯示10行
LIMIT：對輸出結果進行數量限制
[LIMIT {[offset,] row_count | row_count OFFSET offset}]
LIMIT 10:顯示10行
LIMIT 10,10 :顯示11-20行，偏移10行，顯示後10行
mysql> SELECT Name,Age FROM students ORDER BY Age DESC LIMIT 10;
+--------------+-----+
| Name | Age |
+--------------+-----+
| Sun Dasheng | 100 |
| Jinjiao King | 100 |
| Yinjiao King | 98 |
| Xie Yanke | 53 |
| Shi Qing | 46 |
| Tian Boguang | 33 |
| Ding Dian | 32 |
| Xu Xian | 27 |
| Yu Yutong | 26 |
| Lin Chong | 25 |
+--------------+-----+
10 rows in set (0.00 sec)
mysql> SELECT Name,Age FROM students ORDER BY Age DESC LIMIT 10,10;
+---------------+-----+
| Name | Age |
+---------------+-----+
| Ma Chao | 23 |
| Yuan Chengzhi | 23 |
| Hua Rong | 23 |
| Huang Yueying | 22 |
| Shi Potian | 22 |
| Shi Zhongyu | 22 |
| Xu Zhu | 21 |
| Xiao Qiao | 20 |
| Ren Yingying | 20 |
| Duan Yu | 19 |
+---------------+-----+
10 rows in set (0.00 sec)
二、多表查詢
交叉連線,笛卡爾乘積
連線查詢的結果集中包含一個表中的每一條記錄與另一個表中的每一條記錄相互匹配的組合，像這樣的結果集就可以稱之為笛卡爾積。
不推薦此方法查詢。
mysql> mysql> SELECT 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 |
| 26 | Jinjiao King | 100 | M | NULL | NULL |
| 27 | Yinjiao King | 98 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)
mysql> SELECT * FROM teachers;;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM students,teachers;;
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 1 | Song Jiang | 45 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 2 | Zhang Sanfeng | 94 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 4 | Lin Chaoying | 93 | F |
| 2 | Shi Potian | 22 | M | 1 | 7 | 1 | Song Jiang | 45 | M |
| 2 | Shi Potian | 22 | M | 1 | 7 | 2 | Zhang Sanfeng | 94 | M |
| 2 | Shi Potian | 22 | M | 1 | 7 | 3 | Miejue Shitai | 77 | F |
| 2 | Shi Potian | 22 | M | 1 | 7 | 4 | Lin Chaoying | 93 | F |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 1 | Song Jiang | 45 | M |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 2 | Zhang Sanfeng | 94 | M |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 3 | Miejue Shitai | 77 | F |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 4 | Lin Chaoying | 93 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 1 | Song Jiang | 45 | M |
| 4 | Ding Dian | 32 | M | 4 | 4 | 2 | Zhang Sanfeng | 94 | M |
| 4 | Ding Dian | 32 | M | 4 | 4 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
...
...
| 27 | Yinjiao King | 98 | M | NULL | NULL | 3 | Miejue Shitai | 77 | F |
| 27 | Yinjiao King | 98 | M | NULL | NULL | 4 | Lin Chaoying | 93 | F |
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
108 rows in set (0.00 sec)
內連線
等值連線:
讓表之間的欄位以等值的方式建立連線，
兩個表中具有相同意義的欄位或具有關聯的欄位建立連線。
- 等值連線
mysql> SELECT * FROM students,teachers WHERE students.TeacherID=teachers.TID;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.00 sec)
查詢每個同學所屬的門派
mysql> 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 |
| 26 | Jinjiao King | 100 | M | NULL | NULL |
| 27 | Yinjiao King | 98 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
27 rows in set (0.00 sec)
mysql> SELECT * FROM classes;
+---------+----------------+----------+
| ClassID | Class | NumOfStu |
+---------+----------------+----------+
| 1 | Shaolin Pai | 10 |
| 2 | Emei Pai | 7 |
| 3 | QingCheng Pai | 11 |
| 4 | Wudang Pai | 12 |
| 5 | Riyue Shenjiao | 31 |
| 6 | Lianshan Pai | 27 |
| 7 | Ming Jiao | 27 |
| 8 | Xiaoyao Pai | 15 |
+---------+----------------+----------+
8 rows in set (0.00 sec)
mysql> 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 |
| Yu Yutong | QingCheng Pai |
| Shi Qing | Riyue Shenjiao |
| Xi Ren | QingCheng Pai |
| Lin Daiyu | Ming Jiao |
| Ren Yingying | Lianshan Pai |
| Yue Lingshan | QingCheng Pai |
| Yuan Chengzhi | Lianshan Pai |
| Wen Qingqing | Shaolin Pai |
| Tian Boguang | Emei Pai |
| Lu Wushuang | QingCheng Pai |
| Duan Yu | Wudang Pai |
| Xu Zhu | Shaolin Pai |
| Lin Chong | Wudang Pai |
| Hua Rong | Ming Jiao |
| Xue Baochai | Lianshan Pai |
| Diao Chan | Ming Jiao |
| Huang Yueying | Lianshan Pai |
| Xiao Qiao | Shaolin Pai |
| Ma Chao | Wudang Pai |
+---------------+----------------+
23 rows in set (0.00 sec)
- 自連線
mysql> SELECT s.Name,t.Name FROM students AS s,students AS t WHERE s.TeacherID=t.StuID;
+-------------+-------------+
| Name | Name |
+-------------+-------------+
| Shi Zhongyu | Xie Yanke |
| Shi Potian | Xi Ren |
| Xie Yanke | Xu Zhu |
| Ding Dian | Ding Dian |
| Yu Yutong | Shi Zhongyu |
+-------------+-------------+
5 rows in set (0.00 sec)
外連線
左外連線：以左邊表為基準，建立連線，左右都有值的顯示值，左邊有的右邊沒有，顯示為空。
右外連線：以右邊表為基準，建立連線，左右都有值的顯示值，右邊有的左邊沒有，顯示為空。
- 左外連線
mysql> mysql> SELECT s.Name,c.Class FROM students AS s LEFT JOIN classes AS c ON s.ClassID=c.ClassID;
+---------------+----------------+
| Name | Class |
+---------------+----------------+
| Shi Zhongyu | Emei Pai |
| Shi Potian | Shaolin Pai |
| Xie Yanke | Emei Pai |
| Ding Dian | Wudang Pai |
| Yu Yutong | QingCheng Pai |
| Shi Qing | Riyue Shenjiao |
| Xi Ren | QingCheng Pai |
| Lin Daiyu | Ming Jiao |
| Ren Yingying | Lianshan Pai |
| Yue Lingshan | QingCheng Pai |
| Yuan Chengzhi | Lianshan Pai |
| Wen Qingqing | Shaolin Pai |
| Tian Boguang | Emei Pai |
| Lu Wushuang | QingCheng Pai |
| Duan Yu | Wudang Pai |
| Xu Zhu | Shaolin Pai |
| Lin Chong | Wudang Pai |
| Hua Rong | Ming Jiao |
| Xue Baochai | Lianshan Pai |
| Diao Chan | Ming Jiao |
| Huang Yueying | Lianshan Pai |
| Xiao Qiao | Shaolin Pai |
| Ma Chao | Wudang Pai |
| Xu Xian | NULL |
| Sun Dasheng | NULL |
| Jinjiao King | NULL |
| Yinjiao King | NULL |
+---------------+----------------+
27 rows in set (0.00 sec)
- 右外連線
mysql> SELECT s.Name,c.Class FROM students AS s RIGHT JOIN classes AS c ON s.ClassID=c.ClassID;
+---------------+----------------+
| Name | Class |
+---------------+----------------+
| Shi Zhongyu | Emei Pai |
| Shi Potian | Shaolin Pai |
| Xie Yanke | Emei Pai |
| Ding Dian | Wudang Pai |
| Yu Yutong | QingCheng Pai |
| Shi Qing | Riyue Shenjiao |
| Xi Ren | QingCheng Pai |
| Lin Daiyu | Ming Jiao |
| Ren Yingying | Lianshan Pai |
| Yue Lingshan | QingCheng Pai |
| Yuan Chengzhi | Lianshan Pai |
| Wen Qingqing | Shaolin Pai |
| Tian Boguang | Emei Pai |
| Lu Wushuang | QingCheng Pai |
| Duan Yu | Wudang Pai |
| Xu Zhu | Shaolin Pai |
| Lin Chong | Wudang Pai |
| Hua Rong | Ming Jiao |
| Xue Baochai | Lianshan Pai |
| Diao Chan | Ming Jiao |
| Huang Yueying | Lianshan Pai |
| Xiao Qiao | Shaolin Pai |
| Ma Chao | Wudang Pai |
| NULL | Xiaoyao Pai |
+---------------+----------------+
24 rows in set (0.00 sec)
子查詢
- 查詢年齡大於平均年齡的同學
mysql> SELECT Name,Age FROM students WHERE Age> (SELECT avg(Age) FROM students);
+--------------+-----+
| Name | Age |
+--------------+-----+
| Tian Boguang | 33 |
| Shi Qing | 46 |
| Xie Yanke | 53 |
| Yinjiao King | 98 |
| Sun Dasheng | 100 |
| Jinjiao King | 100 |
+--------------+-----+
6 rows in set (0.00 sec)
- 用於IN中的子查詢
mysql> SELECT Name,Age FROM students WHERE Age IN (SELECT Age FROM teachers);
Empty set (0.01 sec)
聯合查詢：將多個查詢語句的執行結果相合並
mysql> SELECT Name,Age FROM students UNION SELECT Name,Age FROM teachers;
+---------------+-----+
| Name | Age |
+---------------+-----+
| Shi Zhongyu | 22 |
| Shi Potian | 22 |
| Xie Yanke | 53 |
| Ding Dian | 32 |
| Yu Yutong | 26 |
| Shi Qing | 46 |
| Xi Ren | 19 |
| Lin Daiyu | 17 |
| Ren Yingying | 20 |
| Yue Lingshan | 19 |
| Yuan Chengzhi | 23 |
| Wen Qingqing | 19 |
| Tian Boguang | 33 |
| Lu Wushuang | 17 |
| Duan Yu | 19 |
| Xu Zhu | 21 |
| Lin Chong | 25 |
| Hua Rong | 23 |
| Xue Baochai | 18 |
| Diao Chan | 19 |
| Huang Yueying | 22 |
| Xiao Qiao | 20 |
| Ma Chao | 23 |
| Xu Xian | 27 |
| Sun Dasheng | 100 |
| Jinjiao King | 100 |
| Yinjiao King | 98 |
| Song Jiang | 45 |
| Zhang Sanfeng | 94 |
| Miejue Shitai | 77 |
| Lin Chaoying | 93 |
+---------------+-----+
31 rows in set (0.00 sec)