一、视图是由数据库中的一个表或多个表导出的虚拟表。其作用是方便用户对数据的操作。
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