本来以为可以很快写完的,但是断断续续写了一天。。。。。。
这也是自己第一次写这么长的代码,很开心,希望能和大家分享我的代码,亲测运行成功
/*****************************************************
copyright (C), 2014-2015, Lighting Studio. Co., Ltd.
File name:
Author:Jerey_Jobs Version:0.1 Date:
Description:
Funcion List:
*****************************************************/
#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
#include <string.h>
#define N 15
int creat_table(sqlite3 *db)//第二步肯定是创建表了
{
char *sql;
char *errmsg;
sql = "create table if not exists contact (id integer primary key,name text,sex text,number text,address text);";
if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))//判断是否成功成功返回SQLITE_OK
{
printf("fail:%s\n");
printf("\n");
exit(-1);
}
}
int insert_table(sqlite3 *db)//第三步向表中插入数据
{
char sql[100];
char *errmsg;
int id;
int i;
int n;
int flag = 1;
char ch;
char name[N];
char sex[N];
char number[N];
char address[N];
while(flag)
{
printf("input the number of contact you want to add!\n");//现插入几个数据
scanf("%d",&n);
getchar();
for(i = 0; i < n; i++)
{
printf("input id:\n");
scanf("%d",&id);
getchar();
printf("input name:\n");
scanf("%s",name);
getchar();
printf("input sex:\n");
scanf("%s",sex);
getchar();
printf("input number:\n");
scanf("%s",number);
getchar();
printf("input address:\n");
scanf("%s",address);
getchar();
sprintf(sql,"insert into contact (id,name,sex,number,address) values (%d,'%s','%s','%s','%s');",id,name,sex,number,address);
if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))//判断是否插入成功成功返回SQLITE_OK
{
printf("fail:%s\n",errmsg);
printf("\n");
exit(-1);
}
}
printf("continue insert[Y/N]?\n");//判断是否继续插入
scanf("%c",&ch);
getchar();
if((ch == 'y') || (ch == 'Y'))
{
flag = 1;
}
else
{
flag = 0;
}
}
}
int display(sqlite3 *db)//查看整个表的数据
{
char *sql;
char *errmsg;
char **aresult;
int i;
int nrow;
int ncol;
sql = "select * from contact;";
if(SQLITE_OK != sqlite3_get_table(db,sql,&aresult,&nrow,&ncol,&errmsg))//判断sqlite3_get_table是否运用成功,成功返回SQLITE_OK
{
printf("fail:%s\n",errmsg);
printf("\n");
exit(-1);
}
for(i = 0; i < (nrow + 1) * ncol; i++)//将表中的数据打印出来
{
printf("%-12s",aresult[i]);
if((i + 1) % ncol == 0)
{
printf("\n");
}
}
sqlite3_free_table(aresult);//释放aresult
}
int delete_table(sqlite3 *db)//删除表中数据
{
char sql[100];
char *errmsg;
char **aresult;
char name[N];
char ch;
int i;
int id;
int nrow;
int ncol;
int flag = 1;
while(flag)
{
printf("input the name you want to delete!\n");//输入想要删除的名字
scanf("%s",name);
getchar();
sprintf(sql,"select * from contact where name = '%s';",name);
if(SQLITE_OK != sqlite3_get_table(db,sql,&aresult,&nrow,&ncol,&errmsg))
{
printf("fail:%s\n",errmsg);
printf("\n");
exit(-1);
}
if(nrow == 0)//如果没有此人
{
printf("no such person!\n");
}
else
if(nrow == 1)//若只有一个人,直接删除
{
memset(sql,0,sizeof(sql));
sprintf(sql,"delete from contact where name = '%s';",name);
if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("fail:%s\n",errmsg);
printf("\n");
exit(-1);
}
}
else//如果有多个人,先把他们的信息打印出来,然后输入想要删除的id
{
for(i = 0; i < (nrow + 1) * ncol; i++)
{
printf("%-12s",aresult[i]);
if((i + 1) % ncol == 0)
{
printf("\n");
}
}
memset(sql,0,sizeof(sql));
printf("input the id you want to delete!\n");
scanf("%d",&id);
getchar();
sprintf(sql,"delete from contact where id = %d",id);
if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("fail:%s\n");
printf("\n");
exit(-1);
}
}
printf("delete again[Y/N]?");//询问是否继续删除
scanf("%c",&ch);
getchar();
if((ch == 'y') || (ch == 'Y'))
{
flag = 1;
}
else
{
flag = 0;
}
}
sqlite3_free_table(aresult);//释放aresult,防止内存泄露,下面不再重复注释
}
int find_name(sqlite3 *db)//按姓名查找
{
char sql[100];
char **aresult;
char *errmsg;
char name[N];
int i;
int nrow;
int ncol;
printf("input the name you want to find!\n");
scanf("%s",name);
getchar();
sprintf(sql,"select * from contact where name = '%s';",name);
if(SQLITE_OK != sqlite3_get_table(db,sql,&aresult,&nrow,&ncol,&errmsg))
{
printf("fail:%s\n");
printf("\n");
exit(-1);
}
for(i = 0; i < (nrow + 1) * (ncol); i++)//打印此人信息
{
printf("%-12s",aresult[i]);
if((i + 1) % ncol == 0)
{
printf("\n");
}
}
sqlite3_free_table(aresult);
}
int find_number(sqlite3 *db)//按号码查找
{
char sql[100];
char **aresult;
char *errmsg;
char number[N];
int i;
int nrow;
int ncol;
printf("input the number you want to find!\n");
scanf("%s",number);
getchar();
sprintf(sql,"select * from contact where number = '%s';",number);
if(SQLITE_OK != sqlite3_get_table(db,sql,&aresult,&nrow,&ncol,&errmsg))
{
printf("fail:%s\n");
printf("\n");
exit(-1);
}
for(i = 0; i < (nrow + 1) * (ncol); i++)
{
printf("%-12s",aresult[i]);
if((i + 1) % ncol == 0)
{
printf("\n");
}
}
}
int find_addr(sqlite3 *db)//按地址查找
{
char sql[100];
char **aresult;
char *errmsg;
char address[N];
int i;
int nrow;
int ncol;
printf("input the address you want to find!\n");
scanf("%s",address);
getchar();
sprintf(sql,"select * from contact where address = '%s';",address);
if(SQLITE_OK != sqlite3_get_table(db,sql,&aresult,&nrow,&ncol,&errmsg))
{
printf("fail:%s\n");
printf("\n");
exit(-1);
}
for(i = 0; i < (nrow + 1) * (ncol); i++)
{
printf("%-8s",aresult[i]);
if((i + 1) % ncol == 0)
{
printf("\n");
}
}
}
int search_table(sqlite3 *db)//查找函数主函数
{
int n;
int flag = 1;
char ch;
while(flag)
{
printf("\n1:search by name\n2:search by number\n3:search by address\n");
printf("input your choic!\n");
scanf("%d",&n);
getchar();
switch(n)
{
case 1:
find_name(db);
break;
case 2:
find_number(db);
break;
case 3:
find_addr(db);
break;
default:
break;
}
printf("search again[Y/N]?\n");//询问是否继续查找
scanf("%c",&ch);
getchar();
if((ch == 'y') || (ch == 'Y'))
{
flag = 1;
}
else
{
flag = 0;
}
}
}
int updata_name(sqlite3 *db,int n)//修改姓名
{
char *errmsg;
char **aresult;
char sql[100];
char sql2[100];
char name[N];
int i;
int nrow;
int ncol;
sprintf (sql,"select * from contact where id = %d;",n);//这边我认为非常重要。因为你不能把sql写成sql = "select * from contact where id = n";这个n只能通过sprintf函数写进去,不然他就一直报错,下面不再重复注释
if(SQLITE_OK != sqlite3_get_table(db,sql,&aresult,&nrow,&ncol,&errmsg))
{
printf("fail: %s\n",errmsg);
printf("\n");
exit(-1);
}
for(i = 0; i < (nrow + 1) * ncol; i++)
{
printf("%-12s",aresult[i]);
if((i + 1) % ncol == 0)
{
printf("\n");
}
}
printf("input the name you want to updata!\n");//这边才开始修改姓名,上面是先打印原先的信息
scanf("%s",name);
getchar();
sprintf(sql2,"update contact set name = '%s' where id = %d;",name,n);
if(SQLITE_OK != sqlite3_exec(db,sql2,NULL,NULL,&errmsg))
{
printf("fail: %s\n");
printf("\n");
exit(0);
}
sqlite3_free_table(aresult);
}
int updata_sex(sqlite3 *db,int n)//修改性别
{
char *errmsg;
char **aresult;
char sql[100];
char sql2[100];
char sex[N];
int i;
int nrow;
int ncol;
sprintf(sql,"select * from contact where id = %d;",n);
if(SQLITE_OK != sqlite3_get_table(db,sql,&aresult,&nrow,&ncol,&errmsg))
{
printf("fail: %s\n",errmsg);
printf("\n");
exit(-1);
}
for(i = 0; i < (nrow + 1) * ncol; i++)
{
printf("%-12s",aresult[i]);
if((i + 1) % ncol == 0)
{
printf("\n");
}
}
printf("input the sex you want to updata!\n");//开始修改性别
scanf("%s",sex);
getchar();
sprintf(sql2,"update contact set sex = '%s' where id = %d;",sex,n);
if(SQLITE_OK != sqlite3_exec(db,sql2,NULL,NULL,&errmsg))
{
printf("fail: %s\n");
printf("\n");
exit(0);
}
sqlite3_free_table(aresult);
}
int updata_number(sqlite3 *db,int n)//修改号码
{
char *errmsg;
char **aresult;
char *sql;
char sql2[100];
char number[N];
int i;
int nrow;
int ncol;
sprintf(sql,"select * from contact where id = %d;",n);
if(SQLITE_OK != sqlite3_get_table(db,sql,&aresult,&nrow,&ncol,&errmsg))
{
printf("fail: %s\n",errmsg);
printf("\n");
exit(-1);
}
for(i = 0; i < (nrow + 1) * ncol; i++)
{
printf("%-12s",aresult[i]);
if((i + 1) % ncol == 0)
{
printf("\n");
}
}
printf("input the number you want to update!\n");//这边开始修改号码
scanf("%s",number);
getchar();
sprintf(sql2,"update contact set number = '%s' where id = %d;",number,n);
if(SQLITE_OK != sqlite3_exec(db,sql2,NULL,NULL,&errmsg))
{
printf("fail: %s\n");
printf("\n");
exit(0);
}
sqlite3_free_table(aresult);
}
int updata_addr(sqlite3 *db,int n)//修改地址
{
char *errmsg;
char **aresult;
char *sql;
char sql2[100];
char address[N];
int i;
int nrow;
int ncol;
sprintf(sql,"select * from contact where id = %d;",n);
if(SQLITE_OK != sqlite3_get_table(db,sql,&aresult,&nrow,&ncol,&errmsg))
{
printf("fail: %s\n",errmsg);
printf("\n");
exit(-1);
}
for(i = 0; i < (nrow + 1) * ncol; i++)
{
printf("%-12s",aresult[i]);
if((i + 1) % ncol == 0)
{
printf("\n");
}
}
printf("input the address you want to updata!\n");//开始修改地址
scanf("%s",address);
getchar();
sprintf(sql2,"update contact set address = '%s' where id = %d;",address,n);
if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("fail: %s\n");
printf("\n");
exit(0);
}
sqlite3_free_table(aresult);
}
int update_table(sqlite3 *db)//修改函数的主函数
{
char sql[100];
char *errmsg;
char **aresult;
char name[N];
char str[N];
char ch;
int i;
int a;
int n;
int id;
int nrow;
int ncol;
int flag = 1;
while(flag)
{
printf("input the name whose imformation you want to updata!\n");//首先先查找你想修改的人的信息
scanf("%s",name);
getchar();
sprintf(sql,"select * from contact where name = '%s';",name);
if(SQLITE_OK != sqlite3_get_table(db,sql,&aresult,&nrow,&ncol,&errmsg))
{
printf("fail:%s\n",errmsg);
printf("\n");
exit(-1);
}
if(nrow == 0)//没有这个人
{
printf("no such person!\n");
}
else
if(nrow == 1)//只有一个人
{
for(i = 0; i < (nrow + 1) * ncol; i++)
{
printf("%-12s",aresult[i]);
if((i + 1) % ncol == 0)
{
printf("\n");
}
}
strcpy(str,aresult[ncol]);
a = atoi(str);
printf("%d\n",a);
printf("\n1:updata name\n2:updata sex\n3:update number\n4:updata address\n");
printf("input your choic!\n");
scanf("%d",&n);
switch(n)
{
case 1:
updata_name(db,a);
break;
case 2:
updata_sex(db,a);
break;
case 3:
updata_number(db,a);
break;
case 4:
updata_addr(db,a);
break;
default:
break;
}
}
else//多个同名的人
{
for(i = 0; i < (nrow + 1) * ncol; i++)
{
printf("%-12s",aresult[i]);
if((i + 1) % ncol == 0)
{
printf("\n");
}
}
printf("input the person`s id which you want to updata!\n");
scanf("%d",&id);
getchar();
printf("\n1:updata name\n2:updata sex\n3:update number\n4:updata address\n");
printf("input your choic!\n");
scanf("%d",&n);
switch(n)
{
case 1:
updata_name(db,id);
break;
case 2:
updata_sex(db,id);
break;
case 3:
updata_number(db,id);
break;
case 4:
updata_addr(db,id);
break;
default:
break;
}
}
printf("continue update [Y/N]?\n");//询问是否继续修改
scanf("%c",&ch);
getchar();
if((ch == 'y') || (ch == 'Y'))
{
flag = 1;
}
else
{
flag = 0;
}
}
sqlite3_free_table(aresult);
}
int menu()//菜单
{
int n;
while(1)
{
printf("********************************\n");
printf("* WELCOME *\n");
printf("* 1:查看联系人 || 2:添加联系人 *\n");
printf("* 3:删除联系人 || 4:修改联系人 *\n");
printf("* 5:查找联系人 || 0:退出 *\n");
printf("input your choice:\n");
scanf("%d",&n);
if(n == 0 || n == 1 || n == 2 || n == 3 || n == 4 || n == 5)
{
return n;
}
else
{
printf("input error! input again:\n");
}
}
return 0;
}
int main()
{
int ret;
int n;
sqlite3 *db;
ret = sqlite3_open("book.db",&db);//打开一个数据库,不存在就创建
if(ret != SQLITE_OK)//打开成功返回SQLITE_OK
{
perror("sqlite open :");
exit(-1);
}
system("clear");
while(1)
{
n = menu();
switch(n)//switch 调用函数与菜单对应
{
case 1:
display(db);
break;
case 2:
system("clear");
insert_table(db);
break;
case 3:
system("clear");
delete_table(db);
system("clear");
break;
case 4:
system("clear");
update_table(db);
system("clear");
break;
case 5:
system("clear");
search_table(db);
system("clear");
break;
case 0:
exit(0);
break;
}
}
return 0;
}