数据库 第04章 创建数据类型和表课件

第4章创建数据类型和表创建数据类型创建表生成列值生成脚本推荐操作第4章创建数据类型和表创建数据类型创建数据类型系统提供的数据类型创建和删除用户定义的数据类型选择数据类型的指导原则3.1创建数据类型创建数据类型系统提供的数据类型3.1创建数据类型系统提供的数据类型数字数据整型数据：存储整数小数数据：包含存储在最小有效数上的数据3.1.1

系统提供的数据类型bigint占8个字节，值的范围为-263～263-1int占4个字节，值的范围为-231～231-1smallint占2个字节，值的范围为-32768～32767tinyint占1个字节，值的范围为0～255decimal[(p[,s])]p为精度，最大38；

s为小数位数，0≤s≤pnumeric[(p[,s])]在SQLServer中，等价于decimal系统提供的数据类型数字数据3.1.1系统提供的数据类型bi系统提供的数据类型（续）数字数据（续）近似数字数据：表示浮点数据的近似数字货币数据：表示正的或负的货币值3.1.1

系统提供的数据类型float[(n)]从-1.79E+308到1.79E+308之间的浮点数字数据；n

为用于存储科学记数法尾数的位数，同时指示其精度和存储大小，1≤n≤53real从3.40E+38到3.40E+38之间的浮点数字数据，存储大小为4字节；SQLServer中，real的同义词为float(24)

money占8个字节，值的范围为-922337203685477.5808～+922337203685477.5807smallmoney占4个字节，值的范围为-214748.3648～214748.3647系统提供的数据类型（续）数字数据（续）3.1.1系统提供的系统提供的数据类型（续）日期和时间数据字符数据和Unicode字符数据3.1.1

系统提供的数据类型datetime占8个字节，表示从1753年1月1日到9999年12月31日的日期smalldatetime占4个字节，表示从1900年1月1日至2079年6月6日的日期char[(n)]存储字符个数为0～8000varchar[(n)]存储字符个数为0～8000text存储字符个数为0～2GBnchar[(n)]存储字符个数为0～4000nvarchar[(n)]存储字符个数为0～4000ntext存储字符个数为0～1GB系统提供的数据类型（续）日期和时间数据3.1.1系统提供的系统提供的数据类型（续）二进制数据其他3.1.1

系统提供的数据类型binary[(n)]存储字节个数0～8000varbinary[(n)]存储字节个数0～8000image存储字节个数0～2Gbit存储位数据cursor存储对游标的引用rowversion(timestamp)时间戳sql_variant可存储除text、ntext、image、rowversion之外的其他类型table存储函数返回结果uniqueidentifier存储GUID以及UUID系统提供的数据类型（续）二进制数据3.1.1系统提供的数据创建和删除用户定义的数据类型3.1.2创建和删除用户定义的数据类型为什么要自定义数据类型当多个表的列中要存储同样类型的数据，且想确保这些列具有完全相同的数据类型、长度和为空性时，可使用用户定义数据类型，以保证数据的一致性创建用户定义的数据类型企业管理器系统存储过程

sp_addtype{类型名},[‘系统数据类型’][,[‘NULL’|‘NOTNULL’]][,’拥有者’]删除用户定义的数据类型

sp_droptype{‘类型名’}

