太原理工大学-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表结构列名数据类型长度是否允许为空值说明EmployeeIDChar6员工编号，主键NameChar10姓名BirthdayDate出生日期SexNumber1性别AddressChar20地址ZipChar6邮编PhoneNumberChar12电话号码DepartmentChar3员工部门号、外键表T1.2 Departments表结构列名数据类型长度是否允许为空值说明DepartmentIDChar3部门编号、主键DepartmentNameChar20部门号NoteVarchar2100备注表T1.3 Salary表结构列名数据类型长度是否允许为空值说明EmplyeeIDChar6员工编号、主键InComeNumber8,2收入OutComeNumber8,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-231中山路32-1-508210003833556682010008伍容华1976-03-281北京东路100-2210001833213211表T2.2 Departments表数据样本部门号部门名称备注部门号部门名称备注1财务部NULL4研发部NULL2人力资源部NULL5市场部NULL3经理办公室NULL表T2.3 Salary表数据样本编号收入支出编号收入支出0000012100.8123.091089913259.98281.520100081582.6288.030200102860.0198.01022012568.88185.650200182347.68180.0三、 主要仪器设备WINDOWS XP/WINDOWS 7Oracle 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, 北京西路3-7-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给出的数据库表结构，查询每个雇员的所有数据 在SQL*Plus窗口或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 EMPLOYEESWHERE Department_ID=(SELECT Department_IDFROM DepartmentsWHERE DepartmentName=财务部);（2） 查询财务部年龄不低于所有研发部雇员年龄的雇员的姓名/* 查找财务部年龄不低于所有研发部雇员年龄的雇员的姓名*/SELECT * FROM EMPLOYEES WHERE department_id IN ( SELECT DEPARTMENT_ID FROM departments WHERE department_name = 财务部 ) AND birthday = 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_employeesAS SELECT EmployeeID,Name,Birthday,Sex,DepartmentID FROM Employees; b、限制各部门经理只能查找本部门雇员的薪水情况，如限制财务经理 想查看自己部门雇员姓名及其薪水详情/* 创建财务部视图*/CREATE OR REPLACE VIEW CX_SALARYAS 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