SQL基本语法示例-基于Oracle 11g(h5osir)

2020-06-11 10:33发布

/*
*实验一 数据定义与数据操纵
*时间: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 ('200101237','赵哥','四川广元广元中学','女','广元','17777353550','4331011998********');
insert into 读者 (借书证号,姓名,单位,性别,地址,联系电话,身份证编号) values ('200111237','赵大大','四川广元广元中学','女','广元','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);
alter table 图书 constraint check_是否借出 check(是否借出 in ('是','否'));

/*
*实验二 数据查询
*时间:2019-11-25
*联系:2838215550@qq.com
*附言:把代码写成一首诗。
*SELECT [ ALL | DISTINCT] < 目标列表达式 > [,< 目标列表达式 > ]...
*FROM <表名或视图名> [,<表名或视图名>]...
*[ WHERE < 条件表达式 > ]
*[ GROUP BY < 列名1> [ HAVING  < 条件表达式 > ]]
*[ ORDER BY < 列名2> [ASC | DESC ]]
*
*alter table 书目 rename column 单位 to 出版单位;
*/

--基础实验
1、 查询“红楼梦”目前可借的各图书编号,及所属版本信息。(是否借出为‘否‘的图书)
select 图书.图书编号,图书.ISBN from 图书,书目 where 图书.ISBN=书目.ISBN and 书目.书名='红楼梦' and 图书.是否借出='否';

2、查找高等教育出版社的所有书目及单价,结果按单价降序排序。
select 书目.书名,书目.单价 from 书目 where 书目.出版单位='高等教育出版社' order by 书目.单价 desc;

3、统计“红楼梦”各版的藏书数量(ISBN不同则版本不同)。
select 图书.ISBN,count(图书.图书编号) 数量 from 图书 join 书目 on 图书.ISBN=书目.ISBN where 书目.书名='红楼梦' group by 图书.ISBN;

4、查询学号“20061234”号借书证借阅未还的图书的信息。
select * from 书目 where 书目.ISBN in (select 图书.ISBN from 图书 join 借阅 on 图书.图书编号=借阅.图书编号 where 借阅.借书证号='20061234');
select * from 书目 where ISBN in (select ISBN from 图书 where 图书编号 in (select 图书编号 from 借阅 where 借阅.借书证号='20061234'));

5、查询各个出版社的图书最高单价、平均单价。
select 出版单位,max(单价) 最高价格,avg(单价) 平均价格 from 书目 group by 出版单位;

6、要查询借阅了两本和两本以上图书的读者的个人信息。
select * from 读者 where 借书证号 in (select 借书证号 from 借阅 group by 借书证号 having count(图书编号)>=2);

7、查询“王菲”的单位、所借图书的书名和借阅日期。
select 读者.单位,书目.书名,to_char(借阅.借书日期,'yyyy/mm/dd') 借书日期 from 书目,图书,读者,借阅 where 书目.ISBN=图书.ISBN and 图书.图书编号=借阅.图书编号 and 借阅.借书证号=读者.借书证号 and 读者.姓名='王菲';

8、查询每类图书的册数和平均单价。
select 图书分类.类名,count(*) 册数,avg(书目.单价) 平均单价 from 书目,图书分类 where 书目.图书分类号=图书分类.图书分类号 group by 图书分类.类名;

9、统计从未借书的读者人数。
select count(*) 从未借书读者人数 from 读者 where 读者.借书证号 not in (select distinct 借阅.借书证号 from 借阅);

10、统计参与借书的人数。
select count(*) 从未借书读者人数 from 读者 where 读者.借书证号 in (select distinct 借阅.借书证号 from 借阅);

11、找出所有借书未还的读者的信息及所借图书编号及名称。
select 读者.*,借阅.图书编号,书目.书名 from 读者,借阅,书目,图书 where 读者.借书证号=借阅.借书证号 and 借阅.图书编号=图书.图书编号 and 图书.ISBN=书目.ISBN and 借阅.归还日期 is null;

12、检索书名是以“Internet”开头的所有图书的书名和作者。
select 书名,作者 from 书目 where 书名='Internet%';

