復(fù)雜sql語句和存儲過程

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

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

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

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

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

6、,DECODE,DUMP,NVL,REPLACE在調(diào)用了NULL參數(shù)時能夠返回非NULL值。在這些中NVL函數(shù)時最重要的，因為他能直接處理NULL值，NVL有兩個參數(shù)：NVL(x1,x2),x1和x2都式表達式，當x1為null時返回X2,否則返回x1,簡單的數(shù)據(jù)查詢,邏輯操作符 AND 兩個條件都必須滿足 OR 只要滿足兩個條件中的一個 NOT 取反 優(yōu)先（例）,簡單的數(shù)據(jù)查詢,操作數(shù)字型數(shù)據(jù) SELECT FUNETION_NAME(PARAMETERS) FUNCTION 參數(shù) 結(jié)果 1.絕對值 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,簡單的數(shù)據(jù)查詢,字符串處理 SUBSTRING，SUBSTR （區(qū)別） CONCAT() 字符串可以做最簡單的“加”法，即合并運算：| 例： SELECT column1|.|column2 |$ FROM tablename STUFF() Replace() Len,datalength length lower,upper ltrim,rtrim ltrim,rtrim,trim,簡單的數(shù)據(jù)查詢,操作日期型數(shù)據(jù) 系統(tǒng)時間 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ù)相加減 求時間 S:select convert(char(8),getdate(),108) value O:select to_char(sysdate,hh24:mm:ss) value from dual,簡單的數(shù)據(jù)查詢,空處理 NVL: 在數(shù)據(jù)列的值為空時（并非為零），以

10、真實的值進行替代處理 例： NVL (base_salary, 2000) 注：任何未被聲明為NOT NULL 或PRIMARY KEY 的列都可包含空值，注意，空值就是空值，并不是值為零或空格，零是一個數(shù)值，而空格是一個字符。,簡單的數(shù)據(jù)查詢,數(shù)據(jù)類型轉(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,簡單的數(shù)據(jù)查詢,子查詢 in,exists not in, not exists,復(fù)雜sql語句,結(jié)果的并、交、差運算 union union all UNION 會去掉結(jié)果集中的重復(fù)記錄，而 UNION ALL 會返回合并后的全部結(jié)果集。 (a union b a中重復(fù)記錄也去掉) Intersect minus （例）,復(fù)雜sql語句,集合函數(shù) AVG求平均值 （整型） COUNT值的計數(shù) MAX求最大值 （字符串） MIN求最小值 SUM求和,復(fù)雜sql語句,分組 group by,having GROUP BY的特征：能按列或表達式分組,一般同集合函數(shù)一起用,為每組產(chǎn)生一個值 ro

12、llup,cube 對于GROUP BY語句來說，只能返回對于每個GROUP BY分組的匯總數(shù)據(jù)。使用ROLLUP除了返回分組匯總數(shù)據(jù)外，還可以返回對于分組匯總的匯總值。CUBE操作除了提供ROLLUP的功能外，還可以對統(tǒng)計的其他維度進行匯總計算。 grouping 函數(shù) （例子） compute by 可以統(tǒng)計排序中結(jié)果完全相同的列，統(tǒng)計值作為查詢結(jié)果以附加行的形式顯示,復(fù)雜sql語句,多表查詢 自連接查詢，對同一個表進行連接操作 內(nèi)連接查詢， 外連接查詢， 交叉連接查詢，也作無條件查詢。 聯(lián)合查詢,復(fù)雜sql語句,自連接查詢 一個表自己與自己建立連接稱為自連接或自身連接 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語句,內(nèi)連接(INNER JOIN) 它只返回兩個數(shù)據(jù)集合之間匹配關(guān)系的那些行.將位于兩個互相交叉的數(shù)據(jù)集合中重疊部分以內(nèi)的那些數(shù)據(jù)行連接起來 select 字段名列表 from 表名 inner join 表名 on 連接條件 where 條件表達式 等值連接 不等值連接 自然連接,復(fù)雜sql語句,外連接(OUTER JOIN) 左外連接(LEFT OUTER JOI

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

15、ORACLE declare 變量聲明； begin 語句部分 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語句 end else if begin SQL語句 end else begin SQL語句 End,Sql編程,oracle if then end if; if then else End if; if then elsif then end if; case語句,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語句 case nwh

17、en 1 then Action1; when 2 then Action2; when 3 then Action3; else ActionOther;end case; Case -CASE 表達式 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 語句 declare count int set count = 0 while(count 10) begin set count = count + 1 print count end 在循環(huán)中常用的語句有break和continue, break為跳出while,而continue為跳出當前循環(huán)，進入下一循環(huán)。 有時候也用到return和goto語句 Oracle PL/SQL EXIT 關(guān)鍵字等同。Oracle 沒有 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編程,游標 定義 屬性 打開 提取 關(guān)閉 參數(shù)化游標 游標變量,Sql編程,游標定義 sql DECLARE cursor_name INSENSITIVE SCROLL CURSOR FOR select_statement FOR READ ONLY | UPDATE OF column_name ,.n INSENSITIVE對基本表的修改并不影響游標提取的數(shù)據(jù),默認可修改 SCROLL表明所有的提取操

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

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

23、nto zffsdm -開始抓第一條數(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)閉游標 deallocate mycursor -刪除游標,Sql編程,Oracle set serveroutput on declare szzffsdm varchar2

24、(20); -定義變量來保存ID號 szdm varchar2(20); szmc varchar2(20); cursor mycursor is select zffsdm from zb_zffs where kjnd=2008 and gsdm=111001 ; -為所獲得的數(shù)據(jù)集指定游標 begin open mycursor; -打開游標 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ù)化游標（oracle才有） 聲明 cursor c_KMXX( cKMXZ char) is select kmdm,kmmc from gl_kmxx where kmxz= cKMXZ; 打開 open c_KMXX(1);,Sql編程,事務(wù)控制 事務(wù)的概念 事務(wù)可以看作是由對數(shù)據(jù)庫的若干操作組成的一個單元，這些操作要么都完成，要么都取消，從而保證

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

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

28、說，DDL會象如下偽碼一樣執(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ù)時DDL語句對事務(wù)的影響與其他DML語句相同，也就是說，在DML語句發(fā)出之前或之后，都不會自動發(fā)出Commit命令。 用戶斷開數(shù)據(jù)庫連接對事務(wù)的影響 Ora是commit，sql是rollback隱式有影響,Sql編程,異常處理 oracle 聲明 系統(tǒng)預(yù)定義異常 如：被0除，約束破壞，數(shù)據(jù)類型轉(zhuǎn)換失敗等 用戶自定義異常 declare epError Exception; Sqlserver2005 try. cache.方式,Sql編程,異常的拋出 用戶自定義的異常由raise語句觸發(fā) 如：raise epError; 系統(tǒng)預(yù)定義的異常自動觸發(fā) 處理 Exception when 異常名1 then 處理語句序列; when 異常名2 then 處理語句序列; when others then 處理語句序列;,S