linux 下执行

#sqlite3 /tmp/test.db

>select * from table;

创建数据库:

create table tb_test( id integer,name varchar,score integer);

增：

insert into tb_test values(1,'aaa',85);
insert into tb_test values(2,'bbb',66);
insert into tb_test values(3,'ccc',85);
insert into tb_test values(1,75);
insert into tb_test values(2,77);

删：

DROP TABLE tb_test;

改：

查询：

select * from tb_test;

select * from tb_test group by id having count(*) > 1 ;

select distinct id,name,score from tb_test group by id;

select distinct id,score from tb_test group by id order by score desc;

select id,score from tb_test group by id order by score;

select * from (select * from (select id,score from tb_test order by id) order by score) group by id;

select * from table;

select * from table where id=5;

select * from table where id=5 and name='123' ;

select * from table where time>'2014-06-24 00:00:00' order by id desc limit 3;

高级用法：

提取重复数据

SELECT * FROM tablename GROUP BY colname HAVING COUNT(*) > 1

过滤重复数据

SELECT * FROM tablename GROUP BY colname HAVING COUNT(*) = 1

数据类型注意事项：

datetime类型可直接比较大小。

一般编程中会用到popen获取当前时间。

以及strToTime and timeToStr等函数自由转换。