Oracle数据库课程讲义.docx
《Oracle数据库课程讲义.docx》由会员分享，可在线阅读，更多相关《Oracle数据库课程讲义.docx（27页珍藏版）》请在冰点文库上搜索。
Oracle数据库课程讲义
Oracle数据库简介
1.介绍课程特点和目标
2.相关背景知识回顾
3.介绍oracle数据库的优点，并比较与db2,Sybase,sqlserver数据库的优势
4.介绍oracle数据库的历史和版本特点
5.介绍安装和使用的基本技巧
Oracle体系结构
体系结构可以分为三个层面来理解：
1.逻辑结构
粒度从细到粗可以分为如下结构：
块，区，段，表空间，数据库，可以用图表述如下：
块
区
段
表空间
数据文件
数据库
段可以跨数据文件存储
2.物理结构
可以分为数据文件，日志文件，控制文件。
此外参数文件虽然不属于数据库物理文件，但是在启动过程中也起到重要作用。
3.内存结构
需要理解数据库和数据库实例的关系。
主要的内存结构是SGA,可以分为如下：
A.DBBuffer
保持buffer,如代码表
再生buffer，使用后就释放的内存，如频繁访问的大表
B.共享池
1.库缓冲区
.共享sql区
保持访问过的sql命令的解释版本
.pl/sql区
保持pl/sql过程，函数的编译版本
2.字典缓冲区
保存数据库字典表的相关内容
C.大共享池
可选的内存区，提供大内存块的隔离内存区
D.固定sqa区
不能手工控制，完全由oracle管理
E.Redobuffer
重做日志缓冲区，管理用于回滚或者重做的内存块
Sqlplus介绍
介绍sqlplus的概念和使用方法
登陆的命令格式
Dos命令提示符下输入sqlplusscott/tiger
如果希望以管理员sys身份登陆必须使用sqlplussys/managerassysdba
关闭和启动oracle实例的命令
Shutdown,shutdowntransactional,shutdownimmediate,shutdownabort,区别如图所示：
允许新连接
等待回话结束
等待事务结束
做检查点并关闭数据库文件
启动需要实例恢复
Shutdown
no
yes
yes
Yesyes
no
Shutdowntransactional
no
no
yes
Yesyes
no
Shutdownimmediate
no
no
no
Yesyes
no
Shutdownabort
no
no
no
Nono
yes
启动命令startup
Startupmount,startupnomount,startup
启动次序和关闭相反，先要启动数据库实例，然后装配控制文件，最后打开数据和日志文件。
Startupnomount,只启动实例，不装配控制文件
Startupmount,启动数据库实例，装配控制文件
Startup,启动数据实例，装配打开所有数据库文件。
运行脚本文件可以用@加上物理文件路径。
Helpindex可以列出所有上下文帮助主题
Set命令可以设置相关环境变量值，常用的有
Setpagesize100
Setlinesize100
Setechoon/off
?
set可以列出所有可以设置的环境参数和使用方法。
常用的编辑sql的命令：
Append,change/old/new
Clearbuffer,del,input,list,runor/
Edi
将缓冲区命令保持至文件命令save
将查询结果保存至文件命令spool
格式化报表的命令:
Columnsalformat$99,990heading‘工资’
Ttitilecenter‘我的标题’skip1left‘测试报表’right‘page:
’format999sql.pno
Breakon+compute命令组合可以产生小计统计的效果
Breakondeptnoskip1onjobskip2
Computeonsumofsaljob
Select*fromemporderbydeptno,job;
Compute可以列出当前compute命令的定义
Clearcompute可以清除该定义
可以在报表结束处现实统计结果
Breakonreport
Computesumofsalonreport
Select*fromemp;
Sql
（一）
分类
1.Ddl
Create,alter,drop,truncate
Createuserkpidentifiedbykpaccountunlock;
Alteruserkpidentifiedbypk;
Dropuserkp;
Truncatetableemp;
Createtableabc（avarchar2（10）,bchar（10））;
Altertableabcmodify（avarchar2（20））;
Altertableabcaddcnumber;
Altertableabcdropcolumnc；
Altertableabcaddconstraintc1check（cbetween1and10）;
altertableabcaddconstraintc2primarykey（a）;
altertableabcaddconstraintc3primarykey（a）;
altertableabcaddconstraintc3foreignkey（b）referencesa（x）;
（createtablea（xchar（10））;altertableaaddconstraintc1primarykey（x））
2.Dml
Select,update,delete,insert
3.Dcl
Grant,revoke,rollback,commit,savepoint
Grantresourcetokp;
Grantselectonemptokp;
Revokeselectonempfromkp;
上机的正常操作顺序应该是先创建表空间，然后创建用户，然后创建相关dbobject
Createtemporarytablespacekp_temptempfile‘‘size50mautoextendonnext50mmaxsize20480m;
Createtablespacekp_datadatafile‘‘sie50mautoextendonnext50mmaxsize20480m;
Createuserkpidentifiedbykpdefaulttablespacekp_datatemporarytablespacekp_temp;
Grantconnect,resourcetokp;
Sql
（二）
系统函数
1.字符
Length,lengthb,ltrim,rtrim,trim,replace,substr
2.日期函数
Sysdate,current_date,next_day
Current_date,sysdate区别
a.前者是当前会话时间，后者是服务器时间
b.有时前者比后者块1秒，四舍五入结果
c.修改时区，将中国从东八区改为东九区，则前者比后者快一个小时
Altersessionsetnls_data_format=’yyyy-mm-ddhh:
mi:
ss’;
Altersessionsetnls_data_format=’yyyy-mm-ddhh24:
mi:
ss’;
Altersessionsettime_zone=’+09:
00’
可以用命令select*fromv$nls_parameters;查询当前nls_date_language
Altersessionsetnls_date_language=AMERICAN;（simplifiedChinese）
可以通过selectusername,sid,serial#fromv$session;
Altersystemkillsession‘sid,serial#’杀死相关会话
Next_day根据相关参数给出对应周几的具体日期
3.类型转换
To_char,to_date,to_number
4.聚集函数
Sum,avg,max,min,count
5.其他
User,decode,nvl,nvl2
Selectsum（decode（sex,’男’,1,0））,sum（decode（sex,’女’,1,0））fromemp;
问题：
统计10,20部门人数？
Selectsum（decode（deptno,10,1,0））,sum（decode（deptno,20,1,0））fromemp;
工资分为三等，一千以内，一千至两千，两千以上，分别统计各部门各等人数
Selectdeptno,sum（decode（greatest（sal,0）,least（sal,1000）,1,0）），sum（decode（greatest（sal,1000）,least（sal,2000）,1,0））,sum（decode（greatest（sal,2000）,sal,1,0））fromemp;
也可以用case命令来实现：
selectx,count（*）asc
from（
selecteno,
（case
whensalbetween0and999then‘一等’
whensalbetween1000and1999then‘二等’
else‘三等'
end）x
fromemp
）
groupbyx
或者
Selectcount（casewhensal<1000then1elsenullend）‘一等’,
Count（casewhensalbetween1000and2000then1elsenullend）‘二等’，
Count（casewhensal>2000then1elsenullend）‘三等’fromemp;
也可以写成：
Selectsum（casewhensal<1000then1else0end）‘一等’,
sum（casewhensalbetween1000and2000then1else0end）‘二等’，
sum（casewhensal>2000then1else0end）‘三等’fromemp;
要注意其异同。
6.分组
7.模糊查询
记住通配符_和%
8.表连接
分为三种，内连接（自然连接），外连接（左，右，全），自连接
A内连接
Selectemp.empno,dept.locformempinnerjoindeptonemp.deptno=dept.deptno
Or
Selectemp.empno,dept.locfromempnaturaljoinonemp.deptno=dept.deptno;
B外连接
Selectemp.empno,dept.deptnofromempleftouterjoindeptonemp.deptno=dept.deptno
Orselectemp.empno,dept.deptnofromempwhereemp.deptno=dept.deptno（+）（左连接）
Selectemp.empno,dept.deptnofromemprightouterjoindeptonemp.deptno=dept.deptno
Orselectemp.empno,dept.deptnofromempwhereemp.deptno（+）=dept.deptno（右连接）
Selectemp.empno,dept.deptnofromempfullouterjoindeptonemp.deptno=detp.deptno（全外连接）
自连接
考虑查询员工和其领导的名字
Selectx.ename,y.enamefromempx,empywherex.mgr=y.empno
9.子查询
10.代数运算
Intersect,union,minus
Sql（三）
伪列rownum,rowid的使用
什么是伪列RowID？
1.首先是一种数据类型，唯一标识一条记录物理位置的一个id，基于64位编码的18个字符显示。
2.未存储在表中，可以从表中查询，但不支持插入，更新，删除它们的值。
RowID的用途
1.在开发中使用频率应该是挺多的，特别在一些update语句中使用更加频繁
2.能以做快的方式访问表中的一行
3.能显示表的行是如何存储的
4.作为表中唯一标识
删除重复数据比较高效
DeleteempaWhereROWIDNotIn（SelectMin（ROWID）FromempaGroupByempno）;
什么是rownum
对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，且rownum不能以任何表的名称作为前缀。
注意selectrownum,id,namefromstudentorderbyname;和selectrownum,id,namefrom（select*fromstudentorderbyname）;的区别
对emp表，显示工资排名第三高的职工信息
selectempno,salfrom（selectrownumasrn,empno,salfrom（select*fromemporderbysaldesc））wherern=3或者
select*from（selectemp.*,dense_rank（）over（orderbysaldesc）rankfromemp）whererank=3;
层次查询中的伪列level
Selectlevel,employee_id,manager_id,first_name||last_namefrommore_employeesstartwith
Employee_id=1connectbyprioremployee_id=manager_id;
可以通过
Selectcount（distinctlevel）frommore_employeesstartwithemployee_id=1connectbyprior
Employee_id=manager_id获得树的层次。
格式化查询结果
Setpagesize999
Columnemployeeformata25
Selectlevel,lpad（‘‘,2*level-1）||first_name||’‘||last_nameasemployeefrommore_employeesstartwithemployee_id=1connectbyprioremployee_id=manager_id;
Sql（四）
小计统计函数rollup,cube
Selectdeptno,job,sum（sal）fromempgroupbyrollup（deptno,job）;
如果希望对以上结果格式化输出，如何更改？
将rollup改为cube，观察结果不同点
使用评价函数rank,dense_rank
考虑问题如下：
找到各部门工资前三高的员工信息
Select*from（selectrank（）over（partitionbydeptnoorderbysaldesc））rk,emp.*fromemp）Twheret.rk<=3
将rank改为dense_rank观察异同
工资为5000的员工在10号部门可以排第几？
Selectrank（10,5000）withingroup（orderbydeptno,sal）fromemp
同义词synonym和序列sequence的定义和使用
同义词分为公共和私有
Createsynonymdeptforscott.emp（私有）
Createpublicsynonymdeptforscott.emp（公有）
序列
Createtabletest（anumber,bvarchar2（20））
Createsequencetest_seqstartwith1incrementby1
Insertintotestvalues（test_seq.nextval,’a’）
Selecttest_seq.currvalfromdual;可以获得当前序列的值
Sql（五）
习题课
1.关于教学数据库的关系模式如下：
s（s#,sname,age,sex）,sc（s#,c#,grade）,
c（c#,cname,teacher）
a.查询至少选修王五老师所教授课程的女生姓名
selectdistinctsnamefroms，sc,c
wheres.sex='女'ands.sno=o=oandc.teacher='王五'
b.查询张三同学不选的课程号
selectc#fromcwhereNOTexists（select*fromsc,swheres.sname='zhang'andsc.s#=s.s#）
selectc#fromcwherec#notin（selectsc.c#froms,cswheres.sname='zhang'ands.s#=cs.c#）
selectc#fromcminus（selectc#fromsc,swheres.sname='zhang'ands.s#=cs.c#）
c.查询至少选修了两门课程的学生学号
selects#fromscgroupbys#havingcount（*）>=2;
selectdistinctx.s#fromscx,scywherex.s#=y.s#
andx.c#!
=y.c#
d.查询全部学生都选修的课程号与课程名
selectc#,cnamefromc
wherec#in（selectc#fromsc
groupbyc#
havingcount（*）=（selectcount（*）froms））;
selectc#,cnamefromcwherenotexists（select*fromswherenotexists（select*fromscwhere
s#=s.s#andc#=c.c#））
e.查询选修了王五老师所教授所有课程的学生学号
selectdistincts#fromscxwherenotexists（
select*fromcwheretechar='wang'andnotexists（
select*fromscywherey.s#=x.s#andy.c#=c.c#））
2.关系模式如下
markext（mno,mname,city）,item（ino,iname,type,color）,
sales（mno,ino,price）
a.列出北京每个商场都有售，且售价都超过10000元的商品号和商品名
selectino,inamefromitemwherenotexists（
select*frommarkedtwherecity='bj'andnotexists（select*fromsaleswhereprice>10000andmarkext.mno=sales.mnoandsales.ino=item.ino））
b.列出在不同商场中最高售价和最低售价之差超过100的商品号，最高售价和最低售价
selectino,max（price）,min（price）fromsalesgroupbyinohavingmax（price）-min（price）>100
selectdeptnofromdeptwherenotexits（select*fromemp
wheredept.deptno=emp.deptno）
Sql（六）
索引和优化
索引可以分为B树索引，位图索引，基于函数的索引，分别对应不同的应用需求
索引的创建语法：
CREATEUNIUQE|BITMAPINDEX.
ON.
（|ASC|DESC,
|ASC|DESC,...）
TABLESPACE
STORAGE
LOGGING|NOLOGGING
COMPUTESTATISTICS
NOCOMPRESS|COMPRESS
NOSORT|REVERSE
PARTITION|GLOBALPARTITION
相关说明
1）UNIQUE|BITMAP：
指定UNIQUE为唯一值索引，BITMAP为位图索引，省略为B-Tree索引。
2）|ASC|DESC：
可以对多列进行联合索引，当为expression时即“基于函数的索引”
3）TABLESPACE：
指定存放索引的表空间（索引和原表不在一个表空间时效率更高）
4）STORAGE：
可进一步设置表空间的存储参数
5）LOGGING|NOLOGGING：
是否对索引产生重做日志（对大表尽量使用NOLOGGING来减少占用空间并提高效率）
6）COMPUTESTATISTI
升级会员 