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') secondfrom 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') number5from 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

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