oracle-2-1基本sql語句單行、通用和多行函數(shù)偽列

SQL語言

SQL語言SQL全稱是“結(jié)構(gòu)化查詢語言(StructuredQueryLanguage)”

SQL是一種數(shù)據(jù)庫查詢和程序設(shè)計(jì)語言，用于存取數(shù)據(jù)以及查詢、更新和管理關(guān)系數(shù)據(jù)庫系統(tǒng)。SQL是專為數(shù)據(jù)庫而建立的操作命令集，是一種功能齊全的數(shù)據(jù)庫語言。隨著關(guān)系數(shù)據(jù)庫系統(tǒng)和SQL語言應(yīng)用的日益廣泛，SQL語言的標(biāo)準(zhǔn)化工作也在緊張革進(jìn)行著，十多年來已制訂了多個(gè)SQL標(biāo)準(zhǔn)；(1)1982年，美國(guó)國(guó)家標(biāo)準(zhǔn)化局（AMERICANNATIONALSTANDARDINSTITUTE，簡(jiǎn)稱ANSI）開始制定SQL標(biāo)準(zhǔn)；

(2)1986年，美國(guó)國(guó)家標(biāo)準(zhǔn)化協(xié)會(huì)公布了SQL語言的第一個(gè)標(biāo)準(zhǔn)SQL86；

(3)1987年，國(guó)際標(biāo)準(zhǔn)化組織（ISO）通過了SQL86標(biāo)準(zhǔn)；

(4)1989年，國(guó)際標(biāo)準(zhǔn)化組織（ISO）對(duì)SQL86進(jìn)行了補(bǔ)充，推出了SQL89標(biāo)準(zhǔn)；

(5)1992年，ISO又推出了SQL92標(biāo)準(zhǔn)，也稱為SQL2；

(6)SQL99（也稱為SQL3），增加了面向?qū)ο蟮墓δ?。SQL語言包含4個(gè)部分：※數(shù)據(jù)定義語言(DDL,DataDefinitionLanguage)，例如：CREATE、DROP、ALTER語句?！鶖?shù)據(jù)操作語言(DML,DataManipulationLanguage)，例如：INSERT、UPDATE、DELETE語句?！鶖?shù)據(jù)查詢語言(DQL,DataQueryLanguageSELECT)，例如：SELECT語句。※數(shù)據(jù)控制語言(DCL,DataControlLanguage)，例如：GRANT、REVOKE、COMMIT、ROLLBACK等語句。書寫SQL應(yīng)遵循以下簡(jiǎn)單原則SQL語句不區(qū)分大小寫SQL語句可以寫成一行或多行關(guān)鍵字不能簡(jiǎn)寫或分割于多行子句通常置于單獨(dú)行這樣更具可讀性并便于編輯Tab和縮進(jìn)的使用可以提高程序的可讀性關(guān)鍵字最好大寫，其他詞諸如表名、列名使用小寫Select語句Select語句功能強(qiáng)大，語法也比較復(fù)雜。完整的select語句由6個(gè)子句構(gòu)成，分別為：Select 指定要獲取表中哪些列數(shù)據(jù)From 指定數(shù)據(jù)來自哪個(gè)（些）表Where 指定獲取哪些行數(shù)據(jù)Groupby 用于對(duì)表中數(shù)據(jù)進(jìn)行分組統(tǒng)計(jì)Having 指定分組統(tǒng)計(jì)條件（跟groupby一起使用！）Orderby 指定排序字段其中select和from子句是必選項(xiàng)，在select語句中同時(shí)包含where、groupby、having、orderby等多個(gè)子句，這些子句使用時(shí)是有一定順序的，orderby必須是最后一條子句。

算術(shù)操作符邏輯操作符比較操作符連接操作符SQL操作符算術(shù)操作符加(+)、減(-)、乘(*)、除(/)(1)SELECTsal,sal*1.1,enameFROMemp;(2)SELECTcomm+200FROMemp;SQL操作符SQL操作符

