sql注入语句示例大全_通过示例了解SQL EXCEPT语句

sql注入语句示例大全

The SQL EXCEPT statement is one of the most commonly used statements to filter records when two SELECT statements are being used to select records.

当使用两个SELECT语句选择记录时，SQL EXCEPT语句是用于过滤记录的最常用语句之一。

The SQL EXCEPT statement returns those records from the left SELECT query, that are not present in the results returned by the SELECT query on the right side of the EXCEPT statement.

SQL EXCEPT语句从左侧的SELECT查询返回那些记录，这些记录在EXCEPT语句右侧的SELECT查询返回的结果中不存在。

A SQL EXCEPT statement works very similarly to the way that the minus operator does in mathematics.

SQL EXCEPT语句的工作方式与减号运算符在数学中的工作方式非常相似。

The EXCEPT statement was introduced in Microsoft SQL Server 2005.

Microsoft SQL Server 2005中引入了EXCEPT语句。

In this article, you will learn how to use SQL EXCEPT with the help of some simple examples.

在本文中，您将通过一些简单示例学习如何使用SQL EXCEPT。

执行SQL EXCEPT语句的条件 (The conditions to execute SQL EXCEPT statement)

There are a couple of conditions that have to be met before you can use the EXCEPT statement in SQL Server:

在SQL Server中使用EXCEPT语句之前，必须满足两个条件：

1. The number of columns and orders in the tables that are being used to execute the SELECT statements should be the same
   表中用于执行SELECT语句的列数和顺序数应该相同
2. The data types of the corresponding columns of both tables involved in the corresponding SELECT queries should be either the same or compatible
   对应的SELECT查询所涉及的两个表的对应列的数据类型应该相同或兼容

创建一个虚拟数据集 (Creating a dummy dataset)

Let’s create a dummy dataset that we will use to execute the example SQL EXCEPT statements unless you are using a fully backed up SQL database.

让我们创建一个虚拟数据集，除非您使用的是完全备份SQL数据库，否则它将用于执行示例SQL EXCEPT语句。

Run the following script:

运行以下脚本：

CREATE Database BookStore;
GO
USE BookStore;
 
CREATE TABLE Books1
(
    id INT,
    name VARCHAR(50) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price INT NOT NULL
 )
 
CREATE TABLE Books2
(
    id INT,
    name VARCHAR(50) NOT NULL,
    category VARCHAR(50) NOT NULL,
    price INT NOT NULL
 )

In the script above, we create a dummy database called BookStore. The database contains two tables: Books1 and Books2.

在上面的脚本中，我们创建了一个名为BookStore的虚拟数据库。 该数据库包含两个表： Books1和Books2 。

Both tables contain 4 columns: id, name, category and price.

两个表都包含4列： id ， name ， category和price 。

Now let’s insert some dummy records into both tables.

现在让我们在两个表中插入一些虚拟记录。

Run the following script:

运行以下脚本：

USE BookStore
 
INSERT INTO Books1
 
VALUES
(1, 'Book1', 'Cat1', 1800),
(2, 'Book2', 'Cat2', 1500),
(3, 'Book3', 'Cat3', 2000),
(4, 'Book4', 'Cat4', 1300),
(5, 'Book5', 'Cat5', 1500),
(6, 'Book6', 'Cat6', 5000),
(7, 'Book7', 'Cat7', 8000),
(8, 'Book8', 'Cat8', 5000),
(9, 'Book9', 'Cat9', 5400),
(10, 'Book10', 'Cat10', 3200)
 
INSERT INTO Books2
 
VALUES
 
(6, 'Book6', 'Cat6', 5000),
(7, 'Book7', 'Cat7', 8000),
(8, 'Book8', 'Cat8', 5000),
(9, 'Book9', 'Cat9', 5400),
(10, 'Book10', 'Cat10', 3200),
(11, 'Book11', 'Cat11', 5000),
(12, 'Book12', 'Cat12', 8000),
(13, 'Book13', 'Cat13', 5000),
(14, 'Book14', 'Cat14', 5400),
(15, 'Book15', 'Cat15', 3200)

The above script inserts 10 records into the Books1 table and 10 records into the Books2 table.

上面的脚本将10条记录插入Books1表中，并将10条记录插入Books2表中。

It is important to mention that the last 5 records in the Books1 table and the first 5 records in the Books2 table are the same.

重要的是要提到，Books1表中的最后5条记录和Books2表中的前5条记录是相同的。

Let’s now see how we can use SQL EXCEPT statement on this data.

现在让我们看看如何在这些数据上使用SQL EXCEPT语句。

使用SQL EXCEPT语句过滤记录 (Filtering records with the SQL EXCEPT statement)

Let’s look at the syntax for using a SQL EXCEPT statement:

让我们看一下使用SQL EXCEPT语句的语法：

Right SELECT Query EXCEPT Left SELECT Query

Yes, it is that simple to execute an EXCEPT statement.

是的，执行EXCEPT语句就这么简单。

Next, we will use the SQL EXCEPT statement to select records from the Books1 table that are not present in the Books2 table. You can see that the records from ids 6 to 10 are the same in both tables.

接下来，我们将使用SQL EXCEPT语句从Books1表中选择Books2表中不存在的记录。 您可以看到两个表中ID为6到10的记录都是相同的。

So, if the Book1 table is on the left of the EXCEPT operator and Books2 table is on the right, the records with ids 1 to 5 will be selected from the table Books1.

