图书管理系统 MYSQL 课程设计

create database tsg; use tsg; create table ts_Book( bnumber varchar(15) not null primary key comment'图书编号', name varchar(15) not null comment'书名', author varchar(20) not null comment'作者', price numeric(5,2) not null comment'价格', press varchar(45) not null comment'出版社', quabtity int(30) not null comment'库存量', ISBN varchar(50) not null comment'ISBN' ); create table ts_Reader( rnumber varchar(15) not null primary key comment'借书证号', name varchar (12) not null comment'读者姓名', sex enum('男','女') not null comment'读者性别', major varchar (12) not null unique comment'专业', count int(30) not null comment'借书量' ); 归还 create table ts_hlend( bnumber varchar(15) not null primary key comment'编号', rnumber varchar(15) not null comment'借书证号', ISBN varchar(50) not null comment'ISBN', bookid char(10) not null comment'图书ID', borrowtime datetime not null comment'借书时间', returntime datetime not null comment'归还时间' ); 借书 create table ts_tlend( rnumber varchar(15) not null comment'借书证号', ISBN varchar(50) not null comment'ISBN', bookid char(10) not null primary key comment'图书ID', borrowtime datetime not null comment'借书时间', returntime datetime not null comment'归还时间' ); 借还情况 create table ts_tblend( ISBN varchar(50) not null comment'ISBN', bookid char(10) not null primary key comment'图书ID', borrow enum('是','否') not null comment'是否借出' ); create table ts_worker( wname char(20) not null primary key comment'姓名', password varchar(20) not null comment'密码' ); load data local infile 'C:\\Users\\zhaog\\Desktop\\ts_book.txt' into table ts_book; select * from ts_book; insert into ts_tlend(rnumber,ISBN,bookid,borrowtime,returntime) values('1006','赵敏','女','物联网','67'); alter table ts_hlend add foreign key (rnumber) references ts_reader(rnumber); alter table ts_hlend add foreign key (bnumber) references ts_book(bnumber); alter table ts_tlend add foreign key (rnumber) references ts_reader(rnumber); alter table ts_tlend add foreign key (rnumber) references ts_reader(rnumber);