预备篇

Size: px

Start display at page:

Download "预备篇"

陌项
4 years ago
Views:

1 第 8 章实现数据完整性数据库中的数据是从外界输入, 这就有不可预料的因素, 会发生输入无效或者是错误的信息为了保证输入数据的符合要求,SQL Server 2005 数据完整性很好地实现了这方面需求本章主要给读者讲解 SQL Server 2005 中实现数据完整性的方法, 希望读者通过本章的学习, 了解数据完整性的定义, 掌握实现数据完整性的各种方法, 譬如使用约束使用规则使用默认值和自定义函数等等 8.1 数据完整性概述数据完整性 (Data Integrity) 是指数据的精确性 (Accuracy) 和可靠性 (Reliability) 它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的数据完整性分为四类 : 实体完整性 Entity Integrity) 域完整性 Domain Integrity) 参照完整性 Referential Integrity) 用户定义的完整性 User-definedIntegrity) 实体完整性 : 实体完整性将行定义为特定表的唯一实体实体完整性通过索引 UNIQUE 约束 PRIMARY KEY 约束或 IDENTITY 属性强制表的标识符列或主键的完整性域完整性 : 域完整性指特定列的项的有效性用户可以强制域完整性限制类型通过使用数据类型 ) 限制格式通过使用 CHECK 约束和规则 ) 或限制可能值的范围 ( 通过使用 FOREIGN KEY 约束 CHECK 约束 DEFAULT 定义 NOT NULL 定义和规则 ) 参照完整性 : 在输入或删除记录时, 引用完整性保持表之间已定义的关系在 SQL Server 2005 中, 引用完整性通过 FOREIGN KEY 和 CHECK 约束, 以外键与主键之间或外键与唯一键之间的关系为基础引用完整性确保键值在所有表中一致这类一致性要求不引用不存在的值, 如果一个键值发生更改, 则整个数据库中, 对该键值的所有引用要进行一致的更改用户定义完整性 : 用户定义完整性使用户可以定义不属于其他任何完整性类别的特定业务规则所有完整性类别都支持用户定义完整性这包括 CREATE TABLE 中所有列级约束和表级约束存储过程以及触发器 8.2 使用约束实现数据完整性通过约束可以定义 SQL Server 2005 Database Engine 自动强制实施数据库完整性的方式约束定义关于列中允许值的规则, 是强制实施完整性的标准机制 SQL Server 2005 支持下列约束类 : NOT NULL 指定列不接受 NULL 值

2 2 CHECK 约束通过限制可放入列中的值来强制实现域完整性 CHECK 约束指定应用于为列输入的所有值的布尔值 ( 计算结果为 TRUE FALSE 或未知 ) 搜索条件所有计算结果为 FALSE 的值均被拒绝可以为每列指定多个 CHECK 约束 UNIQUE 约束强制实现列值的唯一性根据 UNIQUE 约束, 表中的任何两行都不能有相同的列值另外, 主键也强制实施唯一性, 但主键不允许 NULL 作为一个唯一值 PRIMARY KEY 约束标识具有唯一标识表中行的值的列或列集在一个表中, 不能有两行具有相同的主键值不能为主键中的任何列输入 NULL 值建议使用一个小的整数列作为主键每个表都应有一个主键限定为主键值的列或列组合称为候选键 FOREIGN KEY 约束标识并强制实施表之间的关系一个表的外键指向另一个表的候选键如果一个外键值没有候选键, 则不能向行中插入该值 (NULL 除外 ) 这些约束的创建详细见第 3 章的 3.11 节, 这里不作重复讲解了 8.3 使用规则规则是一个向后兼容的功能, 用于执行一些与 CHECK 约束相同的功能使用 CHECK 约束是限制列值的首选标准方法 CHECK 约束还比规则更简明一个列只能应用一个规则, 但可以应用多个 CHECK 约束 CHECK 约束被指定为 CREATE TABLE 语句的一部分, 而规则是作为单独的对象创建, 然后绑定到列上 T-SQL 语言提供 create rule 语句来创建规则, 其语法如下 : CREATE RULE [ schema_name. ] rule_name AS condition_expression [ ; ] 参数说明 : schema_name: 规则所属架构的名称 rule_name: 新规则的名称 condition_expression: 定义规则的条件规则可以是 WHERE 子句中任何有效的表达式, 并且可以包括诸如算术运算符关系运算符和谓词 ( 如 IN LIKE BETWEEN) 这样的元素规则不能引用列或其他数据库对象可以包括不引用数据库对象的内置函数不能使用用户定义函数 condition_expression 包括一个变量每个局部变量的前面都有一个 at 符号 (@) 该表达式引用通过 UPDATE 或 INSERT 语句输入的值在创建规则时, 可以使用任何名称或符号表示值, 但第一个字符必须是 at 符号 (@) 例 : 创建一个规则, 其值在 0 至 100 之间, 其代码如下 : Create rule id_chk between 0 and 100 SQL Server 2005 提供系统存储过程 sp_bindrule, 将规则绑定到列或列名数据类型, 其语法如下 : sp_bindrule = ] 'rule',

