数据库第6次课-数据完整性和TSQL语言

第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