Oracle实验教案.docx

Oracle实验教案.docx

《Oracle实验教案.docx》由会员分享，可在线阅读，更多相关《Oracle实验教案.docx（18页珍藏版）》请在冰点文库上搜索。

Oracle实验教案

实验一创建数据库和表

目的和要求

（1）了解数据库的结构以及一些基恩概念

（2）了解表的结构特点

（3）了解Oracle10gde基本数据类型

（4）学会使用DBCA创建数据库

（5）学会在OEM中创建表

（6）学会使用SQL语句手工创建数据库

（7）学会使用SQL语句创建表

实验准备

首先要明确，能够创建数据库的用户必须是系统管理员，或是被授权使用CREATEDATABASE语句的用户。

其次，创建数据库必须明确数据库名、所有者（即创建数据库的用户）、数据库大小、SGA分配和存储数据库的文件。

然后，确定数据库包含哪些表以及所包含的结构，还要了解Orcale10g的常用数据类型，以创建数据库的表。

此外还要了解两种常用的创建数据库、表的方法，即利用DBCA创建和使用PL/SQL语言的CREATEDATABASE语句创建。

实验内容

创建企业管理的员工管理数据库YGGL，包含Employees（员工自然信息）表、Departments（部门信息表）表和Salary（员工薪水情况）表。

各表的结构如表T1.1至表T1.3所示。

表T1.1Employees表结构

表T1.2Department表结构

表T1.3Salary表结构

1.利用DBCA创建数据库YGGL

利用DBCA创建数据库YGGL的步骤如下：

（1）数据库名称为YGGL，他的全局数据库名称为YGGL。

（2）控制文件有3个，其存放路径为：

D：

\oracle\oradata\yggl\，其名称分别为CONTROL01.CTL、CONTROL02.CTL和CONTROL03.CTL。

（3）重做日志文件有3个，其大小为100MB，存放路径为：

D：

\oracle\oradata\yggl，名称分别为：

redo01.log、redo01.log和redo03.log。

（4）创建临时表空间temp01.dpf。

（5）字符集为：

数字字符集为ZHS16GBK，国家字符集为AL16UTF16。

（6）数据库大小为：

4KB。

进入DBCA，根据其提示逐步完成数据库的创建工作，详细步骤请参照2.2节。

2.利用DBCA删除数据库YGGL

参照2.2节删除数据库方法删除数据库YGGL。

3.使用PL/SQL手工创建数据库（选做）

按照上述要求创建数据库YGGL。

以下给出创建数据库YGGL所用到的CREATEDATABASE语句：

CreatedatabaseYGGL

MAXINSTANCES1

MAXLOGHISTORY1

MAXLOGFILES5

MAXMEMEBERS5

MAXDATAFILES100

DATAFILE'D:

\oracle\oradata\yggl\system01.dbf'

SIZE325MreuseAUTOEXTENONNEXT1024KMAXSIZEUNLIMITED

CHARACTERSETZHS16GBK

NATIONALCHARACTERSETAL16UTF16

LOGFILEGROUP1（'d:

\oracle\oradata\yggl\redo01.log'）SIZE100M

GROUP2（'d:

\oracle\oradata\yggl\redo02.log'）SIZE100M

GROUP3（'d:

\oracle\oradata\yggl\redo03.log'）SIZE100M

defaulttemporarytablespacetemp

tempfile'D:

\oracle\oradata\yggl\temp01.dpf'

extentmanagementlocaluniformsize10M

undotablespaceundo_tsdatafile'D:

\oracle\oradata\yggl\temp01.dbf'

size150Mreuseautoextendonnext10240kmaxsizeunlimited;

4.在OEM中分别创建表Employees、表Departments和表Salary

在OEM目录中，选择表单击鼠标左键，在小狐仙的界面中单击“创建”按钮，进入表创建界面，在各个选项卡上输入表Employees各字段信息、约束条件、分区和存储情况等，单击确定按钮，创建表Employees即可完成。

5.在OEM中删除创建的表Employees、表Departments和表Salary

在OEM界面中选中数据库YGGL的表Employees,单击鼠标右键，在快捷菜单中选择“删除”，即可删除表Employees了。

按同样的操作过程删除表Departments和表Salary。

6.使用PL/SQL语句创建表Employees、表Departments和表Salary

在SQL*plus中输入如下语句：

createtableEmployees

（

EmployessIDchar（6）notnull,

Namechar（10）notnull,

Birthdaydatenotnull,

