数据库开发技术实验报告认识Oracle常用管理工具和DDL、DML实践

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 clerk待添加的隐藏文字内容3allen 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

18、 desc(3) 事务控制、替代变量、case 结构1) *请为用户创建一个定制提示符，提示用户为deptno列输入5099之间的一个值2) *学习使用commit、rollback和savepoint等事务控制语句。3) *学会创建并执行一个脚本文件。4) *观察数据的读一致性。(提示：用两个不同的连接来观察，另一个连接比如可以用scott的身份连接)5) 显示符合任意条件的雇员的编号以及其它任意的列6) 将case结构用于update语句。基于雇员的job值来更新job值。 manager更新为管理人员，president更新为总经理，其余的更新为普通人员。update empset jo

19、b=case when 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 m

20、artin 普通人员 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 expres

21、sion )还是搜索式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);i

22、nsert into 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 into sc values(95001,5,92);insert into sc values(95002,3,80);insert into sc values(95001,1,58);inser