数据库原理实验一 SQL语言 实验报告

                                     广州大学学生实验报告
                                       实验一:SQL语言

此篇博文主要分享本人的SQL语言—实验报告
此篇分享仅供参考学习,图文禁复制,勿作他用!谢谢配合!

数据库原理实验之实验一:SQL语言
软件:Oracle SQL Developer

实验今天刚开始,然后花了几个小时做完了,花费时间多说明之前学的SQL语句记忆还不劳,还得继续苦练,加油!

在这里插入图片描述

一、数据库的创建与删除

1)使用Oracle SQL Developer创建连接
1、新建连接,system用户,密码是之前设置的。
在这里插入图片描述

2、创建新用户,名字为cc,密码是:c123,然后点击新建连接,输入用户名和密码即可。

create user cc identified by c123;
grant resource,connect to cc;

结果如下:
在这里插入图片描述

此次实验涉及的数据表:
在这里插入图片描述

2)创建以下数据表(Student(主码为SNO)、Course(主码为CNO)、SC(主码为(SNO、CNO)),其中SNO引用Student的SNO属性,CNO引用Course的CNO属性)
执行以下语句即可创建Student,Course,SC表:

create table Student
        (Sno    varchar2(17)  primary key,
         Sname  varchar2(10) not null UNIQUE,        
         Sage   INT,
         Ssex   char(2) CHECK(Ssex IN ('男','女') ), 
         Sdept  varchar2(20)
         );


create table Course
  (
   Cno varchar2(5) primary key,
   Cname varchar2(20) not null,
   CPno varchar2(5),
   Ccredit int
  );


  create table SC
    (
  Sno varchar2(17),
  Cno varchar2(5),
  Grade numeric(5,2),
  primary key(Sno,Cno),
  foreign key(Sno) references Student(Sno),
  foreign key(Cno) references Course(Cno)
);

3)修改Course表的CPNO,使其为外码,引用Course表的CNO属性。

--执行语句
alter table course add constraint fk1 
foreign key(CPno) references course(Cno);
--表示CPno是外码,被参照表是Course,被参照列是Cno

4)修改Student表格,用SQL语句为Student表格添加一个“入学时间”属性,属性名为Senrollment。

ALTER TABLE Student ADD Senrollment DATE;  

5)向三个表格中插入3条数据,数据内容自编。
先查询日期格式语句:select sysdate from dual;
结果如下:
在这里插入图片描述
然后,向Student表中插入三行数据:

insert into  Student values ('201000610002','罗嘉铭',
18,'男','网络工程','01-9月-2010');
insert into  Student values  ('201000610003','叶茵',
17,'女','软件工程','01-9月-2010');
insert into  Student values  ('201000610004','陈凯怡',
19,'女','网络工程','01-9月-2010');

select * from Student; 

查看结果如下:
在这里插入图片描述
然后,向Course表中插入三行数据:

insert into Course values('1','离散数学',null,3);
insert into Course values('2','数据结构','1',3);
insert into Course values('3','数据库','2',3);

select * from Course; 

查看结果如下:
在这里插入图片描述
最后向SC表中插入三行数据:

insert into SC values('201000610002','1',92);
insert into SC values('201000610004','2',88.5);    
insert into SC values('201000610003','3',96.5);

select * from SC;

查看结果如下:
在这里插入图片描述
6)限定Ssex的值只能为“男”或者“女”。

--这个在创建表时已经限定了,具体语句为:
  Ssex   char(2)  CHECK(Ssex IN ('男','女') ),

7)修改Course表格,用SQL语句为Course表格添加一个“说明”属性,属性名为“Cdesc”,类型为varchar2,长度为200。

--执行语句:
alter table Course add Cdesc varchar2(200);
--脚本输出:Table COURSE 已变更。

查看结果如下:

在这里插入图片描述
8)更改Course表格的Cdesc属性,使其长度变为500。
执行语句:

alter table Course modify(Cdesc varchar2(500));
--脚本输出显示:Table COURSE已变更。

9)删除刚建立的属性Cdesc。
执行语句:

 alter table Course drop column Cdesc;

再查询Course表,结果可见Cdesc属性已经删除了,如下:

在这里插入图片描述

二、数据查询

1、创建新的用户并授权

create user bb identified by b123;
grant resource,connect,DBA to bb;

