第4章 数据库表

第4章数据库表【学习目标】本章将主要介绍数据库表操作的基本知识，包括数据库表的设计、定义与维护过程。其中包括使用SSMS管理创建、管理数据库表，SQLServer2005中提供的数据类型以及使用T-SQL语句创建与管理数据库表。本章学习要点：SQLServer2005数据类型数据库表的类型数据库表的创建数据库表的管理【学习导航】表是数据库中最基本、最重要、最核心的对象，是组织数据的方式，是存储数据的地方。从某种意义上说，管理数据库说是管理数据库中的表。如何合理的设计表结构将直接影响到数据库中数据的使用效率。本章将全面讨论有关表的管理技术与方案。本章主要内容见图4-1所示的学习导航。图4-1本章学习导航4.1数据表的概念与数据类型4.1.1SQLServer表的概念1．表的的概念表在关系数据库中是用来表示实体的，用来存储和组织数据、具有行、列结构的数据库对象。在SQLServer数据库中，表定义为列的集合，数据在表中是按行、列的格式组织结构的。行，也称为记录，代表一个唯一的实体。列，也称为字段，代表对实体的属性。由上可知：表有以下特点：代表实体，有唯一的名称，由行和列组成，行和列的顺序可以是任意的且列名不允许重复。2．表的分类在MicrosoftSQLServer2005系统中，表大致分为4类，即普通表、已分区表、临时表和系统表，其中每一种表都有其自身的作用和特点。普通表：也称为标准表或基本表，即通常说的在数据库中存储数据的表，是经常使用的表对象，也是最重要、最基本的表，简称表。已分区表：是为提高系统使用效率而派生的表，将数据水平划分成多单元，这些单元可以分散到数据库的多个文件组里，来实现对单元数据的并行访问。若表中的数据量非常庞大，且数据经常以不同的方式来访问，建立分区是一个最好的选择。其优点是：方便管理大型表，来提高对数据的使用效率。临时表：即临时创建的，不可以永久存在的表。临时分为本地临时表和全局临时表。一旦临时表创建后，可以一直存储到SQLServer实例断来链接为止。其中本地临时表只对创建者可见，而全局临时表在创建后对所有的用户和链接都可见。系统表：主要存储有关SQLServer服务器配置、数据库设置、用户和表对象的描述等系统信息。通常只有DBA才能使用系统表。3．表的设计由于数据表是数据库对象中最重要的一个对象，所以数据表的设计好坏直接关系到数据库的维护性和可用性。因此在设计表时，要根据数据库逻辑结构设计的要求确定需要什么样的表、什么样的数据、列的名称、所包含的数据类型，以及是否允许空值、索引、主键以及外键的约束等情况。创建一个表最有效的方法是将表中所需要的信息一次定义完成。然后在使用的过程中再逐一完善。在MicrosoftSQLServer2005系统中，创建表有如下限制：每个数据库中最多可创建20亿个表；每个表中最多可创建1个聚集索引和249个非聚集索引；每个表最多可创建1024列；每条记录最多可以占8060个字节。4.1.2SQLServer2005数据类型数据类型是用来表现数据特征的，决定了数据在计算机中的存储格式、存储长度以及数据精度和小数位数等属性。在创建数据表时，表中的每一个列、局部变量、表达式等都离不开数据类型的支持。确定了数据类型也就确定了该数据的取值范围。在MicrosoftSQLServer2005系统中，需要使用数据类型的对象包括：表中的列、视图中的列、定义的局部变量、存储过程中的参数、Transact-SQL函数、存储过程的返回值等。MicrosoftSQLServer2005系统提供了28种数据类型。这些数据类型可以分为数字数据类型、字符数据类型、日期和时间数据类型、二进制数据类型以及其他数据类型。1．数字数据类型使用数字数据的数据类型称为数字数据类型。该数据类型可以参加各种数学运算。SQLServer2005系统提供了11种数字数据类型，即bigint、int、smallint、tinyint、decimal、numeric、money、smallmoney、float、real、bit种。实际上还可以把这些数据类型继续分类，如整数类型和小数类型，其中整数类型有bigint、int、smallint、tinyint。还可以分为精确数字类型和近似数字类型，其中float和real都是近似数字类型。也可以按是否能表示货币金额，分为货币类型和非货币类型等。（1）整数数据类型整数数据类型表示可以存储整数精确数据，SQLServer2005系统提供了4种整数数据类型：bigint：长度是8个字节，且可以存储正负数，取值范围是-263至263-1。int：长度是4个字节，且可以存储正负数，取值范围是-231至231-1，int是最常用的数据类型，当int数据类型表示的数据长度不足时，才考虑使用bigint数据类型。smallint：长度是2个字节，也可以存储正负数，取值范围是-215至215-1tinyint：长度是1个字节，且都是正数，取值范围是0到255。在选择整数数据类型时，默认情况下考虑使用int数据类型，如果确认要存储的数据可能很大或很小时，可以考虑使用bigint或smallint，只有当存储的数据不超过255且都为正数时，才考虑使用tinyint数据类型。（2）decimal和numeric数据类型1）decimal和numeric也称为精确数据类型，用于存储带小数点且小数点后位数确定的数据。2）decimal[（p[,s]）]：取值范围是-1038+1至1038-13）numeric[（p[,s]）]：用法同decimal[（p[,s]）]。上面的语法中，p表示数字的精度，s表示数字的小数位数。精度p的取值范围是1至38，默认值是18。小数位s的取值范围必须是0到p之间的值，默认值是0。这两种数据类型在功能上是等价的，只是名称不同而已。实际上，在SQLServer2005系统中，这两种数据类型是作为完全相同的一种数据类型来对待的。（3）money和smallmoney数据类型money和smallmoney也称为货币数据类型，这两种数据类型的差别在于存储字节的大小和取值范围不同。1）money：需要8个存储字节，取值范围是-263至263-1，精确到货币单位的千分之十。2）smallmoney：需要4个存储字节，取值范围是-215至215-1。money和smallmoney是一种确定性数值的数据类型，因为它们的精度和小数位都是确定的。但是这两种数据类型也有一些与其他数字数据类型不同的地方。第一，当表示货币时一定要加上$符号。第二，它们的小数位最多是4位，也就是精确到货币单位的万分之一。第三，当小数位位数超过4位时，系统自动按照四舍五入进行处理。（4）float和real数据类型若要进行科学计算，并且希望存储更大的数值，但对数据的精度要求不是绝对严格，可以考虑使用float和real数据类型，这两种数据类型用于表示指定数据的大致数据值的数据类型。1）float（n）：存储8个字节，取值范围是-1.79E+308~1.79E+308的浮点数，其中n为精度，n是从1到53的整数。2）real：存储从-3.04E+38到3.04E+38之间的浮点数，存储大小为4个字节。需要注意的是，如果某些列中的数据或变量将会参与科学计算，建议考虑为这些数据对象指定float或real数据对象，否则运算过程中会形成比较大的误差。（5）bit数据类型bit可以存储1、0、null数据的数据类型，这些数据主要用于一些条件逻辑判断。也可以把TRUE和FALSE数据存储到bit数据类型中，这时需要按照字符格式存储TRUE和FALSE数据。2．字符数据类型SQLServer系统提供了6种字符数据类型，即char、varchar、text、nchar、nvarchar、ntext,其中char、varchar、text是非Unicode字符数据，nchar、nvarchar、ntext是Unicode字符数据。（1）非Unicode字符数据类型非Unicode字符数据用于存储汉字、英文字母、数字、标点和各种符号，输入时必须用半角单引号括起来，是定长的字符数据类型：1）char（n）：为固定长度存储字符串的数据类型，n取值范围是1~8000，存储空间大小为n个字节。2）varchar[(n|max)]：变长存储字符串的数据类型，n可以是1和8000之间的数，max表示最大存储大小为231-1,存储大小为所输入数据的实际长度+2个字节。3）text：可以存储最大长度为231-1个字节的字符数据，微软公司建议尽量避免使用text数据类型，而应该使用varchar(max)存储大文本数据。在选择使用char（n）和varchar[(n|max)]数据类型时，可以考虑使用下面的原则：（1）如果该列存储的数据长度都相同，这时应该使用char（n）数据类型。如果该列存储的数据长度相差比较大，由应该考虑使用varchar（n）。（2）如果存储的数据的长度虽然不是完全相同，但长度差别不大，且希望提高查询的执行效率，可以考虑char（n）数据类型，如果希望降低数据存储的成本，则可以考虑使用varchar（n）数据类型。（2）Unicode字符数据类型Unicode标准为全球商业领域中广泛使用的大部分字符定义了一个单一的编码方案。所有的计算机都采用单一的Unicode标准，Unicode数据中的位模式一致翻译成字符。这确保了同一个位模式在所有的计算机上总是转换成同一个字符。因此当数据库中存储的数据有可能涉及到多种语言时，应该使用Unicode数据类型。用于存储Unicode字符数据的数据类型包括nchar、nvarchar、ntext。就像char、varchar、text类型一样，nchar、nvarchar分别用于存储固定长度和可变长度的Unicode的字符数据。ntext也是将要被取消的数据类型，可以使用nvarchar（max）来代替ntext数据类型。由于每一个Unicode字符数据需要两个存储字节，因此Unicode数据类型的存储范围是1到4000字节。Unicode常量使用N开头来指定：如：N'学生信息系统'.3．日期和时间数据类型日期和时间数据用于存储日期和时间类型的数据，两者区别在于表示的日期和时间范围、精度不同。1）datetime：可以存储从1753年1月1日到9999年12月31日的日期和时间，精确度为3.33毫秒。2）smalldatetime：可以存储从1900年1月1日到2079年6月6日的日期和时间，精确度为分。在向表中输入时间日期数据时，可以使用SETDATEFORMAT语句改变时间的格式。当使用数据日期格式时，在字符串中可以使用‘／’、‘－’、‘.’来作为分隔符。建议用户在大型应用中不要使用smalldatetime数据类型。4．二进制数据类型SQLServer系统提供了３种二进制数据类型，即存储固定长度的binary，存储可变长度的varbinary以及存储图像信息的image。binary(n)和varbinary(n)的数据长度由n来确定，n的取值范围是１到8000，默认值是１，如果存储二进制数据大小一致，则使用binary(n)数据类型，如果要存储的数据类型之间大小差别较大，则使用varbinary(n)数据类型，如果存储数据大于8000字节，就必须使用varbinary(max)数据类型。微软公司建议使用varbinary(max)来代替image数据类型。5．其他数据类型除上面介绍的数据类型外，SQLServer2005系统还提供了cursor、sql_variant、table、timestamp、uniqueidentifier和xml数据类型，使用这些数据类型可以完成对特殊数据对象的定义、存储和使用。cursor：是变量或存储过程OUTPUT参数的一种数据类型，这些参数包含对游标的引用。使用cursor数据类型创建的变量可以为空。该变量只能用