admin 管理员组文章数量: 887021
图书管理系统
选题背景
依照图书管理系统的要求，完成如下任务
- 设计适合此应用需求的规范化的数据库；
- 建立ODBC数据源；
- 创建数据库连接；
- 制作一个具有安全性控制（权限管理和视图管理等）的数据库访问程序。
数据项
借阅人信息：学号(stuid)，姓名(name)，性别(sex)，科别代号(depno)，科别名(depname)，年级(grade)，班级(class)，电话(tel)，地址(addr)
书籍信息：书籍编号(code)，书籍名称(bookname)，作者(writer)，出版商(publisher)，购买日期(buydate)，借阅次数(number)，是否借出(isloan)
借阅信息：借书人号，书号，借阅日期(loandate)，归还日期(returndate)
任务要求
- 学生和图书信息的增删查改及查询操作；
- 借书和还书操作。（其中图书管理员可以更新书库；图书操作人员可以进行借、还书操作；借书人可以查询图书及自己的借阅情况。
实验环境
VSCode
- 创建数据库
mysql> create database library;
Query OK, 1 row affected (0.01 sec)mysql> use library;
Database changed
- 创建表
/* 借阅人信息 */
mysql> create table user(-> stuid varchar(10) primary key,-> name varchar(20) not null,-> sex varchar(20),-> depno varchar(20),-> depname varchar(20),-> grade varchar(20),-> class varchar(20),-> tel varchar(20),-> addr varchar(20)-> );
Query OK, 0 rows affected (0.03 sec)mysql> insert-> into user-> values('2005120220','张三','男','321001','网络空间安全','20级','网安2班','13207527332','武汉工程大学');
Query OK, 1 row affected (0.00 sec)mysql> insert-> into user-> values('2005120213','李四','女','320211','人工智能','20级','人工智能3班','18801447232','武汉工程大学');
Query OK, 1 row affected (0.00 sec)mysql> insert-> into user-> values('2006121110','王五','男','416627','计算机科学与技术','21级','计科6班','18707527556','武汉工程大学');
Query OK, 1 row affected (0.00 sec)mysql> insert-> into user-> values('2004120220','李晨','男','132001','网络空间安全','19级','网安5班','17288644522','武汉大学');
Query OK, 1 row affected (0.00 sec)mysql> select * from user;
+------------+------+------+--------+------------------+-------+-------------+-------------+--------------+
| stuid | name | sex | depno | depname | grade | class | tel | addr |
+------------+------+------+--------+------------------+-------+-------------+-------------+--------------+
| 2004120220 | 李晨 | 男 | 132001 | 网络空间安全 | 19级 | 网安5班 | 17288644522 | 武汉大学 |
| 2005120213 | 李四 | 女 | 320211 | 人工智能 | 20级 | 人工智能3班 | 18801447232 | 武汉工程大学 |
| 2005120220 | 张三 | 男 | 321001 | 网络空间安全 | 20级 | 网安2班 | 13207527332 | 武汉工程大学 |
| 2006121110 | 王五 | 男 | 416627 | 计算机科学与技术 | 21级 | 计科6班 | 18707527556 | 武汉工程大学 |
+------------+------+------+--------+------------------+-------+-------------+-------------+--------------+
4 rows in set (0.00 sec)/* 书籍信息 */
mysql> create table books(-> code varchar(10) primary key,-> bookname varchar(50),-> writer varchar(20),-> pubilsher varchar(50),-> buydate varchar(20),-> number varchar(20),-> isloan varchar(10)-> );
Query OK, 0 rows affected (0.02 sec)mysql> insert-> into books-> values('001','狂人日记','鲁迅','中国商业出版社','2017-07-13','23','否');
Query OK, 1 row affected (0.01 sec)mysql> insert-> into books-> values('002','老人与海','海明威','南海出版社','2016-03-29','122','是');
Query OK, 1 row affected (0.00 sec)mysql> insert-> into books-> values('003','放学后','东野圭吾','南海出版社','2022-07-24','179','否');
Query OK, 1 row affected (0.00 sec)mysql> insert-> into books-> values('004','三国演义','罗贯中','人民出版社','2019-12-21','765','是');
Query OK, 1 row affected (0.00 sec)mysql> insert-> into books-> values('005','计算机操作系统','汤小丹','电子科技大学出版社','2020-10-12','1324','是');
Query OK, 1 row affected (0.01 sec)mysql> insert-> into books-> values('006','通信原理','樊昌信','高等教育出版社','2019-08-19','89','否');
Query OK, 1 row affected (0.00 sec)mysql> insert-> into books-> values('007','数据库系统概论','王珊','高等教育出版社','2018-11-23','72','是');
Query OK, 1 row affected (0.00 sec)mysql> select * from books;
+------+----------------+----------+--------------------+------------+--------+--------+
| code | bookname | writer | pubilsher | buydate | number | isloan |
+------+----------------+----------+--------------------+------------+--------+--------+
| 001 | 狂人日记 | 鲁迅 | 中国商业出版社 | 2017-07-13 | 23 | 否 |
| 002 | 老人与海 | 海明威 | 南海出版社 | 2016-03-29 | 122 | 是 |
| 003 | 放学后 | 东野圭吾 | 南海出版社 | 2022-07-24 | 179 | 否 |
| 004 | 三国演义 | 罗贯中 | 人民出版社 | 2019-12-21 | 765 | 是 |
| 005 | 计算机操作系统 | 汤小丹 | 电子科技大学出版社 | 2020-10-12 | 1324 | 是 |
| 006 | 通信原理 | 樊昌信 | 高等教育出版社 | 2019-08-19 | 89 | 否 |
| 007 | 数据库系统概论 | 王珊 | 高等教育出版社 | 2018-11-23 | 72 | 是 |
+------+----------------+----------+--------------------+------------+--------+--------+
7 rows in set (0.00 sec)/* 借阅信息 */mysql> create table records(-> stuid varchar(10),-> code varchar(10),-> loandate varchar(20),-> returndate varchar(20),-> primary key(stuid,code),-> foreign key(stuid) references user(stuid),-> foreign key(code) references books(code)-> );
Query OK, 0 rows affected (0.07 sec)mysql> insert-> into records-> values('2004120220','002','2023-1-1',null);
Query OK, 1 row affected (0.01 sec)mysql> insert-> into records-> values('2004120220','007','2022-01-02',null);
Query OK, 1 row affected (0.00 sec)mysql> update recodes-> set loandate='2021-07-18'-> where stuid='2004120220' and code ='002';
ERROR 1146 (42S02): Table 'library.recodes' doesn't exist
mysql> update records-> set loandate='2021-07-18'-> where stuid='2004120220' and code ='002';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> update records-> set returndate='2021-08-01'-> where stuid='2004120220' and code ='002';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from records;
+------------+------+------------+------------+
| stuid | code | loandate | returndate |
+------------+------+------------+------------+
| 2004120220 | 002 | 2021-07-18 | 2021-08-01 |
| 2004120220 | 007 | 2022-01-02 | NULL |
+------------+------+------------+------------+
2 rows in set (0.00 sec)mysql> insert-> into records-> values('2005120213','001','2022-03-23','2022-04-12');
Query OK, 1 row affected (0.00 sec)mysql> insert-> into records-> values('2005120213','004','2021-09-27','2021-10-14');
Query OK, 1 row affected (0.00 sec)mysql> insert-> into records-> values('2005120213','006','2023-02-03',null);
Query OK, 1 row affected (0.00 sec)mysql> insert-> into records-> values('2005120220','005','2022-09-21','2022-09-30');
Query OK, 1 row affected (0.00 sec)mysql> insert-> into records-> values('2006121110','003','2021-09-22','2021-10-13');
Query OK, 1 row affected (0.00 sec)mysql> select * from records;
+------------+------+------------+------------+
| stuid | code | loandate | returndate |
+------------+------+------------+------------+
| 2004120220 | 002 | 2021-07-18 | 2021-08-01 |
| 2004120220 | 007 | 2022-01-02 | NULL |
| 2005120213 | 001 | 2022-03-23 | 2022-04-12 |
| 2005120213 | 004 | 2021-09-27 | 2021-10-14 |
| 2005120213 | 006 | 2023-02-03 | NULL |
| 2005120220 | 005 | 2022-09-21 | 2022-09-30 |
| 2006121110 | 003 | 2021-09-22 | 2021-10-13 |
+------------+------+------------+------------+
7 rows in set (0.00 sec)mysql> insert-> into records-> values('2005120220','004','2022-12-21',null);
Query OK, 1 row affected (0.00 sec)mysql> insert-> into records-> values('2006121110','005','2023-2-1',null);
Query OK, 1 row affected (0.00 sec)mysql> update-> );
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 2
mysql> update records-> set returndate='202300203'-> where stuid='2005120213' and code='006';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> update records-> set returndate='2023-02-03'-> where stuid='2005120213' and code='006';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from records-> ;
+------------+------+------------+------------+
| stuid | code | loandate | returndate |
+------------+------+------------+------------+
| 2004120220 | 002 | 2021-07-18 | 2021-08-01 |
| 2004120220 | 007 | 2022-01-02 | NULL |
| 2005120213 | 001 | 2022-03-23 | 2022-04-12 |
| 2005120213 | 004 | 2021-09-27 | 2021-10-14 |
| 2005120213 | 006 | 2023-02-03 | 2023-02-03 |
| 2005120220 | 004 | 2022-12-21 | NULL |
| 2005120220 | 005 | 2022-09-21 | 2022-09-30 |
| 2006121110 | 003 | 2021-09-22 | 2021-10-13 |
| 2006121110 | 005 | 2023-2-1 | NULL |
+------------+------+------------+------------+
9 rows in set (0.00 sec)
- 创建用户
mysql> grant all privileges on library.records to "李四"@"%";
Query OK, 0 rows affected (0.00 sec)mysql> create user "王五" identified by "wangwu123456";
Query OK, 0 rows affected (0.01 sec)mysql> grant select on library.records to "王五"@"%";
Query OK, 0 rows affected (0.00 sec)mysql> grant select,update on library.user to "王五"@"%";
Query OK, 0 rows affected (0.01 sec)mysql> grant all privileges on library to "张三"@"%";
Query OK, 0 rows affected (0.00 sec)
以上是本人数据库的课设，选题是图书管理系统，这一篇是相关数据库设计，网站制作期间如有相关问题会修正，后期还会发布关于网站设计部分，希望各位兄弟姐妹能够多多指教哦！
本文标签： 图书管理系统
版权声明：本文标题：图书管理系统 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.freenas.com.cn/jishu/1687562675h116159.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论