【我的Oracle學習筆記（二）】----- select語句補充

標籤：

一、多表查詢

　　多表查詢是指從多個有關聯的表中查詢資料，其文法與單表查詢類似。一般來說，多表查詢的表要用串連聯絡起來，如果沒串連，則查詢結果是這多個查詢表的笛卡爾積（注釋1）。

 

類比查詢僱員姓名和所在部門名稱：

　　select [僱員姓名],[部門名稱] from [僱員表] a,scott，[部門表] b where a.[部門編號]=b.[部門編號];

上例中，為每一個查詢表指定了別名，便於SQL語句的書寫。

　　

類比查詢在”sales“部門工作的僱員其僱員姓名

　　select [僱員姓名] from [僱員表] a,scott，[部門表] b where a.[部門編號]=b.[部門編號] AND b.[部門名稱]=‘sales’;

 

二、巢狀查詢

　　巢狀查詢指一個select查詢中包含一個以上的子查詢，所謂的子查詢，是指嵌套在另一個select、insert、update或delete語句中的select查詢語句。子查詢的文法與select文法類似，但有所限制，如子查詢不能含有order by和into等關鍵字。

 

類比查詢”sales“部門的僱員姓名：

　　select [僱員姓名] from [僱員表] where [部門編號] in(select [部門編號] from [部門表] where [部門名稱]=‘sales‘);

其中，in關鍵字用於where子句中用來判斷查詢的運算式是否在多個值的列表中。返回滿足in列表中的滿足條件的記錄。

 

類比查詢在僱員表中比平均工資高的僱員名單：

　　select [僱員姓名] from [僱員表] where [僱員工資] >(select AVG([僱員工資]) from [僱員表]);

上例中的AVG函數用來計算平均工資。

 

三、函數查詢

　　SELECT查詢語句可以使用函數，下面列出了SQL語句中常用的一些函數。

1）集合函數

MIN　　　　計算最小值

MAX　　　　計算最大值

AVG　　　　計算平均值

SUM　　　　求和

COUNT　　　計算合格記錄總數

 

2）數值函數

ABS(X)　　　　計算X的絕對值

MOD(X,Y)　　　計算X除以Y的餘數

CEIL(X)　　　　計算大於等於參數X的最小整數

FLOOR（X）　　計算小於等於參數X的最大整數

POWER（X，Y）計算以X為底的Y次冪

EXP（X）　　　　計算e的X次冪（e=2.71828183....）

SQRT（X）　　　計算X的平分根

LN(X)　　　　　　計算X的自然對數

ROUND（X）　　四捨五入

SIN（X）　　　　計算X的正弦值

COS（X）　　　　計算X的餘弦值

 

3）字串函數

LENGTH(S)　　　　　　　　擷取字串S的長度

CONCAT(S1,S2)　　　　　　字串串連，返回將S2添加到S1後面形成的字串

LOWER(S)　　　　　　　　將字串S全部大寫

UPPER(S)　　　　　　　　　將字串S全部小寫

SUBSTER（S,m，n）　　　　截取子串，返回S中從m位置開始，長度為n的子串

REPLACE(S1,S2,S3)　　　　將字串S1中出現的S2都替換成S3，然後返回剩餘的字串，S3預設NULL，則所有S1中的S2字元都被刪除

INSTR(S,c)　　　　　　　　　擷取字元c在字串S中首次出現的位置

 

4)日期函數

SYSDATE　　　　　　　　　　擷取日期和時間

MONTHS_BETWEEN　　　　　擷取兩個日期之間的月份間隔

ADD_MONTHS　　　　　　　　在指定日期上添加月份

NEXT_DAY　　　　　　　　　　指定日期的下一天

LAST_DAY　　　　　　　　　　本月最後一天 

 

5）類型轉換函式

TO_NUMBER　　　　轉化為數實值型別

TO_DATE　　　　　　轉化為日期類型

TO_CHAR　　　　　　轉化為字元類型

HEX_TO_RAW　　　　十六進位轉二進位

RAW_TO_HEX　　　　二進位轉十六進位

 

類比查詢僱員的總人數：

　　select COUNT(*) from [僱員表];

 

查詢2個日期之間的月份間隔：

　　select MONTHS_BETWEEN(TO_DATE(‘2010/01/01‘‘yyyy/mm/dd‘),TO_DATE(‘2011/05/20‘‘yyyy/mm/dd‘)) as "月份間隔" from dual;

dual是一個虛擬表,用來構成select的文法規則,oracle保證dual裡面永遠只有一條記錄。

（

常用日期資料格式（該段為摘抄）

 

Y或YY或YYY 年的最後一位，兩位或三位 Select to_char(sysdate,’YYY’) from dual； 002表示2002年 

SYEAR或YEAR SYEAR使公元前的年份前加一負號 Select to_char(sysdate,’SYEAR’) from dual； -1112表示公元前111 2年 

Q 季度，1～3月為第一季度 Select to_char(sysdate,’Q’) from dual； 2表示第二季度① 

MM 月份數 Select to_char(sysdate,’MM’) from dual； 12表示12月 

RM 月份的羅馬錶示 Select to_char(sysdate,’RM’) from dual； IV表示4月 

Month 用9個字元長度表示的月份名 Select to_char(sysdate,’Month’) from dual； May後跟6個空格表示5月 

WW 當年第幾周 Select to_char(sysdate,’WW’) from dual； 24表示2002年6月13日為第24周 

W 本月第幾周 Select to_char(sysdate,’W’) from dual； 2002年10月1日為第1周 

DDD 當年第幾, 1月1日為001，2月1日為032 Select to_char(sysdate,’DDD’) from dual； 363 2002年1 2月2 9日為第363天 

DD 當月第幾天 Select to_char(sysdate,’DD’) from dual； 04 10月4日為第4天 

D 周內第幾天 Select to_char(sysdate,’D’) from dual； 5 2002年3月14日為星期一 

DY 周內第幾天縮寫 Select to_char(sysdate,’DY’) from dual； SUN 2002年3月24日為星期天 

HH或HH12 12進位小時數 Select to_char(sysdate,’HH’) from dual； 02 午夜2點過8分為02 

HH24 24小時制 Select to_char(sysdate,’HH24’) from dual； 14 下午2點08分為14 

MI 分鐘數(0～59) Select to_char(sysdate,’MI’) from dual； 17下午4點17分 

SS 秒數(0～59) Select to_char(sysdate,’SS’) from dual； 22 11點3分22秒 

提示注意不要將MM格式用於分鐘(分鐘應該使用MI)。MM是用於月份的格式，將它用於分鐘也能工作，但結果是錯誤的。 

）

 

注釋1----笛卡爾積：

在數學中，兩個集合X和Y的笛卡尓積（Cartesian product），又稱直積，表示為X × Y，第一個對象是X的成員而第二個對象是Y的所有可能有序對的其中一個成員。
假設集合A={a, b}，集合B={0, 1, 2}，則兩個集合的笛卡爾積為{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。
類似的例子有，如果A表示某學校學生的集合，B表示該學校所有課程的集合，則A與B的笛卡爾積表示所有可能的選課情況。A表示所有聲母的集合，B表示所有韻母的集合，那麼A和B的笛卡爾積就為所有可能的漢字全拼。

 

【我的Oracle學習筆記（二）】----- select語句補充