Sql 简明教程

SQL - Operators

What is SQL Operator?

SQL 运算符是主要在 SQL 语句的 WHERE 子句中使用的保留字或字符，用于执行操作（例如比较和算术运算）。这些运算符用于指定 SQL 语句中的条件，并用作语句中多个条件的连接条件。

An SQL operator is a reserved word or a character used primarily in an SQL statement’s WHERE clause to perform operation(s), such as comparisons and arithmetic operations. These Operators are used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement.

SQL 运算符可以是 unary 或 binary operator 。单目运算符（例如单目 + 或单目 - ）仅使用一个操作数执行单目运算，而双目运算符（例如 + 或 - 等）使用两个操作数执行双目运算。

An SQL operator can be either a unary or binary operator. A unary operator (example unary + or unary - ) uses only one operand to perform the unary operation, whereas the binary operator (example + or - etc) uses two operands to perform the binary operation.

Types of Operator in SQL

SQL 支持以下类型的运算符：

SQL supports following types of operators:

  1. Arithmetic operators

  2. Comparison operators

  3. Logical operators

  4. Operators used to negate conditions

SQL Arithmetic Operators

SQL 算术运算符用于对数值执行数学运算。SQL 提供了以下运算符来执行数学运算。

SQL Arithmetic Operators are used to perform mathematical operations on the numerical values. SQL provides following operators to perform mathematical operations.

以下是 SQL 中所有算术运算符的列表。

Here is a list of all the arithmetic operators available in SQL.

Operator

Description

Example

+

Addition

10 + 20 = 30

-

Subtraction

20 - 30 = -10

*

Multiplication

10 * 20 = 200

/

Division

20 / 10 = 2

%

Modulus

5 % 2 = 1

SQL Comparison Operators

SQL 比较运算符测试两个给定表达式是否相同。这些运算符在 SQL 条件语句中使用，同时比较一个表达式与另一个表达式，并且它们返回一个可以为 TRUE 或 FALSE 的 Boolean 值。当一个或另一个操作数的值为 NULL 时，SQL 比较运算的结果可能为 UNKNOWN。

SQL Comparison Operators test whether two given expressions are the same or not. These operators are used in SQL conditional statements while comparing one expression with another and they return a Boolean value which can be either TRUE or FALSE. The result of an SQL comparison operation can be UNKNOWN when one or another operand has it’s value as NULL.

以下是 SQL 中所有可用比较运算符的列表。

Here is a list of all the comparison operators available in SQL.

Operator

Description

Example

=

Equal to

5 = 5 returns TRUE

!=

Not equal

5 != 6 returns TRUE

<>

Not equal

5 <> 4 returns TRUE

>

Greater than

4 > 5 returns FALSE

<

Less than

4 < 5 returns TRUE

>=

Greater than or equal to

4 >= 5 returns FALSE

⇐

Less than or equal to

4 ⇐ 5 returns TRUE

!<

Not less than

4 !< 5 returns FALSE

!>

Not greater than

4 !> 5 returns TRUE

SQL Logical Operators

SQL 逻辑运算符与比较运算符非常相似，并且它们测试给定条件的真值。这些运算符返回一个可以为 TRUE 或 FALSE 的 Boolean 值。当一个或另一个操作数的值为 NULL 时，SQL 逻辑运算的结果可能为 UNKNOWN。

SQL Logical Operators are very similar to comparison operators and they test for the truth of some given condition. These operators return a Boolean value which can be either a TRUE or FALSE. The result of an SQL logical operation can be UNKNOWN when one or another operand has it’s value as NULL.

以下是 SQL 中所有可用逻辑运算符的列表。

Here is a list of all the logical operators available in SQL.

Operator

Description

Example

ALL

TRUE if all of a set of comparisons are TRUE.

Example

AND

TRUE if all the conditions separated by AND are TRUE.

Example

ANY

TRUE if any one of a set of comparisons are TRUE.

Example

BETWEEN

TRUE if the operand lies within the range of comparisons.

Example

EXISTS

TRUE if the subquery returns one or more records

Example

IN

TRUE if the operand is equal to one of a list of expressions.

Example

LIKE

TRUE if the operand matches a pattern specially with wildcard.

Example

NOT

Reverses the value of any other Boolean operator.

Example

OR

TRUE if any of the conditions separated by OR is TRUE

Example

IS NULL

TRUE if the expression value is NULL.

Example

SOME

TRUE if some of a set of comparisons are TRUE.

Example

UNIQUE

The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).

Example

SQL Operator Precedence

SQL 中的运算符优先级是在给定表达式中 SQL 评估不同运算符的顺序。具有较高优先级的运算符最先进行评估。

The operator precedence in SQL is the sequence in which the SQL evaluates the different operators in a given expression. The operators with higher precedence get evaluated first.

下表按优先级列出了所有 SQL 运算符。具有最高优先级的运算符位于顶部，具有最低优先级的运算符位于底部。

Following table lists all SQL operators as per their precedence. The operators with the highest precedence are at the top and the operators with the lowest precedence are at the bottom.

Operator

Operation

+, -

identity, negation

*, /

multiplication, division

+, -

addition, subtraction

=, !=, <, >, ⇐, >=, IS NULL, LIKE, BETWEEN, IN

Comparison

NOT

logical negation

AND

conjunction

OR

inclusion

Example

考虑以下 SQL 语句：

Consider the following SQL statement:

SELECT 20 - 3 * 5;

这将导致以下结果。此时，乘法运算符最先进行评估，然后进行减法。

This will result in the following. Here multiplication operator gets evaluated first and then subtraction happens.

5