SQLite常用点滴总结(转)

2023-01-05 15:19:25

这部分有别于其它部分。本文档的其它大多数部分都在讲特定的 SQL
命令。本部分所讲的不是一个单一的命令，而是“表达式”，它经常用作其它命令的一部分来使用。

SQLite
有如下二元运算符，根据其优先级从高到低有：

||
    *    /    %
    +    -

<<   >>   &    |
    <    <=   >    >=

=    ==   !=   <>   IN
    AND

OR

以下是支持的一元运算符：

- + ! ~ NOT

COLLATE
运算符可以被看作是一个后置的一元运算符它具有最高的优先级。通常，与其前面的一元、及二元运算符相比，它与参数结合更为紧密。

一元 [Operator
+] 什么也不做。它可以被应用于字符串，数字，或BLOB，并永远返回跟它作用的数值相同的结果。

注意，两种变体的等号运算符是不一样的。等可以是 =
或 ==。不等操作符有 != 或 <>。 || 运算符为“连接符”，它将两个字符串连接到一起。 %
输出其左边的数除以右面数后的余数。

除 || 之外，任何二元操作符的结果都是一个数值型的值。 ||
返回两个操作数连接后的大字符串。

字面值是一个整数或浮点数的值。也支持科学记数法。小数点永远使用“.” 字符来表示，即使本地设置指定用“,”
来表示也不例外。在这种规则下，使用“,” 作小数点会引起语义上的二义性。字符串值应该用单引号（'）引起来。像在 Pascl
语言中那样，字符串中的单引号应该使用两个单引号表示。由于像 C 语言那样使用反斜线进行转义的方式不是 SQL 语言的标准，所以不支持那种类型的转义。
BLOB字面值是以一个“x” 或“X” 打头的包含十六进制数据的字符串值。例如：

X'53514C697465'

字面值也可以是 "NULL" 记号。

参数用于指定一个字面值在表达式中的存放位置。它将在运行时被使用
sqlite3_bind API 替换。参数有如下几种格式：

?NNN        问号后跟一个数字 NNN 用于存放第 NNN
个参数。 NNN 必须在 1 到 999 之间。
    ?        只有一个问号，它将用于存放紧跟其后的一个未用到的参数。

:AAAA        冒号后面跟一个标志符，用于存放名字为 AAAA
的变量。命名的变量也会被编号，编号将使用第一个未被使用的编号。为避免混淆，最好不要混用名字和编号作为参数。
    @AAAA        一个 @
符号等价于一个冒号。
    $AAAA        美元符号后跟一个标志符也会存放名字为 AAAA
的参数。在这种情况下，参数名字中可以出现很多“::”，并且，在“(...)”后缀中可以包含任何文本。该语法是由 TCL
语言中相同的法而来的。

未使用 sqlite3_bind 来赋值的参数则认为是 NULL。

LIKE
操作符会作一个模式匹配比较。它右边是一个匹配模式，左边包含被匹配的字符串。在匹配模式中，百分号 % 会匹配字符串中任意 0 个或多个字符。一个下划线 _
符号仅匹配一个任意的字符。除此之外，其它的任何字符均只匹配它们自己（在不区分大小写的环境下可能会匹配与之对应的大、小写字母）。（一个BUG：SQLite中不区分大小写仅对
7 比特的拉丁字符有效，对于 8 位的 iso8859 字符集UTF8字符则是区分大小写的）。例如：表达式 'a' LIKE 'A' 是 TRUE 但 'æ'
LIKE 'Æ' 则是 FALSE)。

若有可选的 ESCAPE 子句，那么 ESCAPE
关键字后的表达式必须是一个单个的字符（叫做转义字符）。该字符可以用于 LIKE
模式字符串中来体现百分号或下划线。转义字符后面的百分号或下划线均分别保持它们原来的意思。中缀的 LIKE 操作符是使用 like(X,Y)
函数实现的。
LIKE 操作符不区分大小写，它将匹配一边是小写而另一边是大写的字符串。（一个BUG：SQLite中不区分大小写仅对 7
比特的拉丁字符有效，对于 8 位的 iso8859 字符集UTF8字符则是区分大小写的）。例如：表达式 'a' LIKE 'A' 是 TRUE 但 'æ'
LIKE 'Æ' 则是 FALSE)。

中缀操作符 LIKE 是通过调用用户函数 like(X,Y) 实现的。但若后面还有 ESCAPE
子句，它会在以上函数中增加第三个参数。如果该函数被其它的 like() SQL 函数重载，则需要注意。

