mysql查询某学期开设的课程_Mysql----sql语句练习题(二)

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

多表查询

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.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