《数据库开发技术实验报告认识Oracle常用管理工具和DDL、DML实践.doc》由会员分享，可在线阅读，更多相关《数据库开发技术实验报告认识Oracle常用管理工具和DDL、DML实践.doc（13页珍藏版）》请在三一办公上搜索。
1、福建工程学院信息科学与工程学院实验报告 2013 2014 学年第 1 学期 任课老师： 蒋建辉 课程名称 数据库开发技术班级座号姓名实验题目认识Oracle常用管理工具和DDL、DML实践实验时间实验开始日期： 2013.10. 12 报告提交日期： 2013.10.16 实验目的、要求实验目的：1 认识并熟悉Oracle常用的管理工具：SQL Plus、iSQL Plus、SQL Developter和EM。2 掌握Oracle表的数据定义（DDL）语言，实现表的定义、删除与修改。3 掌握创建Oracle表各种约束的方式和方法。4 掌握利用数据字典视图查看各种有用信息的方法。5 掌握插入、
2、删除和更新表中数据的方法。6 掌握控制事务的方法。7 掌握WHERE子句中条件表达式的使用方法。8 掌握访问替换变量的方法。9 学会创建并执行一个脚本文件。10 掌握CASE 结构的使用方法。实验设计内容及实现步骤实验内容和要求1. *登录到SQL Plus、iSQL Plus、SQL Developter和EM，认识并运用这些常用的Oracle管理工具。2. DDL实践（1） Oracle表的DDL实践。设有如下关系表S_班名座号(如：s_wg100103)： s_wg100103 (SNO, SNAME, SSEX, SAGE, SDEPT)，主关键字是SNO。其中SNO为学号，NUMBE
3、R(5)，学号不能为空，值是惟一的；SNAME为姓名，VARCHAR2(20)；SSEX为性别，char(2)；SAGE为年龄，NUMBER(2)； SDEPT为所在系名，VARCHAR2(10)。写出实现下列功能的SQL语句。1) 创建此表并插入数据；create table s_ji3110307207( sno number(5) primary key not null, sname varchar2(20), ssex char(2), sage number(2), sdept varchar2(10)INSERT INTO S VALUES(95001,李明勇,男,20,CS);
4、INSERT INTO S VALUES(95002,刘晨,女,19,IS);INSERT INTO S VALUES(95003,王名,女,18,MA);INSERT INTO S VALUES(95004,张立,男,19,CS);INSERT INTO S VALUES(95005,张军,男,21,MA);INSERT INTO S VALUES(95006,王张凤,女,19,FL);INSERT INTO S VALUES(95011,王敬,女,18,IS);INSERT INTO S VALUES(95021,张名惠,男,19,FL);2) 查看此表的数据结构和数据;3) 根据此表再复
5、制创建一个只有IS系学生组成的新表S_IS_班名座号;create table s_is_ji3110307207as select * from s_ji3110307207where sdept=is4) 向S表添加“入学时间（comedate）”列，其数据类型为日期型（date）；alter table s_ji3110307207 add comedate date5) 将年龄的数据类型改为NUMBER(3)且默认值为19，并查看这些修改后的表结构;列无数据的情况下直接修改：alter table s_ji3110307207 modify sage munber(3),default
6、(19)列有数据的情况下：新建一列alter table s_ji3110307207 add newsage number(3) default(19)旧列数据复制新列中update s_ji3110307207 set newsage=cast (sage as number(3);删除旧列alter table s_ji3110307207 drop column sage;重新命名新列alter table s_ji3110307207 rename column newsage to sage;6) 删除新添加的列，查看更改情况;alter table s_ji3110307207
7、drop column comedate7) 将表s改名为student;alter table s_ji3110307207 rename to student_31103072078) 为表s添加注释学生信息表comment on table student_3110307207 is 学生信息表读取注释:select * from user_tab_comments where comments is not null9) 截断s表;truncate table s_ji311030720710) 删除生成的新表S_IS_班名座号；truncate和delete只删除数据不删除表的结构(
8、定义)drop语句将删除表的结构被依赖的约束(constrain),触发器(trigger),索引(index);依赖于该表的存储过程drop table s_is_ji3110307207(2) 创建约束要求按普遍采用的约定为这些约束命名，约束创建后要进行约束测试。（参见所附的表结构和数据，表名要加上班名座号）CREATE TABLE COURSE_ji3110307207( CNO NUMBER(4) , CNAME VARCHAR2(30), CPNO NUMBER(4), CCREDIT NUMBER(2), CLIMIT NUMBER(4)1) 为COURSE表添加一个主键约束;al
9、ter table course_ji3110307207 add constraint pk_cno primary key(cno)查看此表的约束：Select * from user_constraints where table_name=COURSE_JI31103072072) 创建SC表的主键约束和外键约束（一个列级和一个表级），表级的外键约束要求允许使用级联删除选项;alter table course_ji3110307207 add constraint pk_cno primary key(cno)select * from user_constraints where
10、table_name=SCalter table sc add constraint sno_cno_fk foreign key(sno,cno) references s_ji3110307207(sno) and course_ji3110307207(cno)ORA-02256: 要引用的列数必须与已引用列数匹配?3) 为S表的SSEX创建CHECK约束;alter table s_ji3110307207 add constraint ssex_chk check(ssex in(男,女)Select * from user_constraints where table_name=
11、S_JI31103072074）为S表创建一个合适的Not null和unique惟一性约束;姓名为非空:select * from USER_CONSTRAINTS where table_name = S_JI3110307207学号是唯一的: alter table s_ji3110307207 add constraint sno_unique unique(sno)建表时已经将sno设为主键了，然后就添加不进去了;ORA-02261: 表中已存在这样的唯一关键字或主键;然后查约束条件的时候又没有看到？5） 在上题基础上，删除其中一个约束，启用/禁用其中一个约束，并测试之。 Alter
12、 table s_ji3110307207 drop constraint ssex_chk alter table s_ji3110307207 disable constraint sys_c005176alter table s_ji3110307207 enable constraint sys_c005176(3) 使用数据字典视图利用任意3种数据字典视图查看数据库对象的信息，如查看表、表空间信息和约束信息等。查看用户下所有的表：select * from user_tables显示用户信息所属的表空间：select default_tablespace,temporary_tabl
13、espace from dba_users查询约束信息：Select * from user_constraints 3. DML实践(1) Oracle表的DML操作。1) 7369号雇员加薪10%。update empset sal=sal*1.1where empno=73692) *从部门表（DEPT）中删除部门30，如果删除不成功，那么写出你自己的建议，说明应该怎么做。应该把30号部门的员工信息删除，因为部门表的部门编号作为了员工表的外键3) 向EMP表中插入一个新雇员。Insert into emp values(7950,QSL,MANAGER ,7839,to_date(21-
14、08-2003,DD-MM-YYYY), 4000.00, NULL, 10)(2) 数据检索。1) *显示EMP表中月薪$2850的雇员的姓名和薪资。select ename,sal from empwhere sal28502) *显示雇员号为7566的员工的姓名和部门号。select ename,deptno from empwhere empno=75663) 显示受雇日期在20-2月-1981与1-5月-1981之间的员工的姓名、工种和受雇日期，要求以受雇日期升序排列。select ename ,job,hiredate from empwhere hiredate between
15、20-2月-1981 and 1-5月-1981order by hiredate4) *显示部门号为10、30的员工的姓名和部门号，要求以姓名序排列。select ename,deptno from empwhere deptno in (10,30)order by ename5) 显示EMP表中所有岗位（job）的类型。select distinct job from emp6) 显示没有上司员工的姓名和工种。select ename ,job from empwhere mgr is null7) 显示部门号为10、30且月薪$1500的所有员工的姓名和月薪，要求显示列名为 “Empl
16、oyee”和“Monthly Salary”。select ename Employee,sal MonthlySalary from empwhere deptno in (10,30) andsal1500 8) 显示哪些姓名中含有字母”A”并且部门号为30的员工或者上司号为7782的员工姓名。select ename from emp where ename like %A% and deptno=30 or mgr=77829) *在查询EMP表中使用连接操作符和原义字符串。查询结果类同以下：select ename | is a |jobfrom emp雇员和职务表-SMITH is
17、 a CLERKALLEN is a SALESMANWARD is a SALESMANJONES is a MANAGERMARTIN is a SALESMAN.10) *显示哪些挣了佣金（comm）的员工的姓名、月薪和佣金，要求显示结果按月薪降序，月薪相同再按佣金降序。降序：select sal from emp order by sal desc升序：select sal from emp order by sal select ename,sal,comm from empwhere comm is not nullorder by sal desc,comm desc(3) 事务
18、控制、替代变量、CASE 结构1) *请为用户创建一个定制提示符，提示用户为Deptno列输入5099之间的一个值2) *学习使用COMMIT、ROLLBACK和SAVEPOINT等事务控制语句。3) *学会创建并执行一个脚本文件。4) *观察数据的读一致性。(提示：用两个不同的连接来观察，另一个连接比如可以用scott的身份连接)5) 显示符合任意条件的雇员的编号以及其它任意的列6) 将CASE结构用于UPDATE语句。基于雇员的job值来更新job值。 MANAGER更新为管理人员，PRESIDENT更新为总经理，其余的更新为普通人员。update empset job=case when
19、 job=MANAGER then管理人员when job=PRESIDENT then总经理else 普通人员 end EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - - 7369 SMITH 普通人员 7902 17-12月-80 800 20 7499 ALLEN 普通人员 7698 20-2月 -81 1600 300 30 7521 WARD 普通人员 7698 22-2月 -81 1250 500 30 7566 JONES 管理人员 7839 02-4月 -81 2975 20 7654 MARTIN 普通人员
20、7698 28-9月 -81 1250 1400 30 7698 BLAKE 管理人员 7839 01-5月 -81 2850 30 7782 CLARK 管理人员 7839 09-6月 -81 2450 10 7788 SCOTT 普通人员 7566 13-7月 -87 3000 20 7839 KING 总经理 17-11月-81 5000 10 已选择14行。一、 *附加题查询EMP表中10号部门的员工平均工资，如果参加平均的员工工资低于2000就以2000作为最低可能的工资参加平均。指出你使用的CASE结构是简单CASE表达式(Simple CASE Expression )还是搜索式
21、CASE表达式(Searched Case Expression)。二、 实验报告根据以上实验内容的要求认真填写实验报告，记录所有的实现方法和运行结果，并记录实验过程中遇到的困难和解决问题的方法。(实验报告中不要求写加*题目的实验过程和内容)三、 附录：CREATE TABLE COURSE(CNONUMBER(4) CNAME VARCHAR2(30),CPNO NUMBER(4),CCREDITNUMBER(2),CLIMITNUMBER(4);CREATE TABLE SC(SNO NUMBER(5),CNONUMBER(4), GRADENUMBER(3,1);INSERT INTO
22、COURSE VALUES(1,数据库,5,5,10);INSERT INTO COURSE VALUES(2,数学,NULL,3.10);INSERT INTO COURSE VALUES(3,信息系统,1,4,12);INSERT INTO COURSE VALUES(4,操作系统,6,4,12);INSERT INTO COURSE VALUES(5,数据结构,7,5,16);INSERT INTO COURSE VALUES(6,数据处理,NULL,3,15);INSERT INTO COURSE VALUES(7,PASCAL语言,6,2,NULL);COMMIT;INSERT IN
23、TO SC VALUES(95001,5,92);INSERT INTO SC VALUES(95002,3,80);INSERT INTO SC VALUES(95001,1,58);INSERT INTO SC VALUES(95002,2,90);INSERT INTO SC VALUES(95003,3,NULL);INSERT INTO SC VALUES(95001,3,70);INSERT INTO SC VALUES(95002,1,84.5);INSERT INTO SC VALUES(95009,2,67);INSERT INTO SC VALUES(95010,2,NUL
24、L);INSERT INTO SC VALUES(95009,1,34.5);INSERT INTO SC VALUES(95001,2,85);COMMIT;调试过程记录4）为S表创建一个合适的Not null和unique惟一性约束;姓名为非空:select * from USER_CONSTRAINTS where table_name = S_JI3110307207学号是唯一的: alter table s_ji3110307207 add constraint sno_unique unique(sno)建表时已经将sno设为主键了，然后就添加不进去了;ORA-02261: 表中已存在这样的唯一关键字或主键;然后查约束条件的时候又没有看到？总结以及心得体会 第一次完成oracle实验，说实话，量还是很多的，幸好老师给了比较多的时间去完成，总体来说难度还算中等，就是有些语法不是很了解，所以要尝试比较多次才能出来结果，但是通过这次的实验，oracle基本的一些操作算是差不多了解并大致掌握了。实验中也遇到了许多困难，而后通过了看书，还有上网百度查资料获得了解决。另外也记录了自己比较不清楚的 知识点在实验报告中，以便以后的复习。指导老师评阅意见指导老师： 年 月 日
