Creating Views in MySQL : How to create mysql views

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

Introduction to CREATE VIEW statement

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

 

1234

5

  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 MERGETEMPTABLE 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 theMERGE 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 SELECTstatement 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 MERGEor 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.

 

1234

5

6

7

CREATE VIEW SalePerOrderASSELECT 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 SELECTstatement against the SalePerOrder view as follows:

 

123SELECT totalFROM salePerOrderWHERE orderNumber = 10102

Create view with JOIN

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

 

1234

5

6

7

8

9

 CREATE VIEW customerOrders ASSELECT  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.

 

1234

5

6

7

8

9

10

 CREATE VIEW vwProducts  ASSELECT 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.

 

Leave a Reply