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

《Oracle 数据库设计实验指导书yk.doc》由会员分享，可在线阅读，更多相关《Oracle 数据库设计实验指导书yk.doc（17页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Oracle数据库设计实验指导书Oracle 数据库设计课程实验一、本实验课在培养实验能力中的地位及作用Oracle 数据库设计是软件工程专业的一门专业课。Oracle数据库是目前最流行的大型数据库平台之一，是一种极具前景的大型数据库。该课程侧重于Oracle数据库维护和管理知识的掌握以及实际应用Oracle数据库的能力的培养。本课程设置的目的是使学生通过本课程的学习逐渐全面了解Oracle数据库，并具有对大型数据库数据库进行安全、维护等的管理技能，同时能应用Oracle数据库进行数据库结构的设计和数据库应用系统的开发。二、应达到的实验能力标准本实验的教学目标是使学生掌握如何使用Oracle

2、数据库开发系统，了解数据库设计及相关操作的基本概念与方法，进而学会建立与组织、操作数据库。上机实验的主要目标如下:(1)通过上机操作，加深对数据库系统理论知识的理解。(2)通过使用具体的DBMS，了解一种实际的数据库管理系统并掌握其操作技术。(3)通过上机实验，提高动手能力，提高分析问题和解决问题的能力。(4)通过上机实验，提高动手能力，掌握大型数据库实际应用与开发技巧。三、实验要求学生在实验课前认真做好预习，实验结束及时提交电子版实验报告。三、实验成绩考核方法实验成绩在课程总成绩中占30%，包括平时每次实验考核与最后一次实验考试。实验内容目录实验1 Oracle常用工具的使用及数据库的创建实

3、验2 SQL*Plus基本命令实验3 oracle存储结构实验4 数据对象的基本操作实验5 数据的简单查询 实验6 数据的高级查询实验7 PL/SQL程序设计 实验8 Oracle 的存储过程与触发器实验9 Oracle数据库安全、恢复与备份实验10 Oracle数据库应用开发实验1 常用工具的使用及数据库的创建和管理实验目的：1. 能熟练开启oracle的服务，熟悉oracle的环境，以及常用的工具，主要包括sql*plus和企业管理器em；2. 通过对数据库的物理文件以及内存参数的查看，结合课件，加深对oracle实例以及整个体系结构的了解；3. 能通过查询数据字典视图，了解数据库里的信息

4、；4. 掌握oracle的用DBCA创建数据库，以及启动和关闭数据库的方法。实验内容：1熟悉开启计算机服务。到C:oracleproduct10.2.0db_1NETWORKADMIN，修改文件listener.ora和tnsnames.ora，把其中的HOST =中的改为你自己的机器名，保存这两个文件。开启服务：我的电脑，右键，管理，服务，开启OracleServiceORCL和OracleOraDb10g_home1TNSListener服务。这样保证了服务器端监听器能正确监听，用服务器端的sql*plus能正确登录.2查看Oracle的安装结果：注册表，环境变量，目录，以及服务注册表：开

5、始运行 regedit查看如下选项：HKEY_LOCAL_MACHINESOFTWARE下的Oracle选项HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServices下的与Oralce服务相关的选项。HKEY_LOCAL_MACHINESYSTEMCurrentControlSetServicesEventlogApplication下的Oracle选项环境变量：我的电脑，右键，属性，高级环境变量目录：C:Oracleproduct10.2.0 OracleHome找到自己机器上的数据库物理文件，包括数据文件，日志文件，控制文件，初始参数文件，用记事本打

6、开参数文件看一下里面内容。 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 accoun

7、t unlock/lock;（5）Conn scott/tigerorcl /改用scott登陆： Select * from user_tables;Desc empDesc dept（6）修改scott用户密码为mytiger: alter user scott identified by mytiger4通过portlist.ini文件查看HTTP服务器所占用的端口号，并启动OEM(企业管理器与isqlplus)。（文件位置D:oracleproduct10.1.0Db_1install目录下的portlist.ini文件, 其URL为http:/localHost:5560/isqlp

8、lus）。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. 在SQ

9、L提示符后面输入下面的SELECT语句，可以查看所有Oracle数据库的名称和创建日期。 SELECT NAME,CREATED FROM V$DATABASE;实验2 SQL*Plus基本命令实验目的：1. 了解SQL*Plus 和iSQL*Plus 工具的基本命令。2. 熟练掌握list、run（/）、edit、save、c、a、del、n 等常用命令。实验内容：1查看scott.emp 的表结构所及所有记录。2练习用edit命令编辑SQL命令的方法。3查询emp表中sal1200的记录信息，用脚本保存到C:test.sql，并运行该脚本（用save与start完成）。4用spool命令把

10、emp表中sal1200信息输出到d:ex1.txt文件中。 5使用替换变量查询Emp表中job为CLERK且sal大于1200的记录信息。6查询scott.emp表中员工号与员工工资，要求在员工工资数值前加上本地货币符号。7查询scott.emp表中员工信息，要求为查询页生成标题和注脚。标题名为“华夏员工信息”，显示居中，注脚为“制作人：学生自己的姓名”。8可以用 LIST 命令来列出当前SQL缓冲区中的第1行或2行到第3行命令语句。（命令：LIST n|n m|n *|n LAST|*|* n|* LAST|LAST）例：SQL LIST1 SELECT ENAME, DEPTNO, JO

11、B2 FROM EMP3 WHERE JOB = CLERK9查询scott.emp表中员工工资在10002000记录信息，使用命令行方式、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

