sqlite3数据库通讯录

#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("	|*                                            *|\n");
	printf("	|*   	 功能选择：1.添加好友信息             *|\n");
	printf("	|*                 2.查看好友信息             *|\n");
	printf("	|*                 3.删除好友信息             *|\n");
    printf("	|*                 4.搜索好友信息             *|\n");
	printf("	|*                 5.退出                     *|\n");
	printf("	|*                                            *|\n");
	printf("	|**********************************************|\n");
	printf("	|     请输入你想要实现的功能：                *|\n");
	printf("	|     1.添加 2.查看 3.删除 4.搜索 5.退出      *|\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("请输入你要实现的功能(1-5)：\n");
    scanf("%c",&action);
 
    switch(action)
    {
        case '1': insert_record(db);	        
		  choose(db);
	          break;
        case '2': inquire_nocb(db);	            
	          choose(db);
	          break;
        case '3': delete_record(db);	    
	          choose(db);
                  break;
		case '4': search_record(db);   
                  choose(db);
	          break;
        case '5': 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;
 
}