day6 网络编程

 数据库

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

//增
int do_insert(sqlite3 *db);
//删
int do_delete(sqlite3 *db);
//改
int do_update(sqlite3 *db);
//查
int do_select(sqlite3 *db);

int main(int argc, const char *argv[])
{
	//如果数据库不存在，则创建后打开
	//如果存在直接打开
	sqlite3 *db = NULL;
	if(sqlite3_open("./sq.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 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;
	}

	//打开表头
	char *errmsg1 = NULL;
	char sql1[128] = ".header on";
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg1) != SQLITE_OK)
	{
		fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg1);
		return -1;
	}

	//对齐
	char *errmsg2 = NULL;
	char sql2[128] = ".mode column";
	if(sqlite3_exec(db,sql,NULL,NULL,&errmsg2) != SQLITE_OK)
	{
		fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg2);
		return -1;
	}


	char c = 0;
	while(1)
	{
		system("clear");
		printf("***********************************\n");
		printf("************   1. 增   ************\n");
		printf("************   2. 删   ************\n");
		printf("************   3. 改   ************\n");
		printf("************   4. 查   ************\n");
		printf("************   0. 退出 ************\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(db);
			break;
		case '0':
			goto END;
			break;
		default:
			printf("选择有误，重新选择\n");

		}
		printf("输入任意字符清屏\n");
		getchar();
		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)
{
	char buf[128] = "";
	char cmd[] = "insert into stu values ";
	int id = 0;
	char name[32] = "";
	int score = 0;
	int j = 0;
	char *errmsg = NULL;

	printf("请依次输入id name score>>\n");
	scanf("%d%s%d",&id,name,&score);
	
	j = sprintf(buf,"%s(",cmd);
	j += sprintf(buf + j,"%d,",id);
	j += sprintf(buf + j,"%s,",name);
	j += sprintf(buf + j,"%d);",score);

	if(sqlite3_exec(db,buf,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
		return -1;
	}
	printf("插入成功\n");
	return 0;
}

//删
int do_delete(sqlite3 *db)
{
	char buf[128] = "";
	int id = 0;
	char *errmsg = NULL;
	printf("请输入要删除的id>>");
	scanf("%d",&id);
	getchar();

	sprintf(buf,"delete from stu where id=%d;",id);

	if(sqlite3_exec(db,buf,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
		return -1;
	}
	printf("删除成功\n");
	return 0;

}


//改
int do_update(sqlite3 *db)
{
	char buf[128] = "";
	int id = 0;
	int score = 0;
	char *errmsg = NULL;


	printf("请依次输入要修改的信息id score>>\n");
	scanf("%d%d",&id,&score);

	sprintf(buf,"update stu set score=%d where id=%d;",score,id);

	if(sqlite3_exec(db,buf,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
		return -1;
	}
	printf("修改成功\n");
	return 0;

}

//查
int do_select(sqlite3 *db)
{
	char buf[128] = "";
	char cmd[] = "select * from stu;";
	char *errmsg = NULL;
	
	sprintf(buf,"%s",cmd);

	if(sqlite3_exec(db,buf,NULL,NULL,&errmsg) != SQLITE_OK)
	{
		fprintf(stderr,"line:%d sqlite3_exec:%s\n",__LINE__,errmsg);
		return -1;
	}

}