华东交通大学数据库实验一Book_Reader_DB

create database Book_Reader_DB;

use Book_Reader_DB;
CREATE TABLE Book(
bno varchar(20) primary key,
b_type nvarchar(50) NOT NULL,
publisher nvarchar(30) NOT NULL,
author nvarchar(10) NOT NULL,
bname nvarchar(20) NOT NULL,
price float NOT NULL,
b_num int not null,
buy_date date null,
remark nvarchar(50) NULL,
)
-- 添加列 alter TABLE Book add shortName nvarchar(10) null;

CREATE TABLE Reader(
rno varchar(20) primary key,
name nvarchar(20) NOT NULL,
department nvarchar(50) NOT NULL,
sex nvarchar(1) not NULL check(sex in('男','女')) default '男',
telephone varchar(11) NULL,
)

CREATE TABLE Borrow(
bno varchar(20) foreign key references Book(bno),
rno varchar(20) foreign key references Reader(rno),
borrow_book_date date not null,
back_book_date date NOT NULL,
constraint pk_bno_rno_id primary key (bno,rno),
)


/*
use Book_Reader_DB;

select * from Book;
select * from book where Bclass='计算机';
select count(*) from book group by Bclass;
select * from Reader;
select * from Borrow;
select rno, count(bno) from Borrow group by rno order by rno;
select bno, count(rno) from Borrow group by bno order by bno;
*/