Mysql----sql语句练习题(二)

 

今天有时间，咱们继续把数据库的查询内容继续补充，上次sql语句练习一是单表查询，今天的练习二是多表查询，

前期准备工作：请看上一篇的单表查询：https://www.cnblogs.com/xj-excellent/p/13331155.html；提前准备好三张表和数据，就可以开始多表查询了

多表查询

1.多表连接

1.1内连接

查询计算机系学生的选课情况，列出学生的名字、所选课程的课程号和考试成绩
查询“信息管理系”选了“计算机文学”课程的学生信息，列出学生姓名、课程名和成绩
查询所有选了"微机原理"课程的学生姓名和所在系
统计每个系学生的考试平均成绩
统计计算机系学生中每门课程的选课人数、平均成绩、最高成绩和最低成绩

1.2自连接

查询与李四在同一个系学习的学生姓名和所在系
查询与“数据结构与算法”课程在同一个学期开设的课程的课程名和开课学期

1.3外连接

查询全体学生的选课情况，包括选了课的学生和没有选课的学生
查询没有人选的课程的课程名
查询计算机系没有选课的学生，列出学生的姓名和性别
统计计算机系每个学生的选课门数，包括没有选课的学生
查询信息管理系选课门数少于3门的学生的学号和选课门数，包括没选课的学生。查询结果按选课门数递增排序

2.top限制

查询年龄最大的三个学生的姓名、年龄、所在系
查询年龄最大的三个学生的姓名、年龄、所在系（包括年龄并列第3名）
查询大学英语考试成绩最高的的前三名学生的姓名、所在系和大学英语考试成绩
查询选课人数最少的两门课程（不包括没有人选的课程），列出课程号和选课人数
查询计算机系选课门数超过两门的学生中，考试平均成绩最高的前两名（包括并列的情况）学生的序号、选课门数和平均成绩

3.将查询结果保存到表中

查询计算机系学生的学号、姓名、性别和年龄，并将查询结果保存到新表student_CS中
查询计算机系学生的学号、姓名、性别和年龄，并将查询结果保存到临时表student_CS_Two中

4.数据更改

4.1插入数据

将一个新生插入student表中，其学号为0821105，姓名为陈学冬，性别为男，年龄18岁，信息管理系学生

4.2更新数据

将所有学生的年龄+1
将'0811103'号学生的年龄改为17岁
将计算机系全体学生的成绩加5分

4.3删除数据

删除所有不及格学生的修课记录
删除计算机系不及格学生的修课记录

 

接下来，把所有的举例的多表查询的结果，放出来，供有需要的同学借鉴使用

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
/*1.1内连接*/
--查询计算机系学生的选课情况，列出学生的名字、所选课程的课程号和考试成绩
SELECT Sname,Cno,Grade FROM student JOIN sc ON student.Sno = sc.Sno WHERE Sdept = '计算机系'
SELECT Sname,Cno,Grade FROM student AS s  JOIN sc ON s.Sno = sc.Sno WHERE Sdept = '计算机系'
--查询“信息管理系”选了“计算机文学”课程的学生信息，列出学生姓名、课程名和成绩
SELECT Sname,Cname,Grade
FROM student AS s JOIN sc ON s.Sno = sc.Sno
JOIN Course ON course.Cno = sc.Cno
WHERE sdept = '信息管理系' AND Cname = '计算机文化学'
-- 查询所有选了"微机原理"课程的学生姓名和所在系
SELECT Sname,Sdept,Cname FROM student s
JOIN sc ON s.Sno=sc.Sno
JOIN course ON course.Cno= sc.Cno
WHERE Cname = '微机原理'
-- 统计每个系学生的考试平均成绩
SELECT Sdept,AVG(Grade) AS 平均成绩 FROM student s JOIN sc ON s.Sno = sc.Sno GROUP BY Sdept
-- 统计计算机系学生中每门课程的选课人数、平均成绩、最高成绩和最低成绩
SELECT Cno,COUNT(*) AS 选课人数,AVG(Grade) AS 平均成绩,MAX(Grade) AS 最高成绩,MIN(Grade) AS 最低成绩
FROM student s JOIN sc ON s.Sno = sc.Sno WHERE Sdept = '计算机系' GROUP BY Cno
 
/*1.2自连接*/
-- 查询与李四在同一个系学习的学生姓名和所在系
SELECT s2.Sname,s2.Sdept
FROM student s1 JOIN student s2 ON s1.Sdept = s2.Sdept
WHERE s1.Sname = '李四'AND s2.Sname != '李四'
-- 查询与“数据结构与算法”课程在同一个学期开设的课程的课程名和开课学期
SELECT c1.Cname,c1.Semester
FROM course c1 JOIN course c2 ON c1.Semester = c2.Semester
WHERE c2.Cname = "数据结构与算法"
 
