Oracle 数据库设计实验指导书yk

Oracle数据库设计实验指导书

《Oracle 数据库设计》课程实验

一、本实验课在培养实验能力中的地位及作用

Oracle 数据库设计是软件工程专业的一门专业课。Oracle数据库是目前最流行的大型数据库平台之一，是一种极具前景的大型数据库。

该课程侧重于Oracle数据库维护和管理知识的掌握以及实际应用Oracle数据库的能力的培养。本课程设置的目的是使学生通过本课程的学习逐渐全面了解Oracle数据库，并具有对大型数据库数据库进行安全、维护等的管理技能，同时能应用Oracle数据库进行数据库结构的设计和数据库应用系统的开发。

二、应达到的实验能力标准

本实验的教学目标是使学生掌握如何使用Oracle 数据库开发系统，了解数据库设计及相关操作的基本概念与方法，进而学会建立与组织、操作数据库。上机实验的主要目标如下:

(1)通过上机操作，加深对数据库系统理论知识的理解。

(2)通过使用具体的DBMS，了解一种实际的数据库管理系统并掌握其操作技术。

(3)通过上机实验，提高动手能力，提高分析问题和解决问题的能力。

(4)通过上机实验，提高动手能力，掌握大型数据库实际应用与开发技巧。

三、实验要求

学生在实验课前认真做好预习，实验结束及时提交电子版实验报告。

三、实验成绩考核方法

实验成绩在课程总成绩中占30%，包括平时每次实验考核与最后一次实验考试。

实验内容目录

实验1 Oracle常用工具的使用及数据库的创建实验2 SQL*Plus基本命令

实验3 oracle存储结构

实验4 数据对象的基本操作

实验5 数据的简单查询

实验6 数据的高级查询

实验7PL/SQL程序设计

实验8 Oracle 的存储过程与触发器

实验9 Oracle数据库安全、恢复与备份

实验10 Oracle数据库应用开发

实验1 常用工具的使用及数据库的创建和管理

实验目的：

1. 能熟练开启oracle的服务，熟悉oracle的环境，以及常用的工具，

主要包括sql*plus和企业管理器em；

2. 通过对数据库的物理文件以及内存参数的查看，结合课件，加深对

oracle实例以及整个体系结构的了解；

3. 能通过查询数据字典视图，了解数据库里的信息；

4. 掌握oracle的用DBCA创建数据库，以及启动和关闭数据库的方法。实验内容：

1．熟悉开启计算机服务。到C:\oracle\product\10.2.0\db_1\NETWORK\ADMIN，修改文件listener.ora和tnsnames.ora，把其中的HOST =×中的×改为你自己的机器名，保存这两个文件。

开启服务：我的电脑，右键，管理，服务，开启OracleServiceORCL和OracleOraDb10g_home1TNSListener服务。这样保证了服务器端监听器能正确监听，用服务器端的sql*plus能正确登录.

2．查看Oracle的安装结果：

注册表，环境变量，目录，以及服务

注册表：开始—运行regedit

查看如下选项：

HKEY_LOCAL_MACHINE\SOFTWARE下的Oracle选项

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services下的与Oralce服务相关的选项。

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Ap plication下的Oracle选项

环境变量：我的电脑，右键，属性，高级—环境变量

目录：C:\Oracle\product\10.2.0 –OracleHome

找到自己机器上的数据库物理文件，包括数据文件，日志文件，控制文件，初始参数文件，用记事本打开参数文件看一下里面内容。

3．用SQL*Plus连接到Oracle数据库，开始→程序→Oracle-OraDb10g_Home1→应用程序开发→SQL*Plus

用户名：sys

口令orcl

主机字符串：orcl as sysdba

练习下面命令：

（1）Select * from V$parameter; //查询数据库的参数信息：

或者show parameter

（2）Show parameter sga // 用以下命令查看实例相应内存的大小

（3）Select * from v$bgprocess; //查看后台进程：

（4）练习把scott用户加锁或解锁：alter user scott account unlock/lock;

（5）Conn scott/tiger@orcl //改用scott登陆：

Select * from user_tables;

Desc emp

Desc dept

（6）修改scott用户密码为mytiger: alter user scott identified by mytiger

4．通过portlist.ini文件查看HTTP服务器所占用的端口号，并启动OEM(企业管理器与isqlplus)。（文件位置D:\oracle\product\10.1.0\Db_1\install目录下的portlist.ini文件, 其URL为http://localHost:5560/isqlplus）。

5．通过客户端的企业管理器查看oracle的信息：

实例/spfile中的参数//方案/表空间/数据文件/控制文件/日志文件

并尝试建表。