2、以用户bb的身份建立连接,并在此连接下执行后面的操作。
在这里插入图片描述
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表中记录了员工的借阅书籍记录,RB表中的RNO属性参照的是READER表中
   的RNO列,如果先删去READER,则会使RB表不满足其表级完整性约束条件。

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)
) ;

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');

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

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);

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)
);

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');

5、初始数据查询结果如下:
reader表数据:
在这里插入图片描述
book表数据:
在这里插入图片描述
RB表数据:
在这里插入图片描述

6、单表查询

1、查询全体员工的姓名和出生年份;

--执行语句:
select Rname,2020-Rage Birth from Reader;

结果如下:
在这里插入图片描述
2、查询工作在416房间的员工的所有信息;

--执行语句:
select * from Reader where raddress='416';

结果:
在这里插入图片描述
3、查询年龄在30到50岁之间的员工姓名、年龄;
select rname,rage from reader where rage>30 and rage<50;
结果:
在这里插入图片描述
4、查询借了书的员工的编号,排除相同的元素;

select  distinct  rno  from rb;

结果:
在这里插入图片描述
5、查询名字中包含字“小”的员工姓名、办公地点;

select rname ,raddress from reader 
where Rname like'小%' or Rname like'_小%' or Rname like'__小%';

结果:
在这里插入图片描述
6、查询名字中第二个字为“小”的员工姓名、办公地点;

select rname,raddress from reader where Rname like'_小%';

结果:
在这里插入图片描述
注:ORACLE只需一个下划线符号代表一个汉字

7、查询所有不姓“李”的员工姓名、性别;

select rname,rsex from reader where  Rname not like'李%';

结果:
在这里插入图片描述
8、查询Book表中价格不为空值的书名、出版社;

select bname,bpress,bprice from book;
--上面语句是为了查看后面的语句结构对不对
select bname,bpress from book where  bprice is not null;

结果:
在这里插入图片描述
9、查询清华大学出版社和高等教育出版社出版的所有书籍,按照出版社降序、书名升序排列;

select * from book;  /*用来查看后面的语句结果对不对*/

select * from book  where   
bpress='清华大学出版社' or bpress='高等教育出版社'
order by bpress desc,bname asc;

结果:
在这里插入图片描述
10、查询员工的总人数

SELECT  count(distinct Rno)  FROM READER
--(若有重复的已消除了!此处Rno唯一的,可以不需要distinct)

结果:
在这里插入图片描述
11、查询借了书的员工的人数;

SELECT  count(distinct Rno)  FROM READER WHERE READER.RNO IN
 (select distinct Rno from RB);
--或:
SELECT  count(distinct Rno)  FROM RB;

结果:
在这里插入图片描述
12、查询“张三”所借图书的数量;

select  count(*)  from rb where rno in
(select Rno from reader where Rname='张三');  
--(此处有两个张三,一男一女,共借阅了四本书!)

结果:
在这里插入图片描述
13、查询最贵的书籍的作者姓名;

select bauthor from book 
where  bprice=(select max(bprice) from book);

结果:
在这里插入图片描述
14、查询Book表中书籍的平均价格,查询结果说明了什么;

select    avg(bprice)  from book;
--(去掉了空值再计算得到的结果。)

结果:
在这里插入图片描述
查询结果说明:当聚集函数avg遇到空值时,会跳过空值而只处理非空值。

15、查询book中包含的各个出版社及其出版书籍的数量;

SELECT  bpress,count(*)  FROM book  group by  bpress;

结果:
在这里插入图片描述

7、复合查询

1、查询每个员工及其借书情况,列出员工编号、姓名和借书日期

select reader.rno,rname,rbdate from reader,rb 
where reader.rno=rb.rno;

结果:
在这里插入图片描述
2、查询每个员工及其借书所有字段(没有借书的员工也列出来);(用左外连接)

select reader.rno,rname,rsex,rb.bno,rbdate 
from reader left outer join rb on (reader.rno=rb.rno);

结果:
在这里插入图片描述
3、查询与“李小龙”工作在同一个办公室的员工信息(用两种方法:自身连接、子查询);

--自身连接:
select b.rname from reader a, reader b where 
a.raddress=b.raddress and a.rname='李小龙';
   
--子查询:
select RNAME from READER WHERE RADDRESS=
(select raddress from reader where rname='李小龙');

