sqlite3 的一些基本命令。

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,'aaa',75);
insert into tb_test values(2,'bbb',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, name, score from tb_test group by id order by score desc;

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

select * from (select * from (select id, name, 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等函数自由转换。