图片说明

图片说明
图片说明 

create table bookBarCode(
barCode char(8) primary key,
ISBN char(13) foreign key reference book(ISBN),
bookstate char(4) check(bookstate='在馆' or bookstate='借书' or bookstate='入藏' or bookstate='丢失')
)

select sex,count(*)
from reader
where name like '[刘%]'
group by sex

select book.ISBN,bookname,author,booktype,publishingHouse,publishingDate,barCode,bookstate
from book,bookBarCode
where book.ISBN=bookBarCode.ISBN and book.ISBN='9787550238770'


select * from reader
where readerNo not in(
select readerNo from borrow
)

图片说明 

insert into borrow(readerNo,barCode,borrowDate,dueDate,returnDate,overdueFine)
value(67004,97800006,2019-01-11,2019-02-11,NULL,0) 

update bookBarCode
set bookstate='借出'
where barCode=97800006

create view borrowView as 
select bookName,book.ISBN,bookBarCode.barCode,readerNo,borrowDate,dueDate,returnDate,overdueFine
from book,bookBarCode,borrow
where bookName='自在独行' and book.ISBN=bookBarCode.ISBN and bookBarCode.barCode =borrow.barCode

图片说明