本文共 2217 字，大约阅读时间需要 7 分钟。

视图的基础与应用

一、创建视图

在实际工作中，通过定义视图可以将复杂的查询逻辑隐藏起来，便于日常使用。MySQL支持CREATE VIEW语句，通过定义视图可以将复杂的查询逻辑隐藏起来，便于日常使用。以下是创建视图的关键点：

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) AS average_salary, department_id FROM employees
GROUP BY department_id;

查询特定部门的信息

SELECT myv2.average_salary, job_grades.grade_level FROM myv2
JOIN job_grades ON myv2.average_salary BETWEEN job_grades.lowest_sal AND job_grades.highest_sal;

二、视图的修改

视图的修改可以通过以下两种方式实现：

1. 使用CREATE OR REPLACE语句

CREATE OR REPLACE VIEW myv3 AS SELECT * FROM myv2 ORDER BY average_salary LIMIT 1;

2. 使用ALTER VIEW语句

ALTER VIEW myv3 AS SELECT * FROM employees;

三、删除视图

当不再需要视图时，可以通过以下语句删除：

DROP VIEW myv1, myv2, myv3;

四、查看视图

可以通过以下语句查看视图的定义和创建细节：

DESC myv3;  -- 查看视图字段信息
SHOW CREATE VIEW myv3;  -- 查看视图的创建语句

五、视图的更新

定义的视图可以像普通表一样进行插入、更新和删除操作，但需要注意以下限制条件。

1. 插入操作

INSERT INTO myv1 VALUES('张飞','zf@qq.com');

2. 修改操作

UPDATE myv1 SET last_name = '张无忌' WHERE last_name = '张飞';

3. 删除操作

DELETE FROM myv1 WHERE last_name = '张无忌';

不允许更新的视图类型

以下类型的视图不允许更新：

CREATE OR REPLACE VIEW myv1 AS SELECT MAX(salary) salary, department_id FROM employees
GROUP BY department_id;

CREATE OR REPLACE VIEW myv2 AS SELECT 'john' AS name;

CREATE OR REPLACE VIEW myv3 AS SELECT department_id, (SELECT MAX(salary) FROM employees) AS highest_salary FROM departments;

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

CREATE OR REPLACE VIEW myv5 AS SELECT * FROM myv3;

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);

在实际项目中，以上类型的视图通常不允许直接更新操作，需要通过基表直接插入或更新，或者删除视图后重新定义。

转载地址：http://phtgz.baihongyu.com/