SQLServer管理数据库表.ppt

第三章 管理数据表 w数据类型 w创建表 w创建、删除和修改约束 w增加、删除和修改字段 w查看表格 w删除表格 商学院 数据类型 P104 l 在计算机中数据有两种特征：类型和长度。所谓数 据类型就是以数据的表现方式和存储方式来划分的 数据类型 商学院 分类类数据类类型 整型数据类型INT或INTEGER、SMALLINT、TINYINT、BIGINT 浮点数据类型REAL、FLOAT、DECIMAL、NUMERIC 二进制数据类型BINARY、VARBINARY 逻辑 数据类型BIT 字符数据类型CHAR、NCHAR、VARCHAR、NVARCHAR 文本和图形数据类型 TEXT、NTEXT、IMAGE 日期和时间 数据类型 DATETIME、SMALLDATETIME 货币 数据类型MONEY、SMALLMONEY 特定数据类型TIMESTAMP、UNIQUEIDENTIFIER 用户自定义数据类型 SYSNAME 新数据类型SQL_VARIANT、TABLE SQL Server 2005提供的数据类型分类 P104 商学院 一、系统数据类型 P104 系统数据类型是SQL Server预先定义好的，可以直接使用。 1整型数据类型 （1）int（integer）：占4个字节,表示范围-231231-1 （2）Smallint ：占2个字节,表示范围-215215-1 （3）Tinyint ：1个字节,表示范围0255 （4）bigint: 8个字节,表示范围-263263-1 商学院 2浮点数据类型(有舍入误差） ： 用于存储十进制小数 ，采用只入不舍的方式 （1）real：其表示范围从3.41038 3.41038。 （2）float：其表示范围从1.7910308 1.7910308。定义数 据时，可以指定存放数据的精度，精度取值范围为153， 例如float(36)。 数据类型 商学院 （3）Decimal和numeric（不带舍入的精确的浮点数 ）：其表示范围最多可达38位数字。定义这种类型 的数据时，可以指定精度及小数位数，可以将其写 为decimal（p，s）的形式。 l 注意：数值类型的总位数不包括小数。 l 例如：decimal（10，5），表示共有10位数，其中整数5位 ，小数5位。 数据类型 商学院 3字符数据类型 用来存储各种字母、数字符号和特殊符号。在使用时需要在 其前后加上英文单引号或者双引号。 （1）Char：每个字符占用1个字节。用来存放固定长度的非 Unicode（统一字符编码标准）字符集 其定义形式为：char（n） n表示字符数，n的取值为18000。默认n的值为1。 l 若输入数据的字符数小于n，则系统自动在其后添加空格来 填满设定好的空间。 l 若输入的数据过长，将会截掉其超出部分。 数据类型 商学院 （2）Varchar：可以存储长达8000个字符的可变长度字符串， 和char类型不同varchar类型根根据输入数据的实际长度而 变化。若输入数据的字符数小于n，则系统不会在其后添加 空格来填满设定好的空间。 其定义形式为：varchar（n）， n表示字符数 （3）Nchar：采用Unicode（统一字符编码标准）字符集每个 Unicode字符用两个字节为一个存储单位。 其定义形式为：nchar（n） n表示字符数，n的取值为14000。默认n的值为1。 （4）Nvarchar：使用Unicode字符集的Varchar数据类型。 其定义形式为：nvarchar（n）， n表示字符数 数据类型 商学院 4日期和时间数据类型 （1）Datetime：占用8个字节。 用于存储日期和时间的结合体，可以存储从公元1753年1 月1日零时起公元9999年12月31日23时59分59秒之间的 所有日期和时间，其精确度可达三百分之一秒，即3.33 毫秒。 当存储datetime数据类型时，默认的格式是：MM DD YYYY hh:mm A.M./P.M。当插入数据或者在其它地方使 用datetime类型时，需要用单引号把它括起来。 （2）Smalldatetime：存储从1900年1月1日2079年6月6日内 的日期。4个字节。 数据类型 商学院 l 日期输入格式 日期的输入格式大致可分为三类： 1）英文+数字格式：此类格式中月份可用英文全名或缩写，且不分大小 写；年和月日之间可不用逗号；年份可为4位或2位；当其为两位时，若 其值小于50则视为20xx年，若大于或等于50则视为19xx年；若日部分省 略，则视为当月的1号。以下格式均为正确的日期格式：Jan 4 1999、 JAN 4 1999、January 4 1999、Jan 1999 4、1999 4 Jan和1999 Jan 4。 2）数字+分隔符格式：允许使用/、-和.作为不同时间单位间的分隔符。 如：YMD: 2000/6/22 2000-6-22 2000.6.22 MDY: 3/5/2000 3-5-2000 3.5.2000 DMY: 31/12/2000 31-12-2000 31.12.2000 3）纯数字格式：以连续的4位、6位或8位数字来表示日期。如果输入的 是6位或8位数字，系统将按年、月、日来识别，即YMD格式，并且月和日 都是用两位数字来表示；如果输入的数字是4位数，系统认为这4位数代 表年份，其月份和日缺省为此年度的1月1日。如： 20000601-2000年6月1日；991212-1999年12月12日；1998-1998年 数据类型 商学院 5文本和图形数据类型 （1）Text：容量可以在1231-1个字节。 在定义Text数据类型时，不需要指定数据长度，SQL Server 会根据数据的长度自动为其分配空间。 （2）Ntext：采用unicode标准字符集，用于存储大容量文本数 据。其理论上的容量为230-1（1，073，741，823）个字节。 （3）Image：用于存储照片或图片等信息，其理论容量为231-1 （2，147，483，647）个字节。 数据类型 商学院 6货币数据类型 P106 （1）Money：用于存储货币值，数值以一个正数部分和一个小 数部分存储在两个4字节的整型值中，存储范围为- 922337213685477.5808 922337213685477.5808，精度为货币 单位的万分之一。 (2)Smallmoney： 其存储范围为 -214748.3468214748.3467。 当为money或smallmoney的表输入数据时，必须在有效位置前 面加一个货币单位符号（如$或其它货币单位的记号）。 数据类型 商学院 7位数据类型 P106 Bit称为位数据类型，有两种取值：0和1。如果一个表中有 8个或更少的bit列时，用1个字节存放。如果有916个bit列 时，用2个字节存放。 在输入0以外的其它值时，系统均把它们当1看待。 数据类型 商学院 8二进制数据类型 P106 （1）Binary： 其定义形式为binary（n），数据的存储长度是固定的，即 n+4个字节。二进制数据类型的最大长度（即n的最大值） 为8000，常用于存储图像等数据。 （2）Varbinary： 其定义形式为varbinary（n），数据的存储长度是变化的， 它为实际所输入数据的长度加上4字节。 在输入二进制常量时，需在该常量前面加一个前缀0x。 数据类型 商学院 9特殊数据类型 P106 （1）Timestamp：也称作时间戳数据类型。任何一个表中只 能有一个Timestamp列，该列的值在该行数据修改时由SQL Server自动更新，反映了数据库中数据修改的相对顺序。 它提供数据库范围内的唯一值。 （2）Uniqueidentifier：也称作唯一标识符数据类型。 Uniqueidentifier用于存储一个16字节长的二进制数据类型 ，它是SQL Server根据计算机网络适配器地址和CPU时钟 产生的全局唯一标识符代码（Globally Unique Identifier， 简写为GUID）。即使在不同的计算机及不同的数据库中 ，也不会有相同的GUID。 数据类型 商学院 二、自定义数据类型 P86 P106 l 在SQL Server2005中，用户定义的数据类型是基于系统数 据类型的。在创建一个用户定义的数据类型时，必须给 出以下3部分信息： a)新数据类型名； b)这种数据类型是基于那种系统数据类型的； c)可否为空 自定义数据类型的创建方法： 1使用企业管理器创建 2利用系统存储过程创建 sp_addtype typename= type, phystype= system_data_type , nulltype= null_type , owner= owner_name 数据类型 商学院 l type：指定用户定义的数据类型的名称。 l system_data_type：指定相应的系统提供的数据类型的名称 及定义。注意，不能使用timestamp数据类型，当所使用的 系统数据类型有额外说明时，需要用引号将其括起来。 l null_type：指定用户自定义数据类型的null属性，其值可以 为null、not null或者nonull。默认与系统默认 的null属性相同。 l owner_name：指定用户自定义数据类型的所有者。 l 例：在查询分析器中使用系统存储过程sp_addtype创建用户 定义数据类型。 sp_addtype typename=address, phystype=char(40),nulltype=not null 数据类型 商学院 删除用户自定义数据 1使用企业管理器 2利用系统存储过程 sp_droptype typename= type 数据类型 商学院 创建表 P76 表是包含数据库中所有数据的数据库对象，用来 存储各种各样的信息。 在SQL Server 2005中，一个数据库中最多可以创 建200万个表，用户创建数据库表时，最多可以定 义1024列。在同一数据库的不同表中，可以有相同 的字段，但在同一个表中不允许有相同的字段，而 且每个字段都要求数据类型相同。 商学院 一、利用企业管理器创建表 l 默认值：指定列的默认值。除定义为timestamp或带 IDENTITY属性的列以外的任何列。删除表时，将删除默认 值定义。只有常量值（如字符串）、SQL Server内部函数 （如SYSTEM_USER（）或NULL值可以用作默认值。 l 精度和小数位数：精度是列的总长度，包括整数部分和小 数部分的长度之和，但不包括小数点；小数位数指定小数 点后面的长度。 创建表 商学院 l 标识：指定列是否是标识列。一个表只能创建一个标识 列。不能对标识列使用绑定默认值和DEFAULT约束。必须 同时指定种子和增量，或者两者都不指定。默认值（1,1 ）。能够成为标识列的数据类型有int、smallint、 tinyint、numeric和decimal等系统数据类型；如果其数 据类型为numeric和decimal，不允许出现小数位数。 l 标识种子：指定标识列的初始值。 l 标识递增量：指定标识列的增量值。 创建表 商学院 创建表 l 是RowGuid：指定列是否使用全局唯一标识符。 l 公式：用于指定计算列的列值表达式。 l 排序规则：指定列的排序规则。 l 说明：在数据库中表名必须是唯一的，但是如果为表指 定了不同的用户，就可以创建多个相同名称的表。 商学院 数据完整性规则 P92 数据完整性是指数据的精确性和可靠性。它是应防止数据 库中存在不符合语义规定的数据和防止因错误信息的输入 和输出造成无效操作或错误信息而提出的。数据完整性分 为4类：实体完整性、域完整性、参照完整性、用户定义 完整性。 商学院 数据完整性规则 P92 1、实体完整性 关系数据库中的一个表是由行和列组成的，实体完整 性规定表的每一行在表中是惟一的实体。即关系中元 组的主键值不能重复和为空（不确定） 。 2、域完整性 域完整性是指数据库中的列必须满足某种特定的数据 类型或约束。 商学院 3、参照完整性 参照完整性是指两个表的主关键字和外部关键字的数据应 对应一致。它确保了有主关键字的表中对应其他表的外部 关键字的行存在，即保证了表之间的数据的一致性，防止 了数据丢失或无意义的数据在数据库中扩散。参照完整性 是建立在外部关键字和主关键字之间或外部关键字 和惟一 性关键字之间的关系上的。在SQL Server中，参照完整性 作用表现在如下几个方面： 1）禁止在从表中插入包含主表中不存在的关键字的数据行； 2）禁止会导致从表中的相应值孤立的主表中的外关键字值改 变； 3）禁止删除在从表中的有对应记录的主表记录。 数据完整性规则 P92 商学院 4、用户定义的完整性 它反映了用户的要求，是某一具体应用所涉及的数据 必须满足的语义要求，对此系统应提供了定义和检验 这类完整性的机制，其中主要的是：规则、默认值、 约束和触发器。 数据完整性规则 P92 商学院 创建、删除和修改约束 P80 l 约束是SQL Server提供的自动保持数据库完整性的一种方 法 ，定义了可输入表或表的单个列中的数据的限制条件。 l 列级约束：列级约束作为列定义的一部分只作用于此列本 身。 l 表级约束：表级约束作为表定义的一部分可以应用在一个 表中的多个列。 l 在SQL Server中有5种约束，主码约束、外码约束、惟一性 约束、检查约束和默认约束。 商学院 创建、删除和修改约束 1、 主码约束 l 主关键字约束指定表的一列或几列的组合的值在表中具 有惟一性，即能惟一地指定表中的每一行记录，主键不 能取空值。主键约束可以保证实体的完整性。 l 通过企业管理器完成添加、删除和修改主键的操作。 商学院 2 唯一性约束 l 唯一性约束用于指定一个或多个列的组合值具有唯一 性，以防止在列中输入重复的值。 l 使用唯一性约束的字段允许为空值； l 一个表中可以允许有多个唯一性约束； l 可以把唯一性约束定义在多个字段上； l 唯一性约束用于强制在指定字段上创建一个唯一性索 引； l 默认情况下，创建的索引类型为非聚集索引。 l 通过企业管理器完成创建和修改唯一性约束的操作 创建、删除和修改约束 商学院 l 创建唯一约束来确保不参与主键的特定列的值不重复。尽管 唯一约束和主键都强制唯一性，但在下列情况下，应该为表 附加唯一约束以取代主键约束： l 如果要对列或列的组合强制唯一性。可以为表附加多个唯一约束，而 只能为表附加一个主键约束。 l 如果要对允许空值的列强制唯一性。可以为允许空值的列附加唯一约 束，而只能将主键约束附加到不允许空值的列。当将唯一约束附加到 允许空值的列时，确保在约束列中最多有一行含有空值。 商学院 3、 检查约束 检查约束对输入列或整个表中的值设置检查条件，以限制 输入值，保证数据库数据的完整性。一个表中可以定义多 个检查约束； 创建、删除和修改约束 商学院 l 当执行INSERT语句或者UPDATE语句时，检查约束将验 证数据； l 使用企业管理器创建检查约束 创建、删除和修改约束 商学院 4、 默认约束 默认约束通过定义列的默认值或使用数据库的默认值对象绑 定表的列，来指定列的默认值。 l 每个字段只能定义一个默认约束； l 如果定义的默认值长于其对应字段的允许长度，那么输入到表中的默 认值将被截断； l 不能加入到带有IDENTITY属性或者数据类型为timestamp的字段上； l 如果字段定义为用户定义的数据类型，而且有一个默认绑定到这个数 据类型上，则不允许该字段有默认约束。 l 不能在创建表时定义默认约束，只能向已经创建好的表中添加默认约 束。 创建、删除和修改约束 商学院 5 外码约束 外关键字约束定义了表之间的关系，它主要用来维护两个 表之间数据的一致性。当一个表中的一个列或多个列的 组合和其他表中的主关键字定义相同时，就可以将这些 列或列的组合定义为外关键字，并设定它与哪个表中哪 些列相关联。 创建、删除和修改约束 商学院 l 当在定义主码约束的表中更新列值时，