第06章 管理数据库表.ppt

1 表操作 创建表增加 删除和修改字段创建 删除和修改约束查看表格 2 创建表 在SQLServer2000中 每个数据库中最多可以创建200万个表 用户创建数据库表时 最多可以定义1024列 也就是可以定义1024个字段 SQLServer2000提供了两种方法创建数据库表 第一种方法是利用企业管理器 EnterpriseManager 创建表 另一种方法是利用Transact SQL语句中的create命令创建表 3 利用企业管理创建表 在EnterpriseManager中 展开指定的服务器和数据库 打开想要创建新表的数据库 用右键单击表对象 从弹出的快捷菜单中选择新建表选项 或者在工具栏中选择图标 就会出现新建表对话框 在该对话框中 可以定义列的以下属性 列名称 数据类型 长度 精度 小数位数 是否允许为空 缺省值 标识列 标识列的初始值 标识列的增量值和是否有行的标识 然后根据提示进行设置 并且保存表 4 常用属性说明 长度 指定字段的长度 即字段所占用的字节数 字段的长度并非越打越好 允许空 指定该字段在表中是否允许空值 空值表示没有输入 并不等于零或零长度的字符串 若指定一列不允许空值 则在相表中写数据时必须在列中输入一个值 否则该行不被接收如数据库 描述 指定字段的注释文本描述 默认值 指定字段的默认值 指在插入记录时 没有指定字段值的情况下 自动使用的默认值 5 精度 指定该字段的位数 对于decimal和numeric类型的字段 可以设置精度属性 小数位数 显示该列值小数点右边能出现的最多数字个数 标识 指定一个字段是否为标识字段 只有bigint int smallint tinyint decimal和numeric可以设置该属性 可能的值是 1 否 不设置该字段为标识字段 2 是 指定该字段为标识字段 表示在插入一个新的数据行时 不必为字段指定数值 系统会根据标识种子和标识递增量自动生成一个字段值 3 是 不适用于复制 类似于第二个 但是若以复制的方式象表中输入数据 系统将不自动生成字段值 6 标识种子 指定标识字段的初始值 标识递增量 指定标识字段的递增值 该选项只使用于其 标识 属性设置为 是 或 是 不适用于复制 的字段 在一个表中 只能定义一个标识字段 公式 指定用于计算字段的公式排序规则 指定当字段值用于对查询结果的记录进行排序时的排序规则 默认情况下使用数据库默认设置 7 向表中添加数据 步骤如下 1 选择要添加数据的表2 选择菜单 操作 打开表 返回所有行 命令 打开数据输入窗口 3 输入新记录 同时可以修改和删除操作 4 保存输入 8 表操作 创建表 同一用户不能建立同一个表名的表 同一表名的表可有多个拥有者 但在使用时 需要在这些表上加上所有者的表名 9 利用create命令创建表 其语法形式如下 CREATETABLE database name owner owner table name column nameAScomputed column expression n ON filegroup DEFAULT TEXTIMAGE ON filegroup DEFAULT column namedata type DEFAULTconstant expression IDENTITY seed increment n 10 database name 用于指定在其中创建表的数据库名称 owner 用于指定新建表的所有者的用户名 table name 用于指定新建的表的名称 column name 用于指定新建表的列的名称 computed column expression 用于指定计算列的列值的表达式 ON filegroup DEFAULT 用于指定存储表的文件组名 如果指定filegroup 则数据表将存储在指定的文件组中 若指定default 或未指定on参数 则数据表将存储在默认文件组中 11 TEXTIMAGE ON 用于指定text ntext和image列的数据存储的文件组 data type 用于指定列的数据类型 DEFAULT 用于指定列的缺省值 constant expression 用于指定列的缺省值的常量表达式 IDENTITY 用于指定列为标识列 Seed 用于指定标识列的初始值 Increment 用于指定标识列的增量值 column constraint和table constraint 用于指定列约束和表约束 12 例 创建一个雇员信息表 其SQL语句的程序清单如下 CREATETABLEemployee numberintnotnull namevarchar 20 NOTNULL sexchar 2 NULL birthdaydatetimenull hire datedatetimeNOTNULLDEFAULT getdate professional titlevarchar 10 null salarymoneynull memontextnull 13 约束 主键约束唯一性约束外键约束核查约束Null约束和default约束 14 在企业管理器中设置主键约束 主键可以用来强制一个字段或多个字段组合值的唯一性 且不允许该字段值为空值 具体步骤如下 1 选中需要设置的表节点 2 在该表节点处 点击右键 并选择 设计表 选项 3 选中需要设置主键的字段 然后点击菜单上的设置主键按钮 即可 在一个表中只能设置一个主键约束 但可以将包含多个字段的字段组合设置为主键 设置时需要按住ctrl键将多个字段选中 然后按照和设置单字段主键相同的方法设置即可 取消方法与设置类似 15 在T SQL中设置主键约束 为数据表创建主键约束的句法如下 constraintconstraint name Primarykey clustered notclustered 字段名 n 其中 constraint name约束名称 若缺省 系统将会自动为创建的约束命名 CLUSTERED NONCLUSTERED 用于指定约束的类型 即聚集约束或非聚集约束 CLUSTERED为默认值 column name 用于指定主键的列名 主键最多由16个列组成 16 主键约束分为字段级约束和表级约束 字段级约束 为某一个字段设置约束 在设置字段级约束时 只需将创建主键约束的语句添加到该字段的定义子句后面 例 Createtableproject 项目编号intconstraintpk pnoprimarykey 项目名称varchar 40 17 表级约束 指将包含多个字段的字段组合设置为主键 设置表级约束时 需要将创建主键约束的语句添加到各个字段定义语句的后面 并在该语句后面的括号中写入字段组合包含的所有字段 字段之间用逗号分隔 例如 Createtableproject 项目编号int 项目名称varchar 40 Constraintpk pno pnameprimarykey 项目编号 项目名称 18 唯一性约束 在一个表中只能设置一个主键约束 如何限制非主键字段输入值的唯一性呢 唯一性约束的设置可以确保在非主关键字段或字段组合中不输入重复值 唯一性约束和主键约束的区别 1 在一个表中只能定义一个主键约束 但可以定义多个唯一性约束 2 允许空值的字段上不能定义主键约束 但可以定义唯一性约束 19 在企业管理器中设置唯一性约束 步骤如下 1 为设置唯一性约束的表打开表设计器 选择工具栏中的 表和索引属性 按钮 打开表的属性窗口 2 选择 索引 键 选项 3 选择 新建 命令 为该表创建新的索引 此时系统分配的名称出现在 索引名 框中 4 在 列名 下展开字段的列表 选择需要附加约束的字段 若要将约束附加到多个字段 在后续行中选择其它的字段 5 选择 创建unique 复选项 6 选择 约束选项 将在该字段上同时创建一个唯一性索引 7 保存表 20 唯一性约束的删除 若想删除唯一性约束 可以在 索引 键 选项中选择索引名 然后选择 删除 命令 21 在T SQL中设置唯一性约束 设置唯一性约束的句法如下 CONSTRAINTconstraint name UNIQUE CLUSTERED NONCLUSTERED 字段名 n 22 例如 创建一个名为project的表 该表包含二个字段 项目编号 和 项目名称 将字段 项目编号 设置为主键 主键约束名为pk Pno 为字段 项目名称 设置唯一性约束 约束名为UN pname Createtableproject 项目编号intconstraintpk pnoprimarykey 项目名称char 20 constraintun pnameunique 23 例 创建一个名为project的表 该表包含3个字段 项目编号 项目名称 和 项目负责人 将字段 项目编号 设置为主键 主键约束名为pk pno 为包含字段 项目名称 和 项目负责人 的组合字段设置唯一性约束 约束名为un pname pm 下面是用来完成这一任务的语句 Createtableproject 项目编号intConstraintpk pnoprimarykey 项目名称char 20 项目负责人char 20 Constraintun pname pmunique 项目名称 项目负责人 24 外键约束 外关键字约束定义了表之间的关系 当一个表中的一个列或多个列的组合和其它表中的主关键字定义相同时 就可以将这些列或列的组合定义为外关键字 并设定它是和哪个表中哪些列相关联 这样 当在定义主关键字约束的表中更新列值时 其它表中有与之相关联的外关键字约束的表中的外关键字列也将被相应地做相同的更新 外关键字约束的作用还体现在 当向含有外关键字的表插入数据时 如果与之相关联的表的列中没有与插入的外关键字列值相同的值时 系统会拒绝插入数据 25 步骤如下 1 打开企业管理器 展开数据库company节点 2 在数据库company的下一级中 右击 关系图 在弹出菜单中选择 新建数据库关系图 命令 3 关闭创建数据库关系图向导 进入数据库关系图创建窗口 4 在数据库关系图创建窗口的工具栏中单击按钮 在关系图上加表 按钮 打开 添加表 窗口 5 在 添加表 窗口中选择主键表employee 并单击 添加 按钮 再选择外键表project 单击 添加 按钮 然后关闭 添加表 窗口 26 6 将鼠标指针移动到employee表中主关键字段 编号 左边的选择栏 按下鼠标左键 并将鼠标指针拖动到project表中字段 负责人编号 上 7 松开鼠标左键 此时弹出 创建关系 对话框 根据需要决定是否选择对话框中的如下选项 8 设置完这些选项以后 单击 确定 按钮 关闭 创建关系 对话框 此时创建数据库关系图窗口中的二个表之间出现一条连接线 表示已经建立了这个关系 9 单击 保存 按钮 在 另存为 窗口中输入关系名 单击 确定 按钮 10 在 保存 对话框中 单击 是 按钮确认对主键表和外键表所作的修改 27 删除外键约束 若想删除该外键约束 可以在关系图中 删除代表要删除的外键约束的关系线 注意 若不删除数据库图中的关系线 即使删除了该关系图 也不会影响设置好的外键约束 因为删除关系图并不对表作相应的修改 28 在T SQL中设置外键约束 定义外关键字约束的语法如下 CONSTRAINTconstraint name FOREIGNKEY REFERENCESref table ref column ONDELETE CASCADE NOACTION ONUPDATE CASCADE NOACTION NOTFORREPLICATION 各参数说明如下 REFERENCES 指定要建立关联的表的信息ref table 指定要建立关联的表的名称ref column 指定要建立关联的表中的相关列的名称 29 ONDELETE CASCADE NOACTION 指定在删除表中数据时 对关联表所做的相关操作 在子表中有数据行与父表中的对应数据行相关联的情况下 如果指定了值CASCADE 则在删除父表数据行时 会将子表中对应的数据行删除 如果指定的是NOACTION 则SQLServer会产生一个错误 并将父表中的删除操作回滚 NOACTION是缺省值ONUPDATE CASCADE NOACTION 指定在更新表中数据时 对关联表所做的相关操作 在子表中有数据行与父表中的对应数据行相关联的情况下 如果指定了值CASCADE 则在更新父表数据行时会将子表中对应的数据行更新 如果指定的是NOACTION 则SQLServer会产生一个错误并将父表中的更新操作回滚 NOACTION是缺省值 NOTFORREPLICATION 指定列的外关键字约束在把从其它表中复制的数据插入到表中时不发生作用 30 例 1 创建一个数据表employees 包含两个字段 员工编号 和 员工姓名 要求将字段 员工编号 设置为主键 并为字段 员工姓名 设置唯一性约束 2 创建另一个数据表project 该表包含三个字段 项目编号 项目名称 和 项目负责人 将字段 项目编号 设置为主键 将字段 项目负责人 设置为外键 其参照字段是表employees中的字段 员工姓名 并设置当删除数据表employees中被引用的记录时 数据表project中的引用记录也随之删除 31 创建数据表employeesCreatetableemployees 员工编号intprimarykey 员工姓名char 20 unique Go 创建数据表projectCreatetableproject 项目编号intprimarykey 项目名称char 20 项目负责人char 20 foreignkeyReferencesemployees 员工姓名 Ondeletecascade go 32 核查约束 核查约束通过检查一个或多个字段的输入值是否符合设定的检查条件来强制数据的完整性 如输入数据不符合核查条件 系统将拒绝这条记录 外键约束与核查约束的区别 外键约束是从另一个表中获得有效数值的列表 而核查约束基于逻辑表达式进行判断 33 利用企业管理器设置核查约束 步骤如下 1 打开企业管理器 展开employee表所在的数据库 并选中表employee 2 单击鼠标右键 在弹出菜单中选择 设计表 选择工具栏中的 表和索引属性 按钮 打开表的属性窗口 3 在属性窗口选择 Check约束 标签 单击标签中 新建 按钮 4 在约束表达式文本窗口中输入一个条件表达式 5 在约束名对话框中输入所建核查约束名 34 6 根据需要决定是否选择下列选项 创建中检查现存数据 将确保根据约束对表中所有创建该约束前存在的数据进行验证 对insert update强制约束 在将数据插入表中或更新表中的数据时 强制该约束 对复制强制约束 在将表复制到另一个数据库中时强制该约束 7 设置完上述选项 关闭 属性 对话框 新建的核查约束将在保存表时予以保存 35 删除核查约束 若想删除一个核查约束 可在 check约束 中选定该约束 然后单击 删除 按钮即可 36 在T SQL中设置核查约束 其语法形式如下 CONSTRAINTconstraint nameCHECK logical expression 其中 logical expression是用于核查的逻辑表达式 37 例 创建一个数据表employees 包含3个字段 员工编号 员工姓名 和 工资 要求将字段 员工编号 设置为主键 为字段 员工姓名 设置唯一性约束 并使 工资 字段的取值范围限定在1000 20000之间 核查约束名为ck es Createtableemployees 员工编号intnotnullprimarykey 员工姓名char 20 unique工资intnotnullConstraintck esCheck 工资 1000and工资 20000 38 Null约束和default约束 用企业管理器设置 在表设计里进行设置 39 用T SQL设置NULL约束 在使用createtable语句创建表时 若没有定义NULL NotNULL约束 则系统默认允许空值 设置方法是 在定义好字段属性后 直接在后面加上NULL或NOTNull即可 例如 Createtablestudent 学号intnotnull 姓名char 8 40 用T SQL设置default约束 定义缺省约束的语法如下CONSTRAINTconstraint nameDEFAULTconstant expression FORcolumn name 例 constraintde order quantitydefault100fororder quantity 41 规则 规则是绑定到字段或用户自定义数据类型 并指定字段可接受哪些数据值的数据库对象 它通常用于执行一些与check约束相同的功能 一个字段可以应用多个check约束 但是却只能应用一个规则 规则以单独的对象创建 然后绑定到字段上 42 利用企业管理器创建规则 步骤如下 1 在企业管理器中展开employee表所在的数据库节点 然后选中该数据库节点下一级菜单中的 规则 节点 并单击鼠标右键 在弹出对话框内选择菜单命令 新建规则 2 在弹出的 规则属性 对话框中的 文本 框中输入正确的条件表达式 注意 规则中不能引用表中的数据字段名以及别的数据对象 在条件表达式中包含一个局部变量 该变量必须以 开始 3 输入完成后 单击 规则属性 对话框中 绑定列 按钮 这时将弹出 将规则绑定到列 对话框 43 4 在 表 下拉列表中选择表employee 然后在 未绑定的列 列表中选择字段 职位 单击 添加 按钮 此时在 绑定列 列表中添加了 职位字段 5 设置完绑定字段后 单击 确定 按钮关闭 将规则绑定到列 对话框 然后再单击 确定 按钮关闭 规则属性 对话框 44 在T SQL中设置规则 句法如下 Createrulerule nameascondition expression其中 rule name 指定新规则的名称 Condition expression 定义规则的条件表达式 规则创建后 就执行sp bindrule来将规则绑定到字段或用户定义数据类型 执行该过程的句法如下 Execsp bindrule rulename rule name objname object name 其中 rulename rule name 由create语句创建的规则名称 objname object name 规则将要绑定到的数据表和字段或是用户定义的数据类型 45 例 创建一个数据表employees 包含3个字段 员工编号 员工姓名 和 工资 要求将字段 员工编号 设置为主键 创建一个规则 规则名为ru salary 将该规则绑定到表employees的字段 工资 上 限定该字段的值为1000 20000之间 Createtableemployees 员工编号intnotnullprimarykey 员工姓名char 20 工资intnotnull Createruleru salaryAs salary 1000and salary 20000GoExecsp bindrule ru alary employees 工资 go 46 默认值 默认值可以帮助处理不包含全部数据表字段的insert操作 若在插入记录时没有指定字段的值 则默认值将指定字段中所使用的值 默认值的使用与规则类似 首先需要创建一个默认值 然后将默认值与特定的表字段或用户自定义数据类型进行绑定 47 用企业管理器设置默认值 步骤如下 1 在企业管理器中展开employee表所在的数据库节点 然后选中该数据库节点下一级菜单中的 默认 节点 并单击鼠标右键 在弹出对话框中选择菜单命令 新建默认 2 在 名称 文本框中输入默认名 在 值 文本框中输入默认取值 3 输入完成后 单击对话框中 绑定列 按钮 这时弹出 将默认值绑定到列 对话框 4 选择表employee 添加字段 职位 到 绑定列 列表中 5 单击 确定 按钮关闭 将默认绑定到列 对话框 然后再单击 确定 按钮关闭 默认属性 对话框 48 在T SQL中设置默认值 语法为 CreateDEFAULTdefaultAsconstant expression其中 default是默认值的名称 Constrant expression为只包含常量值的表达式 该表达式中不能包含任何字段或其它数据库对象的名称 创建默认值后 可以使用sp bindefault将其绑定到字段或用户定义数据类型 执行该操作的sp bindefault句法为 Sp bindefault defname default objname object name 其中 defname default 由createdefault语句创建的默认值名称 objname object name 默认值将要绑定到的数据表和字段的名称或用户定义的数据类型 49 例如 创建规则 将上例中的 工资 字段的默认值设置为1000 规则为de salary Createdefaultde alaryas1000GoExecsp bindefault de salary employees 工资 50 在企业管理器中修改表 重新命名修改数据表结构修改表属性 51 重新命名 步骤如下 1 在企业管理器中展开重新命名的表 2 在希望重新命名的表上单击鼠标右键 在弹出菜单上选择命令 重命名 输入新的表名称 3 按回车键 在弹出的对话框内单击 确定 按钮 52 修改数据表结构 打开希望修改表结构的表设计窗口 可以执行下列操作 1 修改数据表中字段的定义 包括重新设置字段名 字段类型 长度 允许空 描述 默认值 精度 小数位数 标识 公式 排序规则等字段属性 也可以设置和取消一个字段的主键约束 2 插入新字段 在末尾添加和在字段中间插入新字段 3 调整字段顺序4 删除现有字段 53 修改表属性 在表设计窗口中单击 表和索引属性 按钮 打开表属性窗口 在该窗口中可以设置与整个表相关的重要属性 属性窗口包含4个标签 分别是 表 关系 索引 键和check约束选项 下面是表标签中的选项设置 54 选定的表 显示选定的表明 如果在关系图中选定的表不止一个 则只有第一个选定表的名称是可见的 可以展开列表选择其它要检查或修改属性的表 所有者 显示表所有者的名称 所有者名称是SQLServer角色或SQLServer用户 表的标识列 显示由SQLServer用作表标识列的字段 若要更改标识列 从下拉列表中选择即可 表的ROWGUID 行标识符 列 显示由SQLServer用作表的ROWGUID列的字段 若要更改 可从下拉列表中选择 表文件组 指定数据表的数据存放的文件组 默认文件组最初是主文件组 文本文件组 选择要在其中存储选定表的TEXT和image类型的字段的文件组名 默认文件组是主文件组 描述 可以在该字段中输入有关数据表的一些说明文字 55 在T SQL中修改表 修改字段定义添加字段删除字段添加约束删除约束 56 修改字段定义 格式 ALTERTABLEtableALTERCOLUMNcolumn namenew data type precision scale null notnull 其中 table 需要修改的数据表名称Column name 需要修改的字段名称New data type 修改后将要使用的新的数据类型Precision 字段将要使用的新的精度定义 Scale 字段将要使用的新的小数位数 57 例 创建一个名为project的表 该表包含两个字段 项目名称 和 项目编号 将字段 项目名称 从原来的数据类型char 20 更改为varchar 25 再将原来的不可为空属性更改为可以为空 Createtableproject 项目编号intprimarykey 项目名称char 20 notnull 使用sp help查看数据表的定义Execsp helpprojectAltertableprojectAltercolumn项目名称varchar 25 nullExecsp helpprojectgo 58 添加字段 格式 ALTERTABLEtableADD column definition column nameascomputed column expression 其中 table 需要添加字段的数据表名称Column definition 有关添加的字段的定义Column name 添加字段的名称Computed column expression 计算字段表达式 59 例 向刚创建的project表中添加3个字段 分别为 项目负责人 项目难度级别 和 预计工期 要求字段 预计工期 的取值为字段 项目难度级别 的值乘以20 AltertableprojectAdd项目负责人varchar 15 项目难度级别intdefault1 预计工期as项目难度级别 20Execsp helpproject 60 删除字段 格式 ALTERTABLEtableDROPCOLUMNcolumn name n 其中 table需要删除字段的数据表名称Column name 需要删除的字段名称例 删除上面例子中的数据表project中的字段 项目负责人 和 预计工期 AltertableprojectDropcolumn项目负责人 预计工期 61 添加约束 利用altertable命令中的addconstraint子句 可以添加一个或多个约束 语法如下 AlterTABLEtable withcheck withnocheck Addconstraintconstraint nameConstraint definition n 其中 table 需要添加约束的数据表名称 constraint name 需要添加的约束名称 Constraint definition 添加约束的定义 withcheck withnocheck 若选择前者 表示将使用新建的数据表约束对数据表中原有的数据进行检查 若选择后者 表示新建的数据表约束追溯应用于数据表中原有的记录 默认值为前者