Oracle数据库技术与实验指导

1、oracle数据库技术与实验指导2011. 08第0章 实用oracle数据库技术orac 1 e 的官方网站为www. oracle, com；其次http:/metalink. oracle, com/这里有很多权威的解决方案和补丁；然后就是一些著名网站如：asktom.oraclexomwww. orafaq. net> , 这里有很多经验之谈。遇到问题了还可以第一时间找,这里会给你最详细的解释。oracl e 10g/l lg 数据库都分为标准版(standard edition)>标准版 1(standard edition one)以及企业版(enterprise ed

2、ition)。可从如下网址下载、学习或试用oracle：http:/www oracle com/technology/global/cn/software/products/database/orac 1elog/index. htmlhttp:/www. oracle, com/tcchnology/global/cn/software/products/database/inde x. htmlhttp:/www. oracle, com/technology/software/index. htmlsql developer也可以单独免费下载安装的。下载地址为： sql*plus in

3、stant client (sql*plus 即时客户端)sql*plusinstantclient下载：http:/www oracle com/technology/global/cn/software/tech/oci/instantc1ient/ind ex. htmlo实验1数据库系统基础操作oracle database 11 第 2 版(.0)的下载地址： 适用于 microsoft windows (32 位)的 oracle database 11 第 2 版(.0)的下 载地址： 1 lg/112010 win32soft.html实验2数据

4、库基础操作手工建库手工建库须要经过儿个步骤，每一个步骤都非常关键。它包括：1、创建相关目录(数据文件和跟踪文件)(假设要创建kcgl数据库，oracle已安装 于 “c:appqxz” 目录)在c: appqxzadmin这个目录之下创建kcgl文件夹；在c:appqxzadminkcgl这个目录之卜创建adump文件夹；在c:appqxzadminkcgl这个目录之卜创建dpdump文件夹；在c: appqxzadminkcgl这个目录之下创建pf ile文件夹；在c:appqxzoradata这个目录z下创建kcgl文件夹；2、创建初始化参数文件通过复制现有的初始化参数文件c:8ppqxz

5、adminorclpfile这个目录下的参数文 件"init. ora. *” (*为数字扩展名)到 c:appqxzproductll. 2. odbhome ldatabase 这个目录，修改名为in it kcgl. ora,最后用记事本打开这个参数文件，修改如下几个参数 的值：audi t_file_dest=c:appqxzadminkcglaxlumpdb_name=kcglcontrol_files= ("c:appqxzoradatakcglcontrol01. ctl", "c:appqxzoradata kcglcontrol02.c

6、tl")3、打开dos窗口，设置环境变量：set oracle_sid=kcgl4> 创建服务：oradim -new -sid kcgl5、创建口令文件orapwd file=c:appqxzproductl1. 2. odbhome 1databasepwdkcgl. orapassword二123456、启动服务器：sqlplus /nologconn / as sysdbastartup nomount7、执行建库脚本：create database kcgldatafile t c:appqxzoradatakcglsystem01. dbfj size 300mau

