在本教程中,您将学习如何使用SQL创建,更新和删除视图。
视图是一个虚拟表,其定义存储在数据库中。但是,与表不同,视图实际上不包含任何数据。相反,它提供了一种在数据库中存储常用复杂查询的方法。但是,您可以使用SQL SELECT语句来访问视图数据,就像使用普通表或基表一样。
通过允许用户通过视图访问数据,而不是直接授予整个基表访问权限,视图也可以用作安全机制。
使用CREATE VIEW语句创建视图。
CREATE VIEW view_name AS select_statement;
为了清楚地理解这一点,让我们来看看下面employees和departments表。
+--------+--------------+--------+---------+ | emp_id | emp_name | salary | dept_id | +--------+--------------+--------+---------+ | 1 | Ethan Hunt | 5000 | 4 | | 2 | Tony Montana | 6500 | 1 | | 3 | Sarah Connor | 8000 | 5 | | 4 | Rick Deckard | 7200 | 3 | | 5 | Martin Blank | 5600 | NULL | +--------+--------------+--------+---------+ | +---------+------------------+ | dept_id | dept_name | +---------+------------------+ | 1 | Administration | | 2 | Customer Service | | 3 | Finance | | 4 | Human Resources | | 5 | Sales | +---------+------------------+ | |
表: employees | 表: departments |
假设您要检索雇员的ID和姓名以及他们的部门名称,那么您需要执行左加入操作,如下所示:
SELECT t1.emp_id, t1.emp_name, t2.dept_name FROM employees AS t1 LEFT JOIN departments AS t2 ON t1.dept_id = t2.dept_id;
一旦执行了上面的查询,您将获得如下输出:
+--------+--------------+-----------------+ | emp_id | emp_name | dept_name | +--------+--------------+-----------------+ | 1 | Ethan Hunt | Human Resources | | 2 | Tony Montana | Administration | | 3 | Sarah Connor | Sales | | 4 | Rick Deckard | Finance | | 5 | Martin Blank | NULL | +--------+--------------+-----------------+
但是,无论何时要访问此记录,都需要再次键入整个查询。如果您经常执行此类操作,则将变得非常不便和烦人。
在这种情况下,您可以创建一个视图以使查询结果更易于访问,如下所示:
CREATE VIEW emp_dept_view AS SELECT t1.emp_id, t1.emp_name, t2.dept_name FROM employees AS t1 LEFT JOIN departments AS t2 ON t1.dept_id = t2.dept_id;
现在,您可以使用视图emp_dept_view访问相同的记录,如下所示:
SELECT * FROM emp_dept_view;
正如您看到的,您可以在视图上节省多少时间和精力。
提示:视图始终显示最新数据!每次查询视图时,数据库引擎都会执行与视图关联的SQL查询并重新创建数据。
在MySQL中,如果要更新或替换现有视图,则可以删除该视图并创建一个新视图,或者只使用CREATE VIEW语句中的OR REPLACE子句,如下所示:
CREATE OR REPLACE VIEW view_name AS select_statement;
注意:在CREATE VIEW语句中使用OR REPLACE子句时,如果该视图不存在,它将创建一个新视图,否则将替换现有视图。
下面的SQL语句将替换或更改现有视图emp_dept_view视图的定义,方法是向其添加一个新列salary。
-- MySQL数据库的语法 CREATE OR REPLACE VIEW emp_dept_view AS SELECT t1.emp_id, t1.emp_name, t1.salary, t2.dept_name FROM employees AS t1 LEFT JOIN departments AS t2 ON t1.dept_id = t2.dept_id;
更新视图后,如果执行以下语句:
SELECT * FROM emp_dept_view ORDER BY emp_id;
您将在结果输出中看到另一列salary,如下所示:
+--------+--------------+--------+-----------------+ | emp_id | emp_name | salary | dept_name | +--------+--------------+--------+-----------------+ | 1 | Ethan Hunt | 5000 | Human Resources | | 2 | Tony Montana | 6500 | Administration | | 3 | Sarah Connor | 8000 | Sales | | 4 | Rick Deckard | 7200 | Finance | | 5 | Martin Blank | 5600 | NULL | +--------+--------------+--------+-----------------+
注意: SQL Server不支持OR REPLACE子句,因此,要替换视图,您可以直接删除该视图并从stretch中创建一个新视图。
从理论上讲,除了SELECT语句外,您还可以在视图上执行INSERT,UPDATE和DELETE。 但是,并非所有视图都是可更新的,即能够修改基础源表的数据。 对可更新性有一些限制。
通常,如果视图包含以下任何内容,则该视图不可更新:
DISTINCT,GROUP BY或HAVING子句。
聚合函数,例如AVG(),COUNT(),SUM(),MIN(),MAX()等等。
UNION,UNION ALL,CROSSJOIN,EXCEPT或INTERSECT运算符。
WHERE子句中的子查询引用FROM子句中的表。
如果一个视图满足这些条件,则可以使用该视图修改源表。
以下语句将更新emp_id等于1的员工的薪水(salary)。
UPDATE emp_dept_view SET salary = '6000' WHERE emp_id = 1;
注意:为了实现可插入性,该视图必须包含基表中所有没有默认值的列。 同样,为了实现可更新性,视图中的每个可更新列必须与源表中的可更新列相对应。
同样,如果不再需要视图,则可以使用DROP VIEW语句将其从数据库中删除,如以下语法所示:
DROP VIEW view_name;
以下命令将从数据库中删除视图emp_dept_view。
DROP VIEW emp_dept_view;