程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> Oracle lesson 2 SQL 查詢和 SQL 函數(中)

Oracle lesson 2 SQL 查詢和 SQL 函數(中)

編輯:Oracle數據庫基礎
操作符和函數部分

 

操作符

Oracle 支持的 SQL 操作符分類如下:

算術操作符

算術操作符用於執行數值計算

可以在SQL語句中使用算術表達式,算術表達式由數值數據類型的列名、數值常量和連接它們的算術操作符組成

算術操作符包括加(+)、減(-)、乘(*)、除(/)

例:

SQL> SELECT itemdesc, max_level - qty_hand avble_limit

     FROM itemfile WHERE p_category=''spares'';

SQL > SELECT itemdesc, itemrate*(max_level - qty_hand)

      FROM itemfile

      WHERE p_category=''spares'';

 

 

比較操作符

比較操作符用於比較兩個表達式的值

比較操作符包括 =!=<><=>=BETWEEN…ANDINLIKE IS NULL

例:

SQL> SELECT itemdesc, re_level

     FROM  itemfile

     WHERE qty_hand < max_level/2;

SQL> SELECT orderno FROM order_master

     WHERE del_date IN (‘06-1月-05’,‘05-2月-05'');

SQL> SELECT vencode,venname,tel_no

     FROM vendor_master

     WHERE venname LIKE ''j___s'';

 

 

邏輯操作符

邏輯操作符用於組合多個計較運算的結果以生成一個或真或假的結果。

邏輯操作符包括與(AND)、或(OR)和非(NOT)

例:

SQL> SELECT * FROM order_master

     WHERE odate > ‘10-5月-05''

     AND del_date < ‘26-5月-05’

 

 

顯示:2005-5

10 2005-5-26的訂單信息

 

集合操作符

集合操作符將兩個查詢的結果組合成一個結果

如圖集合操作符主要包括:

SQL> SELECT orderno FROM order_master

     MINUS

     SELECT orderno FROM order_detail;

MINUS 操作符返回從第一個查詢結果中排除第二個查

詢中出現的行。

 

鏈接操作符

連接操作符用於將多個字符串或數據值合並成一個字符串

例:通過使用連接操作符可以將表中

SQL> SELECT (venname|| '' 的地址是 ''

     ||venadd1||'' ''||venadd2 ||'' ''||venadd3) address

     FROM vendor_master WHERE vencode=''V001'';

 

的多個列合並成邏輯上的一行列

操作符的優先級

SQL 操作符的優先級從高到低的順序是:

算術操作符           --------最高優先級

連接操作符

比較操作符

NOT 邏輯操作符

AND 邏輯操作符

OR   邏輯操作符   --------最低優先級

 

 

 

函數

Oracle 提供一系列用於執行特定操作的函數

SQL 函數帶有一個或多個參數並返回一個值

以下是SQL函數的分類:

單行函數分類

單行函數分為日期函數,字符函數,數字函數,轉換函數和其他函數。

日期函數

日期函數對日期值進行運算,並生成日期數據類型或數值類型的結果

日期函數包括

ADD_MONTHS

MONTHS_BETWEENLAST_DAY

ROUND

NEXT_DAY

TRUNC

EXTRACT

日期函數舉例源代碼:

SQL> ----日期函數

SQL> select sysdate from dual;

 

SYSDATE                                                                        

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

21-2月 -08                                                                     

 

SQL> select current_date from dual;

 

CURRENT_DATE                                                                   

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

21-2月 -08                                                                     

 

SQL> select systimestamp from dual;

 

SYSTIMESTAMP                                                                   

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

21-2月 -08 02.48.43.734000 下午 +08:00                                         

 

SQL> ----兩個日期的差

SQL> select (sysdate-to_date(''2005-2-21'',''yyyy-mm-dd''))cha from dual;

 

       CHA        &nbsp;                                                             

----------                                                                     

1095.61819                                                                     

 

