ZMF740.mps

Size: px

Start display at page:

Download "ZMF740.mps"

炖除双
7 years ago
Views:

1 第章表的创建与管理学习目标瞯了解表的概念瞯了解 SQL Server 2000 中的数据类型瞯掌握创建及删除自定义数据类型的方法瞯掌握数据表的创建修改和删除方法瞯掌握 DEFAULT CHECK PRIMARY KET UNIQUE 和 FOREIGN KEY 约束的定义及使用瞯掌握使用企业管理器插入修改和删除记录的方法学习重点用瞯 SQL Server 2000 中数据表的创建, 修改和删除瞯 DEFAULT CHECK PRIMARY KET UNIQUE 和 FOREIGN KEY 约束的定义与使通过前面的学习可知, 使用数据库可以更加高效迅速地管理数据在数据库中管理数据时, 将数据存储到表中, 实际上是数据规范化有序化的过程, 创建表的好坏直接影响到数据库的性能通过学习本章将学会如何创建表, 包括数据类型各种实现数据完整性的约束等 3.1 表的概念 SQL Server 2000 是一种关系型数据库管理系统 ( RDBMS) 在这种类型的数据库管理系统

2 48 第 3 章表的创建与管理中, 数据存储在由行和列组成的表中一个数据库可由多个相互联系的表组成, 表是由若干行和列组成的记录的集合如图 3-1 所示, 这是一个存储学生信息的表该表中的每一列分别描述了学生的属性 ( 学号姓名性别等 ), 表中的列也称为字段而每一行则分别描述了一个学生的学号姓名等信息内容, 称之为记录图 3-1 学生信息表 (stu) 3.2 数据类型 SQL Server 2000 中的数据类型可以分为系统数据类型和自定义数据类型系统数据类型是由 SQL Server 提供的可供用户使用的数据类型自定义数据类型是由用户自定义的数据类型由图 3-1 可以看出, 学生的学号 (StuID) 是由数字组成的, 而学生的姓名 ( StuName) 是由字符串组成的, 下面就介绍一下 SQL Server 2000 对这些不同类型的数据进行存储时, 可以供用户选择的数据类型系统数据类型 SQL Server 2000 中提供的数据类型, 如表 3-1 所示表 3-1 SQL Server 数据类型数据类型标识符描述整型精确数值型 bigint int smallint tinyint bit decimal[(p[,s])] numeric[(p[,s])] 可表示 ~ 的整数, 长度为 8 B 可表示 ~ 的整数, 长度为 4 B 可表示 ~ 的整数, 长度为 2 B 可表示 0 ~255 的整数, 长度为 1 B 可表示 1 或 0 的整数数据从 ~ 的固定精度和小数位的数字数据功能上等同于 decimal

3 3.2 数据类型 49 数据类型标识符描述续表货币型近似数字时间日期型字符串 Unicode 字符串 money smallmoney float real datetime smalldatetime char[(n)] varchar[(n)] text ncahr[(n)] nvarchar[(n)] ntext binary[(n)] 货币数据值介于 ~ 之间, 精确到货币单位的千分之十货币数据值介于 ~ 之间, 精确到货币单位的千分之十从 -1.79E +308 ~1.79E +308 的浮点精度数字从 -3-40E +38 ~3-40E +38 的浮点精度数字从 1753 年 1 月 1 日 9999 年 12 月 31 日的日期和时间数据, 精确到 0.03 s( 或 3.33 ms) 从 1900 年 1 月 1 日 2079 年 6 月 6 日的日期和时间数据, 精确到分钟固定长度的非 Unicode 字符数据, 最大存储为个字节可变长度的非 Unicode 字符数据, 最大存储为个字节可变长度的非 Unicode 字符数据, 最大长度为 ( ) 个字节固定长度的 Unicode 字符数据, 最大存储为个字节可变长度 Unicode 字符数据, 最大存储为个字节 sysname 是系统提供用户定义的数据类型, 在功能上等同于 nvarchar(128), 用于引用数据库对象名可变长度 Unicode 字符数据, 其最大长度为 ( ) 个字节固定长度的二进制数据, 其最大长度为 B 二进制字符串 varbinary[(n)] 可变长度的二进制数据, 其最大长度为 B image 可变长度的二进制数据, 其最大长度为 ( )B 其他数据类型 cursor sql_variant table timestamp uniquedentifier 游标的引用一种存储 SQL Server 支持的各种数据类型 ( text ntext timestamp 和 sql_variant 除外 ) 值的数据类型一种特殊的数据类型, 存储供以后处理的结果集数据库范围的唯一数字, 每次更新数据行时也进行更新全局唯一标识符 (GUID)

