oracle筆記4——簡單sql查詢

一、常用的sql語句

 1、使用算術表達式  +  -  *  /

         找出每個員工的獎金和工資的總和

         select sal+comm,ename from emp;

2、連接配接運算符||

3、使用字段别名as

4、空值 is null

5、去除重複行  distinct

6、查詢結果排序order by asc （desc）

7、比較運算符><(!= or <>)  between and

8、in  not in

9、模糊查詢  %----表示零或多個字元

                      _  表示一個字元

對于特殊符号可以用ESCAPE辨別符來查找

select * from emp where ename like '%*_%'  escape  '*'

上面的escape表示  * 後面的那個符号不當成特殊字元處理，就是查找普通的_符号

10、邏輯運算符   or and not

練習：

1、找出部門10中得經理（MANAGER）和部門20中得普通員工（CLERK）

select * from emp where (deptno=10 and job='MANAGER') OR (deptno = 20 and job='clerk');

2、找出部門10中既不是經理也不是普通員工而且工資大于等于2000的員工

select * from emp where deptno=10 and job not in('Manager','Clerk') and sal >=2000

3、找出有獎金的員工的不同工作

select distinct job from emp where commis not null and comm >0

4 、找出沒有獎金或者獎金低于500的員工

select * from emp where comm is null or comm<500

二、sql函數

1、單行函數

1）字元函數

UPPDER  轉換為大寫

select UPPER('abcde') from dual;     ---ABCDE

select * from emp where ename = UPPER('smith');

LOWER  轉換為小寫

select  lower('ABCDE') from dual;

INITCAP--使第一個字母大寫

select inicap(ename) from emp;

concat  --連接配接兩個字段

select concat('a','b') from dual;    

select 'a'||'b' from dual;

結果是：  ab

select concat(empno,ename) from emp; 

結果是： 7369Smith

                 ....

substr

select substr('abcde', length('abcde')-2) from dual;

結果：cde

select substr('abcde',-3,3) from dual;

結果：cde

length

select length(ename) from emp;

replace  将’A‘替換掉’a‘

select replace (ename,'a','A') from emp;

instr  定義字元的位置索引

select instr('Hello World', 'or') from dual;   

結果：8 === equals (index of   in 'java')

Lpad

lpad('Smith',10,'*')  左側填充*   （*****Smith）

Rpad

rpad('Smith',10,'*')  右側填充*  （Smith*****）

注意：10---代表總共十個字元，不足十個字元用’*‘填充，lpad-向左側填充； rpad--向右側填充

Trim

trim('        Mr Smith           ') 作用：過濾掉首尾的空格，相當于java裡面的trim（）方法  （Mr Smith）

2）數值函數

round

select round(412,-2) from dual;          -----------400

select round (412.313,2) from dual;  ------------412.31

mod

trunc 截取

select trunc(412.13,-2) from dual;  ---400

3）日期函數

MONTHS_BETWEEN()傳回兩個日期之間的月份數。

SQL> select months_between(to_date('20090228', 'yyyymmdd'), to_date('20080228', 'yyyymmdd')) as months from dual;

    MONTHS

----------

        12

select months_between(sysdate,hiredate) from emp;

 ADD_MONTHS函數在輸入日期上加上指定的幾個月傳回一個新的日期。如果給出一負數，傳回值日期之前幾個月日期。

 ADD_MONTHS(DATE,NUMBER)中的NUMBER應當是整數，給出小數時，正數被截為小于該數的最大整數，負數被截為大于該數的最小整數。

 例：add_months(to_date('29-Feb-96','d-mon-yyyy'),-12.99)   傳回  28－Feb-95

注：上例中29調整為28，是因為96年二月份最後一天是29号，而95年二月份最後一天是28号。

 add_months(to_date('15-Nov-1961','d-mon-yyyy'),1)  傳回   15-Dec-1961

 add_months(to_date('30-Nov-1961','d-mon-yyyy'),1)  傳回   31-Dec-1961

注：從30調整為31，為了保持都是對應最後一天。

 add_months(to_date('31-Jan-1999','d-mon-yyyy'),1)  傳回   28-Feb-1999

注：函數将31日調為28日，以使結果對應新一月的最後一天，因1999年2月隻有28天。

select add_months(sysdate,1) from dual;

next_day();

NEXT_DAT函數傳回輸入日期開始，緊随其後的指定星期對應的日期，weekday可以用全稱，也可以用縮寫（如'monday','tuesday','wed')

例：next_day(to_date('1999.11.24','yyyy.mm.dd')，'friday')       傳回   1999年11月26日

例：next_day(to_date('1999.11.24','yyyy.mm.dd')，'wed')         傳回   1999年11月01日

注：1999年11月24日是星期三，第二個參數是星期五，是兩天後。第二個例子由于日期正好是星期三，隻能用下一個星期三日期。

last_day（d）： 時間點d當月份最後一天

select last_day(sysdate) from dual;--2011.12.31

NEW_TIME(d,c1,c2)  

NEW_TIME(d,c1,c2)

--轉換新時區

ex.

select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') 台北,

to_char(NEW_TIME(sysdate,'EST','GMT'),'YYYY/MM/DD HH24:MI:SS') 格林威治

from dual;

台北                 格林威治

-------------------- -------------------

2007/09/21 14:36:53  2007/09/21 19:36:53

4)轉換函數

to_char

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

select to_char(sysdate,'fmyyyy-mm-dd') from dual;

select to_char(sal,'L999,999,999') from emp;

select to_char(sysdate,'D') from dual;//傳回星期

to_number

select to_number('13') + to_number('14') from dual;

to_date

select to_date('20111205','yyyyMMdd') from dual;

select to_date('20111205','yyyy-MM-dd') from dual;

5)通用函數

nvl(a,b)函數 如果a不為null 則傳回a,如果a為null則傳回b;

select nvl(comm,0) from emp;---如果comm 為null ，就為0 （預設）

 NULLIF()函數

如果表達式exp1 與 exp2 值相等則傳回null，否則傳回exp1的值

select NULLIF(2,2) from dual;

結果：---null

select NULLIF(2,3) from dual;

結果：---2

nvl2(a,b,c)函數,如果a不為null 則傳回b,如果a為null則傳回c;

select empno,ename,sal,comm,nvl2(comm,sal+comm,sal) total from emp;

 注意：如果comm（獎金）不為空，就獲得獎金+工資的和，否則就獲得工資的金額

COALESCE()函數  依次考察各個參數表達式，遇到非null的值即停止并傳回該值

***********************

CASE表達式

select empno,ename,sal

     case deptno

               when 10 then '财務部'

               when  20 then '研發部'

               when 30 then '銷售部'

               else '未知部門'

               end 部門

  from emp;

decode()函數， 和case表達式類似，decode（）

函數也用于實作多路分支結構

例子：

select empno,ename,sal,decode(deptno,10,'财務部'

                                                                            20，’研發部‘

                                                                             30，’銷售部‘

                                                      ’未知部門‘)

部門

from emp;