Sexnumber

（1）notnull,

Addresschar（20）notnull,

Zipchar（6）null,

PhoneNumberchar（12）null,

EmailAddresschar

（2）null,

DepartmentIDchar（3）notnull,

）;

按回车键，执行上述语句，即可创建表Employees。

按同样的操作过程创建表Departments和表Salary，并在OEM中查看结果。

实验二表数据插入、修改、删除

目的和要求

（1）学会使用PL/SQL语句对数据库表进行插入、修改和删除数据操作。

（2）了解数据更新操作时要注意数据完整性。

（3）了解PL/SQL语句对表数据操作的灵活控制功能。

实验准备

首先要了解对表数据的插入、修改、删除都属于表数据的更新操作。

对表数据的操作可以在企业管理器中进行，也可以在PL/SQL语句实现。

其次要掌握PL/SQL语句中用于对表数据进行插入、修改和删除的命令分别是insertupdate和delete（或trancetetable）。

要特别注意在执行插入、删除、修改等数据更新操作时，必须保证数据的完整性。

此外，还要了解使用PL/SQL语句在对表数据进行插入、修改和删除时，比在OEM中操作表数据要更为灵活，功能更强大。

实验内容

分别使用OEM命令和PL/SQL语句，在实验1建立的数据库库YGGL的表Employees、表Departments和表Salary中插入多行数据记录，然后修改和删除一些记录，使用PL/SQL命令进行有限制的修改和删除。

（1）使用PL/SQL语句分别向YGGL数据库的表Employees、表Departments和表Salary中插入一行记录。

启动SQL*plus界面输入以下PL/SQL语句，按回车键执行语句：

insertintoEmployees

values（'011112','罗林',chartodate（'1973-06-26','yyyymmdd'）,1,'解放路100号',210002,4055663,null,5）;

insertintoDepartments

values（'2','人力资源',null）;

insertintoSalay

values（'011112',1200.09,50）;

注意：

在OEM中分别打开YGGL数据库的表Employees、表Departments和表Salary，观察数据变化。

（2）使用PL/SQL命令修改表Salary中的某个记录的字段值

启动SQL*plus界面输入以下PL/SQL语句，按回车键执行语句：

UPDATESalary

setInCome=2890whereEmployeesID='011112';

按回车键，执行上述语句，将编号为011112的职工收入改为2890.，在OEM中打开YGGL数据库的Salary表，观察数据变化。

（3）修改表Employees和表Departments的记录值，仍要注意完整性。

操作过程同

（2）。

（4）使用PL/SQL命令修改表Salary中的所有记录的字段值

启动SQL*plus界面输入以下PL/SQL语句：

updateSalary

setInCome=InCome+100;

按回车键，执行上述语句，将所有的员工工收入增加100。

可见，使用PL/SQL语句操作表数据比在OEM中操作数据更为灵活，输入一下PL/SQL语句，观察数据变化。

select*fromSalary;

（5）使用TRANCATETABLESalary语句删除表中所有行。

在SQL*plus界面中输入以下PL/SQL语句：

TRANCATETABLESalary;

按回车键，执行上述语句，将删除Salary表中的所有行。

注意：

实验时一般不是轻易的进行这个表的操作，后面实验还要用到这些数据，如要试验该命令的效果，可建一个临时表，输入少量数据进行。

实验三索引和完整性

目的和要求

（1）掌握索引的使用方法

（2）掌握数据完整性的概念及方法

（3）掌握各种数据完整性的实现方法

实验准备

（1）了解索引的作用和分类

（2）掌握索引的创建

（3）理解数据完整性的概念及分类

（4）了解各种数据完整性的实现方法

实验内容

1.建立索引

对YGGL数据库的Employees表中的DepartmentID列建立索引。

在SQL*plus编辑界面输入以下语句并执行：

createindexPK_XS_BAK

onEmployees（DepartmentID）

tablespaceuserspctfree48initrans10maxtrans100

storage（inital64knext64kminextents5maxxtents20pctincrease10freelists1freelistgroups1）

parallel（degreedefault）

loggingnosort;

2.实现域完整性

为YGGL数据库的Employees表中PhoneNumber建立check约束。

在SQL*plus编辑界面输入如下语句并执行：

AltertableEmployees

add（constraintch_phonecheck（PhoneNumberbetween'0'and'9'））;

3.实现实体完整性

实现实体完整性的步骤如下。

（1）使用SQL语句创建表Departments,DepartmentID为主键。

