Student表:
create table Student(S 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('08' , '王菊' , '1990-01-20' , '女');
Course表:
create table Course(C varchar(10),Cname varchar(10),T varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
Teacher表:
create table Teacher(T varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
Scores表:
create table SC(S varchar(10),C varchar(10),score decimal(18,1));
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.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT s,AVG(score) AS 'pj' FROM sc GROUP BY s HAVING pj>=60#查出平均成绩大于等于60的学生编号
SELECT s.s,s.Sname,pj FROM student s,(SELECT s,AVG(score) AS 'pj' FROM sc GROUP BY s HAVING pj>=60) s2 WHERE s.s=s2.s;
2.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT * FROM student s LEFT JOIN sc ON s.s=sc.s;#通过成绩表来连接学生表和课程表 先把两个表连起来
SELECT s,COUNT(s),SUM(score) FROM sc GROUP BY s;#查询出每个人的选课数,和总成绩
SELECT s.s,Sname,IFNULL(`all`,0),`sum` FROM student s LEFT JOIN (SELECT s,COUNT(s) AS 'all',SUM(score) AS 'sum' FROM sc GROUP BY s) s2 ON s.s=s2.s;
3.查没有成绩的学生信息
SELECT * FROM
student
WHERE NOT EXISTS(SELECT*FROM sc WHERE student.S=sc.S)
4.查询「李」姓老师的数量
SELECT COUNT(Tname) FROM teacher WHERE Tname LIKE"李_";
5.查询学过「张三」老师授课的同学的信息
SELECT t FROM teacher WHERE Tname='张三';#先找到张三老师的编号
SELECT c FROM course WHERE t=(SELECT t FROM teacher WHERE Tname='张三');#在通过编号和课程表联查找出张三老师教的课程
SELECT s FROM sc WHERE c IN(SELECT c FROM course WHERE t=(SELECT t FROM teacher WHERE Tname='张三'));#通过成绩表查出上过张三老师课程的学号
SELECT * FROM student WHERE s IN (SELECT s FROM sc WHERE c IN(SELECT c FROM course WHERE t=(SELECT t FROM teacher WHERE Tname='张三'))); #通过学生表查出上过张三老师课程同学的信息
6.查询没有学全所有课程的同学的信息
SELECT s,COUNT(s) AS 'a' FROM sc GROUP BY s #查询出每个人选的课程数
SELECT s.s,IFNULL(s2.a,0) FROM student s LEFT JOIN (SELECT s,COUNT(s) AS 'a' FROM sc GROUP BY s) s2 ON s.s=s2.s;#查询出所有学生的学号和选课数
SELECT COUNT(c) FROM course;#查询出总课程数
SELECT * FROM student WHERE s IN(
SELECT s FROM(SELECT s.s,IFNULL(s2.a,0) AS 'b' FROM student s LEFT JOIN (SELECT s,COUNT(s) AS 'a' FROM sc GROUP BY s) s2 ON s.s=s2.s) c WHERE b<(SELECT COUNT(c) FROM course));
7.查询有两门课程及其以上不及格同学的学号,姓名及其平均成绩
SELECT * FROM sc WHERE score<60; #查询出不及格的学生编号和课程编号以及分数
SELECT s,COUNT(s) FROM sc WHERE score<60 GROUP BY s HAVING COUNT(s)>=2;#查询出不及格数大于等于2的学生编号和不及格的总数
SELECT * FROM student WHERE s IN(SELECT s FROM sc WHERE score<60 GROUP BY s HAVING COUNT(s)>=2);#查询出不及格数大于等于2的学生的信息
SELECT s, AVG(score) FROM sc WHERE s IN (SELECT s FROM sc WHERE score<60 GROUP BY s HAVING COUNT(s)>=2) GROUP BY s;#查询出不合格大于等于2的学生的学号和平均数
SELECT s1.s,s1.Sname,s2.b FROM (SELECT * FROM student WHERE s IN(SELECT s FROM sc WHERE score<60 GROUP BY s HAVING COUNT(s)>=2)) s1,(SELECT s, AVG(score) AS 'b' FROM sc WHERE s IN (SELECT s FROM sc WHERE score<60 GROUP BY s HAVING COUNT(s)>=2) GROUP BY s) s2 WHERE s1.s=s2.s;
8.检索" 01 "课程分数小于 60,按分数降序排列的学生信息
select * from sc where c='01' and score<60 #首先在成绩表中查找课程为01的并且成绩小于60分学生的信息
select s.* from student s,(select * from sc where c='01' and score<60) s2 WHERE s.s = s2.s ORDER BY score desc #通过联查查出结果
9.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩※※※
CREATE view view_a as
select Sname,cname,score from student left join sc on student.s=sc.s LEFT JOIN course on sc.c=course.c; #先将学生表、成绩表和课程表三个表左连接在一起查询出每个人相对应每门课的成绩
#查出每个人所对应的科目成绩
select
sname as '姓名',
sum(
case
when cname='英语' then score
else 0
end
) as '英语',
sum(
case
when cname='数学' then score
else 0
end
) as '数学',
sum(
case
when cname='语文' then score
else 0
end
) as '语文'
from view_a group by sname
#查询出每个人的平均成绩
SELECT sname,if(平均成绩 is null,0,平均成绩) as 'pjcj' from student LEFT JOIN (SELECT s,AVG(score) as '平均成绩' from sc GROUP BY s ) s2 on student.s=s2.s
#最终将以上三表联查得出结果
SELECT ss1.*,ss2.pjcj from (
select
sname as '姓名',
sum(
case
when cname='英语' then score
else 0
end
) as '英语',
sum(
case
when cname='数学' then score
else 0
end
) as '数学',
sum(
case
when cname='语文' then score
else 0
end
) as '语文'
from view_a group by sname) ss1,(SELECT sname,if(平均成绩 is null,0,平均成绩) as 'pjcj' from student LEFT JOIN (SELECT s,AVG(score) as '平均成绩' from sc GROUP BY s ) s2 on student.s=s2.s) ss2 where ss1.姓名=ss2.sname;
10.查询每门课程被选修的学生数
#查询出每门课程(课程编号)选的人数
select c,COUNT(s) as '人数' from sc GROUP BY c
SELECT cname,人数 from course ,(select c,COUNT(s) as '人数' from sc GROUP BY c) c1 WHERE course.C = c1.c
11.查询出只选修两门课程的学生学号和姓名
#查询出选课只有两名科目的学生编号
SELECT s,COUNT(c) from sc GROUP BY s HAVING COUNT(c) = 2
SELECT s1.s,Sname from student,(SELECT s,COUNT(c) from sc GROUP BY s HAVING COUNT(c) = 2) s1 WHERE s1.s=student.s
12.查询男生、女生人数
SELECT Ssex,COUNT(*) 人数 FROM student GROUP BY Ssex
13.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
#查出平均分大于等于85分学生的学号和平均分
SELECT s,AVG(score) from sc GROUP BY s HAVING AVG(score)>=85
SELECT s1.s,s1.Sname,s2.pjcj FROM student s1,(SELECT s,AVG(score) as 'pjcj' from sc GROUP BY s HAVING AVG(score)>=85) s2 WHERE s1.s= s2.s
14.查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
#通过课程表查出数学的编号
SELECT c from course WHERE cname='数学'
#查询出数学分数低于60分的同学的学号
SELECT sc.s,sc.score FROM sc,(SELECT c from course WHERE cname='数学') s1 WHERE score<60 and sc.c=s1.c
#通过学生表和上表连起来查询数学分数低于60分学生的姓名和分数
SELECT Sname,score from student,(SELECT sc.s,sc.score FROM sc,(SELECT c from course WHERE cname='数学') s1 WHERE score<60 and sc.c=s1.c ) ss1 WHERE student.s=ss1.s
15.查询所有学生的课程及分数情况※※※
#和第9题类似,只不过是少了个平均分,解体方法思路一样
CREATE view view_a as
select Sname,cname,score from student left join sc on student.s=sc.s LEFT JOIN course on sc.c=course.c; #先将学生表、成绩表和课程表三个表左连接在一起查询出每个人相对应每门课的成绩
#查出每个人所对应的科目成绩
select
sname as '姓名',
sum(
case
when cname='英语' then score
else 0
end
) as '英语',
sum(
case
when cname='数学' then score
else 0
end
) as '数学',
sum(
case
when cname='语文' then score
else 0
end
) as '语文'
from view_a group by sname
16.查询任何一门课程成绩在 70 分以上的学生姓名、课程名称和分数
SELECT sname,cname,score from student s,course c,sc WHERE s.s=sc.s and c.c=sc.c AND score>70
17.查询选修了全部课程的学生信息
SELECT COUNT(c) from course #查询出全部课程的数目
select s from sc group by s having count(s)=(select count(c) from course) #查询出选修了全部课程学生的学号
SELECT * FROM student WHERE s in (select s from sc group by s having count(s)=(select count(c) from course))
18.成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
SELECT t from teacher WHERE tname='张三' #查出张三老师的编号(t)
SELECT c from course WHERE t=(SELECT t from teacher WHERE tname='张三') #查询出张三老师所授课程的课程编号(c)
SELECT s,score FROM sc WHERE c=(SELECT c from course WHERE t=(SELECT t from teacher WHERE tname='张三')) ORDER BY score DESC LIMIT 1#查询出选修张三老师所授课程的学生最高的成绩的学号和成绩 (按照成绩降序排列然后在用limit查询出第一位)
SELECT s1.*,s2.score FROM student s1,(SELECT s,score FROM sc WHERE c=(SELECT c from course WHERE t=(SELECT t from teacher WHERE tname='张三')) ORDER BY score DESC LIMIT 1) s2 WHERE s1.s=s2.s
19.查询各科成绩前三名的学生信息※※※(不会!等想明白后在写上去) 😭 😓 😥 😔 😞
我实在是不会了,有没有大神帮我看一下。能不能在评论区告诉我该怎么做
20.既学过01课程又学过02课程的所有学生姓名
SELECT * FROM student WHERE s in (SELECT s1.s FROM (SELECT * from sc where c='01') s1,(SELECT * from sc where c='02') s2 where s1.s=s2.s);