13、查询各图书的罚款总数。
select 借阅.图书编号,sum(罚款分类.罚金) 罚款总数 from 借阅,罚款分类 where 借阅.罚款分类号=罚款分类.罚款分类号 group by 借阅.图书编号;

14、查询借阅及罚款分类信息,如果有罚款则显示借阅信息及罚款名称、罚金,如果没有罚款则罚款名称、罚金显示空(左外连接)。
select * from 借阅 left join 罚款分类 on 借阅.罚款分类号=罚款分类.罚款分类号;

15、查询借阅了所有“文学”类书目的读者的姓名、单位。
select 读者.姓名,读者.单位 from 读者,借阅 where 读者.借书证号=借阅.借书证号 and 借阅.图书编号 in (select 图书.图书编号 from 图书,书目,图书分类 where 图书分类.类名='文学' and 图书分类.图书分类号=书目.图书分类号 and 图书.ISBN=书目.ISBN);




--扩展实验
1、在书目关系中新增“出版年份”,并在该属性下添加数据。(使用SQL完成)。
--alter table 书目  add(出版年份 date);
--alter table 书目 add 出版年份 date;
alter table 书目 add (出版年份 number(4,0));
update 书目 set 出版年份=2005 where ISBN=7040195836;
update 书目 set 出版年份=1983 where ISBN=9787508040110;
update 书目 set 出版年份=2008 where ISBN=9787506336239;
update 书目 set 出版年份=2009 where ISBN=9787010073750;

2、求总藏书量、藏书总金额、最高价、最低价。
select count(图书编号) 总藏书量,sum(单价) 藏书总金额,max(单价) 最高价,min(单价) 最低价 from 图书,书目 where 图书.ISBN=书目.ISBN;

3、列出藏书在5本以上的书目(书名、作者、出版社、出版年份)。
select 书名,作者,出版单位,出版年份 from 书目 where ISBN in (select 图书.ISBN from 图书 group by 图书.ISBN having count( 图书.isbn)>5);

4、列出年份最久远的书?
select 书名,作者,出版单位,出版年份 from 书目 where 出版年份 in (select min(书目.出版年份) from 书目);

5、 目前实际已借出多少册书?
select count(借阅流水号) 借出数量 from 借阅 where 归还日期 is null;

6.、哪一年的图书最多?
--嵌套太深,报错:分组函数的嵌套太深
--select 出版年份,count(出版年份) 数量 from 图书,书目 where 图书.ISBN=书目.ISBN group by 出版年份 having count(出版年份)>max(count(出版年份));
select * from (select 出版年份,count(出版年份) 数量 from 图书,书目 where 图书.ISBN=书目.ISBN group by 出版年份 order by count(出版年份) desc ) where rownum=1;


7、 哪本借书证未归还的图书最多?
select * from (select 借书证号 from 借阅 where 归还日期 is null group by 借书证号 order by count(借书证号) desc) where rownum=1;

8、平均每本借书证的借书册数。
select 借书证号, count(借书证号) from 借阅 group by 借书证号;

9、哪个单位的读者平均借书册数最多?
select 读者.单位 from 读者 where 读者.借书证号 in (select 借书证号 from (select 借书证号, count(借书证号) from 借阅 group by 借书证号 order by count(借书证号) desc) where rownum=1);

10、 最近两年都未被借过的书。
select 图书.图书编号,书目.书名,书目.作者 from 图书,书目,借阅 where 图书.图书编号=借阅.图书编号 and 书目.ISBN=图书.ISBN and 借阅.借书日期 not between to_date('20180101','yyyy/mm/dd') and to_date('20191231','yyyy/mm/dd');

11、今年未借过书的借书证。
select 读者.借书证号 from 读者 where 读者.借书证号 not in (select 借阅.借书证号 from 借阅 where 借阅.借书日期 between to_date('20190101','yyyy/mm/dd') and to_date('20191231','yyyy/mm/dd'));
--select 读者.借书证号 from 读者 where not exists (select 借阅.借书证号 from 借阅 where 借阅.借书日期 between to_date('20050101','yyyy/mm/dd') and to_date('20191231','yyyy/mm/dd'));


