`
javatome
  • 浏览: 820140 次
  • 性别: Icon_minigender_1
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

SQL基本函数学习笔记(order by decode)

 
阅读更多
这是我很久以前刚开始学习SQL时作的笔记,今天翻资料的时候被我翻了出来,觉得放在那里以后遗失掉了也是可惜,不如放到博客上,以后也可以看看,还可以帮助一些刚开始学习SQL的朋友入个门。不过都是按我自己的掌握程度来记的,估计也不太适合别人,就随便看看吧。
一、to_char函数
Parameter Explanation
YEAR Year spelled out
YYYY 4 digits of year
YYY 3 digits of year
YY 2 digits of year
Y 1 digit of year
IYYY 4digits year based on the ISO standard
IYY 3 digits of ISO year
IY 2 digits of ISO year
I 1 digit of ISO year
Q Quarter of year (1 .. 4)
MM Month (01 ..12)
MON Abbreviated name of month
MONTH Name of month,
padded with blanks to length of 9 characters.
RM Roman numeral month (I .. XII)
WW Week of year (1-53) where 7 days 1 week (与星期几无关)
W Week of month (1-5) where 7 days 1 week (与星期几无关)
IW Week of year (1-52 or 1-53) based on the ISO standard.
(周一到周日为一周,若1日为周五-周日,则为上年最后一周)
D Day of week (周日1 .. 周六7)
DY Abbreviated name of day.
DAY Name of day
DD Day of month (1-31)
DDTH Day of month (1-31)
DDD Day of year (1-366)
J Julian day;the number of days since January 1, 4712 BC.
HH Hour of day (1-12).
HH12 Hour of day (1-12).
HH24 Hour of day (0-23).
MI Minute (0-59).
SS Second (0-59).
SSSSS Seconds past midnight (0-86399).
FF Fractional seconds.
XXXXX 转换为8进制
to_char(1210.73, '9999.9') would return '1210.7'
to_char(1210.73, '9,999.99') would return '1,210.73'
to_char(1210.73, '$9,999.00') would return '$1,210.73'
to_char(21, '000099') would return '000021'
to_char(21, '999999') would return ' 21'
to_char(21, 'FM999999') would return '21'
to_char(sysdate, 'FMYYY') would return '8' --FM表示去掉0或空格
to_char(125, 'XXXXX') would return '7D'
to_number('7D','XXXXX') would return '125'
另注:trunc与to_char的比较
trunc原意为截取数据小数部分,例如:
trunc(23.48429387) 返回23
trunc(23.48429387,3) 返回23.484
trunc(-1.443432) 返回-1
但trunc(date) 具有与to_char(date) 相似的功能,但有区别:
trunc(sysdate,'cc') 取当世纪的第一天 to_char(sysdate,'cc') 取当世纪数值
trunc(sysdate,'yyyy') 取当年的第一天 to_char(sysdate,'yyyy') 取当年数值
trunc(sysdate,'iyyy') 取上年的最后一天 to_char(sysdate,'iyyy') 取当年数值
trunc(sysdate,'q') 取当季第一天 to_char(sysdate,'iyyy') 取当季数值
trunc(sysdate,'mm') 取当月第一天 to_char(sysdate,'mm') 取当月数值
trunc(sysdate,'ww') 取当周第一天(周二) to_char(sysdate,'ww') 取当周数值(第几周)
trunc(sysdate,'iw') 取当周第一天(周一) to_char(sysdate,'iw') 取当周数值(第几周)
总结:trunc对日期的截取由后面参数决定位置后将之后所有数值为默认初始值!
二、order by函数

-- 自动将结果列表按字段顺序对应排序

order by 1 , 2 , 3

-- 可对字段 decode 后再排序,下例为将 2222 、 1111 排在前两位,其他按顺序排列

select a,b, c from t1

order by decode(a, '2222' , 1 , '1111' , 2 ,a)

个人注:order by decode相当于转换后再排序,所以结果值必须是相同类型,且可排序。如

order by decode(a, '2222' , 1 , '1111' , 2 , 3) <=> order by decode(a, '2222' , 1.0 , '1111' , 2 .5, 3.0) <=> order by decode(a, '2222' ,'a' , '1111' , 'b', 'c')

-- 如遇到空值时, order by 默认将空值排在最下面,如要排在最上面,则:

order by nulls first

三、取整类函数整理

ceil : 取整 ( 大 )

select ceil (- 1.001 ) value from dual /- 1

floor :取整(小)

select floor(- 1.001 ) value from dual /- 2

trunc :取整(截取)

select trunc(- 1.001 ) value from dual /- 1

round :取整 ( 舍入 )

select round(- 1.001 ) value from dual /- 1

应用举例:(根据时间算年龄)
trunc(months_between( sysdate ,birthday)/ 12 ) Age
四、LPAD与RPAD的用法:
比较:select LPAD('WhaT is tHis',5),LPAD('WhaT is tHis',25),LPAD('WhaT is tHis',25,'-') from dual;
|WhaT| WhaT is tHis| -------------WhaT is tHis
select RPAD('WhaT is tHis',5),RPAD('WhaT is tHis',25),RPAD('WhaT is tHis',25,'-') from dual;
|WhaT| WhaT is tHis | WhaT is tHis-------------
作用:作为调整格式的输出,例:

with x as

( select 'aa' chr from dual

union all

select 'bb' chr from dual)

select level ,chr,lpad( ' ' ,( level - 1 )* 5 , '-' )||chr other from x connect by level <= 3

说明:若LPAD对空字符串操作无效,因此至少必须有' '空格符!

LPAD的实际应用:

select distinct lpad(selltype, 2 , '0' ) from lccont;

由于系统中其他的selltype字段均为01、02等2位,但出现7,另有null值
所以使用 lpad(selltype,2,'0') 可以即保存null值又将7更新为07

五、rank() order by()和row_number() order by()的区别:

with t as (

select 1 a from dual

union all

select 2 a from dual

union all

select 1 a from dual

)

select a,rank() over( order by a) rank,row_number() over( order by a) num from t;

六、translate和replace的区别:

select translate('What is this','ait','-*%') from dual;---Wh-% *s %h*s

selectreplace('What is this','ait','-*%') from dual;-----What is this

selectreplace('What is this','hat','-*%') from dual;-----W-*% is this

translate的实际应用:

select translate('12XXX5869XXXX','0123456789'||'12XXX5869XXXX','0123456789')from dual;

<取字符串中的所有数字>

七、sysdate与current_date的差别:

selectsysdate,current_datefrom dual;

某些情况下current_date会比sysdate快一秒。

我们认为current_date是将current_timestamp中毫秒四舍五入后的返回

虽然没有找到文档支持,但是想来应该八九不离十。

八、一些有用的时间函数:

select NEXT_DAY(sysdate,5) from dual;--下一个星期四(不算今天)

select NEXT_DAY(sysdate,'星期三') from dual;--下一个星期一(大小写都可)

select LAST_DAY(sysdate) from dual;--当月最后一天

九、一些有用的数字/字符函数:

select GREATEST(a,b) Greatest from t2;----------求最大值

select LEAST(a,b) LEAST from t2;-------------求最小值

select NULLIF('a','b'),NULLIF('a','a') from dual;-------a=b则返回null;a<>b则返回a

select nvl(null,'a'),nvl('1','a') from dual;------------为null时返回a,不会null返回原值

select nvl2(null,'a','b'),nvl2('1','a','b') from dual;--为null时返回b,不为null返回a

selectCOALESCE(null,5,6,null,9) from dual;-----返回第一个非空值

select POWER(2.2,2.2) from dual; ----a的b次方

十、一些有用的字符串操作函数:

select CHR(95) from dual;-------------ASCII码对应字符

select ASCII('_') from dual;----------字符对应ASCII码

select concat('aa','bb') from dual;------------等同于||

select INITCAP('whaT is this') from dual;------首字母大写,其余小写

select TO_MULTI_BYTE('ABC abc 中华') from dual;----------半角变全角

select TO_SINGLE_BYTE('ABC abc中华') from dual;------全角变半角

select VSIZE('abc中华') from dual;-----返回字节数

select INSTR('CORPORATE FLOOR','OR',3,2) from dual;----从第3位开始查找第2个'OR'

十一、WMSYS.WM_CONCAT函数应用:
此函数作用在于将某字段所有值列出到一个单元格中

select replace (WMSYS.WM_CONCAT(num), ',' , ' ' ) from t1;

行列转换中最简单的一种方法。

十二、单元格内文本换行的方法:
Tab键 chr(9)
换行符chr(10)
回车符chr(13)
空格符chr(32)

select 'a' ||chr( 9 )|| 'b' from dual;

select 'a' ||chr( 13 )|| 'b' from dual;

注:须在SQLPlus中查看结果,PL/SQL Developer中无法显示换行
分享到:
评论

相关推荐

    SQL中 decode()函数简介

    DECODE函数,是ORACLE公司的SQL软件ORACLE PL/SQL所提供的特有函数计算方式,以其简洁的运算方式,可控的数据模型和灵活的格式转换而闻名。 今天看别人的SQL时看这里面还有decode()函数,以前从来没接触到,上网...

    关于decode函数的使用

    主要作用:将查询结果翻译成其他值(即以其他形式表现...Select decode(columnname,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值) From talbename Where … 其中columnname为要选择的table中所定义的column,

    关于oracle decode函数的用法

    关于oracle decode函数的用法

    常用的SQL函数详细讲解

    字符函数 数值函数 日期函数 数据转换函数 decode函数 nvl函数

    decode函数.docx

    decode函数.docx

    orcale中DECODE函数

    1 DECODE 中的if-then-else逻辑 在逻辑编程中,经常用到If – Then –Else 进行逻辑判断。在DECODE的语法中,实际上就是这样的逻辑处理过程。

    Oracle中Decode()函数使用技巧.doc

    Oracle中Decode()函数使用技巧

    SQL语句的DECODE和NVL

    SQL语句的DECODE和NVL,肯定能帮到你,好用!

    Oracle中Decode()函数使用技巧

    Oracle中Decode()函数使用技巧Oracle中Decode()函数使用技巧Oracle中Decode()函数使用技巧

    python decode()和encode()函数详解

    decode和encode是他们之间的转化函数 大致的过程如下 decode encode str --&gt; str(Unicode) --&gt; str decode()方法语法: str.decode(encoding=‘UTF-8’,errors=‘strict’) 参数: encoding – 要使用的编码...

    oracle中decode()函数使用技巧

    oracle中decode()函数使用技巧 很有帮助的哦

    Oracle中Decode()函数的有关用法

    Oracle中Decode()函数的有关用法Oracle中Decode()函数的有关用法

    oracle的decode函数

    DECODE函数相当于一条件语句(IF).它将输入数值与函数中的参数列表相比较,根据输入值返回一个对应值。

    Oracle 中 decode 函数用法

    decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值) 该函数的含义如下: IF 条件=值1 THEN  RETURN(翻译值1) ELSIF 条件=值2 THEN  RETURN(翻译值2)  ...... ELSIF 条件=值n THEN  RETURN(翻译值n) ...

    MYSQL,SQLSERVER,ORACLE常用的函数

    SQL中的单记录函数 1.ASCII 返回与指定的字符对应的十进制数; SQL&gt; select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual; A A ZERO SPACE --------- --------- --------- --------- ...

    Oracle中DECODE()函数的使用法

    DECODE()函数,它将输入数值与函数中的参数列表相比较,根据输入值返回一个对应值。区别于SQL的其它函数,DECODE函数还能识别和操作空值。本文详细介绍了DECODE函数的语法。

    gzip124 decode函数说明

    gzip124 源代码中decode函数说明

    用JavaScript实现PHP的urldecode/urldecode函数

    这个是 utf-8版本的 js实现 php的 urlencode() 和 urldecode()两个函数的功能。 在传送cookie的时候 在 php端实现 url编码 但要用 js来解码cookie的时候 就出现汉字不能不能识别的问题 这个 js 很好的解决

    decode函数[归类].pdf

    decode函数[归类].pdf

    Oracle-SQL基础到存储过程下载(第二阶段)

    014.sql高级查询_去重distinct.mp4 015.sql高级查询_null操作.mp4 016.sql高级查询_in操作.mp4 017.sql高级查询_between-and操作.mp4 018.sql高级查询_like模糊查询.mp4 019.sql高级查询_集合...035.sql函数_decode与

Global site tag (gtag.js) - Google Analytics