7、toextend on next 10m extent management localsysaux datafile * c:appqxzoradatakcglsysaux01. dbfj size 120mundo tablespace undotbsldatafile 1 c:appqxzoradatakcglundotbs01. dbfj size 100mdefault temporary tablespace tcmptbsltompfile * c:appqxzoradatakcgltemp01. dbf* size 50mlogfile group 1 (' c:app

8、qxzoradat8kcglredo01.log') size 50m,group 2 (* c:appqxzoradatakcglredo02. log*) size 50m,group 3 (* c:appqxzoradatakcglredo03. log，) size 50m;用记事本编辑以上内容,假定保存为c:createkcgl. sql文件，然后执行这个脚本。 start c:createkcgl. sql不管出现哪种错误，都要删除c:appqxzoradatakcgl冃录下创建的所有文件，改 正错误后，重新启动实例，再执行建库脚本。8、创建数据字典和包start c:ap

9、pqxzproductll. 2. 0dbhome_lrdbmsadmincatalogstart c:appqxzproductll. 2. 0dbhome lrdbmsadmincatproc9、执行pupbld. sql脚本文件切换成system用户执行如下命令：conn system/managerstart c:appqxzproductll. 2. 0dbhome_lsqlplusadminpupbld10执行scott脚本创建scott方案start c:appqxzproductll. 2. 0dbhome lkdbmsadminscott. sql 这时需要修改密码：conn

10、 / as sysdbaalter user scott identificd by tiger;再连接 scott： conn scott/tiger11、 select * from dept;能显示出dept表的结果，表示新数据库kcgl己安装成功了。2. 2查看数据库1、查看表空间的名称及大小select tablespace name, min extentsmax extents,pct increase, status from dba_tablespaces;select tablespace name, initial extent,next extent,contents,

11、logging,extent management, al location_type from dba tabl espaces order by tabl espace_name; select t tablespace_name, round(sum(bytes/(1024*1024), 0) ts_size from dba tablespaces t, dba data files d where ttablespace name = dtablespace name group by t. tablespace_name;2、查看表空问物理文件的名称及大小column db_blo

12、ck size new value blksz noprintselect value db block size from v$parameter where name=，db block size，;column tablespace name format al6;column file_name format a60;set 1 inesize 160;- 为 sqlplus 命令selectfie_name, round (bytes/ (1024*1024), 0)total_space, autoextcnsible, increment_by*&blksz/(1024*

13、1024)asincement, maxbytes/(1024*1024) as maxsize from dba data files order by tablespace_name; - blksz 一般为 8192select tablespace name, file id, file name, round(bytes/(1024*1024),0)total_spacefrom dba data files order by tablespace name;3、查看回滚段名称及大小select a. owner | |'.| | a. segment name roll n

14、ame , a tablespace nametablespace , to_char(a. initial extent) |' /，| | to char (a. next extent)in extents , to char (a. min extents) | '/' i i to char(a. max extents) m extents , a. status status , bbytes bytes , b.extents extents , d.shrinks shrinks , d. wraps wraps , d. optsize opt fr

15、om dba_rollback_segs a , dba_segments b , v$rollname c , v$rol 1 stat d where a. segmcnt_name = b segment_nanie and a. segmentname = c. name (+) and c.usn = d.usn (+) order by a. segment_name;select segment name, tablespace name, r. status, (initial extent/1024) initialextent, (next extent/1024) nex

16、textent, max extents, vcurext curextent from dba_rollback_segs r, v$rollstat v where r. segment_id = v. usn(+) order by segmcnt_name ;4、查看控制文件select name from v$controlfile;5、查看日志文件select member from v$logfile;6、查看表空间的使用情况select*from(selectsum(bytes)/(1024*1024)as "free space(m)z/, tablespace n

17、ame from dba free space group by tablespace name) order by "free space (m);select a. tablespace_name, a. bytes total,b. bytes used, c.bytes free, (b. bytes*100)/a. bytesused，(c. bytes*100)/a.bytes free" fromsys. sm$ts_avatl a, sys. sm$ts_used b,sys. sm$ts_free c where a. tablespace name二b

18、tablespace name and a. tablespace name二c. tablespace name;7、查看数据库库对彖select owner, object_type, status, count(*) count# from all_objects group by owncr,object_type,status;8、查看数据库的版本select * from v$version;selectversionfromproduct component versionwheresubstr (product, 1,6)= 0racle，;9、查看数据库的创建日期和归档方式s

19、elect created, og_mode, log mode from v$database;10、查看临时数据库文件select status, enabled, name from v$tempfile;常用数据库信息查看命令(1) oracle中怎样查看总共有哪些用户select * from all_ users;(2) 查看oracle当前连接数怎样查看oracle当前的连接数呢？只需要用下面的sql语句查询一下就可以了。select * from v$session where username is not null select username, count(userna

20、me) from v$session where username is not null group by username #查看不同用户的连接数select count (*) from v$session#连接数select count (*) from v$session where status二'active' #并发连接数(3) 列出当前数据库建立的会话情况select sid, serial#, username, program, machine, status from v$session;实验3表与视图的基础操作3. 1创建基本表例3-1创建学生、课程、

21、选课三个表，在sql plus的启动界面输入以下代码： sql>crea.te table s (sno varchar2 (10) pri mary key, sname varchar2 (10) not null,ssex char (2), sage number, sdept varchar2(40);sql>create table course (cno varchar2(10), cname varchar2 (50),ccredit number, constraint pk_c primary key (cno);sql>create table sc (

22、sno varchar2(10), cno varchar2(10), score number default 0 check (score between 0 and 100), constraint pk s primary key (sno, cno) tablespace "testspace"-使用 testspace 表空i、可3. 2修改表1、修改表空间的相关操作1) 增加表空间屮的数据文件al ter tablespace testspace add datafi1e ，c:appqxzfi1e_3. dbfj size 100m;2) 删除表空间中的数据

23、文件alter tablespace testspace drop datafile 'c:appqxzfile 3. dbf'3) 修改表空间文件的数据文件大小alter database datafile ' c:appqxzfile 2.dbfj resize 50m;4) 修改表空间数据文件的自动增长属性。alter database datafile 'c:appqx7file 1. dbf， autoextend off;-off 不能自动增长2、修改表结构的相关操作：1) 插入属性例3-2在s表插入新属性地址。sql>alter table

24、s add( address varchar(100);2) 修改属性例3-3对上述性别属性的数据类型进行修改，并ii默认值为“男”。sql>alter table s modify( ssex varchar2(2) default '男')；3) 删除表属性例3-4删除上述表中的地址属性。命令为:sql>alter table s drop (address); 注意：通常在系统不忙的时候删除不使用的字段，可以先设置字段为unused； alter table s set unused column address;系统不忙时再执行删除：alter table

