Oracle知识点总结.docx
《Oracle知识点总结.docx》由会员分享,可在线阅读,更多相关《Oracle知识点总结.docx(34页珍藏版)》请在冰豆网上搜索。
![Oracle知识点总结.docx](https://file1.bdocx.com/fileroot1/2023-8/13/51224c78-5136-458a-a85c-54c2f351f5bf/51224c78-5136-458a-a85c-54c2f351f5bf1.gif)
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;
⑵动态游标(扩展)