GLOB 操作符与 LIKE
类似，但它使用 Unix 通配符的的文件匹配语法。并且，与 LIKE 不同， GLOB 是大小写敏感的。GLOB 和 LIKE 都可以用 NOT
关键字对匹配结果取反。中缀的 GLOB 操作符是通过调用用户函数 glob(X,Y) 实现的，可以通过该函数对其进行重载。

REGEXP
操作符是使用 regexp() 用户函数的一个特殊语法。默认情况下， regexp() 用户函数没有定义，所以使用 REGEXP
将会出错。如果在运行时增加一个名为 regexp 的用户定义函数，则使用该操作符时将使用此函数来实现 REGEXP 功能。

MATCH
操作符是使用 match() 用户函数的一个特殊语法。默认的 match()
函数实现只会引发一个异常，从而没多大用处。但可以通过扩展该函数来实现更有用的逻辑。

列名colum name 可以是任何在 CREATE
TABLE 语句中指定的列名，也可以是如下一个特殊标志符： "ROWID"、"OID" 或
"_ROWID_"。这些特殊标志符均描述与每个表每一行所关连的唯一的整数键。特殊标志符只会在 CREATE TABLE
语句没有指定相同的列名时才会真正指代每一行。行键值就像只读的列。一个行键值可以用在任何一个正常列所能使用的地方。只是，不能使用 UPDATE 或 INSERT
来改变行的键值。 “SELECT * ...”也不会返回行键值。

SELECT 语句可以出现在 IN
操作符的后面，或作为一个单独的数量值出现，或者跟在 EXISTS 操作符后面。当作为一个单独数量值或在 IN 操作符中时，SELECT 必须只能返回单列。复合
SELECT 查询（使用 UNION 或 EXCEPT 关键字的查询）也是允许的。当使用 EXISTS 关键字时，SELECT 结果中的列被忽略。如果
SELECT 返回了一行或多行，则表达式就为 TRUE，否同就为 FALSE。如果内层 SELECT 表达式中的项目与外层的值无关，则内层 SELECT
会最先求值，并在以后需要时重用该值。如果内层 SELECT 中含有外层查询相关的变量，那么内层 SELECT
会在每次需要的时候被重新求值。

当一个 SELECT 作为 IN 操作符的右操作数时，如果 IN 左边的操作数存在于右边 SELECT
的查询结果中， IN 就返回 TRUE。 IN 操作符也可以搭配 NOT 来对表达式结果取反。

当一个 SELECT 出现在除 IN
之外的其它表达式中时， SELECT 结果的第一行将作为一个单一的值用于该表达式中。如果该SELECT 返回多行，那么其它的行将会被忽略。若 SELECT
返回 0 行，则其结果将是 NULL。

一个 CAST 表达式会将的数据类型转换为指定的类型
<type>。其中<type> 可以是任何在 CREATE TABLE
语句中有效的非空的类型名。

系统支持简单和聚集函数。一个简单函数可以用于任何表达式。简单函数会在其输入的基础上立即返回结果。聚集函数只能用于
SELECT 语句中。聚集函数会在返回的结果集上进行跨行计算，并返回相应的结果。
核心函数 Core
Functions

下列是默认可以的核心函数。额外的函数可以使用 C 语言编写并使用 sqlite3_create_function() API
加入数据库引擎中。
abs(X)     返回参数 X的绝对值。
coalesce(X,Y,...)     返回参数中第一个非 NULL
的值。若所有参数均为 NULL，则返回 NULL。该函数至少需要两个参数。
glob(X,Y)     该函数用户实现 SQLite 的 "X GLOB
Y" 语法。可以使用 sqlite3_create_function() 接口来重载该函数，从而改变 GLOB 操作符的行为方式。
ifnull(X,Y)
    返回第一个非 NULL 的参数值，如果所有参数都是 NULL，则返回 NULL。与上面的 coalesce() 一样。
