#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 contact_list(sqlite3 *db)
{
int nrow,ncolumn;
char **azresult;
char *sql;
char *errmsg = NULL;
int i;
sql = "select *from mytable;";
if(SQLITE_OK!=sqlite3_get_table(db,sql,&azresult,&nrow,&ncolumn,&errmsg))
{
printf("contact error! %s\n",errmsg);
exit(-1);
}
printf("row:%d\n column:%d\n",nrow.ncolumn);
for(i=0;i<(nrow+1)*ncolumn;i++)
{
printf("%10s",azreult[i]);
if((i+1)%ncolumn == 0)
{
printf("\n");
}
}
sqlite_free_table(azresult);
}
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);
}
}
#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 contact_list(sqlite3 *db)
{
int nrow,ncolumn;
char **azresult;
char *sql;
char *errmsg = NULL;
int i;
sql = "select *from mytable;";
if(SQLITE_OK!=sqlite3_get_table(db,sql,&azresult,&nrow,&ncolumn,&errmsg))
{
printf("contact error! %s\n",errmsg);
exit(-1);
}
printf("row:%d\n column:%d\n",nrow.ncolumn);
for(i=0;i<(nrow+1)*ncolumn;i++)
{
printf("%10s",azreult[i]);
if((i+1)%ncolumn == 0)
{
printf("\n");
}
}
sqlite_free_table(azresult);
}
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);
}
}