sqlite的一些东西(杂)

发布时间：2020-12-12 20:28:48 所属栏目：百科来源：网络整理

导读：http://laolang.xtmm.cn/?post=14472 abs(X)返回参数X的绝对值。 coalesce(X,Y,...)返回第一个非空参数的副本。若所有的参数均为NULL，返回NULL。至少2个参数。 glob(X,Y)用于实现SQLite的"XGLOBY"语法。可使用sqlite3_create_function()重载该函数从而改变G

算术函数

abs(X)

返回给定数字表达式的绝对值。

max(X,...])

返回表达式的最大值。

min(X,...])

返回表达式的最小值。

random(*)

返回随机数。

round(X[,Y])

返回数字表达式并四舍五入为指定的长度或精度。

字符处理函数

length(X)

返回给定字符串表达式的字符个数。

lower(X)

将大写字符数据转换为小写字符数据后返回字符表达式。

upper(X)

返回将小写字符数据转换为大写的字符表达式。

substr(X,Z)

返回表达式的一部分。

randstr()

quote(A)

like(A,B)

确定给定的字符串是否与指定的模式匹配。

glob(A,B)

条件判断函数

coalesce(X,...])

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()

SQLite自增ID自段
使用方法为 INTEGER PRIMARY KEY AUTOINCREMENT
如:

CREATETABLE21andy(idINTEGERPRIMARYKEYAUTOINCREMENT,21andyVARCHAR(100)NOTNULL,dateDATE);

注意是AUTOINCREMENT,和MySQL的写法不一样

SQLite清空表
SQLite没有TRUNCATE清空表命令,所以只能这样

DELETEFROM21andy;
SQLite 删除记录后,自增ID置0

只能这样

DELETEFROMsqlite_sequence;

另外,这个sqlite_sequence可以CRUD

SQLite 删除记录后,不会释放空间
必须像这样

VACUUM

SQLite 分页查询
有两种写法:
Select*FromAccountLimit9Offset10;
SELECT*FROMAccountLIMIT10,9他们两个的效果都是一样的,其中第一种写法比较清晰明了,即跳过10行,读取其后的9行数据.

SQLite批量插入数据
很不幸的事情是貌似SQLite只能一条一条的执行插入,但是这是非常非常慢的行为,执行一条就是执行一次写入磁盘的操作,这实在是太可怕了.在SQLite里面执行批量插入,只能将插入操作放入到事务当中去.示例如下:
BEGIN;
CREATETABLEt2(aINTEGER,bINTEGER,cVARCHAR(100));
INSERTINTOt2VALUES(1,59672,'fiftyninethousandsixhundredseventytwo');
INSERTINTOt2VALUES(24999,89569,'eightyninethousandfivehundredsixtynine');
INSERTINTOt2VALUES(25000,94666,'ninetyfourthousandsixhundredsixtysix');
COMMIT;在SQLite当中一系列要进行多次写入操作的时候,建议放入到事务当中去,这个优化的性能提升是可以很明显感觉到的.用与不用的差别是非常大的.

SQLite 数据不存在INSERT,存在UPDATE
SQLite的SQL语法类MySQL,在SQLite里面有一个关键字 REPLACE,可以使用它达到目的:
REPLACEINTO[table](row1,row2)VALUES(2,3);如果该表有一个主键,那么当主键值相等的时候,该行数据不存在执行插入,存在则执行更新操作.但是如果没有主键,那么它就执行的永远都是插入操作了.

SQLite 使用ISNULL函数
SQLite的SQL语法类不同于MySQL,不可以用ISNULL,但可以用 IFNULL 直接代替

ifnull(null,0)

SQLite做查询分页

用SQLite分页是世界上最简单的分页方法。SQLite 分页处理可通过. limit 和 offset 子句实现。例如我要取Account表中的第11-20行的数据，可以如下实现：

Select * From Account Limit 9 Offset 10

以上语句表示从Account表获取数据，跳过10行，取9行。

Selectcount(*) From Account

以上语句返回查询中的总记录数。

如何让sqlite支持浮点运算

sqlite> select 1.0/7;
0.142857142857143
sqlite> select 1/7;
0
sqlite>

格式化输出

.head on

.mode column

参考
1. http://www.21andy.com/blog/20090512/1272.html
2. http://i.cnsoftware.cn/space-3-do-thread-id-35.html

3. http://www.cnblogs.com/devsdk/archive/2009/04/03/1428615.html

4、http://jsjx.jsit.edu.cn/sxsoftservice/Article_Show.asp?ArticleID=666

5.http://laolang.xtmm.cn/?post=14472

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

http://laolang.xtmm.cn/?post=14472

