2019-7-29 [MySQL] 视图 [语法/使用/实例] 索引 [分类/语法/使用技巧]

3.MySql拓展应用

3.1 视图

3.1.1 视图的概述

3.1.1.1 为什么需要视图?

在实际工作中，不同身份的用户所关注的数据库数据可能也有所不同。

例如，企业的员工信息表中保存了该企业所有员工的详细信息，不同职位的人员对该表中查询的数据范围可能是不同的。

根据企业的人力资源管理制度要求:
企业的老板关注企业员工的全部信息，他可以浏览全体员工的全部记录；
企业人力资源主管主要是查询全体员工目前的岗位、薪金和绩效；
企业出纳员只能查询每个员工的薪金，不能也无权看到企业员工的其他信息；
而作为这家企业的一名员工，只能查看本人记录，不得查看其他员工的任何信息。

其实我们使用视图的目的还有一个就是能过简化我们的SQL编写难度

3.1.1.2 什么是视图

视图是一张虚拟表
表示一张表的部分数据或多张表的综合数据
其结构和数据是建立在对表的查询基础上

视图中不存放数据

数据存放在视图所引用的原始表中
一个原始表，根据不同用户的不同需求，可以创建不同的视图

3.1.1.3 视图的用途

筛选表中的行
防止未经许可的用户访问敏感数据
降低数据库的复杂程度
将多个物理数据库抽象为一个逻辑数据库

3.1.2 视图的使用

3.1.2.1 视图的语法

3.1.2.2 视图的使用

1. 将学生基本情况,所属学校,所属地区封装为一个视图

-- 学生表 地区表 学校表的管理查询视图
create view stu_v1 as
select a.a_id aid,a.a_name aname,sc.sc_id scid,sc.sc_name scname,
s.s_id sid,s.s_name sname,s.s_sex sex,s.s_birthday birthday,s.s_examnum examnum 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;
-- 分页查询视图
select * from stu_v1 limit 1,10;

此处需要注意,
视图是一个虚拟表,视图是不允许出现重复列的!
而且封装视图的查询结果中也不能用子查询!
这两点一定要切记!

2. 将各分数线录取情况封装视图

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语句如果需要封装视图的话必须先把子查询封装一个视图之后再对整条SQL语句进行视图的封装,所以此处如果想要封装视图必须分两步操作

第一步 : 将每个学生的高考总分封装一个视图

create view stu_v2 as select s_id sid,SUM(r_score) totalScore from result GROUP BY s_id;

第二步 : 在根据上一个视图封装整体SQL语句

create view stu_v3 as 
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,
sum(CASE WHEN totalScore < 450 and totalScore < 250 then 1 else 0 end) x
from stu_v2;

通过以上两步封装视图

3.2 索引

3.2.1 索引的概述

3.2.1.1 什么是索引

汉语字典中的汉字按页存放，一般都有汉语拼音目录（索引）、偏旁部首目录等
我们可以根据拼音或偏旁部首，快速查找某个字词
索引技术就是为了让数据库 更快的定位一条数据所采用的一种查询性能优化技术
视图 : 意义在于 降低SQL语句的编写难度

3.2.1.2 Mysql索引

索引是一种有效组合数据的方式，为快速查找到指定记录

作用
大大提高数据库的检索速度
改善数据库性能

MySQL索引按存储类型分类
B-树索引
哈希索引

假如说 有100条数据

没有索引 第99个数据 mysql 从第一个开始找 99 返回

mysql 给 100 数据建立一个索引(手动创建)
如果你下次再查询某条数据的花 99 mysql(先翻一下索引找到这条数据的位置 直接指向就可以了)

3.2.1.3 Mysql索引分类

3.2.2 MySql创建索引的语法

1. 学生表的学生姓名添加一个索引

create INDEX index_student_sname on student(s_name);

2. 查看某个表的索引

show index from student;

部分结果说明
Table：创建索引的表
Non_unique：索引是否非唯一
Key_name：索引的名称
Column_name：定义索引的列字段
Seq_in_index：该列在索引中的位置
Null：该列是否能为空值
Index_type：索引类型

3. 删除索引

DROP INDEX index_student_sname on student;

3.2.3 MySql创建索引的使用技巧

创建索引的指导原则

(一) 按照下列标准选择建立索引的列

频繁搜索的列
经常用作查询选择的列
经常排序、分组的列
经常用作连接的列（主键/外键）

(二) 请不要使用下面的列创建索引

仅包含几个不同值的列
表中仅包含几行

(三) 使用索引时注意事项

查询时减少使用*返回全部列，不要返回不需要的列
索引应该尽量小，在字节数小的列上建立索引
WHERE子句中有多个条件表达式时，包含索引列的表达式应置于其他条件表达式之前
避免在ORDER BY子句中使用表达式