-- 创建学生表student
create table student
(Sno varchar(10) not null,
Sname varchar(10) ,
Sage date ,
Ssex varchar(10) ,
primary key (Sno));
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
(Cno varchar(10) not null,
Cname varchar(10) ,
Tno varchar(10) ,
primary key (Cno));
insert into course values ('01', '语文', '02');
insert into course values ('02', '数学', '01');
insert into course values ('03', '英语', '03');
-------------------------------------------------------------------------------------------------------------
-- 创建教师表teacher
create table teacher
(Tno varchar(10) not null,
Tname varchar(10) ,
primary key (Tno));
insert into teacher values ('01', '张三');
insert into teacher values ('02', '李四');
insert into teacher values ('03', '王五');
-------------------------------------------------------------------------------------------------------------
-- 创建成绩表 sc
create table sc
(Sno varchar (10) ,
Cno varchar (10) ,
score decimal(18,1),
primary key (Sno, Cno));
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);
2 Like
2.1 查询「李」姓老师的数量
select count(*) from teacher
where tname like '李%';
-------------------------------------------------------
count(*)
1
2.2 查询名字中含有「风」字的学生信息
select * from student
where sname like '%风%';
-------------------------------------------------------
Sno Sname Sage Ssex
03 孙风 1990-05-20 男
3 聚合函数
3.1 查询男生、女生人数
select ssex, count(*) from student
group by ssex;
-------------------------------------------------------
ssex count(*)
女 4
男 4
3.2 查询课程编号为02的总成绩
select cno, sum(score) from sc
group by cno having cno='02';
-------------------------------------------------------
cno sum(score)
02 436.0
3.3 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select cno, avg(score) as avg_score from sc
group by cno
order by avg_score desc, cno asc;
-------------------------------------------------------
cno avg_score
02 72.66667
03 68.50000
01 64.50000
3.4 求每门课程的学生人数
select cno, count(sno) as student_number
from sc group by cno;
-------------------------------------------------------
cno count(sno)
01 6
02 6
03 6
3.5 统计每门课程的学生选修人数（超过 5 人的课程才统计）
select cno, count(*) as student_number
from sc group by cno having count(*)>5
-------------------------------------------------------
cno student_number
01 6
02 6
03 6
3.6 检索至少选修两门课程的学生学号
select sno from sc
group by sno having count(cno)>=2;
-------------------------------------------------------
sno
01
02
03
04
05
06
07
select * from student
where sno not in (select sno from sc where cno =01)
and sno in (select sno from sc where cno=02);
-------------------------------------------------------
Sno Sname Sage Ssex
07 郑竹 1989-07-01 女
4.3 查询同时存在” 01 “课程和” 02 “课程的学生情况
select * from student
where sno in(select sno from sc where cno='01')
and sno in (select sno from sc where cno='02');
-------------------------------------------------------
Sno Sname Sage Ssex
01 赵雷 1990-01-01 男
02 钱电 1990-12-21 男
03 孙风 1990-05-20 男
04 李云 1990-08-06 男
05 周梅 1991-12-01 女
4.4 查询出只选修两门课程的学生学号和姓名
select sno, sname from student
where sno in (select sno from sc group by sno having count(cno)=2);
-------------------------------------------------------
sno sname
05 周梅
06 吴兰
07 郑竹
4.5 查询没有学全所有课程的同学的信息
select * from student
where sno not in
(select sno from sc group by sno having count(cno)=(select count(*) from course));
-------------------------------------------------------
Sno Sname Sage Ssex
05 周梅 1991-12-01 女
06 吴兰 1992-03-01 女
07 郑竹 1989-07-01 女
08 王菊 1990-01-20 女
4.6 查询学全了所有课程的学生信息
select * from student
where sno in
(select sno from sc group by sno having count(cno)=(select count(*) from course));
-------------------------------------------------------
Sno Sname Sage Ssex
01 赵雷 1990-01-01 男
02 钱电 1990-12-21 男
03 孙风 1990-05-20 男
04 李云 1990-08-06 男
4.7 查询所有课程成绩均小于60分的学号、姓名
select sno,sname
from student
where sno in (select sno from sc group by sno having max(score)<60);
-------------------------------------------------------
sno sname
04 李云
06 吴兰
4.8 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select sno, sname from student
where sno in (select sno from sc where cno='01' and score>80);
-------------------------------------------------------
sno sname
空 空
4.9 查询学过「张三」老师授课的同学的信息
select * from student
where sno in (
select sno from sc, course, teacher
where course.tno=teacher.tno and sc.cno=course.cno and teacher.tname='张三'
);
-------------------------------------------------------
Sno Sname Sage Ssex
01 赵雷 1990-01-01 男
02 钱电 1990-12-21 男
03 孙风 1990-05-20 男
04 李云 1990-08-06 男
05 周梅 1991-12-01 女
07 郑竹 1989-07-01 女
-------------------------------------------------------------------------------------------------------------
select * from student
where sno in (
select sno from sc where cno in (
select cno from course where tno = (
select tno from teacher where tname='张三'
)
)
);
-------------------------------------------------------
Sno Sname Sage Ssex
01 赵雷 1990-01-01 男
02 钱电 1990-12-21 男
03 孙风 1990-05-20 男
04 李云 1990-08-06 男
05 周梅 1991-12-01 女
07 郑竹 1989-07-01 女
4.10 查询没学过”张三”老师讲授的任一门课程的学生姓名
select sname from student
where sno not in (
select sno from sc, course, teacher
where course.tno=teacher.tno and sc.cno=course.cno and teacher.tname='张三'
);
-------------------------------------------------------
sname
吴兰
王菊
4.11 查询至少有一门课与学号为” 01 “的同学所学相同的其他同学的信息
记得排除学号为“01”的同学本人
-------------------------------------------------------------------------------------------------------------
select * from student
where sno in (
select sno from sc where cno in (
select cno from sc where sno='01'
)
and sno !='01'
);
-------------------------------------------------------
Sno Sname Sage Ssex
02 钱电 1990-12-21 男
03 孙风 1990-05-20 男
04 李云 1990-08-06 男
05 周梅 1991-12-01 女
06 吴兰 1992-03-01 女
07 郑竹 1989-07-01 女
-------------------------------------------------------------------------------------------------------------
select * from student
where sno in (
select sno from sc where cno in (
select cno from sc where sno='01'
)
) and sno !='01';
-------------------------------------------------------
Sno Sname Sage Ssex
02 钱电 1990-12-21 男
03 孙风 1990-05-20 男
04 李云 1990-08-06 男
05 周梅 1991-12-01 女
06 吴兰 1992-03-01 女
07 郑竹 1989-07-01 女
4.12 查询和” 01 “号的同学学习的课程完全相同的其他同学的信息
条件一（表a）：选出至少一门课相同的同学（in子查询），与01同学选课相同的科目数=01同学的总科目数
条件二（表b）：选课总数与01同学的选课总数也相同
接下来的步骤，表a与表b相交求学号，再结合student表求信息
-------------------------------------------------------------------------------------------------------------
select * from student where sno in (
select a.sno from
(select sno, count(cno) from sc
where cno in (
select cno from sc where sno=01
)
group by sno
having count(cno)=(select count(cno) from sc where sno=01)
and sno != 01
) a
inner join
(select sno, count(sno) from sc
group by sno
having count(sno)=(select count(cno) from sc where sno=01)
and sno != 01
) b
on a.sno=b.sno
)
-------------------------------------------------------
Sno Sname Sage Ssex
02 钱电 1990-12-21 男
03 孙风 1990-05-20 男
04 李云 1990-08-06 男
4.13 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
注意：题目本身有歧义，我的理解是找出选了不止一门课，且自己所有课程分数相同的学生
思路：条件1：该学生总选课数>1
条件2：满足1，且该学生所有课程分数最大值=最小值，即可保证所有课程分数相等
-------------------------------------------------------------------------------------------------------------
select * from sc where sno in (
select sno
from sc
group by sno
having min(score)=max(score) and count(*)>1
);
-------------------------------------------------------
Sno Cno score
03 01 80.0
03 02 80.0
03 03 80.0
5 内连接
5.1 检索” 01 “课程分数小于 60，按分数降序排列的学生信息
select b.*, a.score
from sc as a inner join student as b on a.sno=b.sno
where a.cno='01' and a.score<60 order by a.score desc;
-------------------------------------------------------
Sno Sname Sage Ssex score
04 李云 1990-08-06 男 50.0
06 吴兰 1992-03-01 女 31.0
-------------------------------------------------------------------------------------------------------------
select a.*, b.score
from student as a inner join
(select sno, score from sc where cno='01' and score<60) as b
on a.sno=b.sno
order by b.score desc;
-------------------------------------------------------
Sno Sname Sage Ssex score
04 李云 1990-08-06 男 50.0
06 吴兰 1992-03-01 女 31.0
5.2 查询不及格的课程及学生名，学号，按课程号从大到小排列
select a.cno, b.sname, b.sno
from sc as a inner join student as b on a.sno=b.sno
where a.score<60 order by a.cno desc;
-------------------------------------------------------
cno sname sno
03 李云 04
03 吴兰 06
02 李云 04
01 李云 04
01 吴兰 06
5.3 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select b.sname, a.score
from sc as a inner join student as b on a.sno=b.sno
where a.score<60 and a.cno =(select cno from course where cname='数学');
-------------------------------------------------------
sname score
李云 30.0
5.4 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select a.sno, b.sname, avg(a.score)
from sc as a inner join student as b on a.sno=b.sno
group by a.sno having avg(a.score)>=85;
-------------------------------------------------------
sno sname avg(a.score)
01 赵雷 89.66667
07 郑竹 93.50000
5.5 查询不同老师所教不同课程平均分从高到低显示
select b.tno, a.cno, avg(a.score)
from sc as a inner join course as b on a.cno=b.cno
group by a.cno order by avg(a.score) desc;
-------------------------------------------------------
tno cno avg(a.score)
01 02 72.66667
03 03 68.50000
02 01 64.50000
5.6 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select a.sno, b.sname, avg(a.score)
from sc as a inner join student as b on a.sno=b.sno
group by a.sno having avg(a.score)>=60;
-------------------------------------------------------
sno sname avg(a.score)
01 赵雷 89.66667
02 钱电 70.00000
03 孙风 80.00000
05 周梅 81.50000
07 郑竹 93.50000
5.7 查询平均成绩及格的同学学号，姓名及其平均成绩
select a.sno, b.sname, avg(a.score)
from sc as a inner join student as b on a.sno=b.sno
group by a.sno having avg(a.score)>=60;
-------------------------------------------------------
sno sname avg(a.score)
01 赵雷 89.66667
02 钱电 70.00000
03 孙风 80.00000
05 周梅 81.50000
07 郑竹 93.50000
5.8 查询同名同性学生名单，并统计同名同性人数
思路：同时将姓名和性别2个字段作为分组依据并计数，超过1就说明有重复
-------------------------------------------------------
select a.*, b.student_number
from student as a inner join
(select sname, ssex, count(*) as student_number from student
group by sname, ssex having count(*)>1) as b
on a.sname=b.sname and a.ssex=b.ssex;
-------------------------------------------------------
Sno Sname Sage Ssex student_number
空 空 空 空 空 空
-------------------------------------------------------------------------------------------------------------
select sname,count(*)
from student
group by sname,ssex
having count(*)>1
-------------------------------------------------------
sname count(*)
空 空
select b.sno, b.sname, count(a.score), sum(a.score)
from sc as a right join student as b on a.sno=b.sno
group by b.sno;
-------------------------------------------------------
sno sname count(a.score) sum(a.score)
01 赵雷 3 269.0
02 钱电 3 210.0
03 孙风 3 240.0
04 李云 3 100.0
05 周梅 2 163.0
06 吴兰 2 65.0
07 郑竹 2 187.0
08 王菊 0
6.3 查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )
select a.*, b.cno, b.score from
(select * from sc where cno='01') as a
left join (select * from sc where cno='02') as b
on a.sno=b.sno;
-------------------------------------------------------
Sno Cno score Cno(1) score(1)
01 01 80.0 02 90.0
02 01 70.0 02 60.0
03 01 80.0 02 80.0
04 01 50.0 02 30.0
05 01 76.0 02 87.0
06 01 31.0
7 三表连接
7.1 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select sname, cname, score
from student, course, sc
where student.sno=sc.sno and course.cno=sc.cno
and score>70;
-------------------------------------------------------
sname cname score
赵雷 语文 80.0
赵雷 数学 90.0
赵雷 英语 99.0
钱电 英语 80.0
孙风 语文 80.0
孙风 数学 80.0
孙风 英语 80.0
周梅 语文 76.0
周梅 数学 87.0
郑竹 数学 89.0
郑竹 英语 98.0
-------------------------------------------------------------------------------------------------------------
select a.sname, c.cname, b.score
from student as a
inner join (select * from sc where score>70) as b on a.sno=b.sno
inner join course as c on b.cno=c.cno;
-------------------------------------------------------
sname cname score
赵雷 语文 80.0
赵雷 数学 90.0
赵雷 英语 99.0
钱电 英语 80.0
孙风 语文 80.0
孙风 数学 80.0
孙风 英语 80.0
周梅 语文 76.0
周梅 数学 87.0
郑竹 数学 89.0
郑竹 英语 98.0
7.2 查询” 01 “课程比” 02 “课程成绩高的学生的信息及课程分数
1.在sc表中选出临时表b（" 01 "课程），临时表c（" 02 "课程）;
2.ab表相交条件：学生号相同且a表成绩>b表成绩
3.再与student表相交
-------------------------------------------------------
select a.*, b.score as '01', c.score as '02'
from student as a
inner join (select * from sc where cno='01') as b on a.sno=b.sno
inner join (select * from sc where cno='02') as c on b.sno=c.sno and b.score>c.score;
-------------------------------------------------------
Sno Sname Sage Sex 01 02
02 钱电 1990-12-21 男 70.0 60.0
04 李云 1990-08-06 男 50.0 30.0
8 limit
8.1 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
1.表联结：可以用inner join；也可以用where+等号
2.成绩最高：成绩降序排列后limit 1；成绩不重复，也可以用ma
-------------------------------------------------------------------------------------------------------------
法1：where+等号+limit
select student.*, sc.score
from sc, course, teacher,student
where teacher.tname='张三' and course.tno=teacher.tno and course.cno=sc.cno and student.sno=sc.sno
order by sc.score desc limit 1;
-------------------------------------------------------------------------------------------------------------
法2：where+等号+max
select student.*, max(sc.score) as max_score
from sc, course, teacher,student
where teacher.tname='张三' and course.tno=teacher.tno and course.cno=sc.cno and student.sno=sc.sno;
-------------------------------------------------------------------------------------------------------------
法3：inner join+limit
select a.*, b.score
from sc as b inner join student as a on a.sno=b.sno
where b.cno=(select cno from course where tno=(select tno
from teacher where tname='张三'))
order by b.score desc limit 1;
-------------------------------------------------------------------------------------------------------------
法4：inner join+max
select a.*, max(b.score) as max_score
from sc as b inner join student as a on a.sno=b.sno
where b.cno=(select cno from course where tno=(select tno
from teacher where tname='张三'));
8.2 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
1.student表与sc表联结为临时表a；
2.用limit/max，求出张三老师所授课程的课程号与最高分为临时表b
3.求表a表b的交集，条件是课程号、成绩与b相等。
-------------------------------------------------------------------------------------------------------------
法1：limit
select a.*
from (select student.*, sc.cno, sc.score from student, sc where student.sno=sc.sno) as a
inner join
(select cno, score from sc where cno=(select cno from course, teacher where teacher.tname='张三' and course.tno=teacher.tno )order by score desc limit 1) as b
on a.cno=b.cno and a.score=b.score;
-------------------------------------------------------------------------------------------------------------
法2：max
select a.*
from (select student.*, sc.cno, sc.score from student, sc where student.sno=sc.sno) as a
inner join
(select cno, max(score) as score from sc
where cno= (select cno from teacher, course where teacher.tname='张三' and teacher.tno=course.tno)
group by cno) as b
on a.cno=b.cno and a.score=b.score;
9 窗口函数
9.1 按各科成绩进行排序，并显示排名，Score 重复时保留名次空缺
select *, rank() over (partition by cno order by score desc) as ranking
from sc;
9.2 按各科成绩进行排序，并显示排名，Score 重复时合并名次
select *, dense_rank() over (partition by cno order by score desc) as ranking
from sc;
9.3 查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select sno, sum(score), rank() over (order by sum(score) desc) as ranking
from sc group by sno;
9.4 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
select sno, sum(score), dense_rank() over (order by sum(score) desc) as ranking
from sc group by sno;
9.5 查询每门功成绩最好的前两名
select * from
(select *, rank() over(partition by cno order by score desc) as ranking from sc) as a
where ranking <=2;
9.6 查询各科成绩前三名的记录
select * from
(select *, dense_rank() over(partition by cno order by score desc) as ranking from sc) as a
where ranking <=3;
9.7 查询所有课程成绩第2名到第3名的学生信息及该课程成绩
select a.*, b.cno, b.score, b.ranking
from student a inner join
(select *, dense_rank() over (partition by cno order by score desc) ranking
from sc ) b
on a.sno=b.sno
where ranking in (2,3);
10 case
10.1 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select b.sno, a.avg_score, a.01, a.02, a.03 from
(select sno, avg(score) as avg_score,
max(case when cno='01' then score else null end) as '01',
max(case when cno='02' then score else null end) as '02',
max(case when cno='03' then score else null end) as '03'
from sc group by sno) as a
right join student as b on a.sno=b.sno;
-------------------------------------------------------
sno avg_score 01 02 03
01 89.66667 80.0 90.0 99.0
02 70.00000 70.0 60.0 80.0
03 80.00000 80.0 80.0 80.0
04 33.33333 50.0 30.0 20.0
05 81.50000 76.0 87.0
06 32.50000 31.0 34.0
07 93.50000 89.0 98.0
08
select a.cno, b.cname,
sum(case when a.score>=85 then 1 else 0 end) as '[100-85]',
sum(case when a.score>=70 and a.score<85 then 1 else 0 end) as '[85-70]',
sum(case when a.score>=60 and a.score<70 then 1 else 0 end) as '[70-60]',
sum(case when a.score<60 then 1 else 0 end) as '[<60]'
from sc as a inner join course as b on a.cno=b.cno
group by a.cno;
-------------------------------------------------------
cno cname [100-85] [85-70] [70-60] [<60]
01 语文 0 4 0 2
02 数学 3 1 1 1
03 英语 2 2 0 2
10.3 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID ，课程 name ，最高分，最低分，平均分，及格率，中等率，优良率，优秀率。及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select a.cno '课程ID', b.cname '课程name', max(a.score) '最高分', min(a.score) '最低分', avg(a.score) '平均分',
sum(case when a.score>=60 then 1 else 0 end)/count(a.score) '及格率',
sum(case when a.score>=70 and a.score<80 then 1 else 0 end)/count(a.score) '中等率',
sum(case when a.score>=80 and a.score<90 then 1 else 0 end)/count(a.score) '优良率',
sum(case when a.score>=90 then 1 else 0 end)/count(a.score) '优秀率'
from sc a inner join course b on a.cno=b.cno
group by a.cno;
-------------------------------------------------------
课程ID 课程name 最高分 最低分 平均分 及格率 中等率 优良率 优秀率
01 语文 80.0 31.0 64.50000 0.6667 0.3333 0.3333 0.0000
02 数学 90.0 30.0 72.66667 0.8333 0.0000 0.5000 0.1667
03 英语 99.0 20.0 68.50000 0.6667 0.0000 0.3333 0.3333
select a.cno, b.cname,
sum(case when a.score>=85 then 1 else 0 end) as '[100-85]人数',
sum(case when a.score>=70 and a.score<85 then 1 else 0 end) as '[85-70]人数',
sum(case when a.score>=60 and a.score<70 then 1 else 0 end) as '[70-60]人数',
sum(case when a.score<60 then 1 else 0 end) as '[60-0]人数',
sum(case when a.score>=85 then 1 else 0 end)/count(a.sno) as '[100-85]百分比',
sum(case when a.score>=70 and a.score<85 then 1 else 0 end)/count(a.sno) as '[85-70]百分比',
sum(case when a.score>=60 and a.score<70 then 1 else 0 end)/count(a.sno) as '[70-60]百分比',
sum(case when a.score<60 then 1 else 0 end)/count(a.sno) as '[60-0]百分比'
from sc as a inner join course as b on a.cno=b.cno
group by a.cno;
-------------------------------------------------------
cno cname [100-85]人数 [85-70]人数 [70-60]人数 [60-0]人数 [100-85]百分比 [85-70]百分比 [70-60]百分比 [60-0]百分比
01 语文 0 4 0 2 0.0000 0.6667 0.0000 0.3333
02 数学 3 1 1 1 0.5000 0.1667 0.1667 0.1667
03 英语 2 2 0 2 0.3333 0.3333 0.0000 0.3333
11 时间函数
11.1 查询各学生的年龄，只按年份来算
select sno, sname,
year(current_date())-year(sage) as age
from student;
-------------------------------------------------------
sno sname age
01 赵雷 33
02 钱电 33
03 孙风 33
04 李云 33
05 周梅 32
06 吴兰 31
07 郑竹 34
08 王菊 33
11.2 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
Select sno, sname,
timestampdiff(year, sage, current_date()) as age
from student;
-------------------------------------------------------
sno sname age
01 赵雷 33
02 钱电 32
03 孙风 33
04 李云 33
05 周梅 31
06 吴兰 31
07 郑竹 34
08 王菊 33
11.3 查询本月过生日的学生
select sno, sname, sage from student
where month(sage)=month(current_date());
-------------------------------------------------------
sno sname sage
空 空 空
11.4 查询下月过生日的学生
select sno, sname, sage from student
where month(sage)=month(current_date())+1;
-------------------------------------------------------
sno sname sage
空 空 空
11.5 查询本周过生日的学生
select sno, sname, sage from student
where week(replace(sage, year(sage), year(current_date())), 1) = week(current_date(),1);
-------------------------------------------------------
sno sname sage
空 空 空
11.6 查询下周过生日的学生
select sno, sname, sage from student
where week(replace(sage, year(sage), year(current_date())), 1) = week(current_date(),1)+1;
-------------------------------------------------------
sno sname sage
空 空 空