通讯录（数据库版）

#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
#include <string.h>
#define N 1024

void menu()
{
	printf("＊＊＊＊＊＊＊＊＊＊欢迎使用通讯录＊＊＊＊＊＊＊＊＊＊\n");
	printf("＊                                                  ＊\n");
	printf("＊1.                添加联系人信息                  ＊\n");
	printf("＊2.                显示联系人信息                  ＊\n");
	printf("＊3.                删除联系人信息                  ＊\n");
	printf("＊4.                查询联系人信息                  ＊\n");
	printf("＊5.                修改联系人信息                  ＊\n");
	printf("＊6.                  退出通讯录                    ＊\n");
	printf("＊                                                  ＊\n");
	printf("＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊\n");
	printf("\n");
	printf("请选择您需要的操作:");
}

int creat_table(sqlite3 *db)
{
	char *errmsg = NULL;
	char *sql;

	sql = "create table if not exists student(id integer,name text,tel text);";
	if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
	{
		printf("operator falied:%s\n",errmsg);
		exit(-1);
	}
}

int delete_record(sqlite3 *db)
{
	char *errmsg = NULL;
	char sql[N] ;
	char name[N];

	printf("请输入您想删除的联系人的姓名:");
	scanf("%s",name);

//	sql = "delete from student where id = 2;";
	sprintf(sql,"delete from student where name = '%s';",name);

	if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
	{
		printf("delete error: %s\n",errmsg);
		exit(-1);
	}
}

int update_record(sqlite3 *db)
{
	char *errmsg = NULL;
	char sql[N];
	int id;
	int new_id;
	int i;
	char new_name[N];
	char new_tel[N];

	printf("请输入您想修改的联系人的id:");
	scanf("%d",id);
	printf("您想修改该联系人的 1.id\n");
	printf("                   2.姓名\n");
	printf("                   3.联系方式\n");
	
	scanf("%d",&i);
	switch(i)
	{
		case 1:
			{   
				printf("请输入新的id:");
				getchar();
				scanf("%d",new_id);
				sprintf(sql,"update student set id = %d where id = %d;",new_id,id);
			}
			break;
		case 2:
			{
				printf("请输入新的姓名:");
				getchar();
				scanf("%s",new_name);
				sprintf(sql,"update student set name = '%s' where id = %d;",new_name,id);
			}
			break;
		case 3:
			{
				printf("请输入新的联系方式:");
				getchar();
				scanf("%s",new_tel);
				sprintf(sql,"update student set tel = '%s' where id = %d;",new_tel,id);
			}
			break;
		default:
			{
				printf("输入有误,请重新输入!\n");
				update_record(db);
			}
	}
	if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
	{
		printf("delete error: %s\n",errmsg);
		exit(-1);
	}
}

int insert_record(sqlite3 *db)
{
	char *errmsg = NULL;
	char sql[N];
	char name[N];
	char tel[N];
	int id;
	int i,n;
	
	printf("请输入联系人的id,姓名，联系方式:\n");
	scanf("%d %s %s",&id,name,tel);
	sprintf(sql,"insert into student (id,name,tel) values(%d,'%s','%s');",id,name,tel);
	
	if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
	{
		printf("insert falied:%s\n",errmsg);
	}
}

int search_record(sqlite3 *db)
{
	int i;
	int nrow,ncolumn;
	char **azresult;
	char sql[N];
	char *errmsg;
	char name[N];

	printf("请输入您想查找的联系人的姓名:");
	scanf("%s",name);

	sprintf(sql,"select id,name,tel from student where name = '%s';",name);

	if(SQLITE_OK != sqlite3_get_table(db,sql,&azresult,&nrow,&ncolumn,&errmsg))
	{
		printf("selset error:%s\n",errmsg);
	}

	for(i = 0;i < (nrow+1)*ncolumn;i++)
	{
		printf("%10s",azresult[i]);

		if((i+1)%3 == 0)
		{
			printf("\n");
		}
	}
	sqlite3_free_table(azresult);
}

int inquire_nocb(sqlite3 *db)
{
	int i;
	int nrow,ncolumn;
	char **azresult;
	char *sql;
	char *errmsg;

	sql = "select * from student;";

	if(SQLITE_OK != sqlite3_get_table(db,sql,&azresult,&nrow,&ncolumn,&errmsg))
	{
		printf("display error:%s\n",errmsg);
	}

	for(i = 0;i < (nrow+1)*ncolumn;i++)
	{
		printf("%10s",azresult[i]);

		if((i+1)%3 == 0)
		{
			printf("\n");
		}
	}
	sqlite3_free_table(azresult);
}

void switch_menu(sqlite3 *db)
{
	int i;
	int n;
	int j;

	scanf("%d",&i);
	switch(i)
	{
		case 1:
			{
				insert_record(db);
				printf("添加成功!\n");
				for(j = 0;;j++)
				{
					printf("是否继续添加联系人?(1/0):\n");
					scanf("%d",&n);

					if(1 == n)
					{
						insert_record(db);
						printf("添加成功!\n");
					}
					else
					{
						system("clear");
					  	menu();
						switch_menu(db);
						break;
					}
				}
			}
			break;
		case 2:
			{
				inquire_nocb(db);
				printf("\n");
				printf("请选择您接下来的操作:");
				switch_menu(db);
			}
			break;
		case 3:
			{
				delete_record(db);  
				printf("删除成功!\n");
				printf("\n");
				printf("请选择您接下来的操作:");
				switch_menu(db);
			}
			break;
		case 4:
			{
				search_record(db);
				printf("\n");
				printf("请选择您接下来的操作:");
				switch_menu(db);
			}
			break;
		case 5:
			{
				update_record(db);
				printf("修改成功!\n");
				printf("请选择你您接下来的操作:");
				switch_menu(db);
			}
			break;
		case 6:
			{
				system("clear");
				printf("＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊\n");
				printf("＊                                ＊\n");
				printf("＊        谢谢使用通讯录          ＊\n");
				printf("＊                                ＊\n");
				printf("＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊\n");
				printf("\n");
			}
			break;
		default:
			{
				printf("您的选择有误,请重新输入!\n");
				switch_menu(db);
			}
			break;

	}
}

int main()
{
	sqlite3 * db = NULL;
	int ret;
	
	ret = sqlite3_open("mydatabase.db",&db);
	if(ret != SQLITE_OK)
	{
		perror("sqlite open");
		exit(1);
	}
	
	system("clear");
	menu();
	creat_table(db);
	switch_menu(db);

    return 0;
}