/*****************************************************
File name:txl_db.c
Author: dtt
Date:2018-01-24 14:16
*****************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <time.h>
int create(sqlite3 *db)
{
char *sql;
char *errmsg;
sql = "create table if not exists contacts_1(id integer primary key,name text,tele text)";
if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("创建表出错:%s\n",errmsg);
exit(0);
}
return 0;
}
int insert(sqlite3 *db)
{
char sql[100];
char *errmsg;
int id;
char tele[11];
char name[20];
printf("*请输入:序号 姓名 电话号码 *\n");
scanf("%d %s %s",&id,name,tele);
sprintf(sql,"insert into contacts_1 values(%d,'%s','%s');",id,name,tele); //sql只能用数组,不能用指针
if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("插入出错:%s\n",errmsg);
exit (0);
}
return 0;
}
int update(sqlite3 *db)
{
int choose;
char sql_1[100],sql_2[100];
char *errmsg;
char **result;
int id_old,id_new;
int row,column;
char tele_old[11],tele_new[11];
char name_old[20],name_new[20]; //old和new分别指旧数据和新数据
printf("*********************************************\n");
printf("*选择输入需要更新的联系人的信息 *\n");
printf("*1:序号 *\n");
printf("*2:姓名 *\n");
printf("*3:电话号码 *\n");
printf("*********************************************\n");
scanf("%d",&choose);
switch(choose)
{
case 1:
printf("*请输入序号 *\n");
scanf("%d",&id_old);
sprintf(sql_1,"select * from contacts_1 where id=%d;",id_old);
if(SQLITE_OK != sqlite3_get_table(db,sql_1,&result,&row,&column,&errmsg))
{
printf("查找此条记录出错:%s!\n",errmsg);
exit (0);
}
if(row == 0 || column == 0)
{
printf("*该联系人不存在 *\n");
return 0;
}
printf("*请输入新的联系人信息:序号 姓名 电话号码 *\n");
scanf("%d",&id_new);
scanf("%s",name_new);
scanf("%s",tele_new);
sprintf(sql_2,"update contacts_1 set id=%d,name='%s',tele='%s' where id=%d;",id_new,name_new,tele_new,id_old);
break;
case 2:
printf("*请输入姓名 *\n");
scanf("%s",name_old);
sprintf(sql_1,"select * from contacts_1 where name='%s';",name_old);
if(SQLITE_OK != sqlite3_get_table(db,sql_1,&result,&row,&column,&errmsg))
{
printf("查找此条记录出错:%s!\n",errmsg);
exit (0);
}
if(row == 0 || column == 0)
{
printf("*该联系人不存在 *\n");
return 0;
}
printf("*请输入新的联系人信息:序号 姓名 电话号码 *\n");
scanf("%d",&id_new);
scanf("%s",name_new);
scanf("%s",tele_new);
sprintf(sql_2,"update contacts_1 set id=%d,name='%s',tele='%s' where name='%s';",id_new,name_new,tele_new,name_old);
break;
case 3:
printf("*请输入电话号码 *\n");
scanf("%s",&tele_old);
sprintf(sql_1,"select * from contacts_1 where tele='%s';",tele_old);
if(SQLITE_OK != sqlite3_get_table(db,sql_1,&result,&row,&column,&errmsg))
{
printf("查找此条记录出错:%s!\n",errmsg);
exit (0);
}
if(row == 0 || column == 0)
{
printf("*该联系人不存在 *\n");
return 0;
}
printf("*请输入新的联系人信息:序号 姓名 电话号码 *\n");
scanf("%d",&id_new);
scanf("%s",name_new);
scanf("%s",tele_new);
sprintf(sql_2,"update contacts_1 set id=%d,name='%s',tele='%s' where tele='%s';",id_new,name_new,tele_new,tele_old);
break;
}
if(SQLITE_OK != sqlite3_exec(db,sql_2,NULL,NULL,&errmsg))
{
printf("更新出错:%s!\n",errmsg);
exit (0);
}
return 0;
}
int delete(sqlite3 *db)
{
char sql_1[100];
char sql_2[100];
char *errmsg;
char **result;
int id;
char tele[11];
char name[20];
int choose;
int row,column;
printf("*********************************************\n");
printf("*选择输入需要删除的联系人的信息 *\n");
printf("*1:序号 *\n");
printf("*2:姓名 *\n");
printf("*3:电话号码 *\n");
printf("*********************************************\n");
scanf("%d",&choose);
switch(choose)
{
case 1:
printf("*请输入序号 *\n");
scanf("%d",&id);
sprintf(sql_1,"select * from contacts_1 where id=%d;",id);
if(SQLITE_OK != sqlite3_get_table(db,sql_1,&result,&row,&column,&errmsg))
{
printf("查找此条记录出错:%s!\n",errmsg);
exit (0);
}
if(row == 0 || column == 0)
{
printf("*该联系人不存在 *\n");
return 0;
}
sprintf(sql_2,"delete from contacts_1 where id=%d;",id);
break;
case 2:
printf("*请输入姓名 *\n");
scanf("%s",name);
sprintf(sql_1,"select * from contacts_1 where name='%s';",name);
if(SQLITE_OK != sqlite3_get_table(db,sql_1,&result,&row,&column,&errmsg))
{
printf("查找此条记录出错:%s!\n",errmsg);
exit (0);
}
if(row == 0 || column == 0)
{
printf("*该联系人不存在 *\n");
return 0;
}
sprintf(sql_2,"delete from contacts_1 where name='%s';",name);
break;
case 3:
printf("*请输入电话号码 *\n");
scanf("%s",tele);
sprintf(sql_1,"select * from contacts_1 where tele='%s';",tele);
if(SQLITE_OK != sqlite3_get_table(db,sql_1,&result,&row,&column,&errmsg))
{
printf("查找此条记录出错:%s!\n",errmsg);
exit (0);
}
if(row == 0 || column == 0)
{
printf("*该联系人不存在 *\n");
return 0;
}
sprintf(sql_2,"delete from contacts_1 where tele='%s';",tele);
break;
}
if(SQLITE_OK != sqlite3_exec(db,sql_2,NULL,NULL,&errmsg))
{
printf("删除出错:%s!\n",errmsg);
exit (0);
}
return 0;
}
int selectrow(sqlite3 *db)
{
char sql[100];
char *errmsg;
char **result;
int id;
char tele[11];
char name[20];
int choose;
int i;
int row,column;
printf("*********************************************\n");
printf("*选择输入需要查询的联系人的信息 *\n");
printf("*1:序号 *\n");
printf("*2:姓名 *\n");
printf("*3:电话号码 *\n");
printf("*********************************************\n");
scanf("%d",&choose);
switch(choose)
{
case 1:
printf("*请输入序号 *\n");
scanf("%d",&id);
sprintf(sql,"select * from contacts_1 where id=%d;",id);
break;
case 2:
printf("*请输入姓名 *\n");
scanf("%s",name);
sprintf(sql,"select * from contacts_1 where name='%s';",name);
break;
case 3:
printf("*请输入电话号码 *\n");
scanf("%s",tele);
sprintf(sql,"select * from contacts_1 where tele='%s';",tele);
break;
}
if(SQLITE_OK != sqlite3_get_table(db,sql,&result,&row,&column,&errmsg))
{
printf("查询出错:%s!\n",errmsg);
exit (0);
}
if(row == 0 || column == 0)
{
printf("*查询为空 *\n");
return 0;
}
printf("*********************************************\n");
printf("*查询结果 *\n");
for(i = 0;i < (row + 1) * column;i++)
{
printf("%8.8s ",result[i]);
if((i+1) % column == 0)
printf("\n");
}
printf("*********************************************\n");
sqlite3_free_table(result);
return 0;
}
int selecttable(sqlite3 *db)
{
char *sql;
char *errmsg;
int i;
char **result;
int row,column;
sql = "select * from contacts_1";
if(SQLITE_OK != sqlite3_get_table(db,sql,&result,&row,&column,&errmsg))
{
printf("错误:%s\n",errmsg);
exit(0);
}
if(row == 0 || column == 0)
{
printf("*表为空 *\n");
return 0;
}
printf("*********************************************\n");
printf("*row:%d,column:%d *\n",row,column);
printf("*查询结果 *\n");
for(i = 0;i < (row + 1) * column;i++)
{
printf("%8.8s ",result[i]);
if((i+1) % column == 0)
printf("\n");
}
printf("*********************************************\n");
sqlite3_free_table(result);
return 0;
}
int main()
{
sqlite3 *db = NULL;
int rc;
int choose;
struct tm *t;
time_t tt;
time(&tt);
t = localtime(&tt); //打印系统时间,头文件time.h
rc = sqlite3_open("database1.db",&db);
if(rc != SQLITE_OK)
{
fprintf(stderr,"can't open database!\n'");
exit(0);
}
else
{
printf(" 创建表成功! \n");
}
while(1)
{
printf("*********************************************\n");
printf("*输入数字执行操作: *\n");
printf("*1:查看联系人 *\n");
printf("*2:增加联系人 *\n");
printf("*3:修改联系人 *\n");
printf("*4:删除联系人 *\n");
printf("*5:查找联系人 *\n");
printf("*0:退出 *\n");
printf("* 操作时间:%4d年%02d月%2d日%02d:%02d:%02d *\n",
t->tm_year+1900,t->tm_mon+1,t->tm_mday,t->tm_hour,t->tm_min,t->tm_sec);
printf("*********************************************\n");
scanf("%d",&choose);
switch(choose)
{
case 1:selecttable(db);
break;
case 2:insert(db);
break;
case 3:update(db);
break;
case 4:delete(db);
break;
case 5:selectrow(db);
break;
case 0:break;
}
if(0 == choose)
break;
}
rc = sqlite3_close(db);
if(SQLITE_OK != rc)
{
printf("关闭出错");
exit(0);
}
return 0;
}
在Linux环境下用数据库sqlite3实现的通讯录
最新推荐文章于 2022-03-02 10:32:00 发布