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

CONSTRAINTg_o_keyPRIMARYKEY(good_type,order_time))8.4.2外鍵約束外鍵約束定義了表與表之間的關系。通過將一個表中一列或多列添加到另一個表中，創建兩個表之間的連接，這個列就成為第二個表的外鍵(ForeignKey，FK)，即外鍵是用于建立和加強兩個表數據之間的連接的一列或多列，通過它可以強制參照完整性。例如，Sales數據庫中的employee、sell_order、goods這3個表之間存在以下邏輯聯系：sell_order(銷售訂單)表中employee_id(員工編號)列的值必須是employee表employee_id列中的某一個值，因為簽訂銷售訂單的人必須是當前公司員工；而sell_order表中goods_id(貨物編號)列的值必須是goods(貨物)表的goods_id列中的某一個值，因為銷售訂單上售出的只能是已知貨物。因此，在sell_order表上應建立兩個外鍵約束FK_sell_order_employee和FK_sell_order_goods來限制sell_order表employee_id列和goods_id列的值必須分別來自employee表的employee_id列及goods表的goods_id列。8.4.2外鍵約束8.4.2外鍵約束級聯操作SQLServer提供了兩種級聯操作以保證數據完整性：(1)級聯刪除確定當主鍵表中某行被刪除時，外鍵表中所有相關行將被刪除。(2)級聯修改確定當主鍵表中某行的鍵值被修改時，外鍵表中所有相關行的該外鍵值也將被自動修改為新值。8.4.2外鍵約束外鍵的表約束與列約束定義表級外鍵約束的語法格式如下：[CONSTRAINT約束名]FOREIGNKEY(列名[,…n])REFERENCES參照主表[(參照列[,…n])][ONDELETE{CASCADE|NOACTION}][ONUPDATE{CASCADE|NOACTION}]][NOTFORREPLICATION]定義列級外鍵約束的語法格式如下：[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創建一個訂貨表sell_order1，與例8-18創建的產品表goods1相關聯。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創建表sell_order2，并為goods_id、employee_id、custom_id三列定義外鍵約束。8.4.2外鍵約束8.4使用約束實施數據完整性8.4.3惟一性約束惟一性(Unique)約束指定一個或多個列的組合的值具有惟一性，以防止在列中輸入重復的值，為表中的一列或者多列提供實體完整性。惟一性約束指定的列可以有NULL屬性。主鍵也強制執行惟一性，但主鍵不允許空值，故主鍵約束強度大于惟一約束。因此主鍵列不能再設定惟一性約束。8.4.3惟一性約束定義列級惟一性約束的語法格式如下：[CONSTRAINTconstraint_name]UNIQUE[CLUSTERED|NONCLUSTERED]惟一性約束應用于多列時的定義格式：[CONSTRAINTconstraint_name]UNIQUE[CLUSTERED|NONCLUSTERED](column_name[,…n])8.4.3惟一性約束例8-23定義一個員工信息表employees，其中員工的身份證號emp_cardid列具有惟一性。CREATETABLEemployees(emp_idchar(8),emp_namechar(10),emp_cardidchar(18),CONSTRAINTpk_emp_idPRIMARYKEY(emp_id),CONSTRAINTuk_emp_cardidUNIQUE(emp_cardid))8.4使用約束實施數據完整性8.4.4檢查約束檢查(Check)約束對輸入列或整個表中的值設置檢查條件，以限制輸入值，保證數據庫的數據完整性。當對具有檢查約束列進行插入或修改時，SQLServer將用該檢查約束的邏輯表達式對新值進行檢查，只有滿足條件(邏輯表達式返回TRUE)的值才能填入該列，否則報錯。可以為每列指定多個CHECK約束。8.4.4檢查約束定義檢查約束的語法格式：[CONSTRAINTconstraint_name]CHECK[NOTFORREPLICATION](logical_expression)例8-25創建一個訂貨表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創建transporters表并定義檢查約束8.4使用約束實施數據完整性8.4.5默認約束默認(Default)約束通過定義列的默認值或使用數據庫的默認值對象綁定表的列，以確保在沒有為某列指定數據時，來指定列的值。默認值可以是常量，也可以是表達式，還可以為NULL值。8.4.5默認約束定義默認約束的語法格式[CONSTRAINTconstraint_name]DEFAULTconstant_expression[FORcolumn_name]例8-27在Sales數據庫中，為員工表employee的sex列添加默認約束，默認值是“男”。ALTERTABLEemployeeADDCONSTRAINTsex_defaultDEFAULT'男'FORsex例8-28更改表employee，為hire_date列定義默認約束。ALTERTABLEemployeeADDCONSTRAINThire_date_dfDEFAULT(getdate())FORhire_date8.4.5默認約束例8-31為表purchase_orders定義多個約束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))本章小結(1)數據完整性有4種類型：實體完整性、域完整性、參照完整性和用戶定義的完整性。在SQLServer2000中可以通過各種約束、默認、規則和觸發器等數據庫對象來保證數據的完整性。(2)規則實施數據的完整性：規則就是數據庫中對存儲在表的列或用戶定義數據類型中的值的規定和限制。可以通過企業管理器和Transact-SQL語句來創建、刪除、查看規則以及規則的綁定與松綁。(3)默認值實施數據完整性：默認值是用戶輸入記錄時沒有指定具體數據的列中自動插入的數據。默認值對象可以用于多個列或用戶定義數據類型，它的管理與應用同規則有許多相似之處。表的一列或一個用戶定義數據類型也只能與一個默認值相綁定。在SQLServer中使用企業管理器和Transact-SQL語句實現默認值的創建、查看、刪除以及默認值的綁定與松綁。(4)使用約束實施數據完整性：約束是SQLServer提供的自動保持數據庫完整性的一種方法，定義了可輸入表或表的單個列中的數據的限制條件。在SQLServer中有6種約束：非空值約束、主鍵約束、外鍵約束、惟一性約束、檢查約束和默認約束。第9章Transact-SQL程序設計

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

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

例9-13round函數的使用。9.2.1常用函數3．日期函數日期函數用來操作datetime和smalldatetime類型的數據，執行算術運算。與其他函數一樣，可以在SELECT語句和WHERE子句以及表達式中使用日期函數。9.2.1常用函數表9-9SQLServer的日期函數函數名稱及格式描述Getdate()返回當前系統的日期和時間Datename(datepart,date_expr)以字符串形式返回date_expr中的指定部分，如果合適的話還將其轉換為名稱(如June)Datepart(datepart,date_expr)以整數形式返回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常用函數表9-10SQLServer的日期部分日期部分寫法取值范圍Yearyy1753~9999Quarterqq1~4Monthmm1~12Dayofyeardy1~366Daydd1~31Weekwk1~54Weekdaydw1~7(Mon~Sun)Hourhh0~23Minutemi0~59Secondss0~59Millisecondms0~9999.2.1常用函數例9-14使用datediff函數來確定貨物是否按時送給客戶。

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