sqlserver知識總結(jié)

Sqlserver基礎(chǔ)：

數(shù)據(jù)庫的冗余就是數(shù)據(jù)就是數(shù)據(jù)存在重復(fù)。（減少冗余最常見的方法是分類儲存）數(shù)據(jù)庫的完整性：就是指的是數(shù)據(jù)庫的準(zhǔn)確性。（當(dāng)一個(gè)表，或者多個(gè)表相互關(guān)聯(lián)的話，當(dāng)一個(gè)表作出修改了，其他表都應(yīng)做出相應(yīng)的修改，否則將會失去準(zhǔn)確性。）Sqlserver數(shù)據(jù)庫按照用途可以分為：系統(tǒng)數(shù)據(jù)庫、用戶數(shù)據(jù)庫系統(tǒng)數(shù)據(jù)庫有四個(gè)：Master數(shù)據(jù)庫：（作用）

1.所有的登錄賬號和系統(tǒng)配置設(shè)置。

2.所有其他的數(shù)據(jù)庫及數(shù)據(jù)庫文件的位置。3.>sqlserver數(shù)據(jù)庫Tempdb數(shù)據(jù)庫

1.tempdb數(shù)據(jù)庫保存所有的臨時(shí)表和臨時(shí)存儲過程以及臨時(shí)生成的工作表，Tempdb

數(shù)據(jù)庫在sqlserver每次啟動(dòng)時(shí)都重新創(chuàng)建。

Model數(shù)據(jù)庫

1.model數(shù)據(jù)庫用作在系統(tǒng)上創(chuàng)建的所有數(shù)據(jù)庫的模板。Msdb數(shù)據(jù)庫

1.msdb數(shù)據(jù)庫供sqlserver代理程序調(diào)度報(bào)警、作業(yè)以及記錄操作時(shí)使用。早右側(cè)視

圖中可以輸入查詢語句來炒作數(shù)據(jù)庫對象、管理和查詢數(shù)據(jù)庫。輸出一條查詢語句，執(zhí)行在后方顯示查詢的結(jié)果。

Sqlserver支持一下兩種身份驗(yàn)證：

1.window身份驗(yàn)證2.sqlserver省份驗(yàn)證用戶的操作權(quán)限分為兩類：

1.第一類是指該用戶在服務(wù)器范圍內(nèi)能夠執(zhí)行哪些操作，有服務(wù)器角色來確定。2.第二類權(quán)限是指該用戶對指定的數(shù)據(jù)庫操作的權(quán)限。數(shù)據(jù)庫包含：數(shù)據(jù)庫文件（。Mdf，默認(rèn)大小是3mb），事務(wù)日志文件（。Ldf，默認(rèn)問1mb），文件組。

數(shù)據(jù)庫文件可以分為：主數(shù)據(jù)庫文件（mdf）、和次數(shù)據(jù)庫文件。（一個(gè)數(shù)據(jù)庫只能有一個(gè)主數(shù)據(jù)庫文件，但可以有多個(gè)次數(shù)據(jù)庫文件。同樣，一個(gè)主數(shù)據(jù)文件，也只能屬于一個(gè)數(shù)據(jù)庫。）文件組也分為主文件組（primaryfilegroup）和此文件組（secondaryfile）。數(shù)據(jù)庫選項(xiàng)（一般要注意的）：

1.兼容級別：數(shù)據(jù)庫向以前的版本兼容

2.數(shù)據(jù)庫只讀：設(shè)置為true或false。True時(shí)，數(shù)據(jù)庫為不可以寫入3.訪問限制：

a．multiple數(shù)據(jù)庫正常裝態(tài)。允許多個(gè)用戶同時(shí)訪問該數(shù)據(jù)庫。b．single用于維護(hù)狀態(tài)，一次只允許一個(gè)用戶訪問

c．Restricted只有管理員角色或者特定的成員才能正常使用該數(shù)據(jù)庫。4.自動(dòng)關(guān)閉:5.自動(dòng)收縮

數(shù)據(jù)完整性

要求數(shù)據(jù)庫中的數(shù)據(jù)的準(zhǔn)確性，而確保準(zhǔn)確性是通過數(shù)據(jù)庫表的世界和約束來實(shí)現(xiàn)的。為實(shí)現(xiàn)完整性，需要1.檢查每行數(shù)據(jù)是否符合要求，2..檢查每列數(shù)據(jù)是否符合要求，為實(shí)現(xiàn)上述要求，sqlserver提供4種類型的約束：

1.實(shí)體完整性約束（要求表中的每一行數(shù)據(jù)都反映不同的實(shí)體，不能存在相同的數(shù)據(jù)

行）

2.域完整性約束(是指定給列輸數(shù)據(jù)數(shù)據(jù)的有效性)

3.引用完整性約束（是用來定義表之間的已定義的關(guān)系,t他是通過主外鍵之間的關(guān)系

來實(shí)現(xiàn)的）

4.自定義完整性約束

主鍵：一個(gè)表只能有一個(gè)主鍵，選擇主鍵的原則是：最少性和穩(wěn)定性外鍵：一個(gè)表可以有多個(gè)外鍵，選擇外鍵的原則和主鍵的原則一樣。Sqlserver數(shù)據(jù)庫類型：

二進(jìn)制數(shù)據(jù)類型：用來存儲非字符和文本的數(shù)據(jù)

1．binary固定長度2．varbinary可變

文本數(shù)據(jù)類型：字符數(shù)據(jù)包括任意字母，符號或數(shù)字字符的組合

1.char固定長度非Unicode字符數(shù)，最大長度8000字符2.varchar可變非Unicode數(shù)據(jù)3.nchar固定長度Unicode數(shù)據(jù)

4.nvarchar可變長度的Unicode數(shù)據(jù)5.text存儲文本信息

6.ntext存儲可變長度的長文本信息時(shí)間和日期：日期和時(shí)間在單引號內(nèi)分別輸入

1.datetime日期和時(shí)間

數(shù)字?jǐn)?shù)據(jù)：該數(shù)據(jù)僅包含數(shù)字，包括正數(shù)、負(fù)數(shù)以及分?jǐn)?shù)正數(shù)

1．Int：2．Smallint3．Tinyint4．Bigint浮點(diǎn)數(shù)1.float：2.real

貨幣數(shù)據(jù)類型：使用十進(jìn)制貨幣值，并且精確到數(shù)據(jù)庫后面的4位數(shù)字

1.money

bit數(shù)據(jù)類型：表示是/否只有兩種選擇。（sqlserver用0/1）

1．Bit

Sqlserver中包括的邏輯運(yùn)算符有and/ornot

T-SQl的組成1.DML（數(shù)據(jù)庫操作語言）：用來查詢、插入、刪除、修改數(shù)據(jù)庫中的數(shù)據(jù)2.DCL（數(shù)據(jù)庫控制語言）：用來控制數(shù)據(jù)庫組件的存取許可，存取權(quán)限等3.DDL(數(shù)據(jù)庫定義語言)：用來建立數(shù)據(jù)庫、數(shù)據(jù)庫對象和定義其列大部分以Create開頭

Sqlserver條件表達(dá)式常量：表示大哥指定數(shù)據(jù)值的符號列明：表中列的名稱

一元運(yùn)算符：僅有一個(gè)操作運(yùn)算符

二元運(yùn)算符：將兩個(gè)操作數(shù)組合執(zhí)行操作的運(yùn)算符列名like‘Co%’以什么開頭的

列名like‘9wo[1-2]’括號內(nèi)所知范圍內(nèi)的一個(gè)字符列名like‘9wo[^1-2]’不在括號內(nèi)所知范圍內(nèi)的一個(gè)字符

Insert插入數(shù)據(jù)

Insertinto表名values列值

一次插入多行數(shù)據(jù)（有三種方法）：

1.insertinto表名（插入到那列）select需要那幾列數(shù)據(jù)from來自那張表

2.select需要那幾列數(shù)據(jù)into插入那張表中from來自那張表（需要注意的是

這個(gè)需要插入的表是不能預(yù)先存在的）a.這里有一個(gè)問題：就是沒有主鍵

b.所以上面的語句可以修改為：select需要那幾列數(shù)據(jù)identity（int，1，1）

as給標(biāo)識列命名into插入那張表中from來自那張表

2．通過union關(guān)鍵字合并數(shù)據(jù)進(jìn)行插入：

Insert表名（插入到哪幾列）Select要插入的值unionSelect要插入的值unionSelect要插入的值unionSelect要插入的值

Update更新數(shù)據(jù)Update表名setwhere條件Delete刪除數(shù)據(jù)Deletefrom表名where條件

使用truncatetable刪除數(shù)據(jù)（刪除所有的數(shù)據(jù)，執(zhí)行速度比delete快）Truncatetable表名（注意，后面不能跟條件）Orderby排序查詢

Select列名form表名orderby排序列名ase/dese（ase升序）使用列名查詢SelectteaNameas姓名form表名where條件

查詢空行

select*fromteacherwhereteaNameisnull在查詢中使用常量SelectctteaName，‘安康市二中’as學(xué)校名稱form表名

使用like模糊查詢

Select*from表名where列名like‘a(chǎn)a%’使用between在某個(gè)范圍之內(nèi)進(jìn)行查詢Select*from表名where列名between第一個(gè)數(shù)and第二個(gè)數(shù)使用in在列舉值內(nèi)進(jìn)行查詢

Select*form表名where列名in（‘列舉名稱’，‘列舉名稱’。。。。）orderby列名（這個(gè)跟前面的那個(gè)是一個(gè)列）

聚合函數(shù)sum，avg，max，min，count分組查詢groupby

Select編號，avg（列名）from表名groupby編號（根據(jù)編號分組）使用having子句進(jìn)行分組篩選Select編號，avg（列名）from表名groupby列名havingavg（列名）

擴(kuò)展閱讀：SQL Server知識點(diǎn)總結(jié)

第一章關(guān)系數(shù)據(jù)庫管理系統(tǒng)基礎(chǔ)知識

1.1數(shù)據(jù)庫系統(tǒng)概述

數(shù)據(jù)：對客觀存在的事物的一種描述。

數(shù)據(jù)庫：長期存儲在計(jì)算機(jī)內(nèi)、與應(yīng)用程序彼此獨(dú)立的、以一定的組織方式存儲在一起的、彼此相互關(guān)聯(lián)的、具有較少冗余的、能被多個(gè)用戶共享的數(shù)據(jù)集合。數(shù)據(jù)庫體系結(jié)構(gòu)：

（1）內(nèi)模式：也稱存儲模式，是數(shù)據(jù)庫全部數(shù)據(jù)的內(nèi)部表示或者底層描述，用來定義數(shù)據(jù)的存儲方式和物理結(jié)構(gòu)。

(2)模式：也稱邏輯模式，實(shí)際上是數(shù)據(jù)在邏輯級上的視圖，是數(shù)據(jù)庫中全體數(shù)據(jù)的邏輯結(jié)構(gòu)和特征描述，即根據(jù)用戶需求設(shè)計(jì)出的數(shù)據(jù)庫模型。

(3)外模式：也稱用戶模式，是數(shù)據(jù)庫用戶能夠看見和使用的數(shù)據(jù)視圖。

數(shù)據(jù)庫管理系統(tǒng)(DBMS)是位于用戶與計(jì)算機(jī)操作系統(tǒng)之間的一個(gè)系統(tǒng)軟件，由一組計(jì)算機(jī)程序組成。DBMS的功能：

數(shù)據(jù)定義功能：DDL

數(shù)據(jù)操作功能：DML分為：交互式命令語言、嵌入式語言。數(shù)據(jù)庫的運(yùn)行與管理,包括數(shù)據(jù)安全控制、數(shù)據(jù)完整性控制、數(shù)據(jù)庫的恢復(fù)、并發(fā)控制。數(shù)據(jù)的建立和維護(hù)功能。初始數(shù)據(jù)的輸入與數(shù)據(jù)轉(zhuǎn)換等。數(shù)據(jù)通信接口：與其它軟件系統(tǒng)通信的功能。

1.2數(shù)據(jù)模型

數(shù)據(jù)模型分三大類：

概念數(shù)據(jù)模型：獨(dú)立于計(jì)算機(jī)系統(tǒng)的數(shù)據(jù)模型，完全不涉及信息在計(jì)算機(jī)系統(tǒng)中的表

示，只是用來描述所使用的信息結(jié)構(gòu)。

邏輯數(shù)據(jù)模型：返回?cái)?shù)據(jù)的邏輯結(jié)構(gòu)，主要有層次數(shù)據(jù)模型、網(wǎng)狀數(shù)據(jù)模型、關(guān)系數(shù)

據(jù)模型。

物理數(shù)據(jù)模型：反映數(shù)據(jù)在計(jì)算機(jī)中的存儲結(jié)構(gòu)。

1.2.1數(shù)據(jù)模型的組成要素：

數(shù)據(jù)結(jié)構(gòu)數(shù)據(jù)操作

數(shù)據(jù)的完整性約束條件

1.2.2概念模型

(一)實(shí)體及其屬性

（1）實(shí)體：現(xiàn)實(shí)世界客觀存在并且可以相互區(qū)分的事物稱為實(shí)體。(2)屬性：實(shí)體所具有的某一特性稱為屬性。

(3)型與值：型，是結(jié)構(gòu)，用實(shí)體名及屬性名集合描述同類實(shí)體，稱為實(shí)體型。值，是數(shù)據(jù)，不同的實(shí)體有不同的屬性內(nèi)容。

(4)實(shí)體集：具有相同實(shí)體型的實(shí)體值的集體合為實(shí)體集。

(5)關(guān)鍵字：能區(qū)別實(shí)體集合中不同個(gè)體的某一個(gè)或某幾個(gè)屬性的集合。(二)實(shí)體間的關(guān)系：(1)一對一（1:1）(2)一對多(1:N)(3)多對多(N:M)

(三)概念模型的表示方法

E-R圖(1)實(shí)體型（2）屬性(3)聯(lián)系

1.2.5關(guān)系模型

(一)基本術(shù)語

(1)關(guān)系：一個(gè)關(guān)系模型的邏輯結(jié)構(gòu)是二維表，它由行和列組成。(2)元組：表中的一行稱為一個(gè)元組，也稱為記錄。(3)屬性：表中的一列稱為屬性，用來描述事物的特征。(4)域：屬性的取值范圍。

(5)關(guān)系字/碼：在關(guān)系中某一個(gè)屬性或?qū)傩越M的值唯一的決定其他所有屬性。

(6)候選鍵/候選關(guān)鍵字/候選碼：一個(gè)關(guān)系中有多個(gè)屬性或?qū)傩越M都能用來標(biāo)識關(guān)系的元組。

(7)主鍵/關(guān)關(guān)系字/主碼：在一個(gè)關(guān)系的多個(gè)候選碼中指定其中一個(gè)作為該關(guān)系的關(guān)鍵字。(8)外鍵/外關(guān)系字/外碼：(二)關(guān)系模型的三要素(1)數(shù)據(jù)結(jié)構(gòu)關(guān)系(2)關(guān)系操作

(3)關(guān)系完整性約束(三)關(guān)系模型的特點(diǎn)(1)嚴(yán)格的理論基礎(chǔ)(2)數(shù)據(jù)結(jié)構(gòu)單一(3)存取簡單

1.3關(guān)系數(shù)據(jù)庫及其設(shè)計(jì)過程1.3.1關(guān)系與表格

表應(yīng)該具有的性質(zhì)：(1)必須是規(guī)范化的關(guān)系(2)表中的“行”是惟一的(3)行的次序可以任意

