mysql视图学习笔记记录

创建视图

在MySQL中，创建视图的完整语法如下：

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

其对应的语法变量信息如下：

[OR REPLACE]
    中括号中的OR REPLACE关键字是可选的。如果当前数据库中已经存在指定名称的视图时，没有该关键字，将会提示错误信息；如果使用了OR REPLACE关键字，则当前正在创建的视图会覆盖掉原来同名的视图。
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    ALGORITHM子句是可选的，它表示使用何种算法来处理视图。此外，它并不属于标准SQL的一部分，而是MySQL对标准SQL进行的功能扩展。ALGORITHM可以设置三个值：MERGE、TEMPTABLE或UNDEFINED。如果没有ALGORITHM子句，则默认值为UNDEFINED(未定义的)。
    对于MERGE，会将引用视图的语句的文本与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分。

    对于TEMPTABLE，视图的结果将被置于临时表中，然后使用它执行语句。

    对于UNDEFINED，MySQL将选择所要使用的算法。如果可能，它倾向于MERGE而不是TEMPTABLE，这是因为MERGE通常更有效，而且如果使用了临时表，视图是不可更新的。

    之所以提供TEMPTABLE选项，是因为TEMPTABLE在创建临时表之后、并在完成语句处理之前，能够释放基表上的锁定。与MERGE算法相比，锁定释放的速度更快，这样，使用视图的其他客户端不会被屏蔽过长时间。

    此外，MERGE算法要求视图中的行和基表中的行具有一对一的关系。如果视图包含聚合函数(SUM(), MIN(), MAX(), COUNT()等)、DISTINCT、GROUP BY、HAVING、UNION或UNION ALL、没有基表的引用文字值(例如：SELECT 'hello';)等结构中的任何一种，将失去一对一的关系，此时必须使用临时表取而代之。
[(column_list)]
    (column_list)用于自定义视图中各个字段的名称。如果没有该命令选项，那么通过视图查询到的各个字段的名称和视图所使用到的数据表的字段名称保持一致。下面是一个常见的用于创建视图的SQL语句：

代码如下
CREATE OR REPLACE VIEW v_user     AS     SELECT id, username FROM user;

    由于user表中的字段名称为id和username，因此视图v_user中的两个字段名称也分别默认为id和username。现在，我们将视图v_user中的字段名称分别自定义为uid和uname。

代码如下
CREATE OR REPLACE VIEW v_user (uid, uname)     AS     SELECT id, username FROM user;

select_statement
    select_statement用于指定视图的内容定义。简而言之，这里就是用户自定义的一个SELECT语句。
[WITH [CASCADED | LOCAL] CHECK OPTION]
    该选项中的CASCADED为默认值，LOCAL CHECK OPTION用于在可更新视图中防止插入或更新行。由于此选项一般不使用，因此不再赘述，具体信息请参考MySQL官方网站上的相关信息。

1.使用举例
Eg. 本例创建一个产品表（product）和一个购买记录表（purchase），再通过视图purchase_detail查询出购买的详细信息。

代码如下

CREATE TABLE product ( product_id INT NOT NULL, name VARCHAR(50) NOT NULL, price DOUBLE NOT NULL ); INSERT INTO product VALUES(1, 'apple ', 5.5); CREATE TABLE purchase ( id INT NOT NULL, product_id INT NOT NULL, qty INT NOT NULL DEFAULT 0, gen_time DATETIME NOT NULL ); INSERT INTO purchase VALUES(1, 1, 10, NOW()); CREATE VIEW purchase_detail AS SELECT product.name as name, product .price as price, purchase.qty as qty, product .price * purchase.qty as total_value from product, purchase where product.product_id = purchase.product_id;

创建成功后，输入：SELECT * FROM purchase_detail;

运行效果如下：

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

| name | price | qty | total_value |

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

| apple | 5.5 | 10 | 55 |

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

1 row in set (0.01 sec)

1.注意事项

创建视图存在如下注意事项：