4 50 第 3 章表的创建与管理 1. 整型整型数据类型包括 bigint int smallint tinyint 和 bit 共 5 种数据类型整型数据包括正整数和负整数, 且小数位数为 0, 其不同的表示范围如表 3-1 所示 bit 数据类型存储 0 或 1 如果一个表中有不多于 8 个的 bit 列, 这些列将作为一个字节存储如果表中有 9 ~16 个 bit 列, 则这些列将作为 2 个字节存储更多列的情况依此类推 2. 精确数值型精确数值型由整数部分和小数部分组成, 包括 decimal 和 numeric SQL Server 2000 中这两种数据类型功能相同精确数值型要求准确地指定使用的小数位数和精度, 精度是十进制数字的最大个数小数位数是小数点右边的十进制数字个数, 例如数的精度是 5, 小数位数是 2 定义方法如下 : decimal[ (p[,s]) ] 和 numeric[(p[,s])] 瞯 p( 精度 ): 指定小数点左边和右边可以存储的十进制数字的最大个数精度必须是从 1 到最大精度之间的值, 最大精度为 38 瞯 s( 小数位数 ) : 指定小数点右边可以存储的十进制数字的最大个数小数位数必须是从 0 ~p 之间的值, 默认小数位数是 0, 因此 0 s p 最大存储大小基于精度而变化例如,decimal(6,4) 精度为 6, 小数位数为 4, 则整数位数为 2, 如精确数值型中不同的精度占用的存储字节数有所不同, 如表 3-2 所示表 3-2 精确数值型占用的存储字节数精度存储字节数 1 ~ ~ ~ ~ 货币型当字段中存放工资价格等与货币有关的数据时, 可使用货币型, 包括 smallmoney 和 money 两种瞯 Money 类型的精度为 19, 占用的字节数为 8 B 瞯 Smallmoney 类型的精度为 10, 占用的字节数为 4 B 这两种数据类型精确到千分之十, 小数位数为 4 4. 近似数字使用近似数字类型可以尽可能精确地存储数据 SQL Server 支持两种近似数字类型 :float 和 real 如果要取整数或要在值之间执行量的检查, 应避免使用近似数字数据类型

5 3.2 数据类型时间日期型 SQL Server 2000 提供两种数据类型, 用于存储日期和时间信息 :datetime 和 smalldatetime datetime 从 1753 年 1 月 1 日 9999 年 12 月 31 日的日期和时间数据, 精确度为 0.03 s( 或 3.33 ms), 占用的存储字节数为 8 B smalldatetime 从 1900 年 1 月 1 日 2079 年 6 月 6 日的日期和时间数据, 精确到分钟, 占用的存储字节数为 4 B 6. 字符数据类型字符数据类型可以分为两种 : 非 Unicode 字符数据和 Unicode 字符数据非 Unicode 字符数据又可分为固定长度 (char) 可变长度(varchar) 和文本型 (text) 所谓固定长度指的是该类型的数据都占用定义时指定的字符串的长度, 无论实际是否占满例如, 对于 char(5),erju ji hidja k 等都符合字符串定义, 并且都占用 5 个字节, 但不能超过定义的长度, 例如 jskche 则不能存入该字段定义为 char(n) 时,n 的取值范围为 1 ~8 000,n 由创建者定义可变长度是指字符串占用的存储空间由实际存储的长度决定, 不能超出定义时的长度例如, 对于 varchar(4), 字符串 "js" 实际占用 2 个字节, 字符串 "ksi" 实际占用 3 个字节, 但字符串 "eirosa" 不能存入, 因为它需占用 6 个字节, 超出了定义的长度定义 varchar( n) 时,n 的取值范围也为 1 ~8 000,n 由创建者定义使用 char 或 varchar 的指导原则如下 : 瞯如果列中的数据长度接近一致, 可使用 char 瞯如果列中的数据长度显著不同, 可使用 varchar 注意 : 一个中文字符占用 2 个字节当一个字段的长度超过 B 时, 可使用 text 数据类型, 该类型也是一个可变长度的数据类型, 根据实际输入的内容占用存储空间其最大长度为 ( ) 个字节, 可用来存储简介个人信息文章等内容较多的数据同样,Unicode 字符数据也可分为固定长度 ( nchar) 可变长度( nvarchar) 和 ntext( 文本型 ) Unicode 采用两个字节编码每个字符, 当需要使用的字符不能全部存储在单字节数据内时, 可使用 Unicode 字符数据当设计的系统必须处理不同的语言时, 为了满足不同国家 / 地区的语言存储需求, 可以通过在整个系统中使用 Unicode 数据类型, 尽量减少字符不能正确转换的问题字符数据类型可定义为 nchar(n) 和 nvarchar(n),n 的值必须介于 1 ~4 000 之间一个 Uni- code 字符数据占用 2 个字节 nchar(4) 占用 8 个字节,nvarchar(4) 中存储 jid 占用 6 个字节注意 : 无论是英文字符还是汉字都使用 2 个字节来存储当一个字段的长度超过 B 时, 可使用 ntext 数据类型, 其最大长度为 ( ) 个字节 7. 二进制字符串二进制字符串可分为固定长度 ( binary) 的二进制字符串和可变长度 ( varbinary) 的二进制字

