oracle SQL简介（一）

select子句

from 子句

where子句

dual表，一行一列，dummy

函数，树结构查询，连接查询，子查询， 优化

1.   基本函数

1.1. 字符串操作

字符串大小写

select lower(ename),upper(ename),initcap(ename) from emp;

 

求长度

select length('王五') from dual;

select lengthb('王五') from dual;

--unicode长度

select lengthc('王五') from dual;

 

取子字符串

select ename,substr(ename,1,1) first,substr(ename,-1) last from emp;

select substr('张三李四王五赵六',1,6) from dual;

select substrb('张三李四王五赵六',1,6) from dual; --按字节取

select substrc('张三李四王五赵六',1,6) from dual; --unicode

select ename,instr(ename, 'A') from emp;

 

截取和添加

select trim (leading 'a' from 'aadddabcaaaaa') from dual;

select trim (trailing 'a' from 'aadddabcaaaaa') from dual;

select trim (both 'a' from 'aadddabcaaaaa') from dual;

select trim ('a' from 'aadddabcaaaaa') from dual;

 

select lpad(ename,20, '-'),rpad(ename,20, '-') from emp;

1.2. 操作数字

round,trunc   (可对日期操作)

ceil

e为底的幂

select exp(1) value from dual;

取e为底的对数

select ln(2.7182818284590451) value from dual;

取10为底对数

select log(10,10) value from dual;

圆周率

select acos(-1)  value  from dual;

取符号(-1,0,1)

select sign(-8) value from dual;

求集合最大值

select greatest(1,-2,4,3) value from dual;

求集合最小值

select least(1,-2,4,3) value from dual;

1.3. 操作日期

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss';

 

select date'1999-02-03' from dual;

select time'5:30:23' from dual;

select timestamp'1999-02-03 5:30:23' from dual;

系统日期时间

sysdate, systimestamp

SELECT to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS.FF3') FROM DUAL;

--日期直接相减

select round(sysdate-hiredate) days,sysdate,hiredate from emp;

--两个日期的月间隔

select trunc(months_between(sysdate,hiredate)),sysdate,hiredate from emp;

--六个月后是哪一天

select add_months(hiredate,6) ,hiredate from emp;

--该日期月底是哪一天

select last_day(hiredate),hiredate from emp;

--当前日期算起，下一个星期五是哪天

select next_day(hiredate,’friday’) hiredate from emp;

select next_day(hiredate,6) hiredate from emp

时间间隔:

select extract(year from sysdate) year,extract(month from sysdate) month, extract(day from sysdate) day from dual;

求某天是星期几

select to_char(sysdate,'day') from dual;

select to_char(sysdate,'d') from dual;

 

1.4. 类型转换

to_char,to_number,to_date

to_char常用格式:

The following are number examples for the to_char function.

select ename,to_char(sal,'9999.00') from salary from emp;

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转换日期为一个字符串的有效参数。这些参数可以用来组合。 

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

Parameter

Explanation

YEAR

Year, spelled out

YYYY

4-digit year

YYY

YY

Y

Last 3, 2, or 1 digit(s) of year.

IYY

IY

I

Last 3, 2, or 1 digit(s) of ISO year.

IYYY

4-digit year based on the ISO standard

Q

Quarter of year (1, 2, 3, 4; JAN-MAR = 1).

MM

Month (01-12; JAN = 01).

MON

Abbreviated name of month.

MONTH

Name of month, padded with blanks to length of 9 characters.

RM

Roman numeral month (I-XII; JAN = I).

WW

Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.

W

Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.

IW

Week of year (1-52 or 1-53) based on the ISO standard.

D

Day of week (1-7).

DAY

Name of day.

DD

Day of month (1-31).

DDD

Day of year (1-366).

DY

Abbreviated name of day.

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.

The following are date examples for the to_char function.

to_char(sysdate, 'yyyy/mm/dd');

would return '2003/07/09'

