太原理工大学-oracle-大型数据库实验报告.doc

本科实验报告

课程名称： 大型数据库系统

实验项目： 创建数据库和表

实验地点： 多学科楼4506

专业班级：软件工程0901 学号：200

学生姓名：

指导教师：

2012年 4 月 17 日

一、 实验目的和要求
1. 了解数据库的结构以及一些基本概念。
2. 了解表的结构特点。
3. 了解Oracle 11g的基本数据类型。
4. 学会使用DBCA创建数据库。
5. 学会使用界面方式创建表。
6. 学会使用SQL语句手工创建数据库。
7. 学会使用SQL语句创建表。
二、 实验内容和原理
1. 能够创建数据库的用户必须是系统管理员，或是被授权使用CREATE DATABASE语句的用户。
2. 创建数据库必须要确定数据库名、所有者（即创建数据库的用户）、数据库大小、SGA分配和存储数据库的文件。
3. 确定数据库包含哪些表以及所包含的各表的结构，还要了解Oracle 11g的常用数据类型，以创建数据库的表。
4. 创建企业管理的员工管理数据库YGGL，包含Employees（员工自然信息 表、Departments（部门信息）表和Salary（员工薪水情况）表。各表的结构如下所示。
表T1.1 Employees表结构
列名
数据类型
长度
是否允许为空值
说明
EmployeeID
Char
6
×
员工编号，主键
Name
Char
10
×
姓名
Birthday
Date

×
出生日期
Sex
Number
1
×
性别
Address
Char
20
√
地址
Zip
Char
6
√
邮编
PhoneNumber
Char
12
√
电话号码
Department
Char
3
×
员工部门号、外键
表T1.2 Departments表结构
列名
数据类型
长度
是否允许为空值
说明
DepartmentID
Char
3
×
部门编号、主键
DepartmentName
Char
20
×
部门号
Note
Varchar2
100
√
备注
表T1.3 Salary表结构
列名
数据类型
长度
是否允许为空值
说明
EmplyeeID
Char
6
×
员工编号、主键
InCome
Number
8,2
×
收入
OutCome
Number
8,2
×
支出

三、 主要仪器设备
Windows XP/7、oracle 10g/11g
四、 操作方法与实验步骤
1. 利用DBCA创建数据库
1) 数据库名称为YGGL，它的全局数据库名称为YGGL。
2) 控制文件三个，存放路径为……………………，名称分别为CONTROL01.CR、CONTROL02.CR和CONTROL03.CR。
3) 重做日志文件三个，大小为100MB，存放路径为：………………，名称分别为redo01.log、redo02.log和redo03.log。
4) 创建临时表空间temp01.dpf。
5) 数字字符集为ZHS16GBK，国家字符集为AL16UTF16。

6) 数据块大小为4KB。
进入DBCA，根据其提示逐步完成数据库的创建工作。
实际情况，我以创建表空间代替。
2. 利用DBCA删除数据库（实际不操作）
3. 在OEM和利用sql语句分别创建表
下面列出建表语句：
/**
创建Departments表
*/
create table Departments(
department_id char(3),
department_name varchar2(20) not null,
note varchar2(100),
constraint departments_pk primary key(department_id)
)
tablespace yggl;

/**
创建Salary表
*/
create table Salary(
employee_id char(6),
income number(8, 2) not null,
outcome number(8, 2) not null,
constraint Salary_pk primary key(employee_id),
constraint employee_id_foreign_key
foreign key(employee_id) references employees(employee_id)
)
tablespace yggl;
五、 实验结果与分析
创建表空间：

以下为创建表，因为自己的命名习惯，对其中部分名称进行了修正，另外，一些数据的数据类型该用更合适的数据类型。
创建employees表：

利用sql语句建表也全部成功。
六、 讨论、心得（可选）
本次试验考察内容基本为数据定义语言，有数据库的创建（因为以前已建过了，所以这次我用名称空间代替），表的创建（图形化操作与sql语句操作）。差不多就这样子，通过这次试验大概可以掌握oracle数据库的基本操作，主要有助于对其环境的熟悉

