创建视图是指在已经存在的数据库表上建立视图。视图可以建立在一张表中,也可以建立在多张表中。
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];
ALGORITHM 是可选参数,表示视图选择的算法。ALGORTHIM参数的取值有3个,分别是UNDEFINED、MERGE和TEMPTABLE。UNDEFINED 表示 MySQL 将自动选择算法M;MERGE 表示将使用的视图语句与视图定义合并,使得视图定义的某一部分取代语句对应的部分; TEMPTABLE 表示将视图的结果存入临时表,然后用临时表来执行语句。
view_name 参数表示要创建的视图名称。
column_list 是可选参数,指定视图中各个属性的名词,默认情况下与 SELECT 语句中查询的属性相同。
SELECT_statement 参数是个完整的查询语句,表示从某个表中查出某些满足条件的记录,将这些记录导入视图中。
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
- 上一篇:Mysql索引优缺点以及分类
- 下一篇:Mysql主键和外键