6 52 第 3 章表的创建与管理符串, 存储十六进制表示的数据瞯 binary[( n)] 表示固定长度为 n 个字节的二进制数据 n 的取值为 1 ~8 000, 存储空间大小为 n +4 个字节瞯 varbinary[ (n) ] 表示变长为 n 个字节的二进制数据 n 的取值为 1 ~8 000, 存储空间大小为实际输入数据长度 +4 个字节, 而不是 n 个字节输入的数据长度可能为 0 字节 image 也可存储可变长度二进制数据介于 0 ~2 31-1( ) 字节之间二进制数据超过 B, 图片或 OLE 对象等数据可存储在 image 类型中 8. 其他数据类型 (1) cursor 数据类型 cursor 数据类型包含对游标的引用, 对于 CREATE TABLE 语句中的列, 不能使用此种类型 (2) sql_variant 数据类型一种存储 SQL Server 支持的各种数据类型 ( text ntext image timestamp 和 sql_variant 除外 ) 值的数据类型 (3) table 数据类型一种特殊的数据类型, 用于存储结果集以供后续处理该数据类型主要用于临时存储一组行, 这些行将作为表值函数的结果集返回 (4) timestamp 数据类型由 timestamp 数据类型自动生成的二进制数, 在数据库中是唯一的 timestamp 一般用作给表中的行加时间戳, 存储大小为 8 个字节 (5) uniqueidentifier 数据类型系统将为这种类型的数据产生唯一的标识值, 常把它比作全局唯一标识符 ( GUID) 用户定义数据类型当为某种类型的数据定义一种数据类型之后, 可能需要在后面反复使用该定义, 此时就可以将其存储成为一种用户定义数据类型, 并可重复使用用户定义数据类型是基于系统提供的数据类型来定义的, 为了确保其一致性, 用户只能在定义它的数据库中使用可通过企业管理器或查询分析器来创建创建用户定义数据类型 1. 企业管理器例如, 现在要存储的数据为电话号码, 由 0 ~9 的数字组成, 但并不会使用它来进行计算, 所以应考虑使用字符串型, 定义为 char(15) 具体的操作步骤如下 : 1 展开服务器组, 然后展开服务器 2 展开数据库文件夹, 再展开要在其中创建用户定义的数据类型的数据库 3 右击用户定义数据类型选项, 从弹出的快捷菜单中选择新建用户定义数据类型命令 ( 见图 3-2), 弹出用户定义的数据类型属性对话框, 如图 3-3 所示

7 3.2 数据类型 53 图 3-2 新建用户定义数据类型图 3-3 用户定义数据类型属性 4 输入新建数据类型的名称 phone 5 在数据类型列表中, 选择 char

8 54 第 3 章表的创建与管理 6 若长度文本框处于可用状态, 且需要更改此数据类型可存储的最大数据长度, 可输入另外的值长度可变的数据类型有 binary char nchar nvarchar varbinary 和 varchar, 此处将长度设置为 15 7 若要允许此数据类型接受空值, 可选中允许 NULL 值复选框最后, 单击确定按钮, 该用户自定义数据类型创建完成在企业管理器的详细窗格中将显示其信息, 如图 3-4 所示图 3-4 用户自定义数据类型创建完成若要删除某个用户定义的数据类型, 可右击该数据类型, 从弹出的快捷菜单中选择删除命令, 如图 3-5 所示选择删除命令后, 将打开如图 3-6 所示的对话框, 单击显示相关性按钮, 将显示该对象的使用状况, 依附于哪些对象, 或有哪些对象依附于它, 如图 3-7 所示若有对象依附于它, 则不能删除简单地说, 若该用户定义数据类型已使用到某个表中, 则不能删除, 若要删除, 需先除去使用它的对象单击全部除去按钮将删除该用户定义的数据类型 2. 在查询分析器中书写 T -SQL 语句在查询分析器中创建用户定义数据类型时可使用系统存储过程 sp_addtype 相关语法如下 :

9 3.2 数据类型 55 图 3-5 删除用户定义的数据类型图 3-6 除去对象图 3-7 相关性 sp_addtype 类型的名称, 系统数据类型 [, NULL NOT NULL ][, 创建者或所有者 ] 例 3-1 为电话创建一个数据类型, 定长字符串型, 长度为 15 USE students

10 56 第 3 章表的创建与管理 EXEC sp_addtype phone, char(15), NULL 若要删除该用户自定义数据类型, 可使用系统存储过程 sp_droptype 相关语法如下 : sp_droptype 类型的名称例 3-2 删除用户自定义数据类型 phone EXEC sp_droptype phone 关于如何使用用户自定义数据类型, 将在 3.3 节中讲述 3.3 创建数据表在关系型数据库管理系统中, 使用表来管理数据表的结构类似于房屋的框架, 只有将框架搭好, 才能存放数据下面就介绍一下创建表的方法创建表在第 2 章新建的 students 数据库中新建一个名为 Stu 的表, 如表 3-3 所示表 3-3 Stu 表列名数据类型是否为空 StuID( 学号 ) int 否 StuName( 姓名 ) char(10) 是 Sex( 性别 ) char(2) 是 Birthday( 生日 ) datetime 是 Phone( 联系电话 ) phone(char(15)) 是 Class( 班级 ) char(16) 是 DepID( 系号 ) char(6) 否 Remark( 备注 ) varchar(50) 是使用企业管理器创建表的步骤如下 : 1 打开企业管理器, 选择要在其中创建表的数据库 students, 选择表此时, 出现许多以 sys 开头的表, 这些表是系统表, 记录该数据库中相关的系统信息创建存储数据的用户表时可单击鼠标右键, 从弹出的快捷菜单中选择新建表命令, 如图 3-8 所示 2 此时, 将打开表结构的设计器, 如图 3-9 所示在此窗口中输入表中各个列的名称数据类型及长度, 并设置是否允许为空列名的命名要符合标识符命名的规则, 为各个列选择合适的数据类型时, 除了字符串类型和二进制类型外, 其他的数据类型的长度不能进行修

