MySQL中的視圖(第十六課)

Views in MySQL

MySQL中的視圖

In this part of the MySQL tutorial, we will mention views.

在MySQL教程的這一部分中，我們將提及視圖。

View definition

視圖定義

A view is a specific look on data from one or more tables. It can arrange data in some specific order, highlight or hide some data. A view consists of a stored query accessible as a virtual table composed of the result set of a query. Unlike ordinary tables a view does not form part of the physical schema. It is a dynamic, virtual table computed or collated from data in the database.

視圖是對一個或多個表中數據的特定外觀。 它可以按特定順序排列數據，突出顯示或隱藏某些數據。 視圖由存儲的查詢組成，該查詢可作為由查詢結果集組成的虛擬表訪問。 與普通表不同，視圖不構成物理模式的一部分。 它是根據數據庫中的數據計算或整理的動態虛擬表。

A view is a pseudo table. It is a stored query which looks like a table. And it can be referenced like a table.

視圖是偽表。 它是一個存儲查詢，看起來像一個表。 它可以像表一樣被引用。

Views can restrict users to specific rows or columns and thus enhance security. They can be used to join columns from multiple tables, so that they look like a single table. They can be used to provide aggregate information.

視圖可以將用戶限制為特定的行或列，從而增強安全性。 它們可用於連接多個表中的列，使它們看起來像一個表。 它們可用於提供彙總信息。

There are several restrictions that apply to views. Here are some of them:

有幾個限制適用於視圖。 這裡是其中的一些：

• The SELECT statement cannot contain a subquery //SELECT語句不能包含子查詢
• The SELECT statement cannot refer to system or user variables//SELECT語句不能引用系統或用戶變量
• Any table or view referred to in the definition must exist//定義中引用的任何表或視圖都必須存在
• A temporary VIEW cannot be created//無法創建臨時視圖
• A VIEW cannot be associated with a trigger//VIEW不能與觸發器關聯

Creating, modifying and dropping a View

創建，修改和刪除視圖

In the next example, we create a simple view. We use CREATE VIEW syntax to create a view.

在下一個示例中，我們創建一個簡單的視圖。 我們使用CREATE VIEW語法創建一個視圖

<code>mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id |
 Name       
| Cost   |
+----+------------+--------+
|  1 |
 Audi       
|  52642 |
|  2 |
 Mercedes   
|  57127 |
|  3 |
 Skoda      
|   9000 |
|  4 |
 Volvo      
|  29000 |
|  5 |
 Bentley    
| 350000 |
|  6 |
 Citroen    
|  21000 |
|  7 |
 Hummer     
|  41400 |
|  8 |
 Volkswagen 
|  21600 |
+----+------------+--------+ 
/<code>

This is our data, upon which we create the view.

這是我們的數據，我們在該數據上創建視圖。

<code>mysql> CREATE VIEW CheapCars 
AS 
 
    -> SELECT Name FROM Cars WHERE Cost<
25000
;
/<code>

We create a view CheapCars. These are cars which cost under 25000.

我們創建一個視圖CheapCars。 這些是價格低於25000的汽車。

<code>mysql> SELECT * FROM CheapCars;
+------------+
| Name       |
+------------+
| Skoda      |
| Citroen    |
| Volkswagen |
+------------+
/<code>

A view is a database object than can be queried. There are three cars which are considered to be cheap.

視圖是無法查詢的數據庫對象。 有三輛被認為很便宜的汽車。

<code>mysql> ALTER VIEW CheapCars AS SELECT Name FROM Cars
    -> WHERE Cost<
30000
;

mysql> SELECT * FROM CheapCars;
+------------+
| Name       |
+------------+
| Skoda      |
| Volvo      |
| Citroen    |
| Volkswagen |
+------------+
/<code>

We can redefine a view. Say we now consider a car to be cheap if it costs under 30000. We use the ALTER VIEW statement to modify our view.

What happens to a view if we delete a table, from which the data is selected?

我們可以重新定義視圖。 假設我們現在認為如果汽車的價格低於30000，則它會便宜。我們使用ALTER VIEW語句修改視圖。

如果我們刪除從中選擇數據的表，對視圖會發生什麼？

<code>mysql> DROP TABLE Cars;

mysql> SELECT * FROM CheapCars;
ERROR 
1356
 (HY000): View 
'mydb.CheapCars'
 references invalid 
table
(s) 
or
 column(s) 
or
 
function
(s)
 
or
 definer/invoker of view lack rights to use them
/<code>

Querying the view we receive the above error.

