跳转到内容

结构化查询语言/视图

Add links
来自维基教科书，开放世界中的开放书籍
SELECT: 子查询 结构化查询语言
视图 		INSERT 1



通常，用户和应用程序会以与现有表结构不同的形式请求信息。为了满足这些请求，SELECT 命令提供了很多可能性：投影、联接、分组依据子句等等。如果总是相同的请求，尤其是在应用程序中，或者如果表结构有意地应该对应用程序级别隐藏，则可以定义视图。此外，对视图的访问权限可能与对表的访问权限不同。

视图看起来像一张表。它们具有特定数据类型的列，可以像检索表列一样检索它们。但视图只是定义，它们没有自己的数据！它们的数据始终是表的數據，或者基于其他视图。视图是对存储数据的不同视角，或者类似于预定义的 SELECT。

创建视图

[编辑 | 编辑源代码]

通过指定视图的名称、可选的列名，尤其是视图所基于的 SELECT 命令来创建一个视图。在该 SELECT 中，所有元素都像独立的 SELECT 命令中一样被允许。如果没有指定列名，则使用 SELECT 的列名。

CREATE VIEW <view_name> [(column_name, ...)] AS
  SELECT ...  -- as usual
;

示例和解释

[编辑 | 编辑源代码]

示例 1：隐藏列

[编辑 | 编辑源代码]

作为第一个示例，这里有视图 person_view_1，它包含表 person 中除 id 和 ssn 列之外的所有列。有权从该视图读取但没有权从表 person 读取的用户没有访问 id 和 ssn 的权限。

CREATE VIEW person_view_1 AS
  SELECT firstname, lastname, date_of_birth, place_of_birth, weight
  FROM   person;

-- SELECTs on views have identical syntax as SELECTs on tables
SELECT *
FROM   person_view_1
ORDER BY lastname;

-- The column 'id' is not part of the view. Therefore it is not seen and cannot be used
-- anywhere in SELECTs to person_view_1.
-- This SELECT will generate an error message because of missing 'id' column:
SELECT *
FROM   person_view_1
WHERE  id = 5;

如上面的 'order by' 示例所示，可以在 SELECT 对视图的任何部分（但不包括表中的所有列！）中使用视图的所有列：在投影中、WHERE 子句中、ORDER BY 子句中、GROUP BY 子句中和 HAVING 子句中，在函数调用中等等。

-- SELECTs on views have identical syntax as SELECTs on tables
SELECT count(lastname), lastname
FROM   person_view_1
GROUP BY lastname
ORDER BY lastname;

示例 2：重命名列

[编辑 | 编辑源代码]

接下来，对列进行重命名。表中的列名 lastname 将在视图中变为 familyname。

-- first technique: list the desired column names within parenthesis after the view name
CREATE VIEW person_view_2a (firstname, familyname, date_of_birth, place_of_birth, weight) AS
  SELECT                    firstname, lastname,   date_of_birth, place_of_birth, weight
  FROM   person;

-- second technique: rename the column in the SELECT part
CREATE VIEW person_view_2b AS
  SELECT firstname, lastname AS familyname, date_of_birth, place_of_birth, weight
  FROM   person;
-- Hint: technique 1 overwrites technique 2

-- Access to person.lastname is possible via person_view_2a.familyname or person_view_2b.familyname.
-- The objects person.familyname, person_view_2a.lastname or person_view_2b.lastname does not exist!

示例 3：应用 WHERE 条件

[编辑 | 编辑源代码]

视图中不仅仅可以隐藏列。还可以隐藏完整的行，因为视图定义可能包含 WHERE 子句。

-- Restrict access to few rows
CREATE VIEW person_view_3 AS
  SELECT firstname, lastname, date_of_birth, place_of_birth, weight
  FROM   person
  WHERE  place_of_birth in ('San Francisco', 'Richland');

-- Verify result:
SELECT *
FROM   person_view_3;

