视图介绍
含义: 将视图当做结果集 ,虚拟表,和普通表一样使用
作用: 提高了代码的重用性,能够有目的的产生视图数据,有更可靠的安全性,可以避免显示一些重要数据
视图的增删改查
创建视图
语法:
create view 视图名
as
查询语句;
#例:
CREATE VIEW v1
AS
SELECT 字段1,字段2,字段3
FROM employees e
JOIN departments d
ON e.department_id = d.department_id
where ... ;
使用:
SELECT *
FROM 视图名
WHERE last_name LIKE '%a%'
... ;
视图的修改
语法:
create or replace view 视图名
as
查询语句;
#例:
将v3修改为查看查询员工的平均工资、工种编号,并通过工种编号分组查看
CREATE OR REPLACE VIEW v3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
删除视图
语法:
drop view 视图名,视图名,...;
DROP VIEW v1, v2, v3;
查看视图
DESC myv3;
SHOW CREATE VIEW myv3;
视图的更新
插入:
INSERT INTO myv1 VALUES('john','zf@qq.com');
修改:
UPDATE myv1 SET last_name = 'mike' WHERE last_name='john';
删除:
DELETE FROM myv1 WHERE last_name = 'john';
包含以下关键字的sql语句:分组函数、distinct、group by、having、union或者union all 的视图不允许更新