sqlite3数据库安装及使用笔记

【1】sqlite3 数据库安装

1、本地安装 官网下载安装包

解压后使用如下命令进行安装

sudo dpkg -i *.deb

2、在线安装

sudo apt-get install -y sqlite3 libsqlite3-dev

【2】sqlite3基本命令

1–系统命令

以’.‘开头的命令
.help 帮助
.quit 退出
.exit 退出
.open xx.db 打开xx.db数据库（相当于打开excel文件名+后缀）
.databases 插看打开的数据库
.table 查看当前数据库的表
.schema 查看当前表的数据结构

2–sql命令

每个命令以;结尾

创建一张数据库表stu(相当于excel工作簿名字)

字段名+数据类型，字符串可以char型也可以string型 【==注意字段名不可以为数字+字母开头，但是可以字母+数字==】
	create table stu(id integer ,name char , score integer);
插入信息，其中字符串可以单引号也可以双引号
	insert into stu values(1001,'zhangsan',80);
	insert into stu values(1002,"lisi",90);

插入指定字段

insert into stu (id,name)values(1003,"wangwu");

查寻所有内容*代表所有，来自stu表

select * from stu;

查询指定字段

select id ,name from stu;

查询符合指定值的所有字段信息

select *from stu where score=80;

查询符合多个指定值同时满足的所有字段信息

select *from stu where score=80 and id=1001;
select *from stu where score=80 and name='zhangsan';
select *from stu where score=80 and name='zhangsan' and id=1001;

查询任意一项符合指定值的所有字段信息

select *from stu where score=90 or name='zhangsan';

查询符合指定值的指定字段信息

select id,name from stu where score=80;

查询并求和计算某一字段

依靠选择的字段进行求和，所以id 与 name两个字段必须统一，也就是id对应 name字段的值必须一致，name对应的id字段的值必须一致

SELECT id ,name ,SUM(score) FROM MAIN.stu WHERE score >= 10 GROUP BY id ,name;

如上图中
选中addressPort与wireID两个字段对TotalCurrent字段中值求和结果是错误的

SELECT addressPort ,wireID ,SUM(TotalCurrent) FROM MAIN.xx WHERE FileNum = 1 GROUP BY addressPort ,wireID;

错误结果如下，尽管wireID字段相同，但是addressPort字段存在不同，最后会将每个不同字段进行一次求和

正确的求和如下：只选择wireID字段，或者相同deviceType字段

#可以这样
SELECT wireID ,SUM(TotalCurrent) FROM MAIN.xx WHERE FileNum = 1 GROUP BY wireID;
#也可以这样
SELECT deviceType,wireID ,SUM(TotalCurrent) FROM MAIN.xx WHERE FileNum = 1 GROUP BY deviceType,wireID;

嵌套查询

SELECT * FROM MAIN.stu WHERE score IN (SELECT score FROM MAIN.另一个表 WHERE name = '小明') AND name = '小明';

查询sqlite_master device_info符合type=‘table’ AND name='device_info’表的数量

SELECT count(*) FROM sqlite_master device_info WHERE type='table' AND name='device_info';

查询device_info表中 update_time <= %d-(period*3) 条件的行信息

SELECT * FROM MAIN.device_info WHERE update_time <= %d-(period*3);
SELECT * FROM device_info WHERE update_time <= %d-(period*3);	

删除表中所有信息

delete from stu;

删除符合指定字段内容的对应信息行

delete from stu where name='zhangsan';

更新id=1001字段行 name内容改为wangwu

update stu set name='wangwu' where id=1001;

更新score="70"字段行name=‘lihoongyang’ , score=70,id=1003 （这里score虽然是整形但是sqlite3并不是严格符合类型改为其他类型也可以

只要值是字母用单引号或双引号括起即可
update stu set name='lihoongyang' , score=70,id=1003 where score="70";
update stu set name='lihoongyang' , score='xxx',id=1003 where score=70;

插入一列字段address

alter table stu add column address char;

删除一列

sqlite3不支持，直接删除一列address 
1、创建一张新表
	create table stu1 as select id , name , score from stu;
2、删除原有的表
	drop table stu;
3、将新表的名字改成原有的旧表名
	alter table stu1 rename to stu;

【3】sqlite3函数接口

int sqlite3_open(const char* filename,sqlite3 **ppDb); 打开一个数据库
						参数：filename 数据库路径名
						ppDb 数据库操作句柄（二级指针）
					返回值	成功 SQLITE_OK，否则错误码
int sqlite3_close(sqlite3 *db);关闭数据库
			参数：db操作数据库的指针
			返回值	成功 SQLITE_OK，否则错误码
const char *sqlite3_errmsg(sqlite3 *db);通过db句柄，得到数据库操作的错误信息
	
