Mysql 简明教程
MySQL - CREATE VIEW
MySQL 视图是一种虚拟表格。它们与一个相关名称一起存储在数据库中。它们允许用户执行以下操作：
MySQL views are a type of virtual tables. They are stored in the database with an associated name. They allow users to do the following −
-
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.
视图可以从一个或多个表格建立，其中包含了所有或选择自其中的行列。除非已建立索引，否则视图不会存在于数据库中。
A view can be created from one or more tables, containing either all or selective rows from them. Unless indexed, a view does not exist in a database.
MySQL Create View Statement
创建视图只是使用一个查询创建一张虚拟表。视图是存储在数据库中并拥有关联名称的一条 SQL 语句。它实际上是通过预定义 SQL 查询形成的一张表。
Creating a view is simply creating a virtual table using a query. A view is an SQL statement 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.
Syntax
以下是 Create View 语句的语法：
Following is the syntax of the CREATE VIEW Statement −
CREATE VIEW view_name AS select_statements FROM table_name;Example
假设我们使用下面的 Select 语句创建了一张表：
Assume we have created a table using the SELECT statement as shown below −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR(15) NOT NULL,
AGE INT NOT NULL,
ADDRESS VARCHAR(25),
SALARY DECIMAL(10, 2),
PRIMARY KEY(ID)
);让我们在以上创建的表中插入 7 条记录：
Let us insert 7 records in the above created table −
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', '32', 'Ahmedabad', 2000),
(2, 'Khilan', '25', 'Delhi', 1500),
(3, 'Kaushik', '23', 'Kota', 2500),
(4, 'Chaitali', '26', 'Mumbai', 6500),
(5, 'Hardik','27', 'Bhopal', 8500),
(6, 'Komal', '22', 'MP', 9000),
(7, 'Muffy', '24', 'Indore', 5500);以下查询基于以上创建的表创建一个视图：
Following query creates a view based on the above create table −
CREATE VIEW first_view AS SELECT * FROM CUSTOMERS;Verification
你可以使用 select 查询验证视图的内容，如下所示 −
You can verify the contents of a view using the select query as shown below −
SELECT * FROM first_view;视图将按如下方式创建：
The view will be created as follows −
With REPLACE and IF NOT EXISTS Clauses
通常情况下，如果你尝试通过一个与现有视图相同的名称创建视图，将生成一个错误，如下所示：
Usually, if you try to create a view with the name same as an existing view an error will be generated as shown as −
CREATE VIEW first_view AS SELECT * FROM CUSTOMERS;因为视图已存在，所以将引发以下错误：
As the view already exists, following error is raised −
ERROR 1050 (42S01): Table 'first_view' already exists因此，你可以使用 Replace 子句以及 Create View 来替换现有视图。
So, you can use the REPLACE clause along with CREATE VIEW to replace the existing view.
CREATE OR REPLACE VIEW first_view AS SELECT * FROM CUSTOMERS;The With Check Option
With Check Option 是与 Create View 语句一起使用的选项。此 With Check Option 的目的是为了确保所有 Update 和 Insert 语句满足查询中的条件。如果它们不满足条件，Update 或 Insert 将返回一个错误。
The WITH CHECK OPTION is an option used with CREATE VIEW statement. The purpose of this WITH CHECK OPTION is to ensure that all UPDATE and INSERT statements satisfy the condition(s) in the query. If they do not satisfy the condition(s), the UPDATE or INSERT returns an error.
Syntax
以下是语法 -
Following is the syntax −
CREATE VIEW view_name
AS SELECT column_name(s)
FROM table_name
WITH CHECK OPTION;Example
在下面的示例中，我们使用 Create View 语句以及 With Check Option 创建视图：
In the following example, we are creating a view using CREATE VIEW statement along with the WITH CHECK OPTION −
CREATE VIEW NEW_VIEW
AS SELECT * FROM CUSTOMERS
WHERE NAME IS NOT NULL
WITH CHECK OPTION;视图按如下方式创建：
The view is created as follows −
Creating a MySQL View Using Client Program
除了使用 SQL 查询在 MySQL 数据库中创建视图，我们还可以使用客户端程序创建视图。
In addition to creating a view in MySQL Database using the SQL queries, we can also do so using a client program.