数据库版通讯录（上周作业）

/*****************************************************
copyright (C), 2014-2015, Lighting Studio. Co.,     Ltd. 
File name：
Author：Jerey_Jobs    Version:0.1    Date: 
Description：
Funcion List: 
*****************************************************/


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


int create_table(sqlite3 *db)//创建数据表
{
char *errmsg = NULL;
char *sql;
sql = "create table if not exists address_book (no integer primary key,name text,age integer,sex text,address text);";
if (SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("operator failed:%s\n",errmsg);
exit(-1);
}
}
int insert_record(sqlite3 *db,int num)//该函数用于录入
{
static int i = 1;
static tmp = 0;
tmp+=num;
char *errmsg = NULL;
int no;//编号
char sql[100];
char name[20];
char age[2];
char sex[2];
char address[50];
for (i ; i <= tmp ; i++)
{
printf("第%d位",i);
no = i;
printf("姓名:\n");
scanf("%s",name);
printf("年龄:\n");
scanf("%s",age);
printf("性别:\n");
scanf("%s",sex);
printf("地址:\n");
scanf("%s",address);
sprintf(sql,"insert into address_book(no,name,age,sex,address) values (%d,'%s','%s','%s','%s')",no,name,age,sex,address);
if (SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("insert failed:%s\n",errmsg);
exit(-1);
}
}
menu(db);
}
int displayed(void *para,int n_col,char **col_value,char **col_name)//回调函数
{
int i;
printf("总列数为:%d\n",n_col);
for ( i = 0 ; i < n_col ; i++)
{
printf("字段名:%s-->数值:%s\n",col_name[i],col_value[i]);
}
printf("----------------------------------------------\n");
return 0;
}
int find_people(sqlite3 *db)//该函数用于查询,由于有select所以要有回调函数
{
char *errmsg = NULL;
char sql[100];
int num;
printf("请输入需要查找的编号:\n");
scanf("%d",&num);
sprintf(sql,"select * from address_book where no= '%d'",num);
    if (SQLITE_OK != sqlite3_exec(db,sql,displayed,NULL,&errmsg))
{
printf("find failed:%s\n",errmsg);
exit(-1);
}
}


int inquire_all(sqlite3 *db)//该函数用于查看全部联系人
{
char *errmsg = NULL;
char *sql;
sql = "select *from address_book";
    if (SQLITE_OK != sqlite3_exec(db,sql,displayed,NULL,&errmsg))
{
printf("find failed:%s\n",errmsg);
exit(-1);
}
menu(db);
}
int del_people(sqlite3 *db)
{
char *errmsg;
int num;
char sql[100];
printf("请决定你要删除哪位联系人:\n");
scanf("%d",&num);
sprintf(sql,"delete from address_book where no = %d;",num);
if (SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("delete failed:%s\n",errmsg);
exit(-1);
}
menu(db);
}
int change_people(sqlite3 *db)
{
char *errmsg=NULL;
int num;
char name[20];
char age[2];
char sex[2];
char address[50];
char a;
char sql[100];
printf("请决定你要更改哪位联系人:\n");
scanf("%d",&num);
printf("请选择需要修改的项目:\n");
printf("1.姓名 2.年龄 3.性别 4.住址\n");
scanf(" %c",&a);
switch(a)
{
case '1':
   printf("请输入姓名:\n");
   scanf("%s",name);
           sprintf(sql,"update address_book set name = '%s' where no = %d;",name,num);
  break;
   case '2':
   printf("请输入年龄:\n");
   scanf("%s",sex);
                sprintf(sql,"update address_book set age = '%s' where no = %d;",age,num);
break;
case '3':
printf("请输入性别:\n");
                sprintf(sql,"update address_book set sex = '%s' where no = %d;",sex,num);
break;
   case '4':
printf("请输入地址:\n");
                sprintf(sql,"update address_book set address = '%s'where no = %d;",address,num);
break;
}
    if (SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
    printf("change failed:%s\n",errmsg);
    exit(-1);
    }
menu(db);
}
int iquire_nocb(sqlite3 *db)
{
int row,column;
char **azresult;//用于存放结果
char *sql;
char *errmsg;
int i;
sql = "select *from address_book;";
if (SQLITE_OK != sqlite3_get_table(db,sql,&azresult,&row,&column,&errmsg))
{
printf("operator error:%s\n",errmsg);
exit(-1);
}
printf("行:%d ,列:%d\n",row,column);
for ( i = 0 ; i < (row+1)*column ; i++)
{
printf("%10s",azresult[i]);
if((i+1)%column == 0)
{
printf("\n");
}
}
sqlite3_free_table(azresult);//由于相当于调用了malloc函数,所以必须释放空间
    menu(db);
}
int menu(sqlite3 *db)
{
printf("/***************欢迎使用通信录****************/\n");
printf("/*               1.添加联系人                */\n");
printf("/*               2.查询联系人                */\n");
printf("/*               3.删除联系人                */\n");
printf("/*               4.查看全部联系人            */\n");
printf("/*               5.修改联系人                */\n");
printf("/*               6.退出                      */\n");
printf("/*********************************************/\n");
char num;
int in;
printf("请输入你所需要的操作:\n");
scanf(" %c",&num);
switch(num)
{
case '1': 
       printf("请输入要录入的用户数量\n");
       scanf("%d",&in);
insert_record(db,in);
break;
case '2':
find_people(db);
    break;
case '3':
del_people(db);
break;
case '4': 
           // inquire_all(db);//该函数用于查看全部联系人
            iquire_nocb(db);
break;
case '5':
   change_people(db);
case '6': return;
default: 
menu(db);
break;
}
return 0;
}


int main()
{
    sqlite3 *db = NULL;//句柄
int ret;//定义函数返回值
    ret = sqlite3_open("mydatabase.db",&db);
if (ret != SQLITE_OK)//错误
{
perror("sqlite open");
exit(-1);
}
else
{
printf("u have opened a sqlites database successfully!\n");
}
create_table(db);//从数据库中创建表格
menu(db);


    return 0;
}