预备篇

Transcription

1 第 3 章表 表是数据的集合, 是用来存储数据和操作数据的逻辑结构 表是由行和列组成的, 其中行也称为记录, 其记录是组织数据的单位 ; 而列也称为字段, 其每一列表示记录的一个属性 在同一个表中列的名字不能相同 本章主要给读者讲解表中的各种元素的属性以及表操作的基本方法, 通过本章的学习, 希望读者深刻理解表的定义, 表中数据的类型以及列的属性, 掌握创建主键, 创建表的方法 3.1 什么是表 数据表是数据库中一个非常重要的对象, 是其他对象的基础 没有数据表, 关键字 主键 索引等也就无从谈起 表是数据的集合, 是用来存储数据和操作数据的逻辑结构 表是由行和列组成的, 其中行也称为记录, 其记录是组织数据的单位 ; 而列也称为字段, 其每一列表示记录的一个属性 在同一个表中列的名字不能相同 如图 3-1 所示, 是教务管理系统中的学生表, 每一行包含了一个学生的信息, 一个列代表了一个信息, 比如学号, 班级 ID, 学生姓名等 图 3-1 学生表示例在 SQL Server 2005 中, 根据表的用途可以分为四类, 即系统表 用户表 已分区表和临时表 系统表 : 系统表是 SQL Server 安装后就有的表, 是特殊的表, 存储了服务器的配置信息 数据表的定义信息等 这些表是只读的, 用户不能进行修改

2 ASP.NET 简明教程 2 用户表 : 是由用户自主创建和维护的, 满足客户需求而产生的表 已分区表 : 已分区表是将数据水平划分为多个单元的表, 这些单元可以分布到数据库的多个文件组中 在维护整个集合的完整性时, 使用分区可以快速而有效地访问或管理数据子集, 从而使大型表或索引更易于管理 如果表非常的大或者是有可能变得非常的大, 可以考虑使用已分区表 临时表 : 临时表是由于系统或用户运算的临时需要而创建的表, 临时表, 顾名思义, 是临时的意思, 即使用完之后就删除 在 SQL Server2005 中, 临时表分为本地临时表和全局临时表 3.2 数据类型 创建表时对表中的每一列都要定义数据类型, 其数据类型用来表现数据的特征 所以 SQL Server 中的数据类型对表来说是很重要的, 在给表添加记录之前必须知道数 据类型 SQL Server 数据类型 在 SQL Server 2005 中, 每个列 局部变量 表达式和参数都具有一个相关的数 据类型 数据类型是一种属性, 用于指定对象可保存数据的类型 : 整数数据 字符数 据 货币数据 日期和时间数据 二进制字符串等 SQL Server 2005 中的数据类型归纳为 : 精确数字 近似数字 日期和时间 字符 串 Unicode 字符串 二进制字符串 其他数据类型 下面分别对这些数据类型作详 细的介绍 1. 精确数字型 精确数字型包括 bigint int smallint tinyint bit decimal numeric money smallmoney 九种, 其数据范围如表 3-1 所示 表 3-1 精确数字型 数据类型 范围 存储 Bigint -2^63 (-9,223,372,036,854,775,808) 到 2^63-1 (9,223,372,036,854,775,807) 8 字节 Int -2^31 (-2,147,483,648) 到 2^ 字节 (2,147,483,647) Smallint -2^15 (-32,768) 到 2^15-1 (32,767) 2 字节 tinyint 0 到 字节 decimal - 10^38 +1 到 10^38-1 存储长度与精度有关 : 1-9 位时 :5 字节 位时 :9 字节 位时 :13 字节 位时 :17 字节 numeric - 10^38 +1 到 10^38-1 存储长度与精度有关 :

3 3 1-9 位时 :5 字节 位时 :9 字节 位时 :13 字节 位时 :17 字节 money 922,337,203,685, 到 8 字节 922,337,203,685, smallmoney -214, 到 214, 字节 bit 0 1 或 null 如果表中的列为 8 bit 或更少, 则这些列作为 1 个字节存储 如果列为 9 到 16 bit, 则这些列作为 2 个字节存储, 以此类推 备注 : 不同的数据类型使用的场合和规则不一样, 下面列举了这些精确数字型数 据类型的一些注意事项 int 数据类型是 SQL Server 2005 中的主要整数数据类型 bigint 数据类型用于整 数值可能超过 int 数据类型支持范围的情况 在数据类型优先次序表中,bigint 介于 smallmoney 和 int 之间 只有当参数表达式为 bigint 数据类型时, 函数才返回 bigint SQL Server 不会自 动将其他整数数据类型 (tinyint smallint 和 int) 提升为 bigint 事实上,decimal 和 numeric 数据类型是一样的 money 和 smallmoney 数据类型精确到它们所代表的货币单位的万分之一 字符串值 TRUE 和 FALSE 可以转换为以下 bit 值 :TRUE 转换为 1,FALSE 转换 为 0 2. 近似数字型 用于表示浮点数值数据的大致数值数据类型 浮点数据为近似值 ; 因此, 并非数 据类型范围内的所有值都能精确地表示, 它有 real 和 float 两种数据类型, 其数据范围 如表 3-2 所示 表 3-2 近似数字型 数据类型 范围 存储 float -1.79E 至 -2.23E 以及 2.23E 至 1.79E real -3.40E + 38 至 -1.18E 以及 1.18E - 38 至 3.40E + 38 取决于 n 的值 4 字节 3. 日期和时间型该类型用于表示某天的日期和时间的数据类型, 其包含 datetime 和 smalldatetime 两种, 其数据范围如表 3-3 所示 表 3-3 日期和时间型

4 ASP.NET 简明教程 4 数据类型 范围 精确度 datetime 1753 年 1 月 1 日到 9999 年 12 月 31 日 3.33 毫秒 smalldatetime 1900 年 1 月 1 日到 2079 年 6 月 6 日 1 分钟 4. 字符串型 该数据类型用于存储字符型数据, 它有 char varchar 和 text 三种, 其数据范围如 表 3-4 所示 表 3-4 字符串型数据类型 数据类型 范围 存储长度 char 1 至 8,000 个字符 1 个字符占 1 字节, 为固定长度, 如果插入的数据不够此长度, 系统会自动补上空格 varchar 语法 :varchar [ ( n max ) ] n 的取值范围为 1 至 8,000 个字符 max 指示最大存储大小是 2^31-1 个字符 参数为 n: 一个字符占 1 个字节, 共 n 个字节, 参数 max: 输入数据的实际长度加 2 个字节 text 1 至 2^31-1 个字符 1 个字符占 1 字节, 存储多少个字符即占多少空间, 最大可存储 2GB 空间 5. Unicode 字符串型 该类型与字符串数据类型相似, 由于 Unicode 是双字节字符编码标准, 所以在 Unicode 字符串中, 一个字符是用 2 个字节来存储的 此类型有 nchar nvarchar 和 ntext 三种 其数据范围如表 3-5 所示 表 3-5 Unicode 字符串型数据类型 数据类型 范围 存储长度 char 1 至 4000 个字符 1 个字符占 2 字节, 为固定长度, 如果插入的数据不够此长度, 系统会自动补上空格 varchar 语法 :varchar [ ( n max ) ] n 的取值范围为 1 至 4,000 个字符 max 指示最大存储大小是 2^31-1 个字符 参数 n: 一个字符占 2 个字节, 共个字节, 参数 max: 输入数据的实际长度加 2 个字节 text 1 至 2^31-1 个字符 1 个字符占 2 字节, 存储多少个字符即占多少空间, 最大可存储 2GB 空间 6. 二进制字符串型 二进制字符串型用来存储二进制数据, 它包括 binary varbinary 和 image 三种 其数据类型范围如表 3-6 所示 表 3-6 二进制字符串型数据类型

