聚合函数函数作用AVG(x)返回 x 列的平均值COUNT(x) / COUNT(*)统计条数，x 表示统计 x 列 不为null 的条数，* 表示统计总条数GROUP_CONCAT(X [,y])使用 "," 拼接 x 列，若传递 y，则使用 x 拼接 y 列MAX(x)返回 x 列中的最大值MIN(x)返回 x 列中的最小值SUM(x)返回 x 列的和，如果输入全是null，则返回 nullTOTAL(x)返回 x 列的和，如果输入全是null，则返回 0.00数值函数更多函数参考：https://www.sqlite.org/lang_mathfunc.html函数作用ABS(x)返回 x 的绝对值random()返回 -9223372036854775808 和 +9223372036854775807 之间的伪随机整数MOD(x,y)返回 x/y 的模pi()返回 π 的近似值MAX(X1,X2[,...Xn])返回传入参数中的最大值MIN(X1,X2[,...Xn])返回传入参数中的最小值CEIL(x)返回大于X的最小整数值，3.35.0+FLOOR(x)返回小于x的最大整数值，3.35.0+ROUND(x,y)返回参数x的四舍五入到小数点y位的值，若忽略 y 则默认为 0POW(x,y) / POWER(x,y)返回 x 的 y 次方逻辑函数函数作用coalesce(X1,X2[,...Xn]) 返回列表中第一个非 NULL 的值，若全是NULL，则返回NULL，至少需要两个参数IFNULL(x,y) 如果 X 为 null，返回 y ,否则返回 xNULLIF(x,y)如果 x 与 y 不相同，则返回 x ，否则返回 NULLIIF(x,y,z)如果 x 为真 则返回 y 否则返回 z字符串函数函数作用replace(str,x,y)用字符串 y 替换字符串 str 中出现的所有字符串 xinstr(x,y)返回字符串 y 在 x 中出现的位置，不存在返回0 ，x、y 任意一个为NULL 则返回nulllength(str)返回字符串 str 的长度substr(str,x,y) 返回字符串 str 中 x 位置开始的 y 个字符，y 省略则返回剩下全部upper(str)返回 str 所有字符的大写lower(str)返回 str 所有字符的小写ltrim(str[,x])删除字符串 str 左侧的空格，如果传递字符串 x 则删除左侧的 x 字符串rtrim(str[,x])删除字符串 str 右侧的空格，如果传递字符串 x 则删除右侧的 x 字符串trim(str[,x])删除字符串 str 左右两侧的空格，如果传递字符串 x 则删除左右两侧的 x 字符串like('%x%', str)模糊查询字符串 x 在字符串 y 中是否存在时间函数函数作用date(timestring, modifier, modifier...)以 YYYY-MM-DD 格式返回日期time(timestring, modifier, modifier...)以 HH:MM:SS 格式返回日期datetime(timestring, modifier, modifier...)以 YYYY-MM-DD HH:MM:SS 格式返回日期julianday(timestring, modifier, modifier...)返回从格林尼治时间的公元前 4714年 11 月 24日 正午算起的天数unixepoch(timestring, modifier...)返回 自 1970-01-01 00:00:00 算起的秒数strftime(format, timestring, modifier, modifier)根据指定字符串格式化日期timediff(timestringA, timestringB)返回 从时间B 到时间A 相差时间的修饰符timestring 时间字符串modifier 修饰符format 格式化上面的几个时间函数，将时间字符串作为参数，时间字符串后面跟零个或多个 modifier 修饰符。时间字符串时间字符串可以使用下面任意一种格式：时间字符串示例YYYY-MM-DD2022-01-15YYYY-MM-DD HH:MM2022-01-15 22:30YYYY-MM-DD HH:MM:SS.SSS2022-01-15 22:35:02.100MM-DD-YYYY HH:MM15-01-2022 12:30HH:MM12:10YYYY-MM-DDTHH:MM2022-01-15 22:36HH:MM:SS22:30:01YYYYMMDD HHMMSS20220115 223701now当前日期 2022-01-15修饰符时间字符串后边可跟着零个或多个的修饰符，这将改变有上述五个函数返回的日期和/或时间。任何上述五大功能返回时间。修饰符应从左到右使用，下面列出了可在 SQLite 中使用的修饰符：修饰符描述±N days / day±N 天±N hours±N 小时±N minutes±N 分钟±N seconds±N 秒±N months±N 月±N years±N 年±HH:MM±时:分±HH:MM:SS±时:分:秒±HH:MM:SS.SSS±时:分:秒.毫秒±YYYY-MM-DD±年-月±YYYY-MM-DD HH:MM±年-月-日 时:分±YYYY-MM-DD HH:MM:SS±年-月-日 时:分:秒±YYYY-MM-DD HH:MM:SS.SSS±年-月-日 时:分:秒.毫秒start of month月初start of year年初start of day一天的开始weekday N返回下一个为周 N 的日期和时间，取值范围 0 ~ 6，0 表示星期天，1 表示星期一unixepoch返回 自 1970-01-01 00:00:00 算起的秒数julianday强制将时间值数字解释为儒略日数字。 强制采用 DDDDDDDDD 时间值格式，如果使用任何其他时间值格式，则会导致返回 NULL。auto自动修饰localtime当地时间utc世界标准时间subsec\subsecond亚秒/亚秒级格式化SQLite 提供了非常方便的函数用来格式化任何日期和时间。替换描述%d一月中的第几天，01-31%f带小数部分的秒，SS.SSS%H小时，00-23%j一年中的第几天，001-366%J儒略日数，DDDD.DDDD%m月，00-12%M分，00-59%s从 1970-01-01 算起的秒数%S秒，00-59%w一周中的第几天，0-6 (0 is Sunday)%W一年中的第几周，01-53%Y年，YYYY%%%时间函数示例-- 查看当前时间，以及 当前时间 2天的修饰符 sqlite> SELECT date('now'),date('now','+2 days'); date('now') date('now','+2 days') ----------- --------------------- 2023-10-15 2023-10-17 -- 计算当前月份的最后一天 sqlite> SELECT date('now','start of month','+1 month','-1 day'); date('now','start of month','+1 month','-1 day') ------------------------------------------------ 2023-10-31 -- 获取当前时间戳 sqlite> select unixepoch('now'),strftime('%s','now'); unixepoch('now') strftime('%s','now') ---------------- -------------------- 1697379431 1697379431 -- 获取指定时间戳的 utc 时间 以及本地时间 sqlite> SELECT datetime(1697379898, 'unixepoch'),datetime(1697379898, 'unixepoch','localtime'); datetime(1697379898, 'unixepoch') datetime(1697379898, 'unixepoch','localtime') --------------------------------- --------------------------------------------- 2023-10-15 14:24:58 2023-10-15 22:24:58 -- NOW 返回的已经是标准的 utc 时间（没加时区）了，如果再使用一次 UTC 修饰符，则会再计算一次 -- 例如 中国是东八区，NOW 时间是 当地时间 -8小时，如果再次使用 UTC 修饰符，则会在 NOW 时间的基础上 再 -8 小时。 sqlite> SELECT datetime('now', 'utc'); datetime('now', 'utc') ---------------------- 2023-10-15 06:33:09JSON 函数和运算符SQLite 版本从 3.38.0 （2022-02-22）版本开始，JOSN 函数与运算符内置到 SQLite 中，在之前的版本里，JSON 函数是一个扩展，只有在包含 -DSQLITE_ENABLE_JSON1 编译选项时才会包含在构建中。SQLite （3.45.0之前）将 JSON 存储为普通文本，为了保证 db文件的向后兼容性，意味着 SQLite 只能存储 Null、整数、浮点数、文本、和 BLOB 值，无法添加第六种 JSON 类型。 SQLite 当前使用的 JSON 库通过递归实现，为了避免使用过多的栈空间，任何超过 1000 层的 JSON 输入都将被视为无效。 JSONB从 3.45.0 （2024-01-15）版本开始，SQLite 重写了所有 JSON 函数，改为新的内部解析树格式 （JSONB），通过将 SQLite 的 JSON 内部二进制表示形式直接存储在数据库中，应用程序可以在读取和更新 JSON 值时绕过解析和呈现 JSON 的开销。内部 JSONB 格式使用的磁盘空间也比文本 JSON 略少。 JSONB 是 SQLite 使用的 JSON 的二进制表示形式，仅供内部使用，应用程序不应该 SQLite 之外使用 JSONB。人话：JSONB 的解析、操作、存储都应该通过 SQLite 进行，从应用层面应无感的，不要直接操作 JSONB，防止因为不规范操作 JSONB 导致 SQLite 读取时无法解析。 JSON 参数说明JSON 参数对于接受 JSON 作为第一个参数的函数，该参数可以是 JSON 对象、数组、数字、字符串或 null。SQLite 数值（整数和浮点）和 Null 值分别被解析为数字和空值。SQLite 字符串可以理解为 JSON 对象、数组或字符串。如果传入错误格式的 JSON 字符串，该函数通常会抛出错误（json_valid() 和 json_quote() 例外）。PATH 参数对于接受 PATH 参数的函数，该PATH 必须格式正确，否则该函数会抛出错误。正确的 PATH 格式是一个字符串，以 $ 开头，后面跟零个、一个或多个 .key 或者 [index] 实例。数组索引说明：数组索引通常为非负整数 N、在这种情况下，选择数组的第 N 个元素则是从左侧 0 下标开始。数组索引也可以使用 "#-N" 的形式，这样就会从右边开始选择第 N 个元素，例如 "#-1" 表示选择数组中的最后一个元素。有时数组索引仅使用 "#" 也很有用，可以将新的值附加到 JSON 数组的末尾，例如：sqlite> select json_set('["a","b","c"]','$[#]','e'); json_set('["a","b","c"]','$[#]','e') ------------------------------------ ["a","b","c","e"]VALUE 参数对于接受 VALUE 参数的函数，这些参数通常被理解为被引用的文本字符串，并在结果中成为 JSON 字符串的值。即使输入的值是一个 JOSN 字符串，也会被解析为文本字符串而非 JSON 对象。但如果值来自于另一个 JOSN 函数返回的结果或 -> 运算符（不包括 ->>），则该参数将被理解为 JSON 对象，而且会完整插入 JSON 对象中，而不是作为字符串引入。如下所示：# 字符串和函数 sqlite> select json_set('["a","b","c"]','$[#]','[1,2,3]'); json_set('["a","b","c"]','$[#]','[1,2,3]') ------------------------------------------ ["a","b","c","[1,2,3]"] sqlite> select json_set('["a","b","c"]','$[#]',json('[1,2,3]')); json_set('["a","b","c"]','$[#]',json('[1,2,3]')) ------------------------------------------------ ["a","b","c",[1,2,3]] # 运算符 -> 和 ->> sqlite> select json_set('["a","b","c"]','$[#]','[1,2,3]'->'$'); json_set('["a","b","c"]','$[#]','[1,2,3]'->'$') ----------------------------------------------- ["a","b","c",[1,2,3]] sqlite> select json_set('["a","b","c"]','$[#]','[1,2,3]'->>'$'); json_set('["a","b","c"]','$[#]','[1,2,3]'->>'$') ------------------------------------------------ ["a","b","c","[1,2,3]"]需要明确的是：json 参数的值无论来自何处，都会被解释为 JSON 对象。value 参数的值只有来自于 另一个JOSN 函数或 -> 操作符时，才会被解析为 JSON 对象，否则被视为 JSON 字符串。在处理 JOSN 字符串参数（JSON 字符串 与 JOSN 对象互转）时，SQLite 不会对 Unicode 进行转义，只会当作纯文本来处理（例如 \uXXXX 这样的Unicode）。JSON 函数函数名说明json(x)将 JSON 字符串转为 JSON 对象，若字符串 x 不是合法 JSON 对象则抛出错误json_array([x...])接受零个或多个参数 x，转为 JSON 数组，当任何参数为 BLOB 时会抛出错误。json_array_length(x[,p])返回 JOSN 数组 x 中的元素数量，若 x 不是 JSON 数组 则返回 0 若传递参数 P,则返回对应路径的数组长度，若 P 路径不存在则返回 NULLjson_extract(x,p1[,...pN])从参数 x 的 JSON 参数中 按照路径 p1[,...pN] 提取一个或多个值，转为 JSON 对象返回。json_insert(x,[...<p,v>])在参数 x 中创建指定路径(参数p)和值(参数v)， 其中路径与参数成对出现，允许零对或多对。 仅路径不存在时插入，若已存在则不会修改json_replace(x,[...<p,v>])在参数 x 中修改指定路径(参数p)的值(参数v)， 其中路径与参数成对出现，允许零对或多对。 仅路径存在时会修改，若不存在则不会创建json_set(x[,...<p,v>])在参数 x 中设置指定路径(参数p)的值(参数v)， 其中路径与参数成对出现，允许零对或多对。 路径不存在时创建，路径存在时则修改json_object([...,<k,v>])接受零对或多对 key-value 参数，并返回由这些参数组成格式正确的 JSON 对象。 每对的第一个参数为 key 第二个参数为 value, 当前版本中 key 重复不会引发错误，但在以后的迭代中可能会发生更改。json_patch(t,p)将参数 P 与 参数 T 进行合并，返回一个新的 JSON 对象。 路径一致时，使用 P 的 value 替换 T 的值，当值替换为 NULL 时表示在合并后删除该元素。 该函数通常是 json_set() 或 json_remove() 函数的替代品， MergePatch 将 JSON Array 对象视为原子对象，只会替换不会追加。json_remove(x[,...p])将参数 x 中的 p路径属性进行删除，允许传递一个零个或多个路径。 没有传递路径时，将返回格式化后的 Xjson_type(x[,p])返回 参数 x 中路径 P 的元素类型，返回类型可能是 NULL,true,false,integer,read,array,object。 不传递参数 P 时，返回最外层元素类型，传递路径不存在时返回 NULLjson_error_position(x)SQLite 3.42.0+，返回 JSON 参数第一个错误字符的位置，若没有错误字符则返回 0 json_valid(x) 严格要求参数符合规范 JSON，而 json_error_position(x) 允许 JSON5 扩展规范json_valid(x)如果参数 x 的 JSON 格式符合 RFC-7159 JSON 规范（不包含 JOSN5 扩展），则返回 1 否则返回 0json_quote(x)将参数 x （数字或字符串）转换为相应的 JSON 表示形式。 如果 x 是另一个 JSON 函数的返回值，则直接返回不做处理。json_group_array(x)聚合函数，GROUP BY 分组后将 x 列转为 JSON 数组返回，类似于 MySQL 的 GROUP_CONCAT()。json_group_object(name,value)聚合函数，GROUP BY 分组后 返回一个由 name 列和 value 列组成的 json 对象json_each(x[,p])将参数 x 的 JSON 参数转为查询结果集，如果不传递参数 p 时，默认只解析第一个，类似于 MySQL 的 json_table() 函数json_tree(x[,p])用法与 json_each() 类似，区别在于 json_tree() 会递归遍历子元素。# json 示例 sqlite> select json('{"a" : "123",b:[1,2,3]}'); json('{"a" : "123",b:[1,2,3]}') ------------------------------- {"a":"123","b":[1,2,3]} # json_array 示例 sqlite> select json_array(1,2,3,4,'6'); json_array(1,2,3,4,'6') ----------------------- [1,2,3,4,"6"] # json_array_length 示例 sqlite> select json_array_length('[1,2,3,4]'); json_array_length('[1,2,3,4]') ------------------------------ 4 # json_error_position 示例 sqlite> select json_error_position('[1,2,3,4]'); json_error_position('[1,2,3,4]') -------------------------------- 0 # json_extract 示例 sqlite> select json_extract('{"a":123,"b":[4,5,6]}','$.b'); json_extract('{"a":123,"b":[4,5,6]}','$.b') ------------------------------------------- [4,5,6] # json_insert 示例 sqlite> select json_insert(json('{"a":123,"b":[4,5,6]}'),'$.c','两双筷子'); json_insert(json('{"a":123,"b":[4,5,6]}'),'$.c','两双筷子') ------------------------------------------------------- {"a":123,"b":[4,5,6],"c":"两双筷子"} # json_replace 示例 sqlite> select json_replace(json('{"a":123,"b":[4,5,6]}'),'$.a','两双筷子'); json_replace(json('{"a":123,"b":[4,5,6]}'),'$.a','两双筷子') -------------------------------------------------------- {"a":"两双筷子","b":[4,5,6]} # json_set 示例 sqlite> select json_set(json('{"a":123,"b":[4,5,6]}'),'$.d','两双筷子'); json_set(json('{"a":123,"b":[4,5,6]}'),'$.d','两双筷子') ---------------------------------------------------- {"a":123,"b":[4,5,6],"d":"两双筷子"} # json_object 示例 sqlite> select json_object('name','dbkuaizi','age',35,'blog','https://www.dbkuaizi.com'); json_object('name','dbkuaizi','age',35,'blog','https://www.dbkuaizi.com') ------------------------------------------------------------ {"name":"dbkuaizi","age":35,"blog":"https://www.dbkuaizi.com"} # json_patch 示例 sqlite> select json_patch('{"a":123,"b":"dbkuaizi"}','{"a":233,"b":null,"c":"两双筷子"}'); json_patch('{"a":123,"b":"dbkuaizi"}','{"a":233,"b":null,"c":"两双筷子"}') ------------------------------------------------------------ {"a":233,"c":"两双筷子"} # json_remove 示例 sqlite> select json_remove('{"a":123,"b":"dbkuaizi"}','$.b'); json_remove('{"a":123,"b":"dbkuaizi"}','$.b') --------------------------------------------- {"a":123} # json_type 示例 sqlite> select json_type('{"a":123,"b":["https://","www.","dbkuaizi",".com"]}','$.b'); json_type('{"a":123,"b":["https://","www.","dbkuaizi",".com"]}','$.b') ------------------------------------------------------------ array # json_error_position 示例 sqlite> select json_error_position('[1,2,3,,4]'); json_error_position('[1,2,3,,4]') --------------------------------- 8 # json_valid 示例 sqlite> select json_valid('{"a":123,"b":}'); json_valid('{"a":123,"b":}') ---------------------------- 0 # json_quote 示例 sqlite> select json_quote('{"a":123}'); json_quote('{"a":123}') ----------------------- "{\"a\":123}" # json_each 示例 sqlite> select * from json_each('[1,"2",["a","b","c"]]'); key value type atom id parent fullkey path --- ------------- ------- ---- -- ------ ------- ---- 0 1 integer 1 1 $[0] $ 1 2 text 2 2 $[1] $ 2 ["a","b","c"] array 3 $[2] $ # json_tree 示例 sqlite> select * from json_tree('[1,"2",["a","b","c"]]'); key value type atom id parent fullkey path --- --------------------- ------- ---- -- ------ ------- ---- [1,"2",["a","b","c"]] array 0 $ $ 0 1 integer 1 1 0 $[0] $ 1 2 text 2 2 0 $[1] $ 2 ["a","b","c"] array 3 0 $[2] $ 0 a text a 4 3 $[2][0] $[2] 1 b text b 5 3 $[2][1] $[2] 2 c text c 6 3 $[2][2] $[2] # json_group_array 示例，获取不同数据类型的 id sqlite> select type,json_group_array(id) from json_tree('[1,"2",["a","b","c"]]') group by type; type json_group_array(id) ------- -------------------- array [0,3] integer [1] text [2,4,5,6] # json_group_object 示例，获取不同数据类型的 id 与父 id sqlite> select type,json_group_object(id,parent) from json_tree('[1,"2",["a","b","c"]]') group by type; type json_group_object(id,parent) ------- ---------------------------- array {"0":null,"3":0} integer {"1":0} text {"2":0,"4":3,"5":3,"6":3}JSON 运算符从 SQLite 3.38.0 (2022-02-22) 开始，支持使用 -> 和 ->> 运算符获取 JSON 的子元素，在使用方法上兼容 MySQL 和 PostgreSQL。-> 运算符返回所选节点的 JSON 对象表示形式，节点不存在返回 NULL。->> 运算符 返回所选节点的 SQL TEXT、INTEGER、REAL 或 NULL 值，当指定节点不存在时 返回 NULL。sqlite> select '{"a":"123","b":[4,5,6]}' -> '$.a'; '{"a":"123","b":[4,5,6]}' -> '$.a' ---------------------------------- "123" sqlite> select '{"a":"123","b":[4,5,6]}' ->> '$.a'; '{"a":"123","b":[4,5,6]}' ->> '$.a' ----------------------------------- 123 sqlite> select '{"a":123,"b":[4,5,6]}' -> '$.b[2]'; '{"a":123,"b":[4,5,6]}' -> '$.b[2]' ----------------------------------- 6窗口函数SQLite 自 3.25.0 版本开始支持窗口函数，窗口函数是一种 SQL 特性，专门用于聚合、排序、分析数据等场景。虽然绝大多数窗口函数能做到的查询，不使用窗口函数也能实现，但实现 SQL 的要复杂很多。窗口函数通常可以在单个查询中完成多个操作，避免了子查询或表连接，因此在大部分分析场景下，比传统 SQL 效率更高。总之 SQLite 引入窗口函数是非常重要的改进，方便开发者处理更复杂的数据分析类 SQL 查询场景。窗口函数与普通聚合函数的区别在于：如果函数具有 OVER 语句，就是窗口函数。窗口函数示例数据表我们模拟一张学生的各科考试成绩表，由于-- 建表语句 CREATE TABLE pupil_grade ( /* 姓名 */ name INTEGER, /* 学科 */ subject text not null, /* 分数，不得小于 0 */ grade INTEGER not null default 0 check(grade > 0) ); -- 示例数据 INSERT INTO pupil_grade (name, subject, grade) VALUES('两双筷子', 'php', 80), ('两双筷子', 'golang', 60), ('两双筷子', 'sql', 85), ('dbkuaizi', 'php', 98), ('dbkuaizi', 'golang', 77), ('dbkuaizi', 'sql', 55), ('一只筷子', 'php', 65), ('一只筷子', 'golang', 69), ('一只筷子', 'sql', 43);举个栗子我们用一个例子，尽可能直观的展现窗口函数的特点。sqlite> SELECT *,row_number() OVER(PARTITION BY subject ORDER BY grade desc) as top FROM pupil_grade; name subject grade top -------- ------- ----- --- dbkuaizi golang 77 1 两双筷子 golang 60 2 一只筷子 golang 54 3 dbkuaizi php 98 1 两双筷子 php 80 2 一只筷子 php 65 3 两双筷子 sql 85 1 dbkuaizi sql 55 2 一只筷子 sql 43 3所谓窗口就是将数据分为一个一个组，然后在各自的组内分表进行数据的操作。例如上面这个例子，以 学科 为分组（也称分区）条件，在每个组中用 分数 进行倒叙排序，再使用窗口函数 row_number() 给分区内的每一行分配序号，得到上面的结果集。对这个例子延申一下，外面包一层查询，就可以轻松获取到不同科目的分数第 N 名。也可以扩展如下：公司各部门薪资最高的员工每个用户第 N 个订单之类的场景sqlite> select * from ( (x1...> SELECT *,row_number() OVER(PARTITION BY subject ORDER BY grade desc) as top FROM pupil_grade (x1...> ) where top = 1; name subject grade top -------- ------- ----- --- dbkuaizi golang 77 1 dbkuaizi php 98 1 两双筷子 sql 85 1取每学科平均分：sqlite> SELECT *,avg(grade) OVER(PARTITION BY subject) as top FROM pupil_grade; name subject grade top -------- ------- ----- ---------------- 两双筷子 golang 60 63.6666666666667 dbkuaizi golang 77 63.6666666666667 一只筷子 golang 54 63.6666666666667 两双筷子 php 80 81.0 dbkuaizi php 98 81.0 一只筷子 php 65 81.0 两双筷子 sql 85 61.0 dbkuaizi sql 55 61.0 一只筷子 sql 43 61.0PARTITION BY窗口函数中，使用 PARTITION BY 将结果集划分为多个分区，每个分区内 PARTITION BY 子句的所有项具有相同的值。如果没有 PARTITION BY 子句，则整个结果集就是单个分区。窗口函数处理的逻辑是根据每个分区单独执行的。内置窗口函数除了聚合窗口函数外，SQLite 还有一组仿照 PostgreSQL 支持的内置窗口函数。一共有 11 个，便于理解我划分为两类：序号类在同一个分区中，按照不同条件对数据进行编号，主要关注 subject 字段和对应函数的结果关系。SELECT *, row_number() over w as `row_number`, rank() over w as `rank`, dense_rank() over w as `dense_rank`, percent_rank() over w as `percent_rank`, cume_dist() over w as `cume_dist`, ntile(2) over w as `ntile` FROM pupil_grade WINDOW w AS (PARTITION BY subject ORDER BY grade desc); # 执行结果 name |subject|grade|row_number|rank|dense_rank|percent_rank|cume_dist |ntile| -----------+-------+-----+----------+----+----------+------------+------------------+-----+ dbkuaizi |golang | 77| 1| 1| 1| 0.0|0.3333333333333333| 1| 两双筷子 |golang | 60| 2| 2| 2| 0.5|0.6666666666666666| 1| 一只筷子 |golang | 54| 3| 3| 3| 1.0| 1.0| 2| 两双筷子 |php | 80| 1| 1| 1| 0.0|0.6666666666666666| 1| dbkuaizi |php | 80| 2| 1| 1| 0.0|0.6666666666666666| 1| 一只筷子 |php | 65| 3| 3| 2| 1.0| 1.0| 2| 两双筷子 |sql | 85| 1| 1| 1| 0.0|0.3333333333333333| 1| dbkuaizi |sql | 55| 2| 2| 2| 0.5|0.6666666666666666| 1| 一只筷子 |sql | 43| 3| 3| 3| 1.0| 1.0| 2|函数名说明row_number()当前分区行号，若定义 ORDER BY 子句，则以定义顺序编号，若值一样或无 ORDER BY 则任意顺序编号。rank()当分数为 80 时序号都为 1，下一个分数 65 序号为 3 ，掠过了序号 2，出现了间隙。（注意 PHP 分区的 rank）dense_rank()当分数为 80 时序号都为 1，下一个分数 65 序号为 2 ，没有间隙。（注意 PHP 分区的 dense_rank）percent_rank()返回 0.0 ~ 1.0 的值，等价于 (rank - 1) / (partition-rows - 1) 。 其中 rank 是内置窗口函数 rank（） 返回的值，partition-rows 是分区中的总行数。 如果分区仅包含一行，则此函数返回 0.0。cume_dist()累计分布，计算方式为： row_number() / 分区总行数， 返回 0 ~ 1。ntile(N)将分区内的数据，尽可能平均的分为 N 个组，一般用于大量数据的处理场景，需要将数据分配给 N 个进程时。 注意：由于记录的数据不一定被N整除，所以每组的记录数量不一定完全一致。值类在同一分区，基于排序结果，对值的各种操作：主要关注 subject 字段和对应函数的结果关系SELECT *, lag(grade,1,0) over w as `lag`, lead(grade,1,0) over w as `lead`, first_value(grade) over w as `fv`, last_value(grade) over w as `lv`, nth_value(grade,2) over w as `nv` FROM pupil_grade WINDOW w AS (PARTITION BY subject ORDER BY grade desc); # 执行结果 name |subject|grade|lag|lead|fv|lv|nv| -----------+-------+-----+---+----+--+--+----+ dbkuaizi |golang | 77| 0| 60|77|77|NULL| 两双筷子 |golang | 60| 77| 54|77|60| 60| 一只筷子 |golang | 54| 60| 0|77|54| 60| 两双筷子 |php | 80| 0| 80|80|80| 80| dbkuaizi |php | 80| 80| 65|80|80| 80| 一只筷子 |php | 65| 80| 0|80|65| 80| 两双筷子 |sql | 85| 0| 55|85|85|NULL| dbkuaizi |sql | 55| 85| 43|85|55| 55| 一只筷子 |sql | 43| 55| 0|85|43| 55|函数名说明lag(expr[,offset,default])返回当前行前一行的值，如果没有前一行（当前行为第一行），则返回 Null offset: 默认为前一行，可以指定前 N 行，default: 若指定行不存在时返回此值而不是 Nulllead(expr[,offset,default])返回当前行后一行的值，如果没有后一行（当前行为最后一行），则返回 Null offset: 默认为后一行，可以指定后 N 行，default: 若指定行不存在时返回此值而不是 Nullfirst_value(expr)返回当前行所在分区的第一行值，注意：逐行计算所以是以当前行为基础last_value(expr)返回当前行所在分区的最后一行的值，注意：逐行计算所以是以当前行为基础，所以看起来是当前行。 若要获取分区最后一行，使用: rows between unbounded preceding and unbounded followingnth_value(expr,N)返回分区的 第 N 行其他函数函数作用typeof(X)返回数据类型sqlite_version()返回 SQLite 版本changes()返回最后完成的插入、更新、删除 SQL 所影响的行数last_insert_rowid()返回最后完成的插入行的 ID参考资料SQLite 官方文档：https://sqlite.org/docs.html
数据库操作SQLite 官方给出一系列命令行工具（简称 CLP），可以通过命令行来操作 SQLite，下载地址：https://sqlite.org/download.html下载名为：sqlite-tools-xxx的文件，下面以 windows 为例：解压后可以使用 目录中的 sqlite3.exe 进入数据库操作。基础命令.open 打开一个 SQLite 文件，若没有则创建。.h 查看帮助.exit 退出命令行工具创建数据库数据库可以使用 Navicat 等数据库管理工具，直接创建一个文件，也可以使用命令行工具创建：PS E:\Tools> sqlite3 test.db虽然我们指定了数据库的名称，但目前位置SQLite 并未创建该数据库，直到数据库内部创建一些内容时，Sqlite 才会创建该数据库。这样做的好处是在数据库保存在磁盘之前，可以进行各种永久性设置，数据库一旦创建，一些设置例如页面大小、字符集是不能轻易改变的。要将数据库文件写入磁盘，只需要创建一个表：sqlite> create table test(id integer primary key,value text);现在创建了一个 test.db 数据库文件，数据库包含 test 表，表中有两列：id 主键列，当定义一个整形的主键列之后，SQLite 会在该列上应用自增函数。value ，一个简单的文本域插入数据sqlite> insert into test(value) values('张三'); sqlite> insert into test(value) values('李四'); sqlite> insert into test(value) values('王五'); sqlite> insert into test(value) values('赵六');查询数据sqlite> .mode column sqlite> select * from test; id value -- ----- 1 张三 2 李四 3 王五 4 赵六退出前创建一个索引和视图sqlite> create index test_idx on test(value); sqlite> create view schema as select * FROM sqlite_master;退出 SQLite 命令行.exitwindows 快捷键 Ctrl+CLinux 快捷键 Ctrl + D数据库信息获取表与视图： .tables查看索引： indices [表名称]查看DDL：使用 .schema [table name] 可以得到一个表或视图的定义语句，如果没有提供表名，则返回所有数据库对象 (table、index、view、trigger) 的定义语句：sqlite> .schema test CREATE TABLE test(id integer primary key,value text); CREATE INDEX test_idx on test(value); sqlite> .schema CREATE TABLE test(id integer primary key,value text); CREATE INDEX test_idx on test(value); CREATE VIEW schema as select * FROM sqlite_mastersqlite_master通过查询系统视图 sqlite_master 表可以获取更详细的信息：字段名说明type对象类型 （table、index、trigger、view）name对象名称tbl_name对象关联的表Rootpage对象跟页面所在的数据库索引sql对象的 SQL 定义查询返回结果如下：sqlite> .mode column sqlite> .headers on sqlite> select * from sqlite_master; type name tbl_name rootpage sql ----- -------- -------- -------- ---------------------------------------------------- table test test 2 CREATE TABLE test(id integer primary key,value text) index test_idx test 3 CREATE INDEX test_idx on test(value) view schema schema 0 CREATE VIEW schema as select * FROM sqlite_master sqlite>导出数据.dump [table_name|table_view]使用 .dump 命令可以将数据库对象导出成 SQL 格式。不带任何参数时，.dump 将导出 DDL 和 DML 命令，或指定需要导出的表名及视图。如果导出的表或视图不存在则会被忽略，在 Shell 模式中 dump 命令默认输出到屏幕，也可以通过 .output [filename] 的方式将数据导出到文件：-- 输出到 test.sql 文件,文件不存在将被创建，如果存在则会被覆盖 sqlite> .output test.sql sqlite> .dump -- 恢复输出到屏幕 sqlite> .output stdout通过 SQL 重定向的各种设置，可以很大程度控制导出的数据。导入数据导入数据支持 SQL 文件和 CSV 文件，可以使用 .read 命令导入 .dump 命令生成的 SQL 文件。使用 .import [file][table] 命令可以导入 CSV 文件。导入SQLsqlite> drop table test; sqlite> drop view schema; sqlite> .read test.sql导入 CSV注意：导入CSV时，分隔符需要使用 ,，否则无法导入。sqlite> CREATE TABLE test2(id integer primary key,value text); sqlite> .separator , sqlite> .import file2.csv test2 sqlite> select * FROM test2;格式化命令行提供了格式化命令的选项，使结果集输出的更加整齐。.mode 命令可以设置结果数据中的几种输出格式，可选格式有CSV、column、html、insert、line、list、tabs 和 tcl。默认格式是 list 模式显示结果集，并通过 管道符分割字段列，每种格式有不同的用途。如果想以 CSV 格式导出数据可以如下操作：sqlite> .output file.csv sqlite> .mode csv sqlite> select * FROM test; sqlite> .output stdout也可以通过设置分割符的方式导出 CSV：sqlite> .output .file2.csv sqlite> .separator , sqlite> select * FROM test; sqlite> .output stdout区别在于 CSV 模式将自动换行字段值并且加上双引号，而列表模式不加。无人值守维护个人理解的无人值守其实就是在不进入 sqlite3 命令行工具的情况下，进行一些数据库操作。需要注意的是，输出文件的编码是根据终端编码来的，比如使用CMD导出的文件编码是gbk，如果使用utf-8 打开就会乱码。建议使用 git bash 等工具操作。导出数据到文件PS E:\Tools> sqlite3 test.db .dump > test.sql这个操作会将所有 DDL 和 DML 语句 导出到SQL文件。 如果只需某个表的所有记录 可以执行如下命令：sqlite3 test.db "select * FROM test" > test.sql通过导出文件创建数据库$ sqlite3 test2.db < test.sql备份数据库备份数据库有两种方法，一种就是使用 SQLite 无人值守导出功能，直接导出数据库。还有一种方法，就是简单粗暴的将数据库的db文件复制一份。当然官方提供了一个清理工具，在备份前执行一下可以清除掉一些被释放掉的对象，减少数据库文件体积。Administrator@DESKTOP-7ROTTMK MINGW64 /e/Tools $ sqlite3 test.db vacuum Administrator@DESKTOP-7ROTTMK MINGW64 /e/Tools $ cp test.db tets.backup重建数据库VACUUM 命令可以重建数据库文件，将其重建并写入到磁盘替换源数据库文件。执行此操作的原因有多种：除非 SQLite 在 auto_vacuum=FULL 模式下运行，否则从数据库文件中删除大量数据时，会留下很多空闲的数据页。这意味着数据库文件会占额外的磁盘空间，运行 VACUUM 来重建数据库，会回收该空间以减小数据库文件的大小。频繁的插入、更新和删除操作会导致数据库文件变得碎片化（单个表或索引的数据分散在数据库文件中），运行 VACUUM 可以保证每个表的索引在很大程度上连续存储在数据库文件中。在某些情况下，VACUUM 还可以减少数据库中部分填充的页数，从而进一步减小数据库文件的大小。在 SQLite 数据库中删除记录时，物理文件中不会被删除，而是将保存的内容标记删除。这样可能会其他人在非法获取到数据库文件时，恢复已删除的内容。运行 VACUUM 可以将已删除的内容在物理层面从数据库中清除，从而防止数据库文件泄漏时，其他人通过特殊手段恢复数据。一些数据库配置项只允许在创建时设置，而 VACUUM 命令会从头创建新的数据库文件，所以也可以用于修改数据库配置信息。工作原理VACUUM 命令的工作原理是将数据库中的内容复制到临时数据中，然后使用临时文件的内容覆盖原始文件。这就意味着对数据库进程 VACUUM 操作时，所需的磁盘空间是原始数据库文件的两倍。sqlite3_analyzer官方的工具包中提供了一个工具，可以查看数据库磁盘结构的详细信息。这些信息包括数据库、表和索引分类的单个对象，以及聚合的统计信息。还提供了如页面大小、表总数、索引、文件大小、和页面平均密度到单个数据库对象的详细说明等信息。Administrator@DESKTOP-7ROTTMK MINGW64 /e/Tools $ sqlite3_analyzer test.db /** Disk-Space Utilization Report For test.db Page size in bytes................................ 4096 Pages in the whole file (measured)................ 4 Pages in the whole file (calculated).............. 4 Pages that store data............................. 4 100.0% Pages on the freelist (per header)................ 0 0.0% Pages on the freelist (calculated)................ 0 0.0% Pages of auto-vacuum overhead..................... 0 0.0% Number of tables in the database.................. 3 Number of indices................................. 1 Number of defined indices......................... 1 ...语法命令SQL 由命令组成，每条命令以 ; 结束（SQL语句需要，而 SQLite 命令不需要）。例如：SELECT * FROM users;命令由一系列记号组成，记号可以是常量、关键字、标识符、表达式或者其他特殊字符。令牌符号以空格分开，例如 空格、tab 和新一行。关键字关键字是指具 SQL 中具有含义的单词，这些单词包括 select、update、insert、create、drop 和 begin 等。标识符是指数据库中具体的对象，如表或者索引，关键字是保留单词，不允许用作标识符。SQLite 不区分关键字和标识符的大小写。常量SQLite 支持三种常量类型：字符串常量、数字常量、二进制常量。字符串常量： SQLIite 支持 单引号和双引号定义字符串，但是建议只使用单引号。如果字符串本身包含单引号，则需要使用两个单引号： 'kanny ''s chicken'数值常量： 整数、十进制数和科学计数法表述的数，下面是一些举例：-13.1426.0221415E23二进制常量： 二进制值使用 x'0000' 的表示方法，其中每一位都是一个16进制数。二进制值必须由两个16进制的整倍数（8bits） 组成，下面是一些例子：x'01'x'0fff'x'0F0EFF'x'0f0effab'SQLite 默认的字符常量值是大小写敏感的，比如 Mike 与 mike 是不同的。注释支持单行 -- 和多行注释 /* */表操作创建数据表语法create [temp] table table_name (column_definitions [, constrainst]);temp 表示这表是临时表，只存在于当前会话中，断开连接会自动销毁。sqlite> create table contacts ( ...> id integer primary key, ...> name text not null collate nocase, ...> phone text not null default 'UNKNOWN', ...> unique(name,phone)); sqlite>语法说明如下：id 为主键，sqlite 会自动为 整型主键使用自增长。name text 类型，约束不能为 null，并且排序不区分大小写。phone text 类型unique() 对 name 和 phone 做唯一约束SQLite 中有五种本地类型，integer、real、test、blob 和 null，类型部分会在后面的 "存储类" 中介绍。自增 IDSQLite 中有两种自增 ID，它们在实际使用时有一些区别，建议使用 AUTOINCREMENT 关键字的方式创建自增 ID使用 INTEGER PRIMARY KEYCREATE TABLE table_name ( id INTEGER PRIMARY KEY, name TEXT );将 ID 列设置为 INTEGER PRIMARY KEY 时，他会成为一个自增的 ID 列，它的自增原理是取 ID 这一列最大的值，然后 +1 用作插入数据的唯一 ID，如果没有则从 1 开始。从这个自增规则中可以发现一个问题，例如数据表中当前的自增 ID 是2，接着插入一条数据，这条数据的 ID 将会是 3。接着我们删掉了这条数据，再插入一条新数据，我们会发现重新插入的数据 ID 依然是 3 。这样就会造成 ID 的不唯一性，如果用于数据关联就会出现异常。使用 AUTOINCREMENTCREATE TABLE table_name ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT );在这种情况下，自增 ID 会记录在 sqlite_sequence 表中，即使删除行后，再次插入的数据 ID 也会按照我们理想中的状态去正常递增。sqlite> select * from sqlite_sequence; name seq -------- --- my_table 5查看表信息查看所有表使用 .tables 命令可以显示当前数据库中所有表sqlite> .tables users查看表结构使用 .schema 命令可以查看表的完整信息。sqlite> .schema users CREATE TABLE users( id integer primary key, name text not null, age integer default 0, sex integer not null default 0, unique(name)); sqlite>修改表名称语法：alert table table_name rename to new_table_name花括号表示在列表中必须选一个做为关键字alter table test2 rename to test2021;删除表DROP TABLE table_name;截断表SQLite 中并没有 TRUNCATE TABLE 命令，但是可以使用 DELETE 语句从数据表中删除全部数据。当 DELETE 中省略 WHERE 条件或者 RETURNING 子句并且要删除的表中没有触发器时，SQLite 会使用类似于截断的方式来删除所有数据，无需获取目标表中的数据用来判断是否符合删除条件，从而提升删除效率。DELETE 命令的基本语法如下：DELETE FROM table_name;但这种方式不会将自增数归零，如果要将自增数归零，则需要在删除数据后再删掉自增:DELETE FROM sqlite_sequence WHERE name = 'table_name';添加字段语法如下alert table table_name add column column_defsqlite> alter table contacts ...> add column email text not null default '' collate nocase;删除或修改字段遗憾的是 SQLite 的 ALTER TABLE 语句仅支持添加字段表、重命名，但我们仍然可以通过其他方法来实现删除字段或修改字段，步骤如下：将现有表名改为其他表名ALTER TABLE "test" RENAME TO "temp_test";创建新表CREATE TABLE "test" ("Id" INTEGER PRIMARY KEY AUTOINCREMENT, "Name" Text);将临时表数据导入新表INSERT INTO "test" ("Id", "Name") SELECT "Id", "Title" FROM "temp_test";修改表自增长值由于在 Sqlite 中使用自增长字段,引擎会自动产生一个 sqlite_sequence 表,用于记录每个表的自增长字段的已使用的最大值，所以要一起更新下。如果有没有设置自增长，则跳过此步骤。UPDATE "sqlite_sequence" SET seq = 3 WHERE name = 'test';删除掉临时表DROP TABLE temp_test;数据操作数据操作部分与 MySQL 差别不大插入数据insert into table_name (field1,field2...) values(value1,value2,value3);如果插入所有的值，则可以忽略字段列表。插入多条可以插入多条，使用逗号分隔。insert into table_name (field1,field2...) values(value1,value2,value3),(value1,value2,value3);也可以将查询结果使用 insert 语句插入，只要字段对齐即可insert into table_name (field1,field2) select field1,field2 from table_name;修改数据update table_name set update_list where predicate;删除数据delete from table_name where predicate;:::alert-warning需要注意的是，如果有自增 id，那么删除数据不会重置自增值，需要手动重置，参考 "截断表" 章节。:::查询数据设计数据库与数据表的最终目的是使用数据，数据操作语句成为 DML，DML 的核心命令是 SELECT 命令，它也是查询数据库的唯一命令。Select 命令从语法上说，SELECT 命令用一系列子句将很多关系操作组合在一起，每个子句代表一种特定的关系操作。SELECT 查询语法如下：SELECT [distinct] heading FROM tables WHERE predicate GROUP BY columns HAVING predicate ORDER BY columns LIMIT COUNT,OFFSET除 SELECT 外所有的子句都是可选的。算术运算符运算符作用+加-减*乘/除%取模比较运算符运算符作用==、=相等!=、<>不相等<、>大于、小于<=、>=大于等于、小于等于!<、!>不大于、不小于逻辑运算符运算符作用AND并且，拼接多个 WHERE 条件BETWEEN范围查找EXISTS子查询，返回布尔值IN在一个给定的列表中查询NOT IN查询不在该列表中的数据LIKE模糊查找, % 表示零个、一个、多个 数字或字符 _ 表示一个数字或字符BLOB大小写敏感的模糊查询 * 表示零个、一个、多个 数字或字符 ? 表示一个数字或字符NOT取反OR或IS NULL比较值是否是 NULLIS等于IS NOT不等于` `拼接字符串UNIQUE搜索指定表中的每一行，确保唯一位运算符运算符作用&二进制 AND 运算符``二进制或运算符~补码运算符，0变1，1变0<<左移运算符>>右移运算符LIKE 与 GLOBLike 运算符用于模糊匹配符合规则的文本值，如果值与表达式匹配则返回 1 否则返回 0，Glob 运算符与 Like 效果类似，区别在于 Glob 是大小写敏感的，且通配符略有不同。LikeGlob说明%*代表零个、一个或多个数字或字符_?代表一个单一的数字或字符使用示例Like 语句Glob 语句描述WHERE SALARY LIKE '200%'WHERE SALARY GLOB '200*'查找以 200 开头的任意值WHERE SALARY LIKE '%200%'WHERE SALARY GLOB '*200*'查找任意位置包含 200 的任意值WHERE SALARY LIKE '_00%'WHERE SALARY GLOB '?00*'查找第二位和第三位为 00 的任意值WHERE SALARY LIKE '2__%'WHERE SALARY GLOB '2??'查找以 2 开头，且长度至少为 3 个字符的任意值WHERE SALARY LIKE '%2'WHERE SALARY GLOB '*2'查找以 2 结尾的任意值WHERE SALARY LIKE '_2%3'WHERE SALARY GLOB '?2*3'查找第二位为 2，且以 3 结尾的任意值WHERE SALARY LIKE '2___3'WHERE SALARY GLOB '2???3'查找长度为 5 位数，且以 2 开头以 3 结尾的任意值大小写敏感示例sqlite> select 'Abcd' LIKE 'a%'; 'Abcd' LIKE 'a%' ---------------- 1 sqlite> select 'Abcd' Glob 'a?'; 'Abcd' Glob 'a?' ---------------- 0 sqlite> select 'Abcd' Glob 'A?'; 'Abcd' Glob 'A?' ---------------- 0排序和限制使用 ORDER BY 语句，可以对结果进行排序，ASC 是默认的升序，DESC 降序，多个字段逗号分隔。SELECT * FROM bank ORDER BY number DESC, id asc使用 limit 和 offset 可以限制结果集的范围：limit 返回条数offset 指定偏移的记录数，越过多少条例如下面的命令就是返回 bank 表中 id 倒数第三大的记录：SELECT * FROM bank ORDER BY id DESC LIMIT 1 OFFSET 2也可以使用下面的方式省略 offset，与上面语句等同：SELECT * FROM bank ORDER BY id DESC LIMIT 2,1::: alert-wlimit 和 offset 不是 ANSI 中的标准 SQL 关键字。不同数据库中语法会有所不同。offset 总是依赖于 limit，可以只用 limit 不带 offset，但反过来却不行。:::聚合查询聚合函数是一类特殊的函数，它从一组记录中计算聚合值。标准的聚合函数包括 sum()、avg()、count()、min()和 max()比如获取工商银行的数量：SELECT count(*) FROM bank WHERE bankTypeName = '工商银行'分组聚合的主要部分就是分组，不只是能计算整个结果集的聚合值，也可以将结果集分成多个组，然后计算每个组的聚合值。分组需要使用到 GROUP BY 子句，下面就获取每个银行的网点数量为例：SELECT count(*),bankTypeName FROM bank GROUP BY bankTypeNameHAVING如果我们想在上面 SQL 的基础上只显示 网点数量大于 100 家的银行，可以使用 HAVING 子句，用法与 WHERE 相同 ，唯一的区别是 HAVING 是针对 聚合值进行过滤：SELECT count(*),bankTypeName FROM bank GROUP BY bankTypeName HAVING count(*) > 100去除重复值distinct 处理 SELECT 的结果并过滤掉其中重复的行。例如 想要获取所有不同的银行：SELECT DISTINCT bankTypeName FROM "bank"多表连接多表连接是关系型数据块工作的关键，join 是 select 命令的第一个操作，将 join 的结果作为输入，供后续 WHERE 条件用于过滤。交叉连接如果两个表没有通过任何方式关联，SELECT 会产生一种更基础的表连接，也成为交叉连接、笛卡尔积或者交叉乘积。笛卡尔积是最基本的运算关系，他说强制的几乎无意义的连接方式，也就是将 table1 中所有行与 table2 中所有行关联起来。隐式交叉连接语法：SELECT <columns...> FROM table1,table2 [WHERE...] 以查出电影上映年份和电影简介为例：sqlite> SELECT film.release_year,film.title,film_text.description FROM film,film_text WHERE film.film_id = film_text.film_id limit 10; +--------------+------------------+-----------------------------------------------------------------------------------------------------------------------+ | release_year | title | description | +--------------+------------------+-----------------------------------------------------------------------------------------------------------------------+ | 2006 | ACADEMY DINOSAUR | A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies | | 2006 | ACE GOLDFINGER | A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China | | 2006 | ADAPTATION HOLES | A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory | | 2006 | AFFAIR PREJUDICE | A Fanciful Documentary of a Frisbee And a Lumberjack who must Chase a Monkey in A Shark Tank | | 2006 | AFRICAN EGG | A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico | | 2006 | AGENT TRUMAN | A Intrepid Panorama of a Robot And a Boy who must Escape a Sumo Wrestler in Ancient China | | 2006 | AIRPLANE SIERRA | A Touching Saga of a Hunter And a Butler who must Discover a Butler in A Jet Boat | | 2006 | AIRPORT POLLOCK | A Epic Tale of a Moose And a Girl who must Confront a Monkey in Ancient India | | 2006 | ALABAMA DEVIL | A Thoughtful Panorama of a Database Administrator And a Mad Scientist who must Outgun a Mad Scientist in A Jet Boat | | 2006 | ALADDIN CALENDAR | A Action-Packed Tale of a Man And a Lumberjack who must Reach a Feminist in Ancient China | +--------------+------------------+-----------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.10 sec)显式交叉连接sqlite> SELECT film.release_year,film.title,film_text.description FROM film -> CROSS JOIN film_text ON film.film_id = film_text.film_id -> limit 10; +--------------+------------------+-----------------------------------------------------------------------------------------------------------------------+ | release_year | title | description | +--------------+------------------+-----------------------------------------------------------------------------------------------------------------------+ | 2006 | ACADEMY DINOSAUR | A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies | | 2006 | ACE GOLDFINGER | A Astounding Epistle of a Database Administrator And a Explorer who must Find a Car in Ancient China | | 2006 | ADAPTATION HOLES | A Astounding Reflection of a Lumberjack And a Car who must Sink a Lumberjack in A Baloon Factory | | 2006 | AFFAIR PREJUDICE | A Fanciful Documentary of a Frisbee And a Lumberjack who must Chase a Monkey in A Shark Tank | | 2006 | AFRICAN EGG | A Fast-Paced Documentary of a Pastry Chef And a Dentist who must Pursue a Forensic Psychologist in The Gulf of Mexico | | 2006 | AGENT TRUMAN | A Intrepid Panorama of a Robot And a Boy who must Escape a Sumo Wrestler in Ancient China | | 2006 | AIRPLANE SIERRA | A Touching Saga of a Hunter And a Butler who must Discover a Butler in A Jet Boat | | 2006 | AIRPORT POLLOCK | A Epic Tale of a Moose And a Girl who must Confront a Monkey in Ancient India | | 2006 | ALABAMA DEVIL | A Thoughtful Panorama of a Database Administrator And a Mad Scientist who must Outgun a Mad Scientist in A Jet Boat | | 2006 | ALADDIN CALENDAR | A Action-Packed Tale of a Man And a Lumberjack who must Reach a Feminist in Ancient China | +--------------+------------------+-----------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.09 sec)内连接内连接就是两张表都匹配时候，才会返回SELECT ... FROM table1 [INNER] JOIN table2 ON conditional_expression ...外连接外连接是内连接的扩展，虽然SQL定义了三种（左连接、右连接、全连接） 但SQLite 只支持左连接。SELECT ... FROM table1 LEFT OUTER JOIN table2 ON conditional_expression ...子查询子查询可以用来嵌套SQL语句，在表达式不适用的地方使用，特别是结合 in 查询的用途：SELECT * FROM actor WHERE actor_id IN(SELECT actor_id FROM film_actor WHERE film_id = 1);复合查询复合查询与子查询相反，它是使用三种特殊关系操作符（联合、交叉连接和差集）处理多个查询的结果，在 SQLite 中对应使用的关键字 union、intersect 和 except。复合查询有如下要求：关联结果集字段数量必须相同。只能有一个 order by 结果集，并且在符合查询最末尾，对联合结果进行排序。union 合并结果集使用 union 可以合并多个结果集，并且在去重后返回，使用方式如下：例 users 表数据如下：sqlite> select * from users; id name age -- -------- --- 1 zhangsan 18 2 lisi 20 3 wangwu 28需要查询出年龄最大和最小的用户，并用使用 union 将两个结果集合并，使用方式如下：sqlite> select * from (select name,age from users order by age limit 1) union select * from (select name,age from users order by age desc limit 1); name age -------- --- wangwu 28 zhangsan 18union all 合并结果集union all 运算符用法与 union 类似，但不会对结果集进行去重，效果如下：sqlite> select * from users union select * from users; id name age -- -------- --- 1 zhangsan 18 2 lisi 20 3 wangwu 28 sqlite> select * from users union all select * from users; id name age -- -------- --- 1 zhangsan 18 2 lisi 20 3 wangwu 28 1 zhangsan 18 2 lisi 20 3 wangwu 28intersect 合并取交集intersect 运算符的作用是取两个结果集的交集，效果如下：sqlite> select * from users where id != 3; id name age -- -------- --- 1 zhangsan 18 2 lisi 20 sqlite> select * from users where id != 1; id name age -- ------ --- 2 lisi 20 3 wangwu 28 sqlite> select * from users where id != 3 intersect select * from users where id != 1; id name age -- ---- --- 2 lisi 20 sqlite>except 合并取差集except 运算符的作用是找出在第一个结果集中存在，在第二个结果集中不存在的数据，效果如下：sqlite> select * from users where id != 3 except select * from users where id != 1; id name age -- -------- --- 1 zhangsan 18case 条件case 表达式可以根据条件不配不同的结果并返回，它的用法与 MySQL 类似有两种用法：第一种用法，使用 case 表达式处理解析不同的字段值，并返回对应的字符串 效果如下：sqlite> select * from users; id name age sex -- -------- --- --- 1 zhangsan 18 1 2 lisi 20 2 3 wangwu 28 0 sqlite> select name || case sex when 1 then ' is Male' when 2 then ' is Female' else ' is unknown' end as 'User Sex' from users; User Sex ----------------- zhangsan is Male lisi is Female wangwu is unknown第二种用法，将表达式写在 when 中：sqlite> select name || case when sex = 1 then ' is Male' when sex = 2 then ' is Female' else ' is unknown' end as 'User Sex' from users; User Sex ----------------- zhangsan is Male lisi is Female wangwu is unknown处理 Null在大多数关系型数据库中，null 都表示未知的概念，null 是缺失信息的占位符，本身并不是值。null 表示该位置没有值。下面是 sqlite 中 null 的三条规则：为了表在逻辑表达式中使用 null，效果如下表达式结果Null AND TrueNullNull OR TrueTrueNull AND FalseFalseNull OR FalseNullNull AND NULLNULLNull OR NULLNULL通过 is null 或者 is not null 操作符来判断值是否是 null。记住 Null 不等于任何值，包括 Null，不能将 Null 用于比较运算，这样写通常不会返回任何数据。视图视图也称派生表，因为它们的内容派生自其他表的查询结果。虽然视图看起来感觉就像基本表一样，但它们不是基本表。因为基本表的内容是持久化的，而视图表的内容是根据基础表动态生成的。创建视图视图表的创建语法如下：create view name as select-stmt视图的名称通过 name 指定，其定义由 select-stmt 定义，最终是视图看起来就像名为 name 的基本表一样。下面是基于 users 表创建 users_view 视图的示例,通过视图将字段名称、字段值进行转换，从而简化查询过程。sqlite> select * from users; id name age sex -- --------- --- --- 1 zhangsan 18 1 3 zhangsan3 18 1 4 zhangsan4 18 1 sqlite> sqlite> create view users_view as ...> select name as '姓名',age as '年龄', ...> case sex when 1 then '男' when 2 then '女' else '未知' end as '性别' ...> from users; sqlite> sqlite> select * from users_view; 姓名 年龄 性别 --------- -- -- zhangsan 18 男 zhangsan3 18 男 zhangsan4 18 男删除视图视图删除通过 drop view 命令实现。sqlite> drop view users_view;索引索引可以用来加速查询，与其他数据库类似，SQLite 使用 B-Tree（B树）做索引。需要注意的是索引也会增加数据库的大小，从字面意义上理解，索引就是将字段值多复制了一份，如果所有字段都创建索引，表的大小可能会翻倍。另外也需要考虑索引的维护，进行 Insert、Update、Delete 操作时除了修改表，对应的索引也必须修改，虽然索引可以加快查询速度，但它们会降低 Insert、Update、Delete 的速度。创建索引创建索引命令如下：create [unique] index index_name on table_name (columns)index_name: 索引名称table_name: 索引所在的表名称columns: 索引字段，若多个字段使用逗号分隔unique: 唯一索引标识创建一个名为 idx_name_age 由 users 表 name 和 age 字段组成的唯一索引。create unique index idx_name_age on users(name,age);删除索引使用 drop index index_name 命令:sqlite> drop index idx_name_age;使用索引SQLite 的索引使用规则与 MySQL 一致，遵守最左原则。参考资料SQLite官方文档：https://sqlite.org/docs.htmlSQLite权威指南（第二版) https://book.douban.com/subject/7061934/SQLite 教程（菜鸟教程）https://www.runoob.com/sqlite/sqlite-tutorial.html
因为 SQLite 是无服务数据库，所以每次操作数据都需要消耗磁盘 IO。而想要快速插入数据，本质上就是想要考虑如何减少磁盘 IO 次数。减少 io 有很多种方法，比如 批量插入、使用事务、内存模式等，这里的优化方案选择批量插入和事务这次测试使用的是一个15W 条数据的 CSV文件，将读入的数据写入数据库。需要注意的是，本文只是优化方案的记录，并非严格的性能测试，所以这些性能结果在不同设备上会有所差异，不过这不重要。单条插入我们先测试一下单条插入的性能：因单条插入性能实在太低，我们只测试一分钟的插入条数。$list=file('bank.csv',FILE_IGNORE_NEW_LINES); foreach ($list as $val){ $temp = explode(',',$val); $data[]= [ 'number' => (int) $temp[1], 'bankTypeName' => $temp[2], 'bankName' => $temp[3], ]; } $stime=microtime(true); foreach ($data as $item){ Db::table('bank_test')->insert($item); // 执行 60 秒退出 if((microtime(true) - $stime) > 60) { exit; } }经过测试一分钟插入数据 665 条，因为每次写入都是一次磁盘操作，所以性能非常低。批量插入$list=file('bank.csv',FILE_IGNORE_NEW_LINES); foreach ($list as $val){ $temp = explode(',',$val); $data[]= [ 'number' => (int) $temp[1], 'bankTypeName' => $temp[2], 'bankName' => $temp[3], ]; } $stime=microtime(true); $data_chunk = array_chunk($data,300); foreach ($data_chunk as $item) { Db::table('bank_test')->insertAll($item); } $etime=microtime(true);//获取程序执行结束的时间 $total=$etime-$stime; //计算差值 // 指令输出 $output->writeln("插入:".count($data)."数据，耗时：{$total}");结果：插入 150792 数据，耗时 64.941246032715 秒可以看出来，即使只用上了批量插入，性能的提升也是非常明显的。需要注意的是，单次批量插入条数有限制，有的文章说单次不能超过 999 条，但我测试不能超过 300 条。使用事务使用事务提升插入性能是 SQLite 的一个特性，原理就是将先将插入数据缓存到内存，最后提交事务的时候将数据写入磁盘，这样操作只会有一次磁盘IO。$list=file('bank.csv',FILE_IGNORE_NEW_LINES); foreach ($list as $val){ $temp = explode(',',$val); $data[]= [ 'number' => (int) $temp[1], 'bankTypeName' => $temp[2], 'bankName' => $temp[3], ]; } $stime=microtime(true); Db::startTrans(); foreach ($data as $item) { Db::table('bank_test')->insert($item); } Db::commit(); $etime=microtime(true);//获取程序执行结束的时间 $total=$etime-$stime; //计算差值 // 指令输出 $output->writeln("插入:".count($data)."数据，耗时：{$total}");结果：插入 150792 数据，耗时 62.787980079651 秒可以看到 使用事务之后，即使是单条插入，性能也是很高的。批量+事务那么我们是否可以将上面的 事务和批量插入结合在一起使用呢？$list=file('bank.csv',FILE_IGNORE_NEW_LINES); foreach ($list as $val){ $temp = explode(',',$val); $data[]= [ 'number' => (int) $temp[1], 'bankTypeName' => $temp[2], 'bankName' => $temp[3], ]; } $stime=microtime(true); // 多次测试发现 事务中批量插入 每次插入 100 条性能最好 $data_chunk = array_chunk($data,100); Db::startTrans(); foreach ($data_chunk as $item) { Db::table('bank_test')->insertAll($item); } Db::commit(); $etime=microtime(true);//获取程序执行结束的时间 $total=$etime-$stime; //计算差值 // 指令输出 $output->writeln("插入:".count($data)."数据，耗时：{$total}");结果：插入 150792 数据，耗时 5.9146549701691 秒而我使用 MySQL 5.7 批量插入 每次1000 条，都需要耗时 14 秒。所以我认为 SQLite 插入 15W 条数据，性能能在 5、6 秒，已经是非常可观了。写同步可以通过关闭写同步来进一步的提升性能，但这样设置可能会在断电、崩溃等情况造成数据丢失，需要谨慎选择。具体可参考官方文档：https://www.sqlite.org/pragma.html#pragma_synchronousDb::query('PRAGMA synchronous = OFF');在开启事务前关闭写同步，既事务+批量+关闭写同步，插入15w 数据 耗时：5.1300809383392 秒。后记SQLite 还支持其他方式用来提升插入性能，比如 内存模式、执行准备等。但操作都比较复杂，如果只是想简单的提升写入性能，我认为 PHP 使用 上面的几种方式配合就可以了。