SQL语言

最新推荐文章于 2022-10-01 13:08:10 发布

Mgxxxxx

最新推荐文章于 2022-10-01 13:08:10 发布

阅读量1k

点赞数

文章标签：数据库

本文链接：https://blog.youkuaiyun.com/weixin_43895948/article/details/106145916

版权

1、创建新的用户并授权：

create user cc identified by ccpassword ;
grant resource, connect, DBA  to cc;

2、以用户CC的身份建立连接，并在此连接下执行后面的操作；
3、拷贝代码运行，删去旧的同名数据表：

Declare
tmp integer default 0;
Begin
select count(*) into tmp from user_tables where table_name='RB';
if(tmp>0) then
execute immediate 'drop table RB';
end if;
select count(*) into tmp from user_tables where table_name='READER';
if(tmp>0) then
execute immediate 'drop table READER';
end if;
select count(*) into tmp from user_tables where table_name='BOOK';
if(tmp>0) then
execute immediate 'drop table BOOK';
end if;
end;

问：为何要先删去RB？能不能先删去READER？
答：因为reader中有的属性为rb的主码，先删除reader的话，rb的数据会丢失，不能构成一个完整的表，会报错，故不能先删除reader。

4、建立表格Reader

create table Reader
(
    RNO	varchar2(4) primary key,
    Rname	varchar2(10) not null,
    Rsex	varchar2(2),
    Rage	integer,
    Rboss	varchar2(10),
    Raddress	varchar2(30)
) ;

5、拷贝代码运行，向Reader表格中插入十条数据

1、	创建新的用户并授权：
create user cc identified by ccpassword ;
grant resource, connect, DBA  to cc;
2、	以用户CC的身份建立连接，并在此连接下执行后面的操作；
3、	拷贝代码运行，删去旧的同名数据表：
Declare
tmp integer default 0;
Begin
select count(*) into tmp from user_tables where table_name='RB';
if(tmp>0) then
execute immediate 'drop table RB';
end if;
select count(*) into tmp from user_tables where table_name='READER';
if(tmp>0) then
execute immediate 'drop table READER';
end if;
select count(*) into tmp from user_tables where table_name='BOOK';
if(tmp>0) then
execute immediate 'drop table BOOK';
end if;
end;

问：为何要先删去RB？能不能先删去READER？
答：因为reader中有的属性为rb的主码，先删除reader的话，rb的数据会丢失，不能构成一个完整的表，会报错，故不能先删除reader。
4、建立表格Reader

create table Reader
(
    RNO	varchar2(4) primary key,
    Rname	varchar2(10) not null,
    Rsex	varchar2(2),
    Rage	integer,
    Rboss	varchar2(10),
    Raddress	varchar2(30)
) ;

5、拷贝代码运行，向Reader表格中插入十条数据

insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R001','张三',20,'男','李四','416');
insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R002','张三',35,'女',null,'417');
insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R003','李四',30,'男',null,'416');
insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R004','王五',20,'男',null,'417');
insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R005','马六',40,'男',null,'416');
insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R006','刘三',20,'男',null,'417');
insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R007','王四',40,'男','李四','416');
insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R008','李小龙',20,'男','李四','417');
insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R009','王小倩',40,'男','李四','416');
insert into Reader (RNO,Rname,Rage,Rsex,Rboss, Raddress) values('R010','王一小',20,'男','李四','417');

6、查询记录：在Reader表中查询直接上司是“李四”的员工的名字

SELECT Rname FROM Reader WHERE Rboss = '李四';

7、修改记录：在Reader表中把直接上司是“李四”的员工的办公地点统一改为“420”

UPDATE Reader SET Raddress=420 WHERE Rboss = '李四';

8、删除记录：在Reader表中把直接上司未赋值（NULL）是记录删去

DELETE FROM Reader WHERE Rboss IS NULL;

9、删去数据表：把整个Reader表删去

DROP TABLE Reader RESTRICT;

