MySQL基础(六)——视图
视频学习来源:MySQL_基础+高级篇- 数据库 -sql -mysql教程_mysql视频_mysql入门_尚硅谷;
作者:木子六日;
sql版本:5.7.33;
文章目录
视图
# 视图
# 如果一个查询语句经常使用到,我们可以将其保存为视图
# 视图与表不同之处在于,表是保存数据的,视图保存的是sql逻辑
# 假设员工名和其部门名经常用到
SELECT
e.last_name,
d.department_name
FROM
employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
# 我们就可以将上述查询语句保存为一个视图
CREATE VIEW employee_department AS SELECT
e.last_name,
d.department_name
FROM
employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
# 下次查询我们直接查询视图就可以了
SELECT
*
FROM
employee_department;
# 修改视图内容有两种办法
CREATE
OR REPLACE VIEW employee_department AS SELECT
*
FROM
employees;
# 看一下改了没
SELECT
*
FROM
employee_department;
# 还可以用alter直接改
ALTER VIEW employee_department AS SELECT
e.last_name,
d.department_name
FROM
employees e
LEFT JOIN departments d ON e.department_id = d.department_id;
#再看一下
SELECT
*
FROM
employee_department;
# 查看视图结构
DESC employee_department;
# 删除视图
DROP VIEW employee_department;
# 对于视图中的记录,我们也可以进行增删改
# 增删改操作会对原表进行
# 但是一般视图都是用来查的,不推荐对视图进行增删改操作,不安全
# 我们常常会将视图设置为只读权限