mysql练习题（一）：查询相关

题一、找出平均成绩大于60的所有学生的学号（即student_id)和姓名
题二、查询所有学生的学号，姓名，选课数和总成绩
题三、查找名字中含有”大“的老师的个数
题四、查询没有学过天老师课的同学的学号、姓名
题五、查询学过“4”且学过编号“5”课程的同学的学号
题六、查询编号“1”成绩比编号“2”成绩低的学生的学号
题七、找出有一门课程低于60分的学生的学号和名字
题八、查询选完全部课程的学生的学号
题九、按平均成绩从高到低，显示所有学生的各科课程成绩
题十、查询各科成绩的最高分和最低分及对应的学生姓名学号

题一答案

mysql> select a.student_id,b.name,AVG(a.number) from score a join student b on a.student_id=b.student_id group by a.student_id having AVG(number) > 70;
+------------+--------+---------------+
| student_id | name   | AVG(a.number) |
+------------+--------+---------------+
|          1 | 巨炮   |       83.4000 |
|          2 | 钢弹   |       80.8000 |
|          3 | 七次郎 |       78.8000 |
|          4 | 米线   |       78.4000 |
+------------+--------+---------------+
4 rows in set (0.01 sec)

题二答案

mysql> select a.student_id,b.name,count(course_id),count(number) from score a join student b on a.student_id=b.student_id group by a.student_id;
+------------+--------+------------------+---------------+
| student_id | name   | count(course_id) | count(number) |
+------------+--------+------------------+---------------+
|          1 | 巨炮   |                5 |             5 |
|          2 | 钢弹   |                5 |             5 |
|          3 | 七次郎 |                5 |             5 |
|          4 | 米线   |                5 |             5 |
|          5 | 辣条   |                4 |             4 |
+------------+--------+------------------+---------------+
5 rows in set (0.00 sec)

题三答案

mysql> select count(*) from teacher where name like '%大%';
+----------+
| count(*) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

题四答案

mysql> select student_id,name from student where student_id not in(select student_id from score where course_id=(select a.course_id from course a left join teacher b on a.teacher_id=b.teacher_id where b.name like '%天%'));
+------------+------+
| student_id | name |
+------------+------+
|          5 | 辣条 |
+------------+------+
1 row in set (0.00 sec)
``

**题五答案**

【class.sql文件】[下载](https://files.cnblogs.com/files/FullStackMe/class.rar)