Summary: in this tutorial, you will learn how to create views in MySQL by using the CREATE VIEW statement.

Introducing to CREATE VIEW statement

The syntax of creating a view in MySQL is as follows:

CREATE     [ALGORITHM = {MERGE  | TEMPTABLE | UNDEFINED}]  VIEW [database_name].[view_name]   AS  [SELECT  statement]

Algorithms

The algorithm attribute allows you to control which mechanism is used when creating a view. MySQL provides the MERGE, TEMPTABLE and UNDEFINED algorithms.

• MERGE means the input query will be combined with the SELECT statement of the view definition. MySQL will execute the combined query to return the result set. This mechanism is more efficient than TEMPTABLE (temporary table) but MERGE only allowed when the rows in the view represent a one-to-one relationship with the rows in the underlying table. In case the MERGE is not allowed, MySQL will switch the algorithm to UNDEFINED. The combination of input query and query in view definition into one query sometimes refers as view resolution.

• TEMPTABLE means that MySQL first creates a temporary table based on the SELECT statement of the view definition, and then it executes the input query against this temporary table. Because MySQL has to create temporary table to store the result set and move the data from the physical tables to the temporary table, the TEMPTABLE algorithm is less efficient than the MERGE algorithm. In addition, a view that uses TEMPTABLE algorithm is not updateable.

• UNDEFINED is the default algorithm when you create a view without specifying an explicit algorithm. The UNDEFINED algorithm allows MySQL to make a decision whether to use MERGE or TEMPTABLE. MySQL prefers MERGE to TEMPTABLE, which is more efficient.

View name

Each view is associated with a specific database therefore you can have database name prefix with the view name. Names of views share the same domain with tables therefore they cannot be the same names as tables in a database.

SELECT statement

In the SELECT statement, you can query data from any table or view that exists in the database. There are several rules that the SELECT statement must follow:

• The SELECT statement can contain a subquery in WHERE clause but not in the FROM clause.

• The SELECT statement cannot refer to any variable including local variable, user variable or session variable.

• The SELECT statement cannot refer to the parameters of prepared statements.

MySQL create view examples

Create a simple view

Let’s take a look at the orderDetails table. We can create a view that represents total sales per order.

CREATE VIEW SalePerOrder    AS    SELECT orderNumber,   SUM  (quantityOrdered * priceEach) total   FROM orderDetails   GROUP by orderNumber   ORDER BY total DESC

If you want to query total sales for each sales order, you just need to execute a simple SELECT statement against the SalePerOrder view as follows:

SELECT total  FROM salePerOrder WHERE orderNumber = 10102

Create view with JOIN

The following is an example of creating a view with an INNER JOIN statement. The view contains order number, customer name and total sales per order.

CREATE VIEW customerOrders AS  SELECT  D.orderNumber,          customerName,          SUM(quantityOrdered * priceEach) total  FROM orderDetails D  INNER JOIN orders O ON O.orderNumber = D.orderNumber  INNER JOIN customers C ON O.customerNumber =  C.customerNumber    GROUP BY D.orderNumber   ORDER BY total DESC

Create view with subquery

The following illustrates how to create a view with subquery. The view contains products whose buy prices are higher than average price of all products.

CREATE VIEW vwProducts  AS   SELECT productCode,         productName,         buyPrice   FROM products  WHERE buyPrice > (       SELECT AVG  (buyPrice)       FROM  products  )  ORDER BY buyPrice DESC

In this tutorial, we have shown you how to create views by using the CREATE VIEW statement.

Related Tutorials

• Views in MySQL

• Introduction to Database View

• Managing Views in MySQL

原文链接：http://outofmemory.cn/mysql/view/create-sql-views-mysql