创建视图是指在已经存在的数据库表上建立视图。视图可以建立在一张表中，也可以建立在多张表中。
1.查看是否有创建视图的权限

mysql> SELECT  Select_priv, Create_view_priv  FROM  mysql.user  WHERE  user='root';
+-------------+------------------+
| Select_priv | Create_view_priv |
+-------------+------------------+
| Y           | Y                |
+-------------+------------------+
1 row in set (0.00 sec)

2.创建视图
语法如下：
CREATE [ALGORITHM={ UNDEFINED | MERGE | TEMPTABLE }]
VIEW view_name [( column_list )]
AS SELECT_statement
[ WITH | [ CASCADED | LOCAL ] CHECK OPTION];http://www.cndba.cn/hbhe0316/article/22220

ALGORITHM 是可选参数，表示视图选择的算法。ALGORTHIM参数的取值有3个，分别是UNDEFINED、MERGE和TEMPTABLE。UNDEFINED 表示 MySQL 将自动选择算法M；MERGE 表示将使用的视图语句与视图定义合并，使得视图定义的某一部分取代语句对应的部分； TEMPTABLE 表示将视图的结果存入临时表，然后用临时表来执行语句。

view_name 参数表示要创建的视图名称。http://www.cndba.cn/hbhe0316/article/22220http://www.cndba.cn/hbhe0316/article/22220

column_list 是可选参数，指定视图中各个属性的名词，默认情况下与 SELECT 语句中查询的属性相同。http://www.cndba.cn/hbhe0316/article/22220

SELECT_statement 参数是个完整的查询语句，表示从某个表中查出某些满足条件的记录，将这些记录导入视图中。http://www.cndba.cn/hbhe0316/article/22220

WITH CHECK OPTION 是可选参数，表示更新视图时要保证在该视图的权限范围之内。
CASCADED 与 LOCAL 为可选参数，CASCADED 为默认值。表示更新视图时要满足所有相关视图和表的条件；LOCAL 表示更新视图时满足该视图本身定义的条件即可。

3.在单表上创建视图

mysql> create table t3 (id int,name varchar(10) not null,sex varchar(10));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t3 values (1,'zhangsan','man');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t3 values (2,'lisi','feman');
Query OK, 1 row affected (0.00 sec)

mysql> select * from t3;
+------+----------+-------+
| id   | name     | sex   |
+------+----------+-------+
|    1 | zhangsan | man   |
|    2 | lisi     | feman |
+------+----------+-------+
2 rows in set (0.00 sec)

mysql> create view view_t3 as select id,name from t3;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from view_t3;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
+------+----------+
2 rows in set (0.00 sec)

4.在多表创建视图

mysql> select * from t3;
+------+----------+-------+
| id   | name     | sex   |
+------+----------+-------+
|    1 | zhangsan | man   |
|    2 | lisi     | feman |
+------+----------+-------+
2 rows in set (0.00 sec)

mysql> select * from t4;
+------+---------+
| id   | menu    |
+------+---------+
|    1 | English |
|    2 | math    |
+------+---------+
2 rows in set (0.00 sec)


mysql> create view view_t5(id) as select t3.id from t3,t4 where t3.id=t4.id;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from view_t5;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> select * from view_t5;
+------+
| id   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

5.查看视图

mysql> show create view view_t5 /G
*************************** 1. row ***************************
                View: view_t5
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_t5` AS select `t3`.`id` AS `id` from (`t3` join `t4`) where (`t3`.`id` = `t4`.`id`)
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)

6.更新视图
可以对视图进行增删改

mysql> update view_t3 set id=88 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from view_t5;
+------+
| id   |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql> select * from t3;
+------+----------+-------+
| id   | name     | sex   |
+------+----------+-------+
|   88 | zhangsan | man   |
|    2 | lisi     | feman |
+------+----------+-------+
2 rows in set (0.00 sec)

mysql> select * from t4;
+------+---------+
| id   | menu    |
+------+---------+
|    1 | English |
|    2 | math    |
+------+---------+
2 rows in set (0.00 sec)

7.删除视图

mysql> drop view view_t5;
Query OK, 0 rows affected (0.00 sec)

版权声明：本文为博主原创文章，未经博主允许不得转载。

MYSQL