DB Fiddle - SQL Database Playground
上面的链接是在线数据库查询，请选择SQL8.0
上面是sql学习网站
资源链接：Mysql+JDBC资料：
链接：https://pan.baidu.com/s/18W0ZRYaCR60mQWFP92CJsg
提取码：2you
SQL99 源自MySQL经典四表查询(教师，学生，成绩，课程表)多表查询_Hank.ll的博客-CSDN博客_学生表成绩表课程表多表查询
SQL92，个人练习的答案！！
-- 1.学生表
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20),
sage DATE,
ssex ENUM ('男','女')
);
-- 2.课程表中使用了外键教师编号，因而需要先建立教师表
CREATE TABLE teacher(
tid INT PRIMARY KEY AUTO_INCREMENT,
tname VARCHAR(20)
);
-- 3.建立课程表
CREATE TABLE course(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20),
tid INT,
FOREIGN KEY (tid) REFERENCES teacher (tid)
);
-- 4.建立成绩表
CREATE TABLE sc(
sid INT,
cid INT,
score INT
);
-- 先给student表插入数据
INSERT INTO student VALUES (1,'赵雷','1990-01-01','男'),
(2,'钱电','1990-12-21','男'),
(3,'孙风','1990-05-20','男'),
(4,'李云','1990-08-06','男'),
(5,'周梅','1991-12-01','女'),
(6,'吴兰','1992-03-01','女'),
(7,'郑竹','1989-07-01','女'),
(8,'王菊','1990-01-20','女');
-- 给teacher表插入数据,这里不可以先给course表插入数据,因为course表外键连接到teacher的主键
INSERT INTO teacher VALUES(1,'张三'),(2,'李四'),(3,'王五');
-- 给course表插入数据
INSERT INTO course VALUES(1,'语文',2),(2,'数学',1),(3,'英语',3);
-- 最后给sc表插入数据
INSERT INTO sc VALUES(1,1,90),(1,2,80),(1,3,90),(2,1,70),(2,2,60),(2,3,80),(3,1,80),
(3,2,80),(3,3,80),(4,1,50),(4,2,30),(4,3,20),(5,1,76),(5,2,87),(6,1,31),(6,3,34),(7,2,89),(7,3,98);
（1）查询 1 课程比 2 课程成绩高的学生的信息及课程分数
select * from student s
join (select s1.sid,s1.score as score1,s2.score as score2 from sc s1 join sc s2
on s1.sid=s2.sid
where s1.cid=1 and s2.cid=2 and s1.score>s2.score)t on
s.sid=t.sid;sid sname sage ssex sid score1 score2
1 赵雷 1990-01-01 男 1 90 80
2 钱电 1990-12-21 男 2 70 60
4 李云 1990-08-06 男 4 50 30
（2）查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select s.sid,s.sname,t.avgscore
from student s
join
(select sid,avg(score) avgscore from sc group by sid having avgscore>=60)t
on s.sid=t.sid;sid sname avgscore
1 赵雷 86.6667
2 钱电 70.0000
3 孙风 80.0000
5 周梅 81.5000
7 郑竹 93.5000
（3）查询名字中含有"风"字的学生信息
select * from student where sname like '%风%';sid sname sage ssex
3 孙风 1990-05-20 男
（4）查询课程名称为"数学"，且分数低于60的学生姓名和分数
select s.sname,t.score
from student s
join (select sc.score,sid
from sc,(select cid,cname from course where cname='数学') t
where sc.cid=t.cid and sc.score<60)t
on s.sid=t.sid;sname score
李云 30
（5）查询所有学生的课程及分数情况
select s.sname, c.cid,c.cname,sc.score from
student s left join sc
on s.sid=sc.sid left join course c
on c.cid=sc.cid order by cid;sname cid cname score
王菊 null null null
赵雷 1 语文 90
吴兰 1 语文 31
钱电 1 语文 70
周梅 1 语文 76
孙风 1 语文 80
李云 1 语文 50
赵雷 2 数学 80
郑竹 2 数学 89
周梅 2 数学 87
李云 2 数学 30
孙风 2 数学 80
钱电 2 数学 60
李云 3 英语 20
孙风 3 英语 80
吴兰 3 英语 34
郑竹 3 英语 98
钱电 3 英语 80
赵雷 3 英语 90
（6）查询没学过"张三"老师授课的同学的信息
select * from student
where sid not in(select sid from sc
where cid=
(select cid from course where tid=(select tid from teacher where tname='张三')))sid sname sage ssex
6 吴兰 1992-03-01 女
8 王菊 1990-01-20 女
（7）查询学过编号为 1 并且也学过编号为 2 的课程的同学的信息
select s.* from student s join
(select s1.sid from sc s1 join sc s2
on s1.sid=s2.sid
where s1.cid=1 and s2.cid=2)t
on s.sid=t.sid;sid sname sage ssex
1 赵雷 1990-01-01 男
2 钱电 1990-12-21 男
3 孙风 1990-05-20 男
4 李云 1990-08-06 男
5 周梅 1991-12-01 女
（8）查询学过编号为 1 但是没有学过编号为 2 的课程的同学的信息
select * from student
where sid=(select sid from sc
where sid not in(select sid from sc where cid =2)
and cid =1);sid sname sage ssex
6 吴兰 1992-03-01 女
（10）查询没有学全所有课程的同学的信息
select student.* from student join (select sid,count(cid) countcid
from sc
group by sid
having countcid<(select count(cid) from course))t
on student.sid=t.sid;改进的SQL语句
(select student.* from student join
(select sid,count(cid) countcid
from sc
group by sid
having countcid<(select count(cid) from course))t
on student.sid=t.sid )
UNION(select * from student where sid not in(select sid from sc))
sid sname sage ssex
5 周梅 1991-12-01 女
6 吴兰 1992-03-01 女
7 郑竹 1989-07-01 女
（11）查询至少有一门课与学号为"1"的同学所学相同的同学的信息
select * from student
where sid
in(select sid from sc where cid
in(select cid from sc where sid=1) group by sid)
and sid<>1;sid sname sage ssex
2 钱电 1990-12-21 男
3 孙风 1990-05-20 男
4 李云 1990-08-06 男
5 周梅 1991-12-01 女
6 吴兰 1992-03-01 女
7 郑竹 1989-07-01 女
（12）查询和"1"号的同学学习的课程完全相同的其他同学的信息
select s.* from student s
join (select sid,count(cid) c from sc group by sid
having c =(select count(cid) from sc where sid=1)) t
on s.sid=t.sid where s.sid<>1;sid sname sage ssex
2 钱电 1990-12-21 男
3 孙风 1990-05-20 男
4 李云 1990-08-06 男
（13）查询没学过"张三"老师讲授的任一门课程的学生信息
select s.*
from student s
where sid not in(select sid from sc
where cid=(select cid from course
where tid=(select tid from teacher where tname='张三' )));sid sname sage ssex
6 吴兰 1992-03-01 女
8 王菊 1990-01-20 女
（14）查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select s.sname,sc.*
from student s
join sc on s.sid=sc.sid
where sc.score>70;sname sid cid score
赵雷 1 1 90
赵雷 1 2 80
赵雷 1 3 90
钱电 2 3 80
孙风 3 1 80
孙风 3 2 80
孙风 3 3 80
周梅 5 1 76
周梅 5 2 87
郑竹 7 2 89
郑竹 7 3 98
15）查询1990年出生的学生信息(注：student表中sage列的类型是datetime)
select * from student where sage like '1990-%';
sid sname sage ssex 1 赵雷 1990-01-01 男 2 钱电 1990-12-21 男 3 孙风 1990-05-20 男 4 李云 1990-08-06 男 8 王菊 1990-01-20 女
（16）查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select avg(score) as avgscore,cid from sc group by cid
order by avgscore desc ,cid asc ;
avgscore cid 71.0000 2 67.0000 3 66.1667 1
（17）查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select s.sname,t.cid,t.score from student s
join (select * from sc where score>70)t on s.sid=t.sid;
sname cid score 赵雷 1 90 赵雷 2 80 赵雷 3 90 钱电 3 80 孙风 1 80 孙风 2 80 孙风 3 80 周梅 1 76 周梅 2 87 郑竹 2 89 郑竹 3 98
（18）查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩,并按照平均成绩降序排列
select s.sid,s.sname,t.avgscore
from student s
join (select sid,avg(score) as avgscore
from sc group by sid having avg(score)>=85 )t
on s.sid=t.sid order by avgscore desc;
sid sname avgscore 7 郑竹 93.5000 1 赵雷 86.6667
（21）求每门课程的学生人数
select cid ,count(sid) from sc group by cid;
cid count(sid) 1 6 2 6 3 6
（23）查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select a1.*,a2.cid,a2.score from sc a1 join sc a2 on a1.sid=a2.sid
where a1.score=a2.score and a1.cid<>a2.cid order by a1.score;
sid cid score cid score 3 3 80 1 80 3 2 80 1 80 3 3 80 2 80 3 1 80 2 80 3 2 80 3 80 3 1 80 3 80 1 3 90 1 90 1 1 90 3 90
扫一扫
2458
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