邏輯操作符說明

xandyx和y全部為真

xoryx和y有一個(gè)為真

notxx的相反值

邏輯運(yùn)算 SELECT*FROMdeptWHERE1=1;SELECT*FROMdeptWHERE1=2;SELECTempno,ename,sal,jobFROMempWHEREsal>=3000ANDJOB!='MANAGER‘;SELECTename,sal,jobFROMempWHEREsal>=3000ORjob='MANAGER';

比較運(yùn)算符（示例）條件選擇 (1)SELECT*FROMempWHEREsal>=5000;(2)SELECT*FROMempWHEREjob<>'MANAGER';復(fù)合條件

(1)SELECT*FROMempWHEREsalBETWEEN3000AND6000;(2)SELECT*FROMempWHEREjobIN('MANAGER','CLERK');(3)SELECT*FROMempWHEREjobNOTIN('MANAGER');

匹配空值（NULL）SELECT*FROMempWHEREcommISNOTNULL;NULL要注意的問題：SELECT*FROMempWHEREempnoNOTIN(

SELECTmgrFROMempWHEREmgrISNOTNULL);SELECT*FROMempWHEREempnoNOTIN(SELECTmgrFROMemp);比較運(yùn)算符（示例）any的用法:

<any意味著小于最大、>any大于最小SELECT*FROMempWHEREsal=ANY(3000,1500,2000,1200);//sal等于其中任何一個(gè)SELECT*FROMempWHEREsal>ANY(3000,1500,2000,1200);SELECT*FROMempWHEREsal<ANY( SELECTsalFROMempWHEREjob='SALESMAN');all的用法:<=all：小于等于所有，即小于最小或者等于最小、>all：大于所有，即大于最大SELECT*FROMempWHEREsal>=ALL(3000,1500,2000,1200);selectempno,ename,sal,jobfromempwheresal<all( selectsalfromempwherejob='SALESMAN');模糊查詢:

_匹配1個(gè)字符、%匹配0到多個(gè)字符(1)SELECTenameFROMempWHEREenameLIKE'____H';(2)SELECTenameFROMempWHEREenameLIKE'A____';(3)SELECTenameFROMempWHEREenameLIKE'A%';(4)SELECTenameFROMempWHEREenameLIKE'%A%';比較運(yùn)算符比較操作符

說明

＝

等于

<>或!=

不等于

<

小于

> 大于

<= 小于等于

>= 大于等于

ANY 任何一個(gè)值比較

ALL 所有的值比較IN(NOTIN) 匹配值列表

BETWEEN(NOTBETWEEN) 匹配值范圍

ISNULL(ISNOTNULL) 匹配空值

LIKE(NOTLIKE) 匹配字符串SQL操作符排序查詢ORDERBYOrderby 指定排序字段SELECTename,salFROMempORDERBYsalASC;SELECTename,salFROMempORDERBYsalDESC;SELECTename,empno,sal,deptno FROMempORDERBYdeptnoASC,salDESC,empno;分組查詢Groupby用于對(duì)表中數(shù)據(jù)進(jìn)行分組統(tǒng)計(jì)Having指定分組統(tǒng)計(jì)條件（跟groupby一起使用?。㏒ELECTjobFROMempGROUPBYjob;SELECTmgr,COUNT(mgr)FROMemp WHEREmgrISNOTNULL GROUPBYmgr;SELECTdeptno,COUNT(*)FROMempGROUPBYdeptno;SELECTdeptno,COUNT(1)FROMemp

GROUPBYdeptnoHAVINGCOUNT(1)>3;SQL操作符

連接操作符

說明

