9、视图

FeiYi

MySQL

发布于：2019年12月9日

次浏览

字数：1.4k字

时长：6分钟

视图

概念

视图在mysql5.0版本之后退出，主要用于保证数据的安全性

属于一张虚拟表，是占用极小空间的逻辑存在的表，视图不存放数据，所有的数据来源于真实表。

视图可以从一个或者多个表中到处，来源于一个表叫做单表视图，来源于多个表为多表视图

视图的优点

简单化

安全性

逻辑结构独立性：可以帮助用户屏蔽因为真实表结构变化带来的影响

创建视图的语法

CREATE [OR REPLACE] [ALGORITHM=视图选择的算法{undefined|merge|temptable}] VIEW 视图名(定义视图的字段) 
AS SELECT_statement [WITH[CASCADED|LOCAL] CHECK OPTION]
缩略语法：create view 视图名 as 查询语句

OR REPLACE 替换
ALGORITHM  选择视图的算法
undefined  视图将自动选择算法（默认为merge）
merge       合并算法
temptable    临时表算法
[WITH[CASCADED|LOCAL] CHECK OPTION] 更新视图时，需要满足视图的权限范围
WITH CASCADED CHECK OPTION 更新视图时需要满足所有视图所有表的相关条件
WITH LOCAL CHECK OPTION 更新视图时满足自身条件即可
如果只写WITH CHECK OPTION = CASCADED
全部不写 =LOCAL

例：

/*例子数据*/
mysql> CREATE TABLE department
    -> (d_id INT PRIMARY KEY NOT NULL,
    -> d_name VARCHAR(30) NOT NULL UNIQUE,
    -> function VARCHAR(50),
    -> address VARCHAR(50));

mysql> CREATE TABLE worker
    -> (num INT NOT NULL PRIMARY KEY,
    -> d_id INT,
    -> name VARCHAR(20) NOT NULL,
    -> sex VARCHAR(4) NOT NULL,
    -> birthday DATE,
    -> homeaddress VARCHAR(50),
    -> CONSTRAINT fk_worker FOREIGN KEY(d_id) REFERENCES department(d_id));
mysql> INSERT INTO department VALUES (1001,'科研部','科学研究','2号楼3层')
    -> ,(1002,'农业部','农业生产','5号楼10层'),
    -> (1003,'财务部','财政拨款','20号楼20层');
mysql> INSERT INTO worker VALUES(10,1001,'ljj','f','20010101','山西'),
    -> (11,1002,'cyj','m','19961001','北京');

/*创建视图*/
mysql> CREATE VIEW department_view1 AS SELECT * FROM department;
mysql> show tables;
+------------------+
| Tables_in_view1  |
+------------------+
| department       |
| department_view1 |            /*视图*/
| worker           |
+------------------+

mysql> SELECT * FROM department_view1;    
/*视图的查询语句时select *，所以查询到的东西和原表一样*/
+------+-----------+--------------+---------------+
| d_id | d_name    | function     | address       |
+------+-----------+--------------+---------------+
| 1001 | 科研部     | 科学研究     | 2号楼3层        |
| 1002 | 农业部     | 农业生产     | 5号楼10层       |
| 1003 | 财务部     | 财政拨款     | 20号楼20层      |
+------+-----------+--------------+---------------+
mysql> CREATE VIEW department_view2(name,funcation,locaion)/*将要显示的字段重命名*/
    -> AS SELECT d_name,function,address    /*查询语句只显示了3个字段*/
    -> FROM department;
mysql> SELECT * FROM department_view2;
+-----------+--------------+---------------+
| name      | funcation    | locaion       |
+-----------+--------------+---------------+
| 科研部     | 科学研究     | 2号楼3层       |
| 农业部     | 农业生产     | 5号楼10层      |
| 财务部     | 财政拨款     | 20号楼20层     |
+-----------+--------------+---------------+

