Sql 简明教程
SQL - CREATE View
What is SQL View
SQL 中的视图是存储在数据库中的虚拟表，并具有关联的名称。它实际上是以预定义 SQL 查询的形式组成的一个表。视图可以包含现有表中的行（全部或选定的）。视图可以从一个或多个表创建。除非编制索引，否则视图在数据库中不存在。
A view in SQL is a virtual table that is stored in the database with an associated name. It is actually a composition of a table in the form of a predefined SQL query. A view can contain rows from an existing table (all or selected). A view can be created from one or many tables. Unless indexed, a view does not exist in a database.
视图中的数据在数据库中物理不存在。视图通常由数据库管理员创建，并用于 −
The data in the view does not exist in the database physically. A view is typically created by the database administrator and is used to −
-
Structure data in a way that users or classes of users find natural or intuitive.
-
Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more.
-
Summarize data from various tables which can be used to generate reports.
The SQL CREATE VIEW Statement
如要在数据库中创建视图, 可以使用 SQL CREATE VIEW 语句。
To create a view in a database, you can use the SQL CREATE VIEW statement.
Syntax
以下是 SQL CREATE VIEW 语句的语法：
Following is the syntax of the SQL CREATE VIEW statement −
CREATE VIEW view_name AS
SELECT column1, column2....
FROM table_name
WHERE [condition];Example
假设我们使用以下查询使用 CREATE TABLE 语句创建名为 CUSTOMERS 的表：
Assume we have created a table named CUSTOMERS using the CREATE TABLE statement using the following query −
CREATE TABLE CUSTOMERS(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);现在，使用如下所示的 INSERT 语句向此表中插入值：
Now, insert values into this table using the INSERT statement as follows −
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );以下查询基于上述创建的表格创建一个视图 −
Following query creates a view based on the above created table −
CREATE VIEW CUSTOMERS_VIEW AS SELECT * FROM CUSTOMERS;Verification
你可以使用 select 查询验证视图的内容，如下所示 −
You can verify the contents of a view using the select query as shown below −
SELECT * FROM CUSTOMERS_VIEW;视图显示如下 −
The view is displayed as follows −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
2 |
Khilan |
25 |
Delhi |
1500.00 |
3 |
Kaushik |
23 |
Kota |
2000.00 |
4 |
Chaitali |
25 |
Mumbai |
6500.00 |
5 |
Hardik |
27 |
Bhopal |
8500.00 |
6 |
Komal |
22 |
Hyderabad |
4500.00 |
7 |
Muffy |
24 |
Indore |
10000.00 |
Create View With WHERE Clause
还可以使用 where 子句连同 SQL CREATE VIEW 语句一起从表中创建带有限定记录的视图，如下所示：
We can also create a view with only specific records from a table using the where clause along with the SQL CREATE VIEW statement as shown below −
CREATE VIEW BUYERS_VIEW as SELECT * FROM CUSTOMERS
WHERE SALARY > 3000;The WITH CHECK OPTION Clause
WITH CHECK OPTION 是 CREATE VIEW 语句选项。WITH CHECK OPTION 的目的是确保所有 UPDATE 和 INSERT 语句都满足 WHERE 子句指定的条件。
The WITH CHECK OPTION is a CREATE VIEW statement option. The purpose of the WITH CHECK OPTION is to ensure that all UPDATE and INSERT statements satisfy the condition(s) specified by the WHERE clause.
如果它们不满足条件，则 UPDATE 或 INSERT 语句返回错误。以下示例使用 WITH CHECK OPTION 子句创建名为 BUYERS_VIEW 的视图。
If they do not satisfy the condition(s), the UPDATE or INSERT statements return an error. The following example creates the view named BUYERS_VIEW with the WITH CHECK OPTION clause.
CREATE VIEW MY_VIEW AS
SELECT name, age
FROM CUSTOMERS
WHERE age >= 25
WITH CHECK OPTION;在这种情况下，WITH CHECK OPTION 应拒绝输入和更新年龄值大于或等于 25 的记录。
The WITH CHECK OPTION in this case should deny the entry and updates of the of records whose age value is greater than or equal to 25.