【详细】MySQL之视图篇

一、基本概念

视图是一个虚拟表，是sql的查询结果，其内容由查询定义。
同真实的表一样，视图包含一系列带有名称的列和行数据，在使用视图时动态生成。

二、视图的作用

1、权限控制

比如说，某几列允许用户查询，其它列不允许，就可以只把允许的列作为查询字段使用视图展示

2、大数据分表使用

比如,表的行数超过200万行时,就会变慢,可以把一张的表的数据拆成4张表来存放.然后使用视图来关联几张表（Create view news as select from n1 union select from n2 union…）

3、提高查询效率

看到的就是需要的。视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不必为以后的操作每次指定全部的条件。

三、视图的操作

视图的数据变化会影响到基表，当视图与多个基表关联时，不能通过视图去修改多个基表，只能去insert/update/delete一个基表的字段，而且在操作视图时基表的约束对视图同样有效
基表的数据变化也会影响到视图

1、创建

注意：Mysql视图的定义在from关键字后面不能包含子查询

create view  视图名  as  select 字段名 from 表名;

2、查看

show tables;
desc 视图名 -- 查看视图结果

3、删除

drop view 视图名

4、使用视图结果

和表查询一样

select * from 视图名

四、应用示例

1、创建userinfo表存储员工基本信息

-- ----------------------------
-- Table structure for userinfo
-- ----------------------------
DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo`  (
  `id` int(11) NOT NULL,
  `userName` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `passWord` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `sex` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `phone` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of userinfo
-- ----------------------------
INSERT INTO `userinfo` VALUES (1, '李虎', '123456', 25, '男', '17694855647');
INSERT INTO `userinfo` VALUES (2, '吴霞', '456789', 20, '女', '13894567899');
INSERT INTO `userinfo` VALUES (3, '张杰', '666666', 16, '男', '15687486985');
INSERT INTO `userinfo` VALUES (23, '赵雷', '7788', 32, '男', '14525896544');

2、创建员工工资表

-- ----------------------------
-- Table structure for job
-- ----------------------------
DROP TABLE IF EXISTS `job`;
CREATE TABLE `job`  (
  `id` int(20) NOT NULL,
  `job` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `salary` int(20) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `INDEX_JOB`(`job`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of job
-- ----------------------------
INSERT INTO `job` VALUES (1, 'java初级开发工程师', 6000);
INSERT INTO `job` VALUES (2, 'c++中级开发工程师', 16000);
INSERT INTO `job` VALUES (3, '数据库工程师', 10000);
INSERT INTO `job` VALUES (4, 'java高级工程师', 25000);
INSERT INTO `job` VALUES (23, 'HR', 4000);

3、创建视图
用来存储员工工资

CREATE VIEW `salaryview` AS select `a`.`id` AS `id`,`a`.`job` AS `job`,`a`.`salary` AS `salary`,`b`.`userName` AS `userName` from (`job` `a` join `userinfo` `b`) where (`a`.`id` = `b`.`id`) order by `a`.`salary`

4、操作视图来
我们在视图中插入一条sql，来看看基表的数据有没有同时变化

insert into salaryView(id, job, salary, userName) values(3, 'php工程师', 7000, '王萌')

报错“Can not modify more than one base table through a join view ‘spring.salaryview’”
意思是

不能通过视图去同时操作多张基表

好，接着我们改下sql，只来操作一张表的字段

insert into salaryView(id, job, salary) values(3, 'php工程师', 7000)

报错Duplicate entry ‘4’ for key ‘PRIMARY’ 意思是：主键重复，这也说明了

基表的约束条件同时也会作用于与其关联的视图

好，接着改下id

insert into salaryView(id, job, salary) values(5, 'php工程师', 7000)
insert into salaryView(id, job, salary) values(6, 'php工程师')

这时候我们发现视图表中并没有新增数据，而操作字段对应的基表job表中却新增了2条数据，这时为什么尼？
我们回头来看看定义视图的语句，很明显视图中封装的是两个基表的联查结果，而只有job表中有这2条数据时当然不会联查出结果

5、操作基表
我们删除一条基表的数据，来看看视图的变化

DELETE from userinfo where id = 3

执行后发现，userinfo中id为3的这条数据删除了，对应视图中的这条数据也删除了
基表的操作之所以会影响到视图，是因为视图的结果来自于基表，所以也只有当视图中存在基表作用后的数据时，对基表的操作才能影响到视图

五、总结

视图实质是对单表或多表联查结果的封装
需要注意以下几点

1、不能通过视图去操作多张基表；
2、基表的约束同时也会作用于对应的视图