前言：
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)