1、表数据：
–1.学生表
student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
–2.课程表
course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
–3.教师表
Teacher(t_id,t_name) –教师编号,教师姓名
–4.成绩表
Score(s_id,c_id,s_score) –学生编号,课程编号,分数
2、测试数据构建
--建表
--学生表
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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--课程表
CREATE TABLE `course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--教师表
CREATE TABLE `teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--成绩表
CREATE TABLE `score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--插入学生表测试数据
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);
3、练习题
1、查询课程编号为"01"的课程比"02"的课程成绩高的所有学生的学号(重点)
# 查询课程编号为"01"的课程比"02"的课程成绩高的所有学生的学号(重点)
select
s1.s_id as '学号'
from
(select * from score where c_id = 01) as s1
inner join
(select * from score where c_id = 02) as s2
on s1.s_id = s2.s_id
where s1.s_score > s2.s_score;
2、查询平均成绩大于60分的学生的学号、学生姓名和平均成绩
# 查询平均成绩大于60分的学生的学号、学生姓名和平均成绩
select
stu.s_id as 学号, stu.s_name as 学生姓名, avg(sco.s_score) as 平均成绩
from score as sco
inner join
student as stu on stu.s_id = sco.s_id
group by sco.s_id
having avg(sco.s_score) > 60;
3、查询所有同学的学号、姓名、选课总数、所有课程的总成绩
# 查询所有同学的学号、姓名、选课总数、所有课程的总成绩
select
stu.s_id as 学号,
stu.s_name as 姓名,
count(sco.c_id) as 选课总数,
sum(case when sco.s_score is null then 0 else sco.s_score end) as 总成绩
from student as stu
left join score as sco
on stu.s_id = sco.s_id
group by stu.s_id;
4、查询"李"姓老师的数量
select count(t_name) from teacher where t_name like '李%';
5、查询没学过"张三"老师授课的同学的信息
# 查询没学过"张三"老师授课的同学的信息
select *
from student
where s_id not in (
select s_id
from teacher as t
inner join course c on t.t_id = c.t_id
inner join score as s on c.c_id = s.c_id
where t.t_name = '张三'
);
6、查询学过"张三"老师授课的同学的信息
# 查询学过"张三"老师授课的同学的信息
select *
from student
where s_id in (
select s_id
from teacher as t
inner join course c on t.t_id = c.t_id
inner join score as s on c.c_id = s.c_id
where t.t_name = '张三'
);
7、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select * from student where s_id in (
select s_id from score where c_id = 02 and s_id in
(select s_id from score where c_id = 01)
)
8、查询课程编号为02的总成绩
# 查询课程编号为02的总成绩
select sum(s_score) from score where c_id = 02
9、查询所有课程成绩小于60分的学生的学号和姓名
# 查询所有课程成绩小于60分的学生的学号和姓名
select * from student where s_id in (
select s1.s_id from
(select s_id ,count(s_id) as coun from score where s_score < 60 group by s_id) as s1
inner join
(select s_id,count(s_id) as coun from score group by s_id) as s2
on s1.s_id = s2.s_id
where s1.coun = s2.coun
);
10、查询没有学全所有课程的同学的信息
# 查询没有学全所有课程的同学的信息
select * from student as stu left join score as sco
on stu.s_id = sco.s_id
group by stu.s_id having count(distinct sco.c_id) <
(select count(distinct c_id) from course);
11、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select * from student where s_id in (
select distinct s_id from score where c_id in (
select distinct c_id from score where s_id = 01
) and s_id != 01
);
12、查询和"01"号的同学学习的课程完全相同的其他同学的信息
# 查询和"01"号的同学学习的课程完全相同的其他同学的信息
select * from student
where s_id in (
select s_id from score where s_id != 01
group by s_id having count(distinct c_id) =
(select count(distinct c_id) from score where s_id = 01)
) and s_id not in (
select distinct s_id from score where c_id not in(
select c_id from score where s_id = 01
)
);
15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩（重点）
select s1.s_id,s_name,s2.avg_score from student as s1 inner join (
select s_id,avg(s_score) as avg_score from score where s_id in (
select s_id from score where s_score < 60 group by s_id having count(distinct c_id) >= 2
) group by s_id
) as s2
on s1.s_id = s2.s_id;
16、检索"01"课程分数小于60，按分数降序排列的学生信息
select s1.*,s2.s_score from student s1 inner join (
select s_id,s_score from score where s_score < 60 and c_id = 01
) as s2
on s1.s_id = s2.s_id order by s2.s_score desc;
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩（重点）
# 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select
s_id '学号' ,
max(case when c_id = 01 then s_score else null end) '语文' ,
max(case when c_id = 02 then s_score else null end) '数学' ,
max(case when c_id = 03 then s_score else null end) '英语' ,
avg(s_score) '平均成绩'
from score group by s_id order by avg(s_score) desc;
18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率（重点）
--及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select
s.c_id as '课程ID',
c_name as '课程名称',
max(s_score) as '最高分',
min(s_score) as '最低分',
avg(s_score) as '平均分',
sum(case when s_score >= 60 then 1 else 0 end) / count(s.c_id) as '及格率',
sum(case when s_score >= 70 and s_score < 80 then 1 else 0 end) / count(s.c_id) as '中等率',
sum(case when s_score >= 80 and s_score < 90 then 1 else 0 end) / count(s.c_id) as '优良率',
sum(case when s_score >= 90 then 1 else 0 end) / count(s.c_id) as '优秀率'
from score as s inner join course as c on s.c_id = c.c_id
group by s.c_id,c.c_name;
19、按各科成绩进行排序，并显示排名(重点row_number、rank、dense_rank)
select
s_id,
c_id,
s_score,
row_number() over (partition by c_id order by s_score desc)
from score;
20、查询学生的总成绩并进行排名（不重点）
select
s_id as '学号',
sum(s_score) as '总成绩'
from score
group by s_id order by sum(s_score) desc;
21、查询不同老师所教不同课程平均分从高到低显示(不重点)
select
t_name as '教师姓名',
c_name as '课程',
avg(s_score) as '平均分'
from score as s inner join course as c on s.c_id = c.c_id inner join teacher t on c.t_id = t.t_id
group by c.c_id,c_name order by avg(s_score) desc;
22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩(重点row_number、rank、dense_rank)
select * from (
select
s_id,
c_id,
row_number() over (partition by c_id order by s_score desc) as 'sort'
from score ) as s
where s.sort in (2,3);
23、使用分段[100-85),[85-70),[70-60),[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称(重点)
注：count（） else后为null 不能为0
select
c_id,
sum(case when s_score > 85 and s_score <= 100 then 1 else 0 end ) as '[100-85)',
count(case when s_score > 70 and s_score <= 85 then 1 else null end ) as '[85-70)',
sum(case when s_score > 60 and s_score <= 70 then 1 else 0 end) as '[70-60)',
count(case when s_score < 60 then 1 else null end ) as '[<60]'
from score group by c_id;
24、查询学生平均成绩及其名次（重点row_number、rank、dense_rank）
select
s_id,avg(s_score) ,
row_number() over (order by avg(s_score) desc)
from score group by s_id;
26、查询每门课程被选修的学生数
select
c_id as '课程ID',
count(distinct s_id) as '选修数'
from score group by c_id;
27、查询出只有两门课程的全部学生的学号和姓名
# 查询出只有两门课程的全部学生的学号和姓名(不重点)
select
s1.s_id,
s_name
from student as s1 inner join (
select s_id from score group by s_id having count(distinct c_id) = 2
) as s2 on s1.s_id = s2.s_id;
28、查询男生、女生人数
select
s_sex as '性别',
count(s_id) as '人数'
from student group by s_sex;
31、查询1990年出生的学生名单（重点）
select * from student where s_birth like '1990%';
# 或者
select * from student where year(s_birth) = '1990';
33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
select c_id, avg(s_score)
from score
group by c_id
order by avg(s_score), c_id desc;
35、查询所有学生的课程及分数情况（重点）
select
s1.s_id,
s_name,
max(case when c_name = '语文' then s_score else null end) as '语文',
max(case when c_name = '数学' then s_score else null end) as '数学',
max(case when c_name = '英语' then s_score else null end) as '英语'
from student as s1 left join (
select
s_id,
c_name,
s_score
from score as sco inner join course as cou on sco.c_id = cou.c_id
) as s2 on s1.s_id = s2.s_id group by s1.s_id, s_name;
40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩
select
stu.s_id,
stu.s_name,
c.c_name,
t.t_name,
s.s_score
from score as s
inner join course as c on s.c_id = c.c_id
inner join teacher as t on c.t_id = t.t_id
inner join student stu on s.s_id = stu.s_id
where t.t_name = '张三' order by s.s_score desc limit 1;
41.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 （重点）
select s_id from (
select s1.s_id,s1.s_score from score s1 inner join (
select s_id from score group by s_id having count(c_id) > 1
) as s2 on s1.s_id = s2.s_id group by s1.s_id,s1.s_score
) as s3
group by s_id having count(s3.s_id) = 1;
46、查询各学生的年龄
备注：floor()：向下取整
datediff()：计算两个时间所差的天数，返回的结果是第一个参数 减 第二个参数的差
curdate()：获取当前的年月日
select
*,
floor(datediff(curdate(), s_birth) / 365) as '年龄'
from student;
48、查询下周过生日的同学
备注：week()：获取当前是今年的几周，第一个参数年月日，第二个参数以星期几开始计算
substring()：截取字字符串
concat()：字符串拼接
思路：把学生的出生年份拼接成当前的年份，在进行计算，如果当前的周数+1等于学生的出生周数，说明下周要过生日
select * from student where
week(
(concat(substring(curdate(),1,4),
substring(s_birth,5))
),1)
= week(curdate(),1) + 1;
49、查询本月过生日的学生
select * from student where month(s_birth) = month(curdate());
50、查询下月过生日的学生
select * from student
where case when month(curdate()) = 12
then month(s_birth) = 01
else month(s_birth) = month(curdate()) + 1 end ;
网友评论