本科实验报告

课程名称： 大型数据库系统

实验项目： 表数据插入、修改和删除

实验地点： 多学科楼4506

专业班级：软件工程0901 学号：20090
学生姓名：

指导教师：

2012年 4 月 19 日

一、 实验目的和要求
1. 学会使用PL/SQL语句对数据库表进行插入、修改和删除数据操作。
2. 学会使用SQL Developer对数据库表进行插入、修改和删除数据的操作。
3. 了解数据更新操作时要注意数据完整性。
4. 了解PL/SQL语句对表数据操作的灵活控制功能。
二、 实验内容和原理
1. 了解对表数据的插入、删除、修改都属于表数据的更新操作。对表数据的操作可以在SQL Developer中进行，也可以由PL/SQL语句实现。
2. 掌握PL/SQL语句中用于对表数据进行插入、修改和删除的命令分别是INSERT、UPDATE和DELETE（或TRANCATE TABLE）。
3. 在执行插入、删除、修改等数据更新操作时，必须保证数据的完整性。
4. 使用PL/SQL语句在对表数据进行插入、修改及删除时，比在OEM中操作表数据更为灵活，功能更强大。
在实验1中，用于实验的YGGL数据库中的三个表已经建立，现在要将各表的样本数据添加到表中。样本数据如表T2.1、表T2.2和表T2.3所示。

表T2.1 Employees表数据样本
编号
姓名
出生日期
性别
住址
邮编
电话号码
部门号
000001
王林
1966-01-23
1
中山路32-1-508
210003
83355668
2
010008
伍容华
1976-03-28
1
北京东路100-2
210001
83321321
1

表T2.2 Departments表数据样本
部门号
部门名称
备注
部门号
部门名称
备注
1
财务部
NULL
4
研发部
NULL
2
人力资源部
NULL
5
市场部
NULL
3
经理办公室
NULL

表T2.3 Salary表数据样本
编号
收入
支出
编号
收入
支出
000001
2100.8
123.09
108991
3259.98
281.52
010008
1582.62
88.03
020010
2860.0
198.0
102201
2568.88
185.65
020018
2347.68
180.0

三、 主要仪器设备
WINDOWS XP/WINDOWS 7
Oracle 10g/11g
四、 操作方法与实验步骤
分别使用SQL Developer和PL/SQL语句，在实验1建立的数据库YGGL的表Employees、表Departments和表Salary中插入多行数据记录，然后修改和删除一些记录。使用PL/SQL命令进行有限制的修改和删除。
(一) 使用SQL Developer操作数据
启动SQL Developer，展开yggl_ora连接，单击”Employees”表，在左边窗口中选择”Data”选项卡。在此窗口中，单击”Insert row”按钮，表中将增加一个新行，在新行中双击一列空白处后输入新数据，输完后单击”Commit Changes”按钮，将数据保存到数据库中。修改数据的方法和添加数据类似，如果要删除一行数据，选中该行数据，单击”Delete Selected Row(s)”按钮，之后该行的行号前会显示一个“——”号，删除后单击”Commit Changes”按钮保存。
(二) 使用PL/SQL命令操作数据
1. 使用PL/SQL语句分别向YGGL数据库的表Employees、表Departments和表Salary中插入插入一行记录。
在启动SQL*Plus窗口或SQL Developer的代码编辑窗口中，输入以下PL/SQL语句并执行：
一下为对3个表的数据的所有插入语句：
/**
向DEPARTMENTS表中插入数据样本
*/
insert into DEPARTMENTS
values('001', '财务部', null);
insert into DEPARTMENTS
values('002', '人力资源部', null);
INSERT INTO Departments
VALUES('003','经理办公室',NULL);
INSERT INTO Departments
VALUES('004','研发部',NULL)
INSERT INTO Departments
VALUES('005','市场部',NULL);
commit;

