1、SQL語(yǔ)句,一、簡(jiǎn)單sql語(yǔ)句 二、復(fù)雜sql語(yǔ)句 三、綜合應(yīng)用 四、SQL編程 五、存儲(chǔ)過(guò)程,內(nèi)容大綱,基本結(jié)構(gòu) SELECT distinct *, column alias, FROM TABLE ORDER BY column SELECT: 選擇要查詢(xún)的數(shù)據(jù)列 *: 選擇所有的列 COLUMN:選擇的列名 FROM: 數(shù)據(jù)列所在的表 ALIAS: 給選定的列或表一個(gè)別名（sql和oracle區(qū)別）,簡(jiǎn)單的數(shù)據(jù)查詢(xún),查詢(xún)結(jié)果排序 使用ORDER BY子句對(duì)查詢(xún)返回的結(jié)果按一列或多列排序。ORDER BY子句的語(yǔ)法格式為：ORDER BY column_name ASC|DESC ,n其

2、中ASC表示升序，為默認(rèn)值，DESC為降序。ORDER BY不能按ntext、text和image數(shù)據(jù)類(lèi)型進(jìn)行排序。例如：Select *FROM usertableORDER BY age desc,userid ASC另外，可以根據(jù)表達(dá)式進(jìn)行排序。,簡(jiǎn)單的數(shù)據(jù)查詢(xún),distinct 刪除所有重復(fù)的值，排序操作增加系統(tǒng)開(kāi)銷(xiāo)。注意 查表的前N行記錄 select top n * from table_name select * from table_name where rownumn 查詢(xún)表結(jié)構(gòu) desc table_name exec sp_help table_name,簡(jiǎn)單的數(shù)據(jù)查詢(xún),

3、DML語(yǔ)句 插入：INSERT 更新：UPDATE 刪除：DELETE,簡(jiǎn)單的數(shù)據(jù)查詢(xún),DDL語(yǔ)句 Create Alter Drop Truncate (區(qū)別，立即提交、不回滾) 數(shù)據(jù)定義語(yǔ)言DDL用來(lái)創(chuàng)建數(shù)據(jù)庫(kù)中的各種對(duì)象 -表、視圖、索引、同義詞、聚簇等如 CREATE TABLE/VIEW/INDEX/SYN/CLUSTER,簡(jiǎn)單的數(shù)據(jù)查詢(xún),DQL語(yǔ)言 DCL 數(shù)據(jù)控制語(yǔ)言DCL用來(lái)授予或回收訪問(wèn)數(shù)據(jù)庫(kù)的某種特權(quán)，并控制 數(shù)據(jù)庫(kù)操縱事務(wù)發(fā)生的時(shí)間及效果，對(duì)數(shù)據(jù)庫(kù)實(shí)行監(jiān)視等。如： 1) GRANT：授權(quán)。 2) ROLLBACK WORK TO SAVEPOINT：回退到某一點(diǎn)。 回滾-

4、ROLLBACK 回滾命令使數(shù)據(jù)庫(kù)狀態(tài)回到上次最后提交的狀態(tài)。其格式為： SQLROLLBACK; 3) COMMIT WORK：提交。 在數(shù)據(jù)庫(kù)的插入、刪除和修改操作時(shí)，只有當(dāng)事務(wù)在提交到數(shù)據(jù) 庫(kù)時(shí)才算完成。在事務(wù)提交前，只有操作數(shù)據(jù)庫(kù)的這個(gè)人才能有權(quán)看 到所做的事情，別人只有在最后提交完成后才可以看到。,簡(jiǎn)單的數(shù)據(jù)查詢(xún),操作符 算術(shù)操作符 關(guān)系操作符 比較操作符 邏輯操作符,簡(jiǎn)單的數(shù)據(jù)查詢(xún),算術(shù)操作符 加：+ 減：- 乘：* 除：/ Sql：% 取模 oracle：Mod,簡(jiǎn)單的數(shù)據(jù)查詢(xún),關(guān)系操作符 主要用于條件判斷語(yǔ)句或用于where子串中，關(guān)系操作符檢查條件和結(jié)果是否為true或fal

