SQL must-know note the 18th Chapter uses the view

18.1 views

The view is a virtual table. Unlike a table that contains data, attempts to include only queries that retrieve data dynamically when they are used.
The entire query can be wrapped into a virtual table named Productcustomers, which makes it easy to retrieve the same data as follows.

SELECT cust_name,cust_contactFROM ProductCustomersWHERE prod_id = ‘RGAN01‘;

Productcustomers is a view that, as a view, does not contain any columns or data, it contains a query.

18.1.1 Why use views

(1) Reusing SQL statements
(2) Simplify complex SQL operations. After writing the query, it is easy to reuse it without having to know the details of his basic query.
(3) Use the parts of the table instead of the entire table.
(4) Protect the data. You can give a user access to a specific part of a table rather than an entire table.
(5) Change of data but and expression. Views can return data that differs from the representation and format of the underlying table.

Rules and restrictions for 18.1.2 views

Some of the most common rules and restrictions on view creation and use
(1) The view must be uniquely named as a table
(2) There is no limit to the number of views that can be worn
(3) In order to create a view, you must have sufficient access rights. These permissions are typically granted by database administrators.
(4) Views can be nested, that is, you can construct a view with queries that retrieve data from other views.
(5) Many DBMS prohibit the use of an ORDER BY clause in a view query.
(6) Some DBMS requires naming all the columns returned, and if the column is a calculated field, an alias will be used.
(7) The view cannot be indexed, nor can it have associated triggers or default values.
(8) Some DBMS use the view as a read-only query, which means that data can be retrieved from the view, but the data cannot be written to the underlying table.
(9) Some DBMS allow the creation of such views, which do not allow inserts or updates that cause rows to no longer belong to the view.

18.2 Creating a View

The view is created with the CREATE VIEW statement. Create view can only be used for creating views that do not exist.
To delete a view with drop:

DROP VIEW viewname;

Overwrite (or update) The view: You must drop it first, and then recreate it.

18.2.1 using views to simplify complex joins

CREATE VIEW ProductCustomers ASSELECT cust_name,cust_contact,prod_idFROM Customers,Orders,OrderItemsWHERE Customers.cust_id = Order.cust_id     AND OrderItems.order_num = Order.order_num;

This statement creates a view named Productcustomers that joins a table to return a list of all customers who have ordered any product.
Search for customers who ordered the product RGAN01

SELECT cust_name,cust_contactFROM ProductCustomersWHERE prod_id = ‘RGAN01‘;

18.2.2 to reformat the retrieved data with a view

Create a formatted view

CREATE VIEW VendorsLocations ASSELECT RTRM(vend_name) || ‘ ( ‘ || RTRIM(vend_country) || ‘ ) ‘ AS vend_titleFROM Vendors;

18.2.3 trying to filter unwanted data

Define the Customeremaillist view to filter customers without e-mail addresses.

CREATE VIEW CustomerEMailList ASSELECT cust_id, cust_name, cust_emailFROM CustomersWHERE cust_email IS NOT NULL;

18.2.4 using views and calculated fields

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

CREATE VIEW OrderItemExpanded ASSELECT order_num,     prod_id,     quantity,     item_price,     quantity*item_price AS expanded_price

from OrderItems;
Retrieve the details of order 20008 according to the view

SELECT *FROM OrderItemsExpandedWHERE order_num = 20008;

SQL must-know note the 18th Chapter uses the view