/**
向employee表中插入数据样本
*/

insert into employees
values('000001', '王林', to_date('1966-01-23', 'YYYY-MM-DD'), 1,
'中山路 32-1-508', '210003', '83355668', '002');
INSERT INTO Employees
VALUES('010008','伍容华',TO_DATE('19760328','YYYYMMDD'),1,
'北京东路100-2','210001','83321321','001');
insert into employees
values('020010', '王向荣', to_date('1982-12-19', 'YYYY-MM-DD'), 1,
'四牌楼 10-0-108', '210006', '83792361', '001');
INSERT INTO Employees
VALUES('020018','李丽',TO_DATE('19600723','YYYYMMDD'),0,
'中山东路102-2','210002','83413301', '001');
INSERT INTO Employees
VALUES('102201','刘明',TO_DATE('19721018','YYYYMMDD'),1,
'虎距路100-2','210013','83606608','005');
INSERT INTO Employees
VALUES('102208','朱俊',TO_DATE('19650928','YYYYMMDD'),1,
'牌楼巷5-3-106','210004','84708817','005');
INSERT INTo Employees
VALUES('108991','钟敏',TO_DATE('19790810','YYYYMMDD'),0,
'中山路10-3-105','210003','83346722','003');
INSERT INTO Employees
VALUES('111006','张石兵',TO_DATE('19741001','YYYYMMDD'),1,
'解放路34-1-203','210010','84563418','005');
INSERT INTO Employees
VALUES('210678','林涛'TO_DATE('19770402','YYYYMMDD'),1,
'中山北路24-35','210008','83467336','003');
INSERT INTo Employees
VaLUES('302566','李玉珉',TO_DATE('19680920','YYYYMMDD'),1,
'热和路209-3','210001','58765991','004');
INSERT INTO Employees
VALUES('308759','叶凡',TO_DATE('19781118','YYYYMMDD'),1,
'北京西路37-52','210002','83308901','004');
INSERT INTO Employees
VALUES('504209','陈琳琳',TO_DATE('19690903','YYYYMMDD'),0,
'汉中路120-4-12','210018','84468158','004');
commit;

/**
向Salary表中插入数据样本
*/
insert into Salary
values('000001', 2100.8, 123.09);
INSERT INTO Salary
VALUES('010008'1582.62,88.03);
INSERT INTO Salary
VALUES('102201',2569.88,185.65);
INSERT INTO Salary
VALUES('111006',1987.01,79.58);
INSERT INTO Salary
VALUES('504209',2066.15,108.0);
INSERT INTO Salary
VALUES('302566',2980.7,210.2);
INSERT INTO Salary
VALUES('108991',3259.98,281.52);
INSERT INTO Salary
VALUES('020010',2860.0,198.0);
INSERT INTO Salary
VALUES('020018',2347.68,180.0);
INSERT INTO Salary
VALUES('308759',2531.98,199.08);
INSERT INTO Salary
VALUES('210678',2240.0,121.0);
INSERT INTO Salary
VALUES('102208',1980.0,100.0);
commit;

2. 使用PL/SQL命令修改表Salary中的某个记录的字段值。
语句:
/**
update SALARY表
**/
UPDATE SALARY
SET INCOME = 2890
WHERE EMPLOYEE_ID = '000001';
COMMIT;
按回车键，执行上述语句，将编号为000001的职工收入改为2890.在OEM中打开YGGL数据库的Salary表，观察数据变化。
3. 使用PL/SQL语句修改表Salary中的所有记录的字段值。
在SQL*Plus界面中输入以下PL/SQL语句：
语句
/**
* 将其所有行的IMCOME加100
**/
UPDATE SALARY
SET INCOME = INCOME + 100;
COMMIT;
按回车键，执行上述语句，将所有职工的收入增加100。
可见，使用PL/SQL语句操作表数据比在SQL Developer中操作表数据更为灵活。输入以下PL/SQL语句，观察数据变化。
SELECT* FROM Salary;
4. 使用DELETE语句删除Salary表中一行记录。
/**
* 删除salary表中的一行记录
**/
DELETE FROM SALARY
WHERE employee_id = '010008';
COMMIT;
5. 使用TRANCATE TABLE语句删除表中所有行。
在SQL*Plus界面中输入以下PL/SQL语句：
/**
*清空表
**/
delete from Salary;
delete from employees;
delete from departments;