10、重新执行第一、二步，即建立数据表、插入数据。
11、创建表格Reader2，比较Reader2和Reader中的记录和结构是否相同

create table reader2 as select *  from reader; --创建表格reader2

12、分别执行下面的每行语句，查看语句是否执行成功，分析为什么？

update reader set RNO='R001' where Rname='张三';
//不成功，因为在Reader中，RNO是主码，一张表中，不能有相同的主码
update reader2 set RNO='R001' where Rname='张三';
//成功，因为在Reader2中，RNO并非主码，RNO可以相同
insert into reader2(RNO,Rname,Rsex,Rage,Rboss, Raddress) values(null,'lisi',null,null,null,null);
//成功，因为在Reader2中，RNO并非主码，RNO可以为空

13、删除Reader2表格；

Drop table reader2;

14、拷贝代码运行，建立表格Book

create table Book
(
    BNO	varchar2(4),
    Bname	varchar2(50) not null,
    Bauthor	varchar2(50),
    Bpress	varchar2(50),
    Bprice	numeric(6,2), 
    primary key(BNO)
);

15、拷贝代码运行，向Book表格中插入5条数据

insert into book (BNO,Bauthor,Bname, bpress, bprice) values('B001','严蔚敏','数据结构','清华大学出版社',null);
insert into book (BNO,Bauthor,Bname, bpress, bprice) values('B002','唐发根','数据结构','北航出版社',24);
insert into book (BNO,Bauthor,Bname, bpress, bprice) values('B003','王珊','数据库原理','高等教育出版社',40);
insert into book (BNO,Bauthor,Bname, bpress, bprice) values('B004','张飞','数据库原理','清华大学出版社',30);
insert into book (BNO,Bauthor,Bname, bpress, bprice) values('B005','王珊','数据库原理','清华大学出版社',null);

16、拷贝代码运行，建立表格RB

create table RB
(
    RNO varchar2(4),
    BNO varchar2(4),
    RBdate date default sysdate,
    primary key(RNO,BNO),
    foreign key (RNO) references Reader(RNO),
    foreign key (BNO) references Book(BNO)
);

17、拷贝代码运行，向RB表格中插入13条数据；

insert into RB (RNO,BNO) values ('R001','B001');
insert into RB (RNO,BNO) values ('R001','B002');
insert into RB (RNO,BNO) values ('R001','B004');
insert into RB (RNO,BNO) values ('R002','B001');
insert into RB (RNO,BNO) values ('R003','B001');
insert into RB (RNO,BNO) values ('R004','B001');
insert into RB (RNO,BNO) values ('R004','B002');
insert into RB (RNO,BNO) values ('R005','B001');
insert into RB (RNO,BNO) values ('R006','B001');
insert into RB (RNO,BNO) values ('R006','B003');
insert into RB (RNO,BNO) values ('R006','B005');
insert into RB (RNO,BNO) values ('R006','B002');
insert into RB (RNO,BNO) values ('R006','B004');

1、执行insert into RB(RNO,BNO) values(‘R010’,‘B005’);
2、写出删除Reader表格中编号为’R010’的员工，如果执行错误，分析错误原因。
因为RB表中已经定义了外键RNO（关联了Reader）和BNO（关联了Book），如果删去’R010’就违反了参照完整性。
3、想办法删除Reader中的’R010’员工；

alter table rb drop constraint sys_c005544;
alter table rb add constraint MYFK foreign key(rno) references reader(rno) on delete cascade;
delete from reader where rno='R010';

4、为Reader表添加一个属性列“出生年份”，名为Rbirthday，整数；

alter table reader add rbirthday INT;

5、对于Reader表格，员工编号可以确定年龄，年龄又可以确定出生年份，因此存在传递函数依赖关系，删除Rbirthday列，使关系模式符合第三范式要求；

alter table reader drop column rbirthday;

6、修改Reader表格的Raddress属性，使其长度为50，数据类型不变；

