导入sql库
create database test1;
use test1;
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(s_id,s_name,s_birth,s_sex)
values('0001' , '猴子' , '1989-01-01' , '男');
insert into student(s_id,s_name,s_birth,s_sex)
values('0002' , '猴子' , '1990-12-21' , '女');
insert into student(s_id,s_name,s_birth,s_sex)
values('0003' , '马云' , '1991-12-21' , '男');
insert into student(s_id,s_name,s_birth,s_sex)
values('0004' , '王思聪' , '1990-05-20' , '男');
insert into score(s_id,c_id,s_score)
values('0001' , '0001' , 80);
insert into score(s_id,c_id,s_score)
values('0001' , '0002' , 90);
insert into score(s_id,c_id,s_score)
values('0001' , '0003' , 99);
insert into score(s_id,c_id,s_score)
values('0002' , '0002' , 60);
insert into score(s_id,c_id,s_score)
values('0002' , '0003' , 80);
insert into score(s_id,c_id,s_score)
values('0003' , '0001' , 80);
insert into score(s_id,c_id,s_score)
values('0003' , '0002' , 80);
insert into score(s_id,c_id,s_score)
values('0003' , '0003' , 80);
insert into course(c_id,c_name,t_id)
values('0001' , '语文' , '0002');
insert into course(c_id,c_name,t_id)
values('0002' , '数学' , '0001');
insert into course(c_id,c_name,t_id)
values('0003' , '英语' , '0003');
-- 教师表：添加数据
insert into teacher(t_id,t_name)
values('0001' , '孟扎扎');
insert into teacher(t_id,t_name)
values('0002' , '马化腾');
汇总分析
-- 1.查询课程编号为0002的总成绩
select sum(s_score) from score
where c_id = 0002;
-- 2.查询选了课程的学生人数
select count(distinct s_id)
from score s left join course c on s.c_id = c.c_id;
分组查询
-- 1.查询各科目成绩最高和最低的分数
select c.c_name, max(s_score), min(s_score)
from score s left join course c on s.c_id = c.c_id
group by c.c_name ;
-- 2. 查询每门课程被选修的学生数
select c.c_id, count(s.s_id)
from score s left join course c on s.c_id = c.c_id
group by c.c_id;
-- 3. 查询男生和女生人数
select s_sex, count(s_id)
from student
group by s_sex;
-- 4. 查询平均成绩大于60分的学生学号和平均成绩
# 对学生进行分组， 对成绩求平均值， 条件是平均成绩大于60
select s.s_id, avg(sc.s_score)
from student s left join score sc on s.s_id = sc.s_id
group by s.s_id
having avg(sc.s_score) > 60;
-- 5查询至少修了两门课程的同学
# 以同学进行分组， 以课程进行统计
select s_id, count(c_id)
from score
group by s_id
having count(c_id) >= 2;
-- 6查询同名同姓学生名单并统计同名人数
# 以姓名进行分组 count（）相同姓名 ， 条件是count(s_name) >1
select s_name , count(s_name)
from student
group by s_name
having count(s_name) > 1;
-- 7. 查询不及格的课程并按照课程号从大到小排列
# 查询课程 课程得分小于60 order by 课程号
select c_id
from score
where s_score <60
order by c_id desc;
-- 8 查询每门课的平均成绩，结果按平均成绩的升序排列， 平均成绩相同，按照课程号进行排列
select c_id, avg(s_score)
from score
group by c_id
order by avg(s_score) asc , c_id desc;
-- 10 统计每门课程的选修人数 ，超过2人的课程才进行统计，要求输出课程号和选修人数，
-- 查询结果按照人数降序排序， 若人数相同，按照课程号升序排序
# group by 课程 count（选修人数） 条件是 count(）>2, order by count(), c_id
select c_id, count(s_id)
from score
group by c_id
having count(s_id) >=2
order by count(s_id) desc, c_id asc;
-- 11 查询两门及以上不及格课程的同学学号及其平均成绩
# 有聚合必须用 grou by , group by 同学学号 ， avg(平均成绩), having count(slect c_id where s_score <60 )
select s_id, avg(s_score)
from score
where score.s_score <60
group by s_id
having count(c_id)>=2;
复杂查询
-- 1.查询所有课程成绩 小于60分学生的学号、姓名
# 两个表连接 student score , 查学号， 姓名 ， 限定条件成绩小于60 ,group by可以去重
select s.s_id, s.s_name
from student s left join score sc on s.s_id = sc.s_id
where sc.s_score <60
group by s.s_id;
# 也可以用子查询
select s_id, s_name
from student
where s_id IN
(select s_id from score where s_score<60);
-- 2. 查询没有学全全部课程的学号， 姓名???????????????????????? 重点
# 查询没有学完全部课程的， 那么学号就在考试号比总的课程号要少的这一组中
select s.s_id, s.s_name
from student s
where s.s_id in
(select score.s_id from score
group by score.s_id
having count(score.c_id) < (select count(course.c_id ) from course)
);
-- 3. 查询只选修了两门课程的全部学生学号和姓名
select s.s_id, s.s_name
from student s
where s.s_id IN
(
select sc.s_id from score sc
group by sc.s_id
having count(sc.c_id) = 2
);
-- 4. 1990年出生的学生名单 ?????????????
select s.s_id, s.s_name
from student s
where year(s_birth ) = 1990;
-- 5. 查询各科成绩前两名的记录 ???????????????????????????????????????????? 重点
# group by 科目， 排序后limit
select *
from
(select * , row_number() over (partition by score.c_id order by score.s_score desc) as ranking
from student join score on student.s_id = score.s_id
) as a
where ranking <=2;
多表查询
-- 1. 查询所有学生的学号、姓名、选课数、总成绩
# group by 学号 ， 对选课数进行count，对成绩进行求和
select s.s_id, s.s_name, count(sc.c_id), sum(sc.s_score)
from student s left join score sc on s.s_id = sc.s_id
group by s.s_id;
-- 2.查询平均成绩大于85分所有学生的学号，姓名，平均成绩
# group by 学号 select s_id, s_name avg(score) having avg
select s.s_id,s.s_name, avg(sc.s_score)
from student s left join score sc on s.s_id = sc.s_id
group by s.s_id
having avg(sc.s_score) > 60;
-- 3. 查询学生的选课情况：学号， 姓名， 课程号， 课程名称
# group by s_id, 三个表进行左连接
select s.s_id, s.s_name,c.c_id, c.c_name
from student s left join score sc on s.s_id = sc.s_id
left join course c on sc.c_id = c.c_id
group by s.s_id;
-- 4. 查询出每门课程的及格人数和不及格人数 ??????????????????????? 重点
# group by c.c_name, 利用case when 去统计及格人数和不及格人数
select c.c_name, sum(case when sc.s_score >=60 THEN 1 ELSE 0 END) as '及格人数',
sum(case when sc.s_score<60 THEN 1 ELSE 0 END) AS '不及格人数'
from score sc join course c on sc.c_id = c.c_id
group by c.c_name;
-- 5.使用分组来统计各科成绩, [100-85],[85-70], [70-60], [<60]来统计各科目成绩
# group by 科目， 然后就是分组统计了， 使用case when
select c.c_name, SUM(case when sc.s_score between 85 and 100 THEN 1 ELSE 0 END) '[85-100]',
SUM(CASE WHEN sc.s_score between 70 and 85 THEN 1 ELSE 0 END) '[70-85]',
sum(CASE WHEN sc.s_score between 60 and 70 THEN 1 ELSE 0 END) '[60-70]',
sum(case when sc.s_score < 60 then 1 else 0 END) '[0-60]'
FROM score sc join course c on sc.c_id = c.c_id
group by c.c_id;
