Oracle实验教案.docx

1、Oracle实验教案实验一 创建数据库和表目的和要求（1）了解数据库的结构以及一些基恩概念（2）了解表的结构特点（3）了解Oracle 10gde 基本数据类型（4）学会使用DBCA创建数据库（5）学会在OEM中创建表（6）学会使用SQL语句手工创建数据库（7）学会使用SQL语句创建表实验准备 首先要明确，能够创建数据库的用户必须是系统管理员，或是被授权使用 CREATE DATABASE语句的用户。其次，创建数据库必须明确数据库名、所有者（即创建数据库的用户）、数据库大小、SGA分配和存储数据库的文件。然后，确定数据库包含哪些表以及所包含的结构，还要了解Orcale 10g的常用数据类型，以

2、创建数据库的表。此外还要了解两种常用的创建数据库、表的方法，即利用DBCA创建和使用PL/SQL语言的CREATE DATABASE语句创建。实验内容创建企业管理的员工管理数据库YGGL，包含Employees（员工自然信息）表、Departments（部门信息表）表和Salary（员工薪水情况）表。各表的结构如表T1.1至表T1.3所示。表T1.1 Employees表结构表T1.2 Department表结构表T1.3 Salary表结构1.利用DBCA创建数据库YGGL 利用DBCA创建数据库YGGL的步骤如下：（1）数据库名称为YGGL，他的全局数据库名称为YGGL。（2）控制文件有3

3、个，其存放路径为： D：oracleoradatayggl，其名称分别为CONTROL01.CTL、CONTROL02.CTL和CONTROL03.CTL。（3）重做日志文件有3个，其大小为100MB，存放路径为： D：oracleoradatayggl，名称分别为：redo01.log、redo01.log和redo03.log。（4）创建临时表空间temp01.dpf。（5）字符集为：数字字符集为ZHS16GBK，国家字符集为AL16UTF16。（6）数据库大小为：4KB。进入DBCA，根据其提示逐步完成数据库的创建工作，详细步骤请参照2.2节。2.利用DBCA删除数据库YGGL参照2.2

4、节删除数据库方法删除数据库YGGL。3.使用PL/SQL手工创建数据库（选做）按照上述要求创建数据库YGGL。以下给出创建数据库YGGL所用到的CREATE DATABASE语句： Create database YGGL MAXINSTANCES 1 MAXLOGHISTORY 1 MAXLOGFILES 5 MAXMEMEBERS 5 MAXDATAFILES 100 DATAFILE D:oracleoradataygglsystem01.dbf SIZE 325M reuse AUTOEXTEN ON NEXT 1024K MAXSIZE UNLIMITED CHARACTER SET

5、 ZHS16GBK NATIONAL CHARACTER SET AL16UTF16 LOGFILE GROUP 1 (d:oracleoradataygglredo01.log) SIZE 100M GROUP 2 (d:oracleoradataygglredo02.log) SIZE 100M GROUP 3 (d:oracleoradataygglredo03.log) SIZE 100M default temporary tablespace temp tempfile D:oracleoradatayggltemp01.dpf extent management local un

6、iform size 10M undo tablespace undo_ts datafile D:oracleoradatayggltemp01.dbf size 150M reuse autoextend on next 10240k maxsize unlimited;4.在OEM中分别创建表Employees、表Departments和表Salary 在OEM目录中，选择表单击鼠标左键，在小狐仙的界面中单击“创建”按钮，进入表创建界面，在各个选项卡上输入表Employees各字段信息、约束条件、分区和存储情况等，单击确定按钮，创建表Employees即可完成。5. 在OEM中删除创建的

7、表Employees、表Departments和表Salary 在OEM界面中选中数据库YGGL的表Employees,单击鼠标右键，在快捷菜单中选择“删除”，即可删除表Employees了。按同样的操作过程删除表Departments和表Salary。6.使用PL/SQL语句创建表Employees、表Departments和表Salary在SQL*plus中输入如下语句：create table Employees(EmployessID char(6) not null,Name char(10) not null,Birthday date not null,Sex number(1)

8、 not null,Address char(20) not null,Zip char(6) null,PhoneNumber char(12) null,EmailAddress char(2) null,DepartmentID char(3) not null,);按回车键，执行上述语句，即可创建表Employees。 按同样的操作过程创建表Departments和表Salary，并在OEM中查看结果。实验二 表数据插入、修改、删除目的和要求（1）学会使用PL/SQL语句对数据库表进行插入、修改和删除数据操作。（2）了解数据更新操作时要注意数据完整性。（3）了解PL/SQL语句对表数据

