單表查詢及函數(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子句子句 運算符運算符 表達式表達式關(guān)于關(guān)于GROUP BY子句子句關(guān)于關(guān)于HAVING子句子句 SELECTSELECT 字段清單 FROMFROM 表名【W(wǎng)HEREWHERE 查詢條件】【GTOUP BYGTOUP BY 分組表達式】【HAVINGHAVING 分組查詢條件】【ORDER BY ORDER BY 排序表達式】可選子句From子子句句Where子子句句Group By子句子句Having子子句句Select子子句句Order By子句子句SELECTSE

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

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

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

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

6、alue在兩值（包含）間進行比較篩選，例：select t.unionorderid 方案號from tb_lotteryschemeinfo twhere t.bonusvalue/ 100 between 500 and 50000select t.unionorderid 方案號 from tb_lotteryschemeinfo t where t.bonusvalue / 100 = 500 and t.bonusvalue / 100 5000 and t.bonusvalue / 100 5000表示“與”的關(guān)系，即AND連接的條件表達式均為“True”時，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連接的條件表達式中只要有一個為“True”時，Where子句的查詢結(jié)果即為“True”用于對搜索條件的布爾值求反；NOT運算符不能單獨應(yīng)用，而要與其他運算符聯(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運算符號運算符號說明說明+加-減*乘/除%求余l(xiāng)對NUMBER型數(shù)據(jù)可以使用的算術(shù)運算符號有（+ - * /）l對DATE型數(shù)據(jù)可以使用的算術(shù)運算符號有（+ -）l算術(shù)表達式可用于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ù)運算2 2比較運算3NOT4AND5OR括號可以改變優(yōu)先級 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 保單號, Currency| |Sumpremiu

10、m 保費 From Prpcmain Where Riskcode=0101 Select 彩民 | t.username | 于 | t.wintime | 日中獎 | 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子句的作用是對分組結(jié)果進行篩選，去除不滿足條件的組；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與單個行有關(guān)；LOWER（字符串）把字符串全部轉(zhuǎn)換為小寫UPPER（字符串）把字符串全部轉(zhuǎn)換為大寫LTRIM（字符串）把字符串頭部的空格去掉RTRIM（字符串）把字符串尾部的空格去掉LENGTH(字符串)返回指定字符串的長度LOWER（字符串）LOWER（aBc）abcUPPER（字符串）UPPER（aBc）ABCLTRIM（字符串）LTRIM（ aBc）aBcRTRIM（字符串）RTRIM（ aBc ） aBc LENGTH(字符串)LENGTH(aBc)3SUBSTR（字符串，n，m）返回部

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

14、e,格式)按照給出的格式要求將日期截斷,如：fmt=mi表示保留分,截斷秒格式Y(jié)Y（年）,MM（月）,DD（日）,HH（時）,MI（分）SYSDATESelect sysdateFrom dual2010/5/12 15:22:21TRUNC(date,格式)trunc(sysdate,hh)2010/5/12 15:00:00p 將日期型或數(shù)值型的表達式，按指定格式轉(zhuǎn)換為字 符型； 在轉(zhuǎn)換日期為字符串的時候，日期中的年，月，日，時，分，秒都需要按相應(yīng)的“格式”來控制轉(zhuǎn)換后的顯示格式。 在轉(zhuǎn)換數(shù)值為字符串的時候，數(shù)值也要按相應(yīng)的“格式”來控制轉(zhuǎn)換后的顯示格式。p YYYY,YYY,YY顯示4位

15、，3位，2位的數(shù)字年份YEAR顯示“年”的英文拼寫MM顯示2位的數(shù)字月份MONTH顯示實際月份的英文全拼名稱MON顯示實際月份的英文縮寫DD顯示2位的數(shù)字“天”DAY顯示實際星期的英文全拼名稱DY顯示實際星期的英文縮寫年、月、日的格式定義p AM顯示為上午或下午HH24,HH12顯示24小時制的“時”或12小時制的“時”MI顯示分鐘數(shù)字SS顯示秒的數(shù)字SP顯示數(shù)字的英文拼寫TH顯示數(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對應(yīng)顯示一個實際的數(shù)字字符0對應(yīng)強制顯示0字符$顯示“$”符號L顯示本地的貨幣符.顯示小數(shù)點，顯示千分位符數(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 將字符型的表達式，按指定格式轉(zhuǎn)換為數(shù)字（“格式”同TO_CHAR函數(shù)的數(shù)字格式定義）； TO_NUMBER($123,345.99,$999,999.00) 轉(zhuǎn)換后的結(jié)果：123345.99p 將字符型的表達式，按指定格式轉(zhuǎn)換為日期（“格式”同TO_CHAR函數(shù)的日期及時間格式定義）； TO_DATE(1998-8-23,YYYY-MM-DD) 轉(zhuǎn)換后的結(jié)果：1998-8-23NVL (表達式，指定值)將空值轉(zhuǎn)換為指定的值；表達式可以是日期，字符，數(shù)字；“表達式”與

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

19、Decode(riskcode,0101,財產(chǎn)基本險)From GupolicyriskSelect Policyno 保單號, (Jfeeflag=1 見費出單) 標(biāo)志From Prpcmain CASECASEWHENWHEN 條件表達式 THENTHEN 表達式返回“True”時的結(jié)果ELSEELSE 表達式返回“False”時的結(jié)果ENDENDSelect Policyno, (CASE WHEN Riskcode=0101 THEN 財產(chǎ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ù)對多行輸入值進行計算，得到多行對應(yīng)的單行結(jié)果。在通常的情況下，會對一個表總的數(shù)據(jù)進行分組，得到多組數(shù)據(jù)，每個組中包括一行或多行數(shù)據(jù)。分組函數(shù)對每組記錄分別進行計算，每組返回一個結(jié)果。最終產(chǎn)生多個組對應(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ù)的總和針對數(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為可選項3.如果指定列中含有NULL值，SUM函數(shù)會忽略該值Select SUMSUM（Sumpremium） 總保費From Prpcmain Where Riskcode=0101Select SUM (Sumnetpremium)From GupolicyriskWhere Riskcode = 0101說明：1.只適用于數(shù)值類型的數(shù)據(jù)2.DISTINCT為可選項3.如果指定列中含有NULL值，AVG函數(shù)會忽略該值Select AVGAVG（Sumpremium） 平均保費From Prpcmain Where Riskcode=0101Select AVG (Sumnetpremium)From GupolicyriskWhere Riskcode = 0101