07 MySQL之视图

01-视图的含义

视图是从一个或者多个表中导出的，视图的行为与表非常相似，但视图是一个虚拟表。视图还可以从已经存在的视图的基础上定义。

02-创建视图

# 基本语法格式：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS SELECT_statement [WITH [CASCADED | LOCAL] CHECK OPTION]

CREATE表示创建新视图；REPLACE表示替换已经创建的视图；

ALGORITHM表示视图选择的算法；view_name 为视图的名称；

column_list 为属性列；SELECT_statement表示select语句；WITH[CASCADED | LOCAL] CHECK OPTION 参数表示视图在更新时保证在视图的权限范围之内。

ALGORITHM的取值有3个，分别是 UNDEFINED | MERGE | TEMPTABLE，

UNDEFINED 表示MySQL将自动选择算法；

MERGE 表示将使用的视图语句与视图定义合并起来，使得视图定义的某一部分取代语句对应的部分；

TEMPTABLE 表示将视图的结果存入临时表，然后用临时表来执行语句。

CASCADED | LOCAL 为可选参数，CASCADED 为默认值，表示更新视图时要满足所有相关视图和表的条件；

LOCAL 表示更新视图时满足该视图本身定义的条件即可。

在单表上创建视图：

mysql> mysql> create t (quantity int, price int);

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id:    6

Current database: test_db

Query OK, 0 rows affected (0.15 sec)

mysql> insert into t values (3, 50);

Query OK, 1 row affected (0.01 sec)

mysql> create view view_t as select quantity,price, quantity*price from t;

Query OK, 0 rows affected (0.03 sec)

mysql> select * from views_t;

ERROR 1146 (42S02): Table 'test_db.views_t' doesn't exist

mysql> select * from view_t;

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

| quantity | price | quantity*price |

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

|        3 |    50 |            150 |

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

1 row in set (0.01 sec)

mysql> create view view_t2 (qty, price, total) as select quantity, price, quantity*price from t;

Query OK, 0 rows affected (0.02 sec)

mysql> select * from view_t2;

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

| qty  | price | total |

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

|    3 |    50 |   150 |

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

1 row in set (0.01 sec)

在多表上创建视图

mysql> create table student (s_id int, name varchar(255));

Query OK, 0 rows affected (0.02 sec)

mysql> insert into student values (1, 'liming'), (2, 'wangwu'), (3, 'lisi');

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

mysql> create table stu_info (s_id int, glass varchar(255), address varchar(255));

Query OK, 0 rows affected (0.02 sec)

mysql> insert into stu_info values (1, 'wuban', 'jilin'), (2, 'liuban', 'shandong'), (3, 'qiban', 'beijing');

Query OK, 3 rows affected (0.01 sec)

Records: 3  Duplicates: 0  Warnings: 0

# 创建视图

mysql> create view stu_glass (id, name, glass) as select student.s_id, student.name, stu_info.glass from student, stu_info where student.s_id = stu_info.s_id;

Query OK, 0 rows affected (0.03 sec)

mysql> select * from stu_glass;

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

| id   | name   | glass  |

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

|    1 | liming | wuban  |

|    2 | wangwu | liuban |

|    3 | lisi   | qiban  |

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

3 rows in set (0.01 sec)

03-查看视图

查看视图是查看数据库中已存在的视图的定义。查看视图必须要有 SHOW VIEW 的权限，MySQL数据库下的user 表中保存着这个信息。

查看视图的方法包括：DESCRIBE、SHOW TABLE STATUS 和 SHOW CREATE VIEW。

DESCRIBE、SHOW TABLE STATUS 和 SHOW CREATE VIEW。

# 1、使用DESCRIBE 语句查看视图基本信息

DESCRIBE 视图名;

例：

mysql> DESCRIBE stu_glass;

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

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

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

| id    | int(11)      | YES  |     | NULL    |       |

| name  | varchar(255) | YES  |     | NULL    |       |

| glass | varchar(255) | YES  |     | NULL    |       |

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

3 rows in set (0.01 sec)

mysql> DESC view_t;

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

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

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

| quantity       | int(11)    | YES  |     | NULL    |       |

| price          | int(11)    | YES  |     | NULL    |       |

| quantity*price | bigint(21) | YES  |     | NULL    |       |

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

3 rows in set (0.00 sec)

# 2、使用SHOW TABLE STATUS语句查看视图基本信息

语法如下：

