信息院数据库原理实验指导书.doc

数据库原理与技术DATABASEPrinciples,Technology and Application实验指导书面向专业：电子信息工程计算机科学与技术计算机应用软件工程信息安全中南大学信息科学与工程学院编写与整理：张祖平2009年3月编写2011年3月、2012年3月有、2013年4月、2014年3月修订有关说明1参考教材1 张祖平,孙星明等.数据库原理及应用,中南大学出版社,2010.11 2 oracle10g教程从入门到精通，百度文库2最终实验结果呈现一个文本文件，包括所有实验内容语句、调试数据、思考题等.3对象命名（1）文件名取*_?,其中为现在班号+学号后4位，如oraexp_10104.txt(sql),表示电子信息1班,学号后四位为0104,总学号为，最后源文件名可取.txt或*.sql等（2）其它对象名如用户、表、视图、过程、触发器等按规则$_*_?,其中$为对象类型标识码，如用户取名u,表取t,视图V，过程取P，触发器取TG；?为班级+学号后四位，与文件名的命名规则类同。*可自己命名，不作强制要求，可以用英文简写法，也可以用中文拼音首字母。如T_STUD_20807, p_sumscore_11517,tg_STUD_ST_12101。目 录实验一、熟悉ORALCE环境并练习SQL的定义1一、目的与要求1二、操作环境1三、实验内容1四、思考题3五、实验报告要求3实验二、数据更新操作4一、目的与要求4二、操作环境4三、实验内容4四、思考题4五、实验报告要求4实验三、视图及权限控制5一、目的与要求5二、操作环境5三、实验内容（综合设计与实现）5四、实验报告要求5实验四、数据库触发器与过程6一、目的与要求6二、操作环境6三、实验内容6四、实验报告要求6附录：ORACLE有关命令及内部字典7ORACLE 的SQL*PLUS基本命令7Oracle的哑表与数据字典7例句中设有一定的错误，请注意调试。7实验一、熟悉ORALCE环境并练习SQL的定义一、目的与要求本实验主要是熟悉ORACLE的运行环境，在CLIENT端进行联接设置与服务器联通，进入ORACLE的SQL*PLUS的操作环境，进一步建立表格，并考虑主键,外部键,值约束。二、操作环境硬件：主频2GHz以上服务器（内存2GB以上、硬件空闲2.2GB以上），主频1GHz以上微机，内存1GB以上。软件：WINDOWS XP/2000/2003/或win7/2008等，如操作系统是 SERVER版 可安装ORACLE/9i/10g/11g/12C FOR NT/WINDOWS(注意在32位与64位的区别，可选企业版) 如果Windows非server如XP/win7等，安装时请选择个人版(PERSONAL)三、实验内容1.上机步骤在实验室上机时,一般安装时管理员已设置好.先找到windows中的oracle服务（一般为OracleServiceORCL，如下图1）,启动此服务,再找到sql*plus所在的位置（一般桌面上有如下图2的程序组有）,运行即可进行登录界面.图1 Oracle在计算机系统中的服务图2称Sql*plus在系统程序组中的位置实验室现有环境操作步骤：通过桌面我的电脑找到管理服务的入口，如图3，或者通过资源管理器找到管理。图3 通过桌面我的电脑找到管理服务的入口然后找到服务，如图4。图4 系统中的服务再找启动oracle的服务如图1：（1） 从桌面或在程序中（图2）执行SQL*PLUS（2） 输入：system/manager（3） 在SQL>状态输入建立用户命令:create user username identified by password;其中username与password不要数字开头，用户名username中按规则加班级学号后缀如：U_11221表示1班学号尾数为1221号的用户。（4） 给用户授权：grant resource,connect to username;(5) 连接用户：connect username/password在SQL>就可以建表等操作了2.根据要求建立如下表定义基本表格dept_?（部门）与emp_?(员工），关系模式如下：Dept_?(DEPTNO char(4) Primary key,NAME VARCHAR2(32),LOC VARCHAR2(32) in(长沙，深圳),tel varchar2(13) 以0731，0755开头）Emp_?(EMPNO char(10) primary key, ENAME VARCHAR2(32), JOB VARCHAR2(12),MGR char(10) foreign key,HIREDATE DATE >, SAL NUMBER(7,2),COMM NUMBER(7,2), DEPTNO char(4) foreign key,sal+comm<=5000)注意表的命名规则3.插入样本数据每个表至少插入10条以上的记录，要求在emp中要有自己的信息，可以学号为职工号，可以参考ORACLE中SCOTT用户下dept的数据(编号与地址、电话需要修改）。Emp中的数据需要参考自己的学号等信息进行修改。 DEPTNO DNAME LOC- - - 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON四、思考题1 能否任意改变表的名称。2 能否先删除dept表？3 能否改变dept表中DEPTNO类型与长度，或改变emp表中empno的类型与长度。4 测试主键，外键与值的约束条件五、实验报告要求1 实验内容2 源程序清单3 回答思考题4 写出在实验过程中遇到的问题及解决方法5 要求字迹端正、条理清晰、概念正确实验二、数据更新操作一、目的与要求本实验主要是熟悉在SQL*PLUS环境下进行数据记录查询、更新与删除，其中查询考虑简单查询,复杂查询,嵌套查询。二、操作环境同实验一。三、实验内容1查询所有职工的记录2查询所有部门的记录，并按部门号升序排列3.查询有自己姓名的记录4.查询李四的EMPNO，ENAME，JOB 5.查询RESEARCH部门所有的职工记录6.查询RESEARCH部门各职工的入司年数（Ceil（sysdate-HIREDATE)/365)）的人数，如10年的为3人，20年的有10人等7.查询李四的EMPNO，ENAME，JOB， DEPTNO，DNAME，LOC8.在emp中增加入司年数years并求出各人的具体数值放到表中（alter 与update)9.删除日期2000年01月01日前的所有员工信息四、思考题1 查询学生的记录数。2 测试表中的完整性约束，并分析不成功的原因3 查询平均入司年数最高与最低的部门五、实验报告要求1实验内容2调试后的各命令清单3写出思考题的语句4写出在实验过程中遇到的问题及解决方法5要求字迹端正、条理清晰、概念正确实验三、视图及权限控制一、目的与要求本实验主要是熟悉在SQL*PLUS环境下进行数据视图及权限控制的操作。二、操作环境同实验一。三、实验内容（综合设计与实现）批处理实现为每个员工建立用户，并授予connect角色，建立视图并给相应查询权限，让每个员工用自己的用户登录能查询到自己的整个信息。如果是manager则可以查询到本部门的所有员工信息，如果是president则可以查询所有员工信息。 四、实验报告要求1实验内容2调试后的各命令清单3回答思考题并写出相应的语句4写出在实验过程中遇到的问题及解决方法5要求字迹端正、条理清晰、概念正确实验四、数据库触发器与过程一、目的与要求本实验主要是熟悉在SQL*PLUS环境下数据库触发器的建立及作用，了解大型数据库编程。二、操作环境同实验一。三、实验内容1先建立表salgrad_?(job,maxsal,minsal)2. 删除emp_?中关于sal+comm<=5000的检查约束3建立触发器满足如下需求：插入或修改emp_?表中的记录时，判定并提示某一job的sal满足不超过maxsal不低于minsal,如提示，“数据输入有误，job为manager的最高工资应为6000,最低应为4000，你输入的工资8000超过了最高6000的限制，请确认输入数据是否正确”4. 测试与调试触发器5设计并调试过程（1）分部门统计工资与津贴的总和，如建立sumsal_?过程，运行时：Exec Sumsal_?(RESEARCH);运行结果为：RESEARCH部门在员工表中的工资与津贴的总额为，有部门中排名第2。四、实验报告要求1实验内容2调试后的程序清单3回答思考题并写出相应的改进语句4写出在实验过程中遇到的问题及解决方法5要求字迹端正、条理清晰、概念正确附录：ORACLE有关命令及内部字典ORACLE 的SQL*PLUS基本命令EDIT 文件名 /*用编辑器编辑缓冲区内容或文件内容*/ /* 执行缓冲区命令 */文件名 /* 执行文件中命令 */LIST /* 列出缓冲区命令内容 */SAVE 文件名 /* 存储缓冲区中命令 */Rollback /* 回退 */Commit /* 提交 */Show error /* 显示错误提示 */Connect user_name/password别名 /* 联接到另一用户 */Spool 文件名 APPEND /* 将结果输出到一个文件中 */Spool off /* 关闭输出结果命令 */DESC 表名 /* 显示表的结构 */基本函数：SUBSTR（VAR，n,m） /* 求字符变量VAR的从n位开始，共m位的子串 */Ceil(num_var) /*求数值变量num_var的最大整数 */to_char(date_var,yyyymmdd hh24:mi:ss) /*日期变量date_var转化为字符 to_date, to_number 类似 */Oracle的哑表与数据字典TAB /*用户主要对象名称及内容 */DUAL /* 系统中最简单的表，只有一个字段，一个记录 */注：例句中设有一定的错误，请注意调试。如条件许可，可以做SQL SERVER的实验，在定义中要作一定的改动，数据库触发器要作大的改动。编写人：张祖平2014-3-25重新整理