Πίνακας VIEW (Προοπτική) SQL

Views are visual tables.

This chapter explains how to create, update, and delete views.

SQL CREATE VIEW statement

What is a view?

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

Views contain rows and columns, like a real table. The fields in the view are from the actual fields of one or more tables in the database. 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 within the view.

SQL CREATE VIEW syntax

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

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

SQL CREATE VIEW example

Views can be used from within a query, within a stored procedure, or from another view. By adding functions, joins, etc., to the view, we can precisely submit the data we want to the user.

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

CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

We can query the above view:

SELECT * FROM [Current Product List]

Another view in the Northwind sample database selects all products from the Products table with unit prices above the average unit price:

CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)

Μπορούμε να κάνουμε την παρακάτω ερώτηση για αυτήν την προβολή:

SELECT * FROM [Products Above Average Price]

Another view instance from the Northwind database calculates the total sales of each category in 1997. Please note that this view retrieves data from another view named "Product Sales for 1997":

CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName, Σύνολο(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName

Μπορούμε να κάνουμε την παρακάτω ερώτηση για αυτήν την προβολή:

SELECT * FROM [Category Sales For 1997]

Μπορούμε επίσης να προσθέσουμε συνθήκες στην ερώτηση. Στην παρούσα κατάσταση, θέλουμε να δούμε την πώληση όλων των προϊόντων της κατηγορίας "Beverages":

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

Ενημέρωση προβολής SQL

Μπορείτε να χρησιμοποιήσετε την παρακάτω γραμματική για να ενημερώσετε τη προβολή:

Γραμματική του SQL CREATE OR REPLACE VIEW
CREATE OR REPLACE VIEW view_name AS
SELECT ονομασία_stolwn(s)
FROM ονομασία_tabutable
WHERE συνθήκη

Τώρα, θέλουμε να προσθέσουμε τη στήλη "Κατηγορία" στη προβολή "Current Product List". Θα ενημερώσουμε τη προβολή με την παρακάτω SQL:

CREATE VIEW [Current Product List] AS
SELECT ProductID, ProductName, Κατηγορία
FROM Προϊόντα
WHERE Διακομισμένο=Όχι

Ακύρωση προβολής SQL

Μπορείτε να διαγράψετε τη προβολή χρησιμοποιώντας την εντολή DROP VIEW.

Γραμματική του SQL DROP VIEW

DROP VIEW view_name