mysql 图书管理数据库 脚本
DROP DATABASE IF EXISTS 孙武浩_图书管理数据库;
CREATE DATABASE 孙武浩_图书管理数据库 DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
use 孙武浩_图书管理数据库;
DROP TABLE IF EXISTS 孙武浩_管理员;
CREATE TABLE 孙武浩_管理员 (
职工编号 int not NULL,
姓名 varchar(20),
性别 varchar(20),
民族 varchar(20),
籍贯 varchar(20),
出生日期 DATE,
工龄 varchar(20),
文化程度 varchar(20),
管理员照片 varchar(200),
PRIMARY KEY (职工编号)
);
insert into `孙武浩_管理员` values(1001,'a','男','汉','湖北','2021-10-22','18','本科','.....');
insert into `孙武浩_管理员` values(1002,'b','男','汉','湖北','2000-10-22','18','专科','.....');
insert into `孙武浩_管理员` values(1003,'c','男','汉','湖北','1999-10-22','9', '硕士','.....');
insert into `孙武浩_管理员` values(1004,'d','女','汉','湖北','1980-10-22','5', '博士','.....');
insert into `孙武浩_管理员` values(1005,'e','女','汉','湖北','1970-10-22','12','本科','.....');
insert into `孙武浩_管理员` values(1006,'f','男','汉','湖北','2015-10-22','33','本科','.....');
insert into `孙武浩_管理员` values(1007,'g','女','汉','湖北','1999-10-22','9', '专科','.....');
insert into `孙武浩_管理员` values(1008,'h','女','汉','湖北','1980-10-22','5', '硕士','.....');
insert into `孙武浩_管理员` values(1009,'i','男','汉','湖北','1970-10-22','12','博士','.....');
insert into `孙武浩_管理员` values(1010,'j','男','汉','湖北','2015-10-22','33','本科','.....');
SELECT * FROM 孙武浩_管理员;
DROP TABLE IF EXISTS 孙武浩_借书证;
CREATE TABLE 孙武浩_借书证 (
借书证号 int not NULL,
姓名 varchar(20),
借书证类型 varchar(20),
单位名称 varchar(20),
职务 varchar(20),
性别 varchar(20),
出生日期 DATE,
联系电话 varchar(20),
办证时间 DATE,
有效时间 int,
PRIMARY KEY (借书证号)
);
insert into `孙武浩_借书证` values(11001,'张三1','学生','湖北警官','学生','男','2021-10-22','110','2021-10-22',20);
insert into `孙武浩_借书证` values(11002,'张三2','教师','湖北税务','教师','男','1980-10-22','112','1980-10-22',50);
insert into `孙武浩_借书证` values(11003,'张三3','其他','湖北水利','其他','女','1958-11-20','113','1958-11-20',180);
insert into `孙武浩_借书证` values(11004,'张三4','学生','湖北消防','学生','男','2001-10-22','119','2001-10-22',520);
insert into `孙武浩_借书证` values(11005,'张三5','教师','湖北公安','教师','女','2000-10-22','110','2000-10-22',20);
insert into `孙武浩_借书证` values(11006,'李四1','其他','湖北警官','其他','男','2021-10-22','110','2021-10-22',20);
insert into `孙武浩_借书证` values(11007,'李四2','学生','湖北税务','学生','男','1980-10-22','112','1980-10-22',50);
insert into `孙武浩_借书证` values(11008,'李四3','教师','湖北水利','教师','女','1958-11-20','113','1958-11-20',180);
insert into `孙武浩_借书证` values(11009,'李四4','其他','湖北消防','其他','男','2001-10-22','119','2001-10-22',520);
insert into `孙武浩_借书证` values(11010,'李四5','学生','湖北公安','学生','女','2000-10-22','110','2000-10-22',123);
SELECT * FROM 孙武浩_借书证;
DROP TABLE IF EXISTS 孙武浩_图书类别;
CREATE TABLE 孙武浩_图书类别 (
图书类别编号 int not NULL,
图书类别名 varchar(20),
PRIMARY KEY (图书类别编号)
);
insert into `孙武浩_图书类别` values(101,'科普');
insert into `孙武浩_图书类别` values(102,'计算机技术');
insert into `孙武浩_图书类别` values(103,'文学');
insert into `孙武浩_图书类别` values(104,'漫画');
insert into `孙武浩_图书类别` values(105,'哲学');
insert into `孙武浩_图书类别` values(106,'经济学');
insert into `孙武浩_图书类别` values(107,'农学');
insert into `孙武浩_图书类别` values(108,'医学');
insert into `孙武浩_图书类别` values(109,'英语');
insert into `孙武浩_图书类别` values(110,'交通运输');
SELECT * FROM 孙武浩_图书类别;
DROP TABLE IF EXISTS 孙武浩_图书存储;
CREATE TABLE 孙武浩_图书存储 (
图书编号 int not NULL,
图书名称 varchar(20),
图书类别 int,
作者 varchar(20),
出版社 varchar(20),
出版日期 date,
价格 double,
图书介绍 varchar(200),
入库时间 DATE,
库存数量 int,
PRIMARY KEY (图书编号),
FOREIGN KEY (图书类别) REFERENCES 孙武浩_图书类别 (图书类别编号)
);
insert into `孙武浩_图书存储` values(201,'DK博物百科',101,'b','b','2021-10-22','55','.......','2021-10-22',2265);
insert into `孙武浩_图书存储` values(202,'数据库系统',102,'b','b','1980-10-22','222','.......','1980-10-22',55);
insert into `孙武浩_图书存储` values(203,'百年孤独',103,'b','b','1958-11-20','18','.......','1958-11-20',18);
insert into `孙武浩_图书存储` values(204,'龙珠',104,'b','b','2001-10-22','168','.......','2001-10-22',51);
insert into `孙武浩_图书存储` values(205,'黑格尔',105,'b','b','2000-10-22','55','.......','2000-10-22',64);
insert into `孙武浩_图书存储` values(206,'国富论',106,'b','b','2021-10-22','16','.......','2021-10-22',15);
insert into `孙武浩_图书存储` values(207,'种子',107,'b','b','1980-10-22','22','.......','1980-10-22',1515);
insert into `孙武浩_图书存储` values(208,'手术刀',108,'b','b','1958-11-20','18','.......','1958-11-20',1);
insert into `孙武浩_图书存储` values(209,'English',109,'b','b','2001-10-22','168','.......','2001-10-22',5);
insert into `孙武浩_图书存储` values(210,'运输技术',110,'b','b','2000-10-22','55','.......','2000-10-22',265);
SELECT * FROM 孙武浩_图书存储;
DROP TABLE IF EXISTS 孙武浩_借书;
CREATE TABLE 孙武浩_借书 (
管理员编号 int not NULL,
借书证号 int not NULL,
图书编号 int not NULL,
借书数量 int,
借阅时间 date,
PRIMARY KEY (管理员编号,借书证号,图书编号),
#PRIMARY KEY (借书证号),
#PRIMARY KEY (图书编号),
FOREIGN KEY (管理员编号) REFERENCES 孙武浩_管理员 (职工编号) on delete CASCADE,
FOREIGN KEY (借书证号) REFERENCES 孙武浩_借书证 (借书证号) on delete CASCADE,
FOREIGN KEY (图书编号) REFERENCES 孙武浩_图书存储 (图书编号) on delete CASCADE
);
insert into 孙武浩_借书 values(1001,11001,201,1,'2000-10-20');
insert into 孙武浩_借书 values(1002,11002,202,1,'2000-10-20');
insert into 孙武浩_借书 values(1003,11003,203,1,'2018-10-20');
insert into 孙武浩_借书 values(1004,11004,204,1,'2018-10-20');
insert into 孙武浩_借书 values(1005,11005,205,1,'2020-10-20');
insert into 孙武浩_借书 values(1006,11006,206,1,'2014-10-20');
insert into 孙武浩_借书 values(1007,11007,207,1,'2011-10-20');
insert into 孙武浩_借书 values(1008,11008,208,1,'2015-10-20');
insert into 孙武浩_借书 values(1009,11009,209,1,'2010-10-20');
insert into 孙武浩_借书 values(1010,11010,210,2,'2000-10-20');
SELECT * FROM 孙武浩_借书;
DROP TABLE IF EXISTS 孙武浩_还书;
CREATE TABLE 孙武浩_还书 (
管理员编号 int not NULL,
借书证号 int not NULL,
图书编号 int not NULL,
还书数量 int,
还书时间 date,
PRIMARY KEY (管理员编号,借书证号,图书编号),
#PRIMARY KEY (借书证号),
#PRIMARY KEY (图书编号),
FOREIGN KEY (管理员编号) REFERENCES 孙武浩_管理员 (职工编号)on delete CASCADE,
FOREIGN KEY (借书证号) REFERENCES 孙武浩_借书证 (借书证号) on delete CASCADE,
FOREIGN KEY (图书编号) REFERENCES 孙武浩_图书存储 (图书编号)on delete CASCADE
);
insert into 孙武浩_还书 values(1001,11001,201,1,'2020-10-20');
insert into 孙武浩_还书 values(1002,11002,202,1,'2020-10-20');
insert into 孙武浩_还书 values(1003,11003,203,1,'2020-10-20');
insert into 孙武浩_还书 values(1004,11004,204,1,'2020-10-20');
insert into 孙武浩_还书 values(1005,11005,205,1,'2020-10-20');
insert into 孙武浩_还书 values(1006,11006,206,1,'2020-10-20');
insert into 孙武浩_还书 values(1007,11007,207,1,'2020-10-20');
insert into 孙武浩_还书 values(1008,11008,208,1,'2020-10-20');
insert into 孙武浩_还书 values(1009,11009,209,1,'2020-10-20');
insert into 孙武浩_还书 values(1010,11010,210,2,'2020-10-20');
SELECT * FROM 孙武浩_还书;
use `孙武浩_图书管理数据库`;
#1、创建一个用于检索读者借阅图书情况的视图，要求显示借书证号、姓名、证件类型、借阅时间、还书时间和图书名称。
DROP view IF EXISTS 图书情况;
create view 图书情况
as
(
select `孙武浩_借书证`.`借书证号`,
`孙武浩_借书证`.`姓名`,
`孙武浩_借书证`.`借书证类型`,
`孙武浩_借书`.`借阅时间`,
`孙武浩_还书`.`还书时间`,
`孙武浩_图书存储`.`图书名称`
FROM `孙武浩_借书证`,`孙武浩_借书`,`孙武浩_还书`,`孙武浩_图书存储`
where `孙武浩_借书证`.`借书证号`=`孙武浩_借书`.`借书证号`
AND `孙武浩_还书`.`借书证号`=`孙武浩_借书证`.`借书证号`
AND `孙武浩_还书`.`图书编号`=`孙武浩_图书存储`.`图书编号`
);
select * from 图书情况;
#2、求各个图书类别号及相应的借书人数。
select `孙武浩_借书`.`借书数量` 借书人数,`孙武浩_图书类别`.`图书类别编号`
from `孙武浩_图书类别`,`孙武浩_图书存储`,`孙武浩_借书`
where `孙武浩_图书类别`.`图书类别编号`=`孙武浩_图书存储`.`图书类别`
and `孙武浩_借书`.`图书编号`=`孙武浩_图书存储`.`图书编号`
GROUP BY `孙武浩_借书`.`借书证号`
;
#3、检索库存数量小于10的图书信息，要求查询结果显示图书编号、图书名称、类别名、作者、出版社、出版日期、价格和数量等8字段，并按“类别名”升序排列。 （9’）
select 图书编号,图书名称,`孙武浩_图书类别`.`图书类别名`,作者,出版社,出版日期,价格,库存数量
from `孙武浩_图书存储`,`孙武浩_图书类别`
where `孙武浩_图书存储`.`图书类别`=`孙武浩_图书类别`.`图书类别编号`
and 库存数量<10
ORDER BY 图书类别名 ASC
;
#4、查有借书并且未归还的人员姓名和单位名称。
select `孙武浩_借书证`.`姓名` ,`孙武浩_借书证`.`单位名称`
from `孙武浩_借书`,`孙武浩_借书证`
WHERE `孙武浩_借书证`.`借书证号`=`孙武浩_借书`.`借书证号`
and
#查询有借书没有还书的记录 和 借了多本书没有还完
(`孙武浩_借书`.`图书编号` not in (select `孙武浩_还书`.`图书编号` from `孙武浩_还书` )
or `孙武浩_借书`.`图书编号` IN
(select `孙武浩_还书`.`图书编号` from `孙武浩_还书`
where`孙武浩_还书`.`还书数量`<`孙武浩_借书`.`借书数量`)
)
;
#5、用嵌套查询：查询借阅了“数据库系统”的学生姓名。
select 姓名 from `孙武浩_借书证`,`孙武浩_借书` where `孙武浩_借书`.`借书证号`=`孙武浩_借书证`.`借书证号`
and 图书编号 in
(select 图书编号 from `孙武浩_图书存储` where `孙武浩_图书存储`.`图书名称`='数据库系统')
;
#6、用于检索姓为“张”的读者基本信息，要求查询结果显示姓名、借书证号、单位名称、职务、性别和出生日期等6个字段。
select `孙武浩_借书证`.`借书证号`,姓名,单位名称,`孙武浩_借书证`.职务,性别,出生日期 from `孙武浩_借书证` where 姓名 like '张%'
;
#7、统计不同图书类别的图书数量，查询结果显示类别名、图书库存量。
select 库存数量,图书类别名 from `孙武浩_图书存储`,`孙武浩_图书类别`
where `孙武浩_图书存储`.`图书类别`=`孙武浩_图书类别`.`图书类别编号`
GROUP BY `孙武浩_图书类别`.`图书类别编号`
;
#8、查询计算机技术类图书定价比交通运输类图书最低定价高的图书信息。
select `孙武浩_图书存储`.* from `孙武浩_图书存储`,`孙武浩_图书类别` where
`孙武浩_图书类别`.`图书类别编号`=`孙武浩_图书存储`.`图书类别` and
`孙武浩_图书类别`.`图书类别名`='计算机技术' and
价格>(select MIN(`孙武浩_图书存储`.`价格`) from `孙武浩_图书存储`,`孙武浩_图书类别` where
`孙武浩_图书类别`.`图书类别编号`=`孙武浩_图书存储`.`图书类别` and
`孙武浩_图书类别`.`图书类别名`='交通运输')
;
#9、删除借书证为201301001的借书证信息。
#DELETE from`孙武浩_借书证` where `孙武浩_借书证`.`借书证号`=11010
DELETE from`孙武浩_借书` where `孙武浩_借书`.`借书证号`=201301001 ;
DELETE from `孙武浩_还书` where `孙武浩_还书`.`借书证号`=201301001 ;
DELETE from `孙武浩_借书证` where `孙武浩_借书证`.`借书证号`=201301001;
#DELETE from `孙武浩_图书存储` where `孙武浩_图书存储`.`图书编号` in (select `孙武浩_还书`.`图书编号` from `孙武浩_还书` WHERE `孙武浩_还书`.`借书证号`=201301001)
#10、将信息技术系全体学生的借书信息（指数量）清空（置零）。
update `孙武浩_借书` set `孙武浩_借书`.`借书数量`=null where `孙武浩_借书`.`借书证号` in
(select `借书证号` from `孙武浩_借书证` WHERE `单位名称`='湖北警官');
浙公网安备 33010602011771号