精通SQL結(jié)構(gòu)化查詢語言詳解

1、關(guān)系：是匯集在表結(jié)構(gòu)中的行和列的集合，每個關(guān)系由一個或多個屬性組成關(guān)系，屬性和數(shù)組這3個術(shù)語在涉及到關(guān)系模型時(shí)使用，SQL在描述這些術(shù)語時(shí)，使用的是表，列和行聯(lián)系指實(shí)體間對應(yīng)關(guān)系三范式：1. 每一列都是不可分的基本數(shù)據(jù)項(xiàng)，同一列中不能有多個值，每一行只包含一個實(shí)例的信息，簡言之，就是無重復(fù)的列2. 屬性完全依賴于主關(guān)鍵字，3. 一個數(shù)據(jù)表中不包含已在其它表中已包含的非主關(guān)鍵字信息SQL組成1. 數(shù)據(jù)定義語言（Data Definition Language），用于創(chuàng)建，修改，刪除數(shù)據(jù)庫對象，SQL關(guān)鍵字包括CREATE，ALTER，DROP2. 數(shù)據(jù)查詢語言（Data Query Langua

2、ge），用于檢索數(shù)據(jù)，主要SQL關(guān)鍵字為SELECT3. 數(shù)據(jù)操縱語言（Data Manipulation Language），用于增，刪，改數(shù)據(jù)庫對象中的數(shù)據(jù)，主要SQL關(guān)鍵字INSERT，UPDATE和REVOKE4. 數(shù)據(jù)控制語言（Data Control Language），用于控制用戶對數(shù)據(jù)庫的訪問，主要SQL關(guān)鍵字GRANT，DENY 和REVOKE5. 其它語言要素，事務(wù)控制，程序化語言= 表 =創(chuàng)建表增加列刪除列：有默認(rèn)值，作為主鍵不能被刪除，具有非空約束的列可以被刪除處理辦法：先刪除約束，再刪除列修改列：TEXT，IMAGE，NTEXT，TIMESTAMP，索引的一部分，是P

3、RIMARY KEY或FOREIGN KEY，有缺省值，有CHECK，UNIQUE時(shí)不能改變其類型重命名表重命名列= 數(shù)據(jù)庫 =創(chuàng)建數(shù)據(jù)庫創(chuàng)建區(qū)分大小寫的列（sql server默認(rèn)不區(qū)分大小寫）= 索引 =索引：一個表的存儲是由兩部分存放的，一部分用來存儲表的數(shù)據(jù)頁面，另一部分存儲索引頁面，索引就存放在索引頁面上。通常，索引面頁相對于數(shù)據(jù)頁面來說小得多，當(dāng)進(jìn)行數(shù)據(jù)檢索時(shí)，系統(tǒng)先搜索索引頁面，從中找到所需要數(shù)據(jù)的指針，再通過指針從數(shù)據(jù)頁面中讀取數(shù)據(jù)簇索引：簇索引與數(shù)據(jù)是混為一體的非簇索引：非簇索引具有與表的數(shù)據(jù)完全分離的結(jié)構(gòu)，由于非簇索引使用索引頁存儲，因此比簇索引需要更多的空間，且檢索效率低

4、創(chuàng)建索引索引改名和刪除= 完整性控制 =完整性約束分3種類型：1. 與表有關(guān)的約束：是在創(chuàng)建有的語句中定義的一種約束，在定義列同時(shí)定義該約束稱為列約束；在列定義之后，單獨(dú)定義多個列的約束稱為表約束上面在name列后面的是列約束，最后一行是表約束，它們都可以實(shí)施primary key，foreign key，unique，check約束，除此之外，列約束還可以實(shí)施not null約束，也可以這樣實(shí)現(xiàn)如果希望刪除主表中數(shù)據(jù)時(shí)同時(shí)刪除子表中相關(guān)數(shù)據(jù)時(shí)可采用2. 域約束(只能使用check約束)：域可以代替數(shù)據(jù)類型使用，如但是在sql server中不支持create domain，不過可以通過給列設(shè)