11 3.3 创建数据表 57 图 3-8 新建表改对于 decimal 和 numeric 数据类型, 其精度和小数位数在选中这一列后, 在下方该列的属性栏中的精度和小数位数栏中进行设置, 在允许空列中打钩, 则表示该字段的内容允许为空值前面定义的用户自定义数据类型也会出现在数据类型的选择列表中, 直接选用即可可将数据类型为整型 ( bigint int smallint tinyint decimal( p,0) numeric( p,0)) 的字段设置成标识列当某个字段被设置成标识列时, 该字段会由 SQL Server 2000 自动地插入值, 唯一地标识每行选中 StuID 列, 在标识下拉菜单中选择是, 标识种子表示该列编号的起始值, 设置为 ; 标识增量表示 SQL Server 编号时的自动增长量, 设置为 1 该表中的第一条记录 StuID 列的值为 , 则第二条记录的 StuID 列的值为若标识增量设置为 3, 则第二条记录的 StuID 列的值就为设置完成后进行保存, 在弹出的选择名称对话框中, 输入该表的名称 Stu, 然后单击确定按钮, 如图 3-10 所示

12 58 第 3 章表的创建与管理图 3-9 新建表的结构图 3-10 输入表的名称创建约束在关系型数据库管理系统中, 以表的形式存储数据, 表是行和列的交集保证数据一致性和完整性的类型有 3 种 : 域完整性实体完整性和引用完整性, 如图 3-11 所示为了避免数据出现冗余, 要求表的每一行数据都具有唯一的标识符, 即实体完整性例如, 在 Stu 表中存放所有学生的相关信息时, 要求每个学生的记录都要存在, 并且只能存在一次学

13 3.3 创建数据表 59 图 3-11 数据完整性生中可能出现同名的学生, 为了能够区别他们, 即唯一地标识他们, 每位学生都有不重复的学号在前面的章节中, 将 StuID 列设置为标识列, 保证该列可以唯一地标识每条记录, 就是实现实体完整性的一种方法在 SQL Server 2000 中, 还可以使用主键和唯一约束保证实体完整性表中列的值要求符合特定的条件, 即域 ( 或列 ) 完整性检查某个字段是否允许有空值, 是否符合限制条件等都是在 SQL Server 2000 中实现域完整性约束的方法例如, 学生的成绩应在 0 ~100 分之间, 超出这个取值范围的成绩是非法的从逻辑上来说, 只有 Stu 表中的学生才能在 Scores( 成绩 ) 表中拥有相应的信息引用完整性可确保相关联表之间数据的一致性使用主键表 ( 被引用表 ) 中的数据限制外键表 ( 引用表 ) 中的数据, 即所引用的数据必须存在 Stu 表中无信息的学生不能在 Scores 表中获得成绩输入该列的数据必须符合约束的定义, 若违反约束, 该语句将会终止在 SQL Server 2000 中, 提供如表 3-4 所示的约束实现强制数据完整性表 3-4 约束类型完整性类型约束类型说明实体完整性域完整性引用完整性 NOT NULL PRIMARY KEY UNIQUE DEFAULT CHECK FOREIGN KEY FOREIGN KEY CHECK 该字段的值不允许为空值该字段或字段组合的值可以唯一标识表中的每一行该字段或字段组合的值是唯一的当用户没有给该字段输入值时, 将默认值作为该字段的值指定该字段可接受的值使用另一个表的数据, 限定可接受的值检查所引用的外部数据是否存在根据同一表中其他列的值, 指定该字段可接受的值 1.NOT NULL 约束 NOT NULL 约束指定某字段的值是否可以存在空值打开企业管理器中设计表结构的窗口

14 60 第 3 章表的创建与管理 ( 见图 3-9), 在允许空列中相应的行单击出现, 表示该字段允许为空 ; 再次单击, 符号消失, 表示该列不允许为空, 向表中输入数据时, 必须为该字段输入数据, 否则不能插入记录 2.PRIMARY KEY( 主键约束 ) 为了能够唯一标识表中的每一行数据, 需要在表中建立主键主键可以是一个字段, 也可以是一组字段 ( 几个字段的组合 ) 每一个表中都应该有主键, 并且只能有一个主键建立主键的字段值必须是唯一的, 并且不能包含空值 ; 若是由几个字段共同构成主键, 则组合值中无重复值在企业管理器中打开设计表结构的窗口, 选择将被定义为主键的行, 单击鼠标右键, 从弹出的快捷菜单中选择设置主键命令 ( 见图 3-12), 在该列前方出现标志, 表示主键设置完成在选择好需要创建主键的列后, 单击工具栏中的按钮, 也可创建主键若该表的主键为两字段的组合, 若两个字段连续则按住 Shift 键, 若不连续则按住 Ctrl 键进行选择然后, 单击鼠标右键选择设置主键命令或者单击工具栏中的按钮均可图 3-12 设置主键约束 3.UNQIUE( 唯一性约束 ) 唯一性约束确保非主键列上无重复记录唯一性约束可以创建在一个列上, 也可以创建在多个列上, 确保列的组合值的唯一性在一个表中可以有多个唯一性约束, 并且在创建了唯一性约束的列值中可以包含一个空值在企业管理器中打开设计表结构的窗口, 单击鼠标右键, 从弹出的快捷菜单中选择索引 / 键命令, 打开如图 3-13 所示的窗口, 单击新建按钮, 选择要创建唯一约束的列或多个列, 选

