11192

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


void create_table(sqlite3 *db);  //创建表
void insert_record(sqlite3 *db); //插入
void contact_list(sqlite3 *db);  //显示联系人
void search_contact();
void delete_contact();
void update_contact();
void menu_face();
void menu();


int main()
{
sqlite3 *db == NULL;
int ret;
ret = sqlite3_open("datasystem.db",&db);


if(ret != SQLITE_OK)
{
perror("sqlite open!\n");
exit(-1);
}
else
{
printf("You have opened a sqlite3 database successfully!\n");
}


create_table(db);
menu(db);
sqlite3_close(db);
return 0;
}


void create_table(sqlite3 *db)
{
char *errmsg = NULL;
char *sql;
sql = "create table if not exists mytable (id integer primary key,name text,phone text,addr text);";
if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("Create table error! %s\n",errmsg);
}
}


void menu_face()
{
printf("\t\t*****************************************************\n");
printf("\t\t                   通讯录管理系统                    \n");
printf("\t\t*****************************************************\n");
printf("\t\t   1.添加联系人           2.显示联系人               \n");
printf("\t\t   3.查询联系人           4.删除联系人               \n");
printf("\t\t   5.更新联系人           6.退出系统                 \n");
printf("\t\t*****************************************************\n");
printf("\t\t请输入:");
}


void menu(sqlite3 *db)
{
int ret;
int choose;
system("clear");
menu_face();
scanf("%d",&b);


switch(choose)
{
case 1:
{
insert_record(db);
printf("insert success!");
break;
}
case 2:
{
contact_list(db);
break;
}
case 3:
{
search_contact(db);
break;
}
case 4:
{
delete_contact(db);
break;
}
case 5:
{
update_contact(db);
break;
}
case 6:
{
printf("Exit success!\n");
exit(0);
break;
}
default:
{
printf("please input right order!\n");
break;
}
}
}




void insert_record(sqlite3 *db)
{
char sql[100];
char *errmsg = NULL;
int id;
char name[20];
char phone[20];
char addr[30];


system("clear");
printf("请输入联系人\n");


printf("请输入ID：\n");
scanf("%d\n",id);




printf("请输入名字: \n");
scanf("%s\n",name);


printf("请输入电话号码:\n");
scanf("%s\n",phone);


printf("请输入地址:\n");
scanf("%s\n",addr);


sprintf(sql,"insert into mytable values(%d,'%s','%s','%s');",id,name,phone,addr);


if(SQLITE_OK !=sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("insert record error! %s\n",errmsg);
}
}












 void search_contact(sqlite3 *db)
 {
char name[20];
char sql[100];
char *errmsg;
int nrow,ncolumn;
cahr **azresult;
int i;
int ret;
system("clear");
printf("查询联系人\n");


printf("查询名字\n");
scanf("%s",name);


sprintf(sql,"select * from mytable where name ='%s'",name);


if(SQLITE_OK != sqlite3_get_table(db,sql,&azresult,&nrow,&ncolumn,&errmsg))
{
printf("search failed:%s\n",errmsg);
exit(-1);
}


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


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


system("clear");
printf("删除联系人");


scanf("%s",name);


sprintf(sql,"delete from mytable where name = '%s'",name);
ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);


if(ret != SQLITE_OK)
{
printf("delete contact error:%s\n",errmsg);
exit(-1);
}
 }


 void update_contact(sqlite *db)
 {
char sql[100];
char name[20];
char phone[20];
char addr[30];
char *errmsg = NULL;
int ret;
system("clear");
printf("更新联系人\n");


printf("name:");
scanf("%s",name);
sprintf(sql."select *from addr where name ='%s'",name);
ret = sqlite3_exec(db,sql,contact_list,NULL,&errmsg);
printf("新号码:");
scanf("%s",phone);


printf("新地址:");
scanf("%s",addr);


sprintf(sql,"update mytable values('%s','%s','%s');",name,phone,addr);
sprintf(sql,"update mytable set phone = '%s'where name = '%s';",addr,name);
sprintf(sql,"update mytable set addr = '%s'where name = '%s';",addr,name);


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