3 3 = ] 'object_name' [, = ] 'futureonly_flag' ] 参数说明 : =] 'rule': 由 CREATE RULE 语句创建的规则名称 rule 的数据类型为 nvarchar(776), 无默认值 = ] 'object_name': 要绑定规则的表和列或别名数据类型不能将规则绑定到 text ntext image varchar(max) nvarchar(max) varbinary(max) xml CLR 用户定义类型或 timestamp 列无法将规则绑定到计算列 = ] 'futureonly_flag': 只有将规则绑定到别名数据类型时才使用 future_only_flag 的数据类型为 varchar(15), 默认值为 NULL 如果绑定成功, 返回 0; 失败, 返回 -1. 例 : 前面创建的规则 id_chk 绑定到班级表的 class_id 列上, 其代码为 : sp_bindrule id_chk, 't_class.class_id' 8.4 使用默认值记录中的每列均必须有值, 即使该值是 NULL 可能会有这种情况: 必须向表中加载一行数据但不知道某一列的值, 或该值尚不存在如果列允许空值, 就可以为行加载空值由于可能不希望有可为空的列, 因此最好是为列定义 DEFAULT 定义 ( 如果合适 ) 例如, 通常为数值列指定零作为默认值, 为字符串列指定 N/A 作为默认值将某行加载到某列具有 DEFAULT 定义的表中时, 即隐式指示 SQL Server 2005 Database Engine 将默认值插入到没有指定值的列中如果列不允许空值且没有 DEFAULT 定义, 就必须为该列显式指定值, 否则数据库引擎会返回错误, 指出该列不允许空值在创建表或者是修改表时可以为列指定默任值, 这里详细语法跟创建表和修改表类似, 这里不介绍了例 : 学员信息表的学员性别默认为男, 其语法如下 : alter table t_student ADD CONSTRAINT col_s_sex default '1' for s_sex T-SQL 语言删除约束是使用 DROP DEFAULT 语句, 其语法如下 : DROP DEFAULT { [ schema_name. ] default_name } [,...n ] [ ; ] 参数说明 : schema_name: 默认值所属架构的名称 default_name: 现有默认值的名称例 : 删除约束 col_s_sex 约束的代码为 : DROP DEFUALT col_s_sex 8.5 使用 IDENTITY 列使用 IDENTITY 属性可以实现标识符列这使得开发人员可以为表中所插入的第一行指定一个标识号 (Identity Seed 属性 ), 并确定要添加到种子上的增量 (Identity Increment 属性 ) 以确定后面的标识号将值插入到有标识符列的表中之后,SQL Server 2005 Database Engine 会通过向种子添加增量来自动生成下一个标识值

