首页 > 资讯 > 数据库 >SQL 算术运算符和比较运算符

641

分享到

SQL 算术运算符和比较运算符

SQL 算术运算符和比较运算符 2021-12-29 11:12:59 641人浏览绘本

摘要

目录一、算术运算符二、需要注意 NULL三、比较运算符四、对字符串使用不等号时的注意事项五、不能对 NULL 使用比较运算符请参阅学习重点运算符就是对其两边的列或者值进行运算（计算或者比较大小等）的符号。使用算术运算符可以

SQL 算术运算符和比较运算符

一、算术运算符
二、需要注意 NULL
三、比较运算符
四、对字符串使用不等号时的注意事项
五、不能对 NULL 使用比较运算符
请参阅

学习重点

运算符就是对其两边的列或者值进行运算（计算或者比较大小等）的符号。

使用算术运算符可以进行四则运算。

括号可以提升运算的优先顺序（优先进行运算）。

包含 NULL 的运算，其结果也是 NULL。

比较运算符可以用来判断列或者值是否相等，还可以用来比较大小。

判断是否为 NULL，需要使用 IS NULL 或者 IS NOT NULL 运算符。

一、算术运算符

sql 语句中可以使用计算表达式。代码清单 17 中的 SELECT 语句，把各个商品单价的 2 倍（sale_price 的 2 倍）以 "sale_price_x2" 列的形式读取出来。

代码清单 17　SQL语句中也可以使用运算表达式

SELECT product_name, sale_price,
       sale_price * 2 AS "sale_price_x2"
  FROM Product;

执行结果

 product_name  | sale_price  | sale_price_x2
---------------+-------------+----------------
 T恤衫         |        1000 |           2000
 打孔器        |         500 |           1000
 运动T恤       |        4000 |           8000
 菜刀          |        3000 |           6000
 高压锅        |        6800 |          13600
 叉子          |         500 |           1000
 擦菜板        |         880 |           1760
 圆珠笔        |         100 |            200

sale_price_x2 列中的 sale_price * 2 就是计算销售单价的 2 倍的表达式。以 product_name 列的值为 "T 恤衫" 的记录行为例，sale_price 列的值 1000 的 2 倍是 2000，它以 sale_price_x2 列的形式被查询出来。同样，"打孔器" 记录行的值 500 的 2 倍 1000，"运动 T 恤" 记录行的值 4000 的 2 倍 8000，都被查询出来了。运算就是这样以行为单位执行的。

SQL 语句中可以使用的四则运算的主要运算符如表 1 所示。

表 1　SQL 语句中可以使用的四则运算的主要运算符

含义	运算符
加法运算	+
减法运算	-
乘法运算	*
除法运算	/

KEYWORD

+ 运算符

- 运算符

* 运算符

/ 运算符

四则运算所使用的运算符（+、-、*、/）称为算术运算符。运算符就是使用其两边的值进行四则运算或者字符串拼接、数值大小比较等运算，并返回结果的符号。加法运算符（+）前后如果是数字或者数字类型的列名的话，就会返回加法运算后的结果。SQL 中除了算术运算符之外还有其他各种各样的运算符。

KEYWORD

算术运算符

运算符

法则 6

SELECT 子句中可以使用常数或者表达式。

当然，SQL 中也可以像平常的运算表达式那样使用括号 ()。括号中运算表达式的优先级会得到提升，优先进行运算。例如在运算表达式 (1 + 2) * 3 中，会先计算 1 + 2 的值，然后再对其结果进行 * 3 运算。

KEYWORD

()

括号的使用并不仅仅局限于四则运算，还可以用在 SQL 语句的任何表达式当中。具体的使用方法今后会慢慢介绍给大家。

二、需要注意 `NULL`

像代码清单 17 那样，SQL 语句中进行运算时，需要特别注意含有 NULL 的运算。请大家考虑一下在 SQL 语句中进行如下运算时，结果会是什么呢？

A. 5 + NULL

B. 10 - NULL

C. 1 * NULL

D. 4 / NULL

E. NULL / 9

F. NULL / 0

正确答案全部都是 NULL。大家可能会觉得奇怪，为什么会这样呢？实际上所有包含 NULL 的计算，结果肯定是 NULL。即使像 F 那样用 NULL 除以 0 时这一原则也适用。通常情况下，类似 5/0 这样除数为 0 的话会发生错误，只有 NULL 除以 0 时不会发生错误，并且结果还是 NULL。

