MySQL视图简介
概述
数据库中关于数据的查询有时非常复杂,例如表连接、子查询等,这种查询编写难度大,很容易出错。另外,在具体操作表时,有时候要求只能操作部分字段。
为了提高复杂 SQL 语句的复用性和表的操作的安全性,MySQL 提供了视图特性。所谓视图,本质上是一种虚拟表,同样包含一系列带有名称的列和行数据。行和列的数据来自自定义视图的查询所引用的基本表,并在具体引用视图时动态生成
视图的特点如下:
- 视图的列可以来自不同的表,是表的抽象和逻辑意义上建立的新关系
- 视图是由基本表(实表)产生的表(虚表)
- 视图的建立和删除不影响基本表
- 对视图内容的更新(增删改)直接影响基本表
- 当视图来自多个基本表,不允许添加和删除数据
创建视图
1. 创建视图的语法形式
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [column_list]
AS SELECT statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
- CREATE 表示创建新的视图,REPLACE 表示替换已经创建的视图
- ALGORITHM 表示视图选择的算法,取值有三种:
- UNDEFINED:MySQL 自动选择算法
- MERGE:将使用的视图语句与视图定义合并起来,使得视图定义的某一部分取代语句对应的部分
- TEMPLATE:表示将视图的结果放入临时表,然后用临时表来执行语句
- view_name 表示视图的名称
- column_list 为属性列
- SELECT statement 表示 SELECT 语句
- 参数 [WITH [CASCADED | LOCAL] CHECK OPTION] 表示视图在更新时保证在视图的权限范围内,CASCADED 与 LOCAL 为可选参数:
- CASCADED 为默认值,表示更新视图时要满足所有相关视图和表的条件
- LOCAL 表示更新视图时满足该视图本身定义的条件即可
该语句要求具有针对视图的 CREATE VIEW 权限,以及针对由 SELECT 语句选择的每一列上的某些权限。对于在 SELECT 语句中其他地方使用的列,必须具有 SELECT 权限,如果还有 OR REPLACE 子句,就必须在视图上具有 DROP 权限
2. 在单表创建视图
示例:在数据库 company 中,由员工表 t_employee 创建出隐藏工资字段 salary 的视图 view_selectemployee
CREATE VIEW view_selectemployee ASSELECT id,name,gender,age,deptno FROM t_employee;
查询视图
SELECT * FROM view_selectemployee;
3. 在多表创建视图
示例:在数据库 company 中,由部门表 t_dept 和员工表 t_employee 创建一个名为 view_dept_employee 的视图
CREATE ALGORITHM=MERGE VIEWview_dept_employee(name,dept,gender,age,loc)
AScSELECT iname,t_dept.deptname,gender,age,t_dept.location
FROM t_employee, t_dept WHERE t_employee.deptno = t_dept.deptno
WITH LOCAL CHECK OPTION;
查看视图
使用 DESCRIBE | DESC 语句查看视图基本信息,因为视图也是一张表,只不过比较特殊
DESCRIBE | DESC viewname
在 MySQL 中,所有视图的定义都存在数据库 information_schema 中的表 views 中,查询 views 表可以得到数据库中所有视图的详细信息
SELECT * FROM information_schema.viewsWHERE table_name = "viewname" G
修改视图
修改视图是指修改数据库中存在的视图,当基本表的某些字段发生变化时,可以通过修改视图来保持与基本表的一致
在 MySQL 中,CREATE OR REPLACE VIEW
语句可以用来修改视图。当视图已经存在,对视图进行修改,否则创建视图
CREATE OR REPLACE [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [column_list]
AS SELECT statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
也可以使用 ALTER 语句修改视图,其语法中的关键字和参数都与创建视图是一样的
ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [column_list]
AS SELECT statement
[WITH [CASCADED | LOCAL] CHECK OPTION]
更新视图
更新视图是指通过视图来插入、更新和删除表中的数据。可以使用 SQL 语句更新视图,达到更新基本表数据。反过来,更新基本表的数据,也会更新视图的数据
但实际上,并不是所有视图都可以更新,以下几种情况是不能更新视图的:
- 视图中包含 SUM()、COUNT()、MAX() 和 MIN() 函数
- 视图中包含 UNION、UNION ALL、DISTINCT、GROUP BY 和 HAVING 等关键字
- 常量视图,例如:
CREATE VIEW view_1 AS SELECT "Rebecca" AS name;
- 包含子查询的视图
- 由不可更新的视图导出的视图
- 创建视图时,ALGORITHM 为 TEMPTABLE 类型
- 视图对应的列存在没有默认值的列,而且该列没有包含在视图里
- 设置了 [WITH [CASCADED | LOCAL] CHECK OPTION] 参数,需要符合对应条件才能更新
删除视图
删除视图是指删除数据库中已存在的视图,不会删除数据
在 MySQL 中,可以使用 DROP VIEW
语句来删除视图,但是用户必须拥有 DROP 权限
DROP VIEW viewname [viewname1,viewname2,...]
以上是 MySQL视图简介 的全部内容, 来源链接: utcz.com/z/536421.html