在SQL*plus编辑界面输入如下程序并执行：

createtableDepartments

（

DepartmentIDchar（3）,

DepartmentNamechar（20）notnull,

Notechar（16）null,

contraintpk_Departmentsprimarykey（DepartmentID）

）;

（2）为表Departments的DepartmentName建立唯一索引

altertableDepartments

add（constraintun_Departments（DepartmentName））;

4.实现参照完整性

为Employees表中的DepartmentID建立外键

altertableEmployees

add（constraintfk_Employeesforeignkey（DepartmentID）referencesDepartments（DepartmentID））;

实验四数据库的查询和视图

目的和要求

（1）掌握select语句的基本用法

（2）掌握子查询的表示

（3）掌握连接查询的表示

（4）掌握数据汇总的方法

（5）掌握select语句groupby子句的用法和使用方法

（6）掌握select语句orderby子句的用法和使用方法

（7）掌握视图的使用方法

实验准备

（1）了解select语句的基本语法格式和执行方法

（2）了解子查询的表示方法，连接查询的数据汇总的方法

（3）了解select语句的GroupBy子句的用法和使用方法

（4）了解select语句orderby子句的用法和使用方法

（5）了解视图的作用和视图的创建方法

（6）了解视图的使用方法

实验内容

1.select语句的基本使用

select语句的基本使用方法有以下几个方面。

（1）对于实验2给出的数据表的结构，查询每一个雇员的所有数据。

在SQL*PLUS界面输入如下的语句并执行：

select*fromEmployees;

思考练习：

用select语句查询Departments表和Salary表的所有数据

（2）查询每一个雇员的电话和地址

在SQL*PLUS界面输入如下的语句并执行：

selectAddress,PhoneNumberfromEmployees;

思考练习：

用select语句查询Departments表和Salary表的一列或若干列。

（3）查询EmployeeID为000001的雇员的地址和电话

在SQL*PLUS界面输入如下的语句并执行：

selectAddress,PhoneNumberfromEmployeeswhereEmployeeID='000001';

思考练习：

用select语句查询Departments表和Salary表满足指定条件的一列或若干列。

（4）查询Employees表中女雇员的电话和地址，使用AS子句将结果中各列的标题分别指定为地址、电话。

在SQL*PLUS界面输入如下的语句并执行：

selectAddressas地址,PhoneNumberas电话fromEmployeeswheresex=0;

注意：

使用as子句可指定目标列的标题

（5）计算每个雇员的实际收入

在SQL*PLUS界面输入如下的语句并执行：

selectEmployeeID,InCome-OutComeas实际收入fromSalary;

（6）找出所有姓王的雇员和部门号。

在查询分析器界面输入如下的语句并执行：

selectDepartmentIDfromEmployeeswhereNamelike'王%';

思考练习：

找出所有地址中含有“中山”的雇员的号码及部门号。

（7）找出所有收入在2000--3000元之间的雇员的号码

在SQL*PLUS界面输入如下的语句并执行：

selectEmploeeIDfromSalarywhereIncombetween2000and3000;

思考练习：

找出所有在部门‘1’或‘2’工作的雇员的号码。

注意：

在select语句中like、betweenand、in、not谓词的作用。

2.子查询的使用

子查询的使用在以下几个方面。

（1）查找在财务部工作的雇员的情况。

在SQL*PLUS界面输入如下的语句并执行：

select*fromEmployeeswhereDepartmentID=（selectDepartmentIDfromDepartmentswhereDepartmentName='财务部'）;

思考练习：

用子查询的方法查找所有收入在2500元以下的雇员的情况。

（2）查找财务部年龄不低于研发部雇员年龄的雇员的姓名

selectName

fromEmployees

whereDepartmentIDin（selectDepartmentIDfromDepartmentswhereDepartmentName='财务部'）

andBirthday!

>ALL

（selectBirthdayfromEmployeeswhereDepartmentIDin（selectDepartmentIDfromDepartmentswhereDepartmentName='研发部'））;

思考练习：

用子查询的方法查找研发部比所有财务部雇员收入都高的雇员的姓名。

（3）查找比所有财务部的雇员收入都高的雇员的姓名

selectname

fromEmployeeswhereEmployeeIDin

（selectEmploteeID

fromSalarywhereInCome>ALL

（selectInComefromSalarywhereEmployeeIDin

（selectEmployeeIDfromEmployeeswhereDepartmentID=

（selectDepartmentID

