SQLite 表达式

最后修改于 2020 年 7 月 6 日

在本 SQLite 教程中，我们将介绍 SQLite 运算符和表达式。

编程语言中的表达式是值、变量、运算符和函数的组合，它们根据特定编程语言的特定优先级和关联规则进行解释（求值），计算然后产生（返回，在有状态环境中）另一个值。表达式被称为求值为该值。

字面值

字面值是某种常量。字面值可以是整数、浮点数、字符串、BLOB 或 NULL。

sqlite> SELECT 3, 'Wolf', 34.5;
3|Wolf|34.5

这里我们返回三个字面量：即整数、字符串和浮点常量。

sqlite> .nullvalue NULL
sqlite> SELECT NULL;
NULL

.nullvalue 命令告诉 SQLite 将 NULL 值显示为 NULL。默认情况下，SQLite 为 NULL 值显示空字符串。 NULL 值也是一个字面量。

sqlite> SELECT quote(x'345eda2348587aeb');
X'345EDA2348587AEB'

BLOB 字面量是包含十六进制数据的字符串字面量，前面有一个 'x' 或 'X' 字符。

运算符

运算符 用于构建表达式。 SQL 运算符与数学运算符非常相似。 SQLite 支持一元和二元运算符。二元运算符使用两个操作数，一元运算符使用一个操作数。一个运算符可以有一个或两个操作数。 操作数 是运算符的其中一个输入（参数）。

SQLite 支持五大类运算符

算术运算符
布尔运算符
关系运算符
位运算符
其他运算符

SQLite 支持以下二元运算符

||
*    /    %
+    -
<<   >>   &    |
<    <=   >    >=
=    ==   !=   <>   IS  IS NOT  IN  LIKE  GLOB  BETWEEN  REGEXP
AND   
OR

运算符按优先级排列。 || 运算符具有最高优先级，OR 运算符具有最低优先级。

这些是一元前缀运算符

-    +    ~    NOT

一元 + 运算符是一个空操作。它什么也不做。一元 - 运算符将正值变为负值，反之亦然。

sqlite> SELECT -(3-44);
41

结果是 41。另外两个运算符将在稍后讨论。

算术运算符

SQLite 理解的算术运算符包括乘法、除法、加法、减法和求模。

sqlite> SELECT 3*3/9;
1

这些是我们从数学中知道的乘法和除法运算符。

sqlite> SELECT 9/2;
4

与 C 语言类似，这是一个整数除法。

sqlite> SELECT 9/2.0;
4.5

为了获得浮点值，其中一个操作数必须是浮点数。

sqlite> .nullvalue NULL
sqlite> SELECT 9 / 0;
NULL

不允许除以零，表达式返回 NULL。

sqlite> SELECT 3 + 4 - 1 + 5;
11

我们展示了加法和减法运算符。

sqlite> SELECT 11 % 3;
2

% 运算符称为求模运算符。它找到一个数除以另一个数的余数。 11 % 3，11 模 3 是 2，因为 3 进入 11 三次，余数为 2。

布尔运算符

使用布尔运算符，我们执行逻辑运算。 SQLite 有三个布尔运算符：AND、OR 和 NOT。布尔运算符返回 true 或 false。在 SQLite 中，1 表示 true，0 表示 false。

如果两个操作数都为真，则 AND 运算符的计算结果为真。

sqlite> SELECT 0 AND 0, 0 AND 1, 1 AND 0, 1 AND 1;
0|0|0|1

前三个运算的计算结果为 false，最后一个运算的计算结果为 true。

sqlite> SELECT 3=3 AND 4=4;
1

两个操作数都为真，因此结果为真 (1)。

如果至少一个操作数为真，则 OR 运算符的计算结果为真。

sqlite> SELECT 0 OR 0, 0 OR 1, 1 OR 0, 1 OR 1;
0|1|1|1

第一个运算的计算结果为 false，其他运算的计算结果为 true。

NOT 运算符是一个求反运算符。它将真变为假，将假变为真。

sqlite> SELECT NOT 1, NOT 0;
0|1
sqlite> SELECT NOT (3=3);
0

关系运算符

关系运算符用于比较值。

符号	含义
`<`	严格小于
`<=`	小于或等于
`>`	大于
`>=`	大于或等于
`=` 或 `==`	等于
`!=` 或 `<>`	不等于

这些运算符总是产生布尔值。

sqlite> SELECT 3*3 == 9, 9 = 9;
1|1

= 和 == 都是相等运算符。

sqlite> SELECT 3 < 4, 3 <> 5, 4 >= 4, 5 != 5;
1|1|1|0

关系运算符的用法是从数学中已知的。

位运算符

十进制数对人类来说是自然的。二进制数是计算机的原生数。二进制、八进制、十进制或十六进制符号只是同一数字的表示法。位运算符使用二进制数的位。我们有二进制逻辑运算符和移位运算符。

按位与运算符对两个数字执行逐位比较。只有当操作数中对应的位都为 1 时，结果的该位才为 1。

    00110
  & 00011
  = 00010

