#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
int create_table(sqlite3*db);//表若不存在,则创建
int menu(sqlite3*db);//菜单函数
int insert_table(sqlite3*db);//插入用户信息
int delete_table(sqlite3*db);//删除用户信息
int update_table(sqlite3*db);//修改用户信息
int select_table(sqlite3*db);//查询用户信息
int displaycb(void*para,int col_count,char**col_value,char**col_name);
int select_alltable(sqlite3*db);//显示所有用户信息
int quit(sqlite3*db);//退出函数
int main()//主函数
{
sqlite3 * db = NULL;
int ret;
ret = sqlite3_open("mydatabase.db",&db);//打开数据库,不存在创建
if(ret != SQLITE_OK)
{
perror("打开数据库失败!\n");
exit(-1);
}
else
{
printf("成功打开数据库!\n");
}
create_table(db);
menu(db);
sqlite3_close(db);
return 0;
}
int create_table(sqlite3*db)//表若不存在,创建表
{
char *errmsg = NULL;
char *sql;
sql = "create table if not exists mytable (name text primary key,age text,qq text,phone text);";
if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("创建表失败!%s\n",errmsg);
exit(-1);
}
else
{
printf("此时表已经在数据库中!\n");
}
return 0;
}
int menu(sqlite3*db)
{
char ch;
printf("/*************通信录v1.3**********/\n");
printf("/**********(1)新建用户信息********/\n");
printf("/**********(2)删除用户信息********/\n");
printf("/**********(3)修改用户信息********/\n");
printf("/**********(4)查找用户信息********/\n");
printf("/**********(5)显示用户信息********/\n");
printf("/**********(6)退出程序************/\n");
while(1)
{
printf("请输入操作序号:");
scanf(" %c",&ch);
if(ch <= '0' || ch >= '7' )
{
printf("输入序号有误,重新输入!\n");
}
else
{
break;
}
}
switch(ch)
{
case '1': insert_table(db);break;
case '2': delete_table(db);break;
case '3': update_table(db);break;
case '4': select_table(db);break;
case '5': select_alltable(db);break;
case '6': quit(db);break;
}
return 0;
}
int insert_table(sqlite3*db)//创建用户信息
{
char *errmsg = NULL;
char sql[100];
char name[10];
char age[10];
char qq[20];
char phone[20];
char ch;
printf("输入姓名:");
scanf("%s",name);
printf("输入年龄:");
scanf("%s",age);
printf("输入QQ:");
scanf("%s",qq);
printf("输入电话:");
scanf("%s",phone);
sprintf(sql,"insert into mytable (name,age,qq,phone) values ('%s','%s','%s','%s');",name,age,qq,phone);
if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("创建新用户失败!%s\n",errmsg);
exit(-1);
}
else
{
printf("成功创建新用户!\n");
}
while(1)
{
printf("是(1)否(2)继续操作!\n");
printf("请输入操作序号:");
scanf(" %c",&ch);
if(ch == '1' || ch == '2')
{
break;
}
else
{
printf("输入操作序号有误!\n");
}
}
if(ch == '1')
{
insert_table(db);
}
else
{
menu(db);
}
return 0;
}
int delete_table(sqlite3 * db)//删除一行表记录
{
char *errmsg = NULL;
char sql[100];
char name[10];
char qq[20];
char phone[20];
char ret;
char ch;
printf("请输入删除用户信息的方式\n");
printf(" (1)姓名 \n");
printf(" (2)QQ \n");
printf(" (3)电话 \n");
while(1)
{
printf("请输入操作的序号:");
scanf(" %c",&ret);
if(ret >= '1' && ret <= '3' )
{
break;
}
else
{
printf("输入序号有误!\n");
}
}
switch(ret)
{
case '1':printf("输入删除用户的姓名:");
scanf("%s",name);
sprintf(sql,"delete from mytable where name = '%s';",name);
break;
case '2':printf("输入删除用户的QQ:");
scanf("%s",qq);
sprintf(sql,"delete from mytable where qq = '%s';",qq);
break;
case '3':printf("输入删除用户的电话:");
scanf("%s",phone);
sprintf(sql,"delete from mytable where phone = '%s';",phone);
break;
}
if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("删除用户信息失败!%s\n",errmsg);
exit(-1);
}
else
{
printf("成功删除用户信息!\n");
}
while(1)
{
printf("是(1)否(2)继续操作!\n");
printf("请输入操作序号:");
scanf(" %c",&ch);
if(ch == '1'|| ch == '2')
{
break;
}
else
{
printf("输入操作序号有误!\n");
}
}
if(ch == '1')
{
delete_table(db);
}
else
{
menu(db);
}
return 0;
}
int update_table(sqlite3 * db)
{
char *errmsg = NULL;
char sql[100];
char oldname[10];
char name[10];
char qq[20];
char phone[20];
char ch;
char ret;
printf("输入修改信息的用户姓名:");
scanf("%s",oldname);
printf("请输入需要修改的项目\n");
printf(" (1)姓名 \n");
printf(" (2)QQ \n");
printf(" (3)电话 \n");
while(1)
{
printf("请输入操作的序号:");
scanf(" %c",&ret);
if(ret >= '1' && ret <= '3' )
{
break;
}
else
{
printf("输入序号有误!\n");
}
}
switch(ret)
{
case '1':printf("输入新的姓名:");
scanf("%s",name);
sprintf(sql,"update mytable set name = '%s' where name = '%s';",name,oldname);
break;
case '2':printf("输入新的QQ:");
scanf("%s",qq);
sprintf(sql,"update mytable set qq = '%s' where name = '%s';",qq,oldname);
break;
case '3':printf("输入新的电话:");
scanf("%s",phone);
sprintf(sql,"update mytable set phone = '%s' where name = '%s';",phone,oldname);
break;
}
if(SQLITE_OK != sqlite3_exec(db,sql,NULL,NULL,&errmsg))
{
printf("修改信息失败!%s\n",errmsg);
exit(-1);
}
else
{
printf("成功修改用户信息!\n");
}
while(1)
{
printf("是(1)否(2)继续操作!\n");
printf("请输入操作序号:");
scanf(" %c",&ch);
if(ch == '1' || ch == '2')
{
break;
}
else
{
printf("输入操作序号有误!\n");
}
}
if(ch == '1')
{
update_table(db);
}
else
{
menu(db);
}
return 0;
}
int select_table(sqlite3*db)
{
char *errmsg = NULL;
char sql[100];
char name[10];
char qq[20];
char phone[20];
char ch;
char ret;
printf("请输入查询用户信息的方式\n");
printf(" (1)姓名 \n");
printf(" (2)QQ \n");
printf(" (3)电话 \n");
while(1)
{
printf("请输入操作的序号:");
scanf(" %c",&ret);
if(ret >= '1' && ret <= '3' )
{
break;
}
else
{
printf("输入序号有误!\n");
}
}
switch(ret)
{
case '1':printf("输入查询用户的姓名:");
scanf("%s",name);
sprintf(sql, "select * from mytable where name = '%s';",name);
break;
case '2':printf("输入查询用户的QQ:");
scanf("%s",qq);
sprintf(sql, "select * from mytable where qq = '%s';",qq);
break;
case '3':printf("输入查询用户的电话:");
scanf("%s",phone);
sprintf(sql, "select * from mytable where phone = '%s';",phone);
break;
}
if(SQLITE_OK != sqlite3_exec(db,sql,displaycb,NULL,&errmsg))
{
printf("查询表失败%s\n",errmsg);
exit(-1);
}
else
{
printf("成功查询用户信息!\n");
}
while(1)
{
printf("是(1)否(2)继续操作!\n");
printf("请输入操作序号:");
scanf(" %c",&ch);
if(ch == '1' || ch == '2')
{
break;
}
else
{
printf("输入操作序号有误!\n");
}
}
if(ch == '1')
{
select_table(db);
}
else
{
menu(db);
}
return 0;
}
int displaycb(void*para,int col_count,char**col_value,char**col_name)//显示表记录的回调函数
{
int i;
for(i = 0; i < col_count; i++)
{
printf("%20s",col_value[i]);
}
printf("\n");
return 0;
}
int select_alltable(sqlite3*db)//显示表记录,用sqlite3_get_table函数
{
int row;
int column;
char **aresult;
char *errmsg = NULL;
char *sql;
int i;
char ch;
sql = "select * from mytable;";
if(SQLITE_OK != sqlite3_get_table(db,sql,&aresult,&row,&column,&errmsg))
{
printf("显示所有用户信息失败!%s\n",errmsg);
exit(-1);
}
if(row == 0)
{
printf("表中没有用户信息!\n");
}
else
{
for(i = 0; i < (row + 1) * column ; i++)
{
printf("%20s",aresult[i]);
if((i + 1) % column == 0)
{
printf("\n");
}
}
}
sqlite3_free_table(aresult);
while(1)
{
printf("是(1)否(2)继续操作!\n");
printf("请输入操作序号:");
scanf(" %c",&ch);
if(ch == '1'|| ch == '2')
{
break;
}
else
{
printf("输入操作序号有误!\n");
}
}
if(ch == '1')
{
select_alltable(db);
}
else
{
menu(db);
}
return 0;
}
int quit(sqlite3*db)
{
printf("非常感谢你的使用!\n");
return 0;
}
用数据库写简单的通信录
最新推荐文章于 2023-06-30 21:58:30 发布