View View table, view (SQL View)

 

View is a data table generated by the dynamic combination of SQL SELECT queries (that is, View is a data table composed of the result set obtained by the query). The data records in View are generated from other actual data tables. It is like a virtual data table. In fact, this data table does not exist in the database (or hard disk) (only this Related definitions of View), but we use it like an actual data table-all SQL query syntax can be operated on this View.

The data table is a physical structure, and the View is a virtual structure.

What are the features of View

1. To enhance the security of the database, View can hide the structure of the physical data table, while restricting which data table fields the user can only view and use.
2. The view table is read-only, that is, external users cannot directly modify internal data through the View.
3. Wrapping complex SQL queries in View can simplify the complexity of the query 度.
4. When the structure of the data table changes, only the setting of View needs to be changed, and the program needs to be changed.

Create View (SQL CREATE VIEW)

CREATE VIEW view_name [(column_list)] AS
SELECT column_name(s)
FROM table_name
WHERE condition;

E.g:

CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;

A View can be created by querying the entity data table, or by querying other existing Views.

Update View (SQL CREATE OR REPLACE VIEW)

If we add OR REPLACEclause means that if the same name already exists on the View coverage to replace it. If View does not exist, we can regard CREATE OR REPLACE VIEW as CREATE VIEW; and if View already exists, we can regard CREATE OR REPLACE VIEW as ALTER VIEW.

CREATE OR REPLACE VIEW view_name [(column_list)] AS
SELECT column_name(s)
FROM table_name
WHERE condition;

Delete View (SQL DROP VIEW)

DROP VIEW view_name;

View usage example (Example)

Suppose this is a product order data table p_orders:

Product	Price	Quantity
LCD	4000	100
CPU	5000	200

We can create a View that is convenient for querying the total sales of each product:

CREATE VIEW view_p_sum (Product, P_SUM) AS
SELECT Product, Price*Quantity FROM p_orders GROUP BY Product;

Then, you can operate the general data table like:

SELECT * FROM view_p_sum

Product	P_SUM
CPU	1000000
LCD	400000