实验二 SQL语言

浅浅的花香味﹌ 2022-11-28 15:16 176阅读 0赞

## **实验二 SQL语言** ##

**实验目的**  
熟悉并掌握创建表，插入记录，查询记录，删除记录，修改记录。  
创建索引，删除索引。  
创建视图，使用视图，删除视图。

**实验内容**  
现有一个单位内部的小型图书借阅系统，假设每本图书的数量无限制，并且可以借给任何单位成员，每个单位成员可以借多本书，单位成员与图书的关系是多对多的关系。假设系统中仅有三个关系模式。

**数据表结构**  
Reader表  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk4MTMxNQ_size_16_color_FFFFFF_t_70_pic_center]

Book表  
![在这里插入图片描述][20200818154319373.JPG_pic_center]  
RB表  
![在这里插入图片描述][20200818154333376.JPG_pic_center]

1、	创建新的用户并授权：
    2、	以用户CC的身份建立连接，并在此连接下执行后面的操作；
    3、	拷贝代码运行，删去旧的同名数据表：
    4、	建立表格Reader
    5、	拷贝代码运行，向Reader表格中插入十条数据
    6、	查询记录：在Reader表中查询直接上司是“李四”的员工的名字
    7、	修改记录：在Reader表中把直接上司是“李四”的员工的办公地点统一改为“420”
    8、	删除记录：在Reader表中把直接上司未赋值（NULL）是记录删去
    9、	删去数据表：把整个Reader表删去
    10、	重新执行第一、二步，即建立数据表、插入数据。
    11、	创建表格Reader2，比较Reader2和Reader中的记录和结构是否相同
    12、	分别执行下面的每行语句，查看语句是否执行成功，分析为什么？
    13、	删除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');

初始数据  
reader表

RNO	RNAME	RSEX	RAGE	RBOSS	RADDRESS
    R001	张三	男	20	李四	416
    R002	张三	女	35			417
    R003	李四	男	30			416
    R004	王五	男	20			417
    R005	马六	男	40			416
    R006	刘三	男	20			417
    R007	王四	男	40	李四	416
    R008	李小龙	男	20	李四	417
    R009	王小倩	男	40	李四	416
    R010	王一小	男	20	李四	417

book表中数据

BNO	BNAME	BAUTHOR	BPRESS	BPRICE
    B002	数据结构	唐发根	北航出版社	24
    B003	数据库原理	王珊	高等教育出版社	40
    B004	数据库原理	张飞	清华大学出版社	30
    B005	数据库原理	王珊	清华大学出版社	
    B001	数据结构	严蔚敏	清华大学出版社

RB表中数据

RNO	BNO	RBDATE（实验时间）
    R001	B001	插入数据时的系统时间
    R001	B002	
    R001	B004	
    R002	B001	
    R003	B001	
    R004	B001	
    R004	B002	
    R005	B001	
    R006	B001	
    R006	B003	
    R006	B005	
    R006	B002	
    R006	B004

1、	执行insert into RB(RNO,BNO) values('R010','B005');
    2、	写出删除Reader表格中编号为’R010’的员工，如果执行错误，分析错误原因。
    3、	想办法删除Reader中的’R010’员工；
    方法一：先把RB中所有’R010’的借书记录都删掉
    方法二：修改数据表READER的结构，允许级联删除（注：ORACLE不支持级联更新）。
    4、	为Reader表添加一个属性列“出生年份”，名为Rbirthday，整数；
    5、	对于Reader表格，员工编号可以确定年龄，年龄又可以确定出生年份，因此存在传递函数依赖关系，删除Rbirthday列，使关系模式符合第三范式要求；
    6、	修改Reader表格的Raddress属性，使其长度为50，数据类型不变；
    7、	修改book表的Bprice属性，使其值得范围在10到100之间；
    8、	修改Reader表的Rage属性，使其值得范围为16到60之间。
    9、	试试是否可以删除Reader表，使用CASCADE是否可以删除？
    10、	删除Reader、Book和RB表。

索引的建立与删除

1、	重新执行前面的代码创建三个数据表并插入数据；
    2、	为Reader表格的Rname建立UNIQUE索引
    如何修改表格数据，再建索引？
    3、	删除索引。

视图

