数据准备
创建学生表(Student)、教师表(Teacher)、科目表(Course)、成绩表(SC)
# 学生表 Student：
create table Student(
SId varchar(10) ,
Sname varchar(10),
Sage datetime,
Ssex varchar(10));
# 教师表 Teacher
create table Teacher(
TId varchar(10),
Tname varchar(10));
# 科目表 Course
create table Course(
CId varchar(10),
Cname nvarchar(10),
TId varchar(10));
# 成绩表 SC
create table SC(
SId varchar(10),
CId varchar(10),
score decimal(18,1)); 插入数据
# 学生表 Student：
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' , '女');
# 科目表 Course
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
# 教师表 Teacher
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
# 成绩表 SC
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98); 题目总览
1.1查询同时存在01课程和02课程的情况
1.2查询存在01课程但可能不存在02课程的情况（不存在时显示为null）
1.3查询不存在01课程但存在02课程的学生情况
2.0查询平均成绩大于等于60分的学生编号和学生姓名和平均成绩
3.0查询在sc表存在成绩的学生信息
4.0查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩（没成绩的显示为nulll）
5.0查询[李]姓老师的数量
6.0查询学过[张三]老师授课的同学信息(难度系数★★★)
7.0查询没有学全所有课程的同学的信息
8.0查询至少有一门课学号为01的同学所学相同的同学信息
9.0查询和01号同学学习的课程完全相同的其他同学的信息
【分析】左边是01的课程记录，右边是02课程的记录，sid能关联上的就是说明01，02课程记录都有，用子查询+inner join。
-- 自关联 --
SELECT *
FROM sc a
INNER JOIN sc b
ON a.sid = b.sid
WHERE a.cid = '01' AND b.cid = '02';
-- 先查询01课程信息 再左关联02课程信息 --
SELECT *
FROM sc
WHERE cid = '01'
SELECT *
FROM (SELECT * FROM sc WHERE cid = '01') a
LEFT JOIN sc
ON a.sid = sc.sid
WHERE sc.cid = '02';
-- 分别查询01和02的课程信息 再进行INNER JOIN --
SELECT *
FROM (SELECT * FROM sc WHERE cid = '01') a
INNER JOIN (SELECT * FROM sc WHERE cid = '02') b
ON a.sid = b.sid;1.2查询存在01课程但可能不存在02课程的情况（不存在时显示为null）
【分析】先找出存在01的课程记录，然后和自己的其他课程做关联，如果是02就关联上了，若不是02就关联不上， 用left join。
-- 先查询01的课程情况 再LEFT JOIN 02课程 --
SELECT *
FROM sc
WHERE cid = '01'
SELECT *
FROM (SELECT * FROM sc WHERE cid = '01') a
LEFT JOIN sc
ON a.sid = sc.sid AND sc.cid = '02';
-- 分别查询01和02课程记录 再进行LEFT JOIN --
SELECT *
FROM (SELECT * FROM sc WHERE cid = '01') a
LEFT JOIN (SELECT * FROM sc WHERE cid = '02') b
ON a.sid = b.sid;
-- 自关联 (a表为主表) --
SELECT *
FROM sc a
LEFT JOIN sc b
ON a.sid = b.sid AND b.cid = '02'
WHERE a.cid = '01';1.3查询不存在01课程但存在02课程的学生情况
【分析】先找出不存在01课程的学生，这些学生里面学习过02课程的就是我们想要的结果
-- 先查询不存在01课程的sid (NOT IN) 再筛选有02课程记录的 --
SELECT sid
FROM sc
WHERE cid = '01'
SELECT *
FROM sc
WHERE sid NOT IN (SELECT sid FROM sc WHERE cid = '01')
AND cid = '02';
-- INNER JOIN --
SELECT *
FROM (SELECT * FROM sc WHERE sid NOT IN (SELECT sid FROM sc WHERE cid = '01')) a
INNER JOIN sc b
ON a.sid = b.sid AND b.cid = '02';2.0查询平均成绩大于等于60分的学生编号和学生姓名和平均成绩
【分析】要求查询平均成绩大于等于60分的同学信息，首先确定实在成绩表里面找，找到这样的同学之后，用sid去学生信息表里面关联，就可以得到学生的姓名信息，关键就是找sid。
-- 先查出学生成绩大于60的学生 再用sid与学生表关联 --
SELECT sid,AVG(score) avg_score
FROM sc
GROUP BY sid
HAVING AVG(score) > 60;
SELECT s.sid 学生编号,s.sname 学生姓名,ROUND(a.avg_score) 平均成绩
FROM (SELECT sid,AVG(score) avg_score FROM sc GROUP BY sid HAVING AVG(score) > 60) a
LEFT JOIN student s
ON a.sid = s.sid;3.0查询在sc表存在成绩的学生信息
【分析】成绩表肯定都是有学生的，用成绩表左关联就可以得到学生信息。
-- 先查询sc表中的sid 再与学生表student关联 --
SELECT sid
FROM sc
SELECT s.*
FROM (SELECT sid FROM sc) a
LEFT JOIN student s
ON a.sid = s.sid
GROUP BY sid;4.0查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩（没成绩的显示为nulll）
【分析】所有同学存在于学生表，课程数、成绩情况在成绩表，那就是要学生表左关联成绩汇总表得到记录，关联不上的说明就是没成绩。
-- 学生表左关联成绩表 --
select a.sid,a.sname,b.couns,b.sum_score
from student a
left join
(select sid,count(cid) as couns,sum(score) as sum_score
from sc
group by sid) b
on a.sid = b.sid;
-- 先把成绩信息和学生信息进行汇总再进行查询 --
select
a.sid,
a.sname,
count(b.cid) as cons,
sum(b.score) as sum_score
from student a
left join sc b
on a.sid = b.sid
group by a.sid;
5.0查询[李]姓老师的数量
【分析】like语法的使用
select count(tid) as cons
from teacher
where tname like '李%';6.0查询学过[张三]老师授课的同学信息(难度系数★★★)
# 方法一
-- 1.先筛选张三老师的tid --
SELECT tid
FROM teacher
WHERE tname = "张三";
-- 从课程表中筛选出张三老师教授的课程id --
SELECT cid
FROM course
WHERE tid IN (SELECT tid
FROM teacher
WHERE tname = "张三");
-- 2.从成绩表中筛选张三老师课程id对应的sid --
SELECT *
FROM sc
WHERE cid IN (SELECT cid
FROM course
WHERE tid IN (SELECT tid
FROM teacher
WHERE tname = "张三"));
-- 3.从学生表中筛选出信息 --
SELECT *
FROM student
WHERE sid IN (SELECT sid
FROM sc
WHERE cid IN (SELECT cid
FROM course
WHERE tid IN (SELECT tid
FROM teacher
WHERE tname = "张三")));
# 方法二
-- 1.先将教师表teacher与科目表course关联tid筛选出tid,tname,cid --
SELECT *
FROM teacher t
INNER JOIN course c
ON t.tid = c.tid AND t.tname = "张三";
-- 2.将以上结果与成绩表sc关联cid筛选出sid,cid,tname --
SELECT sc.sid,a.cid,a.tname
FROM (SELECT t.tid,t.tname,c.cid
FROM teacher t
INNER JOIN course c
ON t.tid = c.tid AND t.tname = "张三") a
INNER JOIN sc
ON a.cid = sc.cid;
-- 3.将以上结果与学生表student关联sid筛选出sid,sname,sage,ssex,tname --
SELECT s.*,a.tname
FROM (SELECT sc.sid,a.cid,a.tname
FROM (SELECT t.tid,t.tname,c.cid
FROM teacher t
INNER JOIN course c
ON t.tid = c.tid AND t.tname = "张三") a
INNER JOIN sc
ON a.cid = sc.cid) a
INNER JOIN student s
ON a.sid = s.sid;
7.0查询没有学全所有课程的同学的信息
【分析】只需要用学生信息关联到成绩sc表得到每个人的课程信息，然后分组计数就知道是不是满足条件，这里需要成绩表一门功课只有一条记录。
-- 先统计出所有课程的数量 --
SELECT COUNT(cid)
FROM course;
-- 从成绩表sc中筛选出选课数目小于以上结果的信息 --
SELECT sc.*,COUNT(sc.cid) 选课数目
FROM sc
GROUP BY sid
HAVING 选课数目 < (SELECT COUNT(cid)FROM course);
-- 把以上结果与学生表student进行sid关联 --
SELECT s.*,a.*
FROM student s
INNER JOIN (SELECT SC.*,COUNT(cid) 选课数目
FROM sc
GROUP BY sid
HAVING 选课数目 < (SELECT COUNT(cid)FROM course c)) a
ON s.sid = a.sid;8.0查询至少有一门课学号为01的同学所学相同的同学信息
# 法一
-- 1.先从成绩表sc筛选出01同学所学的课程信息 --
SELECT cid
FROM sc
WHERE .sid = "01";
-- 2.再从成绩表sc中筛选满足以上条件的信息 --
SELECT *
FROM sc
WHERE cid IN (SELECT cid FROM sc WHERE sid = "01");
-- 3.将以上结果与学生表student自关联去重得到结果 --
SELECT DISTINCT a.*
FROM student a
INNER JOIN (SELECT *
FROM sc
WHERE cid IN (SELECT cid FROM sc WHERE sid = "01")) b
ON a.sid = b.sid;
# 法二
SELECT DISTINCT s.*
FROM sc
INNER JOIN student s
ON sc.sid = s.sid
WHERE sc.cid IN (SELECT cid FROM sc WHERE sid = "01");
# 法三
SELECT s.*
FROM student s
INNER JOIN (SELECT sc.sid
FROM sc
WHERE sc.cid IN (SELECT cid FROM sc WHERE sid = "01")
GROUP BY sc.sid) a
ON s.sid = a.sid;
9.0查询和01号同学学习的课程完全相同的其他同学的信息
【分析】确保没有01课程外的课程记录且课程数量一致，没有学习01号同学课程外的同学信息。
-- 1，先从成绩表sc中筛选出01号同学学习的课程 --
SELECT cid
FROM sc
WHERE sid = "01";
-- 2.再从成绩表sc中筛选出没有选修01号同学课程的sid --
SELECT sid
FROM sc
WHERE cid NOT IN (SELECT cid
FROM sc
WHERE sid = "01");
-- 3.与学生表student关联,用sid进行分组 --
SELECT s.*
FROM sc
INNER JOIN student s
ON sc.sid = s.sid
WHERE sc.cid NOT IN (SELECT sid
FROM sc
WHERE cid NOT IN (SELECT cid
FROM sc
WHERE sid = "01"))
GROUP BY sc.sid
HAVING COUNT(1) = (SELECT COUNT(1) FROM sc WHERE sid = "01");