数据库增删改查

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

int do_delete(sqlite3* db);
int do_insert(sqlite3* db);

int do_update(sqlite3* db);
int do_select(sqlite3* db);

int main(int argc, const char *argv[])
{
	//创建并打开数据库
	sqlite3 *db = NULL;
	if(sqlite3_open("./my.db", &db) != SQLITE_OK)
	{
		fprintf(stderr, "line:%d sqlite3_open:%s\n", __LINE__, sqlite3_errmsg(db));
		fprintf(stderr, "line:%d sqlite3_open:%d\n", __LINE__, sqlite3_errcode(db));
		return -1;
	}
	printf("database open success\n");

	//创建一张表格
	//sql语句，在数据库中怎么写，在代码中就怎么写，不确定sql语句是否是正确的，
	//可以将该sql语句在数据库中执行一遍，看看是不是正确的;
	char sql[128] = "create table if not exists zoo (id int primary key, name char, sore float);";
	// char* ptr = "create table stu (id int, 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("create table success\n");


	char c = 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("请输入>>>");
		c = getchar();
		while(getchar()!=10);

		switch(c)
		{
		case '1':  //增 ---> 全字段插入
			do_insert(db);
			break;
		case '2':  //删 ---> 通过id号去删除
			do_delete(db);
			break;
		case '3':  //改 ---> 通过id号修改
			do_update(db);
			break;
		case '4':
			do_select(db);
			break;
		case '5':
			goto END;
		default:
			printf("输入错误，请重新输入\n");
		}

		printf("输入任意字符清屏>>>");
		while(getchar()!=10);
	}



END:
	//关闭数据库
	if(sqlite3_close(db) != SQLITE_OK)
	{
		fprintf(stderr, "line:%d sqlite3_open:%s\n", __LINE__, sqlite3_errmsg(db));
		fprintf(stderr, "line:%d sqlite3_open:%d\n", __LINE__, sqlite3_errcode(db));
		return -1;
		printf("database close success\n");
	}
	return 0;
}

int do_delete(sqlite3 *db)
{
	int id;
	char act[128];
	char* errmsg = NULL;
	while(1)
	{
		printf("请输入要删除信息的ID：\n如果不需要删除输入99退出\n");
		//			scanf("%d %s %f",&id,name,&sore);
		bzero(act,sizeof(act));

		scanf("%d",&id);
		if(99 == id)
			break;

		
		sprintf(act, "delete from zoo where id=%d", id);

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


int do_insert(sqlite3 *db)
{

	int id;
	char name[20];
	float  sore;
		char act[128];
		char* errmsg = NULL;
		while(1)
		{
			printf("请输入要增加的信息：ID name sore\n如果不需要插入输入99退出\n");
//			scanf("%d %s %f",&id,name,&sore);
			bzero(act,sizeof(act));

			scanf("%d",&id);
			if(99 == id)
				break;

			scanf("%s %f",name,&sore);
			sprintf(act, "insert into zoo values(%d,'%s',%f )", id, name, sore);

			if(sqlite3_exec(db, act, NULL, NULL, &errmsg) != SQLITE_OK)
			{
				fprintf(stderr, "line:%d sqlite3_exec:%s\n", __LINE__, errmsg);
				return -1;
			}
			printf("database intsert success\n");
		}
		return 0;
}
int do_update(sqlite3 *db)
{
	int id;
	char name[20];
	float  sore;
	char act[128];
	char* errmsg = NULL;
	while(1)
	{
		printf("请输入要修改信息的ID：\n如果不需要修改输入99退出\n");
		//			scanf("%d %s %f",&id,name,&sore);
		bzero(act,sizeof(act));

		scanf("%d",&id);
		if(99 == id)
			break;
		printf("请输入要修改为的name sore：\n");
		scanf("%s %f",name,&sore);
		while(getchar() != 10);
		sprintf(act,"update zoo set name='%s',sore=%f where id=%d", name, sore,id);

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

int callback(void *arg, int column, char** column_text, char** column_name) 	//void* arg = &flag
{
	//列数
	//	printf("column=%d __%d__\n", column, __LINE__);

	//column_name
	int i = 0;
	if(0 == *(int*)arg)
	{
		for(i=0; i<column; i++)
		{
			printf("%s\t", column_name[i]);
		}
		printf("\n");
		*(int*)arg = 1;
	}

	//column_text
	for(i=0; i<column; i++)
	{
		printf("%s\t", column_text[i]);
	}
	printf("\n");



	return 0;
}

 int do_select(sqlite3* db)
{
	char act[128];
	char **pres = NULL;
	int row,column;
	char* errmsg = NULL;
	int flag = 0;
	bzero(act,sizeof(act));
	sprintf(act,"select * from zoo;");

	/*if(sqlite3_exec(db, act, callback, &flag, &errmsg) != SQLITE_OK)
	{
		fprintf(stderr, "line:%d sqlite3_exec:%s\n", __LINE__, errmsg);
		return -1;
		}*/
	if(sqlite3_get_table(db,act,&pres,&row,&column,&errmsg) != SQLITE_OK)
	{
		fprintf(stderr, "line:%d sqlite3_exec:%s\n", __LINE__, errmsg);
		return -1;
	}
	int i = 0;
	for(i=0; i<(row+1)*column;i++)
	{
		printf("%s\t",pres[i]);
		if(i%column == column-1)
			putchar(10);
	}
	/*
	 int line,list;
	 int i = 0;
	 for(line=0; line<row+1; line++)
	 {
	 	for(list=0; list<column;list++)
		{
			printf("%s\t",pres[i]);
			i++;
		}
		putchar(10);
	 }

	 */
	printf("database select success\n");
	sqlite3_free_table(pres);
	return 0;
}