sqlite3数据库安装及使用笔记_new sqlite3.database

【1】sqlite3 数据库安装

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

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

sudo dpkg -i *.deb
1

2、在线安装

sudo apt-get install -y sqlite3 libsqlite3-dev
1

【2】sqlite3基本命令

1–系统命令

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

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);
1
2
3
4
5

插入指定字段

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

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

select * from stu;
1

查询指定字段

select id ,name from stu;
1

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

select *from stu where score=80;
1

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

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;
1
2
3

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

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

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

select id,name from stu where score=80;
1

查询并求和计算某一字段

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

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

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

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

错误结果如下，尽管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;
1
2
3
4

嵌套查询

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

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

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

查询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);	
1
2

删除表中所有信息

delete from stu;
1

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

delete from stu where name='zhangsan';
1

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

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

更新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;
1
2
3

插入一列字段address

alter table stu add column address char;
1

删除一列

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

【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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Sqlite3支持的数据类型

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

实用语句

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

代码方式

#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;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244

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

gcc sqlite3.c -lsqlite3
1

运行：

./a.out
1

关于交叉编译

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

3、解压并编译

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

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

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

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

编译：

 sudo make && make install
1

执行成功，在/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
1
2

编译嵌入式版本：

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

嵌入式运行要求：

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