第4讲表管理.ppt

第4讲管理表 目录 4 1表的概述4 2列数据类型4 3列的其他属性4 4维护数据表4 5表数据完整性4 6表的索引与视图4 7扩展练习 在使用数据库的过程中 接触最多的就是数据库中的表 表是数据存储的地方 是一种结构化的文件 可用来存储某种特定类型的数据 是数据库中最重要的部分 管理好表也就管理好了数据库 其他的数据 例如 视图 索引等都是依附于表对象而存在的 本章将详细介绍数据库中基于表的各种操作 创建 修改 删除等 数据完整性以及表的索引和视图等内容 4 本章知识要点 了解表的类型及创建熟悉各种数据类型掌握列的各种属性掌握修改表和列的操作熟悉保持数据完整性的各种约束了解表的索引和视图 4 1表的概述 表是数据库中最重要的数据库对象 是数据库的基本组成部分 是储存数据的逻辑载体 关系型数据库中的表都是二维的 表的一列称为一个字段 表的一行称为一个记录 SQLServer2000中 一个表中最多允许1024列 表的行数没有限制 与磁盘存储空间有关 关系数据库的理论基础是关系模型关系模型的结构是一张二维表结构 关系模型与SQLServer表的对应 根据数据库逻辑设计的要求确定 需要什么样的表 各表有哪些数据 数据类型 表的各列 每一列的数据类型 列宽 是否允许为空 是否需要索引 类型 哪些列是主键 外键 具体地 要考虑很多因素 表的设计 1 因素一 考虑表将要存储哪些数据对象 绘制出ER图 ER图是描述数据库中所有实体以及实体之间关系的图形 是辅助设计关系模型的工具 实际上 表就是关系模型 也对应着模型中的实体 是存储数据的对象 在设计表时 应该综合考虑这些问题 数据库的目的是什么 数据库中将要包含哪些数据 数据库中应该包含多少表 每一个表将要包含哪些数据 表和表之间是否存在关系 如果存在关系 那么存在什么样的关系 对这个因素的深入思考 有助于创建合理 完整的表 2020年2月5日 第9页 实体与记录 2 因素二 考虑表中将要包含的列以及这些列的数据类型 精度等属性 确定了表之后 就要确定表的内容 每一个表包含多个列 每一个列都有一个数据类型 数字数据类型的列还需要确定列的精度和约度 这些都是设计表时必不可少的因素 数字列应该使用数字数据类型 字符列应该使用字符数据类型 日期列应该使用日期数据类型 对于数字列 需要认真考虑其精度和约度 对于字符列 应该考虑其是否使用定长字符列和字符长度 考虑这些因素的目标是 使得表中的列的数量尽可能地少 如果列的数量过多 应该考虑将该表分解成两个表或多个表 2020年2月5日 第11页 3 因素三 考虑列的属性 例如哪些列允许空值 哪些列不允许空值 列允许空值 表示该列可以不包含任何的数据 空值既不是数字0 也不是空字符 而是表示未知 如果允许列包含空值 表示可以不为该列输入具体的数据 如果不允许列包含空值 在输入数据时必须为该列提供数据 例如 在包含订单的orders表中 订单代码 产品代码 客户代码等列不允许空置 但是订单描述列则可以包含空值 考虑这个因素的目标是 尽可能地不使用允许空值的列 因为空值列有可能带来意想不到的查询效果 如果不得不允许某些列为空 那么应该使得这些列的数量最少 2020年2月5日 第12页 4 因素四 考虑表是否使用主键 如果使用则在何处使用主键 主键是唯一确定每一行数据的方式 是一种数据完整性对象 主键往往是一个列或多个列的组合 一个表中最多只能有一个主键 一般地 应该为每一个表都指定主键 借此可以确定行数据的唯一性 2020年2月5日 第13页 5 因素五 考虑是否使用约束 默认值 规则 以及在何处使用这些对象 约束 默认值 规则等都是数据完整性对象 用来确保表中的数据质量 对表中数据的查询操作 只能在满足定义的约束 默认值 规则等条件下 才能执行成功 这些因素的考虑往往与表中数据的商业特性相关 2020年2月5日 第14页 6 因素六 考虑是否使用外键 在何处使用外键 在ER图中 需要绘制出实体之间的关系 在表的设计时 实体之间的关系需要借助主键 外键对来实现 因此 该因素也是确保ER图完整实施的一个重要内容 只有通过这种关系 才能确保表和表之间强制的商业性关系 2020年2月5日 第15页 7 因素七 考虑是否使用索引 在何处使用索引 使用什么样的索引 索引也是一种数据库对象 是加快对表中数据检索的手段 是提高数据库使用效率的一种重要方法 在哪些列上使用索引 在哪些列上不使用索引 是使用聚集索引 还是使用非聚集索引 是否使用全文索引 等等 对这些因素的认真考虑和实现 也是对表质量的更高的要求 2020年2月5日 第16页 SQLServer种创建表的4条限制 1 每个数据库中最多有20亿个表 2 每个表上最多可以创建一个聚集索引和249个非聚集索引 3 每个表最多可以配置1024个字段 4 每条记录最多可以占8060B 但不包括text字段和image字段 4 1 2表的类型普通表又称标准表 数据库中存储数据的表 经常使用 最重要 最基本分区表将数据水平划分成多个单元的表 这些单元可以分散到数据库中的多个文件组里面临时表临时创建不能永久生存系统表主要存储有关SQL服务器的配置 数据库设置 用户和表对象的描述等系统信息 4 1 3见后 4 2列数据类型 1 数值类型2 字符串类型3 二进制类型4 日期时间类型5 货币类型6 文本 图像数据类型7 特殊数据类型8 用户自定义的数据类型9 逻辑型 1 数值类型 整型数据类型 1 bigint 占8字节的存储空间 存储数据范围为 263 263 1 2 int 占4字节的存储空间 存储数据范围为 231 231 1 3 smallint 占2字节的存储空间 存储数据范围为 215 215 1 4 tinyint 占1字节的存储空间 存储数据范围为0 255 实型数据类型 1 decimal p s 小数类型 其中 p为数值总长度 包括小数位数 但不包括小数点 范围1 38 s为小数位数 默认decimal 18 0 2 numeric p s 与decimal p s 等价 3 float n 浮点类型 占8字节的存储空间 4 real 浮点类型 占4字节的存储空间 2 字符串类型 1 char n 定长字符串类型 长度为n n范围为1 8000 默认为char 10 2 text 文本类型 实际也是变长字符串类型 存储长度超过char 8000 的字符串 最大长度231 1 实际上 text类型列中仅存储一个指针 数据本身存储在连接表中 3 varchar n 变长字符串类型 即自动删除字符串尾部空格后存储 长度为n n范围为1 8000 默认为char 50 3 二进制类型 1 binary n 定长二进制类型 占n 4字节的存储空间 n范围为1 8000 默认为binary 50 2 varbinary n 变长二进制类型 占n 4字节的存储空间 n范围为1 8000 默认为binary 50 3 image 大量二进制类型 实际也是变长二进制类型 通常用于以位字符串形式存储图形 最大长度为231 1 约2GB 4 日期时间类型 SQLServer2000的日期时间类型数据同时包含日期和时间信息 没有单独的日期类型或时间类型 1 datetime 日期时间类型 占8字节的存储空间 范围为1753年1月1日 9999年12月31日 2 smalldatetime 日期时间类型 占4字节的存储空间 范围为1900年1月1日 2079年12月31日 日期时间类型常量两端应加单引号 如果只指定日期 则时间默认为午夜12点0分0秒 如果只指定时间 则日期默认为1900年1月1日 如果省略世纪 当年 50默认为20世纪 50默认21世纪 5 货币类型 1 money 货币类型 4位小数 占8字节的存储空间 2 smallmoney 货币类型 4位小数 占4字节的存储空间 货币类型常量应以美元符号 或所定义的金融单位符号作前缀 除了SQLServer2000提供的数据类型外 如果需要 还可以定义新的数据类型 称为 自定义数据类型 6文本 图像类型 1 text 用于存储大量文本数据 其容量理论上为231 1个字节 在实际应用时需要视硬盘的实际空间而定 2 ntext 与text类型相似 不同的是ntext类型采用Unicode标准字符集 因此其理论上容量为230 1个字节 3 image用于存储大量的二进制数据 其理论容量为231 1个字节 通常用来存储图形等对象 在输入数据时同binary数据类型一样 必须在数据前加上字符 0 x 作为二进制标识 7特殊数据类型 1 Timestamp 用于表示SQLServer活动的先后顺序 以二进投影的格式表示 与插入数据或日期和时间没有关系 2 Uniqueidentifier 由十六字节的十六进制数字组成 8逻辑类型 只能存储0或1 当输入0和1以外的值时 系统自动转换为1 通常存储逻辑量 表示真与假 9 Unicode字符数据 unicode标准为全球商业领域中广泛使用的大部分字符定义了一个单一编码方案 所有的计算机都用单一的unicode标准 unicode数据中的位模式一致地翻译成字符 这保证了同一位模式在所有的计算机上总是转换成同一字符 数据可以随意地从一个数据库或计算机传送到另一个数据库或计算机 nchar n 存放n个unicode字符数据 n必须是1 4000之间的数据nvarchar n 存放长度可变的n个unicode字符数据 n必须是1 4000之间的数据ntext 存储最大长度为 230 1 B的unicode字符数据 4 3列的其他属性 在4 2节中 我们详细讲解了列的数据类型及长度 但是对于一个数据库管理员来说 仅仅知道这些是远远不够的 要设计好字段 还需要考虑更多的问题 例如那些字段是记录中必不可少的 那些字段的内容是不可重复的等等 这就要求我们尽可能掌握列更多的属性 以便实现更复杂的功能 30 4 3 1NULL NOTNULL和默认值 在列的 常规 属性中 除了名字 数据类型 长度 还有两个非常重要的选项 就是NULL NOTNULL和默认值 31 4 3 2IDENTITY的应用 使用IDENTITY关键字定义的字段又叫标识字段 一个标识字段是唯一标识表中每条记录的特殊字段 当一个新记录添加到这个表中时 这个字段就被自动赋给一个新值 默认情况下是 1递增 32 4 1 3创建表 1 企业管理器创建表 步骤 1 定义表结构 给表的每一列取字段名 并确定其数据类型 数据长度 是否可以为空2 设置约束 为了限制输入值的取值范围 以保证输入数据的正确性和一致性3 添加数据 2 使用T SQL语句创建表 CREATETABLE语句的语法 CREATETABLE database name owner owner table name column nameascomputed column expression CONSTRAINTconstraint name PRIMARYKEY UNIQUE n ON filegroup DEFAULT TEXTIMAGE ON filegroup DEFAULT 定义计算列值的表达式 表示text ntext和image列存储在指定文件组中的关键字 CREATETABLE语句的语法 续 column namedata type NULL NOTNULL DEFAULTconstant expression IDENTITY seed increment NOTFORREPLICATION ROWGUIDCOL COLLATE n 新列是标识列 表示新列是全局唯一标识符列 命令说明 1 CREATETABLE 这两个关键字指明该命令语句将创建一个表 2 database name owner owner table name 定义表名table name 可以选加数据库名database name和表的所有者名owner 表名必须符合标识符要求 且不得超过128个字符 如果是临时表 表名不得超过116个字符 3 column namedata type 定义列 其中column name为列名 data type为列的数据类型 4 NULL NOTNULL 指定所定义的列是否可以取空值 默认情况下是NULL 5 DEFAULTconstant expression 指定列有一个默认值约束 当向表中插入一条记录时 如果本列插入的数据为空 则系统自动将默认值填充到本列 6 IDENTITY seed increment 定义标识列 也称自动编号列 该列数据不能人工插入 当在表中插入其他列的数据时 SQLServer为标识列提供一个惟一的 递增的非空数值 在一个表中 只能定义一个标识列 Seed为标识列的起始值 increment为标识列的增量值 默认时 seed increment 1 1 7 n 表示可以在表中设计n个列的定义 每个列定义用逗号隔开 8 ONfilegroup 使用该选项时 表将存储在filegroup指定的文件组中 如果省略该选项 则表存储在默认文件组中 9 TEXTIMAGE ONfilegroup 使用该选项时 若表中存在text ntext和image数据类型的列 则将这些列中的数据存储在filegroup指定的文件组中 省略该选项 则储存在默认文件组中 一个简单的CREATETABLE语句 CREATETABLEtable name column name1data type1 NULL NOTNULL column name2data type2 NULL NOTNULL 演示操作 4 4 1修改表 企业管理器修改表在企业管理器中 打开指定的服务器中要修改的表的数据库 右击要进行修改的表 从弹出的快捷菜单中选择 设计表 选项 在出现的 设计表 对话框中 可利用图形化工具完成增加 删除和修改字段的操作 4 4 1修改表 使用T SQL语句修改表 ALTERTABLEtable name ALTERCOLUMNcolumn name new data type precision scale COLLATE NULL NOTNULL ADD DROP ROWGUIDCOL 4 4维护数据表 使用T SQL语句修改表 续 ADD column nameAScomputed column expression n WITHCHECK WITHNOCHECK ADD n 使用T SQL语句修改表 续 DROP CONSTRAINT constraint name COLUMNcolumn n CHECK NOCHECK CONSTRAINT ALL constraint name n ENABLE DISABLE TRIGGER ALL trigger name n 命令说明 1 ALTERTABLE 该关键字表示本命令将修改表的结构 2 table name 指定需要修改的表名称 3 ALTERCOLUMNcolumn name 关键字ALTERCOLUMN表示该命令将修改表中已经存在的列属性 column name指定需要修改的列名称 4 new data type NULL NOTNULL 将表中已存在列的数据类型修改为一个新的数据类型 并可以修改其非空属性 5 ADDcolumn namedata type NULL DEFAULT 指明要对表中添加的列的名称column name及其数据类型data type 只允许添加可包含空值NULL或指定为DEFAULT的非空值列 6 DROPCOLUMNcolumn name n 关键字DROPCOLUMN指明将要删除表中已存在的一列或多列 column name表示列名 n 表示可以同时删除多列 如果列存在默认值约束或其他约束 则必须先删除它们 否则无法删除列 删除约束的方法见第10章 7 除DROP外 该命令一次只允许更改表的一个属性或修改一列 一个简单的ALTER