5 5 数据类型范围存储长度 binary 1 至 8000 个字节为固定长度, 如果插入的数据不够此长度, 系统会自动补上 0X00 varbinary 语法 :varbinary [ ( n max ) ] n 的取值范围为 1 参数 n: 可变长度, 输入数据至 8,000 个字节 max 指示最大存储大小是的实际长度, 参数 max: 输入 2^31-1 个字符数据的实际长度加 2 个字节 image 1 至 2^31-1 个字节可变长度, 输入数据的实际长度 7. 其他数据类型其他数据类型还包括 cursor sql_variant table timestamp uniqueidentifier 和 xml 六种 Cursor 数据类型是变量或存储过程 OUTPUT 参数的一种数据类型, 这些参数包含对游标的引用 ;sql_variant 数据类型用于存储 SQL Server 2005 支持的各种数据类型 ( 不包括 text ntext image timestamp 和 sql_variant) 的值 ;table 数据类型用于存储结果集以进行后续处理 ;timestamp 数据类型用作给表行加版本戳的机制, 存储大小为 8 个字节 BLOB 是非常巨大的不定的二进制或者字符型数据, 通常是文档 (.txt.doc) 和图片 (.jpeg.gif.bmp), 它可以存储在数据库中 在 SQL Server 中,BLOB 可以是 text ntext 或者 image 数据类型 Image 数据类型存储的是长度不确定的二进制数据, 最大长度是 2GB Microsoft SQL Server 2005 将超过 8,000 个字符的字符串和大于 8,000 字节的二进制数据存储为名为 text 和 image 的特殊数据类型 超过 4,000 个字符的 Unicode 字符串存储为 ntext 数据类型 8. 数据类型转换当要对不同类型的数据进行运算时, 就必须将其转换成相同的数据类型才能进行运算 转换类型的方法有 : 当一个对象的数据移到另一个对象, 或两个对象之间的数据进行比较或组合时, 数据可能需要从一个对象的数据类型转换为另一个对象的数据类型 将 Transact-SQL 结果列 返回代码或输出参数中的数据移到某个程序变量中时, 必须将这些数据从 SQL Server 2005 系统数据类型转换成该变量的数据类型 SQL Server 2005 提供了二个函数进行数据类型的转换, 分别是 cast 和 convert (1) 使用 CAST 转换数据类型 CAST 的语法如下 : CAST ( expression AS data_type [ (length ) ]) 参数说明 : Expression: 任何有效的表达式 Data_type: 要转换的数据类型 Length: 数据类型的长度, 是可选参数 一般只有 nchar nvarchar char varchar binary 和 varbinary 这几种数据类型才能使用

6 ASP.NET 简明教程 6 例 : 查询出每个学员的学号和学费, 其打印格式是 学号 :XXXXXX 的学费是 : XXX, 如不使用 cast 语句, 其代码为 : select ' 学号 :'+s_no+' 的学费是 :'+s_fee from t_student 运行结果如图 3-2 所示 图 3-2 不使用 cast 语句执行结果使用 cast 转换函数的代码为 : select ' 学号 :'+s_no+' 的学费是 :'+cast(s_fee as varchar(10)) from t_student 其执行结果如图 3-3 所示 图 3-3 使用 cast 的执行结果由于 s_fee 字段是数字型, 在和字符型数据做连接运算时, 必须将其转换成字符型数据 Cast(s_fee as varchar(10)) 的作用是将 s_fee 字段内容转换成 varchar(10) 的数据类型 (2) 使用 CONVERT 转换数据类型 CONVERT 的语法为 : CONVERT ( data_type [ ( length ) ], expression [, style ] ) 参数说明 : Data_type: 要转换的数据类型 Length: 数据类型的长度 Expression: 任何有效的表达式 Style: 一般用于将 datatime 或 smalldatetime 数据转换为字符数据 (nchar nvarchar char varchar 数据类型 ) 的日期格式的样式 ; 或者是用于将 float real money 或 smallmoney 数据转换为字符数据 (nchar nvarchar char varchar

7 7 数据类型 ) 的字符串格式的样式 如 style 为 null, 则返回的结果也是为 null 例 : 查看班级的班级名称 开班日期 预计毕业时间 毕业时间, 其代码为 : select c_name, convert(varchar(20),c_startdate,1), convert(varchar(20),c_enddate,102), convert(varchar(20),c_finish,110) from t_class 运行结果如图 3-4 所示 图 3-4 使用 convert 转换数据类型如图 3-4 可以看出, 不同的样式, 其显示的格式也不同, 表 3-7 列出日期时间代码与输出样式表 3-7 日期时间 style 代码说明 不带世纪数位 (yy) (1) 带世纪数位 (yyyy) 标准输入 / 输出 (3) - 0 或 100 (1, 2) 默认设置 mon dd yyyy hh:miam ( 或 PM) 美国 mm/dd/yyyy ANSI yy.mm.dd 英国 / 法国 dd/mm/yy 德国 dd.mm.yy 意大利 dd-mm-yy (1) - dd mon yy (1) - mon dd, yy hh:mm:ss - 9 或 109 (1, 2) 默认设置 + 毫秒 Mon dd yyyy hh:mi:ss:mmmam( 或 PM) 美国 mm-dd-yy 日本 yy/mm/dd ISO yymmdd - 13 或 113 (1, 2) 欧洲默认设置 + 毫秒 dd mon yyyy hh:mm:ss:mmm(24h)

