MySQL学习笔记5（视图）

MySQL学习笔记5

视图

视图是从一个或多个表中导出来的表，是一种虚拟存在的表。视图就像一个窗口，通过这个窗口可以看到系统专门提供的数据。这样，用户可以不用看到整个数据库表中的数据，而只关心对自己有用的数据。视图可以使用户的操作更方便，而且可以保障数据库系统的安全性。
1：视图的含义和作用
2：如何创建视图
3：如何修改视图
4：如何查看视图
5：如何删除视图

1.1：视图的含义

视图是一种虚拟的表。视图从数据库中的一个或多个表导出来的表。视图还可以从已经存在的视图的基础上定义。数据库中只存放了视图的定义，而并没有存放视图中的数据。这些数据存放在原来的表中。使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。因此，视图中的数据是依赖于原来的表中的数据的。一旦表中的数据发生改变，显示在视图中的数据也会发生改变。

1.2：视图的作用

视图是在原来的表或者视图的基础上重新定义的虚拟表，这可以从原有的表上选取对用户有用的信息。那些对用户没有用，或者用户没有权限了解的信息，都可以直接屏蔽掉。这样做即使应用简单化，也保证了系统的安全。视图起着类似与帅选的作用。视图的作用归纳为：
1. 使操作简单化
视图需要达到的目的就是所见即所需。也就是说，从视图看到的信息就是多需要了解的信息。视图可以简化对数据的操作。例如，可以为经常使用的查询定义一个视图。使用户不必为同样的查询操作制定条件。这样可以很大程度上方便用户的操作。

1. 增加数据的安全性
   通过视图，用户只能查询和修改指定的数据，制定数据以外的信息，用户根本接触不到。数据库授权命令可以限制用户的操作权限，但不能限制到特定的行和列上。使用视图后，可以简单方便的将用户的权限限制到特定的行和列上。这样可以保证敏感信息不会被没有权限的人看到，可以保证一些机密信息的安全。

2. 提高表的逻辑独立性
   视图可以屏蔽原有表结构变化带来的影响。例如，原有表增加列和删除未被引用的列，对视图不会造成影响。同样，如果修改了表中的某些列，可以修改视图来解决这些列带来的影响。

2：创建视图

创建视图是指在已存在的数据库表上建立视图。视图可以建立在一张表上，也可以建立在多张表上。
MySQL中，创建视图是通过SQL语句CREATE VIEW实现的。其语法形式如下：
CREATE [ALGORITHM={ UNDIFIEND | MERGE | TEMPTABLE }]
VIEW 视图名 [(属性清单)]
AS SELECT 语句
[ WITH [CASCADED | LOCAL ] CHECK OPTION];
UNDIFIEND表示MySQL自动选择所需要的算法。
MERGE表示将使用视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。
TEMPTABLE表示将视图的结果存入临时表。然后使用临时表执行语句。

CASCADED是可选参数，表示更新视图时要满足所有相关视图和表的条件，该参数为默认值。
LOCAL表示更新视图时要满足该视图本身的定义的条件即可。

1. 在单表上创建视图

Department表的创建：

CREATE TABLE department(d_id INT(4) NOT NULL PRIMARY KEY,d_name VARCHAR(20) NOT NULL UNIQUE,function VARCHAR(50),address VARCHAR(50));

在department表上创建视图department_view1：

CREATE VIEW department_view1AS SELECT * FROM department;

在department表上创建视图department_view2：

CREATE VIEW department_view2(name,function,location)AS SELECT d_name,function,address FROM department;

1. 在多表上创建视图
   MySQL中也可以在两个或两个以上的表上创建视图，也是使用CREATE VIEW语句实现的。

Worker表的创建：

CREATE TABLE worker(num INT(10) NOT NULL PRIMARY KEY,d_id INT(4),name VARCHAR(20) NOT NULL,sex VARCHAR(4) NOT NULL,birthday DATETIME,homeaddress VARCHAR(50));

在department表和worker表上创建一个名为worker_view1的视图：

CREATE ALGORITHM=MERGE VIEWworker_view1(name,department,sex,age,address)AS SELECT name,department.d_name,sex,2016-birthday,addressFROM worker,department WHERE worker.d_id=department.d_idWITH LOCAL CHECK OPTION;

3：查看视图