abs(X)返回参数X的绝对值。
coalesce(X,Y,...)返回第一个非空参数的副本。若所有的参数均为NULL，返回NULL。至少2个参数。
glob(X,Y)用于实现SQLite的"XGLOBY"语法。可使用sqlite3_create_function()重载该函数从而改变GLOB运算符的功能。
ifnull(X,Y)返回第一个非空参数的副本。若两个参数均为NULL，返回NULL。与上面的coalesce()类似。
last_insert_rowid()返回当前数据库连接最后插入行的ROWID。sqlite_last_insert_rowid()API函数同样可用于得到该值。
length(X)返回X的长度，以字符计。如果SQLite被配置为支持UTF-8，则返回UTF-8字符数而不是字节数。
like(X,Y[,Z])用于实现SQL语法"XLIKEY[ESCAPEZ]".若使用可选的ESCAPE子句，则函数被赋予三个参数，否则只有两个。可使用sqlite3_create_function()重载该函数从而改变LIKE运算符的功能。注意同时重载like()的两参数和三参数版本，否则在使用/不使用ESCAPE子句时，LIKE运算符的实现可能使用的是不同的代码。
lower(X)返回X字符串的所有字符小写化版本。这一转换使用C语言库的tolower()函数，对UTF-8字符不能提供好的支持。
max(X,...)返回最大值。参数可以不仅仅为数字，可以为字符串。大小顺序由常用的排序法则决定。注意，max()在有2个或更多参数时为简单函数，但当仅给出一个参数时它变为聚集函数。
min(X,...)返回最小值。与max()类似。
nullif(X,Y)当两参数不同时返回X，否则返回NULL.
quote(X)返回参数的适于插入其它SQL语句中的值。字符串会被添加单引号，在内部的引号前会加入逃逸符号。BLOB被编码为十六进制文本。当前的VACUUM使用这一函数实现。在使用触发器实现撤销/重做功能时这一函数也很有用。
random(*)返回介于-2147483648和+2147483647之间的随机整数。
round(X)
round(X,Y)将X四舍五入，保留小数点后Y位。若忽略Y参数，则默认其为0。
soundex(X)计算字符串X的soundex编码。参数为NULL时返回字符串"?000".缺省的SQLite是不支持该函数的，当编译时选项-DSQLITE_SOUNDEX=1时该函数才可用。
sqlite_version(*)返回所运行的SQLite库的版本号字符串。如"2.8.0"。
substr(X,Z)返回输入字符串X中以第Y个字符开始，Z个字符长的子串。X最左端的字符序号为1。若Y为负，则从右至左数起。若SQLite配置支持UTF-8，则“字符”代表的是UTF-8字符而非字节。
typeof(X)返回表达式X的类型。返回值可能为"null","integer","real","text",以及"blob".SQLite的类型处理参见SQLite3的数据类型.
upper(X)返回X字符串的所有字符大写化版本。这一转换使用C语言库的toupper()函数，对UTF-8字符不能提供好的支持。

以下是缺省可用的聚集函数列表。可以使用C语言写出其它的聚集函数然后使用sqlite3_create_function()API函数添加到数据库引擎中。

在单参数聚集函数中，参数可以加前缀DISTINCT。这时重复参数会被过滤掉，然后才穿入到函数中。例如，函数"count(distinctX)"返回字段X的不重复非空值的个数，而不是字段X的全部非空值。

avg(X)返回一组中非空的X的平均值。非数字值作0处理。avg()的结果总是一个浮点数，即使所有的输入变量都是整数。

count(X)
count(*)返回一组中X是非空值的次数的第一种形式。第二种形式(不带参数)返回该组中的行数。
max(X)返回一组中的最大值。大小由常用排序法决定。
min(X)返回一组中最小的非空值。大小由常用排序法决定。仅在所有值为空时返回NULL。
sum(X)
total(X)返回一组中所有非空值的数字和。若没有非空行，sum()返回NULL而total()返回0.0.NULL通常情况下并不是对于“没有行”的和的一个有意义的结果，但SQL标准如此要求，且大部分其它SQL数据库引擎这样定义sum()，所以SQLite也如此定义以保证兼容。我们提供非标准的total()函数作为解决该SQL语言设计问题的一个简易方法。

total()的返回值式中为浮点数。sum()可以为整数，当所有非空输入均为整数时，和是精确的。若sum()的任意一个输入既非整数也非NULL或计算中产生整数类型的溢出时，sum()返回接近真和的浮点数。

SQLite的日期时间函数

1.date(日期时间字符串,修正符,……)
2.time(日期时间字符串,……)
3.datetime(日期时间字符串,……)
4.julianday(日期时间字符串,……)
5.strftime(日期时间格式,日期时间字符串,……)