15 3.3 创建数据表 61 中创建 UNQIUE 复选框及约束选项, 单击确定按钮完成设置图 3-13 创建唯一约束 4.DEFALUT( 默认值约束 ) 当一个字段指定了默认值后, 如果用户未向该字段输入数据, 则将默认值作为该字段的内容如果用户向该字段输入值, 则该字段的内容为用户输入的值该约束可用于需要大量输入重复记录的字段, 以减少输入量例如, 若某学校的学生来源主要是 XXX 市, 则可将籍贯字段的默认值设置为 XXX ; 若为本市学生, 则不输入籍贯字段内容 ; 若为外地学生, 则输入其相应的籍贯在企业管理器中打开设计表结构的窗口, 如图 3-14 所示, 选择要设置默认值的字段, 在下方的默认值栏中输入相应的值判断某字段值的内容时, 应考虑是否可以为空, 是否设立默认值, 用户是否输入等瞯只要用户输入, 最后该字段的值就为用户输入的内容瞯如果用户未输入, 则最后该字段的值为默认值瞯如果用户未输入, 也未设置默认值, 若该字段能为空, 则最后该字段的值为空 ; 若该字段不能为空, 则将出现错误, 不能插入记录 5.CHECK( 检查约束 ) 检查约束用来限定特定列上可以接受的值可以在一个字段上创建多个检查约束, 也可以在同一个表的多个字段上建立一个检查约束检查约束是通过定义表达式来实现的, 输入的数据要符合表达式, 使其成立表达式中需包含检查约束中限制的字段的名称, 例如对于学生的成绩, 应在 0 ~100 之间, 限制表达式为 [Score] >=0 and [ Score] <=100 在企业管理器中打开设计表结构的窗口, 单击鼠标右键, 从弹出的快捷菜单中选择 CHECK 约束命令在打开的属性窗口中, 选择 CHECK 约束选项卡, 如图 3-15 所示单击新

16 62 第 3 章表的创建与管理图 3-14 创建默认值约束图 3-15 创建检查约束

17 3.3 创建数据表 63 建按钮, 输入约束的名称, 也可以使用系统的命名在约束表达式文本框中, 输入限制表达式 :( [Score] >=0 and [Score] <=100) 选中创建中检查现存数据复选框, 将检查已在该表中的数据是否符合该约束 ; 选中对复制强制约束复选框将检查使用复制操作添加进入该表中的数据是否符合该约束, 只有符合者才能插入该表 ; 选中对 INSERT 和 UPDATE 强制约束复选框, 将对插入 ( INSERT) 和更新 ( UP- DATE) 该表数据时检查是否符合检查约束 6.FOREIGN KEY( 外键约束 ) 在关系型数据库中, 表与表之间是相互关联的, 为了确保域完整性和引用完整性,SQL Server 2000 提供外键约束外键约束可确保外键表 ( 引用表 ) 中的数据在主键表 ( 被引用表 ) 中存在例如, 在 Dep( 院系表 ) 中有 5 个系, 如图 3-16 所示此时, 在 Stu( 学生表 ) 中 DepID( 院系编号 ) 字段的值应为 Dep( 院系表 ) 中 DepID 字段中的某个值, 即学生必定是本校 5 个系中某个系的学生若在 Stu( 学生表 ) 建立外键约束, 引用 Dep( 院系表 ) 的 DepID 字段添加一名学生, 其 DepID 字段值为 1007, 将不能添加, 因为违反了外键约束图 3-16 Dep 表在企业管理器中打开设计表结构的窗口, 单击鼠标右键, 从弹出的快捷菜单中选择关系命令, 在属性窗口的关系选项卡中单击新建按钮, 可在关系名文本框中输入自命名的外键约束名称, 也可使用系统命名, 如图 3-17 所示其中, 主键表指限制另一表中值使用范围的表 ; 此处的主键指用来限定的字段, 该字段应有主键约束或唯一性约束外键表指建立外键约束的表, 为被限定的表 ; 外键指外键表中被限定的字段主键字段与外键字段在逻辑上应指同一种属性, 数据类型相匹配, 长度相等在主键表中选择 Stu 表, 外键表中选定 Dep 表 ; 主键表中相应的字段选择 DepID, 外键表中对应的字段选择 DepID 选中创建中检查现存数据复选框, 将检查已在该表中的数据是否符合该约束 ; 选中对复制强制关系复选框, 将对使用复制操作添加进入该表中的数据进行检查是否符合该约束, 若符合者才能插入该表 ; 选中对 INSERT 和 UPDATE 强制关系复选框, 将出现级联更新相关字段和级联删除相关的记录两个复选框级联更新相关字段表示当修改主键表中的主键字段时, 外键表中相应的外键字段也相应地做出修改级联删除相关的记录表示当删除主键表中的记录时, 外键表中相应的记录也将被删除完成设置后, 单击确定按钮

18 64 第 3 章表的创建与管理图 3-17 创建外键用 T -SQL 语句创建表除了可以使用企业管理器创建表之外, 也可以使用 T -SQL 语句在查询分析器中创建表创建表的语句 CREATE TABLE 的语法格式如下 : CREATE TABLE table_name ( column_name data_type [ IDENTITY( seed,increment) <column_constraint >,[,...n] <table_constraint >] ) 其中 : <column_constraint >:: =[ CONSTRAINT constraint_name]{ [ NOT NULL NULL] [{ PRIMARY KEY UNIQUE} [ DEFAULT constant_expression] ] [[ FOREIGN KEY] REFERENCES ref_table [( ref_column)] CHECK search _condi- tions] } <table_constraint >:: =CONSTRAINT constraint_name { [NOT NULL NULL] [{PRIMARY KEY UNIQUE(column_name [,...n]) } [ DEFAULT constant_expression FOR column_name] ] [[ FOREIGN KEY column_name [,...n]] REFERENCES ref_table [( ref_column)] CHECK search_conditions ] } 表的创建包含表结构和约束的创建使用 CREATE TABLE 语句, 可以同时创建表的结构和