int sqlite3_exec(sqlite3 *db,const char *sql, int (*callback)(void *arg,int,char ** ,char**),void *arg,char **errmsg);执行一条sql语句
			参数：db 数据库操作句柄
			callback 回调函数，只有sql为查询语句的时候，才会执行此语句。
			arg 表示给回调函数传递参数
			errmsg 错误信息
			返回值：成功 SQLITE_OK

Sqlite3支持的数据类型

参考：https://www.cnblogs.com/IamLoser/p/6648396.html

数据类型	描述
NULL	空
INTEGER	32 位元的整数
TEXT	文本
BLOB	不限长字符串
smallint	16 位元的整数。
decimal(p,s)	p 精确值和 s 大小的十进位整数，精确值p是指全部有几个数(digits)大小值，s是指小数点後有几位数。如果没有特别指定，则系统会设为 p=5; s=0 。
float	32位元的实数。
double	64位元的实数。
char(n)	n 长度的字串，n不能超过 254。
varchar(n)	长度不固定且其最大长度为 n 的字串，n不能超过 4000。
graphic(n)	和 char(n) 一样，不过其单位是两个字元 double-bytes， n不能超过127。这个形态是为了支援两个字元长度的字体，例如中文字。
vargraphic(n)	可变长度且其最大长度为 n 的双字元字串，n不能超过 2000
date	包含了 年份、月份、日期。
time	包含了 小时、分钟、秒。
timestamp	包含了 年、月、日、时、分、秒、千分之一秒。

实用语句

# 查询打开的数据库表名
SELECT name FROM sqlite_master where type='table' order by name

代码方式

#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
/*退出时执行此函数一次*/
void func1()
{
	printf("The process is done...\n");
}
/*使用查询命令时调用此函数*/
int callback(void *para,int f_num,char **f_value,char **f_name)
{
	int i =0;
	for(i = 0;i<f_num;i++)
	{
		printf("%-11s",f_value[i]);//左对齐总宽度11个字符
	}
	putchar(10);
	return 0;
}
/*插入数据*/
int do_insert(sqlite3 *db)
{
	int id;
	char name[32] = {};
	int score;
	char sql[128] = {};
	char* errmsg;
	printf("input id:");
	scanf("%d",&id);
	getchar();
	printf("input name:");
	scanf("%s",name);
	getchar();
	printf("input score:");
	scanf("%d",&score);
	getchar();
	sprintf(sql,"insert into stu values(%d,'%s',%d);",id,name,score);
	if(sqlite3_exec(db, sql,NULL, NULL,&errmsg) != SQLITE_OK)
	{
		printf( "Can't create table: %s\n", errmsg);
		sqlite3_free(errmsg);//记得释放
	}
	else
	{
		printf("Insert done !\n");
	}
	return 0;
}
/*删除记录*/
int do_delete(sqlite3 *db)
{
	int id;
	char sql[128] = {};
	char* errmsg;
	printf("input query id:");
	scanf("%d",&id);
	getchar();
	
	sprintf(sql,"delete from stu where id = %d;",id);
	if(sqlite3_exec(db, sql,NULL, NULL,&errmsg) != SQLITE_OK)
	{
		printf( "Can't delete table: %s\n", errmsg);
		sqlite3_free(errmsg);//记得释放
	}
	else
	{
		printf("Delete done!\n");
	}
	return 0;
}
/*使用sql命令查询记录*/
int do_query(sqlite3 *db)
{
	int id;
	char sql[128] = {};
	char* errmsg;
	printf("input query id:");
	scanf("%d",&id);
	getchar();
	
	sprintf(sql,"select * from stu where id = %d;",id);
	if(sqlite3_exec(db, sql,callback, NULL,&errmsg) != SQLITE_OK)
	{
		printf( "Can't query table: %s\n", errmsg);
		sqlite3_free(errmsg);//记得释放
	}
	else
	{
		printf("Query done!\n");
	}
	return 0;
}
/*使用sqlite3_get_table函数查询*/
int do_query1(sqlite3 *db)
{
	int id;
	char sql[128] = {};
	char* errmsg;
	char** resultp;
	int nrow,ncloumn;
	int i,j;
	printf("input query id:");
	scanf("%d",&id);
	getchar();
	
	sprintf(sql,"select * from stu where id = %d;",id);
	if(sqlite3_get_table(db, sql,&resultp, &nrow,&ncloumn,&errmsg) != SQLITE_OK)
	{
		printf( "Can't query table: %s\n", errmsg);
		sqlite3_free(errmsg);//记得释放
	}
	else
	{
		printf("Query done!\n");
	}
	for(j = 0; j< ncloumn; j++)
	{
		printf("%-11s",resultp[j]);
	}
	putchar(10);
	int index = ncloumn;
	for(i =0;i< nrow;i++)
	{
		for(j = 0; j< ncloumn; j++)
		{
			printf("%-11s",resultp[index++]);
		}
		putchar(10);
	}
	sqlite3_free_table(resultp);//记得释放
	return 0;
}
/*修改记录*/
int do_update(sqlite3 *db)
{
	int id,cmd,change_id;
	char name[32] = {};
	int score;
	char sql[128] = {};
	char* errmsg;
	printf("need to update id number:");
	scanf("%d",&id);
	getchar();
	printf("*****************************************\n");
	printf("1:id      2:name      3:score      4:quit\n");
	printf("*****************************************\n");
	printf("input cmd:");
	scanf("%d",&cmd);
	getchar();//去掉尾部/0
	switch (cmd)
	{
			case 1:
				printf("input new id:");
				scanf("%d",&change_id);
				getchar();
				sprintf(sql,"update stu set id = %d where id =%d;",change_id,id);
				break;
			case 2:
				printf("input new name:");
				scanf("%s",name);
				getchar();
				sprintf(sql,"update stu set name='%s' where id =%d;",name,id);
				break;
			case 3:
				printf("input new score:");
				scanf("%d",&score);
				getchar();
				sprintf(sql,"update stu set score = %d where id =%d;",score,id);
				break;
			case 4:
				sqlite3_close(db);
				exit(0);
				break;
			default:
				break;
	}
	if(sqlite3_exec(db, sql,NULL, NULL,&errmsg) != SQLITE_OK)
	{
		printf( "Can't Update table: %s\n", errmsg);
	}
	else
	{
		printf("Update done !\n");
	}
		return 0;
}
/*初始化数据库，输入数字，选择要执行的功能*/
int main(int argc, char* argv[])
{
   sqlite3 *db;
   char *errmsg;
	 int cmd;
	 atexit(func1);
   if(sqlite3_open("stu.db", &db) != SQLITE_OK ){
      fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));
			printf( "Can't open database: %s\n", sqlite3_errmsg(db));
      exit(0);
   }else{
      fprintf(stderr, "Opened database successfully\n");
			printf( "Opened database successfully\n");	
   }
	 if(sqlite3_exec(db, "create table stu (id integer , name char , score integer);",NULL, NULL,&errmsg) != SQLITE_OK)
	{
		printf( "Can't create table: %s\n", errmsg);
		sqlite3_free(errmsg);//记得释放
	}
	else
	{
			printf("create or open success !\n");
	}
	while(1)
	{
		printf("*****************************************\n");
		printf("1:insert 2:delete 3:query 4:update 5:quit\n");
		printf("*****************************************\n");
		printf("input cmd:");
		scanf("%d",&cmd);
		getchar();//去掉尾部/0
		switch(cmd)
		{
			case 1:
				do_insert(db);
				break;
			case 2:
				do_delete(db);
				break;	
			case 3:
				//do_query(db);//callback 
				do_query1(db);//sqlite3_get_table
				break;			
			case 4:
				do_update(db);
				break;
			case 5:
				sqlite3_close(db);
				exit(0);
				break;
			default:
				printf("failt input!\n");
				break;
		}
	}
  return 0;
}

