MySQL（五）视图的操作

1.为什么使用视图：
    为了提高复杂SQL语句的复用性和表操作的安全性（例如：工资字段不想展示给所有能查看该查询结果的人），MySQL提供了视图特性。所谓视图，本质上是一种虚拟表，其内容与真实的表相似，包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储数据值的形式存在。行和列的数据来自定义视图的查询所引用的基本表，并且在具体使用视图时动态生成。
    视图有如下特点;
      1. 视图的列可以来自不同的表，是表的抽象和逻辑意义上建立的新关系。
      2. 视图是由基本表（实表）产生的表（虚表）。
      3. 视图的建立和删除不影响基本表。
      4. 对视图内容的更新（添加、删除和修改）直接影响基本表。
      5. 当视图来自多个基本表时，不允许添加和删除数据。
2.创建视图：
    在创建视图时，首先要确保拥有CREATE VIEW的权限，并且同时确保对创建视图所引用的表也具有相应的权限。
  2.1 创建视图的语法形式：
    虽然视图可以被看成是一种虚拟表，但是其在物理上是不存在的，即数据库管理系统没有专门的位置为视图存储数据。根据视图的概念发现其数据来源于查询语句，因此创建视图的语法为：

CREATE VIEW view_name AS 查询语句//说明：和创建表一样，视图名不能和表名、也不能和其他视图名重名。视图的功能实际就是封装了复杂的查询语句。

示例：use zhaojd_test;    //选择一个自己创建的库create table t_product(  //创建表    id int primary key,    pname varchar(20),    price decimal(8,2));  insert into t_product values(1,'apple',6.5); //向表中插入数据insert into t_product values(2,'orange',3);  //向表中插入数据create view view_product as select id,name from t_product; //创建视图select * from view_product;结果为：+------+--------+| id   | name   |+------+--------+| 1    | apple  || 2    | orange |+------+--------+//其实在创建视图时实际代码里写的是一个表查询语句，只不过把这个查询语句封装起来重新起了一个名字，方便可以重复使用。//再者，安全性方面可以隐藏一些不希望看到的字段，比如这里的价格字段。//注意：在SQL语句的命名规范中，视图一般以view_xxx或者v_xxx的样式来命名。视图的查询语句和表的查询语句相同。

  2.2 创建各种视图：
    由于视图的功能实际上是封装查询语句，那么是不是任何形式的查询语句都可以封装在视图中呢？
    2.2.1 封装实现查询常量语句的视图（常量视图）：

示例：mysql> create view view_test1 as select 3.1415926;Query OK, 0 rows affected (0.07 sec)mysql> select * from view_test1;+-----------+| 3.1415926 |+-----------+| 3.1415926 |+-----------+1 row in set (0.00 sec)

    2.2.2 封装使用聚合函数（SUM、MIN、MAX、COUNT等）查询语句的视图：

示例：首先准备需要用到的两张表及其初始化数据;CREATE TABLE t_group(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20));CREATE TABLE t_student(    id INT PRIMARY KEY AUTO_INCREMENT,    NAME VARCHAR(20),    sex CHAR(1),    group_id INT,    FOREIGN KEY (group_id) REFERENCES t_group (id));//t_group表中插入数据INSERT INTO t_group (NAME) VALUES('group_1');INSERT INTO t_group (NAME) VALUES('group_2');INSERT INTO t_group (NAME) VALUES('group_3');INSERT INTO t_group (NAME) VALUES('group_4');INSERT INTO t_group (NAME) VALUES('group_5');//t_student表中插入数据INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_1','M',1);INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_2','M',1);INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_3','M',2);INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_4','W',2);INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_5','W',2);INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_6','W',2);INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_7','M',3);INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_8','W',4);INSERT INTO t_student (NAME,sex,group_id) VALUES('zjd_9','W',4);================================================================mysql> create view view_test2 as select count(name) from t_student;Query OK, 0 rows affected (0.71 sec)mysql> select * from view_test2;+-------------+| count(name) |+-------------+|           9 |+-------------+1 row in set (0.01 sec)

    2.2.3 封装了实现排序功能（ORDER BY）查询语句的视图：

