西南科技大学《数据库原理及应用》实验一（个人版）

一、建立数据库表
1、打开tables文件夹。建立以下各关系：
2、图书分类（图书分类号，类名）
3、书目（ISBN, 书名，作者，出版单位，单价，图书分类号）
4、图书（图书编号，ISBN，是否借出，备注）
5、读者 （借书证号，姓名，单位，性别，地址，联系电话，身份证编号）
6、借阅 （借阅流水号，借书证号，图书编号，借书日期，归还日期，罚款分类号，备注）
7、罚款分类（罚款分类号，罚款名称，罚金）
8、预约 （预约流水号，借书证号，ISBN，预约时间）

二、各关系输入数据如下：
图书分类（图书分类号，类名）

图书分类号	类名
100	文学
200	科技
300	哲学

（此处省略五百字……）

三、sql代码如下

-------------------SQL建表-----------------------
CREATE TABLE BOOKTYPE
(
	type_id varchar(16) primary key,
	type_name varchar(16)
);

CREATE TABLE BOOKLIST
(
	ISBN varchar(16) primary key,
	book_name varchar(16),
	anthor varchar(16),
	publisher varchar(16),
  price number(5,2),
  type_id varchar(16),
  foreign key(type_id) references BOOKTYPE(type_id)
);

CREATE TABLE BOOKS
(
  book_no number(10) primary key,
  ISBN varchar(16),
  isloan varchar(2),
  remark varchar(20),
  foreign key(ISBN) references BOOKLIST(ISBN)
);

CREATE  TABLE READER
(
  r_id varchar(10) primary key,
  r_name varchar(10),
  r_unit varchar(25),
  r_sex varchar(2),
  r_address varchar(16),
  r_phonenum varchar(16),
  r_identitynum varchar(16)
);

CREATE TABLE FINETYPE
(
  fine_id number primary key,
  fine_name varchar(10),
  fine_money number(3)
);

CREATE TABLE BORROW
(
  b_no number primary key,
  r_id varchar(16),
  foreign key(r_id) references READER(r_id),
  book_no number(10),
  foreign key(book_no) references BOOKS(book_no),
  loan_data date,
  return_data date,
  fine_id number,
  foreign key(fine_id) references FINETYPE(fine_id),
  remark varchar(20)
);

CREATE TABLE SUBSCRIBE
(
  s_no number primary key,
  r_id varchar(16),
  foreign key(r_id) references READER(r_id),
  ISBN varchar(16),
  foreign key(ISBN) references BOOKLIST(ISBN),
  s_date date
);

-----------------insert------------------

insert into BOOKTYPE values('100','文学');
insert into BOOKTYPE values('200','科技');
insert into BOOKTYPE values('300','哲学');

select * from BOOKTYPE;

insert into booklist values('7040195836','数据库系统概论','王珊','高等教育出版社',39.00,'200');
insert into booklist values('9787508040110','红楼梦','曹雪芹','人民出版社',20.00,'100');
insert into booklist values('9787506336239','红楼梦','曹雪芹','作家出版社',34.30,'100');
insert into booklist values('9787010073750','心学之路','张立文','人民出版社',33.80,'300');
insert into booklist values('9787500601593','红岩','罗广斌','中国青年出版社',26.00,'100');

select * from booklist;

insert into books values(2001231,'7040195836','否',null);
insert into books values(2001232,'7040195836','是',null);
insert into books values(1005050,'9787506336239','否',null);
insert into books values(1005063,'9787508040110','是',null);
insert into books values(3007071,'9787010073750','是',null);

select * from books;

insert into reader values('20051001','王菲','四川绵阳西科大计算机学院','女',null,null,null);
insert into reader values('20062001','张江','四川绵阳中心医院','男',null,null,null);
insert into reader values('20061234','郭敬明','四川江油305','男',null,null,null);
insert into reader values('20071235','李晓明','四川成都工商银行','男',null,null,null);
insert into reader values('20081237','赵鑫','四川广元广元中学','女',null,null,null);

select * from reader;

insert into finetype values(1,'延期',10);
insert into finetype values(2,'损坏',20);
insert into finetype values(3,'丢失',50);

select * from finetype;


insert into BORROW values(1,'20081237',3007071,to_date('2010-09-19','yyyy/mm/dd'),to_date('2010-09-20','yyyy/mm/dd'),null,null);
insert into BORROW values(2,'20071235',1005063,to_date('2010-10-20','yyyy/mm/dd'),to_date('2010-09-20','yyyy/mm/dd'),1,null);
insert into BORROW values(3,'20071235',2001232,to_date('2011-09-01','yyyy/mm/dd'),null,null,null);
insert into BORROW values(4,'20061234',1005063,to_date('2011-09-20','yyyy/mm/dd'),null,null,null);
insert into BORROW values(5,'20051001',3007071,to_date('2011-09-10','yyyy/mm/dd'),null,null,null);
insert into BORROW values(6,'20071235',1005050,to_date('2011-10-20','yyyy/mm/dd'),to_date('2012-02-20','yyyy/mm/dd'),1,null);


select * from BORROW;

insert into subscribe values(1,'20081237','9787508040110',to_date('2011-09-11','yyyy/mm/dd'));

select * from subscribe;


---------------------添加完整性约束-----------------------

alter table reader
add constraint PHONENUM 
check(regexp_like(r_phonenum,'1[3|4|5|7|8][0-9]{9}'));


---六位数字地址码，八位数字出生日期码，三位数字顺序码和一位数字校验码---

alter table reader
add  constraint  IDENTITYNUM
check(regexp_like(r_identitynum,'[1-9]\d{5}(19|20)\d{2}((0[1-9])|(1[0-2]))(([0-2][1-9])|([1-3]0)|31)\d{3}[0-9Xx]'));


alter table books
add constraint LOAN
check (isloan in('是','否'));

alter table borrow
add  constraint  LOANDATA 
check(loan_data is not null);

-----------------------修改-------------------------------
alter table booktype
add test_no int;

alter table booktype
drop column test_no;

------插入------
select * from books;
insert into books(book_no,ISBN,isloan,remark) values (2001239,'7040195836','是','');
select * from books;
------删除------
select * from books;
delete from books where book_no='2001231';
select * from books;
------更新------
select * from booklist;
update booklist
set price=35.00
where ISBN='9787508040110';
select * from booklist;