基于linux数据库通讯录,sqlite3数据库之通讯录

#includesqlite3 * open_sqlite3(sqlite3 * db)

{

int ret;

ret = sqlite3_open("mytable.db",&db);

if(SQLITE_OK != ret)

{

perror("打开库失败:");

exit(-1);

}

//sqlite3_close(db);

return db;

}

void create_table(sqlite3 * db)

{

int ret;

char * sql;

char * errmsg;

sql ="create table if not exists mytable(id integer primary key,name text,num integer,loc text)";

ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);

if(SQLITE_OK != ret)

{

printf("创建通讯录失败:%s\n",errmsg);

exit(-1);

}

}

int show()

{

int n;

printf("\

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊\n\

＊ 欢迎使用通讯录！ ＊\n\

＊ 1* 添加联系人 2* 编辑联系人 ＊\n\

＊ 3* 查看联系人 4* 查找联系人 ＊\n\

＊ 5* 联系人总数 6* 删除联系人 ＊\n\

＊ 7* 清空联系人 0* 退出 ＊\n\

＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊\n");

printf("请输入要选择的功能：\n");

scanf("%d",&n);

getchar();

if(n < 0 || n > 7)

printf("输入错误！！\n");

else

return n;

}

void add(sqlite3 * db)

{

char sql[100];

char * errmsg;

char name[10];

char loc[100];

long long int num;

int ret;

while(1)

{

printf("请输入联系人的姓名、电话号码和归属地(q退出):\n");

scanf("%s",name);

if(strcmp(name,"q") == 0)

break;

scanf("%lld",&num);

scanf("%s",loc);

if(strcmp(loc,"q") == 0)

break;

sprintf(sql,"insert into mytable(id,name,num,loc) values(NULL,'%s',%lld,'%s');",name,num,loc);

ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);

if(SQLITE_OK != ret)

{

printf("添加联系人失败:%s\n",errmsg);

break;

}

}

}

void see(sqlite3 * db)

{

int ret,i;

int row,col;

char * sql;

char * errmsg;

char **presult;

sql = "select * from mytable;";

ret = sqlite3_get_table(db,sql,&presult,&row,&col,&errmsg);

if(SQLITE_OK != ret)

{

printf("查询失败:%s\n",errmsg);

}

if(row < 2)

printf("当前通讯录没有联系人！\n");

for(i = 0;i < (row + 1)*col;i++)

{

printf("%-20s",presult[i]);

if((i + 1) % col == 0)

printf("\n");

}

sqlite3_free_table(presult);

}

void total(sqlite3 * db)

{

int ret,row,col;

char * sql;

char **presult;

char * errmsg;

sql = "select * from mytable;";

ret = sqlite3_get_table(db,sql,&presult,&row,&col,&errmsg);

if(SQLITE_OK != ret)

{

printf("统计出错:%s\n",errmsg);

}

else

{

printf("当前通讯录有%d个联系人！\n",row);

sqlite3_free_table(presult);

}

}

void clear(sqlite3 * db)

{

char * sql;

int ret;

char * errmsg;

sql = "drop table mytable;";

ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);

if(SQLITE_OK != ret)

{

printf("清空失败:%s\n",errmsg);

}

else

{

create_table(db);

printf("清空完成！\n");

}

}

void quit()

{

printf("谢谢使用通讯录！！\n");

exit(0);

}

void search(sqlite3 * db)

{

char sql[100],* errmsg;

int ret,i;

char n[20];

char **presult;

int row,col;

printf("请输入要搜索联系人的姓名：\n");

scanf("%s",n);

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

ret = sqlite3_get_table(db,sql,&presult,&row,&col,&errmsg);

if(SQLITE_OK != ret)

{

printf("查询失败:%s\n",errmsg);

}

else

{

for(i = 0;i < (row + 1) * col;i++)

printf("%-20s",presult[i]);

if((i + 1) % col == 0)

printf("\n");

}

sqlite3_free_table(presult);

}

void change(sqlite3 * db)

{

int ret;

char sql[100];

char n[20];

char loc2[30],name2[20];

long long int num2;

char * errmsg;

printf("请输入要修改联系人的姓名：\n");

scanf("%s",n);

printf("请输入新的姓名、手机号和归属地\n");

scanf("%s",name2);

scanf("%lld",&num2);

scanf("%s",loc2);

sprintf(sql,"update mytable set name = '%s',num = %lld,loc = '%s' where name like '%s';",name2,num2,loc2,n);

ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);

if(SQLITE_OK != ret)

{

printf("修改失败：%s",errmsg);

}

else

{

printf("修改成功！\n");

return;

}

}

void delete(sqlite3 * db)

{

char n[20];

char sql[100];

int ret;

char *errmsg;

printf("请输入要删除联系人的姓名：\n");

scanf("%s",n);

sprintf(sql,"delete from mytable where name like '%s';",n);

ret = sqlite3_exec(db,sql,NULL,NULL,&errmsg);

if(SQLITE_OK != ret)

{

printf("删除失败：%s",errmsg);

}

else

printf("删除成功！\n");

}

void chose(int n,sqlite3 * db)

{

switch(n)

{

case 1:add(db);break;

case 2:change(db);break;

case 3:see(db);break;

case 4:search(db);break;

case 5:total(db);break;

case 6:delete(db);break;

case 7:clear(db);break;

case 0:quit();break;

default:{printf("请重新输入有效数字！\n");break;}

}

}

int main()

{

sqlite3 * db;

int ret;

db = open_sqlite3(db);

create_table(db);

while(1)

{

int n;

n = show();

chose(n,db);

}

}