sql语句

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='数据库原理'
)