mysql-视图

1、视图是一个虚拟表，是从数据库中一个或多个表中导出的表，其内容由查询定义。视图是存储在数据库中的查询的sql语句，主要出于二种原因：安全原因，视图可以隐藏一些数据，例如：员工信息表，可以用视图只显示姓名、工龄、地址，而不显示社会保险号和工资数，另一原因是可以是复杂的查询易于理解和使用。

2、定义视图的筛选可以来自当前或者其他数据库的一个或多个表，或者其他视图。

3、查询用户是否具有创建视图的权限

mysql> select select_priv,create_view_priv from mysql.user ;+-------------+------------------+| select_priv | create_view_priv |+-------------+------------------+| Y           | Y                || Y           | Y                || N           | N                |+-------------+------------------+3 rows in set (0.05 sec)

4、创建视图

mysql> select * from workmates;+----+-------+------+--------+-------------+---------------------+| id | name  | age  | sex    | tel         | birthday            |+----+-------+------+--------+-------------+---------------------+|  1 | James |   30 | Male   | 13545644544 | 1985-01-23 12:03:45 ||  2 | Marry |   27 | F      | 13601245698 | 1986-11-04 02:11:35 ||  3 | Lily  |   28 | Female | 13424345666 | 1987-09-21 20:00:00 |+----+-------+------+--------+-------------+---------------------+3 rows in set (0.07 sec)mysql> create view workmates_view(a_id, a_name, a_age, a_sex) as select id,name,age,sex from workmates;Query OK, 0 rows affected (0.06 sec)

使用视图

mysql> select * from workmates_view;+------+--------+-------+--------+| a_id | a_name | a_age | a_sex  |+------+--------+-------+--------+|    1 | James  |    30 | Male   ||    2 | Marry  |    27 | F      ||    3 | Lily   |    28 | Female |+------+--------+-------+--------+3 rows in set (0.00 sec)

如果联合二张表查询，则语法为

CREATE ALGORITHM=MERGE VIEW  view_name（view_field1, view_field2, view_field3,） AS  SELET ... FROM  tbl_name1 , tbl_name2 WHERE 匹配条件

WITH LOCAL CHECK OPTION;

5、查看视图

mysql> describe workmates_view;+--------+------------------+------+-----+---------+-------+| Field  | Type             | Null | Key | Default | Extra |+--------+------------------+------+-----+---------+-------+| a_id   | int(10) unsigned | NO   |     | 0       |       || a_name | varchar(30)      | NO   |     | NULL    |       || a_age  | int(11)          | YES  |     | NULL    |       || a_sex  | char(10)         | YES  |     | NULL    |       |+--------+------------------+------+-----+---------+-------+4 rows in set (0.04 sec)

6、查看视图信息

mysql> show table status like 'workmates_view';+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+| Name           | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment|+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+| workmates_view | NULL   |    NULL | NULL       | NULL |           NULL |  NULL |            NULL |         NULL |      NULL |           NULL | NULL   | NULL        | NULL       | NULL      |     NULL | NULL           | VIEW|+----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+-----------+----------+----------------+---------+1 row in set (0.00 sec)

从查询结果可以看出，视图的存储引擎，数据长度等信息都显示为NULL，说明视图为虚拟表，

7、查看视图详细定义

mysql> show create view workmates_view;+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+| View           | Create View                   | character_set_client | collation_connection |+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+| workmates_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `workmates_view` AS select `workmates`.`id` AS `a_id`,`workmates`.`name` AS `a_name`,`workmates`.`age` AS `a_age`,`workmates`.`sex` AS `a_sex` from `workmates` | gbk                  | gbk_chinese_ci       |+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+1 row in set (0.00 sec)

8、修改视图语法

(1)采用CREATE OR REPLACE VIEW

CREATE OR REPLACE VIEW [ ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW 视图[{属性列表}] 

AS SELECT 语句

[WITH [CASCADED | LOCAL] CHECK OPTION];

（2）采用ALTER

ALTER VIEW [ ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW 视图[{属性列表}] 

AS SELECT 语句

[WITH [CASCADED | LOCAL] CHECK OPTION];

9、更新视图，实质上就是更新对应的表的相应字段

mysql> select * from workmates_view;+------+--------+-------+--------+| a_id | a_name | a_age | a_sex  |+------+--------+-------+--------+|    1 | James  |    30 | Male   ||    2 | Marry  |    27 | F      ||    3 | Lily   |    28 | Female |+------+--------+-------+--------+3 rows in set (0.00 sec)mysql> update workmates_view set a_sex = 'Female' where a_id=2;Query OK, 1 row affected (0.07 sec)Rows matched: 1  Changed: 1  Warnings: 0mysql> select * from workmates;+----+-------+------+--------+-------------+---------------------+| id | name  | age  | sex    | tel         | birthday            |+----+-------+------+--------+-------------+---------------------+|  1 | James |   30 | Male   | 13545644544 | 1985-01-23 12:03:45 ||  2 | Marry |   27 | Female | 13601245698 | 1986-11-04 02:11:35 ||  3 | Lily  |   28 | Female | 13424345666 | 1987-09-21 20:00:00 |+----+-------+------+--------+-------------+---------------------+3 rows in set (0.00 sec)

mysql> select * from workmates_view;+------+--------+-------+--------+| a_id | a_name | a_age | a_sex  |+------+--------+-------+--------+|    1 | James  |    30 | Male   ||    2 | Marry  |    27 | Female ||    3 | Lily   |    28 | Female |+------+--------+-------+--------+3 rows in set (0.00 sec)

10、更新视图的限制，以下几种情况不能更新视图：

（1）视图中包含COUNT(), SUM(),MAX(), MIN()等函数；

（2）视同中包含UNION, UNION ALL, DISTINCT, GROUP BY，HAVING 等关键字，

（3）常量视图 CREATE VIEW book_view AS SELECT 'Aric' AS a_book

（4）由不可更新的视图导出的视图，CREATE VIEW book_view1 AS SELECT * FROM book_view2；

（5）创建视图时，ALGORITHM为TEMPTABLE类型，CREATE ALGORITHM=TEMPTABLE VIEW book_view1 AS SELECT * FROM tbl_book；

（6）视同对应的表上存在没有默认值的列，而且该列没有包含在视图里。例如，表中包含的name字段没有默认值，但是视图中不包括该字段，那么该视图不可以更新。

11、删除视图， DROP VIEW IF EXISTS <视图名> [RESTRICT|CASCADE]， 

视图名可以是视图列表，用分号隔开，

在删除视图时候注意，如果当前视图有导出其他视图，则使用CASCADE级联删除，或者先显示删除导出的视图，在删除该视图；删除基表时，由基表导出的所有视图都必须显示删除。