遇到一道数据库大题,考察SQL语句基础,很有代表性,决定记下来。
数据库
字段名 | 注释 | 类型 |
---|---|---|
id | 学号 | bigint(20) |
name | 姓名 | varchar(32) |
city | 城市 | varchar(32) |
电子邮箱 | varchar(32) | |
main_teacher_id | 班主任id | bigint(20) |
主键:id
外键1:main_teacher_id——teacher#id
字段名 | 注释 | 类型 |
---|---|---|
id | 教师号 | bigint(20) |
name | 教师姓名 | varchar(32) |
主键:id
字段名 | 注释 | 类型 |
---|---|---|
id | 课程号 | bigint(20) |
name | 课程名 | varchar(32) |
主键:id
字段名 | 注释 | 类型 |
---|---|---|
id | 选课记录号 | bigint(20) |
student_id | 学号 | bigint(20) |
course_id | 课程号 | bigint(20) |
score | 分数 | tinyint(3) |
主键:id
外键1:student_id —— student#id
外键2:course_id —— course#id
问题和解答(我使用的是 MySQL)
1.查询所有班主任是“马龙”且城市在杭州的学生姓名及电子邮箱。
老师的姓名在 teacher 表中,而学生信息在 student 表中,所以需要多表查询,可以直接使用如下写法:
SELECT
student.`name`,
student.email
FROM student,teacher
WHERE
student.main_teacher_id=teacher.id
AND
student.city='杭州'
AND
teacher.`name`='马龙'
也可以使用显式内连接:
SELECT
student.`name`,
student.email
FROM
student
INNER JOIN
teacher
ON
student.main_teacher_id = teacher.id
WHERE
student.city = '杭州' AND
teacher.`name` = '马龙'
此外也有子查询等方式。
2.查询选修课程数最多的三位学生的姓名,课程数相同则按学生姓名升序排序。
我们可以在 selesource 表中得到选课最多的学生 id,在 student 表中查询姓名。
但本题要求对课程数相同的学生按姓名升序排列,若使用子查询则无法在这种情况下得到正确顺序,所以使用连接查询,如下:
SELECT
student.`name`
FROM
student
INNER JOIN
selecourse
ON
student.id = selecourse.student_id
GROUP BY
student.id #按照学生id分组计数
ORDER BY
COUNT(*) DESC,student.`name` #首按数量排序,次按姓名排序
LIMIT 3 #限前3位
3.为所有分数小于60的选修课成绩加10分,如果加分后超过60则置为60。
我们可以写两条语句,先给 (50,60) 的成绩置为 60,再给 50 以下的成绩+10,注意不能反过来,否则会出现 (40,50) 的成绩 +10 后又被置为 60 的错误。这也是老生常谈的经典问题了。
UPDATE selecourse
SET score=60
WHERE score>50 AND score<60
UPDATE selecourse
SET score=score+10
WHERE score<=50
如果要在一条语句里完成,可以使用 CASE 流程控制语句:
UPDATE selecourse
SET score=(
CASE
WHEN score>50 AND score<60 THEN
60
WHEN score<=50 THEN
score+10
ELSE
score
END
)
4.查询选修的所有课程成绩均大于90的学生名称。
我们可以在 selesource 中查询得到有不大于 90 分成绩的学生 id,在 student 中查询所有不属于上述结果的学生姓名:
SELECT DISTINCT student.`name`
FROM student
WHERE id
NOT IN #查询不在以下结果中的学生
(
SELECT DISTINCT student_id #查询有课程不大于90分的学生
FROM selecourse
WHERE score<=90
)
5.哪些班主任的学生数学平均成绩高于所有学生的数学平均成绩,查询这些班主任的名称,按平均分分数高低排列。
想要得到最终的结果,我们需要对全部的4张表都进行查询,在 source 表获得“数学”的课程 id,在 student 表获得学生对应的班主任 id,在 selesource 表获得学生的数学成绩并按相应的班主任分组计算平均分,在 teacher 表中获得 id 对应的老师姓名。
我最终使用了4表内连接的写法:
SELECT
teacher.`name`,
AVG(score) AS math_avg
FROM
course
INNER JOIN
selecourse
ON
course.id = selecourse.course_id
INNER JOIN
student
ON
selecourse.student_id = student.id
INNER JOIN
teacher
ON
student.main_teacher_id = teacher.id #将四张表全部内连接
WHERE
course.`name`='数学' #获得数学的成绩
GROUP BY teacher.id #按老师id分组
HAVING #大于全体数学平均分的条件
math_avg>(
SELECT AVG(score)
FROM selecourse,course
WHERE course.`name`='数学'
)
小结
由于学识浅薄,我只能用仅有的知识解决问题,无法做到进一步的优化和分析,也希望大佬们发现文中问题时能够多多指正和赐教。