Oracle SQL 課堂筆記資料_第1頁
Oracle SQL 課堂筆記資料_第2頁
Oracle SQL 課堂筆記資料_第3頁
Oracle SQL 課堂筆記資料_第4頁
Oracle SQL 課堂筆記資料_第5頁
已閱讀5頁,還剩45頁未讀, 繼續(xù)免費閱讀

下載本文檔

版權(quán)說明:本文檔由用戶提供并上傳,收益歸屬內(nèi)容提供方,若內(nèi)容存在侵權(quán),請進行舉報或認領(lǐng)

文檔簡介

OracleSQL課堂筆記

第一部分SQL語言基礎(chǔ)

第一章、關(guān)系型與非關(guān)系型數(shù)據(jù)庫

1.1關(guān)系型數(shù)據(jù)庫由來

關(guān)系型數(shù)據(jù)庫,是指采用了關(guān)系模型來組織數(shù)據(jù)的數(shù)據(jù)庫。

關(guān)系模型是在1970年由IBM的研究員E.F.Codd博士首先提出的,在之后的幾十年中,關(guān)系模型的概念得到了充分的發(fā)展并逐漸

成為主流數(shù)據(jù)庫結(jié)構(gòu)的模型。

簡單來說,關(guān)系模型指的就是二維表格模型,而一個關(guān)系型數(shù)據(jù)庫就是由二維表及其之間的聯(lián)系所組成的一個數(shù)據(jù)組織。

1.2關(guān)系型數(shù)據(jù)庫優(yōu)點

1)容易理解:

二維表結(jié)構(gòu)是非常貼近邏輯世界的一個概念,關(guān)系模型相對之前的網(wǎng)狀、層次等其他模型來說更容易理解

2)使用方便:

通用的SQL語言使得操作關(guān)系型數(shù)據(jù)庫非常方便

3)易于維護:

豐富的完整性(實體完整性、參照完整性和用戶定義的完整性)大大減低了數(shù)據(jù)冗余和數(shù)據(jù)不一致的概率

4)交易安全:

所有關(guān)系型數(shù)據(jù)庫都不同程度的遵守事務(wù)的四個基本屬性,因此對于銀行、電信、證券等交易型業(yè)務(wù)的是不可或缺的。

1.3關(guān)系型數(shù)據(jù)庫瓶頸

1)高并發(fā)讀寫需求

網(wǎng)站的用戶并發(fā)性非常高,往往達到每秒上萬次讀寫請求,對于傳統(tǒng)關(guān)系型數(shù)據(jù)庫來說,硬盤I/O是一個很大的瓶頸。

2)海量數(shù)據(jù)的高效率讀寫

互聯(lián)網(wǎng)上每天產(chǎn)生的數(shù)據(jù)量是巨大的,對于關(guān)系型數(shù)據(jù)庫來說,在一張包含海量數(shù)據(jù)的表中查詢,效率是非常低的。

3)高擴展性和可用性

在基于web的結(jié)構(gòu)當中,數(shù)據(jù)庫是最難進行橫向擴展的,當一個應(yīng)用系統(tǒng)的用戶量和訪問量與日俱增的時候,數(shù)據(jù)庫卻沒有辦

法像webserver和appserver那樣簡單的通過添加更多的硬件和服務(wù)節(jié)點來擴展性能和負載能力。對于很多需要提供24小時不

間斷服務(wù)的網(wǎng)站來說,對數(shù)據(jù)庫系統(tǒng)進行升級和擴展是非常痛苦的事情,往往需要停機維護和數(shù)據(jù)遷移。

1.4非關(guān)系型數(shù)據(jù)庫

1)NoSQL特點:

可以彌補關(guān)系型數(shù)據(jù)庫的不足。

針對某些特定的應(yīng)用需求而設(shè)計,可以具有極高的性能。

大部分都是開源的,由于成熟度不夠,存在潛在的穩(wěn)定性和維護性問題。

2)NoSQL分類:

面向高性能并發(fā)讀寫的key-value數(shù)據(jù)庫

面向海量數(shù)據(jù)訪問的面向文檔數(shù)據(jù)庫

面向可擴展性的分布式數(shù)據(jù)庫

1.5優(yōu)勢互補,相得益彰

1)關(guān)系型數(shù)據(jù)庫適用結(jié)構(gòu)化數(shù)據(jù),NoSQL數(shù)據(jù)庫適用非結(jié)構(gòu)化數(shù)據(jù)。

2)Oracle數(shù)據(jù)庫未來的發(fā)展方向:提供結(jié)構(gòu)化、非結(jié)構(gòu)化、半結(jié)構(gòu)化的解決方案,實現(xiàn)關(guān)系型數(shù)據(jù)庫和NoSQL共存互補。值得

強調(diào)的是:目前關(guān)系型數(shù)據(jù)庫仍是主流數(shù)據(jù)庫,雖然NoSql數(shù)據(jù)庫打破了關(guān)系數(shù)據(jù)庫存儲的觀念,可以很好滿足web2.0時代數(shù)

據(jù)存儲的要求,但NoSql數(shù)據(jù)庫也有自己的缺陷。在現(xiàn)階段的情況下,可以將關(guān)系型數(shù)據(jù)庫和NoSQL數(shù)據(jù)庫結(jié)合使用,相互彌

補各自的不足。

第二章、SQL的基本函數(shù)

2.1關(guān)系型數(shù)據(jù)庫命令類別

數(shù)據(jù)操縱語言:DML:select;insert;delete;update;merge.

數(shù)據(jù)定義語言:DDL:create;alter;drop;truncate;rename;comment.

