西南科技大学数据库实验一（Oracle 11g）

/*
*实验一 数据定义与数据操纵
*时间:2019-11-25
*联系:2838215550@qq.com
*附言:把代码写成一首诗。
*/
create table 图书分类
(
       图书分类号 integer primary key,
       类名 varchar2(20)
);
create table 书目
(
       ISBN varchar(20),
       书名 varchar2(20),
       作者 varchar2(20),
       单位 varchar2(20),
       单价 number(6,2),
       图书分类号 integer,
       constraint PK_书目 primary key(ISBN),
       constraint FK_图书分类号 foreign key(图书分类号) references 图书分类(图书分类号) on delete cascade
);
create table 图书 
(
       图书编号 varchar2(20),
       ISBN varchar(20),
       是否借出 varchar2(2),
       备注 varchar(255),
       constraint PK_图书 primary key(图书编号),
       constraint 图书_书目 foreign key (ISBN) references 书目(ISBN),
       constraint check_是否借出 check(是否借出 in ('是','否'))
);
create table 读者
(
       借书证号 varchar2(20) primary key,
       姓名 varchar2(20),
       出版单位 varchar2(50),
       性别 varchar2(2),
       地址 varchar2(50),
       联系电话 number(11),
       身份证编号 varchar2(18),
       constraint check_性别 check(性别 in ('男','女'))
);
create table 罚款分类
(
       罚款分类号 integer primary key,
       罚款名称 varchar2(20),
       罚金 number(5,2)
);
create table 借阅
(
       借阅流水号 varchar2(20) primary key ,
       借书证号 varchar2(20),
       图书编号 varchar2(20),
       借书日期 date not null,
       归还日期 date,
       罚款分类号 integer,
       备注 varchar2(20),
       constraint FK_借书证号_读者 foreign key(借书证号) references 读者(借书证号) on delete cascade,
       constraint FK_图书编号_图书 foreign key(图书编号) references 图书(图书编号) on delete cascade,
       constraint FK_罚款分类号_罚款分类 foreign key(罚款分类号) references 罚款分类(罚款分类号) on delete cascade
);

create table 预约
(
       预约流水号 varchar2(20),
       借书证号 varchar2(20),
       ISBN varchar2(20),
       预约时间 varchar2(20),
       constraint PK_预约 primary key(预约流水号)
);

insert into 图书分类 (图书分类号,类名) values (100,'文学');
insert into 图书分类 values (200,'科技');
insert into 图书分类 (图书分类号,类名) values (300,'哲学');

insert into 书目 values ('7040195836','数据库系统概论','王珊','高等教育出版社',39.00,'200');
insert into 书目 values ('9787508040110','红楼梦','曹雪芹','人民出版社','20.00','100');
insert into 书目 values ('9787506336239','红楼梦','曹雪芹','作家出版社',34.30,'100');
insert into 书目 values ('9787010073750','心学之路','王珊','人民出版社',33.80,'300');

insert into 图书 (图书编号,ISBN,是否借出,备注) values ('2001231','7040195836','否','');
insert into 图书 (图书编号,ISBN,是否借出,备注) values ('2001232','7040195836','是','');
insert into 图书 (图书编号,ISBN,是否借出,备注) values ('1005050','9787506336239','否','');
insert into 图书 (图书编号,ISBN,是否借出,备注) values ('1005063','9787508040110','是','');
insert into 图书 (图书编号,ISBN,是否借出,备注) values ('3007071','9787010073750','是','');

insert into 读者 (借书证号,姓名,出版单位,性别,地址,联系电话,身份证编号) values ('20051001','王菲','四川绵阳西科大计算机学院','女','绵阳','17777353550','4331011998********');
insert into 读者 (借书证号,姓名,出版单位,性别,地址,联系电话,身份证编号) values ('20062001','张江','四川绵阳中心医院','男','绵阳','17777353550','4331011998********');
insert into 读者 (借书证号,姓名,出版单位,性别,地址,联系电话,身份证编号) values ('20061234','郭敬明','四川江油305','男','四川','17777353550','4331011998********');
insert into 读者 (借书证号,姓名,出版单位,性别,地址,联系电话,身份证编号) values ('20071235','李晓明','四川成都工商银行','男','成都','17777353550','4331011998********');
insert into 读者 (借书证号,姓名,出版单位,性别,地址,联系电话,身份证编号) values ('20081237','赵鑫','四川广元广元中学','女','广元','17777353550','4331011998********');

