#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
sqlite3* db = NULL; //初始化数据库
//菜单功能函数
int showMenu()
{
int choice = 0;
while(1)
{
printf("**************************************************\n");
printf(" 欢迎使用通讯录 V3.0 \n");
printf("1. 显示所有联系人信息 2.增加一个联系人信息\n");
printf("3. 删除一个联系人信息 4.修改一个联系人信息\n");
printf("5. 查找一个联系人信息 \n");
printf("**************************************************\n");
printf("请输入对应的数字:\n");
scanf("%d",&choice);
if(choice == 1 || choice == 2 || choice == 3 || choice == 4 || choice == 5 )
{
return choice;
}
else
{
printf("\n");
}
}
return 0;
}
//显示所有联系人信息
void displayAll()
{
int i = 0;
int j = 0;
int index = 0;
int ret = 0;
int row = 0;
int column = 0;
char * sql = NULL;
char ** resultSet = NULL; //存储查询结果
sql = (char*)malloc(sizeof(char)*20);
strcpy(sql,"select * from info;");
ret = sqlite3_get_table(db,sql,&resultSet,&row,&column,0);
if(ret != SQLITE_OK)
{
fprintf(stderr,"select records err\n");
}
printf("一共有 %d 个联系人:\n",row);
index = 0;
for(i=0;i<=row;i++)
{
for(j=0;j<column;j++)
{
printf("%-11s",resultSet[index++]);
}
printf("\n");
}
sqlite3_free_table(resultSet);
free(sql);
}
//增加一个联系人信息
void addContact()
{
int ret = 0;
char* name = NULL;
int age = 0;
char* sex = NULL;
char* phone = NULL;
char* sql = NULL;
name = (char*)malloc(sizeof(char)*10);
sex = (char*)malloc(sizeof(char)*10);
phone = (char*)malloc(sizeof(char)*12);
sql = (char*)malloc(sizeof(char)*64);
printf("请输入 (姓名 年龄 性别 电话):");
scanf("%s %d %s %s\n",name,&age,sex,phone);
//printf("%s, %d, %s,%s\n",name,age,sex,phone);
sprintf(sql,"insert into info values('%s',%d,'%s','%s');",name,age,sex,phone);
//printf("%s\n",sql);
ret = sqlite3_exec(db,sql,0,0,0);
if(ret != SQLITE_OK)
{
printf("failed!\n");
}
else
{
printf("ok!\n");
}
free(name);
free(sex);
free(phone);
free(sql);
}
//查找联系人
void findContact()
{
int i,j,index;
int ret = 0;
int row = 0;
int column = 0;
char* name = NULL;
char* sql = NULL;
char** resultSet = NULL;
name = (char*)malloc(sizeof(char)*10);
printf("请输入你要查找的联系人姓名:");
scanf("%s",name);
sql = (char*)malloc(sizeof(char)*64);
sprintf(sql,"select * from info where name = '%s'",name);
ret = sqlite3_get_table(db,sql,&resultSet,&row,&column,0);
if(ret != SQLITE_OK)
{
fprintf(stderr,"select err:%s\n",sqlite3_errmsg(db));
return;
}
index = 0;
if(row>0)
{
for(i=0;i<=row;i++)
{
for(j=0;j<column;j++)
{
printf("%-11s",resultSet[index++]);
}
printf("\n");
}
}
else
{
printf("查无此人!\n");
}
}
//修改联系人信息
void alterContact()
{
//首先调用查找函数
int i,j,index;
int ret = 0;
int row = 0;
int column = 0;
int age = 0;
char* name = NULL;
char* sex = NULL;
char* phone = NULL;
char* sql = NULL;
char** resultSet = NULL;
name = (char*)malloc(sizeof(char)*10);
printf("请输入你想查找的联系人姓名:");
scanf("%s",name);
sql = (char*)malloc(sizeof(char)*128);
sprintf(sql,"select * from info where name = '%s'",name);
ret = sqlite3_get_table(db,sql,&resultSet,&row,&column,0);
if(ret != SQLITE_OK)
{
fprintf(stderr,"select err:%s\n",sqlite3_errmsg(db));
return;
}
index = 0;
if(row>0)
{
for(i=0;i<=row;i++)
{
for(j=0;j<column;j++)
{
printf("%-11s",resultSet[index++]);
}
printf("\n");
}
sqlite3_free_table(resultSet);
//exist ,then alter
sex = (char*)malloc(sizeof(char)*10);
phone = (char*)malloc(sizeof(char)*12);
printf("请输入新的联系人 (年龄 性别 电话):");
scanf("%d %s %s",&age,sex,phone);
//printf(" %d, %s,%s\n",name,age,sex,phone);
sprintf(sql,"update info set age=%d,sex='%s',phone='%s' where name='%s';",age,sex,phone,name);
//printf("%s\n",sql);
ret = sqlite3_exec(db,sql,0,0,0);
if(ret != SQLITE_OK)
{
printf("操作失败!\n");
}
else
{
printf("操作成功!\n");
}
free(sex);
free(phone);
}
else
{
printf("查无此人!\n");
}
free(sql);
free(name);
}
//删除函数
void deleteContact()
{
int ret = 0;
char* name = NULL;
char* sql = NULL;
name = (char*)malloc(sizeof(char)*10);
sql = (char*)malloc(sizeof(char)*64);
printf("请输入你想删除的姓名:");
scanf("%s",name);
sprintf(sql,"delete from info where name='%s';",name);
//to be simple, there will be no warning if the contact does not exist
ret = sqlite3_exec(db,sql,0,0,0);
if(ret != SQLITE_OK)
{
printf("删除失败:%s",sqlite3_errmsg(db));
}
else
{
printf("删除成功!");
}
free(name);
free(sql);
}
int main()
{
int ret = 0;
int choice = 0;
int ch = 0;
char* errmsg = NULL;
char* sql = NULL;
//open the db if exist or create it
ret = sqlite3_open("contact.db",&db);
if(ret)
{
fprintf(stderr,"Cannot open database:%s\n",sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
else
{
printf("成功载入数据库...\n");
}
//create the table info if not exists
sql = (char*)malloc(sizeof(char)*128);//
//strcpy(sql,);
//printf("Copy sql successfully\n");
ret = sqlite3_exec(db,"create table if not exists info(name varchar(10) primary key,age smallint, sex varchar(10), phone varchar(11));",0,0,&errmsg);
if(ret != SQLITE_OK)
{
//printf("Create table error\n");
fprintf(stderr,"Create table err:%s\n",sqlite3_errmsg(db));
}
//printf("Create table successfully\n");
//insert some initial records,
//it will cause a err if not the frist time,but that does not matter
strcpy(sql,"insert into info values('XuLujun',20,'nan','15850685979');");
ret = sqlite3_exec(db,sql,0,0,&errmsg);
if(ret != SQLITE_OK)
{
fprintf(stderr,"Insert record err:%s\n",sqlite3_errmsg(db));
}
free(sql);
//printf("Insert record successfully\n");
//主菜单
while(1)
{
choice = showMenu(); //调用菜单函数并得到操作对应的数字
switch(choice)
{
case 1:
displayAll();
break;
case 2:
addContact();
break; case 6:
break;
case 3:
deleteContact();
break;
case 4:
alterContact();
break;
case 5:
findContact();
break;
default:
break;
}
//是否退回至菜单界面
printf("\n是否退回至菜单界面 1(是) / 0(否)?");
scanf("%d",&ch);
if(ch == 0)
{
break;
}
system("clear");
}
sqlite3_close(db);
return 0;
}
#include <stdlib.h>
#include <string.h>
#include <sqlite3.h>
sqlite3* db = NULL; //初始化数据库
//菜单功能函数
int showMenu()
{
int choice = 0;
while(1)
{
printf("**************************************************\n");
printf(" 欢迎使用通讯录 V3.0 \n");
printf("1. 显示所有联系人信息 2.增加一个联系人信息\n");
printf("3. 删除一个联系人信息 4.修改一个联系人信息\n");
printf("5. 查找一个联系人信息 \n");
printf("**************************************************\n");
printf("请输入对应的数字:\n");
scanf("%d",&choice);
if(choice == 1 || choice == 2 || choice == 3 || choice == 4 || choice == 5 )
{
return choice;
}
else
{
printf("\n");
}
}
return 0;
}
//显示所有联系人信息
void displayAll()
{
int i = 0;
int j = 0;
int index = 0;
int ret = 0;
int row = 0;
int column = 0;
char * sql = NULL;
char ** resultSet = NULL; //存储查询结果
sql = (char*)malloc(sizeof(char)*20);
strcpy(sql,"select * from info;");
ret = sqlite3_get_table(db,sql,&resultSet,&row,&column,0);
if(ret != SQLITE_OK)
{
fprintf(stderr,"select records err\n");
}
printf("一共有 %d 个联系人:\n",row);
index = 0;
for(i=0;i<=row;i++)
{
for(j=0;j<column;j++)
{
printf("%-11s",resultSet[index++]);
}
printf("\n");
}
sqlite3_free_table(resultSet);
free(sql);
}
//增加一个联系人信息
void addContact()
{
int ret = 0;
char* name = NULL;
int age = 0;
char* sex = NULL;
char* phone = NULL;
char* sql = NULL;
name = (char*)malloc(sizeof(char)*10);
sex = (char*)malloc(sizeof(char)*10);
phone = (char*)malloc(sizeof(char)*12);
sql = (char*)malloc(sizeof(char)*64);
printf("请输入 (姓名 年龄 性别 电话):");
scanf("%s %d %s %s\n",name,&age,sex,phone);
//printf("%s, %d, %s,%s\n",name,age,sex,phone);
sprintf(sql,"insert into info values('%s',%d,'%s','%s');",name,age,sex,phone);
//printf("%s\n",sql);
ret = sqlite3_exec(db,sql,0,0,0);
if(ret != SQLITE_OK)
{
printf("failed!\n");
}
else
{
printf("ok!\n");
}
free(name);
free(sex);
free(phone);
free(sql);
}
//查找联系人
void findContact()
{
int i,j,index;
int ret = 0;
int row = 0;
int column = 0;
char* name = NULL;
char* sql = NULL;
char** resultSet = NULL;
name = (char*)malloc(sizeof(char)*10);
printf("请输入你要查找的联系人姓名:");
scanf("%s",name);
sql = (char*)malloc(sizeof(char)*64);
sprintf(sql,"select * from info where name = '%s'",name);
ret = sqlite3_get_table(db,sql,&resultSet,&row,&column,0);
if(ret != SQLITE_OK)
{
fprintf(stderr,"select err:%s\n",sqlite3_errmsg(db));
return;
}
index = 0;
if(row>0)
{
for(i=0;i<=row;i++)
{
for(j=0;j<column;j++)
{
printf("%-11s",resultSet[index++]);
}
printf("\n");
}
}
else
{
printf("查无此人!\n");
}
}
//修改联系人信息
void alterContact()
{
//首先调用查找函数
int i,j,index;
int ret = 0;
int row = 0;
int column = 0;
int age = 0;
char* name = NULL;
char* sex = NULL;
char* phone = NULL;
char* sql = NULL;
char** resultSet = NULL;
name = (char*)malloc(sizeof(char)*10);
printf("请输入你想查找的联系人姓名:");
scanf("%s",name);
sql = (char*)malloc(sizeof(char)*128);
sprintf(sql,"select * from info where name = '%s'",name);
ret = sqlite3_get_table(db,sql,&resultSet,&row,&column,0);
if(ret != SQLITE_OK)
{
fprintf(stderr,"select err:%s\n",sqlite3_errmsg(db));
return;
}
index = 0;
if(row>0)
{
for(i=0;i<=row;i++)
{
for(j=0;j<column;j++)
{
printf("%-11s",resultSet[index++]);
}
printf("\n");
}
sqlite3_free_table(resultSet);
//exist ,then alter
sex = (char*)malloc(sizeof(char)*10);
phone = (char*)malloc(sizeof(char)*12);
printf("请输入新的联系人 (年龄 性别 电话):");
scanf("%d %s %s",&age,sex,phone);
//printf(" %d, %s,%s\n",name,age,sex,phone);
sprintf(sql,"update info set age=%d,sex='%s',phone='%s' where name='%s';",age,sex,phone,name);
//printf("%s\n",sql);
ret = sqlite3_exec(db,sql,0,0,0);
if(ret != SQLITE_OK)
{
printf("操作失败!\n");
}
else
{
printf("操作成功!\n");
}
free(sex);
free(phone);
}
else
{
printf("查无此人!\n");
}
free(sql);
free(name);
}
//删除函数
void deleteContact()
{
int ret = 0;
char* name = NULL;
char* sql = NULL;
name = (char*)malloc(sizeof(char)*10);
sql = (char*)malloc(sizeof(char)*64);
printf("请输入你想删除的姓名:");
scanf("%s",name);
sprintf(sql,"delete from info where name='%s';",name);
//to be simple, there will be no warning if the contact does not exist
ret = sqlite3_exec(db,sql,0,0,0);
if(ret != SQLITE_OK)
{
printf("删除失败:%s",sqlite3_errmsg(db));
}
else
{
printf("删除成功!");
}
free(name);
free(sql);
}
int main()
{
int ret = 0;
int choice = 0;
int ch = 0;
char* errmsg = NULL;
char* sql = NULL;
//open the db if exist or create it
ret = sqlite3_open("contact.db",&db);
if(ret)
{
fprintf(stderr,"Cannot open database:%s\n",sqlite3_errmsg(db));
sqlite3_close(db);
exit(1);
}
else
{
printf("成功载入数据库...\n");
}
//create the table info if not exists
sql = (char*)malloc(sizeof(char)*128);//
//strcpy(sql,);
//printf("Copy sql successfully\n");
ret = sqlite3_exec(db,"create table if not exists info(name varchar(10) primary key,age smallint, sex varchar(10), phone varchar(11));",0,0,&errmsg);
if(ret != SQLITE_OK)
{
//printf("Create table error\n");
fprintf(stderr,"Create table err:%s\n",sqlite3_errmsg(db));
}
//printf("Create table successfully\n");
//insert some initial records,
//it will cause a err if not the frist time,but that does not matter
strcpy(sql,"insert into info values('XuLujun',20,'nan','15850685979');");
ret = sqlite3_exec(db,sql,0,0,&errmsg);
if(ret != SQLITE_OK)
{
fprintf(stderr,"Insert record err:%s\n",sqlite3_errmsg(db));
}
free(sql);
//printf("Insert record successfully\n");
//主菜单
while(1)
{
choice = showMenu(); //调用菜单函数并得到操作对应的数字
switch(choice)
{
case 1:
displayAll();
break;
case 2:
addContact();
break; case 6:
break;
case 3:
deleteContact();
break;
case 4:
alterContact();
break;
case 5:
findContact();
break;
default:
break;
}
//是否退回至菜单界面
printf("\n是否退回至菜单界面 1(是) / 0(否)?");
scanf("%d",&ch);
if(ch == 0)
{
break;
}
system("clear");
}
sqlite3_close(db);
return 0;
}