hex(X)
参数以 BLOB 对待。结果是 BLOB 内容的 16 进制表示。
last_insert_rowid()     返回当前数据库连接中最后插入的一行的
ROWID。该值与使用 sqlite_last_insert_rowid() API 函数返回的值相同。
length(X)     返回 X
字符串的长度。如果 SQLite 配置为支持 UTF-8，则返回的是 UTF-8
字符的长度，而不是字节数。
like(X,Y)
like(X,Y,Z)     该函数用于实现 SQL 的 "X LIKE Y [ESCAPE
Z]" 语法。如果有可选的 ESCAPE 语句，将使用具有三个参数的函数，否则，将使用只有两个参数的数据。可以使用
sqlite_create_function() 接口来重载该函数，以改变 LIKE
操作符的行为。如果那样做，一定要注意同时重载两个（两个参数的和三个参数的）版本的函数。否则，依据是否使用了 ESCAPE
子句，可以会调用不同的代码。
load_extension(X)
load_extension(X,Y)     装入 SQLite
共享库之外文件名为 X 而入口点为 Y 的扩展库。结果将是 NULL。如果省略了 Y，那么，将使用默认的入口点
sqlite3_extension_init。该函数在装入或初始化失败时会引发一个异常。

如果扩展试图修改或删除一个 SQL
函数或对照序列，则该函数会失败。可以使用扩展增加新函数或对照序列，但不能修改或删除已存在的。这是因为那些函数和/或对照序列可能正在被其它的SQL语句使用。要想装入可以修改或删除函数或对照序列的扩展，使用
sqlite3_load_extension() C 语言API。
lower(X)     返回将字符串 X 转换为小写后的字符串。该函数使用 C
语言库函数 tolower() 进行转换，所以，可能不能正确转换 UTF-8 字符。
ltrim(X)
ltrim(X,Y)
返回一个字符串，它是从字符串 X 的左边删除了任何存在于字符串 Y 中的字符后剩余的字符串。如果省略参数 Y
，则会删除左边的空格。
max(X,Y,...)     返回参数的最大值。除数字外，参数可能是字符串。它使用通常的排序顺序来决定最大值。注意若
max() 有两个或多个参数，则它是一个简单函数。但如果只提供一个参数，它将变成一个聚集函数。
min(X,Y,...)
返回参数的最小值。除数字外，参数可能是字符串。它使用通常的排序顺序来决定最小值。注意若 min()
有两个或多个参数，则它是一个简单函数。但如果只提供一个参数，它将变成一个聚集函数。
nullif(X,Y)
如果参数不同，则返回第一个参数，否则返回NULL。
quote(X)     该函数返回一个对其参数值进行处理后适合包含在其它 SQL
语句中的字符串。单引号包括的字符串将会按需要转换成内部引用的格式。 BLOB 将会编码为十六进制字面值。当前实现的 VACUUM 使用该函数。在写触发器来实现
“撤消/重做” 功能时，该函数也是很有用的。
random(*)     返回一个伪随机数。结果在 -9223372036854775808 与
+9223372036854775807之间。
replace(X,Y,Z)     返回一个将 X 字符串中每一个出现 Y 的位置替换为 Z
后的字符串。它使用二进制对照序列进行比较。
randomblob(N)     返回一个 N 字节长的包含伪随机字节的 BLOG。 N
应该是正整数。
round(X)
round(X,Y)     对 X 圆整成小数点后 Y 位。或省略 Y ，则默认Y 为
0。
rtrim(X)
rtrim(X,Y)     返回从 X 的右边去除所有出现在 Y 中字符以后的字符串。如果省略
Y，则去除空格。
soundex(X)     计算字符串 X的读音编码。如果参数为 NULL，则返回 "?000"。默认情况下 SQLite
忽略该函数。仅当在编译时指定 -DSQLITE_SOUNDEX=1 时才有效。
sqlite_version(*)     返回当前运行的 SQLite
库的版本号。如："2.8.0"
substr(X,Y,Z)
substr(X,Y)     返回字符串 X 从第 Y 个字符开始，长度为 Z
的字符串。如果省略 Z，将返回直到字符串结尾的字符串。 X 的第一个字符从 1 开始。如果 Y 是负数，则从右边开始数。如果 X 是 UTF-8
字符串，那么，下标值将指实际的 UTF-8 characters 字符，而不是字节。如果 X 是一个
BLOB，那么下标就是指字节。
trim(X)
trim(X,Y)     返回从字符串 X 的两头去掉所有存在于 Y 中字符后的字符串。如果省略
Y，则去空格。
typeof(X)     返回表达式 X 的类型。只可能是 "null", "integer", "real", "text", 以及
"blob"。 SQLite 的类型处理在 Datatypes in SQLite Version 3 中有说明。
upper(X)     返回字符串
X 被转换为大写后的字符串。它使用 C 库函数 toupper() 实现，对于 UTF-8，某些字符串可能不能正确转换。
zeroblob(N)
返回一个 N 字节长、全部由 0x00 组成的 BLOB。 SQLite 或以很有效的组织这些 zeroblob。它可以被用于为以后的使用预留空间。以后可以使用
incremental BLOB I/O 来写入 BLOB 数据。
日期和时间函数