/*多表视图*/
mysql> CREATE ALGORITHM=MERGE VIEW worker_view1(name,department,sex,age,address) /*使用合并算法创建视图*/
    -> AS SELECT name,d_name,sex,YEAR(NOW())-YEAR(birthday),address    /*多表查询语句*/
    -> FROM worker,department
    -> WHERE worker.d_id=department.d_id
    -> WITH LOCAL CHECK OPTION;            /*更新视图时满足自身条件即可*/

关于 WITH LOCAL CHECK OPTION

/*创建自身表*/
mysql> CREATE TABLE test1(s INT);

mysql> CREATE VIEW v1 AS SELECT s FROM test1 WHERE s>10;        /*创建视图*/
mysql> INSERT INTO v1 VALUES(5);        /*尝试往视图虚拟表中写入数据*/
mysql> SELECT * FROM v1;        /*查询视图中没有数据*/
mysql> INSERT INTO v1 VALUES(15);        /*插入一条大于10的数据*/
mysql> SELECT * FROM v1;            /*发现视图中的只有一条15的数据*/
+------+
| s    |
+------+
|   15 |
+------+
mysql> SELECT * FROM test1;        /*查询真实表，发现有插入的所有数据*/
+------+
| s    |
+------+
|    5 |
|   15 |
+------+
mysql> CREATE VIEW v2 AS SELECT s FROM v1 WITH CASCADED CHECK OPTION;
mysql> INSERT INTO v2 VALUES(6);        /*因为v2中有满足所有视图的条件，所以v2不满足v1的条件，所有不能写入*/
ERROR 1369 (HY000): CHECK OPTION failed 'view1.v2'
mysql> CREATE VIEW v3 AS SELECT s FROM v2 WHERE s<20;        /*默认是local，满足自身条件即可*/
mysql> INSERT INTO v3 VALUES(29);            /*能够写入，但是查询v3不显示，真实表中有*/

==操作视图时，真实表一并发生变化，同理操作真实表视图发生变化（增删改）==

==可以对视图进行操作，都是视图中的字段如果存在运算规则的话，该字段不能进行更改==

==基于视图可以创建视图==

存在以下请视图无法被更新

1、视图中不包含真实表中被定义为非空的列

2、视图的select语句中包含数学表达式

3、视图的select语句中包含聚合函数

4、视图的select语句中使用group by|having|union 不能被更新

查看视图的方法

SHOW TABLE STATUS LIKE '视图名' \G
SHOW CREATE VIEW 视图名\G
mysql> show table status like 'v1'\G
*************************** 1. row ***************************
           Name: v1                /*表名*/
         Engine: NULL            /*存储引擎*/不能添加索引，不能提高查询速度
        Version: NULL            /*版本*/.frm文件格式版本，表结构文件，跟随mysql版本的文件格式版本
     Row_format: NULL            /*行格式*/
           Rows: NULL            /*行数*/
 Avg_row_length: NULL            /*数据平均长度（单位：字节）*/
    Data_length: NULL            /*数据长度，同上*/
Max_data_length: NULL            /*表能容纳的最大字节数*/
   Index_length: NULL            /*索引长度*/
      Data_free: NULL            /*剩余空间*/
 Auto_increment: NULL            /*下一个要自增的值*/
    Create_time: NULL            /*创建表的时间*/
    Update_time: NULL            /*最后一次更新表的时间*/
     Check_time: NULL            /*最后一次检查表的时间*/
      Collation: NULL            /*排序规则*/
       Checksum: NULL            /*校验和*/服务器之间进行数据传输时，保证数据的完整性
 Create_options: NULL            /*创建表时的选项*/
        Comment: VIEW            /*额外信息*/这是个视图

 mysql> show create view v1\G    /*适用于查看视图*/
*************************** 1. row ***************************
                View: v1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `test1`.`s` AS `s` from `test1` where (`test1`.`s` > 10)
character_set_client: utf8
collation_connection: utf8_general_ci