含义：虚拟表，和普通表一样使用

MySQL15.1版本出现的新特性，是通过表动态生成的数据。

创建语法的关键字 是否实际占用物理空间 使用
视图 create view 只是保存了sql逻辑 增删改查，只是一般不能增删改
表 create table 保存了数据 增删改查
视图的好处：

1：sql语句提高重复性，效率高。

2：和表实现了分离，提高了安全性。

视图的创建：
1
2
3
4
语法：
CREATE VIEW 视图名
AS
查询语句;
视图的增删改查：
1
2
3
4
5
6
7
8
9
10
11
12
1：查看视图的数据
SELECT * FROM my_v4;
SELECT * FROM my_v1 WHERE last_name='partners';

2：插入视图的数据
INSERT INTO my_V4(last_name,department_id) VALUES('柯南',18);

3：修改视图的数据
UPDATE my_v4 SET last_name='秀一'WHERE last_name='安室透';

4：删除视图的数据
DELETE FROM my_v4;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
例：查询姓张的学生名和专业名
SELECT stuname,majorname
FROM stuinfo s
INNER JOIN major m ON s.'majorid'=m.'id'
WHERE s.'stuname' LIKE '张%';

CREATE VIEW v1
AS
SELECT stuname,majorname
FROM stuinfo s
INNER stuinfo s
INNER JOIN major m ON s.'majorid'-m.'id';

SELECT * FROM v1 WHERE stuname LIKE '张%';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
创建视图：
语法：
CREATE VIEW 视图名
AS
查询语句;

USE myemployees;
例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：查询平均工资最低的部门信息
SELECT * FROM myv2 ORDER 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 m.`department_id`=d.`department_id`;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
视图的修改：
方式一：
create or replace view  视图名
as
查询语句;

例：
SELECT * FROM myv3 

CREATE OR REPLACE VIEW myv3
AS
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;

方式二：
alter view 视图名
as
查询语句;

例：
ALTER VIEW myv3
AS
SELECT * FROM employees;
1
2
3
4
5
删除视图：
语法：drop view 视图名,视图名,...;

例：
DROP VIEW emp_v1,emp_v2,myv3;
1
2
3
查看视图：
DESC myv3;
SHOW CREATE VIEW myv3;
1
2
3
4
5
6
7
8
9
10
视图的更新：
CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email,salary*12(1+IFNULL(commission_pct,0)) "annual salary"
FROM employees;

CREATE OR REPLACE VIEW myv1
AS
SELECT last_name,email
FROM employees;
1
2
3
4
5
6
7
8
#1.插入
INSERT INTO myv1 VALUES('恒子','Cy7heng@163.com');

#2.修改
UPDATE myv1 SET last_name = '张无忌' WHERE last_name='宇智波鼬';

#3.删除
DELETE FROM myv1 WHERE last_name = '漩涡鸣人';
具备以下特点的视图不允许更新：

包含以下关键字的sql语句，分组函数、distinot、group by、having、union、或者union all

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
CREATE OR REPLACE VIEW myv1
AS
SELECT MAX(salary) m,department_id
FROM employees
GROUP BY deparment_id;

# 常量视图：
CREATE OR REPLACE VIEW myv2
AS
SELECT 'john' NAME;

# Select中包含子查询：
CREATE OR REPLACE VIEW myv3
AS
SELECT department_id,(SELECT MAX(salary) FROM employees) 最高工资
FROM departments;

# join:
CREATE OR REPLACE VIEW myv4
AS
SELECT last_name,department_name
FROM employees e
JOIN departments d
ON e.department_id  = d.department_id;

# from一个不能更新的视图
CREATE OR REPLACE VIEW myv5
AS
SELECT * FROM myv3;

# where子句的子查询引用了from子句中的表
CREATE OR REPLACE VIEW myv6
AS
SELECT last_name,email,salary
FROM employees
WHERE employee_id IN(
	SELECT  manager_id
	FROM employees
	WHERE manager_id IS NOT NULL
);

2021-04-01