MySQL基础之 视图
视图
视图就是从一个表或多个表导出来的一张虚拟的表。通过这个窗口可以看到系统专门提供的数据，方便用户操作的同时增加了安全性。
视图的特点：
1、视图的列可以来自于不同的表。
2、视图是由实际存在的表创建出来的一个虚表
3、视图的建立和删除不影响虚拟表，但是对视图内容的更新等操作（比如增加、删除、修改）会直接影响实际表。
4、当视图来自多个实际表时，不允许添加和删除数据。
一、创建视图
CREATE [OR REPLACE] [ALGORITHM=[UNDEFINED | MERGE |TEMPLATE]]
VIEW VIEW_NAME [COLUMN_LIST]
AS SELECT STATEMENT
[WITH [CASCADED |LOCAL ] ] CHECK OPTION
讲解：
CREATE表示创建，REPLACE表示替换已经存在的视图
ALGORITHM表示视图选择的算法，默认时UNDEFINED
COLUMN_LIST：表示视图的属性列。一般不使用
SELECT STATEMENT表示select语句
[CASCADED |LOCAL ] ：CASCADED表示更新视图时要满足所有相关视图和表的条件。LOCAL表示更新视图时满足该视图本身定义的条件即可。
注意：
1、创建视图要求使用者有CREATE VIEW权限和SELECT语句选择列权限
2、创建视图最好加上WITH CASCADED CHECK OPTION参数。保证数据的安全性
举例
单表创建视图
mysql> create or replace view view_test1_1 as select id,name from test1_1 with cascaded check option; Query OK, 0 rows affected (0.00 sec) mysql> desc view_test1_1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> select * from view_test1_1; +----+--------+ | id | name | +----+--------+ | 1 | 超峰 | | 2 | b | | 3 | c | | 4 | a | | 5 | chen | | 6 | NULL | +----+--------+ 6 rows in set (0.00 sec)
对于多表创建视图就是将多个表使用join....on.....的方式连接起来然后选择其中的一些字段后创建的视图
二、查看视图
1、DESC语句也是可以查看视图的结构。
2、SHOW TABLES STATUS语句查看视图的基本信息。
语法为：SHOW TABLES STATUS [FROM DATABASE_NAME] LIKE 'VIEW_NAME' \G
这个命令也是可以查看一般表的基本的信息
针对这些参数的意思是什么我们可以去网上搜一下
3、SHOW CREATE VIEW VIEW_NAME语句查看视图创建的详细信息
mysql> SHOW CREATE VIEW view_test1_1\G *************************** 1. row *************************** View: view_test1_1 Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test1_1` AS select `test1_1`.`id` AS `id`,`test1_1`.`name` AS `name` from `test1_1` WITH CASCADED CHECK OPTION character_set_client: utf8 collation_connection: utf8_general_ci 1 row in set (0.00 sec)
4、在views表中查看视图详细信息
在MYSQL中所有视图的定义都保存在数据库information_schema中的表views中。
mysql> select * from information_schema.views where table_name='view_test1_1'\G *************************** 1. row *************************** TABLE_CATALOG: def TABLE_SCHEMA: test1 TABLE_NAME: view_test1_1 VIEW_DEFINITION: select `test1`.`test1_1`.`id` AS `id`,`test1`.`test1_1`.`name` AS `name` from `test1`.`test1_1` CHECK_OPTION: CASCADED IS_UPDATABLE: YES DEFINER: root@localhost SECURITY_TYPE: DEFINER CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci 1 row in set (0.00 sec)
二、修改视图
修改视图有两种语法，
第一种跟创建视图的语法一样：
CREATE [OR REPLACE] [ALGORITHM=[UNDEFINED | MERGE |TEMPLATE]]
VIEW VIEW_NAME [COLUMN_LIST]
AS SELECT STATEMENT
[WITH [CASCADED |LOCAL ] ] CHECK OPTION
使用这种方法的时候记得开头是CREATE OR REPLACE。。。。。。。
mysql> CREATE OR REPLACE VIEW view_test1_1 as select id from test1_1 with check option; Query OK, 0 rows affected (0.01 sec) mysql> select * from view_test1_1; +----+ | id | +----+ | 1 | | 2 | | 3 | | 4 | | 5 | | 6 | +----+ 6 rows in set (0.00 sec)
第二种就是使用ALTER VIEW语句的方式修改视图
ALTER [ALGORITHM=[UNDEFINED | MERGE |TEMPLATE]]
VIEW VIEW_NAME [COLUMN_LIST]
AS SELECT STATEMENT
[WITH [CASCADED |LOCAL ] ] CHECK OPTION
mysql> ALTER VIEW view_test1_1 AS select id,name,grade from test1_1 with check option; Query OK, 0 rows affected (0.00 sec) mysql> select * from view_test1_1; +----+--------+-------+ | id | name | grade | +----+--------+-------+ | 1 | 超峰 | 99 | | 2 | b | 90 | | 3 | c | 95 | | 4 | a | 85 | | 5 | chen | 90 | | 6 | NULL | NULL | +----+--------+-------+ 6 rows in set (0.00 sec)
请注意，修改视图等操作需要用户有DROP权限
三、更新视图
更新视图是指通过视图来插入、更新、删除表中的数据，因为视图是一个虚拟表，其中没有数据，通过视图更新时，都是转换到实际表来更新的。
那么更新视图的数据后对实际表产生影响，所以就有两种情况：一是更新了视图后，实际表数据也更新，二是实际表更新了数据，视图中的数据也会更新。
1、先来讨论第一种情况：
mysql> select * from view_test1_1; +----+--------+-------+ | id | name | grade | +----+--------+-------+ | 1 | 超峰 | 99 | | 2 | b | 90 | | 3 | c | 95 | | 4 | a | 85 | | 5 | chen | 90 | | 6 | NULL | NULL | +----+--------+-------+ 6 rows in set (0.00 sec) mysql> update view_test1_1 set name='chao' where id=4; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from view_test1_1; +----+--------+-------+ | id | name | grade | +----+--------+-------+ | 1 | 超峰 | 99 | | 2 | b | 90 | | 3 | c | 95 | | 4 | chao | 85 | | 5 | chen | 90 | | 6 | NULL | NULL | +----+--------+-------+ 6 rows in set (0.00 sec) mysql> select * from test1_1; +----+--------+-------+ | id | name | grade | +----+--------+-------+ | 1 | 超峰 | 99 | | 2 | b | 90 | | 3 | c | 95 | | 4 | chao | 85 | | 5 | chen | 90 | | 6 | NULL | NULL | +----+--------+-------+ 6 rows in set (0.00 sec)
这个例子中，我是先更新视图，然后查询视图中的数据已经更新了，后来查询实际表数据也更新了
2、看第二种情况
mysql> select * from test1_1; +----+--------+-------+ | id | name | grade | +----+--------+-------+ | 1 | 超峰 | 99 | | 2 | b | 90 | | 3 | c | 95 | | 4 | chao | 85 | | 5 | chen | 90 | | 6 | NULL | NULL | +----+--------+-------+ 6 rows in set (0.00 sec) mysql> update test1_1 set name='feng',grade=93 where id=6; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from test1_1; +----+--------+-------+ | id | name | grade | +----+--------+-------+ | 1 | 超峰 | 99 | | 2 | b | 90 | | 3 | c | 95 | | 4 | chao | 85 | | 5 | chen | 90 | | 6 | feng | 93 | +----+--------+-------+ 6 rows in set (0.00 sec) mysql> select * from view_test1_1; +----+--------+-------+ | id | name | grade | +----+--------+-------+ | 1 | 超峰 | 99 | | 2 | b | 90 | | 3 | c | 95 | | 4 | chao | 85 | | 5 | chen | 90 | | 6 | feng | 93 | +----+--------+-------+ 6 rows in set (0.00 sec)
插入视图中数据
mysql> select * from view_test1_1; +----+--------+-------+ | id | name | grade | +----+--------+-------+ | 1 | 超峰 | 99 | | 2 | b | 90 | | 3 | c | 95 | | 4 | chao | 85 | | 5 | chen | 90 | | 6 | feng | 93 | +----+--------+-------+ 6 rows in set (0.00 sec) mysql> insert into view_test1_1 values (7,'wang',77); Query OK, 1 row affected (0.00 sec) mysql> select * from view_test1_1; +----+--------+-------+ | id | name | grade | +----+--------+-------+ | 1 | 超峰 | 99 | | 2 | b | 90 | | 3 | c | 95 | | 4 | chao | 85 | | 5 | chen | 90 | | 6 | feng | 93 | | 7 | wang | 77 | +----+--------+-------+ 7 rows in set (0.00 sec) mysql> select * from test1_1; +----+--------+-------+ | id | name | grade | +----+--------+-------+ | 1 | 超峰 | 99 | | 2 | b | 90 | | 3 | c | 95 | | 4 | chao | 85 | | 5 | chen | 90 | | 6 | feng | 93 | | 7 | wang | 77 | +----+--------+-------+ 7 rows in set (0.00 sec)
这个例子是演示
删除视图数据
mysql> select * from view_test1_1; +----+--------+-------+ | id | name | grade | +----+--------+-------+ | 1 | 超峰 | 99 | | 2 | b | 90 | | 3 | c | 95 | | 4 | chao | 85 | | 5 | chen | 90 | | 6 | feng | 93 | | 7 | wang | 77 | +----+--------+-------+ 7 rows in set (0.00 sec) mysql> select * from test1_1; +----+--------+-------+ | id | name | grade | +----+--------+-------+ | 1 | 超峰 | 99 | | 2 | b | 90 | | 3 | c | 95 | | 4 | chao | 85 | | 5 | chen | 90 | | 6 | feng | 93 | | 7 | wang | 77 | +----+--------+-------+ 7 rows in set (0.00 sec) mysql> delete from view_test1_1 where id = 7; Query OK, 1 row affected (0.01 sec) mysql> select * from view_test1_1; +----+--------+-------+ | id | name | grade | +----+--------+-------+ | 1 | 超峰 | 99 | | 2 | b | 90 | | 3 | c | 95 | | 4 | chao | 85 | | 5 | chen | 90 | | 6 | feng | 93 | +----+--------+-------+ 6 rows in set (0.00 sec) mysql> select * from test1_1; +----+--------+-------+ | id | name | grade | +----+--------+-------+ | 1 | 超峰 | 99 | | 2 | b | 90 | | 3 | c | 95 | | 4 | chao | 85 | | 5 | chen | 90 | | 6 | feng | 93 | +----+--------+-------+ 6 rows in set (0.00 sec)
当删除视图数据的时候，我们发现实际表的数据也删除了。
四、不能更新的视图
上面的例子中我们演示了插入、更新、删除师徒数据，他们都对实际表产生了影响，所以说更新视图时，实际上更新的是实际表的记录，但是并不是所有的视图都可以更新。下面列举几种情况
1、创建视图时，视图中包含一些函数，比如sum()、count() 、max() 、min()
2、视图中包含关键字 UNION、UNION ALL 、DISTINCT、GROUP BY、HAVING
3、创建常量视图
比如：
mysql> create view view_common AS select 'chaofeng' as name; Query OK, 0 rows affected (0.00 sec) mysql> select * from view_common；+----------+ | name | +----------+ | chaofeng | +----------+ 1 row in set (0.00 sec)
4、视图中select语句包含子查询
5、由不可更新的视图导出的视图
6、创建视图时，ALGORITHM为TEMPTABLE类型
7、视图对于的表存在没有默认值的列，而且该列没有包含在视图里
五、删除视图
删除视图指的是删除数据库中已存在的视图，也就是说我们删除的是视图的定义，而不是删除了数据。
语法：DROP VIEW VIEW_NAME
mysql> show tables; +-----------------+ | Tables_in_test1 | +-----------------+ | test1_1 | | test1_2 | | test1_3 | | test1_4 | | test1_5 | | view_common | | view_test1_1 | +-----------------+ 7 rows in set (0.00 sec) mysql> DROP VIEW view_test1_1; Query OK, 0 rows affected (0.00 sec) mysql> DROP VIEW view_common; Query OK, 0 rows affected (0.00 sec) mysql> show tables; +-----------------+ | Tables_in_test1 | +-----------------+ | test1_1 | | test1_2 | | test1_3 | | test1_4 | | test1_5 | +-----------------+ 5 rows in set (0.00 sec)
MySQL中视图和表的区别及联系是什么？
两者的区别：
1、视图时按照SQL语句生成的一个虚拟表。
2、视图不占实际的物理空间，而表中的记录需要占用物理空间
3、建立和删除视图只影响视图本身，不会影响实际的记录。而更新表会影响实际的记录
两者的联系：
1、视图是建立在实际表之上建立的表，其字段和记录都来自实际表，视图依赖基本表存在
2、一个视图对应一个基本表，也可以对应多个基本表
3、视图时基本表的抽象，在逻辑意义上建立的新关系。