日期和时间函数在 SQLite
Wiki上。
聚集函数

有下列默认可和的聚集函数：可以使用 sqlite3_create_function() API
来增加其它的聚集函数。

任何聚集函数都只有一个参数。其参数前可以有一个 DISTINCT。如果有，重复的元素将会在传递给聚集函数前过滤掉。如，函数
"count(distinct X)" 将只返回在 X 列上不重复的行的总数，而不是在该列上所有非空的行的总数
avg(X)     返回 X
列的一组中所有非空值的平均值。字符串或 BLOB 等非数字值将被认为是 0。即使所有输入都是整数， avg()
的结果也永远是浮点数。
count(X)
count(*)     第一种形式返回在
X列上的一组中非空的行的总数。第二种（无任何参数）返回一组中所有的行数。
group_concat(X)
group_concat(X,Y)
结果是一个所有非空的 X 连接起来的一个字符串。如果有参数 Y，则它会做为连接 X 时的分隔符。若省略Y ，则默认是逗号（,）。
max(X)
返回一组中的最大值。使用通常的排序顺序来确定最大值。
min(X)
返回一组中除非空值以外的最小值。使用通常的排序顺序来确实最小值。只有当整个组中所有值均为 NULL 时才会返回
NULL。
sum(X)
total(X)     返回一组中所有非空值的数值总和。如果在输入中没有非空值，则 sum() 会返回 NULL 而
total() 会返回 0.0。不仅能在 sum() 没有对任何行求和时， NULL能给出有帮助的结果，而且 SQL 标准也需要它。而且其它的 SQL
数据库引擎也是这么实现的。 SQLite 这么做也是为了保持兼容性。我们也提供了一个非标准的 total() 函数，以提供一个方便的途径来绕过 SQL
语言的这一设计问题。

total() 的结果永远是浮点数。如果所有非空的输入都是整数， sum() 的结果将是整数值。若任何输入给 sum()
的值是除整数及 NULL 以外的值， sum() 都将返回浮点数。这可能是最接近标准 sum() 的实现方式吧。

如果输入全部是整数或
NULL，在结果溢出时 sum() 将会产生一个 "integer overflow" 异常。而 total() 永远不会。

限制笔数
select * from film limit 10;
sqlite3 film.db "select * from
film;"

输出 HTML 表格：

sqlite3 -html film.db "select * from
film;"

将数据库「倒出来」：

sqlite3 film.db ".dump" >
output.sql

利用输出的资料，建立一个一模一样的数据库（加上以上指令，就是标准的SQL数据库备份了）：

sqlite3
film.db <
output.sql

在大量插入资料时，你可能会需要先打这个指令：

begin;

插入完资料后要记得打这个指令，资料才会写进数据库中：

commit;

SQLite内建函数表
算术函数
abs(X)
返回给定数字表达式的绝对值。
max(X,Y[,...])    返回表达式的最大值。
min(X,Y[,...])
返回表达式的最小值。
random(*)    返回随机数。
round(X[,Y])
返回数字表达式并四舍五入为指定的长度或精度。
字符处理函数
length(X)    返回给定字符串表达式的字符个数。
lower(X)
将大写字符数据转换为小写字符数据后返回字符表达式。
upper(X)
返回将小写字符数据转换为大写的字符表达式。
substr(X,Y,Z)    返回表达式的一部分。
randstr()

quote(A)
like(A,B) 确定给定的字符串是否与指定的模式匹配。
glob(A,B)

条件判断函数
coalesce(X,Y[,...])
ifnull(X,Y)
nullif(X,Y)

集合函数
avg(X)    返回组中值的平均值。
count(X)    返回组中项目的数量。
max(X)
返回组中值的最大值。
min(X)    返回组中值的最小值。
sum(X)
返回表达式中所有值的和。
其他函数
typeof(X)    返回数据的类型。
last_insert_rowid()
返回最后插入的数据的ID。
sqlite_version(*)    返回SQLite的版本。
change_count()
返回受上一语句影响的行数。
last_statement_change_count()

码农公寓

相关文章