MySQL(作业5)

一、数据表

--1.学生表 student(id,name,birth,gender)

id: 学生编号；name：学生姓名；birth：出生日期；gender：性别；

--2.课程表 course(course_id,course_name,teacher_id)

course_id：课程编号；course_name：课程姓名；teacher_id：教师编号；

--3. 教 师 表 teacher(teacher_id,teacher_name) teacher_id：教师编号；teacher_name：教师姓名

--4. 成 绩 表 score(student_id,course_id,score) student_id：学生编号，course_id：课程编号；score：成绩。

二、结构

三、数据 

 

 

四、能力提升 

1、-- 查询1课程比2课程成绩高的学生的信息及课程分数

第一步
select s1.student_id, s1.score from score s1,score s2 where s1.student_id = s2.student_id and s1.course_id = 1 and s2.course_id = 2 and s1.score>s2.score ;
+------------+-------+
| student_id | score |
+------------+-------+
|          2 |    70 |
|          4 |    50 |
+------------+-------+

2、-- 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩 

1、select name , avg(score) as 平均成绩 from student s1,score s2 where s1.id = s2.student_id group by name having avg(score)>60;

+------+-------------------+
| name | 平均成绩          |
+------+-------------------+
| 赵雷 | 89.66666666666667 |
| 钱电 |                70 |
| 孙云 |                80 |
| 周梅 |              81.5 |
| 郑竹 |              93.5 |
+------+-------------------+

2、select student_id, avg(score) as a1 from score group by student_id having a1>60 ;
+------------+-------------------+
| student_id | a1                |
+------------+-------------------+
|          1 | 89.66666666666667 |
|          2 |                70 |
|          3 |                80 |
|          5 |              81.5 |
|          7 |              93.5 |
+------------+-------------------+

 3、-- 查询在 score 表存在成绩的学生信息

select student.* from student  right join score on id = student_id group by name,id,gender,birth;
+------+------+------------+--------+
| id   | name | birth      | gender |
+------+------+------------+--------+
|    1 | 赵雷 | 1990-01-01 | 男     |
|    2 | 钱电 | 1990-12-21 | 男     |
|    3 | 孙云 | 1990-05-20 | 男     |
|    4 | 李云 | 1990-08-06 | 男     |
|    5 | 周梅 | 1991-12-01 | 女     |
|    6 | 吴兰 | 1992-03-01 | 女     |
|    7 | 郑竹 | 1989-07-01 | 女     |
+------+------+------------+--------+

4、-- 查询所有同学的编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为null ) 

select name,student_id,count(course_id),sum(score) 
from student left join score on id = student_id 
group by name,student_id ;

+------+------------+------------------+------------+
| name | student_id | count(course_id) | sum(score) |
+------+------------+------------------+------------+
| 赵雷 |          1 |                3 |        269 |
| 钱电 |          2 |                3 |        210 |
| 孙云 |          3 |                3 |        240 |
| 李云 |          4 |                3 |        100 |
| 周梅 |          5 |                2 |        163 |
| 吴兰 |          6 |                2 |         65 |
| 郑竹 |          7 |                2 |        187 |
| 张三 |       NULL |                0 |       NULL |
| 李四 |       NULL |                0 |       NULL |
| 赵六 |       NULL |                0 |       NULL |
| 孙七 |       NULL |                0 |       NULL |
+------+------------+------------------+------------+

5、-- 查询学过「老张」老师授课的同学的信息

6、-- 查询没有学全所有课程的同学的信息

7、-- 查询至少有一门课与学号为1的同学所学相同的同学的信息

8、-- 查询和2号的同学学习的课程总数完全相同的其他同学的信息

9、-- 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

10、-- 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

11、-- 查询男生、女生人数

12、-- 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

13、-- 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）

select name,student_id,course_id,score 
from student left join score on id = student_id;

+------+------------+-----------+-------+
| name | student_id | course_id | score |
+------+------------+-----------+-------+
| 赵雷 |          1 |         3 |    99 |
| 赵雷 |          1 |         2 |    90 |
| 赵雷 |          1 |         1 |    80 |
| 钱电 |          2 |         3 |    80 |
| 钱电 |          2 |         2 |    60 |
| 钱电 |          2 |         1 |    70 |
| 孙云 |          3 |         3 |    80 |
| 孙云 |          3 |         2 |    80 |
| 孙云 |          3 |         1 |    80 |
| 李云 |          4 |         3 |    20 |
| 李云 |          4 |         2 |    30 |
| 李云 |          4 |         1 |    50 |
| 周梅 |          5 |         2 |    87 |
| 周梅 |          5 |         1 |    76 |
| 吴兰 |          6 |         3 |    34 |
| 吴兰 |          6 |         1 |    31 |
| 郑竹 |          7 |         3 |    98 |
| 郑竹 |          7 |         2 |    89 |
| 张三 |       NULL |      NULL |  NULL |
| 李四 |       NULL |      NULL |  NULL |
| 李四 |       NULL |      NULL |  NULL |
| 赵六 |       NULL |      NULL |  NULL |
| 孙七 |       NULL |      NULL |  NULL |
+------+------------+-----------+-------+

14、-- 查询不及格的课程人数

15、-- 求每门课程的学生人数

16、-- 统计每门课程的学生选修人数（超过 5 人的课程才统计)