sqlite3官网地址：www.sqlite.org
源上安装：
sudo apt-get install libsqlite3-dev sqlite3
SQL:
1、创建表：
create table student(id integer primary key, name text, age integer, score integer);
2、查询表的内容：
select * from student;
select id, name from student;
3、往表中插入一条记录：
insert into student(id, name, age, score) values(3, 'XiaoMing', 21, 100);
insert into student values(3, 'XiaoMing', 21, 100);
4、修改一条记录：
update student set score=90, age=19 where id=4;
注意要加条件，否则就是修改所有记录了。
5、删除一条记录：
delete from student where id=2; 删除学号是2的学员的记录
delete from student; 删除所有记录
6、修改表的结构：
alter table student add score2 integer; 增加一个字段
但是sqlite数据库暂时不支持删除一个字段，也不支持修改一个字段。
alter table student rename to student1; 把表明从student 改变成student1
7、删除一张表：
drop table student; 删除student表.
8、备份一张表：
create table studentbak as select * from student; 把现有的student表备份成studentbak表。
9、查询表格数据：
select * from student;
select * from student where score=100;
select * from student where score!=80;
select * from student where score>=80;
select * from student order by score;
select * from student order by score asc;把记录按分数升序排列
select * from student order by score desc;把记录按分数降序排列
select * from student where score>=80 order by score;把记录先用where子句过滤，把剩下的记录排序。
select * from student where score between 80 and 90 order by score;升序显示分数在80到90之间的记录。
select * from student where name like 'X%'; 显示名字以X开头的记录。
select * from student where name like '%g'; 显示名字以g结尾的记录。
select * from student limit 5;显示头5条记录。
select * from student order by score desc limit 5; 显示分数最高的5名学员的记录。
select * from student order by score asc limit 2; 显示分数最低的2名学员的记录。
select * from student limit 5 offset 3; 跳过3条记录显示5条记录。
select * from student limit 5 , 3; 和上条语句类似，只不过数值含义相反。
group by用法：
CREATE TABLE employee(id integer primary key, name text, dep text, salary integer);
1|XiaoLi|market|6000
2|XiaoLi|tech|7000
3|XiaoMing|trs|8600
4|XiaoZhang|trs|6000
5|XiaoSong|tech|8900
6|LaoZheng|market|4000
统计整个公司工资总和：
select sum(salary) from employee;
统计每个部门的工资总和：
select dep, sum(salary) from employee group by dep;
select dep, sum(salary) from employee where id>3 group by dep; where子句要放在group by的前面。
select dep, sum(salary) from employee where id>3 group by dep having sum(salary)>5000; having子句是group by的条件子句，where子句先发生，然后才是having 子句执行。
select id from employee group by id,name,dep,salary having count(*)>1;
查找重复记录的方法。
select count(*) from employee; ： 计算记录条数
select dep, avg(salary) from employee group by dep; ：计算平均值
select dep, min(salary) from employee group by dep; ：计算最小值
select dep, count(*) from employee group by dep; ：计算各个部门的记录个数
select * from studentnew where score=(select score from studentnew order by score desc limit 1); ：查询分数最高的所有人
select distinct name from fruit; ：察看fruit表的东西的种类（不重复）
select all name from fruit; ：察看所有的，（可以重复）
select * from studentnew where id > 3 intersect select * from studentnew where id<9; ；交集
select * from studentnew where id > 3 union select * from studentnew where id<9; ：并集
select * from studentnew where id > 3 union all select * from studentnew where id<9; ：并集, 重复的重复查询
select * from studentnew where id > 3 except select * from studentnew where id<9;
两个表(4 种)
1.等值查询
select student.id, student.name, score.score1, score.score2 from student, score where student.id = score.id;
2.自然连接 ：必须是有同名字段
select student.id, student.name, score.score1, score.score2 from student natural join score;
3.内连接
select studentnew.stuno, studentnew.name, score.score1, score.score2 from studentnew inner join score on studentnew.stuno=score.id;
等同于select a.stuno, a.name, b.score1, b.score2 from studentnew as a inner join score as b on a.stuno=b.id;
4.外连接
select a.stuno, a.name, b.score1, b.score2 from studentnew as a left outer join score as b on a.stuno=b.id;
select a.stuno, a.name, b.score1, b.score2 from score as b left outer join studentnew as a on a.stuno=b.id;
命令 ：
.import 文件名 表名 : 把文件中的数据批量导入到表中
.table ： 察看当前的已经创建的表；
.schema 表名：查看表中的各个数据；
sqlite3_mprintf()
sqlite3_free()
