SQLite数据库的增删改查

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

int do_insert(sqlite3 *db)
{
	char buf[128] = "insert into ";
	char val[128] = " values ";
	char data[128] = "";
	printf("请输入：表名(字段名)>>>  ");
	fflush(stdout);
	bzero(data, sizeof(data));
	read(0, data, sizeof(data));
	data[strlen(data) - 1] = '\0';
	strcat(buf,data);
	strcat(buf,val);
	printf("请输入：(数据)>>>  ");
	fflush(stdout);
	bzero(data, sizeof(data));
	read(0, data, sizeof(data));
	data[strlen(data) - 1] = '\0';
	strcat(buf,data);
	strcat(buf,";");
	char *errmsg = NULL;
	if(sqlite3_exec(db, buf, NULL, NULL,&errmsg) != 0)
	{
		printf("__%d__ errmsg:%s\n",__LINE__, errmsg);
		return -1;
	}
}

int do_update(sqlite3 *db)
{	
	char buf[128] = "update ";
	char val[128] = " set ";
	char whe[128] = " where ";
	char data[128] = "";
	printf("请输入：表名>>>  ");
	fflush(stdout);
	bzero(data, sizeof(data));
	read(0, data, sizeof(data));
	data[strlen(data) - 1] = '\0';
	strcat(buf,data);
	strcat(buf,val);
	printf("请输入：字段=修改值>>>  ");
	fflush(stdout);
	bzero(data, sizeof(data));
	read(0, data, sizeof(data));
	data[strlen(data) - 1] = '\0';
	strcat(buf,data);
	strcat(buf,whe);
	printf("请输入：限制条件>>>  ");
	fflush(stdout);
	bzero(data, sizeof(data));
	read(0, data, sizeof(data));
	data[strlen(data) - 1] = '\0';
	strcat(buf,data);
	strcat(buf,";");
	char *errmsg = NULL;
	if(sqlite3_exec(db, buf, NULL, NULL,&errmsg) != 0)
	{
		printf("__%d__ errmsg:%s\n",__LINE__, errmsg);
		return -1;
	}

}

int select_back(void *arg, int num, char**column_text, char**column_name)
{
	if(0 == *(int *)arg)
	{
		for(int i=0; i<num; i++)
			printf("%s\t",column_name[i]);
		puts("");
		*(int *)arg = 1;
	}
	for(int i=0; i<num; i++)
		printf("%s\t",column_text[i]);
	puts("");
	return 0;
}

int do_select(sqlite3 *db)
{
	char buf[128] = "select * from stu;";
	int flag = 0;
	char *errmsg = NULL;
	if(sqlite3_exec(db, buf, select_back, &flag,&errmsg) != 0)
	{
		printf("__%d__ errmsg:%s\n",__LINE__, errmsg);
		return -1;
	}
}

int do_delete(sqlite3 *db)
{
	char buf[128] = "delete from ";
	char val[128] = " where ";
	char data[128] = "";
	printf("请输入：表名>>>  ");
	fflush(stdout);
	bzero(data, sizeof(data));
	read(0, data, sizeof(data));
	data[strlen(data) - 1] = '\0';
	strcat(buf,data);
	strcat(buf,val);
	printf("请输入：限制条件>>>  ");
	fflush(stdout);
	bzero(data, sizeof(data));
	read(0, data, sizeof(data));
	data[strlen(data) - 1] = '\0';
	strcat(buf,data);
	strcat(buf,";");
	char *errmsg = NULL;
	if(sqlite3_exec(db, buf, NULL, NULL,&errmsg) != 0)
	{
		printf("__%d__ errmsg:%s\n",__LINE__, errmsg);
		return -1;
	}

}

int main(int argc, const char *argv[])
{
	//打开数据库
	sqlite3 *db = NULL;
	if(sqlite3_open("./sq.db", &db) != 0)
	{
		printf("__%d__ errcode:%d errmsg:%s\n",__LINE__, sqlite3_errcode(db), sqlite3_errmsg(db));
		return -1;
	}

	//创建列表命令
	char buf[128] = "create table if not exists stu(id int primary key, name char, score float);";
	char *errmsg = NULL;
	if(sqlite3_exec(db, buf, NULL, NULL,&errmsg) != 0)
	{
		printf("__%d__ errmsg:%s\n",__LINE__, errmsg);
		return -1;
	}

	char c = 0;
	while(1)
	{
		puts("------------------------------------");
		puts("-------------1.插入-----------------");
		puts("-------------2.删除-----------------");
		puts("-------------3.修改-----------------");
		puts("-------------4.查询-----------------");
		puts("-------------5.退出-----------------");
		puts("------------------------------------");
		printf("请输入>>> ");

		c = getchar();
		while(getchar() != 10);

		switch(c)
		{
		case '1':
			do_insert(db);
			break;
		case '2':
			do_delete(db);
			break;
		case '3':
			do_update(db);
			break;
		case '4':
			do_select(db);
			break;
		case '5':
			goto END;
			break;
		default:
			puts("输入有误,请重新输入!");
		}
	}
END:
	//关闭数据库
	sqlite3_close(db);
	db = NULL;
	return 0;
}