|| 合并字符串連接操作符和別名問題：SELECTempno||enameFROMemp;SELECTempno||'-'||enameAS姓名和編號(hào)FROMemp;SELECTempno||'-'||enameAS"姓名-編號(hào)"FROMemp;SELECTempno||'-'||ename"編號(hào)姓名"FROMemp;注意：引用標(biāo)識(shí)符(QuotedIdentifiers)：區(qū)分字符的大小寫。用雙引號(hào)("table")將標(biāo)識(shí)符包圍起來以后，就可以使用它們包含一些非標(biāo)準(zhǔn)的字符及空格，甚至還可以包含保留字——其它情況是絕不允許包含保留字的。我們強(qiáng)烈反對(duì)使用這些非標(biāo)準(zhǔn)的標(biāo)識(shí)符。取消重復(fù)DISTINCTSELECTsalFROMempORDERBYsalDESC;SELECTDISTINCTsalFROMempORDERBYsalDESC;SELECTmgrFROMempORDERBYmgr;SELECTDISTINCTmgrFROMempORDERBYmgr;SELECTjob,sal,deptnoFROMempORDERBYdeptno,sal,job;SELECTDISTINCTjob,sal,deptnoFROMempORDERBYdeptno,sal,job;了解DUAL表Dual是Oracle中的一個(gè)實(shí)際存在的表，任何用戶均可讀取，常用在沒有目標(biāo)表的Select語句塊中。注：在Oracle中select后必須要有from1.查看當(dāng)前連接用戶

SELECTuserFROMdual;2.查看系統(tǒng)日期

SELECTSYSDATEFROMdual;3.當(dāng)作計(jì)算器用

SELECT1+5*2FROMdual;SQL函數(shù)單行函數(shù)只對(duì)單行數(shù)據(jù)進(jìn)行操作，并且每行只返回一個(gè)結(jié)果多行函數(shù)對(duì)多數(shù)據(jù)行的群組進(jìn)行操作，并且每組只返回一個(gè)結(jié)果單行函數(shù)字符函數(shù):分為大小寫轉(zhuǎn)換函數(shù)和字符處理函數(shù)。大小寫轉(zhuǎn)換函數(shù)包括：

LOWER(str)

將字符串轉(zhuǎn)換成小寫

UPPER(str)

將字符串轉(zhuǎn)換成大寫

INITCAP(str)將字符串第一個(gè)字母轉(zhuǎn)換成大寫,后面的字母轉(zhuǎn)成小寫字符處理函數(shù)包括：

CONCAT(str1,str2)

連接兩個(gè)字符串

SUBSTR(str,m,n)取出字符串中從m位開始的n個(gè)字符的子串m位為負(fù)數(shù)時(shí)從右數(shù)起

LENGTH(str)求字符串長(zhǎng)度

INSTR(str1,str2,m,n)

在str1字符串中從第m位開始搜索str2字符串第n次出現(xiàn)的位置m和n默認(rèn)值為1

RPAD和LPAD(粘貼字符)

RPAD

在列的右邊粘貼字符LPAD

在列的左邊粘貼字符

SELECTlpad('gao',6,'*')FROMdual; SELECTRPAD(ename,10,'*')FROMemp; SELECTLPAD(RPAD(ename,8,'*'),10,'$')nameFROMemp;

replace(str,seartch_string,replace_string)

將str中查找seartch_string并且將其替換為replace_string

TRIM單行函數(shù)數(shù)值函數(shù)

ROUND(n,m)

將n四舍五入,保留小數(shù)點(diǎn)m位

Selectround(11.1234,2)fromdual;

TRUNC(n,m)

將n保留小數(shù)點(diǎn)m位，其余的小數(shù)直接截掉

MOD(m,n)m除以n的余數(shù)，如n＝0，則返回m

Selectmod(7,5)fromdual;日期函數(shù)

TRUNC(date,t)如t為‘hh’將保留小時(shí)截?cái)喾趾兔?/p>

selectto_char(trunc(sysdate,'hh'),'yyyy.mm.ddhh24:mi:ss')fromdual;

MONTHS_BETWEEN(date1,date2)

返回的月份

ADD_MONTHS(date,n)

