5. SQL--View

A view is a table that is visualized.

SQL CREATE View Statement What is a view?

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

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

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

SQL CREATE VIEW Syntax

CREATE VIEW view_name asselect column_name (s) from table_namewhere condition

Note: Views always display the most recent data. Each time a user queries the view, the database engine rebuilds the data by using SQL statements.

SQL CREATE VIEW Instance

You can use views from within a query, inside a stored procedure, or from within another view. By adding functions, joins, and so on to the view, we can accurately submit the data we wish to submit to the user.

Sample database Northwind has some views that are installed by default. The view "Current Product List" lists all products that are in use from the product 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 products with all units in the product table that are 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 calculates the total sales for each category in 1997. Notice that this view picks up the 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 add conditions to the query. Now, we just need to see all the 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:

CREATE OR REPLACE VIEW view_name asselect column_name (s) from table_namewhere condition

Now, we want to add the Category column to the current Product List view. We will update the view with the following SQL:

CREATE VIEW [Current Product List] asselect productid,productname,categoryfrom productswhere discontinued=no

SQL undo View

You can delete a view by using the drop View command.

DROP VIEW view_name

This article comes from the "Ricky's blog" blog, please be sure to keep this source http://57388.blog.51cto.com/47388/1622004

5. SQL--View