华清数据库

1.完成插入

#include <stdio.h>
#include <head.h>
int do_insert(sqlite3* db)
{
	int id=0;
	char name[20]="";
	float score=0;

	printf("请输入id>>>");
	scanf("%d",&id);
	while(getchar()!=10);	
	printf("请输入name>>>");
	scanf("%s",name);
	while(getchar()!=10);
	printf("请输入score>>>");
	scanf("%f",&score);
	while(getchar()!=10);

	char sql[128]="";
	sprintf(sql,"insert into stu values (%d,'%s',%f);",id,name,score);
	char *errmsg=NULL;

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

	return 0;
}

int do_delete(sqlite3* db)
{
	int id=0;

	printf("请输入要删除的id>>>");
	scanf("%d",&id);
	while(getchar()!=10);

	char sql[128]="";
	sprintf(sql,"delete from stu where id=%d;",id);
	char *errmsg=NULL;

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

	return 0;

}

int do_updata(sqlite3* db)
{
	int id=0;
	char name[20]="";
	float score=0;
	char sql1[128]="";
	char sql2[128]="";

	char *errmsg=NULL;	


	printf("请输入要修改的id>>>");
	scanf("%d",&id);
	while(getchar()!=10);	

	char choose=0;
	while(1)
	{
		printf("==========================\n");
		printf("--------1.修改姓名--------\n");
		printf("--------2.修改分数--------\n");
		printf("--------3.退出------------\n");
		printf("==========================\n");

		printf("请输入>>>>");
		choose=getchar();
		while(getchar()!=10);

		switch(choose)
		{
		case '1':
			printf("请输入要修改的姓名>>>");
			scanf("%s",name);
			while(getchar()!=10);	
			sprintf(sql1,"update stu set name = %s where id=%d;",name,id);
			if(sqlite3_exec(db,sql1,NULL,NULL,&errmsg)!= SQLITE_OK)
			{
				fprintf(stderr,"sqlite3_exec:%s %d __%d__",errmsg,sqlite3_errcode(db),__LINE__);
				return -1;
			}
			printf("update name success\n");
			break;
		case '2':	
			printf("请输入要修改的分数>>>");
			scanf("%f",&score);
			while(getchar()!=10);	
			sprintf(sql2,"update stu set score =%f where id=%d;",score,id);
			if(sqlite3_exec(db,sql2,NULL,NULL,&errmsg)!= SQLITE_OK)
			{
				fprintf(stderr,"sqlite3_exec:%s %d __%d__",errmsg,sqlite3_errcode(db),__LINE__);
				return -1;
			}
			printf("update score success\n");
			break;
		case '3':
			goto AAA;
		}
	}

AAA:
	return 0;


}

int do_selct(sqlite3* db)
{
	int id =0;
	printf("请输入要查找的id>>>");
	scanf("%d",&id);
	while(getchar()!=10);

	char sql[128]="";
	char *errmsg=NULL;
	sprintf(sql,"select * from stu where id = %d;",id);
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!= SQLITE_OK)
	{
		fprintf(stderr,"sqlite3_exec:%s %d __%d__",errmsg,sqlite3_errcode(db),__LINE__);
		return -1;
	}
	printf(" success\n");
	return 0;
}

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

	//创建一个表格 create table stu(id int ,name char ,score float);
	//数据库中sql语句怎么写，这里就怎么写
	char sql[128]="create table stu(id int primary key ,name char ,score float);";
	char *errmsg=NULL;

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

	char choose = 0;
	while(1)
	{
		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_updata(db);
			break;
		case '4':
			do_selct(db);
			break;
		case '5':
			goto END;
		defalut:
			printf("输入错误，请重新输入\n");

	
		}

	}


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

	return 0;
}