fromDepartmentswhereDepartmentName='财务部'）

）

）

）;

思考练习：

用子查询的方法查找所有年龄比研发部雇员年龄都大的雇员的姓名。

3.连接查询的使用

连接查询的使用有以下两个方面

（1）查询每一个雇员的情况及其薪水情况。

在SQL*PLUS界面输入如下的语句并执行：

selectEmployees.*,Salary.*

fromEmployees,Salary

whereEmployees.EmployeeID=Salary.EmployeeID;

思考练习：

查询每一个雇员的情况和部门的情况。

（2）查找财务部收入在2200以上的雇员的姓名及其薪水情况。

在SQL*PLUS界面输入如下的语句并执行：

selectName,InCome,OutCome

fromEmployes,Salary,Departments

whereEmployees.EmployeeID=Salary.EmployeeIDandEmployees.

DepartmentID=Departments.DepartmentIDandDepartmentName='财务部'andInCome>2000;

思考练习：

查询研发部在1966年以前的雇员姓名及其薪水情况

4.数据汇总

数据汇总的内容有以下几个方面。

（1）求财务部雇员的平均收入。

在SQL*PLUS界面输入如下的语句并执行：

selectAVG（InCome）as'财务部平均收入'

fromSalary

whereEmployeeIDin（selectEmployeeIDfromEmployeeswheredepartmentID=（selectDepartmentIDfromDepartmentswhereDepartmentName='财务部'））;

思考练习：

查询财务部雇员的最高和最低收入。

（2）求财务部雇员的平均实际收入

在SQL*PLUS界面输入如下的语句并执行：

selectAVG（InCome-OutCome）as'财务部实际平均收入'

fromSalary

whereEmployeeIDin（selectEmployeeIDfromEmployeeswheredepartmentID=（selectDepartmentIDfromDepartmentswhereDepartmentName='财务部'））;

思考练习：

查询财务部雇员的实际最高和最低收入。

（3）求财务部雇员的总人数

在SQL*PLUS界面输入如下的语句并执行：

selectcount（EmployeeID）

fromEmployees

whereDepartmentID=（selectDepartmentIDfromDepartmentswhereDepartmentName='财务部'）;

思考练习：

统计财务部收入在2500元以上的雇员的人数。

5.GROUPBY、ORDERBY子句的使用

GROUPBY、ORDERBY子句的使用有以下两个方面

（1）求各部门的雇员数

在SQL*PLUS界面输入如下的语句并执行：

selectcount（EmployeeID）

fromEmployeesGroupByDepartmentID

思考练习：

统计各部门收入在2000元以上雇员的人数。

（2）将各雇员的情况按收入由低到高排列

在SQL*PLUS界面输入如下的语句并执行：

selectEmployees.*,Salary.*

fromEmployees,Salary

whereEmployees.EmployeeID=Salary.EmployeeID

OrderByInCome;

思考练习：

将各雇员的情况按出生时间先后排列

6.使用视图

使用视图的方法和步骤如下。

（1）创建视图

a.限制查看雇员的某些情况。

在SQL*PLUS界面输入如下的语句并执行：

Createorreplaceviewcx_employees

as

selectEmployeeID,Name,Birthday,Sex,DepartmentID

fromEmployees;

b.各部门经理只能查找本部门的雇员的薪水情况，如财务部经理查看自己部门的雇员姓名及其薪水情况。

在SQL*PLUS界面输入如下的语句并执行：

createorreplaceviewcx_salary

as

selectName,InCome,OutCome

fromEmployees,Salary,Department

whereEmployees.EmployeeID=Salary.EmployeeIDandEmployees.

DepartmentID=Departments.DepartmentIDandDepartmentName='财务部';

（2）使用视图

a.查询财务部雇员信息。

在SQL*PLUS界面输入如下的语句并执行：

select*fromcx_employees;

b.查询财务部雇员薪水情况。

在SQL*PLUS界面输入如下的语句并执行：

select*fromcx_salary;

c.向Employees表中插入一条记录。

在SQL*PLUS界面输入如下的语句并执行：

insertintocx_employeesvalues（'510888','张无忌',chartodate（'1978-08-23','YYYYMMDD'）,1,'3'）;

d.张无忌从经理办公室转到市场部。

在SQL*PLUS界面输入如下的语句并执行：

updatecx_employeessetDepartmentID='5'whereName='张无忌';

e.把张无忌从表Employees中删除。

在SQL*PLUS界面输入如下的语句并执行：

deletef