(4)表中的確良“列名”是惟一的(5)列的次序可以任意(6)必須滿足完整性約束

1.3.3數(shù)據(jù)設(shè)計(jì)過程

(1)需要分析(2)概念結(jié)構(gòu)設(shè)計(jì)

(3)邏輯結(jié)構(gòu)設(shè)計(jì)(E-R模型到關(guān)系模型)

(4)物理結(jié)構(gòu)設(shè)計(jì)(確定數(shù)據(jù)的存儲結(jié)構(gòu)、索引結(jié)構(gòu)設(shè)計(jì)、數(shù)據(jù)存儲位置的考慮、系統(tǒng)配置的優(yōu)化)

(5)數(shù)據(jù)庫實(shí)施(定義數(shù)據(jù)庫結(jié)構(gòu)、數(shù)據(jù)裝載)(6)數(shù)據(jù)庫的運(yùn)行和維護(hù)(維護(hù)數(shù)據(jù)庫的安全性和完整性、數(shù)據(jù)庫的轉(zhuǎn)儲和恢復(fù)、臨測并改善數(shù)據(jù)性能、數(shù)據(jù)庫的重新組織)

1.4關(guān)系數(shù)據(jù)的規(guī)范化1.4.1數(shù)據(jù)庫的三個(gè)規(guī)范化形式

(一)第一規(guī)范化形式1NF

在一個(gè)關(guān)系（數(shù)據(jù)表）中沒有重復(fù)的數(shù)據(jù)項(xiàng)，每個(gè)屬性都是不可分割的最小數(shù)據(jù)元素。即每列的列名都是惟一的，一個(gè)關(guān)系中不允許有兩個(gè)相同的屬性名，同一列的數(shù)據(jù)具有相同的數(shù)據(jù)類型，列的順序交換不能改變關(guān)系的實(shí)際意義。沒有相同的的列字段惟一。商品名稱商品名進(jìn)貨數(shù)據(jù)數(shù)量單價(jià)銷售數(shù)據(jù)數(shù)據(jù)單價(jià)庫存數(shù)據(jù)備注數(shù)量數(shù)量(二)第二規(guī)范化形式2NF

在滿足1NF的關(guān)系中，一行中所有非關(guān)鍵字?jǐn)?shù)據(jù)元素都完全依整于關(guān)系字。即一個(gè)關(guān)系中不允許有兩個(gè)相同的實(shí)體，行的順序交換后不能改變數(shù)據(jù)表的實(shí)際意義。即數(shù)據(jù)表中沒有相同的行

(三)第三規(guī)范化形式3NF

滿足2NF的關(guān)系中，不存在傳遞依賴于關(guān)系字的數(shù)據(jù)項(xiàng)。傳遞依賴：指某些列的數(shù)據(jù)不是直接依賴于關(guān)鍵字，而是通過某個(gè)非關(guān)系字間接地依賴于關(guān)鍵字。將不依賴關(guān)鍵字的列刪除，單獨(dú)創(chuàng)建一個(gè)數(shù)據(jù)表存儲。

1.4.2數(shù)據(jù)規(guī)范化設(shè)計(jì)的原則

(1)保證數(shù)據(jù)庫中的所有數(shù)據(jù)表都滿足2NF，力求絕大多烽數(shù)據(jù)表滿足3NF(2)保存數(shù)據(jù)的完整性(3)盡可能減少冗余

1.5數(shù)據(jù)表的關(guān)聯(lián)與數(shù)據(jù)的完整性1.5.1表的關(guān)系

(一)交叉連接

也稱為非限制連接、無條件連接或笛卡兒連接。將兩個(gè)表不加任何限制地組合在一起，其連接方法是將第一表中的每條記錄分別與第二個(gè)表中每條記錄連接成一條新記錄，結(jié)果是具有兩個(gè)記錄數(shù)乘職的邏輯數(shù)據(jù)表。學(xué)號1001100210031004

姓名呂川頁鄭學(xué)敏于麗孫立華

學(xué)號100310041005成績9278學(xué)號100110011001100210021002100310031003100410041004姓名呂川頁呂川頁呂川頁鄭學(xué)敏鄭學(xué)敏鄭學(xué)敏于麗于麗于麗孫立華孫立華孫立華學(xué)號100310041005100310041005100310041005100310041005成績927885927885927885927885(二)內(nèi)連接:也稱為自然連接，只將兩個(gè)表中滿足指定條件的記錄連接成一條新記錄，舍棄所有不滿足條件沒有連接的記錄學(xué)號10031004姓名于麗孫立華學(xué)號10031004成績9278

等價(jià)于

學(xué)號10031004姓名于麗孫立華成績9278(三)外連接

可以只限制一個(gè)表，對另外一個(gè)表不加限制（所有行都出現(xiàn)在結(jié)果集中），以便在結(jié)果集中保證該的完整性。

外連接分為左外連接、右外連接、全外連接

(1)左外連接：可以得到左表的全部記錄及右表相關(guān)的記錄信息。學(xué)號1001100210031004學(xué)號10031004Null學(xué)號1001100210031004Null姓名呂川頁鄭學(xué)敏于麗孫立華姓名于麗孫立華Null姓名呂川頁鄭學(xué)敏于麗孫立華Null學(xué)號NullNull10031004學(xué)號100310041005學(xué)號NullNull100310041005成績NullNull9278成績927885成績NullNull927885等價(jià)于等價(jià)于

等價(jià)于

學(xué)號1001100210031004學(xué)號10031004Null學(xué)號1001100210031004Null姓名呂川頁鄭學(xué)敏于麗孫立華姓名于麗孫立華Null姓名呂川頁鄭學(xué)敏于麗孫立華Null成績NullNull9278成績927885成績NullNull927885(2)右外連接：可以得到右表的全部記錄信息及左表相關(guān)的記錄信息。(3)全外連接：可以得到左表與右表的全部記錄信息。1.5.2數(shù)據(jù)的完整性及約束(1)數(shù)據(jù)完整性

1)實(shí)體完整性：數(shù)據(jù)表中的所有行都是惟一的、確定的，所有記錄都是可以區(qū)分的。表中的主鍵值惟一，主要屬性不能為空，主鍵不能為空。

2)參照完整性：定義外鍵與主鍵之間的引用規(guī)則，確保數(shù)據(jù)庫中不會含有無效外鍵。當(dāng)一個(gè)表中的某列數(shù)據(jù)依賴引用另一個(gè)表的某列數(shù)據(jù)時(shí)，這兩個(gè)表之間的相關(guān)數(shù)據(jù)必須保持一致。3)域完整性：表中每列的數(shù)據(jù)具有正確的數(shù)據(jù)類型、格式和有效的取值范圍，保證數(shù)據(jù)的正確性。(2)約束

1)主鍵約束:每個(gè)表必須設(shè)計(jì)主鍵約束，主鍵的特點(diǎn)：不允許重復(fù)、不允許為空、只能有一主鍵，可以是聯(lián)合主鍵。記錄按主鍵值指定順序存儲。

2)惟一約束：一列數(shù)據(jù)或幾列數(shù)據(jù)的組合值在數(shù)據(jù)表中是惟一不能重復(fù)的。保證主鍵外的字段值不能重復(fù)。一個(gè)表中可以定義多個(gè)惟一約束�？梢栽试S為空值。

3)外鍵約束：如果一個(gè)表中某個(gè)字段的數(shù)據(jù)只能取另一個(gè)表中某個(gè)字段值之一，則必須為該字段設(shè)置外鍵約束，設(shè)置外鍵約束的表稱為子表，它所引用的表稱為父表。外鍵約束可以使一個(gè)數(shù)據(jù)庫中的多個(gè)數(shù)據(jù)表之間建立關(guān)系。建立一對多的邏輯關(guān)系。外鍵約束可以保證數(shù)據(jù)的參照完整性和域完整性。

外鍵約束的特點(diǎn)：可以是單一字段，也可以是多個(gè)字段的組合；外鍵所引用父表中的字段必須是創(chuàng)建了主鍵約束或惟一約束的列；外鍵可以允許空值，可以有重復(fù)值，但必須是父表引用列中的數(shù)據(jù)之一；子表中外鍵字段添加的新數(shù)據(jù)，必須先在父表中添加，再在子表中添加；子表中引用父表數(shù)據(jù)的記錄未刪除，則父表中被引用的數(shù)據(jù)不能被刪除。

4)檢查約束：用指定的條件檢查限制輸入數(shù)據(jù)的取值范圍是否正確，用以保證數(shù)據(jù)的參照完整性和域完整性。

5)默認(rèn)值約束：指給某個(gè)字段一個(gè)默認(rèn)的初始值，輸入記錄時(shí)若沒有給出該字段的數(shù)據(jù)，則自動(dòng)填入默認(rèn)值以保證數(shù)據(jù)的域完整性。

6)空值約束:指不知道或不能確定的特殊數(shù)據(jù)，不等同于數(shù)據(jù)0和字符的空格。設(shè)置某個(gè)字段的值是否允許為空。用以保證數(shù)據(jù)的參照完整性和域完整性。

習(xí)題：

(1)關(guān)系數(shù)據(jù)模型中，實(shí)體用(巨型)來表示，實(shí)體間的聯(lián)系用(菱形)來表示

(2)(DBMS)是位于用戶與操作系統(tǒng)之間的一層數(shù)據(jù)管理軟件。數(shù)據(jù)庫在建立、使用和維護(hù)時(shí)是由其統(tǒng)一管理、統(tǒng)一控制。

(3)目前最常用的數(shù)據(jù)模型有(層次模型)、（網(wǎng)狀模型）、（關(guān)系模型）.20世紀(jì)80年代以來，(關(guān)系模型)逐漸占主導(dǎo)地位。

(4)數(shù)據(jù)模型的三要要素包括(數(shù)據(jù)結(jié)構(gòu))、(數(shù)據(jù)操作)和（數(shù)據(jù)的完整性約束條件）.(5)關(guān)系的主鍵是()、外鍵是()

(6)數(shù)據(jù)庫的實(shí)體完整性要求表中的所有(行)惟一，可以通過創(chuàng)建(主鍵約束)(惟一約束)(空值)等約束來實(shí)現(xiàn)

(7)數(shù)據(jù)的參照完整性要有關(guān)聯(lián)的兩個(gè)或兩個(gè)以上數(shù)據(jù)表之間的數(shù)據(jù)(保持一致)數(shù)據(jù)庫參照完整性可通過創(chuàng)建(外鍵約束)和(檢查約束)約束來實(shí)現(xiàn)

(8)數(shù)據(jù)庫域完整性可保證表中指定字段中數(shù)據(jù)的(正確性).要求表中指定列的數(shù)據(jù)具有正確的(數(shù)據(jù)類型)(格式)和(有效的取值范圍).

(9)在一個(gè)表上能創(chuàng)建(一)個(gè)主鍵約束，主鍵值(不允許)為空，在一個(gè)表上能健(多)個(gè)惟一約束，惟一值(允許)為空

(10)外鍵約束來用創(chuàng)建立兩個(gè)表之間的關(guān)聯(lián)。外鍵列的取值可以(空),可以有(重復(fù))值，但其值必須是引用列的值之一。引用列必須是創(chuàng)建了(主鍵)或(惟一)的列。

(11)若為某公司開發(fā)一個(gè)邏輯模型：公司有10個(gè)部門，每個(gè)部門有67個(gè)員工，但每個(gè)員工可能會在不止一個(gè)部門工作。下面所給的模型正確的是(B).A.部門和員工之間是一種確定的一對多的關(guān)系。

B.建立一個(gè)關(guān)聯(lián)表，從該關(guān)聯(lián)表到員工建立一個(gè)一對多的關(guān)系，然后再從該關(guān)聯(lián)表到部門表建立一個(gè)一對多的關(guān)系。

C.建立一個(gè)關(guān)聯(lián)表，從員工表到該關(guān)聯(lián)建立一個(gè)一對多的關(guān)系，然后再從部門表到該關(guān)聯(lián)表建立一個(gè)一對多的關(guān)系。

D.這種情況不能建立正常的數(shù)據(jù)庫型。

(12)假設(shè)有一個(gè)學(xué)生信息表(StuInfo)的設(shè)計(jì)如下：StuId,Name,Address,Departmetn,DepartmentHead該最高滿足第（2NF）范式。(13)指出下列關(guān)系各屬第幾范式。

(1)學(xué)生(學(xué)號，姓名，課程號，成績)(2NF)(2)學(xué)生(學(xué)號，姓名，性別)(3NF)

(3)學(xué)生(學(xué)號，姓名，所在系另，所在系地址)(2NF)

(4)員工(員工編號，基本工資，崗位級別，崗位工資，獎(jiǎng)金，工資總額)(2NF)(5)供貨商(供貨商編號，零件號，零件名，單價(jià)，數(shù)量)(2NF)

第二章SqlServer201*數(shù)據(jù)庫管理系統(tǒng)

1.SQLServer201*的各種版本(1)SQLServer201*企業(yè)版(2)SQLServer201*標(biāo)準(zhǔn)版(3)SQLServer201*個(gè)人版(4)SQLServer201*開發(fā)版

(5)SQLServer201*WindowsCE版(6)SQLServer201*企業(yè)評估版2.SQLServer201*的用戶帳號(1)使用本地系統(tǒng)帳號

(2)使用域用戶帳號(必須是Administrators組的成員、有密碼永不過期的屬性、作為一個(gè)服務(wù)登錄)

3.SQLServer201*的組件

(1)服務(wù)器組件(2)管理工具(3)客戶端連接(4)聯(lián)機(jī)叢書(5)工發(fā)工具(6)代碼示例4.SQLServer提供的服務(wù)

(1)SQLServer服務(wù)：實(shí)現(xiàn)SQLServer數(shù)據(jù)庫引擎，處理所有T-SQL語句，管理服務(wù)器上數(shù)據(jù)庫的所有文件，在多個(gè)并發(fā)用戶之間分配計(jì)算機(jī)資源，防止產(chǎn)生邏輯問題，保證數(shù)據(jù)的安全性、一致性和完整性。

(2)SQLServer代理服務(wù):實(shí)現(xiàn)運(yùn)行調(diào)度的SQLServer管理任務(wù)的代理程序。SQLServer代理是一個(gè)任務(wù)規(guī)劃器和警報(bào)管理器。可以創(chuàng)建和管理作業(yè)、警報(bào)和操作員。

(3)Microsoft搜索服務(wù)：僅用于WindowsNT和Windows201*，實(shí)現(xiàn)全文本檢索引擎。

(4)MSDTC(DistributedTransactionCoordinator)服務(wù):僅用于WindowsNT和Windows201*，管理分布式事務(wù)。

2.4SQLServer201*的系統(tǒng)數(shù)據(jù)庫及系統(tǒng)表(1)系統(tǒng)數(shù)據(jù)庫

1)master數(shù)據(jù)庫：記錄了SQLServer系統(tǒng)級的信息，包括系統(tǒng)中所有登錄帳號、系統(tǒng)配置信息、所有數(shù)據(jù)庫的信息以及所有用戶數(shù)據(jù)庫的主文件地址等。Master數(shù)據(jù)庫中還有很多系統(tǒng)存儲過程和擴(kuò)展存儲過程。

2)tempdb數(shù)據(jù)庫：用于存儲所有連接到系統(tǒng)的用戶臨時(shí)表和臨時(shí)存儲過程以及SQLServer產(chǎn)生的其他臨時(shí)性的對象。Tempdb是SQLServer中負(fù)擔(dān)最重的數(shù)據(jù)庫，因?yàn)閹缀跛�有查詢都需要使用它。關(guān)閉SQLServer時(shí)，tempdb數(shù)據(jù)庫中所有對象會被刪除。