4 4 在用 IDENTITY 属性定义标识符列时, 请注意下列几点 : 一个表只能有一个使用 IDENTITY 属性定义的列, 且必须通过使用 decimal int numeric smallint bigint 或 tinyint 数据类型来定义该列可指定种子和增量二者的默认值均为 1 标识符列不能允许为空值, 也不能包含 DEFAULT 定义或对象在设置 IDENTITY 属性后, 可以使用 $IDENTITY 关键字在选择列表中引用该列, 还可以通过名称引用该列 OBJECTPROPERTY 函数可用于确定一个表是否具有 IDENTITY 列, COLUMNPROPERTY 函数可用于确定 IDENTITY 列的名称通过使值能够显式插入,SET IDENTITY_INSERT 可用于禁用列的 IDENTITY 属性使用 SSMS 创建 identity 列在节有介绍, 这里给读者讲解在 T-SQL 语言中创建 identity 列, 其语法如下 : IDENTITY [ (seed,increment) ] 参数说明 : Seed: 向表中加载第一行时所使用的值 Increment: 加到所加载上一行的标识值上的增量值例 : 教务管理系统 teach 数据库, 为表 t_ stu_fail 列 fail_id 设置为 identity 属性, 其代码如下 : ALTER TABLE t_stu_fail ALTER COLUMN fail_id identity(1,1) 8.6 用户自定义函数用户定义函数是对系统函数的补充与其他编程语言中的函数一样, 用户定义函数可以接受参数执行操作并将操作结果以值的形式返回用户定义函数也可以使用在表达式查询或存储过程中用户定义函数优点在 SQL Server 中使用用户定义函数有以下优点 : 允许模块化程序设计只需创建一次函数并将其存储在数据库中, 以后便可以在程序中调用任意次用户定义函数可以独立于程序源代码进行修改执行速度更快与存储过程相似,Transact-SQL 用户定义函数通过缓存计划并在重复执行时重用它来降低 Transact-SQL 代码的编译开销这意味着每次使用用户定义函数时均无需重新解析和重新优化, 从而缩短了执行时间和用于计算任务字符串操作和业务逻辑的 Transact-SQL 函数相比,CLR 函数具有显著的性能优势减少网络流量基于某种无法用单一标量的表达式表示的复杂约束来过滤数据的操作, 可以表示为函数然后, 此函数便可以在 WHERE 子句中调用, 以减少发送至客户端的数字或行数

5 用户定义函数类型根据用户定义函数返回值可以将用户定义函数分为标量值函数和表值函数两个类型 : 标量值函数 : 标量值函数可以一个在 return 子句里定义的确定类型的标量值返回值的类型可以是除了 text ntext image cursor timestamp 和 table 类型之外的所有其他类型在多语句标量函数里, 函数体语句定义在 begin 和 end 语句之内, 其中包含了可以返回值的 T-SQL 语句表值函数 : 表值函数返回的是 table 数据类型表值函数又可以分为内联表值函数和多语句表值函数两种内联表值函数返回值是一个表, 在内联表值函数里没有由 begin-end 语句括起来的函数体, 的表是由一个位于 return 子句中的 select 语句从数据库里撮的数据内联表值函数看起来有一个具有参数的视图多语句表值函数的返回值也一个表, 与标量值函数一样, 在多语句表值函数里也有一个用 begin-end 语句包括起来的函数体, 在函数体里可以包含多个 T-SQL 语句, 通过这些 T-SQL 语句生成返回的表中的数据由于多语句表值函数里可以包含多个 T-SQL 语句, 所以在多语句表值函数里可以对数据进行多次查询合并等操作, 是内联表值函数的补充创建标量值函数创建标量值函数的语法如下 : CREATE FUNCTION [ schema_name. ] function_name ( [ [ AS ][ type_schema_name. ] parameter_data_type [ = default ] } [,...n ] ] ) RETURNS return_data_type [ WITH <function_option> [,...n ] ] [ AS ] BEGIN function_body RETURN scalar_expression END [ ; ] 参数说明 : schema_name: 用户定义函数所属的架构的名称 function_name: 用户定义函数的参数可声明一个或多个参数函数最多可以有 1,024 个参数 [ type_schema_name. ] parameter_data_type: 参数的数据类型及其所属的架构, 后者为可选项

6 Default: 参数的默认值 return_data_type: 标量用户定义函数的返回值 function_body: 指定一系列定义函数值的 Transact-SQL 语句 scalar_expression: 指定标量函数返回的标量值例 : 创建一个标量函数, 返回当前学员的总人数, 其代码如下 : create function stu_total_no() returns

6 6 Default: 参数的默认值 return_data_type: 标量用户定义函数的返回值 function_body: 指定一系列定义函数值的 Transact-SQL 语句 scalar_expression: 指定标量函数返回的标量值例 : 创建一个标量函数, 返回当前学员的总人数, 其代码如下 : create function stu_total_no() returns int as begin int from t_student end print ' 学员总人数是 :'+cast(dbo.stu_total_no() as varchar) 运行结果如图 8-1 所示图 8-1 函数 stu_total_no 运行结果例 : 教务管理系统, 创建一个标量函数, 计算出班级的人数, 班级名称通过参数其代码如下 : create function stu_class_no(@class_name varchar(10)) returns int as begin int from t_student where class_id in(select class_id from t_class where c_name=@class_name) end print 'JN0704 班级学员人数是 :'+cast(dbo.stu_class_no('jn0704') as varchar) 其运行结果如图 8-2 所示

