sql 视图嵌套视图_SQL视图–综合指南

╰+哭是因爲堅強的太久メ 2023-02-28 06:15 41阅读 0赞

sql 视图嵌套视图

Hey folks! In this article we will be focusing on **SQL Views**.

嘿伙计！ 在本文中，我们将重点介绍**SQL视图** 。

## 什么是SQL视图？ **(****What is a View in SQL?****)** ##

In [SQL][], a **View** can be considered as virtual access to the database in a restricted manner. SQL Views allow limiting the view access to a specific set of data for a set of users. You can create a view with a limited amount of data from one or more tables and columns.

在[SQL中][SQL] ，可以将**View**视为受限地对数据库的虚拟访问。 SQL视图允许将视图访问限制为一组用户对一组特定数据的访问。 您可以使用一个或多个表和列中的有限数量的数据创建视图。

--------------------

## 需要SQL视图 **(****Need of SQL Views****)** ##

Views help the database administrator to provide restricted access to the tables of the database to other users/employees.

视图可帮助数据库管理员向其他用户/雇员提供对数据库表的受限访问。

For example, consider a website’s login database that contains the data columns as ‘User\_id’, ‘Password’, ‘Phone\_num’ and ‘Name’. In case, if the database administrator happens to provide access to the System’s Tester(employee), he can do so by providing restricted/limited access to the user through SQL Views. He can limit the database access by just providing access to data columns such as ‘Name’ and ‘User\_id’.

例如，考虑一个网站的登录数据库，其中包含数据列，如“ User\_id”，“ Password”，“ Phone\_num”和“ Name”。 万一数据库管理员碰巧提供对系统测试人员（员工）的访问权限，则可以通过SQL视图为用户提供受限/受限访问权限。 他可以通过仅提供对数据列（例如“名称”和“ User\_id”）的访问来限制数据库访问。

Thus, SQL Views helps in aligning the confidential data access through various restricted parameters.

因此，SQL视图有助于通过各种受限参数调整机密数据访问。

--------------------

## 创建一个SQL视图 **(****Creating an SQL View****)** ##

By creating a View, we decide the limit to provide access to the original database.

通过创建视图，我们确定了提供对原始数据库的访问的限制。

**Syntax:**

**句法：**

CREATE VIEW View_name AS
    SELECT columns
    FROM  Table;

We use View alongside `SELECT statement` to select the data columns to be made visible to the user.

我们在`SELECT statement`旁边使用View来选择要使用户可见的数据列。

We have created a Table (using data from our previous tutorial on [SQL sum, average, and count functions][SQL sum_ average_ and count functions]) – ‘Info’ as seen below with data columns — ‘id’, ‘Cost’, ‘city’, and ‘Password’.

我们已经创建了一个表（使用上一教程中有关[SQL求和，平均值和计数函数的数据][SQL sum_ average_ and count functions] ）–“信息”，如下所示，其中包含数据列-“ id”，“ Cost”，“ city”和“ Password”。

id      Cost    city    Password
    1	100	Pune	11**&!
    2	50	Satara	1234$
    3	65	Pune	qwerty123@
    4	97	Mumbai	JAM1998#
    5	12	USA	0000%!

Having created a Table, let us start with the creation of View on the database.

创建表后，让我们开始在数据库上创建View。

**Example 1:**

**范例1：**

CREATE VIEW Info_review AS
    SELECT cost, city
    FROM  Info;
    
    SELECT * from Info_review;

We have created a View as ‘Info\_review’ over the Table – ‘Info’ by providing access to the data of the columns ‘Cost’ and ‘city’ using the SELECT statement.

我们已经通过使用SELECT语句提供对“成本”和“城市”列数据的访问，从而在表上创建了一个名为“ Info\_review”的视图-“ Info”。

**Output:**

**输出：**

100	Pune
    50	Satara
    65	Pune
    97	Mumbai
    12	USA

**Example 2:**

**范例2：**

CREATE VIEW Info_review AS
    SELECT cost, city
    FROM  Info
    WHERE city = "Pune";
    SELECT * from Info_review;

In this example, we have created a SQL View using `SELECT statement` and `WHERE clause` by restricting the access such that only those data values are made available that belong to the city ‘Pune’.

在此示例中，我们通过限制访问权限使用`SELECT statement`和`WHERE clause`创建了SQL视图，从而仅使属于城市“浦那”的那些数据值可用。

**Output:**

**输出：**

100	Pune
    65	Pune

--------------------

## 更新SQL视图 **(****Updating an SQL View****)** ##

Updation in a View means to update or manipulate the pre-defined records in the database.

视图中的更新意味着更新或操作数据库中的预定义记录。

We need to follow the below conditions in order to Update the data of a View:

我们需要遵循以下条件才能更新视图的数据：

*  `SQL SELECT statement` should not be used alongside `DISTINCT`keyword and `ORDER BY` clause.
    
    `SQL SELECT statement`不应与`DISTINCT`关键字和`ORDER BY`子句一起使用。
 *  SQL SELECT statement should not contain **set operators**, **set functions,** and **summary functions**.
    
    SQL SELECT语句不应包含**集合运算符** ， **集合函数**和**摘要函数** 。
 *  The View query should not be used alongside `HAVING` and `GROUP BY` clause.
    
    View查询不应与`HAVING`和`GROUP BY`子句一起使用。
 *  The query should not use `FROM clause` to fetch data from multiple columns.
    
    该查询不应使用`FROM clause`从多个列中获取数据。
 *  SQL View should contain all the `NON-NULL values` of the original database.
    
    SQL View应该包含原始数据库的所有`NON-NULL values` 。

