oracle基础知识整理Word文件下载.docx
《oracle基础知识整理Word文件下载.docx》由会员分享，可在线阅读，更多相关《oracle基础知识整理Word文件下载.docx（18页珍藏版）》请在冰豆网上搜索。
字段n数据类型（长度）
）
createtable表名asselect……
select*into新表from旧表
●alter
altertable表名
add字段数据类型（长度）增加字段
modifycolumn字段字段类型（长度）修改字段
drop字段删除字段
rename旧字段名to新字段名
rename旧表名to新表名
●drop
droptable表名删除表
dml
数据操纵语言（insertupdatedeleteselect）
●insert
insertinto表名values（字段1，……字段n）;
按表的字段依次顺序插入
insertinto表名（字段1，字段2）values（值1，值2）按字段插入
insertinto表名（字段……）select……将查询结果插入
●update
update表名set字段=值[where条件]
●delete
deletefrom表名[where条件]
trunctable表名（不可回退的删除速度快）
●select
select[distinct]字段
from表名
where条件
groupby分组
having分组后筛选条件
orderby排序
进阶
复杂查询
子查询
●单列查询
select*from表名where字段=（select字段from表名where条件）
select*from表名where字段in（select字段from表名where条件）
●多列查询
select*from表名where（字段1，字段2）=（select字段1，字段2from表名where条件）
●all
select*from表名where字段表达式all（select……）
●any
select*from表名where字段表达式any（select……）
●子表
select*
fromt1,（select……）t2
连接查询
●自然连接
select*fromt1,t2wheret1.z=t2.z
select*fromt1innerjoint2ont1.z=t2.z
●左连接（左表全部加右表符合条件的）
select*fromt1leftjoint2ont1.z=t2.z
select*fromt1,t2wheret1.z=t2.z（+）
●右连接
select*fromt1rightt2ont1.z=t2.z
select*fromt1,t2wheret1.z（+）=t2.z
●全连接
select*fromt1fulloutjoint2ont1.z=t2.z
分页查询
select*from（selectt1.*,rownumnumfrom（select语句）t1wherenum<
=最大值）twherenum>
=最小值
合并查询
union去重复联合查询unionall不去重复intersect取交集minus取左表有右表没有的值
函数（常用）
单行函数:
查多行只出一行（多用于分组）
多行函数：
查几行出几行
●聚合函数
sum（）avg（）max（）min（）count（）avg会忽略null值
●转换函数
convert（数据类型，字段，格式）cast（字段as数据类型）
●数值函数
abs（）绝对值floor（）小于或等于的最大正式ceil（）大于等于的最小整数
round（）四舍五入trunc（）截取小数mod（值1，值2）取余
●日期函数
add_mouths（）
●字符串函数
substr（字段，起始位置，长度）
lower（）将英文换为小写
upper（）将英文换为大写
length（）查看长度
replace（字段，原值，替换为值）
●其他函数
case字段when值then…when值then…else…end
casewhen字段=？
then…else…end
decode（字段，条件1，值1，条件2.值2……）
dump（字段）查看字段类型
nvl（字段，值）若字段为null则值为值
事务
数据操纵的一组集合
savepoint保存点名字设置保存点
……dml
rollbackto保存点名字回退到保存点，取消设置保存点之后的操作
commit提交
rollback回退
☞可以设置多个保存点，但保存点只可以回退一次，提交之后不可回退
完整性
●基本
notnull非空只能在行级定义
unique唯一不可以重复，但可以为null并且可以多处为null
check检查约束
default默认
primarykey主键非空的唯一标示可以自动创建索引
foreignkey外键跟主表的主键或者唯一约束所在列对应
建表的时候先建主表再建从表，删除的时候先删从表再删主表
●定义
行级定义：
在字段名之后直接定义
表级定义:
建完字段后再用constraint定义
constraint约束名约束类型字段
●修改
addconstraint约束名约束类型字段
modifyconstraint约束名约束类型字段
dropconstraint约束名
序列
createsequence序列名
startwith起始值
incrementby步长
maxval最大值
minval最小值
cycle/nocycle循环、不循环
nocache/cache缓存
序列名.currval当前值
序列名.nextval下一个值
视图
createview视图名asselect……
索引
createindex索引名on表名（字段名）
高级
数据类型
数值型：
number（长度，精度），decimal、int
字符串：
char,varchar,nvarchar,nchar,clob,blob
日期：
date,timestram
●表
变量名表名.字段名%type表的某个字段类型
变量名表名%rowtype某个表的记录类型
●复合类型
type类型名isrecord（
变量名数据类型，
变量名类型名
●游标
type游标名isrefcursor定义游标类型
游标变量游标名
open游标变量forselect……打开游标
fech游标变量into循环取值
close游标变量关闭游标
游标变量%notdate
控制结构
●判断
·
if条件then……endif
if条件then……else……endif
if条件then……elsif条件then……else……endif
●循环
when条件loop……endloop
loop……exitwhen条件endloop
存储过程
●语法
createprocedure过程名（变量名in数据类型，变量名out数据类型）
as
declare变量名数据类型--定义变量，可以用：
=赋初值
begin
处理语句
exception--异常处理
when异常处理then……
whenothersthen……
end
●分页存储过程
createpackagemy_package
is
typemy_cursorisrefcursor
end
createprocedurecut（v_in_tableinvarchar,v_in_sizeinnumber,v_in_pageinnumber,
v_out_countoutnumber,v_out_yeoutnumber,v_out_rsoutmy_package.my_cursor）
as
declarev_sqlvarchar（2000）;
declarev_startnumber;
declarev_endnumber;
begin
executeimmeteate‘selectcount（*）into’||v_out_count||’from’||v_in_table;
ifmod（v_out_count,v_in_size）=0then
v_out_ye:
=v_out_count/v_in_size;
else
=v_out_count/v_in_size+1;
endif;
v_start:
=v_in_size*（v_in_page-1）+1;
v_end:
=v_in_size*v_in_page
v_sql:
=’select*from（selectt1.*,rownumnumfrom（select*from‘||v_in_table||’））twherenum’>
=v_start;
openv_out_rsforv_sql;
函数
createfunction函数名（参数列表）
return返回值类型
as
declare变量名数据类型；
dml……
return变量名；
包
包用于把同一对象的操作放一块，方便管理
●创建包（用于定义类型）
createpackage包名
is
type类型名isrecord;
type游标名isrefcursor;
function函数名（参数列表）return返回值类型；
procedure过程名（参数列表）；
●创建包体
createpackagebody包名
变量名类型名；
变量名游标名；
function函数名（参数列表）return返回值类型
……
end;
procedure过程名（参数列表）
触发器
●dml
createtrigger触发器名
before/after--在操作之前/之后执行之前的值为：
new.字段名之后的值为:
old.字段名
delete[orupdateorinsertof字段名]
on表名
[foreachrow]--加上此句为行级触发，每dml一行都会触发，不加为表级触发，不考虑多少行，只执行一次
--处理语句（可以抛异常调用函数raise_application_error（错误号，‘错误信息’）
错误号在-20001到-29000之间；
可以用deletingupdatinginserting判断操作类型）
end;
●ddl
createtrigger触发器名
after事件
on用户名.shame
●系统
before/after
事件（logonlogoffstartupshutdown）
ondatabase
end
●管理触发器
altertrigger触发器名diable锁定触发器
altertrigger触发器名enable解锁触发器
droptrigger触发器名删除触发器
Java调用数据库
jdbc
●直接调用
publicstaticvoidmain（String[]args）throwsException{
//TODOAuto-generatedmethodstub
Connectionconn=null;
PreparedStatementps=null;
Statementstmt=null;
ResultSetrs=null;
try{
Class.forName（"
oracle.jdbc.driver.OracleDriver"
）;
conn=DriverManager.getConnection（"
jdbc:
oracle:
thin:
@127.0.0.1:
1521:
orcl"
"
test"
//预定义类型
ps=conn.prepareStatement（"
"
ps.setString（1,"
ps.executeUpdate（）;
//执行增删改
rs=ps.executeQuery（）;
//执行查询，需要定义结果集来接收结果
while（rs.next（））{
System.out.println（rs.getString
（1））;
}
//普通类型
stmt=conn.createStatement（）;
stmt.executeUpdate（"
sql"
rs=stmt.executeQuery（"
}catch（Exceptione）{
//TODOAuto-generatedcatchblock
e.printStackTrace（）;
}finally{
if（rs!
=null）{
rs.close（）;
if（stmt!
stmt.close（）;
if（ps!
ps.close（）;
if（conn!
conn.close（）;
}
}
●从外部文件获取连接信息
Propertiespp=newProperties（）;
FileInputStreamfis=null;
fis=newFileInputStream（"
jdbc.properties"
pp.load（fis）;
Stringurl=pp.getProperty（"
url"
System.out.println（url）;
}catch（FileNotFoundExceptione）{
事务处理
Connectionconn=null;
//设置为不自动提交
conn.setAutoCommit（false）;
mit（）;
conn.rollback（）;
//关闭资源
调用无返回值过程
CallableStatementcs=null;
try{
Class.forName（"
conn=DriverManager.getConnection（"
cs=conn.prepareCall（"
{call过程名（?
）}"
cs.setString（1,"
值"
cs.execute（）;
有非列表返回值过程
//1?
输入参数2?
输出参数
cs=conn.prepareCall（"
，?
cs.setString（1,"
cs.registerOutParameter（2,oracle.jdbc.OracleTypes.VARCHAR）;
cs.execute（）;
Strings=cs.getString
（2）;
System.out.println（s）;
有列表型返回参数
输出参数（游标）
cs.registerOutParameter（2,oracle.jdbc.OracleTypes.CURSOR）;
rs=（ResultSet）cs.getObject
（2）;
System.out.println（rs.getString（"
））;
数据库的备份与恢复
●备份
expuserid=用户名/密码@数据库实例table=（t1,t2）file=”地址.dmp”[rows=n]
expuserid=用户名/密码@数据库实例owner=用户名file=”地址.dmp”
expuserid=用户名/密码@数据库实例full=yfile=”地址.dmp”
●恢复
impuserid=用户名/密码@数据库实例table=（t1,t2）file=”地址.dmp”[touser=其他用户]
impuserid=用户名/密码@数据库实例file=”地址.dmp”[fromuser=用户1touser=用户2]
impuserid=用户名/密码@数据库实例table=（t1,t2）file=”地址.dmp”
升级会员 