單表查詢及函數(shù)

查詢SQL培訓(xùn)——單表查詢及函數(shù)培訓(xùn)要點查詢語句整體結(jié)構(gòu)關(guān)于SELECT子句關(guān)于ORDERBY子句關(guān)于WHERE子句運算符表達(dá)式關(guān)于GROUPBY子句關(guān)于HAVING子句查詢語句的整體結(jié)構(gòu)

SELECT

字段清單

FROM

表名【W(wǎng)HERE

查詢條件】

【GTOUPBY

分組表達(dá)式】【HAVING

分組查詢條件】【ORDERBY排序表達(dá)式】可選子句SELECT語句From子句Where子句GroupBy子句Having子句Select子句OrderBy子句查詢語句的執(zhí)行順序SELECT語句關(guān)于Select子句SELECT

字段清單FROM

表名指定多列時，各列之間用逗號分開；指定表中所有列時，可用*實現(xiàn)；AS：在列名后使用此關(guān)鍵字來指定列的別名；Distinct：在字段清單中使用此關(guān)鍵字，可去除查詢結(jié)果中的重復(fù)信息。SelectDistinctRiskcodeAs

險種,KindcodeFromPrpcitemkind例SELECT語句selectt.uuidas編號,t.username名稱fromtb_user_infot關(guān)于OrderBy子句SELECT

字段清單FROM

表名ORDERBY排序表達(dá)式OrderBy子句要放在所有子句的最后；排序表達(dá)式可以指定多列，各列之間用逗號分開，按列名順序確定優(yōu)先級；ASC：升序（默認(rèn)）；DESC：降序。SelectDistinctRiskcode,KindcodeFromPrpcitemkindOrderByRiskcodeDESC,Kindcode例SELECT語句selectt.uuidas編號,t.username名稱,

t.addtimefromtb_user_infotorderbyt.addtimedesc關(guān)于Where子句SELECT

字段清單FROM

表名WHERE

查詢條件ORDERBY排序表達(dá)式

查詢條件可以是單條件，也可以是組合條件；構(gòu)成查詢條件時要使用運算符。查詢條件中字符串和日期要用單引號擴(kuò)起來；字符串是大小寫敏感的；日期值是格式敏感的，缺省的日期格式是'DD-MON-RR'

SelectDistinctRiskcode,KindcodeFromPrpcitemkindWhereRiskcode=‘2701’OrderByRiskcodeDESC,Kindcode例SELECT語句selectt.lotteryid彩種,t.lotteryissue期號,

fromtb_lottery_infotwheret.starttimestamp>to_date('2012-03-06','yyyy-mm-dd')orderbyt.lotteryidasc,t.lotteryissuedesc比較運算符運算符說明=等于>大于<小于>=大于等于<=小于等于<>或!=不等于!<不小于!>不大于

列名運算符常量值Select*FromPrpcfeeWhereCurrecy1=‘CNY’列名運算符列名Select*FromPrpcfeeWhereCurrecy1=Currency例運算符selectt.unionorderid方案號fromtb_lotteryschemeinfotwhere

t.bonusvalue>10000selectt.unionorderid方案號fromtb_lotteryschemeinfotwheret.bonusvalue>t.schemevalueBETWEEN…AND…運算符在兩值（包含）間進(jìn)行比較篩選，例：selectt.unionorderid方案號fromtb_lotteryschemeinfotwheret.bonusvalue/100between500and50000selectt.unionorderid方案號fromtb_lotteryschemeinfotwheret.bonusvalue/100>=500

and

t.bonusvalue/100<=50000運算符IN運算符在指定的多個條件值中進(jìn)行比較篩選，只要滿足其中一個值即條件成立，例：selectt.lotteryid,t.descriptionfromtd_lotterytwheret.lotteryidin(214,215,216,217)selectt.lotteryid,t.descriptionfromtd_lotterytwheret.lotteryid=214ort.lotteryid=215ort.lotteryid=216ort.lotteryid=217運算符LIKE運算符

查詢條件部分匹配即可得查詢結(jié)果，實現(xiàn)模糊查詢；需與通配符一同使用；SQL提供的通配符有：“%”、“-”；%：可表示0個或多個字符；-：可表示一個字符只有CHAR、VARCHAR、TEXT類型的數(shù)據(jù)才能使用Like運算符和通配符；運算符LIKE運算符CNOCNAMECTIMESCOUNT1應(yīng)用數(shù)學(xué)基礎(chǔ)481202生物工程32803計算機軟件基礎(chǔ)32704計算機硬件基礎(chǔ)24805模擬電路設(shè)計28906機械設(shè)計實踐48687數(shù)據(jù)庫設(shè)計16808設(shè)計理論28459計算機入門24150Course運算符

