选题背景
依照图书管理系统的要求,完成如下任务
- 设计适合此应用需求的规范化的数据库;
- 建立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: 0
mysql> 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: 0
mysql> 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: 0
mysql> 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: 0
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 | 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)
以上是本人数据库的课设,选题是图书管理系统,这一篇是相关数据库设计,网站制作期间如有相关问题会修正,后期还会发布关于网站设计部分,希望各位兄弟姐妹能够多多指教哦!