TRUNCATE TABLE SALARY;
五、 实验结果与分析
按上述过程操作，试验结果均满足预期要求。
六、 讨论、心得（可选）
本次试验主要考察对表的数据的基本更新操作，即dml语言。通过对表的增删改操作熟悉对应的sql语句以及OEM操作。

本科实验报告

课程名称： 大型数据库系统

实验项目： 数据库的查询和视图

实验地点： 多学科楼506

专业班级：软件工程0901 学号：200900

学生姓名：

指导教师：

2012年 4月21日

一、 实验目的和要求
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. 了解视图的使用方法
三、实验环境与设备
Win7、Oracle 11g
四、操作方法与实验步骤
1、SELECT语句的基本使用
（1）对于实验2给出的数据库表结构，查询每个雇员的所有数据
在SQLPlus窗口或SQL Developer中输入语句并执行
SELECT * FROM Employees;
SELECT * FROM Departments;
SELECT * FROM Salary;
（2）查询每个雇员的地址和电话
/**
* 查询每个雇员的地址和电话
**/
SELECT ADDRESS, PHONE_NUMBER
FROM EMPLOYEES;
（3）查询EmployeeID为000001的雇员的地址和电话
SELECT Address,PhoneNumber
FROM Employees
WHERE Employee_ID='000001';
（4）查询Employees表中所有女雇员的地址和电话，使用AS子句将结果中各列的标题分别指定为地址和电话
SELECT Address AS 地址,PhoneNumber AS 电话
FROM Employees
WHERE sex=0;
/**
* 查询每个女性雇员的地址和电话，并对列名重新命名
**/
SELECT ADDRESS as 地址, PHONE_NUMBER 电话
FROM EMPLOYEES
WHERE SEX = '0';
（5）计算ID号以10开头的雇员的实际收入
/**
* 计算ID号以10开头的雇员的实际收入
**/
SELECT employee_id， INCOME - OUTCOME AS 实际收入
FROM SALARY
WHERE EMPLOYEE_ID LIKE '10%';

