查询环境

1、student（学生表）

2、课程表(course)

3、教师表(teacher)

4、成绩表(score)

问题

(1) 查询所有学生的学号、姓名、选课数、总成绩

mysql> select s.s_id as 学号,s.s_name as 姓名 from student as s;

+--------+--------+
| 学号   | 姓名   |
+--------+--------+
|      1 | 张三   |
|      2 | 张三   |
|      3 | 张四   |
|      4 | 张五   |
+--------+--------+
4 rows in set (0.00 sec)

mysql> select sc.s_id,sum(s_score) as scoresum ,count(c_id) as coursenumber from score as sc group by sc.s_id;

+------+----------+--------------+
| s_id | scoresum | coursenumber |
+------+----------+--------------+
|    1 |      141 |            3 |
|    2 |      112 |            3 |
|    3 |      217 |            3 |
|    4 |      151 |            3 |
+------+----------+--------------+
4 rows in set (0.00 sec)

把上面的两个表再关联查询

mysql> select s.s_id as 学号,s.s_name as 姓名,d.scoresum as 总成绩,d.coursenumber as 选课数 from student as s,
    -> (select sc.s_id,sum(s_score) as scoresum ,count(c_id) as coursenumber from score as sc group by sc.s_id) as d
    -> where s.s_id=d.s_id;

+--------+--------+-----------+-----------+
| 学号   | 姓名   |   总成绩 | 选课数    |
+--------+--------+-----------+-----------+
|      1 | 张三   |       141 |         3 |
|      2 | 张三   |       112 |         3 |
|      3 | 张四   |       217 |         3 |
|      4 | 张五   |       151 |         3 |
+--------+--------+-----------+-----------+
4 rows in set (0.00 sec)

(2) 查询学过“数学”老师所教的所有课的同学的学号、姓名

mysql> select s_id, s_name from student where s_id in (select s_id from score where c_id in (select c_id from course where t_id = (select t_id from teacher where t_name = "数学老师")));

+------+--------+
| s_id | s_name |
+------+--------+
|    1 | 张三   |
|    3 | 张四   |
|    2 | 张三   |
|    4 | 张五   |
+------+--------+
4 rows in set (0.00 sec)

(3) 查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名

①从成绩表查询1号选的课

mysql> select score.c_id from score
    -> where score.s_id=1;

+------+
| c_id |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

②从成绩表查询所有同学的s_id

mysql> select sc.s_id from score as sc
    -> where sc.c_id in(
    -> select score.c_id from score
    -> where score.s_id=1);

+------+
| s_id |
+------+
|    1 |
|    3 |
|    2 |
|    4 |
|    2 |
|    3 |
|    1 |
|    4 |
|    4 |
|    3 |
|    2 |
|    1 |
+------+

12 rows in set (0.00 sec)

③从student表查询所有学生信息

mysql> select * from student
    -> where student.s_id in
    -> (select sc.s_id from score as sc
    -> where sc.c_id in(
    -> select score.c_id from score
    -> where score.s_id=1)
    -> );

+------+--------+---------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+---------+-------+
|    1 | 张三   |      10 | 男    |
|    2 | 张三   |      11 | 男    |
|    3 | 张四   |       1 | 男    |
|    4 | 张五   |      12 | 女    |
+------+--------+---------+-------+
4 rows in set (0.00 sec)

第③次就查询到了和“1”号的同学学习的课程完全相同的其他同学学号和姓名

(4)按平均成绩从高到低显示所有学生的“数学”(c_id=1)、“语文”(c_id=2) 、英语”(c_id=3)三门的课程成绩，按如下形式显示:学生ID，数学，语文，英语，有效课程数，有效平均分

①查询有效平均分

mysql> select s_id as 学号,avg(s_score) as 有效平均分 from score group by s_id order by 有效平均分 desc;

+--------+-----------------+
| 学号   | 有效平均分      |
+--------+-----------------+
|      3 |         72.3333 |
|      4 |         50.3333 |
|      1 |         47.0000 |
|      2 |         37.3333 |
+--------+-----------------+
4 rows in set (0.00 sec)

以下为参考案例

mysql> select *  from score
    -> left join(
    -> select s_id,avg(s_score) as avscore from score
    -> group by s_id
    -> )as d
    -> on score.s_id=d.s_id
    -> order by avscore desc;

+------+------+---------+------+---------+
| s_id | c_id | s_score | s_id | avscore |
+------+------+---------+------+---------+
|    3 |    3 |      88 |    3 | 72.3333 |
|    3 |    2 |      65 |    3 | 72.3333 |
|    3 |    1 |      64 |    3 | 72.3333 |
|    4 |    1 |      78 |    4 | 50.3333 |
|    4 |    2 |      65 |    4 | 50.3333 |
|    4 |    3 |       8 |    4 | 50.3333 |
|    1 |    1 |      24 |    1 | 47.0000 |
|    1 |    3 |      91 |    1 | 47.0000 |
|    1 |    2 |      26 |    1 | 47.0000 |
|    2 |    2 |      25 |    2 | 37.3333 |
|    2 |    3 |       9 |    2 | 37.3333 |
|    2 |    1 |      78 |    2 | 37.3333 |
+------+------+---------+------+---------+
12 rows in set (0.00 sec)
 

(5) 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数:课程ID和课程名称