第3章数据表的创建和管理.ppt

3 1数据表的创建和管理3 2表数据的管理3 3数据完整性管理 第3章SQLServer数据表 SQLServer2000有两类表 一类是永久表 这类表一旦创建将一直存储在硬盘上 除非被用户删除 另一类是临时表 这类表在用户退出时自动被系统删除 临时表又分为局部临时表与全局临时表 局部临时表的名称以 开头 仅可由创建者本人使用 一旦创建者断开连接 该表将会消失 全局临时表的名称以 开头 创建者在创建后可以由多个授权用户立即使用 一旦最后使用的用户断开连接 该表将会消失 3 1数据表的创建和管理 3 1在企业管理器中创建数据表 每个表都代表一个特定的实体 创建好数据库后 就可以向数据库中添加数据表 数据通常存储在表中 表存储在数据库文件中 任何有相应权限的用户都可以对之进行操作 例 使用企业管理器在数据库Student db创建数据表St info 演示操作 标识列IDENTITY 自动编号 当需要对某一列输入的数据是有规律的顺序编号且保证每个编号惟一时 最好采用IDENTITY 具有IDENTITY属性的列称为标识列 其取值称为标识值 具有如下特点 IDENTITY列的数据类型只能为整型 numeric decimal 当为numeric decimal时 不允许有小数位 当用户向表中插入新记录时 系统将自动在该列添加一个按规定递增 或递减 的数据 该列值由系统自动产生每个表至多有一列具有IDENTITY属性 该列不能为空 不允许具有默认值 不能由用户更新 因此 IDENTITY列常可作为主键使用 使用IDENTITY时 可设置种子 seed 与增量 increment 如果未指定 则取默认值 1 1 当对表中数据进行删除操作后 在标识值之间可能会产生数量不等的差值 3 1 2使用Transact SQL语言创建数据表CREATETABLE database name owner owner table name column namedata type NULL NOTNULL DEFAULTconstant expression IDENTITY seed increment n ONfilegroup DEFAULT TEXTIMAGE ON filegroup 3 2 2利用Transact SQL语言创建表 其中各参数的说明如下 database name 创建新表所属的数据库名称 如果不指定 database name默认为当前数据库 owner 指定新表所有者的用户名 如果不指定 owner默认为当前注册用户名 table name 用于指定新建表的名称 表名必须符合标识符规则 column name 用于指定新建表的字段名 data type 用于指定列的数据类型 DEFAULT 用于指定列的默认值 IDENTITY seed increment 是否为标识列及标识列的种子和增量 ON filegroup DEFAULT 用于指定存储表的文件组名 如果使用了DEFAULT选项 或者省略了ON子句 则新建的表会存储在默认的文件组中 TEXTIMAGE ON 用于指定text ntext和image列的数据存储的文件组 如果表中没有text ntext或image列 则不能使用TEXTIMAGE ON 如果没有指定TEXTIMAGE ON子句 则text ntext和image列的数据将与数据表存储在相同的文件组中 3 2 2利用Transact SQL语言创建表 例3 1 使用查询分析器在数据库Student db中创建Student学生信息表 要求包含S NO 学号 NAME 姓名 AGE 年龄 SEX 性别 信息 其中学号不能为空 1 启动SQLServer服务器 运行查询分析器 并连接到数据库 2 在查询窗口中输入创建表的语句 创建数据表Student的语句如下 CREATETABLEStudent S NOCHAR 7 NOTNULL NAMEVARCHAR 8 AGESMALLINT SEXCHAR 2 3 单击工具栏上的 分析查询 按钮 若结果窗口无错误信息 再单击 执行查询 按钮 将返回 命令已成功完成 的信息 例3 2 使用查询分析器创建Student 1学生信息表 要求包含S NO 学号 NAME 姓名 AGE 年龄 信息 并限制年龄在15到25周岁之间 指定学号为主关键字 创建数据表Student 1的语句如下 CREATETABLEStudent 1 S NOCHAR 7 NOTNULL NAMECHAR 10 AGESMALLINT PRIMARYKEY S NO CHECK AGEBETWEEN15AND25 例3 3 使用查询分析器创建临时表temp student 创建表temp student的语句如下 CREATETABLE temp student 学号SMALLINTNOTNULL 姓名VARCHAR 30 NOTNULL 年龄INTNOTNULL PRIMARYKEY 学号 在Transact SQL中可用CREATETABLE来创建临时表 只要在表名前加 或 符号 其中 表示本地临时表 在当前数据库内使用 表示全局临时表 可在所有数据库内使用 这些表存储在系统数据库tempdb中 它们在与服务器的交互结束时会自动删除 3 1 3使用企业管理器对数据表进行管理 创建数据表之后 可以使用企业管理器或使用Transact SQL语言对数据表和数据表的结构进行操作使用企业管理器管理表使用企业管理器更改数据表名称 使用企业管理器删除数据表 在企业管理器中 打开要修改数据表结构的数据库 右击要进行修改的数据表 从弹出的快捷菜单中选择 设计表 命令 在此窗口中 可以完成增加 删除和修改字段的操作 演示操作 使用ALTERTABLE命令修改表结构语法格式 ALTERTABLEtable name ALTERCOLUMNcolumn name new data type NULL NOTNULL ADDcolumn namedata type NULL DEFAULT DROPCOLUMNcolumn name n 删除数据表可使用DROPTABLE语句语法格式 DROPTABLEtable name参数说明 table 要删除的数据表的名称 注意 1 DROPTABLE不能删除由FOREIGNKEY约束引用的表 必须先删除引用的FOREIGNKEY约束或引用的表 2 在系统表上不能使用DROPTABLE语句 3 4管理数据表中记录 当一个数据表的表结构设计完成后 就可以在数据表中录入数据了 可以使用企业管理器或使用SQL语言中的INSERT语句插入数据 3 4管理数据表中记录 在企业管理器中 打开指定的数据库 右击要录入数据的数据表 从弹出的快捷菜单中选择 打开表 命令 该命令还有三个子命令 返回所有行 用于显示数据表中的所有记录 返回首行 用于显示前N条记录 N可以自己输入 查询 用于查询具体某条记录 演示操作 3 2 2使用Transact SQL语言管理表数据表数据的添加 可使用INSERTINTO语句 语法格式 INSERT INTO table view 字段名1 字段名2 Values 表达式1 表达式2 参数说明 INTO 一个可选的关键字 可以将它用在INSERT和目标表之间 Table view 要插入数据的表名或视图名 表达式1 表达式2 与字段名列表相对应的字段的值 字符型和日期型值插入时要加单引号 使用INSERT语句一次只能插入一行数据 如果某一字段的值违反了约束或规则 或者它有与数据类型不兼容的值 该语句的操作就会失败 例3 13 假设数据库Student db中已经创建好了课程信息数据表C Info 试将新课程记录 9720044 网络技术与应用 选修 3 添加到课程信息数据表C Info中 添加新课程记录的语句如下 INSERTINTOC InfoVALUES 9720044 网络技术与应用 选修 3 null 注意 在插入数据时 对于允许为空的列可使用NULL插入空值 对于具有默认值的列可使用DEFAULT插入默认值 例3 14 在C Info表中添加一条新记录 课程类别和课程说明暂缺 使用如下语句 注意 提供值的个数与表中列的个数不一致 必须列出列名 提供值的个数与表中列的个数相同 可省略列名 此时要求值的顺序与定义的字段顺序一致 C Info中的课程类别和课程说明列必须允许为NULL 因为系统实际插入的数据为 9720045 Web开发技术 null 2 null INSERTINTOC Info C NO C Name C Credit VALUES 9720045 Web开发技术 2 3 2 2使用Transact SQL语言管理表数据表数据修改 使用UPDATE语句语法格式 UPDATE table name view name SET column name expression DEFAULT NULL n where 说明 table name 要更新数据的表名 column name 要更新数据的列名 expression DEFAULT NULL 更新后的数据值 其值可为表达式 expression 的值 或默认值 DEFAULT 或NULL where 要更新数据的记录 只对表中满足该条件的记录进行更新 例3 15 将数据表C Info中的所有学分加1 语句如下 例3 16 将数据表C Info中课程号为 9710011 的学分减1 课程类型改为 必修 语句如下 UPDATEC InfoSETC Credit C Credit 1 UPDATEC InfoSETC Credit C Credit 1 c type 必修 WHEREC NO 9710011 3 1 4数据表中当确定有些记录不需要时 就可以将其删除 其语法格式 DELETEFROM table name view name WHERE 参数说明 table name view name 要删除行的表名或视图名 WHERE 要删除的记录 满足条件的记录 例3 17 在student表中删除所有学生记录 但保留数据表结构 这是无条件的删除记录 使用语句如下 DELETEFROMstudent例3 18 在C Info表中删除课程号为 9720045 的记录 这是有条件的删除记录 使用语句如下 DELETEFROMC InfoWHEREC NO 9720045 使用数据库关系图创建关系数据库关系图是以图形方式显示部分或全部数据库结构 将表和表间关系以及其他对象以图形方式表现出来 数据库关系图工具并不是只能提供创建关系图的功能 还可用来创建和修改表 列 关系 键 索引和约束 可创建一个或更多的关系图 以显示数据库中的部分或全部表 列 键和关系 数据完整性是指数据的正确性 一致性和有效性 是衡量数据库中数据质量好坏的重要标准 正确性 指数据的合法性 取值与数据类型要一致 有效性 表内数据不相互矛盾 在所定义的范围之内 一致性 表间数据不相互矛盾 指数据的关联性不被破坏 当用户用INSERT DELETE或UPDATE语句修改数据库内容时 数据的完整性就可能会遭到破坏 为了解决这些问题 保证数据的完整性 SQLServer提供了实施数据完整性的方法 约束 缺省 规则等 如 字段值不得重重 字段值必须介于1到100之间 甲数据表的A字段值必须参照乙数据表的B字段等 3 3 1数据完整性概述 数据的完整性大致分为以下几种类型 实体完整性 要求数据表中的每一行在表中是唯一的实体 不存在重复的数据行 域完整性 要求某一列的任何值都是有效的数据类型与取值范围引用完整性 也叫参照完整性 指对数据库进行添加 删除 修改数据时 用来维护表间数据的一致性 用户定义完整性 用于实现用户特殊要求的数据规则或格式 实体完整性的设计与实现实体完整性 要求数据表中的每一行在表中是唯一的实体 采用以下方法实现 主键约束 PRIMARYKEY 是最重要的一种约束 通过定义主键 唯一地确定表中的每一条记录 定义主键的列不允许为空 且text image类型的列不能定义为主键 P18T2 1 特性 非空性 唯一性 IDENTITY属性 在数据表中创建一个标识列 该列的数据由系统自动生成 且不会有重复的数据 唯一性约束 唯一性约束 用来限制不受主键约束的列上的数据的唯一性 一个表上可定义多个UNIQUE约束 惟一约束与主键约束的主要区别在于 唯一约束用于非主键列 唯一约束允许NULL值 而主键不允许 域完整性的设计与实现域完整性要求表中某一列的任何值都是有效的数据类型与取值范围 采用以下方法实现 定义字段的数据类型 设置字段非空 检查 CHECK 约束 用来指定某列可取值的范围 对输入的数据值进行检查 满足条件的数据可以保存在数据表中 对于不满足条件的数据则拒绝接受 P18T2 1 定义默认值 DEFAULT 用于给表中指定列赋予一个常量值 默认值 默认值有两种设置方式 默认值约束与默认对象 规则对象 是对存储在数据表中的数据值的规定和限制 规则的功能与CHECK约束的功能相似 规则的好处是只创建一次 但可绑定到多个字段上 默认值DEFAULT当向表中插入一行数据时 如果用户没有明确给出该列的值 SQLServer自动为该列按如下次序取值 若该字段定义有默认值 则系统将默认值填入该字段若该字段定义没有默认值 但允许空 则填入空值若该字段定义没有默认值 又不允许空 则报错 显然 对于非空字段 默认值极为重要 默认值有两种设置方式 默认值约束与默认对象 默认值约束其作用范围仅限于一个表 删除表则删除默认值约束 默认值对象作为数据库对象单独存在于数据库中 需要时可以绑定到任何一个表中的列 也可以绑定到用户自定义数据类型上 规则对象 P21T三 3 4 约束是作用于表 离开了表 约束将不再存在 除了约束外 SQLServer还提供了一个与CHECK约束功能相似数据库对象用以维护数据库的完整性 即规则 规则是对存储在数据表中的数据值的规定和限制 其作用与CHECK约束的功能相似 但规则作为一个独立的数据库对象存储在数据库中 规则的优点是仅创建一次就可以绑定到数据库的多个表的列上 使同一数据库中所有表的不同列共享 参照完整性的设计与实现设置参照完整性是为了保证数据表之间的数据的一致性 通过在两个数据表的主键和外键之间或者唯一键和外键之间建立外键 FOREIGN 约束来实现 P18T2 2 外键约束用于与其他表 称为参照表 中的列 称为参照列 建立连接 设置了参照的完整性可实现以下功能 不能在从表中插入主表中不存在的数据值 如果从表中有对应的数据值 则不能修改主表中对应值 除非设置成级联修改 如果从表中有对应的数据值 则不能删除主表中对应值 除非设置成级联删除 约束约束