目录
基本概念:
视图本身是一张虚拟表,不存放任何数据。在使用SQL语句访问视图的时候,获取的数据是MySQL从其它表中生成的,视图和表在同一个命名空间。视图查询数据相对安全,视可以隐藏一些数据和结构,只让用户看见权限内的数据,使复杂的查询易于理解和使用。
视图的用法:
基本语法:
CREATE OR REPLACE VIEW view_name
AS select_statement
示例:
现在展示基于用户和订单管理演示视图的基本用法。
基础的表结构:
CREATE TABLE v01_user (
id INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
user_name VARCHAR(20) DEFAULT NULL COMMENT '用户名',
phone VARCHAR(20) DEFAULT NULL COMMENT '手机号',
pass_word VARCHAR(64) DEFAULT NULL COMMENT '密码',
card_id VARCHAR(18) DEFAULT NULL COMMENT '身份证ID',
pay_card VARCHAR(25) DEFAULT NULL COMMENT '卡号',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用户表';
CREATE TABLE v02_order (
id INT(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
user_id INT(11) NOT NULL COMMENT '用户ID',
order_no VARCHAR(32) DEFAULT NULL COMMENT '订单编号',
good_name VARCHAR(60) DEFAULT NULL COMMENT '商品名称',
good_id INT(11) DEFAULT NULL COMMENT '商品ID',
num INT(11) DEFAULT NULL COMMENT '购买数量',
total_price DECIMAL(10,2) DEFAULT NULL COMMENT '总价格',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '订单表';
创建用户订单视图:
CREATE OR REPLACE
VIEW user_order_view AS SELECT
t1.id,t1.user_name,t2.order_no,t2.good_id,
t2.good_name,t2.num,t2.total_price
FROM v01_user t1
LEFT JOIN v02_order t2 ON t2.user_id = t1.id;
视图调用:
SELECT * FROM user_order_view WHERE user_name='Cicada';
这里和MySQL的表查询基本一致,可以使用各种查询条件。
查看视图:
SHOW CREATE VIEW user_order_view ;
修改视图:
ALTER VIEW view_name AS select_statement ;
删除视图:
DROP VIEW [IF EXISTS] view_name ;
视图更新:
在指定条件允许的情况下,可以通过在视图上操作更新,删除,甚至写入数据,进而更新视图所涉及的相关表。
UPDATE user_order_view SET user_name='smile' WHERE id='1';
这里就通过对视图执行更新操作,进而更新
v01_user
表数据。如果视图定义时使用聚合函数,分组等特殊操作,则无法更新。MySQL不支持在视图上创建触发器。
视图实现
- 临时表算法
服务器会把视图查询SQL的数据保存在临时表中,临时表的结构和视图字段结构一致,这样是SQL查询优化中最忌讳的操作,数据量稍微偏大,就会严重影响性能。如果视图无法和原有表产生一对一的映射关系,就会产生临时表,由此也可见视图并不是很简单,甚至是非常复杂的功能。
- 合并算法
服务器基于视图中使用的表执行查询,最后把查询结构合并后返回给客户端。
- 区别方法
执行如下查询语句,可以分析执行的性能参数。
EXPLAIN SELECT * FROM user_order_view ;
观察查询结果中
select_type
字段,如果是DERIVED
则说明使用临时表。这里SQL执行分析的语法后面优化部分再详解。
使用视图的优点
定制用户数据,聚焦特定的数据
在实际的应用过程中,不同的用户可能对不同的数据有不同的要求。
例如,当数据库同时存在时,如学生基本信息表、课程表和教师信息表等多种表同时存在时,可以根据需求让不同的用户使用各自的数据。学生查看修改自己基本信息的视图,安排课程人员查看修改课程表和教师信息的视图,教师查看学生信息和课程信息表的视图。
简化数据操作
在使用查询时,很多时候要使用聚合函数,同时还要显示其他字段的信息,可能还需要关联到其他表,语句可能会很长,如果这个动作频繁发生的话,可以创建视图来简化操作。
提高数据的安全性
视图是虚拟的,物理上是不存在的。可以只授予用户视图的权限,而不具体指定使用表的权限,来保护基础数据的安全。
共享所需数据
通过使用视图,每个用户不必都定义和存储自己所需的数据,可以共享数据库中的数据,同样的数据只需要存储一次。
更改数据格式
通过使用视图,可以重新格式化检索出的数据,并组织输出到其他应用程序中。
重用 SQL 语句
视图提供的是对查询操作的封装,本身不包含数据,所呈现的数据是根据视图定义从基础表中检索出来的,如果基础表的数据新增或删除,视图呈现的也是更新后的数据。视图定义后,编写完所需的查询,可以方便地重用该视图。
注意事项:
创建视图时,不能有相同意义的字段存在,否则无法创,下面看一下错误写法
CREATE OR REPLACE
VIEW user_order_view AS SELECT
*
FROM v01_user t1
LEFT JOIN v02_order t2 ON t2.user_id = t1.id;
特意把 * 隔开了,因为 * 中包含了相同的字段,也就是每个表中的外键,如果这样创建,报错