大型数据库实验指导书-陶立新-软件工程14级.doc

大型数据库应用实验指导书 适用专业年级：软件工程14级 目录实验一 创建表和修改表2实验二 数据库的查询和视图5实验三 视图、索引和完整性9实验四 PL/SQL编程11实验五 存储过程和触发器的使用15实验一 创建表和修改表一、实验目的 1、熟悉SQL语言交互式使用工具SQL*PLUS的使用方法2、熟悉SQL*PLUS常用命令的功能和使用方法3、熟悉工具SQL DEVELOPER的使用4、掌握SQL语言中简单增删改语句的功能和使用方法二、实验环境1、硬件设备：计算机局域网，服务器1台，客户机100台2、软件系统：Windows操作系统， Oracle11g or 12c，Oracle客户端工具。三、实验内容1、SQL*PLUS的使用(1) 进入（启动）SQL*PLUS在Windows环境下,双击SQL*PLUS图标或从程序组找SQL*PLUS ，出现登录窗口 输入正确的数据库用户名、密码和连接字符串后，若连接数据库成功，则会出现如下提示符：SQL(2) 退出 SQL*PLUS SQLexit 则退回到Windows桌面。 (3) 创建表结构：Create table 创建部门登记表dept和雇员登记表emp的结构 SQL Create table dept ( deptno number(2) not null， dname char(14)， loc char(13)；SQL Create table emp ( empno number(4) not null， ename char(9)， job char(10), mgr number(4), Hiredate date, sal number(7,2), comm number(7,2), deptno number(2) not null )； (4) 显示表结构 显示部门登记表dept和雇员登记表emp的结构 SQLdesc dept SQL desc emp (5) 向表中插入记录 向部门登记表dept中插入一条记录 SQL insert into dept Values (60，Computer，Beijing)； SQL select * from dept; 向部门登记表dept中连续插入多条记录SQL insert into dept (deptno，dname，loc) Values ( &deptno，&dname，&loc); (6) 执行SQL缓冲区中的命令 SQLedit(7) 执行SQL缓冲区中的命令 SQL/ (直接执行) SQLrun (先显示命令的内容，再执行 ) (8) 执行磁盘上的命令文件 a. 先调入缓冲区，再运行： SQLget f:oradept.sql SQL/ b. 用 或start命令将指定命令文件调入缓冲区并执行。 SQLf:ora dept.sql 或 SQLstart f:ora dept.sql 2、完成教材p243-249，实验一、二的内容。 实验二 数据库的查询和视图一、实验目的 1、掌握SQL语言中子查询命令的功能及其使用方法2、掌握子查询命令中的函数的使用方法二、实验环境1、硬件设备：计算机局域网，服务器1台，客户机100台2、软件系统：Windows 操作系统， Oracle11g or 12c服务端，客户端工具。三、实验内容1、子查询命令的使用(1) 单行值子查询：子查询返回一条纪录 查询工资高于7698号雇员，并且工种与他相同的雇员情况。 SQL select ename，sal，job，empno From emp Where sal ( Select sal From emp Where empno=7698 ) and job=( Select job From emp Where mpno=7698)；查询工资高于或等于20号部门工资额最高的雇员情况 。 SQL Select ename，sal ，deptno From emp Where sal=( Select max(sal) From emp Where deptno=20 ); (2) 多行值子查询：子查询返回一组纪录查工资在￥2000到￥2900之间的雇员所在部门的雇员。 SQL Select ename，sal ，job，deptno From emp Where deptno IN ( Select deptno From emp Where sal between 2000 and 2900); 查工资在￥2000到￥2900之间的雇员所在部门的雇员。 SQL Select ename，sal ，job，deptno From emp Where deptno= ANY ( Select deptno From emp Where sal between 2000 and 2900) Order by sal desc ；(3) 多列值子查询查询工资、奖金与20号部门雇员匹配(相同)的雇员情况 。 SQL Select ename，sal， comm， deptno From emp Where ( NVL(comm，0)，sal) IN ( Select NVL (comm，0)，salFrom empWhere deptno=20) ； 2、子查询命令中函数的使用将所有雇员的名字用大小写形式显示 。 SQL Select empno，Upper(ename)，Initcap(ename) From emp；计算所有雇员的日工资（设一个月为25个工作日） 。 SQL Select ename，round(sal/25，0)，round(sal/25，-1) From emp； 显示当前日期，dual 是系统提供的一张空表 。 SQL Select sysdate From dual ； 显示当前日期。 SQL Select to_char( sysdate，yyyy-mm-dd) From dual ； 分部门统计所有雇员的中收入 。 SQL Select deptno，sum(sal+comm)， sum(sal+NVL(comm，0) From emp Group by deptno； 根据输入的值将雇员信息排序：若输入1，则按ename排序；若输入2，则按SAL排序； 缺省时，按ename 排序。 SQL Select * from emp order by decode ( &ob，1 ，ename，2，sal，ename)； 3、独立完成下列查询（选做）1)、列出XSB表中所有记录的XM、XB和ZY列。 2)、显示所有的专业名称即不重复的专业列。 3)、显示CJB表中成绩在60到80之间的所有记录。 4)、显示CJB表中成绩为85，86或88的记录。 5)、显示XSB表中“计算机”专业或性别为“女”的同学记录。 6)、显示“计算机”专业的学生人数。 7)、显示CJB表中的最高分的学生学号和课程号。 8)、显示“212”号课程的平均分。9)、*显示CJB表中至少有5名学生选修的且课程号以“2”开头的课程的平均分数。 10)、*显示最低分大于70，最高分小于90 的XH。 11)、显示所有学生的 XM、 KCH和CJ列。 12)、显示所有学生的 XM、 KCM和CJ列。 13)、列出“计算机”专业所选课程的平均分。 14)、显示成绩高于学号为“101101”、课程号为“101”的成绩的所有成绩记录。15)、*显示出和学号为“101101”的同学同年出生的所有学生的 XH、XM和 CSSJ列。 16)、显示“软件工程”课程的学生成绩。 17)、*显示选修人数多于5人的课程名称。 18)、*显示存在有85分以上成绩的课程号。19)、*显示 “101”课程成绩高于“102”课程成绩的同学的学号、姓名和成绩。 20)、*列出没有学生选修的课程的基本信息。21)、*检索所有学生都选修的课程名称。22)、查询全体同学的学号、姓名以及选课次数。 实验三 视图、索引和完整性一、实验目的 1、掌握视图的概念及其使用方法2、掌握索引的概念及其使用方法3、掌握数据完整性的应用二、实验环境1、硬件设备：计算机局域网，服务器1台，客户机100台2、软件系统：Windows操作系统， Oracle11g or 12c服务端，客户端工具。三、实验内容1、 视图操作为20号部门的雇员创建一个视图，包括姓名,工资,部门号 。 SQL Create view emp_view(name ，salary ，no) As Select ename ，sal ，deptno From emp where deptno=20 ；2、 索引操作为emp表建立empno列上的唯一索引。 SQL create unique index empno_index on emp(empno); 3、 同义词操作为gx1用户的dept表建立同义词New_dept 。 SQL create synonym new_dept for gx1.dept; 4、 序列生成器操作创建一个序列生成器deptno_seq，起始值为50,间隔为10 。 SQL create sequence deptno_seq Start with 50 increment by 10； 列出deptno_seq的下次值(第一次使用时为50 ) SQL select deptno_seq.nextval NEXT_VAL From dual；5、 数据库链操作创建一个数据库链Link_xsdb，用来连接远程销售数据库xsdb，其用户名为scott，口令为tiger。 SQL create Database Link Link_xsdb connect to scott identified by tiger Using xsdb； 6、完成教材p255-259实验三视图部分和实验四的内容。实验四 PL/SQL编程一、实验目的 1、了解SQL/PL块的基本结构与功能2、掌握SQL/PL块中各种SQL命令的使用方法二、实验环境1、硬件设备：计算机局域网，服务器1台，客户机100台2、软件系统：Windows 2000 Server 网络操作系统，Windows 2000/XP客户机操作系统； Oracle11g服务端数据库系统，客户端工具。三、实验内容1、SQL/PL块中查询命令的使用将7788号雇员的工资和奖金作为Smith的工资和奖金。 SQL DECLARE v_empno emp.empno%TYPE:=7788； v_ename emp.ename%TYPE:=Smith； v_sal emp.sal%TYPE； v_comm m%TYPE； BEGIN SELECT sal，comm INTO v_sal，v_comm FROM EMP WHERE empno=v_empno； update emp set sal=v_sal，comm=v_comm where ename= v_ename ； commit; END；查询SMITH的情况 。 SQL DECLARE emp_rec EMP%ROWTYPE； v_ename EMP.ename%TYPE：=SMITH； BEGIN SELECT * INTO emp_rec FROM EMP WHERE ENAME=v_ename； - END； 2、SQL/PL块中数据操纵命令的使用向EMP表插入一新雇员 。 SQL DECLARE v_deptno dept.deptno%TYPE not null:=50； v_dname dept.dname%TYPE:=Computer； v_loc dept.loc%TYPE:=Beijing； BEGIN insert into dept(deptno，dname，loc) values(v_deptno，v_dname，v_loc)； commit work; END；修改7788号雇员的工资 。 SQL DECLARE v_empno emp.empno%TYPE:=7788； v_addsal emp.sal%TYPE； BEGIN v_addsal:=1000； UPDATE EMP SET sal=sal+v_addsal WHERE empno=v_empno； COMMIT； END； 从EMP表中删除7788号雇员 。 SQL DECLARE BEGIN DELETE FROM EMP WHERE empno=7788； COMMIT； END； 3、完成教材p259-261实验5的内容。 实验五 存储过程和触发器的使用一、实验目的 1、掌握数据库触发器的功能与使用方法2、掌握存储过程的功能与使用方法3、掌握存储函数的功能与使用方法二、实验环境1、硬件设备：计算机局域网，服务器1台，客户机100台2、软件系统：Windows 操作系统， Oracle11g or 12c服务端，客户端工具。三、实验内容1、数据库触发器的使用创建一个数据库触发器，当任何时候某个部门从dept表中删除时，该触发器将从emp表中删除该部门的所有雇员。 SQLCreate trigger del_emp_deptno_trig Before delete ON dept For each row BEGIN delete from emp where deptno=:old.deptno; End del_emp_deptno_trig；2、存储过程的使用创建一个统计各部门雇员人数的据库存储过程。SQLCreate Procedure count_proc(in_deptno in emp.deptno%TYPE， out_num out NUMBER) As BEGIN If in_deptno=10 then select count(deptno) into out_num from emp where deptno=in_deptno； ElsIf in_deptno=20 then select count(deptno) into out_num from emp where deptno= in_deptno ； ElsIf in_deptno=30 then select count(deptno) into out_num from emp where deptno= in_deptno ；