SQL數(shù)據(jù)庫知識總結(jié)

數(shù)據(jù)庫知識總結(jié)

1、創(chuàng)建數(shù)據(jù)庫（命令）

CreatedatabaseBBSOn

(name=BBS_dat,filename=’E:\\\\BBS.mdf’)Logon

(name=BBS_log,filename=’E:\\\\BBS.ldf’)

2、創(chuàng)建關(guān)系（表）（命令）

CreatetablestuInfo

(stuNamevarchar(20)notnull,

stuNovarchar(20)primarykeynotnull,--設(shè)置主鍵stuSexvarchar(10)default("男")notnull,--設(shè)置默認值stuSeatintidentity(0,2)notnull,--設(shè)置標識列，種子，增值stuAddressvarchar(100)default("地址不詳")notnull,check(stuage>15andstuage貨幣數(shù)據(jù)類型Bit數(shù)據(jù)類型貨幣數(shù)據(jù)類型用于十進制貨幣值，并且精確到小數(shù)點后面4位數(shù)字表示是/否的數(shù)據(jù)，用1和0表示，一般1表示是，0表示否MoneyBit布爾數(shù)據(jù)類型Unicode為國際編碼

3、約束（constraint）

約束用來保證數(shù)據(jù)完整性，完整性=可靠性+完整性

五種約束：主鍵約束、外鍵約束、檢查約束、默認約束和唯一性約束

主鍵約束（primarykey）：主鍵只能有一個（非空的、唯一的），可以包含多列（稱為：附和鍵），選擇主鍵的原則：最少性和穩(wěn)定性一般使用標識列（int型，自增長），以保證主鍵的唯一性

外鍵約束（foreignkey）：

外鍵（引用鍵）就是“子表”中對應(yīng)于“主表”的列，它的值要求與主表的主鍵或唯一鍵相對應(yīng)，一個表可以有多個外鍵

表間關(guān)系：主表和外表的關(guān)系是一對多的關(guān)系，一方是主表，多方是子表，在子表中加外鍵約束子表中的相關(guān)項的數(shù)據(jù)，在主表中必須存在主表中相關(guān)項的數(shù)據(jù)更改了，則子表對應(yīng)的數(shù)據(jù)項也要隨之更改在刪除子表前，不能夠刪除主表

檢查約束（Check）：Check約束表達式為boolean類型表達式

4、表達式

比較運算符運算符=><>=%[][^]

任意長度的字符串包含空括號內(nèi)所指定范圍內(nèi)的一個字符不在括號中所指定防衛(wèi)內(nèi)的任意一個字符Blike‘C_%’Clike‘9W0[1-2]’Dlike‘9W0[^1-2]’邏輯表達式And同&&Or同||Not同！用in關(guān)鍵字來限制范圍例：not(card=’信用卡’)or(信用卡in(‘牡丹卡’,‘龍卡’,‘陽光卡’))

5、增

插入單行數(shù)據(jù)

InsertintoBBSUsers(id,number,)Values(‘5’,’BB543’,)注：如果要插入所有列，則可省略列名；無視標識列存在（全列可省列名，不可提供標識列名；default可提供默認值；所有列都可加單引號，money除外）插入多行數(shù)據(jù)

InsertintoUsers(Uid,Unumber,)Selectid,number,FromBBSUsers

注：須先建好Users關(guān)系

SelectBBSUsers.id,BBSUsers.number,）IntoUsersFromBBSUsers

注：執(zhí)行語句時創(chuàng)建Users關(guān)系InsertintoBBSUsers(id,number,)Select‘1’,’BB173’,unionSelect‘2’,’BB143’,unionSelect‘3’,’BB129’,union

Select‘20’,’BB125,注：不可使用default關(guān)鍵字

排錯

1、個數(shù)要對2、數(shù)據(jù)類型相符3、不能有標識符4、不能違反約束

6、改

Update關(guān)系名set列名=更新值where更新條件

注：條件為boolean類型表達式

7、刪

Deletefrom關(guān)系名Where刪除條件

注：用于刪除關(guān)系中指定對象，如有外鍵約束，則先清空子表中的數(shù)據(jù)；刪除后，標識繼續(xù)往下排列，條件為boolean類型表達式

Truncatetable關(guān)系名

注：執(zhí)行清空關(guān)系中的對象，如有外鍵約束，則先清空子表中的數(shù)據(jù)；清空后，標識列從原始值開始Dropdatabase數(shù)據(jù)庫名注：刪除整個數(shù)據(jù)庫Droptable表名注：刪除整個表

8、查★

基本語法

Select列名或*或計算列或常量列注：常量列關(guān)鍵字：asFrom表名

Where查詢條件注：列isnull查詢列為空值的行，and/or/notOrderby排序的列名[asc或desc]注：asc為升序，desc為降序

查詢返回限制的行數(shù)Selecttop5--顯示前5行數(shù)據(jù)Selecttop5percent--顯示前5%行數(shù)據(jù)

使用IN在列舉值內(nèi)進行查詢列In(1,3,5)--顯示值為1、3、5的行列notIn(1,3,5)--排除值為1、3、5的行注：接條件(where/having/on)關(guān)鍵字之后

聚合函數(shù)

Sum（）

返回表達式中所有數(shù)值的總和，只能用于數(shù)字類型的列，不能直接與返回多行的列一起進行查詢

Avg（）

返回表達式中所有數(shù)值的平均值Max（）

返回表達式中的最大值Min（）

返回表達式中的最小值

Count（）

返回提供的表達式中非空值的計數(shù)，可用于數(shù)字和字符類型的列

Groupby分組查詢Groupby列名--對列中的值分組，一般和聚合函數(shù)一起使用，分組后的篩選條件只能是having

查詢步驟：1、from選擇關(guān)系2、用where篩選3、用groupby分組

4、用having篩選

注：where接普通列條件，having接聚合條件

多表聯(lián)接查詢

內(nèi)聯(lián)：列數(shù)是兩表之和，行數(shù)是笛卡爾積（交叉相乘）選擇行Select*from表名1innerjoin表名2On表名1.列名=表名2.列名Innerjoin表名3

On表名1.列名=表名2.列名

無敵聯(lián)接

一Selectt1.列名，表名3.列名

from(Select表名1.列名，表名2.列名

from表名1innerjoin表名2On表名1.列名=表名2.列名)ast1innerjoin表名3On表名3.列名=t1.列名

二、Selectdistinctt1.列名，表名3.列名distinct集合重復(fù)的列

from(Select表名1.列名，表名2.列名

from表名1innerjoin表名2On表名1.列名=表名2.列名)ast1innerjoin表名3On表名3.列名notin（t1.列名）排除=t1.列名的行字符串函數(shù)函數(shù)名CharIndexLenReplaceRightleft日期函數(shù)函數(shù)名Getdate描述舉例用來尋找一個指定的字符串在Selectcharindex(‘傳兵’,’野獸與傳兵’)另一個字符串中的起始位置返回：4Selectlen（‘你好嗎ABC’）返回傳遞給它的字符串長度返回：6Selectreplace(‘美女與野獸’,’野獸’,’傳兵’)替換一個字符串中的字符返回：美女與傳兵從字符串右邊返回指定數(shù)目的Selectright(‘野獸美女傳兵’，3)字符返回：女傳兵Selectleft(‘野獸美女傳兵’，3)(同上)左邊(同上)返回：野獸美描述取得當前的系統(tǒng)日期舉例Selectgetdate()返回：今天的日期將指定的數(shù)值添加到指定的日Selectdateadd（month,4,’201*-05-01’）Dateadd期部分后的日期返回：201*-09-01Selectdatediff(month,’201*-05-01’,’201*-09-01’)兩個日期之間相隔的時間Datediff返回：4日期中指定日期部分的字符串Selectdatename(dw,‘201*-06-01’)Datename形式返回：星期一日期中指定日期部分的整數(shù)形Selectdatepart(day，‘201*-07-09’)datepart式返回：9Selectyear(‘201*-07-09’)日期中指定日期部分的年份Year返回：201*Selectmonth(‘201*-07-09’)日期中指定日期部分的月份Month返回：7日期參數(shù)的簡寫：年:yy季度:qq月:mm日:dd星期:wk每年的某一天:dy工作日:dw小時:hh分鐘:mi秒:ss毫秒:ms

數(shù)學函數(shù)函數(shù)名描述舉例取大于或等于指定數(shù)值、表達Selectceiling(43.5)Ceiling式的最小整數(shù)返回：44有小數(shù)就進一取小于于或等于指定數(shù)值、表Selectfloor(43.5)Floor達式的最大整數(shù)返回：43所有小數(shù)直接去掉將數(shù)值表達式四舍五入為指定Selectround(43.543，1)Round精度返回：43.5系統(tǒng)函數(shù)函數(shù)名描述舉例Selectconvert(verchar(10),12345678)用來轉(zhuǎn)變數(shù)據(jù)類型Convert返回：字符串123456

擴展閱讀：SQL Server知識點總結(jié)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

據(jù)模型。

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

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

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

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

1.2.2概念模型

(一)實體及其屬性

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

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

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

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

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

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

1.2.5關(guān)系模型

(一)基本術(shù)語

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

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

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

(7)主鍵/關(guān)關(guān)系字/主碼：在一個關(guān)系的多個候選碼中指定其中一個作為該關(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)系模型的特點(1)嚴格的理論基礎(chǔ)(2)數(shù)據(jù)結(jié)構(gòu)單一(3)存取簡單

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

(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)系

(一)交叉連接

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

姓名呂川頁鄭學敏于麗孫立華

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

等價于

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

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

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

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

等價于

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

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

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

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

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

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

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

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

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

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

習題：

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3.SQLServer201*的組件

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

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

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

(3)Microsoft搜索服務(wù)：僅用于WindowsNT和Windows201*，實現(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)存儲過程和擴展存儲過程。

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

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

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

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

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

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

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

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

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

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

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

習題：

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

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

(3)在[查詢]窗口中用戶可以輸入SQL語句，按(F5)鍵，或單擊工具欄上的[運行]按鈕，將其送到服務(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個字符

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

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

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

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

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

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

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

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

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

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

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

文件大�。撼跏既萘�(MB)，默認1MB

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

3.2.2用CreateDataBase語句

createdatabaseteacheron(name=teacherdate1,filename="F:\\SqlTest\\tdata1.mdf",size=1MB,--默認字節(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--當前數(shù)據(jù)庫信息(5)execsp_helpfilegroup文件組名

(6)sp_deoption:查看，設(shè)置修改數(shù)據(jù)庫選項語法：sp_deoption[數(shù)據(jù)庫名，[選項，值]]選項為：single單用戶，readonly只讀,autoclose自動關(guān)閉,autoshrink自動收縮如：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說明：文件正在使用時無法操作。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)計信息"如：sp_detach_db"teacher","true"說明：正在使用無法分離，分離后在SQLServer系統(tǒng)中無法看見。(2)刪除

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

(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ù)庫選項，使用（alterdatabase）命令修改數(shù)據(jù)庫結(jié)構(gòu)，使用（dropdatabase）命令刪除數(shù)據(jù)庫.(3)在什么情況下不能刪除數(shù)據(jù)庫

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

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

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

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

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

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

4.1.10局部變量的定義和輸出

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

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

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

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

示例：

printgetdate()--返回當前系統(tǒng)時間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())--當前天數(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*中：

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

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

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

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

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

（1）創(chuàng)建、綁定或解除規(guī)則對象語法：Createrule規(guī)則名稱as條件表達式說明：規(guī)則名稱必須符合標識符的構(gòu)成規(guī)則；條件表達式不能包含任何字段或其他數(shù)據(jù)庫對象名，可以用@開頭的局部變量代表自己表中被綁定的字段;createrule不能與其他語句組合使用，必須單獨作為一個批處理語句。語法：用存儲過程綁定Sp_bindrule‘規(guī)則名稱’‘表名.字段名’|‘自定義數(shù)據(jù)類型名’語法：用存儲過程解除綁定Sp_unbindrule‘表名.字段名’|’自定義數(shù)據(jù)類型名’說明：規(guī)則不能梆定在系統(tǒng)的基本數(shù)據(jù)類型上。規(guī)則必須與綁定字段的數(shù)據(jù)類型相匹配，不能綁定在text,image字段上；若被綁定字段上已有綁定的規(guī)則對象，則綁定新規(guī)則時原有規(guī)則被自動替換；當一個字段同時綁定規(guī)則和默認值時，默認值必須滿足規(guī)則的要求。示例：createrulePAccount--前8個字符只允許數(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)建、綁定和解除默認值對象語法：Createdefault默認值名稱as常量表達式說明：必須單獨作為一個批處理語句。用存儲過程綁定和解除語法：Sp_bindefault‘默認值名稱’,’表名.字段名’|自定義數(shù)據(jù)類型名Sp_unbindefault‘表名.字段名’|自定義數(shù)據(jù)類型名說明：不能將默認值對象綁定到標認列(indentity自動編號)，不能綁定到在企業(yè)管理創(chuàng)建表時已設(shè)置默認值字段，也不能綁定到基本數(shù)據(jù)類型上；若被綁定字段上原有綁定的默認值對象，則綁定新默認值對象時原有默認值對象被自動替換。若綁定成功則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[值列表]說明：只參添加一條記錄；順序可以任意，但個數(shù)、順序和類型必須一致；字段列表可以省略，但順序必須與表中字段順序一致，自動編號標識列不允許提供數(shù)據(jù)（直接省略，也不能使用逗號），允許為空的字段不提供數(shù)據(jù)時必須使用null;只有允許為null的列才能省略不提供數(shù)據(jù)，自動編號字段必須省略；字符型與日期類型用單引號括起來；默認值用default代理默認值；可以嵌套使用子查詢的數(shù)據(jù)，但必須使用圓括號括起來。示例：insertintoprovidervalues("SDLC","山東省浪潮集團","濟南市大山路15號","1002-305-5","趙群","0431-2569874")語法：Insert目標表名字段列表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列進交互或更新，原有數(shù)據(jù)將被完全覆蓋。默認狀態(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è)于清華大學"--復(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:把圖像或文本從字段輸出保存為指定磁盤。安裝時不在目錄中，從安裝盤上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表名說明：一次可以刪除多個表，但不能刪除系統(tǒng)表；不能刪除有外鍵約束的表；數(shù)據(jù)、結(jié)構(gòu)、約束、索引都將同時被刪除。習題：

1.SQLServer提供的系統(tǒng)數(shù)據(jù)類型有(二進制)(數(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ī)則被自動替換）。

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

6.如果當前日期為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子句同時使用。

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

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

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

ALL|Distinct[Row]|Topn[percent]ALLDistinctTop三項參數(shù)必須單獨使用，不能同時出現(xiàn)在一個select語句中。selectall*fromsale--表中所有數(shù)據(jù)selectdistinct*fromsale--去掉重復(fù)項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,原參考價格=PurchasePrice,下浮后價格=PurchasePrice*0.75fromGoodswherePurchasePrice*0.75="201*-2-1"anddateTime名.列名”，不允許使用”表名.列名”5.2.3外連接left|right|full|join

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

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

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

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

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

按某一字段的數(shù)據(jù)值進行分類之后再進行統(tǒng)計格式：Groupby分組字段Having條件說明：使用Groupby子句時，select指定的字段必須包含且只能包含Groupby子句中指定的分組字段，其他必須是由集合函數(shù)組成的一個或多個計算列。Groupby子句不允許使用字段或計算的別名，可直接使用表達式。Groupby子句指定表達式時，select指定的字段中可以不包括該表達式。Having子句用于指定統(tǒng)計結(jié)果所要滿足的條件，表達式中可以直接使用計算列的表達式而不允許使用別名。Having子句必須配合Groupby子句使用，且設(shè)置的條件必須與Groupby子句指定的分組字段有關(guān)。使用Groupby的Select語句仍然可以使用Orderby子句統(tǒng)計結(jié)果排序，但必須在Groupby之后，可以使用別名但不允許對select沒指定的列Having子句是對分組統(tǒng)計后的查詢結(jié)果進行篩選，在統(tǒng)計結(jié)果中選擇滿足條件的記錄作為統(tǒng)計匯總后的結(jié)果集。使用Groupby的select語句仍可使用where子句指定條件，但where子句是在分組前對原表記錄進行篩選，使?jié)M足條件的記錄參加分組統(tǒng)計。分組字段會過濾null,null被忽略selectname,商品數(shù)量=count(id),平均價格=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),平均價格=avg(SalePrice),銷售總額=sum(SalePrice)fromSalewhereGoodname"計算機"groupbyGoodIdselectGoodId,銷售總量=sum(num),平均價格=avg(SalePrice),銷售總額=sum(SalePrice)fromSalegroupbyGoodIdHavingGoodname"計算機"--出錯，因為在分組結(jié)果沒有Goodname5.3.4用Compute子句顯示參加統(tǒng)計的清單及統(tǒng)計結(jié)果

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

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

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

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

視圖：就是基于一個或多個表的動態(tài)數(shù)據(jù)集合，是一個邏輯上的虛擬數(shù)據(jù)表�？梢灾苯釉谝晥D在對數(shù)據(jù)進行編輯修改刪除更新數(shù)據(jù)表中的數(shù)據(jù)。Select,Insert,update語句都可以直接對視圖進行操作。

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

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

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

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

一個視圖最多只能有1024個字段。

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

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

用新字段。

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

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

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

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

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

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

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

(4)視圖是由()構(gòu)成的而不是由()構(gòu)成的慮表。視圖中的數(shù)據(jù)存儲在(數(shù)據(jù)表)。對視圖更新操作時實際操作的是(基表)中的數(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í)行的修改操作必須遵守定義視圖時Where子句指定的條件。(7)更新視圖中的數(shù)據(jù)進，應(yīng)該注意()()().

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

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

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

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

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

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

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

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

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

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

6.1索引概述6.1.1什么是索引

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

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

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

6.1.2索引的分類：

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

(2)非簇索引：具有與表的數(shù)據(jù)完全分離的結(jié)構(gòu)，使用非簇索引不用將物理數(shù)據(jù)頁中的數(shù)據(jù)按列排序。非簇索引中存儲了組成非簇索引的關(guān)鍵字的值和行定位器。行定位器的結(jié)構(gòu)和存儲內(nèi)容取決地數(shù)據(jù)的存儲方式，如果數(shù)據(jù)是以簇索引方式存儲的，則行定位器中存儲的是簇索引的索引鍵。如果數(shù)據(jù)不是簇索引方式存儲的，這種方式又稱為堆存儲方式，則行定位器存儲的是指向數(shù)據(jù)行的指針。非簇索引將行定位器按關(guān)鍵字的值用一定的方式排序，這個順序與表的行在數(shù)據(jù)頁中排序是不匹配的。由于非簇索引使用索引頁存儲，因此它比簇索引需要更多的存儲空間，且檢索效率較低。但一個表最多可以建249個非簇索引。(3)性能比較簇索引每個表只能有一個快速快，取出一個范圍時更快。非簇索引一個表可以有多個，最多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ù)值時，不能創(chuàng)建。列不能包含null索引。Clustered:創(chuàng)建簇索引。默認創(chuàng)建非簇索引。NONClustered:創(chuàng)建非簇索引。一個索引中最多可以指定16個列，但列數(shù)據(jù)類型的長度不能超過900個字節(jié)。PAD-Index:指定填充索引的內(nèi)部節(jié)點的行數(shù)至少應(yīng)大于等于兩行。只有在FillFactor選項指定后才起作用。因為PAD-Index與FillFactor使用相同的百分比。FillFactor：填充因子，它指定創(chuàng)建索引時每個索引頁的數(shù)據(jù)占索引頁大小的百分比。值為1100。Ignore_Dup_key:控制了當往包含一個惟一索引中的列中插入重復(fù)數(shù)據(jù)，SQLServer所作的反應(yīng)。Drop_Existing:指定要刪除并重新創(chuàng)建簇索引。StatisTisc_Norecompute:指定過期的索引統(tǒng)計不會自動重新計算。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個字節(jié)時也不能作為索引列。在創(chuàng)建向?qū)е胁荒軐⒂嬎懔邪�含在索引中。但在直接�?chuàng)建或使用createindex命令創(chuàng)建索引時，則可以對計算列創(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è)計索引

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

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

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

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

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

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

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

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

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