第一个数字是 6 的二进制表示法，第二个是 3，结果是 2。

sqlite> SELECT 6 & 3;
2
sqlite> SELECT 3 & 6;
2

按位或运算符对两个数字执行逐位比较。当操作数中对应的位任一为 1 时，结果的该位为 1。

     00110
  |  00011
   = 00111

结果是 00110 或十进制 7。

sqlite> SELECT 6 | 3;
7

位移位运算符 将位向右或向左移动。

number << n : multiply number 2 to the nth power
number >> n : divide number by 2 to the nth power

这些运算符也称为算术移位。

     00110
 >>  00001
   = 00011

我们将数字 6 的每个位向右移动。它等于将 6 除以 2。结果是 00011 或十进制 3。

sqlite> SELECT 6 >> 1;
3

     00110
  << 00001
   = 01100

我们将数字 6 的每个位向左移动。它等于将数字 6 乘以 2。结果是 01100 或十进制 12。

sqlite> SELECT 6 << 1;
12

位求反运算符 将每个 1 变为 0，将每个 0 变为 1。也称为 tilda 运算符。

sqlite> SELECT ~7;
-8
sqlite> SELECT ~-8;
7

该运算符反转数字 7 的所有位。其中一个位还决定该数字是否为负数。如果我们再次对所有位取反，我们再次得到数字 7。

字符串连接

|| 运算符是字符串连接运算符。它只是连接字符串。

sqlite> SELECT 'wolf' || 'hound';
wolfhound

我们添加两个字符串。

sqlite> SELECT 'star' || 3;
star3

可以连接字符串和数字。

IN 运算符

IN 和 NOT IN 运算符取左侧的表达式和右侧的值列表或子查询。它们检查列表中是否存在某个值。

sqlite> SELECT 'Tom' IN ('Tom', 'Frank', 'Jane');
1

在这里，我们检查 'Tom' 是否在 IN 运算符后面的名称列表中。返回值是一个布尔值。

对于以下示例，我们重新介绍了 Cars 表中的内容。

sqlite> SELECT * FROM Cars;
1|Audi|52642
2|Mercedes|57127
3|Skoda|9000
4|Volvo|29000
5|Bentley|350000
6|Citroen|21000
7|Hummer|41400
8|Volkswagen|21600

IN 运算符允许我们在 WHERE 子句中指定多个值。

sqlite> SELECT * FROM Cars WHERE Name IN ('Audi', 'Hummer');
1|Audi|52642
7|Hummer|41400

从 Cars 表中，我们选择在 IN 运算符之后列出的汽车。

sqlite> SELECT * FROM Cars WHERE Name NOT IN ('Audi', 'Hummer');
2|Mercedes|57127
3|Skoda|9000
4|Volvo|29000
5|Bentley|350000
6|Citroen|21000
8|Volkswagen|21600

使用 NOT IN 运算符，我们进行相反的操作：所有未列出的汽车名称。

sqlite> SELECT * FROM Cars WHERE Name IN (SELECT Name FROM Cars WHERE Price < 30000);
3|Skoda|9000
4|Volvo|29000
6|Citroen|21000
8|Volkswagen|21600

IN 运算符的右侧可以是一个子查询。

LIKE 运算符

LIKE 运算符用于 WHERE 子句中，以搜索列中指定的模式。 LIKE 模式中的百分号 (%) 匹配字符串中零个或多个字符的任何序列。模式中的下划线 (_) 匹配字符串中的任何单个字符。

sqlite> SELECT * FROM Cars WHERE Name LIKE 'Vol%';
4|Volvo|29000
8|Volkswagen|21600

在这里，我们选择名称以 'Vol' 开头的汽车。百分号 (%) 匹配任意数量的字符（包括零个字符）。

sqlite> SELECT * FROM Cars WHERE Name LIKE '____';
1|Audi|52642

下划线字符 (_) 匹配任何单个字符。在这里，我们选择一个正好有四个字符的汽车名称；有四个下划线。

sqlite> SELECT * FROM Cars WHERE Name LIKE '%EN';
6|Citroen|21000
8|Volkswagen|21600

默认情况下，LIKE 运算符不区分大小写。

sqlite> PRAGMA case_sensitive_like = 1;
sqlite> SELECT * FROM Cars WHERE Name LIKE '%EN';

使用 PRAGMA case_sensitive_like = 1 语句，我们可以使其区分大小写。

GLOB 运算符

GLOB 运算符类似于 LIKE，但它使用 Unix 文件全局语法作为其通配符。此外，GLOB 区分大小写，这与（默认）LIKE 不同。

* 通配符匹配任意数量的任何字符，包括无字符，? 匹配任何单个字符。

sqlite> SELECT * FROM Cars WHERE Name GLOB '*en';
6|Citroen|21000
8|Volkswagen|21600

在这里，我们有名称以 'en' 字符结尾的汽车。

sqlite> SELECT * FROM Cars WHERE Name GLOB '????';
1|Audi|52642

在这里，我们选择一个正好有四个字符的汽车名称。