alter table reader modify raddress varchar2(50);

7、修改book表的Bprice属性，使其值得范围在10到100之间；

alter table book add constraint bp check (Bprice BETWEEN 10 and 100);

8、修改Reader表的Rage属性，使其值得范围为16到60之间。

alter table Reader add constraint rr check (Rage BETWEEN 16 and 60);

9、试试是否可以删除Reader表，使用CASCADE是否可以删除？

drop table reader CASCADE;
//删除失败（缺失关键字）

10、删除Reader、Book和RB表。

drop table reader cascade constraints;
drop table book cascade constraints;
drop rb book cascade constraints;

1、重新执行前面的代码创建三个数据表并插入数据；
2、为Reader表格的Rname建立UNIQUE索引

create unique index myindex on reader(rname);
报告出错："cannot CREATE UNIQUE INDEX; duplicate keys found"

如何修改表格数据，再建索引？
把创建唯一索引的那一列重复的数据删掉。
3、删除索引。

drop index myindex;

视图
1、如果上面的运行是在CC的连接中，则需要回到在sysdba 的连接中，执行：

grant resource, connect, DBA  to cc;

2、建立在416办公室工作的视图V416，视图包括员工的编号、姓名、年龄等信息

CREATE VIEW V416 as select rno,rname,rage from reader where raddress='416'

确认该视图的存在：

select * from v416

3、从V416中查询年龄大于30的员工信息

SELECT rno,rname from v416 WHERE rage>30;

4、向视图V416中插入一条新的员工记录，然后从V416中查找该条记录，测试是否可以找到；

insert into v416( rno,rname,rage) values('R999','new reader',99);
select * from reader
select * from v416
//查找不到

插入成功了吗？为什么通过视图插入的纪录在视图中看不见？
插入成功了，因为插入的元组并未设置raddress，只有raddress为416的才会显示到v416中
5、建立在417办公室工作的视图V417，视图包括员工的编号、姓名、性别、年龄等信息，视图定义带with check option选项；

CREATE VIEW V417 as select rno,rname,rsex,rage,raddress from reader where raddress='417' with check option;

6、向视图V417中插入一条新的员工记录，然后从V417中查找该条记录，测试是否可以找到；

insert into v417( rno,rname,rage) values('R777','417 reader',66);
//执行不成功。
drop view v417;
CREATE VIEW V417 as select rno,rname,rsex,rage,raddress from reader where raddress='417' with check option;
insert into v417( rno,rname,rage,raddress) values('R777','417 reader',66,'417');
//执行成功。

7、通过视图删除刚插入的员工记录，在基本表Reader中查看是否已经删除成功？

select * from v416;
delete from v416 where rname='王小倩';
select * from v416;

8、在视图V417上建立所有女员工信息的视图FV417，查询视图结果

CREATE VIEW FV417 as select * from V417 where RSEX='女';

9、删除视图V417中没有借阅图书的员工信息

SELECT * FROM V417;
Delete from v417 where v417.rno not in (SELECT rno from rb);
SELECT * FROM V417;

10、建立视图GV，数据包括每本图书的编号及其借阅数量。思考是否可以向GV中插入数据，为什么？

SELECT BNO,COUNT(*) FROM RB GROUP BY BNO;
CREATE VIEW GV(BNO,COUNTS)  AS  SELECT BNO,COUNT(*) FROM RB GROUP BY BNO;

不可以向GV插入数据，因为GV的内容并非数据，而是数据和相关数据统计结果。
11、删除视图V417

drop view v417;

触发器
参考PLSQL帮助文档，试试建立一个触发器。在用户执行删除某个员工的时候触发，该触发器的功能是先删除RB中该员工的借书记录，然后删除Reader中该员工记录。

create or replace TRIGGER mytir 
BEFORE DELETE ON SCOTT.Reader 
FOR EACH ROW 
BEGIN 
DELETE FROM RB WHERE RNO=:old.RNO; 
END;