SELECT语句（进阶）
SELECT语句的执行优先级：
FROM子句 --> WHERE子句 --> ORDER BY子句 -->SELECT子句 -->LIMIT子句
SELECT语句参数：
#首先定义下面这样一张表
+------+----------+-----------+--------------+-----------+
| Id_P | LastName | FirstName | Address | City |
+------+----------+-----------+--------------+-----------+
| 1 | Gates | Bill | Xuanwumen 10 | Beijing |
| 2 | Case | Jack | buxingjie 20 | Beijing |
| 2 | Blow | Tom | wangfujing 9 | guangzhou |
+------+----------+-----------+--------------+-----------+
DISTINCT：数据去重
MariaDB [mysql]> select distinct City from Persons; #去除重复的City，查询表中一共有几种不同的City
+-----------+
| City |
+-----------+
| Beijing |
| guangzhou |
+-----------+
IN：存在于指定集合。查询结果符合集合中的任意一个元素即可，是或条件表达式
MariaDB [mysql]> select * from Persons where City IN ('Beijing'); #查询City在Beijing的所有成员，如果集合中有多个元素，用逗号分隔
+------+----------+-----------+--------------+---------+
| Id_P | LastName | FirstName | Address | City |
+------+----------+-----------+--------------+---------+
| 1 | Gates | Bill | Xuanwumen 10 | Beijing |
| 2 | Case | Jack | buxingjie 20 | Beijing |
+------+----------+-----------+--------------+---------+
ORDER BY：根据指定的字段对查询结果进行排序。默认升序，降序后面加DESC即可
LIMIT：对查询的结果进行输出行数限制
MariaDB [mysql]> select * from Persons limit 1;
+------+----------+-----------+--------------+---------+
| Id_P | LastName | FirstName | Address | City |
+------+----------+-----------+--------------+---------+
| 1 | Gates | Bill | Xuanwumen 10 | Beijing |
+------+----------+-----------+--------------+---------+
GROUP BY：根据指定条件把查询结果进行“分组”以用于做“聚合”运算
#聚合运算：avg(),max(),min(),count(),sum()
MariaDB [hellodb]> select * from students group by Gender; #以性别对students表进行分组，即F（女）和M（男），group by默认只显示第一个被匹配到的行，因此按性别分组只显示了两行
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
+-------+-------------+-----+--------+---------+-----------+
2 rows in set (0.00 sec)
MariaDB [hellodb]> select avg(Age),Gender from students group by Gender; #group bu内部实际已经包含了所有被匹配到的行，因此使用avg求平均值可以得到所有女性和所有男性的年龄平均值
+----------+--------+
| avg(Age) | Gender |
+----------+--------+
| 19.0000 | F |
| 33.0000 | M |
+----------+--------+
2 rows in set (0.00 sec)
HAVING：与where用法相同，在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。并且只有HAVING可以对group by的结果进行过滤
MariaDB [hellodb]> select ClassID,count(StuID) from students group by ClassID; #查询每个班级的人数
+---------+--------------+
| ClassID | count(StuID) |
+---------+--------------+
| NULL | 2 |
| 1 | 4 |
| 2 | 3 |
| 3 | 4 |
| 4 | 4 |
| 5 | 1 |
| 6 | 4 |
| 7 | 3 |
+---------+--------------+
MariaDB [hellodb]> select ClassID,count(StuID) from students group by ClassID where count(StuID) > 2; #这里使用where对count（StuID）过滤就会报错
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where count(StuID) > 2' at line 1
MariaDB [hellodb]> select ClassID,count(StuID) from students group by ClassID where ClassID > 2; #使用where对group by的结果进行过滤也会报错
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'where ClassID > 2' at line 1
MariaDB [hellodb]> select ClassID,count(StuID) from students where ClassID > 2 group by ClassID having count(StuID) > 2; #where和having组合。或者使用select ClassID,count(StuID) from students group by ClassID having count(StuID) > 2 and ClassID > 2;也可以
+---------+--------------+
| ClassID | count(StuID) |
+---------+--------------+
| 3 | 4 |
| 4 | 4 |
| 6 | 4 |
| 7 | 3 |
+---------+--------------+
MariaDB [hellodb]> select ClassID,count(StuID) from students group by ClassID having count(StuID) > 2; #查询班级人数大于2 的班级。使用having可以对聚合函数进行条件过滤
+---------+--------------+
| ClassID | count(StuID) |
+---------+--------------+
| 1 | 4 |
| 2 | 3 |
| 3 | 4 |
| 4 | 4 |
| 6 | 4 |
| 7 | 3 |
+---------+--------------+
多表查询
#有以下两张表：
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 |
+-------+---------------+-----+--------+---------+-----------+
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 |
+-----+---------------+-----+--------+
内连接：
等值连接：让表之间的字段以‘等值’建立连接关系
MariaDB [hellodb]> select * from students,teachers where students.TeacherID = teachers.TID;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
不等值连接：
自然连接：
自连接：
外连接：
左外连接：两张表取其‘等值’的部分进行连接。但是以第一张表为固定格式（行数等），即使第一张表里某个字段没有与第二张表里相同的值也要显示第一张表的字段，相应的等值部分为NULL即可
格式：from tb1 left join tb2 on tb1.key=tb2.key
MariaDB [hellodb]> select * from students left join teachers on 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 |
| 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | NULL | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL | NULL | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL | NULL | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL | NULL | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | NULL | NULL | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
右外连接：与left join相反
格式：from tb1 right join tb2 on tb1.key=tb2.key
MariaDB [hellodb]> select * from students right join teachers on 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 |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 94 | M |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
子查询：
#基于某语句的查询结果再次进行的查询
用在where子句中的子查询：
1）用于表达式中的子查询：子查询仅可以被允许返回单个值
MariaDB [hellodb]> select Name,Age from students where Age > (select avg(AGE) from students); #查询年龄大于平均值的学生。select avg(AGE) from students表示查询students中AGE字段内容的平均值，avg是select中的内置函数，可计算平均值。
+--------------+-----+
| Name | Age |
+--------------+-----+
| Xie Yanke | 53 |
| Ding Dian | 32 |
| Shi Qing | 46 |
| Tian Boguang | 33 |
| Sun Dasheng | 100 |
+--------------+-----+
2）用于IN中的子查询：子查询应该单键查询并返回一个或多个值构成列表
MariaDB [hellodb]> select Name,Age from students where Age in (select Age from teachers); #查询和老师年龄一致的学生，这两张表中没有相同年龄的，所以为空
Empty set (0.00 sec)
用于FROM子句中的子查询：
使用格式：select tb1.key1,tb1.key2 ... from (select 条件) as tb_别名 where 条件
MariaDB [hellodb]> select Name,Age,Gender from (select Name,Age,Gender from students where Gender = 'M') as new where new.Age < 30; #查询students表中年龄小于30的女性。将select Name,Age,Gender from students where Gender = 'M'查出来的内容当成一个新表new，然后在new中找Age小于30的
+---------------+-----+--------+
| Name | Age | Gender |
+---------------+-----+--------+
| Shi Zhongyu | 22 | M |
| Shi Potian | 22 | M |
| Yu Yutong | 26 | M |
| Yuan Chengzhi | 23 | M |
| Duan Yu | 19 | M |
| Xu Zhu | 21 | M |
| Lin Chong | 25 | M |
| Hua Rong | 23 | M |
| Ma Chao | 23 | M |
| Xu Xian | 27 | M |
+---------------+-----+--------+
联合查询：UNION
#将两个select语句中的查询结果（查询结果必须是相同结构）组合到一张表上
MariaDB [hellodb]> select Name,Age from students;
+---------------+-----+
| 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 |
+---------------+-----+
25 rows in set (0.00 sec)
MariaDB [hellodb]> select Name,Age from teachers;
+---------------+-----+
| Name | Age |
+---------------+-----+
| Song Jiang | 45 |
| Zhang Sanfeng | 94 |
| Miejue Shitai | 77 |
| Lin Chaoying | 93 |
+---------------+-----+
4 rows in set (0.00 sec)
MariaDB [hellodb]> 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 |
| Song Jiang | 45 |
| Zhang Sanfeng | 94 |
| Miejue Shitai | 77 |
| Lin Chaoying | 93 |
+---------------+-----+
29 rows in set (0.00 sec)
练习题
#有下面四张表
MariaDB [hellodb]> select * from students; MariaDB [hellodb]> select * from scores;
+-------+---------------+-----+--------+---------+-----------+ +----+-------+----------+-------+
| StuID | Name | Age | Gender | ClassID | TeacherID | | ID | StuID | CourseID | Score |
+-------+---------------+-----+--------+---------+-----------+ +----+-------+----------+-------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | | 1 | 1 | 2 | 77 |
| 2 | Shi Potian | 22 | M | 1 | 7 | | 2 | 1 | 6 | 93 |
| 3 | Xie Yanke | 53 | M | 2 | 16 | | 3 | 2 | 2 | 47 |
| 4 | Ding Dian | 32 | M | 4 | 4 | | 4 | 2 | 5 | 97 |
| 5 | Yu Yutong | 26 | M | 3 | 1 | | 5 | 3 | 2 | 88 |
| 6 | Shi Qing | 46 | M | 5 | NULL | | 6 | 3 | 6 | 75 |
| 7 | Xi Ren | 19 | F | 3 | NULL | | 7 | 4 | 5 | 71 |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | | 8 | 4 | 2 | 89 |
| 9 | Ren Yingying | 20 | F | 6 | NULL | | 9 | 5 | 1 | 39 |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | | 10 | 5 | 7 | 63 |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | | 11 | 6 | 1 | 96 |
| 12 | Wen Qingqing | 19 | F | 1 | NULL | | 12 | 7 | 1 | 86 |
| 13 | Tian Boguang | 33 | M | 2 | NULL | | 13 | 7 | 7 | 83 |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | | 14 | 8 | 4 | 57 |
| 15 | Duan Yu | 19 | M | 4 | NULL | | 15 | 8 | 3 | 93 |
| 16 | Xu Zhu | 21 | M | 1 | NULL | +----+-------+----------+-------+
| 17 | Lin Chong | 25 | M | 4 | NULL | MariaDB [hellodb]> select * from courses;
| 18 | Hua Rong | 23 | M | 7 | NULL | +----------+----------------+
| 19 | Xue Baochai | 18 | F | 6 | NULL | | CourseID | Course |
| 20 | Diao Chan | 19 | F | 7 | NULL | +----------+----------------+
| 21 | Huang Yueying | 22 | F | 6 | NULL | | 1 | Hamo Gong |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | | 2 | Kuihua Baodian |
| 23 | Ma Chao | 23 | M | 4 | NULL | | 3 | Jinshe Jianfa |
| 24 | Xu Xian | 27 | M | NULL | NULL | | 4 | Taiji Quan |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | | 5 | Daiyu Zanghua |
+-------+---------------+-----+--------+---------+-----------+ | 6 | Weituo Zhang |
| 7 | Dagou Bangfa |
+----------+----------------+
MariaDB [hellodb]> 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 |
+---------+----------------+----------+
1）显示前5位同学的姓名、课程及成绩
MariaDB [hellodb]> select students.Name,courses.Course,scores.Score from (select * from students limit 5) as students,courses,scores where students.StuID = scores.StuID and scores.CourseID = courses.CourseID;
+-------------+----------------+-------+
| Name | Course | Score |
+-------------+----------------+-------+
| Shi Zhongyu | Kuihua Baodian | 77 |
| Shi Zhongyu | Weituo Zhang | 93 |
| Shi Potian | Kuihua Baodian | 47 |
| Shi Potian | Daiyu Zanghua | 97 |
| Xie Yanke | Kuihua Baodian | 88 |
| Xie Yanke | Weituo Zhang | 75 |
| Ding Dian | Daiyu Zanghua | 71 |
| Ding Dian | Kuihua Baodian | 89 |
| Yu Yutong | Hamo Gong | 39 |
| Yu Yutong | Dagou Bangfa | 63 |
+-------------+----------------+-------+
1）显示其成绩高于80的同学的姓名、课程及成绩
MariaDB [hellodb]> select students.Name,courses.Course,scores.Score from students,courses,scores where students.StuID = scores.StuID and courses.CourseID =scores.CourseID and scores.Score > 80;
+-------------+----------------+-------+
| Name | Course | Score |
+-------------+----------------+-------+
| Shi Zhongyu | Weituo Zhang | 93 |
| Shi Potian | Daiyu Zanghua | 97 |
| Xie Yanke | Kuihua Baodian | 88 |
| Ding Dian | Kuihua Baodian | 89 |
| Shi Qing | Hamo Gong | 96 |
| Xi Ren | Hamo Gong | 86 |
| Xi Ren | Dagou Bangfa | 83 |
| Lin Daiyu | Jinshe Jianfa | 93 |
+-------------+----------------+-------+
3）求前8位同学每位同学自己两门课的平均成绩，并按降序排列
MariaDB [hellodb]> select StuID,avg(Score) as avg_Score from scores group by StuID having StuID <= 8 order by avg_Score desc;
+-------+-----------+
| StuID | avg_Score |
+-------+-----------+
| 6 | 96.0000 |
| 1 | 85.0000 |
| 7 | 84.5000 |
| 3 | 81.5000 |
| 4 | 80.0000 |
| 8 | 75.0000 |
| 2 | 72.0000 |
| 5 | 51.0000 |
+-------+-----------+
4）显示每门课程课程名称及学习了这门课的同学的个数
MariaDB [hellodb]> select new.ClassID,classes.Class,new.count_Stu from (select ClassID,count(StuID) as count_Stu from students group by ClassID) as new left join classes on new.ClassID = classes.ClassID;
+---------+----------------+-----------+
| ClassID | Class | count_Stu |
+---------+----------------+-----------+
| NULL | NULL | 2 |
| 1 | Shaolin Pai | 4 |
| 2 | Emei Pai | 3 |
| 3 | QingCheng Pai | 4 |
| 4 | Wudang Pai | 4 |
| 5 | Riyue Shenjiao | 1 |
| 6 | Lianshan Pai | 4 |
| 7 | Ming Jiao | 3 |
+---------+----------------+-----------+
浙公网安备 33010602011771号