创建和删除用户定义的数据类型3.1.2创建和删除用户定义创建和删除用户定义的数据类型(续)3.1.2创建和删除用户定义的数据类型创建用户定义的数据类型： EXECsp_addtypecity,‘varchar(20)’,‘NULL’ EXECsp_addtyperegion,‘varchar(20)’,‘NULL’ EXECsp_addtypecountry,‘varchar(40)’,‘NULL’删除用户定义的数据类型： EXECsp_droptypecity EXECsp_droptyperegion EXECsp_droptypecountry创建和删除用户定义的数据类型(续)3.1.2创建和删除用选择数据类型的指导原则若列值的长度相差很大，那么使用变长数据类型例如某列存储的是人名，地址等谨慎使用tinyint数据类型虽然节省空间，但扩展性很小对于小数数据来说，一般使用decimal数据类型可以精确地控制精度如果行的存储量超过8000字节，使用text或者image若不大于8000字节，可使用char、varchar或者binary数据类型对于货币数据，使用money数据类型不要使用类型为float或者real的列作为主键因为它们不精确，所以不适合用于比较3.1.3选择数据类型的指导原则选择数据类型的指导原则若列值的长度相差很大，那么使用变长数据第4章创建数据类型和表创建数据类型创建表生成列值生成脚本推荐操作第4章创建数据类型和表创建数据类型创建表SQLServer在行中组织数据的方式SQLServer组织text、ntext和image数据的方式创建和删除表添加和删除列3.2创建表创建表SQLServer在行中组织数据的方式3.2创行中组织数据的方式行首定长数据NBVB变长数据空值块变长值块4字节数据部分数据行由行首和数据部分组成行首：四个字节，包括了数据行中每列的信息数据部分定长数据：存放定长数据类型的列的数据空值块：标示值为空的列变长值块：标示值为变长数据的列的信息以及存放位置变长数据：存放变长数据类型的列的数据3.2.1SQLServer在行中组织数据的方式行中组织数据的方式行首定长数据NBVB变长数据空值块变长值块text、ntext和image数据的方式Text、ntext和image一般是存储在数据行之外的因为它们一般都比较大Text、ntext和image的存储结构在数据行内存放一个16字节的指针，指向一个根结构根结构组成了一棵B树的根节点，B树的叶节点指向实际存放数据的数据块将大对象数据类型的数据存储在数据行中避免了多次查找数据块位置，可提高性能用sp_tableoption来设置表的textinrow选项查看表的textinrow选项：objectproperty

函数3.2.2SQLServer组织text、ntext和image数据的方式text、ntext和image数据的方式Text、ntext、ntext和image数据的方式（续）数据行text指针根结构中间节点中间节点块1块2块1块23.2.2SQLServer组织text、ntext和image数据的方式text、ntext和image数据的方式（续）数据行创建表命名表和列：数据库名.拥有者.表名数据库名默认为当前的工作数据库拥有者默认为当前用户或者数据库的拥有者指定NULL或者NOTNULL确定列值是否可为空默认按照会话或者数据库的默认值设定计算列是一个虚的列，并不物理存放在表中当取列值的时候，SQLServer根据其他列的值和一个公式计算出列值创建和删除表3.2.3创建和删除表创建表创建和删除表3.2.3创建和删除表分隔标识符命名规则：保留字，如table、create、select等，不能作为对象标识符不符合标识符格式规则的标识符必须使用分隔符删除表DROPTABLE表名[,…n]创建和删除表（续）3.2.3创建和删除表CREATETABLEcustomer(namechar(30),columnchar(12))CREATETABLEcustomer(namechar(30),“column”char(12))CREATETABLEcustomer(namechar(30),[column]char(12))注：仅当QUOTED_IDENTIFIER选项设置为ON时，被引用的标识符才有效 SETQUOTED_IDENTIFIERON

分隔标识符创建和删除表（续）3.2.3创建和删除表CRE添加和删除列3.2.4添加和删除列添加列语法：ALTERTABLE表名

ADD列名

数据类型[NULL|NOTNULL]ALTERTABLEXSADD

奖学金等级tinyintNULLGOALTERTABLEXSALTERCOLUMN

姓名char(10)ALTERCOLUMN

出身时间datetimeGO添加和删除列3.2.4添加和删除列添加列ALTERTA添加和删除列（续）3.2.4添加和删除列删除列语法：ALTERTABLE表名

DROPCOLUMN列名[,…n]不能删除以下列正在复制的列用在索引中的列用在CHECK、FOREIGNKEY、UNIQUE或PRIMARYKEY