查詢視圖時，我們收到上述錯誤。

<code>mysql> SOURCE cars.sql

mysql> SELECT * FROM CheapCars;
+------------+
| Name       |
+------------+
| Skoda      |
| Citroen    |
| Volkswagen |
+------------+
/<code> 

When we recreate the table the view works again.

當我們重新創建表時，視圖將再次起作用.

<code>
 DROP VIEW CheapCars;
/<code>

Finally, a view is deleted with the DROP VIEW syntax.

最後，使用DROP VIEW語法刪除視圖.

Finding views

尋找視圖

We will mention several ways how to find views in MySQL database.

我們將提到幾種在MySQL數據庫中查找視圖的方法。

<code>mysql> SHOW FULL TABLES;
+----------------+------------+
| Tables_in_mydb |
 Table_type 
|
+----------------+------------+
|
 AA             
| BASE TABLE |
...
| Chars          |
 BASE TABLE 
|
|
 CheapCars      
| VIEW       |
| Customers      |
 BASE TABLE 
|
| 
 Dates          
| BASE TABLE |
| Decimals       |
 BASE TABLE 
|
|
 FavoriteCars   
| VIEW       |
...
/<code>

We can list all tables in a database with a SHOW FULL TABLES statement. In the Table_type column we can see, whether it is a table or a view.

我們可以使用SHOW FULL TABLES語句列出數據庫中的所有表。 在Table_type列中，我們可以看到它是表還是視圖。

<code>mysql> SELECT TABLE_NAME, TABLE_TYPE FROM information_schema.TABLES;
+---------------------------------------+-------------+
| TABLE_NAME                            |
 TABLE_TYPE  
|
+---------------------------------------+-------------+
|
 CHARACTER_SETS                        
| SYSTEM VIEW |
| COLLATIONS                            |
 SYSTEM VIEW 
|
|
 COLLATION_CHARACTER_SET_APPLICABILITY 
| SYSTEM VIEW |
| COLUMNS                               |
 SYSTEM VIEW 
|
|
 COLUMN_PRIVILEGES                     
| SYSTEM VIEW |
| ENGINES                               |
 SYSTEM VIEW 
|
...
|
 Chars                                 
| BASE TABLE  |
| CheapCars                             |
 VIEW        
|
|
 Customers                             
| BASE TABLE  |
 
| Dates                                 |
 BASE TABLE  
|
|
 Decimals                              
| BASE TABLE  |
| FavoriteCars                          |
 VIEW        
|
...
/<code>

In the information_schema database there is a TABLES table. The TABLE_NAME and TABLE_TYPE columns give us information about table names and their types.

在information_schema數據庫中，有一個TABLES表。 TABLE_NAME和TABLE_TYPE列為我們提供了有關表名及其類型的信息。

<code>mysql> SELECT TABLE_NAME FROM information_schema.VIEWS;
+--------------+
| TABLE_NAME   |
+--------------+
| CheapCars    |
| FavoriteCars |
+--------------+
/<code>

This is the most straightforward way to find views. We query the VIEWS table of the information_schema database.

這是查找視圖的最直接的方法。 我們查詢information_schema數據庫的VIEWS表。

Creating a view with a UNION

使用UNION創建視圖

The UNION operator is used to combine result-sets of two or more SELECT statements. Each select must have the same number of columns.

UNION運算符用於合併兩個或多個SELECT語句的結果集。 每個SELECT必須具有相同的列數。

<code>mysql> CREATE VIEW FavoriteCars AS
    -> SELECT * FROM Cars WHERE Id=
7
    -> UNION SELECT * FROM Cars WHERE Id=
4
    -> UNION SELECT * FROM Cars WHERE Id=
5
;
/<code>

We create a view called FavoriteCars. In this view, we have three rows which are considered to be favourite. There are three SELECT statements combined with a UNION operator.

我們創建一個名為FavoriteCars的視圖。 在此視圖中，我們有三行被認為是最受歡迎的。 有三個SELECT語句與UNION運算符結合在一起。

<code>mysql> SELECT * FROM FavoriteCars;
+----+---------+--------+
| Id |
 Name    
| Cost   |
+----+---------+--------+
|  7 |
 Hummer  
|  41400 |
|  4 |
 Volvo   
|  29000 |
|  5 |
 Bentley 
| 350000 |
+----+---------+--------+
/<code>

This is a SELECT from the view.

In this part of the MySQL tutorial, we have worked with views.

這是視圖中的SELECT。

在MySQL教程的這一部分中，我們使用了視圖。

關鍵字: table +------------+ CheapCars