ORASQL.docx

1、ORASQL1、SQL 语句基础下面给出SQL语句的基本介绍。1.1、SQL语句所用符号操作符用途例子+ -表示正数或负数，正数可省去 +-1234.56+将两个数或表达式进行相加A=c+b-将两个数或表达式进行相减34-12*将两个数或表达式进行相乘12*34/除以一个数或表达式18*11NULL空值判断Where name is null;|字符串连接101-|tel_num=等于测试Select * from emp where name=赵元杰;!= 或或=不等于测试Select * from emp where name !=赵元杰; 小于测试Select * from emp wh

2、ere sal 大于测试Select * from emp where sal 5000;=小于等于测试Select * from emp where sal =大于等于测试Select * from emp where sal = 5000;Not in测试某值是否在一个指定的结果集中Select name,addr from expert where local not in(北京,上海);ANY将一个值与一组值进行比较，返回满足条件的结果。必须跟!=,=select ename,sal from emp where sal= any(select sal from emp where d

3、eptno=10)SOME同ANY，必须跟!=,=ALL将一个值与一组值比较，返回满足条件的所有列值。必须跟!=,=Select name,sal from emp w here sal= all ( 500,800,1200);Not betweenA and B判断某个值是否界于两者之间。Select name,sal from emp Where sal between 500 and 1200;notexists判断某个列是否存在于一组值中。select dname,deptno from dept where exists(select * from emp where dept.d

4、eptno=emp.deptno)Anotlike b比较两个模式是否相似，当使用like 语句时Oracle不去访问索引。Select * from empWhere ename like TH%;Is not null 测试值是否为空。Select ename,deptno from empWhere comm. Is null or comm.=0;Not对结果的否定。Select * from emp Where sal not(sal=1000;AND用于判断两个条件十分都满足。Select * from emp where Ename=SIMTH and sal=1000;OR用于

5、判断两个条件中是否有一个满足。Select * from emp where Ename=SIMTH or ename=SCOTT;UNION用于返回（组合）两个查询中所有唯一的行。Select ename from emp unionSelect ename from emp;UNION ALL用于返回（组合）两个查询中所有所有的行。INTERSECT用于返回两个查询中相同的行。Select ename from emp1 intersect select ename from emp2;MINUS用于返回两个查询中的不同的行。1.2、简单select查询 当我们可以用SQL*PLUS登录到

6、SQL下后，我们可以用DESC 显示某表的结构，也可以用select 语句简单查询表中的一些列的内容。例：要查询EMP表中员工的姓名、工资及出生日期，则：SQLselect ename, sal, hiredate from emp;2、伪列及伪表Oracle系统为了实现完整的关系数据库功能，系统专门提供了一组称为伪列（Pseudocolumn）的数据库列，这些列不是在建立对象（如建表）时由我们完成的，而是在我们建立对象时由自动Oracle完成的。Oracle目前有以下的伪列： CURRVAL and NEXTVAL 使用序列号的保留字 LEVEL 查询数据所对应的级 ROWID 记录的唯一标

7、识 ROWNUM 限制查询结果集的数量 Oracle 还提供了一个DUAL 的伪表，该目的表主要是保证在使用SELECT 语句中语句的完整性而提供的，如：我们要查询当前的系统日期及时间，而系统的日期和时间并是放在一个指定的表里。所以在 from 语句后就没有表名给出。为了使用 from 后有个表名，我们就用DUAL代替。如： 例1：查询Oracle系统日期及时间： SQL select to_char( sysdate,yyyy.mm.dd hh24:mi:ss) from DUAL; TO_CHAR(SYSDATE,YY - 2001.06.02 07:28:09 例2：计算一下 5000+

8、5000*0.1 的结果是多少，则： SQL select 5000+5000*0.1 from DUAL; 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.WHERE condition

9、CONNECT BY condition START WITH conditionGROUP BY expn HAVING expn UNION ALL | INTERSECT | MINUS SELECT . . . ORDER BY expn ASC | DESC FOR UPDATE OF user.table | view column NOWAIT详细语法结构需查阅最新ORACLE 原版ORACLE8i SQL REFERENCE MANUAL或ORACLE9i SQL REFERENCE MANUAL4、SQL中的单记录函数许多资料（包括Oracle 的资料）把Oracle的SQL

10、语句中用到的函数分为单值函数和多值函数，单值函数又分为字符函数和数字函数。下面分别介绍它们。4.1、单记录字符函数函 数 说 明 ASCII返回对应字符的十进制值 CHR给出十进制返回字符 CONCAT拼接两个字符串，与 | 相同 INITCAT将字符串的第一个字母变为大写 INSTR找出某个字符串的位置 INSTRB找出某个字符串的位置和字节数 LENGTH以字符给出字符串的长度 LENGTHB以字节给出字符串的长度 LOWER将字符串转换成小写 LPAD使用指定的字符在字符的左边填充 LTRIM在左边裁剪掉指定的字符 RPAD使用指定的字符在字符的右边填充 RTRIM在右边裁剪掉指定的字符

11、 REPLACE执行字符串搜索和替换 SUBSTR取字符串的子串 SUBSTRB取字符串的子串（以字节） SOUNDEX返回一个同音字符串 TRANSLATE执行字符串搜索和替换 TRIM裁剪掉前面或后面的字符串 UPPER将字符串变为大写 NVL以一个值来替换空值1、ASCII()是字符串。返回与指定的字符对应的十进制数。SQL select ascii(A) A,ascii(a) a,ascii(0) zero,ascii( ) space from dual; A a ZERO SPACE- - - -65 97 48 32SQL select ascii(赵) zhao,length(

12、赵) leng from dual; ZHAO LENG- - 54740 12、CHR(NCHAR)给出整数，返回对应字符。如：SQL select chr(54740) zhao,chr(65) chr65 from dual;ZH C- -赵 A3、CONCAT(,)返回字符串c1与字符串c2合并后的值。例如：SQL select concat(010-,8801 8159)|转23 赵元杰电话 from dual;赵元杰电话-010-8801 8159转234、INITCAP()返回字符串c1 并第一个字母变为大写。例如：SQL select initcap(simth) upp fr

13、om dual;UPP-Simth5、INSTR(, )在一个字符串中搜索指定的字符，返回发现指定的字符的位置。C1: 被搜索的字符串C2: 希望搜索的字符串I: 搜索的开始位置，缺省是1J: 出现的位置，缺省是1。SQL SELECT INSTR (Oracle Training, ra, 1, 2) Instring FROM DUAL; Instring- 96、INSTRB(, )除了返回的字节外 ，与INSTR 相同，7、LENGTH( )返回字符串 c 的长度。SQL l 1 select name,length(name),addr,length(addr),sal,length

14、(to_char(sal) 2* from nchar_tstSQL /NAME LENGTH(NAME) ADDR LENGTH(ADDR) SAL LENGTH(TO_CHAR(SAL)- - - - - - 赵元杰 3 北京市海淀区 6 99999.99 88、LENGTHB( )以字节返回字符串的字节数。SQL select name,lengthb(name),length(name) from nchar_tst;NAME LENGTHB(NAME) LENGTH(NAME)- - -赵元杰 6 39、LOWER ( )返回字符串并将所有字符变为小写。SQL select lowe

15、r(AaBbCcDd) AaBbCcDd from dual;AABBCCDD-aabbccdd10、UPPER( )与 LOWER 相反，将给出字符串变为大写。如：SQL select upper(AaBbCcDd) AaBbCcDd from dual;AABBCCDD-AABBCCDD11、RPAD和LPAD(粘贴字符)RPAD(string，Length，set)LPAD(string，Length，set) RPAD在列的右边粘贴字符； LPAD在列的左边粘贴字符。例1：SQLselect RPAD(City，35，.)，temperature from weather; RPAD(

16、City，35，.) temperature - - CLEVELAND. 85 LOS ANGELES. 81 . （即不够35个字符用.填满）12、LTRIM(左截断)RTRIM（右截断） 函数LTRIM (string ,set)Left TRIM (左截断)删去左边出现的任何set 字符。RTRIM (string ,set)Right TRIM (右截断)删去右边出现的任何set 字符。例1：SELECT RTRIM (Mother Theresa, The, The) “Example of RightTrimming” FROM DUAL;Example of Right-Mot

17、her Theresa,13、SUBSTR Substr(string，start，Count)取子字符串中函数对字串（或字段），从start字符 开始，连续取 count 个字符并返回结果，如果没有指count则一直取到尾。select phone,substr(phone,1,3) | 0 | substr(phone,4)from telecommunication where master=中国电信;14、SUBSTRB(string，start，Count)对字串（或字段），从start字节 开始，连续取 count 个字节并返回结果，如果没有指count则一直取到尾。15、REPL

18、ACE (string ,string_in,string_out)String: 希望被替换的字符串或变量。String_in: 被替换字符串。String_out: 要替换字符串。SQL select replace(Informaix中国公司,Informaix,IBM Informix) 2 IBM数据库 from dual;IBM数据库-IBM Informix中国公司16、SOUNDEX( )返回一个与给定的字符串读音相同的字符串（不管拼写是否一样）。SELECT DPL_NAME FROM DENIED_PARTIES_LIST WHERESOUNDEX(DPL_NAME) =

19、SOUNDEX(Saddam Hussain) ;DPL_NAME-Al HusseniSadda Al Sada.17、REPLACE (string ,string_in,string_out)String:希望被替换的字符串或变量。String_in: 被替换字符串。String_out: 要替换字符串。SELECT REPLACE (Oracle, Or, Mir) “Example “ FROM DUAL;Example-Miracle18、TRIM（ FROM ） TRIM可以使你对给定的字符串进行裁剪（前面，后面或前后）。 如果指定 LEADING, Oracle 从trim_c

20、har 中裁剪掉前面的字符； 如果指定TRAILING, Oracle 从trim_char 中裁剪掉尾面的字符； 如果指定两个都指定或一个都没有给出，Oracle从trim_char 中裁剪掉前面及尾面的字符； 如果不指定 trim_character, 缺省为空格符； 如果只指定trim_source, Oracle Oracle从trim_char 中裁剪掉前面及尾面的字符。 例子：将下面字符串中的前面和后面的0字符都去掉： SELECT TRIM (0 FROM 0009872348900) TRIM Example FROM DUAL; TRIM example - 98723489

21、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

22、(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 截取valueVSIZE(value)返回value在ORACLE的存储空间大小1、ABS( )返回指定值的绝对值。如：SQL select abs(100),abs(-

23、100) from dual; ABS(100) ABS(-100)- - 100 1002、ACOS( )给出反余弦的值。如：SQL Select acos(-1) acos from dual; ACOS-3.141592653、ASIN( )给出反正弦的值。如：SQL select asin(-1) arc sine from dual; arc sine-1.57079634、ATAN （ ）返回一个数字的反正切值。如：SQL select atan(-1) arc tangent from dual;arc tangent- -.785398165、CEIL( )返回大于或等于给出数

24、字的最小整数。如：SQL select ceil(3.14159) from dual;CEIL(3.14159)-46、COS ( )返回一个数字余弦值。如：SQL select cos(-3.1415926) from dual;COS(-3.1415926)-17、COSH ( )返回一个数字双曲余弦值。如：SQL select cosh(20) cosh from dual; COSH- 2425825988、EXP ( )返回一个数字 e 的 n 次方的值。如：SQL select exp(2),exp(1) from dual;EXP(2) EXP(1)- - 7.3890561

25、2.718281839、FLOOR ( )对给定的数字取整数，如：SQL select floor(123.45),floor(45.56) from dual;FLOOR(123.45) FLOOR(45.56)- -4510、LN( )返回一个数字的对数值，n 是大于 0 的数字，如：SQL select ln(1),ln(2),ln(3) from dual; LN(1) LN(2) LN(3)- - - 0 .693147181 1.0986122911、LOG( , )返回一个以n1为底的n2的对数，n1不是0或1的正数。如：SQL select log(2,1),log(2,2)

26、from dual; LOG(2,1) LOG(2,2)- -112、MOD( , )SQL Select mod(10,3), mod(10,2), mod(10,4) from dual; MOD(10,3) MOD(10,2) MOD(10,4)- - - 1 0 213、POWER ( , )返回 n1 的 n2 次方值，如：SQL select power(2,10),power(3,3) from dual;POWER(2,10) POWER(3,3)- -2714、ROUND(value,precision) 按照指定的精度进行舍入；select round(55.5),round(-55.5),trunc(55.5),trunc(-55.5)from dual;round(55.5) round(-55.5) trunc(55.5) trunc(-55.5)- - -