25、s drop unused column;4) 表重命名例 3-5 把表 sc 改名为 learno 命令为：sql>rename sc to learn;5) 清空表中的数据例3-6清空学生表的信息。命令为：sql>truncate table s;6) 给表增加注释例3-7对表s添加注释为'this is a test table'sql>comment on table s is 'this ts a test table'7) 给列添加注释例3-8对表s的sno属性添加'学号'的注释。sql>commont on

26、column s. sno is '学号'；3. 3删除表例 3-9 删除 course 表。命令为：sql>drop table course;3.5创建和管理视图1、创建视图例3-10在s表中创建以学号、姓名、系别的新视图。sql>create or replace view v_s(num, name, sdept) as select sno, sname, sdept from s;例3-11在sc上定义新视图，当用update修改数据吋，必须满足视图score>60的条 件，不满足则不能被改变。sql>create or replace vi

27、ew v sc as seiect * from sc where score>60 with check option;例3-12创建新视图，按照学号分组显示学生的最高、最低分和平均成绩。sql>create view v_s_sc (num,smin, smax, savg) as select d. sno, min (e. score), max (e. score), avg(e. score) from sc e, s d where e. sno=d. sno group by d.sno;2、查询视图例3-13查询上述建立的视图。命令为:sql>select

28、* from v_s_sc;3、更新视图例3-14把所有学号为08开头的学生的相关系别信息改为管理系。 sql>update v s set sdept二'management' where num like '08%'3.6表或视图的导入与导出操作1、oracle数据间的导入导出imp/exp下面是导入导出的实例，导入导出的其它例子或方法请参阅实验13。(1) 数据导出1 )将数据库orcl完全导出，用户名system密码orcl,导出到c:orcl. dmp中。exp system/orclorcl2 filo=c:orcl. dmp full=y2)

29、 将数据库中jxgl用户与scott用户的表导出。exp system/orclorcl2 file=c:orcl_jxglscott. dmp owner二(jxgl, scott)3) 将数据库中jxgl用户的表student, sc导出。exp jxgl/jxglorcl2 file=c:orcl_jxgl_studentsc. dmp tables= (student, sc)4) 将数据库屮jxgl用户的表student中年龄大于等于19的学生记录导出。exp jxgl/jxglorcl2 file=c:orcl jxgl student agegel9. dmp tables= (

30、student) query二" where sage>=19/z上面是常用的导出，对于压缩导出，只要在上面命令后面加上compress二y就可以了。(2) 数据的导入1) 将c:orcl. dmp屮的数据导入orcl数据库中。imp system/orclorcl2 file=c:orcl. dmp上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。在 后面加上ignore二y就可以了。2) 将 c:orcl_jxgl_studentsc. dmp 中的表 sc 导入。imp jxgl/jxglorc!2 file二 c:orcl_jxgl_student