$7 图 8-2 标量函数 stu_class_no 运行结果 8.6.4 创建多语句表值函数创建多语句表值函数的语法如下 : CREATE FUNCTION [ schema_name. ] function_name ( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] } [,.$

7 7 图 8-2 标量函数 stu_class_no 运行结果创建多语句表值函数创建多语句表值函数的语法如下 : CREATE FUNCTION [ schema_name. ] function_name ( [ [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] } [,...n ] ] ) TABLE < table_type_definition > [ WITH <function_option> [,...n ] ] [ AS ] BEGIN function_body RETURN END [ ; ] 其参数大部分跟创建标量函数的参数类似, 下面介绍不同的参数说明 : RETURNS: 指定返回值, 为返回值的名称, 多语句表值函数的返回值必须要存放在返回值变量中 ;table 表示返回的是表值 ; <table_type_definition> 是用来表明表的字段及类型 RETRUN: 多语句表值函数同样是用 RETURN 来返回数据, 只是 RETURN 后不需要接任何数据或变量例 : 教务管理系统, 罗列出所有留级 / 休学 / 退学人的学员信息, 包括学号性别出生年月日身份证号, 现在要求创建一个用户定义函数, 用于列出所有人的名单其代码如下 : create function stu_info() table (id int identity(1,1) not null, s_no varchar(10) not null, s_sex varchar(1) null, s_age varchar(18) null, s_cardid varchar(20) null) begin

8 insert @stu select s_no,s_sex,s_age,s_identitycard from t_student where s_id in(select s_id from t_stu_fail) insert @stu select s_no,s_sex,s_age,s_identitycard from t_student where s_id in(select

8 8 select s_no,s_sex,s_age,s_identitycard from t_student where s_id in(select s_id from t_stu_fail) select s_no,s_sex,s_age,s_identitycard from t_student where s_id in(select s_id from t_stu_leave) select s_no,s_sex,s_age,s_identitycard from t_student where s_id in(select s_id from t_stu_rest) return end 运行结果如图 8-3 所示图 8-3 stu_info 函数运行结果创建内联表值函数创建内联表值函数的语法如下 : CREATE FUNCTION [ schema_name. ] function_name ( [ [ AS ] [ type_schema_name. ] parameter_data_type [ = default ] } [,...n ] ] ) RETURNS TABLE [ WITH <function_option> [,...n ] ] [ AS ]

9 RETURN [ ( ] select_stmt [ ) ] [ ; ] 其参数大部分跟创建标量函数的参数类似, 下面介绍不同的参数说明 : RETURNS TABLE: 此处返回值类型固定为 table 型 RETURN[(]select_stmt[)]: 此处固定为一个 select 语句例 : 创建班级的学员的学号性别年龄出生年月日信息, 其代码如下 : create

9 9 RETURN [ ( ] select_stmt [ ) ] [ ; ] 其参数大部分跟创建标量函数的参数类似, 下面介绍不同的参数说明 : RETURNS TABLE: 此处返回值类型固定为 table 型 RETURN[(]select_stmt[)]: 此处固定为一个 select 语句例 : 创建班级的学员的学号性别年龄出生年月日信息, 其代码如下 : create function class_stu_info(@class_name varchar(10)) returns table return select s_no,s_sex,s_age,s_identitycard from t_student where class_id in(select class_id from t_class where c_name=@class_name) go select * from class_stu_info('jn0704') 运行结果如图 8-4 所示图 8-4 函数 class_stu_info 运行结果在 SSMS 中创建用户定义函数在 SSMS 中创建用户自定义函数的方法如下 : (1) 启动 SSMS, 在对象资源管理器对话框中, 展开树形目录, 定位到数据库实例数据库 teach 可编程性函数, 在里面有四个选项表值函数标量值函数聚合函数系统函数这里选择标量值函数右键快捷菜单的新建标量函数选项, 如图 8-5 所示

然后输入模板里参数的值, 再编辑未完成的代码 (4) 单击执行按钮完成操作 8.6.

10 10 图 8-5 对象资源管理器对话框 (2) 打开创建标量函数的模板, 如图 8-6 所示图 8-6 创建标量函数模板 (3) 直接修改里面的代码, 或者是选择菜单栏查询指定模板参数的值选项, 然后输入模板里参数的值, 再编辑未完成的代码 (4) 单击执行按钮完成操作查看和修改用户定义函数在 SSMS 里的对象资源管理器对话框中, 可查看与修改已经创建完毕的用户定义函数, 其步骤为 :

