Explanation of knowledge related to SQL VIEW (view)

View is a visual table. This chapter explains how to create, update and delete views, which will be explained in this article.

SQL CREATE VIEW statement

What is a view?

In SQL, a view is a visual table based on the result set of a SQL statement.

A view contains rows and columns, just like a real table. The fields in the view are fields from real tables in one or more databases. We can add SQL functions, WHERE and JOIN statements to the view, and we can submit data as if it came from a single table.

Note: The design and structure of the database are not affected by functions, where or join statements in views.

SQL CREATE VIEW Syntax

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Note: The view always displays the most recent data. Whenever a user queries a view, the database engine reconstructs the data by using SQL statements.

SQL CREATE VIEW instance

The view can be used from within a query, from within a stored procedure, or from within another view . By adding functions, joins, and so on to the view, we can submit exactly the data we want to the user.

The sample database Northwind has some views installed by default. The view "Current Product List" lists all products in use from the Products table. This view is created using the following SQL:

CREATE VIEW [Current Product List] ASSELECT ProductID,ProductNameFROM ProductsWHERE Discontinued=No

We can query the above view:

SELECT * FROM [Current Product List]

Another view of the Northwind sample database selects all products in the Products table whose unit price is higher than the average unit price :

CREATE VIEW [Products Above Average Price] ASSELECT ProductName,UnitPriceFROM ProductsWHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)

We can query the above view like this:

SELECT * FROM [Products Above Average Price]

Another view instance from the Northwind database will calculate the total sales of each category in 1997. Please note that this view selects data from another view called "Product Sales for 1997":

CREATE VIEW [Category Sales For 1997] ASSELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySalesFROM [Product Sales for 1997]GROUP BY CategoryName

We can query the above view like this:

SELECT * FROM [Category Sales For 1997]

We can also query Add conditions to the query. Now, we just need to view all sales of the "Beverages" category:

SELECT * FROM [Category Sales For 1997]WHERE CategoryName='Beverages'

SQL Update View

You can use the following syntax to update the view:

SQL CREATE OR REPLACE VIEW Syntax
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

Now, we You want to add a "Category" column to the "Current Product List" view. We will update the view through the following SQL:

CREATE VIEW [Current Product List] ASSELECT ProductID,ProductName,CategoryFROM ProductsWHERE Discontinued=No

This article explains the relevant knowledge points of SQL VIEW (view). For more learning materials, please pay attention to the php Chinese website.

Related recommendations:

Explanations related to the SQL AUTO INCREMENT field

Related operations related to the SQL ALTER TABLE statement

Related knowledge about SQL undo indexes, tables and databases

The above is the detailed content of Explanation of knowledge related to SQL VIEW (view). For more information, please follow other related articles on the PHP Chinese website!