Oracle中常用SQL语句（二）

1、时间处理

（1）to_char和to_date基本使用

--日期
--年 yyyy yyy yy year
--月 month mm mon month
--日+星期  dd ddd(一年中第几天) dy day 
--小时  hh hh24 
--分 mi
--秒 ss

--如：

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')currenttime, 
       to_char(sysdate,'yyyy') year,
       to_char(sysdate,'mm') month,
       to_char(sysdate,'dd') day,
       to_char(sysdate,'day') week,
       to_char(sysdate,'hh24')hour,
       to_char(sysdate,'mi') minute,
       to_char(sysdate,'ss') second
from dual;

（2）months_between

select months_between(to_date('07-01-2017','MM-DD-YYYY'),to_date('07-07-2017','MM-DD-YYYY')) "MONTHS"
FROM DUAL; 

（3）next_day

select sysdate today, next_day(sysdate,6) nextweek from dual;

3、字符函数

（1）字符函数

--字符函数
select substr('abcdefg',1,5)substr,                     --字符串截取
       instr('abcdefg','bc') instr,                     --查找子串

       'Hello'||'World' concat,                         --连接

       trim('  wish  ') trim,                           --去前后空格
       rtrim('wish  ') rtrim,                           --去后面空格
       ltrim('  wish') ltrim,                           --去前面空格

       trim(leading 'w' from 'wish') deleteprefix,      --去前缀
       trim(trailing 'h' from 'wish') deletetrailing,   --去后缀
       trim('w' from 'wish') trim1,

       ascii('A') A1, 
       ascii('a') A2,                 --ascii(转换为对应的十进制数)
       chr(65) C1, 
       chr(97) C2,                    --chr(十进制转对应字符)

       length('abcdefg') len,                         --length 

       lower('WISH')lower,                            --变小写
       upper('wish')upper,                            --变大写
       initcap('wish')initcap,                        --大小写变换

       replace('wish1','1','youhappy') replace,       --替换

       translate('wish1','1','y')translate,           --转换，对应一位(前面的位数大于等于后面的位数)
       translate('wish1','sh1','hy')translate1,

       concat('11','22') concat          　　　　　　　　 --连接


from dual;

（2）to_number

--to_number(expr)
--to_number(expr,format)
--to_number(expr,format,'nls-param')

select to_number('0123')number1,        --converts a string to number
       trunc(to_number('0123.123'),2) number2,
       to_number('120.11','999.99') number3,
 　　　 to_number('0a','xx') number4,    --converts a hex number to decimal
       to_number(100000,'xxxxxx') number5

from dual;

4、聚合函数

（1）count

--count (distinct|all)
select count(1) as count from student; --效率最高
select count(*) as count from student;    
select count(distinct score) from student;    

（2）avg

--avg (distinct|all)
select avg(score) score from student;
select avg(distinct score) from student;
select classno,avg(score) score from student group by classno;

（3）max

--max (distinct|all)
select max(score) from student;
select classno, max(score) score from student group by classno;

（4）min

--min (distinct|all)
select min(score) from student;
select classno, min(score) score from student group by classno;

（5）sum

--sum
select sum(score) from student;
select classno, sum(score) score from student group by classno;