创建mysql可更新视图

Summary: in this tutorial, we will show you how to create an updateable view and update data in the underlying table through the view.

In MySQL, views are not only read-only but also updateable. However in order to create an updateable view, the SELECT statement that defines the view has to follow several following rules:

• The SELECT statement must only refer to one database table.

• The SELECT statement must not use GROUP BY or HAVING clause.

• The SELECT statement must not use DISTINCT in the column list of the SELECT clause.

• The SELECT statement must not refer to read-only views.

• The SELECT statement must not contain any expression (aggregates, functions, computed columns…)

When you create updateable views, make sure that you follow the rules above.

Example of creating updateable view

Let’s practice with an example of creating an updateable view.

First, we create a view named officeInfo against the offices table. The view refers to three columns of the offices table:  officeCode, phone and city.

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

Next, we can query data from the officeInfo view using the SELECT statement.

SELECT * FROM officeInfo

Then, we can change the phone number of the office with officeCode 4 through the officeInfo view by using the UPDATE statement.

UPDATE officeInfo SET phone = '[phone]' WHERE officeCode = 4

Finally, to see the change, we can select the data from the officeInfo view by executing following query:

SELECT * FROM officeInfo WHERE officeCode = 4

In this tutorial, we have shown you how to create an updateable view and how to update data in the underlying table through the view.

Related Tutorials

• Views in MySQL

• Introduction to Database View

• Managing Views in MySQL

原文链接：http://outofmemory.cn/mysql/view/create-sql-updatable-views