2小时解不完的数据库练习题，来挑战一下吧！

2023-06-15 16:20 由久曲健发表于 #其他

写在前面

我已经记不起来，有多久没更新文章了。

5月中旬我还在上班，中旬以后一系列发生的事情，真的远远超出了可承受范围，只能硬着头皮面对！

我是谁，我应该是谁，又能怎样，只能向前·····

数据库实例

class表

course表

score表

student表

teacher表

实际语句

1、查询所有的课程的名称以及对应的任课老师姓名

# 自链接  
SELECT c.name,t.name FROM course c,teacher t WHERE c.teacher_id=t.id  
# 内连接  
SELECT course.name,teacher.name FROM course  
INNER JOIN teacher ON course.teacher_id=teacher.id

结果：

2、查询学习课程"数据结构"比课程"java语言"成绩低的学生的学号；

# 内连接  
SELECT shuju.student_id FROM  
(SELECT score.course_id,  
score.student_id,  
score.mark  
FROM score  
INNER JOIN course  
ON score.course_id=course.id  
WHERE course.name='数据结构') AS shuju  
INNER JOIN  
(SELECT score.course_id,  
score.student_id,  
score.mark  
FROM score  
INNER JOIN course  
ON score.course_id=course.id  
WHERE course.name='java') AS java  
ON shuju.student_id=java.student_id  
WHERE shuju.mark<java.mark  
  
# 自连接  
SELECT shuju.student_id  
FROM  
(SELECT s.course_id,  
s.student_id,  
s.mark  
FROM score s, course c  
WHERE c.`name`='数据结构'  
AND s.course_id=c.id) shuju,  
(SELECT s.course_id,  
s.student_id,  
s.mark  
FROM score s, course c  
WHERE c.`name`='java'  
AND s.course_id=c.id) java  
WHERE shuju.student_id=java.student_id  
AND shuju.mark<java.mark

结果：

3、查询平均成绩大于65分的同学的id和平均成绩(保留两位小数)

SELECT score.student_id,  
round(AVG(score.mark),2) AS avgScore  
FROM score  
GROUP BY score.student_id  
HAVING avgScore>65

结果：

4、查询平均成绩大于65分的同学的姓名和平均成绩(保留两位小数)

SELECT student.`name`,  
ROUND(AVG(score.mark),2) AS avgScore  
FROM score  
INNER JOIN student  
ON student.id=score.student_id  
GROUP BY score.student_id  
HAVING avgScore>65

结果：

5、查询所有同学的姓名、选课数、总成绩

SELECT student.name AS '名字', COUNT(score.course_id) AS '选课数',SUM(score.mark) AS '总成绩'FROM score
INNER JOIN student
ON student.id=score.student_id
GROUP BY  student_id

结果：

6、查询没学过"大牛"老师课的同学的姓名

select student.name from student  
where id not in(select student_id from score where course_id in(select course.id from course inner join teacher  
on course.teacher_id = teacher.id where teacher.name='大牛'))

结果：

7、查询学过"大牛"老师所教的全部课程的同学的姓名

select student.name from student  
where id in(select student_id from score where course_id in(3,3))

结果：

8、查询所有课程成绩小于60分的同学的姓名

select student.name from student inner join score on student.id = score.student_id  
where score.mark<60 group by score.student_id

结果：

9、查询选修了全部课程的学生姓名

select student.name from student  
where id in (select score.student_id from score group by score.student_id having count(1)=(select count(1) from course))

结果：

10、查询至少有一门课程与"小草"同学所学课程相同的同学姓名

SELECT student.name
FROM student
WHERE id IN 
    (SELECT student_id
    FROM score
    WHERE course_id IN 
        (SELECT course_id
        FROM score
        WHERE student_id=5))
            AND student.name!='小草'

结果：

11、查询至少有一门课程和"小草"同学所学课程不相同的同学姓名

select student.name from student  
where id in (select student_id from score  
where course_id not in (select course_id from score  
where student_id=5)) and student.name!='小草'

结果：

12、查询各科成绩最高和最低的分：以如下形式显示：课程id，最高分，最低分

select course_id as '课程id',max(mark) as '最高分',min(mark) as '最低分'from score group by course_id

结果：

13、查询只选修了一门课程的学生的学号和姓名

# 感觉有点low，但是能查出来  
select student.id as '学号',student.name as '姓名'from student inner join score on student.id = score.student_id  
where student.id=(select student_id from score group by student_id having count(course_id)=1)

# 这个好一些  
select student.id as '学号',student.name as '姓名'from student inner join score on student.id = score.student_id  
group by student_id having count(course_id)=1

结果：

14、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程id降序排列

select course.id as '课程id',course.name AS "课程名称",avg(mark) as "平均成绩" from course  
inner JOIN score on course.id = score.course_id  
group by course_id order by avg(mark) ,"平均成绩",course_id desc

结果：

15、按平均成绩倒序显示所有学生的"数据库原理"、“java语言”、"C语言"三门的课程成绩，

按如下形式显示：学生id、数据库原理、java语言、C语言、课程数、平均分；(高级应用较难)

select sc.student_id as '学生id',  
(select mark from score inner join course on course.id=score.course_id where course.name='数据库原理' and score.student_id=sc.student_id) as '数据库原理',  
(select mark from score inner join course on course.id=score.course_id where course.name='java' and score.student_id=sc.student_id) as 'java',  
(select mark from score inner join course on course.id=score.course_id where course.name='C语言' and score.student_id=sc.student_id) as 'C语言',  
count(course_id) as '课程数',  
round(avg(sc.mark),2) as '平均分'  
from score as sc group by sc.student_id  
order by avg(sc.mark) desc

结果：

写在最后

整个数据库这部分的复习，早在近一个月前就开始了。

在做了两道题后，就遇到了各种事情，就被搁置了，差点被遗忘了。。。

今天有时间，接着把学习的感觉续上，总体下来，算是初步复习了下sql的一些常用查询操作，就一个测试仔来说，我个人感觉这些都能写正确写出来，真的很厉害，我也是用了近6小时呢。

不管遇到了什么难事，学习、跑步都不能停（我又胖了5斤，好扎心）.....

明天继续我的5公里，加油！