2、 子查询的使用
（1） 查找在财务部工作的雇员的情况
SELECT * FROM EMPLOYEES
WHERE Department_ID=
(
SELECT Department_ID
FROM Departments
WHERE DepartmentName='财务部'
);
（2） 查询财务部年龄不低于所有研发部雇员年龄的雇员的姓名
/**
* 查找财务部年龄不低于所有研发部雇员年龄的雇员的姓名
**/
SELECT * FROM EMPLOYEES
WHERE department_id IN (
SELECT DEPARTMENT_ID FROM departments
WHERE department_name = '财务部'
)
AND
birthday <= ALL (
SELECT BIRTHDAY FROM EMPLOYEES
WHERE DEPARTMENT_ID IN (
SELECT DEPARTMENT_ID FROM departments
WHERE department_name = '研发部'
)
);
（3） 查找比所有财务部的雇员收入都搞得雇员的姓名
/**
* 查找比所有财务部的雇员收入都要高的雇员的姓名
**/
SELECT NAME FROM EMPLOYEES
WHERE EMPLOYEE_id IN (
SELECT EMPLOYEE_ID FROM SALARY
WHERE INCOME >= ALL (
SELECT INCOME FROM SALARY
WHERE EMPLOYEE_ID IN (
SELECT EMPLOYEE_ID FROM EMPLOYEES
WHERE DEPARTMENT_ID = (
SELECT DEPARTMENT_ID FROM DEPARTMENTS
WHERE DEPARTMENT_NAME = '财务部'
)
)
)
)
3、连接查询的使用
（1）查询每个雇员的情况以及其薪水的情况
SELECT Employees.*,Salary.*
FROM Employees,Salary
WHERE Employees.Employee_ID=Salary.Employee_ID;
（2）查找财务部收入在2200元以上的雇员的姓名及其薪水详情
/**
* 查找财务部收入在2200元以上的雇员姓名及其薪水详情
**/
SELECT name, INCOME, OUTCOME
FROM EMPLOYEES, SALARY DEPARTMENTS
WHERE employees.employee_id = salary.employee_id
AND
employees.department_id = departments.department_id
AND
departments.department_NAME = '财务部'
AND
INCOME > 2200;数据汇总
（1） 求财务部雇员的平均收入
/**
* 查找财务部雇员的平均收入
**/
SELECT AVG(INCOME) FROM SALARY, EMPLOYEES, DEPARTMENTS
WHERE salary.employee_id = employees.employee_id
AND
employees.department_id = departments.department_id
AND
departments.department_name = '财务部';
（2） 求财务部雇员的平均实际收入
SELECT AVG(InCome-OutCome) AS 财务部平均实际收入
FROM Salary
WHERE Employee_ID IN
(SELECT Employee_ID FROM Employees
WHERE Department_ID=
(SELECT Department_ID FROM Departments
WHERE Department_Name='财务部'));
（3） 求财务部雇员的总人数
SELECT COUNT(Employee_ID) FROM Employees
WHERE Department_ID=
(SELECT Department_ID FROM Departments
WHERE Department_Name='财务部');
5、GROUP BY和ORDER BY子句的使用
（1）求各部门的雇员数
/**
* 求各部门的雇员数
**/
SELECT DEPARTMENT_NAME, COUNT(*) FROM EMPLOYEES, departments
WHERE employees.department_id = departments.department_id
GROUP BY DEPARTMENTS.department_NAME;
（2）将各部门的情况按收入由低到高排列
/**
* 将各雇员的情况按收入由低到高排序
**/
SELECT EMPLOYEES.*, SALARY.INCOME, salary.outcome
FROM EMPLOYEES, SALARY
WHERE employees.employee_id = salary.employee_id
ORDER BY INCOME;
6、 使用视图
（1）创建视图
a、限制查看雇员的实际情况
CREATE OR REPLACE VIEW cx_employees
AS
SELECT EmployeeID,Name,Birthday,Sex,DepartmentID
FROM Employees;
b、限制各部门经理只能查找本部门雇员的薪水情况，如限制财务经理 想查看自己部门雇员姓名及其薪水详情
/**
* 创建财务部视图
**/
CREATE OR REPLACE VIEW CX_SALARY
AS
SELECT NAME, INCOME, OUTCOME FROM EMPLOYEES, SALARY, DEPARTMENTS
WHERE employees.employee_id = salary.employee_id
AND employees.department_id = departments.department_id
AND department_name = '财务部';
（2）使用视图
/**
* 查询财务部雇员薪水情况
**/
SELECT * FROM CX_SALARY;

五、实验结果与分析
查询employees表的全部数据

查询每个雇员的地址和电话

查询每个女性雇员的地址和电话，并对列名重新命名

计算ID号以10开头的雇员的实际收入

查找财务部年龄不低于所有研发部雇员年龄的雇员的姓名

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

查找财务部收入在2200元以上的雇员姓名及其薪水详情

查找财务部雇员的平均收入

求各部门的雇员数

将各雇员的情况按收入由低到高排序

查询财务部雇员薪水情况

六、讨论、心得
本实验考察对表的查询操作，算是sql语句中最常用的操作，这也是我们每个学生应该必须掌握的基本操作，此后是关于视图的部分，它是一个很有效的功能，能够有效限制各用户只对自己有权操作的数据进行操作。

