SQLite学习--查询操作(一)
数据库查询
select命令与操作管道
从语法上说，select命令用一系列字句将很多关系操作组合在一起，每个字句代表一种特定的关系操作.在SQLite中，几乎所有的子句都是可选的。
select命令的通用格式如下:
select [distinct] heading from tables where predicate group by columns having predicate order by columns limit count,offset; 复制代码
上面命令中的每个关键字例如from,where,having等都是一个单独的子句，每个子句由关键字和跟随的子句组成。
select命令从from开始，接收一个或多个输入关系，将它们组合成单一复合关系，传递给后续操作链。
除了select之外，所有的子句都是可选的，必须提供select才是有效的查询命令，最常见的select命令由三个子句组成:select,from,where。这种基本的语法形式和相关的子句如下所示:
select heading from tables where predicate;
上面的from子句是由逗号分隔的一个或多个表，视图等。如果指定多个表，他们会组合形成单一关系，这是通过链接操作完成的。from产生的结果关系作为最初的数据关系，所有后续的其它操作将直接从最初的数据中工作或者从最初数据的派生内容中工作。
where子句将会过滤上一步from操作后生成的结果R,的行。where的参数是predicate，或者是逻辑表达式，它们定义了R中行的选择标准，那些行将会出现(或排除)在结果中。从where子句中选中的行将形成新的关系R2.
下面的操作演示了从一个数据表中获取数据:
select id,name from food_types; id name ---------- ---------- 1 水果 2 蔬菜 3 牛肉 4 鸡肉 复制代码
上面的查询语句中由于没有where子句过滤行，所以将会返回food_types表中的所有行的数据。同时select子句指定了要查询food_types表中的id和name两列的值，这是food_types表中的所有列。而且from子句并没有连接表，所以上面的语句返回的就是food_types表的一份副本。在SQLite中也支持使用*代表所有列，所以上面的语句也可以写成:
select * from food_types;
总之，SQLite中基本的select处理收集from子句中的所有数据，在where子句中过滤行，在select子句中过滤列。
过滤
SQLite对from子句产生的每一行应用where子句。where是一种限制，也是一种过滤器。where的参数是逻辑预测，最简单的预测形式就是断言某事。
如下面的例子:
The dog(subject主语) is purple and has a toothy grin(predicate谓语).
上面的例子中，dog是主语，预测是由两个断言组成的(颜色是紫色purple的)以及a toothy grin(咧嘴时牙齿很多)。这种声明可能为真，也可能为假，依赖于狗的实际情况。
where子句的主语是行，行是最大的逻辑主语。where子句是逻辑预测，评估为真的所有行将会被包含在结果集中，评估为假的将会被排除。因此，上面对狗的声明翻译成等价的关系操作如下:
select * from dogs where color = 'purple' and grin ='toothy';
上面的SQL语句执行时，数据库会获取dogs表中的所有行，然后应用where子句预测形成逻辑预测：
this row has color = 'purple' and grin = 'toothy'.
值
"值"代表了真是世界的某种数据。值可以按照它们的所属类型进行划分，如数字值(1,2,3等)，或字符串值('Jujy-Fruit')。
"值"可以表现为文字的值(1,2,3或'Jujy-Fruit'),变量(一般是形如foods.name的列名称)，表达式(3+2/5),函数的结果(counts(foods.name))等。
操作符
操作符使用一个或多个值作为输入并产生一个新值作为输出。之所以叫它操作符，是因为它可以完成某种操作并产生某种结果。二元操作符操作两个输入值(或称操作数)，三元操作符操作三个操作数，单目操作符操作一个值等等。
操作符可以捆绑在一起，一个操作符的输出可以作为另一个操作符的输入，形成值表达式。
将操作符串接在一起，可以创建值表达式，以其它值表达机器复杂的表达式，例如:
x = count(episodes.name) y = count(foods.name) z = y / x * 11 复制代码
二元操作符
二元操作符是目前最常用的SQL操作符，下面的表列出了SQLite所支持的二元操作符，表中按优先级从高到低的次序排列，同一区域的一组具有相同的优先级:
| 操作符 | 类型 | 作用 |
|---|---|---|
| ` | ` | String |
* | Arithmetic(算术运算符) | 乘 |
/ | Arithmetic | 除 |
% | Arithmetic | 模 |
+ | Arithmetic | 加 |
- | Arithmetic | 减 |
<< | Bitwise(位运算符) | 右移 |
>> | Bitwise | 左移 |
& | Logical(逻辑运算符) | 与 |
| ` | ` | Logical |
< | Relational(关系运算符) | 小于 |
<= | Relational | 小于或等于 |
> | Relational | 大于 |
>= | Relational | 大于或等于 |
= | Relational | 等于 |
== | Relational | 等于 |
<> | Relational | 不等于 |
!= | Relational | 不等于 |
IN | Logical | In |
AND | Logical | 与 |
OR | Logical | 或 |
IS | Logical Equal | 等于 |
LIKE | Relational St | 字符串匹配 |
GLOB | Relational Fi | 文件名匹配 |
在上面的表中，算术操作符(例如加，减，乘，除，取模)都是输入数字值并产生一个数字值的二元操作符。
sqlite3->select 3 + 3; 3 + 3 ---------- 6 复制代码
关系操作符(例如>,<,=)是比较值和值表达式并产生逻辑结果(也称为真假值)的二元操作符。关系操作符形成逻辑表达式，例如:
x > 5
1 < 2
逻辑表达式是返回真假值的任意表达式。在SQLite中，false可以由数字0代替，true可以由其它的非0值代替，例如:
sqlite3->select 1 > 0; 1 > 0 ---------- 1 sqlite3->select 1 < 0; 1 < 0 ---------- 0 sqlite3->select 2 > 0; 2 > 0 ---------- 1 sqlite3->select -1 and 1; -1 and 1 ---------- 1 sqlite3->select 0 and 1; 0 and 1 ---------- 0 sqlite3-> sqlite3-> sqlite3->select 0 & 1; 0 & 1 ---------- 0 sqlite3->select -1 & 1; -1 & 1 ---------- 1 sqlite3->select 0 or 1; 0 or 1 ---------- 1 sqlite3->select 0 | 1; 0 | 1 ---------- 1 复制代码
逻辑操作符
逻辑操作符(AND,OR,NOT,IN)是对真假值或逻辑表达式进行操作的二元操作符。它们根据输入产生具体的真假值。它们可以用较简单的表达式构造更复杂的逻辑表达式，例如:
(x < 5) AND (x != 3)
(y < 2) OR (y > 4) AND NOT (y = 0)
(color = 'purple') AND (grin = 'toothy')
x in (x,y,z)
真加值遵从通常的布尔逻辑规则，但是考虑到null则会有些麻烦。where子句是由这些真假值组成的--使用逻辑操作符回答SQLite数据库中数据的真实问题，例如:
sqlite3->select id,name from new_contacts where id < 4 AND id >= 2; id name ---------- ---------- 2 ddd 3 eee sqlite3->select id,name from new_contacts where id = 5 OR id = 3; id name ---------- ---------- 3 eee 5 ddd sqlite3->select id,name from new_contacts where id < 5 AND id > 1 AND NOT (id = 4); id name ---------- ---------- 2 ddd 3 eee sqlite3->select id,name from new_contacts where id in (0,5,3); id name ---------- ---------- 3 eee 5 ddd 复制代码
LIKE与GLOB操作符
LIKE操作符是一个很有用的关系操作符。LIKE的作用与相等(==)类似，都是通过一个模式来进行字符串匹配的。例如，要查询new_contacts表中以z开头的联系人信息，则可以使用下面的查询语句:
sqlite> SELECT * FROM new_contacts WHERE name LIKE 'Z%'; id name phone email address ---------- ---------- ----------- ---------- ---------- 1 zyf 13567890987 no address 4 zyf UNKNOW no address 复制代码
模式中的百分号(%)可与任意0个或多个字符匹配。下划线(_)可与任意单个字符进行匹配。百分号是贪婪匹配，它会匹配除了该字符本身外的所有在字符之间的内容。如果百分号在模式的最左边或者最右边，它将匹配字符的另外一边。如下所示:
sqlite> select * from new_contacts where name like '%ac%P%'; id name phone email address ----- -------------------- ---------- ---------- ---------- 7 Guacamole Dip UNKNOW no address 8 Peach Schnapps UNKNOW no address 9 Mackinaw Peaches UNKNOW no address 10 Pie (Blackberry) Pie UNKNOW no address 复制代码
上面的查询语句，我们可以看作是两部分，一部分是字符串中需要包含ac这两个字符，接着是字符串中需要包含以p开始的字符，并且ac在前，包含以p开始的字符串在后。
上面我们通过使用LIKE操作符进行了字符串的匹配操作，我们也可以通过使用NOT LIKE来否定某些模式，如下所示:
sq-> select * from new_contacts where name like '%ac%' and name not like 'p%'; id name phone email address ---------- -------------------------------------------------- ---------- ---------- ---------- 7 Guacamole Dip UNKNOW no address 9 Mackinaw Peaches UNKNOW no address 12 ac Pie Pie UNKNOW no address 复制代码
在上面的查询语句中，我们希望查询出联系人名称中包含ac字符串但是不是以p开头的字符串。
GLOB操作符在行为上和LIKE相似，关键的不同在于它有些像Unix/Linux文件名替换语法。也就是说，它会使用文件名替换相关的通配符，例如*和?(*代表一个或者多个,?代表一个单一的数字或字符),并且匹配是大小写敏感的。如下所示:
sq-> select * from new_contacts where name glob 'Pie*'; id name phone email address ---------- -------------------------------------------------- ---------- ---------- ---------- 10 Pie (Blackberry) Pie UNKNOW no address 13 Pie accc UNKNOW no address 复制代码
?的匹配:
sq-> select * from new_contacts where name glob 'P?ie'; id name phone email address ---------- -------------------------------------------------- ---------- ---------- ---------- 23 Pcie UNKNOW no address 复制代码
限定和排序
可以使用limit和offset关键字限定结果集的大小和范围。limit指定返回记录的最大数量，offset指定偏移的记录数。例如，下面的命令是演示了new_contacts表中第三行的数据:
sq-> select * from new_contacts limit 1 offset 2; id name phone email address ---------- -------------------------------------------------- ---------- ---------- ---------- 3 eee UNKNOW no address 复制代码
关键字offset指定在结果集中跳过指定的行数(上面的命令中指定跳过前两行)，关键字limit限制最多返回多少行数据(上面的命令中指定最多返回一行数据)。
作者：ZhangYiFan
链接：https://juejin.cn/post/7027075302924746759