8 ASP.NET 简明教程 hh:mi:ss:mmm(24h) - 20 或 120 (2) ODBC 规范 yyyy-mm-dd hh:mi:ss(24h) - 21 或 121 (2) ODBC 规范 ( 带毫秒 ) (4) ISO8601 带时区 Z 的 127(6) ISO8601 yyyy-mm-dd hh:mi:ss.mmm(24h) yyyy-mm-ddthh:mm:ss.mmm ( 无空格 ) yyyy-mm-ddthh:mm:ss.mmmz ( 无空格 ) (1, 2) 回历 (5) dd mon yyyy hh:mi:ss:mmmam (2) 回历 (5) dd/mm/yy hh:mi:ss:mmmam float 或 real 转换为字符数据的 style 值, 如表 3-8 所示 表 3-8 float 或 real 转换为字符数据 style 值 值 输出 0( 默认 ) 最多包含 6 位 根据需要使用科学记数法 1 始终为 8 位值 始终使用科学记数法 2 始终为 16 位值 始终使用科学记数法 money 或 mallmoney 转换为字符数据的 style 值, 如表 3-9 所示 表 3-9 float 或 real 转换为字符数据 style 值 值 输出 0( 默认 ) 小数点左侧每三位数字之间不以逗号分隔, 小数点右侧取两位数, 例如 小数点左侧每三位数字之间以逗号分隔, 小数点右侧取两位数, 例如 3, 小数点左侧每三位数字之间不以逗号分隔, 小数点右侧取四位数, 例如 将字符串输入转换为 xml 数据的 style 值, 如表 3-10 所示 表 3-10 转换为 xml 数据的 style 值 值 输出 0( 默认 ) 使用默认的分析行为, 即放弃无用的空格, 且不允许使用内部 DTD 子集 1 保留无用空格 此样式设置将默认的 xml:space 处理方式设置为与指定了 xml:space="preserve" 的行为相同 2 启用有限的内部 DTD 集处理 3 保留无用空格, 并启用有限的内部 DTD 子集处理 9. 隐式数据类型转换 隐式转换指那些没有指定 CAST 或 CONVERT 函数的转换 显式转换指那些需要 指定 CAST 或 CONVERT 函数的转换 图 3-5 显示了可对 SQL Server 2005 系统提供的 数据类型执行的所有显式和隐式数据类型转换 其中包括 xml bigint 和 sql_variant

9 9 不存在对 sql_variant 数据类型的赋值进行的隐式转换, 但是存在转换为 sql_variant 的 隐式转换 用户自定义数据类用户定义的数据类型基于在 Microsoft SQL Server 中提供的数据类型 当几个表中必须存储同一种数据类型时, 并且为保证这些列有相同的数据类型 长度和可空性时, 可以使用用户定义的数据类型 例如, 可定义一种称为 postal_code 的数据类型, 它基于 Char 数据类型 当创建用户定义的数据类型时, 必须提供三个数 : 数据类型的名称 所基于的系统数据类型和数据类型的可空性 (1) 创建用户定义的数据类型创建用户定义的数据类型可以使用 Transact-SQL 语句 系统存储过程 sp_addtype 可以来创建用户定义的数据类型 其语法形式如下 : sp_addtype {type},[,system_data_bype][,'null_type'] 其中,type 是用户定义的数据类型的名称 system_data_type 是系统提供的数据类型, 例如 Decimal Int Char 等等 null_type 表示该数据类型是如何处理空值的, 必须使用单引号引起来, 例如 'NULL' 'NOT NULL' 或者 'NONULL' 例子创建一个用户定义的数据类型 ssn, 其基于的系统数据类型是变长为 11 的字符, 不允许空, 代码如下 : Use cust Exec sp_addtype ssn,'varchar(11)',"not Null' 例子 : 创建一个用户定义的数据类型 birthday, 其基于的系统数据类型是 DateTime, 允许空, 代码如下 : Use cust Exec sp_addtype birthday,datetime,'null' 例子 : 创建两个数据类型, 即 telephone 和 fax,telephone 类型基于系统数据类型 varchar, 长度为 24, 不允许为空 ;fax 类型基于系统数据类型 varchar, 长度为 24, 允许为空, 代码如下 : Use master Exec sp_addtype telephone,'varchar(24),'not Null' Eexc sp_addtype fax,'varchar(24)','null' (2) 删除用户定义的数据类型当用户定义的数据类型不需要时, 可删除 删除用户定义的数据类型的命令是 sp_droptype {'type'} 例子 : 删除上一小节创建的自定义数据类型 ssn, 代码如下 : Use master Exec sp_droptype 'ssn' 注意 : 当表中的列还正在使用用户定义的数据类型时, 或者在其上面还绑定有默认或者规则时, 这种用户定义的数据类型不能删除

10 ASP.NET 简明教程 在 SSMS 中创建表 下面以教务管理系统中的学员信息表为例, 说明如何在 SSMS(SQL Server Management Studio) 中创建表 学员信息表的结构如表 3-11 所示 表 3-11 学员信息表 字段名称 字段中文名 数据类型 是否为空 说明 S_id 学员流水号 Decimal(8,0) N 主键 Class_id 班级流水号 Decimal(8,0) N 外键 S_no 学号 Char(20) Y S_name 姓名 Char(30) Y S_pwd 密码 Char(20) Y S_sex 姓别 Char(4) Y S_age 年龄 Char(10) Y 默认为 男 S_identitycard 身份证号 Char(18) Y S_health 身体状况 Char(20) Y S_telphone 联系电话 Char(20) Y S_address 联系地址 Char(150) Y S_native 出生所在地 Char(50) Y S_ 移动电话 Char(30) Y S_school 毕业院校 Char(30) Y S_specility 专业 Char(4) Y S_degree 学历 Char(4) Y Is_computer 是否计算机 Char(1) Y 专业 S_computelevel 计算机水平 Char(4) Y S_languagelevel 英语水平 Char(4) Y S_interest 兴趣爱好 Char(100) Y Is_hadword 是否有工作 Char(1) Y 经验 S_workexperience 工作经历 Char(400) Y S_refer 资源师 Char(30) Y S_target 培训方向 Char(4) Y S_trainaim 培训目的 Char(100) Y S_workaim 工作目标 Char(100) Y S_workwill 工作意向 Char(100) Y S_begindate 入学时间 date Y S_idealgraduate 预计毕业时 date Y 间 S_tuition 缴费方式 Char(4) Y

11 11 S_graduate 实际毕业时 Char(10) Y 间 S_fee 学费 Numeric(8,0) Y Status 学员状态 Char(1) Y S_checkpoint 考核分 Numeric(8,2) Y S_actionpoint 行为分 Numeric(8,2) Y Is_inschool 是否在校生 Char(1) Y 创建表的步骤如下 : (1) 选择 开始 程序 Microsoft SQL Server 2005 SQL Server Management Studio 选项, 连接到本地默认实例, 打开 SSMS, 如图 3-5 所示 图 3-5 SSMS 主窗体 (2) 在 对象资源管理器 窗口中, 选择 本地数据库实例 数据库 teach 表 右键菜单的 新建表 命令, 打开 表设计器, 如图 3-6 所示

12 ASP.NET 简明教程 12 图 3-6 表设计器 对话框其中, 上半部分为列常用属性的设置, 如列名称 数据类型和允许空 列名 文本框中输入定义表的列的名称 ; 数据类型 下拉列表框用于选择所需要的数据类型 ; 允许空 复选框 : 用于设置该列是否为空 下半部分为列属性对话框, 每增加一列的时候, 会打开列属性对话框, 供用户设置除列名称 数据类型和允许空属性的其他列属性 刚打开 表设计器 窗口时, 会有一个空行供用户输入 比如学员流水号, 在 列名 文本框中输入 s_id, 数据类型 选择 decimal, 允许空 复选框勾不打上 (3) 定义完一个列的列名及其相关属性后, 系统会自动增加一个空行, 供用户定义新的列及相关属性, 如图 3-7 所示, 定义其他列的方法类似, 这里不一一讲解了 图 3-7 创建学号流水号列

