Oracle数据库技术与实验指导.doc

《Oracle数据库技术与实验指导.doc》由会员分享，可在线阅读，更多相关《Oracle数据库技术与实验指导.doc（56页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、 .Oracle数据库技术与实验指导2011.08第0章 实用Oracle数据库技术Oracle的官方为.oracle.；其次metalink.oracle./这里有很多权威的解决方案和补丁；然后就是一些著名如：asktom.oracle.、.dbazine.，这里有很多经验之谈。遇到问题了还可以第一时间找tahiti.oracle.，这里会给你最详细的解释。Oracle 10g/11g数据库都分为标准版（Standard Edition）、标准版1（Standard Edition One）以与企业版（Enterprise Edition）。可从如下网址下载、学习或试用Oracle：.ora

2、cle./technology/global/cn/software/products/database/oracle10g/index.html.oracle./technology/global/cn/software/products/database/index.html.oracle./technology/software/index.html.oracle./technology/software/products/database/index.htmlSQL Developer也可以单独免费下载安装的。下载地址为：.oracle./technology/global/cn/so

3、ftware/products/sql/index.htmlSQL*Plus Instant Client（SQL*Plus即时客户端）SQL*Plus Instant Client下载:.oracle./technology/global/cn/software/tech/oci/instantclient/index.html。实验1 数据库系统基础操作Oracle Database 11g第 2 版(11.2.0.1.0)的下载地址：.oracle./technology/global/cn/software/products/database/index.html适用于 Microso

4、ft Windows（32 位）的 Oracle Database 11g第 2 版 (11.2.0.1.0)的下载地址：.oracle./technology/global/cn/software/products/database/oracle11g/112010_win32soft.html实验2 数据库基础操作手工建库手工建库须要经过几个步骤，每一个步骤都非常关键。它包括： 1、创建相关目录（数据文件和跟踪文件）（假设要创建KCGL数据库，Oracle已安装于“c:appqxz”目录）在c:appqxzadmin这个目录之下创建KCGL文件夹；在C:appqxzadminKCGL 这个

5、目录之下创建adump文件夹；在C:appqxzadminKCGL 这个目录之下创建dpdump文件夹；在C:appqxzadminKCGL 这个目录之下创建pfile文件夹；在C:appqxzoradata 这个目录之下创建KCGL文件夹； 2、创建初始化参数文件通过复制现有的初始化参数文件C:appqxzadminorclpfile这个目录下的参数文件“init.ora.*”（*为数字扩展名）到C:appqxzproduct11.2.0dbhome_1database这个目录，修改名为initKCGL.ora,最后用记事本打开这个参数文件，修改如下几个参数的值：audit_file_des

6、t=C:appqxzadminKCGLadumpdb_name=KCGLcontrol_files=(C:appqxzoradataKCGLcontrol01.ctl,C:appqxzoradataKCGLcontrol02.ctl) 3、打开DOS窗口，设置环境变量：Set oracle_sid=KCGL 4、创建服务： Oradim -new -sid KCGL 5、创建口令文件Orapwd file=C:appqxzproduct11.2.0dbhome_1databasepwdKCGL.oraPassword=12345 6、启动服务器：Sqlplus /nologConn / as

7、sysdbaStartup nomount 7、执行建库脚本：CREATE DATABASE KCGLdatafile c:appqxzoradataKCGLsystem01.dbf size 300mautoextend on next 10m extent management localSysaux datafile c:appqxzoradataKCGLsysaux01.dbf size 120mundo tablespace undotbs1datafile c:appqxzoradataKCGLundotbs01.dbf size 100mdefault temporary tab