insert into 罚款分类 (罚款分类号,罚款名称,罚金) values (1,'延期',10);
insert into 罚款分类 (罚款分类号,罚款名称,罚金) values (2,'损坏',20);
insert into 罚款分类 (罚款分类号,罚款名称,罚金) values (3,'丢失',50);

insert into 借阅 (借阅流水号,借书证号,图书编号,借书日期,归还日期,罚款分类号,备注) values ('1','20081237','3007071',to_date('2010/09/19','yyyy/mm/dd'),to_date('2010/09/20','yyyy/mm/dd'),'','');
insert into 借阅 (借阅流水号,借书证号,图书编号,借书日期,归还日期,罚款分类号,备注) values ('2','20071235','1005063',to_date('2010/10/20','yyyy/mm/dd'),to_date('2011/02/20','yyyy/mm/dd'),'1','');
insert into 借阅 (借阅流水号,借书证号,图书编号,借书日期,归还日期,罚款分类号,备注) values ('3','20071235','2001232',to_date('2011/09/01','yyyy/mm/dd'),'','','');
insert into 借阅 (借阅流水号,借书证号,图书编号,借书日期,归还日期,罚款分类号,备注) values ('4','20061234','1005063',to_date('2011/9/20','yyyy/mm/dd'),'','','');
insert into 借阅 (借阅流水号,借书证号,图书编号,借书日期,归还日期,罚款分类号,备注) values ('5','20051001','3007071',to_date('2011/9/10','yyyy/mm/dd'),'','','');
insert into 借阅 (借阅流水号,借书证号,图书编号,借书日期,归还日期,罚款分类号,备注) values ('6','20071235','1005050',to_date('2011/10/20','yyyy/mm/dd'),to_date('2012/02/20','yyyy/mm/dd'),'1','');

insert into 预约 (预约流水号,借书证号,ISBN,预约时间) values ('1','20081237','9787508040110','2011/09/11');

create table 自己
(
       ID integer,
       姓名 varchar2(20),
       性格 varchar2(20),
       年龄 integer
);

alter table 自己 add constraint PK_自己 primary key (ID);
alter table 自己 rename column 姓名 to name;
alter table 自己 modify (name varchar2(10) not null);
alter table 自己 add ceshi integer default 1 not null;
alter table 自己 drop column ceshi;
drop table 自己;


insert into 借阅 (借阅流水号,借书证号,图书编号,借书日期,归还日期,罚款分类号,备注) values ('7','20071235','1005050',to_date('2012/10/20','yyyy/mm/dd'),to_date('2013/02/20','yyyy/mm/dd'),'1','延期归还');

update 借阅 set 罚款分类号='',备注='延期归还' where 借阅流水号='7';
update 借阅 set 罚款分类号='',备注='延期归还' where 借阅流水号 in (select count(*) from 借阅);
update 借阅 set 罚款分类号='',备注='延期归还' where 借阅流水号=(select count(*) from 借阅);

select * from 借阅 where 借阅流水号='7';
select * from 借阅 where 借阅流水号 in (select count(*) from 借阅);
select * from 借阅 where 借阅流水号=(select count(*) from 借阅);


--alter table 读者 add constraint check_联系电话 check(regexp_like(联系电话,'[0-9](11)'));
alter table 读者 add constraint check_联系电话 check(regexp_like(联系电话,'^\d{11}'));
update 读者 set 身份证编号='43310119980705355X';
alter table 读者 add constraint check_身份证编码 check(regexp_like(身份证编号,'^[0-9]{17}[0-9xX]{1}'));
alter table 借阅 add constraint check_借书日期 check(借书日期 is not null);