一、视图是由数据库中的一个表或多个表导出的虚拟表。其作用是方便用户对数据的操作。

1.视图的概念

2.视图的作用

二、创建视图

创建视图是指在已经存在的数据表上建立视图。视图可以建立在一张表中，也可以建立在多张表中。

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.01 sec)

结果中两列的值都为Y，这表明root用户具有Select(查看)和Create_view(创建视图)的权限。

2.创建视图

mysql> SELECT * FROM tb_bookinfo;

+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+

| barcode | bookname | typeid | author | ISBN | price | page | bookcase | inTime | del | id |

+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+

| 17120107 | Java King | 3 | LianJiang | 115 | 49.80 | 350 | 1 | 2018-04-17 | 0 | 1 |

| 17120108 | Lian | 1 | QiaoJiang | 116 | 50.00 | 351 | 2 | 2018-04-18 | 0 | 2 |

| 17120109 | Tian King | 2 | TianJiang | 117 | 51.10 | 352 | 3 | 2018-04-19 | 0 | 3 |

+----------+-----------+--------+-----------+------+-------+------+----------+------------+------+----+

3 rows in set (0.00 sec)

mysql> SELECT * FROM tb_borrow;

+----+----------+--------+------------+------------+----------+--------+

| id | readerid | bookid | borrowTime | backTime | operator | ifback |

+----+----------+--------+------------+------------+----------+--------+

| 1 | 1 | 1 | 2018-04-17 | 2018-04-20 | mr | 1 |

| 2 | 1 | 2 | 2018-04-16 | 2018-04-21 | mr | 0 |

+----+----------+--------+------------+------------+----------+--------+

2 rows in set (0.00 sec)

mysql> CREATE VIEW

-> v_book(barcode,bookname,author,price,bookcase,borrowTime,backTime)

-> AS SELECT barcode,bookname,author,price,bookcase,borrowTime,backTime

-> FROM tb_bookinfo AS b,tb_borrow AS t WHERE b.typeid=t.id;

Query OK, 0 rows affected (0.53 sec)

mysql> SELECT * FROM v_book;

+----------+-----------+-----------+-------+----------+------------+------------+

| barcode | bookname | author | price | bookcase | borrowTime | backTime |

+----------+-----------+-----------+-------+----------+------------+------------+

| 17120108 | Lian | QiaoJiang | 50.00 | 2 | 2018-04-17 | 2018-04-20 |

| 17120109 | Tian King | TianJiang | 51.10 | 3 | 2018-04-16 | 2018-04-21 |

+----------+-----------+-----------+-------+----------+------------+------------+

2 rows in set (0.00 sec)

3.创建视图的注意事项

三、视图操作

1.查看视图

(1)DESCRIBE语句

mysql> DESC v_book;

+------------+------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+------------+------------------+------+-----+---------+-------+

| barcode | varchar(30) | YES | | NULL | |

| bookname | varchar(70) | YES | | NULL | |

| author | varchar(30) | YES | | NULL | |

| price | float(8,2) | YES | | NULL | |

| bookcase | int(10) unsigned | YES | | NULL | |

| borrowTime | date | YES | | NULL | |

| backTime | date | YES | | NULL | |

+------------+------------------+------+-----+---------+-------+

7 rows in set (0.00 sec)

(2)SHOW TABLE STATUS语句查看视图的信息，这里说明视图为虚拟表，与普通表是有区别的。

mysql> SHOW TABLE STATUS LIKE 'v_book'\G

*************************** 1. row ***************************

Name: v_book

Engine: NULL

Version: NULL

Row_format: NULL

Rows: NULL

Avg_row_length: NULL

Data_length: NULL

Max_data_length: NULL

Index_length: NULL

Data_free: NULL

Auto_increment: NULL

Create_time: NULL

Update_time: NULL

Check_time: NULL

Collation: NULL

Checksum: NULL

Create_options: NULL

Comment: VIEW

1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'tb_bookinfo'\G

*************************** 1. row ***************************

Name: tb_bookinfo

Engine: InnoDB

Version: 10

Row_format: Dynamic

Rows: 3

Avg_row_length: 5461

Data_length: 16384

Max_data_length: 0

Index_length: 0

Data_free: 0

Auto_increment: NULL

Create_time: 2018-04-17 15:17:13

Update_time: 2018-04-17 16:51:30

Check_time: NULL

Collation: latin1_swedish_ci

Checksum: NULL

Create_options:

Comment:

1 row in set (0.00 sec)

(3)SHOW CREATE VIEW语句查看视图的详细定义

mysql> SHOW CREATE VIEW v_book\G