3)model數(shù)據(jù)庫：是系統(tǒng)所有數(shù)據(jù)庫的模板，這個(gè)數(shù)據(jù)庫相當(dāng)一個(gè)模子，所有在系統(tǒng)中創(chuàng)建的新數(shù)據(jù)庫的內(nèi)容，在剛創(chuàng)建時(shí)都和model數(shù)據(jù)庫完全一樣。

4)msdb數(shù)據(jù)庫：被SQLServer代理來安排報(bào)警、作業(yè)，并記錄操作員。(2)系統(tǒng)表

1)Sysdatabases:記錄系統(tǒng)數(shù)據(jù)庫和用戶自定義數(shù)據(jù)庫，只在master表。2)Syslogins:只在mastetr表，記錄每一個(gè)登錄帳號

3)Syslanguages:只在master表，記錄每種語言，美國英語不表中。

4)Sysobjects:在每個(gè)表中，記錄創(chuàng)建的每一個(gè)對象。在tempdb表中，每個(gè)臨時(shí)表被記錄。5)Syscolumns:每個(gè)表中，對基表或者視圖的每一個(gè)列和存儲過程中的每個(gè)參數(shù)。

6)Sysindexes:每個(gè)表中，記錄每個(gè)索引和沒有聚簇索引的每個(gè)表含有的一行記錄，還包括文本或圖像數(shù)據(jù)。

7)Sysusers在每個(gè)表中，記錄整個(gè)數(shù)據(jù)庫中的每個(gè)NT用戶，NT用戶組、SQLServer用戶或者SQLServer角色。

8)Sysdepends:記錄表、視圖、和存儲過程之間每一個(gè)依賴關(guān)系。9)Sysforeignkeys:包含關(guān)系表中的外鍵約束。10)Sysfiles:記錄每一個(gè)文件。

2.5SQL和T-SQL概述

T-SQL的分類：

(1)數(shù)據(jù)定義語句:用來定義和管理數(shù)據(jù)以及數(shù)據(jù)庫中的各種對象的語句。Create,Aler,Drop.(2)數(shù)據(jù)操作語句:用來查詢、添加、修改和刪除數(shù)據(jù)庫中的數(shù)據(jù)語句。Select,Insert,Update,Delete.

(3)數(shù)據(jù)控制語句:用來進(jìn)行安全管理，以確保數(shù)據(jù)庫中數(shù)據(jù)和操作不被未授僅用戶使用和執(zhí)行。Grant,Deny,Revoke.

(4)附加的語言元素:為了編寫腳本而增加的語言元素，包括變量、運(yùn)算符、函數(shù)，流程控制和注釋。

習(xí)題：

(1)服務(wù)管理是用來(啟動(dòng))(停止)和(暫停)，SQLServer服務(wù)的。這些服務(wù)在WindowsNT或Windows201*下也可以通過控制面板的[服務(wù)]項(xiàng)來啟動(dòng)或停止。

(2)在[開始]菜單的MicrosoftSQLServer程序中選擇EnterpriseManager即可啟動(dòng)(???)管理器

(3)在[查詢]窗口中用戶可以輸入SQL語句，按(F5)鍵，或單擊工具欄上的[運(yùn)行]按鈕，將其送到服務(wù)器執(zhí)行。

第三章用戶數(shù)據(jù)庫的創(chuàng)建與操作

3.1SQLServer數(shù)據(jù)庫的存儲結(jié)構(gòu)3.1.1SQLServer數(shù)據(jù)庫

數(shù)據(jù)庫名：

由字母、數(shù)字、漢字、下劃線組成。不能以數(shù)字開頭，不能是關(guān)鍵字不能超過128個(gè)字符

3.1.2數(shù)據(jù)庫文件的分類

(1)數(shù)據(jù)庫文件(課后題3,4)分三類：

主數(shù)據(jù)文件：.MDF有且只能一個(gè)。存儲數(shù)據(jù)和啟動(dòng)信息。輔助數(shù)據(jù)文件：.NDF0或多個(gè)。存儲數(shù)據(jù)

事務(wù)日志文件:LDF1或多個(gè)。存儲對數(shù)據(jù)庫的操作、修改信息。(2)文件組：將多個(gè)數(shù)據(jù)庫文件組成一個(gè)文件組，整體管理。

無論一個(gè)數(shù)據(jù)庫有多少個(gè)數(shù)據(jù)文件，也無論多少個(gè)文件組，主數(shù)據(jù)文件只能有一個(gè)。默認(rèn)組名：Primary

數(shù)據(jù)文件和文件組的規(guī)則：

一個(gè)文件或文件組包括事務(wù)日志文件只能被一個(gè)數(shù)據(jù)庫使用。一個(gè)數(shù)據(jù)文件只能屬于一個(gè)文件組。事務(wù)日志文件不屬于文件組。

3.1.3數(shù)據(jù)庫對象

數(shù)據(jù)庫對象沒有對應(yīng)的磁盤文件。

表、視圖、存儲過程、觸發(fā)器、用戶定義類型、用戶定義函數(shù)、索引、規(guī)則、默認(rèn)、全文目錄、約束

3.2創(chuàng)建數(shù)據(jù)庫3.2.1用企業(yè)管理器創(chuàng)建

文件大小：初始容量(MB)，默認(rèn)1MB

主數(shù)據(jù)庫文件默認(rèn)組primary不可更改（可修改系統(tǒng)設(shè)置）文件自動(dòng)增加分為：按兆字節(jié)和按百分比

3.2.2用CreateDataBase語句

createdatabaseteacheron(name=teacherdate1,filename="F:\\SqlTest\\tdata1.mdf",size=1MB,--默認(rèn)字節(jié)，可以省略，maxsize=10,--最大容量filegrowth=15%--增長量),(name=teacherdata2,filename="F:\\Sqltest\\tdata2.ndf",size=2MB,maxsize=15,filegrowth=2)logon(name=teacherlog,filename="F:\\Sqltest\\tlog.ldf",size=500MB,--初始容量，KB為單位，不能省略。maxsize=unlimited,--最大容量不受限制。filegrowth=500kb--增加量不能省略.)說明：路徑必須得存在3.3.2用T-SQL查看數(shù)據(jù)庫信息

(1)execsp_helpdb--顯示所有數(shù)據(jù)庫信息

(2)execsp_helpdbteacher--顯示指定數(shù)據(jù)庫信息

(3)execsp_databases所有數(shù)據(jù)庫信息，沒有sp_helpdb內(nèi)容多。(4)execsp_helpfile--當(dāng)前數(shù)據(jù)庫信息(5)execsp_helpfilegroup文件組名

(6)sp_deoption:查看，設(shè)置修改數(shù)據(jù)庫選項(xiàng)語法：sp_deoption[數(shù)據(jù)庫名，[選項(xiàng)，值]]選項(xiàng)為：single單用戶，readonly只讀,autoclose自動(dòng)關(guān)閉,autoshrink自動(dòng)收縮如：sp_dboption"teacher","single","true"sp_dboption"teacher"--不給參數(shù)，為查詢修改結(jié)果。(7)AlterDatabase設(shè)置、修改數(shù)據(jù)庫語法：

alterdatabase數(shù)據(jù)庫名addfile[tofilegroup文件組]addlogfileremovefile邏輯文件名addfilegroup文件組名removefilegroup文件組名modifyfilemodifyfilegroup文件組名，文件組屬性示例：alterdatabaseteacheraddfilegroupteachergrouptgoalterdatabaseteacheraddfile(name=teacheradd,filename=’F:\\SqlTest\\tdata3.ndf’)go說明：文件正在使用時(shí)無法操作。3.4數(shù)據(jù)庫的分離與刪除

(1)分離：只是從SQLServer系統(tǒng)中刪除數(shù)據(jù)庫，組成數(shù)據(jù)庫的數(shù)據(jù)文件和事務(wù)日務(wù)文件依然保存在磁盤上。

語法：sp_detach_db"數(shù)據(jù)庫名","是否分離前更新數(shù)據(jù)庫統(tǒng)計(jì)信息"如：sp_detach_db"teacher","true"說明：正在使用無法分離，分離后在SQLServer系統(tǒng)中無法看見。(2)刪除

語法：dropdatabase數(shù)據(jù)庫名如：dropdatabasetecher說明：正在使用無法分離.習(xí)題：

(1)在企業(yè)管理器中，右擊要操作的數(shù)據(jù)庫，在快捷菜單中選擇(新建數(shù)據(jù)庫)命令創(chuàng)建數(shù)據(jù)

庫，選擇(任務(wù)板)命令查看數(shù)據(jù)庫定義信息，選擇（屬性）命令設(shè)置數(shù)據(jù)庫選擇，選擇（屬性）命令修改數(shù)據(jù)庫結(jié)構(gòu)，選擇（屬性）命令查看數(shù)據(jù)庫結(jié)構(gòu)，使用（刪除）命令刪除數(shù)據(jù)庫。

(2)在查詢分析中，使用(createdatabase)命令創(chuàng)建數(shù)據(jù)庫，使用（sp_helpdb）命令查看數(shù)據(jù)

庫定義信息，使用（sp_dboption）命令設(shè)置數(shù)據(jù)庫選項(xiàng)，使用（alterdatabase）命令修改數(shù)據(jù)庫結(jié)構(gòu)，使用（dropdatabase）命令刪除數(shù)據(jù)庫.(3)在什么情況下不能刪除數(shù)據(jù)庫

dropdatabase可以一次刪除多個(gè)數(shù)據(jù)庫，但刪除時(shí)不出現(xiàn)提示。正在被使用的數(shù)據(jù)庫不能被刪除。

第四章數(shù)據(jù)表的創(chuàng)建與操作

4.1數(shù)據(jù)類型4.1.1二進(jìn)制數(shù)據(jù)

(1)定長二進(jìn)制binary(n):按n個(gè)字節(jié)的固定長度存放二進(jìn)制數(shù)據(jù)，最長為8KB.1(2)短整型數(shù)據(jù)：SmallInt:占2個(gè)字節(jié)，固定長度。-32768-32767(3)基本整型數(shù)據(jù)Int：占4個(gè)字節(jié)，最高位為符號位。(4)長整型整數(shù)：Bigint:占8節(jié)字，最高們?yōu)榉�號位�?5)近似值實(shí)型浮點(diǎn)數(shù)：Real，占4個(gè)字節(jié)固定長度。

(6)可變精度實(shí)型浮點(diǎn)數(shù)：Float(n)n值為124時(shí)，精度是7位。N值為2553時(shí)，精度為15位。

(7)精確小數(shù)型數(shù)據(jù)Numeric(p,s)p:總位數(shù)，不含小數(shù)點(diǎn)。取值范圍1(3)Sql_variant:存儲除文本、圖像數(shù)據(jù)和Timestamp類型數(shù)據(jù)外的其他任務(wù)數(shù)據(jù)類型。(4)Table存儲對表或視圖處理后的結(jié)果集。

4.1.10局部變量的定義和輸出

(1)批處理：以Go語句為結(jié)束標(biāo)志。

(2)局部變量：用戶自定義的變量。用于臨時(shí)存儲各種類型數(shù)據(jù)。語法：Declare@變量名數(shù)據(jù)類型(長度)賦值：Set@變量名=表達(dá)式輸出：Print@變量名或表達(dá)式4.2運(yùn)算符與表達(dá)式

(1)算述運(yùn)算符與表達(dá)式+、-、*、/、%(2)邏輯運(yùn)算符

邏輯值：True、False、Unknown：不確定，某個(gè)數(shù)據(jù)型與NULL比較的結(jié)果。比較運(yùn)算符：>、>=、=、語法：select字符from表where字段like"字符轉(zhuǎn)義字符其它"escape‘字符"示例：設(shè)tt表中字段tt值如下：t%%%%%tt%則語句：select*fromttwherettlike"c%%"escape"c",設(shè)置c后面的第一個(gè)字符為轉(zhuǎn)義字符。即c后的%為轉(zhuǎn)義字符，而第二個(gè)%仍為通配符。結(jié)果為：%%%tt%，即查以%開頭的字符。4.3系統(tǒng)內(nèi)置函數(shù)

示例：

printgetdate()--返回當(dāng)前系統(tǒng)時(shí)間printyear("201*-01-01")--返回年份

printDateadd(dd,20,"201*-01-01")--201*-01-01加20天printDateadd(mm,10,"201*-01-01")--201*-01-01加10月printDateadd(yy,10,"201*-01-01")--201*-01-01加10年

printDatediff(dd,"201*-01-01",getdate())--當(dāng)前天數(shù)減指定日期的天數(shù)據(jù)

printDatepart(mm,"201*-01-01")--指定日期的月份printDatepart(dd,"201*-01-01")--指定日期的日期

printdatename(mm,"201*-01-01")--返回字符串，指定日期的月份printcast(year("1979-02-26")aschar(4))+"年出生的人"+cast(datediff(yy,"1979-02-26",getdate())aschar(2))+"歲"

年齡是

4.5用T-SQL語句創(chuàng)建數(shù)據(jù)表及約束對象

在SQLServer201*中：

每個(gè)數(shù)據(jù)庫最多有20億個(gè)表每個(gè)表最多可以設(shè)置1024個(gè)字段

每條記錄最多占8060個(gè)字節(jié)，不包括Text,NText,Image類型。

4.5.1用CreateTable語句創(chuàng)建表結(jié)構(gòu)

CreateTable表名(字段名類型長度，字段名類型長度，….)說明：列定義必須放在括號里；參數(shù)順序不能改動(dòng)；最多可設(shè)置1024列。字段屬性：Identity:自動(dòng)編號。Null|notNull允許空或不允許為空字段約束:Constraint約束名primarykey(主健名)Constraint約束名unique(惟一列名)Constraint約束名foreignkey(外鍵名)references(引用表名)Constraint約束名check(檢查表達(dá)式)Constraint約束名default默認(rèn)值示例：createtableprovider(PIDchar(4)notnullprimarykeycheck(PIDlike"[a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9][a-zA-Z0-9]"),--只允許輸入4位英文或數(shù)字PNameNvarchar(15)notnullunique,PAddressNvarchar(20)notnullunique,PAccountChar(15)notnullunique,PLinkNanvarchar(8))4.5.2用AlterTable語句修改表結(jié)構(gòu)

