《Oracle数据库技术与实验指导.docx》由会员分享，可在线阅读，更多相关《Oracle数据库技术与实验指导.docx（57页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、Oracle数据库技术与实验指导2011.08第0章 实用Oracle数据库技术Oracle的官方网站为；其次然后就是一些著名网站如：、，这里有很多经验之谈。遇到问题了还可以第一时间找，这里会给你最详细的解释。Oracle 10g/11g数据库都分为标准版（Standard Edition）、标准版1（Standard Edition One）以及企业版（Enterprise Edition）。可从如下网址下载、学习或试用Oracle：SQL Developer也可以单独免费下载安装的。下载地址为：SQL*Plus Instant Client（SQL*Plus即时客户端）SQL*Plus I
2、nstant Client下载: 实验1 数据库系统基础操作Oracle Database 11g 第 2 版(11.2.0.1.0)的下载地址： 适用于 Microsoft Windows（32 位）的 Oracle Database 11g 第 2 版 (11.2.0.1.0) 的下载地址：实验2 数据库基础操作 手工建库手工建库须要经过几个步骤，每一个步骤都非常关键。它包括： 1、创建相关目录（数据文件和跟踪文件）（假设要创建KCGL数据库，Oracle已安装于“c:appqxz”目录）在c:appqxzadmin这个目录之下创建KCGL文件夹；在C:appqxzadminKCGL 这个
3、目录之下创建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
4、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.ora Password=12345 6、启动服务器：Sqlplus /nolog Conn /
5、as sysdba Startup 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 100m default temporar
6、y tablespace 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 不管出现哪种
7、错误，都要删除C:appqxzoradataKCGL目录下创建的所有文件，改正错误后，重新启动实例，再执行建库脚本。 8、创建数据字典和包 Start C:appqxzproduct11.2.0dbhome_1RDBMSADMINcatalog Start C:appqxzproduct11.2.0dbhome_1RDBMSADMINcatproc 9、执行pupbld.sql脚本文件 切换成system用户执行如下命令：Conn system/manager Start C:appqxzproduct11.2.0dbhome_1sqlplusadminpupbld 10、执行scott脚本创
8、建scott方案 Start C:appqxzproduct11.2.0dbhome_1RDBMSADMINscott.sql 这时需要修改密码：Conn / as sysdba Alter 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
9、dba_tablespaces;select tablespace_name,initial_extent,next_extent,contents,logging,extent_management,allocation_type from dba_tablespaces order by tablespace_name;select t.tablespace_name, round(sum(bytes/(1024*1024),0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tab
10、lespace_namegroup by 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*102
11、4),0) total_space,autoextensible,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_files order by tablespace_name;3、查看回
12、滚段名称及大小select a.owner | . | 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
13、wraps , d.optsize opt 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_
14、extent/1024) NextExtent, max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v Where 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)
15、,tablespace_name 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.BYTES % USED,(C.BYTES*100)/A.BYTES % FREE FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C WHERE A.TABLESPACE_NAME=B.TABLESP
16、ACE_NAME AND A.TABLESPACE_NAME=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
17、 created,log_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(username) from v
18、$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 创建学生、课程、选课三个表，在SQL PLUS 的启动界面输入以下
19、代码： SQLCreate Table S(Sno Varchar2(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 D
20、efault 0 Check (Score Between 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）修改表空间文件的数据文件
21、大小AlterDatabaseDatafilec:appqxzfile_2.dbfResize50m; 4）修改表空间数据文件的自动增长属性。Alter Database Datafile c:appqxzfile_1.dbf Autoextend Off; -Off不能自动增长 2、修改表结构的相关操作：1）插入属性例3-2 在S表插入新属性地址。SQLAlter Table S Add(Address Varchar(100); 2）修改属性 例3-3 对上述性别属性的数据类型进行修改，并且默认值为“男”。SQLAlter Table S Modify( Ssex Varchar2(2)
22、Default 男); 3）删除表属性 例3-4 删除上述表中的地址属性。命令为：SQLAlter Table S Drop(Address); 注意：通常在系统不忙的时候删除不使用的字段，可以先设置字段为unused；Alter Table S Set Unused Column Address; 系统不忙时再执行删除：Alter Table S Drop Unused Column; 4）表重命名 例3-5 把表SC改名为Learn。命令为：SQLRename Sc To Learn; 5）清空表中的数据 例3-6 清空学生表的信息。命令为： SQLTruncate Table S; 6）
23、给表增加注释 例3-7 对表S添加注释为this Is A Test TableSQLComment 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) As Select
24、 Sno, Sname, Sdept From S; 例3-11 在SC上定义新视图，当用update修改数据时，必须满足视图score60的条件，不满足则不能被改变。 SQLCreate Or Replace View V_SC As Select * From SC Where Score60 With 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.Scor
25、e) From SC E,S D Where E.Sno=D.Sno Group By D.Sno; 2、查询视图 例3-13 查询上述建立的视图。命令为：SQLSelect * From V_S_SC; 3、更新视图 例3-14 把所有学号为08开头的学生的相关系别信息改为管理系。 SQLUpdate V_S Set Sdept=Management Where Num like 08%;3.6 表或视图的导入与导出操作1、Oracle数据间的导入导出imp/exp下面是导入导出的实例，导入导出的其它例子或方法请参阅实验13。（1）数据导出1）将数据库orcl完全导出,用户名system 密
26、码orcl，导出到c:orcl.dmp中。exp system/orclorcl2 file=c:orcl.dmp full=y 2）将数据库中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 jxg
27、l/jxglorcl2 file=c:orcl_jxgl_student_agege19.dmp tables=(student) 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 jxg
28、l/jxglorcl2 file= c:orcl_jxgl_studentsc.dmptables=(sc) ignore=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,Ssex FROM StudentWHERE Sage18 AND Sdept NOT IN (IS, MA);例4-3 SELECT
29、 * FROM Course WHERE Cname LIKE MIS#_%导_ ESCAPE #;例4-4 SELECT COUNT(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
30、 Student Cross Join SC;例4-7 Select * From Student,SC WHERE Student.Sno=SC.Sno;例4-8 SELECT 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-9 SELECT
31、FIRST.Cno, SECOND.cpno FROM Course FIRST, Course SECONDWHERE FIRST.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 SELECT Student.Sno, Sname,
32、 Ssex, Sage, Sdept, Course.Cno, Grade, cname, cpno, ccreditFROM Student Left Outer JOIN SC ON Student.Sno=SC.Sno Full Outer join Course on SC.cno=Co;例4-12 SELECT Student.*,Cno,Grade FROM STUDENT INNER JOIN SC ON Student.Sno=SC.SnoWHERE SSEX=男 AND GRADE =60例4-13 SELECT * FROM StudentWHERE Sdept IN (S
33、ELECT Sdept FROM Student WHERE Sname=钱横);或 SELECT * FROM StudentWHERE Sdept=(SELECT 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 TWhe
34、re (T.sdept,T.sage,T.ssex) IN (Select sdept,sage,ssex From student SWhere S.snoT.sno); -Oracle支持的它等价于逐个成员IN的方式表达，如下：Select * from Student T Where T.sdept IN( Select sdept From student SWhere S.snoT.sno and T.sage IN (Select sage From student X Where S.sno=X.sno and X.snoT.sno and T.ssex IN (Select s
35、sex From student Y Where X.sno=Y.sno and Y.snoT.sno);例4-15 SELECT Sno,Sname,Sdept FROM Student - IN嵌套查询方法WHERE Sno IN( SELECT Sno FROM SCWHERE Cno IN (SELECT Cno FROM Course WHERE Cname=数据库系统);或 SELECT Sno,Sname,Sdept FROM Student - IN、= 嵌套查询方法WHERE Sno IN( SELECT Sno FROM SCWHERE Cno=(SELECT Cno FR
36、OM Course WHERE Cname=数据库系统);或 SELECT Student.Sno,Sname,Sdept -连接查询方法FROM Student,SC,CourseWHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Course.Cname=数据库系统;或 Select Sno,Sname,Sdept From Student - Exists嵌套查询方法Where Exists( Select * From SC Where SC.Sno=Student.Sno AndExists( Select * From Course
37、Where SC.Cno = Course.Cno And Cname=数据库系统);或 Select Sno,Sname,Sdept From Student - Exists嵌套查询方法Where Exists( Select * From course Where Cname=数据库系统 and Exists( Select * From SC Where sc.sno=student.sno and SC.Cno = Course.Cno);例4-16 SELECT Sno,Sname FROM StudentWHERE Sno NOT IN (SELECT Sno FROM SC W
38、HERE Cno IN (2,4);例4-17 SELECT Sname FROM StudentWHERE SageAll(SELECT Sage FROM StudentWHERE Sdept=IS) 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 CNAME FROM COURSE CWHERE 女=ALL( SELECT SSEX FROM SC,STUDENTWHERE SC.SNO=STUDENT.SNO AND SC.CNO=C.CNO);或 SELECT DISTINCT CNAME FROM COURSE CWHERE NOT EXISTS( SELECT * FROM SC,STUDENTWHERE SC.SNO=STUDENT.SNO AND SC.CNO=C.CNO AND STUDENT.SSEX=男);例4-19 SELECT Sname FROM StudentWHERE NOT EXISTS( SELECT * FROM SC WHERE Sn