MySQL Learning note 20

20. Using views

1. View

The view is a virtual table. Unlike a table that contains data, a view contains only queries that retrieve data dynamically when used.

Example: SELECT cust_name, Cust_contact

From customers, orders, OrderItems

WHERE customers.cust_id = orders.cust_id

and Orderitems.order_num = Order.order_num

and prod_id = ' TNT2 ';

This query is used to retrieve users who have ordered a specific product. You can see that the search statement is more complex, and if you want to retrieve the same data for another product, you must modify the final where clause.

If you wrap the entire query as a virtual table named Productcustomers, you can easily retrieve the same data as follows:

SELECT Cust_name, Cust_contact

From Productcustomers

WHERE prod_id = ' TNT2 ';

Why use views :

• Reusing SQL statements
• Simplify Complex SQL operations
• Use parts of a table instead of the entire table
• Protect data
• Changing data formats and representations

A view is simply a facility for viewing data stored elsewhere, and the view itself does not contain data.

Rules and restrictions for views:

• As with tables, views must be uniquely named.
• There is no limit to the number of views you can create
• In order to create a view, you must have sufficient access rights
• Views can be nested. That is, you can construct a view with queries that retrieve data from other views
• The order by can be used in the view, but if the SELECT statement that retrieves data from the view also contains an order by, the order by in that view will be overwritten
• Views cannot be indexed, and cannot have associated triggers or default values
• Views can be used with tables

2. Using views

Creation of the View:

• Views are created with the CREATE VIEW statement
• Use show create VIEW viewname to view the statements that created the views
• Delete the view with Dro, whose syntax is drop view viewname;.
• When you update a view, you can use the drop and create again, or you can REPLACR view directly with Create or. If the view that you want to update does not exist, the second UPDATE statement creates a view, and if the updated view exists, the second UPDATE statement replaces the original view.

(1), using views to simplify complex junctions

Example: CREATE VIEW productcustomers as

SELECT Cust_name, Cust_contact, prod_id

From customers, orders, OrderItems

WHERE customers.cust_id = orders.cust_id

and orderitems.order_num = Orders.order_num;

This statement creates a view named Productcustomers that joins three tables to return a list of all customers who have ordered any product. If you execute select * from Productcustomers, customers who have ordered any product are listed.

In order to retrieve the customer who ordered the product TNT2, the following can be done:

SELECT Cust_name, Cust_contact

From Productcustomets

WHERE prod_id = ' TNT2 ';

As you can see, the view greatly simplifies the use of complex SQL statements. With views, you can write the underlying SQL once and then use it as many times as you want.

(2), reformat the retrieved data with a view

The following SELECT statement returns the vendor name and location in a single combined computed column:

SELECT Contact (RTrim (vend_name), ' (', RTrim (vend_country), ') ')

As Vend_title

From vendors

ORDER by Vend_name;

Converting this statement to a view can be done as follows:
creat VIEW Vendorlocations as

SELECT Contact (RTrim (vend_name), ' (', RTrim (vend_country), ') ')

As Vend_title

From vendors

ORDER by Vend_name;

(3), using the view to filter unwanted data

Views are also useful for applying a common where clause. For example, you can define a customeremaillist view to filter customers without e-mail:

CreateView Customeremaillist as

selectcust_id, Cust_name, Cust_email

From customers

WHERE Cust_eamil is not NULL;

(4), using views and calculated fields

Views are particularly useful for simplifying the use of calculated fields.

For example: Retrieve items from a particular order and calculate the total price for each item:

SELECT prod_id,

Quantity

Item_price,

Quantity*item_price as Expanded_price

From OrderItems

WHERE order_num = 20005;

Convert it to a view:

CREATE VIEW orderitemsexpanded as

SELECT Order_num,

PROD_ID,

Quantity

Item_price,

Quantity*item_price as Expanded_price

from OrderItems;

(5), update view

Whether the view's data can be updated depends on the situation.

Typically views are updatable (that is, you can use INSERT, UPDATE, and delete for them). Updating a view will update its base table. If you add or delete rows to a view, you are actually adding or deleting rows to the base table.

However, not all views are updatable. If MySQL does not correctly determine the base data that is being updated, it is not allowed to update. If the view defines the following actions, the view cannot be updated:

• Grouping (using GROUP by and having);
• Coupling
• Sub-query;
• and
• Aggregation function (Min (), Count (), Sum (), etc.);
• DISTINCT;
• Export (computed) columns.

MySQL Learning note 20