/*
补充:
select sysdate from dual;

--加法 
select sysdate,add_months(sysdate,12) from dual; --加1年 
select sysdate,add_months(sysdate,1) from dual; --加1月 
select sysdate,to_char(sysdate+7,'yyyy-mm-dd HH24:MI:SS') from dual; --加1星期 
select sysdate,to_char(sysdate+1,'yyyy-mm-dd HH24:MI:SS') from dual; --加1天 
select sysdate,to_char(sysdate+1/24,'yyyy-mm-dd HH24:MI:SS') from dual; --加1小时 
select sysdate,to_char(sysdate+1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual; --加1分钟 
select sysdate,to_char(sysdate+1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual; --加1秒 
 
--减法 
select sysdate,add_months(sysdate,-12) from dual; --减1年 
select sysdate,add_months(sysdate,-1) from dual; --减1月 
select sysdate,to_char(sysdate-7,'yyyy-mm-dd HH24:MI:SS') from dual; --减1星期 
select sysdate,to_char(sysdate-1,'yyyy-mm-dd HH24:MI:SS') from dual; --减1天 
select sysdate,to_char(sysdate-1/24,'yyyy-mm-dd HH24:MI:SS') from dual; --减1小时 
select sysdate,to_char(sysdate-1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual; --减1分钟 
select sysdate,to_char(sysdate-1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual; --减1秒 
 
--举例:
 
--1、取得当前日期是本月的第几周
select to_char(sysdate,'YYYYMMDD W HH24:MI:SS') from dual;
select to_char(sysdate,'W') from dual; 
 
--2、取得当前日期是一个星期中的第几天,星期日为第一天
 select sysdate,to_char(sysdate,'D') from dual; 
--类似: 
select to_char(sysdate,'yyyy') from dual; --年 
select to_char(sysdate,'Q' from dual; --季 
select to_char(sysdate,'mm') from dual; --月 
select to_char(sysdate,'dd') from dual; --日 
--ffffd 年中的第几天 
--WW 年中的第几个星期 
--W 该月中第几个星期 
--D 周中的星期几 
--hh 小时(12) 
--hh24 小时(24) 
--Mi 分 
--ss 秒 
 
--3、取当前日期是星期几,中文显示
select to_char(sysdate,'day') from dual;
 
--4、如果一个表在一个date类型的字段上面建立了索引,如何使用 
alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' 
 
--5、得到当前的日期 
select sysdate from dual; 
 
--6、得到当天凌晨0点0分0秒的日期 
select trunc(sysdate) from dual; 
-- 得到这天的最后一秒 
select trunc(sysdate) + 0.99999 from dual; 
-- 得到小时的具体数值 
select trunc(sysdate) + 1/24 from dual; 
select trunc(sysdate) + 7/24 from dual;
 
--7、得到明天凌晨0点0分0秒的日期 
select trunc(sysdate+1) from dual; 
select trunc(sysdate)+1 from dual; 
 
--8、本月一日的日期 
select trunc(sysdate,'mm') from dual; 
 
--9、得到下月一日的日期 
select trunc(add_months(sysdate,1),'mm') from dual; 
 
--10、返回当前月的最后一天
select last_day(sysdate) from dual; 
select last_day(trunc(sysdate)) from dual; 
select trunc(last_day(sysdate)) from dual; 
select trunc(add_months(sysdate,1),'mm') - 1 from dual; 
 
--11、得到一年的每一天 
select trunc(sysdate,'yyyy')+ rn -1 date0 
from 
(select rownum rn from all_objects 
where rownum<366); 
 
--12、今天是今年的第N天 
SELECT TO_CHAR(SYSDATE,'DDD') FROM DUAL; 
 
--13、如何在给现有的日期加上2年 
select add_months(sysdate,24) from dual; 
 
--14、判断某一日子所在年分是否为润年 
select decode(to_char(last_day(trunc(sysdate,'y')+31),'dd'),'29','闰年','平年') from dual; 
 
--15、判断两年后是否为润年 
select decode(to_char(last_day(trunc(add_months(sysdate,24),'y')+31),'dd'),'29','闰年','平年') from dual; 
 
--16、得到日期的季度 
select ceil(to_number(to_char(sysdate,'mm'))/3) from dual; 
select to_char(sysdate, 'Q') from dual;
*/