語法：AlterTable表名Add列名,數(shù)據(jù)類型(長度)default’默認(rèn)值’notnull--添加新列Dropcolumn列名數(shù)據(jù)類型（長度）notnull--刪除列Addconstraint--添加約束Dropconstraint約束名--刪除列約束Nocheckconstraint約束名--設(shè)置列約束無效Checkconstraint約束名--設(shè)置列約束有效Disabletrigger觸發(fā)器名禁用觸發(fā)器Enabletrigger觸發(fā)器名重新啟用觸發(fā)器說明：AlterTable語句中只能使用單個(gè)子句，即各個(gè)子句不能組合使用。(1)使用add子句添加列altertableprovideraddLinkTelchar(15)notnulldefault"00000000000"說明：可以同時(shí)設(shè)置空值約束、默認(rèn)值約束；若不允許為空則必須給新增加的列指定默認(rèn)值，否則出錯(cuò)。(2)使用altercolumn修改字段屬性altertableprovideraltercolumnLinkTelchar(20)說明：將一個(gè)原來允許為空值的列為不允許為空時(shí)，必須保證表中已有記錄中沒有空值，而且該列沒有創(chuàng)建索引；改變數(shù)據(jù)類型時(shí)，如果原來設(shè)置默認(rèn)值約束，一般應(yīng)先觸除或刪除約束后再修改，否則容易出錯(cuò)。(3)使用addconstraint子句添加列約束altertableprovideraddconstraintuniqueProviderunique(PName)說明：約束必須指定約束名，而且必須是惟一的，不能與數(shù)據(jù)庫已定義的其他規(guī)則對象、默認(rèn)值對象同名；若約束類型及定義沒有指定列名時(shí)，必須用for指定列名；若約束類型及定義中已包含了列名，則不允許使用for子句;如果只允許有一個(gè)約束列已經(jīng)設(shè)置了（檢查約束除外），則原有約束未觸除時(shí)不能添加新的約束；altertableprovideraddconstraintTelConstraintunique(LinkTel),default"0431-123456789"forLinkTel/*unique(LinkTel)后面叵使用forLinkTel是錯(cuò)誤的*//*而default后面省略for也是錯(cuò)誤的*//*默認(rèn)值只能有一個(gè)，固無法執(zhí)行*/(4)用nocheck|checconstraint子句設(shè)置約束無效、恢復(fù)有效altertableprovidernocheckconstraintuniqueProvideraltertableprovidercheckconstraintuniqueProvideralter(5)用dropcolumn刪除字段dropconstraint刪除約束altertableproviderdropconstraintTelConstraintaltertableproviderdropcolumnLinkTel說明：刪除列時(shí)必須先刪除該字段上創(chuàng)建索引和索引后，才能刪除。4.5.3

用T-SQL語句、綁定、解除約束對象

（1）創(chuàng)建、綁定或解除規(guī)則對象語法：Createrule規(guī)則名稱as條件表達(dá)式說明：規(guī)則名稱必須符合標(biāo)識符的構(gòu)成規(guī)則；條件表達(dá)式不能包含任何字段或其他數(shù)據(jù)庫對象名，可以用@開頭的局部變量代表自己表中被綁定的字段;createrule不能與其他語句組合使用，必須單獨(dú)作為一個(gè)批處理語句。語法：用存儲過程綁定Sp_bindrule‘規(guī)則名稱’‘表名.字段名’|‘自定義數(shù)據(jù)類型名’語法：用存儲過程解除綁定Sp_unbindrule‘表名.字段名’|’自定義數(shù)據(jù)類型名’說明：規(guī)則不能梆定在系統(tǒng)的基本數(shù)據(jù)類型上。規(guī)則必須與綁定字段的數(shù)據(jù)類型相匹配，不能綁定在text,image字段上；若被綁定字段上已有綁定的規(guī)則對象，則綁定新規(guī)則時(shí)原有規(guī)則被自動(dòng)替換；當(dāng)一個(gè)字段同時(shí)綁定規(guī)則和默認(rèn)值時(shí)，默認(rèn)值必須滿足規(guī)則的要求。示例：createrulePAccount--前8個(gè)字符只允許數(shù)字和-as@xlike"[-0-9][-0-9][-0-9][-0-9][-0-9][-0-9][-0-9][-0-9]"execsp_bindrule"PAccount","provider.PAddress"--綁定execsp_unbindrule"provider.PAddress"--解除綁定(2)使用SQL語句創(chuàng)建、綁定和解除默認(rèn)值對象語法：Createdefault默認(rèn)值名稱as常量表達(dá)式說明：必須單獨(dú)作為一個(gè)批處理語句。用存儲過程綁定和解除語法：Sp_bindefault‘默認(rèn)值名稱’,’表名.字段名’|自定義數(shù)據(jù)類型名Sp_unbindefault‘表名.字段名’|自定義數(shù)據(jù)類型名說明：不能將默認(rèn)值對象綁定到標(biāo)認(rèn)列(indentity自動(dòng)編號)，不能綁定到在企業(yè)管理創(chuàng)建表時(shí)已設(shè)置默認(rèn)值字段，也不能綁定到基本數(shù)據(jù)類型上；若被綁定字段上原有綁定的默認(rèn)值對象，則綁定新默認(rèn)值對象時(shí)原有默認(rèn)值對象被自動(dòng)替換。若綁定成功則sp_bindefault返回0，失敗返回1.createdefaultdefaultAccountas"0000-000-0000"execsp_bindefault"defaultAccount","provider.PAccount"4.6查看表信息、輸入數(shù)據(jù)、編輯和刪除記錄(1)顯示表結(jié)構(gòu)及相關(guān)性Sp_help表名(2)插入記錄語法：Insert[into]表名[字段列表]Values[值列表]說明：只參添加一條記錄；順序可以任意，但個(gè)數(shù)、順序和類型必須一致；字段列表可以省略，但順序必須與表中字段順序一致，自動(dòng)編號標(biāo)識列不允許提供數(shù)據(jù)（直接省略，也不能使用逗號），允許為空的字段不提供數(shù)據(jù)時(shí)必須使用null;只有允許為null的列才能省略不提供數(shù)據(jù)，自動(dòng)編號字段必須省略；字符型與日期類型用單引號括起來；默認(rèn)值用default代理默認(rèn)值；可以嵌套使用子查詢的數(shù)據(jù)，但必須使用圓括號括起來。示例：insertintoprovidervalues("SDLC","山東省浪潮集團(tuán)","濟(jì)南市大山路15號","1002-305-5","趙群","0431-2569874")語法：Insert目標(biāo)表名字段列表select字段列表from源表名where條件示例：inserttProviderselectPId,Pname,Paddress,PAccount,PlinkNanfromprovider4.6.3向表中插入文本或圖像數(shù)據(jù)

使用企業(yè)管理器或Insert語句可以直接向text,ntext字段的行內(nèi)輸入添加數(shù)據(jù)，但數(shù)據(jù)最大長度不能超過對應(yīng)的char,nchar數(shù)據(jù)類型允許的長度。語法：WriteText表名.列名指針數(shù)據(jù)庫說明：對現(xiàn)有text,ntext或image列進(jìn)交互或更新，原有數(shù)據(jù)將被完全覆蓋。默認(rèn)狀態(tài)下，不被記錄入事務(wù)日志;指針表示指向text,ntext或image數(shù)據(jù)的指針，其數(shù)據(jù)類型必須為binary(16)；最大長度為120K.不能用在視圖中的text,ntext和image列上。執(zhí)行WriteText語句必須用TextPTR指針獲取有效的文本指針。示例：inserttProviderselectPId,Pname,Paddress,PAccount,PlinkNanfromprovidersp_dboptionDianNaoXS,"selectinto/bulkcopy","true"--允許大容易復(fù)制updateEmployeesetDescription=nullwhereid="11001"--執(zhí)行更行，以獲得指針declare@ptrtextbinary(16)--定義變量select@ptrtext=textptr([Description])fromEmployeewhereid="11001"--獲得文本指針writetextEmployee.Description@ptrtext"201*畢業(yè)于清華大學(xué)"--復(fù)制大容量數(shù)據(jù)sp_dboptionDianNaoXS,"selectinto/bulkcopy","false"--取消允許大容量復(fù)制語法：TextCopy[/s服務(wù)器名][/U登錄名][/P登錄密碼][/D數(shù)據(jù)庫名][/T表名][/C列名][/Wwhere(條件){/i|/o}]/i:指定的圖像或文本輸入保存到數(shù)據(jù)表中。/o:把圖像或文本從字段輸出保存為指定磁盤。安裝時(shí)不在目錄中，從安裝盤上x86文件中獲取，拷貝到\\80\\Tools\\Binn文件夾內(nèi)。updateEmployeesetPhoto=0xwhereid="11001"--執(zhí)行更行，以獲得指針declare@svarchar(200)set@s="textcopy/Usa/Psa/DDianNaoXS/TEmployee/CPhoto/Wwhere(id=11001)/FF;\\f.jpg/i"execmaster..xp_cmdshell@s4.6.3數(shù)據(jù)表的復(fù)制

語法：Select字段列表into新表from源表where條件說明：源表上綁定的約束不能被復(fù)制。4.6.5更新、編輯或刪除

語法：Update表名Set列名=值,列名=值…from表名UpdateText表名.列名指針null|位置null|長度數(shù)據(jù)Delete表名from表名Where條件TrunCateTable表名快速永久刪除全部記錄。只保留表結(jié)構(gòu)。4.6.2DropTable刪除數(shù)據(jù)庫及約束

DropTable表名說明：一次可以刪除多個(gè)表，但不能刪除系統(tǒng)表；不能刪除有外鍵約束的表；數(shù)據(jù)、結(jié)構(gòu)、約束、索引都將同時(shí)被刪除。習(xí)題：

1.SQLServer提供的系統(tǒng)數(shù)據(jù)類型有(二進(jìn)制)(數(shù)值)Unicode數(shù)據(jù)、(字符)(日期)和貨幣數(shù)據(jù)，也可以使用用戶定義的數(shù)據(jù)類型。

2.文本和圖像數(shù)據(jù)在SQLServer中是用text,ntext和image數(shù)據(jù)類型來表示的，由于它們的數(shù)據(jù)量一般較大，所以經(jīng)常被存儲在專門頁中，在數(shù)據(jù)行的相應(yīng)位置處只保存儲指向這些數(shù)據(jù)存儲位置的(指針).SQLServer201*中，使用（sp_tableOption）可用于指定表中文本和圖像數(shù)據(jù)是否在（表中的行）中存儲。

3.創(chuàng)建表用(createtable)語句，向表中添加記錄用（insertinto）語句，查看表的定義信息用（sp_help）語句，刪除表用（droptable）語句。4.規(guī)則的作用是(用于字段的檢查約束，限制該字段的輸入范圍).創(chuàng)建規(guī)則有(createrule)語句，刪除規(guī)則用（）語句。在列或自定義數(shù)據(jù)類型上綁定規(guī)則用(sp_bindrule)語句。解除綁定的規(guī)則用(sp_unbindrule)語句。再次向已經(jīng)綁定了規(guī)則的列上綁定規(guī)則，將會（原有規(guī)則被自動(dòng)替換）。

5.默認(rèn)值的作用是()。創(chuàng)建默認(rèn)值用(createdefault)語句。刪除默認(rèn)值用()語句。綁定默認(rèn)值用(sp_bindefault)語句。解除綁定的默認(rèn)值用(sp_unbindefault)語句。

6.如果當(dāng)前日期為201*/9/17，下面可以返回17函數(shù)是（datepart(day,getdate())）；7.執(zhí)行下面語句；結(jié)果是：1,2,1.40

createtablenumbers(n1int,n2numeric(5,0),n3numeric(4,2))goinsertnumbersvalues(1.7,1.6,1.4)select*fromnumbers

8.若想刪除orders表中所有超過3年的老定單，可以使用的SQL語句是(deletefromorderwhereorderTime[記錄范圍]-->字段列有[into][From][where][Groupby]Having[Orderby]Compute

(2)Compute子句不能與into或groupby子句同時(shí)使用。

5.1.2無數(shù)據(jù)檢索

select"helloword"select256*256select你好="helloword"select計(jì)算=256*256select@@versionselect@@language5.1.3指定字段列表及列別名

*:全部數(shù)據(jù)表字段表名.*:多表查詢時(shí)指定表的全部字段語法：Select字段From表where條件說明：多表查詢時(shí)同名字段必須加表名前綴；可以有計(jì)算列。別名相當(dāng)于字段變量。別名不允許出現(xiàn)在其他表達(dá)式中。5.1.4指定范圍

ALL|Distinct[Row]|Topn[percent]ALLDistinctTop三項(xiàng)參數(shù)必須單獨(dú)使用，不能同時(shí)出現(xiàn)在一個(gè)select語句中。selectall*fromsale--表中所有數(shù)據(jù)selectdistinct*fromsale--去掉重復(fù)項(xiàng)selecttop5*fromsale--前5條數(shù)據(jù)selecttop20percent*fromsale--前20%條數(shù)據(jù)select商品信息=id+","+name,每件毛利=PurchasePrice*0.1,30*2+5as字段外數(shù)據(jù),256*256fromGoods5.1.5使用Where子句

說明：where必須在from后面。selectid,name,spec,原參考價(jià)格=PurchasePrice,下浮后價(jià)格=PurchasePrice*0.75fromGoodswherePurchasePrice*0.75="201*-2-1"anddateTime名.列名”，不允許使用”表名.列名”5.2.3外連接left|right|full|join

(1)左外連接Select字段列表From表1leftjoin表2on表1.列名=表2.列名說明：默認(rèn)按左表的主鍵順序排序(2)右外連接Select字段列表From表1rightjoin表2on表1.列名=表2.列名(3)全外連接

返回左表與右表的全部記錄。Select字段列表From表1fulljoin表2on表1.列名=表2.列名(4)自內(nèi)連接join是一張表自己對自己的內(nèi)連接，即在一張表的兩個(gè)副本之間進(jìn)行內(nèi)連接。使用自連接時(shí)，必須為兩個(gè)副表指定別名。

Select字段列表From表as別名1join表as別名2on別名1.列名=別名2.列名5.2.5使用Into子句創(chuàng)建新表

Select字段列表into新表名from源表名where條件說明：into子句必須是select語句的第一個(gè)子句。新表名可以是以#開頭的臨時(shí)表，也可以記錄表，新表中沒有原表字段上綁定的約束對象。用戶必須有創(chuàng)建表的權(quán)限。Into子句不能與compute子句一起使用。5.3用select語句對數(shù)據(jù)進(jìn)行統(tǒng)計(jì)匯總5.3.1集合函數(shù)不清

Avg([ALL|Distinct]列名)Sum([ALL|Distinct]列名)Max([ALL|Distinct]列名)Min([ALL|Distinct]列名)Count([ALL|Distinct]列名)Count(*)說明：Count(*)可以包括空值記錄，其他函數(shù)均不統(tǒng)計(jì)空值記錄。集合函數(shù)使用Distinct時(shí)則不允許使用計(jì)算列或字段列名。集合函數(shù)將查詢結(jié)果集統(tǒng)計(jì)為單一數(shù)據(jù)，即匯總為一條記錄，在select中使用了集合函數(shù)就不允許再指定字段名，用Groupby指定的字段除外。5.3.2用Groupby子句對記錄分類統(tǒng)計(jì)匯總

按某一字段的數(shù)據(jù)值進(jìn)行分類之后再進(jìn)行統(tǒng)計(jì)格式：Groupby分組字段Having條件說明：使用Groupby子句時(shí)，select指定的字段必須包含且只能包含Groupby子句中指定的分組字段，其他必須是由集合函數(shù)組成的一個(gè)或多個(gè)計(jì)算列。Groupby子句不允許使用字段或計(jì)算的別名，可直接使用表達(dá)式。Groupby子句指定表達(dá)式時(shí)，select指定的字段中可以不包括該表達(dá)式。Having子句用于指定統(tǒng)計(jì)結(jié)果所要滿足的條件，表達(dá)式中可以直接使用計(jì)算列的表達(dá)式而不允許使用別名。Having子句必須配合Groupby子句使用，且設(shè)置的條件必須與Groupby子句指定的分組字段有關(guān)。使用Groupby的Select語句仍然可以使用Orderby子句統(tǒng)計(jì)結(jié)果排序，但必須在Groupby之后，可以使用別名但不允許對select沒指定的列Having子句是對分組統(tǒng)計(jì)后的查詢結(jié)果進(jìn)行篩選，在統(tǒng)計(jì)結(jié)果中選擇滿足條件的記錄作為統(tǒng)計(jì)匯總后的結(jié)果集。使用Groupby的select語句仍可使用where子句指定條件，但where子句是在分組前對原表記錄進(jìn)行篩選，使?jié)M足條件的記錄參加分組統(tǒng)計(jì)。分組字段會過濾null,null被忽略selectname,商品數(shù)量=count(id),平均價(jià)格=avg(purchasePrice)fromgoodsgroupbynameselect職工人數(shù)=count(*),平均年齡=Cast(avg(year(getdate())-year(brithday))asvarchar(2))+"歲",最大年齡=max(year(getdate())-year(brithday)),最小年齡=min(year(getdate())-year(brithday)),平均工齡=Cast(avg(year(getdate())-year(WorkTime))asvarchar(2))+"年",最長工齡=max(year(getdate())-year(worktime)),最短工齡=min(year(getdate())-year(worktime))fromemployeeselectGoodId,銷售總量=sum(num),平均價(jià)格=avg(SalePrice),銷售總額=sum(SalePrice)fromSalewhereGoodname"計(jì)算機(jī)"groupbyGoodIdselectGoodId,銷售總量=sum(num),平均價(jià)格=avg(SalePrice),銷售總額=sum(SalePrice)fromSalegroupbyGoodIdHavingGoodname"計(jì)算機(jī)"--出錯(cuò)，因?yàn)樵诜纸M結(jié)果沒有Goodname5.3.4用Compute子句顯示參加統(tǒng)計(jì)的清單及統(tǒng)計(jì)結(jié)果

