完成数据库的增删查

增

函数原型：insert into 表名 values(数据1，数据2，数据3)；

删

函数原型：delete from 表名 where 限制条件；

查

函数原型：select * from 表名 限制条件；

全

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


int do_search(sqlite3* db);
int do_delete(sqlite3* db);
int do_insert(sqlite3* db);
int main(int argc, const char *argv[])
{
	//打开数据库，不存在则创建
	sqlite3* db = NULL;
	if(sqlite3_open("./my.db",&db) != SQLITE_OK)
	{
		printf("line: %d sqlite3_open[%d] %s\n",__LINE__,sqlite3_errcode(db),sqlite3_errmsg(db));
		return -1;
	}
	printf("sqlite3_open success\n");

	//创建表格 create table 表名（字段名 数据类型，字段名 数据类型）
	//与数据库的格式一样
	char sql[1024] = "create table if not exists stu(id int,name char,score float)";
	char* errmsg = NULL;

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

	char c = 0;
	while(1)
	{
		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':
			do_delete(db);
			break;
		case '3':

			break;
		case '4':
			do_search(db);
			break;
		case '5':
			goto END;
			break;
		default:
			printf("输入信息错误，重新输入\n");
		}
	}
END:

	//释放数据库内存空间
	if(sqlite3_close(db) != SQLITE_OK)
	{
		printf("line: %d sqlite3_close %s\n",__LINE__,sqlite3_errmsg(db));
		return -1;
	}
	printf("sqlite3_close success\n");
		
	db = NULL;

	return 0;
}

int do_insert(sqlite3* db)
{
	int id;
	char name[20] = "";
	float score;
	char* errmsg = NULL;
	char input[1024] ="";
	scanf("%d %s %f",&id,name,&score);
 	while(getchar() != 10);
	sprintf(input,"insert into stu values(%d,%s,%.2f);",id,name,score);
	if(sqlite3_exec(db,input,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		printf("line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
		return -1;
	}
	return 0;
}

int do_delete(sqlite3* db)
{
	//按照id删除
	int id;
	char* errmsg = NULL;
	char input[1024] ="";
	scanf("%d",&id);
 	while(getchar() != 10);
	sprintf(input,"delete from stu where %d;",id);
	if(sqlite3_exec(db,input,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		printf("line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
		return -1;
	}
	return 0;
}

int do_search(sqlite3* db)
{
	//按照id查找
	int id;
	char* errmsg = NULL;
	char input[1024] ="";
	scanf("%d",&id);
 	while(getchar() != 10);
	sprintf(input,"select * from stu where %d",id);
	if(sqlite3_exec(db,input,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		printf("line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
		return -1;
	}
	printf("查找的数据为:%d %s %.2f\n",id,name,float);
	return 0;
}