SQL 运算符

运算符是用于对值执行操作的符号（和关键字）。

这些运算符与 SQL 子句一起使用，例如：SELECT、WHERE、ON 等。

SQL 中的运算符可以分为：

• 算术运算符
• 比较运算符
• 逻辑运算符

SQL 算术运算符

算术运算符执行简单的算术运算，例如加法、减法、乘法等。

加法运算符

-- returns new column named total_amount which is 
-- 100 added to the amount field
SELECT item, amount, amount+100 AS total_amount
FROM Orders;

减法运算符

-- returns new column named offer_price which is 
-- 20 subtracted to the amount field
SELECT item, amount, amount-20 AS offer_price
FROM Orders;

乘法运算符

-- returns new column named total_amount  which is 
-- 4 multiplied to the amount field
SELECT item, amount, amount*4 AS total_amount
FROM Orders;

除法运算符

-- returns new column named half_amount which is 
-- divided by 2 to the amount field
SELECT item, amount, amount/2 AS half_amount
FROM Orders;

模数（余数）运算符

-- returns 1 which is remainder
SELECT 10 % 3 AS result;

比较运算符

我们可以使用 SQL 中的比较运算符来比较两个值。这些运算符返回 1（表示真）或 0（表示假）。

等于运算符

-- returns records where customer_id is only 4
SELECT order_id, item, amount
FROM Orders
WHERE customer_id = 4;

小于运算符

-- returns records where amount is less than 400 (exclusive)
SELECT order_id, item, amount
FROM Orders
WHERE amount < 400;

大于运算符

-- returns records where amount is greater than 400 (exclusive)
SELECT order_id, item, amount
FROM Orders
WHERE amount > 400;

小于或等于运算符

-- returns records where amount is less than or equal to 400
SELECT order_id, item, amount
FROM Orders
WHERE amount <= 400;

大于或等于运算符

-- returns records where amount is greater than or equal to 400
SELECT order_id, item, amount
FROM Orders
WHERE amount >= 400;

不等于运算符

-- returns records where amount is not equal to 400
SELECT order_id, item, amount
FROM Orders
WHERE amount != 400;

除了 !=，我们还可以使用 <> 符号表示不等于操作。

逻辑运算符

我们可以使用逻辑运算符来比较多个 SQL 命令。这些运算符返回 1（表示真）或 0（表示假）。

SQL 中可用的逻辑运算符有：

• ANY 和 ALL
• AND、OR 和 NOT
• BETWEEN
• EXISTS
• IN
• LIKE
• IS NULL