编辑代码

CREATE DATABASE test;
use test;

# 学生表 Student：
create table Student(
 
SId varchar(10) ,
Sname varchar(10),
Sage datetime,
Ssex varchar(10));
 
insert into Student values('01' , '赵铁柱' , '1990-01-01' , '男');
insert into Student values('02' , '钱重' , '1990-12-21' , '男');
insert into Student values('03' , '孙紫' , '1990-05-20' , '男');
insert into Student values('04' , '李云龙' , '1990-08-06' , '男');
insert into Student values('05' , '周懂眉' , '1991-12-01' , '女');
insert into Student values('06' , '吴狄' , '1992-03-01' , '女');
insert into Student values('07' , '郑迁津' , '1989-07-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李小刀' , '2017-12-25' , '女');
insert into Student values('11' , '王老五' , '2017-12-30' , '女');
insert into Student values('12' , '赵秀' , '2017-01-01' , '女');
insert into Student values('13' , '陈烈' , '2018-01-01' , '女');
insert into Student values('14' , '陈烈' , '2018-01-01' , '女');

-- SELECT * FROM Student;

# 课程表 Course
create table Course(
 
CId varchar(10),
Cname nvarchar(10),
TId varchar(10)); 
 
insert into Course values('01' , '语文' , '02'); 
insert into Course values('02' , '数学' , '01'); 
insert into Course values('03' , '英语' , '03');
insert into Course values('04' , '数学' , '04');

-- SELECT * FROM Course;

# 教师表 Teacher
create table Teacher(
 
TId varchar(10),
Tname varchar(10)); 
 
insert into Teacher values('01' , '张三峰');
insert into Teacher values('02' , '李太急'); 
insert into Teacher values('03' , '王天');
insert into Teacher values('04' , '小李子');

-- SELECT * FROM Teacher;

# 成绩表 SC
 
create table SC(
 
SId varchar(10),
CId varchar(10),
score decimal(18,1)); 
 
insert into SC values('01' , '01' ,90); 
insert into SC values('01' , '02' , 90); 
insert into SC values('01' , '03' , 99); 
insert into SC values('02' , '01' , 90); 
insert into SC values('02' , '02' , 60); 
insert into SC values('02' , '03' , 82); 
insert into SC values('03' , '01' , 86); 
insert into SC values('03' , '02' , 80); 
insert into SC values('03' , '03' , 81); 
insert into SC values('04' , '01' , 59); 
insert into SC values('04' , '02' , 30); 
insert into SC values('04' , '03' , 20); 
insert into SC values('05' , '01' , 75); 
insert into SC values('05' , '02' , 87); 
insert into SC values('06' , '01' , 33); 
insert into SC values('06' , '03' , 39); 
insert into SC values('07' , '02' , 90); 
insert into SC values('07' , '03' , 98);
insert into SC values('14' , '04' , 72);#03改为04， 08改为14

-- SELECT * FROM SC;

-- 查询' 01 '课程比' 02 '课程成绩高的学生的信息及课程分数
-- SELECT s.SId, s.Sname, s.Sage, s.Ssex, 
--     sc1.score AS Score_01, 
--     sc2.score AS Score_02
-- FROM Student s
-- JOIN SC sc1 ON s.SId = sc1.SId AND sc1.CId = '01'
-- JOIN SC sc2 ON s.SId = sc2.SId AND sc2.CId = '02'
-- WHERE sc1.score > sc2.score;

-- 查询同时存在' 01 '课程和' 02 '课程的情况
-- SELECT s.SId, s.Sname, s.Sage, s.Ssex, 
--     sc1.score AS Score_01, 
--     sc2.score AS Score_02
-- FROM Student s
-- JOIN SC sc1 ON s.SId = sc1.SId AND sc1.CId = '01'
-- JOIN SC sc2 ON s.SId = sc2.SId AND sc2.CId = '02'

-- 查询存在' 01 '课程但可能不存在' 02 '课程的情况(不存在时显示为 null )
-- SELECT s.SId, s.Sname, s.Sage, s.Ssex, 
--     sc1.score AS Score_01, 
--     sc2.score AS Score_02
-- FROM Student s
-- JOIN SC sc1 ON s.SId = sc1.SId AND sc1.CId = '01'
-- LEFT JOIN SC sc2 ON s.SId = sc2.SId AND sc2.CId = '02'