8、lespace temptbs1tempfile c:appqxzoradataKCGLtemp01.dbf size 50mlogfile group 1(c:appqxzoradataKCGLredo01.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:app

9、qxzoradataKCGL目录下创建的所有文件，改正错误后，重新启动实例，再执行建库脚本。 8、创建数据字典和包Start C:appqxzproduct11.2.0dbhome_1RDBMSADMINcatalogStart C:appqxzproduct11.2.0dbhome_1RDBMSADMINcatproc 9、执行pupbld.sql脚本文件 切换成system用户执行如下命令：Conn system/managerStart C:appqxzproduct11.2.0dbhome_1sqlplusadminpupbld 10、执行scott脚本创建scott方案Start C

10、:appqxzproduct11.2.0dbhome_1RDBMSADMINscott.sql 这时需要修改密码：Conn / as sysdbaAlter user scott identified by tiger; 再连接scott：Conn scott/tiger 11、select * from dept;能显示出dept表的结果，表示新数据库KCGL已安装成功了。2.2 查看数据库1、查看表空间的名称与大小select tablespace_name,min_extents,max_extents,pct_increase,status from dba_tablespaces;s

11、elect tablespace_name,initial_extent,next_extent,contents,logging,extent_management,allocation_type from dba_tablespacesorder by tablespace_name;select t.tablespace_name, round(sum(bytes/(1024*1024),0) ts_sizefrom dba_tablespaces t, dba_data_files dwhere t.tablespace_name = d.tablespace_namegroup by

12、 t.tablespace_name;2、查看表空间物理文件的名称与大小column db_block_size new_value blksz noprintselect value db_block_size from v$parameter where name=db_block_size;column tablespace_name format a16;column file_name format a60;set linesize 160; - 为sqlplus 命令select file_name,round(bytes/(1024*1024),0) total_space,au

13、toextensible,increment_by*&blksz/(1024*1024) as incement,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_filesorder by tablespace_name;3、查看回滚段名称与大小select a.owner

14、 | . | a.segment_name roll_name , a.tablespace_name tablespace , to_char(a.initial_extent) | / | to_char(a.next_extent) in_extents , to_char(a.min_extents) | / | to_char(a.max_extents) m_extents , a.status status , b.bytes bytes , b.extents extents , d.shrinks shrinks , d.wraps wraps , d.optsize opt

15、 from dba_rollback_segs a , dba_segments b , v$rollname c , v$rollstat d where a.segment_name = b.segment_name and a.segment_name = 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) NextExte

16、nt, max_extents, v.curext CurExtentFrom dba_rollback_segs r, v$rollstat vWhere r.segment_id = v.usn(+)order by segment_name ;4、查看控制文件 select name from v$controlfile;5、查看日志文件 select member from v$logfile;6、查看表空间的使用情况select * from(select sum(bytes)/(1024*1024) as free_space(m),tablespace_name from dba

17、_free_spacegroup 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.BYTES % USED,(C.BYTES*100)/A.BYTES % FREEFROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE CWHEREA.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAM

18、E=C.TABLESPACE_NAME; 7、查看数据库库对象 select owner, object_type, status, count(*) count# from all_objects group by owner,object_type,status;8、查看数据库的版本select * from v$version;Select version FROM Product_component_version Where SUBSTR(PRODUCT,1,6)=Oracle;9、查看数据库的创建日期和归档方式 select created,log_mode,log_mode fr

19、om 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(username) from v$session where username is no

20、t 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 创建学生、课程、选课三个表，在SQL PLUS 的启动界面输入以下代码： SQLCreate Table S(Sno Var

21、char2(10) Primary Key,Sname Varchar2(10) Not Null,Ssex Char(2),Sage Number,Sdept Varchar2(40); SQLCreate Table Course(Cno Varchar2(10),Cname Varchar2(50),Ccredit Number,Constraint Pk_C Primary Key (Cno); SQLCreate Table SC(Sno Varchar2(10),CnoVarchar2(10),Score Number Default 0 Check (Score Between

22、0 And 100),Constraint Pk_S Primary Key (Sno,Cno)TABLESPACE Testspace; -使用Testspace表空间3.2修改表 1、修改表空间的相关操作 1）增加表空间中的数据文件AlterTablespaceTestspace AddDatafilec:appqxzfile_3.dbf size 100m; 2）删除表空间中的数据文件AlterTablespaceTestspace DropDatafilec:appqxzfile_3.dbf; 3）修改表空间文件的数据文件大小AlterDatabaseDatafilec:appqxzf

23、ile_2.dbfResize50m; 4）修改表空间数据文件的自动增长属性。AlterDatabaseDatafilec:appqxzfile_1.dbfAutoextendOff; -Off不能自动增长 2、修改表结构的相关操作：1）插入属性例3-2 在S表插入新属性地址。SQLAlter Table S Add(Address Varchar(100); 2）修改属性 例3-3 对上述性别属性的数据类型进行修改，并且默认值为“男”。SQLAlter Table S Modify( Ssex Varchar2(2) Default 男); 3）删除表属性 例3-4 删除上述表中的地址属性。

24、命令为：SQLAlter Table SDrop(Address); 注意：通常在系统不忙的时候删除不使用的字段，可以先设置字段为unused；Alter Table SSet Unused Column Address; 系统不忙时再执行删除：Alter Table SDrop Unused Column; 4）表重命名 例3-5 把表SC改名为Learn。命令为：SQLRename Sc To Learn; 5）清空表中的数据 例3-6 清空学生表的信息。命令为： SQLTruncate Table S; 6）给表增加注释 例3-7 对表S添加注释为this Is A Test Table

25、SQLComment On Table S Is This Is A Test Table; 7）给列添加注释 例3-8 对表S的Sno属性添加学号的注释。SQLComment On Column S.Sno Is 学号;3.3删除表 例3-9 删除Course表。命令为： SQLDrop Table Course;3.5 创建和管理视图 1、创建视图 例3-10在S表中创建以学号、系别的新视图。SQLCreate Or Replace View V_S(Num,Name,Sdept)AsSelect Sno, Sname, SdeptFromS; 例3-11 在SC上定义新视图，当用upda

26、te修改数据时，必须满足视图score60的条件，不满足则不能被改变。SQLCreate Or Replace View V_SC As Select * From SCWhere Score60With Check Option; 例3-12 创建新视图，按照学号分组显示学生的最高、最低分和平均成绩。SQLCreate 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、查

27、询视图 例3-13 查询上述建立的视图。命令为：SQLSelect * From V_S_SC; 3、更新视图 例3-14 把所有学号为08开头的学生的相关系别信息改为管理系。SQLUpdate V_SSetSdept=ManagementWhereNum like 08%;3.6 表或视图的导入与导出操作1、Oracle数据间的导入导出imp/exp下面是导入导出的实例，导入导出的其它例子或方法请参阅实验13。（1）数据导出1）将数据库orcl完全导出,用户名system 密码orcl，导出到c:orcl.dmp中。exp system/orclorcl2 file=c:orcl.dmp f

28、ull=y2）将数据库中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_agege19.dmp tables=(stu

29、dent) query= where sage=19上面是常用的导出，对于压缩导出，只要在上面命令后面加上 compress=y就可以了。（2）数据的导入1）将c:orcl.dmp中的数据导入orcl数据库中。imp system/orclorcl2file=c:orcl.dmp上面可能有点问题，因为有的表已经存在，然后它就报错，对该表就不进行导入。在后面加上 ignore=y 就可以了。2）将c:orcl_jxgl_studentsc.dmp中的表sc 导入。imp jxgl/jxglorcl2 file= c:orcl_jxgl_studentsc.dmptables=(sc) ignor

30、e=y实验4 SQL语言SELECT查询操作创建Student、SC、Course三表与添加表记录命令如下：Create Table Student( Sno CHAR(5) NOT NULL,Sname VARCHAR(20),Sage SMALLINT CHECK(Sage=15 AND Sage=90;例4-2 SELECT Sname,SsexFROM StudentWHERE Sage18 AND Sdept NOT IN (IS, MA);例4-3 SELECT *FROM CourseWHERE Cname LIKE MIS#_%导_ ESCAPE #;例4-4 SELECT C

31、OUNT(DISTINCT Sno) /* 加DISTINCT 去掉重复值后计数 */FROM SC;例4-5 SELECT Student.Sno FROM Student,SCWHERE Sdept=CS AND Student.Sno=SC.SnoGROUP BY Student.Sno HAVING COUNT(*)=2;例4-6 Select Student.*,SC.* From Student,SC;或 Select Student.*,SC.* From Student Cross Join SC;例4-7 Select * From Student,SC WHERE Stud

32、ent.Sno=SC.Sno;例4-8 SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, GradeFROM Student, SCWHERE Student.Sno=SC.Sno;或 SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, GradeFROM Student INNER JOIN SC ON Student.Sno=SC.Sno;例4-9 SELECT FIRST.Cno, SECOND.cpnoFROM Course FIRST, Course SECONDWHERE FIRS

33、T.cpno=SECOND.Cno;我们为Course表取两个别名FIRST与SECOND，这样就可以在SELECT子句和WHERE子句中的属性名前分别用这两个别名加以区分。例4-10 SELECT Student.Sno, Sname, Ssex, Sage, Sdept, Cno, GradeFROM Student Left Outer JOIN SC ON Student.Sno=SC.Sno;例4-11 SELECTStudent.Sno,Sname,Ssex,Sage,Sdept, Course.Cno,Grade,ame,cpno,ccreditFROM Student Left

34、 Outer JOIN SC ON Student.Sno=SC.Sno Full Outer join Course on SC.cno=Co;例4-12 SELECT Student.*,Cno,GradeFROM STUDENT INNER JOIN SC ON Student.Sno=SC.SnoWHERE SSEX=男 AND GRADE =60例4-13 SELECT * FROM StudentWHERE Sdept IN (SELECT Sdept FROM Student WHERE Sname=钱横);或 SELECT* FROM StudentWHERE Sdept=(S

35、ELECT Sdept FROM StudentWHERE Sname=钱横); -当子查询为单列单行值时可以用“=”或 SELECT S1.* FROM Student S1,Student S2WHERE S1.Sdept=S2.Sdept AND S2.Sname=钱横;一般来说，连接查询可以替换大多数的嵌套子查询。SQL-92支持“多列成员”的属于(IN)条件表达，例：例4-14 Select *from Student TWhere (T.sdept,T.sage,T.ssex) IN (Select sdept,sage,ssexFrom student SWhere S.snoT

36、.sno); -Oracle支持的它等价于逐个成员IN的方式表达，如下：Select *from Student TWhere T.sdept IN(Select sdeptFrom student SWhere S.snoT.sno andT.sage IN (Select sageFrom student X Where S.sno=X.sno and X.snoT.sno and T.ssex IN (Select ssexFrom student Y Where X.sno=Y.sno and Y.snoT.sno);例4-15 SELECT Sno,Sname,Sdept FROM

37、Student -IN嵌套查询方法WHERE Sno IN( SELECT SnoFROM SCWHERE Cno IN (SELECT CnoFROM Course WHERE Cname=数据库系统);或 SELECT Sno,Sname,Sdept FROM Student -IN、= 嵌套查询方法WHERE Sno IN( SELECT Sno FROM SCWHERE Cno=(SELECT CnoFROM Course WHERE Cname=数据库系统);或 SELECT Student.Sno,Sname,Sdept -连接查询方法FROM Student,SC,CourseW

38、HERE Student.Sno=SC.Sno AND SC.Cno=Course.CnoAND Course.Cname=数据库系统;或 Select Sno,Sname,Sdept From Student - Exists嵌套查询方法Where Exists(Select *From SC Where SC.Sno=Student.Sno AndExists(Select *From CourseWhere SC.Cno = Course.Cno And Cname=数据库系统);或 Select Sno,Sname,Sdept From Student - Exists嵌套查询方法Wh

39、ere Exists( Select *From course Where Cname=数据库系统 and Exists( Select * From SCWhere sc.sno=student.sno and SC.Cno = Course.Cno);例4-16 SELECT Sno,Sname FROM StudentWHERE Sno NOT IN (SELECT Sno FROM SCWHERE Cno IN (2,4);例4-17 SELECT SnameFROM StudentWHERE SageAll(SELECT SageFROM StudentWHERE Sdept=IS)

40、 AND Sdept ISORDER BY Sname;本查询实际上也可以用集函数实现：SELECT Sname FROM Student WHERE Sage(SELECT MAX(Sage) FROM StudentWHERE Sdept=IS) AND SdeptISORDER BY Sname;例4-18 SELECT DISTINCT CNAMEFROM COURSE CWHERE 女=ALL( SELECT SSEX FROM SC,STUDENTWHERE SC.SNO=STUDENT.SNO AND SC.CNO=C.CNO);或 SELECT DISTINCT CNAMEFR

41、OM COURSE CWHERE NOT EXISTS( SELECT * FROM SC,STUDENTWHERE SC.SNO=STUDENT.SNO AND SC.CNO=C.CNO AND STUDENT.SSEX=男);例4-19 SELECT SnameFROM StudentWHERE NOT EXISTS(SELECT * FROM SC WHERE Sno=Student.Sno AND Cno=1);或 SELECT SnameFROM StudentWHERE Sno NOT IN (SELECT Sno FROM SC WHERE Cno=1);但如下是错的：SELECT SnameFROM Student,SC WHERE SC.Sno=Student.Sno AND Cno1;例4-20 SELECT SnameFROM StudentWHERE NOT EXISTS(SELECT *FROM CourseWHERE NOT EXISTS(SELECT *F