SQL> ----增加月份

SQL> select add_months(sysdate,5) s from dual;

 

S                                                                              

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

21-7月 -08                                                                     

 

SQL> ---較少月份

SQL> select add_months(sysdate,-1)s from dual;

 

S                                                                              

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

21-1月 -08                                                                      

 

SQL> select add_months(sysdate,-12)s from dual;

 

S                                                                              

--------------      &nbsp;                                                          

21-2月 -07                                                                     

 

SQL> ---least,greatest

SQL> ---least 選擇日期列表裡最前面的日期

SQL> select least(''1-5月-2007'',''1-8月-2007'') l from dual;

 

L                                                                               

----------                                                                     

1-5月-2007                                                                     

 

SQL> select least(2-5月-2007'',''1-8月-2007'') l from dual;

ERROR:

ORA-01756: 引號內的字符串沒有正確結束

 

 

SQL> select least(''2-5月-2007'',''1-8月-2007'') l from dual;

 

L                                                                              

----------                                                                     

1-8月-2007                                                                      

 

SQL> select least(to_date(''2007-5-2'',''yyyy-mm-dd''),to_date(''2007-8-1'',''yyyy-mm-dd''))l from dual;

 

L                                                 &nbsp;                            

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

02-5月 -07                                                                     

 

SQL> -----last_day

SQL> select last_day(sysdate) l from dual;

 

L                                                                              

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

29-2月 -08                                                                     

 

SQL> -----next_day

SQL> select next_day(sysdate,''星期四'')l from dual;

 

L                                                                               

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

28-2月 -08                                                                     

 

SQL> ----months_between

SQL> select months_between(sysdate,to_date(''2008-8-8'',''yyyy-mm-dd''))l from dual;

 

         L                                                                     

----------  &nbsp;                                                                  

-5.5602681                                                                      

 

SQL> ----round

SQL> ----to_date,to_char

SQL> select to_char(sysdate,''yyyy-mm-dd hh:mm:ss'') ch from dual;

 

CH                                                                             

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

2008-02-21 03:02:23                                                            

 

SQL> ---extract

SQL> select extract(month from sysdate)m from dual;

 

         M                                                                      

----------                                                                     

         2                                                                     

 

SQL> select extract(year from sysdate)m from dual;

 

         M                                                                     

----------                                                

      2008                                                                      

 

SQL> select extract(day from sysdate)m from dual;

 

         M                                                                     

----------                                                                     

        21         

 

字符函數 1

字符函數接受字符輸入並返回字符或數值!

如下所示:(部分)

示例代碼:

SQL> -----字符函數

SQL> ----連接符||

SQL> select ''hello''||''world'' str from dual;

 

STR                                                                            

----------                                                                     

helloworld                                                                     

 

SQL> ----rpad和lpad   左右填充

SQL> select rpad(''Hello'',15'',*'') r from dual;

select rpad(''Hello'',15'',*'') r from dual

                      *

 

 

SQL> select rpad(''Hello'',15,''*'') r from dual;

 

R       

;                                               

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

Hello**********                                                                

 

SQL> select lpad(''Hello'',15,''*'') r from dual;

 

R                                                                              

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

**********Hello                                                                

 

SQL> ----ltrim,rtrim,trim   去除空格

SQL> select ltrim(''   zhang    '') name from dual;

 

NAME                                                                            

-----------                                                                    

shangyt                                                                        

 

SQL> select rtrim('' zh a ng   '') name from dual;

 

NAME                                                                            

--------                                  ;                                     

 zh a ng                                                                       

 

SQL> select trim(''   z  h ang   '') name from dual;

 

NAME                                                                           

--------                                                                       

z  h ang                                                                       

 

SQL> -----lower,upper,initcap  大小寫轉化

SQL> select lower(''ADSFADSF'')   s from dual;

 

S                                                                              

--------                                                                       

adsfadsf                                                                        

 

