网络编程6

简单的sqlite3数据库使用

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


int do_insert(sqlite3* db);
int do_delete(sqlite3* db);
int do_update(sqlite3* db);
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\n");


	//创建一个表
	//注意：C代码中编写的SQL语句与在数据库中编写的一致
	//char* ptr = "create table if stu (id int ,name char ,soore float);"
	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 syu 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':
				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;
}
int do_insert(sqlite3* db)
{
	int id;
	char name[20]="";
	float score;
	char sql[128]="";
	char* errmsg = NULL;
	printf("请输入想要增加的ID 姓名　分数:");
	scanf("%d%*c %s %f",&id,name,&score);


	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("插入数据成功\n");

}
int do_delete(sqlite3* db)
{
	int id;
	char sql[128]="";
	char* errmsg = NULL;
	printf("请输入想要删除的ID ");
	scanf("%d",&id);


	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("删除数据成功\n");

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


	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("修改数据成功\n");

}