5、置check約束實(shí)現(xiàn)3. 斷言(只能使用check約束)：sql server2k不支持4. sql server中的完整性控制，包括規(guī)則，缺省值，觸發(fā)器規(guī)則是對表中的列或自定義數(shù)據(jù)類型的限制，規(guī)則是單獨(dú)存儲的，與表對象無關(guān)，即表刪除不會對規(guī)則產(chǎn)生影響，一個規(guī)則可應(yīng)用多個列，一個列最多只能應(yīng)用一個規(guī)則缺省值：性質(zhì)同上= 查詢 =自然連接，自動判斷相同的列，匹配連接（不靈活，不能指定匹配列）CASE WHEN 語句左外連接 = 內(nèi)連接 + 左邊表中不匹配的行右外連接 = 內(nèi)連接 + 右邊表中不匹配的行全外連接 = 內(nèi)連接 + 左邊表中不匹配的行 + 右邊表中不匹配的行不管是哪種連接，DBMS總是

6、一次執(zhí)行兩個表的聯(lián)合，因此為了在單一查詢中連接個或以上表，就需要進(jìn)行多次連接，如：查詢所有教師的姓名，所在系，開設(shè)的課程名以及選修這門課的學(xué)生姓名和成績。總是把前面的結(jié)果和后面的表進(jìn)行連接交叉連接等同于from后的表用逗號隔開，都是返回兩個表中所有行的迪卡爾積UNION可用于合并查詢結(jié)果，自動消除重復(fù)行，如果不希望消除重復(fù)，必須使用UNION ALL可查不同表中的數(shù)據(jù)，但列數(shù)，類型要匹配，還可以對最后的結(jié)果進(jìn)行排序（列的序號也可）注意：多表進(jìn)行UNION運(yùn)算時(shí)，（A UNION ALL B）UNION C和A UNION ALL（B UNION C）結(jié)果可能不同，括號可改變連接順序UNION

7、JOIN把來自一個源表中的行與另一個源表中的行聯(lián)合起來，生成的結(jié)果中包括第一個表中的所有行和列和另一個表中的所有行和列。缺少的屬性值用NULL表示（SQL SERVER不支持）ROLLUP運(yùn)算符，對分組進(jìn)行擴(kuò)展CUBE運(yùn)算符，對分組進(jìn)行擴(kuò)展EXISTS子查詢：用來測試集合是否為空，它總與子查詢一起使用，而且只要子查詢中至少返回一個值，則EXISTS判式的值為TRUE，否則為FALSEUNIQUE子查詢：用來測試子查詢的結(jié)果表是否存在重復(fù)行，若沒有重復(fù)行，則返回TRUE，否則返回FALSE，NOT UNIQUE正好相反，不過SQL SERVER不支持子查詢的執(zhí)行要依賴于上一層查詢行的當(dāng)前值，這種

8、子查詢稱為相關(guān)子查詢，如EXISTS= 存儲過程與函數(shù) =存儲過程的優(yōu)點(diǎn)：允許組件式編程，較快的執(zhí)行速度，減少網(wǎng)絡(luò)流量，安全常用流程控制語句：DECLARE用來定義一個局部變量，可用SELECT語句賦值，這個變量必須以開頭，跟著一個標(biāo)識符，如下：全局變量：冠以 的變量，由系統(tǒng)定義，常見的有 error ：當(dāng)事務(wù)成功后，其值為0，否則為由系統(tǒng)產(chǎn)生的，最近的一個錯誤號（服務(wù)器：消息 后面的值）textsize ：select語句返回的text或image數(shù)據(jù)的字節(jié)數(shù)GOTO lable無條件轉(zhuǎn)到用戶定義的標(biāo)號處RETURN int_value ：無條件的退出一個查詢或一個過程，其中可選整數(shù)表達(dá)式返

9、回給調(diào)用者一個狀態(tài)值WAITFOR ：用來定義某天中的一個時(shí)刻，執(zhí)行一個語句塊print 顯示最長255個字符的消息函數(shù) ：只能返回單一值或表，此外，函數(shù)不能執(zhí)行增刪改操作標(biāo)量型函數(shù)內(nèi)聯(lián)表值型函數(shù)多聲明表值型函數(shù)位非運(yùn)算 ：要求一個負(fù)數(shù)的二進(jìn)制表示方式，可以先求出它的原碼，然后每位取反得出它的反碼，反碼再加1得出補(bǔ)碼如：假設(shè)還是以8位二進(jìn)制來表示帶符號的整數(shù)-56的原碼是：00111000那么它的反碼是：11000111補(bǔ)碼為：11000111+1=11001000得出，-56在計(jì)算機(jī)內(nèi)部是用11001000這串二進(jìn)制來表示的= 觸發(fā)器 =觸發(fā)器是一種特殊的存儲過程，它在表的數(shù)據(jù)變化時(shí)發(fā)生作用