to_char(sysdate, 'Month DD, YYYY');

would return 'July 09, 2003'

to_char(sysdate, 'FMMonth DD, YYYY');

would return 'July 9, 2003'

to_char(sysdate, 'MON DDth, YYYY');

would return 'JUL 09TH, 2003'

to_char(sysdate, 'FMMON DDth, YYYY');

would return 'JUL 9TH, 2003'

to_char(sysdate, 'FMMon ddth, YYYY');

would return 'Jul 9th, 2003'

You will notice that in some examples, the format_mask parameter begins with "FM". This means that zeros and blanks are suppressed. This can be seen in the examples below.

to_char(sysdate, 'FMMonth DD, YYYY');

would return 'July 9, 2003'

to_char(sysdate, 'FMMON DDth, YYYY');

would return 'JUL 9TH, 2003'

to_char(sysdate, 'FMMon ddth, YYYY');

would return 'Jul 9th, 2003'

The zeros have been suppressed so that the day component shows as "9" as opposed to "09".

1.5. 操作null

nvl,nvl2,nullif, coalesce

 

select ename,comm,nvl(comm,-1) from emp;

select nvl(comm,0),nvl2(comm,’A’,’B’) type from emp;

select ename,nullif(ename,'KING') from emp;

COALESCE返回参数列表中的第一个非空值，所有关系型数据库系统都支持的标准函数

select coalesce(comm,sal,100) from emp;

1.6. 分组统计

select sum(comm),count(comm),count(*) from emp;

select avg(comm),avg(nvl(comm,0)) from emp;

求平均工资大于2000的工作

select job,avg(sal) from emp group by job having avg(sal) >2000;

按部门分组

select deptno,sum(sal) from emp group by deptno;

rollup分组

按部门分组并求统计：

select deptno,sum(sal) from emp group by rollup(deptno);

按部门和工种分组并求统计：

select deptno,job,sum(sal) from emp group by rollup(deptno,job);

grouping(列名)，判断列是否参加了分组，0为参加了分组

select deptno,job,grouping(deptno),grouping(job),sum(sal) from emp group by rollup(deptno,job);

cube分组：

select deptno,job,grouping(deptno),grouping(job),sum(sal) from emp group by cube(deptno,job);

为2**n个结果集

1.7. 分支

case

统计各年雇用人数，并转为列

select count(*) total,

       sum(case  to_char(hiredate,'yyyy') when '1980' then count(hiredate) end ) "1980" ,

       sum(case  to_char(hiredate,'yyyy') when '1981' then count(hiredate) end ) "1981" ,

       sum(case  to_char(hiredate,'yyyy') when '1982' then count(hiredate) end ) "1982" ,

       sum(case  to_char(hiredate,'yyyy') when '1983' then count(hiredate) else 0 end ) "1983" ,

       sum(case  to_char(hiredate,'yyyy') when '1984' then count(hiredate) end ) "1984" ,

       sum(case  to_char(hiredate,'yyyy') when '1985' then count(hiredate) end ) "1985" ,

       sum(case  to_char(hiredate,'yyyy') when '1987' then count(hiredate) end ) "1987"

from emp group by hiredate;

 

统计各部门各岗位收入总和，并按部门转为列显示

select deptno,job,sum(sal) from emp group by deptno,job order by 1;

 

select job,

        sum(case DEPTNO  when 10 then sal else 0 end) "dep 10" ,

        sum(case DEPTNO  when 20 then sal else 0 end) "dep 20" ,

        sum(case DEPTNO  when 30 then sal else 0 end) "dep 30" ,

        sum(case DEPTNO  when 40 then sal else 0 end) "dep 40" ,

       sum(sal) total

from emp group by job;

 

decode

求税率

select ename,sal,decode (trunc(sal/2000,0),

  0, 0.00,

  1,0.09,

  2,0.20,

  3,0.30,

  0.40) tax_rate

from emp;