数据库实践报告

中南大学数据库实验报告课题：数据库原理及应用姓名：林一新班级：信息0803学号：0909082112指导老师：张祖平实验时间：2011.04实验目的与要求熟悉ORACLE的运行环境，在CLIENT端进行联接设置与服务器联通，进入ORACLE的SQL*PLUS的操作环境，进一步建立表格，并考虑主键,外部键,值约束。熟悉在SQL*PLUS环境下进行数据记录查询，其中查询考虑简单查询,复杂查询,嵌套查询。熟悉在SQL*PLUS环境下进行数据记录的修改、删除及表结构的修改。熟悉在SQL*PLUS环境下进行数据视图及权限控制的操作。熟悉在SQL*PLUS环境下数据库触发器的建立及作用，了解大型数据库编程。实验环境硬件：586以上服务器、586以上微机，内存512M以上。软件：WINDOWS2000/2003SERVER可安装ORACLE/9i/10g/11g/13iFORNT/WINDOWS(注意在32位与64位的区别)如果windows非server如XP等,安装时请选择个人版(PERSONAL)实验内容及步骤熟悉ORALCE环境并练习SQL的定义打开控制面板，――>管理工具――>服务――>启动OracleServiceslab或者如图通过界面或资源管理器右键找到管理。再找服务：启动的服务如下或有OracleService****的服务。从桌面执行SQL*PLUS或在程序中输入：system/manager在SQL>状态输入建立用户命令:createuserusernameidentifiedbypassword;其中username与password不要数字开头，用户名username中按规则加班级学号后缀如：U_301表示3班学号尾数为1号的用户。给用户授权：grantresource,connecttousername;连接用户：connectusername/password在SQL>就可以建表等操作了根据要求建立如下表，插入数据DROPTABLEDEPT_???;/*系，???为班级学号如dept_512,5班12号代码：createuserU_2112identifiedbylyx330;grantresource,connecttoU_2112;connectU_2112/lyx330;DROPTABLEDEPT_2112;CREATETABLEDEPT_2112(DNOCHAR(3)PRIMARYKEY,DNVARCHAR2(32),DEANCHAR(8),TELCHAR(8)CHECK(SUBSTR(1,3)='8887'));DROPTABLESTUD_2112;CREATETABLESTUD_2112(SNOCHAR(10)PRIMARYKEY,SNAMECHAR(8),BIRTHDAYDATE,NATIVE_PLACEVARCHAR2(20),DNOCHAR(3)REFERENCESDEPT_2112(DNO));DELETEFROMSTUD_2112;

DELETEFROMDEPT_2112;

INSERTINTODEPT_2112VALUES('D01','计算机系','王大明','88879626');

INSERTINTODEPT_2112VALUES('D02','物理系','张大奔','88879726');INSERTINTODEPT_2112VALUES('D03','化学系','李水明','88879826');INSERTINTODEPT_2112VALUES('D04','管理系','欧阳卫红','88879926');INSERTINTODEPT_2112VALUES('D05','外语系','宽左右','88879526');INSERTINTOSTUD_2112VALUES('0909080524','胡明天',SYSDATE-365*21,'湖南长沙','D01');INSERTINTOSTUD_2112VALUES('0909080134','唐明海',SYSDATE-365*21,'湖南长沙','D01');INSERTINTOSTUD_2112VALUES('0909080224','张丽梅',SYSDATE-365*20,'湖南长沙','D01');INSERTINTOSTUD_2112VALUES('0909080324','李有能',SYSDATE-365*22,'湖南长沙','D02');INSERTINTOSTUD_2112VALUES('0909080424','王汉中',SYSDATE-365*22,'湖南长沙','D02');INSERTINTOSTUD_2112VALUES('0909080464','高强',SYSDATE-365*21,'','D02');INSERTINTOSTUD_2112VALUES('0909080474','JONE',SYSDATE-365*23,'','D03');INSERTINTOSTUD_2112VALUES('0909080484','SMITH',SYSDATE-365*21,'','D03');INSERTINTOSTUD_2112VALUES('0909080594','TOMHONE',SYSDATE-365*22,'','D03');INSERTINTOSTUD_2112VALUES('0909080264','张三',SYSDATE-365*22,'','D04');INSERTINTOSTUD_2112VALUES('0909080274','李四',SYSDATE-365*21,'','D04');INSERTINTOSTUD_2112VALUES('0909080284','王老五',SYSDATE-365*20,'','D04');思考题：改变表的名称及数据类型再进行相应的操作。答：改变表名的操作为：ALTERTABLE当前表名RENAMETO新表名;可以通过以下属性改变属性的数据类型：ALTERTABLE表名MODIFY(列名数据类型)。先删除主表DEPT_???，结果怎么样？答：因为DEPT_???中属DNO作为STUD_???中的外键被参照，为了约束完整性不应该被首先删除。但是可以用cascade关键字实现级联删除。修改表中的字段类型与长度，考虑表中有数据或者没数据情况。答：修改表中字段类型与长度：ALTERTABLEDEPT_???MODIFY(DNOCHAR(2))。表中主键值不允许为null；属性定义中声明为notnull也不允许空值。插入相同的学号与相同的系号会出现什么结果答：不允许。主键值不可相同。插入空的学号或空的系号会出现的结果答：不允许。主键值不允许为空值。记录查询1．查询所有学生记录select*fromSTUD_2112;2．查询所有系的记录，并按系号升序排列selectDN,DNOfromDEPT_2112ORDERbyDNODESC;3.查询有自己姓名的记录Select*fromSTUD_2112whereEXISTS(SNAME);4.查询’张三’的SNO，SNAME，BIRTHDAYselectSNO,SNAME,BIRTHDAYfromSTUD_2112whereSNAME=’张三’;5.查询计算系所有的学生记录select*fromSTUD_2112whereDNO=’D01’6.查询各计算系学生在各年龄值的人数，如19的为200，20的有500等selectCOUNT(AGE)fromSTUD_2112groupby(SYSDATE-BIRTHDAY)/365;7.查询’张三’的SNO，BIRTHDAY，DNO，DN，DEANselectSNO，BIRTHDAY,STUD_2112.DNO,DN,DEANfromSTUD_2112,DEPT_2112whereSNAME=’张三’ANDSTUD_2112.DNO=DEPT_2112.DNO;思考题：查询学生的记录数。答：selectCOUNT(*)fromSTUD_2112；查询平均年龄小于22岁的系号及学生SNO，SNAME，AGE答：运用子查询可实现。找出平均年龄大于20的系名及平均年龄数答：运用having（）聚合函数。SQL的数据操纵在STUD中增加AGENUMBER（2）/*年龄*/字段，并求出每个学生入校时的年龄。ALTERTABLESTUD_2112ADDAGENUMBER(2);UPDATESTUD_2112SETAGE=(SYSDATE-BIRTHDAY)/365;UPDATESTUD_2112将STUD_???表中的08级学生的年龄都加3SETAGE=AGE+3WHERESNOlike’090908____’;将DEPT_???表中的’计算机系’改为’信息科学与工程学院’UPDATEDEPT_2112SETDN=’信息科学与工程学院’WHEREDN=’计算机系’;删除07级以前的所有的学生记录DELETEFROMSTUD_2112WHERESNO<0909070000;思考题：误删了STUD_???中的记录，怎么办？答：可以运用事务回滚rollbacktosavepointlabel.此外，为了防止不必要的回滚操作。设置savepoint前应该在适当位置用commit提交事务。怎么样才能做到在一个语句中将学生的年龄加上相应的学习年数（如08级加3，09级加2，10级加1，07级加4等）。答：UPDATESTUD_2112SETAGE=AGE+11-TO_NUMBER(SUBSTR(SNO,5,2));3：建立与STUD_???对应的表ST_???（SNO，SNAME，AGE/*年龄*/），在ST_???输入若干学号（在STUD_？？？中有的有几个），然后将STUD_???中的SNAME，BIRTHDAY转入到ST_???相应的SNO，AGE（多表间的更新）。答：CREATETABLEST_2112(SNOCHAR(10)PRIMARYKEY,SNAMECHAR(8),AGENUMBER(2));INSERTINTOST_2112(SNO)VALUES('0909080424');INSERTINTOST_2112(SNO)VALUES('0909080464');INSERTINTOST_2112(SNO)VALUES('0909080474');INSERTINTOST_2112(SNO)VALUES('0909080484');UPDATEST_2112SETST_2112.AGE=ST_2112.AGE,ST_2112.BIRTHDAY=ST_2112.BIRTHDAYWHEREST_2112.SNO=ST_2112.SNO;视图及权限控制视图的建立connectsystem/manager;grantcreateviewtoU_2112;connectU_2112/lyx330;CREATEVIEWS_D_2112ASSELECTSNO,SNAME,STUD_2112.DNO,DN,TELFROMSTUD_2112,DEPT_2112WHERESTUD_2112.DNO=DEPT_2112.DNOANDTO_CHAR(BIRTHDAY,'YYYY')>='1991';2．对视图进行查询Select*fromS_D_2112;3．分别将对象的各种权限授予相应的用户假若已有用户SCOTT（视系统实际情况），在SCOTT中看操作权限的变化。

