mysql——面试题初体验

查询环境

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)