5、se 大于操作符 = 大于或等于操作符 = 等于操作符 != 不等于操作符 := 賦值操作符 （oracle） 日期、字符串比較例子,簡(jiǎn)單的數(shù)據(jù)查詢(xún),比較操作符 IS NULL 如果操作數(shù)為NULL返回TRUE LIKE 比較字符串值 (通配符，走索引？) BETWEEN 驗(yàn)證值是否在范圍之內(nèi) IN,NOT IN,EXISTS,NOT EXISTS驗(yàn)證操作數(shù)在設(shè)定的一系列值中 在如何理解NULL上開(kāi)始是很困難的，就算是一個(gè)很有經(jīng)驗(yàn)的人依然對(duì)此感到困惑。NULL值表示一個(gè)未知數(shù)據(jù)或者一個(gè)空值，算術(shù)操作符的任何一個(gè)操作數(shù)為NULL值，結(jié)果均為提個(gè)NULL值,這個(gè)規(guī)則也適合很多函數(shù)，只有CONCAT

6、,DECODE,DUMP,NVL,REPLACE在調(diào)用了NULL參數(shù)時(shí)能夠返回非NULL值。在這些中NVL函數(shù)時(shí)最重要的，因?yàn)樗苤苯犹幚鞱ULL值，NVL有兩個(gè)參數(shù)：NVL(x1,x2),x1和x2都式表達(dá)式，當(dāng)x1為null時(shí)返回X2,否則返回x1,簡(jiǎn)單的數(shù)據(jù)查詢(xún),邏輯操作符 AND 兩個(gè)條件都必須滿(mǎn)足 OR 只要滿(mǎn)足兩個(gè)條件中的一個(gè) NOT 取反 優(yōu)先（例）,簡(jiǎn)單的數(shù)據(jù)查詢(xún),操作數(shù)字型數(shù)據(jù) SELECT FUNETION_NAME(PARAMETERS) FUNCTION 參數(shù) 結(jié)果 1.絕對(duì)值 S:select abs(-1) value O:select abs(-1) value

7、from dual 2.取整(大) S:select ceiling(-1.001) value O:select ceil(-1.001) value from dual 3.取整（小） S:select floor(-1.001) value O:select floor(-1.001) value from dual 4.取整（截取） S:select cast(-1.002 as int) value O:select trunc(-1.002) value from dual 5.四舍五入 S:select round(1.23456,4) value 1.23460O:select

8、 round(1.23456,4) value from dual,簡(jiǎn)單的數(shù)據(jù)查詢(xún),字符串處理 SUBSTRING，SUBSTR （區(qū)別） CONCAT() 字符串可以做最簡(jiǎn)單的“加”法，即合并運(yùn)算：| 例： SELECT column1|.|column2 |$ FROM tablename STUFF() Replace() Len,datalength length lower,upper ltrim,rtrim ltrim,rtrim,trim,簡(jiǎn)單的數(shù)據(jù)查詢(xún),操作日期型數(shù)據(jù) 系統(tǒng)時(shí)間 S:select getdate() value O:select sysdate value fr

9、om dual 求日期 S:select convert(char(10),getdate(),20) value O:select trunc(sysdate) value from dual select to_char(sysdate,yyyy-mm-dd) value from dual 前后幾日 直接與整數(shù)相加減 求時(shí)間 S:select convert(char(8),getdate(),108) value O:select to_char(sysdate,hh24:mm:ss) value from dual,簡(jiǎn)單的數(shù)據(jù)查詢(xún),空處理 NVL: 在數(shù)據(jù)列的值為空時(shí)（并非為零），以

10、真實(shí)的值進(jìn)行替代處理 例： NVL (base_salary, 2000) 注：任何未被聲明為NOT NULL 或PRIMARY KEY 的列都可包含空值，注意，空值就是空值，并不是值為零或空格，零是一個(gè)數(shù)值，而空格是一個(gè)字符。,簡(jiǎn)單的數(shù)據(jù)查詢(xún),數(shù)據(jù)類(lèi)型轉(zhuǎn)換 CAST ( expression AS data_type ) CONVERT (data_type(length), expression , style) (r9) CHAR | TO_DATE TO_NUMBER DATE | TO_CHAR NUMBER | TO_CHAR TO_DATE HEXTORAW CHARTOROWI