结果均为:
在这里插入图片描述
4、查询借阅了“数据库原理”的员工所有信息(两种方法:连接查询、子查询)

--连接查询:
select distinct reader.rno,rname,rsex,rage,rboss,raddress 
from reader,rb,book 
where reader.rno=rb.rno 
and rb.bno=book.bno 
and bname='数据库原理';

--子查询:
select RNO,RNAME,RSEX,RAGE,RBOSS,RADDRESS from READER 
where RNO in 
(select Rno from rb,book
 where rb.bno=book.bno and bname='数据库原理');

结果均为:
在这里插入图片描述
5、查询小于或等于同一办公室中成员的平均年龄的员工编号、姓名、年龄。

select RNO,RNAME,RAGE,RADDRESS 
from READER order by raddress,rage asc;
select raddress,avg(rage) from reader group by raddress ;
--上面的语句用来查看后面的语句结果对不对

select a.rno,a.rname,a.rage,a.raddress from reader a where
 a.rage<=(select avg(rage) from reader) order by raddress;

结果:(对照上面两个表格,可证结果正确!)
在这里插入图片描述
6、查询比所有数据库原理价格都低、并且不是清华大学出版社出版的书籍的信息;

select * from BOOK where BNAME='数据库原理'; 
select min(BPRICE) from BOOK where BNAME='数据库原理';
select * from BOOK; 
--以上语句用来查看后面的语句结果对不对

select * from book where bprice<(select min(bprice) from book 
where bname='数据库原理'and bpress!='清华大学出版社';  

结果:(对照上面三个表格,可知结果正确!)
在这里插入图片描述
7、查询借阅了B001的员工的编号、姓名、办公室;

select READER.RNO,RNAME,RADDRESS from READER;
select * from rb where bno='B001';
--上面的语句用来查看后面的语句结果对不对

select reader.rno,rname,raddress from reader,rb where  
reader.rno=rb.rno and bno='B001';

结果:(与上面语句查询所得结果一致!)
在这里插入图片描述
8、查询没有借阅B001的员工的编号、姓名、办公室;

select rno,rname,raddress from reader where rno  
not in(select rno from rb where bno='B001');

结果:
在这里插入图片描述
9、查询借阅了所有书籍的员工的姓名;

select bno from book;
select reader.rno,rname,rb.bno 
from rb left outer join reader  on (reader.rno=rb.rno);
--上面的语句用来查看后面的语句结果对不对

select RNAME from READER 
  where rno=
    (
    select rno  from rb
    group by rb.rno
    having count(rb.bno)>=
        (select count(*)   from book
        )
    );

结果:(对照前面两张表,可知结果正确!)
在这里插入图片描述
10、查询至少借阅了编号为R004的员工借阅的全部书籍的员工姓名、性别、办公室

Select * From Rb Where Rno='R004'; (借阅了B001和B002)
select reader.rno,rname,rb.bno 
from rb left outer join reader  on (reader.rno=rb.rno);
--上面的语句用来查看后面的语句结果对不对

Select Rname,Rsex,raddress From Reader 
  Where  not exists
    (
    select *
    from rb a
    where a.rno='R004' and 
        not exists 
        (
        select *
        from rb b
        where b.rno=reader.rno and a.bno=b.bno
        ));

结果:(对照上面两个表格,可证结果正确!)
在这里插入图片描述
11、查询年龄大于30岁或者工作在416的员工信息(用集合查询完成)

select * from reader where rage>30    
union
select * from reader where raddress='416';

结果:
在这里插入图片描述
12、 查询年龄大于30岁并且工作不在416的员工信息(用集合查询完成)

--有两种方法:差操作或者交操作均可。
1select * from reader where rage>30
minus
select * from reader where raddress='416';

2select * from reader where rage>30    
intersect
select * from reader where raddress!='416';

结果均为:
在这里插入图片描述
注意:ORACLE的集合减,不是’except’,而是’ minus’!

三、数据更新

以用户bb的身份建立连接,并在此连接下执行后面的操作。
1、查询记录:在Reader表中查询直接上司是“李四”的员工的名字

select rname from reader where rboss='李四';

结果:
在这里插入图片描述
2、修改记录:在Reader表中把直接上司是“李四”的员工的办公地点统一改为“420”

update  reader set raddress='420'
where rboss='李四';
--查询:
select rname,rboss,raddress from reader where rboss='李四';

结果:
在这里插入图片描述
3、删除记录:在Reader表中把直接上司未赋值(NULL)是记录删去

/*此处得先删除RB表中没有直接上司的员工的借书记录,
才能删除Reader表中该员工记录!*/
delete from rb 
where rno in
(select rno from reader
 where rboss is null);
delete from reader where Rboss is null;

结果:
在这里插入图片描述
4、删去数据表:把整个Reader表删去

drop table reader cascade constraints;
/*(必须加上关键字:constraints才能删除,
而且该表被RB表参照,得级联删除cascade)
结果:脚本输出:Table READER已删除。*/

5、重新执行建立数据表、插入数据。

create table Reader
(
    RNO	varchar2(4) primary key,
    Rname	varchar2(10) not null,
    Rsex 	varchar2(2),
    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');

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

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);

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)
);

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');