尽管如此，很多时候我们还是希望 NULL 能像 0 一样，得到 5 + NULL = 5 这样的结果。不过也不要紧，SQL 中也为我们准备了可以解决这类情况的方法（将会在各种各样的函数中进行介绍）。

专栏

FROM 子句真的有必要吗？

在 SELECT 语句基础中我们介绍过 SELECT 语句是由 SELECT 子句和 FROM 子句组成的。可实际上 FROM 子句在 SELECT 语句中并不是必不可少的，只使用 SELECT 子句进行计算也是可以的。

代码清单 A　只包含 SELECT 子句的 SELECT 语句
-- SQL Server  postgresql  Mysql
SELECT (100 + 200) * 3 AS calculation;
执行结果
calculation
-------------
        900
实际上，通过执行 SELECT 语句来代替计算器的情况基本上是不存在的。不过在极少数情况下，还是可以通过使用没有 FROM 子句的 SELECT 语句来实现某种业务的。例如，不管内容是什么，只希望得到一行临时数据的情况。

但是也存在像 oracle 这样不允许省略 SELECT 语句中的 FROM 子句的 RDBMS，请大家注意 ^[1]。

三、比较运算符

在 SELECT 语句基础学习 WHERE 子句时，我们使用符号 = 从 Product 表中选取出了商品种类（product_type）为字符串 "衣服" 的记录。下面让我们再使用符号 = 选取出销售单价（sale_price）为 500 日元（数字 500）的记录（代码清单 18）。

代码清单 18　选取出 sale_price 列为 500 的记录

SELECT product_name, product_type
  FROM Product
 WHERE sale_price = 500;

执行结果

 product_name  | product_type
---------------+--------------
 打孔器        | 办公用品
 叉子          | 厨房用具

像符号 = 这样用来比较其两边的列或者值的符号称为比较运算符，符号 = 就是比较运算符。在 WHERE 子句中通过使用比较运算符可以组合出各种各样的条件表达式。

接下来，我们使用“不等于”这样代表否定含义的比较运算符 <> ^[2]，选取出 sale_price 列的值不为 500 的记录（代码清单 19）。

KEYWORD

比较运算符

= 运算符

<> 运算符

代码清单 19　选取出 sale_price 列的值不是 500 的记录

SELECT product_name, product_type
  FROM Product
 WHERE sale_price <> 500;

执行结果

 product_name  | product_type
---------------+--------------
 T恤衫         | 衣服
 运动T恤       | 衣服
 菜刀          | 厨房用具
 高压锅        | 厨房用具
 擦菜板        | 厨房用具
 圆珠笔        | 办公用品

SQL 中主要的比较运算符如表 2 所示，除了等于和不等于之外，还有进行大小比较的运算符。

表 2　比较运算符

运算符	含义
`=`	和 `~` 相等
`<>`	和 `~` 不相等
`>=`	大于等于 `~`
`>`	大于 `~`
`<=`	小于等于 `~`
`<`	小于 `~`

KEYWORD

= 运算符

<> 运算符

>= 运算符

> 运算符

<= 运算符

< 运算符

这些比较运算符可以对字符、数字和日期等几乎所有数据类型的列和值进行比较。例如，从 Product 表中选取出销售单价（sale_price）大于等于 1000 日元的记录，或者登记日期（reGISt_date）在 2009 年 9 月 27 日之前的记录，可以使用比较运算符 >= 和 <，在 WHERE 子句中生成如下条件表达式（代码清单 20、代码清单 21）。

代码清单 20　选取出销售单价大于等于 1000 日元的记录

SELECT product_name, product_type, sale_price
  FROM Product
 WHERE sale_price >= 1000;

执行结果

 product_name  | product_type |  sale_price
---------------+--------------+--------------
 T恤衫         | 衣服         |         1000
 运动T恤       | 衣服         |         4000
 菜刀          | 厨房用具     |         3000
 高压锅        | 厨房用具     |         6800

代码清单 21　选取出登记日期在 2009 年 9 月27日之前的记录

SELECT product_name, product_type, regist_date
  FROM Product
 WHERE regist_date < "2009-09-27";

执行结果

 product_name  | product_type | regist_date