6．使用DBCA创建名为myoracle的数据库。

7．在myoracle的数据库，并创建一个学生表。

8*．启动和关闭数据库。熟悉STARTUP与shutdown命令。

9. 练习在命令行和Windows环境下运行SQL*PLUS的方法，并查看emp表中数

据信息。

10. 掌握两个数据库切换，如当前数据库为orcl切换到myoracle数据库。

11. 掌握Sys,sytem,scott用户登陆以及几个用户之间切换，并用show user查询当

前用户。

12. 在SQL>提示符后面输入下面的SELECT语句，可以查看所有Oracle数据库的名称和创建日期。SELECT NAME,CREATED FROM V$DATABASE;

实验2 SQL*Plus基本命令

实验目的：

1. 了解SQL*Plus 和i SQL*Plus 工具的基本命令。

2. 熟练掌握list、run（/）、edit、save、c、a、del、n 等常用命令。实验内容：

1．查看scott.emp 的表结构所及所有记录。

2．练习用edit命令编辑SQL命令的方法。

3．查询emp表中sal>1200的记录信息，用脚本保存到C:\test.sql，并运行该脚本（用save与start完成）。

4．用spool命令把emp表中sal>1200信息输出到d:\ex1.txt文件中。

5．使用替换变量查询Emp表中job为CLERK且sal大于1200的记录信息。6．查询scott.emp表中员工号与员工工资，要求在员工工资数值前加上本地货币符号。

7．查询scott.emp表中员工信息，要求为查询页生成标题和注脚。标题名为“华夏员工信息”，显示居中，注脚为“制作人：学生自己的姓名”。

8．可以用LIST 命令来列出当前SQL缓冲区中的第1行或2行到第3行命令语句。（命令：L[IST] [n|n m|n *|n LAST|*|* n|* LAST|LAST]）

例：SQL> LIST

1 SELECT ENAME, DEPTNO, JOB

2 FROM EMP

3 WHERE JOB = ‘CLERK‘

9．查询scott.emp表中员工工资在1000~2000记录信息，使用命令行方式、SQL 缓冲区方式、脚本文件三种方式运行SQL语句。

