Oracle知识点新.docx
《Oracle知识点新.docx》由会员分享，可在线阅读，更多相关《Oracle知识点新.docx（22页珍藏版）》请在冰点文库上搜索。
Oracle知识点新
DDL_DML_DQL
创建用户：
CREATEUSERMARTIN-------创建用户名
IDENTIFIEDBYmartinpwd------设定密码
DEFAULTTABLESPACEUSERS------设置表空间
TEMPORARYTABLESPACETEMP;-------临时表空间
授予权限：
GRANTCONNECTTOMARTIN;
CONNECT角色允许用户连接至数据库，并创建数据库对象。
GRANTRESOURCETOMARTIN;
RESOURCE角色允许用户使用，数据库中的存储空间。
GRANTCREATESEQUENCETOMARTIN;
此系统权限允许用户在当前模式中创建序列，此权限包含在CONNECT角色中。
授予用户MARTIN操作TEST表对象的权限：
GRANTSELECTONTESTTOMARTIN;允许用户查询TEST表的记录。
GRANTALLONTESTTOMARTIN;允许用户插入、删除、更新和查询。
ALTERUSER命令可用于更改口令：
ALTERUSERMARTINIDENTIFIEDBYmartinpass;
DROPUSER命令用于删除用户：
DROPUSERMARTINCASCADE;
DDL:
创建表
createtabletable_name（
column1varchar2（32）primarykey,--列名类型（长度）修饰符primarykey代表该列为主键
column2varcahr2（20）notnulldefault'默认值',--notnull设定该列不能为空,default设定该列的默认值
column3number（5,3）unique--设定该列值唯一,不能重复
）;
修改表
altertabletable_nameadd（columnvarchar2（20））;--添加列
modify--修改列
remove--删除列
清空表
truncatetabletable_name;--清空之后不能回滚
删除表
droptabletable_name;
DML:
插入数据
insertintotable_name（column1,column2,....）values（value1,value2,value3...）;--值必须与指定列一一对应
insertintotable_namevalues（value1,value2,value3...）;--值必须与表定义结构一致
insertintotable_nameselectcolumn1,colum2...fromtable_name1where....--将从table_name1中查询到的结果插入到table_name中
修改数据
updatetable_namesetcolumn1=value1,column2=value2..wherecolumn3=value3and...
删除数据
deletefromtable_namewherecolumn1=value1and...
DQL:
查询数据
selectcolumn1,column2...fromtable_namewherecolumn1=value1and...
排序
orderbycolumndesc/asc--desc降序,asc升序
分组
groupbycolumnhavingcolumn1=value1--having是分组条件
查询条件
select...from...wherecolumn1in（value1,value2,value3）--当column1在括号中出现就将该记录查询出来
select...from...wherecolumn1betweenvalue1andvalue2--当column1的值在value1和value2之间时将该记录查询出来
select...from...wherecolumn1like'_value%'--模糊查询_代表一个字符,%代表多个字符
select...from...wherecolumn1isnull--当column1的值为null就将该记录查询出来
连接查询
左外连接-以左边表为基准根据on条件查找右边的表对应数据生成一个临时表供查询
select
...
from
table1t1
left（outer）join
table2t2
on
t1.column1=t2.column2
where
...
右外连接-以右边表为基准根据on条件查找左边表对应数据生成一个临时表供查询
select
...
from
table1t1
right（outer）join
table2t2
on
t1.column1=t2.column2
where
...
内连接-以两边表为基准,当一边出现空值时不选取该行数据
写法一
select
...
from
table1t1
innerjoin
table2t2
on
t1.column1=t2.column2
where
...
写法二
select
...
from
table1t1,
table2t2
where
t1.column1=t2.column2
and
...
全连接-一两边表为基准,当一边有值时查询改行,另一边以空值代替
select
...
from
table1t1
fulljoin
table2t2
on
t1.column1=t2.column2
where
...
exists（）-当括号中查询有值时,该条记录将被查询出来,通常用exists来代替in
select*fromt_schoolsc
where
exists（
select1fromdualwheresc.id>4
）
查询并集-union-默认去重复行,如果要不去重复使用unionall
（select1fromdualwheresc.id=1）
union
（select1fromdualwheresc.id=2）
union
（select1fromdualwheresc.id=5）
union
（select1fromdualwheresc.id=6）
查询交集-intersect
（select*fromt_personwhereid>100）
intersect
（select*fromt_personwhereheight>1.70）
查询差集-minus
函数_锁_表分区
sysdate--获得系统当前时间
函数
to_date（'2011-09-0209:
04:
00','YYYY-MM-DDHH24:
MI:
SS'）--将字符串按照指定的格式转换为时间类型
to_char（sysdate,'YYYY-MM-DDHH24:
MI:
SS'）--将时间对象按照指定的格式转换为字符串
add_months（sysdate,2）--为时间加上指定月份值
last_day（sysdate）--取当月最后一天
trunc（sysdate,'MM'）--按指定格式截取时间,将指定格式之后的值都取0
lower（'ABC'）--将字符串转换为小写
upper（'abc'）--将字符串转为大写
substr（'abcdefg',2,3）--将字符串从下标2开始截取3个字符
initcap（'helLowoRld'）--将字符串格式化,每个单词首字母大写,其余字符小写
trim（'hello'）--去掉字符串两端的空格字符
length（'abc'）--获取字符串的长度
decode--相当于java中的switch,当column的值为condition1时,取value1...如果找不到对应的condition,取defaultValue,如果找不到defaultValue,取null
decode（
column,
condition1,value1,
condition2,value2,
condition3,value3,
...
defaultValue
）
abs（-20）--取绝对值
ceil（12.01）--（13）取大于该小数的最小整数
floor（12.06）--（12）取小于该小数的最大整数
power（x,y）--求x的y次方
round（m,n）--将m值保留n位小数,对n+1位四舍五入
trunc（m,n）--将m值保留n位小数,不会对n+1位四舍五入
nvl（column,value）--当column不为null时,取column的值,否则取value值
nvl2（column,value1,value2）--当column不为空时,取value1,否则取value2
nullif（column1,column2）--当column1的值等会column2的值时,返回null,否则返回column1的值
avg（column）--求平均数
min（column）--取最小值
max（column）--取最大值
sum（column）--求和
count（column）--求总记录数
分析函数:
主要用来做累计排名
row_number（）--直接累计排名,当值相同时排名也不相同
rank（）--当值相同时,排名相同,后续记录跳跃相应的排名数
dense_rank（）--当值相同时,排名相同,后续记录不跳跃
--使用时必须用over函数指定排名规则
row_number（）over（orderbyheightdesc）ashe_order
锁
锁是数据库用来控制共享资源并发访问的机制。
锁用于保护正在被修改的数据
直到提交或回滚了事务之后，其他用户才可以更新数据
行级锁
当执行insert,update,delete,select...from...forupdate
表级锁
共享锁:
允许多个用户同时锁定同一张表,锁定表之后其他用户只能查询数据,不能更新
locktabletable_nameinsharemode
排他锁:
在同一时间仅允许一个用户锁定一张表,其他用户要锁定表必须等待该用户释放锁,使用nowait不等待,直接报错
locktabletable_nameinexclusivemode
当两个事务相互等待对方释放资源时，就会形成死锁,Oracle会自动检测死锁，并通过结束其中的一个事务来解决死锁,右边是一个死锁的例子
表分区:
允许将一张表中的数据分成多个分区来存储,提高检索速度,保证数据安全.
范围分区
以表中一个列或一组列的值进行范围段划分来分区,该列尽可能的是数值或日期型
createtablet_employee（
idnumber（8）primarykey,
namevarchar（20）,
salarynumber（10,2）
）
partitionbyrange（salary）（
partitionpa1valueslessthan（5000）,
partitionpa2valueslessthan（10000）,
partitionpa3valueslessthan（20000）,
partitionpa4valueslessthan（maxvalue）--当列的值为null,也被划分到maxvalue分区
）
查询或删除的时候可以使用partition（分区名）来指定要查询的分区
select*fromt_employeepartition（pa4）;
deletefromt_employeepartition（pa1）;
散列分区
允许用户对不具有逻辑范围的数据进行分区,通过在分区键上执行HASH函数决定存储的分区,将数据平均地分布到不同的分区
partitionbyhash（salary）（
partitionp1,
partitionp2
）
列表分区
允许用户将不相关的数据组织在一起,按照指定列的值分类分区
createtablet_employee（
idnumber（8）primarykey,
namevarchar（20）,
departmentvarchar2（20）,
salarynumber（10,2）
）
partitionbylist（department）（
partitionjishubuvalues（'技术部'）,
partitioncaiwubuvalues（'财务部'）,
partitionxzrsvalues（'行政部','人事部'）,
partitionhwbvalues（'海外部'）
）
复合分区
范围分区与散列分区或列表分区的组合,可以建立子分区
添加分区
altertabletable_nameaddpartitionpartition_namevalueslessthan（...）--范围分区
altertabletable_nameaddpartitionpartition_namevalues（...）--列表分区
altertabletable_nameaddpartitionpartition_name;--散列分区
删除分区
altertabletable_namedroppartitionpartition_name;
截断分区
altertabletable_nametruncatepartitionpartition_name;
合并分区
altertabletable_namemergepartitionspartition1,partition2intopartitionpartition_name;
拆分分区
altertabletable_namesplitpartitionpartition_nameat（...）into（partitionp1,partitionp2）
数据库对象
数据库对象是逻辑结构的集合，最基本的数据库对象是表.
同义词
公有同义词可被所有的数据库用户访问。
CREATESYNONYMempFORSCOTT.emp;CREATEORREPLACESYNONYMemp_synFORSCOTT.emp;
私有同义词只能在其模式内访问，且不能与当前模式的对象同名。
CREATEPUBLICSYNONYMemp_synFORSCOTT.emp;
序列
序列是用于生成唯一、连续序号的对象,序列可以是升序的，也可以是降序的,使用CREATESEQUENCE语句创建序列
CREATESEQUENCEtoys_seq
STARTWITH10
INCREMENTBY10
MAXVALUE2000
MINVALUE10
NOCYCLE
CACHE10;
序列名.NEXTVAL返回序列的下一个值
序列名.CURRVAL返回序列的当前值
使用ALTERSEQUENCE语句修改序列，不能更改序列的STARTWITH参数
使用DROPSEQUENCE语句删除序列,如:
DROPSEQUENCEtoys_seq;
视图
视图以经过定制的方式显示来自一个或多个表的数据,视图可以视为“虚拟表”或“存储的查询”,创建视图所依据的表称为“基表”
视图的优点有：
提供了另外一种级别的表安全性
隐藏的数据的复杂性
简化的用户的SQL命令
隔离基表结构的改变
通过重命名列，从另一个角度提供数据
创建视图的语法:
CREATE[ORREPLACE][FORCE]VIEW
view_name[（alias[,alias]...）]
ASselect_statement
[WITHCHECKOPTION]
[WITHREADONLY];
在视图上也可以使用修改数据的DML语句，如INSERT、UPDATE和DELETE
视图上的DML语句有如下限制：
只能修改一个底层的基表
如果修改违反了基表的约束条件，则无法更新视图
如果视图包含连接操作符、DISTINCT关键字、集合操作符、聚合函数或GROUPBY子句，则将无法更新视图
如果视图包含伪列或表达式，则将无法更新视图
索引
创建标准索引
CREATEINDEXitem_indexONitemfile（itemcode）TABLESPACEindex_tbs;
重建索引
ALTERINDEXitem_indexREBUILD;
删除索引
DROPINDEXitem_index;
PL_SQL
PL/SQL
标量
|----数值
|----字符
|----布尔--booleanPL/SQL所特有的,不能用于sql语句中,不能作为表的字段类型
|----日期
lob
|----BLOB
|----CLOB
|----NCLOB
属性
|----%type--列类型[用户名.]表名.列名%type
|----%rowtype--行类型[用户名.]表名%rowtype
PL/SQL块
是PL/SQL程序的基本单元
主要由三个部分构成:
声明部分,可执行部分,异常处理部分
declare
/*声明变量*/
begin
/*可执行部分*/
exception
/*异常处理部分*/
end;
变量声明
变量名[constant（是否常量）]数据类型（长度）[:
=值];
变量赋值
1.:
=
2.selectcolumn1,column2,column3...intotemp1,temp2,temp3...from...where...
2.1into的变量顺序和类型必须跟查询出来的结果一致
2.2查询出来的结果只能是单条数据
条件判断
--if
ifuser_name='张三'then
DBMS_OUTPUT.PUT_LINE（'user_name是张三'）;
endif;
--ifelse
ifb_output='张三'then
DBMS_OUTPUT.PUT_LINE（'b_output是张三'）;
else
DBMS_OUTPUT.PUT_LINE（'b_output不是张三'）;
endif;
--ifelsif
ifb_output='张三'then
DBMS_OUTPUT.PUT_LINE（'b_output是张三'）;
elsifb_output='李四'then
DBMS_OUTPUT.PUT_LINE（'b_output是李四'）;
else
DBMS_OUTPUT.PUT_LINE（'b_output不是张三也不是李四'）;
endif;
--case相当于java中的switch
caseb_output
when'张三'then
DBMS_OUTPUT.PUT_LINE（'b_output是张三'）;
when'李四'then
DBMS_OUTPUT.PUT_LINE（'b_output是李四'）;
when'王强'then
DBMS_OUTPUT.PUT_LINE（'b_output是王强'）;
else
DBMS_OUTPUT.PUT_LINE（'b_output不是张三也不是李四也不是王强...'）;
endcase;
循环控制
loop
...
[exit|exitwhen]
...
endloop;
while...loop
...
endloop;
foriinxx...oooloop--变量由for循环来隐式定义,不需要在declare块中进行声明
...
endloop;
顺序控制
goto强制流转
null空语句,没有任何实际意义的语句
动态sql
指PL/SQL块在执行过程中动态的生成一端sql语句,并且进行执行
executeimmediatesql语句
into结果集变量
using参数;
异常处理
预定义异常
用户定义异常
需要在declare块中声明,通过raise关键字抛出异常
--异常处理
exception
whentoo_many_rowsthen
dbms_output.put_line（'查询到的结果大于一行,请指定查询条件...'）;
whenmy_exceptionthen
dbms_output.put_line（'user_name的值是张三,怎么可以是张三呢...'）;
游标_子程序和程序包
游标
相当于一个指针,指向某块内存,主要保存一段sql语句执行后的结果.
游标属性:
found当sql执行时,找到要执行的记录时返回true,否则返回false
notfound跟found相反,找到记录返回false,否则返回true
rowcountsql执行后影响的记录数
isopen判断游标是否打开（隐式游标获取到的永远是false）
访问游标属性游标名%属性
隐式游标
由数据库系统维护的游标,当执行了DML（insert,update,delete）语句之后由系统自动的开启和关闭游标
隐式游标的名称是SQL
显示游
升级会员 