10、，觸發(fā)器可以維護(hù)數(shù)據(jù)完整性與存儲過程的區(qū)別：存儲過程通過其它程序啟動運(yùn)行，而觸發(fā)器由一個事件來啟動運(yùn)行，并且不能接收參數(shù)SQL支持三種類型的觸發(fā)器：INSERT，DELETE，UPDATE。當(dāng)向表中插入數(shù)據(jù)，更新數(shù)據(jù)或刪除數(shù)據(jù)時(shí)，觸發(fā)器就被調(diào)用，通過給表定義一個或多個觸發(fā)器，可以指定哪個數(shù)據(jù)修改時(shí)，可以激發(fā)觸發(fā)器執(zhí)行環(huán)境：包括一到兩個稱為測試表的虛表，用于保存目標(biāo)表更新，插入和刪除的數(shù)據(jù)信息。用戶不能直接修改測試表中的數(shù)據(jù)，但能在SELECT語句中使用這些表來檢測INSERT，DELETE和UPDATE的結(jié)果，各種類型觸發(fā)器用到的測試表如下：² Inserted表存入了INSERT和

11、UPDATE語句中的副本。在INSERT或UPDATE語句的執(zhí)行中，這些新行同時(shí)被加到Inserted表和trigger表中。Inserted表中的行是trigger表中新行的副本v 也就是更新和插入操作中，把新行同時(shí)復(fù)制到原表和Inserted表中² Deleted表存放了Delete和Update語句中相關(guān)行的副本。在DELETE或UPDATE語句的執(zhí)行中，這些相關(guān)行從trigger表中移到了Deleted表中。一般情況下，這兩張表中無共同行v 也就是更新和刪除操作中，把原表中刪除的行剪切到deleted表中² 一個UPDATE效果上等價(jià)于一個DELETE再接著一個IN

12、SERT。首先“舊”行被復(fù)制到deleted表中，然后新行被復(fù)制到trigger表和inserted表中INSERT觸發(fā)器DELETE觸發(fā)器UPDATE觸發(fā)器 INSTEAD OF 觸發(fā)器僅執(zhí)行觸發(fā)器本身，而不執(zhí)行具體的SQL語句（INSTEAD OF 的意思）= 事務(wù)控制與并發(fā)處理 =事務(wù)的特性1 原子性：指事務(wù)全有或全無的性質(zhì)。也就是說，事務(wù)就作為一個工作單元，事務(wù)處理完成后，所有的工作要么都在數(shù)據(jù)庫中保留下來，要么完全回滾，全部不保留2 一致性：事務(wù)完成或撤消后，應(yīng)處理一致的狀態(tài)，即事務(wù)中任何數(shù)據(jù)的變化符合數(shù)據(jù)定義的規(guī)則。另外，數(shù)據(jù)結(jié)束后，數(shù)據(jù)庫內(nèi)所有結(jié)構(gòu)都必須正確。3 隔離性：多個事務(wù)

13、同時(shí)進(jìn)行，它們之間應(yīng)該互不干擾，事務(wù)過程中暫時(shí)不一致的數(shù)據(jù)不能被其它事務(wù)應(yīng)用，直到數(shù)據(jù)再次一致4 永久性：一旦事務(wù)引發(fā)了變化，事務(wù)提交以后，所做的工作就被永久的保存下來，即使硬件和應(yīng)用程序發(fā)生錯誤，這些數(shù)據(jù)也會可靠一致SQL中使用事務(wù)的一般過程如為：首先通過SET TRANSACTION 設(shè)置事務(wù)的屬性（主要用于并發(fā)控制），再由START TRANSACTION語句開始事務(wù)。事務(wù)結(jié)束可以有兩種方式：由COMMIT語句提交事務(wù)和由ROLLBACK語句回滾事務(wù)COMMIT提交事務(wù)：該語句將成功地結(jié)束當(dāng)前事務(wù)，并永久的保存事務(wù)中對于數(shù)據(jù)庫的所有改變。而在未提交事務(wù)以前，這些改變是暫時(shí)的，一般情況下，

