SQL view details

View

View is a virtual table. Unlike contained data tables, views only contain data that is dynamically retrieved when used by a query. As a view, it does not contain any columns or data, only a query.

Why use views?

1: Reuse sql statements.

2: Simplify complex sql operations. After you write a query, you can easily reuse it without knowing the details of its underlying query.

3: Use part of the table instead of the entire table.

4: Protect data. Users can be granted access to specific parts of a table rather than the entire table.

5: Change data format and representation. Views can return data that is represented and formatted differently than the underlying table.

After you create views, you can use them in much the same way as tables. You can perform SELECT operations on views, filter and sort data, and join views to other views or tables.

It is important to know that a view is simply a setting for viewing data stored elsewhere. The view itself contains no data, so the data returned is retrieved from other tables. As you add and change data in these tables, the view returns the changed data.

Some of the most common rules and restrictions on view creation and use

(1) Like tables, views must be uniquely named

(2)For items that can be worn There is no limit on the number of views

(3) In order to create a view, you must have sufficient access rights. These permissions are usually granted by the database administrator.

(4) Views can be nested, that is, a view can be constructed using queries that retrieve data from other views.

(5) Many DBMS prohibit the use of ORDER BY clause in view queries.

(6) Some DBMS require naming of all returned columns. If the column is a calculated field, you need to use aliases.

(7) Views cannot be indexed and cannot have associated triggers or default values.

(8) Some DBMS treat views as read-only queries, which means that data can be retrieved from the view, but 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.

Creating views

Views are created using the CREATE VIEW statement.

Use the DROP statement to delete a view, the syntax is: DROP VIEW viewname;

Use views to simplify complex connections

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 A view named ProductCustomers joins three tables to return a list of all customers who have ordered any product.

Retrieve customers who ordered product RGAN01

SELECT cust_name,cust_contactFROM ProductCustomersWHERE prod_id = 'RGAN01';

用视图重新格式化检索出的数据

创建格式化视图

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

用试图过滤不想要的数据

定义CustomerEMailList 视图，过滤没有电子邮件地址的客户。

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

使用视图与计算字段

检索某个特定订单中的物品，计算每种物品的总价格

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

根据视图检索订单20008的详细内容

SELECT *FROM OrderItemsExpandedWHERE order_num = 20008;

 以上就是SQL视图 详细介绍的内容，更多相关内容请关注PHP中文网（m.sbmmt.com）！