SQL> select upper(''zhangxu'') s from dual;

 

S                                                                              

-------                                                                         

ZHANGXU                &nbsp;                                                      

 

SQL> select initcap(''zhang'') s from dual;

 

S                                                                              

-----                                                                           

Zhang                                                                          

 

SQL> -----length  長度

SQL> select length(''zhang'') length from dual;

 

    LENGTH                                                                      

----------                                                                     

         5                                                 

 

SQL> ---substr  鏈接

SQL> select substr(''hello world'',2,4) str from dual;

 

STR                                                                            

----                                                                           

ello                                                                           

 SQL> ----instr

SQL> select instr(''hello'',''o'') instr from dual;

 

     INSTR                                                                     

----------                                                                     

         5 

 

字符函數 2

以下是一些其它的字符函數:

CHRASCII

LPADRPAD

TRIM

LENGTH

DECODE

例:

SQL> SELECT LENGTH(''frances'') FROM dual;

SQL> SELECT vencode,

     DECODE(venname,''frances'',''Francis'') name

     FROM vendor_master WHERE vencode=''v001'';

 

數字函數

數字函數接受字符輸入並返回字符或數值!

如圖所示:(部分)

轉換函數

轉換函數將值從一種數據類型轉換為另一種數據類型

常用的轉換函數有:

TO_CHAR

TO_DATE

TO_NUMBER

示例:

SELECT TO_DATE(‘2005-12-06’ , ‘yyyy-mm-dd’)

FROM dual;

SELECT TO_CHAR(sysdate,''YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS'')

FROM dual; 

SELECT TO_CHAR(sysdate,''YYYY"年"fmMM"月"fmDD"日" HH24:MI:SS'')

FROM dual; 

 

其它函數

 

以下是幾個用來轉換空值的函數:

NVL

NVL2

NULLIF

示例:

SELECT itemdesc, NVL(re_level,0) FROM itemfile;

SELECT itemdesc, NVL2(re_level,re_level,max_level)

FROM itemfile;

SELECT itemdesc, NULLIF(re_level,max_level)

FROM itemfile;

 

 

 

分組函數

分組函數基於一組行來返回結果

為每一組行返回一個值

如圖:

示例:

SELECT COUNT(*) FROM itemfile;

SELECT AVG(re_level) FROM  itemfile

WHERE p_category=''AccessorIEs'';

SELECT COUNT(itemrate) FROM itemfile;

SELECT MAX(max_level) FROM  itemfile;

SELECT COUNT(DISTINCT qty_hand) FROM itemfile;

SELECT SUM(itemrate*max_level) FROM itemfile;

 

分析函數(1 

 

分析函數根據一組行來計算聚合值

用於計算完成聚集的累計排名、移動平均數等

分析函數為每組記錄返回多個行

如圖:

分析函數(2

以下三個分析函數用於計算一個行在一組有序行中的排位,序號從1開始

ROW_NUMBER 返回連續的排位,不論值是否相等

RANK 具有相等值的行排位相同,序數隨後跳躍

DENSE_RANK 具有相等值的行排位相同,序號是連續的

示例:

SELECT d.dname, e.ename, e.sal, DENSE_RANK()

  OVER (PARTITION BY e.deptno ORDER BY e.sal DESC)

  AS DENRANK

FROM emp e, dept d WHERE e.deptno = d.deptno;

 

GROUP BYHAVING子句

GROUP BY子句

用於將信息劃分為更小的組

每一組行返回針對該組的單個結果

HAVING子句

用於指定 GROUP BY 子句檢索行的條件

示例:

SELECT p_category, MAX(itemrate) FROM itemfile GROUP BY p_category;

SELECT p_category, MAX(itemrate) FROM itemfile

GROUP BY p_category

HAVING p_category NOT IN (''AccessorIEs'');

 

 未完…待續… 

  

 

 &nbsp;      

                                                             

;

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved