MySQL笔记参考https://www.jianshu.com/p/302226bea9a8
准备
-- 学生表
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`)
);
-- 课程表
CREATE TABLE `course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);
-- 教师表
CREATE TABLE `teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);
-- 成绩表
CREATE TABLE `score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);
-- 插入学生表测试数据
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' , '小徐' , '1995-06-05' , '男');
insert into student values('07' , '小困' , '1992-11-04' , '男');
insert into student values('08' , '小鑫' , '1993-09-15' , '男');
insert into student values('09' , '小初' , '1994-09-13' , '女');
-- 课程表测试数据
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);
习题
- 查询"01"课程比"02"课程成绩高的学生的名字及课程分数 （2结果集比较取其1）
select
(select s_name from student where s_id = a.s_id) as name,
a.s_score as score01,
b.s_score as score02
from
(select s_id, s_score from score where c_id = 01) a
join
(select s_id, s_score from score where c_id = 02) b
on a.s_id = b.s_id
where a.s_score > b.s_score
- 查询"01"课程比"02"课程成绩低的学生的信息及课程分数（2结果集比较取其1）
select
(select s_name from student where s_id = a.s_id) as name,
a.s_score as score01,
b.s_score as score02
from
(select s_id, s_score from score where c_id = 01) a
join
(select s_id, s_score from score where c_id = 02) b
on a.s_id = b.s_id
where a.s_score < b.s_score
- 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩（聚合运算）
select a.s_id, b.s_name, a.avg_score from
(select s_id, avg(s_score) as avg_score from score group by s_id having avg_score > 60) a
join
student b
on a.s_id = b.s_id
- 查询平均成绩小于等于60分的同学的学生编号和学生姓名和平均成绩（聚合运算）
select a.s_id, a.s_name, avg(b.s_score) as avg_score from student a left join score b on a.s_id = b.s_id
group by a.s_id, a.s_name having avg_score < 60
union
select s_id, s_name, 0 as avg_score from student where s_id not in
(select distinct s_id from score)
- 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩（聚合运算）
select a.s_id, a.s_name, count(b.c_id) as sum_course, sum(b.s_score) as sum_score
from student a join score b on a.s_id = b.s_id group by a.s_id, a.s_name
- 查询"大"姓老师的数量
select count(1) from teacher where t_name like '大%'
- 查询学过"大老师"授课的同学的信息
select s_id, s_name, s_birth, s_sex from student
where s_id in (
select s_id from score where c_id = (
select c_id from course where t_id = (
select t_id from teacher where t_name = '大老师'
)
)
)
- 查询没学过"大老师"授课的同学的信息
select s_id, s_name, s_birth, s_sex from student
where s_id not in (
select s_id from score where c_id = (
select c_id from course where t_id = (
select t_id from teacher where t_name = '大老师'
)
)
)
- 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息（交集）
select * from student where s_id in (
select a.s_id from
(select * from score where c_id = '01') a join
(select * from score where c_id = '02') b
on a.s_id = b.s_id
)
- 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select * from student where s_id in (
select s_id from score where c_id = '01'
) and s_id not in (
select s_id from score where c_id = '02'
)
- 查询没有学全所有课程的同学的信息
select * from student where s_id in (
select s_id from score group by s_id having count(c_id) < 3
)
- 查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select * from student where s_id in (
select s_id from score where c_id in (
select c_id from score where s_id = '01'
) group by s_id
)
- 查询和"01"号的同学学习的课程数目相同的其他同学的信息
select * from student where s_id in (
select s_id from score group by s_id
having count(1) = (select count(1) from score where s_id = '01')
)
- 查询没学过"大"老师讲授的任一门课程的学生姓名
select s_name from student where s_id in (
select s_id from score where c_id = (
select c_id from course where t_id = (
select t_id from teacher where t_name = '大老师'
)
)
)
- 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select a.s_id, b.s_name, a.avg_score from
(select s_id, round(avg(s_score), 2) as avg_score from score group by s_id having sum(s_score <= 60) >= 2) a
join
student b
on a.s_id = b.s_id
- 查询"01"课程分数小于60的同学，并按分数降序排列的学生信息
select a.*, b.s_score from student a join (
select s_id, s_score from score where c_id = '01' and s_score < 60
) b on a.s_id = b.s_id order by s_score desc
- 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩（多表jion）
select a.*, b.s_score as score1, c.s_score as score2, d.s_score as score3, e.avg_score from student a
join (select s_id, s_score from score where c_id = '01') b on a.s_id = b.s_id
join (select s_id, s_score from score where c_id = '02') c on a.s_id = c.s_id
join (select s_id, s_score from score where c_id = '03') d on a.s_id = d.s_id
join (select s_id, avg(s_score) as avg_score from score group by s_id) e on a.s_id = e.s_id
order by avg_score desc
- 查询各科成绩最高分、最低分和平均分。以如下形式显示：课程ID，课程Name，最高分，最低分，平均分，不及格率，及格率，中等率，优良率，优秀率。
其中：不及格为：<60，及格为：60-70，中等为：70-80，优良为：80-90，优秀为：>=90
select b.c_name, a.* from (
select c_id,
max(s_score) as max,
min(s_score) as min,
round(avg(s_score), 2) as avg,
round(sum(s_score < 60) / count(1), 2) as no_pass_rate,
round(sum(s_score >= 60 and s_score < 70) / count(1), 2) as pass_rate,
round(sum(s_score >= 70 and s_score < 80) / count(1), 2) as medium_rate,
round(sum(s_score >= 80 and s_score < 90) / count(1), 2) as good_rate,
round(sum(s_score >= 90) / count(1), 2) as excellent_rate
from score group by c_id
) a join course b on a.c_id = b.c_id
- 按01科成绩进行排序，并显示排名（使用变量 @i:=）
select a.s_id, a.s_name, b.s_score, b.rank from student a
join (
select s_id, s_score, @i:=@i+1 as rank from score
join (select @i:=0) x
where c_id = '01'
order by s_score desc
) b
on a.s_id = b.s_id
- 查询学生的总成绩并进行排名
select a.s_id, a.s_name, a.sum_score, @i:=@i+1 from (
select b.s_id, b.s_name, sum(c.s_score) as sum_score from student b
join score c on b.s_id = c.s_id group by b.s_id, b.s_name
order by sum_score desc
) a join (select @i:=0) x
- 查询所有老师所教课程的平均分从高到低显示
select c.t_id, c.t_name, round(avg(b.s_score), 2) as avg_score from course a
join score b on a.c_id = b.c_id
join teacher c on a.t_id = c.t_id
group by c.t_id, c.t_name
order by avg_score desc
- 查询01课程的成绩第2名到第3名的学生信息及该课程成绩（union all 默认不去重）
select * from (select a.s_id, a.s_name, a.s_birth, a.s_sex, b.c_id, b.s_score from student a join score b on a.s_id = b.s_id where b.c_id = '01' order by b.s_score desc limit 1, 2) x
union all
select * from (select a.s_id, a.s_name, a.s_birth, a.s_sex, b.c_id, b.s_score from student a join score b on a.s_id = b.s_id where b.c_id = '02' order by b.s_score desc limit 1, 2) y
union all
select * from (select a.s_id, a.s_name, a.s_birth, a.s_sex, b.c_id, b.s_score from student a join score b on a.s_id = b.s_id where b.c_id = '03' order by b.s_score desc limit 1, 2) y
- 统计各科成绩各分数段人数：课程编号,课程名称,[85-100],[70-85],[60-70],[0-60]及所占百分比
select a.c_id, a.c_name,
round(sum(b.s_score >= 85) / count(1), 2) as '[85-100]',
round(sum(b.s_score >= 70 and b.s_score < 85) / count(1), 2) as '[70-85]',
round(sum(b.s_score >= 60 and b.s_score < 70) / count(1), 2) as '[60-70]',
round(sum(b.s_score < 60) / count(1), 2) as '[0-60]'
from course a join score b on a.c_id = b.c_id group by a.c_id, a.c_name
24.查询学生平均成绩及其名次
select x.*, @i:=@i+1 as rank from (
select a.s_id, a.s_name, avg(b.s_score) as avg_score from student a
join score b on a.s_id = b.s_id group by a.s_id, a.s_name
order by avg_score desc
) x join (select @i:= 0) y
- 查询各科成绩前三名的记录
select * from (select * from score where c_id = '01' order by s_score desc limit 3) a
union
select * from (select * from score where c_id = '02' order by s_score desc limit 3) b
union
select * from (select * from score where c_id = '03' order by s_score desc limit 3) c
- 查询每门课程被选修的学生数
select c_id, count(1) from score group by c_id
- 查询出只有两门课程的全部学生的学号和姓名
select a.s_id, a.s_name from student a
join score b on a.s_id = b.s_id
group by a.s_id, a.s_name having count(b.c_id) = 2
- 查询男生、女生人数
select s_sex, count(s_sex) from student group by s_sex
- 查询名字中含有"小"字的学生信息
select * from student where s_name like '%小%'
- 查询1990年出生的学生名单
select * from student where s_birth like'1990-%'
- 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c_id, round(avg(s_score), 2) as avg_score from score group by c_id order by avg_score desc
- 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select a.s_id, a.s_name, round(avg(b.s_score), 2) as avg_score from student a
join score b on a.s_id = b.s_id
group by a.s_id, a.s_name
having avg_score > 85
- 查询课程名称为"数学"，且分数低于60的学生姓名和分数
select a.s_name, b.s_score from student a
join score b on a.s_id = b.s_id
where b.c_id = (select c_id from course where c_name = '数学')
and b.s_score <= 60
- 查询任何一门课程成绩在70分以上的学姓名、课程名称和分数
select b.s_name, c.c_name, a.s_score from score a
join student b on a.s_id = b.s_id
join course c on a.c_id = c.c_id
where s_score > 70
- 查询不及格的课程
select a.s_id, a.c_id, b.c_name, a.s_score from score a join course b on a.c_id = b.c_id where a.s_score < 60
- 查询课程编号为01且课程成绩在80分以上的学生的学号和姓名
select * from student a join score b on a.s_id = b.s_id where b.c_id = '01' and b.s_score >= 80
- 查询每门课程的学生人数
select c_name, count(1) as number from course a join score b on a.c_id = b.c_id group by c_name
- 查询选修大老师所授课程的学生中，成绩最高的学生信息及其成绩
select a.*, b.s_score from student a join score b on a.s_id = b.s_id
where b.c_id = (select c_id from course where t_id = (
select t_id from teacher where t_name = '大老师'
))
- 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩（N-N比较，考虑笛卡尔积）
select distinct a.s_id, b.c_id, b.s_score from score a join score b where a.c_id != b.c_id and a.s_score = b.s_score
- 统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select c_id, count(1) as total from score group by c_id having total > 5
- 检索至少选修两门课程的学生学号
select s_id from score group by s_id having count(1) > 2
- 查询选修了全部课程的学生信息
select * from student where s_id in (
select s_id from score group by s_id having count(1) = 3
)
- 查询各学生的年龄
select s_birth,
date_format(now(), '%Y') - date_format(s_birth, '%Y')
- (case when date_format(now(), '%m%d') > date_format(s_birth, '%m%d') then 0 else 1 end)
as age
from student
- 查询本周过生日的学生
select * from student where week(s_birth) = week(date_format(now(), '%Y%m%d'))
- 查询下周过生日的学生
select * from student where week(s_birth) = week(date_format(now(), '%Y%m%d')) + 1
- 查询本月过生日的学生
select * from student where month(s_birth) = month(date_format(now(), '%Y%m%d'))
- 查询下月过生日的学生
select * from student where month(s_birth) = month(date_format(now(), '%Y%m%d')) + 1
网友评论