什么是视图:
视图(view):是mysql5.1版本出现的新特性,是一种虚拟数据表,只保存sql逻辑,不保存sql语句的查询结果,使用方法与普通表一致。(可以类比一个函数,里面封装了一组sql操作)
视图的使用:
1.创建视图:
CREATE VIEW 视图名称
AS
sql语句;
视图可以嵌套,比如可以将视图1、视图2进行联合查询建立视图3
使用视图时,将其当成普通表即可,如:
SELECT * FROM 视图名称 WHERE 条件;
2.查看视图:
查看视图的sql结构,字符集信息:
SHOW CREATE VIEW 视图名称;
查看视图字段信息:
DESC 视图名称;
3.修改视图:
- 方式一:
CREATE OR REPLACE VIEW 视图名称
AS
sql语句;
- 方式二:
ALTER VIEW 视图名称
AS
sql语句;
4.删除视图:
DROP VIEW 视图名称1,视图名称2......;
5.更新视图:
更新视图,实际是通过视图来操作实际表数据,即常规增删改均可以使用,举个简单例子,如:
先创建用户表的视图,
ALTER VIEW v1
AS
SELECT fd_id,fd_name FROM basic_user;
-
插入:
INSERT into v1 VALUES(1,'xy');
注意插入字段需要与视图中的字段对应,如果你创建视图使用的select *,那么需要插入所有字段。 -
更新:
UPDATE v1 SET fd_name = 'xy1' WHERE fd_id = 1;
-
删除:
DELETE FROM v1 WHERE fd_id = 1;
以下情况不允许更新视图:
- 视图中的sql使用了
分组函数、having、group by、DISTINCT关键字、union或union all
。 - 视图中sql使用
select 子查询
:也就是说将子查询的结果当做了一个字段。 视图为常量查询
(一般情况下,常量也不需要创建成视图)。from一个不能更新的视图
:这个很好理解,当前视图的sql查询查询一个不能更新的视图。视图中使用join查询。
视图中sql的where条件为子查询,且子查询的表与from的表为同一个
,一般表设计成树结构可能会出现这种情况。可能不太好理解,举个例子,组织表为basic_organization,组织id为fd_id,上级组织id为fd_parent_id,如果我要查询所有组织中含有下级组织
的记录,则在from和子查询中都使用了basic_organization:
SELECT
*
FROM
basic_organization
WHERE
fd_id IN (
SELECT
fd_parent_id
FROM
basic_organization
WHERE
fd_parent_id IS NOT NULL
);
视图的作用:
1. 复用sql:
项目中反复使用的sql,比如:订单表需要关联用户表、商品表…进行查询,就可以将这组sql创建为一个视图,每次直接视图视图即可。复用了sql,简化了使用。
2. 安全性控制:跟第三方对接,如对接金蝶,我们不需要提供整个表的权限给它,只需要提供相关的数据,即可将相关sql创建成一个视图,只提供视图的权限。
3. 兼容表结构变化:比如原来sql是关联两个表查询,现在修改表中很多字段,或者因为业务需要,拆分了第三个表。那之前编写的sql语句可能需要全部修改一遍,而使用视图则可以避免,只需要将视图修改,之前编写的sql都可以继续使用。
使用视图能提高查询性能吗?
显然不能
,视图只保存sql语句,不保存数据,肯定无法提高性能!mysql视图有两个算法,Merge和TEMPTABLE,默认使用Merge
- MERGE:将视图sql合并到主查询sql中,重新构成新sql进行查询。那么可以被mysql优化器优化,使用索引、mysql查询优化算法等,几乎不会影响性能。
- TEMPTABLE:将视图当作临时表来处理,无法对视图的sql再进行优化。当使用
分组函数、having、group by、DISTINCT关键字、union或union all、子查询
将导致只能使用TEMPTABLE算法。我们需要尽量避免此类查询,如果必须使用,也应当添加where/having条件,想办法减少TEMPTABLE记录数。