語法：Compute集合函數(shù)(列名)說明：Compute子句可以指定多個(gè)集合函數(shù)，但不允許指定別名。Select指定的字段列表是顯示詳細(xì)使用的字段，必須包含Compute子句集合函數(shù)使用的列名1，與by分組字段列名2無關(guān)，也可以使用（*）表示全部字段。Compute子句不帶by表示對全部記錄統(tǒng)計(jì)，相當(dāng)于在select查詢結(jié)果后面帶一個(gè)統(tǒng)計(jì)值的后綴.Compute子句帶by表示對全部記錄統(tǒng)計(jì)，必須配合orderby排序子句使用，且緊跟orderby之后。By后的列名2是要分組的字段（相當(dāng)于groupby），可以不在select指定的字段中，但必須包含在orderby子句中，而且必須是第一順序。By指定多個(gè)字段分組時(shí)，也必須與orderby的第一順序一致。Compute子句不能與into子句或groupby子句同時(shí)使用。一個(gè)select語句中可以使用多個(gè)compute子句，一個(gè)子句顯示一個(gè)附加行，多個(gè)子句時(shí)by分組字段必須一致，且與orderby一致，子句之間不能使用逗號。select*fromsaleorderbyHandlers,DateTimecomputesum(Price)byhandlers5.4Select合并結(jié)果集與子查詢5.4.1合并查詢結(jié)果集

UNION關(guān)鍵了可以把兩個(gè)以上的查詢結(jié)果合并為一個(gè)結(jié)果集。語法：Select語句1UNION[ALL]select語句2說明：Union所合并的是兩個(gè)select的查詢結(jié)果集而不是合并被查詢的數(shù)據(jù)表，兩個(gè)結(jié)果集必須具有相同的列數(shù)、相同的對應(yīng)數(shù)據(jù)類型。合并后結(jié)果集中的列名來自第一個(gè)select語句任一個(gè)select中若包含orderby子句都將被對最后的結(jié)果集排序。使用All關(guān)鍵字則不刪除重復(fù)行，保留兩個(gè)結(jié)果集的全部，若不指定ALL則默認(rèn)在合并后的結(jié)果集中刪除重復(fù)行。selectDateTime,Customer,GoodName,numfromSaleAwherePrice>=1000UnionselectDateTime,Customer,GoodName,numfromSaleBwherePrice(selectavg(price)fromsale)(2)使用子查詢的一列值進(jìn)行列表包含in運(yùn)算select*fromgoodswhereidin(selectgoodidfromsale)(3)使用子查詢的一列值進(jìn)行列表比較ANY/ALL運(yùn)算只要有一個(gè)比較成立：ANY結(jié)果為true只有全部比較都成立：ALL結(jié)果為true.select*fromgoodswhereid=ANY(selectgoodidfromsale)(4)相關(guān)子查詢及記錄的存在性[not]exists

相關(guān)子查詢就是子查詢的執(zhí)行依賴于外部查詢，子查詢根據(jù)外部查詢提供的數(shù)據(jù)得到結(jié)果，再將結(jié)果返回給外部查詢。

外部查詢可以使用存在邏輯運(yùn)算[not]exists檢查相關(guān)子查詢返回的結(jié)果集中是否包含有記錄。若子查詢結(jié)果集包含記錄，則exists為true,否則為false.存在性的邏輯值沒有UnKnown.相關(guān)子查詢引用外部查詢的表時(shí)可以使用該表的別名。select*fromGoodswhereexists(select*fromsalewheregoods.id=sale.goodid)5.6視圖的基本概念

視圖：就是基于一個(gè)或多個(gè)表的動(dòng)態(tài)數(shù)據(jù)集合，是一個(gè)邏輯上的虛擬數(shù)據(jù)表。可以直接在視圖在對數(shù)據(jù)進(jìn)行編輯修改刪除更新數(shù)據(jù)表中的數(shù)據(jù)。Select,Insert,update語句都可以直接對視圖進(jìn)行操作。

使用視圖的優(yōu)點(diǎn)：1)為用戶集中數(shù)據(jù)、簡化查詢和處理2）屏蔽數(shù)據(jù)庫的復(fù)雜性3）簡化用戶權(quán)限的管理4）實(shí)現(xiàn)真正意義上的數(shù)據(jù)共享5）重新組織數(shù)據(jù)。

5.7視圖的創(chuàng)建和使用。5.7.1對創(chuàng)建視圖的限制和要求

創(chuàng)建視圖只能在當(dāng)前數(shù)據(jù)庫中進(jìn)行，創(chuàng)建視圖不能引用臨時(shí)表。視圖的命名不必須遵循標(biāo)識符命名規(guī)則，在一個(gè)數(shù)據(jù)庫中對每個(gè)用戶所定義視圖名必須

是惟一的，也不能與表同名。

一個(gè)視圖最多只能有1024個(gè)字段。

可以引用其他視圖或被其他視圖引用，但視嵌套引用不能超過32層。不能把規(guī)則、默認(rèn)值或觸發(fā)器綁定在視圖上。不能在視圖上建立任何索引。

定義視圖的select查詢不能包含以下子句：into,orderby,compute。使用視圖時(shí)，如果它引用基本表添加了新字段，則必須重新創(chuàng)建或修改視圖才能查詢使

用新字段。

如果與視圖相關(guān)聯(lián)的表或視被刪除，則視該視圖不能再使用。

5.7.3使用SQL語句創(chuàng)建和使用視圖

語法：Createview視圖名[withEncryption]asselect查詢[withcheckoption]說明：Encryption:要求系統(tǒng)存儲時(shí)對該createview語句進(jìn)行加密，不允許另人查看和修改CheckOption:與定義視圖中的select語句的where子句配合使用，指定對視圖中數(shù)據(jù)的修改必須遵守where子句設(shè)置的條件，不滿足條件的數(shù)據(jù)不允許修改，保證修改后的數(shù)據(jù)能通過視圖查看，省略時(shí)可以不違反約束前提示任意修改，但修改后不滿足條件記錄不再出現(xiàn)在視圖。5.8.2用SQL語句查看編輯刪除視圖

Sp_help視圖名Sp_helptext視圖名Sp_depents視圖名AlterView視圖名[withEncryption]asselect查詢[withcheckoption]DropView視圖名習(xí)題

(1)select語句使用(ALL)(Topn)(Distinct)指定查詢的顯示范圍，使用(into)子句創(chuàng)建新表，使用(orderby)子句指定排序字段，使用(where)指定查詢條件,使用(groupby)指定分組條件，使用(compute)指定分組后的查詢條件。

(2)select語句對查詢結(jié)果排序時(shí)，使用(orderby)子句指定排序字段，使用(asc)指定升序，使用(desc)指定降序.

(3)select語句對多表查詢可以使用(交叉連接)(內(nèi)連接)(外連接)(自連接)連接方式，子查詢分為(嵌套子查詢)(相關(guān)子查詢)兩種

(4)視圖是由()構(gòu)成的而不是由()構(gòu)成的慮表。視圖中的數(shù)據(jù)存儲在(數(shù)據(jù)表)。對視圖更新操作時(shí)實(shí)際操作的是(基表)中的數(shù)據(jù)。

(5)創(chuàng)建視圖用(createview)語句，修改視圖用(alterview)語句，刪除視圖用(dropview)語句。查看視圖中的定義數(shù)據(jù)用(select)語句。查看視圖的基本信息用(sp_help)存儲過程。查看視的定義信息用(sp_helptext)存儲過程。查看視圖的依賴關(guān)系用(sp_depends)存儲過程.

(6)創(chuàng)建視圖帶(Encryption)參數(shù)使視圖的定義語句加密。帶(CheckOption)參數(shù)對視圖執(zhí)行的修改操作必須遵守定義視圖時(shí)Where子句指定的條件。(7)更新視圖中的數(shù)據(jù)進(jìn)，應(yīng)該注意()()().

(8)下列可用于創(chuàng)建一個(gè)新表，并用已存在的表的數(shù)據(jù)填充到新表中的是（selectinto）(9)(comoute)子句為聚合函數(shù)生成匯總值，并作為一個(gè)附加的行顯示在結(jié)果集中。(10)有關(guān)comoute子句說法正確的是：（A）?

A.compute子句為聚合函數(shù)生成匯總值B.compute子句必須包括orderby子句C.compute子句只在控制中斷時(shí)會給出匯總。D.compute子句對排序進(jìn)行篩選.(11)(exists)可以與子查詢一起使用檢查行或是否存在。(12)子查詢可以返回()行而不產(chǎn)生錯(cuò)誤。

A.僅一行B.如果不以ANY,ALL,Exists或IN開頭，則僅一行。C.無限多行D.如果不以ANY,ALL,Exists或IN開頭，則為無限行。

13)使用子查詢時(shí)愛一定的限制，下列說明正確的有（包括Groupby的子查詢不能使用Distinct關(guān)鍵字）（如果外部查詢的Where子包括某個(gè)列名，則該子句必須與子查詢選擇列表中的該列在聯(lián)接上兼容）兩項(xiàng)。

14)當(dāng)子查詢使用來自父查詢的參數(shù)，我們稱之為（相關(guān)子查詢）.15)我們將調(diào)用另一個(gè)子查詢的子查詢稱為(嵌套子查詢);16)從“產(chǎn)品”表里查詢出價(jià)格高于產(chǎn)品名稱為“海天醬油”的產(chǎn)品記錄，此SQL語句為（select*from產(chǎn)品where價(jià)格>(select價(jià)格from產(chǎn)品where產(chǎn)品名稱=’海天醬油’)）.17)為數(shù)據(jù)庫中一個(gè)或多個(gè)表提供另一種查看方式的邏輯表被為(視圖)18)SQLServer最多允許視圖嵌套（32）級。19)SQLServer的視圖最多可包括(1024)列

20)在SQL數(shù)據(jù)庫中，要得到Products表中最貴的產(chǎn)品的產(chǎn)品名稱productname和產(chǎn)品價(jià)格price應(yīng)該使用的查詢是：

A.selecttop1productname,pricefromproductsorderbyprice出錯(cuò)，procutname不在groupby分組中。

B.selectprocuctname,max(price)fromproducts出錯(cuò)，procutname不在groupby分組中。C.selectproductname,max(price)fromproductsgroupbyproductname各種產(chǎn)品中價(jià)格最價(jià)的。

D.selectproductname,PricefromproductwherePrice=(selectmax(Price)fromproduct)

第6章數(shù)據(jù)庫索引

6.1索引概述6.1.1什么是索引

索引是一個(gè)在表或視圖上創(chuàng)建的對象，當(dāng)用戶查詢索引字段時(shí)，它可以快速實(shí)施數(shù)據(jù)檢索操作。

索引提供指針以指向存儲在表中指定字段的數(shù)據(jù)值。借助索引，執(zhí)行查詢時(shí)不必掃描整個(gè)表就能快速找到所需要的數(shù)據(jù)。

索引字段，創(chuàng)建索引的字段。索引列是一個(gè)字段，稱為簡單索引。由多個(gè)字段組合的索引，稱為復(fù)合索引。索引列的值可以設(shè)置為唯一，稱為唯一索引。也可以把索引設(shè)置為有重復(fù)值，稱為非惟一索引。

6.1.2索引的分類：

(1)簇索引：簇索引對表的物理數(shù)據(jù)頁中的數(shù)據(jù)按列進(jìn)行排序，然后再重新存儲到磁盤上，即簇索引與數(shù)據(jù)是混為一體的。由于簇索引對表中的數(shù)據(jù)一一進(jìn)行了排序，因此用簇索引查找數(shù)據(jù)很快。但由于簇索引簇索引將表所有數(shù)據(jù)完全重新排序了，它所需要空間大，大概相當(dāng)于表中數(shù)據(jù)所占空間的120%。表的數(shù)據(jù)行只能以一種排序方式存儲在磁盤上，所有一個(gè)只能有一個(gè)簇索引。

(2)非簇索引：具有與表的數(shù)據(jù)完全分離的結(jié)構(gòu)，使用非簇索引不用將物理數(shù)據(jù)頁中的數(shù)據(jù)按列排序。非簇索引中存儲了組成非簇索引的關(guān)鍵字的值和行定位器。行定位器的結(jié)構(gòu)和存儲內(nèi)容取決地?cái)?shù)據(jù)的存儲方式，如果數(shù)據(jù)是以簇索引方式存儲的，則行定位器中存儲的是簇索引的索引鍵。如果數(shù)據(jù)不是簇索引方式存儲的，這種方式又稱為堆存儲方式，則行定位器存儲的是指向數(shù)據(jù)行的指針。非簇索引將行定位器按關(guān)鍵字的值用一定的方式排序，這個(gè)順序與表的行在數(shù)據(jù)頁中排序是不匹配的。由于非簇索引使用索引頁存儲，因此它比簇索引需要更多的存儲空間，且檢索效率較低。但一個(gè)表最多可以建249個(gè)非簇索引。(3)性能比較簇索引每個(gè)表只能有一個(gè)快速快，取出一個(gè)范圍時(shí)更快。非簇索引一個(gè)表可以有多個(gè)，最多249快速慢需要大量的硬空間和內(nèi)存�？梢蕴岣邚谋碇腥�數(shù)據(jù)的速度，但會降低向表中插入數(shù)據(jù)的速度。6.2創(chuàng)建索引