/*
*实验三 存储过程与触发器
*时间:2019-11-25
*联系:2838215550@qq.com
*附言:把代码写成一首诗。
*/
1、建立存储过程完成图书管理系统中的借书功能,并调用该存储过程实现借书功能。
  功能要求:
借书时要求输入借阅流水号,借书证号,图书编号。(即该存储过程有3个输入参数)
借书时,借书日期为系统时间。
图书的是否借出改为“是”。
调用存储过程实现借书证号“20051001”借出图书编号为“1005050”的图书。

create or replace procedure PR_借书 
(
v_借阅流水号 in 借阅.借阅流水号%type,
v_借书证号 in 借阅.借书证号%type,
v_图书编号 in 借阅.图书编号%type
)
as
begin
  insert into 借阅 values(v_借阅流水号,v_借书证号,v_图书编号,sysdate,null,null,null);
  update 图书 set 图书.是否借出='是' where 图书.图书编号=v_图书编号;
  commit;
end;

call PR_借书(7,20051001,1005050);
--调用完成。
exec PR_借书(8,20051001,1005050);
--PL/SQL 过程已成功完成。
       

2、建立存储过程完成图书管理系统中的预约功能。
预约时要求输入预约流水号,借书证号,ISBN。(即该存储过程有3个输入参数)
存储过程先检查输入的ISBN版本的图书是否都已借出,如果是则进行预约,否则提示“该书目有可借图书,请查找”。
预约时间为系统时间。
调用存储过程实现借书证号“20081237”预约ISBN为“9787508040110”的图书。

create or replace procedure PR_预约
(
v_预约流水号 in 预约.预约流水号%type,
v_借书证号 in 预约.借书证号%type,
v_ISBN in 预约.ISBN%type
)
as
v_数量 number;
begin
  select count(*) into v_数量 from 图书 where 图书.ISBN=v_ISBN and 图书.是否借出='否';
  if v_数量=0 then
    insert into 预约 values(v_预约流水号,v_借书证号,v_ISBN,sysdate);
    commit;
  else
    dbms_output.put_line('该书目有可借图书,请查找!');
  end if;
end;

call PR_预约(2,20081237,9787508040110);


3、建立存储过程完成图书管理系统中的还书功能。
还书时要求输入借书证号,图书编号,罚款分类号(即该存储过程有3个输入参数)。
还书日期为系统时间。
图书的是否借出改为‘否’。
调用存储过程实现借书证号“20051001”归还图书编号为“1005050”的图书。

create or replace procedure PR_还书
(
v_借书证号 借阅.借书证号%type,
v_图书编号 借阅.图书编号%type,
v_罚款分类号 借阅.罚款分类号%type
)
as
begin
  update 借阅 set 借阅.归还日期=sysdate,借阅.罚款分类号=v_罚款分类号 where 借阅.借书证号=v_借书证号 and 借阅.图书编号=v_图书编号;
  update 图书 set 图书.是否借出='否' where 图书.图书编号=v_图书编号;
  commit;
end;

call PR_还书(20051001,1005050,null);

4、通过序列和触发器实现借阅表中借阅流水号字段的自动递增。

create sequence SEQ_序列
minvalue 1
maxvalue 1.0E28
start with 8
increment by 1
cache 20;

create or replace trigger TR_借阅流水号自增 before insert on 借阅 for each row
begin
  select SEQ_序列.nextval into :new.借阅流水号 from dual;
end;


5、修改借书功能的存储过程。
   该存储过程要求:
  (1)借书时输入借书证号,图书编号。(即该函数有2个输入参数)
  (2)借书时,借书日期为系统时间。
       *该存储过程主体部分只有insert into语句。