Select

*

From

CourseWhere

CnameLIKE

‘%設(shè)計%’ISNULL運算符

查詢包含空值的記錄；Select*FromPrplclaimWhereendcasedateISNULL例運算符

selectt.uuid,t.realusername,t.usermobile

from

tb_user_privateinfot

where

t.cardidisnullselectt.lotterylevel2id,

t.lotteryvalue/100,

t.bonusvalue/100

fromtb_lottery_orderdetailstwhere

t.lotteryvalue/100>5000

and

t.bonusvalue/100>5000邏輯運算符AND、OR、NOT表示“與”的關(guān)系，即AND連接的條件表達(dá)式均為“True”時，Where子句的查詢結(jié)果才為“True”AND運算符selectt.lotterylevel2id,t.lotteryvalue/100,t.bonusvalue/100

fromtb_lottery_orderdetailstwheret.lotteryvalue/100>5000

ort.bonusvalue/100>5000表示“或”的關(guān)系，即OR連接的條件表達(dá)式中只要有一個為“True”時，Where子句的查詢結(jié)果即為“True”O(jiān)R運算符邏輯運算符AND、OR、NOT邏輯運算符AND、OR、NOT用于對搜索條件的布爾值求反；NOT運算符不能單獨應(yīng)用，而要與其他運算符聯(lián)合使用NOTNOTBETWEEN……AND……NOTINNOTLIKEISNOTNULL

selectt.uuid,t.username,t.email

fromtb_user_infotwheret.emailisnotnull運算符算術(shù)運算符運算符號說明+加-減*乘/除%求余對NUMBER型數(shù)據(jù)可以使用的算術(shù)運算符號有（+-*/）對DATE型數(shù)據(jù)可以使用的算術(shù)運算符號有（+-）算術(shù)表達(dá)式可用于Select子句、Where子句例表達(dá)式

select

t.lotterylevel2id,t.uuid

fromtb_lottery_orderdetailst

where

(t.bonusvalue-t.lotteryvalue)/100>500

andt.tickettime>sysdate-10運算符的優(yōu)先級1算術(shù)運算2比較運算3NOT4AND5OR括號可以改變優(yōu)先級運算符

select

t.lotterylevel2id,t.uuid

fromtb_lottery_orderdetailst

where

(t.bonusvalue-t.lotteryvalue)/100>500

andt.tickettime>sysdate-10例連接符（||）作用：將列或字符與其它列連結(jié)；相連接的列必須有相同或相近的數(shù)據(jù)類型；可用于Select子句、Where子句SelectPolicyno保單號,Currency||’’||Sumpremium保費FromPrpcmainWhereRiskcode=‘0101’例表達(dá)式

Select‘彩民’

||

t.username||

‘于’

||

t.wintime||

‘日中獎’

||

t.bonusvalue||'元'

fromtb_lottery_winnertWhere

t.wintime='2012-02-29‘關(guān)于GroupBy子句SELECT

字段清單FROM

表名WHERE

查詢條件GroupBY列名[，列名…]

既可以按照單列分組，也可以按照多列分組；出現(xiàn)在SELECT子句中的字段，如果不是用在組函數(shù)中，那么必須出現(xiàn)在GROUPBY子句中；

Selectjob,avg(sal)FromempWhereGroupByjob;例SELECT語句關(guān)于Having子句SELECT

字段清單FROM

表名WHERE

查詢條件GroupBY列名[，列名…]Having

分組查詢條件Having子句的作用是對分組結(jié)果進(jìn)行篩選，去除不滿足條件的組；

包含在Having子句中的列必須是組列，即通過分組函數(shù)構(gòu)成的條件；

Selectjob,avg(sal)Fromemp

WhereGroupByjobHavingavg(sal)>2000;例SELECT語句Having子句與Where子句Having子句與Where子句的相似之處在于，兩者都定義了篩選條件。但Having子句與Where子句不同在于，Having子句與組有關(guān)，而Where與單個行有關(guān)；SELECT語句單行函數(shù)單行函數(shù)字符函數(shù)數(shù)字函數(shù)日期函數(shù)轉(zhuǎn)換函數(shù)其他函數(shù)函數(shù)字符函數(shù)函數(shù)格式參數(shù)及功能說明LOWER（字符串）把字符串全部轉(zhuǎn)換為小寫UPPER（字符串）把字符串全部轉(zhuǎn)換為大寫LTRIM（字符串）把字符串頭部的空格去掉RTRIM（字符串）把字符串尾部的空格去掉LENGTH(字符串)返回指定字符串的長度函數(shù)格式舉例結(jié)果LOWER（字符串）LOWER（’aBc’）abcUPPER（字符串）UPPER（’aBc’）ABCLTRIM（字符串）LTRIM（’aBc’）aBcRTRIM（字符串）RTRIM（’aBc’）

