一.数据准备
---------------------表名和字段-----------------
--学生表
--Student(s_id,s_name,s_birth,s_sex) 学生编号,学生姓名, 出生年月,学生性别
--课程表
--Course(c_id,c_name,t_id) 课程编号, 课程名称, 教师编号
--教师表
--Teacher(t_id,t_name) 教师编号,教师姓名
--成绩表
--Score(s_id,c_id,s_score) 学生编号,课程编号,分数
create database if not exists exercise;
use exercise;
---------------------建表------------------
--学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`s_id` varchar(20),
`s_name` varchar(20),
`s_birth` varchar(20),
`s_sex` varchar(10)
)
row format delimited
fields terminated by ','
lines terminated by '\n';
load data local inpath './student.csv' into table student;
--课程表
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`c_id` varchar(20),
`c_name` varchar(20),
`t_id` varchar(20)
)
row format delimited
fields terminated by ','
lines terminated by '\n';
load data local inpath 'course.csv' into table course;
--教师表
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher`(
`t_id` varchar(20),
`t_name` varchar(20)
)
row format delimited
fields terminated by ','
lines terminated by '\n';
load data local inpath 'teacher.csv' into table teacher;
--成绩表
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score`(
`s_id` varchar(20),
`c_id` varchar(20),
`s_score` int
)
row format delimited
fields terminated by ','
lines terminated by '\n';
load data local inpath 'score.csv' into table score;
01,赵雷,1990-01-01,男
02,钱电,1990-12-21,男
03,孙风,1990-05-20,男
04,李云,1990-08-06,男
05,周梅,1991-12-01,女
06,吴兰,1992-03-01,女
07,郑竹,1989-07-01,女
08,王菊,1990-01-20,女
09,张飞,1990-9-25,男
10,刘备,1990-01-25,男
11,关羽,1990-01-25,男
01,01,80
01,02,90
01,03,99
02,01,70
02,02,60
02,03,80
03,01,80
03,02,80
03,03,80
04,01,50
04,02,30
04,03,20
05,01,76
05,02,87
06,01,31
06,03,34
07,02,89
07,03,98
09,01,85
09,02,80
09,04,99
10,01,80
10,02,56
10,03,30
10,04,90
11,04,90
01,语文,02
02,数学,01
03,英语,03
04,物理,04
01,张三
02,李四
03,王五
04,赵六
二.练习题
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
写法1：
SELECT *
FROM (
SELECT A.s_id, A.s_name, A.s_birth, A.s_sex
, MAX(if(c_id = '01', s_score, NULL)) AS chinese
, MAX(if(c_id = '02', s_score, NULL)) AS math
, MAX(if(c_id = '03', s_score, NULL)) AS english
FROM student A
JOIN score B ON A.s_id = B.s_id
GROUP BY A.s_id, A.s_name, A.s_birth, A.s_sex
) C
WHERE chinese > math;
写法2：
SELECT A.*, B.s_score, C.s_score
FROM student A
JOIN score B
ON A.s_id = B.s_id
AND B.c_id = '01'
JOIN score C
ON A.s_id = C.s_id
AND C.c_id = '02'
WHERE B.s_score > C.s_score;
2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
SELECT *
FROM (
SELECT A.s_id, A.s_name, A.s_birth, A.s_sex, MAX(if(c_id = '01', s_score, NULL)) AS chinese, MAX(if(c_id = '02', s_score, NULL)) AS math, MAX(if(c_id = '03', s_score, NULL)) AS english
FROM student A JOIN score B ON A.s_id = B.s_id GROUP BY A.s_id, A.s_name, A.s_birth, A.s_sex
) C
WHERE chinese < math;
3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select A.s_id,A.s_name,avg(nvl(B.s_score,0))
from student A join score B on A.s_id = B.s_id
group by A.s_id,A.s_name
having avg(nvl(B.s_score,0))>=60;
4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)
select A.s_id,A.s_name,avg(nvl(B.s_score,0))
from student A left join score B on A.s_id = B.s_id
group by A.s_id,A.s_name
having avg(nvl(B.s_score,0))<60;
注意：左外连接显示左表的所有信息
5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select A.s_id,A.s_name,count(c_id),sum(s_score)
from student A left join score B on A.s_id = B.s_id
group by A.s_id,A.s_name;
注意：王菊这个人没有考试，所以在左外连接时，count(1)不合适，会统计出一条记录，如果是count(c_id)，统计的就是0条。
6、查询"李"姓老师的数量
select count(1)
from teacher
where t_name like '李%';
7、查询学过"张三"老师授课的同学的信息
select A.s_id,A.s_name,A.s_birth,A.s_sex
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 C.t_id = D.t_id
where t_name = '张三';
8、查询没学过"张三"老师授课的同学的信息
思路：排除学过张三课程的学生，剩下的就是没学过的
select A.s_id,A.s_name,A.s_birth,A.s_sex
from student A
where A.s_id not in
(select B.s_id
from score B join course C on B.c_id = C.c_id
join teacher D on C.t_id = D.t_id
where t_name = '张三');
写法2： 效率高一些
not in 换成not exists
select A.s_id,A.s_name,A.s_birth,A.s_sex
from student A
where not exists
(select B.s_id
from score B join course C on B.c_id = C.c_id
join teacher D on C.t_id = D.t_id
where t_name = '张三' and A.s_id = B.s_id)
9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select A.s_id,A.s_name,A.s_birth,A.s_sex
from student A join score B on A.s_id = B.s_id
join score C on A.s_id = C.s_id
where B.c_id = '01' and C.c_id = '02';
10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select A.s_id,A.s_name,A.s_birth,A.s_sex
from student A join score B on A.s_id = B.s_id
where B.c_id = '01' and A.s_id not in (
select s_id from score where c_id = '02');
写法2：
换not exists
select A.s_id,A.s_name,A.s_birth,A.s_sex
from student A join score B on A.s_id = B.s_id
where B.c_id = '01' and not exists (
select s_id from score where c_id = '02' and s_id=B.s_id);
11、查询没有学全所有课程的同学的信息
写法1： 8个job
select A.s_id,A.s_name,A.s_birth,A.s_sex
from student A join score B on A.s_id = B.s_id
group by A.s_id,A.s_name,A.s_birth,A.s_sex
having count(1) not in (select count(1) from course);
写法2：6个job
select A.s_id,A.s_name,A.s_birth,A.s_sex
from student A join score B on A.s_id = B.s_id
join (select count(1) num from course) C
group by A.s_id,A.s_name,A.s_birth,A.s_sex,C.num
having count(1)<C.num;
写法3： 5个job
select s.*,tmp1.*,tmp2.*
from student s join (select count(c_id) num1 from course) tmp1
left join (select s_id,count(c_id) num2 from score group by s_id) tmp2 on s.s_id=tmp2.s_id and tmp1.num1=tmp2.num2
where tmp2.num2 !=4;
12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select distinct A.s_id,A.s_name,A.s_birth,A.s_sex
from student A join score B on A.s_id = B.s_id
where B.c_id in (select c_id from score where s_id = '01') and A.s_id<>'01';
换exists
select distinct A.s_id,A.s_name,A.s_birth,A.s_sex
from student A join score B on A.s_id = B.s_id
where exists (select c_id from score where s_id = '01' and c_id = B.c_id) and A.s_id<>'01';
13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
01,02,03
1: 先找到学了 01这个人没学过的课程的人的信息
not in (01这个学生的所有学过的c_id)
2: 再排除上述那些人，剩下的就是和01这个要么完全相同，要么子集。
3: 再判断学习的课程数量相同，就是结果
select B.s_id,B.s_name,B.s_birth,B.s_sex
from student B join score C on B.s_id = C.s_id
join (select count(1) num from score where s_id = '01') D
where not exists
(select A.s_id
from score A
where not exists (select t.c_id from score t where t.s_id = '01' and A.c_id =t.c_id) and B.s_id = A.s_id)
and B.s_id <>'01'
group by B.s_id,B.s_name,B.s_birth,B.s_sex
having count(1) = D.num;
换思路： 搜集函数的应用
select *
from student C join
(select A.s_id,collect_list(A.c_id) arr from score A group by A.s_id) B
on C.s_id = B.s_id
where B.arr in (select collect_list(c_id) num from score where s_id = '01')
and C.s_id <>'01';
select *
from student C join
(select A.s_id,collect_list(A.c_id) arr from score A group by A.s_id) B
on C.s_id = B.s_id
where exists (select 1 from score where s_id = '01' and B.arr = collect_list(c_id))
and C.s_id <>'01';
14、查询没学过"张三"老师讲授的任一门课程的学生姓名
思路：排除学过张三课程的学生，剩下的就是没学过的
select A.s_name
from student A
where A.s_id not in
(select B.s_id
from score B join course C on B.c_id = C.c_id
join teacher D on C.t_id = D.t_id
where t_name = '张三');
写法2： 效率高一些
not in 换成not exists
select A.s_name
from student A
where not exists
(select B.s_id
from score B join course C on B.c_id = C.c_id
join teacher D on C.t_id = D.t_id
where t_name = '张三' and A.s_id = B.s_id)
15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
写法1： 只计算不及格的平均成绩
select A.s_id,A.s_name,avg(nvl(B.s_score,0))
from student A join score B on A.s_id = B.s_id
where B.s_score<60
group by A.s_id,A.s_name
having count(1)>=2;
写法2： 某一个人 及格了1科，不及格的2科，算平均成绩应该除以3的写法。
select A.s_id,A.s_name,C.avg_sal
from student A join score B on A.s_id = B.s_id
join (select s_id,avg(nvl(s_score,0)) avg_sal from score group by s_id) C
on A.s_id = C.s_id
where B.s_score<60
group by A.s_id,A.s_name,C.avg_sal
having count(1)>=2;
16、检索"01"课程分数小于60，按分数降序排列的学生信息
select A.s_id,A.s_name,A.s_birth,A.s_sex,B.s_score
from student A join score B on A.s_id = B.s_id
where B.c_id = '01' and B.s_score <60
order by B.s_score desc;
17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
写法1： 不使用开窗函数
select A.s_id,A.c_id,A.s_score,B.avg_score
from score A join (select s_id,avg(nvl(s_score,0)) avg_score from score group by s_id) B
on A.s_id = B.s_id
order by B.avg_score desc;
写法2： 使用开窗函数
select s_id,c_id,s_score,avg(nvl(s_score,0)) over(distribute by s_id) avg_score from score order by avg_score desc;
18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
select A.c_id,B.c_name,max(A.s_score),min(A.s_score),avg(nvl(A.s_score,0)),
round(((count(if(s_score>=60,1,null)))/count(1)),2) a,
round(((count(if(s_score>=70 and s_score<80,1,null)))/count(1)),2) b,
round(((count(if(s_score>=80 and s_score<90,1,null)))/count(1)),2) c,
round(((count(if(s_score>=90,1,null)))/count(1)),2) d
from score A join course B on A.c_id = B.c_id
group by A.c_id,B.c_name;
