数据库原理实验指导书13

数据库原理与技术DATABASEPrinciples,Technology and Application实验指导书面向专业：电子信息工程计算机科学与技术计算机应用软件工程信息安全中南大学信息科学与工程学院编写与整理：张祖平2009 年 3 月编写2011 年 3 月、2012 年 3 修订2013 年 4 月再次修订1有关说明1参考教材1 张祖平,孙星明等.数据库原理及应用,中南大学出版社,2010.11 2 Ramez Elmasri，Shamkant B.Navathe 著，孙瑜注释.Fundamentals of Database Systems (Fourth Edition)(数据库系统基础初级篇 英文注释版 第四版)人民邮电出版社(影印,英文版). 2008-10 2最终实验结果呈现一个文本文件，包括所有实验内容语句、调试数据、思考题等.3对象命名（1）文件名取*_?,其中为现在班号+学号后 4 位，如 orateT_ST_10104,表示电子信息 1 班,学号后四位为 0104,总学号为 0909100104，最后源文件名可取ora_10104.txt 或 abc_10104.sql 等（2）其它对象名如用户、表、视图、过程、触发器等按规则$_*_?,其中$为对象类型标识码，如用户取名 u,表取 t,视图 V，过程取 P，触发器取 TG；?为班级+学号后四位，与文件名的命名规则类同。*可自己命名，不作强制要求，可以用英文简写法，也可以用中文拼音首字母。如 T_STUD_20804, p_sumscore_31516,tg_STUD_ST_42309。目 录实验一、熟悉 ORALCE 环境并练习 SQL 的定义 1一、目的与要求 1二、操作环境 1三、实验内容 1四、思考题 3五、实验报告要求 4实验二、记录查询 5一、目的与要求 5二、操作环境 5三、实验内容 5四、思考题 5五、实验报告要求 5实验三、SQL 的数据操纵 .6一、目的与要求 6二、操作环境 6三、实验内容 6四、思考题 6五、实验报告要求 6实验四、视图及权限控制 7一、目的与要求 7二、操作环境 7三、实验内容 7四、思考题 7五、实验报告要求 7实验五、数据库触发器与过程 9一、目的与要求 9二、操作环境 9三、实验内容 9四、思考题 10五、实验报告要求 10附录：ORACLE 有关命令及内部字典 .11ORACLE 的 SQL*PLUS 基本命令 .11ORACLE 的哑表与数据字典 .11例句中设有一定的错误，请注意调试。 111实验一、熟悉 ORALCE 环境并练习 SQL 的定义一、目的与要求本实验主要是熟悉 ORACLE 的运行环境，在 CLIENT 端进行联接设置与服务器联通，进入 ORACLE 的 SQL*PLUS 的操作环境，进一步建立表格，并考虑主键,外部键,值约束。二、操作环境硬件：主频 2GHz 以上服务器（内存 2GB 以上、硬件空闲 2.2GB 以上） ，主频 1GHz以上微机，内存 1GB 以上。软件：WINDOWS XP/2000/2003 SERVER 可安装 ORACLE/9i/10g/11g/13i FOR NT/WINDOWS(注意在 32 位与 64 位的区别，可选企业版 ) 如果 windows 非 server 如 XP等，安装时请选择个人版(PERSONAL)三、实验内容1.上机步骤单机版：（1）先启动 PERSONAL ORACLE（START DATABASE）（2）用 sql*plus 登录（开始-程序-ORACLE FOR WIN-SQLP*PLUS（与网络版相同）进入 SQL*PLUS 用户名：SYSTEM/manager （其中 manager 为缺省口令，安装需要设定，根据设定的密码进行相应变化；不要数据库联接串，其中 SYSTEM 为 DBA 用户）还有一般用户 scott/tiger(3)进入 SQL状态，可以执行 SQL 中的任何命令（建表，插入、删除、修改、查询等） ，还可建立用户、数据库等操作。(4)退出，在 SQL状态，EXIT 回车 网络版：在实验室上机时,一般安装时管理员已设置好.先找到 windows 中的 oracle 服务（一般为 oracleserviceORCL）,启动此服务,再找到 sql*plus 所在的位置（一般桌面上有）,运行即可进行登录界面.实验室现有环境操作步骤：（1） 打开控制面板，管理工具服务启动 Oracle ServiceORCL2或者如图通过界面或资源管理器右键找到管理：再找服务： 启动的服务如下或有 OracleService*的服务：（2） 从桌面执行 SQL*PLUS 或在程序中（3） 输入：system/manager（4） 在 SQL状态输入建立用户命令:create user username identified by password;其中 username 与 password 不要数字开头，用户名 username 中按规则加班级学号后缀如：U_31521 表示 3 班学号尾数为 1521 号的用户。（5） 给用户授权：grant resource,connect to username;(6) 连接用户：connect username/password在 SQL就可以建表等操作了2.根据要求建立如下表DROP TABLE T_DEPT_?; /* 系，?为班级学号如 t_dept_42405,4 班 2405 号，不能别人的同名，否则视为抄袭,以下各类对象类同要求 */CREATE TABLE T_DEPT_?(DNO CHAR(3) PRIMARY KEY,DN VARCHAR2(32),DEAN CHAR(8),TEL CHAR(8) CHECK(SUBSTR(1,4)=8887);DROP TABLE T_STUD_?; /* 学生 */3CREATE TABLE T_STUD_?(SNO CHAR(10) PRIMARY KEY,SNAME varCHAR2(8),BIRTHDAY DATE,DNO CHAR(3) REFERENCES T_DEPT_?(DNO);/* ALTER TABLE T_STUD_? ADD AGE NUMBER(2) 在修改时候用，去掉注释即可 */* DROP TABLE T_ST_?;CREATE TABLE T_ST_?(SNO CHAR(10),SNAME CHAR(8),AGE NUMBER(2);在建触发器时用，去掉注释即可 */3.插入样本数据/*要求有一个本人的数据记录 */DELETE FROM T_STUD_?;DELETE FROM T_DEPT_?;INSERT INTO T_DEPT_? VALUES(D01,计算机系,王大明,88879626);INSERT INTO T_DEPT_? VALUES(D02,物理系,张大奔,88879726);INSERT INTO T_DEPT_? VALUES(D03,化学系,李水明,88879826);INSERT INTO T_DEPT_? VALUES(D04,管理系,欧阳卫红,88879926);INSERT INTO T_DEPT_? VALUES(D05,外语系,宽左右,88879526);INSERT INTO T_STUD_? VALUES(0909100524,胡明天,SYSDATE-365*21,湖南长沙,D01);INSERT INTO T_STUD_? VALUES(0909100134,唐明海,SYSDATE-365*21,湖南长沙,D01);INSERT INTO T_STUD_? VALUES(0909100224,张丽梅,SYSDATE-365*20,湖南长沙,D01);INSERT INTO T_STUD_? VALUES(0909100324,李有能,SYSDATE-365*22,湖南长沙,D02);INSERT INTO T_STUD_? VALUES(0909100424,王汉中,SYSDATE-365*22,湖南长沙,D02);INSERT INTO T_STUD_? VALUES(0909100464,高 强,SYSDATE-365*21, ,D02);INSERT INTO T_STUD_? VALUES(0909100474,JONE,SYSDATE-365*23, 4,D03);INSERT INTO T_STUD_? VALUES(0909100484,SMITH,SYSDATE-365*21, ,D03);INSERT INTO T_STUD_? VALUES(0909100594,TOM HONE,SYSDATE-365*22, ,D03);INSERT INTO T_STUD_? VALUES(0909100264,张 三, SYSDATE-365*22, ,D04);INSERT INTO T_STUD_? VALUES(0909100274,李 四, SYSDATE-365*21, ,D04);INSERT INTO T_STUD_? VALUES(0909100284,王老五, SYSDATE-365*20, ,D04);四、思考题1 改变表的名称及数据类型再进行相应的操作。2 先删除主表 T_DEPT_?，结果怎么样？3 修改表中的字段类型与长度，考虑表中有数据或者没数据情况。4 插入相同的学号与相同的系号会出现什么结果5 插入空的学号或空的系号会出现的结果6 改变数据库的别名再进行联接(如有网络版 ORACLE)。五、实验报告要求1 实验内容2 源程序清单3 回答思考题4 写出在实验过程中遇到的问题及解决方法5 要求字迹端正、条理清晰、概念正确5实验二、记录查询一、目的与要求本实验主要是熟悉在 SQL*PLUS 环境下进行数据记录查询，其中查询考虑简单查询,复杂查询,嵌套查询。二、操作环境同实验一。三、实验内容1查询所有学生记录2查询所有系的记录，并按系号升序排列3.查询有自己姓名的记录4.查询李四的 SNO，SNAME ，BIRTHDAY5.查询计算系所有的学生记录6.查询各计算系学生在各年龄值的人数，如 19 的为 200，20 的有 500 等7.查询李四的 SNO，BIRTHDAY ，DNO，DN，DEAN四、思考题1 查询学生的记录数。2 在 DEPT_?中输入不是8887开头的电话记录。3 查询平均年龄小于 22 岁的系号及学生 SNO，SNAME，AGE4 找出平均年龄大于 20 的系名及平均年龄数5 执行教材上的多个查询例子.五、实验报告要求1实验内容2调试后的各命令清单3写出思考题的语句4写出在实验过程中遇到的问题及解决方法5要求字迹端正、条理清晰、概念正确6实验三、SQL 的数据操纵一、目的与要求本实验主要是熟悉在 SQL*PLUS 环境下进行数据记录的修改、删除及表结构的修改二、操作环境同实验一。三、实验内容1 在 T_STUD 中增加 AGE NUMBER（2）/*年龄*/字段，并求出每个学生入校时的年龄。2 将 T_STUD_?表中的 08 级学生的年龄都加 33 将 T_DEPT_?表中的计算机系 改为信息科学与工程学院4 删除 07 级以前的所有的学生记录四、思考题1误删了 T_STUD_?中的记录，怎么办？2怎么样才能做到在一个语句中将学生的年龄加上相应的学习年数（如 08 级加3，09 级加 2，10 级加 1，07 级加 4 等） 。3 建立与 T_STUD_?对应的表 T_ST_?（SNO ，SNAME，AGE/* 年龄*/ ） ，在T_ST_?输入若干学号（在 T_STUD_？中有的有几个） ，然后将T_STUD_?中的 SNAME，BIRTHDAY 转入到 T_ST_?相应的SNO，AGE（多表间的更新） 。4 行教材上的更新（包括更新）的例子。五、实验报告要求1实验内容2调试后的各命令清单3写出思考题的解决办法及相应语句4写出在实验过程中遇到的问题及解决方法5要求字迹端正、条理清晰、概念正确7实验四、视图及权限控制一、目的与要求本实验主要是熟悉在 SQL*PLUS 环境下进行数据视图及权限控制的操作。二、操作环境同实验一。三、实验内容1 视图的建立/* 视图建立 */CREATE VIEW V_S_D_? AS SELECT SNO,SNAME,T_STUD_?.DNO,DN,TEL FROM T_STUD_?,T_DEPT_?WHERE T_STUD_?.DNO=T_DEPT_?.DNO AND TO_CHAR(BIRTHDAY,YYYY)=1991;2对视图进行查询3分别将对象的各种权限授予相应的用户假若已有用户 SCOTT（视系统实际情况） ，在SCOTT 中看操作权限的变化。GRANT SELECT ON T_STUD_? TO SCOTT;/* GRANT INSERT ON T_STUD_? TO SCOTT; */* GRANT UPDATE(BIRTHDAY) ON T_STUD_? TO SCOTT; */* GRANT DELETE ON T_STUD_? TO SCOTT; */* GRANT SELECT ON V_S_D_? TO SCOTT; */四、思考题1在视图 V_S_D_?中只考虑信息 09 级，视图将怎样建立？2如果用户为 DBA，还需要给授权吗？3考虑基于一个表、简单条件的视图的更新操作。4考虑基于多表视图的更新操作。5设计与实现针对用户的不同,建立视图,只出现用户本身的记录提示:有用户创建、表创建、视图建立、权限控制、用户联接、查询等操作五、实验报告要求1实验内容2调试后的各命令清单83回答思考题并写出相应的语句4写出在实验过程中遇到的问题及解决方法6要求字迹端正、条理清晰、概念正确9实验五、数据库触发器与过程一、目的与要求本实验主要是熟悉在 SQL*PLUS 环境下数据库触发器的建立及作用，了解大型数据库编程。二、操作环境同实验一。三、实验内容1先建立表 T_ST_?(SNO,SNAME,AGE)2建立触发器CREATE OR REPLACE TRIGGER tg_STUD_TRG_?BEFORE INSERT OR UPDATE OR DELETE ON T_STUD_?FOR EACH ROWBEGIN/* 插入 */IF INSERTING THENINSERT INTO T_ST_? VALUES(:NEW.SNO,:NEW.SNAME,(SYSDATE-:NEW.BIRTHDAY)/365);END IF;/* 修改 *IF UPDATING THENDELETE FROM T_ST_? WHERE SNO=:OLD.SNO;INSERT INTO T_ST_?VALUES(:NEW.SNO,:NEW.SNAME,(SYSDATE-:NEW.BIRTHDAY)/365);END IF;/* 删除 */IF DELETING THENDELETE FROM T_ST_? WHERE SNO=:OLD.SNO;END IF;END.103验证触发器的作用在 T_STUD_?中输入、修改、删除数据时，查看 T_ST_?表中的变化。4设计并调试过程（1）分系 dno 统计 T_STUD_?表中各年龄的人数四、思考题1 如果在 T_STUD_?中不允许修改 SNO，触发器将怎样改进？2 调试过程（1）中，如只要某系号 dno 的 T_STUD_?表中各年龄的统计人数，过程（1）怎么样改进？3在过程（2）中，如果要考虑客户或代理或产品的分月统计情况，过程将怎么样设计并实