2019-7-28 [MySQL实战] 某年北京市高考成绩管理与分析系统数据库

1.数据库结构介绍与准备

1.1 数据库结构介绍

1. 数据库名称 : examdb
2. 数据库字符集 : utf8 排序方式 : utf8_general_ci
3. 表结构说明 :

a.地区表 : 存储北京是的所有区县信息

b.学校表 : 保存各区域的学校信息

c.学生表 存储学生信息

d.考试科目表 考试科目 一共有四个 1: 语文 2: 数学 3: 英语 4 : 综合

e.考试成绩表 : 保存考试成绩

4) 表关系说明 :

a. 一个地区有多个学校,所以地区表和学校表是一对多关系
b. 一个学校有多个学生,所以学校表和学生表是一对多关系
c. 一个学生有多个考试结果,所以学生表和考试成绩表是一对多关系
d. 一个考试科目下有多个考试成绩,所以考试科目表和考试成绩表示一对多关系

2.导入SQL脚本

1) 创建examdb数据库
2) 导入脚本即可

3.SQL语句实战练习

3.1 基础SQL,难度 ★

3.1.1 : 查询各表数据的基本情况

1. 查询地区表数据

select * from area;

2. 查询学校表数据

select * from school;

3. 查询学生表数据

select * from student;

4. 查询学科表数据

select * from `subject`;

5. 查询考试成绩表数据

select * from result;

3.1.2 : 查询各表数据的数据总量

1. 查询地区表数据

select count(*) from area;

2. 查询学校表数据

select count(*) from school;

3. 查询学生表数据

select count(*) from student;

4. 查询学科表数据

select count(*) from `subject`;

5. 查询考试成绩表数据

select count(*) from result;

3.2 新知识:分页查询数据,难度 ★★

在上面的案例中我们不难发现,有的表的数据量很小,但是有的表数据量很大,所以这样在查看表数据的时候就非常不好查看,比如学生表,
那么这个时候有没有什么技术能够帮助我们更好的查询数据呢?

回想现实生活中,比如一本书的内容非常多,但是我们看书却不觉得书很难看,是为什么呢?那是因为书把自己的内容进行了分页,然后把
每页的数据展示量限制在你可以接受的范围,所以你看书才不会被书的海量内容所困扰.
那么这种限制数据展示量并且分多页展示数据的方式我们叫做分页展示;

图书可以分页,数据库的表数据能不能分页展示呢? 当然可以

3.2.1 MySQL查询语句中使用LIMIT子句限制结果集

位置偏移量：指从结果集中第几条数据开始显示（第1条记录的位置偏移量是0，第2条记录的位置偏移量是1……)

具体用法 :
比如展示1-5条的学生数据

1. 查询前五条学生数据

select * from student LIMIT 0,5

2. 查询5-10条学生数据

select * from student LIMIT 5,5

3. 查询11-15条学生数据

select * from student LIMIT 10,5

4. 查询15-20条学生数据

select * from student LIMIT 15,5

总结规律 :

根据上面的查询规律,我们可以假设 如果我一共有 20 条数据的话 每页展示5条 那么上面的这个查询就是每页展示的数据量;
也就是说我们无意之间完成了一个分4页展示的案例;

所以总结规律我们可以得出一个算法:
如果想完成一个分页,我们必须知道以下几个参数

1. 总数据量 总条数 totalCount
2. 每页展示的数据量 页容量 pageSize
3. 一共有多少页 总页数 totalPage
4. 当前页数 页码 pageIndex
   根据以上参数我们能得出以下公式:

*1) totalPage = totalCount%pageSize==0? totalCount/pageSize: totalCount/pageSize+1;
2) limit的第一个参数 = (pageIndex-1)pageSize;
3) limit的第二个参数 = pageSize;

以后大家学习大数据课程的时候也会有很多这样根据数据总结规律得出公式的过程,大家需要注意对数据的总结与分析;

3.3 连接基础,难度 ★★★

1. 查询每个地区的学校信息

select a.a_id,s.sc_id,a.a_name,s.sc_name from school s inner join area a on s.a_id = a.a_id;

2. 分页查询所有学生的基本信息以及所属院校和所属地区 分页展示 每页10条 展示第1页

select s.*,sc.sc_name,a.* from student s 
inner join school sc on s.sc_id = sc.sc_id
inner join area a on sc.a_id = a.a_id
LIMIT 0,10;

3.4 连接进阶,难度 ★★★★

1. 查询语文考试的前十名成绩

select r.s_id,r.r_score from result r inner join `subject` s on r.su_id = s.su_id
where s.su_name = '语文' order by r.r_score desc LIMIT 0,10;