（1） 运行创建视图的语句需要用户具有创建视图（CRATE VIEW）的权限，若加了[OR REPLACE]时，还需要用户具有删除视图（DROP VIEW）的权限；

（2） SELECT语句不能包含FROM子句中的子查询；

（3） SELECT语句不能引用系统或用户变量；

（4） SELECT语句不能引用预处理语句参数；

（5） 在存储子程序内，定义不能引用子程序参数或局部变量；

（6） 在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用CHECK TABLE语句；

（7） 在定义中不能引用TEMPORARY表，不能创建TEMPORARY视图；

（8） 在视图定义中命名的表必须已存在；

（9） 不能将触发程序与视图关联在一起；

（10） 在视图定义中允许使用ORDER BY，但是，如果从特定视图进行了选择，而该视图使用了具有自己ORDER BY的语句，它将被忽略。

删除视图

在MySQL中删除视图的方法非常简单，其详细语法如下：

--删除指定名称的一个或多个视图

代码如下
DROP VIEW [IF EXISTS]  view_name [, view_name2]...

其中，关键字IF EXISTS用于防止因视图不存在而提示出错，此时，只有存在该视图才会执行删除操作。DROP VIEW语句可以一次性删除多个视图，只需要在多个视图名称之间以英文逗号隔开即可。如果多个视图存在于不同的数据库中，不数据当前数据库的视图名称之前还必须加上db_name.前缀。

--删除视图v_user
DROP VIEW v_user;

1.使用举例

Eg1. 删除在前面的小节中创建的视图purchase_detail：DROP VIEW purchase_detail;

Eg2. 删除一个未知的视图：DROP VIEW IF EXISTS test_view;

Eg3. 删除多个视图：DROP VIEW IF EXISTS test_view1, test_view2;

1.注意事项

必须对要删除的一个或多个视图拥有DROP VIEW的权限。

修改视图

请参考创建视图语法中的OR REPLACE关键字，只要具备该关键字的视图创建语句就是修改视图的SQL语句。
查看视图

在MySQL中，show tables不仅可以用于查看当前数据库中存在哪些数据表，同时也可以查看到当前数据库中存在哪些视图。

代码如下
--执行show tables mysql> show tables; --以下是输出结果 +----------------+ | Tables_in_test | +----------------+ | user           | | v_user         | +----------------+ 2 rows in set (0.00 sec)

不过，仅仅使用show tables语句，在输出结果中，我们根本无法区分到底哪些才是视图哪些才是真实的数据表(当然，视图的命名我们可以统一约定以”v_”开头)。此时，我们需要使用命令show full tables，该命令可以列出额外的table_type列，如果对应输出行上该列的值为”VIEW”，则表示这是一个视图。

代码如下
--执行show full tables mysql> show full tables; --以下是输出结果 +----------------+------------+ | Tables_in_test | Table_type | +----------------+------------+ | user           | BASE TABLE | | v_user         | VIEW       | +----------------+------------+ 2 rows in set (0.00 sec)

当我们通过上述命令找到了我们所需要的视图之后，我们可以使用如下命令查看创建该视图的详细语句：

show create view view_name

例如，我们使用该命令查看创建视图v_user的SQL语句：

代码如下

--由于该语句的输出结果较为杂乱，因此使用G命令进行格式化输出 mysql> show create view v_user G; --以下是格式化的输出结果 *************************** 1. row ***************************                 View: v_user          Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_user` AS select `user`.`id` AS `uid`,`user`.`username` AS `uname` from `user` character_set_client: utf8 collation_connection: utf8_general_ci 1 row in set (0.00 sec)

1.使用举例
Eg. 将上一小节中中创建的视purchase_detail进行修改，去掉qty列，语句如下：

代码如下
ALTER VIEW purchase_detail AS SELECT product.name as name, product .price as price, product .price * purchase.qty as total_value from product, purchase where product.product_id = purchase.product_id;

此时通过语句：select * from purchase_detail;对视图进行查询时，结果如下：

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

| name | price | total_value |

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

| apple | 5.5 | 55 |

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