时间：2021-07-01 10:21:17 帮助过：12人阅读
连接查询：事先将两张或多张表join，根据join的结果进行查询；
【导入hellodb.sql数据库】，输入密码即可
[root@pc0003 home]# mysql -uroot -p mydb < /home/hellodb.sql
hellodb.sql数据库下载链接：http://pan.baidu.com/s/1pJKK4w7 密码：a0re
【查看students表】
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 | +-------+---------------+-----+--------+---------+-----------+
【查看 classes表】
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 | +---------+----------------+----------+
【外连接】：
左外连接：只保留出现在左外连接运算之前（左边）的关系中的元组；
left_tb LEFT JOIN right_tb ON 连接条件
右外连接：只保留出现在右外连接运算之后（右边）的关系中的元组；
left_tb RIGHT JOIN right_tb ON 连接条件
全外连接
【交叉连接查询】
cross join: 交叉连接
(a+b)(c+d+e)=
mysql> select * from students,classes; +-------+---------------+-----+--------+---------+ | StuID | Name | Age | Gender | ClassID | +-------+---------------+-----+--------+---------+ | 1 | Shi Zhongyu | 22 | M | 2 | | 1 | Shi Zhongyu | 22 | M | 2 | | 1 | Shi Zhongyu | 22 | M | 2 | | 1 | Shi Zhongyu | 22 | M | 2 | | 1 | Shi Zhongyu | 22 | M | 2 | | 1 | Shi Zhongyu | 22 | M | 2 | | 1 | Shi Zhongyu | 22 | M | 2 | | 1 | Shi Zhongyu | 22 | M | 2 | | 2 | Shi Potian | 22 | M | 1 | | 2 | Shi Potian | 22 | M | 1 | | 2 | Shi Potian | 22 | M | 1 | | 2 | Shi Potian | 22 | M | 1 | | 2 | Shi Potian | 22 | M | 1 | | 2 | Shi Potian | 22 | M | 1 | | 2 | Shi Potian | 22 | M | 1 | | 2 | Shi Potian | 22 | M | 1 | ………………………此处省略很多行……………………………………………………………………… | 24 | Xu Xian | 27 | M | NULL | | 24 | Xu Xian | 27 | M | NULL | | 24 | Xu Xian | 27 | M | NULL | | 24 | Xu Xian | 27 | M | NULL | | 24 | Xu Xian | 27 | M | NULL | | 24 | Xu Xian | 27 | M | NULL | | 24 | Xu Xian | 27 | M | NULL | | 25 | Sun Dasheng | 100 | M | NULL | | 25 | Sun Dasheng | 100 | M | NULL | | 25 | Sun Dasheng | 100 | M | NULL | | 25 | Sun Dasheng | 100 | M | NULL | | 25 | Sun Dasheng | 100 | M | NULL | | 25 | Sun Dasheng | 100 | M | NULL | | 25 | Sun Dasheng | 100 | M | NULL | | 25 | Sun Dasheng | 100 | M | NULL | +-------+---------------+-----+--------+---------+ 200 rows in set (0.00 sec)
【自然连接：等值连接】
mysql> select * from students,classes where students.ClassID = classes.ClassID; +-------+---------------+-----+--------+----------------+ | StuID | Name | Age | Gender | Class | +-------+---------------+-----+--------+----------------+ | 1 | Shi Zhongyu | 22 | M | Emei Pai | | 2 | Shi Potian | 22 | M | Shaolin Pai | | 3 | Xie Yanke | 53 | M | Emei Pai | | 4 | Ding Dian | 32 | M | Wudang Pai | | 5 | Yu Yutong | 26 | M | QingCheng Pai | | 6 | Shi Qing | 46 | M | Riyue Shenjiao | | 7 | Xi Ren | 19 | F | QingCheng Pai | | 8 | Lin Daiyu | 17 | F | Ming Jiao | | 9 | Ren Yingying | 20 | F | Lianshan Pai | | 10 | Yue Lingshan | 19 | F | QingCheng Pai | | 11 | Yuan Chengzhi | 23 | M | Lianshan Pai | | 12 | Wen Qingqing | 19 | F | Shaolin Pai | | 13 | Tian Boguang | 33 | M | Emei Pai | | 14 | Lu Wushuang | 17 | F | QingCheng Pai | | 15 | Duan Yu | 19 | M | Wudang Pai | | 16 | Xu Zhu | 21 | M | Shaolin Pai | | 17 | Lin Chong | 25 | M | Wudang Pai | | 18 | Hua Rong | 23 | M | Ming Jiao | | 19 | Xue Baochai | 18 | F | Lianshan Pai | | 20 | Diao Chan | 19 | F | Ming Jiao | | 21 | Huang Yueying | 22 | F | Lianshan Pai | | 22 | Xiao Qiao | 20 | F | Shaolin Pai | | 23 | Ma Chao | 23 | M | Wudang Pai | +-------+---------------+-----+--------+----------------+ 23 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.08 sec)
【别名】：
表别名
字段别名
mysql> select Name from students; +---------------+ | Name | +---------------+ | Shi Zhongyu | | Shi Potian | | Xie Yanke | | Ding Dian | | Yu Yutong | | Shi Qing | | Xi Ren | | Lin Daiyu | | Ren Yingying | | Yue Lingshan | | Yuan Chengzhi | | Wen Qingqing | | Tian Boguang | | Lu Wushuang | | Duan Yu | | Xu Zhu | | Lin Chong | | Hua Rong | | Xue Baochai | | Diao Chan | | Huang Yueying | | Xiao Qiao | | Ma Chao | | Xu Xian | | Sun Dasheng | +---------------+ 25 rows in set (0.00 sec)
【Name as StuNmae】
mysql> select Name as StuNmae from students; +---------------+ | StuNmae | +---------------+ | Shi Zhongyu | | Shi Potian | | Xie Yanke | | Ding Dian | | Yu Yutong | | Shi Qing | | Xi Ren | | Lin Daiyu | | Ren Yingying | | Yue Lingshan | | Yuan Chengzhi | | Wen Qingqing | | Tian Boguang | | Lu Wushuang | | Duan Yu | | Xu Zhu | | Lin Chong | | Hua Rong | | Xue Baochai | | Diao Chan | | Huang Yueying | | Xiao Qiao | | Ma Chao | | Xu Xian | | Sun Dasheng | +---------------+ 25 rows in set (0.00 sec)
【内连接，等值连接：表别名】调用时也使用别名，否则报错
mysql> select s.Name,c.Class from students as s,classes as c where 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 | +---------------+----------------+ 23 rows in set (0.00 sec)
总结：MySQL的连接查询及子查询
连接：
交叉连接
内连接
外连接
左外
右外
【自连接，StuID 连接 TeacherID 】
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 | Tom | 33 | F | 8 | 11 | | 27 | Jerry | 25 | M | 9 | 2 | +-------+---------------+-----+--------+---------+-----------+ 27 rows in set (0.00 sec)
【自连接 ：显示学生对应的老师】左边学生，右边老师
mysql> select t.Name,s.Name from students as s,students as t where s.StuID = t.TeacherID; +-------------+---------------+ | Name | Name | +-------------+---------------+ | Shi Zhongyu | Xie Yanke | | Shi Potian | Xi Ren | | Xie Yanke | Xu Zhu | | Ding Dian | Ding Dian | | Yu Yutong | Shi Zhongyu | | Tom | Yuan Chengzhi | | Jerry | Shi Potian | +-------------+---------------+ 7 rows in set (0.00 sec)
【【【【 外连接】】】】
【左连接，显示每个人学的武功，保留左表人名】 left join ... on...
以左表为准，保留左表的显示，对应的右边有也显示，没有就为null
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 | +---------------+----------------+ 25 rows in set (0.00 sec)
【右连接：显示每门功夫 谁在学 ，保留右表功夫课程】，以classes表为准，显示全部课程，没有人学的就显示空
mysql> select s.Name,c.Class from students as s right join classes as c on s.ClassID = c.ClassID; +---------------+----------------+ | Name | Class | +---------------+----------------+ | Shi Potian | Shaolin Pai | | Wen Qingqing | Shaolin Pai | | Xu Zhu | Shaolin Pai | | Xiao Qiao | Shaolin Pai | | Shi Zhongyu | Emei Pai | | Xie Yanke | Emei Pai | | Tian Boguang | Emei Pai | | Yu Yutong | QingCheng Pai | | Xi Ren | QingCheng Pai | | Yue Lingshan | QingCheng Pai | | Lu Wushuang | QingCheng Pai | | Ding Dian | Wudang Pai | | Duan Yu | Wudang Pai | | Lin Chong | Wudang Pai | | Ma Chao | Wudang Pai | | Shi Qing | Riyue Shenjiao | | Ren Yingying | Lianshan Pai | | Yuan Chengzhi | Lianshan Pai | | Xue Baochai | Lianshan Pai | | Huang Yueying | Lianshan Pai | | Lin Daiyu | Ming Jiao | | Hua Rong | Ming Jiao | | Diao Chan | Ming Jiao | | NULL | Xiaoyao Pai | +---------------+----------------+ 24 rows in set (0.00 sec)
练习：导入hellodb.sql，完成以下题目：
1、显示前5位同学的姓名、课程及成绩；【内链接练习】
2、显示其成绩高于80的同学的名称及课程；
3、求前8位同学每位同学自己两门课的平均成绩，并按降序排列；
4、显示每门课程课程名称及学习了这门课的同学的个数；
1、 显示前5位同学的姓名、课程及成绩；【内链接练习】
【显示姓名，课程】
mysql> select Name,Course from students as s,courses as c,coc where s.ClassID = coc.ClassID and coc.CourseID = c.CourseID; +---------------+----------------+ | Name | Course | +---------------+----------------+ | Shi Zhongyu | Kuihua Baodian | | Shi Zhongyu | Weituo Zhang | | Shi Potian | Kuihua Baodian | | Shi Potian | Daiyu Zanghua | | Xie Yanke | Kuihua Baodian | | Xie Yanke | Weituo Zhang | | Ding Dian | Daiyu Zanghua | | Ding Dian | Kuihua Baodian | | Yu Yutong | Hamo Gong | | Yu Yutong | Dagou Bangfa | | Shi Qing | Hamo Gong | | Xi Ren | Hamo Gong | | Xi Ren | Dagou Bangfa | | Lin Daiyu | Taiji Quan | | Lin Daiyu | Jinshe Jianfa | | Ren Yingying | Jinshe Jianfa | | Ren Yingying | Taiji Quan | | Yue Lingshan | Hamo Gong | | Yue Lingshan | Dagou Bangfa | | Yuan Chengzhi | Jinshe Jianfa | | Yuan Chengzhi | Taiji Quan | | Wen Qingqing | Kuihua Baodian | | Wen Qingqing | Daiyu Zanghua | | Tian Boguang | Kuihua Baodian | | Tian Boguang | Weituo Zhang | | Lu Wushuang | Hamo Gong | | Lu Wushuang | Dagou Bangfa | | Duan Yu | Daiyu Zanghua | | Duan Yu | Kuihua Baodian | | Xu Zhu | Kuihua Baodian | | Xu Zhu | Daiyu Zanghua | | Lin Chong | Daiyu Zanghua | | Lin Chong | Kuihua Baodian | | Hua Rong | Taiji Quan | | Hua Rong | Jinshe Jianfa | | Xue Baochai | Jinshe Jianfa | | Xue Baochai | Taiji Quan | | Diao Chan | Taiji Quan | | Diao Chan | Jinshe Jianfa | | Huang Yueying | Jinshe Jianfa | | Huang Yueying | Taiji Quan | | Xiao Qiao | Kuihua Baodian | | Xiao Qiao | Daiyu Zanghua | | Ma Chao | Daiyu Zanghua | | Ma Chao | Kuihua Baodian | +---------------+----------------+ 45 rows in set (0.00 sec)
【显示前5名姓名，课程】
mysql> select Name,Course from students as s,courses as c,coc where s.ClassID = coc.ClassID and coc.CourseID = c.CourseID and s.StuID<=5; +-------------+----------------+ | Name | Course | +-------------+----------------+ | Shi Potian | Kuihua Baodian | | Shi Potian | Daiyu Zanghua | | Shi Zhongyu | Kuihua Baodian | | Xie Yanke | Kuihua Baodian | | Shi Zhongyu | Weituo Zhang | | Xie Yanke | Weituo Zhang | | Yu Yutong | Hamo Gong | | Yu Yutong | Dagou Bangfa | | Ding Dian | Daiyu Zanghua | | Ding Dian | Kuihua Baodian | +-------------+----------------+ 10 rows in set (0.02 sec)
【显示这个表】
mysql> show tables; +-------------------+ | Tables_in_hellodb | +-------------------+ | classes | | coc | | courses | | scores | | students | | teachers | | toc | +-------------------+ 7 rows in set (0.03 sec)
【查看成绩表】
mysql> select * from scores; +----+-------+----------+-------+ | ID | StuID | CourseID | Score | +----+-------+----------+-------+ | 1 | 1 | 2 | 77 | | 2 | 1 | 6 | 93 | | 3 | 2 | 2 | 47 | | 4 | 2 | 5 | 97 | | 5 | 3 | 2 | 88 | | 6 | 3 | 6 | 75 | | 7 | 4 | 5 | 71 | | 8 | 4 | 2 | 89 | | 9 | 5 | 1 | 39 | | 10 | 5 | 7 | 63 | | 11 | 6 | 1 | 96 | | 12 | 7 | 1 | 86 | | 13 | 7 | 7 | 83 | | 14 | 8 | 4 | 57 | | 15 | 8 | 3 | 93 | +----+-------+----------+-------+ 15 rows in set (0.01 sec)
【每个人每门功课成绩，此时有重复的数据】
mysql> select Name,Course,Score from students as s,courses as c,coc,scores as ss where s.ClassID = coc.ClassID and coc.CourseID = c.CourseID and s.StuID <=5 and s.StuID=ss.StuID; +-------------+----------------+-------+ | Name | Course | Score | +-------------+----------------+-------+ | Shi Zhongyu | Kuihua Baodian | 77 | | Shi Zhongyu | Weituo Zhang | 77 | | Shi Zhongyu | Kuihua Baodian | 93 | | Shi Zhongyu | Weituo Zhang | 93 | | Shi Potian | Kuihua Baodian | 47 | | Shi Potian | Daiyu Zanghua | 47 | | Shi Potian | Kuihua Baodian | 97 | | Shi Potian | Daiyu Zanghua | 97 | | Xie Yanke | Kuihua Baodian | 88 | | Xie Yanke | Weituo Zhang | 88 | | Xie Yanke | Kuihua Baodian | 75 | | Xie Yanke | Weituo Zhang | 75 | | Ding Dian | Daiyu Zanghua | 71 | | Ding Dian | Kuihua Baodian | 71 | | Ding Dian | Daiyu Zanghua | 89 | | Ding Dian | Kuihua Baodian | 89 | | Yu Yutong | Hamo Gong | 39 | | Yu Yutong | Dagou Bangfa | 39 | | Yu Yutong | Hamo Gong | 63 | | Yu Yutong | Dagou Bangfa | 63 | +-------------+----------------+-------+ 20 rows in set (0.00 sec)
【每个人每门功课成绩，】
mysql> select Name,Course,Score from students as s,courses as c,coc,scores as ss where s.ClassID = coc.ClassID and coc.CourseID = c.CourseID and s.StuID <=5 and s.StuID=ss.StuID and coc.CourseID = ss.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 | +-------------+----------------+-------+ 10 rows in set (0.00 sec)
【第一题完成！】
2、显示其成绩高于80的同学的名称及课程；
【前8位】
mysql> select Name,Course from students as s,courses as c,coc where s.ClassID = coc.ClassID and coc.CourseID = c.CourseID and s.StuID<=8; +-------------+----------------+ | Name | Course | +-------------+----------------+ | Shi Zhongyu | Kuihua Baodian | | Shi Zhongyu | Weituo Zhang | | Shi Potian | Kuihua Baodian | | Shi Potian | Daiyu Zanghua | | Xie Yanke | Kuihua Baodian | | Xie Yanke | Weituo Zhang | | Ding Dian | Daiyu Zanghua | | Ding Dian | Kuihua Baodian | | Yu Yutong | Hamo Gong | | Yu Yutong | Dagou Bangfa | | Shi Qing | Hamo Gong | | Xi Ren | Hamo Gong | | Xi Ren | Dagou Bangfa | | Lin Daiyu | Taiji Quan | | Lin Daiyu | Jinshe Jianfa | +-------------+----------------+ 15 rows in set (0.05 sec) mysql> select Name,Course,Score from students as s,courses as c,coc,scores as ss where s.ClassID = coc.ClassID and coc.CourseID = c.CourseID and s.StuID <=8 and s.StuID=ss.StuID and coc.CourseID = ss.CourseID group by Name; +-------------+----------------+-------+ | Name | Course | Score | +-------------+----------------+-------+ | Ding Dian | Daiyu Zanghua | 71 | | Lin Daiyu | Taiji Quan | 57 | | Shi Potian | Kuihua Baodian | 47 | | Shi Qing | Hamo Gong | 96 | | Shi Zhongyu | Kuihua Baodian |