2. 查询总分前十名的成绩

select r.s_id,sum(r.r_score) zf from result r inner join `subject` s on r.su_id = s.su_id
group by r.s_id order by sum(r.r_score) desc limit 0,10;

3. 显示上面SQL的考生姓名

select r.s_id,stu.s_name,sum(r.r_score) zf from result r 
inner join `subject` s on r.su_id = s.su_id
inner join student stu on r.s_id = stu.s_id
group by r.s_id order by sum(r.r_score) desc limit 0,10;

4. 显示上面SQL的考生所在学校和所属地区

select r.s_id,stu.s_name,sc.sc_name,a.a_name,sum(r.r_score) from result r 
inner join `subject` s on r.su_id = s.su_id
inner join student stu on r.s_id = stu.s_id
inner join school sc on stu.sc_id = sc.sc_id
inner join area a on sc.a_id = a.a_id
group by r.s_id order by sum(r.r_score) desc limit 0,10;

3.5 项目实战基础,难度 ★★★★★

1. 统计一共有多少人参加高考,过一本线(大于等于550分),二本线(大于等于450分),专科线(大于等级250分的)分别有多少人;

青铜写法

-- 一共有多少人参加考试
select count(DISTINCT s_id) from result;

-- 过一本线的
select s_id,sum(r_score) from result 
group by s_id HAVING SUM(r_score) >= 550 ORDER BY SUM(r_score) desc;

-- 过二本线的
select s_id,sum(r_score) from result 
group by s_id HAVING SUM(r_score) < 550 and SUM(r_score) >= 450 
ORDER BY SUM(r_score) desc;

-- 过专科线的
select s_id,sum(r_score) from result 
group by s_id HAVING SUM(r_score) < 450 and SUM(r_score) >= 250 
ORDER BY SUM(r_score) desc;

王者写法

select 
count(sid) t,
sum(CASE WHEN totalScore >= 550 then 1 else 0 end) f,
sum(CASE WHEN totalScore < 550 and totalScore >= 450 then 1 else 0 end) s, 
sum(CASE WHEN totalScore < 450 and totalScore >= 250 then 1 else 0 end) o
from (
select s_id sid,SUM(r_score) totalScore from result GROUP BY s_id
) a

上面的写法中除了我们认识的一些SQL语法外还出现了case wen 这种SQL结构;

上面的SQL我们再分步骤看下实现思路
第一步 : 查询所有考生的考试成绩

select s_id sid,SUM(r_score) totalScore from result GROUP BY s_id

查询出来之后 将查询结果先封装成临时表 a 然后在表 a 的基础上进行查询

第二步 : 查询总数
count(sid) t,

第三步 : 查询各分数线录取情况
一本 :
sum(CASE WHEN totalScore >= 550 then 1 else 0 end) f

case when 负责筛选数据,比如5条数据
567
553
421
359
668

使用case when 筛选结果 大于等于 550 标记 1 小于 550 标记 0
567 1
553 1
421 0
359 0
668 1

统计标记求和 sum(1,1,0,0,1) = 3
从而算出 一本线 有3个人
此处大家要领悟这种简写SQL的技巧;

3.6 项目实战高级,难度 ★★★★★★

1. 每个区参数考试人数

select a.a_name areaname,count(*) scount from student s 
inner join (select DISTINCT s_id sid from result) r on s.s_id = r.sid
inner join school sc on s.sc_id = sc.sc_id
inner join area a on a.a_id = sc.a_id
group by a.a_name;

此处需要注意

select a.a_name areaname,count(*) scount from student s 
inner join (select DISTINCT s_id sid from result) r on s.s_id = r.sid

这个地方的细节运用,此处的SQL语句用意是统计每个区域参考人员的信息,如果这个学生没有高考成绩是不会被统计的

2. 每个区的录取情况

查询所有区域

select * from area;

每个区域加以统计

select
a.a_name areaname, 
count(sid) t,
sum(CASE WHEN totalScore >= 550 then 1 else 0 end) f,
sum(CASE WHEN totalScore < 550 and totalScore >= 450 then 1 else 0 end) s, 
sum(CASE WHEN totalScore < 450 and totalScore >= 250 then 1 else 0 end) o,
sum(CASE WHEN totalScore < 450 and totalScore < 250 then 1 else 0 end) x
from (
select r.*,a.a_name from student s 
inner join (select s_id sid,SUM(r_score) totalScore from result GROUP BY s_id) r on s.s_id = r.sid
inner join school sc on s.sc_id = sc.sc_id
inner join area a on a.a_id = sc.a_id
) a GROUP BY a.a_name