------------------------------老样子,删除答案自己先做--------------------------------------
create database db_2;
use db_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`)
);-- 课程表
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', '吴兰', '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);
-- 1 查询" 01 "课程比" 02"课程成绩高的学生的信息及课程分数
-- 自查询
select *
from Score a
join Score b on a.s_id = b.s_id
join student c on a.s_id = c.s_id
where a.c_id = '01'
and b.c_id = '02'
and a.s_score > b.s_score;
-- 2 查询同时存在" 01 "课程和" 02 "课程的学生信息
select *
from score a
join student b on a.s_id = b.s_id
where c_id in ('01', '02');
-- 3 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select *
from score
where s_id not in (select s_id from score where c_id = '02')
and c_id = '01';
-- 4 查询不存在" 01 "课程但存在" 02 "课程的情况
select *
from score
where Score.s_id not in (select s_id from score where score.c_id = '01')
and score.c_id = '02';
-- 5. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select a.s_id, s_name, avg(s_score)
from score a
join student b on a.s_id = b.s_id
group by a.s_id
having avg(s_score) >= 60;
-- 6. 查询在 Score 表存在成绩的学生信息
select *
from student a
left join score b on a.s_id = b.s_id
where s_score is not null;
-- 7 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select a.s_id, s_name, count(c_id), sum(s_score)
from score a
right join student b on a.s_id = b.s_id
group by s_id, s_name
-- 8 查没有成绩的学生信息
select *
from student a
left join Score S on a.s_id = S.s_id
where s_score is null;
-- 9. 查询「李」姓学生的数量
select *
from student
where s_name like '李%';
-- 10. 查询学过「张三」老师授课的同学的信息
select a.*
from student a
join Score S on a.s_id = S.s_id
join Course C on S.c_id = C.c_id
join teacher t on C.t_id = t.t_id
where t_name = '张三';
-- 11. 查询没有学全所有课程的同学的信息(没学过的也算)
# with t1 as
# (select a.s_id,count(c_id) cnt
# from student a
# left join Score S on a.s_id = S.s_id
# # join course c on S.c_id = c.c_id
# group by a.s_id
# having cnt != (select count(c_id) from course))
# select *
# from student b
# where s_id is exists(select * from t1 join b t1.s_id = b.s_id )
SELECT a.*
FROM Student a
LEFT JOIN Score b ON a.s_id = b.s_id
LEFT JOIN Course c ON b.c_id = c.c_id
GROUP BY a.s_id
HAVING COUNT(*) < (select count(c_id) from course);
-- 12 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
# with t1 as ( select c_id from score where s_id = '01' )
select distinct a.*
from student a
left join Score S on a.s_id = S.s_id
where c_id in (select c_id from score where s_id = '01');
-- 13 查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
select a.*
from student a
join Score S on a.s_id = S.s_id
group by a.s_id
having count(c_id) = (select count(c_id) from score where s_id = '01');
select distinct a.*
from student a
left join Score S on a.s_id = S.s_id
where c_id in (select c_id from score where s_id = '01')
group by a.s_id
having count(c_id) = (select count(c_id) from score where s_id = '01');
-- 14 查询没学过"张三"老师讲授的任一门课程的学生姓名
select s_name
from student a
join Score S on a.s_id = S.s_id
join Course C on S.c_id = C.c_id
join Teacher T on C.t_id = T.t_id
where C.c_id not in (select c_id
from course a
join Teacher T2 on a.t_id = T2.t_id
where T2.t_name = '张三');
SELECT *
FROM Student
WHERE s_id NOT IN (
SELECT b.s_id
FROM Score a
JOIN Student b ON a.s_id = b.s_id
WHERE c_id = (
SELECT c_id
FROM Teacher a
JOIN Course b ON a.t_id = b.t_id
WHERE t_name = '张三'
));
-- 15 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
select b.*
from score a
join student b on a.s_id = b.s_id
where s_score < 60
group by a.s_id
having count(s_score) >= 2;
-- 16 检索" 01 "课程分数小于 60,按分数降序排列的学生信息
select student.*
from student
join Score S on Student.s_id = S.s_id
where c_id = '01'
and s_score < 60
order by s_score desc;
-- 17 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT a.*,
GROUP_CONCAT(s_score),
AVG(s_score)
FROM Student a
left JOIN Score b ON a.s_id = b.s_id
GROUP BY a.s_id
ORDER BY AVG(s_score) DESC;
-- 18.查询各科成绩最高分、最低分和平均分:
-- 以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
-- 及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
SELECT a.c_id,
c_name,
MAX(s_score) '最高分',
MIN(s_score) '最低分',
AVG(s_score) '平均分',
sum(case when s_score >= 60 then 1 else 0 end) / COUNT(s_id) '及格率',
sum(case when s_score >= 70 and s_score < 80 then 1 else 0 end) / COUNT(s_id) '中等率',
sum(case when s_score >= 80 and s_score < 90 then 1 else 0 end) / COUNT(s_id) '优良率',
sum(case when s_score >= 90 then 1 else 0 end) / COUNT(s_id) '优秀率'
FROM Score a
join Course b on a.c_id = b.c_id
group by c_id;
-- 19 按各科成绩进行排序,并各科成绩从高到底显示排名, Score 重复时保留名次空缺 RANK()
select *,
rank() over (partition by s_id order by s_score desc)
from score;
-- 20各科成绩进行排序,并显示排名, Score 重复时合并名次
select *,
rank() over (partition by s_id order by s_score)
from score;
-- 21查询学生的总成绩,总成绩并进行排名,总分重复时保留名次空缺
SELECT s_id,
sum_score,
rank() over (ORDER BY sum_score DESC) AS '排名'
FROM (
SELECT s_id,
SUM(s_score) AS sum_score
FROM Score
GROUP BY s_id) a;
-- 22统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
-- 同18题
select a.c_id,
c_name,
sum(case when s_score <= 100 and s_score > 85 then 1 else 0 end) as '(100-85)',
sum(case when s_score <= 85 and s_score > 70 then 1 else 0 end) as '(85-70)',
sum(case when s_score <= 70 and s_score > 60 then 1 else 0 end) as '(70-60)',
sum(case when s_score <= 60 then 1 else 0 end) as '(60-0)',
concat(round((sum(case when s_score <= 100 and s_score > 85 then 1 else 0 end) / count(s_id)), 2) * 100,
'%') as '(100-85)所占比分比',
concat(round((sum(case when s_score <= 85 and s_score > 70 then 1 else 0 end) / count(s_id)), 2) * 100,
'%') as '(85-70)所占比分比',
concat(round((sum(case when s_score <= 70 and s_score > 60 then 1 else 0 end) / count(s_id)), 2) * 100,
'%') as '(70-60)所占比分比',
concat(round((sum(case when s_score <= 60 then 1 else 0 end) / count(s_id)), 2) * 100,
'%') as '(60-0)所占比分比'
from score a
join course c on a.c_id = c.c_id
group by a.c_id, c_name
-- 易忘记关键词:
-- round(数字,保留几位小数)
-- concat(内容,需要拼接的符号)
-- 23查询各科成绩前三名的记录
select a.*
from Score a
where (select count(1) from Score b where b.c_id = a.c_id and b.s_score > a.s_score) < 3
order by c_id desc, s_score desc;
# order by s_score desc
# limit 3
-- 24查询每门课程被选修的学生数
select a.c_id, count(1) as '各科总人数'
from score a
join course c on a.c_id = c.c_id
join student s on a.s_id = s.s_id
group by a.c_id;
-- 25 查询出只选修两门课程的学生学号和姓名
select a.s_id, s_name
from score a
join student s on a.s_id = s.s_id
group by s_id, s_name
having count(a.c_id) = 2;
-- 26 查询男生、女生人数
select s_sex, count(1) as '人数'
from student
group by s_sex;
-- 27 查询名字中含有「风」字的学生信息
select *
from student
where s_name like '%风%';
-- 28 查询同名同性学生名单,并统计同名人数
select a.s_name,
a.s_sex,
count(*)
from Student a
join Student b on a.s_name = b.s_name and a.s_sex = b.s_sex and a.s_id != b.s_id
group by a.s_sex, a.s_name;
-- 29查询 1990 年出生的学生名单
select *
from student
where year(s_birth) = '1990';
-- 30查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
select avg(s_score)
from score
group by c_id
order by avg(s_score) desc, c_id asc;
-- 31查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select s.s_id, s_name, avg(s_score) as ag_s
from score a
join student s on a.s_id = s.s_id
group by s_id, s_name
having ag_s >= 85;
-- 32查询课程名称为「数学」,且分数低于 60 的学生姓名和分数
select s_name, s_score
from score a
join student s on a.s_id = s.s_id
join course c on a.c_id = c.c_id
where c.c_name = '数学'
and s_score < 60;
-- 33查询所有学生的课程名称及分数情况(存在学生没成绩,没选课的情况)
select s_name, c_name, s_score
from student a
left join score s on a.s_id = s.s_id
left join Course C on s.c_id = C.c_id;
-- 34查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
/*
思路查询有70分以下的人的s_id
not in
*/
select s_name, c_name, s_score
from score a
join course c on a.c_id = c.c_id
join student s on a.s_id = s.s_id
where a.s_id not in (select s_id
from score
where s_score <= 70);
#错
# select s_name, c_name, s_score
# from Student a
# join (SELECT * from Score where s_score > 70) b on a.s_id = b.s_id
# join Course c on b.c_id = c.c_id
-- 35 查询不及格的课程
select distinct c_name
from score
join Course C on Score.c_id = C.c_id
where s_score < 60;
-- 36 查询课程编号为 01 且课程成绩在 60 分以上的学生的学号和姓名
select a.s_id, s_name
from student a
join Score S on a.s_id = S.s_id
where c_id = '01'
and s_score > 60;
-- 37 求每门课程的学生人数
select c_id, count(1)
from score
group by c_id;
-- 38成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩 ROW_NUMBER()
select S.*, s_score
from score
join Course C on Score.c_id = C.c_id
join Teacher T on C.t_id = T.t_id
join Student S on Score.s_id = S.s_id
where t_name = '张三'
order by s_score desc
limit 1;
select s.*,
row_number() over (order by s_score desc ) as rn
from score a
join course c on a.c_id = c.c_id
join student s on a.s_id = s.s_id
join teacher t on c.t_id = t.t_id
where t_name = '张三'
limit 1;
-- 39成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
select s.*,
rank() over (order by s_score desc ) as rn
from score a
join course c on a.c_id = c.c_id
join student s on a.s_id = s.s_id
join teacher t on c.t_id = t.t_id
where t_name = '张三'
limit 1;
select *
from (
SELECT a.s_id,
s_name,
s_score,
RANK() over (order by s_score desc) as ranking
FROM Student a
join Score b on a.s_id = b.s_id
join Course c on b.c_id = c.c_id
join Teacher d on d.t_id = d.t_id
where t_name = '张三'
) as st
where ranking = 1;
-- 40查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select a.s_id, a.c_id, a.s_score
from score a
join score s on a.s_id = s.s_id and a.c_id != s.c_id and a.s_score = s.s_score;
-- 41查询每门课程成绩最好的前两名同学 RANK
select *
from (select s.*, rank() over (partition by c_id order by s_score desc ) as rn
from score a
join student s on a.s_id = s.s_id
) as t1
where rn in (1, 2);
-- 42统计每门课程的学生选修人数(超过 5 人的课程才统计)。
select C.c_id, c_name, count(1) as cnt
from score
join Course C on Score.c_id = C.c_id
group by C.c_id, c_name
having cnt >= 5;
-- 43检索至少选修两门课程的学生学号
select s_id
from score
group by s_id
having count(c_id) >= 2;
-- 44查询选修了全部课程的学生信息
select s.*
from score
join Course C on Score.c_id = C.c_id
join Student S on Score.s_id = S.s_id
group by S.s_id
having count(C.c_id) = (select count(1) from course);
-- 45 查询各学生的年龄,只按年份来算
select *, TIMESTAMPDIFF(YEAR, s_birth, now()) AS age
from student;
-- 46 按照出生日期来算,当前月日 < 出生年月的月日则年龄减一;
select *,
year(now()) - YEAR(s_birth) age
from Student;
-- 47 查询本周过生日的学生
select *
from student
where week(s_birth) = week(now());
-- 48 查询下周过生日的学生 -
SELECT * ,
WEEK(s_birth),
WEEK(NOW())
from Student
where WEEK(s_birth) =( WEEK(NOW()) + 1)
-- 49 查询本月过生日的学生
SELECT *,
MONTH(s_birth),
MONTH(NOW())
FROM Student
where MONTH(s_birth) = MONTH(NOW());
-- 50查询下月过生日的学生
SELECT *,
MONTH(s_birth),
MONTH(NOW())
FROM Student
where MONTH(s_birth) = (MONTH(NOW())+1)