保存为：sqlite3.c文件
使用编译命令：

gcc sqlite3.c -lsqlite3

运行：

./a.out

关于交叉编译

1、下载源码点击前往下载地址
选择如下：
2、拖到虚拟机中

3、解压并编译

#解压
tar xvzf sqlite-autoconf-3290000.tar.gz

进入到此文件夹中执行命令：

./configure CC=/usr/bin/arm-linux-gnueabihf-gcc --host=arm-linux --prefix=/usr/tmp/

说明：CC赋值为嵌入式开发环境所使用的交叉编译工具，–host指定软件运行环境为arm-linux， --prefix指定源码交叉编译后生成文件的路径。

编译：

 sudo make && make install

执行成功，在/usr/tmp/文件夹下有bin include lib share四个文件夹，bin下有可执行文件sqlite3，include包含头文件msvc.h sqlite3.h sqlite3ext.h, lib文件件下是库文件：
libsqlite3.a是静态库，libsqlite3.so.0.8.6是动态库，libsqlite3.so 和 libsqlite3.so.0是libsqlite3.so.0.8.6的链接文件
PC端编译：

gcc sqlite3.c -lsqlite3
./a.out

编译嵌入式版本：

arm-linux-gnueabihf-gcc sqlite3.c -I/usr/tmp/include -L/usr/tmp/lib -lsqlite3
#也可以将库文件放到/lib下或者/usr/lib/下面，再进行编译即可！

嵌入式运行要求：

把bin/sqlite3拷贝到ARM板上的根目录/bin下，还有把/usr/tmp/lib下的libsqlite3.so.0.8.6 libsqlite3.so libsqlite3.so.0拷贝到ARM板根目录/lib目录下，就可以在ARM板上运行sqlite应用程序了。