第五章表的创建与管理PPT课件.ppt

第5章表的创建和管理 5 1数据类型5 2创建表5 3创建 删除和修改约束5 4增加 删除和修改字段5 5查看表格5 6删除表 1 5 1数据类型 5 1 1系统数据类型5 1 2自定义数据类型 2 5 1 1系统数据类型 1 整型数据类型2 浮点数据类型3 字符数据类型4 日期和时间数据类型5 文本和图形数据类型6 货币数据类型 7 位数据类型8 二进制数据类型9 特殊数据类型10 新增数据类型 3 1 整型数据类型 整型数据类型是最常用的数据类型之一 它主要用来存储数值 可以直接进行数据运算 而不必使用函数转换 int integer int 或integer 数据类型可以存储从 231 2 147 483 648 到231 1 2 147 483 647 范围之间的所有正负整数 Smallint 可以存储从 215 32 768 到215 1范围之间的所有正负整数 Tinyint 可以存储从0到255范围之间的所有正整数 4 2 浮点数据类型 近似 用于存储十进制小数 浮点数值的数据在SQLServer中采用只入不舍的方式进行存储 Real 可以存储正的或者负的十进制数值 最大可以有7位精确位数 Float 可以精确到第15位小数 其范围从 1 79E 308到1 79E 308 Decimal和numeric Decimal数据类型和numeric数据类型完全相同 它们可以提供小数所需要的实际存储空间 但也有一定的限制 可以用2到17个字节来存储从 1038 1到1038 1之间的数值 注意 数值类型的总位数不包括小数 例1 decimal 10 5 表示共有10位数 其中整数5位 小数5位 例2 numeric 6 4 若某项纪录赋值56 342689时 实际存储的是56 3427 5 3 字符数据类型 用来存储各种字母 数字符号和特殊符号 在使用时需要在其前后加上英文单引号或者双引号 如 abc abc cde 1 Char 占用1个字节 其定义形式为 char n n的取值为1 8000 默认n的值为1 若实际要存储的字符串长度不足n时 在串尾添加空格 以达到长度n 例如 char 20 当输入的字符串 ahjm2004 则存储的是jhjm2004和12个空格 6 2 Varchar 可以存储长达8000个字符的可变长度字符串 和char类型不同varchar类型根据输入数据的实际长度而变化 其定义形式为 varchar n 例如 varchar 20 输入字符串为 ahjm2004 则存储的是 ahjm2004 其长度为8个字节 3 Nchar 采用Unicode 统一字符编码标准 字符集每个Unicode字符用两个字节为一个存储单位 其定义形式为 nchar n 4 Nvarchar 使用Unicode字符集的Varchar数据类型 其定义形式为 nvarchar n 注 Nchar和Nvarchar属于统一字符类型 Unicode字符串 7 4 日期和时间数据类型 1 Datetime 占用8个字节 用于存储日期和时间的结合体 可以存储从公元1753年1月1日零时起 公元9999年12月31日23时59分59秒之间的所有日期和时间 其精确度可达三百分之一秒 即3 33毫秒 当存储datetime数据类型时 默认的格式是 MMDDYYYYhh mmA M P M 当插入数据或者在其它地方使用datetime类型时 需要用单引号把它括起来 8 datetime数据类型允许使用 和 作为不同时间单位间的分隔符 例如 2004年10月28日可以采用下列几种输入格式 oct282004 英文格式 2004 10 28或2004 10 28 数字加分隔符格式 20041028 纯数字格式 2 Smalldatetime 存储从1900年1月1日 2079年6月6日内的日期 4个字节 9 5 文本和图形数据类型 1 Text 容量可以在1 231 1个字节 在定义Text数据类型时 不需要指定数据长度 SQLServer会根据数据的长度自动为其分配空间 2 Ntext 采用unicode标准字符集 用于存储大容量文本数据 其理论上的容量为230 1 1 073 741 823 个字节 3 Image 用于存储照片 目录图片或者图画 其理论容量为231 1 2 147 483 647 个字节 10 6 货币数据类型 1 Money 用于存储货币值 数值以一个正数部分和一个小数部分存储在两个4字节的整型值中 存储范围为 922337213685477 5808 922337213685477 5808 精度为19 小数位为4 2 Smallmoney 其存储范围为 214748 3468 214748 3467 精度为10 小数位为4 当为money或smallmoney的表输入数据时 必须在有效位置前面加一个货币单位符号 如 或其它货币单位的记号 11 7 位数据类型 Bit称为位数据类型 有两种取值 0和1 如果一个表中有8个或更少的bit列时 用1个字节存放 如果有9 16个bit列时 用2个字节存放 在输入0以外的其它值时 系统均把它们当1看待 12 8 二进制数据类型 1 Binary 其定义形式为binary n 数据的存储长度是固定的 即n 4个字节 二进制数据类型的最大长度 即n的最大值 为8000 常用于存储图像等数据 2 Varbinary 其定义形式为varbinary n 数据的存储长度是变化的 它为实际所输入数据的长度加上4字节 在输入二进制常量时 需在该常量前面加一个前缀0 x 13 9 特殊数据类型 1 Timestamp 也称作时间戳数据类型 是一种自动记录时间的数据类型 主要用于在数据表中记录其数据的修改时间 它提供数据库范围内的唯一值 2 Uniqueidentifier 也称作唯一标识符数据类型 Uniqueidentifier用于存储一个16字节长的二进制数据类型 它是SQLServer根据计算机网络适配器地址和CPU时钟产生的全局唯一标识符代码 GloballyUniqueIdentifier 简写为GUID 14 10 新增数据类型 1 Bigint 占用8个字节 2 sql variant 用于存储除文本 图形数据和timestamp类型数据外的其它任何合法的SQLServer数据 3 table 用于存储对表或者视图处理后的结果集 15 5 1 2自定义数据类型 1 使用企业管理器创建2 利用系统存储过程创建 16 1 使用企业管理器创建 17 18 19 2 利用系统存储过程创建 系统存储过程sp addtype为用户提供了T SQL语句创建自定义数据类型的途径 其语法形式如下 sp addtype typename type phystype system data type nulltype null type owner owner name 20 type 指定用户定义的数据类型的名称 system data type 指定相应的系统提供的数据类型的名称及定义 注意 不能使用timestamp数据类型 当所使用的系统数据类型有额外说明时 需要用引号将其括起来 null type 指定用户自定义数据类型的null属性 其值可以为 null notnull 或者 nonull 默认与系统默认的null属性相同 owner name 指定用户自定义数据类型的所有者 21 例子5 1 自定义一个地址数据类型 sp addtypeaddress varchar 80 notnull 其运行结果如下 1row s affected typeadded 22 3 删除用户自定义数据 1 使用企业管理器2 利用系统存储过程sp droptype typename type 23 例子5 2 删除自定义的地址数据类型 sp droptypeaddress其运行结果如下 1row s affected 0row s affected Typehasbeendropped 24 5 2创建表 表是包含数据库中所有数据的数据库对象 用来存储各种各样的信息 在SQLServer2000中 一个数据库中最多可以创建200万个表 用户创建数据库表时 最多可以定义1024列 在同一数据库的不同表中 可以有相同的字段 但在同一个表中不允许有相同的字段 而且每个字段都要求数据类型相同 SQLServer2000提供了两种方法创建数据库表 一是利用企业管理器 EnterpriseManager 创建表 二是利用Transact SQL语句中的create命令创建表 25 5 2 1利用企业管理器创建表 在EnterpriseManager中 展开指定的服务器和数据库 打开想要创建新表的数据库 用右键单击表对象 从弹出的快捷菜单中选择新建表选项 或者在工具栏中选择图标 就会出现新建表对话框 在该对话框中 可以定义列的以下属性 列名称 数据类型 长度 精度 小数数 是否允许为空 缺省值 标识列 标识列的初始值 标识列的增量值和是否有行的标识 然后根据提示进行设置 26 5 2 1利用企业管理器创建表 27 5 2 2利用Transact SQL语言创建表 其语法格式如下 CREATETABLE database name owner owner table name column nameAScomputed column expression n ON filegroup DEFAULT TEXTIMAGE ON filegroup DEFAULT 28 创建表的各参数的说明如下 database name 用于指定在其中创建表的数据库名称 owner 用于指定新建表的所有者的用户名 table name 用于指定新建的表的名称 column name 用于指定新建表的列的名称 computed column expression 用于指定计算列的列值的表达式 ON filegroup DEFAULT 用于指定存储表的文件组名 TEXTIMAGE ON 用于指定text ntext和image列的数据存储的文件组 data type 用于指定列的数据类型 DEFAULT 用于指定列的缺省值 29 constant expression 用于指定列的缺省值的常量表达式 IDENTITY 用于指定列为标识列 Seed 用于指定标识列的初始值 Increment 用于指定标识列的增量值 NOTFORREPLICATION 用于指定列的IDENTITY属性在把从其它表中复制的数据插入到表中时不发生作用 即不足的生成列值 使得复制的数据行保持原来的列值 ROWGUIDCOL 用于指定列为全球唯一鉴别行号列 COLLATE 用于指定表使用的校验方式 column constraint和table constraint 用于指定列约束和表约束 30 例 使用create创建工资表 Createtable工资 工作编号工作编号 typenotnull 最低工资intnull 最高工资intnull 31 5 3创建 删除和修改约束 在SQLServer2000中有5种约束 主键约束 primarykeyconstraint 唯一性约束 uniqueconstraint 检查约束 checkconstraint 缺省约束 defaultconstraint 外部键约束 foreignkeyconstraint 32 5 3 1主键约束 主键能够唯一地确定表中的每一条记录 主键不能取空值 主键约束可以保证实体的完整性 1 通过企业管理器完成添加 删除和修改主键的操作 在企业管理器中 用右键单击要操作的数据库表 从弹出的快捷菜单中选择设计表选项 然后根据提示操作 例 为工资表添加主键约束 2 使用Transact SQL语句设置主键约束CONSTRAINTconstraint namePRIMARYKEY CLUSTERED NONCLUSTERED column name n 33 例题5 1对雇员表添加主键约束 语句如下 Altertable雇员addconstraintpk gyprimarykey 雇员编号 34 例题5 2 在执行创建产品信息表的操作时 指定产品编号为主键值 并且创建一个聚簇索引 createtableproducts idchar 10 notnull namechar 20 notnull pricemoneydefault0 01 quantitysmallintnull constraintpk idprimarykeyclustered id 35 5 3 2唯一性约束 唯一性约束用于指定一个或多个列的组合值具有唯一性 以防止在列中输入重复的值 使用唯一性约束的字段允许为空值 一个表中可以允许有多个唯一性约束 可以把唯一性约束定义在多个字段上 唯一性约束用于强制在指定字段上创建一个唯一性索引 默认情况下 创建的索引类型为非聚集索引 36 1 通过企业管理器完成创建和修改唯一性约束的操作 37 1 通过企业管理器完成创建和修改唯一性约束的操作 38 2 使用Transact SQL语句完成唯一性约束的操作 其语法如下 CONSTRAINTconstraint nameUNIQUE CLUSTERED NONCLUSTERED column name n 39 例5 3为雇员表 身份证号 添加unique约束 其语法如下 Altertable雇员addCONSTRAINTuk idUNIQUE 身份证号 40 5 3 3检查约束 check 保证数据库数据的完整性 一个列级检查约束只能与限制的字段有关 一个表级检查约束只能与限制的表中字段有关 一个表中可以定义多个检查约束 每个CREATETABLE语句中每个字段只能定义一个检查约束 在多个字段上定义检查约束 则必须将检查约束定义为表级约束 当执行INSERT语句或者UPDATE语句时 检查约束将验证数据 检查约束中不能包含子查询 41 1 使用企业管理器创建检查约束 42 2 用Transact SQL语句创建检查约束 具体语法如下 CONSTRAINTconstraint nameCHECK NOTFORREPLICATION logical expression 43 例5 4添加检查约束 具体语法如下 Altertable雇员Addconstraintchk salaryCHECK 工资 600 Altertable雇员Addconstraintchk phoneCHECK 电话like 0 9 0 9 0 9 0 9 0 9 0 9 0 9 0 9 44 5 3 4默认约束 default 每个字段只能定义一个默认约束 如果定义的默认值长于其对应字段的允许长度 那么输入到表中的默认值将被截断 不能加入到带有IDENTITY属性或者数据类型为timestamp的字段上 如果字段定义为用户定义的数据类型 而且有一个默认绑定到这个数据类型上 则不允许该字段有默认约束 45 1 使用企业管理器创建默认约束 46 2 使用Transact SQL语句创建默认约束 例 Altertable雇员Addconstraintdf phoneDefault 6666666 for电话 47 5 3 5外键约束 外键约束主要用来维护两个表之间数据的一致性 外键约束提供了字段参照完整性 外键从句中的字段数目和每个字段指定的数据类型都必须和REFERENCES从句中的字段相匹配 外键约束不能自动创建索引 需要用户手动创建 用户想要修改外键约束的数据 必须有对外键约束所参考表的SELECT权限或者REFERENCES权限 参考同一表中的字段时 必须只使用REFERENCES子句 不能使用外键子句 48 一个表中最多可以有31个外键约束 在临时表中 不能使用外键约束 主键和外键的数据类型必须严格匹配 49 1 在企业管理器中添加外键约束 50 1 在企业管理器中添加外键约束 51 2 使用Transact SQL语句设置外键约束 基本语法如下 CONSTRAINTconstraint nameFOREIGNKEY column name n REFERENCESref table ref column n 52 2 使用Transact SQL语句设置外键约束 基本语法如下 CONSTRAINTconstraint nameFOREIGNKEY column name n REFERENCESref table ref column n 例如 Altertable雇员addconstraintfk job idForeignkey 工作编号 references工资 工作编号 53 练习 p1377 6习题 创建一个顾客购物数据库 在此数据库中创建如下表 添加如下约束 1 为顾客表和订购表添加主码约束 顾客表的主码为顾客ID 订购表的主码为 顾客ID和商品ID 2 为订购表添加外码约束 限制订购表的顾客必须来自于顾客表 3 限制顾客表电话号码的形式为 三位区号 8位电话号码 且每一位均为数字 4 当顾客没有提供地址值时 使用默认的值 UNKNOWN 5 限制