查询教材java和数据库代码数据库查询课程

转载

mob6454cc6b413f 2024-01-12 09:21:11

文章标签 查询教材java和数据库代码数据库主键字段名 ci 文章分类 Java 后端开发

遇到一道数据库大题，考察SQL语句基础，很有代表性，决定记下来。

数据库

student——学生表

字段名	注释	类型
id	学号	bigint(20)
name	姓名	varchar(32)
city	城市	varchar(32)
email	电子邮箱	varchar(32)
main_teacher_id	班主任id	bigint(20)

主键：id

外键1：main_teacher_id——teacher#id

teacher——教师表

字段名	注释	类型
id	教师号	bigint(20)
name	教师姓名	varchar(32)

主键：id

course——课程表

字段名	注释	类型
id	课程号	bigint(20)
name	课程名	varchar(32)

主键：id

selesource——选课成绩表

字段名	注释	类型
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`='数学'
    )