SQLite 表达式

字面值
运算符
字符串连接
IN运算符
LIKE运算符
GLOB运算符
BETWEEN运算符
REGEXP运算符
- IS和IS NOT运算符
CASE表达式
优先级
关联性

原文： http://zetcode.com/db/sqlite/expressions/

在 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 乘以 3 变成 11，余数为 2。

布尔运算符

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

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

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

两个操作数都为true，因此结果为true（1）。

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

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 的每个位向右移动。等于将六除以 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。它也称为波浪运算符。

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`运算符

WHERE子句中使用LIKE运算符在列中搜索指定的模式。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 文件通配符语法作为通配符。此外，与LIKE（默认）不同，GLOB区分大小写。

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

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运算符等效于一对比较。 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（true），而IS NOT运算符的计算结果为 0（false）。如果一个操作数是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 运算符和表达式。我们已经在表达式中介绍了优先级和关联性规则。

字面值

运算符

算术运算符

布尔运算符

关系运算符

按位运算符

字符串连接

IN运算符

LIKE运算符

GLOB运算符

BETWEEN运算符

REGEXP运算符

IS和IS NOT运算符

CASE表达式

优先级