數(shù)據(jù)庫(kù)第6次課-數(shù)據(jù)完整性和TSQL語(yǔ)言

第8章數(shù)據(jù)完整性AnIntroductiontoDatabaseSystems本章內(nèi)容8.1數(shù)據(jù)完整性概述8.2使用規(guī)則實(shí)施數(shù)據(jù)完整性8.3使用默認(rèn)值實(shí)施數(shù)據(jù)完整性8.4使用約束實(shí)施數(shù)據(jù)完整性8.1數(shù)據(jù)完整性概述數(shù)據(jù)完整性防止數(shù)據(jù)庫(kù)中存在不符合語(yǔ)義規(guī)定的數(shù)據(jù)和防止因錯(cuò)誤信息的輸入輸出造成無(wú)效操作或錯(cuò)誤信息而提出的。數(shù)據(jù)完整性有4種類型：實(shí)體完整性(EntityIntegrity)、域完整性(DomainIntegrity)、參照完整性(ReferentialIntegrity)、用戶定義的完整性(User-definedIntegrity)。在SQLServer中可以通過(guò)各種規(guī)則(Rule)、默認(rèn)(Default)、約束(Constraint)和觸發(fā)器(Trigger)等數(shù)據(jù)庫(kù)對(duì)象來(lái)保證數(shù)據(jù)的完整性。8.2使用規(guī)則實(shí)施數(shù)據(jù)完整性8.2.1創(chuàng)建規(guī)則8.2.2查看和修改規(guī)則8.2.3規(guī)則的綁定與松綁8.2.4刪除規(guī)則8.2.1創(chuàng)建規(guī)則規(guī)則(Rule)就是數(shù)據(jù)庫(kù)中對(duì)存儲(chǔ)在表的列或用戶定義數(shù)據(jù)類型中的值的規(guī)定和限制。規(guī)則是單獨(dú)存儲(chǔ)的獨(dú)立的數(shù)據(jù)庫(kù)對(duì)象。規(guī)則和約束可以同時(shí)使用，表的列可以有一個(gè)規(guī)則及多個(gè)約束。規(guī)則與檢查約束在功能上相似，但在使用上有所區(qū)別。檢查約束是在CREATETABLE或ALTERTABLE語(yǔ)句中定義的，嵌入了被定義的表結(jié)構(gòu)，即刪除表的時(shí)候檢查約束也就隨之被刪除。而規(guī)則需要用CREATERULE語(yǔ)句定義后才能使用，是獨(dú)立于表之外的數(shù)據(jù)庫(kù)對(duì)象，刪除表并不能刪除規(guī)則，需要用DROPRULE語(yǔ)句才能刪除。相比之下，使用在CREATETABLE或ALTERTABLE語(yǔ)句中定義的檢查約束是更標(biāo)準(zhǔn)的限制列值的方法，但檢查約束不能直接作用于用戶定義數(shù)據(jù)類型。1．用企業(yè)管理器創(chuàng)建規(guī)則8.2.1創(chuàng)建規(guī)則在企業(yè)管理器中選擇數(shù)據(jù)庫(kù)對(duì)象“規(guī)則”，單擊右鍵從快捷菜單中選擇“新建規(guī)則”選項(xiàng)，即會(huì)彈出如圖所示的“規(guī)則屬性”對(duì)話框。輸入規(guī)則名稱和表達(dá)式之后，單擊“確定”按鈕，即完成規(guī)則的創(chuàng)建。2．用CREATERULE語(yǔ)句創(chuàng)建規(guī)則8.2.1創(chuàng)建規(guī)則CREATERULE語(yǔ)句用于在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建規(guī)則，其語(yǔ)法格式如下： CREATERULErule_nameAScondition_expression例8-1創(chuàng)建雇傭日期規(guī)則hire_date_rule。CREATERULEhire_date_ruleAS@hire_date>='1980-01-01'and@hire_date<=getdate()CREATERULEsex_ruleAS@sexin('男','女')例8-2創(chuàng)建性別規(guī)則sex_rule。例8-4創(chuàng)建字符規(guī)則my_character_rule。Createrulemy_character_ruleAs@valuelike'[a-z]%[0-9]'例8-3創(chuàng)建評(píng)分規(guī)則grade_rule。CREATERULEgrade_ruleAS@valuebetween1and1008.2.1創(chuàng)建規(guī)則1．用企業(yè)管理器查看和修改規(guī)則在企業(yè)管理器的數(shù)據(jù)庫(kù)對(duì)象中選擇“規(guī)則”對(duì)象，即可從右邊的任務(wù)板中看到規(guī)則的大部分信息，包括規(guī)則的名稱、所有者、創(chuàng)建時(shí)間等。8.2.2查看和修改規(guī)則8.2使用規(guī)則實(shí)施數(shù)據(jù)完整性8.2.2查看和修改規(guī)則使用sp_helptext系統(tǒng)存儲(chǔ)過(guò)程可以查看規(guī)則的文本信息。例8-5查看規(guī)則hire_date_rule的文本信息EXECUTEsp_helptexthire_date_rule運(yùn)行結(jié)果如圖所示2．用系統(tǒng)存儲(chǔ)過(guò)程sp_helptext查看規(guī)則8.2.3規(guī)則的綁定與松綁需要將規(guī)則與數(shù)據(jù)庫(kù)表或用戶定義對(duì)象聯(lián)系起來(lái)，才能發(fā)生作用。聯(lián)系的方法稱為綁定，所謂綁定就是指定規(guī)則作用于哪個(gè)表的哪一列或哪個(gè)用戶定義數(shù)據(jù)類型。表的一列或一個(gè)用戶定義數(shù)據(jù)類型只能與一個(gè)規(guī)則相綁定，而一個(gè)規(guī)則可以綁定多對(duì)象。解除規(guī)則與對(duì)象的綁定稱為松綁。8.2使用規(guī)則實(shí)施數(shù)據(jù)完整性8.2.3規(guī)則的綁定與松綁在企業(yè)管理器中，展開(kāi)數(shù)據(jù)庫(kù)（Sales）文件夾，鼠標(biāo)單擊“規(guī)則”選項(xiàng)，在右窗格中選擇要進(jìn)行綁定的規(guī)則（hire_date），單擊鼠標(biāo)右鍵，從快捷菜單中選擇“屬性”菜單項(xiàng)，打開(kāi)“規(guī)則屬性”對(duì)話框，如圖8-4所示。圖中的“綁定UDT(U)”按鈕用于綁定規(guī)則到用戶定義的數(shù)據(jù)類型，“綁定列(B)”按鈕用于綁定規(guī)則到表的列。1．用企業(yè)管理器管理規(guī)則的綁定和松綁8.2.3規(guī)則的綁定與松綁在圖8-4中單擊“綁定UDT(U)”按鈕，則出現(xiàn)“將綁定規(guī)則到用戶定義的數(shù)據(jù)類型”對(duì)話框，如圖8-5所示；8.2.3規(guī)則的綁定與松綁單擊“綁定列(B)”按鈕，則出現(xiàn)如圖8-6所示的“將綁定規(guī)則到列”對(duì)話框。在“將規(guī)則綁定列”對(duì)話框的左邊“未綁定的列”列表框中選擇一列“添加”到右邊“綁定列”列表框中，就實(shí)現(xiàn)規(guī)則綁定了。同樣，去掉“將規(guī)則綁定到用戶定義的數(shù)據(jù)類型”對(duì)話框的列表框的“綁定”列下的標(biāo)識(shí)或刪除“將規(guī)則綁定列”對(duì)話框的右邊“綁定列”列表框的列，就實(shí)現(xiàn)了規(guī)則的松綁操作。8.2.3規(guī)則的綁定與松綁2．用系統(tǒng)存儲(chǔ)過(guò)程sp_bindrule綁定規(guī)則系統(tǒng)存儲(chǔ)過(guò)程sp_bindrule可以綁定一個(gè)規(guī)則到表的一個(gè)列或一個(gè)用戶定義數(shù)據(jù)類型上。其語(yǔ)法格式如下：sp_bindrule[@rulename=]'rule',[@objname=]'object_name'例8-6將例8-1創(chuàng)建的規(guī)則hire_date_rule綁定到employee表的hire_date列上。EXECsp_bindrulehire_date_rule,'employee.hire_date' 運(yùn)行結(jié)果為：已將規(guī)則綁定到表的列上。8.2.3規(guī)則的綁定與松綁系統(tǒng)存儲(chǔ)過(guò)程sp_unbindrule可解除規(guī)則與列或用戶定義數(shù)據(jù)類型的綁定，其語(yǔ)法格式如下：sp_unbindrule[@objname=]'object_name'[,[@futureonly=]'futureonly']3．用系統(tǒng)存儲(chǔ)過(guò)程sp_unbindrule解除規(guī)則的綁定例8-9解除例8-6綁定在employee表的hire_date列和用戶定義數(shù)據(jù)類型pat_char上的規(guī)則。EXECsp_unbindrule'employee.hire_date'運(yùn)行結(jié)果如下：（所影響的行數(shù)為1行）已從表的列上解除了規(guī)則的綁定。8.2.4刪除規(guī)則使用DROPRULE語(yǔ)句刪除當(dāng)前數(shù)據(jù)庫(kù)中的一個(gè)或多個(gè)規(guī)則。其語(yǔ)法格式如下：DROPRULE{rule_name}[,...n]注意：在刪除一個(gè)規(guī)則前，必須先將與其綁定的對(duì)象解除綁定。例8-10刪除例8-1和8-2中創(chuàng)建的規(guī)則。DROPRULEsex_rule,hire_date_rule8.2使用規(guī)則實(shí)施數(shù)據(jù)完整性8.3.1創(chuàng)建默認(rèn)值8.3.2查看默認(rèn)值8.3.3默認(rèn)值的綁定與松綁8.3.4刪除默認(rèn)值8.3使用默認(rèn)值實(shí)施數(shù)據(jù)完整性8.3使用默認(rèn)值實(shí)施數(shù)據(jù)完整性8.3.1創(chuàng)建默認(rèn)值默認(rèn)值(Default)是用戶輸入記錄時(shí)往沒(méi)有指定具體數(shù)據(jù)的列中自動(dòng)插入的數(shù)據(jù)。默認(rèn)值對(duì)象與CREATETABLE或ALTERTABLE語(yǔ)句操作表時(shí)用默認(rèn)約束指定的默認(rèn)值功能相似，兩者的區(qū)別類似于規(guī)則與檢查約束在使用上的區(qū)別。默認(rèn)值對(duì)象可以用于多個(gè)列或用戶定義數(shù)據(jù)類型。表的一列或一個(gè)用戶定義數(shù)據(jù)類型只能與一個(gè)默認(rèn)值相綁定。默認(rèn)值的創(chuàng)建、查看、綁定、松綁和刪除等操作可在企業(yè)管理器中進(jìn)行，也可利用Transact-SQL語(yǔ)句進(jìn)行。8.3.1創(chuàng)建默認(rèn)值8.3.1創(chuàng)建默認(rèn)值1．用企業(yè)管理器創(chuàng)建默認(rèn)值在企業(yè)管理器中選擇數(shù)據(jù)庫(kù)對(duì)象的“默認(rèn)值”對(duì)象，單擊右鍵，從快捷菜單中選擇“新建默認(rèn)值”選項(xiàng)，打開(kāi)“默認(rèn)屬性”對(duì)話框，如圖8-7所示。輸入默認(rèn)值名稱和值表達(dá)式之后，單擊“確定”按鈕，即完成默認(rèn)值的創(chuàng)建。8.3.1創(chuàng)建默認(rèn)值2．用CREATEDEFAULT語(yǔ)句創(chuàng)建默認(rèn)值CREATEDEFAULT語(yǔ)句用于在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建默認(rèn)值對(duì)象，其語(yǔ)法格式如下：CREATEDEFAULTdefault_nameASconstant_expression例8-11創(chuàng)建生日默認(rèn)值birthday_defa。CREATEDEFAULTbirthday_defaAS'1978-1-1'例8-12創(chuàng)建當(dāng)前日期默認(rèn)值today_defa。CREATEDEFAULTtoday_defaASgetdate()1．用企業(yè)管理器查看默認(rèn)值在企業(yè)管理器中選擇數(shù)據(jù)庫(kù)對(duì)象的“默認(rèn)值”對(duì)象，即可從右邊的任務(wù)板中看到默認(rèn)值的大部分信息，如圖8-8所示。8.3.2查看默認(rèn)值8.3使用默認(rèn)值實(shí)施數(shù)據(jù)完整性8.3.2查看默認(rèn)值選擇要查看的默認(rèn)值，單擊右鍵，從快捷菜單中選擇“屬性”選項(xiàng)，就會(huì)出現(xiàn)圖8-9所示的“默認(rèn)屬性”對(duì)話框，可以從中編輯默認(rèn)值的值表達(dá)式。2．用系統(tǒng)存儲(chǔ)過(guò)程sp_helptext查看默認(rèn)值使用sp_helptext系統(tǒng)存儲(chǔ)過(guò)程可以查看默認(rèn)值的細(xì)節(jié)。例8-13查看默認(rèn)值today_defa。EXECsp_helptexttoday_defa運(yùn)行結(jié)果如圖8-10所示。8.3.2查看默認(rèn)值8.3.3默認(rèn)值的綁定與松綁1．用企業(yè)管理器管理默認(rèn)值的綁定和松綁在企業(yè)管理器中，選擇要進(jìn)行綁定設(shè)置的默認(rèn)值，單擊右鍵，從快捷菜單中選擇“屬性”選項(xiàng)，打開(kāi)“默認(rèn)屬性”對(duì)話框，參見(jiàn)圖8-9。圖8-9中的“綁定UDT(U)”按鈕用于將默認(rèn)值綁定到用戶定義數(shù)據(jù)類型，“綁定列(B)”按鈕用于將默認(rèn)值綁定到表的列。單擊“綁定UDT(U)”按鈕，則出現(xiàn)如圖8-11所示的“將綁定默認(rèn)值到用戶定義的數(shù)據(jù)類型”對(duì)話框8.3使用默認(rèn)值實(shí)施數(shù)據(jù)完整性8.3.3默認(rèn)值的綁定與松綁單擊“綁定列(B)”按鈕，則出現(xiàn)如圖8-12所示的“將綁定默認(rèn)值到表的列”對(duì)話框。管理默認(rèn)值與用戶定義數(shù)據(jù)類型以及表的列之間的綁定和松綁與規(guī)則相同。8.3.3默認(rèn)值的綁定與松綁2．用sp_bindefault綁定默認(rèn)值系統(tǒng)存儲(chǔ)過(guò)程sp_bindefault可以綁定一個(gè)默認(rèn)值到表的一個(gè)列或一個(gè)用戶定義數(shù)據(jù)類型上。其語(yǔ)法格式如下：sp_bindefault[@defname=]'default',[@objname=]'object_name'例8-14綁定默認(rèn)值today_defa到employee表的hire_date列上。 EXECsp_bindefaulttoday_defa,'employee.hire_date'運(yùn)行結(jié)果如下：已將默認(rèn)值綁定到列。8.3.3默認(rèn)值的綁定與松綁3．用sp_unbindefault解除默認(rèn)值的綁定系統(tǒng)存儲(chǔ)過(guò)程sp_unbindefault可以解除默認(rèn)值與表的列或用戶定義數(shù)據(jù)類型的綁定，其語(yǔ)法格式如下： sp_unbindefault[@objname=]'object_name' [,[@futureonly=]'futureonly']例8-15解除默認(rèn)值today_defa與表employee的hire_date列的綁定。EXECsp_unbindefault'employee.hire_date'運(yùn)行結(jié)果如下：（所影響的行數(shù)為1行）已從表的列上解除了默認(rèn)值的綁定。8.3使用默認(rèn)值實(shí)施數(shù)據(jù)完整性8.3.4刪除默認(rèn)值可以在企業(yè)管理器中選擇默認(rèn)值，單擊右鍵，從快捷菜單中選擇“刪除”選項(xiàng)刪除默認(rèn)值，也可以使用DROPDEFAULT語(yǔ)句刪除當(dāng)前數(shù)據(jù)庫(kù)中的一個(gè)或多個(gè)默認(rèn)值。其語(yǔ)法格式如下：DROPDEFAULT{default_name}[,...n]例8-16刪除生日默認(rèn)值birthday_defa。DROPDEFAULTbirthday_defa8.4.1主鍵約束8.4.2外鍵約束8.4.3惟一性約束8.4.4檢查約束8.4.5默認(rèn)約束8.4使用約束實(shí)施數(shù)據(jù)完整性8.4使用約束實(shí)施數(shù)據(jù)完整性8.4.1主鍵約束約束(Constraint)是SQLServer提供的自動(dòng)保持?jǐn)?shù)據(jù)庫(kù)完整性的一種機(jī)制，它定義了可輸入表或表的單個(gè)列中的數(shù)據(jù)的限制條件。使用約束優(yōu)先于使用觸發(fā)器、規(guī)則和默認(rèn)值。約束獨(dú)立于表結(jié)構(gòu)，作為數(shù)據(jù)庫(kù)定義部分在CREATETABLE語(yǔ)句中聲明，可以在不改變表結(jié)構(gòu)的基礎(chǔ)上，通過(guò)ALTERTABLE語(yǔ)句添加或刪除。當(dāng)表被刪除時(shí)，表所帶的所有約束定義也隨之被刪除。8.4.1主鍵約束主鍵表的一列或幾列的組合的值在表中惟一地指定一行記錄，這樣的一列或多列稱為表的主鍵（PrimaryKey，PK），通過(guò)它可強(qiáng)制表的實(shí)體完整性。主鍵不允許為空值，且不同兩行的鍵值不能相同。表中可以有不止一個(gè)鍵惟一標(biāo)識(shí)行，每個(gè)鍵都稱為侯選鍵，只可以選一個(gè)侯選鍵作為表的主鍵，其他侯選鍵稱作備用鍵。如果一個(gè)表的主鍵由單列組成，則該主鍵約束可以定義為該列的列約束。如果主鍵由兩個(gè)以上的列組成，則該主鍵約束必須定義為表約束。定義列級(jí)主鍵約束的語(yǔ)法格式如下：[CONSTRAINTconstraint_name]PRIMARYKEY[CLUSTERED|NONCLUSTERED]定義表級(jí)主鍵約束的語(yǔ)法格式如下：[CONSTRAINTconstraint_name]PRIMARYKEY[CLUSTERED|NONCLUSTERED]{(column_name[,…n])}8.4.1主鍵約束例8-17在Sales數(shù)據(jù)庫(kù)中創(chuàng)建customer表，并聲明主鍵約束。CREATETABLESales.dbo.customer(customer_idbigintNOTNULLIDENTITY(0,1)PRIMARYKEY,customer_namevarchar(50)NOTNULL,linkman_namechar(8),addressvarchar(50),telephonechar(12)NOTNULL)8.4.1主鍵約束非聚集主鍵約束若要定義customer_id列為非聚集主鍵約束，并指定約束名為PK_customer，使用以下語(yǔ)句：customer_idchar(5)CONSTRAINTPK_customerPRIMARYKEYNONCLUSTERED8.4.1主鍵約束CREATETABLEgoods1(goods_idchar(6)NOTNULL,goods_namevarchar(50)NOTNULL,classification_idchar(6)NOTNULL,unit_pricemoneyNOTNULL,stock_quantityfloatNOTNULL,order_quantityfloatNULL

CONSTRAINTpk_p_idPRIMARYKEY(goods_id))ON[PRIMARY]例8-18創(chuàng)建一個(gè)產(chǎn)品信息表goods1，將產(chǎn)品編號(hào)goods_id列聲明為主鍵。8.4.1主鍵約束例8-19根據(jù)商品銷售的時(shí)間和商品類別來(lái)確定銷售的商品的數(shù)量。CREATETABLEg_order(good_typeint,order_timedatetime,order_numint,

CONSTRAINTg_o_keyPRIMARYKEY(good_type,order_time))8.4.2外鍵約束外鍵約束定義了表與表之間的關(guān)系。通過(guò)將一個(gè)表中一列或多列添加到另一個(gè)表中，創(chuàng)建兩個(gè)表之間的連接，這個(gè)列就成為第二個(gè)表的外鍵(ForeignKey，F(xiàn)K)，即外鍵是用于建立和加強(qiáng)兩個(gè)表數(shù)據(jù)之間的連接的一列或多列，通過(guò)它可以強(qiáng)制參照完整性。例如，Sales數(shù)據(jù)庫(kù)中的employee、sell_order、goods這3個(gè)表之間存在以下邏輯聯(lián)系：sell_order(銷售訂單)表中employee_id(員工編號(hào))列的值必須是employee表employee_id列中的某一個(gè)值，因?yàn)楹炗嗕N售訂單的人必須是當(dāng)前公司員工；而sell_order表中g(shù)oods_id(貨物編號(hào))列的值必須是goods(貨物)表的goods_id列中的某一個(gè)值，因?yàn)殇N售訂單上售出的只能是已知貨物。因此，在sell_order表上應(yīng)建立兩個(gè)外鍵約束FK_sell_order_employee和FK_sell_order_goods來(lái)限制sell_order表employee_id列和goods_id列的值必須分別來(lái)自employee表的employee_id列及goods表的goods_id列。8.4.2外鍵約束8.4.2外鍵約束級(jí)聯(lián)操作SQLServer提供了兩種級(jí)聯(lián)操作以保證數(shù)據(jù)完整性：(1)級(jí)聯(lián)刪除確定當(dāng)主鍵表中某行被刪除時(shí)，外鍵表中所有相關(guān)行將被刪除。(2)級(jí)聯(lián)修改確定當(dāng)主鍵表中某行的鍵值被修改時(shí)，外鍵表中所有相關(guān)行的該外鍵值也將被自動(dòng)修改為新值。8.4.2外鍵約束外鍵的表約束與列約束定義表級(jí)外鍵約束的語(yǔ)法格式如下：[CONSTRAINT約束名]FOREIGNKEY(列名[,…n])REFERENCES參照主表[(參照列[,…n])][ONDELETE{CASCADE|NOACTION}][ONUPDATE{CASCADE|NOACTION}]][NOTFORREPLICATION]定義列級(jí)外鍵約束的語(yǔ)法格式如下：[CONSTRAINT約束名][FOREIGNKEY]REFERENCES參照主表[NOTFORREPLICATION]CREATETABLEsell_order1(order_id1char(6)NOTNULL,goods_idchar(6)NOTNULL,employee_idchar(4)NOTNULL,customer_idchar(4)NOTNULL,transporter_idchar(4)NOTNULL,order_numfloatNULL,discountfloatNULL,order_datedatetimeNOTNULL,send_datedatetimeNULL,arrival_datedatetimeNULL,costmoneyNULL,

CONSTRAINTpk_order_idPRIMARYKEY(order_id1),

FOREIGNKEY(goods_id)REFERENCESgoods1(goods_id))例8-20創(chuàng)建一個(gè)訂貨表sell_order1，與例8-18創(chuàng)建的產(chǎn)品表goods1相關(guān)聯(lián)。8.4.2外鍵約束CREATETABLEsell_order2(order_id1char(6)PRIMARYKEY,goods_idchar(6)NOTNULL

CONSTRAINTFK_goods_idFOREIGNKEY(goods_id)REFERENCESGoods1(goods_id)ONDELETENOACTIONONUPDATECASCADE,employee_idchar(4)NOTNULL

FOREIGNKEY(employee_id)REFERENCESemployee(employee_id)ONUPDATECASCADE,customer_idchar(4)NOTNULL,……

CONSTRAINTFK_customer_idFOREIGNKEY(customer_id)REFERENCEScustomer(customer_id))例8-21創(chuàng)建表sell_order2，并為goods_id、employee_id、custom_id三列定義外鍵約束。8.4.2外鍵約束8.4使用約束實(shí)施數(shù)據(jù)完整性8.4.3惟一性約束惟一性(Unique)約束指定一個(gè)或多個(gè)列的組合的值具有惟一性，以防止在列中輸入重復(fù)的值，為表中的一列或者多列提供實(shí)體完整性。惟一性約束指定的列可以有NULL屬性。主鍵也強(qiáng)制執(zhí)行惟一性，但主鍵不允許空值，故主鍵約束強(qiáng)度大于惟一約束。因此主鍵列不能再設(shè)定惟一性約束。8.4.3惟一性約束定義列級(jí)惟一性約束的語(yǔ)法格式如下：[CONSTRAINTconstraint_name]UNIQUE[CLUSTERED|NONCLUSTERED]惟一性約束應(yīng)用于多列時(shí)的定義格式：[CONSTRAINTconstraint_name]UNIQUE[CLUSTERED|NONCLUSTERED](column_name[,…n])8.4.3惟一性約束例8-23定義一個(gè)員工信息表employees，其中員工的身份證號(hào)emp_cardid列具有惟一性。CREATETABLEemployees(emp_idchar(8),emp_namechar(10),emp_cardidchar(18),CONSTRAINTpk_emp_idPRIMARYKEY(emp_id),CONSTRAINTuk_emp_cardidUNIQUE(emp_cardid))8.4使用約束實(shí)施數(shù)據(jù)完整性8.4.4檢查約束檢查(Check)約束對(duì)輸入列或整個(gè)表中的值設(shè)置檢查條件，以限制輸入值，保證數(shù)據(jù)庫(kù)的數(shù)據(jù)完整性。當(dāng)對(duì)具有檢查約束列進(jìn)行插入或修改時(shí)，SQLServer將用該檢查約束的邏輯表達(dá)式對(duì)新值進(jìn)行檢查，只有滿足條件(邏輯表達(dá)式返回TRUE)的值才能填入該列，否則報(bào)錯(cuò)?？梢詾槊苛兄付ǘ鄠€(gè)CHECK約束。8.4.4檢查約束定義檢查約束的語(yǔ)法格式：[CONSTRAINTconstraint_name]CHECK[NOTFORREPLICATION](logical_expression)例8-25創(chuàng)建一個(gè)訂貨表orders，保證各訂單的訂貨量必須不小于10。CREATETABLEorders(order_idchar(8),p_idchar(8),p_namechar(10),quantitysmallintCONSTRAINTchk_quantityCHECK(quantity>=10),CONSTRAINTpk_orders_idPRIMARYKEY(order_id))CREATETABLEtransporters(transporter_idchar(4)NOTNULL,transport_namevarchar(50),linkman_namechar(8),addressvarchar(50),telephonechar(12)NOTNULLCHECK(telephoneLIKE'0[1-9][0-9][0-9]-[1-9][0-9][0-9][0-9][0-9][0-9][0-9]'ORtelephoneLIKE'0[1-9][0-9]-[1-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'))8.4.4檢查約束例8-26創(chuàng)建transporters表并定義檢查約束8.4使用約束實(shí)施數(shù)據(jù)完整性8.4.5默認(rèn)約束默認(rèn)(Default)約束通過(guò)定義列的默認(rèn)值或使用數(shù)據(jù)庫(kù)的默認(rèn)值對(duì)象綁定表的列，以確保在沒(méi)有為某列指定數(shù)據(jù)時(shí)，來(lái)指定列的值。默認(rèn)值可以是常量，也可以是表達(dá)式，還可以為NULL值。8.4.5默認(rèn)約束定義默認(rèn)約束的語(yǔ)法格式[CONSTRAINTconstraint_name]DEFAULTconstant_expression[FORcolumn_name]例8-27在Sales數(shù)據(jù)庫(kù)中，為員工表employee的sex列添加默認(rèn)約束，默認(rèn)值是“男”。ALTERTABLEemployeeADDCONSTRAINTsex_defaultDEFAULT'男'FORsex例8-28更改表employee，為hire_date列定義默認(rèn)約束。ALTERTABLEemployeeADDCONSTRAINThire_date_dfDEFAULT(getdate())FORhire_date8.4.5默認(rèn)約束例8-31為表purchase_orders定義多個(gè)約束CREATETABLEpurchase_orders(order_id2char(6)NOTNULL,goods_idchar(6)NOTNULL,employee_idchar(4)NOTNULL,supplier_idchar(5)NOTNULL,transporter_idchar(4),order_numfloatNOTNULL,discountfloat

CHECK(discount>=0ANDdiscount<=50)DEFAULT(0),order_datedatetimeNOTNULLDEFAULT(GetDate()),send_datedatetime,arrival_datedatetime,

CONSTRAINTCK_Send_dateCHECK(send_date>order_date),CHECK(arrival_date>send_date))本章小結(jié)(1)數(shù)據(jù)完整性有4種類型：實(shí)體完整性、域完整性、參照完整性和用戶定義的完整性。在SQLServer2000中可以通過(guò)各種約束、默認(rèn)、規(guī)則和觸發(fā)器等數(shù)據(jù)庫(kù)對(duì)象來(lái)保證數(shù)據(jù)的完整性。(2)規(guī)則實(shí)施數(shù)據(jù)的完整性：規(guī)則就是數(shù)據(jù)庫(kù)中對(duì)存儲(chǔ)在表的列或用戶定義數(shù)據(jù)類型中的值的規(guī)定和限制。可以通過(guò)企業(yè)管理器和Transact-SQL語(yǔ)句來(lái)創(chuàng)建、刪除、查看規(guī)則以及規(guī)則的綁定與松綁。(3)默認(rèn)值實(shí)施數(shù)據(jù)完整性：默認(rèn)值是用戶輸入記錄時(shí)沒(méi)有指定具體數(shù)據(jù)的列中自動(dòng)插入的數(shù)據(jù)。默認(rèn)值對(duì)象可以用于多個(gè)列或用戶定義數(shù)據(jù)類型，它的管理與應(yīng)用同規(guī)則有許多相似之處。表的一列或一個(gè)用戶定義數(shù)據(jù)類型也只能與一個(gè)默認(rèn)值相綁定。在SQLServer中使用企業(yè)管理器和Transact-SQL語(yǔ)句實(shí)現(xiàn)默認(rèn)值的創(chuàng)建、查看、刪除以及默認(rèn)值的綁定與松綁。(4)使用約束實(shí)施數(shù)據(jù)完整性：約束是SQLServer提供的自動(dòng)保持?jǐn)?shù)據(jù)庫(kù)完整性的一種方法，定義了可輸入表或表的單個(gè)列中的數(shù)據(jù)的限制條件。在SQLServer中有6種約束：非空值約束、主鍵約束、外鍵約束、惟一性約束、檢查約束和默認(rèn)約束。第9章Transact-SQL程序設(shè)計(jì)

