嵌入式-网络编程-数据库-sqlite3

基于sqlite3的增删改查

#include "/home/ubuntu/myhead.h"

//函数声明
//遍历
void ergodic();
//增
int insert(sqlite3* db);
//删
int delete(sqlite3* db);
//改
int change(sqlite3* db);
//查
int check(sqlite3* db);

//回调函数
int callback(void *arg,int num,char **f_name,char **f_num);


int main()
{
	//打开数据库
	sqlite3 *mydb = NULL;
	sqlite3_open("./Anime_Library.db",&mydb);

	while(1)
	{
		int num;
		//界面设计
		printf("******************************************\n");
		printf("请选择:\n");
		printf("1.遍历数据\n");
		printf("2.增加数据\n");
		printf("3.删除数据\n");
		printf("4.修改数据\n");
		printf("5.查找数据\n");
		printf("6.退出\n");
		printf("******************************************\n");		
		scanf("%d",&num);
		/*if(-1 == num)
		{
			break;
		}
		*/
		
		switch(num)
		{
			case 1:
			ergodic(mydb);
			break;
			
			case 2:
			insert(mydb);
			break;
			case 3:
			delete(mydb);
			break;
			
			case 4:
			change(mydb);
			break;
			case 5:
			check(mydb);
			break;
			
			case 6:
			system("clear");
			sqlite3_close(mydb);
			printf("正在退出......期待您的下次使用！\n");
			sleep(1);
			system("clear");
			break;
			default:
			system("clear");
			printf("输入有误，请重新输入!\n");
			sleep(1);
			continue;
		
		}
	}
	
}

//1.遍历
void ergodic(sqlite3* db)
{

	//用于执行查询相关的语句
	char **result;
	int row,column;
	sqlite3_get_table(db,"select *from Anime;",&result, &row,&column,NULL);
	
	for(int i = 0;i <(row+1)*column;i++)
	{
		printf("%-10s",result[i]);
		if((i+1)%column == 0)
		{
			printf("\n");
		}
	}	
}


//增
int insert(sqlite3* db)
{
	char name[10]={0};
	int id;
	char sex[10]={0};
	int age;
	printf("请输入要增加的数据name,id,sex(male/female),age:\n");
	scanf("%s %d %s %d",name,&id,sex,&age);
	getchar();
	
	char sql[100]={0};
	sprintf(sql,"insert into Anime values('%s',%d,'%s',%d)",name,id,sex,age);
	printf("sql = %s\n",sql);
	
	//执行一条sql语句
	char *errmsg = NULL;
	int ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
	//sqlite3_exec()的返回值SQLITE_OK，本质上是0
	if(ret != SQLITE_OK)
	{
		printf("%s\n",errmsg);
		return -1;		
	}
	printf("insert OK!\n");
}

//删除
int delete(sqlite3* db)
{
	int id = 0;
	printf("请输入ID：\n");
	scanf("%d",&id);
	getchar();
	
	char sql[100]={0};
	sprintf(sql,"delete from Anime where id = %d",id);
	printf("sql = %s\n",sql);
	
		//执行一条sql语句
	char *errmsg = NULL;
	int ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
	//sqlite3_exec()的返回值SQLITE_OK，本质上是0
	if(ret != SQLITE_OK)
	{
		printf("%s\n",errmsg);
		return -1;		
	}
	printf("delete OK!\n");	
}

//改
int change(sqlite3* db)
{
	char str[20]={0};
	char ptr[20] = {0};
	
	printf("请输入需要修改参数(格式为xx=xx)：\n");
	scanf("%s %s",str,ptr);
	getchar();
	
	char sql[100]={0};
	sprintf(sql,"update Anime set %s where %s",str,ptr);
	printf("sql = %s\n",sql);
	
	//执行一条sql语句
	char *errmsg = NULL;
	int ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
	//sqlite3_exec()的返回值SQLITE_OK，本质上是0
	if(ret != SQLITE_OK)
	{
		printf("%s\n",errmsg);
		return -1;		
	}
	printf("change OK!\n");		
}

//查
int check(sqlite3* db)
{

	int ret = sqlite3_exec(db, "select *from Anime;",&callback, NULL, NULL);
	//sqlite3_exec()的返回值SQLITE_OK，本质上是0
	if(ret != SQLITE_OK)
	{
		return -1;		
	}
	printf("check OK!\n");		
}
//回调函数
int callback(void *arg,int num,char **f_name,char **f_num)//char **f_name, char *f_name[]
{                 	 //数据个数，//每一个数据，//每一个表头
	static int flag = 0;
	if(flag == 0)
	{
		//打印查询结果
		for(int i = 0;i < num;i++)
		{
			printf("%-10s",f_num[i]);
		}
		flag = 1;
		printf("\n");
	}		
	for(int i = 0;i < num;i++)
	{
		printf("%-10s",f_name[i]);
	}
	printf("\n");
	
	return 0;
}