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_1G
*************************** 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、视图时基本表的抽象，在逻辑意义上建立的新关系。