1. 题目概览
- 查询"01"课程比"02"课程成绩高的学生的信息及课程分数
- 查询"01"课程比"02"课程成绩低的学生的信息及课程分数
- 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
- 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
- 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
- 查询"李"姓老师的数量
- 查询学过"张三"老师授课的同学的信息
- 查询没学过"张三"老师授课的同学的信息
- 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
- 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
- 查询没有学全所有课程的同学的信息
- 查询至少有一门课与学号为"01"的同学所学相同的同学的信息
- 查询和"01"号的同学学习的课程完全相同的其他同学的信息
- 查询没学过"张三"老师讲授的任一门课程的学生姓名
- 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
- 检索"01"课程分数小于60,按分数降序排列的学生信息
- 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
- 查询各科成绩最高分、最低分和平均分:
以如下形式显示:
课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90 - 按各科成绩进行排序,并显示排名
- 查询学生的总成绩并进行排名
- 查询不同老师所教不同课程平均分从高到低显示
- 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
- 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
- 查询学生平均成绩及其名次
- 查询各科成绩前三名的记录
- 查询每门课程被选修的学生数
- 查询出只有两门课程的全部学生的学号和姓名
- 查询男生、女生人数
- 查询名字中含有"风"字的学生信息
- 查询同名同性学生名单,并统计同名人数
- 查询1990年出生的学生名单(注:Student表中Sage列的类型是datetime)
- 查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号
- 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
- 查询课程名称为"数学",且分数低于60的学生姓名和分数
- 查询所有学生的课程及分数情况
- 查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
- 查询不及格的课程
- 查询课程编号为01且课程成绩在80分及以上的学生的学号和姓名;
- 求每门课程的学生人数
- 查询选修"李四"老师所授课程的学生中,成绩最高的学生信息及其成绩
- 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
- 查询每门课程成绩最好的前两名
- 统计每门课程的学生选修人数(超过5人的课程才统计)
要求输出课程号和选修人数,查询结果按人数降序排列,
若人数相同,按课程号升序排列 - 检索至少选修两门课程的学生学号
- 查询选修了全部课程的学生信息
- 查询各学生的年龄
- 查询本周过生日的学生
- 查询下周过生日的学生
- 查询本月过生日的学生
- 查询下月过生日的学生
2. 创建库
/*
# 创建数据库:
drop database if exists student1;
create database if not exists Student1;
show databases;
use Student1 ;
#创建测试数据
create table Student(Sno varchar(10),Sname varchar(10),Sage datetime,Ssex nvarchar(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' , '女');
create table Course(Cno varchar(10),Cname varchar(10),Tno varchar(10));
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
create table Teacher(Tno varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
create table SC(Sno varchar(10),Cno 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);
# 查看数据:
select * from student ;
select * from course;
select * from teacher;
select * from SC;
*/
#3. 练习题
use Student1 ;
#1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数:
select a.*,b.score as '01-score',c.score as '02-score'
from student a
inner join SC b on a.SNO=b.SNO and b.cno='01'
inner join SC c on a.SNO=c.SNO and c.cno='02'
where b.score>c.score ;
#2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select a.*,b.score as '01-score',c.score as '02-score'
from student a
inner join SC b on a.SNO=b.SNO and b.cno='01'
inner join SC c on a.SNO=c.SNO and c.cno='02'
where b.score<c.score ;
#3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select a.sno,a.sname,avg(ifnull(b.score,0)) as '平均成绩'
from student a
left join SC b using(SNO)
group by a.sno
having avg(ifnull(b.score,0))>=60;
#4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
select sno,sname,avg(ifnull(b.score,0)) as avgnum
from student a
left join SC as b using(sno)
group by a.sno
having avg(ifnull(b.score,0))<=60;
#5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select sno,
sname,
count(cno) as '选课总数',
sum(score) as '总成绩'
from student a
left join SC b using(sno)
group by sno
order by sum(score) desc;
#6、查询"李"姓老师的数量
select count(*) as '李老师人数' from teacher where TNAME like '李%';
#7、查询学过"张三"老师授课的同学的信息
select a.* from student a
inner join (SC b,course c,teacher d)
on a.sno=b.sno
and b.Cno=c.CNO
and c.TNO=d.TNO
where d.tname='张三';
select a.* from student a
inner join SC as b on a.sno=b.sno
inner join course as c on b.Cno=c.CNO
inner join teacher as d on c.TNO=d.TNO
where d.TNAME='张三';
#8、查询没学过"张三"老师授课的同学的信息
select aa.* from student aa where not exists (
select * from SC as b
inner join course as c on b.Cno=c.CNO
inner join teacher as d on c.TNO=d.TNO
where d.TNAME='张三' and aa.sno=b.sno
);
#9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select a.* from student a
inner join SC b on a.sno=b.sno and b.Cno='01' where exists(
select * from SC c where c.Cno='02' and a.sno=c.sno );
select a.* from student a
inner join SC b on a.sno=b.sno and b.cno='01'
inner join SC c on a.sno=c.sno and c.Cno='02';
#10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select a.* from student a
inner join SC b on a.sno=b.sno and b.cno='01' where not exists(
select * from SC c where c.cno='02' and a.SNO=c.sno);
select a.* from student a
left join SC b on a.sno=b.sno and b.cno='01'
left join SC c on a.sno=c.sno and c.Cno='02'
where c.Cno is null and b.cno='01';
#11、查询没有学全所有课程的同学的信息
select a.* from student a
left join SC b using(sno)
group by a.sno
having count(cno)<(
select count(cno) from course);
SELECT * FROM student a
LEFT JOIN sc b ON a.sno=b.sno
LEFT JOIN (SELECT COUNT(1) anum FROM course) c
ON 1=1
GROUP BY 1,2,3,4
HAVING MAX(c.anum)>COUNT(b.cno);
#12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select a.* from student a
left join SC b on a.sno=b.sno where exists (
select * from SC where sno='01' and b.cno=cno)
group by a.sno;
#13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select a.* from student a
inner join SC b using(sno)
group by sno
having count(cno)=(select count(*) from SC where sno='01') and Sno != '01';
SELECT aa.sno,aa.sname,aa.sage,aa.ssex
FROM (
SELECT a.*,COUNT(b.cno) AS snum FROM student a
INNER JOIN sc b ON a.sno=b.sno
WHERE EXISTS( SELECT 1 FROM sc WHERE sno='01' AND cno=b.cno ) GROUP BY 1,2,3,4
) aa
INNER JOIN (
SELECT a.*,COUNT(b.cno) AS anum FROM student a
INNER JOIN sc b ON a.sno=b.sno GROUP BY 1,2,3,4
) bb ON aa.sno=bb.sno
INNER JOIN (
SELECT COUNT(1) AS num1 FROM sc WHERE sno='01'
) cc ON 1=1
WHERE aa.snum=bb.anum AND aa.snum=cc.num1 and aa.sno !='01';
SELECT a.*,COUNT(b.cno) AS anum
,SUM(CASE WHEN EXISTS(SELECT 1 FROM sc WHERE sno='01' AND cno=b.cno)THEN 1 ELSE 0 END) AS snum
,MAX(c.num1) AS num1
FROM student a
INNER JOIN sc b
ON a.sno=b.sno
INNER JOIN (SELECT COUNT(1) AS num1 FROM sc WHERE sno='01') c
ON 1=1
GROUP BY 1,2,3,4
HAVING anum=snum AND anum=num1;
#14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select * from student aa where not exists (
select * from teacher a
inner join course b using(tno)
inner join SC c using(cno)
where a.Tname='张三' and aa.sno=c.sno
);
select aa.* from student aa
left join(
select * from teacher a
inner join course b using(tno)
inner join SC c using(cno)
where a.Tname='张三'
) bb
on aa.sno =bb.sno where bb.tno is null;
SELECT a.* FROM student a
LEFT JOIN(
SELECT * FROM student a
LEFT JOIN sc b ON a.sno=b.sno
LEFT JOIN course c ON b.cno=c.cno
LEFT JOIN teacher d ON c.tno=d.tno AND d.tname='张三'
WHERE d.tno IS NOT NULL
) b
ON a.sno=b.sno WHERE b.sno IS NULL;
#15、查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
select a.sno,a.sname,avg(b.score) as avgnum
from student a
left join SC b on a.Sno=b.Sno and b.score<60
group by a.sno
having count(cno)>=2;
select a.sno,a.sname,avg(b.score) as avgnum
from student a
left join SC b on a.Sno=b.Sno
group by a.sno
having SUM(CASE WHEN b.score>=60 THEN 0 ELSE 1 END)>=2;
#16、检索"01"课程分数小于60,按分数降序排列的学生信息
select a.*,b.score from student a
inner join SC b using(sno)
where b.cno='01' and b.score<60
order by score desc;
#17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.*,b.sc01,c.sc02,d.sc03,avg(dd.score) as avgN
from student a
left join (
select sno,score as 'sc01' from SC where cno='01'
) b on a.Sno=b.sno
left join (
select sno,score as 'sc02' from SC where cno='02'
) c on a.sno=c.sno
left join (
select sno,score as 'sc03' from SC where cno='03'
) d on a.sno=d.sno
left join SC dd on a.sno=dd.sno
group by sno
order by avg(dd.score) desc;
SELECT a.*
,SUM(CASE WHEN b.cno='01' THEN b.score ELSE 0 END) AS s01
,SUM(CASE WHEN b.cno='02' THEN b.score ELSE 0 END) AS s02
,SUM(CASE WHEN b.cno='03' THEN b.score ELSE 0 END) AS s03
,AVG(CASE WHEN b.score IS NULL THEN 0 ELSE b.score END) avs
FROM student a
LEFT JOIN sc b
ON a.sno=b.sno
GROUP BY 1,2,3,4
ORDER BY avs DESC;
/*18、查询各科成绩最高分、最低分和平均分:
以如下形式显示:
课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90 */
select cno as '课程ID',
Cname as '课程name',
max(score) as '最高分',
min(score) as '最低分',
avg(score) as '平均分',
sum(case when score >=60 then 1 else 0 end)/count(cno) as '及格率',
sum(case when score >=70 and score<80 then 1 else 0 end)/count(cno) as '中等率',
sum(case when score >=80 and score<90 then 1 else 0 end)/count(cno) as '优良率',
sum(case when score >=90 then 1 else 0 end)/count(cno) as '优秀率'
from course a
inner join SC b using(cno)
group by cno;
#19、按各科成绩进行排序,并显示排名
SET @yw:=0;
set @sx:=0;
set @yy:=0;
SELECT aa.*,
(case cno when '01' then @yw:=@yw+1
when '02' then @sx:=@sx+1
when '03' then @yy:=@yy+1 else 0 end) as rank
FROM(
SELECT a.*,b.Sno,b.score FROM course a
INNER JOIN sc b ON a.cno=b.cno
ORDER BY b.cno,score DESC,sno
)aa;
#第二种:
SELECT a.*,c.cname,count(b.cno)+1 as rank FROM sc a
left JOIN sc b ON a.cno=b.cno and a.score<b.score
left join course c on a.Cno=c.Cno
group by 1,2,3
order by a.cno,a.score desc;
#20、查询学生的总成绩并进行排名
set @b:=0;
select bb.*,@b:=@b+1 as '名次' from (
select a.*,
sum(case cno when '01' then score else 0 end) as '语文',
sum(case cno when '02' then score else 0 end) as '数学',
sum(case cno when '03' then score else 0 end) as '英语',
sum(score) as '总成绩'
from student a
left join sc b on a.sno=b.sno group by 1,2,3,4 order by sum(score) desc
) bb;
#21、查询不同老师所教不同课程平均分从高到低显示
select b.cno,b.Cname,b.Tno,c.Tname,
avg(score) as '平均分'
from SC a
inner join course b on a.Cno=b.Cno
inner join teacher c on b.Tno=c.Tno
group by a.cno
order by 平均分 desc;
#22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
SELECT a.*,b.cname,b.score
FROM student a
INNER JOIN (
SELECT a.*
,c.cname
,COUNT(b.cno)+1 AS tp
FROM sc a
LEFT JOIN sc b
ON a.cno=b.cno AND a.score<b.score
LEFT JOIN course c
ON a.cno=c.cno
GROUP BY 1,2,3,4
HAVING tp IN(2,3)
ORDER BY a.cno,tp)b
ON a.sno=b.sno order by cname,score desc;
select aa.*,c.Cno,c.Cname,a.score
from student aa
inner join SC a on aa.Sno=a.Sno
inner join course c on c.Cno=a.Cno
where (select count(*) from SC b where b.cno=a.cno and b.score<a.score) in(2,3)
order by c.cname,a.score desc;
#23、统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
select
cno as '课程号',
cname as '课程名',
sum(case when score>=85 then 1 else 0 end) as '[100-85]',
sum(case when score>=70 and score<85 then 1 else 0 end) as '[85-70]',
sum(case when score>=60 and score<70 then 1 else 0 end) as '[70-60]',
sum(case when score<60 then 1 else 0 end) as '[0-60]',
concat(round(sum(case when score>=85 then 1 else 0 end)/count(*)*100,1),'%') as '[100-85]%',
concat(round(sum(case when score>=70 and score<85 then 1 else 0 end)/count(*)*100,1),'%') as '[85-70]%',
concat(round(sum(case when score>=60 and score<70 then 1 else 0 end)/count(*)*100,1),'%') as '[70-60]%',
concat(round(sum(case when score<60 then 1 else 0 end)/count(*)*100,1),'%') as '[0-60]%'
from SC a
inner join course b using(cno)
group by cno,cname;
#24、查询学生平均成绩及其名次
#First method:
select aa.*,
@rownum := @rownum + 1 AS rank
from (
select a.*,
avg(ifnull(score,0)) as avgnum
from student a
left join SC b using(sno)
group by sno
order by avgnum desc
)aa,
(SELECT @rownum:= 0) r;
#Second method:
SELECT a.*,COUNT(b.sno)+1 as rank
FROM (
SELECT a.*,AVG(CASE WHEN b.score IS NULL THEN 0 ELSE b.score END) AS ascore
FROM student a
LEFT JOIN sc b
ON a.sno=b.sno
GROUP BY 1,2,3,4
)a
LEFT JOIN(
SELECT a.*,AVG(CASE WHEN b.score IS NULL THEN 0 ELSE b.score END) AS ascore
FROM student a
LEFT JOIN sc b
ON a.sno=b.sno
GROUP BY 1,2,3,4
)b
ON a.ascore<b.ascore
GROUP BY 1,2,3,4,5 order by ascore desc;
#25、查询各科成绩前三名的记录
select a.sno,c.cno,c.cname,a.score
from SC a
inner join course c on c.Cno=a.Cno
where (select count(*)+1 from SC b where b.cno=a.cno and b.score>a.score)<=3
order by c.cname,a.score desc;
SELECT a.*,COUNT(b.cno)+1 AS ascore
FROM sc a
LEFT JOIN sc b
ON a.cno=b.cno AND a.score<b.score
GROUP BY 1,2,3
HAVING ascore<=3
ORDER BY a.cno,ascore;
#26、查询每门课程被选修的学生数
select a.Cname as '课程',
count(*) as '学生数'
from course a,SC b where a.cno=b.cno
group by a.cno;
#27、查询出只有两门课程的全部学生的学号和姓名
select a.sno,a.Sname
from student a
left join SC b using(sno)
group by sno
having count(cno)=2;
#28、查询男生、女生人数
select ssex,count(*) as '人数' from student group by ssex;
select
sum(case when Ssex='男' then 1 else 0 end) as '男生数',
sum(case when Ssex='女' then 1 else 0 end) as '女生数'
from student;
#29、查询名字中含有"风"字的学生信息
select * from student where Sname like '%风%';
#30、查询同名同性学生名单,并统计同名人数
select sname,ssex,count(*) as '同名人数'
from student
group by sname,ssex
having count(*)>1;
#31、查询1990年出生的学生名单(注:Student表中Sage列的类型是datetime)
select * from student where year(Sage)=1990;
#32、查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号
select cno,round(avg(score),2) as avgnum
from course a
left join SC b using(cno)
group by cno
order by avgnum desc,cno;
#33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select sno,Sname,avg(score) as ascore
from student a
left join SC b using(sno)
group by sno
having ascore>=85;
#34、查询课程名称为"数学",且分数低于60的学生姓名和分数
select a.Sname,c.Cname,b.score
from student a
left join SC b on a.Sno=b.Sno
left join course c on b.Cno=c.Cno
where b.score<60 and c.Cname='数学';
#35、查询所有学生的课程及分数情况
select a.Sname,c.Cname,b.score
from student a
left join SC b on a.Sno=b.Sno
left join course c on b.Cno=c.Cno;
#36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
select a.Sname,c.Cname,b.score
from student a
left join SC b on a.Sno=b.Sno
left join course c on b.Cno=c.Cno
where b.score>70
order by sname,cname;
#37、查询不及格的课程
select a.Sname,c.Cname,b.score
from student a
left join SC b on a.Sno=b.Sno
left join course c on b.Cno=c.Cno
where b.score<70
order by sname,cname;
#38、查询课程编号为01且课程成绩在80分及以上的学生的学号和姓名;
select a.sno,a.sname,b.score from student a
inner join SC b using(sno)
where b.cno='01' and b.score>=80;
#39、求每门课程的学生人数
select a.Cname,count(*) as '人数'
from course a
left join SC b on a.Cno=b.Cno
group by a.cno;
#40、查询选修"李四"老师所授课程的学生中,成绩最高的学生信息及其成绩
select a.*,score from student a
inner join SC b using(sno)
inner join (
select b.cno,max(b.score) as maxcj
from SC b
inner join course c on b.Cno=c.cno
inner join teacher d on c.Tno=d.Tno
where d.Tname='李四'
)bb
on b.cno=bb.cno and b.score=bb.maxcj;
#41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select a.Sname,b.cno,b.score
from student a
left join SC b using(sno) where exists
(
select * from SC cc
where b.score=cc.score and b.cno!=cc.cno and a.sno=cc.sno
);
SELECT a.*
FROM sc a
LEFT JOIN sc b
ON a.sno=b.sno AND a.score=b.score AND a.cno<>b.cno
GROUP BY 1,2,3
HAVING COUNT(b.sno)>0;
#42、查询每门课程成绩最好的前两名
select a.sno,c.cno,c.cname,a.score
from SC a
inner join course c on c.Cno=a.Cno
where (select count(*)+1 from SC b where b.cno=a.cno and b.score>a.score)<=2
order by c.cname,a.score desc;
SELECT a.*,COUNT(b.cno)+1 AS ascore
FROM sc a
LEFT JOIN sc b
ON a.cno=b.cno AND a.score<b.score
GROUP BY 1,2,3
HAVING ascore<=2
ORDER BY a.cno,ascore;
/*43、统计每门课程的学生选修人数(超过5人的课程才统计)。
要求输出课程号和选修人数,查询结果按人数降序排列,
若人数相同,按课程号升序排列
*/
select cno,count(*) as num from SC
group by Cno
having num>5
order by num desc,cno;
#44、检索至少选修两门课程的学生学号
select sno from SC group by sno having count(*)>=2;
#45、查询选修了全部课程的学生信息
select a.* from student a
inner join SC b using(sno)
group by 1,2,3,4
having count(*)=(select count(*) from course);
#46、查询各学生的年龄
select sno,sname,(year(curdate())-year(Sage)) as age from student;
#47、查询本周过生日的学生
select * from student where
date_format(sage,'%m%d')
between date_format(subdate(curdate(),weekday(curdate())),'%m%d')
and (date_format(subdate(curdate(),weekday(curdate())),'%m%d')+6);
-- 48、查询下周过生日的学生
# 关闭安全模式,修改数据,开启安全模式
set sql_safe_updates=0;
#update student set sage = '1990-01-01' where sno='01';
update student set sage = '1990-10-16' where sno='01';
set sql_safe_updates=1;
set @NextMonday:=(date_format(subdate(curdate(),weekday(curdate())),'%m%d')+7);
set @NextSunday:=(date_format(subdate(curdate(),weekday(curdate())),'%m%d')+13);
select * from student where
date_format(sage,'%m%d') between @NextMonday and @NextSunday;
-- 49、查询本月过生日的学生
select * from student where month(Sage)=month(curdate());
-- 50、查询下月过生日的学生
select * from student where month(Sage)=(month(curdate())+2);