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 運算符和表達式。我們已經在表達式中介紹了優先級和關聯性規則。