AnIntroductiontoDatabaseSystems本章內(nèi)容9.1數(shù)據(jù)與表達(dá)式9.2函數(shù)9.3程序控制流語(yǔ)句9.4游標(biāo)管理與應(yīng)用9.1數(shù)據(jù)與表達(dá)式9.1.1用戶定義數(shù)據(jù)類型9.1.2常量與變量9.1.3運(yùn)算符與表達(dá)式9.1數(shù)據(jù)與表達(dá)式9.1.1用戶定義數(shù)據(jù)類型1．使用系統(tǒng)存儲(chǔ)過(guò)程來(lái)創(chuàng)建用戶定義數(shù)據(jù)類型,命令格式如下：sp_addtype[@typename=]type,[@phystype=]system_data_type[,[@nulltype=]'null_type'][,[@owner=]'owner_name']9.1.1用戶定義數(shù)據(jù)類型例如，為Sales數(shù)據(jù)庫(kù)創(chuàng)建—個(gè)不允許為NULL值的test_add用戶定義數(shù)據(jù)類型。 USESales GO EXECsp_addtypetest_add,'Varchar(10)','NOTNULL' GO此后，test_add可用為數(shù)據(jù)列或變量的數(shù)據(jù)類型。9.1.1用戶定義數(shù)據(jù)類型2．使用企業(yè)管理器創(chuàng)建用戶定義數(shù)據(jù)類型在企業(yè)管理器中，為Sales數(shù)據(jù)庫(kù)創(chuàng)建—個(gè)不允許NULL值的test_add用戶定義數(shù)據(jù)類型，操作步驟如下。(1)選擇Sales數(shù)據(jù)庫(kù)。(2)在右窗格中選擇“用戶定義的數(shù)據(jù)類型”項(xiàng)，單擊鼠標(biāo)右鍵，在出現(xiàn)的快捷菜單中選擇“新建用戶定義數(shù)據(jù)類型”命令。(3)在“用戶定義的數(shù)據(jù)類型屬性”對(duì)話框中的文本框內(nèi)輸入test_add。(4)在“數(shù)據(jù)類型”下拉列表框中，選擇char。(5)在“長(zhǎng)度”文本框中輸入10。(6)選中“允許NULL值”復(fù)選框。(7)單擊“確定”按鈕完成創(chuàng)建用戶自定義數(shù)據(jù)類型。9.1數(shù)據(jù)與表達(dá)式9.1.2常量與變量在程序運(yùn)行中保持常值的數(shù)據(jù)，即程序本身不能改變其值的數(shù)據(jù)，稱為常量，在程序中經(jīng)常直接使用文字符號(hào)表示。相應(yīng)地，在程序運(yùn)行過(guò)程中可以改變其值的數(shù)據(jù)，稱為變量。9.1.2常量與變量1．常量常量是表示特定數(shù)據(jù)值的符號(hào)，其格式取決于其數(shù)據(jù)類型(1)字符串和二進(jìn)制常量字符串常量括在單引號(hào)內(nèi)并包含字母數(shù)字字符(a-z、A-Z和0-9)以及特殊字符，如感嘆號(hào)(!)、at符(@)和數(shù)字號(hào)(#)。例如：‘Cincinnati’、‘O’‘Brien’、‘ProcessXis50%complete.’、“O‘Brien”為字符串常量。二進(jìn)制常量具有前輟0x并且是十六進(jìn)制數(shù)字字符串，它們不使用引號(hào)。例如0xAE、0x12Ef、0x69048AEFDD010E、0x(空串)為二進(jìn)制常量。(2)日期/時(shí)間常量datetime常量使用特定格式的字符日期值表示，用單引號(hào)括起來(lái)。輸入時(shí)，可以使用“/”、“.”、“-”作日期/時(shí)間常量的分隔符。輸入格式datetime值Smalldatetime值Sep3,20051:34:34.1222005-09-0301:34:34.1232005-09-0301:35:009/3/20051PM2005-09-0313:00:00.0002005-09-0313:00:009.3.200513:002005-09-0313:00:00.0002005-09-0313:00:0013:25:191900-01-0113:25:19.0001900-01-0113:25:009/3/20052005-09-0300:00:00.0002005-09-0300:00:009.1.2常量與變量(3)數(shù)值常量①整型常量由沒(méi)有用引號(hào)括起來(lái)且不含小數(shù)點(diǎn)的一串?dāng)?shù)字表示。例如，1894、2為整型常量。②浮點(diǎn)常量主要采用科學(xué)記數(shù)法表示，例如，101.5E5、0.5E-2為浮點(diǎn)常量。③精確數(shù)值常量由沒(méi)有用引號(hào)括起來(lái)且包含小數(shù)點(diǎn)的一串?dāng)?shù)字表示。例如，1894.1204、2.0為精確數(shù)值常量。④貨幣常量是以“$”為前綴的一個(gè)整型或?qū)嵭统Ａ繑?shù)據(jù)，不使用引號(hào)。例如，$12.5、$542023.14為貨幣常量。⑤uniqueidentifier常量是表示全局惟一標(biāo)識(shí)符GUID值的字符串。可以使用字符或二進(jìn)制字符串格式指定。9.1.2常量與變量邏輯數(shù)據(jù)常量使用數(shù)字0或1表示，并且不使用引號(hào)。非0的數(shù)字當(dāng)作1處理。(5)空值在數(shù)據(jù)列定義之后，還需確定該列是否允許空值(NULL)。允許空值意味著用戶在向表中插入數(shù)據(jù)時(shí)可以忽略該列值。空值可以表示整型、實(shí)型、字符型數(shù)據(jù)。(4)邏輯數(shù)據(jù)常量9.1.2常量與變量變量用于臨時(shí)存放數(shù)據(jù)，變量中的數(shù)據(jù)隨著程序的運(yùn)行而變化，變量有名字與數(shù)據(jù)類型兩個(gè)屬性。變量的命名使用常規(guī)標(biāo)識(shí)符，即以字母、下劃線(_)、at符號(hào)(@)、數(shù)字符號(hào)(#)開(kāi)頭，后續(xù)字母、數(shù)字、at符號(hào)、美元符號(hào)($)、下劃線的字符序列。不允許嵌入空格或其他特殊字符。2．變量9.1.2常量與變量全局變量由系統(tǒng)定義并維護(hù)，通過(guò)在名稱前面加“@@”符號(hào)局部變量的首字母為單個(gè)“@”。全局變量和局部變量9.1.2常量與變量(1)局部變量局部變量使用DECLARE語(yǔ)句定義DECLARE{@local_variabledata_type}[,...n]變量名最大長(zhǎng)度為30個(gè)字符。一條DECLARE語(yǔ)句可以定義多個(gè)變量，各變量之間使用逗號(hào)隔開(kāi)。例如DECLARE@namevarchar(30),@typeint9.1.2常量與變量局部變量的賦值①用SELECT為局部變量賦值SELECT@variable_name=expression[,…n]FROM…WHERE…例如DECLARE@int_varintSELECT@int_var=12/*給@int_var賦值*/SELECT@int_var/*將@int_var的值輸出到屏幕上*/9.1.2常量與變量在一條語(yǔ)句中可以同時(shí)對(duì)幾個(gè)變量進(jìn)行賦值例如DECLARE@LastNamechar(8),@Firstnamechar(8),@BirthDatedatetimeSELECT@LastName='Smith',@Firstname='David',@BirthDate='1985-2-20'SELECT@LastName,@Firstname,@BirthDate局部變量沒(méi)有被賦值前，其值是NULL，若要在程序中引用它，必須先賦值。9.1.2常量與變量例9-1使用SELECT語(yǔ)句從customer表中檢索出顧客編號(hào)為“C0002”的行，再將顧客的名字賦給變量@customer。DECLARE@customervarchar(40),@curdatedatetimeSELECT@customer=customer_name,@curdate=getdate()FROMcustomerWHEREcustomer_id='C0002'9.1.2常量與變量②利用UPDATE為局部變量賦值例9-2將sell_order表中的transporter_id列值為“T001”、goods_id列值為“G00003”的order_num列的值賦給局部變量@order_num。DECLARE@order_numfloatUPDATEsell_orderSET@order_num=order_num*2 WHEREtransporter_id='T001'ANDgoods_id='G00003'9.1.2常量與變量③用SET給局部變量賦值SET語(yǔ)句格式為：SET{@local_variable=expression}使用SET初始化變量的方法與SELECT語(yǔ)句相同，但一個(gè)SET語(yǔ)句只能為一個(gè)變量賦值。例9-3計(jì)算employee表的記錄數(shù)并賦值給局部變量@rows。DECLARE@rowsintSET@rows=(SELECTCOUNT(*)FROMemployee)SELECT@rows9.1.2常量與變量(2)全局變量全局變量通常被服務(wù)器用來(lái)跟蹤服務(wù)器范圍和特定會(huì)話期間的信息，不能顯式地被賦值或聲明。全局變量不能由用戶定義，也不能被應(yīng)用程序用來(lái)在處理器之間交叉?zhèn)鬟f信息。9.1.2常量與變量①@@rowcount@@rowcount存儲(chǔ)前一條命令影響到的記錄總數(shù)，除了DECLARE語(yǔ)句之外，其他任何語(yǔ)句都可以影響@@rowcount的值。例如DECLARE@rowsintSELECT@rows=@@rowcount9.1.2常量與變量②@@error如果@@error為非0值，則表明執(zhí)行過(guò)程中產(chǎn)生了錯(cuò)誤，此時(shí)應(yīng)當(dāng)在程序中采取相應(yīng)的措施加以處理。@@error的值與@@rowcount一樣，會(huì)隨著每一條SQLServer語(yǔ)句的變化而改變。例9-4使服務(wù)器產(chǎn)生服務(wù)，并用顯示錯(cuò)誤號(hào)。raiserror('miscellaneouserrormessage',16,1)/*產(chǎn)生一個(gè)錯(cuò)誤*/if@@error<>0SELECT@@erroras'lasterror'運(yùn)行結(jié)果：服務(wù)器:消息50000，級(jí)別16，狀態(tài)1，行1miscellaneouserrormessagelasterror09.1.2常量與變量例9-5捕捉例9-4中服務(wù)器產(chǎn)生的錯(cuò)誤號(hào)，并顯示出來(lái)。DECLARE@my_errorintRAISERROR('miscellaneouserrormessage',16,1)SELECT@my_error=@@errorIF@my_error<>0SELECT@my_erroras'lasterror'運(yùn)行結(jié)果：服務(wù)器:消息50000，級(jí)別16，狀態(tài)1，行2miscellaneouserrormessagelasterror500009.1.2常量與變量③@@trancount@@trancount記錄當(dāng)前的事務(wù)數(shù)量，當(dāng)某個(gè)事務(wù)當(dāng)前并沒(méi)有結(jié)束會(huì)話過(guò)程時(shí)，@@trancount的值大于0。④@@version@@version的值代表服務(wù)器的當(dāng)前版本和當(dāng)前操作系統(tǒng)版本，是SQLServer中一項(xiàng)較實(shí)用的技術(shù)支持，通常對(duì)識(shí)別網(wǎng)絡(luò)中某個(gè)未命名的服務(wù)器時(shí)非常有用。⑤@@spid@@spid返回當(dāng)前用戶進(jìn)程的服務(wù)器進(jìn)程ID，可以用來(lái)識(shí)別sp_who輸出中的當(dāng)前用戶進(jìn)程。9.1.2常量與變量例9-6使用@@spid返回當(dāng)前用戶進(jìn)程的ID。SELECT@@spidas'ID',SYSTEM_USERAS'LoginName',USERAS'UserName'運(yùn)行結(jié)果：IDLoginNameUserName52sa dbo 9.1.2常量與變量9.1數(shù)據(jù)與表達(dá)式9.1.3運(yùn)算符與表達(dá)式運(yùn)算符用來(lái)執(zhí)行數(shù)據(jù)列之間的數(shù)學(xué)運(yùn)算或比較操作。表達(dá)式是符號(hào)與運(yùn)算符的組合。簡(jiǎn)單的表達(dá)式可以是一個(gè)常量、變量、列或函數(shù)，復(fù)雜表達(dá)式是由運(yùn)算符連接一個(gè)或多個(gè)簡(jiǎn)單表達(dá)式。9.1.3運(yùn)算符與表達(dá)式1.算術(shù)運(yùn)算符與表達(dá)式算術(shù)運(yùn)算符用于數(shù)值型列或變量間的算術(shù)運(yùn)算。算術(shù)運(yùn)算符包括加(+)、減(-)、乘(*)、除(/)和取模(%)運(yùn)算等。例9-9使用“+”將goods表中高于9000的商品價(jià)格增加15元：SELECTgoods_name,unit_price,(unit_price+15)ASnowpriceFROMgoodsWHEREunit_price>9000運(yùn)行結(jié)果如圖所示。9.1.3運(yùn)算符與表達(dá)式2.位運(yùn)算符與表達(dá)式位運(yùn)算符用以對(duì)數(shù)據(jù)進(jìn)行按位與(＆)、或(|)、異或(^)、求反(~)等運(yùn)算。&運(yùn)算只有當(dāng)兩個(gè)表達(dá)式中的兩個(gè)位值都為1時(shí)，結(jié)果中的位才被設(shè)置為1，否則結(jié)果中的位被設(shè)置為0。|運(yùn)算時(shí)，如果在兩個(gè)表達(dá)式的任一位為1或者兩個(gè)位均為1，那么結(jié)果的對(duì)應(yīng)位被設(shè)置為1；如果表達(dá)式中的兩個(gè)位都不為1，則結(jié)果中該位的值被設(shè)置為0。^運(yùn)算時(shí)，如果在兩個(gè)表達(dá)式中，只有一位的值為1，則結(jié)果中位的值被設(shè)置為1；如果兩個(gè)位的值都為0或者都為1，則結(jié)果中該位的值被清除為0。9.1.3運(yùn)算符與表達(dá)式例如，170與75進(jìn)行&運(yùn)算先將170和75轉(zhuǎn)換為二進(jìn)制數(shù)0000000010101010和0000000001001011，再進(jìn)行&運(yùn)算的結(jié)果是0000000000001010，即十進(jìn)制數(shù)10。同樣，表達(dá)式5^2，~1，5|2的運(yùn)算結(jié)果為：7，0，7。9.1.3運(yùn)算符與表達(dá)式3.比較運(yùn)算符與表達(dá)式比較運(yùn)算符用來(lái)比較兩個(gè)表達(dá)式的值是否相同，可用于字符、數(shù)字或日期數(shù)據(jù)。SQLServer中的比較運(yùn)算符有大于(>)、小于(<)、大于等于(>=)、小于等于(<=)和不等于(!=)等，比較運(yùn)算返回布爾值，通常出現(xiàn)在條件表達(dá)式中。比較運(yùn)算符的結(jié)果為布爾數(shù)據(jù)類型，其值為TRUE、FALSE及UNKNOWN。例如，表達(dá)式2=3的運(yùn)算結(jié)果為FALSE。9.1.3運(yùn)算符與表達(dá)式4.邏輯運(yùn)算符與表達(dá)式邏輯運(yùn)算符與(AND)、或(OR)、非(NOT)等，用于對(duì)某個(gè)條件進(jìn)行測(cè)試，以獲得其真實(shí)情況。邏輯運(yùn)算符和比較運(yùn)算符一樣，返回TRUE或FALSE的布爾數(shù)據(jù)值。9.1.3運(yùn)算符與表達(dá)式表9-5邏輯運(yùn)算符運(yùn)算符含義AND如果兩個(gè)布爾表達(dá)式都為TRUE，那么結(jié)果為TRUE。OR如果兩個(gè)布爾表達(dá)式中的一個(gè)為TRUE，那么結(jié)果就為TRUE。NOT對(duì)任何其他布爾運(yùn)算符的值取反。LIKE如果操作數(shù)與一種模式相匹配，那么值為TRUE。IN如果操作數(shù)等于表達(dá)式列表中的一個(gè)，那么值為TRUE。ALL如果一系列的比較都為TRUE，那么值為TRUE。ANY如果一系列的比較中任何一個(gè)為TRUE，那么值為TRUE。BETWEEN如果操作數(shù)在某個(gè)范圍之內(nèi)，那么值為TRUE。EXISTS如果子查詢包含一些行，那么值為TRUE。9.1.3運(yùn)算符與表達(dá)式邏輯運(yùn)算符通常和比較運(yùn)算一起構(gòu)成更為復(fù)雜的表達(dá)式。邏輯運(yùn)算符的操作數(shù)都只能是布爾型數(shù)據(jù)。例如，在表employee中查找1973年以前與1980年以后出生的男員工的表達(dá)式為：(year(birth_date)<1973ORyear(birth_date)>1980)ANDsex='男'9.1.3運(yùn)算符與表達(dá)式LIKE運(yùn)算符確定給定的字符串是否與指定的模式匹配，通常只限于字符數(shù)據(jù)類型。LIKE的通配符如下表運(yùn)算符描述示例%包含零個(gè)或多個(gè)字符的任意字符串。addressLIKE'%公司%'將查找地址任意位置包含公司的所有職員。_下劃線，對(duì)應(yīng)任何單個(gè)字符。employee_nameLIKE'_海燕'將查找以“海燕”結(jié)尾的所有6個(gè)字符的名字。[]指定范圍([a-f])或集合([abcdef])中的任何單個(gè)字符。employee_nameLIKE'[張李王]海燕'將查找張海燕、李海燕、王海燕等。[^]不屬于指定范圍([a-f])或集合([abcdef])的任何單個(gè)字符。employee_nameLIKE'[^張李]海燕'將查找不姓張、李的名為海燕的職員。9.1.3運(yùn)算符與表達(dá)式例如,查找所有姓“錢”的員工及住址SELECTemployee_name,addressFROMemployeeWHEREemployee_nameLIKE'錢%'9.1.3運(yùn)算符與表達(dá)式4．連接運(yùn)算符與表達(dá)式連接運(yùn)算符(+)用于兩個(gè)字符串?dāng)?shù)據(jù)的連接，通常也稱為字符串運(yùn)算符。在SQLServer中，對(duì)字符串的其他操作通過(guò)字符串函數(shù)進(jìn)行。字符串連接運(yùn)算符的操作數(shù)類型有char、varchar和text等。例如，‘Dr.’+‘Computer’的運(yùn)算結(jié)果:'Dr.Computer'9.1.3運(yùn)算符與表達(dá)式5．運(yùn)算符的優(yōu)先級(jí)別SQLServer中各種運(yùn)算符的優(yōu)先順序如下：()→~→^→＆→|→*、/、%→+、-→NOT→AND→OR 排在前面的運(yùn)算符的優(yōu)先級(jí)高于其后的運(yùn)算符。在一個(gè)表達(dá)式中，先計(jì)算優(yōu)先級(jí)較高的運(yùn)算，后計(jì)算優(yōu)先級(jí)低的運(yùn)算，相同優(yōu)先級(jí)的運(yùn)算按自左向右的順序依次進(jìn)行。9.2函數(shù)9.2.1常用函數(shù)9.2.2用戶定義函數(shù)9.2函數(shù)9.2.1常用函數(shù)函數(shù)是—組編譯好的Transact-SQL語(yǔ)句，它們可以帶一個(gè)或一組數(shù)值做參數(shù)，也可不帶參數(shù)，它返回一個(gè)數(shù)值、數(shù)值集合，或執(zhí)行一些操作。函數(shù)能夠重復(fù)執(zhí)行一些操作，從而避免不斷重寫代碼。SQLServer2000支持兩種函數(shù)類型：(1)內(nèi)置函數(shù):是一組預(yù)定義的函數(shù)，是Transact-SQL語(yǔ)言的一部分，按Transact-SQL參考中定義的方式運(yùn)行且不能修改。(2)用戶定義函數(shù):由用戶定義的Transact-SQL函數(shù)。它將頻繁執(zhí)行的功能語(yǔ)句塊封裝到一個(gè)命名實(shí)體中，該實(shí)體可以由Transact-SQL語(yǔ)句調(diào)用。9.2.1常用函數(shù)1．字符串函數(shù)字符串函數(shù)用來(lái)實(shí)現(xiàn)對(duì)字符型數(shù)據(jù)的轉(zhuǎn)換、查找、分析等操作，通常用做字符串表達(dá)式的一部分。表9-7中列出了SQLServer的常用字符串函數(shù)。9.2.1常用函數(shù)(1)使用datalength和Len函數(shù)datalength函數(shù)主要用于判斷可變長(zhǎng)字符串的長(zhǎng)度，對(duì)于定長(zhǎng)字符串將返回該列的長(zhǎng)度。要得到字符串的真實(shí)長(zhǎng)度，通常需要使用rtrim函數(shù)截去字符串尾部的空格。Len函數(shù)可以獲取字符串的字符個(gè)數(shù)，而不是字節(jié)數(shù)，也不包含尾隨空格。9.2.1常用函數(shù)例9-10從表department中讀取manger列的各記錄的實(shí)際長(zhǎng)度。SELECTDatalength(rtrim(manger))AS'DATALENGTH',Len(rtrim(manger))AS'LEN'FROMdepartment運(yùn)行結(jié)果如下：DATALENGTHLEN426363639.2.1常用函數(shù)(2)使用Soundex函數(shù)soundex函數(shù)將char_expr轉(zhuǎn)換為4個(gè)字符的聲音碼，其中第一個(gè)碼為原字符串的第一個(gè)字符，第2~4個(gè)字符為數(shù)字，是該字符串的聲音字母所對(duì)應(yīng)的數(shù)字，但忽略了除首字母外的串中的所有元音。Soundex函數(shù)可用來(lái)查找聲音相似的字符串，但它對(duì)數(shù)字和漢字均只返回0值。例如SELECTsoundex('1'),soundex('a'),soundex('計(jì)算機(jī)'),soundex('abc'),soundex('abcd'),soundex('a12c'),soundex('a數(shù)字')返回值為：0000A0000000A120A120A000A0009.2.1常用函數(shù)(4)使用Charindex函數(shù)實(shí)現(xiàn)串內(nèi)搜索charindex函數(shù)主要用于在串內(nèi)找出與指定串匹配的串，如果找到的話，charindex函數(shù)返回第一個(gè)匹配的位置。格式：Charindex(expr1,expr2[,start_location])expr1是待查找的字符串expr2是用來(lái)搜索expr1的字符表達(dá)式，start_location是在expr2中查找expr1的開(kāi)始位置，如果此值省略、為負(fù)或?yàn)?，均從起始位置開(kāi)始查找。9.2.1常用函數(shù)例如SELECTcharindex(',','red,white,blue') 該查詢確定了字符串'red,white,blue'中第一個(gè)逗號(hào)的位置。9.2.1常用函數(shù)(5)使用Patindex函數(shù)patindex函數(shù)返回在指定表達(dá)式中模式第一次出現(xiàn)的起始位置，如果模式?jīng)]有則返回0。格式：Patindex('%pattern%',expression)pattern是字符串，%字符必須出現(xiàn)在模式的開(kāi)頭和結(jié)尾。expression通常是搜索指定子串的表達(dá)式或列。例如： SELECTpatindex('%abc%','abc123'),patindex('123','abc123') 子串“abc”和“123”在字符串“abc123”中出現(xiàn)的起始位置分別為：1和0。因?yàn)樽哟?23”不是以%開(kāi)頭和結(jié)尾。9.2.1常用函數(shù)2．?dāng)?shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)用來(lái)實(shí)現(xiàn)各種數(shù)學(xué)運(yùn)算，如指數(shù)運(yùn)算、對(duì)數(shù)運(yùn)算、三角運(yùn)算等，其操作數(shù)為數(shù)值型數(shù)據(jù)，如int、float、real、money等表9-8列出了SQLServer的數(shù)學(xué)函數(shù)。9.2.1常用函數(shù)例9-11在同一表達(dá)式中使用sin、atan、rand、pi、sign函數(shù)。 SELECTsin(23.45),atan(1.234),rand(),pi(),sign(-2.34)運(yùn)行結(jié)果如下：-0.993740710172659640.889762448959189320.19756617656167863.1415926535897931-1.009.2.1常用函數(shù)例9-12用ceiling和floor函數(shù)返回大于或等于指定值的最小整數(shù)值和小于或等于指定值的最大整數(shù)值。 SELECTceiling(123),floor(321),ceiling(12.3),ceiling(-32.1),floor(-32.1)運(yùn)行結(jié)果如下：123 321 13 -32 -339.2.1常用函數(shù)

SELECTround(12.34512,3),round(12.34567,3),round(12.345,-2),round(54.321,-2)運(yùn)行結(jié)果如下：12.34500 12.34600.000 100.000Round(numeric_expr,int_expr)的int_expr為負(fù)數(shù)時(shí)，將小數(shù)點(diǎn)左邊第int_expr位四舍五入。

例9-13round函數(shù)的使用。9.2.1常用函數(shù)3．日期函數(shù)日期函數(shù)用來(lái)操作datetime和smalldatetime類型的數(shù)據(jù)，執(zhí)行算術(shù)運(yùn)算。與其他函數(shù)一樣，可以在SELECT語(yǔ)句和WHERE子句以及表達(dá)式中使用日期函數(shù)。9.2.1常用函數(shù)表9-9SQLServer的日期函數(shù)函數(shù)名稱及格式描述Getdate()返回當(dāng)前系統(tǒng)的日期和時(shí)間Datename(datepart,date_expr)以字符串形式返回date_expr中的指定部分，如果合適的話還將其轉(zhuǎn)換為名稱(如June)Datepart(datepart,date_expr)以整數(shù)形式返回date_expr中的datepart指定部分Datediff(datepart,date_expr1,date_expr2)以datepart指定的方式，返回date_expr2與date_expr1之差Dateadd(datepart,number,date_expr)返回以datepart指定方式表示的date_expr加上number以后的日期Day(date_expr)返回date_expr中的日期值Month(date_expr)返回date_expr中的月份值Year(date_expr)返回date_expr中的年份值9.2.1常用函數(shù)表9-10SQLServer的日期部分日期部分寫法取值范圍Yearyy1753~9999Quarterqq1~4Monthmm1~12Dayofyeardy1~366Daydd1~31Weekwk1~54Weekdaydw1~7(Mon~Sun)Hourhh0~23Minutemi0~59Secondss0~59Millisecondms0~9999.2.1常用函數(shù)例9-14使用datediff函數(shù)來(lái)確定貨物是否按時(shí)送給客戶。

SELECTgoods_id,datediff(dd,send_date,arrival_date) FROMpurchase_order為了從datediff中得到一個(gè)正值，應(yīng)注意把較早的日期放在前面9.2.1常用函數(shù)例9-15使用datename函數(shù)返回員工的出生日期的月份(mm)名稱。SELECTemployee_name,datename(mm,birth_date)FROMemployee運(yùn)行結(jié)果如下：錢達(dá)理 December東方牧 April郭文斌 March肖海燕 July張明華 August9.2.1常用函數(shù)4．系統(tǒng)函數(shù)系統(tǒng)函數(shù)用于獲取有關(guān)計(jì)算機(jī)系統(tǒng)、用戶、數(shù)據(jù)庫(kù)和數(shù)據(jù)庫(kù)對(duì)象的信息。與其他函數(shù)一樣，可以在SELECT和WHERE子句以及表達(dá)式中使用系統(tǒng)函數(shù)。表9-11列出了SQLServer的系統(tǒng)函數(shù)。9.2.1常用函數(shù)例9-16使用object_name函數(shù)返回已知ID號(hào)的對(duì)象名。SELECTobject_name(469576711)運(yùn)行結(jié)果如下：Employee9.2.1常用函數(shù)例9-17利用object_id函數(shù)，根據(jù)表名返回該表的ID號(hào)。SELECTnameFROMsysindexesWHEREid=object_id('customer')運(yùn)行結(jié)果如下：namecustomer_WA_Sys_customer_id_75D7831F9.2函數(shù)9.2.2用戶定義函數(shù)根據(jù)函數(shù)返回值形式的不同將用戶定義函數(shù)分為3種類型。(1)標(biāo)量函數(shù) 標(biāo)量函數(shù)返回一個(gè)確定類型的標(biāo)量值，其函數(shù)值類型為SQLServer的系統(tǒng)數(shù)據(jù)類型（除text、ntext、image、cursor、timestamp、table類型外）。函數(shù)體語(yǔ)句定義在BEGIN…END語(yǔ)句內(nèi)。(2)內(nèi)嵌表值函數(shù) 內(nèi)嵌表值函數(shù)返回的函數(shù)值為一個(gè)表。內(nèi)嵌表值函數(shù)的函數(shù)體不使用BEGIN…END語(yǔ)句，其返回的表是RETURN子句中的SELECT命令查詢的結(jié)果集，其功能相當(dāng)于一個(gè)參數(shù)化的視圖。(3)多語(yǔ)句表值函數(shù) 多語(yǔ)句表值函數(shù)可以看作標(biāo)量函數(shù)和內(nèi)嵌表值函數(shù)的結(jié)合體。其函數(shù)值也是一個(gè)表，但函數(shù)體也用BEGIN…END語(yǔ)句定義，返回值的表中的數(shù)據(jù)由函數(shù)體中的語(yǔ)句插入。9.2.2用戶定義函數(shù)1.創(chuàng)建用戶定義函數(shù)(1)使用CREATEFUNCTION創(chuàng)建用戶定義函數(shù)標(biāo)量函數(shù)的語(yǔ)法格式：CREATEFUNCTION[owner_name.]function_name([{@parameter_name[AS]scalar_parameter_data_type[=default]}[,...n]])RETURNSscalar_return_data_type[WITH<function_option>[[,]...n]][AS]BEGINfunction_bodyRETURNscalar_expressionEND9.2.2用戶定義函數(shù)內(nèi)嵌表值函數(shù)的語(yǔ)法格式：CREATEFUNCTION[owner_name.]function_name([{@parameter_name[AS]scalar_parameter_data_type[=default]}[,...n]])RETURNSTABLE[WITH<function_option>[[,]...n]][AS]RETURN[(]select_stmt[)]9.2.2用戶定義函數(shù)多語(yǔ)句表值函數(shù)的語(yǔ)法格式：CREATEFUNCTION[owner_name.]function_name([{@parameter_name[AS]scalar_parameter_data_type[=default]}[,...n]])RETURNS@return_variableTABLE<table_type_definition>[WITH<function_option>[[,]...n]][AS]BEGINfunction_bodyRETURNEND9.2.2用戶定義函數(shù)例9-18創(chuàng)建一個(gè)用戶定義函數(shù)DatetoQuarter，將輸入的日期數(shù)據(jù)轉(zhuǎn)換為該日期對(duì)應(yīng)的季度值。如輸入'2006-8-5'，返回'3Q2006'，表示2006年3季度。CREATEFUNCTIONDatetoQuarter(@dqdatedatetime)RETURNSchar(6)ASBEGINRETURN(datename(q,@dqdate)+'Q'+datename(yyyy,@dqdate))END9.2.2用戶定義函數(shù)例9-19創(chuàng)建用戶定義函數(shù)goodsq，返回輸入商品編號(hào)的商品名稱和庫(kù)存量。CREATEFUNCTIONgoodsq(@goods_idvarchar(30))RETURNSTABLEASRETURN(SELECTgoods_name,stock_quantityFROMgoodsWHEREgoods_id=@goods_id)9.2.2用戶定義函數(shù)例9-20根據(jù)輸入的訂單編號(hào)，返回該訂單對(duì)應(yīng)商品的編號(hào)、名稱、類別編號(hào)、類別名稱。CREATEFUNCTIONgood_info(@in_o_idvarchar(10))RETURNS@goodinfoTABLE(o_idchar(6),g_idchar(6),g_namevarchar(50),c_idchar(6),c_namevarchar(20))ASBEGINDECLARE@g_idvarchar(10),@g_namevarchar(30)DECLARE@c_idvarchar(10),@c_namevarchar(30)SELECT@g_id=goods_idFROMsell_orderWHEREorder_id1=@in_o_idSELECT@g_name=goods_name,@c_id=classification_idFROMgoodsWHEREgoods_id=@g_idSELECT@c_name=classification_nameFROMgoods_classificationWHERE@c_id=classification_idINSERT@goodinfoVALUES(@in_o_id,@g_id,@g_name,@c_id,@c_name)RETURNEND9.2.2用戶定義函數(shù)①在企業(yè)管理器中選擇要?jiǎng)?chuàng)建用戶定義函數(shù)的數(shù)據(jù)庫(kù)（如Sales），在數(shù)據(jù)庫(kù)對(duì)象“用戶定義函數(shù)”項(xiàng)上單擊右鍵，從彈出的快捷菜單中選擇“新建用戶定義的函數(shù)...”選項(xiàng)，出現(xiàn)如圖所示的“用戶定義函數(shù)屬性”對(duì)話框。(2)使用企業(yè)管理器創(chuàng)建用戶定義函數(shù)9.2.2用戶定義函數(shù)②在“用戶定義函數(shù)屬性”對(duì)話框的文本框中指定函數(shù)名稱（如numtostr），編寫函數(shù)的代碼。③單擊“檢查語(yǔ)法”按鈕，出現(xiàn)“語(yǔ)法檢查成功”消息框后，單擊“確定”按鈕，將用戶定義函數(shù)對(duì)象添加到數(shù)據(jù)庫(kù)中。9.2.2用戶定義函數(shù)2.執(zhí)行用戶定義函數(shù)使用函數(shù)需要指出函數(shù)所有者，即為函數(shù)加上所有者權(quán)限作為前綴。其語(yǔ)法格式如下： [database_name.]owner_name.function_name([argument_expr][,...])9.2.2用戶定義函數(shù)例如，調(diào)用例9-18創(chuàng)建的用戶定義函數(shù)DatetoQuarterSELECTdbo.DatetoQuarter('2006-8-5')運(yùn)行結(jié)果為：3Q2006調(diào)用例9-20創(chuàng)建的用戶定義函數(shù)good_info，使用以下語(yǔ)句：SELECT*FROMdbo.good_info('S00002')運(yùn)行結(jié)果為表的記錄，如圖9-3所示。9.2.2用戶定義函數(shù)3.修改和刪除用戶定義函數(shù)用企業(yè)管理器修改用戶定義函數(shù)，選擇要修改函數(shù)，單擊右鍵，從快捷菜單中選擇“屬性”選項(xiàng)，打開(kāi)圖9-2所示的“用戶定義函數(shù)屬性”對(duì)話框。在該對(duì)話框中可以修改用戶定義函數(shù)的函數(shù)體、參數(shù)等。從快捷菜單中選擇“刪除”選項(xiàng)，則可刪除用戶定義函數(shù)。用ALTERFUNCTION命令也可以修改用戶定義函數(shù)。此命令的語(yǔ)法與CREATFUNCTION相同，使用ALTERFUNCTION命令相當(dāng)于重建一個(gè)同名的函數(shù)。使用DROPFUNCTION命令刪除用戶定義函數(shù)，其語(yǔ)法如下：DROPFUNCTION{[owner_name.]function_name}[,...n]其中，function_name是要?jiǎng)h除的用戶定義的函數(shù)名稱。9.2.2用戶定義函數(shù)例如，刪除例9-18創(chuàng)建的用戶定義函數(shù)DROPFUNCTONDatetoQuarter刪除用戶定義函數(shù)時(shí)，可以不加所有者前綴。9.3程序控制流語(yǔ)句9.3.1語(yǔ)句塊和注釋9.3.2選擇控制9.3.3循環(huán)控制9.3.4批處理9.3程序控制流語(yǔ)句9.3.1語(yǔ)句塊和注釋Transact-SQL提供了控制流語(yǔ)言的特殊關(guān)鍵字和用于編寫過(guò)程性代碼的語(yǔ)法結(jié)構(gòu)，可進(jìn)行順序、分支、循環(huán)、存儲(chǔ)過(guò)程、觸發(fā)器等程序設(shè)計(jì)，編寫結(jié)構(gòu)化的模塊代碼，并放置到數(shù)據(jù)庫(kù)服務(wù)器上。9.3.1語(yǔ)句塊和注釋BEGIN...END用來(lái)設(shè)定一個(gè)語(yǔ)句塊，將在BEGIN...END內(nèi)的所有語(yǔ)句視為一個(gè)邏輯單元執(zhí)行。語(yǔ)句塊BEGIN...END的語(yǔ)法格式為：BEGIN{sql_statement|statement_block}END1.語(yǔ)句塊BEGIN...END9.3.1語(yǔ)句塊和注釋USESalesGODECLARE@linkman_namechar(8)BEGINSELECT@linkman_name=(SELECTlinkman_nameFROMcustomerWHEREcustomer_idLIKE'C0001')SELECT@linkman_nameEND例9-21顯示Sales數(shù)據(jù)庫(kù)中customer表的編號(hào)為'C0001'的聯(lián)系人姓名。9.3.1語(yǔ)句塊和注釋在BEGIN...END中可嵌套另外的BEGIN...END來(lái)定義另一程序塊。例9-22語(yǔ)句塊嵌套舉例。DECLARE@errorcodeint,@nowdatedateTIMEBEGINSET@nowdate=getdate()INSERTsell_order(order_date,send_date,arriver