1、 函数
日期时间相关:
To_Date(时间字段,时间格式) 字符串转换为日期、时间格式
TO_DATE('2004-8-4','YYYY-MM-DD’);
insert into ins_date values(to_date('2004-08-10 15:33:12','yyyy-mm-dd hh24:mi:ss'));
insert into tdate(col2) values(to_date('2004-8-10 下午 03:33:12',15:17:26 2 'yyyy-mm-dd am hh:mi:ss'));
Trunc(Sysdate)
(1).TRUNC(for dates)
TRUNC函数为指定元素而截去的日期值。
其具体的语法格式如:TRUNC(date[,fmt])
date一个日期值
fmt日期格式,该日期将由指定的元素格式所截去。忽略它则由最近的日期截去
select trunc(sysdate)-to_date('2004-11-4','yyyy-mm-dd') from dual;
select TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss') from dual;
--1999-12-23 8:40:44
select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss')) from dual;
--1999-12-23
select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss'),'yyyy') from dual;
--1999-01-01
select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss'),'MM') from dual;
--1999-12-01
select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss'),'dd') from dual;
--1999-12-23
select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss'),'hh') from dual;
--1999-12-23 8:00:00
select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss'),'mi') from dual;
--1999-12-23 8:40:00
(2).TRUNC(for number)
TRUNC函数返回处理后的数值,其工作机制与ROUND函数极为类似,只是该函数不对指定小数前或后的部分做相应舍入选择处理,而统统截去。
其具体的语法格式如下
TRUNC(number[,decimals])
number待做截取处理的数值
decimals指明需保留小数点后面的位数。可选项,忽略它则截去所有的小数部分
下面是该函数的使用情况:
TRUNC(89.985,2)=89.98
TRUNC(89.985)=89
TRUNC(89.985,-1)=80
注意:第二个参数可以为负数,表示为小数点左边指定位数后面的部分截去,即均以0记。
SYSDATE 取得数据库服务器当前日期、时间
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS') FROM DUAL;
NEXT_DAY
LAST_DAY
ADD_MONTHS
MONTHS_BETWEEN
SELECT LAST_DAY(SYSDATE) FROM DUAL
日期的各部分的常用的的写法
取时间点的年份的写法:
SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL;
取时间点的月份的写法:
SELECT TO_CHAR(SYSDATE,'MM') FROM DUAL;
取时间点的日的写法:
SELECT TO_CHAR(SYSDATE,'DD') FROM DUAL;
取时间点的时的写法:
SELECT TO_CHAR(SYSDATE,'HH24') FROM DUAL;
取时间点的分的写法:
SELECT TO_CHAR(SYSDATE,'MI') FROM DUAL;
取时间点的秒的写法:
SELECT TO_CHAR(SYSDATE,'SS') FROM DUAL;
取时间点的日期的写法:
SELECT TRUNC(SYSDATE) FROM DUAL;
取时间点的时间的写法:
SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') FROM DUAL;
日期,时间形态变为字符形态:
SELECT TO_CHAR(SYSDATE) FROM DUAL;
将字符串转换成日期或时间形态:
SELECT TO_DATE('2003/08/01') FROM DUAL;
返回参数的星期几的写法:
SELECT TO_CHAR(SYSDATE,'D') FROM DUAL;
返回参数一年中的第几天的写法:
SELECT TO_CHAR(SYSDATE,'DDD') FROM DUAL;
返回午夜和参数中指定的时间值之间的秒数的写法:
SELECT TO_CHAR(SYSDATE,'SSSSS') FROM DUAL;
返回参数中一年的第几周的写法:
SELECT TO_CHAR(SYSDATE,'WW') FROM DUAL;
数子相关:
To_Number()
+、-、*、/
ABS、COS、EXP、LN、LOG、MOD、POWER、ROUND
SIN、SINH、SQRT、TAN、TRUNC
AVG、COUNT、MAX、MIN、SUM
GREATEST 返回参数的最大值
LEAST 返回参数的最小值
SELECT GREATEST(3, 4, 5)*4 FROM DUAL;
SELECT POWER(2,3) FROM DUAL;
字符串相关:
To_Char()
To_Char(日期字段,'yyyymmdd')
|| 连接两个字符串
SELECT RM_INT_FIELD||'--'||RM_STR_FIELD||'YES' FROM ROYAL_MTABLE;
TRIM/LTRIM/RTRIM 截断串左(右)指定字符串(包括空串)
ASCII(CHAR) 得到字符串的第一个字符的ASCII值
SELECT ASCII('ABCDE') FROM DUAL;
结果: 65
CHR(N) 得到数值N指定的字符
SELECT CHR(68) FROM DUAL;
结果: D
CONCAT(CHAR1,CHAR2) 如何连接两个字符串
SELECT CONCAT('ABC','DEFGH') FROM DUAL;
结果: 'ABCDEFGH'
DECODE(CHAR,N1,CHAR1,N2,CHAR2...) 如何将列中的数值代替为字符串
SELECT DECODE(DAY,1,'SUN',2,'MON') FROM DUAL;
INITCAP(CHAR) 将字符串CHAR的第一个字符为大写,其余为小写
SELECT INITCAP('ABCDE') FROM DUAL;
LENGTH(CHAR) 取一字符串CHAR的长度。
SELECT LENGTH('ABCDE') FROM DUAL;
LOWER(CHAR) 将字符串CHAR全部变为小写。
SELECT LOWER('ABCDE') FROM DUAL;
LPAD(CHAR1,N,CHAR2) 用字符串CHAR2包括的字符左填CHAR1,使其长度为N
SELECT LPAD('ABCDEFG',10'123') FROM DUAL;
结果: '123ABCDEFG'
LTRIM(CHAR,SET) 从字符串CHAR的左边移去字符串SET中的字符,直到第一个不是SET中的字符为止。
SELECT ('CDEFG','CD') FROM DUAL;
结果: 'EFG'
NLS_INITCAP(CHAR) 取字符CHAR的第一个字符大写,其余字符为小写。
SELECT NLS_INITCAP('ABCDE') FROM DUAL;
NLS_LOWER(CHAR) 将字符串CHAR包括的字符全部小写。
SELECT NLS_LOWER('AAAA') FROM DUAL;
NLS_UPPER(CHAR) 将字符串CHAR包括的字符全部大写。
SELECT NLS_UPPER('AAAA') FROM DUAL;
REPLACE(CHAR1,CHAR2,CHAR3) 用字符串CHAR3代替每一个列值为CHAR2的列,其结果放在CHAR1中
SELECT REPLACE(EMP_NO,'123','456') FROM DUAL;
RPAD(CHAR1,N,CHAR2) 用字符串CHAR2右填字符串CHAR1,使其长度为N
SELECT RPAD('234',8,'0') FROM DUAL;
RTRIM(CHAR,SET) 移去字符串CHAR右边的字符串SET中的字符,直到最后一个不是SET中的字符为止
SELECT RTRIM('ABCDE','DE') FROM DUAL;
SUBSTR(CHAR,M,N) 得到字符串CHAR从M处开始的N个字符. 双字节字符,一个汉字为一个字符的
SELECT SUBSTR('ABCDE',2,3) FROM DUAL;
TRANSLATE(CHAR1,CHAR2,CHAR3) 将CHAR1中的CHAR2的部分用CHAR3代替。
SELECT TRANSLATE('ABDCDEFGdH','DE','MN') FROM DUAL; 结果: ABMCMNFGdH
SELECT translate('12a3.456','0123456789.',' ') FROM DUAL; 结果:a
UPPER(CHAR) 将字符串CHAR全部为大写。
TO_MULTI_BYTE(CHAR) 将半角转换为全角。
select 'a',to_multi_byte('a') from dual; 结果:a a
系统函数:
DECODE函数
格式:DECODE(value, if1, then1, if2, then2...,else)
例子:
假设表ROYAL_DTABLE中有如下数据:
RD_INT_FIELD RD_STR_FIELD RM_INT_FIELD
--------------------------------------
1 royal 1
2 bill 2
3 joy 1
请观察如下SQL语句输出结果。
SELECT DECODE(RD_STR_FIELD, 'royal', 'royaltest', 'bill', 'billgates', RD_STR_FIELD) AS DC FROM ROYAL_DTABLE;
DC
---------
royaltest
billgates
joy
2、 语法
having子句的用法
having子句对group by子句所确定的行组进行控制,having子句条件中只允许涉及常量,聚组函数或group by 子句中的列。
外部联接"+"的用法
外部联接"+"按其在"="的左边或右边分左联接和右联接.若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行,则前者的
行与后者中的一个空行相匹配并被返回.若二者均不带’+’,则二者中无法匹配的均被返回.利用外部联接"+",可以替代效率十分低下的 not in
运算,大大提高运行速度.例如,下面这条命令执行起来很慢
用外联接提高表连接的查询速度
在作表连接(常用于视图)时,常使用以下方法来查询数据:
SELECT PAY_NO, PROJECT_NAME
FROM A
WHERE A.PAY_NO NOT IN (SELECT PAY_
NO FROM B WHERE VALUE >=120000);
---- 但是若表A有10000条记录,表B有10000条记录,则要用掉30分钟才能查完,主要因为NOT IN要进 行一条一条的比较,共需要10000*10000
次比较后,才能得到结果。该用外联接后,可以缩短到1分左右的时间:
SELECT PAY_NO,PROJECT_NAME
FROM A,B
WHERE A.PAY_NO=B.PAY_NO(+)
AND B.PAY_NO IS NULL
AND B.VALUE >=12000;
3、 SQL语句
Select 检索相关:
设置日期语言
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
也可以这样
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')
可查看
select * from nls_session_parameters
select * from V$NLS_PARAMETERS
处理不同时区
select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate from dual;
时间为null的用法
select id, active_date from table1
UNION
select 1, TO_DATE(null) from dual;
注意:要用TO_DATE(null)
求某天是星期几
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;
显示:星期一
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
显示:monday
查询两个日期间的天数
select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;
查询某天的数据
select * from table_name where trunc(日期字段)=to_date('2003-05-02','yyyy-mm-dd');
查询某时间段的数据
select * from table_name where 日期字段>=To_Date('2004-09-01','YYYY-MM-DD hh24:mi:ss') AND 日期字段<=
To_Date('2004-09-30','YYYY-MM-DD hh24:mi:ss');
查询某月中所有周五的具体日期
select to_char(t.d,'YY-MM-DD') from (
select trunc(sysdate, 'MM')+rownum-1 as d
from dba_objects
where rownum < 32) t
where to_char(t.d, 'MM') = to_char(sysdate, 'MM') --找出当前月份的周五的日期
and trim(to_char(t.d, 'Day')) = '星期五'
--------
03-05-02
03-05-09
03-05-16
03-05-23
03-05-30
如果把where to_char(t.d, 'MM') = to_char(sysdate, 'MM')改成sysdate-90,即为查找当前月份的前三个月中的每周五的日期。
日期时间间隔操作
当前时间减去7分钟的时间
select sysdate,sysdate - interval '7' MINUTE from dual
当前时间减去7小时的时间
select sysdate - interval '7' hour from dual
当前时间减去7天的时间
select sysdate - interval '7' day from dual
当前时间减去7月的时间
select sysdate,sysdate - interval '7' month from dual
当前时间减去7年的时间
select sysdate,sysdate - interval '7' year from dual
时间间隔乘以一个数字
select sysdate,sysdate - 8 *interval '2' hour from dual
日期到字符操作
select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual
select sysdate,to_char(sysdate,'yyyy-ddd hh:mi:ss') from dual
select sysdate,to_char(sysdate,'yyyy-mm iw-d hh:mi:ss') from dual
参考oracle的相关关文档(ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4.HTM#48515)
字符到日期操作
select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual
具体用法和上面的to_char差不多。
毫秒级的数据类型
--返回当前时间 年月日小时分秒毫秒
select to_char(current_timestamp(5),'DD-MON-YYYY HH24:MI:SSxFF') from dual;
--返回当前 时间的秒毫秒,可以指定秒后面的精度(最大=9)
select to_char(current_timestamp(9),'MI:SSxFF') from dual;
获取当前年龄、天数
SELECT TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(birthday,'YYYY') Age FROM 表名;
SELECT TO_CHAR(sysdate,'yyyyMMdd')-TO_CHAR(birthday,'yyyyMMdd') Days FROM 表名;
查找出前N条记录
SELECT * FROM empLOYEE WHERE ROWNUM < n ORDER BY empno;
查找表中[N,M]条记录
取得某列中第N大的行
select column_name from
(select table_name.*,dense_rank() over (order by column desc) rank from table_name)
where rank = &N;
假如要返回前5条记录:
select * from tablename where rownum<6;(或是rownum <= 5 或是rownum != 6)
假如要返回第5-9条记录:
select * from tablename
where …
and rownum<10
minus
select * from tablename
where …
and rownum<5
order by name
选出结果后用name排序显示结果。(先选再排序)
注意:只能用以上符号(<、<=、!=)。
select * from tablename where rownum != 10;返回的是前9条记录。
不能用:>,>=,=,Between...and。由于rownum是一个总是从1开始的伪列,Oracle 认为这种条件 不成立,查不到记录.
另外,这个方法更快:
select * from (
select rownum r,a from yourtable
where rownum <= 20
order by name )
where r > 10
这样取出第11-20条记录!(先选再排序再选)
要先排序再选则须用select嵌套:内层排序外层选。
rownum是随着结果集生成的,一旦生成,就不会变化了;同时,生成的结果是依次递加的,没有1就永远不会有2!
rownum 是在 查询集合产生的过程中产生的伪列,并且如果where条件中存在 rownum 条件的话,则:
1: 假如 判定条件是常量,则:
只能 rownum = 1, <= 大于1 的自然数, = 大于1 的数是没有结果的,大于一个数也是没有结果的
即 当出现一个 rownum 不满足条件的时候则 查询结束 this is stop key!
2: 当判定值不是常量的时候
若条件是 = var , 则只有当 var 为1 的时候才满足条件,这个时候不存在 stop key ,必须进行 full scan ,对每个满足其他where条件的数
据进行判定
选出一行后才能去选rownum=2的行……
查找一列中第N大的值
select * from (select t.*,dense_rank() over (order by sal) rank from employee) where rank = N;
查找、删除重复记录
法一: 用Group by语句此查找很快的
select count(num), max(name) from student --查找表中num列重复的,列出重复的记录数,并列出他的name属性
group by num
having count(num) >1 --按num分组后找出表中num列重复,即出现次数大于一次
delete from student(上面Select的)
这样的话就把所有重复的都删除了。-----慎重
法二:当表比较大(例如10万条以上)时,这个方法的效率之差令人无法忍受,需要另想办法:
---- 执行下面SQL语句后就可以显示所有DRAWING和DSNO相同且重复的记录
SELECT * FROM EM5_PIPE_PREFAB
WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D --D相当于First,Second
WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND
EM5_PIPE_PREFAB.DSNO=D.DSNO);
---- 执行下面SQL语句后就可以刪除所有DRAWING和DSNO相同且重复的记录
DELETE FROM EM5_PIPE_PREFAB
WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D
WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND
EM5_PIPE_PREFAB.DSNO=D.DSNO);
两个结果集互加的函数
SQL>SELECT * FROM BSEMPMS_OLD INTERSECT SELECT * FROM BSEMPMS_NEW;
SQL>SELECT * FROM BSEMPMS_OLD UNION SELECT * FROM BSEMPMS_NEW
SQL>SELECT * FROM BSEMPMS_OLD UNION ALL SELECT * FROM BSEMPMS_NEW;
两个结果集互减的函数
SQL>SELECT * FROM BSEMPMS_OLD MINUS SELECT * FROM BSEMPMS_NEW;
把select出来的结果导到一个文本文件中
SQL>SPOOL C:/ABCD.TXT;
SQL>select * from table;
SQL >spool off;
中文排序
Oracle9i之前,中文是按照二进制编码进行排序的
在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值
SCHINESE_RADICAL_M 按照部首(第一顺序)、笔划(第二顺序)排序
SCHINESE_STROKE_M 按照笔划(第一顺序)、部首(第二顺序)排序
SCHINESE_PINYIN_M 按照拼音排序
日期时间相关:
To_Date(时间字段,时间格式) 字符串转换为日期、时间格式
TO_DATE('2004-8-4','YYYY-MM-DD’);
insert into ins_date values(to_date('2004-08-10 15:33:12','yyyy-mm-dd hh24:mi:ss'));
insert into tdate(col2) values(to_date('2004-8-10 下午 03:33:12',15:17:26 2 'yyyy-mm-dd am hh:mi:ss'));
Trunc(Sysdate)
(1).TRUNC(for dates)
TRUNC函数为指定元素而截去的日期值。
其具体的语法格式如:TRUNC(date[,fmt])
date一个日期值
fmt日期格式,该日期将由指定的元素格式所截去。忽略它则由最近的日期截去
select trunc(sysdate)-to_date('2004-11-4','yyyy-mm-dd') from dual;
select TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss') from dual;
--1999-12-23 8:40:44
select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss')) from dual;
--1999-12-23
select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss'),'yyyy') from dual;
--1999-01-01
select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss'),'MM') from dual;
--1999-12-01
select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss'),'dd') from dual;
--1999-12-23
select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss'),'hh') from dual;
--1999-12-23 8:00:00
select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss'),'mi') from dual;
--1999-12-23 8:40:00
(2).TRUNC(for number)
TRUNC函数返回处理后的数值,其工作机制与ROUND函数极为类似,只是该函数不对指定小数前或后的部分做相应舍入选择处理,而统统截去。
其具体的语法格式如下
TRUNC(number[,decimals])
number待做截取处理的数值
decimals指明需保留小数点后面的位数。可选项,忽略它则截去所有的小数部分
下面是该函数的使用情况:
TRUNC(89.985,2)=89.98
TRUNC(89.985)=89
TRUNC(89.985,-1)=80
注意:第二个参数可以为负数,表示为小数点左边指定位数后面的部分截去,即均以0记。
SYSDATE 取得数据库服务器当前日期、时间
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS') FROM DUAL;
NEXT_DAY
LAST_DAY
ADD_MONTHS
MONTHS_BETWEEN
SELECT LAST_DAY(SYSDATE) FROM DUAL
日期的各部分的常用的的写法
取时间点的年份的写法:
SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL;
取时间点的月份的写法:
SELECT TO_CHAR(SYSDATE,'MM') FROM DUAL;
取时间点的日的写法:
SELECT TO_CHAR(SYSDATE,'DD') FROM DUAL;
取时间点的时的写法:
SELECT TO_CHAR(SYSDATE,'HH24') FROM DUAL;
取时间点的分的写法:
SELECT TO_CHAR(SYSDATE,'MI') FROM DUAL;
取时间点的秒的写法:
SELECT TO_CHAR(SYSDATE,'SS') FROM DUAL;
取时间点的日期的写法:
SELECT TRUNC(SYSDATE) FROM DUAL;
取时间点的时间的写法:
SELECT TO_CHAR(SYSDATE,'HH24:MI:SS') FROM DUAL;
日期,时间形态变为字符形态:
SELECT TO_CHAR(SYSDATE) FROM DUAL;
将字符串转换成日期或时间形态:
SELECT TO_DATE('2003/08/01') FROM DUAL;
返回参数的星期几的写法:
SELECT TO_CHAR(SYSDATE,'D') FROM DUAL;
返回参数一年中的第几天的写法:
SELECT TO_CHAR(SYSDATE,'DDD') FROM DUAL;
返回午夜和参数中指定的时间值之间的秒数的写法:
SELECT TO_CHAR(SYSDATE,'SSSSS') FROM DUAL;
返回参数中一年的第几周的写法:
SELECT TO_CHAR(SYSDATE,'WW') FROM DUAL;
数子相关:
To_Number()
+、-、*、/
ABS、COS、EXP、LN、LOG、MOD、POWER、ROUND
SIN、SINH、SQRT、TAN、TRUNC
AVG、COUNT、MAX、MIN、SUM
GREATEST 返回参数的最大值
LEAST 返回参数的最小值
SELECT GREATEST(3, 4, 5)*4 FROM DUAL;
SELECT POWER(2,3) FROM DUAL;
字符串相关:
To_Char()
To_Char(日期字段,'yyyymmdd')
|| 连接两个字符串
SELECT RM_INT_FIELD||'--'||RM_STR_FIELD||'YES' FROM ROYAL_MTABLE;
TRIM/LTRIM/RTRIM 截断串左(右)指定字符串(包括空串)
ASCII(CHAR) 得到字符串的第一个字符的ASCII值
SELECT ASCII('ABCDE') FROM DUAL;
结果: 65
CHR(N) 得到数值N指定的字符
SELECT CHR(68) FROM DUAL;
结果: D
CONCAT(CHAR1,CHAR2) 如何连接两个字符串
SELECT CONCAT('ABC','DEFGH') FROM DUAL;
结果: 'ABCDEFGH'
DECODE(CHAR,N1,CHAR1,N2,CHAR2...) 如何将列中的数值代替为字符串
SELECT DECODE(DAY,1,'SUN',2,'MON') FROM DUAL;
INITCAP(CHAR) 将字符串CHAR的第一个字符为大写,其余为小写
SELECT INITCAP('ABCDE') FROM DUAL;
LENGTH(CHAR) 取一字符串CHAR的长度。
SELECT LENGTH('ABCDE') FROM DUAL;
LOWER(CHAR) 将字符串CHAR全部变为小写。
SELECT LOWER('ABCDE') FROM DUAL;
LPAD(CHAR1,N,CHAR2) 用字符串CHAR2包括的字符左填CHAR1,使其长度为N
SELECT LPAD('ABCDEFG',10'123') FROM DUAL;
结果: '123ABCDEFG'
LTRIM(CHAR,SET) 从字符串CHAR的左边移去字符串SET中的字符,直到第一个不是SET中的字符为止。
SELECT ('CDEFG','CD') FROM DUAL;
结果: 'EFG'
NLS_INITCAP(CHAR) 取字符CHAR的第一个字符大写,其余字符为小写。
SELECT NLS_INITCAP('ABCDE') FROM DUAL;
NLS_LOWER(CHAR) 将字符串CHAR包括的字符全部小写。
SELECT NLS_LOWER('AAAA') FROM DUAL;
NLS_UPPER(CHAR) 将字符串CHAR包括的字符全部大写。
SELECT NLS_UPPER('AAAA') FROM DUAL;
REPLACE(CHAR1,CHAR2,CHAR3) 用字符串CHAR3代替每一个列值为CHAR2的列,其结果放在CHAR1中
SELECT REPLACE(EMP_NO,'123','456') FROM DUAL;
RPAD(CHAR1,N,CHAR2) 用字符串CHAR2右填字符串CHAR1,使其长度为N
SELECT RPAD('234',8,'0') FROM DUAL;
RTRIM(CHAR,SET) 移去字符串CHAR右边的字符串SET中的字符,直到最后一个不是SET中的字符为止
SELECT RTRIM('ABCDE','DE') FROM DUAL;
SUBSTR(CHAR,M,N) 得到字符串CHAR从M处开始的N个字符. 双字节字符,一个汉字为一个字符的
SELECT SUBSTR('ABCDE',2,3) FROM DUAL;
TRANSLATE(CHAR1,CHAR2,CHAR3) 将CHAR1中的CHAR2的部分用CHAR3代替。
SELECT TRANSLATE('ABDCDEFGdH','DE','MN') FROM DUAL; 结果: ABMCMNFGdH
SELECT translate('12a3.456','0123456789.',' ') FROM DUAL; 结果:a
UPPER(CHAR) 将字符串CHAR全部为大写。
TO_MULTI_BYTE(CHAR) 将半角转换为全角。
select 'a',to_multi_byte('a') from dual; 结果:a a
系统函数:
DECODE函数
格式:DECODE(value, if1, then1, if2, then2...,else)
例子:
假设表ROYAL_DTABLE中有如下数据:
RD_INT_FIELD RD_STR_FIELD RM_INT_FIELD
--------------------------------------
1 royal 1
2 bill 2
3 joy 1
请观察如下SQL语句输出结果。
SELECT DECODE(RD_STR_FIELD, 'royal', 'royaltest', 'bill', 'billgates', RD_STR_FIELD) AS DC FROM ROYAL_DTABLE;
DC
---------
royaltest
billgates
joy
2、 语法
having子句的用法
having子句对group by子句所确定的行组进行控制,having子句条件中只允许涉及常量,聚组函数或group by 子句中的列。
外部联接"+"的用法
外部联接"+"按其在"="的左边或右边分左联接和右联接.若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行,则前者的
行与后者中的一个空行相匹配并被返回.若二者均不带’+’,则二者中无法匹配的均被返回.利用外部联接"+",可以替代效率十分低下的 not in
运算,大大提高运行速度.例如,下面这条命令执行起来很慢
用外联接提高表连接的查询速度
在作表连接(常用于视图)时,常使用以下方法来查询数据:
SELECT PAY_NO, PROJECT_NAME
FROM A
WHERE A.PAY_NO NOT IN (SELECT PAY_
NO FROM B WHERE VALUE >=120000);
---- 但是若表A有10000条记录,表B有10000条记录,则要用掉30分钟才能查完,主要因为NOT IN要进 行一条一条的比较,共需要10000*10000
次比较后,才能得到结果。该用外联接后,可以缩短到1分左右的时间:
SELECT PAY_NO,PROJECT_NAME
FROM A,B
WHERE A.PAY_NO=B.PAY_NO(+)
AND B.PAY_NO IS NULL
AND B.VALUE >=12000;
3、 SQL语句
Select 检索相关:
设置日期语言
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
也可以这样
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')
可查看
select * from nls_session_parameters
select * from V$NLS_PARAMETERS
处理不同时区
select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate from dual;
时间为null的用法
select id, active_date from table1
UNION
select 1, TO_DATE(null) from dual;
注意:要用TO_DATE(null)
求某天是星期几
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;
显示:星期一
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
显示:monday
查询两个日期间的天数
select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;
查询某天的数据
select * from table_name where trunc(日期字段)=to_date('2003-05-02','yyyy-mm-dd');
查询某时间段的数据
select * from table_name where 日期字段>=To_Date('2004-09-01','YYYY-MM-DD hh24:mi:ss') AND 日期字段<=
To_Date('2004-09-30','YYYY-MM-DD hh24:mi:ss');
查询某月中所有周五的具体日期
select to_char(t.d,'YY-MM-DD') from (
select trunc(sysdate, 'MM')+rownum-1 as d
from dba_objects
where rownum < 32) t
where to_char(t.d, 'MM') = to_char(sysdate, 'MM') --找出当前月份的周五的日期
and trim(to_char(t.d, 'Day')) = '星期五'
--------
03-05-02
03-05-09
03-05-16
03-05-23
03-05-30
如果把where to_char(t.d, 'MM') = to_char(sysdate, 'MM')改成sysdate-90,即为查找当前月份的前三个月中的每周五的日期。
日期时间间隔操作
当前时间减去7分钟的时间
select sysdate,sysdate - interval '7' MINUTE from dual
当前时间减去7小时的时间
select sysdate - interval '7' hour from dual
当前时间减去7天的时间
select sysdate - interval '7' day from dual
当前时间减去7月的时间
select sysdate,sysdate - interval '7' month from dual
当前时间减去7年的时间
select sysdate,sysdate - interval '7' year from dual
时间间隔乘以一个数字
select sysdate,sysdate - 8 *interval '2' hour from dual
日期到字符操作
select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual
select sysdate,to_char(sysdate,'yyyy-ddd hh:mi:ss') from dual
select sysdate,to_char(sysdate,'yyyy-mm iw-d hh:mi:ss') from dual
参考oracle的相关关文档(ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4.HTM#48515)
字符到日期操作
select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual
具体用法和上面的to_char差不多。
毫秒级的数据类型
--返回当前时间 年月日小时分秒毫秒
select to_char(current_timestamp(5),'DD-MON-YYYY HH24:MI:SSxFF') from dual;
--返回当前 时间的秒毫秒,可以指定秒后面的精度(最大=9)
select to_char(current_timestamp(9),'MI:SSxFF') from dual;
获取当前年龄、天数
SELECT TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(birthday,'YYYY') Age FROM 表名;
SELECT TO_CHAR(sysdate,'yyyyMMdd')-TO_CHAR(birthday,'yyyyMMdd') Days FROM 表名;
查找出前N条记录
SELECT * FROM empLOYEE WHERE ROWNUM < n ORDER BY empno;
查找表中[N,M]条记录
取得某列中第N大的行
select column_name from
(select table_name.*,dense_rank() over (order by column desc) rank from table_name)
where rank = &N;
假如要返回前5条记录:
select * from tablename where rownum<6;(或是rownum <= 5 或是rownum != 6)
假如要返回第5-9条记录:
select * from tablename
where …
and rownum<10
minus
select * from tablename
where …
and rownum<5
order by name
选出结果后用name排序显示结果。(先选再排序)
注意:只能用以上符号(<、<=、!=)。
select * from tablename where rownum != 10;返回的是前9条记录。
不能用:>,>=,=,Between...and。由于rownum是一个总是从1开始的伪列,Oracle 认为这种条件 不成立,查不到记录.
另外,这个方法更快:
select * from (
select rownum r,a from yourtable
where rownum <= 20
order by name )
where r > 10
这样取出第11-20条记录!(先选再排序再选)
要先排序再选则须用select嵌套:内层排序外层选。
rownum是随着结果集生成的,一旦生成,就不会变化了;同时,生成的结果是依次递加的,没有1就永远不会有2!
rownum 是在 查询集合产生的过程中产生的伪列,并且如果where条件中存在 rownum 条件的话,则:
1: 假如 判定条件是常量,则:
只能 rownum = 1, <= 大于1 的自然数, = 大于1 的数是没有结果的,大于一个数也是没有结果的
即 当出现一个 rownum 不满足条件的时候则 查询结束 this is stop key!
2: 当判定值不是常量的时候
若条件是 = var , 则只有当 var 为1 的时候才满足条件,这个时候不存在 stop key ,必须进行 full scan ,对每个满足其他where条件的数
据进行判定
选出一行后才能去选rownum=2的行……
查找一列中第N大的值
select * from (select t.*,dense_rank() over (order by sal) rank from employee) where rank = N;
查找、删除重复记录
法一: 用Group by语句此查找很快的
select count(num), max(name) from student --查找表中num列重复的,列出重复的记录数,并列出他的name属性
group by num
having count(num) >1 --按num分组后找出表中num列重复,即出现次数大于一次
delete from student(上面Select的)
这样的话就把所有重复的都删除了。-----慎重
法二:当表比较大(例如10万条以上)时,这个方法的效率之差令人无法忍受,需要另想办法:
---- 执行下面SQL语句后就可以显示所有DRAWING和DSNO相同且重复的记录
SELECT * FROM EM5_PIPE_PREFAB
WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D --D相当于First,Second
WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND
EM5_PIPE_PREFAB.DSNO=D.DSNO);
---- 执行下面SQL语句后就可以刪除所有DRAWING和DSNO相同且重复的记录
DELETE FROM EM5_PIPE_PREFAB
WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D
WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND
EM5_PIPE_PREFAB.DSNO=D.DSNO);
两个结果集互加的函数
SQL>SELECT * FROM BSEMPMS_OLD INTERSECT SELECT * FROM BSEMPMS_NEW;
SQL>SELECT * FROM BSEMPMS_OLD UNION SELECT * FROM BSEMPMS_NEW
SQL>SELECT * FROM BSEMPMS_OLD UNION ALL SELECT * FROM BSEMPMS_NEW;
两个结果集互减的函数
SQL>SELECT * FROM BSEMPMS_OLD MINUS SELECT * FROM BSEMPMS_NEW;
把select出来的结果导到一个文本文件中
SQL>SPOOL C:/ABCD.TXT;
SQL>select * from table;
SQL >spool off;
中文排序
Oracle9i之前,中文是按照二进制编码进行排序的
在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值
SCHINESE_RADICAL_M 按照部首(第一顺序)、笔划(第二顺序)排序
SCHINESE_STROKE_M 按照笔划(第一顺序)、部首(第二顺序)排序
SCHINESE_PINYIN_M 按照拼音排序