19 3.3 创建数据表 65 约束其中, 方括号 [ ] 包含的内容表示可选可以先创建表的结构后, 再添加约束瞯 table_name: 要被创建的表的名称瞯 column_name: 表中的字段名称瞯 data_type: 被定义的字段的数据类型瞯 IDENTITY( seed,increment) :IDENTITY 表示设置标识列, 分别写出两个参数标识种子 ( seed) 和标识递增量 (increment) 瞯 <column_constraint >: 列约束当约束只与某个字段有关时, 使用 <column_constraint > 语法创建约束瞯 <table_constraint >: 表约束当约束与表中几个字段相关时, 使用 <table_constraint > 语法创建约束关于列约束和表约束的具体意义和使用方法, 将在后面的章节中详细介绍例 3-3 根据表 3-5 使用 T -SQL 语句创建 Stu 表, 并为相应的字段建立主键外键及默认值约束表 3-5 Stu 表列名数据类型是否为空说明 StuID( 学号 ) int 否主键标识列标识种子标识增量 1 StuName( 姓名 ) char(10) 否 Sex( 性别 ) char(2) 是 Birthday( 生日 ) datetime 是 Phone( 联系电话 ) phone(char(15)) 是用户自定义数据类型 Class( 班级 ) char(16) 是 DepID( 系号 ) char(6) 否外键约束, 被引用表 Dep, 字段 DepID Remark( 备注 ) varchar(50) 是默认值暂无内容 1 字段 Phone 的数据类型是一个用户自定义数据类型, 在创建表之前先创建用户自定义数据类型 USE students EXEC sp_addtype Phone, char(15),null 2 创建表 Stu, 用户自定义数据类型的使用与系统数据类型的使用相同, 直接写出类型名称 CREATE TABLE Stu ( StuID int IDENTITY( ,1) PRIMARY KEY, StuName char(10) NOT NULL, Sex char(2) NULL, Birthday datetime NULL,

20 66 第 3 章表的创建与管理 Phone phone NULL, Class char(16) NULL, DepID char(6) NOT NULL REFERENCES Dep( DepID), Remark varchar(50) DEFAULT 暂无内容 ) 例 3-4 根据表 3-6 使用 T -SQL 语句创建表 Course 表 3-6 Course 表列名数据类型是否为空说明 CouID( 课程号 ) char(6) 否检查约束, 以 A B C 开头, 分别表示不同类型的课程 CouName( 课程名称 ) char(20) 是唯一约束 TeacherName( 教师姓名 ) char(10) 是 USE students CREATE TABLE Course (CouID char(6)check(couid LIKE [ ABC] % ), CouName char(20) NULL UNIQUE, TeacherName char(10)) 上面各个例子所创建的约束都是只与一个字段有关, 下面的例子将创建与几个字段有关的约束例 3-5 根据表 3-7 使用 T -SQL 语句创建表 Scores 表 3-7 Scores 表列名数据类型是否为空说明 StuID( 学号 ) int 否 Term( 学期 ) char(1) 是 CouID( 课程号 ) char(6) 否 Score( 成绩 ) tingint 是 StuID 与 CouID 组合作为主键 ; StuID Term 和 CouID 建立唯一约束 ; Score( 分数 )0 ~100 之间 ; 建立外键约束, 被引用表 Stu(StuID) USE students CREATE TABLE Scores (StuID int, Term char(1), CouID char(6), Score tinyint CHECK( Score >=0 and Score <=100),

21 3.4 管理表 67 CONSTRAINT pk_sc PRIMARY KEY( StuID,CouID), CONSTRAINT un_stc UNIQUE( StuID,Term,CouID), CONSTRAINT fk_ss FOREIGN KEY( StuID) REFERENCES Stu( StuID) ) 3.4 管理表在企业管理器中查看和修改表在创建好表之后, 如果需要查看数据库中某表的定义, 可打开企业管理器, 找到数据库, 选择对象表, 在右侧窗格的列表中, 双击需要查看的表, 打开表属性对话框, 如图 3-18 所示从中可以看到该表的名称所有者创建时间及表中各字段的名称数据类型能否为空默认值等属性图 3-18 表属性对话框若需要修改表的结构, 则选中需修改的表, 单击鼠标右键, 从弹出的快捷菜单中选择设计表命令, 如图 3-19 所示此后, 将打开表的设计窗口, 该窗口与新建表时的设计窗口相同, 选择需要修改的相应的项目进行修改即可完成后, 保存退出可以修改列名数据类型是否为空默认值标识列等, 也可以添加或删除字段若要修改表的名称, 可以单击鼠标右键, 从弹出的快捷菜单中选择重命名命令但此项修改将导致引用该表的对象不可用