示例：mysql> create view view_test3 as select name from t_student order by id desc;Query OK, 0 rows affected (0.06 sec)mysql> select * from view_test3;+-------+| name  |+-------+| zjd_9 || zjd_8 || zjd_7 || zjd_6 || zjd_5 || zjd_4 || zjd_3 || zjd_2 || zjd_1 |+-------+9 rows in set (0.00 sec)

    2.2.4 封装了实现表内连接查询语句的视图：

示例：（第二组学生的姓名）mysql> create view view_test5 as select s.name from t_student s,t_group g where s.group_id=g.id and g.id=2;Query OK, 0 rows affected (0.07 sec)mysql> select * from view_test5;+-------+| name  |+-------+| zjd_3 || zjd_4 || zjd_5 || zjd_6 |+-------+4 rows in set (0.00 sec)

    2.2.5 封装了实现表外连接（LEFT JOIN和RIGHT JOIN）查询语句的视图：

示例：（第二组学生姓名）mysql> create view view_test6 as select s.name from t_student s left join t_group g on s.group_id=g.id where g.id=2;Query OK, 0 rows affected (0.09 sec)mysql> select * from view_test6;+-------+| name  |+-------+| zjd_3 || zjd_4 || zjd_5 || zjd_6 |+-------+4 rows in set (0.01 sec)

    2.2.6 封装了实现子查询相关查询语句的视图：

示例：mysql> create view view_test7 as select s.name from t_student s where s.id in(select id from t_group);Query OK, 0 rows affected (0.08 sec)mysql> select * from view_test7;+-------+| name  |+-------+| zjd_1 || zjd_2 || zjd_3 || zjd_4 || zjd_5 |+-------+5 rows in set (0.00 sec)

    2.2.7 封装了实现记录联合（UNION和UNION ALL）查询语句的视图：

mysql> create view view_test8 as select id,name from t_student union all select id,name from t_group;Query OK, 0 rows affected (0.08 sec)mysql> select * from view_test8;+----+---------+| id | name    |+----+---------+|  1 | zjd_1   ||  2 | zjd_2   ||  3 | zjd_3   ||  4 | zjd_4   ||  5 | zjd_5   ||  6 | zjd_6   ||  7 | zjd_7   ||  8 | zjd_8   ||  9 | zjd_9   ||  1 | group_1 ||  2 | group_2 ||  3 | group_3 ||  4 | group_4 ||  5 | group_5 |+----+---------+14 rows in set (0.01 sec)

3.查看视图：
  3.1 SHOW TABLES语句查看视图名：
    执行SHOW TABLES 语句时不仅可以显示表的名字，同时也是显示出视图的名字。

示例：mysql> show tables;+------------------+| Tables_in_zhaojd |+------------------+| t_group          || t_product        || t_student        || v_product        || view_test1       || view_test2       || view_test3       || view_test4       || view_test5       || view_test6       || view_test8       |+------------------+11 rows in set (0.00 sec)

  3.2 SHOW TABLE STATUS语句查看视图详细信息：
    和SHOW TABLES语句一样，SHOW TABLE STATUS语句不仅会显示表的详细信息，同时也会显示视图的详细信息。

语法如下：SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']//参数db_name用来设置数据库，SHOW TABLES STATUS表示将显示所设置库的表和视图的详细信息。//设置LIKE关键字，可以查看某一个具体表或视图的详细信息。例如： SHOW TABLE STATUS FROM zhaojd LIKE 't_group' \G示例：mysql> show table status from zhaojd \G*************************** 1. row ***************************           Name: t_group         Engine: InnoDB        Version: 10     Row_format: Compact           Rows: 5 Avg_row_length: 3276    Data_length: 16384Max_data_length: 0   Index_length: 0      Data_free: 7340032 Auto_increment: 6    Create_time: 2016-08-19 16:26:06    Update_time: NULL     Check_time: NULL      Collation: utf8_general_ci       Checksum: NULL Create_options:        Comment:=============================================================            Name: view_test8         Engine: NULL        Version: NULL     Row_format: NULL           Rows: NULL Avg_row_length: NULL    Data_length: NULLMax_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

  3.3 SHOW CREATE VIEW语句查看视图定义信息：

语法为： SHOW CREATE VIEW viewname; 示例： mysql> show create view view_test8 \G*************************** 1. row ***************************View: view_test8Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `view_test8` AS select `t_student`.`id` AS `id`,`t_student`.`NAME` AS `name` from `t_student` union all select `t_group`.`id` AS `id`,`t_group`.`NAME` AS `name` from `t_group`character_set_client: utf8collation_connection: utf8_general_ci1 row in set (0.00 sec)//根据执行结果可以发现，SHOW CREATE VIEW语句返回两个字段，分别为表示视图名的View字段和关于视图定义的Create view字段。

  3.4 DESCRIBE | DESC 语句查看视图定义信息：