9、操作的灵活控制功能。实验准备 首先要了解对表数据的插入、修改、删除都属于表数据的更新操作。对表数据的操作可以在企业管理器中进行，也可以在PL/SQL语句实现。 其次要掌握PL/SQL语句中用于对表数据进行插入、修改和删除的命令分别是insert update和delete（或trancete table）。 要特别注意在执行插入、删除、修改等数据更新操作时，必须保证数据的完整性。 此外，还要了解使用PL/SQL语句在对表数据进行插入、修改和删除时，比在OEM中操作表数据要更为灵活，功能更强大。实验内容 分别使用OEM命令和PL/SQL语句，在实验1建立的数据库库YGGL的表Employees、

10、表Departments和表Salary中插入多行数据记录，然后修改和删除一些记录，使用PL/SQL命令进行有限制的修改和删除。 （1）使用PL/SQL语句分别向YGGL数据库的表Employees、表Departments和表Salary中插入一行记录。 启动SQL*plus界面 输入以下PL/SQL语句，按回车键执行语句：insert into Employeesvalues(011112,罗林,chartodate(1973-06-26,yyyymmdd),1,解放路100号,210002,4055663,null,5);insert into Departmentsvalues(2,人

11、力资源,null);insert into Salayvalues(011112,1200.09,50 );注意：在OEM中分别打开YGGL数据库的表Employees、表Departments和表Salary，观察数据变化。（2）使用PL/SQL命令修改表Salary中的某个记录的字段值启动SQL*plus界面 输入以下PL/SQL语句，按回车键执行语句：UPDATE Salary set InCome=2890 where EmployeesID=011112;按回车键，执行上述语句，将编号为011112的职工收入改为2890.，在OEM中打开YGGL数据库的Salary表，观察数据变化。

12、（3）修改表Employees和表Departments的记录值，仍要注意完整性。操作过程同（2）。（4）使用PL/SQL命令修改表Salary中的所有记录的字段值启动SQL*plus界面 输入以下PL/SQL语句：update Salaryset InCome= InCome+100;按回车键，执行上述语句，将所有的员工工收入增加100。 可见，使用PL/SQL语句操作表数据比在OEM中操作数据更为灵活，输入一下PL/SQL语句，观察数据变化。 select * from Salary;（5）使用TRANCATE TABLE Salary语句删除表中所有行。在SQL*plus界面中输入以下P

13、L/SQL语句：TRANCATE TABLE Salary;按回车键，执行上述语句，将删除Salary表中的所有行。注意：实验时一般不是轻易的进行这个表的操作，后面实验还要用到这些数据，如要试验该命令的效果，可建一个临时表，输入少量数据进行。实验三 索引和完整性目的和要求（1）掌握索引的使用方法（2）掌握数据完整性的概念及方法（3）掌握各种数据完整性的实现方法实验准备（1）了解索引的作用和分类（2）掌握索引的创建（3）理解数据完整性的概念及分类（4）了解各种数据完整性的实现方法实验内容1.建立索引 对YGGL数据库的Employees表中的DepartmentID列建立索引。在SQL*plus

14、编辑界面输入以下语句并执行：create index PK_XS_BAK on Employees(DepartmentID)tablespace users pctfree 48 initrans 10 maxtrans 100storage(inital 64k next 64k minextents 5 maxxtents 20 pctincrease 10 freelists 1 freelist groups 1)parallel(degree default)logging nosort;2.实现域完整性 为YGGL数据库的Employees表中PhoneNumber建立check