6.2.1用CreateIndex命令創(chuàng)建索引語法：Create[Unique][Clustered|NoNClustered]Index索引名on{表|視圖}列名[ASC|DESC][with[PAD_INDEX][FillFactor=fillfactor][Ignore_Dup_Key][Drop_Existing][Statistics_Norecompute][Soort_In_Temped]][On文件組]說明：Unique:創(chuàng)建惟一索引，索引鍵值不重復(fù)。在列包含重復(fù)值時(shí)，不能創(chuàng)建。列不能包含null索引。Clustered:創(chuàng)建簇索引。默認(rèn)創(chuàng)建非簇索引。NONClustered:創(chuàng)建非簇索引。一個(gè)索引中最多可以指定16個(gè)列，但列數(shù)據(jù)類型的長度不能超過900個(gè)字節(jié)。PAD-Index:指定填充索引的內(nèi)部節(jié)點(diǎn)的行數(shù)至少應(yīng)大于等于兩行。只有在FillFactor選項(xiàng)指定后才起作用。因?yàn)镻AD-Index與FillFactor使用相同的百分比。FillFactor：填充因子，它指定創(chuàng)建索引時(shí)每個(gè)索引頁的數(shù)據(jù)占索引頁大小的百分比。值為1100。Ignore_Dup_key:控制了當(dāng)往包含一個(gè)惟一索引中的列中插入重復(fù)數(shù)據(jù)，SQLServer所作的反應(yīng)。Drop_Existing:指定要?jiǎng)h除并重新創(chuàng)建簇索引。StatisTisc_Norecompute:指定過期的索引統(tǒng)計(jì)不會自動(dòng)重新計(jì)算。Sor_in_tempdb:指定用于創(chuàng)建索引的分類排序結(jié)果，將被存儲到tmpdeb數(shù)據(jù)庫中。OnFilegroup:指定存放的文件組說明：數(shù)據(jù)類型textntext,image或bit的列不能作為索引列。數(shù)據(jù)類型char,varchar,binary,varbinary的列寬超過900節(jié)字，或數(shù)據(jù)類型nchar,nvarchar的列寬超過450個(gè)字節(jié)時(shí)也不能作為索引列。在創(chuàng)建向?qū)е胁荒軐⒂?jì)算列包含在索引中。但在直接創(chuàng)建或使用createindex命令創(chuàng)建索引時(shí)，則可以對計(jì)算列創(chuàng)建索引。createuniqueclusteredindexPk_goods--惟一簇索引ongoods(id)withpad_index,fillfactor=10,drop_existingcreateindexindex_goods--非簇索引ongoods(id,Spec)withpad_index,fillfactor=50on[primary]6.3查詢索引

Sp_helpindex表名--返回表的所有索引信息Sp_rename‘舊名’,’新名’,’index’--改名6.4刪除索引

Dropindex‘表名.索引名’說明：不能刪除由Createtable或Altertable命令創(chuàng)建的primarykey或unique約束索引，也不能刪除系統(tǒng)表中的索引。6.5設(shè)計(jì)索引

創(chuàng)建索引的思路：

(1)主鍵時(shí)常作為where子句的條件。

(2)有大量重復(fù)值且經(jīng)常有范圍查詢和排序、分組發(fā)生的列。非常頻繁被訪問的列�？梢越�立簇索引。

(3)經(jīng)常同時(shí)存取多列，且每列都含有重復(fù)值。

(4)如果知道索引鍵所有值都有是惟一的，可以定義惟一索引.

(5)在一個(gè)經(jīng)常做插入操作的表上建索引是，使用fillfactor來減少頁分裂，可以提高并發(fā)度降低列鎖發(fā)生。

(6)設(shè)法選擇那些采用小數(shù)據(jù)類型列作為索引列。(7)下面情況不應(yīng)該使用索引

1）索引總是不能被優(yōu)化程序使用。

2)返回的記錄數(shù)高于總記錄10%20%。3)該列只有一個(gè)、兩個(gè)或三個(gè)不同的值。4)被索引的列較長工

5)維護(hù)索引的開銷超過了建立索引的價(jià)值。

6.6索引的維護(hù)

（1）重建索引

下面需要重建索引：

1)數(shù)據(jù)和使用模式大幅度變化。2)排序的順序發(fā)生改變。

3)要進(jìn)行大量插入操作或已經(jīng)完成

4)使用大塊I/O的查詢的磁盤讀次比預(yù)料的要多.

5)由于大量數(shù)據(jù)修改，使得數(shù)據(jù)頁和索引頁沒有充分使用而導(dǎo)致空間的使用超出估算。(2)索引統(tǒng)計(jì)信息的更新

下面情況需要運(yùn)行updatestatistics命令：1)數(shù)據(jù)行的插入和刪除修改了數(shù)據(jù)上的分布

2)對用truncatetable刪除數(shù)據(jù)的表上增加數(shù)據(jù)行。3)修改索引列的值。

習(xí)題：

1)在SQLServer的數(shù)據(jù)庫中按存儲結(jié)構(gòu)的不同將索引分為兩類：(簇索引)和(非簇索引);2)在使用Createindex語句創(chuàng)建簇索引時(shí)使用關(guān)鍵字(Clustered)；建立惟一索引的關(guān)鍵字是()3)查看索引使用的系統(tǒng)存儲過程（sp_index），為索引更改名稱使用系統(tǒng)存儲過程（sp_rename）4)下列（A）類型的索引總要對數(shù)據(jù)進(jìn)行排序

A.聚集索引B.非聚集索引C.組合索引D.惟一索引5)一個(gè)表最多允許有(249)個(gè)非聚集索引6)一個(gè)組合索引最多可包含(16)列

第七章T-SQL程序設(shè)計(jì)、自定義類型、函數(shù)和游標(biāo)

7.1批處理、腳本、注釋與變量7.1.1批處理的概念

批處理就是一個(gè)或多個(gè)相關(guān)SQL語句的集合，用GO語句作為批處理的結(jié)束標(biāo)志。若沒有GO語句，默認(rèn)所有的語句屬于一個(gè)批處理。

SQLServer的程序發(fā)送和編譯以批處理為一個(gè)程序執(zhí)行單元。如果一個(gè)批處理中任何一個(gè)語句有語法錯(cuò)誤，則整個(gè)批處理都不能執(zhí)行，若只是批處理中的某個(gè)語句有很執(zhí)行錯(cuò)誤，則該語句不能執(zhí)行，其他語句仍可以正常執(zhí)行。編寫SQL語句注意事項(xiàng)：

1)CreateDefault,Createrule,Createview,CreateProcedure,CreateTrigger，都必須單獨(dú)作為一個(gè)批處理，不能與其他語句放在一個(gè)批處理中。

2)不能創(chuàng)建定義check檢查索引后在同一個(gè)批處理中馬上使用這個(gè)約束。

3)不能把默認(rèn)值或規(guī)則對象綁定到字段或自定義類型以后，在同一個(gè)批處理中馬上使用它們。

4)在一個(gè)批處理中定義的局變量只在該批處理中有效，不能用于其他批處理。5)批處理結(jié)束語句GO必須單獨(dú)一行，可在其后使用注釋。

6)如果批處理第一個(gè)語句是執(zhí)行存儲過程，則語句開頭的execute關(guān)鍵字可以省略，否則不允許省略。

7.1.2SQL腳本文件

腳本就是包含一個(gè)或多個(gè)批處理的程序文件。

7.1.3SQL注釋

(1)行內(nèi)注釋：--(2)塊注釋：/**/

7.1.4局部變更和全局變量

分為兩類：局部變量和全局變量(1)局部變量1)聲明定義語法：Declare@變量類型(長度)說明：局部變量必須以@開頭以區(qū)別字段名變量。變量名必須符合標(biāo)識符的構(gòu)成規(guī)則。變量的數(shù)據(jù)類型可以是系統(tǒng)類型，也可以用戶自定義類型，但不允許是text,ntext和image類型2)賦值Set@變量名=表達(dá)式Select@變量名=表達(dá)式說明：Set只能給一個(gè)變量賦值，而select可以給多個(gè)變量賦值。表達(dá)式中可以包含select語句子查詢，但只能是集合函數(shù)返回的單值。且必須用圓括號括起來。Select也可以直接使用查詢的單值結(jié)果給局部變量賦值。Select@變量=表達(dá)式或字段from表3)用print、select顯示變量的值語法：Print表達(dá)式Select表達(dá)式說明：使用print必須有且只能有一個(gè)表達(dá)式，其值在查詢分析器的消息窗口顯示。Select可以有多個(gè)表達(dá)式，結(jié)果以數(shù)據(jù)表的格式在查詢分析器的柵格顯示。4)作用域

在一個(gè)批處理、一個(gè)存儲過程或一個(gè)觸發(fā)器內(nèi)，其生命周期從定義開始到它遇到第一個(gè)GO語句或者到存儲過程、觸發(fā)器結(jié)尾結(jié)束。即只在當(dāng)前批處理、存儲過程或觸發(fā)器內(nèi)有效。(2)全局變量:系統(tǒng)定義的無參函數(shù)

全局變量是由系統(tǒng)提供的有確定值的變量，用戶不能自定義全局變量，也不能用Set語句業(yè)修改全局變量的值，只可使用全局變量的值。全局變量都是以@@開頭的。@@error@@max_connections:@@connections:@@version@@cursor_rows@@fetch_status最后一次執(zhí)行錯(cuò)誤的SQL語句產(chǎn)生的錯(cuò)誤代碼SQLServer允許多用戶同時(shí)連接的最大數(shù)最近一次啟動(dòng)后已連接或嘗試連接的次數(shù)本地SQLServer服務(wù)器的版本信息得到已打開的游標(biāo)中當(dāng)前存在的記錄行數(shù)游標(biāo)的當(dāng)前狀態(tài)7.2T-SQL流程控制語句7.2.1Begin..End

語法：Begin語句1語句2End說明：無論多少個(gè)語句，放在Begin..end中間就構(gòu)成一個(gè)獨(dú)立的語句塊，被系統(tǒng)當(dāng)作一個(gè)整體單元來處理。條件的某個(gè)分支或循環(huán)體語句，如果要執(zhí)行兩個(gè)以上的復(fù)合語句，則必須將它們放在Beign..end中間作為一個(gè)單元來執(zhí)行。7.2.2IF/Else條件語句

