精通SQL結(jié)構(gòu)化查詢(xún)語(yǔ)言詳解

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

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

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

4、創(chuàng)建索引索引改名和刪除= 完整性控制 =完整性約束分3種類(lèi)型：1. 與表有關(guān)的約束：是在創(chuàng)建有的語(yǔ)句中定義的一種約束，在定義列同時(shí)定義該約束稱(chēng)為列約束；在列定義之后，單獨(dú)定義多個(gè)列的約束稱(chēng)為表約束上面在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ù)類(lèi)型使用，如但是在sql server中不支持create domain，不過(guò)可以通過(guò)給列設(shè)

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

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

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

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

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

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

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

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

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

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

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

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

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