上述五个函数需要一个日期时间字符串做参数，后面可以跟零到多个修正符参数。而strftime()函数还需要一个日期时间格式字符串做第一个参数。

　　1.date()函数返回一个以“YYYY-MM-DD”为格式的日期；
2.time()函数返回一个以“YYYY-MM-DDHH:MM:SS”为格式的日期时间；
3.julianday()函数返回一个天数，从格林威治时间公元前4714年11月24号开始算起；
4.strftime()函数返回一个经过格式话的日期时间，它可以用下面的符号对日期和时间进行格式化：
%d一月中的第几天01-31
%f小数形式的秒，SS.SSSS
%H小时00-24
%j一年中的第几天01-366
%JJulianDayNumbers
%m月份01-12
%M分钟00-59
%s从1970-01-01日开始计算的秒数
%S秒00-59
%w星期，0-6，0是星期天
%W一年中的第几周00-53
%Y年份0000-9999
%%%百分号

　　其他四个函数都可以用strftime()函数来表示：

　　1.date(…)->strftime(“%Y-%m-%d”,…)
2.time(…)->strftime(“%H:%M:%S”,…)
3.datetime(…)->strftime(“%Y-%m-%d%H:%M:%S”,…)
4.julianday(…)->strftime(“%J”,…)

　　日期时间字符串，可以用以下几种格式：

　　YYYY-MM-DD
YYYY-MM-DDHH:MM
YYYY-MM-DDHH:MM:SS
YYYY-MM-DDHH:MM:SS.SSS
YYYY-MM-DDTHH:MM
YYYY-MM-DDTHH:MM:SS
YYYY-MM-DDTHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
DDDD.DDDD

　　在第五种到第七种格式中的“T”是一个分割日期和时间的字符；第八种到第十种格式只代表2000-01-01日的时间，第十一种格式的’now’表示返回一个当前的日期和时间，使用格林威治时间(UTC）；第十二种格式表示一个JulianDayNumbers。

　　修正符，日期和时间可以使用下面的修正符来更改日期或时间：

　　NNNdays
NNNhours
NNNminutes
NNN.NNNNseconds
NNNmonths
NNNyears
startofmonth
startofyear
startofweek
startofday
weekdayN
unixepoch
localtime
utc

　　前六个修正符就是简单的增加指定数值的时间和日期；第七到第十个修正符表示返回当前日期的开始；第十一个修正符表示返回下一个星期是N的日期和时间；第十二个修正符表示返回从1970-01-01开始算起的秒数；第十三个修正符表示返回本地时间。

　　下面举一些例子：

　　计算机当前时间
SELECTdate(‘now’)
计算机当前月份的最后一天
SELECTdate(‘now’,’startofmonth’,’+1month’,’-1day’)
计算UNIX时间戳1092941466表示的日期和时间
SELECTdatetime(‘1092941466’,’unixepoch’)
计算UNIX时间戳1092941466表示的本地日期和时间
SELECTdatetime(‘1092941466’,’unixepoch’,’localtime’)
计算机当前UNIX时间戳
SELECTstrftime(‘%s’,’now’)
两个日期之间相差多少天
SELECTjolianday(‘now’)-jolianday(‘1981-12-23’)
两个日期时间之间相差多少秒
SELECTjulianday('now')*86400-julianday('2004-01-0102:34:56')*86400
计算今年十月份第一个星期二的日期
SELECTdate('now','startofyear','+9months','weekday2');

SQLite函数整理

SQLite包含了如下时间/日期函数：
datetime().......................产生日期和时间
date()...........................产生日期
time()...........................产生时间
strftime().......................对以上三个函数产生的日期和时间进行格式化

datetime()的用法是：datetime(日期/时间,修正符...)
date()和time()的语法与datetime()相同。
在时间/日期函数里可以使用如下格式的字符串作为参数：
YYYY-MM-DD
YYYY-MM-DDHH:MM
YYYY-MM-DDHH:MM:SS
YYYY-MM-DDHH:MM:SS.SSS
HH:MM
HH:MM:SS
HH:MM:SS.SSS
now
其中now是产生现在的时间。

举个例子：
selectdate('2006-10-17','+1day','+1year');
结果：2007-10-18

strftime()函数可以把YYYY-MM-DDHH:MM:SS格式的日期字符串转换成其它形式的字符串。
strftime()的语法是strftime(格式,日期/时间,...)

举个例子：
selectstrftime('%Y.%m.%d%H:%M:%S','now','localtime');
结果：2006.10.1721:41:09

在使用中发现SQLite不支持YYYY-MM-DD格式化，所以使用了strftime来处理。

SQLite内建函数表