1、	如果上面的运行是在CC的连接中，则需要回到在sysdba 的连接中，执行：
    grant resource, connect, DBA  to cc;
    否则系统显示没有创建视图的权限。执行完之后再回到CC的连接：
    在ORACLE SQL Developer 的左上方：  
    2、	建立在416办公室工作的视图V416，视图包括员工的编号、姓名、年龄等信息
    3、	从V416中查询年龄大于30的员工信息
    4、	向视图V416中插入一条新的员工记录，然后从V416中查找该条记录，测试是否可以找到；
    插入成功了吗？为什么通过视图插入的纪录在视图中看不见？
    5、	建立在417办公室工作的视图V417，视图包括员工的编号、姓名、性别、年龄等信息，视图定义带with check option选项；
    6、	向视图V417中插入一条新的员工记录，然后从V417中查找该条记录，测试是否可以找到；
    7、	通过视图删除刚插入的员工记录，在基本表Reader中查看是否已经删除成功？
    8、	在视图V417上建立所有女员工信息的视图FV417，查询视图结果
    9、	删除视图V417中没有借阅图书的员工信息
    10、	建立视图GV，数据包括每本图书的编号及其借阅数量。思考是否可以向GV中插入数据，为什么？
    11、	删除视图V417

五、实验过程原始记录(实验过程、数据、图表、计算等)  
1、2、

1，2连接数据库按指导书代码执行完毕。

3、

3、删除同名数据库按指导书书代码执行完毕，
    问：为何要先删去RB？能不能先删去READER？
    答：因为RB引用了READER的RNO作为外码，所以，要先删去RB，才能删去READER。

4、

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、

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、

select Rname
    from reader
    where (rboss='李四');

7、

UPDATE reader
    set Raddress='420'
    where rboss='李四';

8、

DELETE 
    FROM reader
    WHERE rboss is NULL;

9、

DROP TABLE  Reader;

10、

create table Reader(
        RNO varchar2(4) primary key,
        Rname varchar2(10) not null,
        Rsex varchar2(3),
        Rage integer,
        Rboss varchar2(10),
        Raddress varchar2(30)
    );
    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');

11、

create table reader2 as select *  from reader;
    desc reader; 
    desc reader2;

12、

update reader set RNO='R001' where Rname='张三';
    update reader2 set RNO='R001' where Rname='张三';*/
    insert into reader2(RNO,Rname,Rsex,Rage,Rboss, Raddress) values(null,'lisi',null,null,null,null);

13、

drop table reader2;

14、

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、

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、

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、

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、

delete from reader where RNO='R010';

2、

delete from reader where RNO='R010';

3、

delete from RB where RNO='R010';
    delete from reader where RNO='R010';

4、

alter table reader add Rbirthday integer;

5、

alter table reader drop column Rbirthday;

6、

alter table reader modify Raddress varchar2(50);

7、

alter table book add constraint ck_bprice check (bprice between 10 and 100);

8、

alter table Reader add constraint ck_Rage check(Rage between 16 and 60);

9、

drop table reader cascade

10、

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

索引的建立与删除  
1、

create table Reader(
        RNO varchar2(4) primary key,
        Rname varchar2(10) not null,
        Rsex varchar2(2),
        Rage integer,
        Rboss varchar2(10),
        Raddress varchar2(30)
    );
    create table Book(
        BNO varchar2(4) primary key,
        Bname varchar2(50) not null,
        Bauthor varchar2(50),
        Bpress varchar2(50),
        Bprice numeric(6,2)
    );
    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)
    );
    alter table Reader modify Rsex varchar(3);
    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');
    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);
    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');

2、

delete from RB where RNO='R002';
    delete from reader where RNO='R002';
    create unique index myindex on reader(rname);

3、

Drop index myindex;

视图  
1、

grant resource, connect, DBA  to cc;

2、

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

3、

select *
    from V416
    WHERE rage>30;

4、

insert into v416( rno,rname,rage) values('R999','new reader',99);
    select * from reader
    select * from v416

5、

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

6、

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、

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

8、

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

9、

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

10、

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

11、

drop view v417

六、 实验结果及分析  
实验结果截图，对实验进行说明和分析。  
本处由于实验中所需截图过多，不进行一一上传，有需要实验结果截图或者其他实验报告的同学可以点击https://download.csdn.net/download/weixin\_43981315/12721378获取！

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80Mzk4MTMxNQ_size_16_color_FFFFFF_t_70_pic_center]: /images/20221124/cd5043770a184a8fa473230ceb907ffb.png
[20200818154319373.JPG_pic_center]: /images/20221124/146bc72f3e7c46f5a88c782c4cd57413.png
[20200818154333376.JPG_pic_center]: /images/20221124/28ae9465940e4c259803f02ac0b48b4b.png