aBcLENGTH(字符串)LENGTH(‘a(chǎn)Bc’)3單行函數(shù)字符函數(shù)函數(shù)格式參數(shù)及功能說明SUBSTR（字符串，n，m）返回部分字符串，返回的子串是從字符串左邊第n個字符起，m個字符函數(shù)格式舉例結(jié)果SUBSTR（字符串，n，m）SUBSTR（'abcde',2,3）bcd單行函數(shù)數(shù)字函數(shù)函數(shù)格式參數(shù)及功能說明ABS（數(shù)字表達(dá)式）返回表達(dá)式的絕對值ROUND（數(shù)字表達(dá)式，n）四舍五入到指定的小數(shù)位TRUNC（數(shù)字表達(dá)式，n）將數(shù)字表達(dá)式截斷到指定的小數(shù)位MOD（被除數(shù)，除數(shù)）返回相除后的余數(shù)函數(shù)格式舉例結(jié)果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)100單行函數(shù)日期函數(shù)函數(shù)格式參數(shù)及功能說明SYSDATE返回系統(tǒng)當(dāng)前日期TRUNC(date,格式)按照給出的格式要求將日期截斷,如：fmt=mi表示保留分,截斷秒格式—YY（年）,MM（月）,DD（日）,HH（時）,MI（分）函數(shù)格式舉例結(jié)果SYSDATESelectsysdateFromdual2010/5/1215:22:21TRUNC(date,格式)trunc(sysdate,’hh’)2010/5/1215:00:00單行函數(shù)轉(zhuǎn)換函數(shù)

TO_CHAR（表達(dá)式，‘格式’）

將日期型或數(shù)值型的表達(dá)式，按指定格式轉(zhuǎn)換為字

符型；在轉(zhuǎn)換日期為字符串的時候，日期中的年，月，日，時，分，秒都需要按相應(yīng)的“格式”來控制轉(zhuǎn)換后的顯示格式。在轉(zhuǎn)換數(shù)值為字符串的時候，數(shù)值也要按相應(yīng)的“格式”來控制轉(zhuǎn)換后的顯示格式。單行函數(shù)轉(zhuǎn)換函數(shù)

TO_CHAR（表達(dá)式，‘格式’）

函數(shù)中的指定格式轉(zhuǎn)換后的顯示YYYY,YYY,YY顯示4位，3位，2位的數(shù)字年份YEAR顯示“年”的英文拼寫MM顯示2位的數(shù)字月份MONTH顯示實際月份的英文全拼名稱MON顯示實際月份的英文縮寫DD顯示2位的數(shù)字“天”DAY顯示實際星期的英文全拼名稱DY顯示實際星期的英文縮寫年、月、日的格式定義單行函數(shù)轉(zhuǎn)換函數(shù)

TO_CHAR（表達(dá)式，‘格式’）

函數(shù)中的指定格式轉(zhuǎn)換后的顯示AM顯示為上午或下午HH24,HH12顯示24小時制的“時”或12小時制的“時”MI顯示分鐘數(shù)字SS顯示秒的數(shù)字SP顯示數(shù)字的英文拼寫TH顯示數(shù)字的在英文中序數(shù)詞的形式“特殊字符”顯示在日期時間中加入的特殊字符時、分、秒的格式定義單行函數(shù)轉(zhuǎn)換函數(shù)

TO_CHAR（表達(dá)式，‘格式’）

函數(shù)轉(zhuǎn)換后的顯示TO_CHAR(‘1980-12-17’,'YYYY-MM-DD:DAY')1980-12-17:星期三TO_CHAR(‘2007-07-2423:55:17’,'YYYY-MM-DDHH24:MI:SSAM‘)2007-07-2423:55:17下午TO_CHAR(‘2007-10-2423:55:17’,'DD"of"MONTH‘)24ofOCTOBERTO_CHAR(‘2007-07-1423:55:17’,‘ddspth‘)fourteenth例單行函數(shù)轉(zhuǎn)換函數(shù)

TO_CHAR（表達(dá)式，‘格式’）

函數(shù)中的指定格式轉(zhuǎn)換后的顯示9對應(yīng)顯示一個實際的數(shù)字字符0對應(yīng)強制顯示0字符$顯示“$”符號L顯示本地的貨幣符.顯示小數(shù)點，顯示千分位符數(shù)字格式定義單行函數(shù)轉(zhuǎn)換函數(shù)