22 68 第 3 章表的创建与管理图 3-19 修改表若要修改表中的约束, 可打开表的设计窗口, 在空白处单击鼠标右键, 从弹出的快捷菜单中选择索引 / 键命令可以修改删除添加唯一约束, 选择关系命令可修改删除添加外键约束, 选择 CHECK 约束命令可修改删除添加检查约束, 如图 3-20 所示用 T -SQL 语句查看和修改表在使用查询分析器编写 T -SQL 语句时, 如果需要查看表的结构, 可以在查询分析器中展开对象浏览器的树状目录, 然后在展开数据库中相应的表, 就可以在列目录下看到相应的列名数据类型等信息, 如图 3-21 所示若要详细查看, 可使用 sp_help 存储过程该存储过程可以显示表对象的详细信息, 包括表的所有者创建日期列名数据类型标识列和约束等该存储过程的语法如下 : EXEC sp_help =] name] 在查询分析器中可以使用该存储过程查看 Stu 表的信息及其结果, 如图 3-21 所示使用 ALTER TABLE 语句可以修改表 ALTER TABLE 语句的部分语法格式如下 : ALTER TABLE table_name {[ ALTER COLUMN column_name new_data_type] [ ADD <column_definition >] [ DROP COLUMN column_name]}

23 3.4 管理表 69 图 3-20 修改约束图 3-21 查询分析器查看表