6、创建表格Reader2,比较Reader2和Reader中的记录和结构是否相同

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

分别执行:

desc reader; 

结果:
在这里插入图片描述

desc reader2;

结果:
在这里插入图片描述
通过操作可知:两个表的内容一样,但结构中reader2中属性RNO没有非空约束。Reader中RNO是主码,非空。
也可以在可视化界面查看这两个表所附带的约束,如下:(也很容易就看出区别了!)
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
7、分别执行下面的每行语句,查看语句是否执行成功,分析为什么?

update reader set RNO='R001' where Rname='张三';
答:执行失败:因为违反了唯一约束条件,一个更新试图插入重复的建。
Reader表中RNO是主码,主码能唯一标识一个元组,由于表中有两个“张三“,
如果不约束而修改成功的话,那么RNO就不再能唯一标识一个元组,
违反了主码的性质。

update reader2 set RNO='R001' where Rname='张三';
答:执行成功:结果是两个张三的RNO都变成:R001,reader2中RNO不是主码,
因此,两个员工的RNO可以相同,所以可以成功执行。

insert into reader2(RNO,Rname,Rsex,Rage,Rboss, Raddress) 
values(null,'lisi',null,null,null,null);
答:执行成功:
由上面查看表的结构约束条件可知RNO的属性没有了非空的约束,
或者说RNO不是主码了,reader2表中仅有Rname属性有非空约束,
因此此语句可以执行,成功插入数据。

8、删除Reader2表格;

drop table reader2 cascade constraints;  --删除reader2表格

以下为查询READER表中创建的约束类型,其中constraint_type为约束类型,该属性值的取值Type Code如下表所示。(可以根据上课所讲的实体完整性、参照完整性、用户自定义完整性理解以下约束类型;直接在SQL DEVELOPER中双击表格也可以查看在表上所建立的约束)

select table_name,constraint_name,constraint_type 
from user_constraints where table_name ='READER';

我的执行结果:
在这里插入图片描述
在这里插入图片描述
1、执行

insert into RB(RNO,BNO) values('R010','B005');

自动生成日期啦,而不是空值!
2、写出删除Reader表格中编号为’R010’的员工,如果执行错误,分析错误原因。
执行错误,原因:刚刚在RB表中添加了·RNO为“R010”的员工的借书记录, RB表中记录了员工的借阅书籍记录,RB表中的RNO属性参照的是READER表中的RNO列,如果删去READER表中编号为“R010”的员工,则会使RB表不满足其表级完整性约束条件。

3、想办法删除Reader中的’R010’员工;
方法一

/*先把RB中所有’R010’的借书记录都删掉*/
--执行以下语句:
delete from rb where Rno=’R010’;
delete from reader where Rno=’R010’;

方法二
修改数据表READER的结构,允许级联删除(注:ORACLE不支持级联更新)。
依次执行以下黄底语句:

alter table rb add constraint MYFK 
foreign key(rno) references reader(rno) on delete cascade;
--系统提示:SQL 错误: ORA-02275: 此表中已经存在这样的引用约束条件。

查看rb约束条件:
在这里插入图片描述

--此处的sys_c0011079因人而异,具体看自己的!
alter table rb drop constraint sys_c0011079;
alter table rb add constraint MYFK 
foreign key(rno) references reader(rno) on delete cascade;

在这里插入图片描述

