数据库管理中一个强大的工具是使用视图(Views),MySQL视图为数据库提供了一个弹性的层,是的数据的展示和管理变得更加灵活和安全
一、视图的概念
(一)视图概念
视图是一个虚拟表,其内容由查询定义。与真实的表不同,视图不包含数据本身,而是仅仅保存了一个SQL逻辑。每次访问视图时,MYSQL会动态的执行查询并返回结果。视图可以包含所有表中的行和列,或者仅仅选中那部分。
(二)视图优势
安全性:通过视图可以限制用户访问特定的数据,提高数据库的安全性。
简便性:对于复杂的SQL操作,用户可以通过视图来简化查询。
数据隔离:视图可以提供数据的逻辑上的隔离。
数据独立性:改变视图定义不需要改变底层数据库中表的结构。
(三)应用场景
1、多个地方用到相同的查询结果
2、该查询结果使用的SQL语句比较复杂
二、创建视图
创建视图语法:
CREATE VIEW 视图名
AS
SELECT column1, column2, ...
FROM 表
WHERE condition;
三、使用视图
案例1、查询邮箱中包含a的员工名,部门名和工种信息
(1)创建视图查询员工的员工名,部门名和工种名
CREATE VIEW myv1
AS
SELECT last_name,department_name,job_title
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON j.job_id=e.job_id;
(2)使用视图,查询姓名中包含a的
SELECT * FROM myv1 WHERE last_name LIKE "%a%";
案例2、查询各部门的平均工资级别
(1)创建视图查看每个部门的平均工资
CREATE VIEW myv2
AS
SELECT AVG(salary),department_id
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id;
(2)使用视图查找工资级别
SELECT grade_level,department_id
FROM myv2
JOIN job_grades
ON 平均工资 BETWEEN lowest_sal AND highest_sal;
案例3、查询平均工资最低的部门信息
(1)创建视图查询每个部门的平均工资
Select * from myv2 order by ag limit 1;
案例4、查询工资最低的部门名和工资
Select * from myv2 order by 平均工资 limit 1;
Select d.*,ag
From myv3 m
Join deoartments d on m.department_id=d.department_id;
视图检查选项
当使用WITH CHECK OPTION子句创建视图时,MySQL会通过视图检查正在更改的每个行,例如 插入,更新,删除,以使其符合视图的定义。 MySQL允许基于另一个视图创建视图,它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围,mysql提供了两个选项:
CASCADED 和 LOCAL ,默认值为 CASCADED (级联)。
CASCADED :
create view v1 as select id,name from student where id <= 20;
insert into v1 values(18,tom);#可以成功
create view v2 as select id , name from v1 where id >= 10 with cascaded check option ;insert into v2 values(22,tom);#失败,因为不光要检查本级还要检查v1是否符合条件
create view v3 as select id , name from v2 where id <= 15;insert into v3 values(11,tom);#成功
insert into v3 values(17,tom);#成功
insert into v3 values(23,tom);#失败,经过v2要检查选项是否符合v2和v1条件
LOCAL :
create view v4 as select id,name from student where id <= 15
create view v5 as select id , name from v4 where id >= 10 with local check option ;
create view v6 as select id , name from v5 where id < 20insert into v6 values(4,tom); #失败
insert into v6 values(18,tom);# 成功
insert into v6 values(22,tom);#成功
视图更新数据原则
要使视图可更新,视图中的行与基础表中的行之间必须存在一对一(一行对一行)的关系。如果视图包含以下任何一项,则该视图不可更新:
1. 聚合函数或窗口函数(SUM()、 MIN()、 MAX()、 COUNT()等)
2. DISTINCT
3. GROUP BY
4. HAVING
5. UNION 或者 UNION ALL
四、管理视图
insert into v3 values(11,tom);
(一)修改视图
方式一
CREATE OR repalce VIEW 视图名
As
查询语句;
方式二
ALTER VIEW 视图名
As
查询语句;
(二)删除视图
Drop view 视图名,视图名……;(删除多个视图名);
(三)查看视图定义
Desc 视图名;
Show create view 视图名;
五、视图的限制和注意事项
(一)性能:视图在每次查询时都要执行SQL语句,对于大型数据库或复杂查询可能会影响性能。
更新限制:
(二)并非所有的视图都支持更新操作。如果视图包含分组函数、DISTINCT 关键字、group by、having、union、常量视图、select中包含子查询等,则不能进行更新。
(三)权限管理:需要考虑对视图的权限管理,以防止未授权的访问。
六、总结
MySQL视图是强大的数据库工具,通过抽象和简化数据库查询,它能够提高数据访问的效率和安全性。恰当使用视图可以保护数据,简化复杂的SQL操作,但同时也需要注意视图的性能影响和更新限制。掌握视图的使用能够在数据库设计和管理中带来显著的优势。