Detailed explanation of creating a view (CREATE VIEW) and usage restrictions in MySQL

This article uses examples to describe the creation of MySQL views (CREATE VIEW) and usage restrictions. Share with you for your reference, the details are as follows:

MySQL 5.x and later versions support database views. In MySQL, almost all features of views conform to the SQL: 2003 standard. MySQL handles queries against views in two ways:

• In the first way, MySQL creates a temporary table based on the view definition statement and executes the incoming query on this temporary table.
• In the second way, MySQL combines the incoming query with the query definition into one query and executes the combined query.

MySQL supports a versioning system for views. Each time a view is changed or replaced, a copy of the view is backed up in an arc (archive) folder residing in a specific database folder. The name of the backup file is view_name.frm-00001. If you alter the view again, mysql creates a new backup file named view_name.frm-00002. MySQL allows you to create views based on other views, that is, in the select statement of the view definition, you can reference another view.

Well, I won't go into more details. Next, let's try to create a view using the CREATE VIEW statement. Let's take a look at the syntax structure first:

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

Then let's take a closer look at what the various words in the above SQL mean. First of all, the first bracket represents the algorithm attribute for creating a view. It allows us to control the mechanism used by MySQL when creating a view, and MySQL provides three algorithms: MERGE, TEMPTABLE, and UNDEFINED. Let’s look at each of them separately:

• Using the MERGE algorithm, MySQL first combines the input query with the select statement that defines the view into a single query. MySQL then executes the combined query to return the result set. If the select statement contains aggregate functions (such as min, max, sum, count, avg, etc.) or distinct, group by, having, limit, union, union all, subquery, the MERGE algorithm is not allowed. If the select statement does not reference a table, the MERGE algorithm is not allowed. If the MERGE algorithm is not allowed, MySQL changes the algorithm to UNDEFINED. We should note that combining the input query and the query in the view definition into a single query is called view resolution.
• With the TEMPTABLE algorithm, MySQL first creates a temporary table based on the SELECT statement that defines the view, and then executes the input query against the temporary table. Because MySQL must create a temporary table to store the result set and move the data from the base table to the temporary table, the TEMPTABLE algorithm is less efficient than the MERGE algorithm. Additionally, views that use the TEMPTABLE algorithm are not updatable.
• UNDEFINED is the default algorithm when we create a view without specifying an explicit algorithm. The UNDEFINED algorithm enables MySQL to choose between using the MERGE or TEMPTABLE algorithm. MySQL prefers the MERGE algorithm to the TEMPTABLE algorithm because the MERGE algorithm is more efficient.

Then there is the phrase after view, which means the name. In the database, views and tables share the same namespace, so views and tables cannot have the same name. In addition, the name of the view must follow the naming conventions for the table.

The last statement is the SELECT statement. In the SELECT statement, you can query data from any table or view in the database. At the same time, the SELECT statement must follow the following rules:

• A SELECT statement can contain subqueries in the where clause, but not in the FROM clause.
• The SELECT statement cannot reference any variables, including local variables, user variables, and session variables.
• A SELECT statement cannot reference parameters of a prepared statement.

One thing to note here is that the SELECT statement does not need to reference any tables. To finish, let's try to create a view based on the orderDetails table to represent the total sales of each order:

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

If we use the SHOW TABLES command to view all the tables in the sample database (yiibaidb), we will also see that the SalesPerOrder view is also displayed in the list of tables:

mysql> SHOW TABLES;
+--------------------+
| Tables_in_yiibaidb |
+--------------------+
| article_tags |
| contacts |
| customers |
| departments |
|employees|
| offices |
|offices_bk|
| offices_usa |
|orderdetails|
| orders |
| payments |
| productlines |
| products |
|saleperorder|
+--------------------+
14 rows in set

This is because views and tables share the same namespace. To know which object is a view or a table, use the SHOW FULL TABLES command as follows:

mysql> SHOW FULL TABLES;
+--------------------+------------+
| Tables_in_yiibaidb | Table_type |
+--------------------+------------+
| article_tags | BASE TABLE |
| contacts | BASE TABLE |
| customers | BASE TABLE |
| departments | BASE TABLE |
| employees | BASE TABLE |
| offices | BASE TABLE |
| offices_bk | BASE TABLE |
| offices_usa | BASE TABLE |
| orderdetails | BASE TABLE |
| orders | BASE TABLE |
| payments | BASE TABLE |
| productlines | BASE TABLE |
| products | BASE TABLE |
| saleperorder | VIEW |
+--------------------+------------+
14 rows in set

