图书管理系统小作业

创建数据库

CREATE DATABASE `book_management`; 
USE book_management;

读者表

CREATE TABLE `book_management`.`reader` 
( `r_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '学号', 
`r_name` VARCHAR(11) NOT NULL COMMENT '姓名', 
`r_sex` CHAR(1) NOT NULL COMMENT '性别', 
`accumulative` INT(10) UNSIGNED DEFAULT 0 COMMENT '累计借书',
`violation` INT(5) DEFAULT 0 COMMENT '违规次数',
`department` VARCHAR(25) NOT NULL COMMENT '所在院系',
`r_studentid` INT(11) NOT NULL COMMENT '学号',
PRIMARY KEY (`s_id`) )ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_bin; 

书籍表

CREATE TABLE `book_management`.`book` 
( `b_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '书籍编号',
 `p_date` DATE COMMENT '出版日期',
 `p_house` VARCHAR(25) COMMENT '出版社', 
 `Author` VARCHAR(25) NOT NULL COMMENT '作者',
  `name` VARCHAR(33) NOT NULL COMMENT '书名', 
  `test` VARCHAR(200) COMMENT  '简介',
   PRIMARY KEY (`b_id`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_bin; 

管理员表

CREATE TABLE `book_management`.`manager`
( `m_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '管理员编号',
 `selery` INT COMMENT '工资',
  `m_name` VARCHAR(11) NOT NULL COMMENT '姓名', 
  `m_sex` CHAR(1) COMMENT '性别', `
  phone` INT(13) NOT NULL COMMENT '电话', 
  PRIMARY KEY (`m_id`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_bin; 

借阅关系表

CREATE TABLE `book_management`.`reder_book` (  
  `b_id` INT(11) UNSIGNED NOT NULL COMMENT '书籍号',
  `r_id` INT(11) NOT NULL COMMENT '读者编号',
  `b_date` DATETIME NOT NULL DEFAULT  CURRENT_TIMESTAMP COMMENT '借阅日期',
  `r_date` DATE  COMMENT '还书日期',
  PRIMARY KEY (`b_id`，·r_id`) 
) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_bin;

书籍管理员关系表

CREATE TABLE `book_management`.`manager_book` 
( `m_id` INT(11) UNSIGNED NOT NULL COMMENT '管理员编号', 
`b_id` INT(11) UNSIGNED NOT NULL COMMENT '书籍编号', 
`addtime` DATE COMMENT '添加属性时间', `attr` CHAR(14) COMMENT '是否在管',
PRIMARY KEY (`m_id`, `b_id`) ); 

读者管理员关系表

CREATE TABLE `book_management`.`reder_manager` 
( `m_id` INT(11) UNSIGNED NOT NULL COMMENT '管理员id', 
`r_id` INT(11) UNSIGNED NOT NULL COMMENT '读者id', 
`rORb` INT(1) NOT NULL COMMENT '借出0，归还1', 
PRIMARY KEY (`m_id`, `r_id`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_bin; 

创建一个读者

INSERT INTO `reader`
(`r_name`,`r_sex`,`department`,`r_stduentid`)
VALUES('灯火酱','男','计算机','198212222');
-- 跟新一个读者
UPDATE `reader` SET `r_name`='XXX' r_sex='女' WHERE `r_name`= 'xxxx';
-- 删除
DELETE FROM `reader` WHERE `r_name`='XX';

创建图书

INSERT INTO `book_management`.`book` (`p_date`, `Author`, `name`, `test`) VALUES ('2021-05-10', '灯火酱', 'MySQL', '很简单'); 

UPDATE `book` SET `b_name`='XXX' `test`='xxxx' WHERE `b_name`= 'xxxx';
-- 删除图书
DELETE FROM `book_management`.`book` WHERE `b_id` = '1'; 

创建管理员

INSERT INTO `book_management`.`manager` (`selery`, `m_name`, `m_sex`, `phone`) VALUES ('3000', '麻贵', '男', '111111122'); 
UPDATE `book_management`.`manager` SET `m_sex` = '女' WHERE `m_id` = '1'; 
DELETE FROM `book_management`.`manager` WHERE `m_id` = '1'; 

提高查询速度，添加索引

ALTER TABLE `book_management`.`book` ADD KEY (`name` , `Author`);  


ALTER TABLE `book_management`.`manager` ADD INDEX (`m_sex` , `m_name`); 
-- m_sex 字段设为第一个索引,减少维护索引数量，通过select * from manager where `m_sex` in (男,女) where m_name = 'xxx' 

ALTER TABLE `book_management`.`reader` ADD INDEX (`r_sex` , `r_name` , `r_stduentid`); 



ALTER TABLE `book_management`.`reder_manager` ADD INDEX (`rORb`);

根据书名(MySQL)和借书人编号(1)，管理员编号(1)借书

SET autocommit = 0;


-- 查询没有借出书的id
SET @b_id =
(SELECT b_id FROM `book` 
JOIN `manager_book` USING(`b_id`) WHERE  `book`.`name`= 'MySQL' AND `manager_book`.`attr` IS NULL LIMIT 1);


-- 插入 借阅表
INSERT INTO `reder_book`(`r_id`,`b_id`,b_date)
VALUES (
1,
@b_id,
NOW()
)ON DUPLICATE KEY UPDATE `b_date` = NOW();
-- 更新读者管理关系表
INSERT INTO `reder_manager`(`m_id`,`r_id`,`rORb`)VALUES(1,1,0)ON DUPLICATE KEY UPDATE `rORb`=0;
-- 更新书籍管理员关系表
INSERT INTO `manager_book`(`m_id`,`b_id`,`addtime`,`attr`)VALUES (
1,
@b_id,
NOW(),
'借出'
)ON DUPLICATE KEY UPDATE `addtime` = NOW();


COMMIT;

根据书号 和 reader id还书

如书号 1 读者编号2

SET autocommit = 0;
-- 更新为已归还
UPDATE `reder_manager` SET `rORb` = 1 WHERE  `m_id`=1 AND `r_id`=2;
## 更新attr字段为null表示未借出 
INSERT INTO `manager_book`(`m_id`,`b_id`,`addtime`,`attr`)VALUES (
1,
2,
NOW(),
NULL
)ON DUPLICATE KEY UPDATE `addtime` = NOW();
-- 更新reader阅读累计数
UPDATE `reader` SET `accumulative`=`accumulative`+1 WHERE `r_id` = 1;
COMMIT;

创建视图

功能查询所有已经借出书籍的读者和书籍信息

CREATE
    /*[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = { user | CURRENT_USER }]
    [SQL SECURITY { DEFINER | INVOKER }]*/
    VIEW `book_management`.`view_books_already_lent` 
    AS
(SELECT * FROM `book` JOIN `reder_book` USING(`b_id`) JOIN `reader` USING(`r_id`));

}]
[SQL SECURITY { DEFINER | INVOKER }]*/
VIEW book_management.view_books_already_lent
AS
(SELECT * FROM book JOIN reder_book USING(b_id) JOIN reader USING(r_id));