單表查詢及函數(shù)

1、單表查詢及函數(shù) 查詢查詢語句語句 整體整體結(jié)構(gòu)結(jié)構(gòu) 關(guān)于關(guān)于SELECT 子句子句 關(guān)于關(guān)于ORDER BY 子句子句 關(guān)于關(guān)于WHERE子句子句 運(yùn)算符運(yùn)算符 表達(dá)式表達(dá)式 關(guān)于關(guān)于GROUP BY 子句子句 關(guān)于關(guān)于HAVING 子句子句 SELECTSELECT 字段清單 FROMFROM 表名 【W(wǎng)HEREWHERE 查詢條件】 【GTOUP BYGTOUP BY 分組表達(dá)式】 【HAVINGHAVING 分組查詢條件】 【ORDER BY ORDER BY 排序表達(dá)式】 可選子句 From子子 句句 Where子子 句句 Group By 子句子句 Having子子 句句 Selec

2、t子子 句句 Order By 子句子句 SELECTSELECT 字段清單 FROMFROM 表名 n 指定多列時(shí)，各列之間用逗 號分開； n 指定表中所有列時(shí)，可用*實(shí) 現(xiàn)； n AS：在列名后使用此關(guān)鍵字 來指定列的別名； n Distinct：在字段清單中使 用此關(guān)鍵字，可去除查詢結(jié)果中 的重復(fù)信息。 Select Riskcode 險(xiǎn) 種,Kindcode From Prpcitemkind select t.uuid as 編號編號,t.username 名稱名稱 from tb_user_info t SELECTSELECT 字段清單 FROMFROM 表名 ORDER BY

3、ORDER BY 排序表達(dá)式 n Order By子句要放在 所有子句的最后； n 排序表達(dá)式可以指定 多列，各列之間用逗號分 開，按列名順序確定優(yōu)先 級； n ASC：升序（默認(rèn)）； n DESC：降序。 Select Riskcode,Kindcode From Prpcitemkind Order By Riskcode ,Kindcode select t.uuid as 編號編號, t.username 名稱名稱, t.addtime from tb_user_info t order by t.addtime desc SELECTSELECT 字段清單 FROMFROM 表名 W

4、HEREWHERE 查詢條件 ORDER BY ORDER BY 排序表達(dá)式 n 查詢條件可以是單條件，也 可以是組合條件； n 構(gòu)成查詢條件時(shí)要使用運(yùn)算 符。 n查詢條件中字符串和日期要用 單引號擴(kuò)起來； n字符串是大小寫敏感的；日期 值是格式敏感的，缺省的日期 格式是 DD-MON-RR Select Riskcode,Kindcode From Prpcitemkind Where Riskcode=2701 Order By Riskcode ,Kindcode select t.lotteryid 彩種, t.lotteryissue 期號, from tb_lottery_info

5、 t where t.starttimestampto_date(2012-03-06,yyyy-mm-dd) order by t.lotteryid asc, t.lotteryissue desc 運(yùn)算符運(yùn)算符說明說明 =等于 大于 =大于等于 =小于等于 或!=不等于 !不大于 Select * From Prpcfee Where Currecy1=CNY Select * From Prpcfee Where Currecy1=Currency select t.unionorderid 方案號 from tb_lotteryschemeinfo t where t.bonusva

6、lue10000 select t.unionorderid 方案號 from tb_lotteryschemeinfo t where t.bonusvaluet.schemevalue 在兩值（包含）間進(jìn)行比較篩選，例： select t.unionorderid 方案號 from tb_lotteryschemeinfo t where t.bonusvalue/ 100 between 500 and 50000 select t.unionorderid 方案號 from tb_lotteryschemeinfo t where t.bonusvalue / 100 = 500 an

7、d t.bonusvalue / 100 5000 and t.bonusvalue / 100 5000 表示“與”的關(guān)系，即AND連接的條件表 達(dá)式均為“True”時(shí)，Where子句的查詢 結(jié)果才為“True” select t.lotterylevel2id, t.lotteryvalue / 100, t.bonusvalue / 100 from tb_lottery_orderdetails t where t.lotteryvalue / 100 5000 or t.bonusvalue / 100 5000 表示“或”的關(guān)系，即OR連接的條件表 達(dá)式中只要有一個(gè)為“True”時(shí)