---------------+--------------+-----------
 T恤衫         | 衣服         | 2009-09-20
 打孔器        | 办公用品     | 2009-09-11
 菜刀          | 厨房用具     | 2009-09-20
 高压锅        | 厨房用具     | 2009-01-15
 叉子          | 厨房用具     | 2009-09-20
 擦菜板        | 厨房用具     | 2008-04-28

小于某个日期就是在该日期之前的意思。想要实现在某个特定日期（包含该日期）之后的查询条件时，可以使用代表大于等于的 >= 运算符。

另外，在使用大于等于（>=）或者小于等于（<=）作为查询条件时，一定要注意不等号（<、>）和等号（=）的位置不能颠倒。一定要让不等号在左，等号在右。如果写成（=<）或者（=>）就会出错。当然，代表不等于的比较运算符也不能写成（><）。

法则 7

使用比较运算符时一定要注意不等号和等号的位置。

除此之外，还可以使用比较运算符对计算结果进行比较。代码清单 22 在 WHERE 子句中指定了销售单价（sale_price）比进货单价（purchase_price）高出 500 日元以上的条件表达式。为了判断是否高出 500 日元，需要用 sale_price 列的值减去 purchase_price 列的值。

代码清单 22　WHERE 子句的条件表达式中也可以使用计算表达式

SELECT product_name, sale_price, purchase_price
  FROM Product
 WHERE sale_price - purchase_price >= 500;

执行结果

 product_name  | sale_price  | purchase_price
---------------+-------------+---------------
 T恤衫         |        1000 |            500
 运动T恤       |        4000 |           2800
 高压锅        |        6800 |           5000

四、对字符串使用不等号时的注意事项

对字符串使用大于等于或者小于等于不等号时会得到什么样的结果呢？接下来我们使用表 3 中的 Chars 表来进行确认。虽然该表中存储的都是数字，但 chr 是字符串类型（CHAR 类型）的列。

表 3　Chars 表

chr（字符串类型）
1
2
3
10
11
222

可以使用代码清单 23 中的 SQL 语句来创建 Chars 表。

代码清单 23　创建 Chars 表并插入数据

-- DDL ：创建表
CREATE TABLE Chars
(chr CHAR(3) NOT NULL,
PRIMARY KEY (chr));

-- SQL Server  PostgreSQL
-- DML ：插入数据
BEGIN TRANSACTION; -------------①

INSERT INTO Chars VALUES ("1");
INSERT INTO Chars VALUES ("2");
INSERT INTO Chars VALUES ("3");
INSERT INTO Chars VALUES ("10");
INSERT INTO Chars VALUES ("11");
INSERT INTO Chars VALUES ("222");

COMMIT;

特定的 SQL

代码清单 23 中的 DML 语句根据 DBMS 的不同而略有差异。在 mysql 中执行该语句时，请大家把 ① 的部分改成“START TRANSACTION;”。在 Oracle 和 DB2 中执行时不需用到 ① 的部分，请删除。

那么，对 Chars 表执行代码清单 24 中的 SELECT 语句（查询条件是 chr 列大于 "2"）会得到什么样的结果呢？

代码清单 24　选取出大于 "2" 的数据的 SELECT 语句

SELECT chr
  FROM Chars
 WHERE chr > "2";

大家是不是觉得应该选取出比 2 大的 3、10、11 和 222 这 4 条记录呢？下面就让我们来看看该 SELECT 语句的执行结果吧。

执行结果

 chr
-----
 3
 222

没想到吧？是不是觉得 10 和 11 比 2 大，所以也应该选取出来呢？大家之所以这样想，是因为混淆了数字和字符串，也就是说 2 和 "2" 并不一样。

现在，chr 列被定为字符串类型，并且在对字符串类型的数据进行大小比较时，使用的是和数字比较不同的规则。典型的规则就是按照字典顺序进行比较，也就是像姓名那样，按照条目在字典中出现的顺序来进行排序。该规则最重要的一点就是，以相同字符开头的单词比不同字符开头的单词更相近。

Chars 表 chr 列中的数据按照字典顺序进行排序的结果如下所示。

"10" 和 "11" 同样都是以 "1" 开头的字符串，首先判定为比 "2" 小。这就像在字典中“提问”“提议”和“问题”按照如下顺序排列一样。

