/*************************************************************************
> File Name: addressbook.c
> Author: hhp
> Created Time: 2017年07月04日 星期二 14时34分43秒
************************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <string.h>
#include <unistd.h>
int age;
char name[20];
char sex[2];
char number[20];
void menu(sqlite3 *db);
void insert(sqlite3 *db);
void delete(sqlite3 *db);
void update(sqlite3 *db);
void query(sqlite3 *db);
void showtable(sqlite3 *db);
int main()
{
int ret;
sqlite3 *db;
char *errmsg;
ret = sqlite3_open("address.db", &db);
if (ret != SQLITE_OK)
{
perror("open");
exit(1);
}
char sql_create[] = "create table stu(name text, sex text, age integer, number text);";
ret = sqlite3_exec(db, sql_create, NULL, NULL, &errmsg);
if (ret != SQLITE_OK)
{
perror("create");
printf("error: %s\n", errmsg);
exit(2);
}
menu(db);
ret = sqlite3_close(db);
if (ret != SQLITE_OK)
{
perror("close");
exit(3);
}
return 0;
}
void menu(sqlite3 *db)
{
int sel;
printf(" welcome to my addressbook!\n\n" );
printf("----------------------------------------------\n");
printf("1:add information | 2:delete information\n");
printf("3:update information | 4:query information\n");
printf("5:show all information | 0:exit the system \n");
printf("----------------------------------------------\n");
printf("\n");
printf("please input your choice:");
//scanf("%d", &sel);
while (scanf("%d", &sel) == 1)
{
if (0 == sel)
{
printf("===>thanks for you to use!\n");
sleep(1);
printf("byebye-_- -_-\n");
break;
}
switch (sel)
{
case 1:
{
insert(db);
break;
}
case 2:
{
delete(db);
break;
}
case 3:
{
update(db);
break;
}
case 4:
{
query(db);
break;
}
case 5:
{
showtable(db);
break;
}
default:
{
printf("your input is error, please input again!!\n");
break;
}
}
}
}
void insert(sqlite3 *db)
{
int ret;
char *errmsg;
char sql_insert[1024] = {0};
printf("please input name:\n");
scanf("%s", name);
printf("please input sex(m/f):\n");
scanf("%s", sex);
printf("please input age:\n");
scanf("%d", &age);
printf("please input number:\n");
scanf("%s", number);
sprintf(sql_insert, "insert into stu values('%s','%s','%d','%s');", name, sex, age, number);
ret = sqlite3_exec(db, sql_insert, NULL, NULL, &errmsg);
if (ret != SQLITE_OK)
{
perror("insert");
exit(1);
}
printf("insert sucess!\n");
memset(name, 0, 20);
memset(sex, 0, 2);
memset(number, 0, 20);
}
void delete(sqlite3 *db)
{
int ret, sel;
char *errmsg;
char sql_delete[1024] = {0};
printf("1.delete by name 2.delete by number\n");
printf("please input your choice:");
scanf("%d", &sel);
switch (sel)
{
case 1:
{
printf("input the name to delete\n");
scanf("%s", name);
sprintf(sql_delete, "delete from stu where name = '%s';", name);
ret = sqlite3_exec(db, sql_delete, NULL, NULL, &errmsg);
if (ret != SQLITE_OK)
{
perror("delete");
exit(1);
}
printf("delete sucessfully!\n");
memset(name, 0, 20);
break;
}
case 2:
{
printf("input the number to delete\n");
scanf("%s", number);
sprintf(sql_delete, "delete from stu where number = '%s';", number);
ret = sqlite3_exec(db, sql_delete, NULL, NULL, &errmsg);
if (ret != SQLITE_OK)
{
perror("delete");
exit(1);
}
printf("delete sucessfully!\n");
memset(number, 0, 20);
break;
}
default:
{
printf("please input sel(1 or 2)\n");
break;
}
}
}
void update(sqlite3 *db)
{
int ret, sel;
int updateage;
char *errmsg;
char sql_update[1024] = {0};
char updatename[1024] = {0};
char updatesex[1024] = {0};
char updatenumber[1024] = {0};
printf("1.update by name 2.update by number\n");
printf("please input your choice:");
scanf("%d", &sel);
switch (sel)
{
case 1:
{
printf("input the name to update\n");
scanf("%s", name);
printf("1.update name 2.update sex 3.update age 4.update number\n");
printf("input your choice:");
scanf("%d", &sel);
switch (sel)
{
case 1:
{
printf("please input name\n");
scanf("%s", updatename);
sprintf(sql_update, "update stu set name = '%s'where name = '%s';", updatename, name);
ret = sqlite3_exec(db, sql_update, NULL, NULL, &errmsg);
if (ret != SQLITE_OK)
{
perror("update11");
exit(1);
}
printf("update successfully!\n");
memset(name, 0, 20);
break;
}
case 2:
{
printf("please input sex\n");
scanf("%s", updatesex);
sprintf(sql_update, "update stu set sex = '%s'where name = '%s';", updatesex, name);
ret = sqlite3_exec(db, sql_update, NULL, NULL, &errmsg);
if (ret != SQLITE_OK)
{
perror("update12");
exit(1);
}
printf("update successfully!\n");
memset(name, 0, 20);
break;
}
case 3:
{
printf("please input age\n");
scanf("%d", &updateage);
sprintf(sql_update, "update stu set age = '%d'where name = '%s';", updateage, name);
ret = sqlite3_exec(db, sql_update, NULL, NULL, &errmsg);
if (ret != SQLITE_OK)
{
perror("update13");
exit(1);
}
printf("update successfully!\n");
memset(name, 0, 20);
break;
}
case 4:
{
printf("please input number\n");
scanf("%s", updatenumber);
sprintf(sql_update, "update stu set number = '%s'where name = '%s';", updatenumber, name);
ret = sqlite3_exec(db, sql_update, NULL, NULL, &errmsg);
if (ret != SQLITE_OK)
{
perror("update14");
exit(1);
}
printf("update successfully!\n");
memset(name, 0, 20);
break;
}
default:
{
printf("please input sel(1,2,3,4)!\n");
break;
}
}
}
case 2:
{
printf("input the number to update\n");
scanf("%s", number);
printf("1.update name 2.update sex 3.update age 4.update number\n");
printf("input your choice:");
scanf("%d", &sel);
switch (sel)
{
case 1:
{
printf("please input name\n");
scanf("%s", updatename);
sprintf(sql_update, "update stu set name = '%s'where name = '%s';", updatename, name);
ret = sqlite3_exec(db, sql_update, NULL, NULL, &errmsg);
if (ret != SQLITE_OK)
{
perror("update21");
exit(1);
}
printf("update successfully!\n");
memset(number, 0, 20);
break;
}
case 2:
{
printf("please input sex\n");
scanf("%s", updatesex);
sprintf(sql_update, "update stu set sex = '%s'where name = '%s';", updatesex, name);
ret = sqlite3_exec(db, sql_update, NULL, NULL, &errmsg);
if (ret != SQLITE_OK)
{
perror("update22");
exit(1);
}
printf("update successfully!\n");
memset(number, 0, 20);
break;
}
case 3:
{
printf("please input age\n");
scanf("%d", &updateage);
sprintf(sql_update, "update stu set age = '%d'where name = '%s';", updateage, name);
ret = sqlite3_exec(db, sql_update, NULL, NULL, &errmsg);
if (ret != SQLITE_OK)
{
perror("update23");
exit(1);
}
printf("update successfully!\n");
memset(number, 0, 20);
break;
}
case 4:
{
printf("please input number\n");
scanf("%s", updatenumber);
sprintf(sql_update, "update stu set number = '%s'where name = '%s';", updatenumber, name);
ret = sqlite3_exec(db, sql_update, NULL, NULL, &errmsg);
if (ret != SQLITE_OK)
{
perror("update24");
exit(1);
}
printf("update successfully!\n");
memset(number, 0, 20);
break;
}
default:
{
printf("please input sel(1,2,3,4)!\n");
break;
}
}
}
default:
{
printf("please input sel(1 or 2)!\n");
break;
}
}
}
void query(sqlite3 *db)
{
int sel;
int ret, i, j;
int row, column;
char *errmsg;
char **result;
char sql_query[1024] = {0};
printf("1.query by name 2.query by number\n");
printf("please input your choice:");
scanf("%d", &sel);
switch (sel)
{
case 1:
{
printf("input the name to query\n");
scanf("%s", name);
sprintf(sql_query, "select * from stu where name = '%s';", name);
ret = sqlite3_get_table(db, sql_query, &result, &row, &column, &errmsg);
if (ret != SQLITE_OK)
{
perror("query1");
exit(1);
}
for (i = 0; i <= row; i++)
{
for (j = 0; j < column; j++)
{
printf("%s |", result[i * column + j]);
}
printf("\n");
}
printf("query successfully!\n");
memset(name, 0, 20);
break;
}
case 2:
{
printf("input the number to query\n");
scanf("%s", number);
sprintf(sql_query, "select * from stu where number = '%s';", number);
ret = sqlite3_get_table(db, sql_query, &result, &row, &column, &errmsg);
if (ret != SQLITE_OK)
{
perror("query2");
exit(1);
}
for (i = 0; i <= row; i++)
{
for (j = 0; j < column; j++)
{
printf("%s |", result[i * column + j]);
}
printf("\n");
}
printf("query successfully!\n");
memset(number, 0, 20);
break;
}
}
}
void showtable(sqlite3 *db)
{
int i, j, ret;
char *errmsg;
int row, column;
char **result;
char sql_select[] = "select * from stu;";
ret = sqlite3_get_table(db, sql_select, &result, &row, &column, &errmsg);
if (ret != SQLITE_OK)
{
perror("showtable");
exit(1);
}
for (i = 0; i <= row; i++)
{
for (j = 0; j < column; j++)
{
printf("%s |", result[i * column + j]);
}
printf("\n");
}
}
数据库存储通讯录,实现“增删改查”
最新推荐文章于 2024-05-10 10:55:12 发布