14、在其他事務(wù)中不能看到本事務(wù)中對數(shù)據(jù)庫的修改ROLLBACK回滾事務(wù)：該語句也結(jié)束當(dāng)前事務(wù)，但他當(dāng)撤消該事務(wù)所做的全部工作，即事務(wù)中對于數(shù)據(jù)庫的任何修改都將無效，回滾一般是指回到事務(wù)開始時(shí)的狀態(tài)，但也可以在事務(wù)中設(shè)置保存點(diǎn)，然后回到事務(wù)指定的保存點(diǎn)。保存點(diǎn)和ROLLBACK語句一起使用，可使當(dāng)前事務(wù)進(jìn)行部分回滾在SQL：1999定義中，事務(wù)即可以隱式開始，也可以顯式開始，所謂隱式開始就是當(dāng)執(zhí)行某些SQL語句時(shí)，事務(wù)自動開始，即這些語句必須在事務(wù)中執(zhí)行。而顯式事務(wù)就是通過START TRANSACTION語句來開始事務(wù)SQL SERVER中有3種開始事務(wù)的方式：隱式，顯式，自動提交（缺省）自動提交

15、：是指對于用戶發(fā)出的每條SQL語句，SQL SERVER都會自動開始一個事務(wù)，并且在執(zhí)行后自動進(jìn)行提交操作來完成這個事務(wù)。可以說，在這種事務(wù)模式下，一個SQL語句就是一個事務(wù)顯示提交：以BEGIN TRANSACTION命令開始一個事務(wù)隱式提交：是指在當(dāng)前會話中，通過SET IMPLICIT_TRANSACTION ON命令設(shè)置事務(wù)類型，這時(shí)，任何DML語句（DELETE，UPDATE，INSERT）事務(wù)都會開始一個事務(wù)，而事務(wù)的結(jié)束也是使用COMMIT或ROLLBACK延期約束：SQL SERVER不支持，指事務(wù)中的每條語句都必須立即應(yīng)用約束，還是在事務(wù)結(jié)束后再應(yīng)用約束使用ROLLBACK

16、SAVE_POINT語句回到保存點(diǎn)時(shí)，事務(wù)并不終止（意味著這種語句可以和COMMIT一起被執(zhí)行，而ROLLBACK和COMMIT只能執(zhí)行其一，因?yàn)槿我豢梢越Y(jié)束事務(wù)，再執(zhí)行另一個會提示事務(wù)并沒有開始）事務(wù)隔離級別（以下開銷越來越大，但出錯的可能越來越小）READ UNCOMMITTED未提交的讀?。合拗谱钌?，它允許讀取已經(jīng)被其它用戶修改但尚未提交的數(shù)據(jù)，用于結(jié)果不要求太精確的統(tǒng)計(jì)數(shù)據(jù)，開銷少READ COMMITTED提交讀?。和ㄟ^隱藏未提交的變化解決了讀取“臟數(shù)據(jù)”的問題，雖然READ COMMITTED消除了讀取未提交數(shù)據(jù)，但并不能防止不可重復(fù)的讀取和幻影讀REPEATABLE READ可重

17、復(fù)讀取：用SELECT命令讀取的數(shù)據(jù)在整個命令執(zhí)行過程中由DBMS實(shí)施鎖定，不會被更改SERIALIZABLE可串行化：指的是將事務(wù)以一種順序方式連接起來，防止一個事務(wù)影響其它事務(wù)總結(jié)：READ UNCOMMITTED可以讀取其它用戶未提交的數(shù)據(jù)，READ COMMITTED可以讀取已提交的數(shù)據(jù)，REPEATABLE READ鎖定表，其它用戶同時(shí)不允許更新數(shù)據(jù)（可以插入），SERIALIZABLE不允許插入在SQL SERVER中，從數(shù)據(jù)庫角度鎖可以分為：1 共享鎖：共享鎖鎖定的資源可以被其它用戶讀取，但不能修改。在SELECT命令執(zhí)行時(shí)，SQL SERVER通常會對對象進(jìn)行共享鎖定。一般情況下，加共享鎖的數(shù)據(jù)頁被讀取完畢后，共享鎖就立即被釋放2 獨(dú)占鎖：獨(dú)占鎖鎖定的資源只允許進(jìn)行鎖定操作的程序使用，對它的任何其它操作都被拒絕。執(zhí)行數(shù)據(jù)更新命令，如INSERT，UPDATE，DELETE命令時(shí)，SQL SERVER會動使用獨(dú)占鎖。但當(dāng)對象上有其它鎖存在時(shí)，無法對其加獨(dú)占鎖。直到更新結(jié)束，獨(dú)占鎖才被釋放3 更新鎖：為防止死鎖而設(shè)定。當(dāng)SQL SERVER準(zhǔn)備更新數(shù)據(jù)庫時(shí)，它首先對數(shù)據(jù)庫對象做更新鎖鎖定，這樣數(shù)據(jù)將不能被修改，但可以