Mysql视图的学习与案例的讲解

视图的定义

MySQL 视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。

视图的优点

1、简单化，数据所见即所得；
2、安全性，用户只能查询或修改他们所能见到得到的数据，同时也可以隐蔽真实表中的数据结构

视图的缺点

1、性能相对较差，从视图查询数据可能会很慢，特别是如果视图是基于其他视图创建的；
2、 修改不方便，特别是复杂的聚合视图基本无法修改，添加数据时也有可能会出现不能直接观察到添加的数据。

视图案例讲解

准备数据库与表数据

创建数据库sql语句

CREATE DATABASE   数据库名称

创建等级数据表和添加数据

CREATE TABLE `type_info` (
  `t_id` int(11) NOT NULL AUTO_INCREMENT,
  `type_name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8
INSERT  INTO `type_info`(`t_id`,`type_name`) VALUES (1,'青铜'),(2,'白银'),(3,'黄金'),(4,'铂金'),(5,'钻石'),(6,'王者');

创建用户数据表和添加数据

CREATE TABLE `user_info` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `nick_name` varchar(20) DEFAULT NULL,
  `user_name` varchar(20) DEFAULT NULL,
  `user_pwd` varchar(20) DEFAULT NULL,
  `t_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK1gy0qoa8oxhd6lv5j41ubn0if` (`t_id`),
  CONSTRAINT `FK1gy0qoa8oxhd6lv5j41ubn0if` FOREIGN KEY (`t_id`) REFERENCES `type_infos` (`t_id`)
) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8

INSERT  INTO `user_info`(`id`,`user_name`,`user_pwd`,`nick_name`,`t_id`) VALUES (1,'admin','000000','光头强',2),(2,'scott','111111','曹操',3),(3,'sa','111111','刘备',4),(4,'root','222222','美人鱼',2),(5,'3306','222222','风情扬',1),(6,'1433','1111','阿里巴巴',2),(7,'1521','11111','琅琊榜',3),(8,'oracle','222','熊大',2),(9,'mysql','333','熊二',6),(11,'sqlserver','555','混太郎',3),(12,'tomcat','2222222','吕布',6),(13,'apache','111111','赤兔',1),(14,'linux','111111','貂蝉',2),(15,'yum','111111','汉武帝',3),(16,'rpm','111111','波波',2),(17,'tar','111111','大力水手',1),(20,'zhangsan','666666','唐家老三',6);

说明：用户列表中存在外键关系，t_id对应等级表中的t_id;

创建视图语法和实现一个简单案例

创建视图的语法只是在原来的 select 语句前添加“create view 视图名 as”就可
以了。

CREATE VIEW 视图名称 as 跟上select查询语句

根据上面提供的数据表创建一个单表视图

CREATE VIEW v_user
AS SELECT nick_name,user_name,user_pwd FROM user_info

通过视图查询

SELECT * FROM v_user

给视图列名起别名创建视图案例

通过这种方式可以隐藏数据表的字段名称和表名等。

CREATE VIEW v_users(昵称,用户名,密码)
AS SELECT nick_name,user_name,user_pwd FROM user_info

SELECT * FROM v_users

注意：视图括号内的参数列表要和select后的参数要一 一对应。

实现两表联立查询创建视图案例（重要）

内连接实现（左边表的t_id要和右边表的t_id要与之对应才会显示出来）

CREATE VIEW v_user_type AS 
SELECT nick_name,user_name,user_pwd,type_name FROM user_info u INNER JOIN  type_info t ON u.t_id=t.t_id

SELECT * FROM v_user_type

左外连接实现（以左表为准，查询出所有数据，如果右表t_id不能与之对应，则该条数据的所有列补充null）

CREATE VIEW v_user_types AS 
SELECT nick_name,user_name,user_pwd,type_name FROM user_info u LEFT JOIN type_info t ON u.t_id=t.t_id

SELECT * FROM v_user_types

右外连接实现（以右表为准，查询出所有数据，如果左表t_id不能与之对应，则该条数据的所有列补充null）

CREATE VIEW v_user_typess AS
SELECT nick_name,user_name,user_pwd,type_name FROM user_info u RIGHT JOIN type_info t ON u.t_id=t.t_id
SELECT * FROM v_user_typess

使用视图更新数据

在 MySQL 中，视图不仅是可查询的，而且是可更新的。这意味着您可以使用 INSERT 或 UPDATE 语句通过可更新视图插入或更新基表的行。另外，您可以使用 DELETE 语句通过视图删除底层表的行。但是，要创建可更新视图，定义视图的 SELECT 语句不能包含以下任何元素：
➢ 聚合函数；
➢ distinct 子句；
➢ group by 子句；
➢ having 子句；
➢ union 和 union all 子句；
➢ 外连接
注意：不建议使用基于多表创建的视图进行更新操作。

使用视图更新底层数据案例

CREATE VIEW view_user 
AS SELECT * FROM user_info

SELECT * FROM view_user

UPDATE view_user SET user_pwd='qwes' WHERE user_name='admin'

WITH CHECK OPTION 子句的使用

有时候，创建一个视图来显示表的部分数据。然而，简单视图是可更新的，因此可以更新通过视图不可见的数据。此更新使视图不一致。为了确保视图的一致性，在创建或修改视图时使用 WITH CHECK OPTION 子句。

CREATE VIEW view1
AS
SELECT * FROM user_info WHERE id<10;

INSERT view1(nick_name,user_name,user_pwd,t_id)VALUES('mybatis-plus','mp','1254',3) 

SELECT * FROM view1

通过查询视图没有发现刚才添加的数据

但是在底层数据表中发现已经添加成功了，如图：

那么对于一个没有权限操作数据表的开发人员来说是不是看不到数据是否添加成功，为了确保视图的一致性，用户只能显示或更新通过视图可见的数据，则在创建或修改视图时使用 WITH CHECK OPTION。

CREATE VIEW view2
AS
SELECT * FROM user_info WHERE t_id<2 WITH CHECK OPTION;

INSERT view2(nick_name,user_name,user_pwd,t_id)VALUES('mybatis-pluss','mps','12545465',2) 

SELECT * FROM view2

此时若插入视图可见数据是正确的，大家可以自己动手试一试。因此，没有特殊理由的情况下，建议都带上 WITH CHECK OPTION。

视图的管理

视图管理最简单的方式就是通过 navicat 客户端工具鼠标点击操作，但是下面我给大家介绍一下sql的管理方式：
1、查看视图定义

SHOW CREATE VIEW view_user

2、显示视图
视图本质上也是表，因此查看视图可以使用 show tables 命令查看。
3、 修改或替换视图使用 alter view 替换 create view 就可以实现视图的修改操作，还可以使用 CREATE OR REPLACE VIEW 语句来创建或替换现有视图。如果一个视图已经存在，MySQL 只会修改视图。如果视图不存在，MySQL 将创建一个新的视
图。
4、 删除视图
使用“drop view 视图名”命令就可以删除视图了。