GRANTINSERTONSTUD_2112TOSCOTT;GRANTUPDATE(BIRTHDAY)ONSTUD_2112TOSCOTT;GRANTDELETEONSTUD_2112TOSCOTT;GRANTSELECTONS_D_2112TOSCOTT;思考题：在视图S_D_???中只考虑信息08级，视图将怎样建立？答;CREATEVIEWS_D_2114ASSELECTSNO,SNAME,STUD_2112.DNO,DN,TELFROMSTUD_2112,DEPT_2112

WHERESTUD_2112.DNO=DEPT_2112.DNOANDSNOlike’090908____’;如果用户为DBA，还需要给授权吗？答：不需要。考虑基于一个表、简单条件的视图的更新操作。答：类同。4.考虑基于多表视图的更新操作。答：一般不允许。数据库触发器与过程1．先建立表ST_???(SNO,SNAME,AGE)DROPTABLEST_2112;CREATETABLEST_2112(SNOCHAR(10),SNAMECHAR(8),AGENUMBER(2));2．建立触发器

CREATEORREPLACETRIGGERSTUD_TRG_2112

BEFOREINSERTORUPDATEORDELETEONSTUD_2112

FOREACHROW

BEGINIFINSERTINGTHEN

INSERTINTOST_2112VALUES(:NEW.SNO,:NEW.SNAME,(SYSDATE-:NEW.BIRTHDAY)/365);ENDIF;IFUPDATINGTHENDELETEFROMST_2112WHERESNO=:OLD.SNO;INSERTINTOST_2112VALUES(:NEW.SNO,:NEW.SNAME,(SYSDATE-:NEW.BIRTHDAY)/365);ENDIF;IFDELETINGTHENDELETEFROMST_2112WHERESNO=:OLD.SNO;ENDIF;END./3．验证触发器的作用在STUD_2112中输入、修改、删除数据时，查看ST_2112表中的变化。ALTERTABLEST_2112DROPCOLUMNAGEcascadeconstraints;INSERTINTOST_2112VALUES('0909081111','一一一',date'1988-05-02','中国福建INSERTINTOST_2112VALUES('0909082222','二二二',date'1988-04-02INSERTINTOST_2112VALUES('0909083333','一一一',date'1988-04-02','中国福建INSERTINTOST_2112VALUES('0909084444','一一一',date'1988-02-02','中国福建SELECT*FROMST_2112;UPDATEST_2112SETBIRTHDAY=DATE'1988WHERESNAME='二二二';SELECT*FROMST_2112;DELETEFROMST_2112WHERESNAME='一一一';SELECT*FROMST_2112;4．设计并调试过程（1）分系dno统计STUD_2112表中各年龄的人数（2）根据Bank数据库，分街道(street)统计客户的存款余额（balance）,其中街道名为变量。出现的问题和解决方法建立视图时显示权限不足。解决方法：进行授权。connectsystem/manager;grantcreateviewtoU_2112;connectU_2112/lyx330;插入数据时，出现错误。解决方法：根据提示查找错误。检查语法错误。检查拼写错误。检查标点符号，引号是否缺少，标点是否为英文标点。心得体会数据库的上机实验结束了，通过对数据库的学习也初步掌握了其各方面的知识，数据库的的功能是强大的，面对目前的信息化社会，在整理、查询、分析数据方面是一款强有力的工具。学习的目的在于将知识能合理顺利的运用，将书本知识化为己用，将理论知识用在显示当中的问题，是一个不知道到知道，了解完善应用的过程，尤其是计算机方面的知识更是如此，必要的上机练习是必不可少的。本次数据库上机主要用到的是SQL结构化查询语言，它是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。通过这几次上机实验，我们学会了数据查询、修改、视图建立和查询、权限的修改、触发器的建立等。上机过程中也遇到不少问题，通过老师的帮助和同学互相探讨，解决了很多问题。通过本次实验，我了解到数据库今后的发展方向以及其广泛应用，在今后应加强学习。参考文献[1]RamezElmasri，ShamkantB.Navathe著，孙瑜注释.FundamentalsofDatabaseSystems(FourthEdition)(数据库系统基础初级篇英文注释版第四版)人民邮电出版社(影印,英文版).2008-10[2]张祖平,孙星明等.数据库原理及应用,中南大学出版社,2010.11代码汇总/*建表*/createuserU_2112identifiedbylyx330;grantresource,connecttoU_2112;connectU_2112/lyx330;DROPTABLEDEPT_2112;CREATETABLEDEPT_2112(DNOCHAR(3)PRIMARYKEY,DNVARCHAR2(32),DEANCHAR(8),TELCHAR(8)CHECK(SUBSTR(1,3)='8887'));DROPTABLESTUD_2112;CREATETABLESTUD_2112(SNOCHAR(10)PRIMARYKEY,SNAMECHAR(8),BIRTHDAYDATE,NATIVE_PLACEVARCHAR2(20),DNOCHAR(3)REFERENCESDEPT_2112(DNO));DELETEFROMSTUD_2112;

DELETEFROMDEPT_2112;

INSERTINTODEPT_2112VALUES('D01','计算机系','王大明','88879626');

INSERTINTODEPT_2112VALUES('D02','物理系','张大奔','88879726');

INSERTINTODEPT_2112VALUES('D03','化学系','李水明','88879826');INSERTINTODEPT_2112VALUES('D04','管理系','欧阳卫红','88879926');INSERTINTODEPT_2112VALUES('D05','外语系','宽左右','88879526');INSERTINTOSTUD_2112VALUES('0909080524','胡明天',SYSDATE-365*21,'湖南长沙','D01');INSERTINTOSTUD_2112VALUES('0909080134','唐明海',SYSDATE-365*21,'湖南长沙','D01');INSERTINTOSTUD_2112VALUES('0909080224','张丽梅',SYSDATE-365*20,'湖南长沙','D01');INSERTINTOSTUD_2112VALUES('0909080324','李有能',SYSDATE-365*22,'湖南长沙','D02');INSERTINTOSTUD_2112VALUES('0909080424','王汉中',SYSDATE-365*22,'湖南长沙','D02');INSERTINTOSTUD_2112VALUES('0909080464','高强',SYSDATE-365*21,'','D02');

INSERTINTOSTUD_2112VALUES('0909080474','JONE',SYSDATE-365*23,'','D03');

INSERTINTOSTUD_2112VALUES('0909080484','SMITH',SYSDATE-365*21,'','D03');

INSERTINTOSTUD_2112VALUES('0909080594','TOMHONE',SYSDATE-365*22,'','D03');INSERTINTOSTUD_2112VALUES('0909080264','张三',SYSDATE-365*22,'','D04');INSERTINTOSTUD_2112VALUES('0909080274','李四',SYSDATE-365*21,'','D04');INSERTINTOSTUD_2112VALUES('0909080284','王老五',SYSDATE-365*20,'','D04');/*查询：*/select*fromDEPT_2112;select*fromSTUD_2112;selectDN,DNOfromDEPT_2112ORDERbyDNODESC;/*查询系名称，降序*/Select*fromSTUD_2112whereSNAME=’胡明天’;Select*fromSTUD_2112whereEXISTS(SNAME);selectSNO,SNAME,BIRTHDAYfromSTUD_2112whereSNAME=’张三’;select*fromSTUD_2112whereDNO=’D01’selectCOUNT(AGE)fromSTUD_2112groupby(SYSDATE-BIRTHDAY)/365;selectSNO，BIRTHDAY,STUD_2112.DNO,DN,DEANfromSTUD_2112,DEPT_2112whereSNAME=’张三’ANDSTUD_2112.DNO=DEPT_2112.DNO;select*fromSTUD_2112whereSNOlike’090908____’;/*数据操纵*/ALTERTABLESTUD_2112ADDAGENUMBER(2);UPDATESTUD_2112SETAGE=(SYSDATE-BIRTHDAY)/365;UPDATESTUD_2112SETAGE=AGE+3WHERESNOlike’090908____’;UPDATEDEPT_2112SETDN=’信息科学与工程学院’WHEREDN=’计算机系’;DELETEFROMSTUD_2112WHERESNO<0909070000;selectCOUNT(*)fromSTUD_2112；CREATETABLEST_2112(SNOCHAR(10)PRIMARYKEY,SNAMECHAR(8),AGENUMBER(2));INSERTINTOST_2112(SNO)VALUES('0909080424');INSERTINTOST_2112(SNO)VALUES('0909080464');INSERTINTOST_2112(SNO)VALUES('0909080474');INSERTINTOST_2112(SNO)VALUES('0909080484');UPDATEST_2112SETST_2112.AGE=ST_2112.AGE,ST_2112.BIRTHDAY=ST_2112.BIRTHDAYWHEREST_2112.SNO=ST_2112.SNO;/*视图权限修改*/connectsystem/manager;grantcreateviewtoU_2112;connectU_2112/lyx330;CREATEVIEWS_D_2112ASSELECTSNO,SNAME,STUD_2112.DNO,DN,TELFROMSTUD_2112,DEPT_2112WHERESTUD_2112.DNO=DEPT_2112.DNOANDTO_CHAR(BIRTHDAY,'YYYY')>='1991';GRANTSELECTONSTUD_2112TOSCOTT;Select*fromS_D_2112;GRANTINSERTONSTUD_2112TOSCOTT;GRANTUPDATE(BIRTHDAY)ONSTUD_2112TOSCOTT;GRANTDELETEONSTUD_2112TOSCOTT;GRANTSELECTON