*************************** 1. row ***************************

View: v_book

Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_book` AS select `b`.`barcode` AS `barcode`,`b`.`bookname` AS `bookname`,`b`.`author` AS `author`,`b`.`price` AS `price`,`b`.`bookcase` AS `bookcase`,`t`.`borrowTime` AS `borrowTime`,`t`.`backTime` AS `backTime` from (`tb_bookinfo` `b` join `tb_borrow` `t`) where (`b`.`typeid` = `t`.`id`)

character_set_client: gbk

collation_connection: gbk_chinese_ci

1 row in set (0.00 sec)

2.修改视图

修改视图是指修改数据库中已存在的表的定义。当基本表的某些字段发生变化时，可以通过修改视图来保持视图和基本表之间一致。

(1)CREATE OR REPLACE VIEW语句在视图已经存在的情况下，对视图进行修改；视图不存在时，可以创建视图。

mysql> CREATE OR REPLACE VIEW

-> v_book(barcode,bookname,borrowTime,backTime)

-> AS SELECT barcode,bookname,borrowTime,backTime

-> FROM tb_bookinfo AS b,tb_borrow AS t WHERE b.typeid=t.id;

Query OK, 0 rows affected (0.01 sec)

mysql> DESC v_book;

+------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+------------+-------------+------+-----+---------+-------+

| barcode | varchar(30) | YES | | NULL | |

| bookname | varchar(70) | YES | | NULL | |

| borrowTime | date | YES | | NULL | |

| backTime | date | YES | | NULL | |

+------------+-------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

(2)ALTER VIEW语句改变了视图的定义，包括被索引视图，但不影响所依赖的存储过程或触发器。

mysql> ALTER VIEW

-> v_book(barcode,borrowTime,backTime)

-> AS SELECT barcode,borrowTime,backTime

-> FROM tb_bookinfo AS b,tb_borrow AS t WHERE b.typeid=t.id

-> WITH CHECK OPTION;

Query OK, 0 rows affected (0.00 sec)

mysql> DESC v_book;

+------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+------------+-------------+------+-----+---------+-------+

| barcode | varchar(30) | YES | | NULL | |

| borrowTime | date | YES | | NULL | |

| backTime | date | YES | | NULL | |

+------------+-------------+------+-----+---------+-------+

3 rows in set (0.00 sec)

3.更新视图

对视图的更新其实就是对表的更新，更新视图是通过视图来插入、更新和删除表中的数据。因为视图是一个虚拟表，其中没有数据，通过视图更新时，都是转换到基本表来更新。更新视图时，只能更新权限范围内的数据，超出了范围，就不能更新。

(1)更新视图，同时也把原来的表更新了

mysql> SELECT * FROM v_book;

+----------+------------+------------+

| barcode | borrowTime | backTime |

+----------+------------+------------+

| 17120108 | 2018-04-17 | 2018-04-20 |

| 17120109 | 2018-04-16 | 2018-04-21 |

+----------+------------+------------+

2 rows in set (0.00 sec)

mysql> UPDATE v_book SET borrowTime='2018-04-19' WHERE barcode='17120108';

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM v_book;

+----------+------------+------------+

| barcode | borrowTime | backTime |

+----------+------------+------------+

| 17120108 | 2018-04-19 | 2018-04-20 |

| 17120109 | 2018-04-16 | 2018-04-21 |

+----------+------------+------------+

2 rows in set (0.00 sec)

mysql> SELECT * FROM tb_borrow;

+----+----------+--------+------------+------------+----------+--------+

| id | readerid | bookid | borrowTime | backTime | operator | ifback |

+----+----------+--------+------------+------------+----------+--------+

| 1 | 1 | 1 | 2018-04-19 | 2018-04-20 | mr | 1 |

| 2 | 1 | 2 | 2018-04-16 | 2018-04-21 | mr | 0 |

+----+----------+--------+------------+------------+----------+--------+

2 rows in set (0.00 sec)

(2)更新视图的限制

a.视图中包含COUNT()、SUM()、MAX()、MIN()等函数

b.视图中包含UNION、UNION ALL、DISTINCT、GROUP BY和HAVIG等关键字

c.常量视图

d.视图中的SELECT中包含子查询

f.由不可更新的视图导出的视图

g.创建视图时，ALGORITHM为TEMPTABLE类型

h.视图对应的表上存在没有默认值的列，而且该列没有包含在视图里。

4.删除视图

mysql> DROP VIEW IF EXISTS v_book;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM v_book;

ERROR 1146 (42S02): Table 'db_library.v_book' doesn't exist