Ref
https://zhuanlan.zhihu.com/p/50662216
https://www.cnblogs.com/yuanyuan2017/
00 建表和添加数据
|
-- 建表 -- 学生表 CREATE TABLE `Student`( `s_id` VARCHAR(20), `s_name` VARCHAR(20) NOT NULL DEFAULT '', `s_birth` VARCHAR(20) NOT NULL DEFAULT '', `s_sex` VARCHAR(10) NOT NULL DEFAULT '', PRIMARY KEY(`s_id`) ); -- 课程表 CREATE TABLE `Course`( `c_id` VARCHAR(20), `c_name` VARCHAR(20) NOT NULL DEFAULT '', `t_id` VARCHAR(20) NOT NULL, PRIMARY KEY(`c_id`) ); -- 教师表 CREATE TABLE `Teacher`( `t_id` VARCHAR(20), `t_name` VARCHAR(20) NOT NULL DEFAULT '', PRIMARY KEY(`t_id`) ); -- 成绩表 CREATE TABLE `Score`( `s_id` VARCHAR(20), `c_id` VARCHAR(20), `s_score` INT(3), PRIMARY KEY(`s_id`,`c_id`) ); -- 插入学生表测试数据 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('08' , '王菊' , '1990-01-20' , '女'); -- 课程表测试数据 insert into Course values('01' , '语文' , '02'); insert into Course values('02' , '数学' , '01'); insert into Course values('03' , '英语' , '03');
-- 教师表测试数据 insert into Teacher values('01' , '张三'); insert into Teacher values('02' , '李四'); insert into Teacher values('03' , '王五');
-- 成绩表测试数据 insert into Score values('01' , '01' , 80); insert into Score values('01' , '02' , 90); insert into Score values('01' , '03' , 99); insert into Score values('02' , '01' , 70); insert into Score values('02' , '02' , 60); insert into Score values('02' , '03' , 80); insert into Score values('03' , '01' , 80); insert into Score values('03' , '02' , 80); insert into Score values('03' , '03' , 80); insert into Score values('04' , '01' , 50); insert into Score values('04' , '02' , 30); insert into Score values('04' , '03' , 20); insert into Score values('05' , '01' , 76); insert into Score values('05' , '02' , 87); insert into Score values('06' , '01' , 31); insert into Score values('06' , '03' , 34); insert into Score values('07' , '02' , 89); insert into Score values('07' , '03' , 98); |
01课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号。
很明显，需要查询的是分数score表
select s_id from score where c_id='01'
select s_id from score where c_id='02'
-- 既选了01 又选了02课的同学 我的写法
select tb1.s_id sid from
(select * from score where c_id='01') tb1,
(select * from score where c_id='02') tb2
where tb1.s_id = tb2.s_id and tb1.s_score > tb2.s_score;
-- 看了参考答案后背着写
将一张表当作两张表使用
select a.s_id from score a join score b on a.s_id=b.s_id and a.s_score > b.s_score
where a.c_id='01' and b.c_id='02' ;
-- sql99标准的表连接查询，改造最初的写法
select tb1.s_id from (select * from score where c_id='01') tb1
join (select * from score where c_id='02') tb2
on tb1.s_id=tb2.s_id
where tb1.s_score > tb2.s_score;
02查询平均成绩大于60分的学生的学号和平均成绩
查询score表，单表查询
select s_id, avg(s_score) avgScore from score GROUP BY s_id having avgScore > 60;
03 查询所有学生的学号、姓名、选课数、总成绩
两表联合查询，用student表左连接score表，可以查询出student表的所有信息。
select stu.s_id, stu.s_name,count(score.s_id), sum(score.s_score)
from student stu
left join score
on stu.s_id = score.s_id
group by stu.s_id
注意是左连接，否则会丢失数据——王菊同学一门课也没选，但是题目要求查询所有的同学。
04 查询姓“张”的老师的个数
select teacher.*, count(*) from teacher where t_name like '张%'
05 查询没学过“张三”老师课的学生的学号、姓名(重点)
-- 最初的思路，下面的sql是查询张三老师的所有学生id
select s.s_id from student s join score
on s.s_id = score.s_id join course c
on score.c_id = c.c_id join teacher t
on c.t_id=t.t_id
where t.t_name='张三'
-- 我的做法，稍微罗嗦了点
select s_id, s_name from student where s_id not in(
select s.s_id from student s join score
on s.s_id = score.s_id join course c
on score.c_id = c.c_id join teacher t
on c.t_id=t.t_id
where t.t_name='张三'
)
-- 参考答案
select s_id,s_name from student where s_id not in (
select s_id from score join course c
on score.c_id = c.c_id join teacher t
on c.t_id=t.t_id
where t.t_name='张三'
)
06 查询学过“张三”老师所教的所有课的同学的学号、姓名
这道题是上道题的一个子集。注意灵活使用“in”关键词
select s.s_id,s.s_name from student s join score
on s.s_id = score.s_id join course c
on score.c_id = c.c_id join teacher t
on c.t_id=t.t_id
where t.t_name='张三'
参考答案
select s_id, s_name
from Student
where s_id in
(select s_id from Score join Course on Score.c_id = Course.c_id
join Teacher on Course.t_id = Teacher.t_id
where t_name = '张三');
07 查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
查询course表、score表、student表
学过01课的sid
select s_id from score where c_id='01'
学过02课的sid
select s_id from score where c_id='02'
既学了课01有学了课02的sid
select t1.s_id from
(select * from score where c_id='01') t1
join
(select * from score where c_id='02') t2
on t1.s_id=t2.s_id
完整sql
select s_id, s_name from student where s_id in(
select t1.s_id from
(select * from score where c_id='01') t1
join
(select * from score where c_id='02') t2
on t1.s_id=t2.s_id
)
另一种写法
将一张表当两张表用
select s_id, s_name from student where s_id in(
select a.s_id from score a join score b
on a.c_id='01' and b.c_id='02'
where a.s_id=b.s_id
)
08 查询课程编号为“02”的总成绩
select sum(s_score) from score where c_id='02'
select sum(s_score) from (select s_score from score where c_id='02')b
09 查询所有课程成绩小于60分的学生的学号、姓名
这条sql会缺失数据，因为没有成绩的学生被排除在外了，不知道怎么解决
select * from student left join score
on student.s_id=score.s_id
group by student.s_id
having s_score < 60;
换一种思路
select * from student where s_id not in(
select s_id from score where s_score >=60
)
10 查询没有学全所有课的学生的学号、姓名
“没有学全课”意思是说总共有n门课，某同学学习的课程数小于n
查询“学全”了课的同学，再取反
select s_id from score
group by s_id
having count(c_id)=(select count(*) from course)
需要注意，没选课的同学也要被包含进去
select s_id, s_name from student where s_id not in (
select s_id from score
group by s_id
having count(c_id)=(select count(*) from course)
)
写不动了=。=晚点再来。