约束中的列与DEFAULT定义关联或绑定到某一默认对象的列绑定到规则的列添加和删除列（续）3.2.4添加和删除列删除列课堂练习添加列添加一个数值列添加列描述设置精度和小数位数设置列为标识列添加一个GUID列添加一个日期列添加一个字符列保存并关闭表设计器课堂练习添加列添加一个数值列第4章创建数据类型和表创建数据类型创建表生成列值生成脚本推荐操作第4章创建数据类型和表创建数据类型生成列值使用Identity属性使用NEWID函数和uniqueidentifier数据类型3.3生成列值生成列值使用Identity属性3.3生成列值使用Identity属性使用Identity属性的要求每个表只能有一个标识列只用在int、smallint、tinyint和decimal数据类型上。若用于decimal，小数位数必须为0标识列不能进行更新操作标识列不允许空值在查询中，可以用关键字IDENTITYCOL

来代表一个表中的标识列使得不必指明标识列的列名，增加了灵活性3.3.1使用Identity

属性使用Identity属性使用Identity属性的要使用Identity属性（续）检索Identity属性的信息使用全局变量@@identity获得当前会话的所有作用域中的任何表最后生成的标识值

例如：select@@identityfromEmployees

返回Employees表中的标示列的最后一行的值使用函数IDENT_CURRENT返回任何会话和任何作用域中的特定表最后生成的标识值

例如：selectIDENT_CURRENT(‘Employees’)管理Identity属性设置IDENTITY_INSERT为ON，可在INSERT中为标识列显式地插入用户提供的值使用DBCCCHECKIDENT检查当前标识值是否越界3.3.1使用Identity属性使用Identity属性（续）检索Identity属使用uniqueidentifier数据类型作用：确保ID的全局惟一性uniqueidentifier数据类型：存储GUIDNEWID函数：产生一个GUID两者常常和DEFAULT约束配合使用

CREATETABLECustomer(CustIDuniqueidentifierNOTNULLDEFAULTNEWID(),CustNamechar(30)NOTNULL)3.3.2使用NEWID函数和uniqueidentifier数据类型使用uniqueidentifier数据类型作用：确保第4章创建数据类型和表创建数据类型创建表生成列值生成脚本推荐操作第4章创建数据类型和表创建数据类型生成脚本使用企业管理器将模式生成Transact-SQL脚本维护备份脚本创建或者更新数据库开发脚本建立测试或开发环境训练新员工可以生成整个数据库的一个创建脚本文件一个或多个表的若干个创建脚本文件表、索引以及存储过程等各个对象的脚本，可以分别存放在不同脚本文件里3.4生成脚本生成脚本使用企业管理器将模式生成Transact-SQL第4章创建数据类型和表创建数据类型创建表生成列值生成脚本推荐操作第4章创建数据类型和表创建数据类型推荐操作在CREATETABLE语句中总是指定列的特性产生脚本以重建数据库和数据库对象指定适当的数据类型和大小3.5

推荐操作推荐操作在CREATETABLE语句中总是指定列的特目标创建用户定义数据类型创建表添加和删除列从数据库中生成Transact-SQL脚本练习1创建用户自定义数据类型练习2在ClassNorthwind数据库中创建表练习3添加和删除列练习4生成Transact-SQL脚本练习5在ClassNorthwind数据库中加载数据实验

创建数据类型和表目标实验创建数据类型和表回顾学习完本章后，将能够：

创建和删除用户定义数据类型创建和删除用户表生成列值生成脚本回顾学习完本章后，将能够：第4章创建数据类型和表创建数据类型创建表生成列值生成脚本推荐操作第4章创建数据类型和表创建数据类型创建数据类型系统提供的数据类型创建和删除用户定义的数据类型选择数据类型的指导原则3.1创建数据类型创建数据类型系统提供的数据类型3.1创建数据类型系统提供的数据类型数字数据整型数据：存储整数小数数据：包含存储在最小有效数上的数据3.1.1

系统提供的数据类型bigint占8个字节，值的范围为-263～263-1int占4个字节，值的范围为-231～231-1smallint占2个字节，值的范围为-32768～32767tinyint占1个字节，值的范围为0～255decimal[(p[,s])]p为精度，最大38；

s为小数位数，0≤s≤pnumeric[(p[,s])]在SQLServer中，等价于decimal系统提供的数据类型数字数据3.1.1系统提供的数据类型bi系统提供的数据类型（续）数字数据（续）近似数字数据：表示浮点数据的近似数字货币数据：表示正的或负的货币值3.1.1

