Library 数据视图

CREATE DATABASE Cook;
use Cook;

CREATE TABLE Book(
	id varchar(255),
	BookName varchar(255),
	cat varchar(255),
	author varchar(255),
    press varchar(255),
    price double,
    number int 
);

INSERT INTO Book VALUES ('A0120', '庄子     ', '文学', '庄周','吉林大学出版社',18.5,5);
INSERT INTO Book VALUES ('A0134', '唐诗三百首', '文学', '李平','安徽科学出版社',28,10);
INSERT INTO Book VALUES ('B1101', '西方经济史', '财经', '莫竹','海南出版社',39.8,8);
INSERT INTO Book VALUES ('B2213', '商业博弈  ', '财经', '孔英','北京大学出版社',39,15);
INSERT INTO Book VALUES ('C1269', '数据结构  ', '计算机', '李刚','高等教育出版社',29,20);
INSERT INTO Book VALUES ('C3121', '品牌策划与推广实践', '计算机', '张晓红','人民邮电出版社',42,6);
INSERT INTO Book VALUES ('C3182', 'C语言程序设计', '计算机', '李学刚','高等教育出版社',36.8,11);
INSERT INTO Book VALUES ('C3256', 'MYSQL数据库 ', '计算机', '孙季红','电子电工出版社',29,9);

CREATE TABLE Stock(
	code varchar(255),
	BooknNumber varchar(255),
	position varchar(255),
    stock varchar(255)
);
INSERT INTO Stock VALUES ('123412','A0120','1-A-56','在馆');
INSERT INTO Stock VALUES ('123413','A0120','1-A-57','借出');
INSERT INTO Stock VALUES ('223410','A0134','2-B-01','在馆');
INSERT INTO Stock VALUES ('223411','A0134','2-B-02','借出');
INSERT INTO Stock VALUES ('311231','B1101','2-C-23','在馆');
INSERT INTO Stock VALUES ('321123','C1269','3-A-12','丢失');
INSERT INTO Stock VALUES ('321124','C1269','3-A-13','借出');
INSERT INTO Stock VALUES ('411111','C3256','3-B-01','借出');
INSERT INTO Stock VALUES ('411112','C3256','3-B-01','借出');
INSERT INTO Stock VALUES ('411113','C3256','3-B-01','在馆');


CREATE TABLE Reader(
	category varchar(255),
	ClassName varchar(255),
	quantity int,
    isday int
);
INSERT INTO Reader VALUES ('1','学生',10,30);
INSERT INTO Reader VALUES ('2','教师',20,60);
INSERT INTO Reader VALUES ('3','职工',15,20);



CREATE TABLE Readers(
	ReaderNumber varchar(255),
	isName varchar(255),
	category varchar(255),
    Company varchar(255),
    Effective varchar(255)
);
INSERT INTO Readers VALUES ('0001','张小东','1','软件学院','有效');
INSERT INTO Readers VALUES ('0002','苏明','1','财经学院','有效');
INSERT INTO Readers VALUES ('1001','梁小红','2','软件学院','有效');
INSERT INTO Readers VALUES ('1002','赵明敏','2','传媒学院','有效');
INSERT INTO Readers VALUES ('2001','李丰年','3','计财处','有效');



CREATE TABLE Borrow(
	BorrowingNumber varchar(255),
	code varchar(255),
    ReaderNumber varchar(255),
    idate varchar(255),
    ydate varchar(255),
    isstate varchar(255)
);
INSERT INTO Borrow VALUES ('100001','123413','0001','2020-11-05',NULL,'借阅');
INSERT INTO Borrow VALUES ('100002','223411','0002','2020-09-28','2020-10-13','已还');
INSERT INTO Borrow VALUES ('100003','321123','1001','2020-07-01',NULL,'过期');
INSERT INTO Borrow VALUES ('100004','321124','2001','2020-10-09','2020-10-14','已还');
INSERT INTO Borrow VALUES ('100005','321124','0001','2020-10-15',NULL,'借阅');
INSERT INTO Borrow VALUES ('100006','223411','2001','2020-10-16',NULL,'借阅');
INSERT INTO Borrow VALUES ('100007','411111','1002','2020-09-01','2020-09-24','已还');
INSERT INTO Borrow VALUES ('100008','411111','0001','2020-09-25',NULL,'借阅');
INSERT INTO Borrow VALUES ('100009','411111','1001','2020-10-08',NULL,'借阅');

-- -- 2
CREATE VIEW L_view1("读者编号","姓名","类名","可借数量","可借天数")
AS
SELECT Readers.ReaderNumber,Readers.isName,Reader.ClassName,Reader.quantity,Reader.isday
FROM Readers,Reader 
where Readers.category=Reader.category;
SELECT "读者编号","姓名","可借数量","可借天数"
FROM L_view1;

CREATE VIEW L_view2("借阅号","书号","读者姓名","借阅日期","还书日期")
AS
   (SELECT Borrow.BorrowingNumber,Stock.BooknNumber,Readers.isName,Borrow.idate,Borrow.ydate
   FROM Borrow 
   JOIN Stock ON (Borrow.code = Stock.code)
   JOIN Readers ON (Borrow.ReaderNumber = Readers.ReaderNumber)
   );
SELECT *
FROM L_view2
WHERE "还书日期" = '';

CREATE VIEW L_view3("借阅号","条码","读者编号","借阅日期","还书日期","借阅状态")
AS
   SELECT *
   FROM Borrow
   WHERE Borrow.isstate = '已还' OR Borrow.isstate = '借阅'
    WITH CHECK OPTION;
SELECT *
FROM L_view3;

-- 2
INSERT INTO  L_view3
VALUES('100010','41112','2001','2020-10-18',NULL,'借阅');
SELECT *
FROM L_view3;

UPDATE L_view2 
SET "借阅日期" = '2022-04-25'
WHERE "借阅号" = '100001';
SELECT *
FROM L_view2;

DELETE FROM L_view3 WHERE "还书日期" IS NOT NULL;
SELECT *
FROM L_view3;
DROP VIEW L_view2,L_view3;