--再执行:
delete from reader where rno='R010';
--结果:
顺利删去一行,reader表成功删除编号为“R010“的员工记录。
查看RB表,发现前面插入的('R010','B005')也不在了。
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属性,使其值得范围在10100之间;
**alter table book add constraint bp 
check (Bprice BETWEEN 10 and 100);**

8、修改Reader表的Rage属性,使其值得范围为1660之间。
**alter table reader add constraint bk 
check(Rage between 16 and 60);**

9、试试是否可以删除Reader表,使用CASCADE是否可以删除?
**drop table reader CASCADE;
答:不可以,执行:drop table reader CASCADE** constraints;
才能成功删除!

10、删除Reader、Book和RB表。
执行以下语句删除基本表:(reader表在上一问已删除。)
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)
) ;

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');

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

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);

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)
);

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、 为Reader表格的Rname建立UNIQUE索引

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

如何修改表格数据,再建索引?
把编号为“R001”或者“R002”员工名字改为其他不同名的就可以了,改动一个就好。
或者两个都改成跟其他员工名字不同的,
且修改的两个员工名字改完后名字也不相同。

先执行:update reader set rname='王诠胜' where Rno='R001';
再执行:create unique index myindex on reader(rname);
myindex索引即创建成功!

3、	删除索引     
执行语句: drop index myindex;   即可删除索引。

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

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

确认该视图的存在:

select * from v416

结果:
在这里插入图片描述
2、 从V416中查询年龄大于30的员工信息

执行语句:select * from v416 where rage>30;

结果:
在这里插入图片描述
3、 向视图V416中插入一条新的员工记录,然后从V416中查找该条记录,测试是否可以找到;

insert into v416( rno,rname,rage) values('R999','new reader',99);
select * from reader
select * from v416
问:插入成功了吗?为什么通过视图插入的纪录在视图中看不见?
答:插入成功了,但是,在reader表中可以看到新插入的数据,在v416中却看不到
新插入的数据。
原因:刚插入的数据条件不满足视图v416的条件!

原因截图:
在这里插入图片描述
4、 建立在417办公室工作的视图V417,视图包括员工的编号、姓名、性别、年龄等信息,视图定义带with check option选项;

创建视图:CREATE VIEW V417 as select rno,rname,rsex,rage,raddress 
from reader where raddress='417' with check option;
查询:select * from v417;

视图V417创建成功:
在这里插入图片描述
5、 向视图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');
(此处注意:插入数据时也要插入raddress!) 

执行成功。
查询:select * from v417;

查询结果如下:
在这里插入图片描述
6、 通过视图v416删除员工"王小倩"的记录,在基本表Reader中查看是否已经删除成功?
(员工王小倩并无借书记录,所以通过视图删除该员工记录后,基本表Reader中该员工记录也已删除!此处跟原本实验题目有异,因为我刚开始看错题了,在这就将错就错,把题目改了。)

select * from v416;
delete from v416 where rname='王小倩';
select * from v416;
执行delete from v416 where rname=’王小倩’; 删除成功!

结果:(视图v416和基本表reader中均已无:王小倩,已删除成功!)
在这里插入图片描述
在这里插入图片描述
7、 在视图V417上建立所有女员工信息的视图FV417,查询视图结果

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

结果:
在这里插入图片描述
8、 删除视图V417中没有借阅图书的员工信息

select * from V417;
delete from v417 where v417.rno not in (select rno from rb);
select * from V417;

结果:
在这里插入图片描述
9、 建立视图GV,数据包括每本图书的编号及其借阅数量。思考是否可以向GV中插入数据,为什么?

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;

结果:
在这里插入图片描述
思考回答:不能向GV中插入数据!因为视图GV的内容并非数据,而是数据和相关数据统计结果。

10、 删除视图V417

drop view v417;
select * from v417;  提示:表或视图不存在(因为上一步删除成功了!)

结果:
在这里插入图片描述

实验一:SQL语言的实验报告分享就到此为止啦,分享的同时加深了自己的理解和
记忆,希望也能帮到大家的学习,如有错漏欢迎大家来纠正与补充,谢谢!
欢迎大家来交流,一起进步呀😊😊

欲浏览更多?欢迎到访---->CSDN个人主页,多多支持咯!

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 数字20 设计师:CSDN官方博客 返回首页