/*1.3外连接*/
-- 查询全体学生的选课情况，包括选了课的学生和没有选课的学生
SELECT s.Sno,Sname,Cno,Grade FROM student s LEFT JOIN sc ON s.Sno = sc.Sno
-- 查询没有人选的课程的课程名
SELECT Cname FROM course c LEFT JOIN sc ON c.Cno = sc.Cno WHERE sc.Cno IS null
-- 查询计算机系没有选课的学生，列出学生的姓名和性别
SELECT Sname,Ssex FROM student s  LEFT JOIN  sc ON s.Sno = sc.Sno WHERE Sdept = '计算机系' AND sc.Sno IS null
-- 统计计算机系每个学生的选课门数，包括没有选课的学生
SELECT Sname,s.Sno,COUNT(*) AS 选课门数 FROM student s  LEFT JOIN  sc ON s.Sno = sc.Sno WHERE Sdept = '计算机系' GROUP BY s.Sno
-- 查询信息管理系选课门数少于3门的学生的学号和选课门数，包括没选课的学生。查询结果按选课门数递增排序
SELECT s.Sno,Sname,COUNT(sc.Cno) AS 选课门数
FROM student s  LEFT JOIN  sc ON s.Sno = sc.Sno
WHERE Sdept = '信息管理系'
GROUP BY s.Sno
HAVING COUNT(sc.Cno)<3
ORDER BY COUNT(sc.Cno) ASC
 
 
/*2、top限制*/
-- 查询年龄最大的三个学生的姓名、年龄、所在系
SELECT top 3 Sname, Sage, Sdept FROM student ORDER BY Sage DESC
-- 查询年龄最大的三个学生的姓名、年龄、所在系（包括年龄并列第3名）
SELECT top 3 WITH TIES Sname, Sage, Sdept FROM student ORDER BY Sage DESC
-- 查询大学英语考试成绩最高的的前三名学生的姓名、所在系和大学英语考试成绩
SELECT top 3 WITH TIES Sname,Sdept,Cname,Grade FROM student s JOIN sc ON s.Sno = sc.Sno JOIN course ON course.Cno = sc.Cno WHERE Cname = '大学英语' ORDER BY Grade DESC
-- 查询选课人数最少的两门课程（不包括没有人选的课程），列出课程号和选课人数
SELECT top 2 WITH TIES Cno,COUNT(*)AS 选课人数 FROM sc  GROUP BY Cno ORDER BY COUNT(*) ASC
-- 查询计算机系选课门数超过两门的学生中，考试平均成绩最高的前两名（包括并列的情况）学生的序号、选课门数和平均成绩
SELECT top 2 WITH TIES s.Sno,AVG(Grade)AS 平均成绩 ,COUNT(*) AS 选课门数 FROM student s JOIN sc ON s.Sno = sc.Sno
WHERE Sdept = '计算机系' GROUP BY s.Sno HAVING COUNT(*) > 2 ORDER BY AVG(Grade) DESC
 
/*3、将查询结果保存到表中*/
-- 查询计算机系学生的学号、姓名、性别和年龄，并将查询结果保存到新表student_CS中
SELECT Sno,Sname,Ssex,Sage INTO student_CS FROM student WHERE Sdept = '计算机系'
-- 查询计算机系学生的学号、姓名、性别和年龄，并将查询结果保存到临时表student_CS_Two中
SELECT Sno,Sname,Ssex,Sage INTO #student_CS_Two FROM student WHERE Sdept = '计算机系'
 
/*4、数据更改*/
/*4.1插入数据*/
-- 将一个新生插入student表中，其学号为0821105，姓名为陈学冬，性别为男，年龄18岁，信息管理系学生
INSERT INTO student VALUES('0821105','陈学冬','男','18','信息管理系')
/*4.2更新数据*/
-- 将所有学生的年龄+1
UPDATE student SET Sage = Sage + 1
-- 将'0811103'号学生的年龄改为17岁
UPDATE student SET Sage = 17 WHERE Sno = '0811103'
-- 将计算机系全体学生的成绩加5分
UPDATE sc SET Grade = Grade + 5
FROM sc JOIN student  ON sc.Sno = student.Sno
WHERE Sdept = '计算机系'
 
/*4.3删除数据*/
-- 删除所有不及格学生的修课记录
DELETE FROM sc WHERE Grade < 60
-- 删除计算机系不及格学生的修课记录
DELETE FROM sc
   FROM sc JOIN student ON sc.Sno = student.Sno WHERE Sdept = '计算机系' AND Grade < 60

　　

　　

 


__EOF__

本文作者：excellent_1
本文链接：https://www.cnblogs.com/xj-excellent/p/13740971.html
关于博主：互联网小萌新一名，希望从今天开始慢慢提高，一步步走向技术的高峰！
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力！
posted @   excellent_1  阅读(4737)  评论(0)  编辑  收藏  举报
编辑推荐：
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
阅读排行：
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下
· 【杭电多校比赛记录】2025“钉耙编程”中国大学生算法设计春季联赛（1）
点击右上角即可分享
微信分享提示