SQLite 表达式 · ZetCode 中文系列教程

# SQLite 表达式 > 原文： [http://zetcode.com/db/sqlite/expressions/](http://zetcode.com/db/sqlite/expressions/) 在 SQLite 教程的这一部分中，我们介绍了 SQLite 运算符和表达式。编程语言中的表达式是值，变量，运算符和函数的组合，这些值，变量，运算符和函数根据特定的优先级规则和特定编程语言的关联规则进行解释（求值），然后计算并生成（返回）有状态的环境）的另一个值。据说该表达式可以计算出该值。 ## 字面值字面值是某种常量。字面值可以是整数，浮点数，字符串，`BLOB`或`NULL`。 ```sql sqlite> SELECT 3, 'Wolf', 34.5; 3|Wolf|34.5 ``` 在这里，我们返回三个字面值：即整数，字符串和浮点常量。 ```sql sqlite> .nullvalue NULL sqlite> SELECT NULL; NULL ``` `.nullvalue`命令告诉 SQLite 将`NULL`值显示为`NULL`。默认情况下，SQLite 显示`NULL`值的空字符串。 `NULL`值也是字面值。 ```sql sqlite> SELECT quote(x'345eda2348587aeb'); X'345EDA2348587AEB' ``` `BLOB`字面值是包含十六进制数据并以单个`"x"`或`"X"`字符开头的字符串字面值。 ## 运算符运算符用于构建表达式。 SQL 运算符与数学运算符非常相似。 SQLite 支持一元和二进制运算符。二元运算符使用两个操作数，一元运算符使用一个。一个运算符可以有一个或两个操作数。操作数是运算符的输入（参数）之一。 SQLite 支持五种主要的运算符： * 算术运算符 * 布尔运算符 * 关系运算符 * 按位运算符 * 其他运算符 SQLite 支持以下二进制运算符： ```sql || * / % + - << >> & | < <= > >= = == != <> IS IS NOT IN LIKE GLOB BETWEEN REGEXP AND OR ``` 运算符按照优先级排列。 `||`运算符的优先级最高，`OR`运算符的优先级最低。这些是一元前缀运算符： ```sql - + ~ NOT ``` 一元`+`运算符是空操作。它什么也没做。一元`-`运算符将正值更改为负值，反之亦然。 ```sql sqlite> SELECT -(3-44); 41 ``` 结果为 41。其他两个运算符将在后面讨论。 ### 算术运算符 SQLite 理解的算术运算符是乘法，除法，加法，减法和模运算。 ```sql sqlite> SELECT 3*3/9; 1 ``` 这些是我们从数学中学到的乘法和除法运算符。 ```sql sqlite> SELECT 9/2; 4 ``` 与 C 语言类似，这是整数除法。 ```sql sqlite> SELECT 9/2.0; 4.5 ``` 为了获得浮点值，操作数之一必须是浮点数。 ```sql sqlite> .nullvalue NULL sqlite> SELECT 9 / 0; NULL ``` 不允许被零除，表达式返回`NULL`。 ```sql sqlite> SELECT 3 + 4 - 1 + 5; 11 ``` 我们显示加法和减法运算符。 ```sql 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`。 ```sql sqlite> SELECT 0 AND 0, 0 AND 1, 1 AND 0, 1 AND 1; 0|0|0|1 ``` 前三个操作求值为 false，最后一个求值为 true。 ```sql sqlite> SELECT 3=3 AND 4=4; 1 ``` 两个操作数都为`true`，因此结果为`true`（1）。如果至少一个操作数为`true`，则 OR 运算符的计算结果为 true。 ```sql sqlite> SELECT 0 OR 0, 0 OR 1, 1 OR 0, 1 OR 1; 0|1|1|1 ``` 第一个操作求值为`false`，其他操作求值为`true`。 `NOT`运算符是一个否定运算符。它使真假成为假。 ```sql sqlite> SELECT NOT 1, NOT 0; 0|1 sqlite> SELECT NOT (3=3); 0 ``` ### 关系运算符关系运算符用于比较值。 | 符号 | 含义 | | --- | --- | | `<` | 严格小于 | | `<=` | 小于或等于 | | `>` | 比...更棒 | | `>=` | 大于或等于 | | `=`或`==` | 等于 | | `!=`或`<>` | 不等于 | 这些运算符总是产生布尔值。 ```sql sqlite> SELECT 3*3 == 9, 9 = 9; 1|1 ``` `=`和`==`都是相等运算符。 ```sql sqlite> SELECT 3 < 4, 3 <> 5, 4 >= 4, 5 != 5; 1|1|1|0 ``` 关系运算符的用法从数学上是已知的。 ### 按位运算符小数对人类是自然的。二进制数是计算机固有的。二进制，八进制，十进制或十六进制符号仅是相同数字的符号。按位运算符使用二进制数的位。我们有二进制逻辑运算符和移位运算符。按位且运算符在两个数字之间进行逐位比较。仅当操作数中的两个对应位均为 1 时，位位置的结果才为 1。 ```sql 00110 & 00011 = 00010 ``` 第一个数字是二进制符号 6，第二个数字是 3，结果是 2。 ```sql sqlite> SELECT 6 & 3; 2 sqlite> SELECT 3 & 6; 2 ``` 按位或运算符在两个数字之间进行逐位比较。如果操作数中的任何对应位为 1，则位位置的结果为 1。 ```sql 00110 | 00011 = 00111 ``` 结果为 00110 或十进制 7。 ```sql sqlite> SELECT 6 | 3; 7 ``` 按位移位运算符向右或向左移位。 ```sql number << n : multiply number 2 to the nth power number >> n : divide number by 2 to the nth power ``` 这些运算符也称为算术移位。 ```sql 00110 >> 00001 = 00011 ``` 我们将数字 6 的每个位向右移动。等于将六除以 2。结果为 00011 或十进制 3。 ```sql sqlite> SELECT 6 >> 1; 3 ``` ```sql 00110 << 00001 = 01100 ``` 我们将数字 6 的每个位向左移动。等于将数字 6 乘以 2。结果为`01100`或十进制 12。 ```sql sqlite> SELECT 6 << 1; 12 ``` 按位取反运算符分别将 1 更改为 0，将 0 更改为 1。它也称为波浪运算符。 ```sql sqlite> SELECT ~7; -8 sqlite> SELECT ~-8; 7 ``` 运算符将数字 7 的所有位取反。其中一位还确定数字是否为负。如果我们再一次对所有位取反，我们将再次得到 7。 ## 字符串连接 `||`运算符是字符串连接运算符。它只是连接字符串。 ```sql sqlite> SELECT 'wolf' || 'hound'; wolfhound ``` 我们添加两个字符串。 ```sql sqlite> SELECT 'star' || 3; star3 ``` 可以连接字符串和数字。 ## `IN`运算符 `IN`和`NOT IN`运算符的左侧为表达式，右侧为值列表或子查询。他们检查列表中是否存在值。 ```sql sqlite> SELECT 'Tom' IN ('Tom', 'Frank', 'Jane'); 1 ``` 在这里，我们检查[Tom]是否在`IN`运算符后面的名称列表中。返回值是一个布尔值。对于以下示例，我们概括了`Cars`表中的内容。 ```sql 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`子句中指定多个值。 ```sql sqlite> SELECT * FROM Cars WHERE Name IN ('Audi', 'Hummer'); 1|Audi|52642 7|Hummer|41400 ``` 从`Cars`表中，我们选择在`IN`运算符之后列出的汽车。 ```sql 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`运算符，我们可以进行反向操作：未列出的所有汽车名称。 ```sql 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`模式中的百分号（`%`）与字符串中任何零个或多个字符的序列匹配。模式中的下划线（`_`）与字符串中的任何单个字符匹配。 ```sql sqlite> SELECT * FROM Cars WHERE Name LIKE 'Vol%'; 4|Volvo|29000 8|Volkswagen|21600 ``` 在这里，我们选择名称以`"Vol"`开头的汽车。百分号（`%`）匹配任意数量的字符（包括零个字符）。 ```sql sqlite> SELECT * FROM Cars WHERE Name LIKE '____'; 1|Audi|52642 ``` 下划线字符（_）匹配任何单个字符。在这里，我们选择正好有四个字符的汽车名称；有四个下划线。 ```sql sqlite> SELECT * FROM Cars WHERE Name LIKE '%EN'; 6|Citroen|21000 8|Volkswagen|21600 ``` 默认情况下，`LIKE`运算符不区分大小写。 ```sql 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`区分大小写。 `*`通配符与任意数量的任意字符匹配，包括空，`?`匹配任何单个字符。 ```sql sqlite> SELECT * FROM Cars WHERE Name GLOB '*en'; 6|Citroen|21000 8|Volkswagen|21600 ``` 在这里，我们有一些车名以`"en"`字符结尾的汽车。 ```sql sqlite> SELECT * FROM Cars WHERE Name GLOB '????'; 1|Audi|52642 ``` 在这里，我们选择正好有四个字符的汽车名称。 ```sql 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]`模式匹配括号中给定的一个字符。 ```sql 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`。 ```sql 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 单位之间的汽车。 ```sql 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`运算符，我们需要安装其他包。 ```sql $ sudo apt-get install sqlite3-pcre ``` 我们安装`sqlite3-pcre`，它是 SQLite 的 Perl 兼容正则表达式库。 ```sql sqlite> .load /usr/lib/sqlite3/pcre.so ``` 我们加载扩展库。 ```sql 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（真）。 ```sql 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`表达式。 ```sql sqlite> CREATE TEMP TABLE Numbers(Val INTEGER); sqlite> INSERT INTO Numbers VALUES (1), (-3), (3), (0), (-5), (6); ``` 我们用一些整数值创建一个临时表。 ```sql 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 的结果是什么？ ```sql 3 + 5 * 5 ``` 像数学中一样，乘法运算符的优先级高于加法运算符。结果是 28。 ```sql (3 + 5) * 5 ``` 要更改求值的顺序，可以使用括号。括号内的表达式始终首先被求值。 ```sql sqlite> SELECT 3+5*5, (3+5)*5; 28|40 ``` 第一个表达式的计算值为 28，因为乘法运算符的优先级高于加法器。在第二个示例中，我们使用括号来更改求值顺序。因此第二个表达式的值为 40。在这里，我们再次将操作符列表放到 SQLite 中。 ```sql unary + - ~ NOT || * / % + - << <> & | < <= > >= = == != <> IS IS NOT IN LIKE GLOB BETWEEN REGEXP AND OR ``` 同一行上的运算符具有相同的优先级。优先级从低到高。 ## 关联性有时，优先级不足以确定表达式的结果。第二组规则，称为关联性规则，确定具有相同优先级的运算符的求值顺序。 ```sql 9 / 3 * 3 ``` 此表达式的结果是 9 还是 1？乘法，删除和模运算符从左到右关联。因此，该表达式的计算方式为：`(9 / 3) * 3`，结果为 9。 ```sql sqlite> SELECT 9 / 3 * 3; 9 ``` 关联规则是从左到右。算术，布尔，关系和按位运算符都是从左到右关联的。在 SQLite 教程的这一部分中，我们介绍了 SQLite 运算符和表达式。我们已经在表达式中介绍了优先级和关联性规则。