8、，Where 子句的查詢結(jié)果即為“True” 用于對搜索條件的布爾值求反； NOT運(yùn)算符不能單獨(dú)應(yīng)用，而要與其他 運(yùn)算符聯(lián)合使用 NOT BETWEENAND NOT IN NOT LIKE IS NOT NULL select t.uuid,t.username,t.email from tb_user_info t where t.email is not null 運(yùn)算符號運(yùn)算符號說明說明 +加 -減 *乘 /除 %求余 l對NUMBER型數(shù)據(jù)可以使 用的算術(shù)運(yùn)算符號有（+ - * /） l對DATE型數(shù)據(jù)可以使用 的算術(shù)運(yùn)算符號有（+ -） l算術(shù)表達(dá)式可用于 Select子句、Whe

9、re子句 select t.lotterylevel2id,t.uuid from tb_lottery_orderdetails t where (t.bonusvalue - t.lotteryvalue) / 100 500 and t.tickettime sysdate - 10 1算術(shù)運(yùn)算 2 2比較運(yùn)算 3NOT 4AND 5OR 括號可以改變優(yōu)先級 select t.lotterylevel2id,t.uuid from tb_lottery_orderdetails t where (t.bonusvalue - t.lotteryvalue) / 100 500 and t

10、.tickettime sysdate - 10 l 作用：將列或字符與其它列連結(jié)； l 相連接的列必須有相同或相近的數(shù)據(jù)類型； l 可用于Select子句、Where子句 Select Policyno 保單號, Currency| |Sumpremium 保費(fèi) From Prpcmain Where Riskcode=0101 Select 彩民 | t.username | 于 | t.wintime | 日中獎(jiǎng) | t.bonusvalue | 元 from tb_lottery_winner t Where t.wintime = 2012-02-29 SELECTSELECT 字段

11、清單 FROMFROM 表名 WHEREWHERE 查詢條件 Group BY Group BY 列名，列名 n 既可以按照單列分組， 也可以按照多列分組； n 出現(xiàn)在SELECT子句中的 字段，如果不是用在組函數(shù) 中，那么必須出現(xiàn)在GROUP BY子句中； Select job,avg(sal) From emp Where Group Group By By job; SELECTSELECT 字段清單 FROMFROM 表名 WHEREWHERE 查詢條件 Group BY Group BY 列名，列名 HavingHaving 分組查詢條 件 n Having子句的作用是對 分組結(jié)果進(jìn)

12、行篩選，去除不 滿足條件的組； n 包含在Having子句中的 列必須是組列，即通過分組 函數(shù)構(gòu)成的條件； Select job,avg(sal) From emp Where Group By job Having avg(sal)2000; pHaving子句與Where子句的相似之處在于，兩者都 定義了篩選條件。 p但Having子句與Where子句不同在于，Having子句 與組有關(guān)，而Where與單個(gè)行有關(guān)； LOWER（字符串）把字符串全部轉(zhuǎn)換為小寫 UPPER（字符串）把字符串全部轉(zhuǎn)換為大寫 LTRIM（字符串）把字符串頭部的空格去掉 RTRIM（字符串）把字符串尾部的空格去掉

13、LENGTH(字符串)返回指定字符串的長度 LOWER（字符串）LOWER（aBc）abc UPPER（字符串）UPPER（aBc）ABC LTRIM（字符串）LTRIM（ aBc）aBc RTRIM（字符串）RTRIM（ aBc ） aBc LENGTH(字符串)LENGTH(aBc)3 SUBSTR（字符串，n，m） 返回部分字符串，返回的 子串是從字符串左邊第n個(gè) 字符起，m個(gè)字符 SUBSTR（字符串，n，m）SUBSTR（abcde,2,3） bcd ABS（數(shù)字表達(dá)式）返回表達(dá)式的絕對值 ROUND（數(shù)字表達(dá)式，n）四舍五入到指定的小數(shù)位 TRUNC（數(shù)字表達(dá)式，n）將數(shù)字表達(dá)式截

