MySql实战 --- 2018年北京市高考成绩管理与分析系统数据
2.1 数据库结构介绍与准备
2.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.2 导入SQL脚本
1) 创建examdb数据库
2) 导入脚本即可
2.3 SQL语句实战练习
2.3.1 基础SQL,难度 ★
2.3.1.1 : 查询各表数据的基本情况
-
查询地区表数据
-
查询学校表数据
-
查询学生表数据
-
查询学科表数据
- 查询考试成绩表数据
select * from result;
2.3.1.2 : 查询各表数据的数据总量
-
查询地区表数据
-
查询学校表数据
-
查询学生表数据
-
查询学科表数据
- 查询考试成绩表数据
2.3.2 新知识:分页查询数据,难度 ★★
在上面的案例中我们不难发现,有的表的数据量很小,但是有的表数据量很大,所以这样在查看 表数据的时候就非常不好查看,比如学生表,
那么这个时候有没有什么技术能够帮助我们更好的查询数据呢?
回想现实生活中,比如一本书的内容非常多,但是我们看书却不觉得书很难看,是为什么呢?那 是因为书把自己的内容进行了分页,然后把
每页的数据展示量限制在你可以接受的范围,所以你看书才不会被书的海量内容所困扰.
那么这种限制数据展示量并且分多页展示数据的方式我们叫做分页展示;
图书可以分页,数据库的表数据能不能分页展示呢? 当然可以
MySQL查询语句中使用LIMIT子句限制结果集
位置偏移量：指从结果集中第几条数据开始显示（第1条记录的位置偏移量是0，第2条记录 的位置偏移量是1……)
具体用法 :
比如展示1-5条的学生数据
-
查询前五条学生数据
-
查询5-10条学生数据
-
查询11-15条学生数据
- 查询15-20条学生数据
总结规律 :
根据上面的查询规律,我们可以假设 如果我一共有 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;
以后大家学习大数据课程的时候也会有很多这样根据数据总结规律得出公式的过程,大家需 要注意对数据的总结与分析;
2.3.3 连接基础,难度 ★★★
- 查询每个地区的学校
- 分页查询所有学生的基本以及所属院校和所属地区 分页展示 每页10条 展示第1页
2.3.4 连接进阶,难度 ★★★★
-
查询语文考试的前十名成绩
-
查询总分前十名的成绩
-
显示上面SQL的考生姓名
- 显示上面SQL的考生所在学校和所属地区
2.3.5 项目实战基础,难度 ★★★★★
- 统计一共有多少人参加高考,过一本线(大于等于550分),二本线(大于等于450分),专科线(大 于等级250分的)分别有多少人;
青铜写法
王者写法
上面的写法中除了我们认识的一些SQL语法外还出现了case wen 这种SQL结构;
上面的SQL我们再分步骤看下实现思路
第一步 : 查询所有考生的考试成绩
查询出来之后 将查询结果先封装成临时表 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的技巧;
2.3.6 项目实战高级,难度 ★★★★★★
- 每个区参数考试人数
此处需要注意
这个地方的细节运用,此处的SQL语句用意是统计每个区域参考人员的,如果这个学生没 有高考成绩是不会被统计的
- 每个区的录取情况
查询所有区域
每个区域加以统计