sqlite> SELECT * FROM Cars WHERE Name GLOB '*EN';
sqlite> SELECT * FROM Cars WHERE Name LIKE '%EN';
6|Citroen|21000
8|Volkswagen|21600

这两个语句表明 LIKE 不区分大小写，而 GLOB 区分大小写。

[abc] 模式匹配括号中给出的一个字符。

sqlite> SELECT * FROM Cars WHERE Name GLOB '[VHS]*';
3|Skoda|9000
4|Volvo|29000
7|Hummer|41400
8|Volkswagen|21600

在示例中，我们选择名称以 V、H 或 S 字符开头的所有汽车。

BETWEEN 运算符

BETWEEN 运算符等效于一对比较； a BETWEEN b AND c 等效于 a>=b AND a<=c。

sqlite> SELECT * FROM Cars WHERE Price BETWEEN 20000 AND 55000;
1|Audi|52642
4|Volvo|29000
6|Citroen|21000
7|Hummer|41400
8|Volkswagen|21600

在此 SQL 语句中，我们选择了价格在 20000 到 55000 之间的汽车。

sqlite> SELECT * FROM Cars WHERE Price > 20000 AND Price > 55000;
1|Audi|52642
4|Volvo|29000
6|Citroen|21000
7|Hummer|41400
8|Volkswagen|21600

此表达式与之前的表达式等效。

REGEXP 运算符

对于 REGEXP 运算符，我们需要安装其他软件包。

$ sudo apt-get install sqlite3-pcre

我们安装 sqlite3-pcre，它是 SQLite 的 Perl 兼容正则表达式库。

sqlite> .load /usr/lib/sqlite3/pcre.so

我们加载扩展库。

sqlite> SELECT * FROM Cars WHERE Name REGEXP '^.{5}$';
3|Skoda|9000
4|Volvo|29000

'^.{5}$' 正则表达式查找正好有五个字符的汽车名称。

IS 和 IS NOT 运算符

IS 和 IS NOT 运算符的工作方式与 = 和 != 相同，除非其中一个或两个操作数都是 NULL。

如果两个操作数都是 NULL，则 IS 运算符的计算结果为 1（真），IS NOT 运算符的计算结果为 0（假）。如果一个操作数是 NULL 而另一个不是，则 IS 运算符的计算结果为 0（假），IS NOT 运算符的计算结果为 1（真）。

sqlite> .nullvalue NULL
sqlite> SELECT NULL = 0;
NULL
sqlite> SELECT NULL IS 0;
0
sqlite> SELECT NULL IS NOT 0;
1

当使用 NULL 值时，IS 和 IS NOT 运算符很有用。

CASE 表达式

使用 CASE WHEN ELSE 可以创建条件表达式。表达式以 END 关键字结尾。 SQLite 中的 CASE WHEN ELSE 表达式类似于编程语言中的 if-elseif-else 表达式。

sqlite> CREATE TEMP TABLE Numbers(Val INTEGER);
sqlite> INSERT INTO Numbers VALUES (1), (-3), (3), (0), (-5), (6);

我们创建一个包含一些整数值的临时表。

sqlite> SELECT Val, CASE WHEN Val>0 THEN 'positive'
   ...> WHEN Val < 0 THEN 'negative'
   ...> ELSE 'zero' END FROM Numbers;
1|positive
-3|negative
3|positive
0|zero
-5|negative
6|positive

CASE WHEN ELSE 表达式用于描述值。

优先级

运算符优先级 的规则指定首先计算哪些运算符。优先级级别对于避免表达式中的歧义是必要的。

以下表达式的结果是 28 还是 40？

3 + 5 * 5

就像在数学中一样，乘法运算符的优先级高于加法运算符。所以结果是 28。

(3 + 5) * 5

要更改求值顺序，我们可以使用括号。括号内的表达式始终首先被求值。

sqlite> SELECT 3+5*5, (3+5)*5;
28|40

第一个表达式的计算结果为 28，因为乘法运算符的优先级高于加法。在第二个示例中，我们使用括号来更改计算顺序。因此，第二个表达式的计算结果为 40。

这里我们再次列出了 SQLite 中的运算符。

unary + - ~ NOT
||
*    /    %
+    -
<<   <>   &    |
<    <=   >    >=
=    ==   !=   <>   IS  IS NOT  IN  LIKE  GLOB  BETWEEN  REGEXP
AND   
OR

同一行的运算符具有相同的优先级。优先级从下往上增加。

结合性

有时，优先级不足以确定表达式的结果。第二组规则（称为关联性规则）确定具有相同优先级级别的运算符的计算顺序。

9 / 3 * 3

此表达式的结果是什么，9 还是 1？乘法、除法和模运算符都是从左到右关联的。因此，表达式的求值方式如下：(9 / 3) * 3，结果为 9。

sqlite> SELECT 9 / 3 * 3;
9

关联性规则是从左到右。

算术、布尔、关系和位运算符都是从左到右关联的。

在本 SQLite 教程中，我们介绍了 SQLite 运算符和表达式。我们介绍了表达式中的优先级和关联性规则。

电子书