关于SQLite的移动开发必读

SQLite是遵守ACID的关系数据库管理系统，广泛使用在终端设备上。

它包含在一个相对小的C库中。它是D.RichardHipp创建的公有领域项目。

不像常见的客户端/服务器结构范例，SQLite引擎不是个程序与之通信的独立进程，而是连接到程序中成为它的一个主要部分。所以主要的通信协议是在编程语言内的直接API调用。这在消耗总量、延迟时间和整体简单性上有积极的作用。整个数据库（定义、表、索引和数据本身）都在宿主主机上存储在一个单一的文件中。它的简单的设计是通过在开始一个事务的时候锁定整个数据文件而完成的。

SQLite支持事务，索引，还有触发器。它的常用功能我测试了一下，在下面做一个梳理。

1、建表

字段类型包括integer,varchar,date三个常用类型。唉，其实sqlite没数据类型的概念，都是number。
create table t_shp_bill(billid integer primary key ,userid integer,dayid integer,itemtxt varchar(200),state integer,billsn integer,created datetime,modified datetime);

选择主键字段为billid，非联合主键。

2、插入数据

insert into t_shp_bill(billid,userid,dayid,itemtxt,state,billsn,created,modified)
values(110119,110110,20130924,'国内气节',0,1,datetime('2013-12-30 12:10:04'),datetime('2013-12-30 12:10:04.100'));

insert into t_shp_bill(billid,userid,dayid,itemtxt,state,billsn,created,modified)
values(110112,110110,strftime('%Y%m%d','now','localtime'),'国内气节',0,1,datetime('now','localtime'),datetime('now','localtime'));

insert into t_shp_bill(billid,userid,dayid,itemtxt,state,billsn,created,modified)
values(110117,110110,strftime('%Y%m%d','now','localtime'),'国及',0,1,datetime('2013-09-11 12:12:12','localtime'),datetime('now','localtime'));

3、介绍几个简单的日期转字符串函数

select strftime('%Y%m%d','now') as c1;
select strftime('%Y%m%d','now','localtime');
select strftime('%Y%m%d%H%M%S','now');
select strftime('%Y%m%d%H%M%S','now','localtime');

sqlite> select strftime('%Y%m%d','now') as c1;
20130924
sqlite>
sqlite> select strftime('%Y%m%d','now','localtime');
20130924
sqlite>
sqlite> select strftime('%Y%m%d%H%M%S','now');
20130924092216
sqlite>
sqlite> select strftime('%Y%m%d%H%M%S','now','localtime');
20130924172216

select strftime('%Y%m%d%H%M%S',datetime('2013-12-30 12:10:04'),'localtime');

sqlite> select strftime('%Y%m%d%H%M%S',datetime('2013-12-30 12:10:04'),'localtime');
20131230201004
sqlite> select strftime('%Y%m%d%H%M%S',datetime('2013-12-30 12:10:04'));
20131230121004
sqlite>

4、继续插入数据

insert into t_shp_bill(billid,userid,dayid,itemtxt,state,billsn,created,modified) values(replace(strftime('%Y%m%d%H%M%f','now','localtime'),'.','') ,110110,strftime('%Y%m%d','now','localtime'),'国及',0,1,datetime('2013-09-11 12:12:12','localtime'),datetime('now','localtime'));

select replace(strftime('%Y%m%d%H%M%f','now','localtime'),'.','')
使用精确到毫秒的时间值作为主键，嵌入式系统不可能在一个毫秒内插入两条记录，因此该主键的约束条件是成立的。


5、继续建表
drop table t_shp_bill;
create table t_shp_bill(billid integer,userid integer,dayid integer,itemtxt varchar(200),state integer,billsn integer,created datetime,modified datetime,constraint pk_shp_bill primary key (billid,userid));

考虑到数据会上传到服务器端，所以加上userid字段建立联合主键。

6、创建索引
create index ind_shp_bill_1 on t_shp_bill(dayid);

insert into t_shp_bill(billid,userid,dayid,itemtxt,state,billsn,created,modified) values(replace(strftime('%Y%m%d%H%M%f','now','localtime'),'.','') ,110112,strftime('%Y%m%d','now','localtime'),'国及',0,1,datetime('2013-09-11 12:12:12','localtime'),datetime('now','localtime'));

insert into t_shp_bill(billid,userid,dayid,itemtxt,state,billsn,created,modified) values(replace(strftime('%Y%m%d%H%M%f','now','localtime'),'.','') ,110112,strftime('%Y%m%d','now','-1 day','localtime'),'国及',0,1,datetime('2013-09-11 12:12:12','localtime'),datetime('now','localtime'));


select * from t_shp_bill where dayid=20130923;

7、使用分组函数

select count(*),dayid from t_shp_bill group by  dayid order by dayid;

sqlite> .explain on
sqlite> .timer on
sqlite> select count(*),dayid from t_shp_bill group by  dayid order by dayid;
coun  dayid
----  -------------
1     20130923
24    20130924
CPU Time: user 0.000000 sys 0.000000
sqlite> select * from t_shp_bill where dayid=20130923;
bill  userid         dayi  item  stat  billsn         cr  modified
----  -------------  ----  ----  ----  -------------  --  -------------
20130924173121781  110112         20130923  国及  0     1              2013-09-1
1 20:12:12  2013-09-24 17:31:21

CPU Time: user 0.000000 sys 0.000000

参考

http://www.tutorialspoint.com/sqlite/sqlite_date_time.htm

http://baike.baidu.com/view/19310.htm