增加或減去月份（date+ndate1-date2)

NEXT_DAY(date,day)計(jì)算給出的日期date下一個(gè)星期‘day’的日期

selectnext_day(sysdate,‘星期二’)fromdual;算下星期二的日期

selectnext_day(sysdate,2)fromdual;計(jì)算下星期第二天的日期

LAST_DAY(date)

本月的最后一天

Selectlast_day(sysdate)fromdual;SELECTEXTRACT(MONTHFROMSYSDATE)FROMDUAL;單行函數(shù)隱式轉(zhuǎn)換:表達(dá)式中oracle自動(dòng)完成轉(zhuǎn)換VARCHAR2orCHAR----NUMBER select*fromempwhere'123'=123;VARCHAR2orCHAR----DATE

SELECTename,hiredateFROMempWHEREhiredate>'03-12月-81';單行函數(shù)數(shù)據(jù)類型的顯式轉(zhuǎn)換TO_CHAR(date,'format')

selectto_char(sysdate,'yyyy/mm/ddhh24:mi:ss')fromdual;SELECTTO_CHAR'99,999,999,999')FROMdual;TO_NUMBER(str)將給出的數(shù)字字符轉(zhuǎn)換為數(shù)字

selectto_number('1999')yearfromdual;TO_DATE(string,‘format’)將字符串轉(zhuǎn)化為ORACLE中的一個(gè)日期

selectto_date(18880312,'yyyymmdd')fromdual;（SELECTDATE'1999-1-2'FROMdual;--這種寫法只能是年月日的形式）單行函數(shù)通用函數(shù)NVL(v,value)

如果v為null返回value的值,否則返回v的值

selectavg(nvl(comm,0))fromemp; NVL2(v,v1,v2)如果v不為null則返回v1的值，如果v為null則返回v2的值

selectcomm,nvl2(comm,comm*1.01,0)comfromemp;COALESCE(expr1[,expr2,expr3,…exprN])若expr1值不為null,則返回expr1的值,若expr1值為null則計(jì)算expr2值是否為null,若不為null則返expr2值,若為null則再計(jì)算expr3…… selectename,coalesce(sal+comm,sal,0)sl,sal,commfromemp;DECODE(value,if1,then1[,if2,then2,if3,then3,...][,else])

Value代表某個(gè)表的任何類型的任意列或一個(gè)通過計(jì)算所得的任何結(jié)果。當(dāng)每個(gè)value值被測(cè)試，如果value的值為if1，Decode函數(shù)的結(jié)果是then1；如果value等于if2，Decode函數(shù)結(jié)果是then2；等等。事實(shí)上，可以給出多個(gè)if/then配對(duì)。如果value結(jié)果不等于給出的任何配對(duì)時(shí)，Decode結(jié)果就返回else。

SELECTename,job,sal,DECODE(job,'MANAGER',sal*0.5,'SALESMAN',sal*0.1,sal*0)add_sal

FROMemp;練習(xí)

函數(shù)

輸入

輸出Initcap(char)

Selectinitcap(‘hello’)fromdual;Hello

Lower(char)

Selectlower(‘FUN’)fromdual;fun

Upper(char)

Selectupper(‘sun’)fromdual;SUN

Ltrim(char,set)

Selectltrim(‘xyzadams’,’xyz’)fromdual;adamsRtrim(char,set)

Selectrtrim(‘xyzadams’,’ams’)fromdual;

xyzad

Translate(char,from,to)

Selecttranslate(‘jack’,’j’,’b’)fromdual;

back

Replace(char,searchstring,[repstring])

Selectreplace(‘jackandjue’,’j’,’bl’)fromdual;blackandblue

Instr(char,m,n)

Selectinstr(‘worldwide’,’d’)fromdual;

5

Substr(char,m,n)

Selectsubstr(‘a(chǎn)bcdefg’,3,2)fromdual;

cdConcat(expr1,expr2)Selectconcat(‘Hello’,’wo