31、sc. dmp tables二(sc) ignore二y实验4 sql语言select查询操作el创建student sc、course三表及添加表记录命令如下：create table student(sno char(5) not null,sname varchar(20),sage smallint check (sage>=15 and sage<=45),ssex char(2) default '男'check (ssex二'男'or ssex=女')，sdept char(2), constraint pr sno prima

32、ry key (sno);create table course ( cno char (2) not null, cname varchar(20), cpno char(2), ccredi t smallint, constraint pr_cno primary key (cno);create table sc( sno char (5) not null, cno char (2) not null, grade smallint check (grade is null) or (grade between 0 and 100), primary key(sno, cno), c

33、onstraint c_f foreign key(cno) references course(cno), constraints_f foreign key (sno) references student (sno);values c 9800 v ,'钱横',18/ 男，cs，);values (' 98002','王林',19/ 女'，'cs，);values(' 98003','李民，20,'男，is，);values (' 98004','赵三',16/

34、 女'，ma');values c t/ 数据库系统'，5',4);values('2','数学分析null ,2);values c 3','信息系统导论，1', 3);values (' 4','操作系统原理'， 6', 3);values ('5','数据结构，7', 4);values (' 6','数据处理基础',null,4);values ('7'c 语言，6', 3);sc

35、insertinsertinsertinsertinsertinsertinsertinsertinsertinsertinsertinsertintointointointointointointointointointointostudentstudentstudentstudentcoursecoursecoursecoursecoursecoursecourseintovaluesc 98001， 2', 67);insertvalues ('98002',，2, 95) ; insert例4-1select dtsttnct snointointovalues

36、(' 98001'，1', 87);insert into sc sc values (，98001，,，3, ,90) ; insert tnto sc sc values ('98002','3', 88);from scwhere grade二90;例4-2select sname,ssexfrom studentwhere sage>18 and sdept not tn ('ts', 'ma');例4-3select * from course where cname like z导一

37、9;escape v ；例4-4select count (distinct sno) /* 加 distinct 去掉重复值后计数 */ from sc;例4-5select student.sno from student,scwhere sdept二'cs' and student.sno二sc.snogroup by student. sno having count(*)>=2;例4-6select student. *, sc. * from student, sc;select student*, sc. * from student cross join

38、sc; 例4-7select * from student, sc where student.sno=sc. sno;例4-8select student sno, sname, ssex, sage, sdept, cno, gradefrom student, sc where student.sno=sc. sno;或 select student.sno, sname, ssex, sage, sdept, cno, gradefrom student inner join sc on student. sno=sc. sno;例4-9select first. cno, secon

39、d, cpno from course first, course secondwhere ftrst. cpno=second. cno;我们为course表取两个别名first与second,这样就可以在select子句和where子 句中的屈性名前分别用这两个别名加以区分。例 4-10select student.sno, sname, ssex, sage, sdept, cno, gradefrom student left outer join sc on student. sno=sc. sno; 例 4-11select student. sno, sname, ssex, s

40、age, sdept, course. cno, grade, cname, cpno, ccreditfrom student left outer join sc on student. sno=sc. sno full outer join course on sc. eno二course, eno;例 4-12select student. *, cno, grade from student inner join sc on student. sno二sc. sno where ssex二'男'and grade >=60例 4-13select * from

41、studentwhere sdept in (select sdept from student where sname=，钱横');或 select * from studentwhere sdept=(select sdept from studentwhere sname=钱横');一当子查询为单列单行值时可以用“二”或 select si.* from student si,student s2where si. sdept=s2. sdept and s2. sname=，钱横'一般来说，连接查询可以替换大多数的嵌套子查询。sql-92支持“多列成员”的属于(

42、in)条件表达,例：例 4-14select * from student twhere (t. sdept, t. sage,tssex) in (select sdept,sage, ssex from student swhere s. snoot. sno) ; oracle 支持的它等价于逐个成员tn的方式表达，如下：select * from student t where t. sdept in(select sdept from student swhere s. snoot. sno and t. sage tn(select sage from student x wher

43、e s. sno=x. sno and x. sno>t. sno and t.ssex in(select ssex from student y where x. sno二y. sno and y. snoot. sno); 例 4-15select sno, sname, sdept from student in 嵌套查询方法where sno tn(select sno from scwhere cno in (select cno from course where cname=，数据库系统')； 或 select sno, sname, sdept from stu

44、dent in、=嵌套查询方法where sno in(select sno from scwhere cno= (select cno from course where cname=，数据库系统')； 或 select student. sno, sname, sdept-连接查询方法from student, sc, coursewhere student. sno=sc. sno and sc. cno二course. cno and course. cname=，数据库系统 yf或 select sno, sname, sdept from student - exists

45、嵌套查询方法where exists( select * from sc where sc.sno二student.sno andexi sts ( seiect * from coursewhere sc. cno = course. cno and cname=，数据库系统')； 或 select sno, sname, sdept from student - exists 嵌套查询方法where exists ( select * from course where cname=，数据库系统'andexists ( select * from sc where sc.

46、sno二student, sno and sc. cno = course. cno);例 4-16select sno, sname from studentwhere sno not in (select sno from sc where cno in ('2','4');例 4-17select sname from studentwhere sage>all(select sage from studentwhere sdept= is，) and sdept <> ' is，order by sname;本查询实际上也可以用

47、集两数实现：select sname from studentwhere sage>(select max (sage) from studentwhere sdept二'is，) and sdept<>，is，order by sname;例 4-18select distinct cname from course cwhere '女，=all ( select ssex from sc, studentwhere sc. sno=student. sno and sc. cno=c. cno); 或 select distinct cname from

48、course cwhere not exists(select * from sc, studentwhere sc. sno二student. sno and sc. cno=c. cno and student. ssex男');例 4-19select sname from studentwhere not exists(select * from sc where sno二student. sno and cno二t);或 select sname from studentwhere sno not in (select sno from sc where cno二'1

49、');但如下是错的：select sname from student, sc where sc. sno二student.sno and cno<>，t ;例 4-20select sname from studentwhere not exists(select * from course where not exists(select * from sc where sno=sc.sno and cno二course.cno);由于没有全称量词，我们将题目的意思转换成等价的存在量词的形式：查询这样的学 生姓名没有一门课程是他不选的。本题的另一操作方法是：select

50、sname from student, sc where student. sno二sc. snogroup by student. sno, sname having count (*)>=(select count (*) from course); 例 4-21select sno from student sxwhere not exists(select * from sc scywhere scy.sno= 98001， and not exists(select * from sc scz where scz. sno二sx. sno and sc乙 cno二scy. cn

51、o);例 4-22select sno from sc where cno= tunionselect sno from sc where cno=，2'select sno from sc where cno二t'intersectselect sno from sc where cno=，2' 查询既选课程1又选课程2的学生学号集 例 4-23select * from student where sdept二'cs'intersectselect * from student where sage<=19;本查询等价于“查询计算机科学系中年龄

52、不大于19岁的学生。”，为此变通法为：select * from student where sdept二'cs' and sage二19;例 4-24select sno from sc where cno二'2'minusselect sno from sc where cno二t ;本例实际上是查询选修了课程2但没有选修课程1的学生。为此变通法为：select sno from scwhere cno二'2' and sno not in (select sno from sc where cno= 1');例 4-25select

53、 stu_no, sname, avgrfrom student, ( select sno stu_no, avg(grade) avgr from sc group by sno) sg where student, sno二sg. stu_no and avgr>85;sql-92允许在from中使用舌询表达式，并必须为查询表达式取名。它等价于如下未 使用查询表达式的形式：select student. sno, sname, avg(grade)from student,sc where student.sno = sc.snogroup by student.sno, snam

54、e having avg (grade)>85;例 4-26select sname, cname, gradefrom (select sname, cname, grade from student, sc, coursewhere ssex二'女'and student. sno=sc. sno and sc. cno=course. cno) tempwhere grade>90;一特意用查询表达式实现，完全可用其它方式实现但如下使用查询表达式的查询，则不易改写为其它形式。例 4-27select avgr, count(*)from (select sno

55、, avg(grade) avgr from sc group by sno) sggroup by avgr;例 4-28grant create view to jxgl 赋予用户 jxgl create view 的权力create vtew ts_studentas select sno, sname, sage, ssexfrom student where sdept= is' with check optiongoselect * from is_student where sage>=18 and ssex=女'实验5 sql语言一一数据更新操作5.1 i

56、nsert 命令例5-1insert into student values('98011','张静'，27,'女'，'cs') ; commi t;insert语句后可跟returning子句来获取插入记录的某字段值。程序代码如下：set serveroutput ondeclarebndl student. sno%type;bnd2 student. sname%type;begininsert tnto student (sno, sname, sage, ssex, sdept) val ues c 9801 v / 张

57、静 ',27,'女'，'cs') returning sno, student, sname into bndl,bnd2;dbms output.put line(bndl|'| |bnd2);end;例5-2insert tnto student (sno, sname, sage) values ('98012','李四'，16); commi t; 例5-3create sequence tt increment by 1 minvalue 101 maxvalue 9999999 cycle; create table testable (id int,rq date);insert into testable values(tt. nextval, sysdate);若要删除序列命令为：drop sequence tt;若删除测试表testable命令为：drop table testable;例5-4insert into scselect sno,eno, nul1 from student, course where sdept二