11、D,簡(jiǎn)單的數(shù)據(jù)查詢(xún),子查詢(xún) in,exists not in, not exists,復(fù)雜sql語(yǔ)句,結(jié)果的并、交、差運(yùn)算 union union all UNION 會(huì)去掉結(jié)果集中的重復(fù)記錄，而 UNION ALL 會(huì)返回合并后的全部結(jié)果集。 (a union b a中重復(fù)記錄也去掉) Intersect minus （例）,復(fù)雜sql語(yǔ)句,集合函數(shù) AVG求平均值 （整型） COUNT值的計(jì)數(shù) MAX求最大值 （字符串） MIN求最小值 SUM求和,復(fù)雜sql語(yǔ)句,分組 group by,having GROUP BY的特征：能按列或表達(dá)式分組,一般同集合函數(shù)一起用,為每組產(chǎn)生一個(gè)值 ro

12、llup,cube 對(duì)于GROUP BY語(yǔ)句來(lái)說(shuō)，只能返回對(duì)于每個(gè)GROUP BY分組的匯總數(shù)據(jù)。使用ROLLUP除了返回分組匯總數(shù)據(jù)外，還可以返回對(duì)于分組匯總的匯總值。CUBE操作除了提供ROLLUP的功能外，還可以對(duì)統(tǒng)計(jì)的其他維度進(jìn)行匯總計(jì)算。 grouping 函數(shù) （例子） compute by 可以統(tǒng)計(jì)排序中結(jié)果完全相同的列，統(tǒng)計(jì)值作為查詢(xún)結(jié)果以附加行的形式顯示,復(fù)雜sql語(yǔ)句,多表查詢(xún) 自連接查詢(xún)，對(duì)同一個(gè)表進(jìn)行連接操作 內(nèi)連接查詢(xún)， 外連接查詢(xún)， 交叉連接查詢(xún)，也作無(wú)條件查詢(xún)。 聯(lián)合查詢(xún),復(fù)雜sql語(yǔ)句,自連接查詢(xún) 一個(gè)表自己與自己建立連接稱(chēng)為自連接或自身連接 select x.

13、* from sclass x,sclass y where o=101 and x.degreey.degree and y.sno=9505201 and o=101 order by x.degree desc,復(fù)雜sql語(yǔ)句,內(nèi)連接(INNER JOIN) 它只返回兩個(gè)數(shù)據(jù)集合之間匹配關(guān)系的那些行.將位于兩個(gè)互相交叉的數(shù)據(jù)集合中重疊部分以?xún)?nèi)的那些數(shù)據(jù)行連接起來(lái) select 字段名列表 from 表名 inner join 表名 on 連接條件 where 條件表達(dá)式 等值連接 不等值連接 自然連接,復(fù)雜sql語(yǔ)句,外連接(OUTER JOIN) 左外連接(LEFT OUTER JOI

14、N或LEFT JOIN) 右外連接(RIGHT OUTER JOIN或RIGHT JOIN) 全外連接(FULL OUTER JOIN或FULL JOIN) 替換in，not in (例),復(fù)雜sql語(yǔ)句,交叉連接 交叉連接不使用任何連接條件來(lái)限制結(jié)果集合,將各表的記錄以“笛卡爾”積的方式組合起來(lái) select * from student,sclass select * from student a CROSS JOIN sclass ORDER BY a.sno,復(fù)雜sql語(yǔ)句,復(fù)制表結(jié)構(gòu) 行列轉(zhuǎn)換 找含匹配列的行 創(chuàng)建復(fù)雜的視圖,綜合應(yīng)用,程序段 SQLSERVER BEGIN END

15、ORACLE declare 變量聲明； begin 語(yǔ)句部分 EXCEPTION -exception statements end;,Sql編程,控制結(jié)構(gòu) 順序結(jié)構(gòu) 選擇結(jié)構(gòu) 循環(huán)結(jié)構(gòu),Sql編程,選擇結(jié)構(gòu) sqlserver if 條件 begin SQL語(yǔ)句 end else if begin SQL語(yǔ)句 end else begin SQL語(yǔ)句 End,Sql編程,oracle if then end if; if then else End if; if then elsif then end if; case語(yǔ)句,Sql編程,例子： declare szbz int set sz

16、bz=3 if szbz=1 select szbz=1 else if szbz=2 select szbz=2 else select szbz=3,Sql編程,set serveroutput on declare szbz int; begin szbz:=3; if szbz=1 then dbms_output.put_line(szbz=1); elsif szbz=2 then dbms_output.put_line(szbz=2); else dbms_output.put_line(szbz=3); end if; end; /,Sql編程,case語(yǔ)句 case nwh

