ORASQL.docx
《ORASQL.docx》由会员分享，可在线阅读，更多相关《ORASQL.docx（33页珍藏版）》请在冰豆网上搜索。
ORASQL
1、SQL语句基础
下面给出SQL语句的基本介绍。
1.1、SQL语句所用符号
操作符
用途
例子
+-
表示正数或负数，正数可省去+
-1234.56
+
将两个数或表达式进行相加
A=c+b
-
将两个数或表达式进行相减
34-12
*
将两个数或表达式进行相乘
12*34
/
除以一个数或表达式
18*11
NULL
空值判断
Wherenameisnull;
||
字符串连接
‘101-’||tel_num
=
等于测试
Select*fromempwherename=’赵元杰’;
!
=或<>或^=
不等于测试
Select*fromempwherename!
=’赵元杰’;
<
小于测试
Select*fromempwheresal<5000;
>
大于测试
Select*fromempwheresal>5000;
<=
小于等于测试
Select*fromempwheresal<=5000;
>=
大于等于测试
Select*fromempwheresal>=5000;
Notin
测试某值是否在一个指定的结果集中
Selectname,addrfromexpertwherelocalnotin（‘北京’,’上海’）;
ANY
将一个值与一组值进行比较，返回满足条件的结果。
必须跟!
=,<,>,<=,>=
selectename,salfromempwheresal<=any（selectsalfromempwheredeptno=10）
SOME
同ANY，必须跟!
=,<,>,<=,>=
ALL
将一个值与一组值比较，返回满足条件的所有列值。
必须跟!
=,<,>,<=,>=
Selectname,salfromempwheresal<=all
（500,800,1200）;
Notbetween
AandB
判断某个值是否界于两者之间。
Selectname,salfromempWhere
salbetween500and1200;
[not]exists
判断某个列是否存在于一组值中。
selectdname,deptnofromdeptwhereexists
（select*fromempwheredept.deptno=emp.deptno）
A[not]likeb
比较两个模式是否相似，当使用like语句时Oracle不去访问索引。
Select*fromemp
Whereenamelike‘TH%’;
Is[not]null
测试值是否为空。
Selectename,deptnofromemp
Wherecomm.Isnullorcomm.=0;
Not
对结果的否定。
Select*fromempWheresalnot（sal<1000）;等价于selectename,salfromempwheresal>=1000;
AND
用于判断两个条件十分都满足。
Select*fromempwhere
Ename=’SIMTH’andsal>=1000;
OR
用于判断两个条件中是否有一个满足。
Select*fromempwhere
Ename=’SIMTH’orename=’SCOTT’;
UNION
用于返回（组合）两个查询中所有唯一的行。
Selectenamefromempunion
Selectenamefromemp;
UNIONALL
用于返回（组合）两个查询中所有所有的行。
INTERSECT
用于返回两个查询中相同的行。
Selectenamefromemp1intersectselectenamefromemp2;
MINUS
用于返回两个查询中的不同的行。
1.2、简单select查询
当我们可以用SQL*PLUS登录到SQL>下后，我们可以用DESC显示某表的结构，也可以用select语句简单查询表中的一些列的内容。
例：
要查询EMP表中员工的姓名、工资及出生日期，则：
SQL>selectename,sal,hiredatefromemp;
2、伪列及伪表
Oracle系统为了实现完整的关系数据库功能，系统专门提供了一组称为伪列（Pseudocolumn）的数据库列，这些列不是在建立对象（如建表）时由我们完成的，而是在我们建立对象时由自动Oracle完成的。
Oracle目前有以下的伪列：
CURRVALandNEXTVAL使用序列号的保留字
LEVEL查询数据所对应的级
ROWID记录的唯一标识
ROWNUM限制查询结果集的数量
Oracle还提供了一个DUAL的伪表，该目的表主要是保证在使用SELECT语句中语句的完整性而提供的，如：
我们要查询当前的系统日期及时间，而系统的日期和时间并是放在一个指定的表里。
所以在from语句后就没有表名给出。
为了使用from后有个表名，我们就用DUAL代替。
如：
例1：
查询Oracle系统日期及时间：
SQL>selectto_char（sysdate,'yyyy.mm.ddhh24:
mi:
ss'）fromDUAL;
TO_CHAR（SYSDATE,'YY
-------------------
2001.06.0207:
28:
09
例2：
计算一下5000+5000*0.1的结果是多少，则：
SQL>select5000+5000*0.1fromDUAL;
5000+5000*0.1
-------------------
5500
3、SELECT语句
在关系数据库中，使用频率最高要算SELECT语句了。
尽管SELECT语句的使用非常简单，但确很有学问。
下面简单介绍有关SELECT语句的常用方法。
1.命令语法：
SELECT的简单语法：
SELECT[DISTINCT|ALL]{*|column1[,column2]...}
FROM{table_1|（subquery）}[alias]
[,{table_2|（subquery）}[alias]]...
[WHEREcondition]
[CONNECTBYcondition[STARTWITHcondition]
[GROUPBYexpn][HAVINGexpn]
[{UNION[ALL]|INTERSECT|MINUS}SELECT...]
[ORDERBY[expn][ASC|DESC]
[FORUPDATE[OF[user.]table|view]column]
[NOWAIT]
详细语法结构需查阅最新ORACLE原版《ORACLE8iSQLREFERENCEMANUAL》或《ORACLE9iSQLREFERENCEMANUAL》
4、SQL中的单记录函数
许多资料（包括Oracle的资料）把Oracle的SQL语句中用到的函数分为单值函数和多值函数，单值函数又分为字符函数和数字函数。
下面分别介绍它们。
4.1、单记录字符函数
函数
说明
ASCII
返回对应字符的十进制值
CHR
给出十进制返回字符
CONCAT
拼接两个字符串，与||相同
INITCAT
将字符串的第一个字母变为大写
INSTR
找出某个字符串的位置
INSTRB
找出某个字符串的位置和字节数
LENGTH
以字符给出字符串的长度
LENGTHB
以字节给出字符串的长度
LOWER
将字符串转换成小写
LPAD
使用指定的字符在字符的左边填充
LTRIM
在左边裁剪掉指定的字符
RPAD
使用指定的字符在字符的右边填充
RTRIM
在右边裁剪掉指定的字符
REPLACE
执行字符串搜索和替换
SUBSTR
取字符串的子串
SUBSTRB
取字符串的子串（以字节）
SOUNDEX
返回一个同音字符串
TRANSLATE
执行字符串搜索和替换
TRIM
裁剪掉前面或后面的字符串
UPPER
将字符串变为大写
NVL
以一个值来替换空值
1、ASCII（）
是字符串。
返回与指定的字符对应的十进制数。
SQL>selectascii（'A'）A,ascii（'a'）a,ascii（'0'）zero,ascii（''）spacefromdual;
AaZEROSPACE
----------------------------------------
65974832
SQL>selectascii（'赵'）zhao,length（'赵'）lengfromdual;
ZHAOLENG
--------------------
547401
2、CHR（[NCHAR]）
给出整数，返回对应字符。
如：
SQL>selectchr（54740）zhao,chr（65）chr65fromdual;
ZHC
---
赵A
3、CONCAT（,）
返回字符串c1与字符串c2合并后的值。
例如：
SQL>selectconcat（'010-','88018159'）||'转23'赵元杰电话fromdual;
赵元杰电话
-----------------
010-88018159转23
4、INITCAP（）
返回字符串c1并第一个字母变为大写。
例如：
SQL>selectinitcap（'simth'）uppfromdual;
UPP
-----
Simth
5、INSTR（,[,[,]]）
在一个字符串中搜索指定的字符，返回发现指定的字符的位置。
C1:
被搜索的字符串
C2:
希望搜索的字符串
I:
搜索的开始位置，缺省是1
J:
出现的位置，缺省是1。
SQL>SELECTINSTR（'OracleTraining','ra',1,2）"Instring"FROMDUAL;
Instring
----------
9
6、INSTRB（,[,[,]]）
除了返回的字节外，与INSTR相同，
7、LENGTH（）
返回字符串c的长度。
SQL>l
1selectname,length（name）,addr,length（addr）,sal,length（to_char（sal））
2*fromnchar_tst
SQL>/
NAMELENGTH（NAME）ADDRLENGTH（ADDR）SALLENGTH（TO_CHAR（SAL））
------------------------------------------------------------------------
赵元杰3北京市海淀区699999.998
8、LENGTHB（）
以字节返回字符串的字节数。
SQL>selectname,lengthb（name）,length（name）fromnchar_tst;
NAMELENGTHB（NAME）LENGTH（NAME）
-------------------------------
赵元杰63
9、LOWER（）
返回字符串并将所有字符变为小写。
SQL>selectlower（'AaBbCcDd'）AaBbCcDdfromdual;
AABBCCDD
--------
aabbccdd
10、UPPER（）
与LOWER相反，将给出字符串变为大写。
如：
SQL>selectupper（'AaBbCcDd'）AaBbCcDdfromdual;
AABBCCDD
--------
AABBCCDD
11、RPAD和LPAD（粘贴字符）
RPAD（string，Length[，'set']）
LPAD（string，Length[，'set']）
RPAD在列的右边粘贴字符；
LPAD在列的左边粘贴字符。
例1：
SQL>selectRPAD（City，35，'.'），temperaturefromweather;
RPAD（City，35，'.'）temperature
------------------------------------------
CLEVELAND......85
LOSANGELES..81
.........................
（即不够35个字符用'.'填满）
12、LTRIM（左截断）RTRIM（右截断）函数
LTRIM（string[,’set’]）
LeftTRIM（左截断）删去左边出现的任何set字符。
RTRIM（string[,’set’]）
RightTRIM（右截断）删去右边出现的任何set字符。
例1：
SELECTRTRIM（‘MotherTheresa,The’,‘The’）“ExampleofRight
Trimming”FROMDUAL;
ExampleofRight
----------------
MotherTheresa,
13、SUBSTRSubstr（string，start[，Count]）
取子字符串中函数
对字串（或字段），从start字符开始，连续取count个字符并返回结果，如果没有指count则一直取到尾。
selectphone,substr（phone,1,3）||‘0’||substr（phone,4）
fromtelecommunicationwheremaster=’中国电信’;
14、SUBSTRB（string，start[，Count]）
对字串（或字段），从start字节开始，连续取count个字节并返回结果，如果没有指count则一直取到尾。
15、REPLACE（‘string’[,’string_in’,’string_out’]）
String:
希望被替换的字符串或变量。
String_in:
被替换字符串。
String_out:
要替换字符串。
SQL>selectreplace（'Informaix中国公司','Informaix','IBMInformix'）
2IBM数据库fromdual;
IBM数据库
--------------------
IBMInformix中国公司
16、SOUNDEX（）
返回一个与给定的字符串读音相同的字符串（不管拼写是否一样）。
SELECTDPL_NAMEFROMDENIED_PARTIES_LISTWHERE
SOUNDEX（DPL_NAME）=SOUNDEX（‘SaddamHussain’）;
DPL_NAME
----------------------------------------------
AlHusseni
SaddaAlSada.
17、REPLACE（‘string’[,’string_in’,’string_out’]）
String:
希望被替换的字符串或变量。
String_in:
被替换字符串。
String_out:
要替换字符串。
SELECTREPLACE（‘Oracle’,‘Or’,‘Mir’）“Example“FROMDUAL;
Example
-------
Miracle
18、TRIM（[]FROM）
TRIM可以使你对给定的字符串进行裁剪（前面，后面或前后）。
如果指定LEADING,Oracle从trim_char中裁剪掉前面的字符；
如果指定TRAILING,Oracle从trim_char中裁剪掉尾面的字符；
如果指定两个都指定或一个都没有给出，Oracle从trim_char中裁剪掉前面及尾面的字符；
如果不指定trim_character,缺省为空格符；
如果只指定trim_source,OracleOracle从trim_char中裁剪掉前面及尾面的字符。
例子：
将下面字符串中的前面和后面的‘0‘字符都去掉：
SELECTTRIM（0FROM0009872348900）"TRIMExample"FROMDUAL;
TRIMexample
--------------------------------
98723489
4.2、单记录数字函数
函数
说明
Value1+value2
加
Value1-value2
减
Value1*value2
乘
Value1/value2
除
ABS（value）
绝对值
CEIL（value）
大于或等于value的最小整数
COS（value）
余弦
COSH（value）
反余弦
EXP（value）
e的value次幂
FLOOR（value）
小于或等于value的最大整数
LN（value）
value的自然对数
LOG（value）
value的以10为底的对数
MOD（value,divisor）
求模
NVL（value,substitute）
value为空时以substitute代替
POWER（value,exponent）
value的exponent次幂
ROUND（value,precision）
按precision精度4舍5入
SIGN（value）
value为正返回1;为负返回-1;为0返回0.
SIN（value）
余弦
SINH（value）
反余弦
SQRT（value）
value的平方根
TAN（value）
正切
TANH（value）
反正切
TRUNC（value,按precision）
按照precision截取value
VSIZE（value）
返回value在ORACLE的存储空间大小
1、ABS（）
返回指定值的绝对值。
如：
SQL>selectabs（100）,abs（-100）fromdual;
ABS（100）ABS（-100）
--------------------
100100
2、ACOS（）
给出反余弦的值。
如：
SQL>Selectacos（-1）acosfromdual;
ACOS
----------
3.14159265
3、ASIN（）
给出反正弦的值。
如：
SQL>selectasin（-1）"arcsine"fromdual;
arcsine
----------
-1.5707963
4、ATAN（）
返回一个数字的反正切值。
如：
SQL>selectatan（-1）"arctangent"fromdual;
arctangent
-----------
-.78539816
5、CEIL（）
返回大于或等于给出数字的最小整数。
如：
SQL>selectceil（3.14159）fromdual;
CEIL（3.14159）
-------------
4
6、COS（）
返回一个数字余弦值。
如：
SQL>selectcos（-3.1415926）fromdual;
COS（-3.1415926）
---------------
-1
7、COSH（）
返回一个数字双曲余弦值。
如：
SQL>selectcosh（20）coshfromdual;
COSH
----------
242582598
8、EXP（）
返回一个数字e的n次方的值。
如：
SQL>selectexp
（2）,exp
（1）fromdual;
EXP
（2）EXP
（1）
--------------------
7.38905612.71828183
9、FLOOR（）
对给定的数字取整数，如：
SQL>selectfloor（123.45）,floor（45.56）fromdual;
FLOOR（123.45）FLOOR（45.56）
-------------------------
45
10、LN（）
返回一个数字的对数值，n是大于0的数字，如：
SQL>selectln
（1）,ln
（2）,ln（3）fromdual;
LN
（1）LN
（2）LN（3）
------------------------------
0.6931471811.09861229
11、LOG（,）
返回一个以n1为底的n2的对数，n1不是0或1的正数。
如：
SQL>selectlog（2,1）,log（2,2）fromdual;
LOG（2,1）LOG（2,2）
--------------------
1
12、MOD（,）
SQL>Selectmod（10,3）,mod（10,2）,mod（10,4）fromdual;
MOD（10,3）MOD（10,2）MOD（10,4）
------------------------------
102
13、POWER（,）
返回n1的n2次方值，如：
SQL>selectpower（2,10）,power（3,3）fromdual;
POWER（2,10）POWER（3,3）
---------------------
27
14、ROUND（value,precision）
按照指定的精度进行舍入；
selectround（55.5）,round（-55.5）,trunc（55.5）,trunc（-55.5）
fromdual;
round（55.5）round（-55.5）trunc（55.5）trunc（-55.5）
----------------------------------
升级会员 