C 通讯录数据库版

1. 
   #include <stdio.h>  
   #include <sqlite3.h>  
   #include <stdlib.h>  
   #include <string.h>  
   #define N 1024  
   
   
   void menu()
   {
   
   
   printf("|-----------------------------欢迎使用煜D通讯录管理系统---------------------------|\n");
   printf("|-----------------------------输入1~5选择功能 输入q退出---------------------------|\n");
   printf("|-----------------------------(1)添加联系人---------------------------------------|\n");
   printf("|-----------------------------(2)查询联系人---------------------------------------|\n");
   printf("|-----------------------------(3)修改联系人---------------------------------------|\n");
   printf("|-----------------------------(4)删除联系人---------------------------------------|\n");
   printf("|-----------------------------(5)显示联系人---------------------------------------|\n");
   printf("|-----------------------------(q)退出---------------------------------------------|\n");
   }
   
   
   int creat_table(sqlite3 *db)
   {
   char *errmsg = NULL;
   char *sql;
   
   
   sql = "create table if not exists student(id integer,name text,tel text);";
   if (SQLITE_OK != sqlite3_exec(db, sql, NULL, NULL, &errmsg))
   {
   printf("operator falied:%s\n", errmsg);
   exit(-1);
   }
   }
   
   
   int delete_record(sqlite3 *db)
   {
   char *errmsg = NULL;
   char sql[N];
   char name[N];
   
   
   printf("请输入您想删除的联系人的姓名:");
   scanf("%s", name);
   
   
   //  sql = "delete from student where id = 2;";  
   sprintf(sql, "delete from student where name = '%s';", name);
   
   
   if (SQLITE_OK != sqlite3_exec(db, sql, NULL, NULL, &errmsg))
   {
   printf("delete error: %s\n", errmsg);
   exit(-1);
   }
   }
   
   
   int update_record(sqlite3 *db)
   {
   char *errmsg = NULL;
   char sql[N];
   int id;
   int new_id;
   int i;
   char new_name[N];
   char new_tel[N];
   
   
   printf("请输入您想修改的联系人的id:");
   scanf("%d", id);
   printf("您想修改该联系人的 1.id\n");
   printf("                   2.姓名\n");
   printf("                   3.联系方式\n");
   
   
   scanf("%d", &i);
   switch (i)
   {
   case 1:
   {
   printf("请输入新的id:");
   getchar();
   scanf("%d", new_id);
   sprintf(sql, "update student set id = %d where id = %d;", new_id, id);
   }
   break;
   case 2:
   {
   printf("请输入新的姓名:");
   getchar();
   scanf("%s", new_name);
   sprintf(sql, "update student set name = '%s' where id = %d;", new_name, id);
   }
   break;
   case 3:
   {
   printf("请输入新的联系方式:");
   getchar();
   scanf("%s", new_tel);
   sprintf(sql, "update student set tel = '%s' where id = %d;", new_tel, id);
   }
   break;
   default:
   {
   printf("输入有误,请重新输入!\n");
   update_record(db);
   }
   }
   if (SQLITE_OK != sqlite3_exec(db, sql, NULL, NULL, &errmsg))
   {
   printf("delete error: %s\n", errmsg);
   exit(-1);
   }
   }
   
   
   int insert_record(sqlite3 *db)
   {
   char *errmsg = NULL;
   char sql[N];
   char name[N];
   char tel[N];
   int id;
   int i, n;
   
   
   printf("请输入联系人的id,姓名，联系方式:\n");
   scanf("%d %s %s", &id, name, tel);
   sprintf(sql, "insert into student (id,name,tel) values(%d,'%s','%s');", id, name, tel);
   
   
   if (SQLITE_OK != sqlite3_exec(db, sql, NULL, NULL, &errmsg))
   {
   printf("insert falied:%s\n", errmsg);
   }
   }
   
   
   int search_record(sqlite3 *db)
   {
   int i;
   int nrow, ncolumn;
   char **azresult;
   char sql[N];
   char *errmsg;
   char name[N];
   
   
   printf("请输入您想查找的联系人的姓名:");
   scanf("%s", name);
   
   
   sprintf(sql, "select id,name,tel from student where name = '%s';", name);
   
   
   if (SQLITE_OK != sqlite3_get_table(db, sql, &azresult, &nrow, &ncolumn, &errmsg))
   {
   printf("selset error:%s\n", errmsg);
   }
   
   
   for (i = 0; i < (nrow + 1)*ncolumn; i++)
   {
   printf("%10s", azresult[i]);
   
   
   if ((i + 1) % 3 == 0)
   {
   printf("\n");
   }
   }
   sqlite3_free_table(azresult);
   }
   
   
   int inquire_nocb(sqlite3 *db)
   {
   int i;
   int nrow, ncolumn;
   char **azresult;
   char *sql;
   char *errmsg;
   
   
   sql = "select * from student;";
   
   
   if (SQLITE_OK != sqlite3_get_table(db, sql, &azresult, &nrow, &ncolumn, &errmsg))
   {
   printf("display error:%s\n", errmsg);
   }
   
   
   for (i = 0; i < (nrow + 1)*ncolumn; i++)
   {
   printf("%10s", azresult[i]);
   
   
   if ((i + 1) % 3 == 0)
   {
   printf("\n");
   }
   }
   sqlite3_free_table(azresult);
   }
   
   
   void switch_menu(sqlite3 *db)
   {
   int i;
   int n;
   int j;
   
   
   scanf("%d", &i);
   switch (i)
   {
   case 1:
   {
   insert_record(db);
   printf("添加成功!\n");
   for (j = 0;; j++)
   {
   printf("是否继续添加联系人?(1/0):\n");
   scanf("%d", &n);
   
   
   if (1 == n)
   {
   insert_record(db);
   printf("添加成功!\n");
   }
   else
   {
   system("clear");
   menu();
   switch_menu(db);
   break;
   }
   }
   }
   break;
   case 2:
   {
   inquire_nocb(db);
   printf("\n");
   printf("请选择您接下来的操作:");
   switch_menu(db);
   }
   break;
   case 3:
   {
   delete_record(db);
   printf("删除成功!\n");
   printf("\n");
   printf("请选择您接下来的操作:");
   switch_menu(db);
   }
   break;
   case 4:
   {
   search_record(db);
   printf("\n");
   printf("请选择您接下来的操作:");
   switch_menu(db);
   }
   break;
   case 5:
   {
   update_record(db);
   printf("修改成功!\n");
   printf("请选择你您接下来的操作:");
   switch_menu(db);
   }
   break;
   case 6:
   {
   system("clear");
   printf("＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊\n");
   printf("＊                                ＊\n");
   printf("＊        谢谢使用通讯录          ＊\n");
   printf("＊                                ＊\n");
   printf("＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊\n");
   printf("\n");
   }
   break;
   default:
   {
   printf("您的选择有误,请重新输入!\n");
   switch_menu(db);
   }
   break;
   
   
   }
   }
   
   
   int main()
   {
   sqlite3 * db = NULL;
   int ret;
   
   
   ret = sqlite3_open("mydatabase.db", &db);
   if (ret != SQLITE_OK)
   {
   perror("sqlite open");
   exit(1);
   }
   
   
   system("clear");
   menu();
   creat_table(db);
   switch_menu(db);
   
   
   return 0;
   }