14、斷到指定的小數(shù)位 MOD（被除數(shù)，除數(shù)）返回相除后的余數(shù) ABS（數(shù)字表達(dá)式）ABS（-190）190 ROUND（數(shù)字表達(dá)式，n）ROUND(45.926, 2)45.93 TRUNC（數(shù)字表達(dá)式，n）TRUNC(45.926, 2)45.92 MOD（被除數(shù)，除數(shù)）MOD(1600, 300)100 SYSDATE返回系統(tǒng)當(dāng)前日期 TRUNC(date,格式) 按照給出的格式要求將日期截?cái)? 如：fmt=mi表示保留分,截?cái)嗝?格式Y(jié)Y（年）,MM（月）,DD （日）,HH（時(shí)）,MI（分） SYSDATE Select sysdate From dual 2010/5/12 15:22:

15、21 TRUNC(date,格式) trunc(sysdate,h h) 2010/5/12 15:00:00 p 將日期型或數(shù)值型的表達(dá)式，按指定格式轉(zhuǎn)換為字 符型； 在轉(zhuǎn)換日期為字符串的時(shí)候，日期中的年，月， 日，時(shí)，分，秒都需要按相應(yīng)的“格式”來控制轉(zhuǎn) 換后的顯示格式。 在轉(zhuǎn)換數(shù)值為字符串的時(shí)候，數(shù)值也要按相應(yīng)的 “格式”來控制轉(zhuǎn)換后的顯示格式。 p YYYY,YYY,YY顯示4位，3位，2位的數(shù)字年份 YEAR顯示“年”的英文拼寫 MM顯示2位的數(shù)字月份 MONTH顯示實(shí)際月份的英文全拼名稱 MON顯示實(shí)際月份的英文縮寫 DD顯示2位的數(shù)字“天” DAY顯示實(shí)際星期的英文全拼名稱 DY

16、顯示實(shí)際星期的英文縮寫 年、月、日的格式定義 p AM顯示為上午或下午 HH24,HH12顯示24小時(shí)制的“時(shí)”或12小時(shí)制的“時(shí)” MI顯示分鐘數(shù)字 SS顯示秒的數(shù)字 SP顯示數(shù)字的英文拼寫 TH顯示數(shù)字的在英文中序數(shù)詞的形式 “特殊字符”顯示在日期時(shí)間中加入的特殊字符 時(shí)、分、秒的格式定義 p 函數(shù)函數(shù)轉(zhuǎn)換后的顯示轉(zhuǎn)換后的顯示 TO_CHAR(1980-12-17,YYYY-MM- DD:DAY) 1980-12-17:星期三 TO_CHAR(2007-07-24 23:55:17, YYYY-MM-DD HH24:MI:SS AM) 2007-07-24 23:55:17 下午 TO_C

17、HAR(2007-10-24 23:55:17, DD of MONTH) 24 of OCTOBER TO_CHAR(2007-07-14 23:55:17, ddspth) fourteenth p 函數(shù)中的指定格式函數(shù)中的指定格式轉(zhuǎn)換后的顯示轉(zhuǎn)換后的顯示 9對應(yīng)顯示一個(gè)實(shí)際的數(shù)字字符 0對應(yīng)強(qiáng)制顯示0字符 $顯示“$”符號 L顯示本地的貨幣符 .顯示小數(shù)點(diǎn) ，顯示千分位符 數(shù)字格式定義 p 函數(shù)函數(shù)轉(zhuǎn)換后顯示轉(zhuǎn)換后顯示 TO_CHAR(800.00, $99,999.99） $800.00 TO_CHAR(800.00, L00,000.00) RMB00,800.00 p 將字符型的表

18、達(dá)式，按指定格式轉(zhuǎn)換為數(shù)字（“格 式”同TO_CHAR函數(shù)的數(shù)字格式定義）； TO_NUMBER($123,345.99,$999,999.00) 轉(zhuǎn)換后的結(jié)果：123345.99 p 將字符型的表達(dá)式，按指定格式轉(zhuǎn)換為日期（“格 式”同TO_CHAR函數(shù)的日期及時(shí)間格式定義）； TO_DATE(1998-8-23,YYYY-MM-DD) 轉(zhuǎn)換后的結(jié)果：1998-8-23 NVL (表達(dá)式，指定值) 將空值轉(zhuǎn)換為指定的值； 表達(dá)式可以是日期，字符，數(shù)字； “表達(dá)式”與“指定值”數(shù)據(jù)類型 必須匹配 NVL2 (表達(dá)式，值1，值2) 若“表達(dá)式”不為Null，則返回 “值1”，空返回“值2” Se

19、lect a.policyno, quotationno, NVL(quotationno,直接出單), a.* From Gupolicymain a Select Policyno 保單號, （Jfeeflag，1，見費(fèi)出單) 標(biāo)志From Prpcmain 查詢結(jié)果： 保單號 標(biāo) 志 301012009110000000001 301012009120000000005 見費(fèi)出單 DECODE（表達(dá)式, 判斷值, 結(jié)果值 , 判斷值, 結(jié)果 值,） Select Policyno, Decode(riskcode,0101,財(cái)產(chǎn)基本險(xiǎn)) From Gupolicyrisk Select

