Reader和Book是多对多的关系,需要重新定义一张表Borrow来维持Reader和Book之间的联系。
Book和Publisher是多对一的关系,多的一方负责联系的位置,所以由Book关系来维持Book和Publisher之间的联系。
此外,还需要定义外键约束,主键约束。
创建表,插入数据
--创建表
use school;
create table Reader(
RNo varchar(20) primary key not null,
RName varchar(20) not null,
Sex varchar(10)
);
create table Publisher(
PNo varchar(20) primary key not null,
PName varchar(20) not null
);
create table Book(
BNo varchar(20) primary key not null,
BName varchar(20) not null,
PublisherNo varchar(20) foreign key references Publisher(PNo),
PublishDate datetime
);
create table Borrow(
RNo varchar(20) not null foreign key references Reader(RNo),
BNo varchar(20) not null foreign key references Book(BNo),
borrow_date datetime,
return_date datetime,
primary key(RNo,BNo)
);
--插入读者
insert into Reader values('001','张三','男');
insert into Reader values('002','李四','男');
insert into Reader values('003','王二','女');
--插入出版社
insert into Publisher values('201','清华大学');
insert into Publisher values('202','浙江大学');
--插入图书
insert into Book values('101','数据库原理','201','2014-01-05');
insert into Book values('102','操作系统','202','2013-02-15');
insert into Book values('103','软件工程','201','2015-05-27');
--插入借书信息
insert into Borrow values('001','101','2015-02-04','2015-03-04');
insert into Borrow values('002','101','2016-03-04','2016-04-04');
insert into Borrow values('003','102','2017-06-16','2017-06-28');
查询数据
use school;
/*select * from Reader;
select * from Book;
select * from Publisher;
select * from Borrow;*/
select RName from Reader R,Book B,Borrow Bo
where Bo.BNo=B.BNo and Bo.RNo=R.RNo
and B.BNo in(
select B.BNo from Book B,Publisher P
where B.PublisherNo=P.PNo and B.BName='数据库原理'
)