sql 視圖嵌套視圖
SQL | 觀看次數 (SQL | Views)
Views in SQL are virtual tables. A view also has rows and columns as they're during a real table within the database. We will create a view by selecting fields from one or more tables present within the database. A View can either have all the rows of a table or specific rows supported under certain conditions. A view is nothing quite a SQL statement that's stored within the database with an associated name. A view is a composition of a table within the sort of a predefined SQL query. All rows of a table or select rows from a table can be contained inside the view. A view is often created from one or many tables which depend on the written SQL query to make a view.
SQL中的視圖是虛擬表 。 視圖還具有行和列,就像它們在數據庫中的真實表中一樣。 我們將通過從數據庫中存在的一個或多個表中選擇字段來創建視圖。 視圖可以具有表的所有行或在特定條件下受支持的特定行。 視圖只不過是存儲在數據庫中且具有關聯名稱SQL語句。 視圖是預定義SQL查詢中的表的組成。 表的所??有行或表中的選擇行都可以包含在視圖內部。 通常從一個或多個表創建視圖,這些表取決于編寫SQL查詢來創建視圖。
Views, which are a kind of virtual tables allow users to try to the subsequent:
視圖是一種虛擬表,允許用戶嘗試以下操作:
Structure data during a way that users or classes of users find natural or intuitive.
在用戶或用戶類別覺得自然或直觀的方式下構造數據。
Restrict access to the info in such a way that a user can see and (sometimes) modify exactly what they have and no more.
限制訪問信息的方式,使用戶可以看到和(有時)準確地修改自己擁有的內容,而不再修改。
Summarize data from various tables which may be wont to generate reports.
匯總來自各種表的數據,這些數據可能不會生成報告。
Here we will discuss creating, deleting and updating views.
在這里,我們將討論創建,刪除和更新視圖。
創建視圖 (Creating the view)
Views can be created in SQL by using the CREATE VIEW command. This order gives the name to the view and determines the rundown of credits and tuples to be incorporated utilizing a subquery.
可以使用CREATE VIEW命令在SQL中創建視圖 。 該順序為視圖指定名稱,并使用子查詢確定要合并的學分和元組的精簡。
The syntax to create a view is given here,
這里給出了創建視圖的語法,
CREATE VIEW <view_name>
As <subquery>;
For example, the command to create a view containing details of books which belong to text-book and Language Book can be specified as:
例如,用于創建包含教科書和語言書的書的詳細信息的視圖的命令可以指定為:
CREATE VIEW Book_1
As SELECT *
FROM BOOK
WHERE Category IN ('Textbook','LanguageBook');
Output
輸出量

This command creates the view, named Book_1, having details of books satisfying the condition specified in the WHERE clause. The view created like this consists of all the attributes of Book relation also.
此命令創建名為Book_1的視圖,該視圖具有滿足WHERE子句中指定條件的書籍的詳細信息。 這樣創建的視圖也包含Book關系的所有屬性。
For example, consider the command given below:
例如,考慮以下命令:
CREATE VIEW Book_2(B_code,B_title,B_category,B_price)
As SELECT ISBN,Book_Tiltle,Category,Price
FROM Book
WHERE Category IN ('Textbook','LanguageBook');
Output
輸出量

This command creates a view Book_2, which consists of the attributes, ispn, book-name, categori, and price from the relation Book with new names, namely, b_code, b_title, b_category, and b_price respectively. Now queries can be performed on these views as they are performed on other relations.
此命令創建一個視圖Book_2 ,該視圖由關系Book中的屬性ispn , book-name , categori和price組成,具有新名稱,分別為b_code , b_title , b_category和b_price 。 現在,可以在這些視圖上執行查詢,就像在其他關系上執行查詢一樣。
Consider the example given below:
考慮下面給出的示例:
SELECT *
FROM Book_1;
Output
輸出量

SELECT *
FROM Book_2
WHERE price>300;
Output
輸出量

SELECT b_title, b_category
FROM Book_2
WHERE price BETWEEN 200 and 350;
Output
輸出量

Views can contain more than one relation. The views that depend on more than one relation are known as complex views. These types of views are inefficient as they are time-consuming to execute, especially if multiple queries are involved in the view definition. Since their content is not physically stored, they are executed whenever their reference is done inside the program.
視圖可以包含多個關系。 依賴于多個關系的視圖稱為復雜視圖 。 這些類型的視圖效率低下,因為它們執行起來很耗時,尤其是在視圖定義中涉及多個查詢的情況下。 由于它們的內容不是物理存儲的,因此只要在程序內部完成引用就可以執行它們。
更新視圖 (Updating Views)
A view can be updated based upon several conditions as mentioned below,
可以根據以下幾種條件來更新視圖,
Keyword DISTINCT should not be present in the SELECT statement.
關鍵字DISTINCT不應出現在SELECT語句中。
The summary function should not be there in the SELECT statement.
摘要函數不應在SELECT語句中存在。
Set function and Set Operations should not be present in the SELECT statement.
設置函數和設置操作不應出現在SELECT語句中。
ORDER BY clause should not be present in the SELECT statement.
SELECT語句中不應存在ORDER BY子句。
Multiple tables should not be contained in the FROM statement.
FROM語句中不應包含多個表。
Subqueries should not be present inside the WHERE clause.
子查詢不應出現在WHERE子句中。
A query written in SQL must not contain GROUP BY or HAVING.
用SQL編寫的查詢不得包含GROUP BY或HAVING 。
Columns that are calculated may not get updated.
計算的列可能不會更新。
If the view satisfies the above-mentioned conditions then the user or programmer is able to update the view. The code written below can be used for serving the purpose.
如果該視圖滿足上述條件,則用戶或程序員可以更新該視圖。 下面編寫的代碼可用于實現此目的。
UPDATE <Name of VIEW>
SET <parameter to be updated>=<value>
WHERE <condition>;
放下視圖 (Dropping View)
The view needs to be dropped (i.e. Deleted permanently) when not in further use. The syntax for doing so is,
不使用該視圖時,需要將其刪除(即永久刪除)。 這樣做的語法是
DROP VIEW <view_name>;
翻譯自: https://www.includehelp.com/sql/views.aspx
sql 視圖嵌套視圖