Oracle笔记.docx
- 文档编号:8053702
- 上传时间:2023-01-28
- 格式:DOCX
- 页数:29
- 大小:369.69KB
Oracle笔记.docx
《Oracle笔记.docx》由会员分享,可在线阅读,更多相关《Oracle笔记.docx(29页珍藏版)》请在冰豆网上搜索。
Oracle笔记
查看当前用户所有表:
Select*fromtab;
连接符:
||
空值:
isnull
除去重复行:
distinct
查询结果排序:
orderby排序字段asc(desc)
比较运算符:
><(!
=or<>)betweenand
in操作notin
模糊查询Like使用:
“%”:
代表匹配任意长度的任意字符。
“_”:
代表匹配一个长度的任意字符。
特殊字符使用ESCAPE标示:
select*fromposgoodswherepgcnamelike'%*_%'escape'*';
查询posgoods表中pgcname字段中有‘_’字符的结果。
escape'*'表示*字符后面的是字符代表其实际意思,不做转义字符。
Oracle10g支持正则表达式的四个新函数分别是:
REGEXP_LIKE、REGEXP_INSTR、REGEXP_SUBSTR、和REGEXP_REPLACE。
它们使用POSIX正则表达式代替了老的百分号(%)和通配符(_)字符。
特殊字符:
'^'匹配输入字符串的开始位置,在方括号表达式中使用,此时它表示不接受该字符集合。
'$'匹配输入字符串的结尾位置。
如果设置了RegExp对象的Multiline属性,则$也匹配'\n'或'\r'。
'.'匹配除换行符\n之外的任何单字符。
'?
'匹配前面的子表达式零次或一次。
'*'匹配前面的子表达式零次或多次。
'+'匹配前面的子表达式一次或多次。
'()'标记一个子表达式的开始和结束位置。
'[]'标记一个中括号表达式。
'{m,n}'一个精确地出现次数范围,m=<出现次数<=n,'{m}'表示出现m次,'{m,}'表示至少出现m次。
'|'指明两项之间的一个选择。
例子'^([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的字符串。
\num匹配num,其中num是一个正整数。
对所获取的匹配的引用。
正则表达式的一个很有用的特点是可以保存子表达式以后使用,被称为Backreferencing.允许复杂的替换能力
如调整一个模式到新的位置或者指示被代替的字符或者单词的位置.被匹配的子表达式存储在临时缓冲区中,缓冲区从左到右编号,通过\数字符号访问。
下面的例子列出了把名字aabbcc变成cc,bb,aa.
SelectREGEXP_REPLACE('aabbcc','(.*)(.*)(.*)','\3,\2,\1')FROMdual;
REGEXP_REPLACE('ELLENHILDISMIT
cc,bb,aa
'\'转义符。
字符簇:
[[:
alpha:
]]任何字母。
[[:
digit:
]]任何数字。
[[:
alnum:
]]任何字母和数字。
[[:
space:
]]任何白字符。
[[:
upper:
]]任何大写字母。
[[:
lower:
]]任何小写字母。
[[unct:
]]任何标点符号。
[[:
xdigit:
]]任何16进制的数字,相当于[0-9a-fA-F]。
各种操作符的运算优先级
\转义符
(),(?
(?
=),[]圆括号和方括号
*,+,?
{n},{n,},{n,m}限定符
^,$,\anymetacharacter位置和顺序
|“或”操作
--测试数据
createtabletest(mcvarchar2(60));
insertintotestvalues('112233445566778899');
insertintotestvalues('221133445566778899');
insertintotestvalues('331122445566778899');
insertintotestvalues('441122335566778899');
insertintotestvalues('551122334466778899');
insertintotestvalues('661122334455778899');
insertintotestvalues('771122334455668899');
insertintotestvalues('881122334455667799');
insertintotestvalues('991122334455667788');
insertintotestvalues('aabbccddee');
insertintotestvalues('bbaaaccddee');
insertintotestvalues('ccabbddee');
insertintotestvalues('ddaabbccee');
insertintotestvalues('eeaabbccdd');
insertintotestvalues('ab123');
insertintotestvalues('123xy');
insertintotestvalues('007ab');
insertintotestvalues('abcxy');
insertintotestvalues('Thefinaltestisisishowtofindduplicatewords.');
commit;
一、REGEXP_LIKE
select*fromtestwhereregexp_like(mc,'^a{1,3}');
select*fromtestwhereregexp_like(mc,'a{1,3}');
select*fromtestwhereregexp_like(mc,'^a.*e$');
select*fromtestwhereregexp_like(mc,'^[[:
lower:
]]|[[:
digit:
]]');
select*fromtestwhereregexp_like(mc,'^[[:
lower:
]]');
SelectmcFROMtestWhereREGEXP_LIKE(mc,'[^[:
digit:
]]');
SelectmcFROMtestWhereREGEXP_LIKE(mc,'^[^[:
digit:
]]');
二、REGEXP_INSTR
SelectREGEXP_INSTR(mc,'[[:
digit:
]]$')fromtest;
SelectREGEXP_INSTR(mc,'[[:
digit:
]]+$')fromtest;
SelectREGEXP_INSTR('Thepriceis$400.','\$[[:
digit:
]]+')FROMDUAL;
SelectREGEXP_INSTR('onetwothree','[^[[:
lower:
]]]')FROMDUAL;
SelectREGEXP_INSTR(',,,,,','[^,]*')FROMDUAL;
SelectREGEXP_INSTR(',,,,,','[^,]')FROMDUAL;
三、REGEXP_SUBSTR
SELECTREGEXP_SUBSTR(mc,'[a-z]+')FROMtest;
SELECTREGEXP_SUBSTR(mc,'[0-9]+')FROMtest;
SELECTREGEXP_SUBSTR('aababcde','^a.*b')FROMDUAL;
四、REGEXP_REPLACE
SelectREGEXP_REPLACE('JoeSmith','(){2,}',',')ASRX_REPLACEFROMdual;
SelectREGEXP_REPLACE('aabbcc','(.*)(.*)(.*)','\3,\2,\1')FROMdual
四个函数是:
regexp_like.regexp_instr.regexp_substr.与regexp_replace.它们在用法上与oraclesql函数like.instr.substr与replace用法,但是它们使用posix正则表达式代替了老的百分号(%)与通配符(_)字符.
单行函数
Upper(Str1)
将Str1串的内容转换为大写。
selectupper('Abc')fromdual;
Lower(Str1)
将Str1串的内容转换为小写。
selectlower('ABC')fromdual;
Initcap(Str1)
将Str1字符串第一个字母转换为大写的
selectinitcap('abc')fromdual;
Concat(Str1,Str2)
将Str1和Str2连接起来。
selectconcat('abc','123')fromdual;
select'111'||'222'fromdual;
Substr(Str1,index[,length])
获得一个子字符串,截取字符串Str1从index位置开始,长度为length的子字符串
selectsubstr('abcde',2,5)fromdual;
Length(Str1)
获得Str1字符串的长度。
selectlength('1231321')lengthfromdual;
Replace(Str1,old,new)
将字符串Str1中old字符串替换为new字符串
selectreplace('name','a','B')fromdual;
Instr(Str1,str[,n])
获取第n个字符串str在Str1中位置。
selectinstr('HelloWorld','or')fromdual;
Lpad(Str1,length,str);
在字符串Str1左边填充str至长度为length。
selectlpad('Stream',10,'+12')fromdual;
Rpad(Str1,length,str);
在字符串Str1右边填充str至长度为length。
selectrpad('Stream',10,'+')fromdual;
Trim(Str1)
过滤字符串str1两边的空格。
selecttrim('1231')fromdual;
数值函数:
Round(Num,n)
将数值Num按n位精确度进行四舍五入
selectround(123.126,-2)fromdual;
Mod(Num1,Num2)
将数值Num1按Num2取模。
selectmod(13,5)fromdual;
Trunc(Num1,n)
将数值Num1按n位截取。
selecttrunc(123.356,1)fromdual;
日期函数:
Months_between(date1,date2)
比较日期date1和date2之间相差的月份。
selectmonths_between(sysdate,rqsj)fromsalehead;
Add_Months(date1,n)
在日期date1上添加n月。
selectadd_months(sysdate,1)fromdual;
Next_day(date1,’星期一’)
下个星期一的时间。
selectnext_day(sysdate,'星期一')fromdual;
Last_day(date1)
当前日期月份的最后一天。
selectlast_day(sysdate)fromdual;
转换函数
To_char(Str1,mode)
将Str1(可以是日期,数值)按mode模式转换成字符串
selectto_char(sysdate,'yyyy-mm-dd')fromdual;
fm表示转换的日期中月份或日期第一个值若为0,则不显示
selectto_char(sysdate,'fmyyyy-mm-dd')fromdual;
将数值按指定格式显示,9代表数字,L代表本地化钱币符号
selectto_char(27892342,'L99,999,999')fromdual;
selectto_char(127892342,'999,999,999')fromdual;
获得指定日期对应的星期(星期日,1代替,其它以此类推)
selectto_char(sysdate+2,'D')fromdual;--每星期以星期日为起始日期,数字1代表
To_number(str1)
将str1字符串转换成数值
selectto_number('123')+to_number('12')fromdual;
To_date(str1,mode)
将字符串str1按mode模式转换成日期
selectto_date('2014101212:
14:
23','yyyyMMddHH:
mi:
ss')fromdual;
HH24中24代表24小时制
selectto_date('2005-01-0113:
14:
20','YYYY-MM-ddHH24:
mi:
ss')fromdual;
通用函数:
NVL(str,default)
当Str代表的对象或者字符串的内容为空,则返回默认的default值。
selectcode,paravalue,NVL(memo,'Ye')fromsysparawherecode='13'orcode='OC'orderbycodedesc;
NVL2(str,expr1,expr2)
当str指定的内容为空(null)时,返回expr2的值,当不为NULL时,返回expr1的内容。
selectcode,paravalue,NVL2(memo,'Stream','Liu')fromsysparawherecode='13'orcode='OC'orderbycodedesc;
COALESCE(expression_1,expression_2,...,expression_n)
依次参考各参数表达式,遇到非null值即停止并返回该值。
如果所有的表达式都是空值,最终将返回一个空值。
使用COALESCE在于大部分包含空值的表达式最终将返回空值。
selectcoalesce(null,null,null,3)fromdual;
CASE表达式
selectcode,
name,
casecode
when'13'then
'北京店'
when'14'then
'201'
else
'未知参数'
endvalue
fromsyspara
Decode(value,if1,then1,if2,then2,if3,then3,...else)
当value值等于if1,则返回then1的值,当value值等于if2,则返回then2...否则返回默认值。
selectcode,name,decode(code,'13','北京店','14','201','未知参数')fromsyspara;
分组函数:
Count()求查询内容的条数
Sum()求字段对应值的合计
Avg()求字段对应值的平均值
Max()求字段对应值中最大的值
Min()求字段对应值中最小值
GROUPBY分组:
只有表中某个字段存在重复的内容才有可能考虑到分组
selectcatid,count(catid),sum(lsj),Round(avg(lsj),2)AVG,min(lsj)MIN,max(lsj)MAx
fromBusiness_goodsgroupbycatidhavingavg(lsj)>200;
selects.mkt,s.paravalue,i.count,i.sum
fromsysparas,
(selectb.mktmkt,count(b.mkt)count,sum(b.lsj)SUM
fromBusiness_goodsb
whereb.mkt<>'101'
groupbyb.mkt
havingsum(b.lsj)>10000
orderbySUMasc)i
wheres.code='13'
ands.mkt=i.mkt;
selects.mkt,s.paravalue,i.sum,i.min,g.name
fromsysparas,
(selectmkt,sum(lsj)sum,min(lsj)min
frombusiness_goods
groupbymkt)i,
business_goodsg
wheres.code='13'
ands.mkt=i.mkt
andi.min=g.lsj;
1.如果SQL语句使用了分组函数,则有两种可以使用的情况:
*SQL语句中存在了GROUPBY分组条件,则可以将分组条件一起查询出来。
Selectmkt,count(mkt)fromBusiness_goodsgroupbymkt;
*如果不使用GROUPBY分组条件,则只能单独的使用分组函数。
Selectsum(lsj),avg(lsj)fromBusiness_goods;
2.在使用分组函数的时候,不能出现分组函数和分组条件之外的字段。
3.在SELECT列表中的字段,如果不包含在分组函数中,那么该字段必须同时出现在GROUPBY子句中。
包含在GROUP子句中的字段则不必出现在SELECT列表中
多表查询
1.笛卡尔集:
Select*fromempty,dept;
2.等值连接:
Selectempno,ename,sal,emp.deptno,dnamefromemp,deptwhereemp.deptno=dept.deptno;
3.集合操作
*UNION:
并集,所有的内容都查询,重复的显示一次。
Select*fromempUNIONselect*fromemp20
*UNIONALL:
并集,所有的内容都显示,包括重复的
*INTETSECT:
交集,只显示重复的
*MINUS:
差集,只显示对方没有的(跟顺序是有关系的)
序列,同义词
createsequenceTEST
minvalue1
maxvalue9999999
startwith1
incrementby1
cache20;
--Createsequence
createsequenceTEST
minvalue1--最小值
maxvalue9999999--最大值
startwith1--开始值
incrementby1--增长值
cache20;--缓存大小
Selecttest.nextvalfromdual;
Selecttest.currvalfromdual;
必须先有nextval,才能有currval
同以词
Createsynonymdeptforsocct.dept;--(创建私有,创建者才能使用)
Dropsynonymdept;
Createpublicsynonymdeptforsocct.dept;(公有)
Droppublicsynonymdept;
PL/SQL块
PL/SQL块,类似编程代码块
DECLARE
声明部分
BEGIN
逻辑处理
EXCEPTION
END;
PL/SQL可接受用户的输入。
输入信息使用“&”表示。
emp.empno%TYPE:
表示以emp表中的empno字段的类型定义变量。
表示定义一个变量,变量类型和emp表中的empno字段的类型定义的变量一致。
Deptrdept%rowtype:
表示定义一个接收dept的行数据的变量。
常见预定义异常
ORACLE定义了他们的错误编号和异常名字,常见的预定义异常处理Oracle常见的错误
NO_DATA_FOUNDSELECT...INTO...时,没有找到数据
DUL_VAL_ON_INDEX试图在一个有惟一性约束的列上存储重复值
CURSOR_ALREADY_OPEN试图打开一个已经打开的游标
TOO_MANY_ROWSSELECT...INTO...时,查询的结果是多值
ZERO_DIVIDE零被整除
OTHERS其他未知错误
在 PL/SQL 中使用 SQLCODE, SQLERRM异常处理函数获得异常信息
whenNO_DATA_FOUNDthen
dbms_output.put_line('NO_DATA_FOUNDcode:
'||SQLCODE||'error:
'||
SQLERRM);
错误号
异常错误信息名称
说明
ORA-0001
Dup_val_on_index
违反了唯一性限制
ORA-0051
Timeout-on-resource
在等待资源时发生超时
ORA-0061
Transaction-backed-out
由于发生死锁事务被撤消
ORA-1001
Invalid-CURSOR
试图使用一个无效的游标
ORA-1012
Not-logged-on
没有连接到ORACLE
ORA-1017
Login-denied
无效的用户名/口令
ORA-1403
No_data_found
SELECT INTO没有找到数据
ORA-1422
Too_many_rows
SELECT INTO 返回多行
ORA-1476
Zero-divide
试图被零除
ORA-1722
Invalid-NUMBER
转换一个数字失败
ORA-6500
Storage-error
内存不够引发的内部错误
ORA-6501
Program-error
内部错误
ORA-6502
Val
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Oracle 笔记