Detailed explanation of the role of MySQL views (2) - filtering data, calculating fields, and updating views

Use views to filter unwanted data

Views are also useful for applying ordinary WHERE clauses. For example, you can define a customeremaillist view that filters customers without email addresses. For this purpose, the following statement can be used:

Input:

create view customeremaillist as select cust_id,cust_name,cust_email from customers where cust_email is not null;

Analysis: Obviously, when sending an email to a mailing list, you need to exclude users who do not have an email address. The WHERE clause here filters those rows with NULL values ​​in the cust_email column so that they are not retrieved.

Now, you can use the view customeremaillist like any other table.

Input:

select * fromcustomeremaillist;

Output:

WHERE clause and WHERE clause If a WHERE subclause is used when retrieving data from the view clause, the two sets of clauses (one in the view and the other passed to the view) are automatically combined.

Using Views with Calculated Fields

Views are particularly useful for simplifying the use of calculated fields. Below is a SELECT statement introduced in Chapter 10. It retrieves the items in a specific order and calculates the total price for each item:

Input:

select prod_id,quantity,item_price,quantity*item_price as expanded_price from orderitems where order_num = 20005;

Output:

To convert it into a view, proceed as follows:

Input:

create view orderitemsexpanded as select prod_id,quantity,item_price,quantity*item_price as expanded_price from orderitems;

To retrieve the details of order 20005 (the output above), proceed as follows:

Enter:

select * from orderitemsexpanded where order_num = 20005;

Output:

As you can see, the view is very easy to create and easy to use. Used correctly, views can greatly simplify complex data processing.

Updating views

All views so far are used with SELECT statements. However, can the view's data be updated? The answer depends on the situation.

Typically, views are updatable (that is, INSERT , UPDATE , and DELETE can be used on them). Updating a view updates its base table (recall that the view itself has no data). If you add or delete rows to a view, you are actually adding or deleting rows to its base table.

However, not all views are updateable. Basically it can be said that if MySQL cannot correctly determine the base data being updated, updates (including inserts and deletes) are not allowed. This actually means that if there are the following operations in the view definition, the view cannot be updated:

1. Grouping (using GROUP BY and HAVING);

2. Join;

3. Subquery;

4. Union;

5. Aggregation function (Min(), Count(), Sum(), etc.);

6.DISTINCT;

7.Export (calculated) column.

In other words, the views in many of the examples in this chapter are not updatable. This may sound like a serious limitation, but it is not, since views are primarily used for data retrieval.

Possible changes The limitations listed above are correct since MySQL 5. However, future versions of MySQL are likely to lift some restrictions.

Use views for retrieval Generally, views should be used for retrieval (SELECT statement) rather than for updates (INSERT, UPDATE and DELETE).

Summary:

Views are virtual tables. They contain not data but queries to retrieve the data as needed. Views provide a hierarchical encapsulation of MySQL SELECT statements that can be used to simplify data processing and reformat or protect the underlying data.

The above is the detailed content of Detailed explanation of the role of MySQL views (2) - filtering data, calculating fields, and updating views. For more information, please follow other related articles on the PHP Chinese website!