语法为：    DESCRIBE | DESC viewname;示例：mysql> desc view_test8;+-------+-------------+------+-----+---------+-------+| Field | Type        | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id    | int(11)     | NO   |     | 0       |       || name  | varchar(20) | YES  |     | NULL    |       |+-------+-------------+------+-----+---------+-------+2 rows in set (0.02 sec) 

  3.5 通过系统表查看视图信息：
    当MySQL安装成功后，会自动创建系统数据库infomation_schema。在该数据库中存在一个包含视图信息的表格，可以通过查看表格views来查看所有视图的相关信息。

示例：mysql> use information_schema;Database changedmysql> select * from views where table_name='view_test8' \G*************************** 1. row ***************************       TABLE_CATALOG: def        TABLE_SCHEMA: zhaojd          TABLE_NAME: view_test8     VIEW_DEFINITION: select `zhaojd`.`t_student`.`id`AS`id`,`zhaojd`.`t_student`.`NAME` AS `name` from `zhaojd`.`t_student` union all select `zhaojd`.`t_group`.`id` AS`id`,`zhaojd`.`t_group`.`NAME` AS `name` from `zhaojd`.`t_group`        CHECK_OPTION: NONE        IS_UPDATABLE: NO             DEFINER: root@localhost       SECURITY_TYPE: DEFINERCHARACTER_SET_CLIENT: utf8COLLATION_CONNECTION: utf8_general_ci1 row in set (0.01 sec)

4.删除视图：
    在删除视图时首先要确保拥有删除视图的权限。

语法为：DROP VIEW view_name [,view_name] ......//从语法可以看出，DROP VIEW一次可以删除多个视图示例：mysql> use zhaojd;Database changedmysql> show tables;+------------------+| Tables_in_zhaojd |+------------------+| t_group          || t_product        || t_student        || v_product        || view_test1       || view_test2       || view_test3       || view_test4       || view_test5       || view_test6       || view_test8       |+------------------+11 rows in set (0.00 sec)mysql> drop view view_test1, view_test2;Query OK, 0 rows affected (0.01 sec)mysql> show tables;+------------------+| Tables_in_zhaojd |+------------------+| t_group          || t_product        || t_student        || v_product        || view_test3       || view_test4       || view_test5       || view_test6       || view_test8       |+------------------+9 rows in set (0.00 sec)

5.修改视图：
  5.1 CREATE OR REPLACE VIEW语句修改视图：
    对于已经创建好的表，尤其是已经有大量数据的表，通过先删除，然后再按照新的表定义重新建表的方式来修改表，需要做很多额外的工作，例如数据的重载等。可是对于视图来说，由于是“虚表”，并没有存储数据，所以完全可以通过该方式来修改视图。

实现思路就是：先删除同名的视图，然后再根据新的需求创建新的视图即可。DROP VIEW view_name; CREATE VIEW view_name as 查询语句;

    但是如果每次修改视图，都是先删除视图，然后再次创建一个同名的视图，则显得非常麻烦。于是MySQL提供了更方便的实现替换的创建视图的语法，完整语法为：

CREATE OR REPLACE VIEW view_name as 查询语句；

  5.2 ALTER语句修改视图：

语法为：ALTER VIEW view_name as 查询语句;

6.利用视图操作基本表：
    再MySQL中可以通常视图检索基本表数据，这是视图最基本的应用，除此之后还可以通过视图修改基本表中的数据。
  6.1检索（查询）数据：
    通过视图查询数据，与通过表进行查询完全相同，只不过通过视图查询表更安全，更简单实用。只需要把表名换成视图名即可。
  6.2利用视图操作基本表数据：
    由于视图是“虚表”，所以对视图数据进行的更新操作，实际上是对其基本表数据进行的更新操作。在具体更新视图数据时，需要注意以下两点;
    1. 对视图数据进行添加、删除、修改直接影响基本表。
    2. 视图来自于多个基本表时，不允许添加、删除数据。
    视图中的添加数据操作、删除数据操作、更新数据操作的语法同表完全相同。只是将表名换成视图名即可。