15、约束。在SQL*plus编辑界面输入如下语句并执行： Alter table Employees add (constraint ch_phone check(PhoneNumber between 0 and 9);3.实现实体完整性实现实体完整性的步骤如下。（1）使用SQL语句创建表Departments,DepartmentID为主键。在SQL*plus编辑界面输入如下程序并执行：create table Departments（ DepartmentID char(3),DepartmentName char(20) not null,Note char(16) null,contra

16、int pk_Departments primary key(DepartmentID)）;(2)为表Departments的DepartmentName建立唯一索引 alter table Departments add(constraint un_Departments(DepartmentName)）;4.实现参照完整性为Employees 表中的DepartmentID建立外键 alter table Employees add(constraint fk_Employees foreign key(DepartmentID) references Departments(Depart

17、mentID);实验四 数据库的查询和视图目的和要求（1）掌握select语句的基本用法（2）掌握子查询的表示（3）掌握连接查询的表示（4）掌握数据汇总的方法（5）掌握select语句group by 子句的用法和使用方法（6）掌握select语句order by 子句的用法和使用方法（7）掌握视图的使用方法实验准备（1）了解select语句的基本语法格式和执行方法（2）了解子查询的表示方法，连接查询的数据汇总的方法（3）了解select语句的Group By 子句的用法和使用方法（4）了解select语句order by 子句的用法和使用方法（5）了解视图的作用和视图的创建方法（6）了解视图

18、的使用方法实验内容1.select语句的基本使用select语句的基本使用方法有以下几个方面。（1）对于实验2给出的数据表的结构，查询每一个雇员的所有数据。在SQL*PLUS界面输入如下的语句并执行：select * from Employees;思考练习：用select语句查询Departments表和Salary表的所有数据（2）查询每一个雇员的电话和地址在SQL*PLUS界面输入如下的语句并执行：select Address,PhoneNumber from Employees;思考练习：用select语句查询Departments表和Salary表的一列或若干列。（3）查询Employ

19、eeID为000001的雇员的地址和电话在SQL*PLUS界面输入如下的语句并执行：select Address,PhoneNumber from Employees where EmployeeID=000001;思考练习：用select语句查询Departments表和Salary表满足指定条件的一列或若干列。（4）查询Employees表中女雇员的电话和地址，使用AS子句将结果中各列的标题分别指定为地址、电话。在SQL*PLUS界面输入如下的语句并执行：select Address as 地址,PhoneNumber as电话 from Employees where sex=0;注意：

20、使用as子句可指定目标列的标题（5）计算每个雇员的实际收入在SQL*PLUS界面输入如下的语句并执行：select EmployeeID ,InCome-OutCome as 实际收入 from Salary;(6)找出所有姓王的雇员和部门号。在查询分析器界面输入如下的语句并执行：select DepartmentID fromEmployees where Name like 王%;思考练习：找出所有地址中含有“中山”的雇员的号码及部门号。（7）找出所有收入在2000-3000元之间的雇员的号码在SQL*PLUS界面输入如下的语句并执行： select EmploeeID from Sala

21、ry where Incom between 2000 and 3000;思考练习：找出所有在部门1或2工作的雇员的号码。注意：在select语句中like 、between and、in、not谓词的作用。2.子查询的使用子查询的使用在以下几个方面。（1）查找在财务部工作的雇员的情况。在SQL*PLUS界面输入如下的语句并执行：select * from Employees where DepartmentID=(select DepartmentID from Departments where DepartmentName=财务部);思考练习：用子查询的方法查找所有收入在2500元以下的

22、雇员的情况。（2）查找财务部年龄不低于研发部雇员年龄的雇员的姓名select Name from Employees where DepartmentID in(select DepartmentID from Departments where DepartmentName=财务部 ) and Birthday ! ALL(select Birthday from Employees where DepartmentID in (select DepartmentID from Departments where DepartmentName=研发部);思考练习：用子查询的方法查找研发部比所

23、有财务部雇员收入都高的雇员的姓名。（3）查找比所有财务部的雇员收入都高的雇员的姓名select namefrom Employees where EmployeeID in (select EmploteeID from Salary where InComeALL (select InCome from Salary where EmployeeID in (select EmployeeID from Employees where DepartmentID= (select DepartmentID from Departments where DepartmentName=财务部) )

24、 ) );思考练习：用子查询的方法查找所有年龄比研发部雇员年龄都大的雇员的姓名。3.连接查询的使用连接查询的使用有以下两个方面（1）查询每一个雇员的情况及其薪水情况。在SQL*PLUS界面输入如下的语句并执行：select Employees.*,Salary.* from Employees,Salary where Employees.EmployeeID=Salary.EmployeeID;思考练习：查询每一个雇员的情况和部门的情况。(2)查找财务部收入在2200以上的雇员的姓名及其薪水情况。在SQL*PLUS界面输入如下的语句并执行：select Name,InCome,OutCome

25、from Employes,Salary,Departmentswhere Employees.EmployeeID=Salary.EmployeeID and Employees.DepartmentID=Departments.DepartmentID and DepartmentName=财务部 and InCome2000;思考练习：查询研发部在1966年以前的雇员姓名及其薪水情况4.数据汇总数据汇总的内容有以下几个方面。（1）求财务部雇员的平均收入。在SQL*PLUS界面输入如下的语句并执行：select AVG（InCome） as 财务部平均收入 from Salary wher

26、e EmployeeID in (select EmployeeID from Employees where departmentID=(select DepartmentID from Departments where DepartmentName=财务部);思考练习：查询财务部雇员的最高和最低收入。（2）求财务部雇员的平均实际收入在SQL*PLUS界面输入如下的语句并执行：select AVG（InCome-OutCome） as 财务部实际平均收入 from Salary where EmployeeID in (select EmployeeID from Employees wh

27、ere departmentID=(select DepartmentID from Departments where DepartmentName=财务部);思考练习：查询财务部雇员的实际最高和最低收入。（3）求财务部雇员的总人数在SQL*PLUS界面输入如下的语句并执行：select count(EmployeeID)from Employeeswhere DepartmentID=(select DepartmentID from Departments where DepartmentName=财务部);思考练习：统计财务部收入在2500元以上的雇员的人数。5.GROUP BY 、O

28、RDER BY子句的使用GROUP BY 、ORDER BY子句的使用有以下两个方面（1）求各部门的雇员数在SQL*PLUS界面输入如下的语句并执行：select count（EmployeeID） from Employees Group By DepartmentID思考练习：统计各部门收入在2000元以上雇员的人数。（2）将各雇员的情况按收入由低到高排列在SQL*PLUS界面输入如下的语句并执行：select Employees.*,Salary.*from Employees,Salarywhere Employees.EmployeeID=Salary.EmployeeIDOrder

29、 By InCome;思考练习：将各雇员的情况按出生时间先后排列6.使用视图使用视图的方法和步骤如下。（1）创建视图a.限制查看雇员的某些情况。在SQL*PLUS界面输入如下的语句并执行：Create or replace view cx_employeesasselect EmployeeID,Name,Birthday,Sex,DepartmentIDfrom Employees;b.各部门经理只能查找本部门的雇员的薪水情况，如财务部经理查看自己部门的雇员姓名及其薪水情况。在SQL*PLUS界面输入如下的语句并执行：create or replace view cx_salaryassel

30、ect Name,InCome,OutComefrom Employees,Salary,Departmentwhere Employees.EmployeeID=Salary.EmployeeID and Employees.DepartmentID=Departments.DepartmentID and DepartmentName=财务部;(2)使用视图a.查询财务部雇员信息。在SQL*PLUS界面输入如下的语句并执行：select * from cx_employees;b.查询财务部雇员薪水情况。在SQL*PLUS界面输入如下的语句并执行：select * from cx_salary;c.向Employees表中插入一条记录。 在SQL*PLUS界面输入如下的语句并执行：insert into cx_employees values(510888,张无忌,chartodate(1978-08-23,YYYYMMDD),1,3);d.张无忌从经理办公室转到市场部。在SQL*PLUS界面输入如下的语句并执行：update cx_employees set DepartmentID=5 where Name=张无忌;e.把张无忌从表Employees中删除。在SQL*PLUS界面输入如下的语句并执行：delete f