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