17、en 1 then Action1; when 2 then Action2; when 3 then Action3; else ActionOther;end case; Case -CASE 表達(dá)式 when n = 1 then Action1;when n = 2 then Action2;when n = 3 then Action3;when ( n 3 and n 8 ) then Action4;else ActionOther;end case;,Sql編程,CASE_NOT_FOUND 異常 .p:=0; q:=0; r:=0; case when p = 1 then

18、Action1; when r = 2 then Action2; when q 1 then Action3; end case; exception when case_not_found then Dbms_Output.Put_Line ( Trapped:case_not_found );.,Sql編程,例子 declare i int; begin i:=2; case i when 0 then dbms_output.put_line(i=0); when 1 then dbms_output.put_line(i=1); else dbms_output.put_line(i

19、=2); end case; end; /,Sql編程,循環(huán)結(jié)構(gòu) sql while 語(yǔ)句 declare count int set count = 0 while(count 10) begin set count = count + 1 print count end 在循環(huán)中常用的語(yǔ)句有break和continue, break為跳出while,而continue為跳出當(dāng)前循環(huán)，進(jìn)入下一循環(huán)。 有時(shí)候也用到return和goto語(yǔ)句 Oracle PL/SQL EXIT 關(guān)鍵字等同。Oracle 沒(méi)有 CONTINUE 關(guān)鍵字,Sql編程,ORACLE loop if then exi

20、t; end loop; loop exit when ; end loop; while loop end loop; for 變量 in 下界上界 loop end loop;,Sql編程,游標(biāo) 定義 屬性 打開(kāi) 提取 關(guān)閉 參數(shù)化游標(biāo) 游標(biāo)變量,Sql編程,游標(biāo)定義 sql DECLARE cursor_name INSENSITIVE SCROLL CURSOR FOR select_statement FOR READ ONLY | UPDATE OF column_name ,.n INSENSITIVE對(duì)基本表的修改并不影響游標(biāo)提取的數(shù)據(jù),默認(rèn)可修改 SCROLL表明所有的提取操

21、作（如FIRST、 LAST、 PRIOR、 NEXT、 RELATIVE (+ -)、 ABSOLUTE(+ -)）都可用，如果不使用該保留字，那么只能進(jìn)行NEXT 提取操作,Sql編程,oracle cursor 游標(biāo)名 is select ; 屬性 %FOUND: 判斷當(dāng)前游標(biāo)是否指向有效的一條行記錄. 是則返回TRUE, 否則返回FALSE %NOTFOUND: 與%FOUND 恰好相反. %ISOPEN: 判斷游標(biāo)是否打開(kāi), 打開(kāi)則返回TRUE, 否則返回FALSE. %ROWCOUNT: 判斷當(dāng)前游標(biāo)在所指向的結(jié)果集中提取的行數(shù). 并非所有總記錄數(shù),Sql編程,游標(biāo)打開(kāi) open游

22、標(biāo)名; 提取游標(biāo) Fetch next from 游標(biāo)名 into 變量(sql) Fetch 游標(biāo)名 into 變量;(ora) 關(guān)閉游標(biāo) close 游標(biāo)名; deallocate游標(biāo)名 (sql用),Sql編程,例子 sql declare zffsdm nvarchar(20) -定義變量來(lái)保存ID號(hào) declare mycursor cursor for select zffsdm from zb_zffs where kjnd=2009 and gsdm=000 -為所獲得的數(shù)據(jù)集指定游標(biāo) open mycursor -打開(kāi)游標(biāo) fetch next from mycursor i

23、nto zffsdm -開(kāi)始抓第一條數(shù)據(jù) while(fetch_status=0) -如果數(shù)據(jù)集里一直有數(shù)據(jù) begin select zffsdm,zffsmc from zb_zffs where zffsdm = zffsdm and kjnd=2009 and gsdm=000 fetch next from mycursor into zffsdm -跳到下一條數(shù)據(jù) end close mycursor -關(guān)閉游標(biāo) deallocate mycursor -刪除游標(biāo),Sql編程,Oracle set serveroutput on declare szzffsdm varchar2

24、(20); -定義變量來(lái)保存ID號(hào) szdm varchar2(20); szmc varchar2(20); cursor mycursor is select zffsdm from zb_zffs where kjnd=2008 and gsdm=111001 ; -為所獲得的數(shù)據(jù)集指定游標(biāo) begin open mycursor; -打開(kāi)游標(biāo) loop fetch mycursor into szzffsdm; exit when mycursor%notfound ; select zffsdm,zffsmc into szdm,szmc from zb_zffs where zff