提问
提议
问题

或者我们以书籍的章节为例也可以。1-1 节包含在第 1 章当中，所以肯定比第 2 章更靠前。

进行大小比较时，得到的结果是 "1-3" 比 "2" 小（"1-3" < "2"），"3" 大于 "2-2"（"3" > "2"）。

比较字符串类型大小的规则今后还会经常使用，所以请大家牢记 ^[3]。

法则 8

字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。

五、不能对 `NULL` 使用比较运算符

关于比较运算符还有一点十分重要，那就是作为查询条件的列中含有 NULL 的情况。例如，我们把进货单价（purchase_price）作为查询条件。请注意，商品“叉子”和“圆珠笔”的进货单价是 NULL。

我们先来选取进货单价为 2800 日元（purchase_price = 2800）的记录（代码清单 25）。

代码清单 25　选取进货单价为 2800 日元的记录

SELECT product_name, purchase_price
  FROM Product
 WHERE purchase_price = 2800;

执行结果

 product_name  | purchase_price
---------------+---------------
 运动T恤       |           2800
 菜刀          |           2800

大家对这个结果应该都没有疑问吧？接下来我们再尝试选取出进货单价不是 2800 日元（purchase_price <> 2800）的记录（代码清单 26）。

代码清单 26　选取出进货单价不是 2800 日元的记录

SELECT product_name, purchase_price
  FROM Product
 WHERE purchase_price <> 2800;

执行结果

 product_name  | purchase_price
---------------+---------------
 T恤衫         |            500
 打孔器        |            320
 高压锅        |           5000
 擦菜板        |            790

执行结果中并没有“叉子”和“圆珠笔”。这两条记录由于进货单价不明（NULL），因此无法判定是不是 2800 日元。

那如果想选取进货单价为 NULL 的记录的话，条件表达式该怎么写呢？历经一番苦思冥想后，用“purchase_price = NULL”试了试，还是一条记录也取不出来。

代码清单 27　错误的 SELECT 语句（一条记录也取不出来）

SELECT product_name, purchase_price
  FROM Product
 WHERE purchase_price = NULL;

执行结果

= NULL

即使使用 <> 运算符也还是无法选取出 NULL 的记录 ^[4]。因此，SQL 提供了专门用来判断是否为 NULL 的 IS NULL 运算符。想要选取 NULL 的记录时，可以像代码清单 28 那样来书写条件表达式。

KEYWORD

IS NULL 运算符

代码清单 28　选取 NULL 的记录

SELECT product_name, purchase_price
  FROM Product
 WHERE purchase_price IS NULL;

执行结果

 product_name  | purchase_price
---------------+---------------
 叉子          |
 圆珠笔        |

反之，希望选取不是 NULL 的记录时，需要使用 IS NOT NULL 运算符（代码清单 29）。

KEYWORD

IS NOT NULL 运算符

代码清单 29　选取不为 NULL 的记录

SELECT product_name, purchase_price
  FROM Product
 WHERE purchase_price IS NOT NULL;

执行结果

 product_name  | purchase_price
---------------+---------------
 T恤衫         |            500
 打孔器        |            320
 运动T恤       |           2800
 菜刀          |           2800
 高压锅        |           5000
 擦菜板        |            790

法则 9

希望选取 NULL 记录时，需要在条件表达式中使用 IS NULL 运算符。希望选取不是 NULL 的记录时，需要在条件表达式中使用 IS NOT NULL 运算符。

除此之外，对 NULL 使用比较运算符的方法还有很多，详细内容将会在各种各样的函数中进行介绍。

请参阅

SELECT 语句基础
算术运算符和比较运算符
逻辑运算符

（完）

在 Oracle 中，FROM 子句是必需的，这种情况下可以使用 DUAL 这个临时表。另外，DB2 中可以使用 SYSIBM.SYSDUMMY1 这个临时表。 ↩︎
有很多 RDBMS 可以使用比较运算符“!=”来实现不等于功能。但这是限于不被标准 SQL 所承认的特定 SQL，出于安全的考虑，最好不要使用。 ↩︎
该规则对定长字符串和可变长字符串都适用。 ↩︎
SQL 不识别“= NULL”和“<> NULL”的理由将会在逻辑运算符（包含 NULL 情况下的真值）中进行说明。 ↩︎