Shulou(Shulou.com)06/01 Report--

This article gives an example of creating views (CREATE VIEW) and usage restrictions for mysql views. Share with you for your reference, the details are as follows:

Database views are supported after the mysql5.x version, and in mysql, almost the characteristics of the views conform to the SQL:2003 standard. Mysql handles queries to 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 the temporary table. In the second way, MySQL defines the incoming query and query as a query and executes a composite query.

Mysql supports views of a version of the system, and each time the 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.

All right, let's not dwell on it. Let's try to create a view using the CREATE VIEW statement. Let's take a 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 the meanings of the various words in the sql above. First of all, the first parenthesis represents the algorithmic properties of creating views, which allows us to control the mechanism that mysql uses when creating views, and mysql provides three algorithms: MERGE,TEMPTABLE and UNDEFINED. Let's take a look at this 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 composite 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,havaing,limit,union,union all, a subquery, the MERGE algorithm is not allowed. The MERGE algorithm is also not allowed if the select statement does not have a reference table. 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 a single 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 temporary tables 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 that use 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 of using either the MERGE or TEMPTABLE algorithm. Mysql gives priority to MERGE algorithm for 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 convention of the table.

Finally, there is the SELECT statement. In the 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:

SELECT statements can include subqueries in where statements, but subqueries cannot be included in the FROM clause. SELECT statements cannot refer to any variables, including local variables, user variables, and session variables. The SELECT statement cannot refer to the parameters of the prepare statement.

We need to note here that the SELECT statement does not need to reference any tables. When we're done, let's try to create a view that represents the total sales of each 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, you have to 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 a table (base table). As shown above, the value of the table_type column for saleperorder is: VIEW. However, if you want to query the total sales for each 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 |-- many many data is 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, a view called large sales order (BigSalesOrder) based on the SalesPerOrder view to display each sales order with a total greater than 60000, as follows:

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

We can now query the data from the BigSalesOrder view, as follows:

SELECT orderNumber, totalFROM 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

When we're done, 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 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:

+-+-+ | customerNumber | amount | +-+-+ | 101244.59 | 101244.59 | 85559.12 | | 239 | 80375.24 | * many many data * omitted here * | 219 | 3452.75 | 216 | 3101. | 4 | | 161 | 2434.25 | | 1960.8 | +-+-+ 98 rows in set |

Then try to use a subquery to create a view of a product whose price is 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 for the aboveAvgProducts view:

SELECT * FROM aboveAvgProducts

Execute the above query statement and get the following results:

+-+-+ | productCode | productName | buyPrice | +-+- -+-+ | S10room4962 | 1962 LanciaA Delta 16V | 103.42 | | S18room2238 | 1998 Chrysler Plymouth Prowler | 101.51 | | S10room1949 | 1952 Alpine Renault 1300 | 98.58 | * many many data is omitted here * * | | S18room3320 | 1917 Maxwell Touring Car | 57.54 | | S24room4258 | 1936 Chrysler Airflow | 57.46 | | S18room3233 | 1985 Toyota Supra | 57.01 | | S18room2870 | 1999 Indy Monte Carlo SS | 56.76 | | S32 Ducati 4485 | 1974 Ducati 350 Mk3 Desmo | 56.13 | S12room4473 | 1957 Chevy Pickup | 55.7 | S7003167 | F | / A 18 Hornet 1 Hornet 72 | 54.4 | +-+ 54 rows in set

Well, that's it. The creation and use of views have been introduced almost. But aren't there any restrictions on the use of views? Of course the answer is yes, let's take a look at it separately.

First of all, we cannot create indexes on views, and then when views that use the merge algorithm query data, mysql uses the index of the underlying table, and for views that use temptation algorithms, when we query data against views, indexes are not used.

It is also important to note that prior to mysql5.7.7, views cannot be defined using subqueries in the FROM clause of the SELECT statement.

Then mysql does not issue any errors if you delete or rename the table on which the view is based. However, mysql invalidates the view, and we can use the CHECK TABLE statement to check whether the view is valid.

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

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

All right, that's all for the view this time.

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection", "MySQL Database Lock related skills Summary" and "MySQL Common function Summary".

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.