事務(wù)控制語言:TCL:commit;rollback;savepoint.

數(shù)據(jù)控制語言:DCL:grant;revoke.

2.2單行函數(shù)與多行函數(shù)

單行函數(shù):指一行數(shù)據(jù)輸入,返回一個值的函數(shù)。所以查詢一個表時,對選擇的每一行數(shù)據(jù)都返回一個結(jié)果。

SQL>selectempnojower(ename)fromemp;

多行函數(shù):指多行數(shù)據(jù)輸入,返回一個值的函數(shù)。所以對表的群組進行操作,并且每組返回一個結(jié)果。(典型的是聚合函數(shù))

SQL>selectsum(sal)fromemp;

本小結(jié)主要介紹常用的一些單行函數(shù),分組函數(shù)見第五章

2.3單行函數(shù)的幾種類型

單行郵多行頻

2.3.1字符型函數(shù)?-------------------------------------------------------------

lowerfSQLCourse')---->sqlcourse返回小寫

upperfsqlcourse')---->SQLCOURSE返回大寫

initcap('SQLcourse')——>SqlCourse每個單字返回首字母大寫

concatCgood'/string1)——>goodstring拼接只能拼接2個字符串

substr('String',l,3)-->Str從第1位開始截取3位數(shù),

演變:只有兩個參數(shù)的

substrCString',3)正數(shù)第三位起始,得到后面所有字符

substrCStringrZ)倒數(shù)第二位,起始,得到最后所有字符

instift#i#m#r#a#n#?卻一〉找第一個#字符在那個絕對位置,得到的數(shù)值

Instr參數(shù)經(jīng)常作為substr的第二個參數(shù)值

演變:Instr參數(shù)可有四個之多

如selectinstrCaunfukk'/u',-1,1)fromdual;倒數(shù)第二個u是哪個位置,結(jié)果返回5

length('String')-一>6長度,得到的是數(shù)值

length參數(shù)又經(jīng)常作為substr的第三個參數(shù)

lpad(¥irst門0,$)左填充

