Oracle知识点总结.docx
《Oracle知识点总结.docx》由会员分享，可在线阅读，更多相关《Oracle知识点总结.docx（34页珍藏版）》请在冰豆网上搜索。
Oracle知识点总结
Oracle知识点总结
根据阎赫老师讲义整理Zealjiang
1、Oracle数据库的安装和配置
⑴OracleInternet级数据库
SQLServer中小企业级数据库
Access桌面级数据库
⑵Oracle的安装
注意：
来源和目标的目录不允许有中文或空格
⑶Oracle数据库启动时必需开启的后台服务
①OracleOrahome90TNSListener使第三方的软件或语言访问
②OracleServiceETCOracle的实例CRUD增删改查
注意：
②中的ETC是你起的实例的名字
⑷Oracle的开发工具
①DOS界面的开发平台->运行->sqlplus
②Oracle本身基于DOS的平台->运行->sqlplusw
③OracleEnterpriseManagerConsole
④PL/SQL Developer7.1.5
⑸创建一个表的完整流程
①创建一个数数库
例子：
创建一个数据库ETC,物理文件放在F:
\，初始化1m,自增长开启
createtablespaceetc
datafile'f:
\etc.dbf'
size1m
autoextendon;
删除表空间
droptablespace数据库名称;
②为该数据库创建一个帐号和密码
语法：
createuser用户名称
identifiedby密码
defaulttablespace数据库名称
注意：
1、在Oracle中账号和数据库one2one绑定
2、Oracle的密码不允许为纯数字或空
3、需要在system账号完成动作
修改Oracle的账号密码
语法：
alteruser用户
identifiedby新密码
③用grant权限to用户为该帐户授权
语法：
grant权限名称to用户;
撤销权限
语法：
revoke权限名称from用户名;
④在该帐号下创建表
⑹Oracle中的事务处理
Transacation事务
特点：
整体提交（commit）整体回滚（rollback）
事务的四个特性
①原子性不可分割
②持久性->当数据整整写入到数据库物理文件中后，该数据被持久化
③隔离性->事务之间相互独立互不干扰
④一致性->数据安全
⑺Oracle中的保留点（还原点）
关键字：
savepoint
使用方法：
savepoint名称;
如进行保留点回滚
rollbacktomark2
⑻Oracle备份数据表数据
①createtable备份表名称as查询语句;
②Oracle备份表结构
createtableemp_bakasselect*fromempwhere1=2;
2、Oracle的函数
⑴function（系统预定义函数）
函数和存储过程相比，在于函数必须有返回值，而存储过程只有输出参数。
语法：
createorreplacefunction函数名称
return返回类型
as
PL/SQL语句块
例子：
输入一个员工工号，返回该员工所在部门平均工资
createorreplacefunctionfun_demo1（enoemp.empno%type）
returnemp.sal%type
as
avgsalemp.sal%type;
begin
selectavg（sal）intoavgsalfromempwhereempno=eno;
returnavgsal;
end;
调用：
selectfun_demo1（7788）fromdual;
publicintadd（intnum1,intnum2）{
intres=0;
res=num1+num2;
returnres;
}
⑵单行函数（单值函数）：
函数有且只有返回1个值
|-字符函数substrinstr
①字符串连接函数：
concat
concat（字符串1,字符串2）
Oracle中字符连接操作符号"||"
②字符串对齐函数：
lpad->leftpadding左填充/rpad
语法：
lpad（字符串,预留位置,填充字符）
SQL:
selectlpad（ename,10,''）fromemp;
③字符串截取函数：
substr->substring
语法：
substr（字符串,截取的起始位置,截取个数）
substr（字符串,截取的起始位置）
SQL:
>selectsubstr（ename,1,3）fromeemp;
④字符查找函数：
instr->innerstring
语法：
instr（字符串,待找的字符,查找的起始位置,出现次数）
SQL:
>selectename,instr（ename,'T',1,1）fromemp;
注意：
当返回值为0时，字符不存在
⑤函数：
initcap->initialcaptor
语法：
initcap（字符串）
SQL:
>selectinitcap（ename）fromemp;
⑥函数：
length
语法：
length（字符串）
SQL:
>select*fromempwherelength（ename）=5;
⑦函数：
lower/upper
语法：
lower（字符串）
SQL:
>selectlower（ename）fromemp;
|-日期函数sysdateadd_months
①函数：
sysdate
SQL:
>selectsysdatefromdual;dual-无实际意义，函数测试或其他测试使用
②月份差：
months_between
语法：
months_between（日期1,日期2）
SQL:
>select*fromempwheremonths_between（sysdate,hiredate）>=144
③函数：
last_day
语法：
last_day（日期数据）
SQL:
>select*fromempwherelast_day（hiredate）-2=hiredate;
④函数：
add_months
语法：
add_months（时间,添加月份）
SQL：
>selectadd_months（sysdate,3）fromdual;
|-转换函数to_charto_date
①转换函数to_char
语法：
to_char（时间数据,制定格式）
SQL:
>selectto_char（sysdate,'yyyy-mm-ddhh24:
mi:
ssddddday'）fromdual;
yyyy-mm-dd年月日
hh24:
mi:
ss时分秒（24）
d一周第几天
ddd一年的第几天
day星期几
②to_date:
将文本类型数据按照制定格式转换成时间类型数据
语法：
to_date（'具体的时间字符数据','时间字符串格式'）
to_date（'2010-01-19','yyyy-mm-dd'）
注意：
Oracle中的date字段类型及其特殊
|-数学函数absceilfloor
abs（n）绝对值
ceil（n）向上取值ceilling天花板ceil（1.1）->2
floor（n）向下取值floor地板floor（2.9）->2
mod（m,n）求模取余mod（5,2）->1
power（m,n）m的n次方power（2,8）->256
round（m,n）四舍五入m待处理数据n精度round（3.1415,2）->3.14
trunc（m）整数截取trunc（3.1415）=3
sign（m）符号
sqrt（m）平方根
|-混合函数usernvl
①user:
返回当前登录账号
SQL>selectuserfromdual;
②nvl:
替空函数
语法：
nvl（字符串,替换后的数据）
注意：
替换后的数据必须与该字段类型保持一致
SQL>selectename,nvl（comm,0）fromemp;
⑶分析函数：
数据分析和挖掘功能DataMiningrankdense_rank
作用：
主要用于排名使用
函数：
rank
①语法：
rank（）over（orderby字段名称）
SQL:
>selectrank（）over（orderbysaldesc）,ename,salfromemp;若排名相同下一个将跳转
SQL:
>selectdense_rank（）over（orderbysaldesc）,ename,salfromemp;连续
②语法：
rank（参数1，参数2）withingroup（orderby字段1，字段2）
SQL:
>selectename,rank（2850,'BLAKE'）withingroup（orderbysal,ename）fromemp;
⑷分组函数：
类似于SQLServer中的聚合函数sumavgcount
groupby分组关键字having条件
3、Oracle数据库的查询
⑴制定区间查询
Oracle中伪列：
Oracle为每张数据表（包括用户自定义创建的数据表）都自动创建两个位列分别是：
rowid、rownum
通过命令：
SQL>desc表名;--查看表结构
rowid:
由18个字符组成唯一标识每一行
rownum:
行号
⑵介绍问题：
使用Oracle分层查询以数据结构显示emp表中的人员关系
语法：
selectlpad（ename,level*5,''）fromemp
connectbypriorempno=mgr
startwithmgrisnull;
⑶并集：
查询部门10的办事员和部门20的经理
关键字：
unionall
select*fromempwheredeptno=10andjob='CLERK'
unionall
select*fromempwheredeptno=20andjob='MANAGER';
注意：
unionall不会消除查询结果中的重复数据,union会消除查询结果中的重复记录
⑷交集：
查询部门10和部门20都有的工作类型
关键字：
intersect
selectjobfromempwheredeptno=10
intersect
selectjobfromempwheredeptno=20;
⑸差集：
查询部门30中有，而部门10中没有的工作类型
关键字：
minus
selectjobfromempwheredeptno=30
minus
selectjobfromempwheredeptno=10;
4、Oracle数据库的数据对象
Oracle的数据库对象：
用户、表、约束、序列、视图、同义词和索引
定义：
但凡使用"create"开头创建的对象称之为数据库对象。
⑴锁定/解除用户
语法：
alteruser用户名称accountlock;
注意：
该指令一般在system账号下输入
解除用户锁定
语法：
alteruser用户名称accountunlock;
SQL>alteruserscottaccountunlock;
注意：
Oracle11g中scott账号是默认锁定的，需要进行解锁处理。
⑵用户授权/撤销
grant权限名称to用户名称;
撤销用户权限
revoke权限名称from用户名;
⑶表
数据字段的类型
①数字类型：
number
语法：
number（长度,精度）
number（5,2）数字长度为3，精度为2范围：
-999.99~999.99
number（5）数字长度为5，精度为0范围：
-99999~99999
number精度默认为0范围：
-32767~32768
②字符类型：
varchar2可变长度char字符类型
语法：
varchar2（长度）
③日期类型：
date
④long数据类型：
该字段最大存储空间为2GB，该字段不允许添加索引
LOB数据类型：
该字段最大存储空间为4GB，该字段不允许添加索引
CLOB：
大字符类型->文章（小说，cnki）
BLOB：
大二进制类型->图片、音频、视频.....多媒体文件
FLOB：
文件定位器->内存指针
⑷创建数据表的语法结构：
createtable表名
（
字段名称1类型[约束],
字段名称1类型[约束],
……
字段名称n类型[约束]
）
①使用desc表名查看表结构
②给表添加字段address和telephone
语法：
altertable表名add（字段名称类型）;
③删除表字段address
语法：
altertable表名dropcolumn字段名称;
④修改表中已有字段的类型
语法：
altertable表名modify（字段名称新类型）;
⑤修改表名称
语法：
rename原表名to新表名;
约束
完整性约束=准确性+一致性
⑸约束的四大分类：
①实体完整性约束（行约束）->尽量减少数据表中数据的冗余（重复的数据）
技术实施：
主键约束、唯一约束
语法：
altertable表名addprimarykey（字段名称）;
altettable表名addunique（字段名称）;
添加非空约束
语法：
altertable表名modify（字段名称notnull）;
②域完整性约束（列约束）->达到数据的准确性，控制数据的大小或范围或格式
技术实施：
check检查约束
altertable表名addconstraint约束名称check（条件）;
③引用完整性约束（表间约束）->达到数据的一致性
技术实施：
外键约束
添加外键约束
altertable外键表addconstraint约束名称foreignkey（外键字段）references主键表（主键字段）;
删除外键约束
语法：
altertable表名dropconstraint约束名称;
删除匿名约束
语法：
altertable表名modify（字段名称null）;
④自定义完整性约束->以上三种数据库内置约束不能满足开发人员的需求是，需要开发人员自定一些约束条件
技术实施：
触发器
⑹使用sql脚本批量插入数据
使用命令SQL>@路径+文件名称
SQL>@f:
/a.sql;
⑺序列
定义：
Oracle中使用sequence来实现字段的自增长功能，和SQLServer中的identity属性类型
语法：
createsequence序列名称
startwith起始数字
incrementby增长量;
用法：
序列对象通过两个重要的属性进行访问取值
.nextval->nextvalue->下一个值
例子：
selectseq_1.nextvalfromdual;
.currval->currentvalue->当前值
例子：
selectseq_1.currvalfromdual;
如何实现其自增长功能
通过语法结构将序列对象与对应的数据表进行绑定，实现其自增长功能。
例子：
createtableusers（useridnumber
（2）,usernamevarchar2（10））;
createsequenceseq_usersstartwith1incrementby1;
insertintousersvalues（seq_users.nextval,'alvin'）;
注意：
Oracle建议一个序列对象尽与一张数据表进行绑定
技巧：
createsequence序列名称;默认从1每次增长1
⑻视图
作用：
1、简化复杂的SQL语句
2、提高数据的访问安全性
语法：
createorreplaceview视图名称
as
复杂的SQL查询语句
视图是一张虚拟的数据表，在Table对象中不存在，只存在于内存中
注意：
若视图由1张基表组成，修改视图将会修改基表数据，若视图由多张表组成，则不会修改基表数据
⑼同义词
作用：
Oracle中的同义词提供各种数据库对象（表）的别名，目的在于提高数据表访问的安全性，尤其多用户并发访问时。
语法：
createsynonym表的别名for表名;
SQL>createsynonymbak1foremp;
扩展：
公有同义词public
语法：
createpublicsynonym别名for表名;
⑽索引
作用：
提高SQL查询语句按照制定字段查询的效率
语法：
createindex索引的名称on数据表（字段）
例子：
为hiredate字段添加索引，提高按日期查询的SQL语句的效率
createindexidx_hireonemp（hiredate）
原理：
空间换效率
SQL语句索引优化规则
①不带where条件的SQL语句一定不能使用索引
②在where条件中有索引的字段不能使用函数
例子：
假设我们查询在1987年参加工作的所有员工
SQL>select*fromempwhereto_char（hiredate,'yyyy'）='1987';
SQL>select*fromempwherehiredate=to_date（'1987','yyyy'）;
③在where条件中有索引的字段不能参与运算
例子：
查询在10000天以前参加工作的员工信息
SQL>select*fromempwheresysdate-hiredate>10000;
SQL>select*fromempwherehiredate④尽量少用notin,notexists,like'%'等关键字
⑤书写查询语句的时候，应该首先考虑关联查询、其次考虑集合查询，最后考虑子查询
⑥where条件中限制性强的条件应该写在where最后的一个条件
where条件从右向左经行条件解析
⑦多表查询中，驱动表应该是返回数据少的表
例子：
查询部门account的所有员工信息
SQL>selecte.*,d.dnamefromempe,deptdwheree.deptno=d.deptnoandd.dname='ACCOUNTING';
⑾Oracle中常用的数据字典
Oracle是以中以表管表的模式，同时数据字典是有表或视图组成。
数据字典的分类：
USER_xxx：
表示当前用户所拥有的数据库对象
ALL_xxxx：
表示当前用户与权力查看的数据库对象
DBA_xxxx：
表示数据中所拥有的全部对象，只有在超级管理员级别下才可查看。
xxxx：
代表数据库对象的复数形式，例如：
tablesindexes.......
5、Oracle数据库的多表查询
⑴等连接：
selectemp.ename,dept.dnamefromemp,deptwhereemp.deptno=dept.deptno;
使用内连接模式编写：
⑵内连接：
innerjoin......on......
selectemp.ename,dept.dnamefromempinnerjoindeptonemp.deptno=dept.deptno;
分析：
部门为主显示字段，dept为主表
姓名为辅助显示字段，emp为辅助表
⑶外连接：
selectd.dname,e.enamefromempe,deptdwheree.deptno（+）=d.deptno;
6、Oracle数据库的游标
游标cursor
游标的分类：
隐式游标：
Oracle自动应以一个隐式游标名称为SQL,该游标不被程序员控制，自动开启、操作及结束。
显式游标：
可供程序员自己创建及操作
|---静态游标
|---动态游标
⑴静态游标loop循环游标/for循环游标
====loop循环游标=====
步骤1：
创建一个游标
cursor游标名称isSQL查询语句;
步骤2：
开启游标
open游标名称;->执行定义的SQL查询语句并将结果集合存放到游标中
步骤3：
使用loop循环遍历游标中的数据并进行相应处理
loop
fetch游标名称into变量;
exitwhen游标名称%notfound;
.....
endloop;
步骤4：
关闭游标
close游标名称;
例子：
显示部门编号为10的员工姓名
declare
--步骤1：
创建一个游标
cursormycurisselectenamefromempwheredeptno=10;
enemp.ename%type;
begin
--步骤2：
开启游标
openmycur;
--步骤3：
使用loop循环遍历游标中的数据并进行相应处理
loop
fetchmycurintoen;
exitwhenmycur%notfound;
dbms_output.put_line（en）;
endloop;
--步骤4：
关闭游标
closemycur;
end;
=======for循环游标=======
declare
--步骤1：
创建一个游标
cursormycurisselect*fromempwheredeptno=10;
erowemp%rowtype;
begin
--步骤3：
使用for循环遍历游标中的数据并进行相应处理
forerowinmycur
loop
dbms_output.put_line（erow.ename）;
endloop;
end;
⑵动态游标（扩展）
升级会员 