SHOW TABLE STATUS LIKE '视图名';

例：

mysql> SHOW TABLE STATUS like 'view_t' \G

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

           Name: view_t

         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)

# 3、使用 SHOW CREATE VIEW 语句查看视图详细信息

语法如下：

SHOW CREATE VIEW 视图名;

例：

mysql> SHOW CREATE VIEW view_t \G;

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

                View: view_t

         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`skip-grants user`@`skip-grants host` SQL SECURITY DEFINER VIEW `view_t` AS select `t`.`quantity` AS `quantity`,`t`.`price` AS `price`,(`t`.`quantity` * `t`.`price`) AS `quantity*price` from `t`

character_set_client: utf8

collation_connection: utf8_general_ci

1 row in set (0.00 sec)

ERROR:

No query specified

# 4、在views表中查看视图详细信息

在MySQL中，information_schema 数据库下的views表中存储了所有视图的定义。通过对views表的查询，可以查看数据库中所有视图的详细信息，查询语句如下：

SELECT * FROM information_schema.views;

查询结果太长就不在这里显示，结果显示当前以及定义的所有视图的详细信息，在这里也可以看到前面定义的 stu_glass、view_t 视图的详细信息。

04-修改视图

# 1、使用 CREATE OR REPLACE VIEW 语句修改视图

语法如下：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS SELECT_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

修改视图的语句和创建视图的语句是完全一样的。当视图已经存在时，修改语句对视图进行修改；当视图不存在时，创建视图。

例：

mysql> desc view_t;

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

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

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

| quantity       | int(11)    | YES  |     | NULL    |       |

| price          | int(11)    | YES  |     | NULL    |       |

| quantity*price | bigint(21) | YES  |     | NULL    |       |

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

3 rows in set (0.00 sec)

mysql> create or replace view view_t as select * from t;

Query OK, 0 rows affected (0.01 sec)

mysql> desc view_t;

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

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

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

| quantity | int(11) | YES  |     | NULL    |       |

| price    | int(11) | YES  |     | NULL    |       |

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

2 rows in set (0.00 sec)

# 2、使用 ALTER 语句修改视图

语法如下：

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]

VIEW view_name [(column_list)]

AS SELECT_statement

[WITH [CASCADED | LOCAL] CHECK OPTION]

ALTER VIEW view_t AS SELECT quantity from t;

例：

mysql> desc view_t;

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

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

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

| quantity | int(11) | YES  |     | NULL    |       |

| price    | int(11) | YES  |     | NULL    |       |

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

2 rows in set (0.01 sec)

mysql> ALTER VIEW view_t AS SELECT quantity from t;

Query OK, 0 rows affected (0.02 sec)

mysql> desc view_t;

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

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

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

| quantity | int(11) | YES  |     | NULL    |       |

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

1 row in set (0.00 sec)

05-更新视图

1、更新视图是指通过视图来插入、更新、删除表中的数据，因为视图是一个虚拟表，其中没有数据。

2、通过视图更新的时候都是转到基本表上进行更新的，如果对视图增加或者删除记录，实际上是对基本表增加或者删除记录。

# 1、使用 UPDATE 语句更新视图 view_t

UPDATE view_t SET quantity=5;

例：

mysql> select * from view_t;

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

| quantity |

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

|        3 |

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

1 row in set (0.00 sec)

mysql> UPDATE view_t SET quantity=5;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from view_t;

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

| quantity |

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

|        5 |

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

1 row in set (0.00 sec)

# 当视图中包含有如下内容时，视图的更新操作将不能被执行：

（1）视图中不包含基本表中被定义为非空的列。

（2）在定义视图的 SELECT 语句后的字段列表中使用了数学表达式。

（3）在定义视图的SELECT 语句后的字段列表中使用了聚合函数。

（4）在定义视图的SELECT 语句中使用了DISTINCT，UNION，TOP，GROUP BY 或HAVING子句。

06-删除视图

当视图不再需要时，可以将其删除。删除一个或多个使用 DROP VIEW 语句，

语法如下：

DROP VIEW [IF EXISTS]

    view_name [, view_name] ...

    [RESTRICT | CASCADE]

其中，view_name 是要删除的视图名称，可以添加多个需要的视图名称，各个名称之间使用逗号分隔开。

删除视图必须拥有 DROP 权限。

例：

mysql> drop view if exists stu_glass;

Query OK, 0 rows affected (0.01 sec)

巴特西