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 MERGE, TEMPTABLE and UNDEFINED algorithms.
MERGEmeans 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 thanTEMPTABLE(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 theMERGEis not allowed, MySQL will switch the algorithm toUNDEFINED. The combination of input query and query in view definition into one query sometimes refers as view resolution.TEMPTABLEmeans that MySQL first creates a temporary table based on theSELECTstatement 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, theTEMPTABLEalgorithm is less efficient than theMERGEalgorithm. In addition, a view that usesTEMPTABLEalgorithm is not updateable.UNDEFINEDis the default algorithm when you create a view without specifying an explicit algorithm. TheUNDEFINEDalgorithm allows MySQL to make a decision whether to useMERGEorTEMPTABLE. MySQL prefersMERGEtoTEMPTABLE, 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
SELECTstatement can contain a subquery in WHERE clause but not in theFROMclause. - The
SELECTstatement cannot refer to any variable including local variable, user variable or session variable. - The
SELECTstatement 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:
| 123 | SELECT 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 number, customer 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.