13 13 (4) 定义完成后, 单击 保存 按钮保存 3.4 列的属性 3.3 节创建表的时候, 已经提过, 创建表的过程是设置列属性的过程 在表设计器的上半部分窗口只能设置列名称 数据类型和允许空属性, 其他属性需要通过列属性对话框来设置, 本节着重讲解列属性的设置 数据列如果在插入记录时, 没有为其中的一个或多个字段指定内容, 可以使用默认值来指定这些字段中使用什么值 默认值可以是计算结果为常量的任何值 比如说常量 函数和数学表达式, 都可以用来设置默认值 例如, 学员的性别默认为男, 用代码 1 来表示 如图 3-8 所示, 在 表设计器窗口 内, 选择 s_sex 子段, 在 列属性 对话框里的 默认值或绑定 文本框中输入 1 图 3-8 设置字段默认值 单击 保存 按钮 完成操作 生成 identity 值 identity 值也叫标识列, 是一个自增长列 在 SQL Server 2005 中, 标识列除了可设置步长为 1 外, 还可以设置其他增长量, 也可以设置自增长的起始值, 也叫标识种子 教务管理系统中, 学生流水号是一个自增长列, 标识种子为 1, 标识增量为 1 设置自增长列方法如下 : 在 表设计器 窗口中选择 s_id 列, 在 列属性 对话框里展开 标识规范,

