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

This paper gives an example of how to create an updatable view of mysql view. Share with you for your reference, the details are as follows:

We know that in mysql, views are not only queryable, but also updatable. This means that we can use insert or update statements to insert or update rows of the base table through updatable views. In addition, we can use the delete statement to delete the rows of the underlying table through the view. However, to create an updatable view, the select statement that defines the view cannot contain any of the following elements:

Aggregate functions, such as min,max,sum,avg,count, etc. DISTINCT clause GROUP BY clause HAVING clause left join or outer join. A subquery in the SELECT clause of the UNION or UNION ALL clause or a subquery in the where clause that references the table appears in the FROM clause. Referencing a non-updatable view in the FROM clause references only multiple references of text values to any column of the base table

If we use the temptable algorithm to create views, we cannot update them, but sometimes we can use internal joins to create updatable views based on multiple tables. Needless to say, let's first look at how to create an updatable view. Let's first try to create a view called officeInfo based on the offices table, which refers to the three columns in the offices table: officeCode,phone and city:

CREATE VIEW officeInfo AS SELECT officeCode, phone, city FROM offices

Next, query the data from the officeInfo view using the following statement:

SELECT * FROM officeInfo

Execute the above query statement and get the following results:

Mysql > SELECT * FROM officeInfo +-+ | officeCode | phone | city | +-+ | 1 | + 1 650 219 4782 | San Francisco | | 2 | | + 1 215 837 0825 | Boston | | 3 | + 1 212 555 3000 | NYC | | 4 | + 33 14 723 4404 | Paris | | 5 | + 86 33 224 5000 | Beijing | | 6 | + 61 2 9264 2451 | Sydney | | 7 | + 44 20 7877 2041 | London | +-+ 7 rows in set |

Then, use the following update statement to change the office phone number with the value of officeCode: 4 through the officeInfo view:

UPDATE officeInfoSET phone ='+ 86 089866668888'WHERE officeCode = 4

Finally, verify the result of the change and query the data in the officeInfo view by executing the following query:

Mysql > SELECT * FROM officeInfoWHERE officeCode = 4 +-+ | officeCode | phone | city | +-+ | 4 | + 86 089866668888 | Paris | +-+- -+-+ 1 row in set

When done, we can check whether the views in the database are updatable by querying the is_updatable column from the views table in the information_schema database. For example, let's query the luyaran database to get all the views and show which views are updatable:

SELECT table_name, is_updatableFROM information_schema.viewsWHERE table_schema = 'luyaran'

Execute the above query statement and get the following results:

+-+ | table_name | is_updatable | +-+-+ | aboveavgproducts | YES | | bigsalesorder | YES | | customerorders | NO | | officeinfo | YES | | saleperorder | NO | +- -+-+ 5 rows in set

Let's try to delete rows through the view again. First, create a table named items, insert some rows in the items table, and create a view with a query containing items with a price greater than 700:

USE testdb;-- create a new table named itemsCREATE TABLE items (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR (100,100) NOT NULL, price DECIMAL (11,2) NOT NULL);-- insert data into the items tableINSERT INTO items (name,price) VALUES ('Laptop',700.56), (' Desktop',699.99), ('iPad',700.50);-- create a view based on items tableCREATE VIEW LuxuryItems AS SELECT * FROM items WHERE price > 700 Malaysia-query data from the LuxuryItems viewSELECT * FROM LuxuryItems

After executing the above query, you get the following results:

+-+ | id | name | price | +-+ | 1 | Laptop | 700.56 | 3 | iPad | 700.5 | +-+ 2 rows in set

When finished, use the delete statement to delete the row with id 3:

DELETE FROM LuxuryItemsWHERE id = 3

Mysql returns a message indicating that 1 line is affected:

Query OK, 1 row affected

Let's examine the data again through the view:

Mysql > SELECT * FROM LuxuryItems;+----+ | id | name | price | +-+ | 1 | Laptop | 700.56 | +-+ 1 row in set

We can also query the data from the base table items to verify that the DELETE statement actually deleted the row:

Mysql > SELECT * FROM items;+----+ | id | name | price | +-- + | 1 | Laptop | 700.56 | 2 | Desktop | 699.99 | +-+ 2 rows in set

We can see that the row with an ID of 3 is deleted in the base table.

All right, that's all for this record.

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.