数据库实验报告.docx
《数据库实验报告.docx》由会员分享,可在线阅读,更多相关《数据库实验报告.docx(14页珍藏版)》请在冰点文库上搜索。
数据库实验报告
数据库原理与技术
DATABASE
Principles,TechnologyandApplication
实验报告
目录
实验一、熟悉ORALCE环境并练习SQL的定义2-
一、实验内容2-
二、思考题3-
三、遇到的问题及解决方法4-
实验二、记录查询4-
一、实验内容5-
二、思考题6-
三、遇到的问题及解决方法6-
实验三、SQL的数据操纵6-
一、实验内容6-
二、思考题7-
三、遇到的问题及解决方法8-
实验四、视图及权限控制8-
一、实验内容8-
二、思考题9-
三、遇到的问题及解决方法10-
实验五、数据库触发器与过程11-
一、实验内容11-
二、思考题12-
三、遇到的问题及解决方法13-
实验一、熟悉ORALCE环境并练习SQL的定义
一、实验内容
建表
createuserU_42918identifiedbynxy431;
grantresource,connecttoU_42918;
connectU_42918/nxy431;
DROPTABLET_DEPT_42918;
CREATETABLET_DEPT_42918(DNOCHAR(3)PRIMARYKEY,DNVARCHAR2(32),DEANCHAR(8),TELCHAR(8)CHECK(SUBSTR(TEL,1,4)='8887'));
DROPTABLET_STUD_42918;/*学生*/
CREATETABLET_STUD_42918(SNOCHAR(10)PRIMARYKEY,SNAMEvarCHAR2(8),BIRTHDAYDATE,NATIVE_PLACEVARCHAR2(20),DNOCHAR(3)REFERENCEST_DEPT_42918(DNO));
DELETEFROMT_STUD_42918;
DELETEFROMT_DEPT_42918;
INSERTINTOT_DEPT_42918VALUES('D01','计算机系','王大明','88879626');
INSERTINTOT_DEPT_42918VALUES('D02','物理系','张大奔','88879726');
INSERTINTOT_DEPT_42918VALUES('D03','化学系','李水明','88879826');
INSERTINTOT_DEPT_42918VALUES('D04','管理系','欧阳卫红','88879926');
INSERTINTOT_DEPT_42918VALUES('D05','外语系','宽左右','88879526');
INSERTINTOT_STUD_42918VALUES('0909100524','胡明天',SYSDATE-365*21,'湖南长沙','D01');
INSERTINTOT_STUD_42918VALUES('0909102918','',SYSDATE-365*20,'吉林长春','D01');
INSERTINTOT_STUD_42918VALUES('0909100134','唐明海',SYSDATE-365*21,'湖南长沙','D01');
INSERTINTOT_STUD_42918VALUES('0909100224','张丽梅',SYSDATE-365*20,'湖南长沙','D01');
INSERTINTOT_STUD_42918VALUES('0909100324','李有能',SYSDATE-365*22,'湖南长沙','D02');
INSERTINTOT_STUD_42918VALUES('0909100424','王汉中',SYSDATE-365*22,'湖南长沙','D02');
INSERTINTOT_STUD_42918VALUES('0909100464','高强',SYSDATE-365*21,'','D02');
INSERTINTOT_STUD_42918VALUES('0909100474','JONE',SYSDATE-365*23,'','D03');
INSERTINTOT_STUD_42918VALUES('0909100484','SMITH',SYSDATE-365*21,'','D03');
INSERTINTOT_STUD_42918VALUES('0909100594','TOMHONE',SYSDATE-365*22,'','D03');
INSERTINTOT_STUD_42918VALUES('0909100264','张三',SYSDATE-365*22,'','D04');
INSERTINTOT_STUD_42918VALUES('0909100274','李四',SYSDATE-365*21,'','D04');
INSERTINTOT_STUD_42918VALUES('0909100284','王老五',SYSDATE-365*20,'','D04');
二、思考题
1.改变表的名称及数据类型再进行相应的操作。
ALTERTABLET_DEPT_42918RENAMETOT_DEPT_42918A;
RENAMET_DEPT_42918ATOT_DEPT_42918;
ALTERTABLET_DEPT_42918MODIFYDNOINT;
2.先删除主表T_DEPT_?
?
?
?
?
,结果怎么样?
DROPTABLET_DEPT_42918
*
第1行出现错误:
ORA-02449:
表中的唯一/主键被外键引用
3.修改表中的字段类型与长度,考虑表中有数据或者没数据情况。
SQL>ALTERTABLET_DEPT_42918MODIFYDNOCHAR
(2);
ALTERTABLET_DEPT_42918MODIFYDNOCHAR
(2)
*
第1行出现错误:
ORA-01441:
无法减小列长度,因为一些值过大
SQL>ALTERTABLET_DEPT_42918MODIFYDNOCHAR(3);
4.插入相同的学号与相同的系号会出现什么结果
INSERTINTOT_DEPT_42918VALUES('D01','计算机系','王明','88879626')
*
第1行出现错误:
ORA-00001:
违反唯一约束条件(U_42918.SYS_C005421)
主键值不可相同。
5.插入空的学号或空的系号会出现的结果
INSERTINTOT_STUD_42918VALUES('','胡明天',sysdate-365*21,'湖南长沙','D01')
*
第1行出现错误:
ORA-01400:
无法将NULL插入("U_42918"."T_STUD_42918"."SNO")
主键值不允许为空值。
3、遇到的问题及解决方法
本次实验出现的主要问题为,在建立T_STUD_42918表时,缺少了一个地点的定义,需加NATIVE_PLACEVARCHAR2(20)型定义。
建表时还需注意双引号的类型和位置,以免出错。
实验二、记录查询
一、实验内容
1.查询所有学生记录
select*fromT_STUD_42918;
2.查询所有系的记录,并按系号升序排列
selectDNfromT_DEPT_42918ORDERBYDNOASC;
3.查询有自己姓名的记录
Select*fromT_STUD_42918whereSNAME=’’;
4.查询’李四’的SNO,SNAME,BIRTHDAY
selectSNO,SNAME,BIRTHDAYfromT_STUD_42918whereSNAME=’李四’;
5.查询计算系所有的学生记录
SELECT*FROMT_STUD_42918WHEREDNOIN(SELECTDNOFROMT_DEPT_42918WHEREDN='计算机系');
6.查询各计算系学生在各年龄值的人数,如19的为200,20的有500等
altertableT_STUD_42918ADDAGENUMBER
(2);
UPDATET_STUD_42918SETAGE=(SYSDATE-BIRTHDAY)/365;
SELECTAGE,COUNT(T_STUD_42918.SNO)FROMT_STUD_42918WHERET_STUD_42918.DNO='D01'GROUPBYT_STUD_42918.AGE;
7.查询’李四’的SNO,BIRTHDAY,DNO,DN,DEAN
SELECTT_STUD_42918.SNO,T_STUD_42918.BIRTHDAY,T_DEPT_42918.DNO,T_DEPT_42918.DN,T_DEPT_42918.DEANFROMT_DEPT_42918,T_STUD_42918
WHERET_DEPT_42918.DNO=T_STUD_42918.DNOANDT_STUD_42918.SNAME='李四';
二、思考题
1.查询学生的记录数。
selectcount(SNAME)FROMT_STUD_42918;
2.在DEPT_?
?
?
?
?
中输入不是’8887’开头的电话记录。
INSERTINTOT_DEPT_42918VALUES('D07','外语系','王上下','88879526');
INSERTINTOT_DEPT_42918VALUES('D08','外语系','王左右','82659526');
.
3.查询平均年龄小于22岁的系号及学生SNO,SNAME,AGE
SELECTDNO,SNO,SNAME,AGEFROMT_STUD_42918
WHEREDNOIN(SELECTDNOFROMT_STUD_42918GROUPBYDNOHAVINGAVG(AGE)<22);
4.找出平均年龄大于20的系名及平均年龄数
SELECTDN,FLOOR(AVG(AGE))FROMT_DEPT_42918,T_STUD_42918WHERET_DEPT_42918.DNOIN(SELECTDNOFROMT_STUD_42918GROUPBYDNO)GROUPBYT_DEPT_42918.DNO,T_DEPT_42918.DNHAVINGFLOOR(AVG(AGE))>20;
3、遇到的问题及解决方法
本次实验出现的主要问题为在查询带有名字的记录时,应将两个汉字的名字中间留两空格,否则无法查询;删除表中新加的列属性可用此语句ALTERTABLET_STUD_42918DROPCOLUMNAGE;
实验三、SQL的数据操纵
一、实验内容
1.在T_STUD中增加AGENUMBER
(2)/*年龄*/字段,并求出每个学生入校时的年龄。
ALTERTABLESTUD_42918ADDAGENUMBER
(2);
UPDATET_STUD_42918SETAGE=2010-to_number(to_char(birthday,'yyyy'));
selectSNAME,AGEFROMT_STUD_42918;
2.将T_STUD_?
?
?
?
?
表中的08级学生的年龄都加3
INSERTINTOT_STUD_42918VALUES('0909080523','胡天',SYSDATE-365*21,'湖南长沙','D01',21);
INSERTINTOT_STUD_42918VALUES('0909080623','胡月',SYSDATE-365*22,'湖南长沙','D01',22);
UPDATET_STUD_42918SETAGE=AGE+3WHERESNOlike'090908____';
3.将T_DEPT_?
?
?
?
?
表中的’计算机系’改为’信息科学与工程学院’
UPDATET_DEPT_42918SETDN='信息科学与工程学院'WHEREDN='计算机系';
4.删除07级以前的所有的学生记录
INSERTINTOT_STUD_42918VALUES('0909051525','王老吉',SYSDATE-365*25,'湖南长沙','D01',26);
INSERTINTOT_STUD_42918VALUES('0909061525','李逍遥',SYSDATE-365*27,'湖南长沙','D01',24);
DELETEFROMT_STUD_42918WHERESNO<0909070000;
二、思考题
1.误删了T_STUD_?
?
?
?
?
中的记录,怎么办?
Deletefromt_stud_42918Wheresname='张三';
Rollback;
2.怎么样才能做到在一个语句中将学生的年龄加上相应的学习年数(如08级加3,09级加2,10级加1,07级加4等)。
UPDATET_STUD_42918SETAGE=AGE+11-TO_NUMBER(SUBSTR(SNO,5,2));
3.建立与T_STUD_?
?
?
?
?
对应的表T_ST_?
?
?
?
?
(SNO,SNAME,AGE/*年龄*/),在T_ST_?
?
?
?
?
输入若干学号(在T_STUD_?
?
?
中有的有几个),然后将T_STUD_?
?
?
?
?
中的SNAME,BIRTHDAY转入到T_ST_?
?
?
?
?
相应的SNO,AGE(多表间的更新)。
CREATETABLET_ST_42918(SNOCHAR(10),SNAMECHAR(8),AGENUMBER
(2));
INSERTINTOT_ST_42918(SNO)VALUES('0909100524');
INSERTINTOT_ST_42918(SNO)VALUES('0909100134');
INSERTINTOT_ST_42918(SNO)VALUES('0909100224');
INSERTINTOT_ST_42918(SNO)VALUES('0909100324');
updateT_ST_42918setSNAME=(selectSNAMEfromT_STUD_42918whereT_ST_42918.SNO=T_STUD_42918.SNO),
AGE=(selectfloor((sysdate-BIRTHDAY)/365)fromT_STUD_42918whereT_ST_42918.SNO=T_STUD_42918.SNO);
3、遇到的问题及解决方法
本次实验主要学会了substr()的用法,用substr()限制电话号码的前四位,选取学号中的某段。
实验四、视图及权限控制
一、实验内容
1.视图的建立
connectsystem;
grantcreateviewtoU_42918;
connectU_42918/nxy431;
/*视图建立*/
CREATEVIEWV_S_D_42918ASSELECTSNO,SNAME,T_STUD_42918.DNO,DN,TELFROMT_STUD_42918,T_DEPT_42918
WHERET_STUD_42918.DNO=T_DEPT_42918.DNOANDTO_CHAR(BIRTHDAY,'YYYY')>='1991';
2.对视图进行查询
Select*fromV_S_D_42918;
3.分别将对象的各种权限授予相应的用户假若已有用户SCOTT(视系统实际情况),在SCOTT中看操作权限的变化。
GRANTSELECTONSTUD_42918TOSCOTT;GRANTSELECTONT_STUD_42918TOSCOTT;/*GRANTINSERTONT_STUD_42918TOSCOTT;*//*GRANTUPDATE(BIRTHDAY)ONT_STUD_42918TOSCOTT;*//*GRANTDELETEONT_STUD_42918TOSCOTT;*//*GRANTSELECTONV_S_D_42918TOSCOTT;*/
connectscott/tiger;
select*fromU_42918.T_STUD_42918;
insertintoU_42918.T_STUD_42918values('0909102931','',SYSDATE-365*23,'江苏南京','D01',23);
insertintoU_42918.T_STUD_42918values('0909102931','李明',SYSDATE-365*23,'江苏南京','D01');
updateU_42918.T_STUD_42918setBIRTHDAY=BIRTHDAY+365;
deletefromU_42918.T_STUD_42918whereSNAME='张三';
二、思考题
1.在视图V_S_D_?
?
?
?
?
中只考虑信息09级,视图将怎样建立?
connectU_42918/nxy431;
dropviewV_S_D_42918;
CREATEVIEWV_S_D_42918ASSELECTSNO,SNAME,T_STUD_42918.DNO,DN,TEL
FROMT_STUD_42918,T_DEPT_42918
WHERET_STUD_42918.DNO=T_DEPT_42918.DNOANDT_STUD_42918.DNO='D01'ANDSUBSTR(SNO,5,2)='09';
SELECT*FROMV_S_D_42918;
2.如果用户为DBA,还需要给授权吗?
答:
不能
3.考虑基于一个表、简单条件的视图的更新操作。
updateV_S_D_42918setDNO='D02'whereSNAME='胡明天';
4.考虑基于多表视图的更新操作。
DROPVIEWVIEW_42918;
SELECT*FROMVIEW_42918;
CREATEVIEWVIEW_42918ASSELECTT_STUD_42918.DNO,SNO,SNAME,BIRTHDAY,DN,TELFROMT_STUD_42918,T_DEPT_42918WHERET_STUD_42918.DNO=T_DEPT_42918.DNO;
UPDATEVIEW_42918SETDNO='D02'WHERESNAME='胡明天';
5.设计与实现
针对用户的不同,建立视图,只出现用户本身的记录
提示:
有用户创建、表创建、视图建立、权限控制、用户联接、查询等操作
SELECT'CREATEUSERU_'||SUBSTR(SNO,7)||'IDENTIFIEDBYP_'||SUBSTR(SNO,7)||';'FROMT_STUD_42918;
SELECT'GRANTCONNECTTOU_'||SUBSTR(SNO,7)||';'FROMT_STUD_42918;
SELECT'DROPUSERU_'||SUBSTR(SNO,7)||'CASCADE;'FROMT_STUD_42918;
CONNECTSYSTEM/MANAGER;
SELECTUSERFROMDUAL;
connectU_42918/nxy431;
CREATEVIEWSTUD_PUBLICASSELECT*FROMV_S_D_42918WHERESUBSTR(SNO,7)=SUBSTR(USER,3);
GRANTSELECTONSTUD_PUBLICTOPUBLIC;
SELECT*FROMSTUD_PUBLIC;
3、遇到的问题及解决方法
注意建立视图的方法和用户权限控制代码的编写。
实验五、数据库触发器与过程
一、实验内容
1.先建立表T_ST_?
?
?
?
?
(SNO,SNAME,AGE)
DROPTABLET_ST_42918;
CREATETABLET_ST_42918(SNOCHAR(10),SNAMECHAR(8),AGENUMBER
(2));
2.建立触发器createorreplacetriggertg_STUD_TRG_42918
beforeinsertorupdateordeleteont_stud_42918
foreachrow
begin
/*插入*/
ifinsertingthen
insertintot_st_42918
values(:
new.sno,:
new.sname,(sysdate-:
new.birthday)/365);
endif;
/*修改*/
ifupdatingthen
deletefromt_st_42918wheresno=:
old.sno;
insertintot_st_42918values(:
new.sno,:
new.sname,(sysdate-:
new.birthday)/365);
endif;
/*删除*/
ifdeletingthen
deletefromt_st_42918wheresno=:
old.sno;
endif;
end;
/
3.验证触发器的作用,在T_STUD_?
?
?
?
?
中输入、修改、删除数据时,查看T_ST_?
?
?
?
?
表中的变化。
DELETEFROMT_STUD_42918WHERESNO='0909102918';
UPDATET_STUD_42918SETAGE=20WHERESNO='0909102918';
DELETEFROMT_STUD_42918WHERESNO='0909102918';
insertintot_stud_42918values('0909102302','张天一',sysdate-365*20,'湖南长沙','D01',20);
select*fromt_st_42918;
4.设计并调试过程
分系dno统计T_STUD_?
?
?
?
?
表中各年龄的人数
SELECTDNO,AGE,COUNT(SNO)FROMT_STUD_42918GROU