因此，如果Book1表位于EXCEPT运算符的左侧，而Books2表位于右侧，则将从表Books1中选择ID为1到5的记录。

To give it a try, run the following script:

要尝试一下，请运行以下脚本：

USE BookStore
SELECT id, name, category, price FROM Books1
Except
SELECT id, name, category, price FROM Books2

Here is the output of the above script:

这是上面脚本的输出：

You can see that only records with ids 1 to 5 have been selected from the Books1 table since the records with ids 6 to 10 also exist in the Books2 table.

您可以看到，从Books1表中只选择了ID为1至5的记录，因为在Books2表中也存在ID为6至10的记录。

Similarly, if the Books2 table is on the left side of the SQL EXCEPT statement and the Books1 table is on the right, you will see records from the Books2 table not present in the Books1 table.

同样，如果Books2表位于SQL EXCEPT语句的左侧，而Books1表位于右侧，则您将看到Books1表中不存在的Books2表中的记录。

Execute the following script to see this in action:

执行以下脚本以查看实际效果：

USE BookStore
SELECT id, name, category, price FROM Books2
Except
SELECT id, name, category, price FROM Books1

Here is the output:

这是输出：

You can see that only records with ids 11 to 15 have been selected since records with ids 6-10 from the Books2 table, also exist in the Books1 table.

您可以看到，由于Books2表中的ID为6-10的记录也存在于Books1表中，因此仅选择了ID为11至15的记录。

在单个表中使用EXCEPT语句 (Using EXCEPT statements in a single table)

In addition to using a SQL EXCEPT statement for filtering records from two tables, an EXCEPT statement can also be used to filter records from a single table. For example, the following EXCEPT statement will return all the records from the Books1 table where the price is less than or equal to 5000:

除了使用SQL EXCEPT语句从两个表中过滤记录之外，EXCEPT语句还可用于过滤单个表中的记录。 例如，以下EXCEPT语句将返回Books1表中价格小于或等于5000的所有记录：

USE BookStore
SELECT id, name, category, price FROM Books1
Except
SELECT id, name, category, price FROM Books1 WHERE price > 5000

In the script above, we have two SELECT statements operating on a single table i.e. Books1.

在上面的脚本中，我们在单个表（即Books1）上运行两个SELECT语句。

The SELECT statement on the right-hand side of the EXCEPT statement selects all the records where the price is greater than 5000. The SELECT statement on the left side of the EXCEPT statement returns all the records from the Books1 table.

EXCEPT语句右侧的SELECT语句选择价格大于5000的所有记录。EXCEPT语句左侧的SELECT语句返回Books1表中的所有记录。

Next, the EXCEPT statement filters the records selected by the SELECT statement on the right, from the records returned by the SELECT statement on the left. Hence, we are only left with the records from the Books table, where the price is not greater than 5000.

接下来，EXCEPT语句从左侧的SELECT语句返回的记录中过滤右侧的SELECT语句选择的记录。 因此，我们只剩下价格不超过5000的Books表中的记录。

Here is the output of the above query:

这是上面查询的输出：

除vs非空 (EXCEPT vs NOT NULL )

Now that you know how an EXCEPT statement works, it is important to understand the difference between SQL EXCEPT statement and NOT IN statement. There are two major differences:

既然您知道EXCEPT语句的工作原理，那么了解SQL EXCEPT语句和NOT IN语句之间的区别就很重要。 有两个主要区别：

1. The EXCEPT statement only returns the distinct records, whereas a NOT IN statement returns all the records that are not filtered by the NOT IN statement
   EXCEPT语句仅返回不同的记录，而NOT IN语句返回未由NOT IN语句过滤的所有记录
2. In the EXCEPT statement, the comparison between two SELECT statements is based on all the columns in both the tables. While a NOT IN statement compares values from a single column
   在EXCEPT语句中，两个SELECT语句之间的比较基于两个表中的所有列。 当NOT IN语句比较单个列中的值时

Here is an example of how a NOT IN statement can be used to filter all records from the Books1 table, that also exist in the Books2 table:

这是一个示例，该示例说明如何使用NOT IN语句来过滤Books1表中的所有记录，这些记录也存在于Books2表中：

USE BookStore
SELECT id, name, category, price FROM Books1
WHERE id NOT IN (SELECT id from Books2)

Output:

输出：

You can see that here the comparison between the first and second columns is only based on the id column.

您可以看到此处第一列和第二列之间的比较仅基于id列。

结论 (Conclusion)

The SQL EXCEPT statement is used to filter records based on the intersection of records returned via two SELECT statements. The records that are common between the two tables are filtered from the table on the left side of the SQL EXCEPT statement and the remaining records are returned.

SQL EXCEPT语句用于根据通过两个SELECT语句返回的记录的交集来过滤记录。 从SQL EXCEPT语句左侧的表中过滤出两个表之间的公用记录，并返回其余记录。

In this article, we looked at how to use an EXCEPT statement to filter records from two tables as well as from a single table. The article also covered the difference between the EXCEPT and NOT IN statements.

在本文中，我们研究了如何使用EXCEPT语句从两个表以及单个表中过滤记录。 本文还介绍了EXCEPT和NOT IN语句之间的区别。

翻译自: https://www.sqlshack.com/understanding-the-sql-except-statement-with-examples/

sql注入语句示例大全