create or replace procedure PR_借书 
(
v_借书证号 in 借阅.借书证号%type,
v_图书编号 in 借阅.图书编号%type
)
as
v_是否借出 图书.是否借出%type;
begin
  select 图书.是否借出 into v_是否借出 from 图书 where 图书.图书编号=v_图书编号;
  if v_是否借出='否' then
    insert into 借阅(借书证号,图书编号,借书日期) values(v_借书证号,v_图书编号,sysdate);
    update 图书 set 图书.是否借出='是' where 图书.图书编号=v_图书编号;
    commit;
  else
    dbms_output.put_line('该书已经被借走了!');
  end if;
end;       
       

6、建立与借书存储过程相对应的触发器,当借阅表中加入借阅信息时,该触发器触发,自动修改所借图书的是否借出改为‘是’。

create or replace trigger TR_借书 after insert on 借阅 for each row
begin
  update 图书 set 图书.是否借出='是' where 图书.图书编号=:new.图书编号;
end;



/*
*实验四 数据库恢复与安全性
*时间:2019-11-25
*联系:2838215550@qq.com
*附言:把代码写成一首诗。
*/
1、逻辑备份
(1)导出自己用户中的“预约”表
在运行中输入:exp 用户名/密码@orcl 
按照提示进行导出
(2)删除自己用户中的“预约”表
(3)进行导入数据库操作
在运行中输入:IMP 用户名/密码@orcl
按照提示进行导入
(4)查询导入的“预约”表中的信息。
(5)导出数据库(以全库方式导出)。
•必须是DBA才能执行完整数据库或表空间导出操作。

--exp s5120176308/orcl@orcl file=d:\back预约.dmp tables=预约 buffer=4096 log=d:\back预约.log;
---用cmd方式exp导出,如果存在空表则需要分配表空间,不然无法正常导出,本次实验要导出的表无空表。
--select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0 or num_rows is null;
--select table_name,segment_created from user_tables;

2、使用Flashback
  (1)设置行可移动
SQL>ALTER TABLE 读者 ENABLE   ROW  MOVEMENT
(2)在读者表中添加多条记录(或者删除没有借书的读者记录)。
(3)闪回到改变前(TO_ TIMESTAMP函数完成对非时间戳类型数据的转换)
SQL>FLASHBACK TABLE   读者 TO TIMESTAMP   TO_ TIMESTAMP(….)

alert table 读者 enable row movement;
flashback table   读者 to timestamp   to_timestamp('2019-12-01:11:00:00','yyyy-mm-dd-hh24-mi-ss');


3、使用PLSQL/developer 来完成SQL导出
(1)打开PLSQL/developer,选择菜单“工具“导出表
(2)点击你要导出的表(如:“预约”表),然后选择标签SQL 插入
(3)选中复选框创建表(create tables),浏览或者输入输出文件,然后点击导出
(4)在你输入的目录下找到你的导出文件(SQL 文件)
(5)删除自己表空间中的“预约”表
(6)通过“工具“导入表,利用SQL插入导入“预约”表。
(7)查询导入的“预约”表,检查导出是否正确。


4、使用PLSQL/developer 来完成PLSQL/developer方式导出
(1)打开PLSQL/developer,选择菜单“工具“导出表
(2)点击你要导出的表(如:“预约”表),然后选择标签PLSQL/developer
(3)浏览或者输入输出文件,然后点击导出。
(4)在你输入的目录下找到你的导出文件。
(5)删除自己表空间中的“预约”表
(6)通过“工具“导入表,PLSQL/developer方式并选中复选框“创建表”,导入“预约”表,。
(7)查询导入的“预约”表,检查导出是否正确。


5、以SYSTEM登录数据库,为你的帐号增加系统角色DBA.
conn system/orcl@orcl as sysdba;
grant dba to s5120176308;

6、 重新以自己的帐号登录,创建一个数据库用户:帐号_USER1(注:帐号即学生登录数据库帐号,如:S20160001),该用户拥有所有CONNECT, resource,DBA系统角色权限。
create user s5120176308_user1 identified by orcl;
grant connect,resource,dba to s5120176308_user1;