25、sdm = szzffsdm and kjnd=2008 and gsdm=111001; dbms_output.put_line(szdm| = |szmc); end loop ; close mycursor; end;,Sql編程,參數(shù)化游標(biāo)（oracle才有） 聲明 cursor c_KMXX( cKMXZ char) is select kmdm,kmmc from gl_kmxx where kmxz= cKMXZ; 打開(kāi) open c_KMXX(1);,Sql編程,事務(wù)控制 事務(wù)的概念 事務(wù)可以看作是由對(duì)數(shù)據(jù)庫(kù)的若干操作組成的一個(gè)單元，這些操作要么都完成，要么都取消，從而保證

26、數(shù)據(jù)滿(mǎn)足一致性的要求。事務(wù)的一個(gè)典型例子是銀行中的轉(zhuǎn)帳操作，帳戶(hù)A把一定數(shù)量的款項(xiàng)轉(zhuǎn)到帳戶(hù)B上，這個(gè)操作包括兩個(gè)步驟，一個(gè)是從帳戶(hù)A上把存款減去一定數(shù)量，二是在帳戶(hù)B上把存款加上相同的數(shù)量。這兩個(gè)步驟顯然要么都完成，要么都取消，否則銀行就會(huì)受損失。顯然，這個(gè)轉(zhuǎn)帳操作中的兩個(gè)步驟就構(gòu)成一個(gè)事務(wù)。,Sql編程,語(yǔ)法 oracle commit; rollback; savepoint 點(diǎn)名; rollback to savepoint 點(diǎn)名; sql Begin Transaction Commit Transaction | Rollback Transaction,Sql編程,事務(wù)設(shè)置及類(lèi)型的

27、區(qū)別sqlserver 隱式事務(wù)（Set Implicit_Transactions On命令設(shè)置的事務(wù)類(lèi)型 ） 顯式事務(wù) 自動(dòng)提交事務(wù) （默認(rèn)） Oracle中沒(méi)有SQL Server的這些事務(wù)類(lèi)型，缺省情況下任何一個(gè)DML語(yǔ)句都會(huì)開(kāi)始一個(gè)事務(wù)，直到用戶(hù)發(fā)出Commit或Rollback操作，這個(gè)事務(wù)才會(huì)結(jié)束，這與SQL Server的隱式事務(wù)模式相似。(鎖問(wèn)題),Sql編程,DDL語(yǔ)句對(duì)事務(wù)的影響 在Oracle中，執(zhí)行DDL語(yǔ)句(如Create Table、Create View等)時(shí)，會(huì)在執(zhí)行之前自動(dòng)發(fā)出一個(gè)Commit命令，并在隨后發(fā)出一個(gè)Commit或者Rollback命令，也就是

28、說(shuō)，DDL會(huì)象如下偽碼一樣執(zhí)行 Commit; DDL_Statement; If (Error) then Rollback; Else Commit; End if;,Sql編程,例子 Insert into some_table values(Before); Create table T(x int); Insert into some_table values(After); Rollback; 在SQL Server中，隱式事務(wù)時(shí)DDL語(yǔ)句對(duì)事務(wù)的影響與其他DML語(yǔ)句相同，也就是說(shuō)，在DML語(yǔ)句發(fā)出之前或之后，都不會(huì)自動(dòng)發(fā)出Commit命令。 用戶(hù)斷開(kāi)數(shù)據(jù)庫(kù)連接對(duì)事務(wù)的影響 Ora是commit，sql是rollback隱式有影響,Sql編程,異常處理 oracle 聲明 系統(tǒng)預(yù)定義異常 如：被0除，約束破壞，數(shù)據(jù)類(lèi)型轉(zhuǎn)換失敗等 用戶(hù)自定義異常 declare epError Exception; Sqlserver2005 try. cache.方式,Sql編程,異常的拋出 用戶(hù)自定義的異常由raise語(yǔ)句觸發(fā) 如：raise epError; 系統(tǒng)預(yù)定義的異常自動(dòng)觸發(fā) 處理 Exception when 異常名1 then 處理語(yǔ)句序列; when 異常名2 then 處理語(yǔ)句序列; when others then 處理語(yǔ)句序列;,S