本科实验报告

课程名称： 大型数据库系统

实验项目： 索引和完整性

实验地点： 多学科楼506

专业班级：软件工程0901 学号：2009
学生姓名：

指导教师：

2012年 4月21日

一、实验目的和要求
目的：
（1）掌握索引的使用方法
（2）掌握数据完整性的概念及分类
（3）掌握各种数据完整性的实现方法
要求：
（1）了解索引的作用与分类
（2）掌握索引的创建
（3）理解数据完整性的概念及分类
（4）了解各种数据完整性的实现方法
二、实验内容和原理
1、建立索引
对YGGL数据库中Employees表的DepartmentID列建立索引
/**
* 在Employees表的Departm_ID 列建立索引
**/
create index EMP_DEID ON EMPLOYEES(DEPARTMENT_ID)
TABLESPACE USERS PCTFREE 48 INITRANS 10 MAXTRANS 100
STORAGE (INITIAL 64K NEXT 64K MINEXTENTS 5 MAXEXTENTS 20
PCTINCREASE 10 FREELISTS 1 FREELIST GROUPS 1)
PARALLEL (DEGREE DEFAULT);
2、 实现域完整性
/**
* 在Employees表的phone_number 列上建立check约束
**/
ALTER TABLE EMPLOYEES
ADD( CONSTRAINT CH_PHONE CHECK(PHONE_NUMBER BETWEEN '0' AND '9'));
3、 实现实体完整性
（参见实验1）
4、 实现参照完整性
（参见实验1）

三、主要仪器设备
Win7、Oracle 11g
四、实验结果与分析（必填）
在Employees表的Departm_ID 列建立索引

在Employees表的phone_number 列上建立check约束

五、讨论、心得
本次实验首先是对索引的建立，它是一种可以提升用户查询效率的机制，在这一部分，我有注意到建立索引时是需要指定模式名的，似乎不同的用户查询同一张表格可能会用到不同的索引，不知道这样理解正不正确。
然后就是关于完整性的部分，由于在实验一建立表的时候，我就已经安照要求建立好了完整性约束，所以这里没有再重复做。

本科实验报告

课程名称： 大型数据库系统

实验项目： PL/SQL编程

实验地点： 多学科楼506

专业班级：软件工程1 学号：2002

学生姓名：

指导教师：

2012年 4月21日

一、 实验目的与要求
（1） 进一步巩固第2章~第4章所学内容
（2） 掌握变量的分类与使用
（3） 掌握各种运算符的使用
（4） 掌握各种控制语句的使用
（5） 掌握系统函数及用户自定义函数的使用
二、 实验内容和原理
（1） 了解PL/SQL支持的各种基本数据类型
（2） 了解PL/SQL各种运算符、控制语句的功能及使用方法
（3） 了解系统函数的调用方法
（4） 了解用户自定义函数时用的一般步骤
三、 主要仪器设备
Windows XP/7、oracle 10g/11g
四、 操作方法与实验步骤
1． 条件结构的使用
在SQL*Plus的编辑窗口中分别输入【例6.5】、【例6.6】、【例6.7】和例【6.8】程序并执行。观察结果。
2． 循环结构的使用
在SQL*Plus的编辑窗口中分别输入【例6.9】、【例6.10】、【例6.11】和例【6.12】程序并执行。观察结果。
3． 选择和跳转语句
在SQL*Plus的编辑窗口中分别输入【例6.13】和【例6.14】程序并执行。观察结果。
4． 自定义函数的使用
（1） 定义一个函数实现如下功能。对于给定的DepartmentID值，查询该值在Departments表中是否存在，若存在返回0，否则返回-1。
语句：
/**
* 给定一个Department_id值，查询其在Departments表中是否存在，若存在返回0，否则返回-1.
**/
CREATE OR REPLACE FUNCTION CHECK_ID (P_DEPARTMENT_ID IN