7、 建立角色:帐号_OPER,该角色拥有调用存储过程借书、还书、预约的权限,以及CONNECT系统角色权限。
(注:执行存储过程的授权语句Grant execute on procedure_name  to user/role)
create role s5120176308_oper;
grant execute on PR_还书 to s5120176308_oper;
grant execute on PR_预约 to s5120176308_oper;
grant execute on PR_借书 to s5120176308_oper;
grant connect to s5120176308_oper;

8、创建一个数据库用户:帐号_USER2(注:帐号如:S20160001)
为该用户授权角色:帐号_OPER。以该用户登录,完成借书功能。
create user s5120176308_user2 identified by orcl;
grant s5120176308_oper to s5120176308_user2;
conn s5120176308_user2/orcl@orcl;
call s5120176308.PR_借书(20051001,1005050);

9、以自己的帐号登录,如:S20160001,建立视图VIEW_READER, 该视图包含书目(ISBN, 书名,作者,出版单位,图书分类名称)(注:所有属性来自关系书目和图书分类)
/*
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name
    [(alias[, alias]...)]
AS subquery
     [WITH CHECK OPTION [CONSTRAINT constraint]]
     [WITH READ ONLY]*/

conn s5120176308/orcl@orcl;
create view view_reader as select ISBN,书名,作者,出版单位,类名 图书分类名称 from 书目,图书分类 where 书目.图书分类号=图书分类.图书分类号; 

10、 创建一个数据库用户:帐号_USER3(注:帐号如:S20160001),该用户具有对视图VIEW_READER查询的权限。
create user s5120176308_user3 identified by orcl;
grant select on view_reader to s5120176308_user3;


11、创建一个概要文件,如果 帐号_USER3连续3次登录失败,则锁定该账户,10天后该账户自动解锁。把创建的概要文件分配给该账号,然后以该用户登录进行权限测试。
/*
CREATE PROFILE profile_name LIMIT
{ resource_parameters | password_parameters }
[ resource_parameters | password_parameters ]...;

{ { SESSIONS_PER_USER | CPU_PER_SESSION | CPU_PER_CALL | CONNECT_TIME | IDLE_TIME    
     | LOGICAL_READS_PER_SESSION | LOGICAL_READS_PER_CALL | COMPOSITE_LIMIT }
     { integer | UNLIMITED | DEFAULT }
| PRIVATE_SGA { integer [ K | M ] | UNLIMITED | DEFAULT }
}

{ { FAILED_LOGIN_ATTEMPTS | PASSWORD_LIFE_TIME | PASSWORD_REUSE_TIME
     | PASSWORD_REUSE_MAX | PASSWORD_LOCK_TIME | PASSWORD_GRACE_TIME }
     { expr | UNLIMITED | DEFAULT }
| PASSWORD_VERIFY_FUNCTION { function | NULL | DEFAULT }
}
select * from dba_profiles;
drop profile profile_name;
*/
create profile profile_s5120176308_user3 limit
failed_login_attempts 3
password_lock_time 10;

alter user s5120176308_user3 profile profile_s5120176308_user3;



/*
*实验结束 删库跑路
*时间:2019-11-25
*联系:2838215550@qq.com
*附言:把代码写成一首诗。
*/
drop view view_reader;
drop profile profile_s5120176308_user3 cascade;
drop user s5120176308_user3;
drop user s5120176308_user2;
drop user s5120176308_user1;
drop role s5120176308_oper;
drop trigger TR_借书;
drop trigger TR_借阅流水号自增;
drop sequence SEQ_序列;
drop procedure PR_还书;
drop procedure PR_借书;
drop procedure PR_预约;
drop table 预约;
drop table 借阅;
drop table 罚款分类;
drop table 读者;
drop table 图书;
drop table 书目;
drop table 图书分类;

未经作者允许,禁止转载!

首发https://blog.csdn.net/Boy_z/article/details/103250346


登录 后发表评论
1条评论