Oracleのいくつかのクエリーの例、(クエリーの優先度、Sql--plus関数、数値関数、日付時間関数)[詳細]
12087 ワード
（ ，Sql--plus ， ， ）
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)];
Condition(s) :
=
>
>=
<
<=
<>
!=
And
Or
Not
Between and : BETWEEN ( )
In: IN 。 In(,,,) ,
Like：
l LIKE
l :
• % ( )。
• _ 。
Escape：
： 。 ： [%] [/%]、[_] [/_]， [ESCAPE ‘/’]
Null:
IS (NOT) NULL 。
// 10
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp where deptno=10;
// MANAGER
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp where job='MANAGER';
// 10 MANAGER
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp where job='MANAGER' anddeptno=10;
：
l 。
l ， 。
l DD-MON-RR。
// 10
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp where deptno>10;
// 3000
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp where sal>=3000;
// 3000
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp where sal<>3000;
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp where sal !=3000;
// 2000 3000
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp where sal>2000 andsal<3000;
// 2000 3000
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp where sal between 1600and 3000;
//
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp where sal>=1600 andsal<=3000;
// 10，20
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp where deptno in(10,20);
// ： ：
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp where deptno =10 ordeptno=20;
// S
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp where ename like 'S%';
// S
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp where ename like '%S';
// N
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp where ename like '__N%';
// N
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp where ename like '%N%';
// %
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp where ename like'%/%_'escape'/';
// %
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp where ename like'%/%%'escape'/';
// null
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp where comm is null;
// null
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp where comm is not null;
：
1
2
3
4
Is not null like not in
5
Not between
6
not
7
and
8
Or
：
-à ---》 -à -à 。
：
l ORDER BY
• ASC（ascend）:
• DESC（descend）:
l ORDERBY SELECT 。
// asc
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp order by deptno;
// desc
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp order by deptno desc;
// 20
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp where deptno=20 order by empno asc;
： order by
//
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp order by deptno asc,empno desc;
： : ,
：
：
SELECT *|{[DISTINCT] column|expression [alias],...}
FROM table
[WHERE condition(s)]
[ORDER BY {column, expr, alias} [ASC|DESC]];
where 、between、like、in、null、not 、or、and ， order by 。
Sql--plus ：
oracle .
LOWER:
UPPER：
INITCAP：
CONCAT：
SUBSTR： ( ， ， )
LENGTH：
INSTR：（ ， ）
LPAD：（ , ， ）
| RPAD：（ ， , ）
TRIM ：
REPLACE：（ , [, ]）: , , ， 。
// scott （ ）
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp where lower(ename)='scott';
SQL> selectempno,ename,job,mgr,hiredate,sal,comm,deptno from emp where upper(ename)='SCOTT';
//
SQL> selectempno,concat(ename,job),mgr,hiredate,sal,comm,deptno from emp;
//
SQL> select empno,concat(ename||'iswork:',job),mgr,hiredate,sal,comm,deptno from emp;
// O
SQL> selectempno,instr(ename,'O'),job,mgr,hiredate,sal,comm,deptno from emp;
// O
SQL> selectempno,instr(ename,'O'),length(ename),job,mgr,hiredate,sal,comm,deptno from emp;
// ERK
SQL> selectempno,instr(ename,'O'),length(ename),job,mgr,hiredate,sal,comm,deptno from empwhere substr(job,3)='ERK';
： ,
// 3
SQL> selectempno,instr(ename,'O'),length(ename),job,mgr,hiredate,sal,comm,deptno from empwhere substr(job,3,3)='ERK';
// 10 *
SQL> selectempno,ename,job,mgr,hiredate,LPAD(sal,10,'*'),comm,deptno from emp;
// 10 *
SQL> selectempno,ename,job,mgr,hiredate,RPAD(sal,10,'*'),comm,deptno from emp;
// S
SQL> selectempno,TRIM('S' from ename),job,mgr,hiredate,10,comm,deptno from emp;
：
SQL> selectempno,TRIM( both 'S' from ename),job,mgr,hiredate,10,comm,deptno from emp;
// S
SQL> selectempno,TRIM( Leading 'S' from ename),job,mgr,hiredate,10,comm,deptno from emp;
//
SQL> selectempno,LTRIM( ename,'S'),job,mgr,hiredate,10,comm,deptno from emp;
// S
SQL> selectempno,TRIM( trailing 'S' from ename),job,mgr,hiredate,10,comm,deptno from emp;
：
SQL> selectempno,RTRIM( ename,'S'),job,mgr,hiredate,10,comm,deptno from emp;
：
ROUND:
ROUND(45.926, 2) 45.93
TRUNC:
TRUNC(45.926, 2) 45.92
MOD:
MOD(1600, 300) 100
ABS：
CEIL： value
FLOOR： value
SQRT : value 。
// 46
SQL> select round(45.56) from dual;
// 45.56
SQL> select abs(-45.56) from dual;
// -45
SQL> select ceil(-45.56) from dual;
// -46
SQL> selectfloor(-45.56) from dual;
// 300
SQL> selectmod(1800,500) from dual
// ： 1800.11
SQL> selecttrunc(1800.11111,2) from dual;
// ： , 0. 1000
SQL> selecttrunc(1899.11111,-3) from dual;
Oracle : 。 DD-MON-RR. 、 。
add_months(date,count); count
last_day(date); date
months_between(date1,dates); date1 date2
new_time(date,this’,’other’); date this other
next_day(day,’day’); , day
sysdate();
current_timestamp();
round：
trunc
：
l 。
l 。
l 24 。
// ：07-4 -11 11.15.38.390000 +08:00
SQL> selectcurrent_timestamp from dual;
// ：2011-4-7 11
SQL> selectsysdate from dual;
// 3 ：2011-7-7 11:18:36
selectadd_months(sysdate,3) from dual;
// ：2011-4-30 11:19:4
selectlast_day(sysdate) from dual;
// ： ：4
SQL> selectmonths_between(add_months(sysdate,4),sysdate) from dual;
// GMT AST
SQL> select new_time(sysdate,'GMT','AST')from dual;
// ’ ’
SQL> select next_day(sysdate,' ') from dual;
：
：
： 。
to_char(date,’format’): format
:
。
。
。
// YYYY/MM/DD
SQL> selectto_char(current_timestamp,'YYYY/MM/DD') from dual;
// YYYY/MM/DDHH24/MI/SS AM
SQL> selectto_char(current_timestamp,'YYYY/MM/DD HH24/MI/SS AM') from dual;
//DD “of” MONTH
SQL> selectto_char(current_timestamp,'YYYY DD "of" MONTH HH/MI/SS AM') from dual;
// $99,999 5( $ ) , ########
SQL> selectto_char(11111,'$99,999') from dual;
to_number(char);
to_date(string,’format’); format , foramt , (DD-MON-YY);
//
SQL> selectto_date('2011-02-08','YYYY-MM-DD') from dual;
//
SQL> selectto_date('2011-07-15','YYYY-MM-DD')-to_date('2011-02-08','YYYY-MM-DD') fromdual;
//
SQL> select(to_date('2011-07-15','YYYY-MM-DD')-to_date('2011-02-08','YYYY-MM-DD'))/7 fromdual;
//
SQL> selectceil((to_date('2011-07-15','YYYY-MM-DD')-to_date('2011-02-08','YYYY-MM-DD'))/7)from dual;
chartorowid(char); rowid
rowidtochar（x）; rowid
， ：
NVL (expr1, expr2)
NVL2 (expr1, expr2, expr3)
NULLIF (expr1, expr2)
COALESCE (expr1, expr2, ..., exprn)
nvl()
：
l 、 、 。
l :
• NVL(commission_pct,0)
• NVL(hire_date,'01-JAN-97')
• NVL(job_id,'No Job Yet')
// comm null 0
SQL> select empno,ename,job,mgr,hiredate,sal,nvl(comm,0),deptnofrom emp;
//
SQL>select empno,ename,job,mgr,nvl(hiredate,to_date('2011-1-10','YYYY-MM-DD')),sal,nvl(comm,0),deptnofrom emp;
// job redarmy
SQL> selectempno,ename,nvl(job,'redarmy'),mgr,nvl(hiredate,to_date('2011-1-10','YYYY-MM-DD')),sal,nvl(comm,0),deptnofrom emp;
// =（ + ）
SQL> selectempno,ename,job,mgr,hiredate,(nvl(sal,0)+nvl(comm,0)) as " ",deptnofrom emp;
NVL2 (expr1, expr2, expr3) : expr1 NULL， expr2； NULL， expr3。
:expr1!=null?exrp2:expr3;
//
SQL> selectempno,ename,job,mgr,hiredate,nvl2(comm,sal+comm,sal) as " ",deptnofrom emp;
NULLIF (expr1, expr2) : NULL， expr1
//
SQL> select enameas "expr1",job as "expr2",nullif(length(ename),length(job))from emp;
l COALESCE NVL COALESCE 。
l , ， COALESCE 。
SQL> selectempno,ename,job,mgr,hiredate,coalesce(comm,sal,10000) as "salll",deptno from emp;
l ： 。 。
：IF-THEN-ELSE
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHENcomparison_expr2 THEN return_expr2
WHENcomparison_exprn THEN return_exprn
ELSE else_expr]
END
DECODE(col|expression, search1, result1
[, search2,result2,...,]
[, default])
// Manager 5000
SQL> selectename,job,case job when 'MANAGER' then nvl(sal,0)+5000 end as" " from emp;
//
SQL> select ename,job,
2 casejob when 'MANAGER' then nvl(sal,0)+5000+nvl(comm,0)
3 elsenvl(sal,0)+nvl(comm,0)
4 end
5 fromemp;
// decode
SQL> selectename,job
2 ,decode(job,'MANAGER', nvl(sal,0)+5000+nvl(comm,0),
3 'CLERK',nvl(sal,0)+nvl(comm,0)+200,
4 nvl(sal,0)+nvl(comm,0)) as " "
5 fromemp;