系统提供的数据类型float[(n)]从-1.79E+308到1.79E+308之间的浮点数字数据；n

为用于存储科学记数法尾数的位数，同时指示其精度和存储大小，1≤n≤53real从3.40E+38到3.40E+38之间的浮点数字数据，存储大小为4字节；SQLServer中，real的同义词为float(24)

money占8个字节，值的范围为-922337203685477.5808～+922337203685477.5807smallmoney占4个字节，值的范围为-214748.3648～214748.3647系统提供的数据类型（续）数字数据（续）3.1.1系统提供的系统提供的数据类型（续）日期和时间数据字符数据和Unicode字符数据3.1.1

系统提供的数据类型datetime占8个字节，表示从1753年1月1日到9999年12月31日的日期smalldatetime占4个字节，表示从1900年1月1日至2079年6月6日的日期char[(n)]存储字符个数为0～8000varchar[(n)]存储字符个数为0～8000text存储字符个数为0～2GBnchar[(n)]存储字符个数为0～4000nvarchar[(n)]存储字符个数为0～4000ntext存储字符个数为0～1GB系统提供的数据类型（续）日期和时间数据3.1.1系统提供的系统提供的数据类型（续）二进制数据其他3.1.1

系统提供的数据类型binary[(n)]存储字节个数0～8000varbinary[(n)]存储字节个数0～8000image存储字节个数0～2Gbit存储位数据cursor存储对游标的引用rowversion(timestamp)时间戳sql_variant可存储除text、ntext、image、rowversion之外的其他类型table存储函数返回结果uniqueidentifier存储GUID以及UUID系统提供的数据类型（续）二进制数据3.1.1系统提供的数据创建和删除用户定义的数据类型3.1.2创建和删除用户定义的数据类型为什么要自定义数据类型当多个表的列中要存储同样类型的数据，且想确保这些列具有完全相同的数据类型、长度和为空性时，可使用用户定义数据类型，以保证数据的一致性创建用户定义的数据类型企业管理器系统存储过程

sp_addtype{类型名},[‘系统数据类型’][,[‘NULL’|‘NOTNULL’]][,’拥有者’]删除用户定义的数据类型

sp_droptype{‘类型名’}

创建和删除用户定义的数据类型3.1.2创建和删除用户定义创建和删除用户定义的数据类型(续)3.1.2创建和删除用户定义的数据类型创建用户定义的数据类型： EXECsp_addtypecity,‘varchar(20)’,‘NULL’ EXECsp_addtyperegion,‘varchar(20)’,‘NULL’ EXECsp_addtypecountry,‘varchar(40)’,‘NULL’删除用户定义的数据类型： EXECsp_droptypecity EXECsp_droptyperegion EXECsp_droptypecountry创建和删除用户定义的数据类型(续)3.1.2创建和删除用选择数据类型的指导原则若列值的长度相差很大，那么使用变长数据类型例如某列存储的是人名，地址等谨慎使用tinyint数据类型虽然节省空间，但扩展性很小对于小数数据来说，一般使用decimal数据类型可以精确地控制精度如果行的存储量超过8000字节，使用text或者image若不大于8000字节，可使用char、varchar或者binary数据类型对于货币数据，使用money数据类型不要使用类型为float或者real的列作为主键因为它们不精确，所以不适合用于比较3.1.3选择数据类型的指导原则选择数据类型的指导原则若列值的长度相差很大，那么使用变长数据第4章创建数据类型和表创建数据类型创建表生成列值生成脚本推荐操作第4章创建数据类型和表创建数据类型创建表SQLServer在行中组织数据的方式SQLServer组织text、ntext和image数据的方式创建和删除表添加和删除列3.2创建表创建表SQLServer在行中组织数据的方式3.2创行中组织数据的方式行首定长数据NBVB变长数据空值块变长值块4字节数据部分数据行由行首和数据部分组成行首：四个字节，包括了数据行中每列的信息数据部分定长数据：存放定长数据类型的列的数据空值块：标示值为空的列变长值块：标示值为变长数据的列的信息以及存放位置变长数据：存放变长数据类型的列的数据3.2.1SQLServer在行中组织数据的方式行中组织数据的方式行首定长数据NBVB变长数据空值块变长值块text、ntext和image数据的方式Text、ntext和image一般是存储在数据行之外的因为它们一般都比较大Text、ntext和image的存储结构在数据行内存放一个16字节的指针，指向一个根结构根结构组成了一棵B树的根节点，B树的叶节点指向实际存放数据的数据块将大对象数据类型的数据存储在数据行中避免了多次查找数据块位置，可提高性能用sp_tableoption来设置表的textinrow选项查看表的textinrow选项：objectproperty

