sql 运算符_了解SQL ANY和ALL运算符

落日映苍穹つ 2023-02-28 06:05 189阅读 0赞

sql 运算符

Hello, folks! Hope you all are doing well. In this article, we will be discussing about **SQL ANY and ALL Operators**.

大家好！ 希望大家一切都好。 在本文中，我们将讨论**SQL ANY和ALL运算符** 。

As we know, Operators acts as a medium or catalyst to perform any operation on some data values. Various kinds of Operators include Bitwise, Arithmetic, Logical operators, etc.

众所周知，运算符充当对某些数据值执行任何操作的媒介或催化剂。 各种运算符包括按位运算符，算术运算符，逻辑运算符等。

**SQL ANY and ALL Operators** fall under the category of **Logical Operators** i.e. they evaluate to true or false upon the fulfilment of the conditions.

**SQL ANY和ALL运算符**属于**逻辑运算符**类别，即，在满足条件时，它们的评估结果为true或false。

Let us now understand in detail about SQL ANY and ALL Operators.

现在让我们详细了解有关SQL ANY和ALL运算符的信息。

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

## 什么是SQL ANY运算符？ **(****What is SQL ANY Operator?****)** ##

`SQL ANY operator`, as mentioned above, is a logical operator that evaluates to true upon the fulfillment of the below condition.

如上所述， `SQL ANY operator`是一种逻辑运算符，在满足以下条件时其评估结果为true。

*  Any record/data value that meets the condition mentioned in the sub-query.
    
    满足子查询中提到的条件的任何记录/数据值。

Thus, we can say that ANY operator returns to TRUE, if any of the data values pass the sub-query condition.

因此，如果任何数据值通过子查询条件，我们都可以说ANY运算符返回TRUE。

Let us now understand the structure of ANY operator in the upcoming section.

现在，让我们在接下来的部分中了解ANY运算符的结构。

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

### SQL ANY运算符的语法 **(****Syntax of SQL ANY Operator****)** ###

SELECT columns
    FROM table
    WHERE column-name operator ANY
    (sub-query);

*  `sub-query`: This query could be any standard SQL query.
    
    `sub-query` ：此查询可以是任何标准SQL查询。
 *  `operator`: SQL comparison operator to compare data values.
    
    `operator` ：SQL比较运算符，用于比较数据值。

SQL ANY Operator **evaluates to TRUE**. And thus returns the selected columns or performs the appropriate operation/query.

SQL ANY运算符**求值为TRUE** 。 从而返回选定的列或执行适当的操作/查询。

Having understood the working of SQL ANY operator, let us now implement the same through examples in the upcoming section.

了解了SQL ANY运算符的工作原理之后，现在让我们通过后面的示例中的示例来实现相同的功能。

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

## 通过示例实现SQL ANY运算符 **(****Implementing SQL ANY Operator through examples****)** ##

In the below example, we have implemented SQL ANY operator with [SELECT ][SELECT]statement and an assignment operator(=).

在下面的示例中，我们使用[SELECT][]语句和赋值运算符（=）实现了SQL ANY运算符。

SELECT Cost,City
    FROM Info
    WHERE Cost = ANY (SELECT Cost FROM Info WHERE Cost = 100);

Here, we have displayed the data values of the columns ‘Cost’ and ‘city’ whose at least one ‘cost’ value is 100.

在这里，我们显示了“成本”和“城市”列的数据值，其至少一个“成本”值为100。

**NOTE: We can use the SQL ANY operator to match columns and data from other tables in a similar manner.**

**注意：我们可以使用SQL ANY运算符以类似的方式匹配其他表中的列和数据。**

**Output:**

**输出：**

![SQL ANY OPERATOR Example 1][]

**SQL ANY OPERATOR Example** **SQL ANY OPERATOR示例**

Now, we have selected all the data values of the table for which any one data value of the column ‘cost’ is greater than 50.

现在，我们选择了表的所有数据值，其中“成本”列的任何一个数据值都大于50。

SELECT *
    FROM Info
    WHERE Cost = ANY (SELECT Cost FROM Info WHERE Cost > 50);

**Output:**

**输出：**

![SQL ANY OPERATOR Example 2][]

**SQL ANY OPERATOR Example 2** **SQL ANY OPERATOR示例2**

Further, we have selected all the data values of the table where any one ‘cost’ value is not equal to 100.

此外，我们选择了表的所有数据值，其中任何一个“成本”值都不等于100。

SELECT *
    FROM Info
    WHERE Cost = ANY (SELECT Cost FROM Info WHERE Cost <> 100);

**Output:**

**输出：**

![SQL ANY OPERATOR Example 3][]

**SQL ANY OPERATOR Example 3** **SQL ANY OPERATOR示例3**

In the below piece of code, we have displayed all the data values for which any value of the column ‘cost’ is greater than or equal to 50.

在下面的代码中，我们显示了“成本”列中任何值大于或等于50的所有数据值。

SELECT *
    FROM Info
    WHERE Cost = ANY (SELECT Cost FROM Info WHERE Cost >= 50);

**Output:**

**输出：**

![SQL ANY OPERATOR Example 4][]

**SQL ANY OPERATOR Example 4** **SQL ANY OPERATOR示例4**

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

## 什么是SQL ALL运算符？ **(****What is SQL ALL Operator?****)** ##

`SQL ALL operator` evaluates to TRUE, only if all the data values of the table pass the condition.

仅当表的所有数据值均通过条件时， `SQL ALL operator`计算结果才为TRUE。

We won’t get the desired results if any of the data values violate the conditions mentioned in the query.

如果任何数据值违反查询中提到的条件，我们将无法获得理想的结果。

