图书管理系统的相关表的创建维护

-- 图书管理系统数据库

use lms;

-- 创建用户表
create table user(
    userID int primary key auto_increment comment '用户id',
    name varchar(10) unique not null comment '用户姓名',
    password varchar(10) not null comment '密码'
);

-- 创建管理员表
create table manager like user;
alter table manager change userID managerID int comment '管理员ID';
alter table manager modify managerID int auto_increment;

-- 创建图书信息表
create table book(
  bookID char(10) unique not null comment '图书编号',
  bookName varchar(20) not null comment '书名',
  author varchar(20) not null comment '作者姓名',
  press varchar(20) not null comment '出版社',
  inventory int not null comment '库存'
);

-- 创建借阅历史表
create table history(
  bookID char(10) unique not null comment '图书编号',
  bookName varchar(20) not null comment '书名',
  num int check ( num>0 ) comment '借阅数量',
  date date comment '借阅时间'
);



drop table history;


desc manager;
desc user;
desc book;


select * from manager;
select * from user;
select * from history;

select * from book;


-- 插入用户信息
insert into user (name, password)
values ('任正非','12345678');
insert into user (name, password)
values ('马云','12345678');
insert into user (name, password)
values ('马化腾','12345678');

insert into user (name, password)
values ('刘强东','12345678');


-- 插入管理员信息
insert into manager (name, password) values ('熊壮','985211');
insert into manager (name, password) values ('戴慧','985211');
insert into manager (name, password) values ('熊壮','985211');

select * from manager;

-- 插入图书信息
insert into book value ('','','','',0);
insert into book value ('20180418hn','新概念英语','俞敏洪','新东方教育出版社',50);
insert into book value ('20220410bj','奇妙算法之旅','洪崇英','北京邮电出版社',30);
insert into book value ('20211020zj','更高更妙的数学解题思想方法','蔡培','浙江大学出版社',10);



update book set bookName = '', author = '', press = '', inventory = 0 where bookID = '';
update book set inventory = 0 where bookid = '' or bookName = '';
update book set bookID = '20170928bj' where author = '曲一线';


desc book;
select * from book;
select * from book where bookID = '1233333333' or bookName = '';
select count(*) from book;


-- 删除图书
delete from book where bookID = '' or bookName = '';

-- 查询图书库存
select inventory from book where bookID = '' or bookName = '';

-- 向借阅历史表插入信息
insert into history values('','',0,'');
insert into history value ('{BookID}','{BookName}',0,'{DateTime.Now.ToShortDateString()}';

--
update book set inventory = (inventory+1) where bookName = '新概念英语';

--
update history set num = (num+{num}) where bookID = '{BookID}' or bookName = '{BookName}'



select * from book;
select * from history;

use lms;


-- 还书方法
-- 更新书库信息
update book set inventory = (inventory + Num) where bookID = '';
-- 更新借阅历史
update history set num = (num - Num) where bookID = '';
-- 清空借阅信息
delete from history where bookID = '';