SQL学习笔记系列(六)视图的讲解
视图的介绍
定义
理解成一张虚拟的表(和普通表一样使用)
是通过表动态生成的数据,只保存了sql逻辑,不保存查询结果。
#案例:查询姓张的同学的学生名和专业名
CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo a
INNER JOIN major m ON s.'majorid'=m.'id'
WHERE s.'stuname' LIKE '张%';
SELECT *
FROM v1
WHERE stuname LIKE '张%';
视图和表的区别
使用方式 占用物理空间
视图 完全相同 不占用,仅仅保存的是sql逻辑
表 完全相同 占用
视图的好处
1、sql语句提高重用性,效率高
2、和表实现了分离,提高了安全性
视图的创建
#语法
CREATE VIEW 视图名
AS
查询语句;#简单语句不需要视图
#案例1:查询姓名中包含a字符的员工名、部门名和工种信息
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;
SELECT *
FROM myv1
WHERE last_name LIKE '%a%';
#案例2:查询各部门的平均工资级别
CREATE VIEW myv2
AS
SELECT AVG(salary) ag ,department_id
FROM employees
GROUP BY department_id;
SELECT myv2.`ag`,g.grade_level
FROM myv2
JOIN job_grades g
ON myv2.`ag` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
#案例3:查询平均工资最低的部门信息
CREATE VIEW myv2
AS
SELECT AVG(salary) ag ,department_id
FROM employees
GROUP BY department_id;
SELECT *
FROM departments
WHERE department_id=(
SELECT department_id
FROM myv2
GROUP BY ag
LIMIT 1
);
#案例4:查询平均工资最低的部门名和工资
CREATE VIEW myv3
AS
SELECT *
FROM myv2
ORDER BY ag
LIMIT 1;
SELECT d.*,m.ag
FROM myv3 m
JOIN departments d
ON d.`department_id`=m.`department_id`;
视图的修改
语法
#方式一
create or replace view 视图名
as
查询语句;
#案例一:修改myv3
create or replace view myv3
as
select avg(salary),job_id
from employees
group by job_id;
#方式二
alter view 视图名
as
查询语句;
#案例2:修改myv3
ALTER VIEW myv3
AS
SELECT *
FROM employees;
删除视图
语法
DROP VIEW 视图名,视图名,...;
#案例1
DROP VIEW myv1,myv2,myv3;
查看视图
DESC myv3;
SHOW CREATE VIEW test_v7;
案例讲解
#案例1:创建视图emp_v1,要求查询电话号码以‘011’开头的员工姓名和工资、邮箱
create or replace view emp_v1
as
select last_name,salary,email
from employees
where phone_number like '011%';
#案例2:创建视图emp_v2,要求查询部门的最高工资高于12000的部门信息。
CREATE VIEW emp_v2
AS
SELECT MAX(salary) mx_dep,department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)>12000;
SELECT d.*,m.`mx_dep`
FROM departments d
JOIN emp_v2 m
ON m.`department_id`=d.`department_id`;
视图的更新
插入
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;
INSERT INTO myv1 VALUES('张飞','zf@qq.com');
SELECT *
FROM myv1;#也会对原始表进行更新
修改
UPDATE myv1 SET last_name='张无忌'
WHERE last_name='张飞';
#修改视图对应的基表引用字段集
删除
delete from myv1
where last_name='张无忌';
某些视图不能更新
1.包含以下关键字的sql语句:分组函数、distinct、group by、having、union或者union all
2.常量视图
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;
UPDATE myv2
SET NAME='lucy';
3.Select中包含子查询
4.join(连接的都算)
5.from一个不能更新的视图
6.where子句的子查询引用了from子句中的表
视图和表的对比
1.视图
- 创建语法的关键字:create view
- 是否实际占用物理空间:否(但保存了逻辑)
- 使用:增删改查,一般不能增删改
2.表
- 创建语法的关键字:create table
- 是否实际占用物理空间:是
- 使用:增删改查