衡阳商城网站建设,跨境进口网站怎么做,英文网站如何做,桂林两江四湖景区实验二
SQL语言
实验目的
熟悉并掌握创建表#xff0c;插入记录#xff0c;查询记录#xff0c;删除记录#xff0c;修改记录。
创建索引#xff0c;删除索引。
创建视图#xff0c;使用视图#xff0c;删除视图。 实验内容
现有一个单位内部的小型图书借阅系统插入记录查询记录删除记录修改记录。
创建索引删除索引。
创建视图使用视图删除视图。 实验内容
现有一个单位内部的小型图书借阅系统假设每本图书的数量无限制并且可以借给任何单位成员每个单位成员可以借多本书单位成员与图书的关系是多对多的关系。假设系统中仅有三个关系模式。
数据表结构
Reader表 属性名 类型 长度 是否空 含义 RNO varchar2 4 员工编号主码 Rname varchar2 10 否 员工姓名 Rsex varchar2 2 性别 Rage integer integer 年龄 Rboss varchar2 10 直接上司 Raddress varchar2 30 办公地点
Book表 属性名 类型 长度 是否空 含义 BNO varchar2 4 书本编号主码 Bname varchar2 50 否 书名 Bauthor varchar2 50 作者 Bpress varchar2 50 出版社 Bprice varchar2 numeric(6,2) 价格
RB表 属性名 类型 长度 是否空 含义 RNO varchar2 4 员工编号 BNO varchar2 4 书本编号 RBdate date 借阅日期
部分提示黄色底的代码可以直接粘贴运行
创建新的用户并授权
create user cc identified by ccpassword ;
grant resource, connect, DBA to cc;
以用户CC的身份建立连接并在此连接下执行后面的操作拷贝代码运行删去旧的同名数据表
Declare
tmp integer default 0;
Begin
select count(*) into tmp from user_tables where table_nameRB;
if(tmp0) then
execute immediate drop table RB;
end if;
select count(*) into tmp from user_tables where table_nameREADER;
if(tmp0) then
execute immediate drop table READER;
end if;
select count(*) into tmp from user_tables where table_nameBOOK;
if(tmp0) then
execute immediate drop table BOOK;
end if;
end; 问为何要先删去RB能不能先删去READER
建立表格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)
) ;
拷贝代码运行向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);
查询记录在Reader表中查询直接上司是“李四”的员工的名字
select Rname from Reader where Rboss李四;
修改记录在Reader表中把直接上司是“李四”的员工的办公地点统一改为“420”
UPDATE reader SET Raddress420 WHERE rboss李四;
删除记录在Reader表中把直接上司未赋值NULL是记录删去
DELETE FROM reader WHERE rbossNULL;
删去数据表把整个Reader表删去
DROP TABLE reader;
重新执行第一、二步即建立数据表、插入数据。创建表格Reader2比较Reader2和Reader中的记录和结构是否相同
create table reader2 as select * from reader; --创建表格reader2
查看这两个数据表的结构、内容是否相同分别执行
desc reader; desc reader2; 也可以在可视化界面查看这两个表所附带的约束 分别执行下面的每行语句查看语句是否执行成功分析为什么
update reader set RNOR001 where Rname张三; 表reader中的RNO是primary key,更新后的RNO与现存的RNO重复违反唯一约束条件 update reader2 set RNOR001 where Rname张三; insert into reader2(RNO,Rname,Rsex,Rage,Rboss, Raddress) values(null,lisi,null,null,null,null); READER2 删除Reader2表格
DROP TABLE READER2;
拷贝代码运行建立表格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)
); 拷贝代码运行向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);
拷贝代码运行建立表格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)
);
拷贝代码运行向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 以下为查询READER表中创建的约束类型其中constraint_type为约束类型该属性值的取值Type Code如下表所示。可以根据上课所讲的实体完整性、参照完整性、用户自定义完整性理解以下约束类型直接在SQL DEVELOPER中双击表格也可以查看在表上所建立的约束
select table_name,constraint_name,constraint_type from user_constraints where table_name READER; Type Code Type Description Acts On Level C Check on a table Column O Read Only on a view Object P Primary Key Object R Referential AKA Foreign Key Column U Unique Key Column V Check Option on a view Object 执行insert into RB(RNO,BNO) values(R010,B005);写出删除Reader表格中编号为’R010’的员工如果执行错误分析错误原因。想办法删除Reader中的’R010’员工
方法一先把RB中所有’R010’的借书记录都删掉
方法二修改数据表READER的结构允许级联删除注ORACLE不支持级联更新。
alter table rb add constraint MYFK foreign key(rno) references reader(rno) on delete cascade; 系统提示SQL 错误: ORA-02275: 此表中已经存在这样的引用约束条件 可见首先要先删去原来的约束因建表时没给该约束命名故应查这个约束的名字才能删去。注意这个名字由系统自动赋值不同的环境下约束名可能不同 alter table rb drop constraint SYS_C0011303; alter table rb add constraint MYFK foreign key(rno) references reader(rno) on delete cascade; 再执行delete from reader where rnoR010; 顺利删去一行打开RB表发现前面插入的(R010,B005)也不在了。 为Reader表添加一个属性列“出生年份”名为Rbirthday整数
alter table reader ADD Rbirthday integer ; 对于Reader表格员工编号可以确定年龄年龄又可以确定出生年份因此存在传递函数依赖关系删除Rbirthday列使关系模式符合第三范式要求alter table reader DROP column Rbirthday; 修改Reader表格的Raddress属性使其长度为50数据类型不变
alter table reader modify Raddress varchar2(50);
修改book表的Bprice属性使其值得范围在10到100之间
alter table book add constraint bp check (Bprice BETWEEN 10 and 100);
修改Reader表的Rage属性使其值得范围为16到60之间。
alter table Reader add constraint rr check (Rage BETWEEN 16 and 60);
试试是否可以删除Reader表使用CASCADE是否可以删除
drop table reader CASCADE;
drop table reader CASCADE CONSTRAINTS; 删除Reader、Book和RB表。
drop table rb CASCADE CONSTRAINTS; drop table book CASCADE CONSTRAINTS; 索引的建立与删除
重新执行前面的代码创建三个数据表并插入数据为Reader表格的Rname建立UNIQUE索引
create unique index myindex on reader(rname); 报告出错cannot CREATE UNIQUE INDEX; duplicate keys found
如何修改表格数据再建索引 解决方法很简单要么把数据库的数据清理掉要么把你要创建唯一索引的那一列重复的数据删掉。我选择的是创建唯一索引的那一列重复的数据删掉一个。
删除索引。
drop index myindex; 视图
如果上面的运行是在CC的连接中则需要回到在sysdba 的连接中执行
grant resource, connect, DBA to cc;
否则系统显示没有创建视图的权限。执行完之后再回到CC的连接
在ORACLE SQL Developer 的左上方
建立在416办公室工作的视图V416视图包括员工的编号、姓名、年龄等信息
CREATE VIEW V416 as select rno,rname,rage from reader where raddress416 确认该视图的存在
select * from v416 从V416中查询年龄大于30的员工信息
SELECT rno,rname from v416 WHERE rage30; 向视图V416中插入一条新的员工记录然后从V416中查找该条记录测试是否可以找到
insert into v416( rno,rname,rage) values(R999,new reader,99);
select * from reader select * from v416 插入成功了吗为什么通过视图插入的纪录在视图中看不见
刚插入数据条件不满足此视图的条件了 建立在417办公室工作的视图V417视图包括员工的编号、姓名、性别、年龄等信息视图定义带with check option选项
CREATE VIEW V417 as select rno,rname,rsex,rage,raddress from reader where raddress417 with check option; select * from v417 向视图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 raddress417 with check option;
insert into v417( rno,rname,rage,raddress) values(R777,417 reader,66,417);
执行成功。 通过视图删除刚插入的员工记录在基本表Reader中查看是否已经删除成功
select * from v416; delete from v416 where rname王小倩; select * from v416; 在视图V417上建立所有女员工信息的视图FV417查询视图结果
CREATE VIEW FV417 as select * from V417 where RSEX女; select * from FV417; 删除视图V417中没有借阅图书的员工信息
SELECT * FROM V417; Delete from v417 where v417.rno not in (SELECT rno from rb); SELECT * FROM V417; 建立视图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插入数据因为GV的内容并非数据而是数据和相关数据统计结果。
删除视图V417
drop view v417 触发器可不做时间多再做
参考PLSQL帮助文档试试建立一个触发器。在用户执行删除某个员工的时候触发该触发器的功能是先删除RB中该员工的借书记录然后删除Reader中该员工记录。
实验报告要求
写出实验步骤及相关的SQL语句即可。
