sqlite数据库的插入，删除，修改

代码如下：

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

//插入函数
void do_insert(sqlite3 *db);

//删除函数
void do_delete(sqlite3 *db);

//修改函数
void do_update(sqlite3 *db);

int main(int argc, const char *argv[])
{
	sqlite3 *db = NULL;
	//创建并打开一个数据库
	if(sqlite3_open("./sql.db",&db) != SQLITE_OK)
	{
		fprintf(stderr,"line:%d sqlite3_open file:%d:%s\n",__LINE__,sqlite3_errcode(db),sqlite3_errmsg(db));
		return -1;
	}
	printf("sqlite3_open success\n");
	
	//创建表格
	char sql[128] = "create table if not exists stu(id int primary key,name char,score float);";
	char *errmsg = NULL;

	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
		return -1;
	}
	printf("table stu success\n");

	char choose = 0;
	while(1)
	{
		system("clear");
		printf("------------------------------\n");
		printf("-----------1.插入-------------\n");
		printf("-----------2.删除-------------\n");
		printf("-----------3.修改-------------\n");
		printf("-----------4.查询-------------\n");
		printf("-----------5.退出-------------\n");
		printf("------------------------------\n");
		printf("请输入>>>");
		choose = getchar();
		while(getchar() != 10);
		switch(choose)
		{
			case '1':
				do_insert(db);
				break;
			case '2':
				do_delete(db);
				break;
			case '3':
				do_update(db);
				break;
			case '4':
				//do_select
				break;
			case '5':
				goto AP;
				break;
			default:
				printf("输入错误，请输入正确的字符\n");
				break;
		}
		printf("请输入任意字符清屏>>>");
		while(getchar()!=10);
	}
AP:
	//关闭数据库
	if(sqlite3_close(db) != SQLITE_OK)
	{
		fprintf(stderr,"line:%d sqlite3_close file:%d:%s\n", __LINE__,sqlite3_errcode(db),sqlite3_errmsg(db));
		return -1;		
	}
	return 0;
}


//插入函数
void do_insert(sqlite3 *db)
{
	char *errmsg = NULL;
	int id = 0;
	char name[20] = "";
	float score = 0;

	printf("请输入id:");
	scanf("%d",&id);
	while(getchar() != 10);

	printf("请输入姓名:");
	fgets(name,sizeof(name),stdin);
	name[strlen(name)-1] = 0;

	printf("请输入成绩:");
	scanf("%f",&score);
	while(getchar() != 10);

	char str[128] = "";	
	sprintf(str,"insert into stu values (%d,\"%s\",%g);",id,name,score);
	
	if(sqlite3_exec(db,str,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
		return;
	}
	printf("insert success\n");

}

//删除函数
void do_delete(sqlite3 *db)
{
	int id = 0;
	char *errmsg = NULL;
	printf("请输入要删除得id号>>>");
	scanf("%d",&id);
	while(getchar()!=10);

	char str[128] = "";
	sprintf(str,"delete from stu where id=%d;",id);

	if(sqlite3_exec(db,str,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
		return;
	}
	printf("delete success\n");

}

//修改函数
void do_update(sqlite3 *db)
{
	char c = 0;
	char *errmsg = NULL;
	int id = 0,temp_id = 0;
	char name[20] = "",temp_name[20] = "";
	float score = 0,temp_score = 0;
	char str[128] = "";

	printf("---------请输入要修改那个字段----------\n");
	printf("-------------1.id----------------------\n");
	printf("-------------2.name--------------------\n");
	printf("-------------3.score-------------------\n");
	printf("---------------------------------------\n");
	c = getchar();
	while(getchar()!=10);
	switch(c)
	{
		case '1':
			printf("请输入要修改的id号:");
			scanf("%d",&id);
			while(getchar()!=10);
			printf("请输入修改后的id号:");
			scanf("%d",&temp_id);
			while(getchar()!=10);
			sprintf(str,"update stu set id=%d where id=%d",temp_id,id);
			break;
		case '2':
			printf("请输入要修改的id号:");
			scanf("%d",&id);
			while(getchar()!=10);
			printf("请输入修改后的name:");
			fgets(name,sizeof(name),stdin);
			str[strlen(str)-1] = 0;
			sprintf(str,"update stu set name=%s where id=%d",name,id);
			break;
		case '3':
			printf("请输入要修改的id号:");
			scanf("%d",&id);
			while(getchar()!=10);
			printf("请输入修改后的score:");
			scanf("%f",&temp_score);
			while(getchar()!=10);
			sprintf(str,"update stu set score=%g where id=%d",temp_score,id);
			break;
		default:
			printf("输入字符错误\n");
			break;
	}
	
	if(sqlite3_exec(db,str,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
		return ;
	}
	printf("update success\n");

}

终端执行结果：

ubuntu@ubuntu:sqlite$ gcc 01_selite3.c -lsqlite3
ubuntu@ubuntu:sqlite$ ./a.out 
sqlite3_open success
table stu success
------------------------------
-----------1.插入-------------
-----------2.删除-------------
-----------3.修改-------------
-----------4.查询-------------
-----------5.退出-------------
------------------------------
请输入>>>1
请输入id:6
请输入姓名:aaaa
请输入成绩:60
insert success
请输入任意字符清屏>>>1
------------------------------
-----------1.插入-------------
-----------2.删除-------------
-----------3.修改-------------
-----------4.查询-------------
-----------5.退出-------------
------------------------------
请输入>>>2
请输入要删除得id号>>>1
delete success
请输入任意字符清屏>>>3
------------------------------
-----------1.插入-------------
-----------2.删除-------------
-----------3.修改-------------
-----------4.查询-------------
-----------5.退出-------------
------------------------------
请输入>>>3
---------请输入要修改那个字段----------
-------------1.id----------------------
-------------2.name--------------------
-------------3.score-------------------
---------------------------------------
3
请输入要修改的id号:6
请输入修改后的score:130
update success
请输入任意字符清屏>>>1
------------------------------
-----------1.插入-------------
-----------2.删除-------------
-----------3.修改-------------
-----------4.查询-------------
-----------5.退出-------------
------------------------------
请输入>>>5
ubuntu@ubuntu:sqlite$ sqlite3 sql.db 
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> .header on
sqlite> .mode column
sqlite> SELECT * FROM stu
   ...> ;
id          name        score     
----------  ----------  ----------
2           lisi        120.0     
3           wangwu      60.0      
4           zhaoliu     140.0     
5           jcbjsdbc    120.0     
6           aaaa        130.0     
sqlite> .exit
ubuntu@ubuntu:sqlite$