+——————-+
| Tables_in_dapeng3 |
+——————-+
| class |
| course |
| s1 |
| score |
| student |
| teacher |
+——————-+
6 rows in set (0.00 sec)
mysql> select * from class;
+—-+————–+
| id | caption |
+—-+————–+
| 1 | 三年二班 |
| 2 | 三年三班 |
| 3 | 一年二班 |
+—-+————–+
3 rows in set (0.11 sec)
mysql> select * from course;
+—-+——–+————+
| id | cname | teacher_id |
+—-+——–+————+
| 1 | 生物 | 1 |
| 2 | 物理 | 2 |
| 3 | 体育 | 3 |
| 4 | 美术 | 2 |
+—-+——–+————+
4 rows in set (0.00 sec)
mysql> select * from student;
+—-+——–+——–+———-+
| id | sname | gender | class_id |
+—-+——–+——–+———-+
| 1 | 铁蛋 | male | 2 |
| 2 | 钢蛋 | female | 1 |
| 3 | 铜蛋 | male | 3 |
| 4 | 银弹 | male | 1 |
| 5 | ety | male | 1 |
| 6 | yuu | female | 2 |
+—-+——–+——–+———-+
6 rows in set (0.11 sec)
mysql> select * from teacher;
+—-+—————–+
| id | tname |
+—-+—————–+
| 1 | 张磊老师 |
| 2 | 李平老师 |
| 3 | 刘海燕老师 |
| 4 | 朱云海老师 |
| 5 | 李杰老师 |
+—-+—————–+
5 rows in set (0.11 sec)
mysql> select * from score;
+—-+————+———–+——–+
| id | student_id | course_id | number |
+—-+————+———–+——–+
| 1 | 1 | 1 | 100 |
| 2 | 2 | 1 | 58 |
| 3 | 2 | 2 | 56 |
| 4 | 3 | 3 | 90 |
+—-+————+———–+——–+
1、查询所有的课程的名称以及对应的任课老师姓名
mysql> select course.cname,teacher.tname from course inner join teacher on course.teacher_id = teacher.id;
+——–+—————–+
| cname | tname |
+——–+—————–+
| 生物 | 张磊老师 |
| 物理 | 李平老师 |
| 美术 | 李平老师 |
| 体育 | 刘海燕老师 |
+——–+—————–+
2、查询学生表中男女生各有多少人
mysql> select gender,count(gender) as male_count from student where gender = ‘male’ group by gender ;
+——–+————+
| gender | male_count |
+——–+————+
| male | 4 |
+——–+————+
1 row in set (0.00 sec)
mysql> select gender,count(gender) as male_count from student where gender = ‘female’ group by gender ;
+——–+————+
| gender | male_count |
+——–+————+
| female | 2 |
+——–+————+
3、查询物理成绩等于100的学生的姓名
mysql> select sname from (select score.student_id,score.number,student.id,student.sname
from score inner join student where student.id = score.student_id) as t1 where number = 100;
+——–+
| sname |
+——–+
| 铁蛋 |
+——–+
4、查询成绩大于八十分的同学的姓名和平均成绩
mysql> select student.id,student.sname,score.student_id,score.number from student inner join
score on student.id = score.student_id where score.number > 80 ;
+—-+——–+————+——–+
| id | sname | student_id | number |
+—-+——–+————+——–+
| 3 | 铜蛋 | 3 | 90 |
| 1 | 铁蛋 | 1 | 100 |
| 4 | 银弹 | 4 | 98 |
+—-+——–+————+——–+
mysql> select avg(number) from (select student.id,student.sname,score.student_id,score.number
from student inner join score on student.id = score.student_id where score.number > 80) as t;
+————-+
| avg(number) |
+————-+
| 96.0000 |
+————-+
5、查询所有学生的学号，姓名,总成绩
mysql> select student.id,student.sname,t1.total_num from student left join(select score.student_id,
sum(score.number) as total_num from score group by score.student_id) as t1 on student.id = t1.student_id;
+—-+——–+———–+
| id | sname | total_num |
+—-+——–+———–+
| 1 | 铁蛋 | 100 |
| 2 | 钢蛋 | 80 |
| 3 | 铜蛋 | 90 |
| 4 | 银弹 | 98 |
| 5 | ety | NULL |
| 6 | yuu | NULL |
+—-+——–+———–+
6、 查询姓李老师的个数
mysql> select tname from teacher where tname like ‘李%’;
+————–+
| tname |
+————–+
| 李平老师 |
| 李杰老师 |
+————–+
7、 查询没有报李平老师课的学生姓名(未完成)
结果：学生姓名
查看李平老师教授的课程，然后和学生表建立关联
select student.sname,student.id from student left join
(select teacher.id,course.teacher_id,course.cname,teacher.tname from teacher
left join course on teacher.id = course.teacher_id) as t1 ;
8、 查询物理课程比生物课程高的学生的学号
结果：学生的学号-student.id
score绑定课程
mysql> select t1.student_id from (select student_id,number from score
where course_id = (select id from course where cname = ‘物理’)) as t1 inner join
(select student_id,number from score where course_id = (select id from course where cname = ‘生物’))
as t2 on t1.student_id = t2.student_id where t1.number > t2.number;
+————+
| student_id |
+————+
| 2 |
+————+
9、 查询没有同时选修物理课程和体育课程的学生姓名
结果：学生姓名
select student.sname from student where id in(select student_id from score where
course_id in(select id from course where cname = ‘物理’ or cname = ‘体育’)
group by student_id having count(course_id) = 1);
10、查询挂科超过两门(包括两门)的学生姓名和班级
结果：学生姓名和班级
mysql> select student.sname,class.caption from student inner join(select student_id from score where number <60
group by student_id having count(course_id) >= 2) as t1 inner join class on student.id = t1.student_id and
student.class_id = class.id;
流程注释：select 字段 from table. table里有学生姓名和班级，假设先把学生表和班级表连接起来，他们有共同字段class_id，内连接后可得到一张
学生对应班级的表。然后查询score表里的学生的每门成绩，如果有两门不到60的，获得该学生student_id，然后把上个表当作参数传入。
+——–+————–+
| sname | caption |
+——–+————–+
| 钢蛋 | 三年二班 |
+——–+————–+
11 、查询选修了所有课程的学生姓名
mysql> select student.sname from student where id in (select student_id from score group by student_id having
count(course_id) = (select count(id) from course));
Empty set (0.14 sec)
12、查询李平老师教的课程的所有成绩记录
mysql> select * from score where course_id in (select id from course inner join
teacher on course.teacher_id = teacher.id where teacher.tname = ‘李平老师’);
13、查询全部学生都选修了的课程号和课程名
mysql> select id,cname from course where id in (select course_id from score group by course_id
having count(student_id) = (select count(id) from student));
Empty set (0.01 sec)
14、查询每门课程被选修的次数
mysql> select course_id,count(student_id) from score group by course_id;
+———–+——————-+
| course_id | count(student_id) |
+———–+——————-+
| 1 | 2 |
| 2 | 1 |
| 3 | 1 |
+———–+——————-+
15、查询只选修了一门课程的学生姓名和学号
mysql> select id,sname from student where id in (select student_id from score group by student_id having count(course_id) = 1);
+—-+——–+
| id | sname |
+—-+——–+
| 1 | 铁蛋 |
| 3 | 铜蛋 |
+—-+——–+
16、查询所有学生考出的成绩并按从高到低排序(成绩去重)
mysql> select distinct number from score order by number desc;
+——–+
| number |
+——–+
| 100 |
| 90 |
| 58 |
| 56 |
+——–+
17、查询平均成绩大于85的学生姓名和平均成绩
mysql> select sname,t1.avg_num from student inner join (select student_id,avg(number) as avg_num from score group by student_id having avg(number) >85) as t1 on student.id = t1.student_id;
+——–+———-+
| sname | avg_num |
+——–+———-+
| 铁蛋 | 100.0000 |
| 铜蛋 | 90.0000 |
+——–+———-+
2 rows in set (0.11 sec)
18、查询生物成绩不及格的学生姓名和对应生物分数
SELECT
sname ,
number
FROM
score
LEFT JOIN course ON score.course_id = course.id
LEFT JOIN student ON score.student_id = student.id
WHERE
course.cname = ‘生物’
AND score.number < 60;
+——–+——–+
| sname | number |
+——–+——–+
| 钢蛋 | 58 |
+——–+——–+
19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
SELECT
sname
FROM
student
WHERE
id = (
SELECT
student_id
FROM
score
WHERE
course_id IN (
SELECT
course.id
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.id
WHERE
teacher.tname = ‘李平老师’
)
GROUP BY
student_id
ORDER BY
AVG(number) DESC
LIMIT 1
);
+——–+
| sname |
+——–+
| 钢蛋 |
+——–+
20、查询每门课程成绩最好的前两名学生姓名