此视图仅包含出生在旧金山或里士满的个人。所有其他人员都被隐藏。因此，以下 SELECT 不会检索任何内容，尽管表中存在满足条件的个人。

-- No hit
SELECT *
FROM   person_view_3
WHERE  place_of_birth = 'Dallas';

-- One hit
SELECT *
FROM   person
WHERE  place_of_birth = 'Dallas';

示例 4：使用函数

[编辑 | 编辑源代码]

此示例使用 sum() 函数。

-- 
CREATE VIEW person_view_4 AS
  -- General hint: Please consider that not all columns are available in a SELECT containing a GROUP BY clause
  SELECT lastname, count(lastname) AS count_of_members
  FROM   person
  GROUP BY lastname
  HAVING count(*) > 1;

-- Verify result: 2 rows
SELECT *
FROM   person_view_4;

-- The computed column 'count_of_members' may be part of a WHERE condition. 
-- This SELECT results in 1 row 
SELECT *
FROM   person_view_4
WHERE  count_of_members > 2;

在此示例中，详细的结构 'GROUP BY / HAVING' 对用户和应用程序隐藏。

示例 5：联接

[编辑 | 编辑源代码]

视图可以使用 JOIN 命令包含来自多个表的列。以下示例视图包含个人的姓名及其可用的联系信息。由于使用了 INNER JOIN，一些个人会多次出现，而另一些则根本不会出现。

-- Persons and contacts
CREATE VIEW person_view_5 AS
  SELECT p.firstname, p.lastname, c.contact_type, c.contact_value
  FROM   person p
  JOIN   contact c ON p.id = c.person_id;

-- Verify result
SELECT *
FROM   person_view_5;

SELECT *
FROM   person_view_5
WHERE  lastname = 'Goldstein';

列 person.id 和 contact.person_id 在视图的定义过程中被使用。但它们不是投影的一部分，因此无法用于 SELECT 对视图进行查询。

提示：联接操作的语法和语义在单独的 页面 中进行了解释。

更多提示

[编辑 | 编辑源代码]

在 CREATE VIEW 语句中，可以使用常规 SELECT 语句的更多元素，如本页中所示，特别是：SET 操作、递归定义、CASE 表达式、ORDER BY 等等。

如果 CREATE VIEW 中存在 ORDER BY 子句，而在 SELECT 对该视图进行查询时又存在另一个 ORDER BY 子句，则后者会覆盖前者。

通过视图进行写入访问

[编辑 | 编辑源代码]

在某些情况下，但并非总是如此，应该可以通过访问视图来更改表中的数据（UPDATE、INSERT 或 DELETE 命令）。例如，假设要将 person_view_4 的 count_of_members 列更改为另一个值。DBMS 该怎么做？该列受聚合函数的影响，该函数统计底层表中现有行的数量。是应该添加更多随机值到新行中，还是应该删除随机行来满足 count_of_members 的新值？当然不行！

另一方面，非常简单的视图，如 'CREATE VIEW person_0 AS SELECT * from person;'，它是一个原始表的 1:1 复制，DBMS 应该能够处理。可更新视图和不可更新视图之间的界限在哪里？SQL 标准没有定义它。但具体的 SQL 实现会根据自己的规则提供有限的视图写入访问权限。有时这些规则非常固定，而其他情况下则包括灵活的技术，如 'INSTEAD OF' 触发器，以便程序员有机会实现自己的规则。

以下是一些一般规则，它们可能包含在实现者定义哪些视图在该意义上可更新的固定规则中

  • 视图定义基于一个且仅一个表。它包括此底层表的 PRIMARY KEY。
  • 视图定义不得使用任何聚合函数。
  • 视图定义不得有任何 DISTINCT 子句、GROUP BY 子句或 HAVING 子句。
  • 视图定义不得有任何 JOIN、SUBQUERY、SET 操作、EXISTS 或 NOT EXISTS 谓词。
  • 许多实现都有一个关键字，可以用来强制视图只读，即使从技术上讲它不必如此。