The table_type column in the result set specifies which object is a view and which object is a table (base table). As shown above, the value of the table_type column corresponding to saleperorder is: VIEW. However, if you want to query the total sales amount for each sales order, you only need to execute a simple SELECT statement on the SalePerOrder view, as shown below:

SELECT 
  *
FROM
  salePerOrder;

Execute the above query statement and get the following results:

+-------------+----------+
| orderNumber | total |
+-------------+----------+
| 10165 | 67392.85 |
| 10287 | 61402.00 |
| 10310 | 61234.67 |
| 10212 | 59830.55 |
|-- many many data omitted here-- |
| 10116 | 1627.56 |
| 10158 | 1491.38 |
| 10144 | 1128.20 |
| 10408 | 615.45 |
+-------------+----------+
327 rows in set

Let's create a view based on another view. For example, we can create a view called BigSalesOrder based on the SalesPerOrder view to display each sales order with a total greater than 60,000, as shown below:

CREATE VIEW BigSalesOrder AS
  SELECT 
    orderNumber, ROUND(total,2) as total
  FROM
    saleperorder
  WHERE
    total > 60000;

Now, we can query data from the BigSalesOrder view as follows:

SELECT 
  orderNumber, total
FROM
  BigSalesOrder;

Execute the above query statement and get the following results:

+-------------+----------+
| orderNumber | total |
+-------------+----------+
| 10165 | 67392.85 |
| 10287 | 61402.00 |
| 10310 | 61234.67 |
+-------------+----------+
3 rows in set

Now let's try to use inner join to create a view containing the customer number and the total amount paid by the customer, as shown below:

CREATE VIEW customerOrders AS
  SELECT 
    c.customerNumber,
    p.amount
  FROM
    customers
      INNER JOIN
    payments p ON p.customerNumber = c.customerNumber
  GROUP BY c.customerNumber
  ORDER BY p.amount DESC;

We use the following SQL to query the data in the customerOrders view:

+----------------+-----------+
| customerNumber | amount |
+----------------+-----------+
| 124 | 101244.59 |
| 321 | 85559.12 |
| 239 | 80375.24 |
| **** many many data omitted here ***|
| 219 | 3452.75 |
| 216 | 3101.4 |
| 161 | 2434.25 |
| 172 | 1960.8 |
+----------------+-----------+
98 rows in set

Now try to use a subquery to create a view that contains products whose prices are higher than the average price of all products, as follows:

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

Let's query the data of the aboveAvgProducts view:

SELECT 
  *
FROM
  aboveAvgProducts;

Execute the above query statement and get the following results:

+-------------+-----------------------------------------+----------+
| productCode | productName | buyPrice |
+-------------+-----------------------------------------+----------+
| S10_4962 | 1962 LanciaA Delta 16V | 103.42 |
| S18_2238 | 1998 Chrysler Plymouth Prowler | 101.51 |
| S10_1949 | 1952 Alpine Renault 1300 | 98.58 |
|**************** many many data is omitted here*********************************|
| S18_3320 | 1917 Maxwell Touring Car | 57.54 |
| S24_4258 | 1936 Chrysler Airflow | 57.46 |
| S18_3233 | 1985 Toyota Supra | 57.01 |
| S18_2870 | 1999 Indy 500 Monte Carlo SS | 56.76 |
| S32_4485 | 1974 Ducati 350 Mk3 Desmo | 56.13 |
| S12_4473 | 1957 Chevy Pickup | 55.7 |
| S700_3167 | F/A 18 Hornet 1/72 | 54.4 |
+-------------+-----------------------------------------+----------+
54 rows in set

Well, here we have almost covered the creation and use of views. However, are there no restrictions on the use of views? The answer is of course yes, let’s take a look at them one by one.

First of all, we cannot create indexes on views. Secondly, when querying data using a view that uses the merge algorithm, MySQL will use the index of the underlying table. Also, for views that use the temptation algorithm, when we query data against the view, the index will not be used.

Also, please note that in versions prior to MySQL 5.7.7, you cannot use subqueries in the FROM clause of a SELECT statement to define a view.

If you drop or rename the table on which the view is based, MySQL will not issue any errors. However, mysql will invalidate the view, we can use the CHECK TABLE statement to check whether the view is valid.

A simple view can update data in a table, but a view created based on a complex select statement with joins, subqueries, etc. cannot be updated.

MySQL does not support physical views like other database systems such as Oracle and PostgreSQL. MySQL does not support physical views.

Well, that’s all I have to say about views this time.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.