rpad(676768,10/*')右填充

replacefJACKandJUE'/J'/BL')一一>BLACKandBLUE

trimfm'from'mmtimranm')-->timran兩頭截,這里的,m,是截取集,僅能有一個字符

處理字符串時,利用字符型函數(shù)的嵌套組合是非常有效的,試分析一道考題:

createtablecustomers(cust_namevarchar2(20));

insertintocustomersvalues('LexDeHann');

insertintocustomersvaluesfRenskeLadwig');

insertintocustomersvalues('JoseManuelUrman');

insertintocustomersvalues('JosonMalin');

select*fromcustomers;

CUST_NAME

LexDeHann

RenskeLadwig

JoseManuelUrman

JosonMalin

一共四條記錄,客戶有兩個名的,也有三個名的,現(xiàn)在想列出僅有三個名的客戶,且第一個名字用*號略去

答案之一:

SELECTLPAD(SUBSTR(cust_name,INSTR(cust_nameJ)),LENGTH(cust_name)「*')"CUSTNAME"

FROMcustomers

WHEREINSTR(cust_name;',l,2)<>0;

CUSTNAME

***DeHann

****ManuelUrman

分析:

先用INSTR(cust_nameJ')找出第一個空格的位置,

然后,SUBSTR(cust_name,INSTR(cust_name,'1))從第一個空格開始往后截取字符串到末尾,結(jié)果是第一個空格以后所有的字符,

最后,LPAD(SUBSTR(cust_name,INSTR(cust_name,'')),LENGTH(cust_name),'*')用LPAD左填充到cust_name原來的長度,不足的部

分用*填充,也就是將第二個空格前一的位置,用*填充。

where后過濾是否有三個名字,INSTR(cust_name,'',1,2)從第一個位置,從左往右,查找第二次出現(xiàn)的空格,如果返回非0值,

則說明有第二個空格,則有第三個名字。

2.3.2數(shù)值型函數(shù)

round對指定的值做四舍五入,round(p,s)s為正數(shù)時,表示小數(shù)點后要保留的位數(shù),s也可以為負數(shù),但意義不大。

round:按指定精度對十進制數(shù)四舍五入,如:round(45.923,1),結(jié)果,45.9

round(45.923,0),結(jié)果,46

round(45.923,-1),結(jié)果,50

trunc對指定的值取整trunc(p,s)

trunc:按指定精度截斷十進制數(shù),如:trunc(45.923,1),結(jié)果,45.9

trunc(45.923),結(jié)果,45

trunc(45.923,-1),結(jié)果,40

mod返回除法后的余數(shù)

SQL>selectmod(100,12)fromdual;

2.3.3日期型函數(shù)

因為日期在。racle里是以數(shù)字形式存儲的,所以可對它進行加減運算,計算是以天為單位。

缺省格式:DD-MON-RR.

可以表示日期范圍:(公元前)4712至(公元)9999

時間格式

SQL>selectto_date('2003-ll-0400:00:00';YYYY-MM-DDHH24:MI:SS')FROMdual;

SQL>selectsysdate+2fromdual;當前時間+2day

SQL>selectsysdate+2/24fromdual;當前時間+2hour

SQL>select(sysdate-hiredate)/7weekfromemp;兩個date類型差,結(jié)果是以天為整數(shù)位的實數(shù)。

①MONTHS_BETWEEN計直兩個日期之間而月數(shù)

SQL>selectmonths_between('1994-04-01','1992-04-01')mmfromdual;

查找emp表中參加工作時間>30年的員工

SQL>select*fromempwheremonths_between(sysdate,hiredate)/12>30;

很容易認為單行函數(shù)返回的數(shù)據(jù)類函與函數(shù)類型一致,對于數(shù)值函數(shù)類型而言的確如此,但字符和日期函數(shù)可以返回任何數(shù)據(jù)

類型的值。比如instr函數(shù)是字符型的,months_between函數(shù)是日期型的,但它們返回的都是數(shù)值。

②ADD_MONTHS給日期增加月份

SQL>selectadd_months('1992-03-01',4)amfromdual;

③LAST_DAY日期當前月份的最后一天

SQL>selectlast_day('1989-03-28')l_dfromdual;

@NEXT_DAYNEXT_DAY的第2個參數(shù)可以是數(shù)字1-7,分別表示周日-周六(考點)

比如要取下一個星期六,則應(yīng)該是:

SQL>selectnext_day(sysdate,7)FROMDUAL;

⑤ROUND(p,s),TRUNC(p,s)在日期中的應(yīng)用,如何舍入要看具體情況,s是MONTH按30天計,應(yīng)該是15舍16入,s是YEAR

則按6舍7入計算。

SQL>SELECTempno,hiredate,round(hiredate,'MONTH')ASround,trunc(hiredate,'MONTH')AStrunc

FROMempWHEREempno=7844;

SQL>SELECTempno,hiredate,round(hiredate,'YEAR')ASround,trunc(hiredate,'YEAR')AStrunc

FROMempWHEREempno=7839;

2.3.4幾個有用的函數(shù)和表達式

1)DECODE函數(shù)和CASE表達式:

實現(xiàn)sql語句中的條件判斷語句,具有類似高級語言中的if-then語句的功能。

decode函數(shù)源自oracle,case表達式源自sql標準,實現(xiàn)功能類似,decode語法更簡單些。

decode函數(shù)用法:

SQL>SELECTjob,sal,

decodefjob,'ANALYST',SAL*1.1,'CLERK',SAL*1.15;MANAGER',SAL*1.20,SAL)SALARY

FROMemp

/

decode函數(shù)的另兩種常見用法:

SQL>selectename,job,decode(comm^ull/nonsale'/sale1)salemanfromemp;

注:單一列處理,共四個參數(shù):含義是:comm如果為null就取'nonsale,否則取'sale'

SQL>selectename,decode(sign(sal-2800),1,'HIGH'/LOW')as"LEV"fromemp;

注:sign。函數(shù)根據(jù)某個值是0、正數(shù)還是負數(shù),分別返回0、1、-1,含義是:工資大于2800,返回1,真取,HIGHT假取10W,

CASE表達式第一種用法:

SQL>SELECTjob,sal,casejob

when'CLERK'thenSAL*1.15

when'MANAGER'thenSAL*1.20

elsesalendSALARY

FROMemp

/

CASE表達式第二種用法:

SQL>SELECTjob,sal,case

whenjob='ANALYSTthenSAL*1.1

whenjob='CLERK'thenSAL*1.15

whenjob='MANAGER'thenSAL*1.20

elsesalendSALARY

FROMemp

/

以上三種寫法結(jié)果都是一樣的

CASE第二種語法比第一種語法增加了搜索功能。形式上第一種when后跟定值,而第二種還可以使用表達式和比較符。

看一個例子

SQL>SELECTename,sal,case

whensal>=3000then'高級'

whensal>=2000then'中級]

else'低級'end級別

FROMemp

/

再看一個例子:使用了復(fù)雜的表達式

SQL>SELECTAVG(CASE

WHENsalBETWEEN500AND1000ANDJOB='CLERK'

THENsalELSEnullEND)"CLERK_SAL"

fromemp;

比較;

SQL>selectavg(sal)fromempwherejob='CLERK,;

2)DISTINCT(去重)限定詞的用法:

distinct貌似多行函數(shù),嚴格來說它不是函數(shù)而是select子句中的關(guān)鍵字。

SQL>selectdistinctjobfromemp;消除表行重復(fù)值。

SQL>selectdistinctjob,deptnofromemp;重復(fù)值是后面的字段組合起來考慮的

3)sys.context獲取環(huán)境上下文的函數(shù)(應(yīng)用開發(fā))

scott遠程登錄

SQL>selectSYS_CONTEXT('USERENV'「IP_ADDRESS')fromdual;

36

SQL>selectsys^ontextCuserenv'/sid')fromdual;

SYS-CONTEXTCUSERENV/SID')

129

SQL>selectsys_context('userenv7terminar)fromdual;

SYS_CONTEXT('USERENV'「TERMINAL')

TIMRAN-222C75E5

4)處理空值的幾種函數(shù)(見第四章)

5)轉(zhuǎn)換函數(shù)TO_CHAR、TO_DATE>TO_NUMBER(見第三章)

第三章、SQL的數(shù)據(jù)類型(表的字段類型)

3.1四種基本的常用數(shù)據(jù)類型(表的字段類型)

1、字符型,2、數(shù)值型,3、日期型,4、大對象型

3.1.1字符型:

數(shù)據(jù)類型說明

CHAR(size)2000固定長度字符串,size^示存儲的字符數(shù)

NCHAR(size)2000固定長度的NLS(NationalLanguage

Support序符串,siz基示存褶的字符數(shù)

NVARCHAR2(size)4000可變長度的NLS字符串,siz漾示存儲的

字符數(shù)量

VARCHAR2(size)4000可變長度字符串,五2將示存儲的字符數(shù)

RAW2000可變長度二進制字符串

字符類型char和varchar2的區(qū)別

SCOTT@prod>createtabletl(clchar(10),c2varchar2(10));

SCOTT@prod>insertintotlvalues('a','ab');

SCOTT@prod>selectIength(cl),length(c2)fromtl;

LENGTH(Cl)LENGTH(C2)

102

3.1.2數(shù)值型:

數(shù)據(jù)類型說明

NUMBER(p:s)包含小數(shù)位的數(shù)值類型。參數(shù)p表示精度,參數(shù)S

表示小數(shù)點后面的位數(shù)。例如:NUMBERC10.2)

表示小數(shù)點之前最多可以有8位數(shù)字,小數(shù)點后

有2位數(shù)字

NUMERIC(p5s)與NUMBER(p,s沖目同

FLOAT浮點數(shù)類型。它屬于近似數(shù)據(jù)類型,并不存儲數(shù)

數(shù)字的精確值,只存儲最近似值

DEC(pss)與NUMBER(p.s湘同

DECIMAL(pss)與NUMBER**湘同

INTEGER整數(shù)類型

INT同INTEGER

SMALLINT短整類型

REAL實數(shù)類型,與FLOAT一樣,屬于近似數(shù)據(jù)類型

DOUBLE雙精度類型

PRECISION

3.1.3日期型:

數(shù)據(jù)類型說明

DATE日期類型

TIMESTAMP與DATE數(shù)據(jù)類型相比,TIMESTAMP類型可以精

確到微秒,微秒的精確范圍為0-9,默認為6

TIMESTAMPWITH帶時區(qū)偏移量的TIMESTAM啜據(jù)類型

TIMEZONE

TIMESTAMPWITH帶時區(qū)偏移量的TIMESTAM啜據(jù)類型

LOCALTIME

ZONE

INTERVALYEAR使用YEAR和MONTH日期時間字段存儲一個時間

TOMONTH段。年份精度指定表示年份的數(shù)字的位數(shù)。默認

為2

INTERVALDAY用于按照日、小時、分鐘和秒來存儲一個時段。

TOSECOND日精度表示DAY字段的位數(shù),默認為2,微秒的

精度范圍為0-9,默認為6

系統(tǒng)安裝后,默認日期格式是DD-MON-RR,RR和YY都是表示兩位年份,但RR是有世紀認知的,它將指定日期的年份和當前年

份比較后確定年份是上個世紀還是本世紀(如表)。

當前年份指定日期RR格式Y(jié)Y格式

199527-OCT-9519951995

199527-OCT-1720171917

200127-OCT-1720172017

201327-OCT-9519952095

3.1.4LOBgJ:

大對象是10g引入的,在11g中又重新定義,在一個表的字段里存儲大容量數(shù)據(jù),所有大對象最大都可能達到4G

數(shù)據(jù)類型說明

BFILE指向服務(wù)器文件系統(tǒng)上的二進制文件的文件定位器,

該二進制文件保存在數(shù)據(jù)庫之外

BLOB保存非結(jié)構(gòu)化的二進制大對象數(shù)據(jù)

CLOB保存單字節(jié)或多字節(jié)字符數(shù)據(jù)

NCLOB保存Unicode編碼字符數(shù)據(jù)

CLOB,NCLOB,BLOB都是內(nèi)部的LOB類型,沒有LONG只能有一列的限制。

保存圖片或電影使用BLOB最好、如果是小說則使用CLOB最好。

雖然LONG、RAW也可以使用,但LONG是oracle將要廢棄的類型,因此建議用LOB。

雖說將要廢棄,但還沒有完全廢棄,比如oracle11g里的一些視圖如dba_views,對于text(視圖定義)仍然沿用了LONG類型。

Oracle11g重新設(shè)計了大對象,推出SecureFileLobs的概念,相關(guān)的參數(shù)是db_securefile,采用SecureFileLobs的前提條件是11g

以上版本,ASSM管理等,符合這些條件的

BasicFileLobs也可以轉(zhuǎn)換成SecureFileLobs。較之過去的BasicFileLobs,SecureFileLobs有幾項改進:

1)壓縮,2)去重,3)加密。

當createtable定義LOB列時,也可以使用LOB_storage_clause指定SecureFileLobs或BasicFileLobs

而LOB的數(shù)據(jù)操作則使用Oracle提供的DBMS_LOB包,通過編寫PL/SQL塊完成LOB數(shù)據(jù)的管理。

3.2數(shù)據(jù)類型的轉(zhuǎn)換

3.2.1轉(zhuǎn)換的需求

什么情況下需要數(shù)據(jù)類型轉(zhuǎn)換

1)如果表中的某字段是日期型的,而日期又是可以進行比較和運算的,這時通常要保證參與比較和運算的數(shù)據(jù)類型都是日期型o

2)當對函數(shù)的參數(shù)進行抽(截)取、拼接,或運算等操作時,需要轉(zhuǎn)換為那個函數(shù)的參數(shù)要求的數(shù)據(jù)類型。

3)制表輸出有格式需求的,可將date類型,或number類型轉(zhuǎn)換為char類型

4)轉(zhuǎn)換成功是有條件的,有隱性轉(zhuǎn)換和顯性轉(zhuǎn)換兩種方式

3.2.2隱性類型轉(zhuǎn)換:

是指oracle自動完成的類型轉(zhuǎn)換。在一些帶有明顯意圖的字面值上,可以由Oracle自主判斷進行數(shù)據(jù)類型的轉(zhuǎn)換。

一般規(guī)律:

比較或運算時:一般是字符轉(zhuǎn)為數(shù)值或日期(字符長的要像數(shù)值或日期)

賦值或調(diào)用函數(shù)時:一般是數(shù)值或日期轉(zhuǎn)字符(以定義的字段類型、或變量類型為準)

連接時:一般是數(shù)值或日期轉(zhuǎn)字符

如:

SQL>selectempno,enamefromempwhereempno='7788';

empno本來是數(shù)值類型的,這里字符‘7788'隱性轉(zhuǎn)換成數(shù)值7788

SQL>selectlength(sysdate)fromdual;

將date型隱轉(zhuǎn)成字符型后計算長度

SQL>SELECT'12.5'+11FROMdual;

將字符型'12.5'隱轉(zhuǎn)成數(shù)字型再求和

SQL>SELECT10+('12.5'|111)FROMdual;

將數(shù)字型11隱轉(zhuǎn)成字符與'12.5'合并,其結(jié)果再隱轉(zhuǎn)數(shù)字型與10求和

3.2.3顯性類型轉(zhuǎn)換

即強制完成類型轉(zhuǎn)換(推薦),有三種形式的數(shù)據(jù)類型轉(zhuǎn)換函數(shù):

TO_CHAR

TODATE

TONUMBER

TO_NUMBERTO_DATE

1)日期字符

SQL>selectename,hiredate,to_char(hiredate,'DD-MON-YY')month_hiredfromemp

whereename='SCOTT';

ENAMEHIREDATEMONTH_HIRED

SCOTT1987-04-1900:00:0019-4月-87

fm壓縮空格或左邊的'O'

SQL>selectename,hiredate,to_char(hiredate,'fmyyyy-mm-dd')month_hiredfromemp

whereename='SCOTT';

ENAMEHIREDATEMONTH_HIRED

SCOTT1987-04-1900:00:001987-419

其實DD-MM-YY是比較糟糕的一種格式,因為當日期中天數(shù)小于12時,DD-MM-YY和MM-DD-YY容易造成混亂。

以下用法也很常見

SQL>selectto_char(hiredate,'yyyy')FROMemp;

SQL>selectto_char(hiredate/mm')FROMemp;

SQL>selectto^harthiredate/dd')FROMemp;

SQL>selectto_char(hiredate/DAY')FROMemp;

2)數(shù)字字符:9表示數(shù)字,L本地化貨幣字符

SQL>selectename,to_char(sal,199,999.99')Salaryfromempwhereename='SCOTT';

ENAMESALARY

SCOTT¥3,000.00

以下四個語句都是一個結(jié)果(考點),

SQL>selectto_char(1890.55J$99,999.99')fromdual;

SQL>selectto_char(1890.55,'$0G000D00')fromdual;

SQL>selectto_char(1890.55;$99G999D99')fromdual;

SQL>selectto_char(1890.55;$99G999D00')fromdual;9和0可用,其他數(shù)字不行

3)字符日期

SQL>selectto_date('1983-ll-12','YYYY-MM-DD')tmp_DATEfromdual;

4)字符一>數(shù)予:

SQL>SELECTto_number(,$123.45';$9999.99')resultFROMdual;

使用to_number時如果使用較短的格式掩碼轉(zhuǎn)換數(shù)字,就會返回錯誤。不要混淆to_number和to_char轉(zhuǎn)換。

SQL>selectto_number(,123.56','999.9,)fromdual;

報錯:ORA-01722:無效數(shù)字

練習(xí):建立tl表,包括出生日期,以不同的日期描述方法插入數(shù)據(jù),顯示小于15歲的都是誰

SQL>createtabletl(idint,namechar(10),birthdate);

insertintotlvaluestl/tim\sysdate);

insertintotlvalues(2,'brian',sysdate-365*20);

insertintotlvalues(3/mike,,to_date(,1998-05-117yyyy-mm-dd'));

insertintotlvalues(4,'nelson',to_date('15-2月-127dd-mon-rr'));

SQL>select*fromtl;

IDNAMEBIRTH

ltim2016-02-2517:34:00

2brian1996-03-0117:34:22

3mike1998-05-1100:00:00

4nelson2012-02-1500:00:00

SQL>selectname,birth,to_char(months_between(sysdate,birth)/12,99)agefromtlwheremonths_between(sysdate,birth)/12<15;

SQL>selectname||'的年齡是'||to_char(months_between(sysdate,birth)/12,99)agefromtl

wheremonths_between(sysdate,birth)/12<15;

AGE

tim的年齡是0

nelson的年齡是4

第四章、WHERE子句中常用的運算符

4.1運算符及優(yōu)先級:

算數(shù)運算符

*,/,+,

邏輯運算符

not,and,or

比較運算符

1)單行比較運算

2)多行比較運算>any,>all,<any,<all,in,notin

3)模糊比較like(配合"%”和)

4)特殊比較isnull

5)()優(yōu)先級最高

SQL>selectenamejob,sal,commfromempwherejob=,SALESMAN'ORjob='PRESIDENT,ANDsal>1500;

注意:條件子句使用比較運算符比較兩個選項,重要的是要理解這兩個選項的數(shù)據(jù)類型。必須得一致,所以這里常用顯性轉(zhuǎn)換。

數(shù)值型、日期型、字符型都可以與同類型比較大小,但數(shù)值和日期比的是數(shù)值的大小,而字符比的是acsii碼的大小

試比較下面語句,結(jié)果為什么不同

SQL>select*fromempwherehiredate>to_datef1981-02-21','yyyy-mm-dd');

SQL>select*fromempwhereto_char(hiredate,'dd-mon-rr')>'21-feb-81';

4.2常用謂詞

4.2.1用BETWEENAND操作符來查詢出在某一范圍內(nèi)的行.

SQL>SELECTename,salFROMempWHEREsalBETWEEN1000AND1500;

between低值and高值,包括低值和高值。

4.2.2模糊查詢及其通配符:

在where字句中使用like謂詞,常使用特殊符號"%"或匹配查找內(nèi)容,也可使用escape可以取消特殊符號的作用。

SQL>

createtabletest(namechar(10));

insertintotestvalues('sFdL');

insertintotestvalues('AEdLHH');

insertintotestvalues('A%dMH');

commit;

SQL>select*fromtest;

NAME

sFdL

AEdLHH

A%dMH

SQL>select*fromtestwherenamelike'A\%%'escape

NAME

A%dMH

4.2.3"和,,"的用法:

單引號的轉(zhuǎn)義:連續(xù)兩個單引號表示轉(zhuǎn)義.

''內(nèi)表示字符或日期數(shù)據(jù)類型,而----般用于別名中有大小寫、保留字、空格等場合,引用recyclebin中的《表名》也需要"

SQL>selectempno11'isScott"sempno'fromempwhereempno=7788;

EMPNO|riSSCOTT"SEMPNO'

7788isScott'sempno

4.2.4交互輸入變量符&和&&的用途:

①使用&交互輸入

SQL>selectempno,enamefromempwhereempno=&empnumber;

輸入empnumber的值:7788

&后面是字符型的,注意單引號問題,可以有兩種寫法:

SQL>selectempno,enamefromempwhereename='&emp_name';

輸入emp_name的值:SCOTT

SQL>selectempno,enamefromempwhereename=&emp_name;

輸入emp_name的值:'SCOTT'

②使用&&可以將&保存為變量

作用是使后面的相同的&不再提問,自動取代。

SQL>selectempno,ename,&&salaryfromempwheredeptno=10orderby&salary;

輸入salary的值:sal

上例給的&salary已經(jīng)在當前session下存儲了,可以使用undefinesalary解除。

&&salary和&的提示是按所在位置從左至右邊掃描,&&salary寫在左邊(首位),&salary(第二位)

③define(定義變量)和undefine命令(解除變量)

SQL>define--顯示當前已經(jīng)定義的變量(包括默認值)

setdefineon|off可以打開和關(guān)閉&。

SQL>defineemp_num=7788定義變量emp_num

SQL>selectempno,ename,salfromempwhereempno=&emp_num;

SQL>undefineemp_num取消變量

如果不想顯示“威直”和“新值”的提示,可以使用setverifyon|off命令

④Accept接收一個變量

類似define功能,但通常和&配合使用

SQL>acceptlowdateprompt'Pleaseenterthelowdaterange("MM/DD/VYW"):';

SQL>accepthighdateprompt'Pleaseenterthehighdaterange("MM/DD/YYYY"):';

SQL>selectenamel|'/1|jobasEMPLOYEES,hiredatefromempwherehiredatebetween^dateC&lowdate'/MM/DD/YYYY')and

tO-dateC&highdate'/MM/DD/YYYY');

4.2.5使用邏輯操作符:AND;OR;NOT

AND兩個條件都為TRUE,則返回TRUE

SQL>SELECTempno,ename,job,salFROMempWHEREsal>=1100ANDjob='CLERK,;

OR兩個條件中任何一個為TRUE,則返回TRUE

SQL>SELECTempno,ename,job,salFROMempWHEREsal>=1100ORjob='CLERK';

NOT如果條件由FALSE,返回TRUE

SQL>SELECTenamejobFROMempWHEREjobNOTIN('CLERK'/MANAGER'/ANALYST');

4.2.6什么是偽列

簡單理解它是表中的列,但不是你創(chuàng)建的。

Oracle數(shù)據(jù)庫有兩個著名的偽列rowid和rownum

ROWID的含義

rowid可以說是物理存在的,表示記錄在表空間中的唯一位置ID,所以表的每一行都有一個獨一無二的物理門牌號。

ROWNUM的含義

rownum是對結(jié)果集增加的一個偽列,即先查到結(jié)果集,當輸出時才加上去的一個編號。rownum必須包含1才有值輸出。

SQL>selectrowid,rownum,enamefromemp;

SQL>select*fromempwhererowid='AAARZ+AAEAAAAG0AAH';

SQL>select*fromempwhererownum<=3;

第五章、分組函數(shù)

5.1五個分組函數(shù)

sum();avg();count();max();min().

數(shù)值類型可以使用所有組函數(shù)

SQL>selectsum(sal)sum,avg(sal)avg,max(sal)max,min(sal)min,count(*)countfromemp;

MIN(),MAX(),count??梢宰饔糜谌掌陬愋秃妥址愋?/p>

SQL>selectmin(hiredate),max(hiredate),min(ename),max(ename),count(hiredate)fromemp;

COUNT(*)函數(shù)返回表中行的總數(shù),包括重復(fù)行與數(shù)據(jù)列中含有空值的行,而其他分組函數(shù)的統(tǒng)計都不包括空值的行。

COUNT(comm)返回該列所含非空行的數(shù)量。

SQL>selectcount(*),count(comm)fromemp;

COUNT(*)COUNT(COMM)

144

5.2GROUPBY建立分組

SQL>selectdeptno,avg(sal)fromempgroupbydeptno;

groupby后面的列也叫分組特性,一旦使用了groupby,select后面只能有兩種列,一個是組函數(shù)列,而另一個是分組特性列(可

選)。

對分組結(jié)果進行過濾

SQL>selectdeptno,avg(sal)avgcommfromempgroupbydeptnohavingavg(sal)>2000;

SQL>selectdeptno,avg(sal)avgcommfromempwhereavg(sal)>2000groupbydeptno;錯誤的,應(yīng)該使用HAVING子句

對分組結(jié)果排序

SQL>selectdeptno,avg(nvl(sal,O))avgcommfromempgroupbydeptnoorderbyavg(nvl(sal,O));

排序的列不在select投影選項中也是可以的,這是因為orderby是在select投影前完成的。

5.3分組函數(shù)的嵌套

單行函數(shù)可以嵌套任意層,但分組函數(shù)最多可以嵌套兩層。

比如:count(sum(avg)))會返回錯誤"ORA-00935:groupfunctionisnestedtoodeeply^.

在分組函數(shù)內(nèi)可以嵌套單行函數(shù),如:要計算各個部門ename值的平均長度之和

SQL>selectsum(avg(length(ename)))fromempgroupbydeptno;

第六章、數(shù)據(jù)限定與排序

6.1SQL語句的編寫規(guī)則

1)SQL語句是不區(qū)分大小寫的,關(guān)鍵字通常使用大寫;其它文字都是使用小寫

2)SQL語句可以是一行,也可以是多行,但關(guān)鍵字不能在兩行之間一分為二或縮寫

3)子句通常放在單獨的行中,這樣可以增強可讀性并且易于編輯

4)使用縮進是為了增強可讀性

簡單查詢語句執(zhí)行順序

簡單查詢一般是指一個SELECT查詢結(jié)構(gòu),僅訪問一個表。

基本語法如下:

SELECT子句一指定查詢結(jié)果集的列組成,列表中的列可以來自一個或多個表或視圖。

FROM子句一指定要查詢的一個或多個表或視圖。

WHERE子句一指定查詢的條件。

GROUPBY子句一對查詢結(jié)果進行分組的條件。

HAVING子句一指定分組或集合的查詢條件。

ORDERBY子句一指定查詢結(jié)果集的排列順序

語句執(zhí)行的一般順序為①from,②where,③groupby,④having,⑤orderby,?select

where限定from后面的表或視圖,限定的選項只能是表的列或列單行函數(shù)或列表達式,where后不可以直接使用分組函數(shù)

SQL>selectempnojobfromempwheresal>2000;

SQL>selectempnojobfromempwherelength(job)>5;

SQL>selectempnojobfromempwheresal+comm>2000;

having限定groupby的結(jié)果,限定的選項必須是groupby后的聚合函數(shù)或分組列,不可以直接使用where后的限定選項。

SQL>selectsum(sal)fromempgroupbydeptnohavingdeptno=10;

SQL>selectdeptno,sum(sal)fromempgroupbydeptnohavingsum(sal)>7000;

也存在一些不規(guī)范的寫法:不建議采用。如上句改成having位置在groupby之前

SQL>selectdeptno,sum(sal)fromemphavingsum(sal)>7000groupbydeptno;

6.2排序(orderby)

1)位置:orderby語句總是在一個select語句的最后面。

2)排序可以使用列名,列表達式,列函數(shù),列別名,列位置編號等都沒有限制,select的投影列可不包括排序列,除指定的列

位置標號外。

3)升序和降序,升序ASC(默認),降序DESC。有空值的列的排序,缺省(ASC升序)時null排在最后面(考點)。

4)混合排序,使用多個列進行排序,多列使用逗號隔開,可以分別在各列后面加升降序。

SQL>selectename,salfromemporderbysal;

SQL>selectename,salassalaryfromemporderbysalary;

SQL>selectename,salassalaryfromemporderby2;

SQL>selectename,sal,sal+100fromemporderbysal+comm;

SQL>selectdeptno,avg(sal)fromempgroupbydeptnoorderbyavg(sal)desc;

SQL>selectenamejob,sal+commfromemporderby3nullsfirst;

SQL>selectename,deptnojobfromemporderbydeptnoascjobdesc;

6.3空值(null)

空值既不是數(shù)值0,也不是字符"",null表示不確定。

6.3.1空值參與運算或比較時要注意幾點:

1)空值(null)的數(shù)據(jù)行將對算數(shù)表達式返回空值

SQL>selectename,sal,comm,sal+commfromemp;

2)分組函數(shù)忽略空值

SQL>selectsum(sal),sum(sal+comm)fromemp;為什么sal+comm的求和小于sal的求和?

SUM(SAL)SUM(SAL+COMM)

290257800

3)比較表達式選擇有空值(null)的數(shù)據(jù)行時,表達式返回為“假”,結(jié)果返回空行。

SQL>selectename,sal,commfromempwheresal>=comm;

4)非空字段與空值字段做"||”時,null值轉(zhuǎn)字符型合并列的數(shù)據(jù)類型為varchar2。

SQL>selectename,sal||commfromemp;

5)notin在子查詢中的空值問題(見第八章)

6)外鍵值可以為null,唯一約束中,null值可以不唯一(見十二章)

7)空值在where子句里使用“isnull"或"isnotnull”

SQL>selectename,mgrfromempwheremgrisnull;

SQL>selectename,mgrfromempwheremgrisnotnull;

8SQL>updateempsetcomm=nullwhereempno=7788;

6.3.2處理空值的幾種函數(shù)方法:

1)nvl(exprl,expr2)

當?shù)谝粋€參數(shù)不為空時取第一個值,當?shù)谝粋€值為NULL時,取第二個參數(shù)的值。

SQL>selectnvl(l,2)fromdual;

NVL(1,2)

1

SQL>selectnvl(null,2)fromdual;

NVL(NULL,2)

2

nvl函數(shù)可以作用于數(shù)值類型,字符類型,日期類型,但數(shù)據(jù)類型盡量匹配。

NVL(comm,0)

NVL(hiredate,'1970-01-01,)

NVL(ename/nomanager')

2)nvl2(exprl,expr2,expr3)

當?shù)谝粋€參數(shù)不為NULL,取第二個參數(shù)的值,當?shù)谝粋€參數(shù)為NULL,取第三個數(shù)的值。

SQL>selectnvl2(l,2,3)fromdual;

NVL2(1,2,3)

2

SQL>selectnvl2(null,2,3)fromdual;

NVL2(NULL,2,3)

3

SQL>selectename,sal,comm,nvl2(comm,SAL+COMM,SAL)income,deptnofromempwheredeptnoin(10,30);

考點:nvl和nvl2中的第二個參數(shù)不是一回事。

3)NULLIF(exprl,expr2)

當?shù)谝粋€參數(shù)和第二個參數(shù)相同時,返回為空,當?shù)谝粋€參數(shù)和第二個數(shù)不同時,返回第一個參數(shù)值,第一個參數(shù)值不允許為

null

SQL>selectnullif(2,2)fromdual;

SQL>selectnullif(l,2)fromdual;

4)coalesce(exprl,expr2.....)

返回從左起始第一個不為空的值,如果所有參數(shù)都為空,那么返回空值。

這里所有的表達式都是同樣的數(shù)據(jù)類型

SQL>selectcoalesced,2,3,4)fromdual;

SQL>selectcoalesce(null,2,null,4)fromdual;

第七章、復(fù)雜查詢(上):多表連接技術(shù)

7.1簡單查詢的解析方法:

全表掃描:指針從第一條記錄開始,依次逐行處理,直到最后一條記錄結(jié)束;

橫向選擇+縱向投影=結(jié)果集

7.2多表連接

7.2.1多表連接的優(yōu)缺點

優(yōu)點:

1)減少冗余的數(shù)據(jù),意味著優(yōu)化了存儲空間,降低了10負擔。

2)根據(jù)查詢需要決定是否需要表連接。

3)靈活的增加字段,各表中字段相對獨立(非主外鍵約束),增減靈活。

缺點:

1)多表連接語句可能冗長復(fù)雜,易讀性差。

2)可能需要更多的CPU資源,一些復(fù)雜的連接算法消耗CPU和Memory。

3)只能在一個數(shù)據(jù)庫中完成多表連接查詢。

7.2.2多表連接中表的對應(yīng)關(guān)系

1)一對一關(guān)系

將表一份為二,最簡單的對應(yīng)關(guān)系

2)一對多關(guān)系

兩表通過定義主外鍵約束,符合第三范式標準的對應(yīng)關(guān)系。

CourseExamples:HRSampleSchema

REGIONS

RRtSTC?N_TTJ(PK)

REG1ON二ANME

COUNTRIES

COUNTRY_ID(PK)

COUNT復(fù)二YNAMEJOBHISTORY

REGI0N_ID(FK)MPLOYEE_ID(PK)

ffTART_T>ATR(PR)

1ND_DAT3

zTKJOB二工方(FK)

IX)CATIONSEEPAR?ENT_ID(FKI

I/3CATION_ID(PK)

ICTRESTADDRBCC

SH

mounnoMS

nig$

HamaMult?

Mame

PROVOIDMlm?

NOTWU-|VtID

PROMO.NAWENOTNIAlVTIMCHAMJI3C;

OAVJNAMC"WCXARJE

NOTNUIXNOTMUU.

OAY.NUNOtR.lN.MONTWNUVBtRpI

雁?我配NOTMAXMdMBtRNOTMAX

CAItfilJAHSUMMtH

PROMO.CATEGORYNOTNULLVftHCMAHitKMOTMB1NUWBtRUl

CALENDARUONTMNUMBERNUUBERU)

溫馨提示

  • 1. 本站所有資源如無特殊說明,都需要本地電腦安裝OFFICE2007和PDF閱讀器。圖紙軟件為CAD,CAXA,PROE,UG,SolidWorks等.壓縮文件請下載最新的WinRAR軟件解壓。
  • 2. 本站的文檔不包含任何第三方提供的附件圖紙等,如果需要附件,請聯(lián)系上傳者。文件的所有權(quán)益歸上傳用戶所有。
  • 3. 本站RAR壓縮包中若帶圖紙,網(wǎng)頁內(nèi)容里面會有圖紙預(yù)覽,若沒有圖紙預(yù)覽就沒有圖紙。
  • 4. 未經(jīng)權(quán)益所有人同意不得將文件中的內(nèi)容挪作商業(yè)或盈利用途。
  • 5. 人人文庫網(wǎng)僅提供信息存儲空間,僅對用戶上傳內(nèi)容的表現(xiàn)方式做保護處理,對用戶上傳分享的文檔內(nèi)容本身不做任何修改或編輯,并不能對任何下載內(nèi)容負責。
  • 6. 下載文件中如有侵權(quán)或不適當內(nèi)容,請與我們聯(lián)系,我們立即糾正。
  • 7. 本站不保證下載資源的準確性、安全性和完整性, 同時也不承擔用戶因使用這些下載資源對自己和他人造成任何形式的傷害或損失。

最新文檔

評論

0/150

提交評論