create table book ---book表
(
book_id number(4) primary key,--id是自增长的，所以需要使用序列
book_name varchar2(30) unique not null,
book_count number(5) not null,--记录概述被借阅的次数
status number(1) check(status in(1,0))--1表示可借，0表示借出
);
create sequence seq_book_id;--序列
insert into book(book_id,book_name,book_count,status) values(seq_book_id.nextval,'道德经',0,1);
insert into book(book_id,book_name,book_count,status) values(seq_book_id.nextval,'金刚经',0,1);
insert into book(book_id,book_name,book_count,status) values(seq_book_id.nextval,'Java入门',0,1);
insert into book(book_id,book_name,book_count,status) values(seq_book_id.nextval,'Web入门',0,1);
insert into book(book_id,book_name,book_count,status) values(seq_book_id.nextval,'三书',0,1);
insert into book(book_id,book_name,book_count,status) values(seq_book_id.nextval,'项目',0,1);
select * from book
--user表
create table lib_users
(
user_id number(4) primary key,--用户编号，自增长
user_name varchar2(30) unique,--用户姓名
user_password varchar(20) not null,--用户密码
user_type number(1) check(user_type in(1,2))--1表示普通用户，2表示管理员
);
create sequence seq_users_id;--创建序列
insert into lib_users(user_id,user_name,user_password,user_type)values(seq_users_id.nextval,'zhangsan','zhangsan',1);
insert into lib_users(user_id,user_name,user_password,user_type)values(seq_users_id.nextval,'lisi','lisi',1);
insert into lib_users(user_id,user_name,user_password,user_type)values(seq_users_id.nextval,'laozi','laozi',2);
select * from lib_users
--record表
create table record
(
record_id number(4) primary key,
user_id number(4) not null,
book_id number(4) not null,
lend_time date not null,
return_time date
);
alter table record add constraints record_userid_fk foreign key(user_id) references lib_users(user_id);--外键约束
alter table record add constraints record_bookid_fk foreign key(book_id) references book(book_id);--外键约束
create sequence seq_record_id;--创建序列
insert into record(record_id,user_id,book_id,lend_time,return_time)
values(seq_record_id.nextval,1,1,to_date('2017-12-12','yyyy-MM_dd'),to_date('2018-1-12','yyyy-MM-dd'))
insert into record(record_id,user_id,book_id,lend_time,return_time)
values(seq_record_id.nextval,1,2,to_date('2017-10-11','yyyy-MM-dd'),to_date('2017-11-12','yyyy-MM-dd'));
insert into record(record_id,user_id,book_id,lend_time,return_time)
values(seq_record_id.nextval,2,3,to_date('2017-9-11','yyyy-MM-dd'),to_date('2017-10-11','yyyy-MM-dd'));
insert into record(record_id,user_id,book_id,lend_time,return_time)
values(seq_record_id.nextval,3,1,to_date('2017-9-15','yyyy-MM-dd'),to_date('2017-10-31','yyyy-MM-dd'));
insert into record(record_id,user_id,book_id,lend_time,return_time)
values(seq_record_id.nextval,1,1,to_date('2017-11-11','yyyy-MM-dd'),to_date('2018-2-11','yyyy-MM-dd'));
insert into record(record_id,user_id,book_id,lend_time,return_time)
values(seq_record_id.nextval,1,1,to_date('2017-12-11','yyyy-MM-dd'),to_date('2018-5-11','yyyy-MM-dd'));
select *from record