24 70 第 3 章表的创建与管理其中,table_name 为表名 ;ALTER COLUMN 修改列,column_name 为列名,new_data_type 为新的数据类型 ;ADD 添加列, <column_definition > 为列属性, 包括列名数据类型和约束 ;DROP COL- UMN 删除列例 3-6 为表 Stu 添加一列 Address, 数据类型为 varchar(60), 长度为 60, 允许为空 USE students ALTER TABLE Stu ADD Address varchar(60) NULL 例 3-7 修改表 Stu 的列 Address, 将其数据类型改为 nvarchar(40) USE students ALTER TABLE Stu ALTER COLUMN Address nvarchar(40) 例 3-8 删除 Stu 表中的 Address 列 USE students ALTER TABLE Stu DROP COLUMN Address 若要修改表中的约束, 仍使用 ALTER TABLE 语句, 语法如下 : ALTER TABLE table_name ADD CONSTRAINT constraint_name { PRIMARY KEY UNIQUE( column_name [,... n]) } [ DEFAULT constant_expression FOR column_name] ] CHECK logical_ex- pression ] [[FOREIGN KEY column_name [,...n] ]REFERENCES ref_table [( ref_column) ] 其中,constraint_name 为约束名,constant_expression 为默认值,logical_expression 为检查约束表达式,ref_table 为引用表,ref_column 为引用字段例 3-9 为 Stu 表中的 Birthday 字段添加默认值约束, 将默认值设为 USE students ALTER TABLE Stu ADD CONSTRAINT df_bd DEFAULT FOR Birthday 例 3-10 students 数据库中已创建完成表 Dep( 见图 3-22), 现需要为其添加一个唯一约束, 系主任需要有自己的电话号码, 不能与他人共用 USE students ALTER TABLE Dep ADD CONSTRAINT qu_dp UNIQUE( Phone) 例 3-11 Scores 表中的字段 CouID 的值必须是 Course 表中 CouID 字段中存在的值, 为

25 3.5 删除表 71 Scores 表建立外键约束 USE students ALTER TABLE Scores ADD CONSTRAINT fk_sc FOREIGN KEY( CouID) REFERENCES Course( CouID) 图 3-22 Dep 表若要删除某个表中创建的约束, 语法格式如下 : ALTER TABLE 表名 DROP CONSTRAINT 约束名例 3-12 删除上例中创建的约束 USE students ALTER TABLE Scores DROP CONSTRAINT fk_sc 3.5 删除表对于一个已经不需要的表, 可以从数据库中将其删除删除后表的结构约束表中的数据

26 72 第 3 章表的创建与管理以及索引等全部从数据库中永久删除, 不可恢复, 因此需谨慎对待删除操作在企业管理器中, 选中要删除的表, 单击鼠标右键, 从弹出的快捷菜单中选择删除命令在如图 3-23 所示的除去对象对话框中, 单击显示相关性按钮查看相关性, 若无对象依附于该表, 则可单击全部除去按钮删除该表图 3-23 删除表使用 DROP TABLE 语句可以删除表, 语法格式如下 : DROP TABLE 表名例 3-13 删除表 Stu USE students DROP TABLE Stu 3.6 插入修改和删除记录在完成表的创建后, 就可以按照设计将数据存放到表中在企业管理器中, 选择要添加数据的表, 单击鼠标右键, 从弹出的快捷菜单中选择返回所有行 ( 见图 3-24), 此时将出现一个只有字段名称的空表将光标定位到起始位置, 逐行输入数据即可需要注意的是, 定义为标识列的字段, 用户不能输入数据, 由系统自动生成若要插入一条记录, 可将光标定位到最后一行, 该行为空白行, 输入数据即可若要修改某条记录, 可将光标定位到需要修改的字段, 重新输入该字段的所有内容若要删除一条记录, 可单击该行最左端的行选择器, 选中整行, 然后单击鼠标右键, 选择删除命令即可, 如图 3-24 所示

27 习题 73 图 3-24 数据管理本章小结本章主要介绍了 SQL Server 2000 中数据类型及表的创建与管理数据类型分为系统数据类型和用户自定义数据类型根据数据的不同特点, 选择不同的数据类型创建用户自定义数据类型可使用系统存储过程 sp_addtpye 无论是哪种数据类型都可以直接在定义表时使用创建及管理表可以使用企业管理器, 也可以在查询分析器中使用 T -SQL 语句为保证数据的完整性,SQL Server 2000 中使用 NOT NULL PRIMARY KEY UNIQUE CHECK DEFALUT FOREIGN KEY 等约束来实现创建表的 T -SQL 语句为 CREATE TABLE, 修改表的语句为 ALTER TABLE, 删除表的语句为 DROP TA- BLE 在企业管理器中插入修改及删除数据的方法习题 1 畅下列符合 decimal(7,5) 的定义 A B C D 畅某字段的数据类型为 char(8), 下列字符串数据库占用字节 A.3 B.6 C.8 D.16 3 畅某字段的数据类型为 varchar(8), 下列字符串数据库占用字节 A.3 B.6 C.8 D.16 4 畅某字段的数据类型为 nchar(8), 下列字符串数据库占用字节 A.3 B.6 C.8 D.16 5 畅用户自定义数据类型是由用户根据需要自己定义的数据类型, 与系统的数据类型无关, 是否正确? 为什么?

28 74 第 3 章表的创建与管理 6 畅用户自定义数据类型现已定义为某个字段的数据类型, 则该用户自定义数据类型不能被删除, 是否正确? 为什么? 7 畅表 Teacher 中 TeaID 字段为标识列, 定义为 IDENTITY(00001,1) 第 5 个插入该表中的记录 TeaID 字段的值为 8 畅某个字段不允许为空, 设置默认值为 33579, 用户未输入, 则该字段的值为 9 畅若要限制学生的性别字段可接受的值为男或女, 则应选择约束 10 畅创建表的 T -SQL 语句为操作训练实验三创建表实验目的 : 瞯掌握使用企业管理器创建表及约束的方法瞯掌握使用查询分析器书写 T -SQL 语句创建表及约束的方法实验内容 : 1 创建用户自定义数据类型 Phone, 其数据类型为 char(11), 可以为空 2 根据表 3-8 和表 3-9, 使用企业管理器在 students 数据库中创建表及其约束表 3-8 Stu 表列名数据类型是否为空说明 StuID( 学号 ) int 否标识列, 种子 , 增量 1, 主键 StuName( 姓名 ) char(10) 否 Sex( 性别 ) char(2) 是 Birthday( 生日 ) datetime 是 Phone( 联系电话 ) phone(char(11)) 是用户自定义数据类型 Class( 班级 ) char(16) 是 DepID( 系号 ) char(6) 否表 3-9 Course 表列名数据类型是否为空说明 CouID( 课程号 ) char(6) 否检查约束, 以 A B C 开头, 分别表示不同类型的课程, 主键 CouName( 课程名称 ) char(20) 是唯一约束 TeacherName( 教师姓名 ) char(10) 是 3 根据表 3-10 和表 3-11, 在查询分析器中书写 T -SQL 语句创建表及其约束

29 操作训练 75 表 3-10 Scores 表列名数据类型是否为空说明 StuID( 学号 ) int 否 Term( 学期 ) char(1) 是 CouID( 课程号 ) char(6) 否 StuID 与 CouID 组合作为主键 ; Score( 分数 ) 在 0 ~100 之间 ; 建立外键约束, 被引用的表为 Stu(StuID) Score( 成绩 ) tingint 是表 3-11 Dep 表列名数据类型是否为空说明 DepID( 院系编号 ) char(6) 否主键 DepName( 院系名称 ) char(20) 是 Director( 系主任 ) char(10) 是 Director 与 Phone 建立唯一性约束 Phone( 联系电话 ) Phone(char(11)) 是自定义数据类型实验四表的管理实验目的 : 瞯掌握用企业管理器修改表及约束的方法瞯掌握 ALTER TABLE 语句的使用方法瞯掌握企业管理器中插入删除和更新数据的方法实验内容 : 接实验三的内容, 继续完成以下内容 : 1 书写 T -SQL 语句将 Stu 表中 Birthday 字段的数据类型修改为 smalldatetime 2 书写 T -SQL 语句为表 Stu 添加一个 Remark 字段, 数据类型为 varchar(50), 可以为空 3 书写 T -SQL 语句为 Stu 表的 DepID 字段添加外键约束, 被引用的表为 Dep 表, 被引用的字段为 DepID 4 书写 T -SQL 语句为 Stu 表的 Remark 字段添加默认值约束, 其值为暂无内容 5 书写 T -SQL 语句为 Scores 表添加约束,StuID Term 和 CouID 建立唯一约束 6 书写 T -SQL 语句删除 Scores 表中与 Stu 表之间的外键约束 7 使用企业管理器为 Scores 表的 CouID 字段建立外键约束, 被引用的字段为 Course ( CouID), 并选择级联更新相关字段和级联删除相关记录 8 根据图 3-25 图 3-26 图 3-27 和图 3-28, 向实验三中建立的 4 个用户表中添加数据

30 76 第 3 章表的创建与管理 9 删除 Stu 表中的第 10 条记录, 并向该表中任意插入一条记录新插入记录的 StuID 字段的值是什么? 10 删除 Course 表中的第二条记录, 查看 Scores 表中课程编号 (CouID) 为 C002 的记录是否存在, 为什么? 图 3-25 Dep 表图 3-26 Stu 表图 3-27 Course 表

31 操作训练 77 图 3-28 Scores 表

项目 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 项目