【MySQL】数据库-图书管理系统（CC++实现）

一.预期功能

该图书管理系统设计提供基本的设计模版，涉及数据库的增删查改等操作，包含登录功能，图书管理功能，图书借阅功能，用户管理功能等基础功能，详细功能查看以下菜单表，共包含三个菜单，登录菜单，管理员菜单和用户菜单。

void Menu()
{printf("***************************************
");printf("*      欢迎进入线上图书管理系统！     *
");printf("*                                     *
");printf("*            1.管理员登录             *
");printf("*            2.读者登录               *
");printf("*            3.读者注册               *
");printf("*            0.退出本系统             *
");printf("***************************************
");
}
void manager_Menu()
{printf("***************************************
");printf("*            欢迎你，管理员            
");printf("*                                     
");printf("*            1.添加书籍              
");printf("*            2.显示书籍              
");printf("*            3.查找书籍               
");printf("*            4.修改书籍               
");printf("*            5.显示用户数据            
");printf("*            6.返回主菜单              
");printf("*            7.添加新管理员              
");printf("*            0.退出本系统             
");printf("***************************************
");
}
void reader_Menu(user* head)
{printf("***************************************
");printf("*          欢迎你，读者           
");printf("*                                     
");printf("*          1.借阅书籍                  
");printf("*          2.归还书籍                  
");printf("*          3.修改密码                  
");printf("*          4.返回主菜单                
");printf("*          0.退出本系统                
");printf("*                                     
");printf("***************************************
");
}

二.建表

1.管理员表（manager）

列名

数据类型

约束

说明

M_ID

INT

PRIMARY KEY

管理员ID编号

M_Account

VARCHAR(30)

NOT NULL

管理员账号

M-Password

VARCHAR(30)

NOT NULL

密码

2.图书信息表(book)

列名

数据类型

约束

说明

B_ID

INT

PRIMARY KEY

图书ID编号

B_Name

VARCHAR(30)

NOT NULL

图书名称

Author

VARCHAR(30)

NOT NULL

作者

Publish

VARCHAR(30)

NOT NULL

出版社名称

Price

DECIMAL(10,2)

NOT NULL

价格

Quantity

INT

DEFAULT 0

库存量

3.用户表(user)

列名

数据类型

约束

说明

U_ID

INT

PRIMARY KEY

用户ID编号

U_Account

VARCHAR(30)

NOT NULL

用户账号

U_Password

VARCHAR(30)

NOT NULL

密码

4.借书表(borrow_book)

列名

数据类型

约束

说明

U_ID

INT

FOREIGN KEY

用户ID编号

B_ID

INT

FOREIGN KEY

图书ID编号

Borrow_time

VARCHAR(30)

NOT NULL

借阅时间

Return_time

VARCHAR(30)

NOT NULL

归还时间

State

VARCHAR(30)

NOT NULL

状态

备注1：登录功能使用管理员账号/用户账号+密码进行登录，在这里省略了管理员/用户昵称。

备注2：价格DECIMAL对应VS中的float（浮点型）进行转化。

备注3：时间可以使用Date类型，但为了方便在VS中添加到数据库使用varchar类型，在VS上将当前时间转化为字符串进行添加。

备注4：借书表中U_ID和B_ID共同构成主码。

附上MySQL建表，数据插入代码：

CREATE TABLE manager(
M_ID INT PRIMARY KEY,
M_Account VARCHAR(30) NOT NULL,
M_Password VARCHAR(30) NOT NULL
);CREATE TABLE book(
B_ID INT PRIMARY KEY,
B_NAME VARCHAR(30) NOT NULL,
Author VARCHAR(30) NOT NULL,
Publish VARCHAR(30) NOT NULL,
Price DECIMAL(10,2) NOT NULL,
Quantity INT DEFAULT 0
);CREATE TABLE user(
U_ID INT PRIMARY KEY,
U_Account VARCHAR(30) NOT NULL,
U_Password VARCHAR(30) NOT NULL
);CREATE TABLE borrow_book(
U_ID INT NOT NULL,	
B_ID INT NOT NULL,
Borrow_time VARCHAR(30) NOT NULL,
Return_time VARCHAR(30),
State VARCHAR(30),
PRIMARY KEY (U_ID,B_ID),
FOREIGN KEY(U_ID) REFERENCES user(U_ID) ON DELETE CASCADE,
FOREIGN KEY(B_ID) REFERENCES book(B_ID) ON DELETE CASCADE
);-- 管理员表数据
INSERT INTO manager (M_ID, M_Account, M_Password) VALUES
(3, 'alice_admin', 'alice123'),
(4, 'bob_admin', 'bob123'),
(5, 'charlie_admin', 'charlie123');-- 书籍表数据
INSERT INTO book (B_ID, B_NAME, Author, Publish, Price, Quantity) VALUES
(101, '百年孤独', '加布里埃尔·加西亚·马尔克斯', '南海出版公司', 59.90, 20),
(102, '追风筝的人', '卡勒德·胡赛尼', '上海人民出版社', 39.50, 15),
(103, '解忧杂货店', '东野圭吾', '南海出版公司', 39.00, 30),
(104, '活着', '余华', '北京十月文艺出版社', 35.00, 25),
(105, '哈利·波特与魔法石', 'J.K.罗琳', '人民文学出版社', 69.00, 50);-- 用户表数据
INSERT INTO user (U_ID, U_Account, U_Password) VALUES
(201, 'diana_user', 'diana123'),
(202, 'eric_user', 'eric123'),
(203, 'fiona_user', 'fiona123'),
(204, 'george_user', 'george123'),
(205, 'hannah_user', 'hannah123');-- 借阅记录表数据
INSERT INTO borrow_book (U_ID, B_ID, Borrow_time, Return_time, State) VALUES
(201, 101, '2024-06-01', '2024-06-15', 'RETURNED'),
(202, 102, '2024-06-02', NULL, 'BORROWED'),
(203, 103, '2024-06-03', '2024-06-10', 'RETURNED'),
(204, 104, '2024-06-04', NULL, 'BORROWED'),
(205, 105, '2024-06-05', '2024-06-20', 'RETURNED'),
(201, 105, '2024-06-21', NULL, 'BORROWED');

三.环境配置

声明:本文使用Visual studio2022 编写，其他编译器可自行考量,下面进行环境配置。

首先，找到项目的属性页面，找到C/C++，在常规的附加包含目录中选中安装的MySQL文件中的Include文件

然后在连接器的常规中，找到附加库目录，选中mysql文件中的lib文件

在链接器的输入中，选择附加依赖项，手动输入libmysql.lib

最后一步，将MySQL文件lib文件中的libmysql.dll复制到项目的x64目录下。

这样环境就配置好了。

四.数据库连接

这是链接数据库和编译器的起点，系统整体的功能都在这个实现内部，注意设置字符编码，否则可能会出现乱码。

//初始化
MYSQL* con = mysql_init(NULL);//设置字符编码,以防出现乱码
mysql_options(con, MYSQL_SET_CHARSET_NAME, "GBK");//开始连接
if (!mysql_real_connect(con, host, use, password, database_name, port, NULL, 0))
{fprintf(stderr, "connect fail", mysql_error(con));return -1;
}//关闭连接
mysql_close(con);

五.添加、删除信息

在编译器中定义表格数据对应的结构体，在编译器中输入的数据存入结构体链表中，在通过sprintf将对应的MySQL插入/删除语句输出执行。本系统在VS上使用结构体链表储存对应表格的所有数据，因此设置三个链表的头指针：book_head,user_head,manager_head，初始化为NULL

//结构体定义
typedef struct manager_table
{int M_ID;char M_Account[30];char M_Password[30];struct manager_table* next;
}manager;typedef struct book_table
{int B_ID;char B_NAME[30];char Author[30];char Publish[30];float Price;int Quantity;struct book_table* next;
}book;typedef struct user_table
{int U_ID;char U_Account[30];char U_Password[30];struct user_table* next;
}user;//链表头节点指针book* book_head = NULL;user* user_head = NULL;manager* manager_head = NULL;

添加新书籍首先找到链表的尾部，创建新节点，将书籍信息输入，随后将mysql语句存入字符数组sql中，使用mysql_query即可。当然在编号输入中可以添加重复检查等功能，实现就是链表的遍历，在此不进行实现该功能。书籍添加实现，其余管理员添加，用户添加也是同理。

book* Add_new_book(book* head, MYSQL* mysql)
{book* newbook = (book*)malloc(sizeof(book));if (newbook == NULL){perror("malloc fail");exit(1);}if (head == NULL){head = newbook;}else{//找到尾节点book* pcur = head;while (pcur->next){pcur = pcur->next;}pcur->next = newbook;}printf("请输入书籍编号：");scanf("%d", &(newbook->B_ID));//检测编号重复（未实现）printf("请输入名称：");scanf("%s", newbook->B_NAME);printf("请输入作者：");scanf("%s", newbook->Author);printf("请输入出版社：");scanf("%s", newbook->Publish);printf("请输入价格：");scanf("%f", &(newbook->Price));printf("请输入库存量：");scanf("%d", &(newbook->Quantity));//将新增书籍信息存入数据库char sql[256];sprintf(sql, "insert into book values('%d','%s','%s','%s','%f','%d')",newbook->B_ID, newbook->B_NAME, newbook->Author, newbook->Publish, newbook->Price, newbook->Quantity);mysql_query(mysql, sql);newbook->next = NULL;printf("添加成功！
");return head;
}

删除书籍需要进行以下两步：找到对应编号的节点，然后释放该节点。防止删除错误，加入了删除时显示书籍信息并且进行二次确认功能。

//删除书籍信息
book* Delete_Book(book* head, MYSQL* mysql)
{book* p = head;book* pcur = NULL;int id, option;char sql[256];printf("请输入删除书籍的编号：");scanf("%d", &id);while (p){if (id == p->B_ID){break;}pcur = p;p = p->next;}if (p == NULL){printf("未找到此ID编号的书籍
");return 0;}else{printf("===================================================================================
");printf("|书名                    |作者            |出版社          |价格  |库存  |编号    |
");printf("-----------------------------------------------------------------------------------
");printf("|%-24s|%-16s|%-16s|%-6.2f|%-6d|%-8d|
", p->B_NAME, p->Author, p->Publish, p->Price, p->Quantity, p->B_ID);printf("===================================================================================
");printf("================
");printf("|是否确定删除？|
");printf("|0.取消  1.确定|
");printf("================
");scanf("%d", &option);switch (option){case 1:sprintf(sql, "delete from book where B_ID = %d;", id);mysql_query(mysql, sql);if (p == head){head = head->next;free(p);}else{pcur->next = p->next;free(p);}printf("已成功删除
");option = 0;break;case 0:printf("取消删除
");break;default:printf("输入错误
");break;}}return head;
}

六.加载数据库信息

在加载数据库的信息前需要定义res 和 row来查询结果集和记录结构体，随后将获得的结果插入进链表，将数据库的内容拷贝到编译器的链表中。

MYSQL_RES* res = NULL;//查询结果集
MYSQL_ROW row = NULL;//记录结构体//从数据库中加载书籍信息
book* load_Book(book* head, MYSQL_RES* res, MYSQL_ROW row, MYSQL* mysql)
{book* p = NULL;book* pcur = head;//查询数据mysql_query(mysql, "select * from book;");//获取结果集res = mysql_store_result(mysql);//将值赋为链表while (row = mysql_fetch_row(res)){p = NULL, pcur = head;p = (book*)malloc(sizeof(book));if (p == NULL){perror("malloc fail");exit(1);}if (head == NULL){head = p;}else{while (pcur->next != NULL){pcur = pcur->next;}pcur->next = p;}p->B_ID = atoi(row[0]);strcpy(p->B_NAME, row[1]);strcpy(p->Author, row[2]);strcpy(p->Publish, row[3]);p->Price = atoi(row[4]);p->Quantity = atoi(row[5]);p->next = NULL;}//释放结果集mysql_free_result(res);return head;
}

七.查找与登录

使用者输入账号存入account数组，遍历链表使用strcmp进行比较，匹配成功后再输入密码进行对比，密码有可能输入错误，设置循环再次尝试。

manager* manager_Login(manager* head)
{manager* p = head;char account[30], password[30];printf("请输入账号：");scanf("%s", account);while (p != NULL){if (!strcmp(account, p->M_Account)){break;}p = p->next;}if (p == NULL){printf("没有查找到该账号！
");return NULL;}else{while (1){printf("请输入密码：");scanf("%s", password);if (strcmp(password, p->M_Password) == 0){printf("成功登录！
");break;}else {printf("密码错误！请重新输入！
");}}}return p;
}

八.借阅与归还

借阅归还中与前面不同的就在于时间，在VS中得到当前日期的字符数组，可以这样实现：

#include<time.h>time_t rawtime;
struct tm* timeinfo;
char date_str[11]; // 足够存储 "YYYY-MM-DD" 格式的日期字符串// 获取当前时间的时间戳
time(&rawtime);// 将时间戳转换为本地时间
timeinfo = localtime(&rawtime);// 使用sprintf将年、月、日格式化为字符串
// 注意：tm_year是从1900年开始的，所以需要+1900
// tm_mon是从0开始的，所以需要+1
sprintf(date_str, "%d-%02d-%02d",timeinfo->tm_year + 1900,timeinfo->tm_mon + 1,timeinfo->tm_mday);

因此在借阅时，填入借阅时间，并将状态改为“BORROWED”，在归还时填入归还时间，并将状态改为“RETURNED”即可。

//借阅图书
int borrow_book(int id, MYSQL_RES* res, MYSQL_ROW row, MYSQL* mysql, book* head)
{Show_remaining_book(res, row, mysql);char sql[256];int b_id;book* p = head;printf("请输入要借阅的书籍编号：");scanf("%d", &b_id);while (head != NULL){if (b_id == p->B_ID){sprintf_s(sql, sizeof(sql), "select * from book where B_ID = %d;", b_id);mysql_query(mysql, sql);res = mysql_store_result(mysql);row = mysql_fetch_row(res);if (atoi(row[5]) <= 0){printf("库存量不足！
");return 0;}else{time_t rawtime;struct tm* timeinfo;char date_str[11]; // 足够存储 "YYYY-MM-DD" 格式的日期字符串// 获取当前时间的时间戳time(&rawtime);// 将时间戳转换为本地时间timeinfo = localtime(&rawtime);// 使用sprintf将年、月、日格式化为字符串// 注意：tm_year是从1900年开始的，所以需要+1900// tm_mon是从0开始的，所以需要+1sprintf(date_str, "%d-%02d-%02d",timeinfo->tm_year + 1900,timeinfo->tm_mon + 1,timeinfo->tm_mday);sprintf(sql, "insert into borrow_book values (%d,%d,'%s','%s','%s');", id, b_id, date_str, "NULL", "BORROWED");mysql_query(mysql, sql);sprintf(sql, "update book set Quantity = Quantity - 1 where B_ID = %d;", b_id);mysql_query(mysql, sql);printf("借阅成功!
");return 0;}}p = p->next;}if (p == NULL) {printf("图书编号不存在
");}return 0;
}
//归还图书
int return_book(int id, MYSQL_RES* res, MYSQL_ROW row, MYSQL* mysql, book* head)
{int b_id;book* p = head;char sql[256];printf("请输入要归还的书籍编号：");scanf("%d", &b_id);while (p != NULL){if (b_id == p->B_ID){time_t rawtime;struct tm* timeinfo;char date_str[11];time(&rawtime);timeinfo = localtime(&rawtime);sprintf(date_str, "%d-%02d-%02d",timeinfo->tm_year + 1900,timeinfo->tm_mon + 1,timeinfo->tm_mday);sprintf(sql, "update book set Quantity = Quantity + 1 where B_ID = %d;", p->B_ID);mysql_query(mysql, sql);sprintf(sql, "update borrow_book set Return_time = '%s', State = '%s' where U_ID = %d AND B_ID = %d;", date_str, "RETURNED", id, p->B_ID);mysql_query(mysql, sql);printf("归还成功!
");break;}p = p->next;}if (p == NULL) {printf("不存在该借出的图书！
");}return 0;
}

九.完整代码

备注：如果是C++要进行封装，可以建立对象book、manager、user等类，放入对应的结构体和函数方法即可实现，以下代码是按照C语言编写。

头文件：Library.h

#pragma once
#include<stdio.h>
#include<stdlib.h>
#include<string.h>
#include<mysql.h>
#include<time.h>//结构体定义
typedef struct manager_table
{int M_ID;char M_Account[30];char M_Password[30];struct manager_table* next;
}manager;typedef struct book_table
{int B_ID;char B_NAME[30];char Author[30];char Publish[30];float Price;int Quantity;struct book_table* next;
}book;typedef struct user_table
{int U_ID;char U_Account[30];char U_Password[30];struct user_table* next;
}user;//函数定义//展示菜单
void Menu();
void manager_Menu();
void reader_Menu(user* head);
void show_Menu_user(user* head, MYSQL_RES* res, MYSQL_ROW row, MYSQL* mysql);//从数据库中加载书籍信息
book* load_Book(book* head, MYSQL_RES* res, MYSQL_ROW row, MYSQL* mysql);//添加新管理员
manager* Add_new_manager(manager* head, MYSQL* mysql);
//添加新借书记录
//borrow_book* Add_new_borrow(borrow_book* head, MYSQL* mysql);//从数据库中加载书籍信息
book* load_Book(book* head, MYSQL_RES* res, MYSQL_ROW row, MYSQL* mysql);
//从数据库中加载借阅信息//添加新书籍
book* Add_new_book(book* head, MYSQL* mysql);
//展示书籍信息
void Show_All_book(book* head);
//查找书籍信息
void Search_book(book* head);
//删除书籍信息
book* Delete_Book(book* head, MYSQL* mysql);
//修改书籍信息
book* Change_book(book* head, MYSQL* mysql);//添加新用户
user* Add_new_user(user* head, MYSQL* mysql);
//展示所有的用户信息
void Show_All_user(user* head);
//从数据库中加载用户信息
user* load_User(user* head, MYSQL_RES* res, MYSQL_ROW row, MYSQL* mysql);
//从数据库中加载管理员信息
user* load_Manager(user* head, MYSQL_RES* res, MYSQL_ROW row, MYSQL* mysql);
//修改用户密码
int Change_User_password(user* head, MYSQL* mysql);
//用户登陆
user* user_Login(user* head);
//管理员登录
manager* manager_Login(manager* head);//显示有库存的书籍
void Show_remaining_book(MYSQL_RES* res, MYSQL_ROW row, MYSQL* mysql);
//借阅图书
int borrow_book(int id, MYSQL_RES* res, MYSQL_ROW row, MYSQL* mysql, book* head);
//归还图书
int return_book(int id, MYSQL_RES* res, MYSQL_ROW row, MYSQL* mysql, book* head);

源文件：Library.c

#define _CRT_SECURE_NO_WARNINGS 1
#include"Library.h"//初始菜单
void Menu()
{printf("***************************************
");printf("*      欢迎进入线上图书管理系统！     *
");printf("*                                     *
");printf("*            1.管理员登录             *
");printf("*            2.读者登录               *
");printf("*            3.读者注册               *
");printf("*            0.退出本系统             *
");printf("***************************************
");
}
void manager_Menu()
{printf("***************************************
");printf("*            欢迎你，管理员            
");printf("*                                     
");printf("*            1.添加书籍              
");printf("*            2.显示书籍              
");printf("*            3.查找书籍               
");printf("*            4.修改书籍               
");printf("*            5.删除书籍               
");printf("*            6.显示用户数据            
");printf("*            7.返回主菜单              
");printf("*            8.添加新管理员              
");printf("*            0.退出本系统             
");printf("***************************************
");
}
void reader_Menu(user* head)
{printf("***************************************
");printf("*          欢迎你，读者           
");printf("*                                     
");printf("*          1.借阅书籍                  
");printf("*          2.归还书籍                  
");printf("*          3.修改密码                  
");printf("*          4.返回主菜单                
");printf("*          0.退出本系统                
");printf("*                                     
");printf("***************************************
");
}//从数据库中加载书籍信息
book* load_Book(book* head, MYSQL_RES* res, MYSQL_ROW row, MYSQL* mysql)
{book* p = NULL;book* pcur = head;//查询数据mysql_query(mysql, "select * from book;");//获取结果集res = mysql_store_result(mysql);//将值赋为链表while (row = mysql_fetch_row(res)){p = NULL, pcur = head;p = (book*)malloc(sizeof(book));if (p == NULL){perror("malloc fail");exit(1);}if (head == NULL){head = p;}else{while (pcur->next != NULL){pcur = pcur->next;}pcur->next = p;}p->B_ID = atoi(row[0]);strcpy(p->B_NAME, row[1]);strcpy(p->Author, row[2]);strcpy(p->Publish, row[3]);p->Price = atoi(row[4]);p->Quantity = atoi(row[5]);p->next = NULL;}//释放结果集mysql_free_result(res);return head;
}//添加新书籍
book* Add_new_book(book* head, MYSQL* mysql)
{book* newbook = (book*)malloc(sizeof(book));if (newbook == NULL){perror("malloc fail");exit(1);}if (head == NULL){head = newbook;}else{//找到尾节点book* pcur = head;while (pcur->next){pcur = pcur->next;}pcur->next = newbook;}printf("请输入书籍编号：");scanf("%d", &(newbook->B_ID));//检测编号重复printf("请输入名称：");scanf("%s", newbook->B_NAME);printf("请输入作者：");scanf("%s", newbook->Author);printf("请输入出版社：");scanf("%s", newbook->Publish);printf("请输入价格：");scanf("%f", &(newbook->Price));printf("请输入库存量：");scanf("%d", &(newbook->Quantity));//将新增书籍信息存入数据库char sql[256];sprintf(sql, "insert into book values('%d','%s','%s','%s','%f','%d')",newbook->B_ID, newbook->B_NAME, newbook->Author, newbook->Publish, newbook->Price, newbook->Quantity);mysql_query(mysql, sql);newbook->next = NULL;printf("添加成功！
");return head;
}
//添加新用户
user* Add_new_user(user* head, MYSQL* mysql)
{user* newuser = (user*)malloc(sizeof(user));user* pcur = head;if (newuser == NULL){perror("malloc fail");exit(1);}if (head == NULL){head = newuser;}else{//找到尾节点book* pcur = head;while (pcur->next){pcur = pcur->next;}pcur->next = newuser;}printf("请输入用户编号：");scanf("%d", &(newuser->U_ID));printf("请输入用户账号：");scanf("%s", newuser->U_Account);printf("请输入用户密码：");scanf("%s", newuser->U_Password);char sql[256];sprintf(sql, "insert into user values('%d','%s','%s')",newuser->U_ID, newuser->U_Account, newuser->U_Password);mysql_query(mysql, sql);newuser->next = NULL;printf("添加成功！
");return head;
}
//添加新管理员
manager* Add_new_manager(manager* head, MYSQL* mysql)
{manager* newmanager = (manager*)malloc(sizeof(manager));user* pcur = head;if (newmanager == NULL){perror("malloc fail");exit(1);}if (head == NULL){head = newmanager;}else{//找到尾节点book* pcur = head;while (pcur->next){pcur = pcur->next;}pcur->next = newmanager;}printf("请输入管理员编号：");scanf("%d", &(newmanager->M_ID));printf("请输入管理员账号：");scanf("%s", newmanager->M_Account);printf("请输入管理员密码：");scanf("%s", newmanager->M_Password);char sql[256];sprintf(sql, "insert into manager values('%d','%s','%s')",newmanager->M_ID, newmanager->M_Account, newmanager->M_Password);mysql_query(mysql, sql);newmanager->next = NULL;printf("添加成功！
");return head;
}//展示书籍信息
void Show_All_book(book* head)
{book* p = head;printf("===================================================================================
");printf("|书名                    |作者            |出版社          |价格  |库存  |编号    |
");while (p != NULL) {printf("-----------------------------------------------------------------------------------
");printf("|%-24s|%-16s|%-16s|%-6.2f|%-6d|%-8d|
", p->B_NAME, p->Author, p->Publish, p->Price, p->Quantity, p->B_ID);p = p->next;}printf("===================================================================================
");}
//查找书籍信息
void Search_book(book* head)
{book* p = head;char search[100];int option;printf("=======================
");printf("|   请输入查询的项目  |
");printf("|    1.图书名         |
");printf("|    2.书作者         |
");printf("|    3.出版社         |
");printf("=======================
");scanf("%d", &option);while (1){printf("=======================
");printf("|   请输入查询的内容   |
");printf("=======================
");scanf("%s", search);printf("===================================================================================
");printf("|书名                    |作者            |出版社          |价格  |库存  |编号    |
");int flag = 1;while (p != NULL){switch (option){case 1:if (strstr(p->B_NAME, search)){flag = 0;printf("-----------------------------------------------------------------------------------
");printf("|%-24s|%-16s|%-16s|%-6.2f|%-6d|%-8d|
", p->B_NAME, p->Author, p->Publish, p->Price, p->Quantity, p->B_ID);}break;case 2:if (strstr(p->Author, search)){flag = 0;printf("-----------------------------------------------------------------------------------
");printf("|%-24s|%-16s|%-16s|%-6.2f|%-6d|%-8d|
", p->B_NAME, p->Author, p->Publish, p->Price, p->Quantity, p->B_ID);}break;case 3:if (strstr(p->Publish, search)){flag = 0;printf("-----------------------------------------------------------------------------------
");printf("|%-24s|%-16s|%-16s|%-6.2f|%-6d|%-8d|
", p->B_NAME, p->Author, p->Publish, p->Price, p->Quantity, p->B_ID);}break;}p = p->next;}if (flag){printf("-----------------------------------------------------------------------------------
");printf("|%-24s|%-16s|%-16s|%-6s|%-6s|%-8s|
", "无", "无", "无", "无", "无", "无");}printf("===================================================================================
");break;}
}
//删除书籍信息
book* Delete_Book(book* head, MYSQL* mysql)
{book* p = head;book* pcur = NULL;int id, option;char sql[256];printf("请输入删除书籍的编号：");scanf("%d", &id);while (p){if (id == p->B_ID){break;}pcur = p;p = p->next;}if (p == NULL){printf("未找到此ID编号的书籍
");return 0;}else{printf("===================================================================================
");printf("|书名                    |作者            |出版社          |价格  |库存  |编号    |
");printf("-----------------------------------------------------------------------------------
");printf("|%-24s|%-16s|%-16s|%-6.2f|%-6d|%-8d|
", p->B_NAME, p->Author, p->Publish, p->Price, p->Quantity, p->B_ID);printf("===================================================================================
");printf("================
");printf("|是否确定删除？|
");printf("|0.取消  1.确定|
");printf("================
");scanf("%d", &option);switch (option){case 1:sprintf(sql, "delete from book where B_ID = %d;", id);mysql_query(mysql, sql);if (p == head){head = head->next;free(p);}else{pcur->next = p->next;free(p);}printf("已成功删除
");option = 0;break;case 0:printf("取消删除
");break;default:printf("输入错误
");break;}}return head;
}
//修改书籍信息
book* Change_book(book* head, MYSQL* mysql)
{int id;int option = 0;book* p = head;char sql[256];printf("请输入你要修改的书籍编号：");scanf("%d", &id);while (p){if (id == p->B_ID){break;}p = p->next;}if (p == NULL){printf("未找到此ID编号的书籍
");return 0;}else{do{printf("===================================================================================
");printf("|书名                    |作者            |出版社          |价格  |库存  |编号    |
");printf("-----------------------------------------------------------------------------------
");printf("|%-24s|%-16s|%-16s|%-6.2f|%-6d|%-8d|
", p->B_NAME, p->Author, p->Publish, p->Price, p->Quantity, p->B_ID);printf("===================================================================================
");printf("================
");printf("|请选择修改的内容|
");printf("|     1.书名    |
");printf("|     2.作者    |
");printf("|    3.出版社   |
");printf("|     4.价格    |
");printf("|    5.库存量   |
");printf("|   0.退出修改  |
");printf("================
");scanf("%d", &option);switch (option){case 1:printf("请输入修改后的书名：");scanf("%s", p->B_NAME);break;case 2:printf("请输入修改后的作者：");scanf("%s", p->Author);break;case 3:printf("请输入修改后的出版社：");scanf("%s", p->Publish);break;case 4:printf("请输入修改后的价格：");scanf("%f", &(p->Price));break;case 5:printf("请输入修改后的库存量：");scanf("%d", &(p->Quantity));break;case 0:break;}} while (option);}sprintf(sql, "update book set B_NAME = '%s', Author = '%s', Publish = '%s', Price = %f, Quantity = %d where B_ID = %d;",p->B_NAME, p->Author, p->Publish, p->Price, p->Quantity, p->B_ID);mysql_query(mysql, sql);printf("修改成功！
");return 0;
}//展示所有的用户信息
void Show_All_user(user* head)
{user* p = head;printf("=========================================================
");printf("|用户编号                   |账号          |密码          |
");while (p != NULL) {printf("---------------------------------------------------------
");printf("|%-27d|%-14s|%-14s|
", p->U_ID, p->U_Account, p->U_Password);p = p->next;}printf("=========================================================
");
}
//从数据库中加载用户信息
user* load_User(user* head, MYSQL_RES* res, MYSQL_ROW row, MYSQL* mysql)
{user* p = NULL, * pr = head;mysql_query(mysql, "select * from user;");res = mysql_store_result(mysql);while (row = mysql_fetch_row(res)){p = NULL, pr = head;p = (user*)malloc(sizeof(user));if (p == NULL){perror("malloc fail");exit(1);}if (head == NULL){head = p;}else{while (pr->next != NULL){pr = pr->next;}pr->next = p;}p->U_ID = atoi(row[0]);strcpy(p->U_Account, row[1]);strcpy(p->U_Password, row[2]);p->next = NULL;}mysql_free_result(res);return head;
}
//从数据库中加载管理员信息
user* load_Manager(user* head, MYSQL_RES* res, MYSQL_ROW row, MYSQL* mysql)
{manager* p = NULL, * pr = head;mysql_query(mysql, "select * from manager;");res = mysql_store_result(mysql);while (row = mysql_fetch_row(res)){p = NULL, pr = head;p = (manager*)malloc(sizeof(manager));if (p == NULL){perror("malloc fail");exit(1);}if (head == NULL){head = p;}else{while (pr->next != NULL){pr = pr->next;}pr->next = p;}p->M_ID = atoi(row[0]);strcpy(p->M_Account, row[1]);strcpy(p->M_Password, row[2]);p->next = NULL;}mysql_free_result(res);return head;
}
//修改用户密码
int Change_User_password(user* head, MYSQL* mysql)
{char account[30];char password[30];char sql[256];int option;user* p = head;printf("请输入用户账号：");scanf("%s", account);while (p){if (!strcmp(account, p->U_Account)){break;}p = p->next;}if (p == NULL){printf("没有查找到该用户
");return 0;}else{printf("==============================
");printf("|账号                |编号  |
");printf("------------------------------
");printf("|%-20s|%6d|
", p->U_Account, p->U_ID);printf("==============================
");}while (1){printf("请输入该账号密码:");scanf("%s", password);if (strcmp(password, p->U_Password) == 0){printf("请输入新密码：");scanf("%s", password);sprintf(sql, "update user set U_Password = '%s' where U_ID = %d;", password, p->U_ID);mysql_query(mysql, sql);printf("修改成功！");break;}else{printf("密码错误,请重试
");}}
}
//用户登陆
user* user_Login(user* head)
{user* p = head;char account[30], password[30];printf("请输入账号：");scanf("%s", account);while (p != NULL){if (!strcmp(account, p->U_Account)){break;}p = p->next;}if (p == NULL){printf("没有查找到该账号！
");return 0;}else{while (1){printf("请输入密码：");scanf("%s", password);if (strcmp(password, p->U_Password) == 0){printf("成功登录！
");break;}else {printf("密码错误！请重新输入！
");}}}return p;
}
//管理员登录
manager* manager_Login(manager* head)
{manager* p = head;char account[30], password[30];printf("请输入账号：");scanf("%s", account);while (p != NULL){if (!strcmp(account, p->M_Account)){break;}p = p->next;}if (p == NULL){printf("没有查找到该账号！
");return NULL;}else{while (1){printf("请输入密码：");scanf("%s", password);if (strcmp(password, p->M_Password) == 0){printf("成功登录！
");break;}else {printf("密码错误！请重新输入！
");}}}return p;
}//显示有库存的书籍
void Show_remaining_book(MYSQL_RES* res, MYSQL_ROW row, MYSQL* mysql)
{mysql_query(mysql, "select * from book;");res = mysql_store_result(mysql);printf("===================================================================================
");printf("|书名                    |作者            |出版社          |库存量         |编号    
");while (row = mysql_fetch_row(res)) {//若余量大于0则输出if (atoi(row[5]) > 0) {printf("-----------------------------------------------------------------------------------
");printf("|%-24s|%-16s|%-16s|%-15d|%-8d
", row[1], row[2], row[3], atoi(row[5]), atoi(row[0]));}}printf("===================================================================================
");
}
//借阅图书
int borrow_book(int id, MYSQL_RES* res, MYSQL_ROW row, MYSQL* mysql, book* head)
{Show_remaining_book(res, row, mysql);char sql[256];int b_id;book* p = head;printf("请输入要借阅的书籍编号：");scanf("%d", &b_id);while (head != NULL){if (b_id == p->B_ID){sprintf_s(sql, sizeof(sql), "select * from book where B_ID = %d;", b_id);mysql_query(mysql, sql);res = mysql_store_result(mysql);row = mysql_fetch_row(res);if (atoi(row[5]) <= 0){printf("库存量不足！
");return 0;}else{time_t rawtime;struct tm* timeinfo;char date_str[11]; // 足够存储 "YYYY-MM-DD" 格式的日期字符串// 获取当前时间的时间戳time(&rawtime);// 将时间戳转换为本地时间timeinfo = localtime(&rawtime);// 使用sprintf将年、月、日格式化为字符串// 注意：tm_year是从1900年开始的，所以需要+1900// tm_mon是从0开始的，所以需要+1sprintf(date_str, "%d-%02d-%02d",timeinfo->tm_year + 1900,timeinfo->tm_mon + 1,timeinfo->tm_mday);sprintf(sql, "insert into borrow_book values (%d,%d,'%s','%s','%s');", id, b_id, date_str, "NULL", "BORROWED");mysql_query(mysql, sql);sprintf(sql, "update book set Quantity = Quantity - 1 where B_ID = %d;", b_id);mysql_query(mysql, sql);printf("借阅成功!
");return 0;}}p = p->next;}if (p == NULL) {printf("图书编号不存在
");}return 0;
}
//归还图书
int return_book(int id, MYSQL_RES* res, MYSQL_ROW row, MYSQL* mysql, book* head)
{int b_id;book* p = head;char sql[256];printf("请输入要归还的书籍编号：");scanf("%d", &b_id);while (p != NULL){if (b_id == p->B_ID){time_t rawtime;struct tm* timeinfo;char date_str[11];time(&rawtime);timeinfo = localtime(&rawtime);sprintf(date_str, "%d-%02d-%02d",timeinfo->tm_year + 1900,timeinfo->tm_mon + 1,timeinfo->tm_mday);sprintf(sql, "update book set Quantity = Quantity + 1 where B_ID = %d;", p->B_ID);mysql_query(mysql, sql);sprintf(sql, "update borrow_book set Return_time = '%s', State = '%s' where U_ID = %d AND B_ID = %d;", date_str, "RETURNED", id, p->B_ID);mysql_query(mysql, sql);printf("归还成功!
");break;}p = p->next;}if (p == NULL) {printf("不存在该借出的图书！
");}return 0;
}

源文件：Test.c

在该文件前要定义自己的数据库数据：

Test02()
{MYSQL_RES* res = NULL;//查询结果集MYSQL_ROW row = NULL;//记录结构体//初始化MYSQL* con = mysql_init(NULL);//设置字符编码,以防出现乱码mysql_options(con, MYSQL_SET_CHARSET_NAME, "GBK");//开始连接if (!mysql_real_connect(con, host, use, password, database_name, port, NULL, 0)){fprintf(stderr, "connect fail", mysql_error(con));return -1;}book* book_head = NULL;user* user_head = NULL;manager* manager_head = NULL;book_head = load_Book(book_head, res, row, con);user_head = load_User(user_head, res, row, con);manager_head = load_Manager(manager_head, res, row, con);//主体int input = 0;do{Menu();scanf("%d", &input);switch (input){case 1:if (manager_Login(manager_head)){int input2 = 0;do{manager_Menu();scanf("%d", &input2);switch (input2){case 1:Add_new_book(book_head, con);break;case 2:Show_All_book(book_head);break;case 3:Search_book(book_head);break;case 4:Change_book(book_head,con);break;case 5:Delete_Book(book_head, con);break;case 6:Show_All_user(user_head);break;case 7:input2 = 0;break;case 8:Add_new_manager(manager_head, con);break;case 0:input2 = 0;input = 0;break;}} while (input2);}break;case 2:printf("
");user * cur = user_Login(user_head);if (cur){int input3 = 0;do{reader_Menu(user_head);scanf("%d", &input3);switch (input3){case 1:borrow_book(cur->U_ID, res, row, con, book_head);break;case 2:return_book(cur->U_ID, res, row, con, book_head);break;case 3:Change_User_password(user_head, con);break;case 4:input3 = 0;break;case 0:input3 = 0;input = 0;break;}} while (input3);}break;case 3:Add_new_user(user_head, con);break;}} while (input);printf("再会！
");//关闭连接mysql_close(con);
}int main()
{//Test01();Test02();return 0;
}