sqlite3数据库之通讯录

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

sqlite3 *  open_sqlite3(sqlite3 * db)
{
	int ret;
	ret = sqlite3_open("mytable.db",&db);
	if(SQLITE_OK != ret)
	{
		perror("打开库失败:");
		exit(-1);
	}
//	sqlite3_close(db);
	return db;
}

void create_table(sqlite3 * db)
{
	int ret;
	char * sql;
	char * errmsg;

	sql ="create table if not exists mytable(id integer primary key,name text,num integer,loc text)";
	ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
	if(SQLITE_OK != ret)
	{
		printf("创建通讯录失败:%s\n",errmsg);
		exit(-1);
	}
}


int  show()
{
	int n;
	printf("\
              ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊\n\
              ＊          欢迎使用通讯录！          ＊\n\
              ＊  1* 添加联系人      2* 编辑联系人  ＊\n\
              ＊  3* 查看联系人      4* 查找联系人  ＊\n\
              ＊  5* 联系人总数      6* 删除联系人  ＊\n\
              ＊  7* 清空联系人      0* 退出        ＊\n\
              ＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊\n");
	printf("请输入要选择的功能：\n");
	scanf("%d",&n);
	getchar();
	if(n < 0 || n > 7)
		printf("输入错误！！\n");
	else
		return n;

}

void add(sqlite3 * db)
{
	char sql[100];
	char * errmsg;
	char name[10];
	char loc[100];
    long long int num;
	int ret;

	while(1)
	{
		printf("请输入联系人的姓名、电话号码和归属地(q退出):\n");
		scanf("%s",name);
		if(strcmp(name,"q") == 0)
			break;
	    scanf("%lld",&num);
		scanf("%s",loc);
		if(strcmp(loc,"q") == 0)
			break;

		sprintf(sql,"insert into mytable(id,name,num,loc) values(NULL,'%s',%lld,'%s');",name,num,loc);
		ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
		if(SQLITE_OK != ret)
		{
			printf("添加联系人失败:%s\n",errmsg);
			break;
		}
	}
}

void see(sqlite3 * db)
{
	int ret,i;
	int row,col;
	char * sql;
	char * errmsg;
	char **presult;

	sql = "select * from mytable;";
	ret = sqlite3_get_table(db,sql,&presult,&row,&col,&errmsg);
	if(SQLITE_OK != ret)
	{
		printf("查询失败:%s\n",errmsg);
	}

	if(row < 2)
		printf("当前通讯录没有联系人！\n");

	for(i = 0;i < (row + 1)*col;i++)
	{
		printf("%-20s",presult[i]);
		if((i + 1) % col == 0)
			printf("\n");
	}
    sqlite3_free_table(presult);
}

void total(sqlite3 * db)
{
	int ret,row,col;
	char * sql;
	char **presult;
	char * errmsg;

	sql = "select * from mytable;";
	ret = sqlite3_get_table(db,sql,&presult,&row,&col,&errmsg);
	if(SQLITE_OK != ret)
	{
		printf("统计出错:%s\n",errmsg);
	}
	else
	{
		printf("当前通讯录有%d个联系人！\n",row);
		sqlite3_free_table(presult);
	}
}

void clear(sqlite3 * db)
{
	char * sql;
	int ret;
	char * errmsg;

	sql = "drop table mytable;";
	ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
	if(SQLITE_OK != ret)
	{
		printf("清空失败:%s\n",errmsg);
	}
	else
	{
	    create_table(db);
		printf("清空完成！\n");
	}
}

void quit()
{
	printf("谢谢使用通讯录！！\n");
	exit(0);
}

void search(sqlite3 * db)
{
	char sql[100],* errmsg;
	int ret,i;
	char n[20];
	char **presult;
	int row,col;

	printf("请输入要搜索联系人的姓名：\n");
	scanf("%s",n);

	sprintf(sql,"select * from mytable where name like '%s';",n);
	ret = sqlite3_get_table(db,sql,&presult,&row,&col,&errmsg);
	if(SQLITE_OK != ret)
	{
		printf("查询失败:%s\n",errmsg);
	}
	else
	{
		for(i = 0;i < (row + 1) * col;i++)
		printf("%-20s",presult[i]);
		if((i + 1) % col == 0)
			printf("\n");
	}
    sqlite3_free_table(presult);
}

void change(sqlite3 * db)
{
	int ret;
	char sql[100];
	char n[20];
	char loc2[30],name2[20];
	long long int num2;
	char * errmsg;

	printf("请输入要修改联系人的姓名：\n");
	scanf("%s",n);
	printf("请输入新的姓名、手机号和归属地\n");
    scanf("%s",name2);
	scanf("%lld",&num2);
	scanf("%s",loc2);
	sprintf(sql,"update mytable set name = '%s',num = %lld,loc = '%s' where name like '%s';",name2,num2,loc2,n);
	ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
	if(SQLITE_OK != ret)
	{
		printf("修改失败：%s",errmsg);
	}
	else
	{
		printf("修改成功！\n");
		return;
	}


}

void delete(sqlite3 * db)
{
	char n[20];
	char sql[100];
	int ret;
	char *errmsg;

	printf("请输入要删除联系人的姓名：\n");
	scanf("%s",n);

	sprintf(sql,"delete from mytable where name like '%s';",n);
	ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
	if(SQLITE_OK != ret)
	{
		printf("删除失败：%s",errmsg);
	}
	else
		printf("删除成功！\n");

}

void chose(int n,sqlite3 * db)
{
	switch(n)
	{
		case 1:add(db);break;
		case 2:change(db);break;
		case 3:see(db);break;
		case 4:search(db);break;
		case 5:total(db);break;
		case 6:delete(db);break;
		case 7:clear(db);break;
		case 0:quit();break;
		default:{printf("请重新输入有效数字！\n");break;}
	}
}

int main()
{
	sqlite3 * db;
	int ret;

    db = open_sqlite3(db);
	
	create_table(db);

	while(1)
	{
		int n;
		n = show();

		chose(n,db);
	}

}