Let us now understand the syntax of SQL ALL operator in the below section.

现在让我们在下面的部分中了解SQL ALL运算符的语法。

### SQL ALL运算符的语法 **(****Syntax of SQL ALL Operator****)** ###

SQL ALL operator results to TRUE when all the data values pass the test condition mentioned in the query.

当所有数据值都通过查询中提到的测试条件时，SQL ALL运算符的结果为TRUE。

SELECT columns
    FROM table
    WHERE column-name operator ALL
    (sub-query);

**Note:** SQL ANY and ALL operators can be used with [SQL WHERE][], GROUPBY as well as [HAVING][] clauses.

**注意：** SQL ANY和ALL运算符可以与[SQL WHERE][] ，GROUPBY以及[HAVING][]子句一起使用。

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

## 通过示例实现SQL ALL Operator **(****Implementing SQL ALL Operator through examples****)** ##

Now, let us apply the syntax of SQL ALL operator and go through the below examples.

现在，让我们应用SQL ALL运算符的语法并仔细阅读以下示例。

SELECT ALL city 
    FROM Info
    WHERE TRUE;

In the above example, we have displayed all the data values of the column ‘city’ where the value is not NULL i.e. there is a value specified for every row of the column city.

在上面的示例中，我们显示了列“ city”的所有数据值，其中该值不为NULL，即为列city的每一行都指定了一个值。

**Output:**

**输出：**

![SQL ALL OPERATOR - Displaying All Data values][]

**SQL ALL OPERATOR** – Displaying All Data values **SQL ALL OPERATOR** –显示所有数据值

Now, we have selected all the data values from the table, for which every data value of the column ‘cost’ is greater than 100. While we have passed a subquery selecting all the data values less than 100 to pass to the ALL operator.

现在，我们从表中选择了所有数据值，其中“ cost”列的每个数据值都大于100。尽管我们通过了一个子查询，选择了所有小于100的数据值，然后传递给ALL运算符。

SELECT *
    FROM Info
    WHERE Cost > All (SELECT Cost FROM Info WHERE Cost <100);

**Output:**

**输出：**

![SQL ALL OPERATOR Select Cost Less than 100][]

**SQL ALL OPERATOR Select Cost Less than 100** **SQL ALL OPERATOR选择成本小于100**

Here, we have used SQL GROUP BY and SQL HAVING clause along with SQL ALL Operator.

在这里，我们使用了SQL GROUP BY和SQL HAVING子句以及SQL ALL Operator。

Now, we have selected all the data columns of the table and grouped it using ‘city’ values. Further, we have selected all the data values in such a manner that the minimum ‘cost’ of every data value selected, should be less than the [AVERAGE][] of the column ‘cost’.

现在，我们选择了表的所有数据列，并使用“ city”值将其分组。 此外，我们以这样一种方式选择了所有数据值：所选的每个数据值的最小“成本”应小于“成本”列的“ [平均值][AVERAGE] ”。

SELECT id,city ,Cost
    FROM Info 
    GROUP BY city 
    HAVING MIN(Cost) < ALL (SELECT AVG(Cost) FROM Info);

**Output:**

**输出：**

![SQL ALL OPERATOR Selecting Average Cost][]

**SQL ALL OPERATOR Selecting Average Cost** **SQL ALL OPERATOR选择平均成本**

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

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

By this, we have come to the end of this topic. Please feel free to comment below in case, you come across any doubt!

至此，我们到了本主题的结尾。 如果您有任何疑问，请在下面发表评论。

For more such posts related to SQL, do visit [SQL JournalDev][].

有关与SQL有关的更多此类帖子，请访问[SQL JournalDev][] 。

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

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

*  [SQL ANY ALL — StackOverFlow][SQL ANY ALL _ StackOverFlow]
    
    [SQL ANY ALL — StackOverFlow][SQL ANY ALL _ StackOverFlow]

> 翻译自: [https://www.journaldev.com/41966/sql-any-all-operators][https_www.journaldev.com_41966_sql-any-all-operators]

sql 运算符

[SELECT]: https://www.journaldev.com/18262/sql-select
[SQL ANY OPERATOR Example 1]: /images/20230209/02dfa27f09414578a22becdf75ae6c07.png
[SQL ANY OPERATOR Example 2]: /images/20230209/f6d63e0b749443c9b7d5f4842e894faf.png
[SQL ANY OPERATOR Example 3]: /images/20230209/00826f36c1aa4648b430e5fa232ec1e9.png
[SQL ANY OPERATOR Example 4]: /images/20230209/6578c048c7a744928b6d28d52643a9b0.png
[SQL WHERE]: https://www.journaldev.com/18411/sql-where-clause
[HAVING]: https://www.journaldev.com/40737/sql-having-clause#:~:text=The%20Having%20clause%20adds%20a,certain%20condition%20stated%20by%20it.
[SQL ALL OPERATOR - Displaying All Data values]: /images/20230209/09cfbcb660b448188b97d37cde1c207a.png
[SQL ALL OPERATOR Select Cost Less than 100]: /images/20230209/1018475c138d4234a5b20b96fd62ee9c.png
[AVERAGE]: https://www.journaldev.com/40669/sql-sum-avg-count
[SQL ALL OPERATOR Selecting Average Cost]: https://cdn.journaldev.com/wp-content/uploads/2020/06/SQL-ALL-OPERATOR-Example-3.png
[SQL JournalDev]: https://www.journaldev.com/sql
[SQL ANY ALL _ StackOverFlow]: https://stackoverflow.com/questions/5980474/sql-any-all-operators
[https_www.journaldev.com_41966_sql-any-all-operators]: https://www.journaldev.com/41966/sql-any-all-operators