网络编程DAY6

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/stat.h>
#include <sys/types.h>
#include <fcntl.h>
#include <unistd.h>
#include <sqlite3.h>
int do_insert(sqlite3 *db)
{
	char sql[128]="";
	char *errmsg=NULL;
	int id;
	char name[20]="";
	float score;
	printf("请输入id:");
	scanf("%d",&id);
	getchar();
	printf("请输入姓名:");
	scanf("%s",name);
	getchar();
	printf("请输入分数:");
	scanf("%f",&score);
	getchar();

	sprintf(sql,"insert into stu values (%d,'%s',%f);",id,name,score);
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		fprintf(stderr,"line:%d sqlite3_exec :%s\n",__LINE__,errmsg);
		return -1;
	}
	printf("insert success");
	return 0;

}
int do_delete(sqlite3 *db)
{
	char sql[128]="";
	char *errmsg=NULL;
	int id;
	printf("请输入id:");
	scanf("%d",&id);
	getchar();

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

}
int do_update(sqlite3 *db)
{
	char sql[128]="";
	char *errmsg=NULL;
	int id;
	float score;
	printf("请输入id:");
	scanf("%d",&id);
	getchar();
	printf("请输入分数:");
	scanf("%f",&score);
	getchar();

	sprintf(sql,"update stu set score=%f where id=%d;",score,id);
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg)!=SQLITE_OK)
	{
		fprintf(stderr,"line:%d sqlite3_exec :%s\n",__LINE__,errmsg);
		return -1;
	}
	printf("update success");
	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 errcode:%d\n",sqlite3_errmsg(db),sqlite3_errcode(db));
		return -1;
	}
	printf("sqlite3_open success");

	//创建一个表
	//C代码中编写的sql语句与在数据库中的编写一致
	char sql[128]="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)
	{
		fprintf(stderr,"line:%d sqlite3_exec :%s\n",__LINE__,errmsg);
		return -1;
	}
	printf("create table stu sucess\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':
			do_delete(db);
			break;
		case '3':
			do_update(db);
			break;
		case '4':
		//	do_select();
			break;
		case '5':
			goto END;
			break;
		default:
			printf("输入错误,请重新输入\n");


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



	}

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