11 11 (1) 启动 SSMS, 在对象资源管理器对话框中, 展开树形目录, 定位到数据库实例数据库 teach 可编程性函数, 在里面有四个选项表值函数标量值函数聚合函数系统函数展开标量值函数表值函数等, 可以看到前面创建的例子, 如图 8-7 所示图 8-7 对象资源管理器对话框 (2) 如果要修改某个用户定义函数, 选择该用户定义函数右键快捷菜单的修改选项, 然后打开修改用户定义函数的查询编辑器对话枉, 在该对话框里可以查看用户定义函数的内容, 也可以修改其内容使用 T-SQL 语言也可以修改用户定义函数, 其语句为 alter function, 其语法跟 create function 类似, 只是把 create 修改为 alter, 这里不详细讲解了删除用户定义函数当用户定义函数不再需要使用时, 可以将其删除, 下面分别通过 SSMS 和 T-SQL 语言介绍如何删除用户定义函数 1. 在 SSMS 中删除用户定义函数在 SSMS 中删除用户定义函数的步骤如下 : (1) 启动 SSMS, 在对象资源管理器对话框中, 展开树形目录, 定位到数据库实例数据库 teach 可编程性函数, 在里面有四个选项表值函数标量值函数聚合函数系统函数展开标量值函数表值函数等, 可以看到前面创建的例子, 选择要删除的函数右键快捷菜单的删除选项, 如图 8-8 所示

$语句来删除用户定义函数, 其语法为 : DROP FUNCTION { [ schema_name.$

12 12 图 8-8 对象资源管理器对话框 (2) 打开删除对象对话框, 如图 8-9 所示图 8-9 删除对象对话框 (3) 单击确定按钮, 完成用户定义函数的操作 2. 使用 T-SQL 语言删除用户定义函数 T-SQL 语言里使用 DROP FUNCTION 语句来删除用户定义函数, 其语法为 : DROP FUNCTION { [ schema_name. ] function_name } [,...n ]

13 13 参数说明 : schema_name: 用户定义函数所属的架构的名称 function_name: 要删除的用户定义函数的名称可以选择是否指定架构名称不能指定服务器名称和数据库名称例 : 删除用户定义函数 stu_class_no, 其代码为 : Drop function stu_class_no 8.7 上机实践教务管理系统, 罗列出某班级留级 / 休学 / 退学人的学员信息, 包括学号性别出生年月日身份证号, 现在要求创建一个用户定义函数, 用于列出所有人的名单其代码如下 : create function class_stu_ 休转退 (@class_name varchar(10)) table (id int identity(1,1) not null, s_no varchar(10) not null, s_sex varchar(1) null, s_age varchar(18) null, s_cardid varchar(20) null) begin select s_no,s_sex,s_age,s_identitycard from t_student where s_id in(select s_id from t_stu_fail) and class_id in(select class_id from t_class where c_name=@class_name) select s_no,s_sex,s_age,s_identitycard from t_student where s_id in(select s_id from t_stu_leave) and class_id in(select class_id from t_class where c_name=@class_name) select s_no,s_sex,s_age,s_identitycard from t_student where s_id in(select s_id from t_stu_rest) and class_id in(select class_id from t_class where c_name=@class_name) return end select * from class_stu_ 休转退 ('JN0704') 运行结果如图 8-10 所示

14 14 图 8-10 class_stu_ 休转退运行结果

项目 3 创建和管理表任务实现 Office Visio PK 3 FK FK1 3.1 相关知识 SQL Server 一制订表规划 1. 表要存储什么对象 2. 表中每一列的数据类型和长度 059

项目 3 创建和管理表任务实现 Office Visio PK 3 FK FK1 3.1 相关知识 SQL Server 一制订表规划 1. 表要存储什么对象 2. 表中每一列的数据类型和长度 059 项目 3 创建和管理表表是数据库中最重要的基础对象它包含数据库中的所有数据其他数据库对象例如索引和视图等都是依赖于表而存在的若要使用数据库来存储和组织数据首先就需要创建表在本项目中将通过 15 个任务来创建和管理表主要内容包括表的设计理解 SQL Server 2008 中的数据类型以及创建和修改表等任务 1 表的设计任务描述 Office Visio 3.1 3.1 项目