mysql View Creation View (CREATE VIEW) and Usage Restriction Example Explanation

This article illustrates the creation view of the mysql view (CREATE VIEW) and its usage limitations. Share it for your reference, as follows:

Database views are supported after mysql 5. x, and in mysql, almost the characteristics of the view 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 an incoming query on this temporary table. In the second way, MySQL defines the incoming query and query as one query and executes a combined query.

mysql supports views of version systems, and each time a view is changed or replaced, a copy of the view is backed up in the arc (archive) folder that resides in a specific database folder. The name of the backup file is view_name. frm-00001. If you change 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.

Ok, let's not go into details. Next, let's try to use CREATE VIEW statement to create a view. First, let's look at the syntax structure:

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 sql above mean. First of all, the first bracket represents the algorithm attribute of creating views, which allows us to control the mechanism used by mysql when creating views, and mysql provides three algorithms: MERGE, TEMPTABLE and UNDEFINED. Let's take a look at it 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 a combined query to return a result set. The MERGE algorithm is not allowed if the select statement contains aggregate functions (such as min, max, sum, count, avg, etc.) or distinct, group by, havaing, limit, union, union all, subqueries. If the select statement does not reference tables, the MERGE algorithm is also not allowed. If the MERGE algorithm is not allowed, mysql changes the algorithm to UNDEFINED. We should note that combining the input query and query in the view definition into one query is called view resolution. Using the TEMPTABLE algorithm, mysql first creates a temporary table based on the SELECT statement that defines the view, and then executes an input query against the temporary table. Because mysql must create a temporary table to store the result set and move data from the base table to the temporary table, the TEMPTABLE algorithm is less efficient than the MERGE algorithm. In addition, views using the TEMPTABLE algorithm are not updatable. When we create views without specifying an explicit algorithm, UNDEFINED is the default algorithm. The UNDEFINED algorithm gives mysql the option to use either the MERGE or the TEMPTABLE algorithm. mysql prefers MERGE algorithm to TEMPTABLE algorithm because MERGE algorithm is more efficient.

Then there is the phrase after view, which means name. In a 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 rules of the table.

Finally, there is the SELECT statement. In an SELECT statement, you can query data from any table or view that exists in the database, and the SELECT statement must follow the following rules:

The SELECT statement can include subqueries in the where statement, but the FROM clause cannot. SELECT statements cannot reference any variables, including local variables, user variables, and session variables. SELECT statements cannot reference parameters of prepared statements.

Here we have to note a little that the SELECT statement does not need to reference any tables. After that, let's try to create a view representing the total sales per order based on the orderDetails table:

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 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 the view and which is the 1 table (base table). As shown above, the value of saleperorder for the column table_type is: VIEW. However, if you want to query the total sales per sales order, you only need to execute a simple SELECT statement on the SalePerOrder view, as follows:

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 |
|--  Omitted here many many Data -- |
|    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, a view called Large Sales Order (BigSalesOrder) based on the SalesPerOrder view to show each sales order that totals more than 60,000, as follows:

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

Let's try to use inner join to create a view with the customer number and the total amount paid by the customer, as follows:

CREATE VIEW customerOrders AS
  SELECT 
    c.customerNumber,
    p.amount
  FROM
    customers c
      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:

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

0

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

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

1

Let's query the data of the aboveAvgProducts view:

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

2

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  |
|*************  Omitted here many many Data  *********************************|
| 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

Ok, that's it. The creation and use of views have been introduced almost. However, are there no restrictions on the use of views? Of course, the answer is yes. Let's take a look at it separately.

First, we can't create an index on the view, then mysql uses the index of the underlying table when querying data for the view using merge algorithm, and then, for the view using temptation algorithm, we don't use the index when querying data against the view.

Also note that prior to mysql 5.7. 7, it was not possible to use subqueries to define views in the FROM clause of an SELECT statement.

Again, mysql does not issue any errors if the table on which the view is based is deleted or renamed. However, mysql invalidates the view, and we can use the CHECK TABLE statement to check that the view is valid.

A simple view can update the data in the table, but views created based on complex select statements with joins, subqueries, and so on cannot.

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

Ok, that's all for the view.

More readers interested in MySQL can check out the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Transaction Operation Skills Summary", "MySQL Stored Procedure Skills Encyclopedia", "MySQL Database Lock Skills Summary" and "MySQL Common Functions Summary"

I hope this article is helpful to everyone's MySQL database.