語法：IF條件表達(dá)式語句塊1Else語句塊2說明：條件表達(dá)式中可包含select子查詢，但必須用圓括號括起來。ifexists(select*fromgoodswherestock提示信息=casewheng.Stock>=50then"貨源充足，不需考慮"wheng.Stock>=20then"可以維護(hù)，以后再說"wheng.Stock>=10then"已經(jīng)不多，準(zhǔn)備進(jìn)貨"wheng.Stock>=0then"馬上缺貨，抓緊進(jìn)貨"wheng.Stock=0then"已經(jīng)缺貨，馬上進(jìn)貨"endfromgoodsgleftjoinV_providervong.id=v.貨號7.2.4WaitFor暫停語句

語法：WaitForDelay‘時(shí)間’|Time‘時(shí)間’功能：使程序暫停指定時(shí)間后再繼續(xù)執(zhí)行。Delay:指定暫停時(shí)間長短相對時(shí)間。Time:指定暫停到什么時(shí)間再重新執(zhí)行程序絕對時(shí)間�！畷r(shí)間’參數(shù)必須是datatime類型的時(shí)間部分，格式為”hh:mm:ss”，不能含有日期部分select程序開始時(shí)間=getdate(),開始的時(shí)間秒數(shù)=Datepart(second,getdate())gowaitfordelay"00:00:20"--延遲20秒select延遲以后的時(shí)間=getdate(),延遲后的時(shí)間秒數(shù)=datepart(second,getdate())go7.2.5While循環(huán)語句

語法：While邏輯條件表達(dá)式Begin循環(huán)體語句系列[break][continue]Enddeclare@iint,@sumintselect@i=1,@sum=0while@i7.3.2創(chuàng)建自定義類型

語法：Sp_addtype數(shù)據(jù)類型名,系統(tǒng)數(shù)據(jù)類型名，Null|NotNull,所有者說明：凡是包含帶有長度的系統(tǒng)數(shù)據(jù)類型，如char(5)必須使用括號括起來用戶自定義類型的命名必須惟一，不同名字可以定義相同的類型。execsp_addtypeteletephone,"varchar(24)","notnull"execsp_addtypefax,"varchar(24)","null"7.3.3刪除自定義類型

Sp_dropType自定義數(shù)據(jù)類型名--刪除自定義數(shù)據(jù)類型execsp_droptypefax7.4用戶自定義函數(shù)

SQLServer201*支持三種用戶自定義函數(shù)，即標(biāo)量函數(shù)、內(nèi)嵌表值函數(shù)和多語句表值函數(shù)。

7.4.1創(chuàng)建自定義函數(shù)

語法：CreateFunction所有者.函數(shù)名[@參數(shù)名as數(shù)據(jù)類型[=默認(rèn)值]]returns返回值類型[as]Begin函數(shù)體SQL語句Return數(shù)值表達(dá)式End說明：自定義函數(shù)必須在當(dāng)前數(shù)據(jù)庫中定義。函數(shù)名：必須符合標(biāo)符構(gòu)成規(guī)則，必須惟一，所有者默認(rèn)系統(tǒng)管理員dbo.@參數(shù)名：用局部變量定義的形式參數(shù)，用于接收調(diào)用函數(shù)時(shí)傳遞過來的參數(shù)。調(diào)用與系統(tǒng)標(biāo)準(zhǔn)函數(shù)調(diào)用相同，但必須寫出“所有者名稱.函數(shù)名”并在圓括內(nèi)給出參數(shù)createfunction相對年齡(@出生年月datetime,@defyearint)returnsintasbeginreturn@defyear-year(@出生年月)endgoselectname,brithday,到201*的年齡=dbo.相對年齡(brithday,201*)fromemployeewheredbo.相對年齡(brithday,201*)7.5游標(biāo)的創(chuàng)建與使用7.5.1游標(biāo)的概念

游標(biāo)的主要用途是在T-SQL腳本程序、存儲過程和觸發(fā)器中對Select語句返回的結(jié)果集進(jìn)行逐行逐字段處理，把一個(gè)完整的數(shù)據(jù)表按行分開，一行一行地逐一提取記錄，并從這一記錄中逐一提取各項(xiàng)數(shù)據(jù)。

游標(biāo)與變量類似，必須先定義后使用。

游標(biāo)的使用過程：定義聲明游標(biāo)>打開游標(biāo)從游標(biāo)中提取記錄并分離數(shù)據(jù)關(guān)閉游標(biāo)釋放游標(biāo).

7.5.2定義游標(biāo)

語法：Declare游標(biāo)名Cursor[Forward_only|Scroll][Static|KeySet|Dynamic|Fast_ForWard][Read_Onley|Optimistic][Type_Warning]froselect語句[forUpdate[Of字段]]說明：Forward_only:指定該游標(biāo)的順序結(jié)果集，只能用next語向后方式順序提取記錄。SCRoll:滾動(dòng)結(jié)果集可以使用向前、向后或定位方式提取記錄。Static與Insensitive含義相同：在系統(tǒng)Tmepdb數(shù)據(jù)庫中創(chuàng)建臨時(shí)表存儲游標(biāo)使用的數(shù)據(jù),即游標(biāo)不會隨基本表內(nèi)容而變化，同時(shí)也無法通過游標(biāo)來更新基本表。KeySet:指定游標(biāo)中列的順序是固定的，并且在tempdb內(nèi)建立一個(gè)KeySet表，基本表數(shù)據(jù)修改時(shí)能返回到游標(biāo)中。如果基本表添加符合游標(biāo)的新記錄時(shí)該游標(biāo)無法讀取。如果游標(biāo)中的一行被刪除掉，則用游標(biāo)提取時(shí)@@Fetch_status返回-2。Dynamic:游標(biāo)中的數(shù)據(jù)將隨基本表而變化，但需要大量的游標(biāo)資源。Fast_ForWard指定ForWard_Only而且Read_only類型游標(biāo)。使用Fast_Forward參數(shù)則不能同時(shí)使用ForWard_only、Scroll、Optimistic或ForUpdate參數(shù).Optimistic指明若游標(biāo)中的數(shù)據(jù)已發(fā)生變化，則對游標(biāo)數(shù)據(jù)進(jìn)行更新或刪除時(shí)可能會導(dǎo)致失敗。Type_Warning:游標(biāo)中的數(shù)據(jù)類型被修改成其他類型時(shí)，給客戶端發(fā)送警告。7.5.3打開游標(biāo)

語法：Open[Global]游標(biāo)名說明：Global:打開全局游標(biāo)。打開游標(biāo)后，可以使用全局變量@@Error判斷該游標(biāo)是否打開成功。0成功@@Cursor_Rows可得到打開的游標(biāo)中當(dāng)前存在的記錄行數(shù)。其返回值為：0:無符合條件的記錄或該游標(biāo)已經(jīng)關(guān)閉或翻譯。-1:該游標(biāo)為動(dòng)態(tài)的，記錄行經(jīng)常變動(dòng)無法確定。n:指定的結(jié)果集已從表中全部讀入，總共n條記錄。-m:指定的結(jié)果集還沒有全部讀入，目前游標(biāo)中有m條記錄。7.5.4用Fetch語句從游標(biāo)中提取數(shù)據(jù)語法：Fetch[next|prior|first|last|absolute{n|@nvar}|relative{n|@nvar}]From[Global]游標(biāo)名[into@變量名]說明：在游標(biāo)內(nèi)有一個(gè)游標(biāo)指針Cursor指向游標(biāo)結(jié)果集的某個(gè)記錄行稱為當(dāng)前行，游標(biāo)剛打開時(shí)Cursor指向游標(biāo)結(jié)果集第一行之前。Fetch之后的參數(shù)為提取記錄的方式，可以是以下方式之一：Next:順序向下提取當(dāng)前行的下一行。處理完最后一行，再用FetchNext則Cursor指向結(jié)果集最后一行之后，@@Fetch_status的值為-1.Prior:順序向前提取當(dāng)前記錄的前一行，并將其作為當(dāng)前行。第一次用FetchPrior對游標(biāo)操作時(shí)，沒有返回記錄返回，游標(biāo)指針Cursor為指向第一行之前。First:提取第一條。Last:提取最后一條。Absolute{n|@nvar}：按絕對位置提取游標(biāo)結(jié)果集的第n或第@nvar條記錄。n為負(fù)提取結(jié)行之前。n為整數(shù)。Relative{n|@nvar}:按相對位置提取當(dāng)前記錄之后（正數(shù)）之前（負(fù)數(shù)）的第n或@nvar條記錄。打開游標(biāo)后用@@Fetch_Status檢測游標(biāo)的當(dāng)前狀態(tài)：0:Fetch語句提取記錄成功：-1:失敗或提取記錄不在結(jié)果集內(nèi)-2:被提取的記錄已被刪除或根本不存在。@@Fetch_Status只能檢測游標(biāo)提取記錄后的狀態(tài)，若用循環(huán)條件輸出多條記錄時(shí)，必須在循環(huán)之前先用Fetch提取一條記錄。7.5.5關(guān)閉游標(biāo)語法：Close[Global]游標(biāo)名作用：翻譯游標(biāo)中結(jié)果集，解除游標(biāo)記錄行上的游標(biāo)指針。當(dāng)游標(biāo)提取記錄完畢后，應(yīng)及時(shí)關(guān)閉該游標(biāo)釋放結(jié)果集的內(nèi)存空間。游標(biāo)關(guān)閉后，其定義結(jié)構(gòu)仍然存儲在系統(tǒng)中，但不能提取記錄和定位更新，需要時(shí)可以和Open再次打開7.5.6釋放游標(biāo)語法：Deallocate[Global]游標(biāo)名作用：刪除指定的游標(biāo)，釋放該游標(biāo)所占用的所有系統(tǒng)資源。declareCsEmployeecursorkeyset--定義游標(biāo)forselectname,sex,age=datediff(yy,brithday,getdate()),departmentfromemployeewheredatediff(yy,brithday,getdate())0beginprint"游標(biāo)記錄數(shù)為:"+convert(varchar(2),@@cursor_rows)declare@xmvarchar(8),@xbnchar,@nlint,@bmnvarchar(5),@rsint,@pjnlintfetchabsolute2fromCsEmployeeinto@xm,@xb,@nl,@bm--提取記錄print"第2條記錄："+@xm+@xb+@bmfetchrelative2fromCsEmployeeinto@xm,@xb,@nl,@bmprint"后移2條記錄:"+@xm+@xb+cast(@nlaschar(2))+@bmset@rs=0;set@pjnl=0;print"全部記錄為："fetchfirstfromCsEmployeeinto@xm,@xb,@nl,@bm--先提取第一條記錄while@@fetch_status=0beginprintcast(@rs+1aschar(2))+":"+@xm+@xb+cast(@nlaschar(2))+@bmfetchnextfromCsEmployeeinto@xm,@xb,@nl,@bmset@rs=@rs+1set@pjnl=@pjnl+@nlendprint"實(shí)際統(tǒng)計(jì)記錄數(shù)為"+cast(@rsaschar(2))+"平均年齡為:"+cast(@pjnl/@rsaschar(6))endendelseprint"游標(biāo)打開失敗!"closeCsEmployeedeallocateCsEmployee習(xí)題：

1.什么是批處理批處理用（）作結(jié)束樗,建立批處理有哪些注意事項(xiàng)?2.什么是腳本?腳本文件的擴(kuò)展名是（），執(zhí)行腳本的方法是（）、（）。3.注釋是程序中不被執(zhí)行的正文，其作用是（），SQLServer中的注釋語句有（）和（）。4.T-SQL的局部變量用（）聲明，給變量賦值的語句是（）、（），輸出語句是（）、（）。5.局部變量的作用域是（），從（）開始，到（）結(jié)束。6.CASE表達(dá)式用于（），它可以用在（）地方并根據(jù)條件的不同而返回（）。CASE表達(dá)式它不能單獨(dú)執(zhí)行，而只能作為（）來使用。CASE表達(dá)式分為（）和（）兩種類型。7.執(zhí)行WHILE語句時(shí)，當(dāng)條件成立時(shí)（），當(dāng)條件不成立時(shí)（）。在循環(huán)體內(nèi)使用BREAK或CONTNUE，可以（）。

8.用戶自定義數(shù)據(jù)類型的作用是（），用戶自定義數(shù)據(jù)類型用（）語句創(chuàng)建，用（）語句刪除。

9.用（）語句創(chuàng)建自定義函數(shù)，函數(shù)參數(shù)的作用是（），用（）指定返回類型，用（）指定返回值。

10.定義游標(biāo)用（）語句，打開游標(biāo)用（），提取數(shù)據(jù)用（），關(guān)閉用（），釋放刪除用（）。11.下列語句能否正確執(zhí)行？為什么？DECLARE@assvarchar（50）SET@ass=’sadfasf’GO

PRINT@ass

12.SQLServer提供的注釋類型有（）兩項(xiàng)。

13.@n是使用Declare語句聲明的一個(gè)局部變量，能對變變量賦值的語句是（）。14.閱讀下面T-SQL語句，對變量賦值時(shí)存在錯(cuò)誤的是（）兩項(xiàng)。15.下列（）語句可以用來從WHILE語句塊中退出。

16.要將一組語句執(zhí)行10次。下列（）結(jié)構(gòu)可以用來完成此項(xiàng)任務(wù)。17.有以下代碼：Declare@xintSet@x=1While@x語法：Createprocedure存儲過程名[;整數(shù)][@形參數(shù)據(jù)類型][Varying][=默認(rèn)值][output][withrecompile|encryption|{recompile,encryption}][forreplication]AsSQL語句系列遵守規(guī)則：名稱標(biāo)識符的長度最大為128個(gè)字符，且必須唯一。每個(gè)存儲過程最多可以使用1024個(gè)參數(shù)。存儲過程的最大容量有一定的限制。存儲過程支持多達(dá)32層嵌套。在對存儲過程命鋰最和系統(tǒng)存儲過程區(qū)分。說明：可以創(chuàng)建永久存儲過程，也可以創(chuàng)建一個(gè)在一個(gè)會話中臨時(shí)使用的局部存儲過程（名稱前加#），還可以創(chuàng)建一個(gè)在所有會話中臨時(shí)使用的全局存儲過程（名稱前加##）整數(shù)：可作為同名過程分組的后綴序號。同組的過程將來可以用一條dropproedure全部刪除@形參：所有數(shù)據(jù)類型都可以作為參數(shù)類型。Varying:僅適用于游標(biāo)參數(shù)，指定形參變量可作支持結(jié)果集返回參數(shù)。Recompile:執(zhí)行完存儲過程后不保留存儲過程的備份，每次執(zhí)行都需要對存儲過程重新編譯。Encryoption:存儲過程作為數(shù)據(jù)庫對象在系統(tǒng)的syscomments表中留下完整的代碼信息，并對訪問這些數(shù)據(jù)的入口進(jìn)行加密。createprocgood_pro1@goodnamenvarchar(8)asselects.datetime,p.pname,g.Name,s.num,s.receivepricefromGoodsgjoinstocksons.goodid=g.Idjoinproviderponp.pid=s.provideridwhereg.name=@goodnameexecgood_pro1"計(jì)算機(jī)"8.1.3用execute執(zhí)行存儲過程語法：Execute@整型變更=存儲過程名[參數(shù)列表]8.1.4用execute執(zhí)行SQL語句Execute(@字符串變量|[n]’SQL語句字符串’)exec(N"select*fromgoods")exec("select*fromgoods")8.2.2查看存儲過程定義查看存儲過程的定義Sp_helptext存儲過程名加密看不到查看參數(shù)及一般信息sp_help存儲過程名查直相關(guān)信息sp_depends存儲過程名8.2.3修改、刪除存儲過程修改存儲過程Alterproc存儲過程名asSQL語句重命名Sp_rename原名，新名刪除Dropproe存儲過程名8.3觸發(fā)器的創(chuàng)建和使用8.3.1觸發(fā)器的概念(1)觸發(fā)器

是一段自動(dòng)執(zhí)行的程序，是一種特殊的存儲過程，其特殊在于：1)不允許使用參數(shù)，沒有返回值；2）不允許用戶調(diào)用，當(dāng)對表進(jìn)行插入、刪除和修改操作時(shí)由系統(tǒng)自動(dòng)調(diào)用并執(zhí)行。

觸發(fā)器可以實(shí)現(xiàn)復(fù)雜的完整性約束：1）擴(kuò)展約束、默認(rèn)值的規(guī)則對象的完整性檢查。2）自動(dòng)生成數(shù)據(jù)。3)檢查數(shù)據(jù)的修改，防止對數(shù)據(jù)不正確的修改，保證數(shù)據(jù)表之間數(shù)據(jù)的正確性和一致性。4)自定義復(fù)雜的安全權(quán)限(2)用途和優(yōu)點(diǎn)

實(shí)現(xiàn)數(shù)據(jù)庫中多個(gè)表的級聯(lián)修改；檢查數(shù)據(jù)輸入的正確性；檢查數(shù)據(jù)修改的正確性。(3)觸發(fā)器的觸發(fā)方式

在數(shù)據(jù)表中某個(gè)字段設(shè)置觸發(fā)器后，當(dāng)該字段的數(shù)據(jù)被插入、刪除或修改更新時(shí)，觸發(fā)器便被激活并自動(dòng)執(zhí)行。

SQLServer按觸發(fā)器被激活的時(shí)機(jī)分為“后觸發(fā)”“替代觸發(fā)”兩種。

1)后觸發(fā)：或引發(fā)觸發(fā)器執(zhí)行的語句通過了各種約束檢查，成功執(zhí)行后才激活并執(zhí)行觸發(fā)器程序。特點(diǎn)：若引發(fā)觸發(fā)器執(zhí)行的語句違反了某種約束，該語句不會執(zhí)行，則后觸發(fā)方式的觸發(fā)也不會被激活；只能創(chuàng)建在數(shù)據(jù)表上，不能創(chuàng)建在視圖上；一個(gè)表可以有多個(gè)后觸發(fā)觸發(fā)器。

2)替代觸發(fā)：若激活觸發(fā)器的語句僅僅起到激活觸發(fā)器的作用，一旦激活觸發(fā)器后該語句即停止執(zhí)行，立即轉(zhuǎn)去執(zhí)行觸發(fā)器的程序邏輯激活觸發(fā)器語句并不被執(zhí)行，相當(dāng)于禁止某種操作。特點(diǎn)：保以創(chuàng)建在表上，也可創(chuàng)建在視圖上。一個(gè)表只能有一個(gè)替代觸發(fā)的觸發(fā)器。（4）臨時(shí)表

無論后觸發(fā)或替代觸發(fā)，每個(gè)觸發(fā)器被激活時(shí)，系統(tǒng)都自動(dòng)為它們創(chuàng)建兩個(gè)臨時(shí)表inserted和deleted表。

兩個(gè)表的結(jié)構(gòu)與激活觸發(fā)器的原數(shù)據(jù)表結(jié)構(gòu)相同。

用Insert語句插入記錄激活觸發(fā)器時(shí)，系統(tǒng)在原表中插入記錄的同時(shí)，也自動(dòng)把插入的記錄插入到inserted臨時(shí)表。

用delete語句刪除記錄激活觸發(fā)器時(shí)，系統(tǒng)在原表中刪除記錄的同時(shí)，會自動(dòng)把刪除的記錄添加到deleted臨時(shí)表。

有update語句修改數(shù)據(jù)激活觸發(fā)器時(shí)，系統(tǒng)在先原的表中刪除其有的記錄，刪除的記錄被添到deleted臨時(shí)表，然后再插入新數(shù)據(jù)記錄，新插入的記錄同時(shí)被插入到inserted臨時(shí)表。

