使用数据库完成工人管理系统:
ubuntu@ubuntu:DB$
ubuntu@ubuntu:DB$ cat 2.c
#include <myhead.h>
#include <sqlite3.h>
#include <string.h>
typedef struct
{
int id;
char name[20];
double salary;
} Worker;
int do_insert(sqlite3 *ppDb)
{
Worker work;
printf("请输入工号:");
scanf("%d", &work.id);
getchar(); // 清除缓冲区
printf("请输入姓名:");
fgets(work.name, sizeof(work.name), stdin);
work.name[strcspn(work.name, "\n")] = 0; // 移除换行符
printf("请输入薪水:");
scanf("%lf", &work.salary);
char *errmsg = NULL;
char sqlite[200];
snprintf(sqlite, sizeof(sqlite), "INSERT INTO Work VALUES(%d, '%s', %lf);",
work.id, work.name, work.salary);
if(sqlite3_exec(ppDb, sqlite, NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("错误码:%d 错误信息:%s, 出错行是:%d\n",
sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb), __LINE__);
printf("错误信息:%s\n", errmsg);
sqlite3_free(errmsg);
return -1;
}
printf("添加工人信息成功\n");
return 0;
}
int do_delete(sqlite3 *ppDb)
{
char name[20];
printf("请输入要删除工人的姓名:");
fgets(name, sizeof(name), stdin);
name[strcspn(name, "\n")] = 0; // 移除换行符
char *errmsg = NULL;
char sqlite[200];
snprintf(sqlite, sizeof(sqlite), "DELETE FROM Work WHERE name='%s';", name);
if(sqlite3_exec(ppDb, sqlite, NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("错误码:%d 错误信息:%s, 出错行是:%d\n",
sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb), __LINE__);
printf("错误信息:%s\n", errmsg);
sqlite3_free(errmsg);
return -1;
}
printf("删除工人信息成功\n");
return 0;
}
int do_update(sqlite3 *ppDb)
{
char name[20];
double new_salary;
printf("请输入要修改工人的姓名:");
fgets(name, sizeof(name), stdin);
name[strcspn(name, "\n")] = 0; // 移除换行符
printf("请输入新的薪水:");
scanf("%lf", &new_salary);
getchar(); // 清除缓冲区
char *errmsg = NULL;
char sqlite[200];
snprintf(sqlite, sizeof(sqlite), "UPDATE Work SET salary=%lf WHERE name='%s';", new_salary, name);
if(sqlite3_exec(ppDb, sqlite, NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("错误码:%d 错误信息:%s, 出错行是:%d\n",
sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb), __LINE__);
printf("错误信息:%s\n", errmsg);
sqlite3_free(errmsg);
return -1;
}
printf("修改工人信息成功\n");
return 0;
}
int callback(void *arg, int n, char **msgtext, char **msgtable)
{
int i;
for(i = 0; i < n; i++)
{
printf("%s\t", msgtable[i]);
}
putchar(10);
for(i = 0; i < n; i++)
{
printf("%s\t", msgtext[i] ? msgtext[i] : "NULL");
}
putchar(10);
return 0;
}
int do_seek(sqlite3 *ppDb)
{
char key[20];
printf("请输入你要查找的工人姓名:");
fgets(key, sizeof(key), stdin);
key[strcspn(key, "\n")] = 0; // 移除换行符
char *errmsg = NULL;
char sqlite[200];
snprintf(sqlite, sizeof(sqlite), "SELECT * FROM Work WHERE name='%s';", key);
if(sqlite3_exec(ppDb, sqlite, callback, NULL, &errmsg) != SQLITE_OK)
{
printf("错误码:%d 错误信息:%s, 出错行是:%d\n",
sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb), __LINE__);
printf("错误信息:%s\n", errmsg);
sqlite3_free(errmsg);
return -1;
}
return 0;
}
int show_all(void *arg, int n, char **msgtext, char **msgtable)
{
static int row = 0;
int i;
if (row == 0) {
for(i = 0; i < n; i++)
{
printf("%s\t", msgtable[i]);
}
printf("\n");
}
for(i = 0; i < n; i++)
{
printf("%s\t", msgtext[i] ? msgtext[i] : "NULL");
}
printf("\n");
row++;
return 0;
}
int do_showall(sqlite3 *ppDb)
{
char *errmsg = NULL;
const char *sqlite = "SELECT * FROM Work;";
if(sqlite3_exec(ppDb, sqlite, show_all, NULL, &errmsg) != SQLITE_OK)
{
printf("错误码:%d 错误信息:%s, 出错行是:%d\n",
sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb), __LINE__);
printf("错误信息:%s\n", errmsg);
sqlite3_free(errmsg);
return -1;
}
return 0;
}
void menu(sqlite3 *ppDb)
{
int n;
printf("\n\t\t\t*********肖申克的救赎*********\n");
printf("\t\t\t1、插入一个工人信息\n");
printf("\t\t\t2、删除一个工人信息\n");
printf("\t\t\t3、修改一个工人信息\n");
printf("\t\t\t4、查找一个工人信息\n");
printf("\t\t\t5、输出所有工人信息\n");
printf("\t\t\t0、退出\n");
printf("请输入你的选择:");
scanf("%d", &n);
getchar(); // 清除缓冲区
switch(n)
{
case 1:
do_insert(ppDb);
break;
case 2:
do_delete(ppDb);
break;
case 3:
do_update(ppDb);
break;
case 4:
do_seek(ppDb);
break;
case 5:
do_showall(ppDb);
break;
case 0:
exit(0);
default:
printf("输入有误,请重新输入\n");
}
}
int main(int argc, const char *argv[])
{
sqlite3 *ppDb;
if(sqlite3_open("./hhh.db", &ppDb) != SQLITE_OK)
{
printf("错误码:%d 错误信息:%s, 出错行是:%d\n",
sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb), __LINE__);
return -1;
}
printf("数据库打开成功\n");
char *errmsg = NULL;
const char *sqlite = "CREATE TABLE IF NOT EXISTS Work(id INT, name TEXT, salary REAL);";
if(sqlite3_exec(ppDb, sqlite, NULL, NULL, &errmsg) != SQLITE_OK)
{
printf("错误码:%d 错误信息:%s, 出错行是:%d\n",
sqlite3_errcode(ppDb), sqlite3_errmsg(ppDb), __LINE__);
printf("错误信息:%s\n", errmsg);
sqlite3_free(errmsg);
sqlite3_close(ppDb);
return -1;
}
while(1)
{
menu(ppDb);
}
sqlite3_close(ppDb);
printf("数据库关闭成功\n");
return 0;
}