5)維護索引的開銷超過了建立索引的價值。

6.6索引的維護

（1）重建索引

下面需要重建索引：

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

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

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

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

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

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

習題：

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

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

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

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

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

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

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

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

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

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

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

7.1.2SQL腳本文件

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

7.1.3SQL注釋

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

7.1.4局部變更和全局變量

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

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

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

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

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

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

語法：While邏輯條件表達式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ù)，即標量函數(shù)、內(nèi)嵌表值函數(shù)和多語句表值函數(shù)。

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

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

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

游標與變量類似，必須先定義后使用。

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

7.5.2定義游標

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

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

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

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

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

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

PRINT@ass

12.SQLServer提供的注釋類型有（）兩項。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

用戶可用select語句查詢這個臨時表，但不允許進行修改。觸發(fā)器一旦執(zhí)行完成，這個表將被自動刪除。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ā)器文本進行加密，禁止查閱修改。Notforreplication:在復(fù)制過程中，不激活觸發(fā)器操作。Setnocount:觸發(fā)器一般不能有返回值，所以也不應(yīng)該用select語句進行查詢或給變量賦值，如果必須使用變量賦值語句，可能在開頭使用該語句避免返回結(jié)果。SQL語句:即可以包含獲得被操作的select語句。后觸方式被操作數(shù)據(jù)一定在inserted或deleted臨時表中。如果被操作的數(shù)據(jù)是多值的，可用in判斷是否被包含在其中：被操作數(shù)據(jù)in(select被操作字段from臨時表)如果被操作的數(shù)據(jù)是單值，可用下面語句Select@變量=被操作字段from臨時表``說明：Createtrigger語句必須是一個批處理的第一條語句。創(chuàng)建觸發(fā)器的權(quán)限默認屬于表的所有者，而且不能授權(quán)給其他人。觸發(fā)器不能在臨時表或系統(tǒng)一是創(chuàng)建，后觸發(fā)也不能創(chuàng)建在視圖上。一個觸發(fā)器只能創(chuàng)建在一個表上；一個表可以有有一個替代觸發(fā)器和多個后觸發(fā)器（可以是同一種操作類型，可同時觸發(fā)）由于TruncateTable語句刪除記錄時不被記入事務(wù)日志，所以該該語句不能激活deleted刪除操作的觸發(fā)器如果外鍵所引用的父表已創(chuàng)建了對子表級聯(lián)修改或刪除的觸發(fā)器，則子表不允許創(chuà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的安全機制

SQLServer201*的安全性管理是建立在認證和訪問許可兩者機制上的。

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

SQLServer參在兩種安全模式下運行：Windows認證模式和混合混式。

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

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

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

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

Grust用戶主要是讓那些沒有屬于自己的用戶帳號的SQLServer登錄者把其用為默認用戶，從而使該登錄者能夠訪問具有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。后臺表名盡量與前臺表名相同，后臺獨有的表應(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ù)部分主要以動賓形式構(gòu)成，并用下劃線分割各個組成部分。如增加BSC機架的DRT單板的存儲過程為"sp_ins_board_drt"。5變量名

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

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

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

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

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

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

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

2、注釋盡可能詳細、全面。

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

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

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

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

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

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

CREATEPROCEDUREsp_xxx

條件執(zhí)行語句ifelse

條件語句塊（statenemtblock，以beginend為邊界）僅在if子句的條件為真時才被執(zhí)行。為提高代碼的可讀性，建議嵌套不多于5層。還有，當嵌套層次太多時，應(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重新計算while條件，如果條件為真，則從循環(huán)開始處重新執(zhí)行各語句。

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

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

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

在保證基本縮進格式的前提下，可以通過對齊某些重要關(guān)鍵字（如條件關(guān)鍵字AND、OR，符號=、等）來進一步提高文本的易讀性和可維護性。相鄰兩級的縮進量為10個空格。這也是ISQL編輯器默認的文本縮進量。另外，在ISQL編輯器中，一個TAB鍵也相當于10個空格。注：按照功能，四類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ù)對象名）小寫。需要特殊強調(diào)的部分可以大寫。一條完整注釋語句的首字符應(yīng)大寫。對某變量、某條件字句的注釋可以全部使用小寫。

SQLServer單詞表

單詞Primarykey主鍵含義單詞Identity含義自動編號

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

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

《SQL數(shù)據(jù)庫知識總結(jié)》由互聯(lián)網(wǎng)用戶整理提供,轉(zhuǎn)載分享請保留原作者信息,謝謝!
鏈接地址：http://www.taixiivf.com/gongwen/672275.html

• 上一篇：優(yōu)化SQL Server數(shù)據(jù)庫的經(jīng)驗總結(jié)
• 下一篇：VB實訓總結(jié)