用戶可用select語句查詢這個(gè)臨時(shí)表，但不允許進(jìn)行修改。觸發(fā)器一旦執(zhí)行完成，這個(gè)表將被自動(dòng)刪除。8.3.2創(chuàng)建觸發(fā)器語法：CreateTrigger[擁有者.]觸發(fā)器名On[擁有者.]表名|視圖名{fro|after|insteadof}[inset,update,delete][withencryption][notfromreplication]As[setnocunot]不返回給變更賦值的結(jié)果SQL語句系統(tǒng)[RollbackTransaction]--事務(wù)回滾說明：For與alter：相同，創(chuàng)建后觸發(fā)觸發(fā)器Insteadof:創(chuàng)建替代觸發(fā)觸發(fā)器.Withencryption:對觸發(fā)器文本進(jìn)行加密，禁止查閱修改。Notforreplication:在復(fù)制過程中，不激活觸發(fā)器操作。Setnocount:觸發(fā)器一般不能有返回值，所以也不應(yīng)該用select語句進(jìn)行查詢或給變量賦值，如果必須使用變量賦值語句，可能在開頭使用該語句避免返回結(jié)果。SQL語句:即可以包含獲得被操作的select語句。后觸方式被操作數(shù)據(jù)一定在inserted或deleted臨時(shí)表中。如果被操作的數(shù)據(jù)是多值的，可用in判斷是否被包含在其中：被操作數(shù)據(jù)in(select被操作字段from臨時(shí)表)如果被操作的數(shù)據(jù)是單值，可用下面語句Select@變量=被操作字段from臨時(shí)表``說明：Createtrigger語句必須是一個(gè)批處理的第一條語句。創(chuàng)建觸發(fā)器的權(quán)限默認(rèn)屬于表的所有者，而且不能授權(quán)給其他人。觸發(fā)器不能在臨時(shí)表或系統(tǒng)一是創(chuàng)建，后觸發(fā)也不能創(chuàng)建在視圖上。一個(gè)觸發(fā)器只能創(chuàng)建在一個(gè)表上；一個(gè)表可以有有一個(gè)替代觸發(fā)器和多個(gè)后觸發(fā)器（可以是同一種操作類型，可同時(shí)觸發(fā)）由于TruncateTable語句刪除記錄時(shí)不被記入事務(wù)日志，所以該該語句不能激活deleted刪除操作的觸發(fā)器如果外鍵所引用的父表已創(chuàng)建了對子表級聯(lián)修改或刪除的觸發(fā)器，則子表不允許創(chuàng)建具有相同動(dòng)作的替代觸發(fā)器。觸發(fā)器的定義中不能有任何create,alter語句。也不允許使用drop刪除語句，也不允許使用下列語句：Grant/restoredatabase/restorelogrevoke/truncatetablecreatetriggerDelGoodsongoodsfordeleteasdeleteSalewheregoodidin(selectidfromdeleted)8.3.4禁用/啟用觸發(fā)器禁用：altertable表名disabletrigger觸發(fā)器名啟用：altertable表名enabletrigger觸發(fā)器名8.4觸發(fā)器的查看、編輯、重命名與刪除查看觸發(fā)器基本信息：Sp_help觸發(fā)器名查看觸發(fā)器定義：sp_helptext觸發(fā)器名查看依賴關(guān)系：sp_depends觸發(fā)器名查看指定表指定類型的觸發(fā)器信息Sp_helpTrigger表名,[insert|update|delete]8.4.3修改和刪除觸發(fā)器修改AlterTrigger[擁有者.]觸發(fā)器名On[擁有者.]表名|視圖名{fro|after|insteadof}[inset,update,delete][withencryption][notfromreplication]As[setnocunot]不返回給變更賦值的結(jié)果SQL語句系統(tǒng)[RollbackTransaction]--事務(wù)回滾刪除Droptrigger觸發(fā)器名第九章SQLServer的權(quán)限管理與代理服務(wù)臺9.1SQLServer的安全機(jī)制

SQLServer201*的安全性管理是建立在認(rèn)證和訪問許可兩者機(jī)制上的。

在SQLServer的安全模型中包括幾個(gè)部分：SQLServer登錄，數(shù)據(jù)庫用戶，權(quán)限，角色。9.1.1SQLServer登錄認(rèn)證簡介

SQLServer參在兩種安全模式下運(yùn)行：Windows認(rèn)證模式和混合混式。

SQLServer認(rèn)證模式下，用戶在SQLServer時(shí)必須提供登錄名和登錄密碼，SQLServer自己執(zhí)行認(rèn)證處理，它將會與存儲在系統(tǒng)表syslogins中的登錄信息驗(yàn)證。9.2.2使用T-SQL管理SQLServer登錄(1)sp_addlogin創(chuàng)建新的使用SQLServer認(rèn)證模式登錄帳號：Sp_addlogin‘登錄名’，’登錄密碼’,’默認(rèn)數(shù)據(jù)庫’,’默認(rèn)語言’其中登錄名和密碼可包含1128個(gè)字符，由字母、漢字和數(shù)據(jù)組成。不能包含\\，保留的登錄名稱sa或已存在的登錄名，也不能是空字符串或null.execsp_addlogin"User1","User1","pubs","us_english"(2)sp_droplogin刪除登錄帳號，禁止防問SQLServerSp_droplogin‘登錄名稱’execsp_droplogin"User1"不能刪除系統(tǒng)管理者sa以及當(dāng)前連接到SQLServer的登錄；如果與登錄相匹配的用戶仍存在sysusers表中，則不能刪除該登錄帳號；sp_addlogin和sp_droplogin只能在用SQLServer認(rèn)證模式下。(3)sp_grantLogin設(shè)WindowsNT用戶或用戶組為SQLServer登錄者Sp_grantlogin‘登錄名稱’(4)sp_denylogn拒絕NT用戶或用戶組連接到SQLServer。Sp_grantlogin‘登錄名稱’(5)sp_revokelogin刪除NT用戶或用戶組在SQLServer上的登錄信息。Sp_revokelogin‘登錄名稱’(6)sp_helplogins顯示SQLServer所有登錄者的信息，包括每一個(gè)數(shù)據(jù)庫里與該登錄者相對應(yīng)的用戶名稱。Sp_helplogins‘登錄名’9.3數(shù)據(jù)庫用戶

數(shù)據(jù)庫用戶用來指出哪一個(gè)人可以訪問哪一個(gè)數(shù)據(jù)庫。在一個(gè)數(shù)據(jù)庫中用戶ID惟一標(biāo)識一個(gè)用戶，用戶對數(shù)據(jù)的訪問權(quán)限以及對數(shù)據(jù)庫對象的所有關(guān)系都是通過用戶帳號來控制的。用戶帳號總是基于數(shù)據(jù)庫的，即兩個(gè)不同數(shù)據(jù)庫中可以有兩個(gè)相同的用戶帳號。

在數(shù)據(jù)庫中用戶帳號與登錄登錄是兩個(gè)不同概念。一個(gè)合法的登錄帳號表明該帳號通過了NT認(rèn)證或SQLServer認(rèn)證，但不能表明其可以對數(shù)據(jù)庫數(shù)據(jù)和數(shù)據(jù)對象進(jìn)行某種或某些操作，所以一個(gè)登錄帳號總是一個(gè)或多個(gè)數(shù)據(jù)庫用戶帳號相對尖，這樣才可以訪問數(shù)據(jù)庫。通過，數(shù)據(jù)庫用戶帳號總是與某一登錄帳號相關(guān)聯(lián)，但有一個(gè)例那guest.在安裝系統(tǒng)時(shí)guest用戶被加入到master,pubs,tempdb和northwoind中。

用戶通過NT或SQLServer認(rèn)證成功登錄到SQLServer之后，SQLServer又做了哪些事呢？1)SQLServer檢查該登錄用戶是否有合法的用戶名，如果有合法的用戶，則允許其以用戶名訪問數(shù)據(jù)庫；否則，執(zhí)行第二步。2）SQLServer檢查是否有g(shù)uest用戶，如果有，則允許登錄用戶以guest用戶來訪問數(shù)據(jù)庫，如果沒有，則該登錄用戶被拒絕。

Grust用戶主要是讓那些沒有屬于自己的用戶帳號的SQLServer登錄者把其用為默認(rèn)用戶，從而使該登錄者能夠訪問具有g(shù)uest用戶的數(shù)據(jù)庫。9.3.2管理數(shù)據(jù)庫用戶1)創(chuàng)建新的數(shù)據(jù)庫用戶Sp_grantdbaccess‘登錄帳號名’,’用戶帳號名稱’2)刪除數(shù)據(jù)庫用戶]Sp_revokedbaccess‘用戶帳號名稱’3)查看數(shù)據(jù)庫用戶信息Sp_helpuser‘用戶帳號名稱’9.4權(quán)限管理

T-SQL命名規(guī)則

1表名

XXX相關(guān)表以r_作為前綴，YYY相關(guān)表以t_作為前綴。如r_acc、t_bcc。后臺表名盡量與前臺表名相同，后臺獨(dú)有的表應(yīng)以_b作為后綴。如r_gggd_b。

命名應(yīng)盡量反映存儲的數(shù)據(jù)內(nèi)容。

2視圖名

視圖以v_作為前綴。由于前臺無視圖，故不需加_b。命名應(yīng)盡量體現(xiàn)各視圖的功能。3觸發(fā)器名

觸發(fā)器名為相應(yīng)的表名加上后綴，Insert觸發(fā)器加"_i"，Delete觸發(fā)器加"_d"，Update觸發(fā)器加"_u"，如：r_bch_i，r_bch_d，r_bch_u。

4存儲過程名

存儲過程應(yīng)以"sp_"開頭，后續(xù)部分主要以動(dòng)賓形式構(gòu)成，并用下劃線分割各個(gè)組成部分。如增加BSC機(jī)架的DRT單板的存儲過程為"sp_ins_board_drt"。5變量名

變量名采用小寫，若屬于詞組形式，用下劃線分隔每個(gè)單詞，如@my_err_no。6命名中其他注意事項(xiàng)

以上命名都不得超過30個(gè)字符的系統(tǒng)限制。變量名的長度限制為29（不包括標(biāo)識字符@）。

數(shù)據(jù)對象、變量的命名都采用英文字符。禁止使用中文命名。

編程結(jié)構(gòu)和描述

SQLSERVER系統(tǒng)中，一個(gè)批處理是從客戶傳給服務(wù)器的一個(gè)完整的包，可以包含若干條SQL語句。批處理中的語句是作為一組去進(jìn)行語法分析、編譯和執(zhí)行的。觸發(fā)器、存儲過程等數(shù)據(jù)對象則是將批處理永久化的方法。

注釋:注釋可以包含在批處理中。在觸發(fā)器、存儲過程中包含描述性注釋將大大增加文本的可讀性和可維護(hù)性。本規(guī)范建議：

1、注釋以英文為主。實(shí)際應(yīng)用中，發(fā)現(xiàn)以中文注釋的SQL語句版本在英文環(huán)境中不可用。為避免后續(xù)版本執(zhí)行過程中發(fā)生某些異常錯(cuò)誤，建議使用英文注釋。

2、注釋盡可能詳細(xì)、全面。

創(chuàng)建每一數(shù)據(jù)對象前，應(yīng)具體描述該對象的功能和用途。

傳入?yún)��?shù)的含義應(yīng)該有所說明。如果取值范圍確定，也應(yīng)該一并說明。取值有特定含義的變量（如boolean類型變量），應(yīng)給出每個(gè)值的含義。

3、注釋語法包含兩種情況：單行注釋、多行注釋

單行注釋：注釋前有兩個(gè)連字符（--），最后以行尾序列（CR-LF）結(jié)束。一般，對變量、條件子句可以采用該類注釋。多行注釋：符號/*和*/之間的內(nèi)容為注釋內(nèi)容。對某項(xiàng)完整的操作建議使用該類注釋。4、注釋簡潔，同時(shí)應(yīng)描述清晰。

函數(shù)注釋:編寫函數(shù)文本--如觸發(fā)器、存儲過程以及其他數(shù)據(jù)對象--時(shí)，必須為每個(gè)函數(shù)增加適當(dāng)注釋。該注釋以多行注釋為主，主要結(jié)構(gòu)如下：

/*************************************************************************name:--函數(shù)名*function:--函數(shù)功能*input:--輸入?yún)��?shù)*output:--輸出參數(shù)*author:--作者*CreateDate:--創(chuàng)建時(shí)間*UpdateDate:--函數(shù)更改信息（包括作者、時(shí)間、更改內(nèi)容等）*************************************************************************/

CREATEPROCEDUREsp_xxx

條件執(zhí)行語句ifelse

條件語句塊（statenemtblock，以beginend為邊界）僅在if子句的條件為真時(shí)才被執(zhí)行。為提高代碼的可讀性，建議嵌套不多于5層。還有，當(dāng)嵌套層次太多時(shí)，應(yīng)該考慮是否可以使用case語句。重復(fù)執(zhí)行while和跳轉(zhuǎn)語句goto

需要多次執(zhí)行的語句，可以使用while結(jié)構(gòu)。其中，控制while循環(huán)的條件在任何處理開始之前需要先執(zhí)行一次。循環(huán)體中的保留字break無條件的退出while循環(huán)，然后繼續(xù)處理后續(xù)語句；保留字continue重新計(jì)算while條件，如果條件為真，則從循環(huán)開始處重新執(zhí)行各語句。

使用跳轉(zhuǎn)語句goto和標(biāo)簽label也可以方便地實(shí)現(xiàn)循環(huán)和其他更靈活的操作。SQLSERVER僅具有單通道語法分析器，因此不能解析對尚未創(chuàng)建的對象所做的前向參考。換言之，跳轉(zhuǎn)到某標(biāo)簽的后續(xù)語句應(yīng)該是可執(zhí)行的（如不存在可能尚未創(chuàng)建的數(shù)據(jù)對象）。書寫格式

數(shù)據(jù)庫服務(wù)器端的觸發(fā)器和存儲過程是一類特殊的文本，為方便開發(fā)和維護(hù)，提高代碼的易讀性和可維護(hù)性。規(guī)范建議按照分級縮進(jìn)格式編寫該文本。順序執(zhí)行的各命令位于同一級；條件語句塊（statenemtblock，以beginend為邊界）位于下一級，類推。

SQL語句是該文本的主體。為適應(yīng)某些教復(fù)雜的用戶需求，SQL語句可能比較龐大。為方便閱讀和維護(hù)，規(guī)范建議按照SQL語句中系統(tǒng)保留字的關(guān)鍵程度再劃分為三級。具體分級請參照下表。其中，非系統(tǒng)保留字（如字段名、數(shù)據(jù)表名、標(biāo)點(diǎn)符號）相對本級保留字再縮進(jìn)一級。多個(gè)連續(xù)的非保留字可以分行書寫，也可以寫在同一行。當(dāng)WHERE包含的條件子句教復(fù)雜時(shí)，應(yīng)該每行只寫一個(gè)條件分句，并為重要的條件字句填寫單行注釋。

在保證基本縮進(jìn)格式的前提下，可以通過對齊某些重要關(guān)鍵字（如條件關(guān)鍵字AND、OR，符號=、等）來進(jìn)一步提高文本的易讀性和可維護(hù)性。相鄰兩級的縮進(jìn)量為10個(gè)空格。這也是ISQL編輯器默認(rèn)的文本縮進(jìn)量。另外，在ISQL編輯器中，一個(gè)TAB鍵也相當(dāng)于10個(gè)空格。注：按照功能，四類SQL語句（SELECT、INSERT、UPDATE、DELETE）的關(guān)鍵字可以劃分為三類：主關(guān)鍵字、次關(guān)鍵字、一般關(guān)鍵字。如下表所示：主關(guān)鍵字次關(guān)鍵字一般關(guān)鍵字

SELECTINSERT（INTO）UPDATEDELETE

FROMWHEREVALUESINSERTSELECTFROM語句中的SELECT和FROMANDORBETWEENINLIKE字體

系統(tǒng)保留字應(yīng)大寫，包括系統(tǒng)公共變量等。其他字符（如用戶自定義變量、用戶自定義數(shù)據(jù)對象名）小寫。需要特殊強(qiáng)調(diào)的部分可以大寫。一條完整注釋語句的首字符應(yīng)大寫。對某變量、某條件字句的注釋可以全部使用小寫。

SQLServer單詞表

單詞Primarykey主鍵含義單詞Identity含義自動(dòng)編號

友情提示：本文中關(guān)于《sqlserver知識總結(jié)》給出的范例僅供您參考拓展思維使用，sqlserver知識總結(jié)：該篇文章建議您自主創(chuàng)作。

來源：網(wǎng)絡(luò)整理 免責(zé)聲明：本文僅限學(xué)習(xí)分享，如產(chǎn)生版權(quán)問題，請聯(lián)系我們及時(shí)刪除。

《sqlserver知識總結(jié)》由互聯(lián)網(wǎng)用戶整理提供,轉(zhuǎn)載分享請保留原作者信息,謝謝!
鏈接地址：http://m.seogis.com/gongwen/672253.html

• 上一篇：數(shù)據(jù)庫 學(xué)習(xí)總結(jié)
• 下一篇：Access總結(jié)報(bào)告