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

一、算术运算符

二、需要注意 NULL

三、比较运算符

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

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

请参阅

学习重点运算符就是对其两边的列或者值进行运算（计算或者比较大小等）的符号。使用算术运算符可以进行四则运算。括号可以提升运算的优先顺序（优先进行运算）。包含 <code>NULL</code> 的运算，其结果也是 <code>NULL</code>。比较运算符可以用来判断列或者值是否相等，还可以用来比较大小。判断是否为 <code>NULL</code>，需要使用 <code>IS NULL</code> 或者 <code>IS NOT NULL</code> 运算符。

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

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

执行结果

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

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

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

含义

运算符

加法运算

减法运算

乘法运算

除法运算

KEYWORD <code>+</code> 运算符 <code>-</code> 运算符 <code>*</code> 运算符 <code>/</code> 运算符

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

算术运算符

法则 6 <code>SELECT</code> 子句中可以使用常数或者表达式。

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

<code>()</code>

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

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

A. <code>5 + NULL</code>

B. <code>10 - NULL</code>

C. <code>1 * NULL</code>

D. <code>4 / NULL</code>

E. <code>NULL / 9</code>

F. <code>NULL / 0</code>

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

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

专栏 FROM 子句真的有必要吗？在 SELECT 语句基础中我们介绍过 <code>SELECT</code> 语句是由 <code>SELECT</code> 子句和 <code>FROM</code> 子句组成的。可实际上 <code>FROM</code> 子句在 <code>SELECT</code> 语句中并不是必不可少的，只使用 <code>SELECT</code> 子句进行计算也是可以的。代码清单 A　只包含 <code>SELECT</code> 子句的 <code>SELECT</code> 语句

实际上，通过执行 <code>SELECT</code> 语句来代替计算器的情况基本上是不存在的。不过在极少数情况下，还是可以通过使用没有 <code>FROM</code> 子句的 <code>SELECT</code> 语句来实现某种业务的。例如，不管内容是什么，只希望得到一行临时数据的情况。但是也存在像 Oracle 这样不允许省略 <code>SELECT</code> 语句中的 <code>FROM</code> 子句的 RDBMS，请大家注意 [1]。

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

代码清单 18　选取出 <code>sale_price</code> 列为 500 的记录

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

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

比较运算符 <code>=</code> 运算符 <code><></code> 运算符

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

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

表 2　比较运算符

和 <code>~</code> 相等

和 <code>~</code> 不相等

大于等于 <code>~</code>

大于 <code>~</code>

小于等于 <code>~</code>

小于 <code>~</code>

<code>>=</code> 运算符 <code>></code> 运算符 <code><=</code> 运算符 <code><</code> 运算符

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

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

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

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

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

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

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

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

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

表 3　<code>Chars</code> 表

chr（字符串类型）

222

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

代码清单 23　创建 <code>Chars</code> 表并插入数据

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

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

代码清单 24　选取出大于 <code>'2'</code> 的数据的 <code>SELECT</code> 语句

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

<code>IS NULL</code> 运算符

代码清单 28　选取 <code>NULL</code> 的记录

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

<code>IS NOT NULL</code> 运算符

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

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

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

SELECT 语句基础

算术运算符和比较运算符

逻辑运算符

（完）

在 Oracle 中，<code>FROM</code> 子句是必需的，这种情况下可以使用 <code>DUAL</code> 这个临时表。另外，DB2 中可以使用 <code>SYSIBM.SYSDUMMY1</code> 这个临时表。 ↩︎

有很多 RDBMS 可以使用比较运算符“<code>!=</code>”来实现不等于功能。但这是限于不被标准 SQL 所承认的特定 SQL，出于安全的考虑，最好不要使用。 ↩︎

该规则对定长字符串和可变长字符串都适用。 ↩︎

SQL 不识别“<code>= NULL</code>”和“<code><> NULL</code>”的理由将会在逻辑运算符（包含 <code>NULL</code> 情况下的真值）中进行说明。 ↩︎