**Syntax:**

**句法：**

UPDATE View-name
       SET column="value"
       WHERE condition;

**Example:**

**例：**

CREATE VIEW Info_review AS
    SELECT id, city
    FROM  Info;
    
    UPDATE Info_review
       SET city="Satara"
       WHERE id = 1;
    
    SELECT * from Info_review;

In the above example, we have updated the city value whose id = 1.

在上面的示例中，我们更新了ID = 1的城市值。

**Output:**

**输出：**

1	Satara
    2	Satara
    3	Pune
    4	Mumbai
    5	USA

Once an updation is made in the View, it immediately gets reflected in the table of the original database.

一旦在视图中进行了更新，它就会立即反映在原始数据库的表中。

SELECT * from Info;

If we execute the above SELECT statement, we can see that the updation made in the View of id = 1 is reflected in the original Table – ‘Info’.

如果执行上面的SELECT语句，我们可以看到在id = 1的视图中进行的更新反映在原始表–“信息”中。

1	100	Satara	11**&!
    2	50	Satara	1234$
    3	65	Pune	qwerty123@
    4	97	Mumbai	JAM1998#
    5	12	USA	0000%!

--------------------

## 从SQL视图删除行 **(****Deletion of rows from SQL Views****)** ##

Data rows can be deleted from SQL View by using the below command:

可以使用以下命令从SQL View中删除数据行：

DELETE FROM View-name
       WHERE column= "value";

**Example:**

**例：**

create table Info(id integer, Cost integer, city varchar(200),Password varchar(200));
    insert into Info(id, Cost,city,Password) values(1, 100,"Pune","11**&!");
    insert into Info(id, Cost,city,Password) values(2, 50, "Satara","1234$");
    insert into Info(id, Cost,city,Password) values(3, 65,"Pune","qwerty123@");
    insert into Info(id, Cost,city,Password) values(4, 97,"Mumbai","JAM1998#");
    insert into Info(id, Cost,city,Password) values(5, 12,"USA","0000%!");
    
    CREATE VIEW Info_review AS
    SELECT id, city
    FROM  Info;
    
    DELETE FROM Info_review
       WHERE city = "USA";
    
    SELECT * from Info_review;

**Output:**

**输出：**

1	Pune
    2	Satara
    3	Pune
    4	Mumbai

**Changes made in the View are reflected in the original table** as seen below.

**在视图中所做的更改将反映在原始表中** ，如下所示。

SELECT * from Info;

1	100	Pune	11**&!
    2	50	Satara	1234$
    3	65	Pune	qwerty123@
    4	97	Mumbai	JAM1998#

As you may notice, the row containing USA is no longer available in both, the view and the original table.

您可能会注意到，视图和原始表中都不再包含USA的行。

--------------------

## 在SQL视图中插入一行 **(****Insertion of a row in SQL Views****)** ##

Insertion of a data row in SQL View should follow all the conditions mentioned in the Updation of View section.

在SQL视图中插入数据行应遵循“更新视图”部分中提到的所有条件。

**Syntax**:

**语法** ：

INSERT into View-name(columns) values(val1,..,valN)

**Example:**

**例：**

INSERT into Info_review(id,city) values(12,'France');
    
    SELECT * from Info_review;

**Output:**

**输出：**

1	Pune
    2	Satara
    3	Pune
    4	Mumbai
    5	USA
    12	France

All the changes made in the SQL View are reflected in the original table as shown below.

SQL视图中所做的所有更改都反映在原始表中，如下所示。

SELECT * from Info;

1	100	Pune	11**&!
    2	50	Satara	1234$
    3	65	Pune	qwerty123@
    4	97	Mumbai	JAM1998#
    5	12	USA	0000%!
    12	NULL	France	NULL

--------------------

## 删除SQL视图 **(****Dropping an SQL View****)** ##

If we wish to delete the entire View along with the data and its schema, we need to use Drop statement.

如果我们希望删除整个View以及数据及其架构，则需要使用Drop语句。

The `Drop statement` deletes the entire View from the memory along with its structure(schema).

`Drop statement`从内存中删除整个View及其结构（模式）。

**Syntax:**

**句法：**

DROP VIEW View-name;

**Example:**

**例：**

DROP VIEW Info_review;

Having deleted the View, we can use the below SELECT statement to verify its deletion.

删除视图后，我们可以使用下面的SELECT语句来验证其删除。

Select * from Info_review;

**Output:**

**输出：**

ERROR 1146 (42S02) at line 15: Table 'test.Info_review' doesn't exist

--------------------

## 结论 **(****Conclusion****)** ##

By this, we have come to the end of this topic. I believe that the concept is clear to all the readers.

至此，我们到了本主题的结尾。 我相信所有读者都清楚这个概念。

Please feel free to comment in case you come across a doubt.

如果您有任何疑问，请随时发表评论。

--------------------

## 参考资料 **(****References****)** ##

*  [SQL VIEWS – Documentation][SQL VIEWS _ Documentation]
    
    [SQL视图–文档][SQL VIEWS _ Documentation]

> 翻译自: [https://www.journaldev.com/40677/sql-views][https_www.journaldev.com_40677_sql-views]

sql 视图嵌套视图

[SQL]: https://www.journaldev.com/sql
[SQL sum_ average_ and count functions]: https://www.journaldev.com/40669/sql-sum-avg-count
[SQL VIEWS _ Documentation]: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver15
[https_www.journaldev.com_40677_sql-views]: https://www.journaldev.com/40677/sql-views