如果可以对视图使用 UPDATE、INSERT 或 DELETE 命令，则语法与表相同。

清理示例数据库

[编辑 | 编辑源代码]

DROP VIEW 语句用于删除视图定义。这样做不会影响底层表的数据。

不要将 DROP 命令（定义）与 DELETE 命令（数据）混淆！

DROP VIEW person_view_1;
DROP VIEW person_view_2a;
DROP VIEW person_view_2b;
DROP VIEW person_view_3;
DROP VIEW person_view_4;
DROP VIEW person_view_5;

练习

[编辑 | 编辑源代码]

创建一个名为 'hobby_view_1' 的视图，其中包含 'hobby' 表的所有列，除了 'id' 列。
将列 'remark' 重命名为 'explanation'。创建两种不同的解决方案。

点击查看解决方案
CREATE VIEW hobby_view_1a AS
  SELECT hobbyname, remark AS explanation
  FROM   hobby;
-- Verification
SELECT * FROM hobby_view_1a;

CREATE VIEW hobby_view_1b (hobbyname, explanation) AS
  SELECT hobbyname, remark
  FROM   hobby;
-- Verification
SELECT * FROM hobby_view_1b;

创建一个名为 'hobby_view_2' 的视图，其标准与上一个示例相同。唯一的区别
是解释列的长度限制为 30 个字符。提示：使用函数
substr(<列名>, 1, 30) 来确定前 30 个字符 - 这不是 SQL 标准的一部分，但在许多实现中都有效。

点击查看解决方案
CREATE VIEW hobby_view_2 AS
  SELECT hobbyname, substr(remark, 1, 30) AS explanation
  FROM   hobby;
-- Verification
SELECT * FROM hobby_view_2;

创建一个名为 'contact_view_3' 的视图，其中包含 'contact' 表的所有行，除了 'icq' 行。统计视图行的数量，并将其与 'contact' 表中的行数进行比较。

点击查看解决方案
CREATE VIEW contact_view_3 AS
  SELECT *
  FROM   contact
  WHERE  contact_type != 'icq';  -- an alternate operator with the same semantic as '!=' is '<>'

-- Verification
SELECT 'view',  count(*) FROM contact_view_3
  UNION
SELECT 'table', count(*) FROM contact;

创建一个名为 'contact_view_4' 的视图，其中包含每个联系类型及其表示法以及出现次数。然后选择出现次数超过一次的联系类型。

点击查看解决方案
CREATE VIEW contact_view_4 AS
  SELECT contact_type, count(*) AS cnt
  FROM   contact
  GROUP BY contact_type;

-- Verification
SELECT *
FROM   contact_view_4;

-- Use columns of a view with the same syntax as a column of a table.
SELECT *
FROM   contact_view_4
WHERE  cnt > 1;

创建一个名为 'person_view_6' 的视图，其中包含人员的姓和名，以及与该人员同名（姓氏）的人员数量。提示：解决方案使用 相关子查询。

点击查看解决方案
CREATE VIEW person_view_6 AS
  SELECT firstname, lastname, (SELECT count(*) FROM person sq WHERE sq.lastname = p.lastname) AS cnt_family 
  FROM   person p;

-- Verification
SELECT *
FROM   person_view_6;

清理示例数据库。

点击查看解决方案
DROP VIEW hobby_view_1a;
DROP VIEW hobby_view_1b;
DROP VIEW hobby_view_2;
DROP VIEW contact_view_3;
DROP VIEW contact_view_4;
DROP VIEW person_view_6;


SELECT: 子查询 结构化查询语言
视图 		INSERT 1
检索自 "https://wikibooks.cn/w/index.php?title=Structured_Query_Language/Views&oldid=4226811"
华夏公益教科书