12、oracle存储结构实验目的：1. 掌握Oracle数据库与实例概念；2. 掌握Oracle数据库的物理存储；3. 掌握Oracle数据库的逻辑存储。实验内容：1使用SQL命令创建一个本地管理方式下的自动分区管理的表空间USERTBS1，其对应的数据文件大小为20MB。2修改USERTBS1表空间的大小，将该表空间的数据文件修改为自动扩展方式，最大值为100MB。3为USERTBS1表空间添加一个数据文件，以改变该表空间的大小。4删除表空间USERTBS1，同时删除该表空间的内容以及对应的操作系统文件。 5查询当前数据库中所有的表空间及其对应的数据文件信息。6为USERS表空间增加一个数据文件

13、，文件名为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. 掌握表的约束类别、及各个约束的

14、应用；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数据库中创建一个名为cou

15、rse2的表（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的同学记录删除。

16、8创建一个student_list表（列、类型与student表的列、类型相同）按学生性别分为两个区。9*创建一个class_number簇，聚簇字段名为CNO,类型为NUMBER(2)。然后利用该簇，创建student和class表。10为SCOTT模式下的emp表创建一个公共同义词，名称为employee2.11创建一个视图，包含数据为软件工程系学生的考试成绩。12用创建一个Users表，按照下面表所示，设计表的结构，并且在UserType字段必须大于0且小于3，在UserPwd字段的默认值为。编 号字 段 名 称数 据 结 构说 明1UserIdNUMBER用户编号2UserNameVA

17、RCHAR2 40用户名3UserTypeNUMBER 1用户类型（1表示管理用户，2表示普通用户）4UserPwdVARCHAR2 40密码13创建序列USER_S，该序列为1-1000之间整数，自动增加1。使用该序列向表USERS中插入2条新的记录。实验5 数据的简单查询实验目的：1. 掌握SELECT 语句的基本语法；2. 掌握子查询及order by 子句与GROUP BY用法；3. 掌握SELECT 语句的统计函数的作用和使用方法；实验内容：1针对student表查询所有学生的基本信息，并按出生日期升序排列。2针对student表查询女同学的平均年龄和女同学的人数。3在student

18、表中查询所有男同学的年龄。4在student表中，(1) 查询刘姓学生的信息;(2) 查询polity为团员或党员的学生信息。5在sc表中，(1) 查询各门课程的选课人数; (2) 查询缺少成绩的学生的学号及课程号。6查询与刘成同一个系的学生情况。7查询选修了课程名为MIS的学生的学号和姓名8查询姓是”周”,”吴”,”郑”,”王”的男学生数量。9统计每个系的001号课程考试平均分，并按从高到低排序。10针对student、sc、course三张表所有选课学生的姓名、选修课程名及成绩。11. 查出不及格学生的姓名。12查询缺考学生的姓名。13查询每个学生基本情况及其所选课程号和成绩（没选课的学生

19、其课程号及成绩为空）。14查询比王军的数学成绩都高的学生的档案信息（嵌套查询）。15查询计算机系的选课成绩大于85分的学生信息（嵌套查询）。 注：求年龄公式：trunc(sysdate-birthday)/365)实验6 数据的高级查询实验目的：1. 掌握表中数据的查询方法及操作方法2. 掌握连接查询与嵌套查询的方法实验内容：在oracle数据库scott模式下的emp表和dept表，完成下列操作：1查询至少有一个员工的所有部门。2查询薪金比“SMITH”多的所有员工。3查询所有员工的姓名及其直接上级的姓名。4查询受雇日期早于其直接上级的所有员工。5查询部门名称和这些部门的员工信息，同时查询那

20、些没有员工的部门。6查询所有“CLERK”（办事员）的姓名及其部门名称。7查询最低薪金大于1500的各种工作。8查询在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。9查询薪金高于公司平均薪金的所有员工。10查询与“SCOTT”从事相同工作的所有员工。11查询薪金等于部门30中员工的薪金的所有员工的姓名和薪金。12查询薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。13查询在每个部门工作的员工数量、平均工资和平均服务期限。14查询所有员工的姓名、部门名称和工资。15查询所有部门的详细信息和部门人数。16查询各种工作的最低工资。17查询各个部门的MANAGER（

21、经理）的最低薪金。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输入一个员工号，

22、修改该员工的工资，如果该员工为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根据输入的部门号查询某个部门的员工信息

23、，部门号在程序运行时指定。（用游标）。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创

24、建一个存储过程，以部门号为参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。3创建一个存储过程，以部门号为参数，返回该部门的人数和最高工资。4创建一个以部门号为参数，返回该部门最高工资的函数。5创建一个触发器，禁止在休息日改变雇员信息。6为emp表创建一个触发器，当执行插入操作时，统计操作后员工人数；当执行更新工资操作时，统计更新后员工平均工资；当执行删除操作时，统计删除后各个部门的人数。7创建一个insert触发器,当在student表中插入一条新记录时，给出你已经插入了一条新记录！的提示信息。8创建一个insert触发器,当在student表中插入一条新记录时，不允许

25、在学号中出现重复的编号或出现空值。9创建一个insert触发器,当在sc表中插入一条新记录时，sno和cno必须是已经存在的学号和课程号，且grade应该在0-100之间。10创建一个after触发器,在student表中删除某学生的记录时，删除其相应的选课记录。11创建一个instead of 触发器，当在course表中上删出记录时，不允许删除course表中的数据。12为emp表创建一个触发器，保证修改员工工资时，修改后的工资低于该部门最高工资，同时高于该部门的最低工资。P363实验9 Oracle的数据库安全、恢复与备份实验目的：1. 掌握Oracle数据库的完整性约束；2. 掌握Or

26、acle 的恢复与备份，数据的导入与导出；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权限授予user

27、b_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学生成绩管理系统数据库表设计,其中包括：学生表、选课程信息表、成绩信息表、教师表等。