MySql（二十二）视图

视图

含义：

1. 视图是一个虚拟表但是可以和普通的表一样使用。
2. 它是my5.1出现的新特性，是通过表动态生成的数据
3. 视图行和列的数据来自于定义视图的查询中使用到的表，并且是在使用视图中动态生成的，只保存了sql逻辑，不保存查询结果.

应用场景：

1. 多个地方用到相同的查询结果
2. 该查询结果使用到的sql语句较复杂

举个栗子：将学生major_id为1的学生的姓名和专业名添加到视图中（"姓名"和"专业名"分别在"student"表和"major"表中）

CREATE VIEW myview
AS
SELECT name, majorName
FROM student s
INNER JOIN major m
ON s.major_id = m.major_id
WHERE s.major_id = 1;

视图的创建

语法：

create view 视图名

as

查询语句;

举个栗子：

1. 查询姓名中包含字母a的员工名、部门名和工种信息

/**①：创建视图**/
CREATE VIEW my_view
AS
SELECT lastname, department_name,job_title
FROM employees e
INNER JOIN departments d ON e.'department_id' = d.'department_id'
INNER JOIN job j ON e.'job_id' = j.'job_id'
/**②：使用视图**/
SELECT * FROM my_view
WHERE last_name LIKE '%a%';

2.查询各部门的平均工资级别

/**①：创建视图:查看每个部门的平均工资**/
CREATE VIEW my_view
AS
SELECT AVG(salary) ag
FROM employees
GROUP BY department_id;
/**②：使用视图**/
SELECT v.vg,g.grade_level FROM my_view v
INNER JOIN grade g
ON v.ag BETWEEN g.lowest_sal AND g.highest_sal;

3.查询平均工资最低的部门信息

/**①：创建视图:查看每个部门的平均工资**/
CREATE VIEW my_view
AS
SELECT AVG(salary) ag, department_id
FROM employees
GROUP BY department_id;
/**②：在创建一个视图：里面只有一条数据，即工资最低的department_id+平均工资**/
CREATE VIEW my_view2
AS
SELECT * FROM my_view
ORDER BY ag
LIMIT 1;
/**③：使用视图**/
SELECT d.* ,v2.ag 
FROM my_view2 v2
INNER JOIN departments d
ON v2.'department_id' = d.'department_id'

使用视图的好处：

1. 重用sql语句
2. 简化复杂的sql操作，不必知道它的细节
3. 保护数据，提高了安全性

视图的修改

1. 方式一：

create or replace view 视图名

as

查询语句;

2. 方式二：

alter view 视图名

as

查询语句;

删除视图

语法：

drop view 视图名,视图名…;

查看视图

1. 方式一：

desc 视图名;

2. 方式二：

show create view 视图名;

案例讲解1,

创建一个视图emp_v1，要求查询电话号码以’011’开头的员工的姓名、工资和邮箱

CREATE OR REPLACE VIEW emp_v1
AS
SELECT last_name,salary,email
FROM employees
WHERE telphone LIKE '011%';

案例讲解2,

创建视图emp_v2，要求查询部门的最高工资大于12000的部门信息

/**创建视图**/
CREATE OR REPLACE VIEW emp_v2
AS
SELECT MAX(salary) mx,employee_id
FROM employees
GROUP BY employee_id
HAVING mx > 12000;
/**使用视图**/
SELECT d.* v2.mx
FROM departments d
INNER JOIN emp_v2 v2
ON d.'department_id' = v2.'department_id';

视图的更新

这里的“更新”指的是对数据的insert、update、delete操作

具备以下特点的视图是不允许更新的：

1. 含以下关键字的sql语句：distinct、分组函数、group by、having、union或unionall

2.常量视图

/** 创建常量视图**/
CREATE OR REPLACE my_view
AS
SELECT 'Join' name;
/**尝试更新常量视图（当然是不可以实现更新操作的）**/
UPDATE my_view SET name = 'Lucy';

3.select中包含子查询

/** 创建常量视图**/
CREATE OR REPLACE my_view
AS
SELECT (SELECT MAX(salary) FROM employees) 最高工资
/**尝试更新常量视图（当然是不可以实现更新操作的）**/
UPDATE my_view SET 最高工资 = 10000

4.join以及sql92中的’,’（实现连接两个表的逗号）

5.from一个不可以更新的视图

CREATE OR REPLACE my_view
AS
/**其中my_view1是一个不可以更新的视图**/
SELECT * FROM my_view1;

6.where子句的子查询引用了from子句中的表

CREATE OR REPLACE my_view
AS
SELECT last_name,email
FROM employees
WHERE employee_id IN(
	SELECT manager_id 
    FROM employees 
    WHERE manager_id IS NOT NULL
);

视图和表的比较

	语法	是否占用实际物理空间	使用
视图	create view	只是保存了sql逻辑	增、删、改、查（一般不能增、删、改）
表	create table	保存了数据	增、删、改、查