vlambda博客
学习文章列表

MySQL视图了解一下

简介

MySQL视图是一种虚拟的表,本身不包含任何数据,可以看作是对SQL查询的封装,它的数据都是动态执行SQL查询的结果。

MySQL视图常见应用主要有以下两种:

  1. 重用SQL语句,简化复杂SQL的操作。

  2. 保护数据,可以给用户授予表特定部分的访问权限,而不是整个表的访问权限。

视图的使用

创建视图

语法:CREATE VIEW <视图名> AS <SELECT语句>

CREATE VIEW student_class_view AS SELECT
 t.*, c.class_name
FROM
 tb_student t
LEFT JOIN tb_class c ON t.class_id = c.class_id;

如上创建了一个名为student_class_view的视图,它包含一个tb_student表和tb_class表的联结查询,现在我们就可以操作表一样操作student_class_view视图了:

SELECT * FROM student_class_view;

输出:

+----+------+-----+-------------+-----------+----------+------------+
| id | name | age | phone       | address   | class_id | class_name |
+----+------+-----+-------------+-----------+----------+------------+
|  1 | 小明 |  18 | 188xxxx1234 | xxxxxxxxx |        1 | IT2班      |
|  2 | 小米 |  28 | 188xxxx1234 | xxxxxxxxx |        2 | IT3班      |
|  3 | 小看 |  28 | 188xxxx1234 | xxxxxxxxx |        3 | IT1班      |
|  4 | 小阿 |  38 | 188xxxx1234 | xxxxxxxxx |        3 | IT1班      |
+----+------+-----+-------------+-----------+----------+------------+

CREATE VIEW完整语法:

CREATE
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCALCHECK OPTION]

查看创建视图的语句

语法:SHOW CREATE VIEW <视图名>

SHOW CREATE VIEW student_class_view;

输出:

+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View               | Create View                                                                                                                                                                                                                                                                                                                                                                    | character_set_client | collation_connection |
+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| student_class_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `student_class_view` AS select `t`.`id` AS `id`,`t`.`name` AS `name`,`t`.`age` AS `age`,`t`.`phone` AS `phone`,`t`.`address` AS `address`,`t`.`class_id` AS `class_id`,`c`.`class_name` AS `class_name` from (`tb_student` `t` left join `tb_class` `c` on((`t`.`class_id` = `c`.`class_id`))) | utf8                 | utf8_general_ci      |
+--------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

Create View列为该视图的创建语句。

删除视图

语法:DROP VIEW <视图名>

DROP VIEW student_class_view;

再次查询该视图,视图已被删除:

[SQL]SELECT * FROM student_class_view;
[Err] 1146 - Table 'resource.student_class_view' doesn't exist

DROP VIEW完整语法:

DROP VIEW [IF EXISTS]
    view_name [, view_name] ...
    [RESTRICT | CASCADE]

修改视图定义

修改视图定义可以使用以下方式:

1.先DROP删除视图,然后重新使用CREATE VIEW创建视图。

2.使用CREATE OR REPLACE VIEW,如果原视图不存在,则会创建新的视图,如果原视图存在,则会替换原视图。

CREATE OR REPLACE VIEW student_class_view AS 
SELECT
 ...

3.使用ALTER VIEW,原视图必须要存在。

ALTER VIEW student_class_view AS 
SELECT
 ...

ALTER VIEW完整语法:

ALTER
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCALCHECK OPTION]

视图的更新

更新视图是指对视图使用INSERT、UPDATE、DELETE语句,对视图的更新都是直接更新基础表的数据。

「并非所有视图都能更新,如果MySQL不能确定要更新的基础数据,则不允许更新」。

如果视图定义中包含以下操作,则不允许更新:

  • 分组
  • 联结
  • 子查询
  • 聚集函数
  • DISTINCT
  • 导出(计算)列

更新视图的限制挺多,其实影响不大,因为「MySQL视图主要用于数据查询」,很少会直接更新视图。

参考:MySQL必知必会