TO_CHAR（表達(dá)式，‘格式’）

函數(shù)轉(zhuǎn)換后顯示TO_CHAR(‘800.00’,‘$99,999.99’）

$800.00TO_CHAR(‘800.00’,‘L00,000.00’)RMB00,800.00例單行函數(shù)轉(zhuǎn)換函數(shù)

TO_NUMBER（表達(dá)式，‘格式’）

將字符型的表達(dá)式，按指定格式轉(zhuǎn)換為數(shù)字（“格式”同TO_CHAR函數(shù)的數(shù)字格式定義）；

TO_NUMBER('$123,345.99','$999,999.00')

轉(zhuǎn)換后的結(jié)果：123345.99例單行函數(shù)轉(zhuǎn)換函數(shù)

TO_DATE（表達(dá)式，‘格式’）

將字符型的表達(dá)式，按指定格式轉(zhuǎn)換為日期（“格式”同TO_CHAR函數(shù)的日期及時間格式定義）；

TO_DATE('1998-8-23','YYYY-MM-DD')

轉(zhuǎn)換后的結(jié)果：1998-8-23例單行函數(shù)其它函數(shù)-空值函數(shù)函數(shù)格式參數(shù)及功能說明NVL(表達(dá)式，指定值)將空值轉(zhuǎn)換為指定的值；表達(dá)式可以是日期，字符，數(shù)字；“表達(dá)式”與“指定值”數(shù)據(jù)類型必須匹配NVL2(表達(dá)式，值1，值2)若“表達(dá)式”不為Null，則返回“值1”，空返回“值2”單行函數(shù)例Selecta.policyno,quotationno,

NVL(quotationno,‘直接出單'),a.*FromGupolicymaina其它函數(shù)-判斷函數(shù)SelectPolicyno保單號,

Decode（Jfeeflag，’1’，‘見費出單’)標(biāo)志FromPrpcmain查詢結(jié)果：保單號標(biāo)志301012009110000000001301012009120000000005見費出單

例DECODE（表達(dá)式,判斷值,結(jié)果值

[,判斷值,結(jié)果值,]……）單行函數(shù)SelectPolicyno,

Decode(riskcode,‘0101’,‘財產(chǎn)基本險')

FromGupolicyriskCASE表達(dá)式SelectPolicyno保單號,(CASEWHEN

Jfeeflag=’1’

THEN‘見費出單’)標(biāo)志FromPrpcmain例CASEWHEN

條件表達(dá)式

THEN

表達(dá)式返回“True”時的結(jié)果[ELSE

表達(dá)式返回“False”時的結(jié)果]ENDSelectPolicyno,

(CASE

WHENRiskcode='0101'THEN'財產(chǎn)基本險‘

END)FromGupolicyriskEMPLOYEES4400…95003500640010033數(shù)據(jù)分組EMPDEPTNOSAL------------------10245010500010130020800201100203000203000202975301600員工表中工資的最高值MAX(SAL)---------5000SELECT語句分組函數(shù)也叫多行函數(shù)。分組函數(shù)對多行輸入值進(jìn)行計算，得到多行對應(yīng)的單行結(jié)果。在通常的情況下，會對一個表總的數(shù)據(jù)進(jìn)行分組，得到多組數(shù)據(jù)，每個組中包括一行或多行數(shù)據(jù)。分組函數(shù)對每組記錄分別進(jìn)行計算，每組返回一個結(jié)果。最終產(chǎn)生多個組對應(yīng)的數(shù)據(jù)集合。分組有多少，最終的結(jié)果集就有多少行。EMPDEPTNOSAL------------------10245010500010130020800201100203000203000202975301600員工表中工資的最高值MAX(SAL)---------5000分組函數(shù)的概念分組函數(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ù)分組函數(shù)適用于Select子句，Having子句分組函數(shù)求和函數(shù)—SUM()格式：SUM([DISTINCT]列名)說明：只適用于數(shù)值類型的數(shù)據(jù)DISTINCT為可選項如果指定列中含有NULL值，SUM函數(shù)會忽略該值SelectSUM（Sumpremium）

總保費FromPrpcmainWhereRiskcode=‘0101’例分組函數(shù)Select

SUM(Sumnetpremium)FromGupolicyriskWhereRiskcode='0101'均值函數(shù)—AVG()格式：AVG([DISTINCT]列名)說明：只適用于數(shù)值類型的數(shù)據(jù)DISTINCT為可選項如果指定列中含有NULL值，AVG函數(shù)會忽略該值Sel