查看视图是指查看数据库中已存在的视图的定义。查看视图必须要有SHOW VIEW的权限，mysql数据库下的user表中保存着这个信息。查看视图的方法包括DESCRIBE语句、SHOW TABLE STATUS语句、SHOW CREATE VIEW语句和查询information_schema数据库下的views表等。

1. DESCRIBE语句查看视图基本信息

2. SHOW TABLE STATUS语句查看视图基本信息
   语法形式如下：
   SHOW TABLE STATUS LIKE ‘视图名’；

SHOW TABLE STATUS LIKE 'department_view2'\G

1. SHOW CREATE VIEW语句查看视图详细信息
   语法形式如下：
   SHOW CREATE VIEW 视图名

SHOW CREATE VIEW department_view2\G

1. 在views表中查看视图详细信息
   MySQL中，所有视图的定义都存在information_schema数据库下的views表中。查询views表，可以查看到数据库中所有视图详细信息。查询语句如下：

SELECT * FROM information_schema.views;

4：修改视图

修改视图是指修改数据库中已存在的表的定义。当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致。MySQL中通过CREATE OR REPLACE VIEW语句和ALTER语句来修改视图。

1. CREATE OR REPLACE VIEW 语句修改视图
   MySQL中，CREATE OR REPLACE VIEW 语句可以用来修改视图。该语句的使用非常灵活。该语句的使用非常灵活。在视图已经存在的情况下，对视图进行修改；视图不存在时，可以创建视图。
   CREATE OR REPLACE [ ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
   VIEW 视图名 [（属性清单）]
   AS SELECT 语句
   [WITH [CASCADED | LOCAL ] CHECK OPTION ];

CREATE OR REPLACE ALGORITHM=TEMPTABLEVIEW department_view1(department,function,location)AS SELECT d_name,function,address FROM department;

1. ALTER语句修改视图
   在MySQL中，ALTER语句可以修改表的定义，可以创建索引。不仅如此，ALTER语句还可以用来修改视图。
   ALTER语句来修改视图的语法格式如下：
   ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
   VIEW 视图名 [（属性清单）]
   AS SELECT 语句
   [WITH [CASCADED | LOCAL ] CHECK OPTION ];

ALTER VIEW department_view2(department,name,sex,location)AS SELECT d_name,worker.name,worker.sex,addressFROM department,worker WHERE department.d_id=worker.d_idWITH CHECK OPTION;

5：更新视图

更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的数据。因为视图是一个虚拟表，其中没有数据。通过视图更新时，都是转换到基本表来更新。更新视图时，只能更新权限范围内的数据。超出了范围，就不能更新。

并不是所有的视图都是可以更新的。以下这几种情况是不能更新视图
1. 视图中包含有SUM()、MAX()、MIN()、COUNT()等函数
2. 视图中包含UNION、UNION ALL、DISTINCT、GROUP BY、HAVING等关键字
3. 常量视图

CREATE VIEW worker_view2AS SELECT 'Aric' as name;

视图worker_view2就是常量视图。
因为name字段是个字符串常量‘Aric’，所以该视图也是不能更新的，使用UPDATE语句更新时，会出现系统报错。信息如下：

mysql> UPDATE worker_view2 SET name='hj';ERROR 1288 (HY000): The target table worker_view2 of the UPDATE is not updatable

1. 视图中的SELECT中包含了子查询
2. 由不可更新的视图导出的视图

CREATE VIEW worker_view3AS SELECT * FROM worker_view2;

1. 创建视图时，ALGORITHM为TEMPTABLE类型
2. 视图对应的表上存在没有默认值的列，而且该列没有包含在视图中。例如，表中包含的name字段没有默认值，但是视图中不包括该字段。那么这个视图是不能更新的。因为在更新视图时。这个没有默认值的记录将没有值插入，也没有NULL值插入。数据库系统是不会允许这样的情况出现的。

6：删除视图

删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据。MySQL中，使用DROP VIEW语句来删除视图。但是，用户必须拥有DROP权限。形式如下：
DROP VIEW [ IF EXISTS ] 视图名列表 [ RESTRICT | CASCADE]
删除一个视图

DROP VIEW IF EXISTS worker_view1;

删除多个视图

DROP VIEW IF EXISTS department_view1,department_view2;