10．把Select sal,sal*100 from emp语句中乘号（* ）改为加号（+ ）。即：用语句（c/*/+/ ）

11．在当前行select sal,sal+100 from emp 后加where sal>=2000,显示运行结果。12．设置一行可容纳的120个字符，显示表emp的信息

13．设置每页显示的15行，显示表emp的信息

14．设置列名员工号、员工姓名、工资，显示emp表中信息。

实验3 oracle存储结构

实验目的：

1. 掌握Oracle数据库与实例概念；

2. 掌握Oracle数据库的物理存储；

3. 掌握Oracle数据库的逻辑存储。

实验内容：

1．使用SQL命令创建一个本地管理方式下的自动分区管理的表空间USERTBS1，其对应的数据文件大小为20MB。

2．修改USERTBS1表空间的大小，将该表空间的数据文件修改为自动扩展方式，最大值为100MB。

3．为USERTBS1表空间添加一个数据文件，以改变该表空间的大小。

4．删除表空间USERTBS1，同时删除该表空间的内容以及对应的操作系统文件。5．查询当前数据库中所有的表空间及其对应的数据文件信息。

6．为USERS表空间增加一个数据文件，文件名为userdatao3.dbf,大小为50M。7．修改USERS表空间中的userdatao3.dbf为自动扩展方式，每次扩展5MB，最大为100MB。

8．将USERS表空间中的userdatao3.dbf更名为userdatao4.dbf。

9．为数据库添加一个重做日志文件组，组内包含两个成员文件，分别为redo4a.log 和redo4b.log，大小为分别为5MB.

10．为新建的重做日志文件组添加一个成员文件，名称为redo4c.log。

11．将数据库设置为归档模式，并采用自动归档方式。

实验4 表的基本操作

实验目的：

1. 掌握Oracle表的创建与基本操作；

2. 掌握表的约束类别、及各个约束的应用；

3. 掌握索引、索引化表、分区、视图、序列、同一词功能。

实验内容：

1．在orcl数据库中创建一个名为student2的表，要求：(sno char(6) not null, sname char(10) not null, ssex char(2) not null, birthday date not null, polity char(20),sdept char(20))，其中表中字段满足：sno设置为主键，sname字段设置惟一性约束；2．将student2表的ssex设置检查性约束，要求ssex只能为‘M’或‘F’，polity 字段设置默认约束，值为‘群众’

3．在orcl数据库中创建一个名为course2的表（cno, cname, teacher, class）。4．创建一个学生选课表sc2(sno char(6) not null,cno char(10) not null,grade real).

将sc表创建外键约束，把sc表的sno和student表的sno关联起来，在这两个表之间创建一种制约关系。

5．利用insert语句向student2表中插入一条新的记录：（‘0007’，‘张三’，‘M’，to_date(‘1982-3-21’, ?yyyy-mm-dd‘)，‘团员’,‘计算机系‘）

6．利用update语句将编号为‘0004’的学生polity改为‘党员’：

7．利用delete语句将英语成绩大于90的同学记录删除。

8．创建一个student_list表（列、类型与student表的列、类型相同）按学生性别分为两个区。

9*．创建一个class_number簇，聚簇字段名为CNO,类型为NUMBER(2)。然后利用该簇，创建student和class表。

10．为SCOTT模式下的emp表创建一个公共同义词，名称为employee2. 11．创建一个视图，包含数据为软件工程系学生的考试成绩。

12．用创建一个Users表，按照下面表所示，设计表的结构，并且在UserType 字段必须大于0且小于3，在UserPwd字段的默认值为111111。

13．创建序列USER_S，该序列为1-1000之间整数，自动增加1。使用该序列向表USERS中插入2条新的记录。

实验5 数据的简单查询

实验目的：

1. 掌握SELECT 语句的基本语法；

2. 掌握子查询及order by 子句与GROUP BY用法；

3. 掌握SELECT 语句的统计函数的作用和使用方法；

实验内容：

1．针对student表查询所有学生的基本信息，并按出生日期升序排列。

2．针对student表查询女同学的平均年龄和女同学的人数。

3．在student表中查询所有男同学的年龄。

4．在student表中，(1) 查询‘刘’姓学生的信息;(2) 查询polity为‘团员‘或‘党员‘的学生信息。

5．在sc表中，(1) 查询各门课程的选课人数; (2) 查询缺少成绩的学生的学号及课程号。

6．查询与‘刘成’同一个系的学生情况。

7．查询选修了课程名为‘MIS’的学生的学号和姓名

8．查询姓是‖周‖,‖吴‖,‖郑‖,‖王‖的男学生数量。

9．统计每个系的‘001’号课程考试平均分，并按从高到低排序。

10．针对student、sc、course三张表所有选课学生的姓名、选修课程名及成绩。

11. 查出不及格学生的姓名。

12．查询缺考学生的姓名。

13．查询每个学生基本情况及其所选课程号和成绩（没选课的学生其课程号及成绩为空）。

14．查询比王军的数学成绩都高的学生的档案信息（嵌套查询）。

15．查询计算机系的选课成绩大于85分的学生信息（嵌套查询）。

注：求年龄公式：trunc((sysdate-birthday)/365)

实验6 数据的高级查询

实验目的：

1. 掌握表中数据的查询方法及操作方法

2. 掌握连接查询与嵌套查询的方法

实验内容：

在oracle数据库scott模式下的emp表和dept表，完成下列操作：

1．查询至少有一个员工的所有部门。

2．查询薪金比―SMITH‖多的所有员工。

3．查询所有员工的姓名及其直接上级的姓名。

4．查询受雇日期早于其直接上级的所有员工。

5．查询部门名称和这些部门的员工信息，同时查询那些没有员工的部门。6．查询所有―CLERK‖（办事员）的姓名及其部门名称。

7．查询最低薪金大于1500的各种工作。

8．查询在部门―SALES‖（销售部）工作的员工的姓名，假定不知道销售部的部门编号。

9．查询薪金高于公司平均薪金的所有员工。

10．查询与―SCOTT‖从事相同工作的所有员工。

11．查询薪金等于部门30中员工的薪金的所有员工的姓名和薪金。

12．查询薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。13．查询在每个部门工作的员工数量、平均工资和平均服务期限。

14．查询所有员工的姓名、部门名称和工资。

15．查询所有部门的详细信息和部门人数。

16．查询各种工作的最低工资。

17．查询各个部门的MANAGER（经理）的最低薪金。

18．查询所有员工的年收入,按年薪从低到高排序。

补充：已知emp表和dept表的结构说明如下：

emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)

dept部门表(deptno部门编号/dname部门名称/loc地点)

工资＝薪金＋佣金

实验7 PL/SQL程序设计

实验目的：

1. 掌握SQL 语言流程控制结构；

2. 掌握游标的使用；

实验内容：

1．编写一个PL/SQL块，输出所有员工的员工名、员工号、工资和部门号2．为工资小于2000元的员工增加200元。（用if语句）

3．输入一个员工号，修改该员工的工资，如果该员工为10号部门，工资增加100；若为20号部门，工资增加150；若为30号部门，工资增加200；否则增加300。用if语句完成

4．执行CREATE TABLE temp_table(num_col NUMBER,info_col CHAR(10)) 语句创建temp_table表，然后利用循环向temp_table表中插入50条记录。见课件.

5．根据输入的员工号，修改该员工工资。如果该员工工资低于1000，则工资增加200；如果工资在1000-2000之间，则增加150；如果工资在2000-3000之间，则增加100；否则增加50。

6．根据输入的部门号查询某个部门的员工信息，部门号在程序运行时指定。（用游标）。

7．利用WHILE循环统计并输出各个部门的平均工资。

8．修改员工的工资，如果员工的部门号为10，工资提高100；部门号为20，工资提高150；部门号为30，工资提高200；否则工资提高250。（用游标完成）。9．使用游标提取部门‘‘员工的姓名和工资。

10．修改员工号为1200的员工工资，将其工资提高100；如果该员工不存在，则向emp表中插入一个员工为号1200，工资为2000的员工。

实验8 Oracle的存储过程与触发器

实验目的：

1. 掌握Oracle 的存储过程与函数

2. 掌握Oracle 的触发器

实验内容：

1．创建一个函数，以员工号为参数，返回该员工的工资。

2．创建一个存储过程，以部门号为参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。

3．创建一个存储过程，以部门号为参数，返回该部门的人数和最高工资。4．创建一个以部门号为参数，返回该部门最高工资的函数。

5．创建一个触发器，禁止在休息日改变雇员信息。

6．为emp表创建一个触发器，当执行插入操作时，统计操作后员工人数；当执行更新工资操作时，统计更新后员工平均工资；当执行删除操作时，统计删除后各个部门的人数。

7．创建一个insert触发器,当在student表中插入一条新记录时，给出‘你已经插入了一条新记录！！！’的提示信息。

8．创建一个insert触发器,当在student表中插入一条新记录时，不允许在学号中出现重复的编号或出现空值。

9．创建一个insert触发器,当在sc表中插入一条新记录时，sno和cno必须是已经存在的学号和课程号，且grade应该在0----100之间。

10．创建一个after触发器,在student表中删除某学生的记录时，删除其相应的选课记录。

11．创建一个instead of 触发器，当在course表中上删出记录时，不允许删除course 表中的数据。

12．为emp表创建一个触发器，保证修改员工工资时，修改后的工资低于该部门最高工资，同时高于该部门的最低工资。P363

实验9 Oracle的数据库安全、恢复与备份

实验目的：

1. 掌握Oracle数据库的完整性约束；

2. 掌握Oracle 的恢复与备份，数据的导入与导出；

3. 掌握Oracle 的安全与权限。

实验内容：

1．创建一个口令认证的数据库用户usera_exer，口令为usera默认表空间为USERS,配额为10MB,初始帐号为锁定状态。

2．创建一个口令认证的数据库用户userb_exer，口令为userb。

3．为usera_exer用户授予CREATE SESSION权限、SCOTT.emp的SELECT权限和UPDATE权限。同时允许该用户将获得的权限授予其他用户。

4．用usera_exer登陆数据库，查询和更新scott.emp中的数据。同时，将scott.emp 的SELECT权限和UPDATE权限授予userb_exer。

5．禁止用户usera_exer将获得的scott.emp的SELECT权限和UPDATE权限授予其他人。

6．创建角色rolea和roleb,将CREATE TABLE权限、SCOTT.emp的SELECT权限和UPDATE权限授予rolea；将CONNECT, RESOURCE角色授予roleb。7．将角色rolea、roleb授予用户usera_exer。

8．使用冷物理备份对数据库进行完全备份。

9．假定丢失了一个数据文件example01.dbf，试使用前面（第8题）做过的完全备份对数据库进行恢复，并验证恢复是否成功。

10．使用热物理备份对表空间users的数据文件user01.dbf进行备份。

11．使用EXP命令导出SCOTT用户下的所有数据库对象。

12．创建一个用户JOHN，并使用IMP命令将SCOTT用户下的所有数据库对象导入。

实验10 Oracle数据库应用开发

实验目的：

1. 复习学过的基本知识；

2. 把所学过的知识应用与实际；

3. 掌握Oracle开发流程。

实验内容：

1．人事管理系统数据库表设计,其中包括：员工基本信息表、员工工资信息表、员工请假信息表、管理员表等。

2．图书管理系统数据库表设计,其中包括：图书信息表、借阅信息表、员工请假信息表、管理员表等。

3．学生成绩管理系统数据库表设计,其中包括：学生表、选课程信息表、成绩信息表、教师表等。