20、Policyno 保單號, ( Jfeeflag=1 見費(fèi)出單) 標(biāo)志 From Prpcmain CASECASE WHENWHEN 條件表達(dá)式 THENTHEN 表達(dá)式返回“True”時(shí)的結(jié)果 ELSEELSE 表達(dá)式返回“False”時(shí)的結(jié)果 ENDEND Select Policyno, (CASE WHEN Riskcode=0101 THEN 財(cái)產(chǎn)基本險(xiǎn) END) From Gupolicyrisk EMPLOYEES 4400 9500 3500 6400 10033 DEPTNO SAL - - 10 2450 10 5000 10 1300 20 800 20 1100 20

21、 3000 20 3000 20 2975 30 1600 員工表中工資的最高值員工表中工資的最高值 MAX(SAL) - 5000 分組函數(shù)也叫多行函數(shù)。分組函數(shù)對多行輸入值進(jìn)行 計(jì)算，得到多行對應(yīng)的單行結(jié)果。在通常的情況下， 會(huì)對一個(gè)表總的數(shù)據(jù)進(jìn)行分組，得到多組數(shù)據(jù)，每個(gè) 組中包括一行或多行數(shù)據(jù)。分組函數(shù)對每組記錄分別 進(jìn)行計(jì)算，每組返回一個(gè)結(jié)果。最終產(chǎn)生多個(gè)組對應(yīng) 的數(shù)據(jù)集合。分組有多少，最終的結(jié)果集就有多少行。 DEPTNO SAL - - 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 員工

22、表中工資的最高值員工表中工資的最高值 MAX(SAL) - 5000 函數(shù)名稱函數(shù)名稱函數(shù)功能函數(shù)功能說明說明 SUM（）返回一組數(shù)據(jù)的總和 針對數(shù)值類型 的數(shù)據(jù) AVG（）返回一組數(shù)據(jù)的平均值 MAX（）返回一組數(shù)據(jù)的最大值 適用于任何數(shù) 據(jù)類型的數(shù)據(jù) MIN（）返回一組數(shù)據(jù)的最小值 COUNT（）返回一組數(shù)據(jù)中的記錄數(shù) l分組函數(shù)適用于Select子句，Having子句 說明： 1.只適用于數(shù)值類型的數(shù)據(jù) 2.DISTINCT為可選項(xiàng) 3.如果指定列中含有NULL值，SUM函數(shù)會(huì)忽 略該值 Select SUMSUM（Sumpremium） 總保費(fèi) From Prpcmain Where Riskcode=0101 Select SUM (Sumnetpremium) From Gupolicyrisk Where Riskcode = 0101 說明： 1.只適用于數(shù)值類型的數(shù)據(jù) 2.DISTINCT為可選項(xiàng) 3.如果指定列中含有NULL值，AVG函數(shù)會(huì)忽 略該值 Select AVGAVG（Sumpremium） 平均保費(fèi) From Prpcmain Where Riskcode=0101 Select AVG (Sumnetpremium) From Gupolicyrisk Where Riskcode = 0101 說