SQLite 运算符
我们已经创建了 company 和 department 表，又插入了很多数据，但是如果数据量再大个10倍左右，那么列出所有数据的方式查看数据就显得工作效率太低了。我们能不能在列出数据的时候设置一些条件呢？ 比如 年龄大于 25，薪水高于 20000 的。
这时候，SQLite 的运算符就排上用场了
SQLite 运算符是什么？
运算符是一个保留字或字符，主要用在 SQLite 语句的 WHERE 子句中指定条件。
运算符可以在 WHERE 子句中连接多个条件。
SQLite 的运算符有：
- 算术运算符
- 比较运算符
- 逻辑运算符
- 位运算符
SQLite 算术运算符
假设变量 a= 3，变量 b= 7 ，那么
| 运算符 | 描述 | 范例 |
|---|---|---|
| + | 加法 : 把运算符两边的值相加 | a + b 将得到 10 |
| - | 减法 : 左操作数减去右操作数 | a - b 将得到 -4 |
| * | 乘法 : 把运算符两边的值相乘 | a * b 将得到 21 |
| / | 除法 : 左操作数除以右操作数 | b / a 将得到 2 |
| % | 取模 : 左操作数除以右操作数后得到的余数 | b % a 将得到 1 |
范例: SQLite 算术运算符
sqlite> select 13 + 17; 13 + 17 = 30
sqlite> select 13 - 17; 13 - 17 = -4
sqlite> select 13 * 17; 13 * 17 = 221
sqlite> select 17 / 13; 17 / 13 = 1 -- 两个整数相除，返回的结果为整数，直接抛弃小数点
sqlite> select 17.0 / 13; 17.0 / 13 = 1.30769230769231 -- 两个数相除，只要有一个是浮点数，那么返回的结果也是浮点数
sqlite> select 17 % 13; 17 % 13 = 4
select 17 % 13.0; 17 % 13.0 = 4.0
SQLite 比较运算符
假设变量 a=13，变量 b=17
| 运算符 | 描述 | 范例 |
|---|---|---|
| == | 如果两个操作数的值相等，结果为真 | (a == b) 不为真 |
| = | 如果两个操作数的值相等，结果为真 | (a = b) 不为真 |
| != | 如果两个操作数的值不相等，结果为真 | (a != b) 为真 |
| <> | 如果两个操作数的值不相等，结果为真 | (a <> b) 为真 |
| > | 如果左操作数的值大于右操作数的值，结果为真 | (a > b) 不为真 |
| < | 如果左操作数的值小于右操作数的值，结果为真 | (a < b) 为真 |
| >= | 如果左操作数的值大于等于右操作数的值，结果为真 | (a >= b) 不为真 |
| <= | 如果左操作数的值小于等于右操作数的值，结果为真 | (a <= b) 为真 |
| !< | 如果左操作数的值不小于右操作数的值，结果为真 | (a !< b) 为假 |
| !> | 如果左操作数的值不大于右操作数的值，结果为真 | (a !> b) 为真 |
范例
假设我们之前往 company 中添加的数据如下
sqlite> SELECT * FROM company; id name age address salary ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
接下来的范例演示了各种 SQLite 比较运算符的用法
在这里，我们使用了 WHERE 子句，这将会在后边单独的一个章节中讲解 但现在你只需要知道：WHERE 子句是用来设置 SELECT 语句的条件语句
列出 salary 大于 50,000.00 的记录
sqlite> SELECT * FROM company WHERE salary > 50000; id name age address salary ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
列出 salary 等于 20,000.00 的记录：
sqlite> SELECT * FROM company WHERE salary = 20000; id name age address salary ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0
列出 salary 不等于 20,000.00 的记录
sqlite> SELECT * FROM company WHERE salary != 20000; id name age address salary ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
列出 salary 不等于 20,000.00 的记录
sqlite> SELECT * FROM company WHERE salary <> 20000; id name age address salary ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
列出 salary 大于等于 65,000.00 的记录：
sqlite> SELECT * FROM company WHERE salary >= 65000; id name age address salary ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
SQLite 逻辑运算符
下表是 SQLite 中所有的逻辑运算符
| 运算符 | 描述 |
|---|---|
| AND | AND 运算符允许在 SQL 语句的 WHERE 子句中的多个条件的存在 |
| BETWEEN | BETWEEN 运算符用于在给定最小值和最大值之间的值中搜索值 |
| EXISTS | EXISTS 运算符用于在满足一定条件的指定表中搜索行的存在 |
| IN | IN 运算符用于把某个值与一系列指定列表的值进行比较 |
| NOT IN | IN 运算符的对立面，用于把某个值与不在指定列表的值进行比较 |
| LIKE | LIKE 运算符用于把某个值与使用通配符运算符的相似值进行比较 |
| GLOB | GLOB 运算符用于把某个值与使用通配符运算符的相似值进行比较 GLOB 与 LIKE 不同之处在于，它是大小写敏感的 |
| NOT | NOT 运算符是所用的逻辑运算符的对立面 比如 NOT EXISTS、NOT BETWEEN、NOT IN，等等 它是否定运算符 |
| OR | OR 运算符用于结合一个 SQL 语句的 WHERE 子句中的多个条件 |
| IS NULL | NULL 运算符用于把某个值与 NULL 值进行比较 |
| IS | IS 运算符与 = 相似 |
| IS NOT | IS NOT 运算符与 != 相似 |
| || | 连接两个不同的字符串，得到一个新的字符串 |
| UNIQUE | UNIQUE 运算符搜索指定表中的每一行，确保唯一性（无重复） |
范例
假设我们之前往 company 中添加的数据如下
sqlite> SELECT * FROM company; id name age address salary ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
下面的范例演示了 SQLite 逻辑运算符的用法
列出 age 大于等于 25 且 工资大于等于 65000.00 的记录
sqlite> SELECT * FROM company WHERE age >= 25 AND salary >= 65000; id name age address salary ---------- ---------- ---------- ---------- ---------- 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
列出 age 大于等于 25 或 工资大于等于 65000.00 的所有记录
sqlite> SELECT * FROM company WHERE AGE >= 25 OR salary >= 65000; id name age address salary ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
列出 age 不为 NULL 的所有记录
结果显示所有的记录，意味着没有一个记录的 age 等于 NULL
sqlite> SELECT * FROM company WHERE age IS NOT NULL; id name age address salary ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 2 Allen 25 Texas 15000.0 3 Teddy 23 Norway 20000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
列出 name 以 'Ki' 开始的所有记录，'Ki' 之后的字符不做限制
sqlite> SELECT * FROM company WHERE name LIKE 'Ki%'; id name age address salary ---------- ---------- ---------- ---------- ---------- 6 Kim 22 South-Hall 45000.0
列出 name 以 'Ki' 开始的所有记录，'Ki' 之后的字符不做限制
sqlite> SELECT * FROM company WHERE name GLOB 'Ki*'; id name age address salary ---------- ---------- ---------- ---------- ---------- 6 Kim 22 South-Hall 45000.0
列出 age 的值为 25 或 27 的所有记录
sqlite> SELECT * FROM company WHERE age IN ( 25, 27 ); id name age address salary ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
列出 age 的值既不是 25 也不是 27 的记录
sqlite> SELECT * FROM company WHERE age NOT IN ( 25, 27 ); id name age address salary ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0 3 Teddy 23 Norway 20000.0 6 Kim 22 South-Hall 45000.0 7 James 24 Houston 10000.0
列出 age 的值在 25 与 27 之间的所有记录
sqlite> SELECT * FROM company WHERE age BETWEEN 25 AND 27; id name age address salary ---------- ---------- ---------- ---------- ---------- 2 Allen 25 Texas 15000.0 4 Mark 25 Rich-Mond 65000.0 5 David 27 Texas 85000.0
使用 SQL 子查询
下面的 SELECT 语句使用 SQL 子查询，子查询查找 salary 大于 65000 的带有 age 字段的所有记录，后边的 WHERE 子句与 EXISTS 运算符一起使用，列出了外查询中的 age 存在于子查询返回的结果中的所有记录：
sqlite> SELECT age FROM company WHERE EXISTS (SELECT age FROM company WHERE salary > 65000); age ----- 32 25 23 25 27 22 24
下面的 SELECT 语句使用 SQL 子查询，子查询查找 salary 大于 65000 的带有 age 字段的所有记录，后边的 WHERE 子句与 > 运算符一起使用，列出了外查询中的 age 大于子查询返回的结果中的年龄的所有记录：
sqlite> SELECT * FROM company WHERE age > (SELECT age FROM company WHERE salary > 65000); id name age address salary ---------- ---------- ---------- ---------- ---------- 1 Paul 32 California 20000.0
SQLite 位运算符
位运算符作用于位，并逐位执行操作。
真值表 & 和 | 如下：
| p | q | p & q | p | q |
|---|---|---|---|
| 0 | 0 | 0 | 0 |
| 0 | 1 | 0 | 1 |
| 1 | 1 | 1 | 1 |
| 1 | 0 | 0 | 1 |
假设 A = 60，且 B = 13，它们的二进制格式如下：
A = 0011 1100 B = 0000 1101
那么
A&B = 0000 1100 A|B = 0011 1101 ~A = 1100 0011
下表中列出了 SQLite 语言支持的位运算符。
假设变量 A=60，变量 B=13，则
| 运算符 | 描述 | 范例 |
|---|---|---|
| & | 如果同时存在于两个操作数中，二进制 AND 运算符复制一位到结果中 | (A & B) 将得到 12 即为 0000 1100 |
| | | 如果存在于任一操作数中，二进制 OR 运算符复制一位到结果中 | (A | B) 将得到 61 即为 0011 1101 |
| ~ | 二进制补码运算符是一元运算符 具有"翻转"位效应，即0变成1，1变成0。 |
(~A ) 将得到 -61 即为 1100 0011 一个有符号二进制数的补码形式 |
| << | 左移运算符 左操作数的值向左移动右操作数指定的位数 |
A << 2 将得到 240 即为 1111 0000 |
| >> | 右移运算符 左操作数的值向右移动右操作数指定的位数 |
A >> 2 将得到 15 即为 0000 1111 |
范例: SQLite 二进制操作符用法
sqlite> .mode line sqlite> select 60 | 13; 60 | 13 = 61
sqlite> select 60 & 13; 60 & 13 = 12
sqlite> select 60 ^ 13; 10 * 20 = 200
sqlite> select (~60); (~60) = -61
sqlite> select (60 << 2); (60 << 2) = 240
sqlite> select (60 >> 2); (60 >> 2) = 15