SQLite之API操作_sqlite 用api如何按列插入

SQLite编程预备

(1)下载安装数据库

 sudo apt update
 sudo apt install sqlite3
1
2

(2)安装函数库

 sudo apt-get install libsqlite3-dev
1

否则编译会出现以下问题：
zgy@zgy-virtual-machine:~/sqlite$ gcc student.c -o student -lsqlite3
student.c:2:10: fatal error: sqlite3.h: 没有那个文件或目录
2 | #include <sqlite3.h>
| ^~~~~~~~~~~
compilation terminated.
zgy@zgy-virtual-machine:~/sqlite$ sudo apt-get install libsqlite3-dev

SQLite编程接口

（1）int sqlite3_open(char * path,sqlite3 **db);
功能:打开sqlite数据库,没有则创建
参数：path------数据库文件路径
db--------指向sqlite句柄的指针
返回值–成功返回0（SQLITE_OK），失败返回错误码

（2）int sqlite3_close(sqlite3 *db);
功能:关闭sqlite数据库
参数 : db--------指向sqlite句柄的指针
返回值–成功返回0（SQLITE_OK），失败返回错误码

（3）const char* sqlite3_errmsg(sqlite3 *db);
功能:通过db句柄，得到数据库操作的错误信息
参数 : db--------指向sqlite句柄的指针
返回值–返回错误信息的首地址

（4）int sqlite3_exec(
sqlite3 *db, //打开的数据库
const char * sql, //要执行的sql语句
int (*callback)(void *arg,int,char **,char **) //回调函数，保存我们所查询的表格，以供查询
void * arg, //回调函数参数
char **errmsg, //储存错误消息的首地址
);
功能:执行一条sql语句
返回值–成功返回0（SQLITE_OK），失败非0值（错误码），并把错误信息放在errmsg里。

int (*callback)(
void *arg, //传递给回调函数的参数
int, //记录包含的字段数目—就是列的数目
char **, //每个字段值的指针数组，具体数据
char ** //每个字段名称的指针数组，表头
)
成功返回0，失败返回-1；

（4）int sqlite3_get_table(
sqlite3 *db, //打开的数据库
const char * sql, //执行的SQL语句
char ***resultp, //用来指向sql执行结果的指针
int *nrow, //满足条件记录的数目-----多少行（不包括表头）
int *ncolumn, //每条记录包含的字段数目----多少列
char **errmsg //错误信息
)

成功返回0，失败错误码。

eg.

则nrow=3，ncolumn=2;
resultp[0]=“Name”;
resultp[1]=“score”;
resultp[2]=“zhangsan”;
resultp[3]=“90”;
resultp[4]=“lisi”;
…

代码演示

#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
#include <unistd.h>



#define DATABASE "stu.db"

int do_insert(sqlite3 *db,char *errmsg);
int do_delete(sqlite3 *db,char *errmsg);
int do_query(sqlite3 *db,char *errmsg);
int do_update(sqlite3 *db,char *errmsg);
int do_quit(sqlite3 *db);
int callback(void * arg,int f_num,char **f_values,char **f_name);

int main(int argc,const char *argv[])
{
	sqlite3 *db;
	char *errmsg;
	char sql[128] = {0};
	int cmd=5;

	if(sqlite3_open(DATABASE,&db) != SQLITE_OK){
		printf("%s\n",sqlite3_errmsg(db));
		return -1;
	}else{
		printf("open DATABASE success.\n");
	}

	sprintf(sql,"create table stu(id integer primary key autoincrement not null,name char unique,score integer);");

	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK){
		printf("%s\n",errmsg);
	}else{
		printf("create table success.\n");
	}
	sqlite3_exec(db,".header on",NULL,NULL,&errmsg);
	sqlite3_exec(db,".mode column",NULL,NULL,&errmsg);
	while(1){
		printf("\033[41;33m**********************************************\033[0m\n");
		printf("\033[42;33m1:insert 2:delete 3:query 4:update 5:quit     \033[0m\n");
		printf("\033[41;33m**********************************************\033[0m\n");
//\033[41;33m 红底黄字 \033[0m
		scanf("%d",&cmd);
		getchar();
		switch(cmd){
			case 1:
				do_insert(db,errmsg);
				break;
			case 2:
				do_delete(db,errmsg);
				break;
			case 3:
				do_query(db,errmsg);
				break;
			case 4:
				do_update(db,errmsg);
				break;
			case 5:
				do_quit(db);
				break;
			dafault:
				printf("error cmd!\n");
				break;
		}
		if(cmd==5){
			printf("quit success.\n");
			sleep(1);
			system("clear");	
			break;
		}
	system("clear");	
	}
}

int do_insert(sqlite3 *db,char * errmsg){
//	printf("insert\n");
	char sql[128] = {0};
	char name[24] = {0};
	int score = 0;
	printf("please input name:");
	scanf("%s",name);
	getchar();

	printf("please input score:");
	scanf("%d",&score);
	getchar();

	sprintf(sql,"insert into stu(name,score) values('%s',%d);",name,score);
	score = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
	if(score != 0){
		printf("%s\n",errmsg);
		sleep(1);
	}else{
		printf("insert success.\n");
		sleep(1);
	}
	return 0;
}
int do_delete(sqlite3 *db,char *errmsg){
//	printf("delete\n");
	char sql[128] = {0};
	char name[24] = {0};
	int score = 0;
	printf("please input name:");
	scanf("%s",name);
	getchar();
/*
	printf("please input score:");
	scanf("%d",&score);
	getchar();
*/
	sprintf(sql,"delete from stu where name = '%s'",name);
	score = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
	if(score != 0){
		printf("%s\n",errmsg);
		sleep(1);
	}else{
		printf("delete success.\n");
		sleep(1);
	}

	return 0;
}
int callback(void * arg,int f_num,char **f_values,char **f_name)
{
	int i = 0;
	for(i = 0;i < f_num;i++){
		printf("%s",f_name[i]);
	}
	putchar(10);
	
	for(i = 0;i < f_num;i++){
		printf("%s",f_values[i]);
	}
	putchar(10);
	return 0;
}



int do_query(sqlite3 *db,char *errmsg)
{
//	printf("query\n");
	int t=0;
	char sql[128] = {0};
	sprintf(sql,"select * from stu;");

	t = sqlite3_exec(db,sql,callback,NULL,&errmsg);
	if(t != 0){
		printf("%s\n",errmsg);
		sleep(1);
	}else{
		printf("querry success.\n");
		sleep(5);
	}
	return 0;
}
int do_update(sqlite3 *db,char *errmsg){
//	printf("update\n");
	char sql[128] = {0};
	char name[24] = {0};
	int score = 0;
	printf("please input name:");
	scanf("%s",name);
	getchar();

	printf("please input score:");
	scanf("%d",&score);
	getchar();

	sprintf(sql,"update stu set score = %d where name = '%s'",score,name);
	score = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
	if(score != 0){
		printf("%s\n",errmsg);
		sleep(1);
	}else{
		printf("update success.\n");
		sleep(1);
	}
	return 0;
}
int do_quit(sqlite3 *db){
	printf("quiting\n");
	sqlite3_close(db);
//	system("clear");	
	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