單表查詢及函數(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子子句句Select子子句句Order By子句子句SELECTSE

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

3、以指定多列，各列之間用逗號(hào)分開，按列名順序確定優(yōu)先級(jí)；n ASC：升序（默認(rèn)）；n DESC：降序。 Select Riskcode,Kindcode From Prpcitemkind Order By Riskcode ,Kindcodeselect t.uuid as 編號(hào)編號(hào), t.username 名稱名稱, t.addtimefrom tb_user_info torder by t.addtime descSELECTSELECT 字段清單FROMFROM 表名WHEREWHERE 查詢條件ORDER BY ORDER BY 排序表達(dá)式n 查詢條件可以是單條件，也可以是組合條件；

4、n 構(gòu)成查詢條件時(shí)要使用運(yùn)算符。n查詢條件中字符串和日期要用單引號(hào)擴(kuò)起來；n字符串是大小寫敏感的；日期值是格式敏感的，缺省的日期格式是 DD-MON-RR Select Riskcode,Kindcode From Prpcitemkind Where Riskcode=2701 Order By Riskcode ,Kindcodeselect t.lotteryid 彩種, t.lotteryissue 期號(hào), from tb_lottery_info twhere t.starttimestampto_date(2012-03-06,yyyy-mm-dd)order by t.lotte

5、ryid asc, t.lotteryissue desc運(yùn)算符運(yùn)算符說明說明=等于大于=大于等于=小于等于或!=不等于!不大于 Select *From PrpcfeeWhere Currecy1=CNYSelect *From PrpcfeeWhere Currecy1=Currencyselect t.unionorderid 方案號(hào)from tb_lotteryschemeinfo twhere t.bonusvalue10000select t.unionorderid 方案號(hào)from tb_lotteryschemeinfo twhere t.bonusvaluet.schemev

6、alue在兩值（包含）間進(jìn)行比較篩選，例：select t.unionorderid 方案號(hào)from tb_lotteryschemeinfo twhere t.bonusvalue/ 100 between 500 and 50000select t.unionorderid 方案號(hào) from tb_lotteryschemeinfo t where t.bonusvalue / 100 = 500 and t.bonusvalue / 100 5000 and t.bonusvalue / 100 5000表示“與”的關(guān)系，即AND連接的條件表達(dá)式均為“True”時(shí)，Where子句的查詢結(jié)

7、果才為“True”select t.lotterylevel2id, t.lotteryvalue / 100, t.bonusvalue / 100 from tb_lottery_orderdetails twhere t.lotteryvalue / 100 5000 or t.bonusvalue / 100 5000表示“或”的關(guān)系，即OR連接的條件表達(dá)式中只要有一個(gè)為“True”時(shí)，Where子句的查詢結(jié)果即為“True”用于對(duì)搜索條件的布爾值求反；NOT運(yùn)算符不能單獨(dú)應(yīng)用，而要與其他運(yùn)算符聯(lián)合使用 NOT BETWEENAND NOT IN NOT LIKE IS NOT NUL

8、L select t.uuid,t.username,t.email from tb_user_info twhere t.email is not null運(yùn)算符號(hào)運(yùn)算符號(hào)說明說明+加-減*乘/除%求余l(xiāng)對(duì)NUMBER型數(shù)據(jù)可以使用的算術(shù)運(yùn)算符號(hào)有（+ - * /）l對(duì)DATE型數(shù)據(jù)可以使用的算術(shù)運(yùn)算符號(hào)有（+ -）l算術(shù)表達(dá)式可用于Select子句、Where子句 select t.lotterylevel2id,t.uuid from tb_lottery_orderdetails t where (t.bonusvalue - t.lotteryvalue) / 100 500 and

9、 t.tickettime sysdate - 101算術(shù)運(yùn)算2 2比較運(yùn)算3NOT4AND5OR括號(hào)可以改變優(yōu)先級(jí) select t.lotterylevel2id,t.uuid from tb_lottery_orderdetails t where (t.bonusvalue - t.lotteryvalue) / 100 500 and t.tickettime sysdate - 10l 作用：將列或字符與其它列連結(jié)；l 相連接的列必須有相同或相近的數(shù)據(jù)類型；l 可用于Select子句、Where子句 Select Policyno 保單號(hào), Currency| |Sumpremiu

10、m 保費(fèi) From Prpcmain Where Riskcode=0101 Select 彩民 | t.username | 于 | t.wintime | 日中獎(jiǎng) | t.bonusvalue | 元 from tb_lottery_winner tWhere t.wintime = 2012-02-29SELECTSELECT 字段清單FROMFROM 表名WHEREWHERE 查詢條件Group BY Group BY 列名，列名n 既可以按照單列分組，也可以按照多列分組；n 出現(xiàn)在SELECT子句中的字段，如果不是用在組函數(shù)中，那么必須出現(xiàn)在GROUP BY子句中； Select j

11、ob,avg(sal) From emp Where Group Group By By job;SELECTSELECT 字段清單FROMFROM 表名WHEREWHERE 查詢條件Group BY Group BY 列名，列名HavingHaving 分組查詢條件n Having子句的作用是對(duì)分組結(jié)果進(jìn)行篩選，去除不滿足條件的組；n 包含在Having子句中的列必須是組列，即通過分組函數(shù)構(gòu)成的條件； Select job,avg(sal) From emp Where Group By job Having avg(sal)2000;pHaving子句與Where子句的相似之處在于，兩者都

12、定義了篩選條件。p但Having子句與Where子句不同在于，Having子句與組有關(guān)，而Where與單個(gè)行有關(guān)；LOWER（字符串）把字符串全部轉(zhuǎn)換為小寫UPPER（字符串）把字符串全部轉(zhuǎn)換為大寫LTRIM（字符串）把字符串頭部的空格去掉RTRIM（字符串）把字符串尾部的空格去掉LENGTH(字符串)返回指定字符串的長(zhǎng)度LOWER（字符串）LOWER（aBc）abcUPPER（字符串）UPPER（aBc）ABCLTRIM（字符串）LTRIM（ aBc）aBcRTRIM（字符串）RTRIM（ aBc ） aBc LENGTH(字符串)LENGTH(aBc)3SUBSTR（字符串，n，m）返回部

13、分字符串，返回的子串是從字符串左邊第n個(gè)字符起，m個(gè)字符SUBSTR（字符串，n，m）SUBSTR（abcde,2,3） bcdABS（數(shù)字表達(dá)式）返回表達(dá)式的絕對(duì)值ROUND（數(shù)字表達(dá)式，n）四舍五入到指定的小數(shù)位TRUNC（數(shù)字表達(dá)式，n）將數(shù)字表達(dá)式截?cái)嗟街付ǖ男?shù)位MOD（被除數(shù)，除數(shù)）返回相除后的余數(shù)ABS（數(shù)字表達(dá)式）ABS（-190）190ROUND（數(shù)字表達(dá)式，n）ROUND(45.926, 2)45.93TRUNC（數(shù)字表達(dá)式，n）TRUNC(45.926, 2)45.92MOD（被除數(shù)，除數(shù)）MOD(1600, 300)100SYSDATE返回系統(tǒng)當(dāng)前日期TRUNC(dat

14、e,格式)按照給出的格式要求將日期截?cái)?如：fmt=mi表示保留分,截?cái)嗝敫袷結(jié)Y（年）,MM（月）,DD（日）,HH（時(shí)）,MI（分）SYSDATESelect sysdateFrom dual2010/5/12 15:22:21TRUNC(date,格式)trunc(sysdate,hh)2010/5/12 15:00:00p 將日期型或數(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位

15、，3位，2位的數(shù)字年份YEAR顯示“年”的英文拼寫MM顯示2位的數(shù)字月份MONTH顯示實(shí)際月份的英文全拼名稱MON顯示實(shí)際月份的英文縮寫DD顯示2位的數(shù)字“天”DAY顯示實(shí)際星期的英文全拼名稱DY顯示實(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:

16、星期三TO_CHAR(2007-07-24 23:55:17, YYYY-MM-DD HH24:MI:SS AM) 2007-07-24 23:55:17 下午TO_CHAR(2007-10-24 23:55:17, DD of MONTH) 24 of OCTOBERTO_CHAR(2007-07-14 23:55:17, ddspth) fourteenthp 函數(shù)中的指定格式函數(shù)中的指定格式轉(zhuǎn)換后的顯示轉(zhuǎn)換后的顯示9對(duì)應(yīng)顯示一個(gè)實(shí)際的數(shù)字字符0對(duì)應(yīng)強(qiáng)制顯示0字符$顯示“$”符號(hào)L顯示本地的貨幣符.顯示小數(shù)點(diǎn)，顯示千分位符數(shù)字格式定義p 函數(shù)函數(shù)轉(zhuǎn)換后顯示轉(zhuǎn)換后顯示TO_CHAR(800.

17、00, $99,999.99） $800.00TO_CHAR(800.00, L00,000.00) RMB00,800.00p 將字符型的表達(dá)式，按指定格式轉(zhuǎn)換為數(shù)字（“格式”同TO_CHAR函數(shù)的數(shù)字格式定義）； TO_NUMBER($123,345.99,$999,999.00) 轉(zhuǎn)換后的結(jié)果：123345.99p 將字符型的表達(dá)式，按指定格式轉(zhuǎn)換為日期（“格式”同TO_CHAR函數(shù)的日期及時(shí)間格式定義）； TO_DATE(1998-8-23,YYYY-MM-DD) 轉(zhuǎn)換后的結(jié)果：1998-8-23NVL (表達(dá)式，指定值)將空值轉(zhuǎn)換為指定的值；表達(dá)式可以是日期，字符，數(shù)字；“表達(dá)式”與

18、“指定值”數(shù)據(jù)類型必須匹配 NVL2 (表達(dá)式，值1，值2)若“表達(dá)式”不為Null，則返回“值1”，空返回“值2”Select a.policyno, quotationno, NVL(quotationno,直接出單), a.* From Gupolicymain aSelect Policyno 保單號(hào), （Jfeeflag，1，見費(fèi)出單) 標(biāo)志From Prpcmain查詢結(jié)果：保單號(hào) 標(biāo)志301012009110000000001 301012009120000000005 見費(fèi)出單 DECODE（表達(dá)式, 判斷值, 結(jié)果值 , 判斷值, 結(jié)果值,）Select Policyno,

19、Decode(riskcode,0101,財(cái)產(chǎn)基本險(xiǎn))From GupolicyriskSelect Policyno 保單號(hào), (Jfeeflag=1 見費(fèi)出單) 標(biāo)志From Prpcmain CASECASEWHENWHEN 條件表達(dá)式 THENTHEN 表達(dá)式返回“True”時(shí)的結(jié)果ELSEELSE 表達(dá)式返回“False”時(shí)的結(jié)果ENDENDSelect Policyno, (CASE WHEN Riskcode=0101 THEN 財(cái)產(chǎn)基本險(xiǎn) END)From GupolicyriskEMPLOYEES440095003500640010033 DEPTNO SAL- - 10 2

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

21、0 20 3000 20 2975 30 1600員工表中工資的最高值員工表中工資的最高值 MAX(SAL)- 5000函數(shù)名稱函數(shù)名稱函數(shù)功能函數(shù)功能說明說明SUM（）返回一組數(shù)據(jù)的總和針對(duì)數(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=0101Select SUM (Sumnetpremium)From GupolicyriskWhere Riskcode = 0101說明：1.只適用于數(shù)值類型的數(shù)據(jù)2.DISTINCT為可選項(xiàng)3.如果指定列中含有NULL值，AVG函數(shù)會(huì)忽略該值Select AVGAVG（Sumpremium） 平均保費(fèi)From Prpcmain Where Riskcode=0101Select AVG (Sumnetpremium)From GupolicyriskWhere Riskcode = 0101