14 ASP.NET 简明教程 14 如图 3-9 所示, 将 标识规范 设为 是, 标识增量 设置为 1, 标识种子 设置 为 1 图 3-9 设置 identify 属性 设置完后, 单击 保存 按钮 完成操作 NULL 值的使用 NULL 值表示列的数据未知或不可用 NULL 值与零 ( 数字值或二进制值 ) 零长度的字符串或空白 ( 字符值 ) 的含义不同 相反, 空值可以用于区分输入的是零 ( 数字列 ) 或空白 ( 字符列 ) 还是无数据输入 (NULL 值可用于数字列和字符列 ) 在创建表的时候, 默认为是可以为空, 要修改为不为空只需要把是否为空的复选框不选中即可, 如图 3-9 所示,class_id 为空 它位于表设计器上半部分的最右边的列选项 3.5 通过查询分析器定义表 除了使用 SSMS 创建表外, 可以使用查询分析器创建表, 这种创建表的方式, 就是使用代码方式, 创建表的语法是 : CREATE TABLE [ database_name. [ schema_name ]. schema_name. ] table_name ( { <column_definition> <computed_column_definition> } [ <table_constraint> ] [,...n ] ) [ ON { partition_scheme_name ( partition_column_name ) filegroup "default"}] [ { TEXTIMAGE_ON { filegroup "default" } ] [ ; ] <column_definition> ::= column_name <data_type>

15 15 [ COLLATE collation_name ] [ NULL NOT NULL ] [ [ CONSTRAINT constraint_name ] DEFAULT constant_expression ] [ IDENTITY [ ( seed,increment ) ] [ NOT FOR REPLICATION ] ] [ ROWGUIDCOL ] [ <column_constraint> [...n ] ] <data type> ::= [ type_schema_name. ] type_name [ ( precision [, scale ] max [ { CONTENT DOCUMENT } ] xml_schema_collection ) ] <column_constraint> ::= [ CONSTRAINT constraint_name ] { { PRIMARY KEY UNIQUE } [ CLUSTERED NONCLUSTERED ] [ WITH FILLFACTOR = fillfactor WITH ( < index_option > [,...n]) } ] [ ON { partition_scheme_name ( partition_column_name ) filegroup "default"}] [ FOREIGN KEY ] REFERENCES [ schema_name. ] referenced_table_name [ ( ref_column ) ] [ ON DELETE { NO ACTION CASCADE SET NULL SET DEFAULT } ] [ ON UPDATE { NO ACTION CASCADE SET NULL SET DEFAULT } ] [ NOT FOR REPLICATION ] CHECK [ NOT FOR REPLICATION ] ( logical_expression ) <computed_column_definition> ::= column_name AS computed_column_expression [ PERSISTED [ NOT NULL ] ] [ [ CONSTRAINT constraint_name ] { PRIMARY KEY UNIQUE } [ CLUSTERED NONCLUSTERED ] [ WITH FILLFACTOR = fillfactor WITH ( <index_option> [,...n ] ) ] [ FOREIGN KEY ]

16 ASP.NET 简明教程 16 ] REFERENCES referenced_table_name [ ( ref_column ) ] [ ON DELETE { NO ACTION CASCADE } ] [ ON UPDATE { NO ACTION } ] [ NOT FOR REPLICATION ] CHECK [ NOT FOR REPLICATION ] ( logical_expression ) [ ON { partition_scheme_name ( partition_column_name ) filegroup "default"}] < table_constraint > ::= [ CONSTRAINT constraint_name ] { { PRIMARY KEY UNIQUE } [ CLUSTERED NONCLUSTERED ] } (column[ ASC DESC ][,...n]) [ WITH FILLFACTOR = fillfactor WITH ( <index_option> [,...n ] ) ] [ ON { partition_scheme_name (partition_column_name) filegroup "default"}] FOREIGN KEY ( column [,...n]) REFERENCES referenced_table_name [ ( ref_column [,...n ] ) ] [ ON DELETE { NO ACTION CASCADE SET NULL SET DEFAULT } ] [ ON UPDATE { NO ACTION CASCADE SET NULL SET DEFAULT } ] [ NOT FOR REPLICATION ] CHECK [ NOT FOR REPLICATION ] ( logical_expression ) <index_option> ::= { PAD_INDEX = { ON OFF } FILLFACTOR = fillfactor IGNORE_DUP_KEY = { ON OFF } STATISTICS_NORECOMPUTE = { ON OFF } ALLOW_ROW_LOCKS = { ON OFF} ALLOW_PAGE_LOCKS ={ ON OFF} } 其中 : database_name: 在其中创建表的数据库的名称 database_name 必须指定现有数据库的名称 如果未指定, 则 database_name 默认为当前数据库 当前连接的登录名必须与 database_name 所指定数据库中的一个现有用户 ID 关联, 并且该用

17 17 户 ID 必须具有 CREATE TABLE 权限 schema_name: 新表所属架构的名称 table_name: 新表的名称 表名必须遵循标识符规则 除了本地临时表名 ( 以单个数字符号 (#) 为前缀的名称 ) 不能超过 116 个字符外,table_name 最多可包含 128 个字符 column_name: 表中列的名称 列名必须遵循标识符规则, 并在表中唯一 column_name 可包含 1 至 128 个字符 对于使用 timestamp 数据类型创建的列, 可以省略 column_name 如果未指定 column_name, 则 timestamp 列的名称将默认为 timestamp computed_column_expression: 定义计算列的值的表达式 PERSISTED: 指定 SQL Server Database Engine 将在表中物理存储计算值, 而且, 当计算列依赖的任何其他列发生更新时对这些计算值进行更新 ON { <partition_scheme> filegroup "default" }: 指定存储表的分区架构或文件组 如果指定了 <partition_scheme>, 则该表将成为已分区表, 其分区存储在 <partition_scheme> 所指定的一个或多个文件组的集合中 如果指定了 filegroup, 则该表将存储在命名的文件组中 数据库中必须存在该文件组 如果指定了 "default", 或者根本未指定 ON, 则表存储在默认文件组中 CREATE TABLE 中指定的表的存储机制以后不能进行更改 TEXTIMAGE_ON { filegroup "default" }: 指示 text ntext image xml varchar(max) nvarchar(max) varbinary(max) 和 CLR 用户定义类型的列存储在指定文件组的关键字 [ type_schema_name. ] type_name: 指定列的数据类型以及该列所属的架构 precision: 是指定数据类型的精度 Scale: 是指定数据类型的小数位数 max: 只适用于 varchar nvarchar 和 varbinary 数据类型, 用于存储 2^31 个字节的字符和二进制数据, 以及 2^30 个字节的 Unicode 数据 CONTEN: 指定 column_name 中每个数据类型为 xml 的实例都可包含多个顶级元素 DOCUMENT: 指定 column_name 中每个数据类型为 xml 的实例都只能包含一个顶级元素 xml_schema_collection: 仅适用于 xml 数据类型, 用于将 XML 架构集合与该类型相关联 DEFAULT: 如果在插入过程中未显式提供值, 则指定为列提供的值 constant_expression: 是用作列的默认值的常量 NULL 或系统函数 IDENTITY: 表示新列是标识列 在表中添加新行时, 数据库引擎将为该列提供一个唯一的增量值 seed: 是装入表的第一行所使用的值 increment: 是向装载的前一行的标识值中添加的增量值

18 ASP.NET 简明教程 18 NOT FOR REPLICATION: 在 CREATE TABLE 语句中, 可为 IDENTITY 属性 FOREIGN KEY 约束和 CHECK 约束指定 NOT FOR REPLICATION 子句 ROWGUIDCOL: 指示新列是行 GUID 列 COLLATE collation_name: 指定列的排序规则 排序规则名称可以是 Windows 排序规则名称或 SQL 排序规则名称 CONSTRAINT: 可选关键字, 表示 PRIMARY KEY NOT NULL UNIQUE FOREIGN KEY 或 CHECK 约束定义的开始 constraint_name: 约束的名称 约束名称必须在表所属的架构中唯一 NULL NOT NULL: 确定列中是否允许使用空值 PRIMARY KEY: 是通过唯一索引对给定的一列或多列强制实体完整性的约束 UNIQUE: 一个约束, 该约束通过唯一索引为指定的一列或多列提供实体完整性 一个表可以有多个 UNIQUE 约束 CLUSTERED NONCLUSTERED: 指示为 PRIMARY KEY 或 UNIQUE 约束创建 聚集索引或非聚集索引 FOREIGN KEY REFERENCES: 为列中的数据提供引用完整性的约束 [ schema_name. ] referenced_table_name ]: 是 FOREIGN KEY 约束引用的表的名 称, 以及该表所属架构的名称 ( ref_column [,... n ] ): 是 FOREIGN KEY 约束所引用的表中的一列或多列 ON DELETE { NO ACTION CASCADE SET NULL SET DEFAULT }: 指定如 果已创建表中的行具有引用关系, 并且被引用行已从父表中删除, 则对这些行采取的操作 默认值为 NO ACTION ON UPDATE { NO ACTION CASCADE SET NULL SET DEFAULT }: 指定如果表中发生更改的行有引用关系, 并且被引用行在父表中已更新, 则这些行将发生什么操作 默认值为 NO ACTION CHECK: 通过限制可输入到一列或多列中的可能值来强制域完整性的约束 计算列上的 CHECK 约束也必须标记为 PERSISTED [ ASC DESC ]: 指定加入到表约束中的一列或多列的排序顺序 默认设置为 ASC partition_scheme_name: 分区架构的名称, 该分区架构定义要将已分区表的分区映射到的文件组 数据库中必须存在该分区架构 [ partition_column_name. ]: 指定对已分区表进行分区所依据的列 WITH FILLFACTOR = fillfactor: 指定数据库引擎存储索引数据时每个索引页的填充程度 <index_option> ::= : 指定一个或多个索引选项 PAD_INDEX = { ON OFF }: 如果为 ON, 则 FILLFACTOR 指定的可用空间百分比将应用于该索引的中间级别页 FILLFACTOR = fillfactor: 指定一个百分比, 指示在索引创建或更改过程中数据库引擎应使每个索引页的叶级别达到的填充程度

19 19 IGNORE_DUP_KEY = { ON OFF }: 指定对唯一聚集索引或唯一非聚集索引的多行 INSERT 事务中重复键值的错误响应 STATISTICS_NORECOMPUTE = { ON OFF }: 如果为 ON, 则过期的索引统计信息不会自动重新计算 ALLOW_ROW_LOCKS = { ON OFF }: 如果为 ON, 则访问索引时允许使用行锁 如果为 OFF, 则启用自动统计信息更新 默认值为 OFF ALLOW_ROW_LOCKS = { ON OFF }: 如果为 ON, 则访问索引时允许使用行锁 如果为 OFF, 则不使用行锁 默认设置为 ON ALLOW_PAGE_LOCKS = { ON OFF }: 如果为 ON, 则访问索引时允许使用页锁 数据库引擎确定何时使用页锁 如果为 OFF, 则不使用页锁 默认设置为 ON 下面举例说明 例如 : 创建学员退学申请表, 其表 3-12 结构如下 : 表 3-12 学员退学申请表结构字段名称字段中文名数据类型是否为空说明 Leave_id 退学流水号 decimal Not null 主键 S_id 学员流水号 deicmal Not null Class_id 班级流水号 deicmal Study_phase 学习阶段 Char(4) Apply_date 申请日期 Char(10) Leave_date 退学日期 Char(10) Leave_reason 退学原因 Char(100) Remark 备注 Char(50) 其创建表的 SQL 代码如下 : Create table t_stu_leave( Leave_id decimal(8,0) not null primary key, S_id decimal(8,0) not null, Class_id decimal(8,0), Study_phase char(4), Apply_date char(10), Leave_date char(10), Leave_reason char(100), Remark char(50)) 3.6 管理模板 模板即为样板文件, 包含的 SQL 脚本可以帮助开发人员在数据库中创建对象 Microsft SQL Server 2005 提供了多种模板 首次打开模板资源管理器时, 会将一个模板的副本置于 Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\Templates 下的 Documents and Settings 文件夹中 这些模板适用于解决方案 项目和各种类

20 ASP.NET 简明教程 20 型的代码编辑器 模板可用于创建对象, 如数据库 表 视图 索引 存储过程 触发器 统计信息和函数 此外, 通过创建用于 Analysis Services 和 SQL Server Compact Edition 的扩展属性 链接服务器 登录名 角色 用户和模板 在 SSMS 中, 默认是不打开的, 打开模板资源管理器的方法是选择 视图 模板资源管理器 选项, 即可打开 如果要打开模板, 则在资源管理器选择所需要打开的模板, 双击即可 例如, 要打开创建表的模板 方法是 : 在 模板资源管理器 窗口中, 选择 SQL Server 模板 table 选项, 展开后, 双击 create table, 则在 SSMS 的视图区可以查看其模板的相关代码, 如图 3-10 所示 图 3-10 模板资源管理器在模板资源管理器中可以实现对模板的创建 修改和删除, 创建模板的操作如下 : 选择 模板资源管理器 窗口的 SQL Server 模板 右击菜单的 新建 模板 选项, 在模板资源管理器的窗口的最下面会新建一个 新建 SQL Sever 模板 的项, 如图 3-11 所示

21 21 图 3-11 新建 SQL Sever 模板这里把 SQL Server 模板命名为所需要的名称, 这里命名为 模板示例, 这个命名的修改操作跟 windows 创建文件名的操作是一致的, 这里不详细阐述 双击 模板示例 项, 在视图区中出现一片空白, 在里面输入模板的代码, 单击 保存 按钮保存即可 修改模板的操作很简单, 只需要双击所要修改模板的项右键的 编辑 选项, 然后在其视图区域中修改里面模板的内容, 单击 保存 按钮保存即可 删除模板的操作也很简单, 只需要选择要修改模板的项右键菜单的 删除 选项即可 3.7 在查询分析器中修改表 在查询分析器中修改表, 跟在查询分析器中定义表的操作是类似的, 是使用 SQL 语句来完成, 其修改表的语法是 : ALTER TABLE [ database_name. [ schema_name ]. schema_name. ] table_name { ALTER COLUMN column_name { [ type_schema_name. ] type_name [ ( { precision [, scale ] max xml_schema_collection })] [ NULL NOT NULL ] [ COLLATE collation_name ] {ADD DROP } { ROWGUIDCOL PERSISTED } } [ WITH { CHECK NOCHECK } ] ADD { <column_definition> <computed_column_definition> <table_constraint>

22 ASP.NET 简明教程 22 } [ ; ] } [,...n ] DROP { [ CONSTRAINT ] constraint_name [ WITH ( <drop_clustered_constraint_option> [,...n ] ) ] COLUMN column_name } [,...n ] [ WITH { CHECK NOCHECK } ] { CHECK NOCHECK } CONSTRAINT { ALL constraint_name [,...n]} { ENABLE DISABLE } TRIGGER { ALL trigger_name [,...n]} SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name. ] target_table [ PARTITION target_partition_number_expression ] <drop_clustered_constraint_option> ::= { MAXDOP = max_degree_of_parallelism ONLINE = {ON OFF } MOVE TO { partition_scheme_name ( column_name ) filegroup "default"} } 其参数意义跟创建表类似, 参照 3.7 节, 这里就不详细阐述了 例如, 把学员退学表中的备注的字段扩大到 100 个, 其 SQL 语句是 : Alter table t_stu_leave alter column remark char(100) 3.9 定义其余的表 使用 SSMS 或者是查询分析器创建的表是用户创建的, 称为用户表, 永久存储数据的表 在实际开发中, 除了永久存储数据外, 有时需要创建临时存储数据的表 ; 有时候一个区不够存储一个表的数据, 还得对表进行分析,SQL Server 2005 提供了临时表和已分区来解决临时存储和分区问题 临时表在 SQL Server 2005 中除了可以定义一般的表外, 还可以定义临时表, 可以创建本地临时表和全局临时表 本地临时表仅在当前会话中可见, 而全局临时表在所有会话中都可见 临时表不能分区 本地临时表的名称前面有一个数字符号 (#table_name), 而全局临时表的名称前面有两个数字符号 (##table_name) 如果本地临时表由存储过程创建或由多个用户同时执行的应用程序创建, 则数据库引擎必须能够区分由不同用户创建的表 为此, 数据库引擎在内部为每个本地临时表的表名追加一个数字后缀 存储在 tempdb 的 sysobjects 表中的临时表, 其全名由

23 23 CREATE TABLE 语句中指定的表名和系统生成的数字后缀组成 为了允许追加后缀, 为本地临时表指定的 table_name 不能超过 116 个字符 已分区表已分区表是将超大表按照某种业务规则分别存储在不同的物理介质中 ( 文件组 ), 以提高性能 方便管理 已分区表是将一个表分为两个或多个表, 这些表在物理上说是多个表, 但是从逻辑上来说, 是同一个表 一般来说, 当一个数据表里的数据达到千万级时, 就应该要考虑使用已分区表了 分区表将一个表拆分成了多个表, 那么每个表是记录数就会相对来说减少 这样, 对单个表的维护的成本也会大大减少 例如数据库备份, 对于一个千万级记录的表备份, 可能需要数分钟, 但对于十个百万级记录的表备份, 可能只要数秒钟 因为数据库备份时需要将数据库锁定, 不让其他用户操作数据库, 那么备份数以加的时间越长, 就意味着有越多的用户等待 另一种情况, 当然数据表里数据量大, 且数据有着不同的操作方式时, 也可以考虑使用已分区表 例如一个数据表里的记录, 对于当月的记录, 所进行的操作主要是插入 修改和删除, 而对于以往的记录, 所进行的操作主要是统计和查询, 那么也可以考虑使用已分区表 将数据表按月份进行分区, 那么对数据表的管理可能会更容易一些 在对表进行分区之前, 必须先创建分区函数和分区方案 分区函数是定义如何根据某些列 ( 如时间字段 ) 的值将表的行映射到一组分区 分区方案将把分区函数指定的每个分区映射到文件组 由于创建表分区不是本书的内容, 所在在此不介绍如何对数据表进行分区, 有兴趣的读者可以查询帮助文档 3.10 设置主键 主键是一种约束, 称为主关键字约束 它指定表的一列或几列的组全的值在表中具有唯一性, 即能唯一地指定一行记录 每个表中只能有一列被指定为主关键字, 且 IMAGE 和 TEXT 类型的列不能被指定为主关键字, 也不允许指定主关键字列有 NULL 属性 创建主键的方法有二种, 一种是在 SSMS 中创建, 另一种是通过查询分析器创建 在 SSMS 中创建主键例如, 为退学申请表创建主键 在 SSMS 的表设计窗口, 选中要创建主键的列 leave_id, 选择其右键菜单的 设置主键 选项, 在 leave_id 字段的左边出现一个图标, 如图 3-12 所示

24 ASP.NET 简明教程 退学申请表设置主键 单击 保存 按钮即可 使用查询分析器创建主键 例如, 为退学申请表创建主键 根据修改表的语法, 创建主键的 SQL 语句是 : Alter table t_stu_leave add primary key(leave_id) 3.11 表的其他约束 为了减少输入错误和数据库数据的完整性, 可以对字段设置约束 例如, 学员出生日期, 其输入范围应该在 1970 年到 2000 年之间 约束是为了保证数据库里数据的完整笥而实现的一套机制 它包括主键约束 外键约束 Unique 约束 Check 约束 缺省值和允许空 6 种机制 前面已经讲了空约束 主键约束, 本小节讲解其余的四种约束 在创建时检查现有数据在创建时检查现在有数据, 是通过 check 来实现, 也称为检查约束 用来给输入列或整个表中的值设置检查条件, 以限制输入值, 保证数据库的数据完整性 检查约束的创建方法有使用 SSMS 和使用 Transact-SQL 两种 下面对这两种方法分别进行介绍 例如, 在 SSMS 中创建学员信息表中的性别的约束, 键名为 CK_T_STUDENT 在如图 3-1 的 表设计器 窗口, 选择 s_sex 右键快捷菜单的 check 约束 选项, 打开 check 约束 对话框, 如图 3-13 所示 图 3-13 check 约束对话框单击 添加 按钮, 则新增了一个约束 ck_t_student, 在表达式中输入约束的表达式, 这里为 :s_sex= 1 or s_sex= 0, 然后单击 关闭 按钮, 在 SSMS 主窗口中单击 保存 按钮即可保存 单击 删除 按钮, 可删除创建的 check 约束 使用查询分析器创建性别约束的代码是 : Alter table t_student constraint ck_s_sex check(s_sex= 1 or s_sex= 0 )

25 强制外建约束 FOREIGN KEY 约束, 即外键约束 用来与其它表 ( 参照表 ) 中的列建立连接 若参照表中的主键列或具有惟一性约束的列包含在另一个表中, 则该字段就是另一个表中的外键, 其中 IMAGE 和 TEXT 类型的列不能被指定为外键 注意 : 在定义主键约束的表中更新列值时, 则其它表中有与之对应关联的外键约束的表中的外关键字列也将相应得做出更改 例如, 将学员信息表的班级学号字段设置为班级表的外键 其键名为 FK_T_STUDENT_T_CLASS 在 SSMS 中创建外键的约束的方法是 : (1) 在 SSMS 表设计窗口中, 选择 学员信息表 键 右键快捷菜单的 新建外键 选项, 打开 外键关系 对话框, 如图 3-14 所示 图 3-14 外键关系 对话框 单击 表和列规范 右边的浏览按钮, 打开 表和列 对话框, 如图 3-15 所示 图 3-15 表和列 对话框其中, 关系名 文本框 : 用于指定关系名 主键表 下拉列框 : 选择作为主键的列, 下面的列表框则选择该表的字段 外键表 下拉列表框 : 用于选择外键的字段 这里主键表为 t_class 的 class_id 字段, 外键表的字段是 class_id 单击 确定 按钮, 回到 外键关系 对话框, 单击 关闭 按钮, 回到 SSMS 主窗口, 单击 保存 按钮保存即可 除了在 SSMS 中创建外键外, 也可以使用查询分析器创建外键, 即 SQL 语句, 其

26 ASP.NET 简明教程 26 语法详细见创建或修改表的语法, 其增加外键代码为 : Alter table t_student add constraint fk_class_id foreign key (class_id) references t_class(class_id) Unique 约束 UNIQUE 约束强制实施列集中值的唯一性 创建唯一性约束的语法是 : [CONSTRAINT constraint_name] UNIQUE [CLUSTERED NONCLUSTERED] [WITH [FILLFACTOR = fillfactor]] [ON {filegroup DEFAULT}] 教务管理系统中班级表的名称不能重复, 其代码是 : alter table t_class add constraint unique_c_name unique(c_name) 设置数据表的约束数据表约束是要同时针对多个字段设置约束 教务管理系统中, 班级信息的预计毕业时间和实际毕业时间有一个不能为空 alter table t_class add constraint check_date check(c_enddate is not null or c_finish is not null) 3.12 约束的操作 前面介绍约束时, 详细介绍了各种约束的创建, 接下来详细介绍如何禁用约束 启用约束 删除约束 (1) 禁用约束禁用 节创建的约束 unique_c_name, 代码为 : Alter table t_class nocheck constraint unique_c_name 禁用所有约束的代码是 : Alter table t_class nocheck constraint (2) 启用约束启用上面的禁用的约束, 其代码为 : Alter table t_class check constraint unique_c_name 启用所有约束的代码为 : Alter table t_class check constraint all (3) 删除约束如果要把约束给删除, 其代码为 : Alter table t_class drop unique_c_name 3.13 表关系 关系通过匹配键列 ( 通常是两个表中同名的列 ) 中的数据来发挥作用 在大多数情况下, 关系将一个表的主键 ( 它为每行提供唯一标识符 ) 与另一表的外键中的项相匹配 表之间有三种类型的关系, 分别是一对一 一对多和多对多 一对多关系 : 一对多关系是最常见的关系类型 在这种关系中, 表 A 中的一行

27 27 在表 B 中可以有许多匹配行, 但表 B 中的一行在表 A 中只能有一个匹配行 例如, publishers 和 titles 表具有一对多关系 : 每个出版商可出版很多书籍, 但每本书籍只能出自一个出版商 如果在相关列中只有一列是主键或具有唯一约束, 则会创建一对多关系 一对多关系的主键方由键符号表示 关系的外键方由无穷符号表示 一对一关系 : 在一对一关系中, 表 A 中的一行最多只能与表 B 中的一行相匹配, 反之亦然 当两个相关列都是主键或都具有唯一约束时, 将创建一对一关系 这种关系不常见, 因为以这种方式相关的多数信息都在一个表中 一对一关系可以用于 : 分割一个含有许多列的表 ; 由于安全原因而隔离表的某一部分 ; 存储只需删除表就会很容易删除的临时数据 ; 存储只应用于主表的一个子集的信息 一对一关系的主键方由键符号表示 外键方也由键符号表示 多对多关系 : 在多对多关系中, 表 A 中的一行在表 B 中可以有许多匹配行, 反之亦然 您需要通过定义第三个表 ( 称为联接表 ) 来创建这样的关系, 该表的主键由来自表 A 和表 B 的外键组成 例如,authors 表和 titles 表具有多对多关系, 该关系由这两个表中任意一个表与 titleauthors 表之间的一对多关系定义 titleauthors 表的主键是 au_id 列 (authors 表的主键 ) 和 title_id 列 (titles 表的主键 ) 的组合 创建关系图使用服务器资源管理器创建新的数据库关系图 数据库关系图以图形方式显示数据库的结构 使用数据库关系图可以创建和修改表 列 关系和键 此外, 还可以修改索引和约束 其方法是 : (1) 打开 SSMS, 在 对话资源管理器 选择数据库下的 数据库关系图 右键快捷菜单的 新建数据库关系图, 如图 3-16 所示, 初次使用时, 需要安装关系图支持程序, 打开安装关系图对象消息框, 如图 3-17 所示, 单击 是 按钮安装 图 3-16 对象资源管理器 对话框

28 ASP.NET 简明教程 28 图 3-17 安装关系图支持程序 提示对话框 (2) 安装完毕后, 重复步骤 (1), 打开 添加表 对话框, 如图 3-18 所示 图 3-18 添加表 对话框 (3) 选中 T_CLASS 和 T_STUDENT, 单击 关闭 按钮, 回到 数据库关系 图, 如图 3-19 所示, 在这里可以看到 T_CLASS 和 T_STUDENT 的关系, 还可以看到这两张表的主键 图 3-19 数据库关系图 对话框 (4) 如需要增加数据表到关系中, 则选择空白处右键快捷菜单 添加表 选项, 重复上述步骤即可 在关系图删除数据表在关系图删除数据表, 他有二种方法 : 一种是在关系图中选中表, 再按键盘的 delete 键, 另一种是在关系图里, 选择表右键菜单的 从关系图中移除 选项

29 建立和删除表的关系 建立表的关系方法是 : 创建一个新关系, 从需要创建关系的一张表中选择关联的 字段, 拖动到另一张表中, 打开 表和对 和 外键关系 对话框, 如图 3-20 所示 图 3-20 表和列 对话框 设定好关联的字段, 单击 确定 按钮, 回到 关系图 对话框, 此时可以看到 T_FEE_INFO 表与 T_STUDENT 的关系, 如图 3-21 所示 图 3-21 建立新关系删除关系的方法很简单, 只要选中要删除的关系连接线, 选择右键快捷菜单的 从数据库中删除关系 选项, 则删除关系 单击 保存 按钮, 打开 选择名称 对话框, 该对话框用于设置数据库关系图的名称, 这里设置为 学生班级 关系图, 如图 3-22 所示 图 3-22 选择名称 对话框

30 ASP.NET 简明教程 综合实例 在上一张章综合实例, 创建了图书管理系统数据库, 该数据库有五张表, 分别是 读者种类信息表, 如表 3-13 所示 读者信息表, 如表 3-14 所示 书籍类别信息表, 如表 3-15 所示 书籍信息表, 如表 3-16 所示 借阅信息表, 如表 3-17 所地不 表 3-13 readertype 读者种类信息表 列名 数据类型 可否为空 说明 Typeno Varchar(4) NOT NULL 种类编号 Typename Varchar(20) NOT NULL 种类名称 booknumber Numeric NULL 借书数量 Bookday Numeric NULL 借书期限 Userfullife Numberc NULL 有效期限 Memo Text NULL 备注 表 3-14 readers 读者信息表 列名 数据类型 可否为空 说明 Readerno Varchar(8) Not null 读者编号 Readername Varchar(20) Not null 读者姓名 Readersex Varchar(4) Not null 读者性别 Readertype Varchar(20) Null 读者种类 Readerdep Varchar(30) Null 工作单位 Address Varchar(40) Null 家庭地址 Readertel Varchar(13) Null 电话号码 Varchar(20) Null 电子邮件地址 Checkdate Datetime Null 登记日期 Readermemo Varchar(4000) Null 备注 表 3-15 booktype 书籍类别信息表 列名 数据类型 可否为空 说明 Booktypeno Varchar(4) Not Null 类别编号 Typename Varchar(20) Not null 类别名称 Keywork Text Not null 备注 表 3-16 books 书籍信息表 列名 数据类型 可否为空 说明 Bookid Varchar(13) Not null 书籍编号 Bookname Varchar(30) Not null 书籍名称 Booktype Varchar(4) Not null 书籍类别 Bookauthor Varchar(8) Not null 书籍作者 Bookpub Varchar(30) Null 出版社名称

31 31 Bookpubdate Datetime Null 出版日期 Bookpages Numeric Null 书籍页码 Bookkeyword Varchar(20) Null 关键词 Bookindate Datetime Null 登记日期 Putup Varchar(2) Null 是否被借出 Bookmemo Text Null 备注 表 3-17 borrowinfo 借阅信息表 列名 数据类型 可否为空 说明 Borrowno Varchar(4) Not null 借阅编号 readerid Varchar(8) Not null 读者编号 Readername Varchar(4) Not null 读者姓名 Bookid Varchar(13) Not null 书籍编号 Bookname Varchar(30) Null 书籍名称 Borrowdate Datetime Null 书籍名称 Returndate Datetime Null 还书日期 Memo Text null 备注信息 其关系如图 3-23 示 图 3-23 图书管理系统实体关系图在数据库 book 中, 使用 SQL 语句创建这五张表, 并设置主键 非空约束等约束及其关系 创建 book 表及约束的步骤如是 : 启动 SSMS, 单击工具栏按钮, 打开查询编辑器对话框, 输入如下 T-SQL 语句 :

32 ASP.NET 简明教程 32 use book 创建读者种类信息表 create table readertype( typeno varchar(4) not null primary key, typename varchar(20) not null, booknumber numeric null, bookday numeric null, userfulllift numeric null, memo text) 创建读者信息表 create table readers( readerno varchar(8) not null primary key, readername varchar(20) not null, readersex varchar(4) not null, readertype varchar(4) null constraint fk_readertype foreign key references readertype(typeno), readerdep varchar(30) null, address varchar(40) null, readertel varchar(13) null, varchar(30) null, checkdate datetime null, readermemo varchar(400) null) 创建书籍种类信息表 create table booktype( booktypeno varchar(4) not null primary key, typename varchar(20) not null, keyword text not null) 创建书籍信息表 create table Books( bookid varchar(13) not null primary key, bookname varchar(30) not null, booktype varchar(4) not null constraint fk_booktype foreign key references booktype(booktypeno), bookauthor varchar(8) not null, bookpub varchar(30) null, bookpubdate datetime null, bookpages numeric null, bookkeyword varchar(20) null, bookindate datetime null, putup varchar(2) null, bookmemo text)

33 33 创建借阅信息表 create table borrowinfo( borrowno varchar(4) not null primary key, readerid varchar(8) not null constraint fk_readerid foreign key references readers(readerno), readername varchar(4) not null, bookid varchar(13) null constraint fk_bookid foreign key references books(bookid), bookname varchar(30) null, borrowdate datetime null, returndate datetime null, Memo text); 单击工具栏运行按钮, 执行这 T-SQL 语句 在 对话资源管理器 对话框, 选择数据库下的 数据关系图 右键的 新建数据库关系图 选项, 打开其关系图, 如图 3-24 所示 第一次打开时, 则根据表与表之间的主外键关系建立关系, 也可以在关系图中创建新的关系, 这里关系是由上述的 T-SQL 创建的 图 3-24 数据库关系图 对话框 单击 保存 按钮, 保存为 图书管理关系图