函数3.2.2SQLServer组织text、ntext和image数据的方式text、ntext和image数据的方式Text、ntext、ntext和image数据的方式（续）数据行text指针根结构中间节点中间节点块1块2块1块23.2.2SQLServer组织text、ntext和image数据的方式text、ntext和image数据的方式（续）数据行创建表命名表和列：数据库名.拥有者.表名数据库名默认为当前的工作数据库拥有者默认为当前用户或者数据库的拥有者指定NULL或者NOTNULL确定列值是否可为空默认按照会话或者数据库的默认值设定计算列是一个虚的列，并不物理存放在表中当取列值的时候，SQLServer根据其他列的值和一个公式计算出列值创建和删除表3.2.3创建和删除表创建表创建和删除表3.2.3创建和删除表分隔标识符命名规则：保留字，如table、create、select等，不能作为对象标识符不符合标识符格式规则的标识符必须使用分隔符删除表DROPTABLE表名[,…n]创建和删除表（续）3.2.3创建和删除表CREATETABLEcustomer(namechar(30),columnchar(12))CREATETABLEcustomer(namechar(30),“column”char(12))CREATETABLEcustomer(namechar(30),[column]char(12))注：仅当QUOTED_IDENTIFIER选项设置为ON时，被引用的标识符才有效 SETQUOTED_IDENTIFIERON

分隔标识符创建和删除表（续）3.2.3创建和删除表CRE添加和删除列3.2.4添加和删除列添加列语法：ALTERTABLE表名

ADD列名

数据类型[NULL|NOTNULL]ALTERTABLEXSADD

奖学金等级tinyintNULLGOALTERTABLEXSALTERCOLUMN

姓名char(10)ALTERCOLUMN

出身时间datetimeGO添加和删除列3.2.4添加和删除列添加列ALTERTA添加和删除列（续）3.2.4添加和删除列删除列语法：ALTERTABLE表名

DROPCOLUMN列名[,…n]不能删除以下列正在复制的列用在索引中的列用在CHECK、FOREIGNKEY、UNIQUE或PRIMARYKEY

约束中的列与DEFAULT定义关联或绑定到某一默认对象的列绑定到规则的列添加和删除列（续）3.2.4添加和删除列删除列课堂练习添加列添加一个数值列添加列描述设置精度和小数位数设置列为标识列添加一个GUID列添加一个日期列添加一个字符列保存并关闭表设计器课堂练习添加列添加一个数值列第4章创建数据类型和表创建数据类型创建表生成列值生成脚本推荐操作第4章创建数据类型和表创建数据类型生成列值使用Identity属性使用NEWID函数和uniqueidentifier数据类型3.3生成列值生成列值使用Identity属性3.3生成列值使用Identity属性使用Identity属性的要求每个表只能有一个标识列只用在int、smallint、tinyint和decimal数据类型上。若用于decimal，小数位数必须为0标识列不能进行更新操作标识列不允许空值在查询中，可以用关键字IDENTITYCOL

来代表一个表中的标识列使得不必指明标识列的列名，增加了灵活性3.3.1使用Identity

属性使用Identity属性使用Identity属性的要使用Identity属性（续）检索Identity属性的信息使用全局变量@@identity获得当前会话的所有作用域中的任何表最后生成的标识值

例如：select@@identityfromEmploy