-- 查询不存在'01 '课程但存在' 02 '课程的情
-- SELECT s.SId, s.Sname, s.Sage, s.Ssex, 
--     sc1.score AS Score_01, 
--     sc2.score AS Score_02
-- FROM Student s
-- LEFT JOIN SC sc1 ON s.SId = sc1.SId AND sc1.CId = '01'
-- RIGHT JOIN SC sc2 ON s.SId = sc2.SId AND sc2.CId = '02'
-- WHERE s.SId NOT IN (
--     SELECT sc1.SId
--     FROM SC sc1
--     WHERE sc1.CId = '01'
-- );

-- 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
-- SELECT s.SId, s.Sname, AVG(sc.score) AS AvgScore
-- FROM Student s
-- JOIN SC sc ON s.SId = sc.SId
-- GROUP BY s.SId, s.Sname # 需要包含所有非聚合列，即s.Sname也必须写上
-- HAVING AVG(sc.score) >= 60;

-- 查询在 SC 表存在成绩的学生信息
-- #解题思路:sc表中有成绩的sid是重复的即每一个学生不止一门成绩,用student表中sid 存在于成绩表的sc表里面的条件,即可查出结果.
-- SELECT * FROM Student WHERE sid IN(SELECT sid FROM SC)

-- 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 NULL )
-- SELECT 
--     s.SId, 
--     s.Sname, 
--     COUNT(sc.CId) AS CourseCount, 
--     SUM(sc.score) AS TotalScore
-- FROM 
--     Student s
-- LEFT JOIN 
--     SC sc 
-- ON 
--     s.SId = sc.SId
-- GROUP BY 
--     s.SId, s.Sname; # 需要包含所有非聚合列，即s.Sname也必须写上

-- 查有成绩的学生信息
-- SELECT * FROM Student WHERE sid IN(SELECT sid FROM SC)

-- 查询「李」姓老师的数量
-- #解题思路:在计算数量时 count要带括号且括号里面有数字,进行匹配时用like语法; COUNT(1)和COUNT(*)作用相同,都是对所有的结果进行count
-- SELECT COUNT(*) AS TeacherCount
-- FROM Teacher
-- WHERE Tname LIKE '李%';

-- 查询学过「张三峰」老师授课的同学的信息
-- SELECT DISTINCT s.SId, s.Sname, s.Sage, s.Ssex
-- FROM Student s
-- JOIN SC sc ON s.SId = sc.SId
-- JOIN Course c ON sc.CId = c.CId
-- JOIN Teacher t ON c.TId = t.TId
-- WHERE t.Tname = '张三峰';

-- 查询没有学全所有课程的同学的信息
#解题思路: 所有课程即为course的数量,查询每个学生所学课程数量少于此数量的值即可,即sc表中cid的数量小于 course的总数量的同学信息;
-- SELECT DISTINCT a.SId, a.Sname, a.Sage, a.Ssex FROM Student a 
-- LEFT JOIN SC b ON a.sid=b.sid
-- GROUP BY a.sid, a.Sname, a.Sage, a.Ssex # DISTINCT 和 GROUP BY 的结合使用在这里是多余的，因为 GROUP BY 已经确保了结果的唯一性。
-- HAVING COUNT(b.sid) < (SELECT COUNT(*) FROM Course)

-- 查询至少有一门课与学号为' 01 '的同学所学相同的同学的信息
-- SELECT DISTINCT a.SId, a.Sname, a.Sage, a.Ssex, b.sid, b.cid FROM 
-- Student a JOIN SC b ON a.sid=b.sid
-- WHERE b.cid IN (SELECT cid FROM SC WHERE sid='01')

-- 查询和' 01 '号的同学学习的课程 完全相同的其他同学的信息

-- 查询没学过"张三峰"老师讲授的任一门课程的学生姓名
#解题思路:四表联合查出张三峰老师所上课程中上过这个课程的学生信息,利用排除法,不在其中的即为没有学过张三峰老师任何一门课程的学生;
SELECT sid, sname FROM Student WHERE sid NOT IN 
(SELECT DISTINCT a.sid FROM Student a LEFT JOIN SC b ON a.sid=b.sid
LEFT JOIN Course c ON b.cid=c.cid LEFT JOIN Teacher d ON c.tid=d.tid
WHERE d.tname='张三峰')