Linux c 通讯录+数据库

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

void interface()
{
    system("clear");

    printf("\n");
    printf("\n");
    printf("          |***********************************************|\n");
    printf("          |*          多 功 能 电 子 通 讯 录            *|\n");
    printf("          |*                                             *|\n");
    printf("          |*                              版本号：V_3.0.0*|\n");
    printf("          |***********************************************|\n");
    printf("          |*                                             *|\n");
    printf("          |*      功能选择:   1. 添加好友信息            *|\n");
    printf("          |*                  2. 查看好友信息            *|\n");
    printf("          |*                  3. 删除好友信息            *|\n");
    printf("          |*                  4. 搜索好友信息            *|\n");
    printf("          |*                                             *|\n");
    printf("          |***********************************************|\n");
    printf("          |*      请输入你想要实现的功能：               *|\n");
    printf("          |*                                             *|\n");
    printf("          |*       0添加 1查看 2删除 3搜索 4退出         *|\n");
    printf("          |***********************************************|\n");

}

void create_table(sqlite3 *db)
{
    char *sql= "create table if not exists mytable(id integer primary key, name text,number)";
    char *errmsg;
    int ret;

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

void insert_record(sqlite3 *db)
{   
    char sql[100];
    char *errmsg;
    int ret;
    char name[20];
    char number[20];
    char flag;
    int id = 1;

    printf("please input id, name, number:");
    scanf("%d %s %s",&id,name,number);
    sprintf(sql,"insert into mytable(id,name,number) values(%d,'%s','%s');",id,name,number);
    
    ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);
    if(ret)
    {
       	printf("insert record error:%s\n",errmsg);
	exit(-1);
    }
    getchar();
    //判断是否继续输入
    printf("continue insert next member？Y / N   ");
    scanf("%c",&flag);
    if(flag == 'Y' || flag == 'y')
    {
       	insert_record(db);        
    }
    else if(flag == 'N' || flag == 'n')
    {   
       	printf("please press ENTER to return\n");
       	getchar();
	getchar();
       	interface(db);
    }

}

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

    sql = "select * from mytable";
    ret = sqlite3_get_table(db,sql,&azresult,&nrow,&ncolumn,&errmsg);
    if(ret)
    {
        printf("get table error:%s\n",errmsg);
	exit(-1);
    }

    for(i = ncolumn; i <(nrow + 1) *ncolumn; i++)
    {
        printf("%20s",azresult[i]);
	if((i + 1) % ncolumn == 0)
	{
	    printf("\n");
	}
    }

    sqlite3_free_table(azresult);


    printf("please press ENTER to return\n");
    getchar();
	 getchar();
    interface(db);
    
}

void delete_record(sqlite3 *db)
{
    char sql[50];
    char *errmsg;
    int ret;
    char name[20];
    char flag;
    char **azresult;
    int i;
    int nrow;
    int ncolumn;

   printf("please input the name to delete\n");
   scanf("%s",name);
   sprintf(sql,"select id,name,number  from mytable where name = '%s';",name);
   sqlite3_get_table(db,sql,&azresult,&nrow,&ncolumn,&errmsg);
   printf("you detele the following member.\n");
   for(i = ncolumn; i <(nrow + 1) *ncolumn; i++)
   {
        printf("%20s",azresult[i]);
	if((i + 1) % ncolumn == 0)
	{
	    printf("\n"); 
	}
   }
   memset(sql,0,sizeof(sql));
   sprintf(sql,"delete from mytable where name = '%s';",name);

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


    getchar();
    //判断是否继续删除
    printf("continue delete next member？Y / N   ");
    scanf("%c",&flag);
    if(flag == 'Y' || flag == 'y')
    {
       	delete_record(db);        
    }
    else if(flag == 'N' || flag == 'n')
    {   
        printf("\nAfter deleting:\n");
        inquire_nocb(db);
    }

}

void search_record(sqlite3 *db)
{
    char sql[100];
    char *errmsg;
    int ret;
    char name[20];
    char flag;
    char **azresult;
    int i;
    int nrow;
    int ncolumn;

    printf("please input the name to search\n");
    scanf("%s",name);
    sprintf(sql,"select id,name,number  from mytable where name = '%s';",name);
    sqlite3_get_table(db,sql,&azresult,&nrow,&ncolumn,&errmsg);
    for(i = ncolumn; i <(nrow + 1) *ncolumn; i++)
    {
        printf("%20s",azresult[i]);
	if((i + 1) % ncolumn == 0)
	{
	    printf("\n"); 
	}
 }

    getchar();
    //判断是否查找
    printf("continue search ？Y / N   ");
    scanf("%c",&flag);
    if(flag == 'Y' || flag == 'y')
    {
       	search_record(db);        
    }
    else if(flag == 'N' || flag == 'n')
    {   
       	printf("please press ENTER to return\n");
       	getchar();
        getchar();
       	interface(db);
    }

}



void choose(sqlite3 *db)
{
    char action;
    printf("请输入你要实现的功能(0-3)：\n");
    scanf("%c",&action);

    switch(action)
    {
        case '0': insert_record(db);	        
		  choose(db);
	          break;
        case '1': inquire_nocb(db);	            
	          choose(db);
	          break;
        case '2': delete_record(db);	    
	          choose(db);
                  break;
	case '3': search_record(db);   
                  choose(db);
	          break;
        case '4': sqlite3_close(db);
                  printf("bye bye!\n");
                  exit(1);
                  break;
	default:  printf("输入指令有误，请重新输入！\n");
	          choose(db);	 	          	     
     }
}

int main()
{
    sqlite3 *db;
    int rc;
    rc = sqlite3_open("mydatabase.db",&db);
    if(rc)
    {
        printf("can not open database!\n");
	exit(-1);
    }
    else
    {
        printf("open database success!\n");
    }

    interface();
    create_table(db);

    choose(db);

    return 0;

}