第4章 数据库表

1、第4章 数据库表【学习目标】本章将主要介绍数据库表操作的基本知识，包括数据库表的设计、定义与维护过程。其中包括使用SSMS管理创建、管理数据库表，SQL Server 2005中提供的数据类型以及使用T-SQL语句创建与管理数据库表。本章学习要点：u SQL Server2005数据类型u 数据库表的类型u 数据库表的创建u 数据库表的管理【学习导航】表是数据库中最基本、最重要、最核心的对象，是组织数据的方式，是存储数据的地方。从某种意义上说，管理数据库说是管理数据库中的表。如何合理的设计表结构将直接影响到数据库中数据的使用效率。本章将全面讨论有关表的管理技术与方案。本章主要内容见图4-1所示

2、的学习导航。图4-1 本章学习导航4.1 数据表的概念与数据类型4.1.1 SQL Server表的概念1表的的概念表在关系数据库中是用来表示实体的，用来存储和组织数据、具有行、列结构的数据库对象。在SQL Server 数据库中，表定义为列的集合，数据在表中是按行、列的格式组织结构的。行，也称为记录，代表一个唯一的实体。列，也称为字段，代表对实体的属性。由上可知：表有以下特点：代表实体，有唯一的名称，由行和列组成，行和列的顺序可以是任意的且列名不允许重复。2表的分类在Microsoft SQL Server 2005系统中，表大致分为4类，即普通表、已分区表、临时表和系统表，其中每一种表都有

3、其自身的作用和特点。普通表：也称为标准表或基本表，即通常说的在数据库中存储数据的表，是经常使用的表对象，也是最重要、最基本的表，简称表。已分区表：是为提高系统使用效率而派生的表，将数据水平划分成多单元，这些单元可以分散到数据库的多个文件组里，来实现对单元数据的并行访问。若表中的数据量非常庞大，且数据经常以不同的方式来访问，建立分区是一个最好的选择。其优点是：方便管理大型表，来提高对数据的使用效率。临时表：即临时创建的，不可以永久存在的表。临时分为本地临时表和全局临时表。一旦临时表创建后，可以一直存储到SQL Server实例断来链接为止。其中本地临时表只对创建者可见，而全局临时表在创建后对所有

4、的用户和链接都可见。系统表：主要存储有关SQL Server服务器配置、数据库设置、用户和表对象的描述等系统信息。通常只有DBA才能使用系统表。3表的设计由于数据表是数据库对象中最重要的一个对象，所以数据表的设计好坏直接关系到数据库的维护性和可用性。因此在设计表时，要根据数据库逻辑结构设计的要求确定需要什么样的表、什么样的数据、列的名称、所包含的数据类型，以及是否允许空值、索引、主键以及外键的约束等情况。创建一个表最有效的方法是将表中所需要的信息一次定义完成。然后在使用的过程中再逐一完善。在Microsoft SQL Server 2005系统中，创建表有如下限制：l 每个数据库中最多可创建2

5、0亿个表；l 每个表中最多可创建1个聚集索引和249个非聚集索引；l 每个表最多可创建1024列；l 每条记录最多可以占8060个字节。4.1.2 SQL Server2005数据类型数据类型是用来表现数据特征的，决定了数据在计算机中的存储格式、存储长度以及数据精度和小数位数等属性。在创建数据表时，表中的每一个列、局部变量、表达式等都离不开数据类型的支持。确定了数据类型也就确定了该数据的取值范围。在Microsoft SQL Server 2005系统中，需要使用数据类型的对象包括：表中的列、视图中的列、定义的局部变量、存储过程中的参数、Transact-SQL函数、存储过程的返回值等。Mic

6、rosoft SQL Server 2005系统提供了28种数据类型。这些数据类型可以分为数字数据类型、字符数据类型、日期和时间数据类型、二进制数据类型以及其他数据类型。1数字数据类型使用数字数据的数据类型称为数字数据类型。该数据类型可以参加各种数学运算。SQL Server2005系统提供了11种数字数据类型，即bigint、int、smallint、tinyint、decimal、numeric、money、smallmoney、float、real、bit种。实际上还可以把这些数据类型继续分类，如整数类型和小数类型，其中整数类型有bigint、int、smallint、tinyint。还

7、可以分为精确数字类型和近似数字类型，其中float和real都是近似数字类型。也可以按是否能表示货币金额，分为货币类型和非货币类型等。（1）整数数据类型整数数据类型表示可以存储整数精确数据，SQL Server2005系统提供了4种整数数据类型：l bigint ：长度是8个字节，且可以存储正负数，取值范围是-263至263-1。l int ：长度是4个字节，且可以存储正负数，取值范围是-231至231-1，int是最常用的数据类型，当int数据类型表示的数据长度不足时，才考虑使用bigint数据类型。l smallint ：长度是2个字节，也可以存储正负数，取值范围是-215至215-1l

8、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表示数字的

9、小数位数。精度p的取值范围是1至38，默认值是18。小数位s的取值范围必须是0到p之间的值，默认值是0。这两种数据类型在功能上是等价的，只是名称不同而已。实际上，在SQL Server2005系统中，这两种数据类型是作为完全相同的一种数据类型来对待的。（3）money和smallmoney数据类型money和smallmoney也称为货币数据类型，这两种数据类型的差别在于存储字节的大小和取值范围不同。1）money：需要8个存储字节，取值范围是-263至263-1，精确到货币单位的千分之十。2）smallmoney：需要4个存储字节，取值范围是-215至215-1。money和smallmon

10、ey是一种确定性数值的数据类型，因为它们的精度和小数位都是确定的。但是这两种数据类型也有一些与其他数字数据类型不同的地方。第一，当表示货币时一定要加上$符号。第二，它们的小数位最多是4位，也就是精确到货币单位的万分之一。第三，当小数位位数超过4位时，系统自动按照四舍五入进行处理。（4）float和real数据类型若要进行科学计算，并且希望存储更大的数值，但对数据的精度要求不是绝对严格，可以考虑使用float和real数据类型，这两种数据类型用于表示指定数据的大致数据值的数据类型。1）float（n）：存储8个字节，取值范围是-1.79E+3081.79E+308的浮点数，其中n为精度，n是从1

11、到53的整数。2）real：存储从-3.04E+38到3.04E+38之间的浮点数，存储大小为4个字节。需要注意的是，如果某些列中的数据或变量将会参与科学计算，建议考虑为这些数据对象指定float或real数据对象，否则运算过程中会形成比较大的误差。（5）bit数据类型bit可以存储1、0、null数据的数据类型，这些数据主要用于一些条件逻辑判断。也可以把TRUE和FALSE数据存储到bit数据类型中，这时需要按照字符格式存储TRUE和FALSE数据。2字符数据类型SQL Server系统提供了6种字符数据类型，即char、varchar、text、nchar、nvarchar、ntext,其

12、中char、varchar、text是非Unicode字符数据，nchar、nvarchar、ntext是Unicode字符数据。（1）非Unicode字符数据类型非Unicode字符数据用于存储汉字、英文字母、数字、标点和各种符号，输入时必须用半角单引号括起来，是定长的字符数据类型：1）char（n）：为固定长度存储字符串的数据类型，n取值范围是18000，存储空间大小为n个字节。2）varchar(n|max) ：变长存储字符串的数据类型，n 可以是1和8000之间的数，max表示最大存储大小为231-1,存储大小为所输入数据的实际长度+2个字节。3）text ：可以存储最大长度为231-

13、1个字节的字符数据，微软公司建议尽量避免使用text数据类型，而应该使用varchar(max)存储大文本数据。在选择使用char（n）和varchar(n|max)数据类型时，可以考虑使用下面的原则：（1）如果该列存储的数据长度都相同，这时应该使用char（n）数据类型。如果该列存储的数据长度相差比较大，由应该考虑使用varchar（n）。（2）如果存储的数据的长度虽然不是完全相同，但长度差别不大，且希望提高查询的执行效率，可以考虑char（n）数据类型，如果希望降低数据存储的成本，则可以考虑使用varchar（n）数据类型。（2）Unicode字符数据类型Unicode标准为全球商业领域中

14、广泛使用的大部分字符定义了一个单一的编码方案。所有的计算机都采用单一的Unicode标准，Unicode数据中的位模式一致翻译成字符。这确保了同一个位模式在所有的计算机上总是转换成同一个字符。因此当数据库中存储的数据有可能涉及到多种语言时，应该使用Unicode数据类型。用于存储Unicode字符数据的数据类型包括nchar、nvarchar、ntext。就像char、varchar、text类型一样，nchar、nvarchar分别用于存储固定长度和可变长度的Unicode的字符数据。ntext也是将要被取消的数据类型，可以使用nvarchar（max）来代替ntext数据类型。由于每一个U

15、nicode字符数据需要两个存储字节，因此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日的日期和时间，精确度为分。在向表中输入时间日期数据时，可以使用SET DATEFORMAT语句改变时间的格式。当使用数据日期格式时，在字符串中可以使

16、用、.来作为分隔符。建议用户在大型应用中不要使用smalldatetime数据类型。4二进制数据类型SQL Server系统提供了种二进制数据类型，即存储固定长度的binary，存储可变长度的varbinary以及存储图像信息的image。binary(n)和varbinary(n)的数据长度由n 来确定，n的取值范围是到8000，默认值是，如果存储二进制数据大小一致，则使用binary(n)数据类型，如果要存储的数据类型之间大小差别较大，则使用varbinary(n)数据类型，如果存储数据大于8000字节，就必须使用varbinary(max)数据类型。微软公司建议使用varbinary(m

17、ax)来代替image数据类型。5其他数据类型除上面介绍的数据类型外，SQL Server 2005系统还提供了cursor、sql_variant、table、timestamp、uniqueidentifier和xml数据类型，使用这些数据类型可以完成对特殊数据对象的定义、存储和使用。l cursor：是变量或存储过程 OUTPUT 参数的一种数据类型，这些参数包含对游标的引用。使用 cursor 数据类型创建的变量可以为空。该变量只能用于与定义游标和使用游标的有关语句中，不能在诸如CREATE TABLE之类的语句中使用。l sql_variant：用于存储 SQL Server 支持的

18、各种数据类型（不包括 text、ntext、image、timestamp 和 sql_variant）的值。sql_variant 可以用在列、参数、变量和用户定义函数的返回值中。sql_variant 使这些数据库对象能够支持其他数据类型的值。类型为 sql_variant 的列可能包含不同数据类型的行。例如，定义为 sql_variant 的列可以存储 int、binary 和 char 值。sql_variant最大长度是8016个字节，其中包括基类型信息和基类型值，基类型值的最大长度是8000个字节。l table：一种特殊的数据类型，用于存储结果集以进行后续处理。table 主要用

19、于临时存储一组作为表值函数的结果集返回的行。l timestamp：公开数据库中自动生成的唯一二进制数字的数据类型。rowversion 通常用作给表行加版本戳的机制。存储大小为 8 个字节。rowversion 数据类型只是递增的数字，不保留日期或时间。若要记录日期或时间，请使用 datetime2 数据类型。l uniqueidentifier：是一个特殊的数据类型，这是一个具有16 个字节的全局唯一性标志符，用来确保对象的唯一性，可以通过使用NEWID函数得到该数据类型的初始值。l xml：是SQL Server 2005系统中新增的数据类型，用于存储XML的数据类型。可以像使用int数

20、据类型一样使用XML数据类型。需要注意的是，存储XML数据类型表示实例最大值是2GB。4.2 创建数据表在Microsoft SQL Server 2005系统中，既可以使用CREATE TABLE语句创建表，也可以使用可视化的Microsoft SQL Server Management Studio工具来创建。下面将分别介绍这两种方法。4.2.1 使用SQL Server Management Studio创建数据表数据表主要包括表结构、约束和数据三部分。因此创建表也就包括这三个部分：定义表结构、设置约束和添加数据三步，其中设置约束可以在定义表结构时创建，也可以在定义表完成后再创建，步骤如

21、下：（1）定义表结构：定义列的列名、数据类型、数据长度以及是否为空等属性。（2）设置约束：设置约束来限制输入值的取值范围，以保证输入数据的正确性和一致性。（3）输入数据：表建立完成后，就可以向表中输入数据。在SQL Server 2005系统中，可以使用SQL Server Management Studio中的表设计器完成。【案例4-1】在stu数据库中,创建系部信息表 Department ，表结构如表4-1所示：表4-1 系部信息表结构字段名数据类型长度是否为空约束d_id（系部代码）char2否主键d_name（系部名称）varchar30是d_director（系部负责人）varch

22、ar8是利用表设计器创建表操作步骤如下：（1）启动SQL Server Management Studio，在【对象资源管理器】窗口中，依次展开【数据库】【stu】【表】节点，右击【表】节点，单击右键，在弹出的快捷菜单中选择【新建表】命令，打开【表设计器】对话框。 。 （2）在【表设计器】对话框中，窗口上部分，其中每一行描述了表中的字段，每一行有三列，包括：列名、数据类型、长度以及是否允许为空的属性，当每选中一行时，在窗口下部分，就会显示相应的该列的详细属性：包括名称、长度、约束、规则等。如图4-2所示。图4-2 【表设计器】窗口（3）插入、删除列，在定义表结构时，可以在选中的某一字段的上边插

23、入一个新字段，也可以删除一个字段。操作如下：在表设计器窗口的上部分中右击该字段，在弹出的快捷菜单中选择【插入列】或【删除列】即可，如图4-3所示。图4-3 插入列可删除列（4）保存表。完成以上操作后，单击工具栏上的“保存”按钮，打开保存对话框，输入“Department”，单击“确定”按钮，完成了表的定义。 4.2.2 使用Transact-SQL创建数据表在SQL Server 2005中，除使用SQL Server Management Studio以图形界面创建数据表外,还可以在查询编辑器中用T-SQL语句中的CREATE TABLE语句创建数据表,下面介绍如何使用 CREATE TAB

24、LE语句创建数据表。CREATE TABLE语句是一种最灵活、最强大的创建表的方式，为了更好的理解CREATE TABLE语句，下面我们通过实例来介绍它的使用方法：【案例4-1】 在stu数据库中，建立一个 speciality （专业）表，表结构如下表4.2所示：表4-2 speciality （专业）表结构字段名数据类型长度是否为空sp_id（专业代码）char4否sp_name（专业名称）varchar20否d_id（系部代码）char2否利用T-SQL语句创建该表的步骤如下：（1）启动SQL Server Management Studio，单击工具栏上的“新建查询”按钮，打开查询编辑

25、窗口，输入如下SQL语句：USE stuGOCREATE TABLE speciality (sp_id char (4) NOT NULL,sp_name varchar (20) NOT NULL,d_id char(2) NOT NULL) GO（2） 输入上面代码后，单击工具栏上的“分析”按钮或用“CTRL+F5”键，对输入代码进行分析查询，通过检查后，单击工具栏上的“执行”按钮或用“F5”键，消息框中返回“命令成功完成”表示完成数据表的创建。（3）在“对象资源管理器”窗口中，右击表节点，在弹出的快捷菜单中选择“刷新”命令，就可看到新创建的“speciality”数据表。通过是例可以看

26、到，创建表的关键字是“CREATE TABLE”；表名是“speciality”，在括号内给出的是该表的三个字段，如：“sp_id char (4) NOT NULL”的含义是，字段名“sp_id”，数据类型是char，长度为4个字节，不允许出现NULL值，字段与字段定义之间用西文的逗号分开。实际上，在使用CREATETABLE语句创建表时，可以同时指定列的主健、外键以及其他约束、规则等属性，本节将不做详细介绍，后续章节将会详细介绍这些内容。【案例4-2】 在stu数据库中，建立一个class（班级）表，表结构如下表4.3所示：表4-3 class（班级）表结构字段名数据类型长度是否为空约束c

27、_id（班级代码）char9否主键c_name（班级名称）varchar20否sp_id（专业代码）char4否外键d_id(系部代码)char2否外键c_memo（备注）varchar50是利用T-SQL语句创建该表：USE stuGO CREATE TABLE class (c_id char (9) CONSTRAINT pk_bjdm PRIMARY KEY,c_name varchar (20) NOT NULL,sp_id char (4) CONSTRAINT fk_zydm REFERENCES speciality(sp_id) ,d_id char (2) CONSTRAI

28、NT fk_xbdm REFERENCES department (d_id),c_memo varchar(50)GO在创建“class”表时，sp_id char (4) CONSTRAINT fk_zydm REFERENCES speciality(sp_id)的含义是指定speciality 表的“sp_id”字段为“class”表的外键。有关主键、外键的详细介绍详见第4章。 4.3 数据的添加与查看4.3.1 数据的添加数据表定义完成后，此新表中并不包含任何数据记录，在SQL Server 2005中，可以通过SQL Server Management Studio中对象资源管理器

29、向表中添加数据，也可以使用T-SQL语句向表中添加数据（详细用法将在第6章中详细介绍），下面主要介绍第一种方法。下面我们以stu数据为例，介绍如何使用SQL Server Management Studio向表中添加数据的方法及步骤：（1）启动SQL Server Management Studio，在【对象资源管理器】窗口中，展开【数据库】节点，选择相应的【stu】数据库。（2）展开选中的数据库节点，再展开【表】节点，右键单击要添加数据的【表】，如【class】，在弹出的快捷菜单中选择【打开表】命令，就会打开【查询设计器】的结果窗口。 （3）在【查询设计器】的表中可以输入新记录，也可以修改和

30、删除已经输入的数据。如图4-4所示：图4-4 利用对象资源管理器向class表中添加数据4.3.2 数据的查看在SQL Server 2005中，可以通过SQL Server Management Studio中对象资源管理器查看表中的相关数据，也可以使用T-SQL语句完成（详细用法将在第6章中详细介绍），下面主要介绍第一种方法。下面我们以stu数据为例，介绍如何使用SQL Server Management Studio查看表中数据的方法及步骤：（1）启动SQL Server Management Studio，在【对象资源管理器】窗口中，展开【数据库】节点，选择相应的【stu】数据库。（2

31、）展开选中的数据库节点，再展开【表】节点，右键单击要添加数据的【表】，如【class】，在弹出的快捷菜单中选择【打开表】命令，就会打开【查询设计器】的结果窗口，来查看详细的数据信息。 4.4 数据表的的修改与删除4.4.1 数据表的修改当表创建好后，由于实际需要可能会对表的结构进行一些调整、修改操作，这些操作包括：修改表中列的名称、数据类型，添加列、调整列的次序、删除列等操作。在SQL Server 2005中，可以通过SQL Server Management Studio中对象资源管理器中打开相应表的表设计器完成，也可以使用T-SQL语句完成，下面详细介绍两种方法。1使用SQL Serve

32、r Management Studio中表设计器修改表结构使用表设计器修改表结构的操作方法及步骤如下：（1）启动SQL Server Management Studio，在【对象资源管理器】窗口中，展开【数据库】节点，选择相应的数据库，如【stu】。（2）展开【stu】节点，再展开【表】节点，右键单击要修改的【class】表，在弹出的快捷菜单中选择【修改】命令，启动表设计器。（3）若要修改字段名，选中要修改的字段，即可修改字段名、字段类型、字段长度、是否为空等属性。（4）若要添加,将光标移动到最后一个字段的下边，输入新字段的定义即可。 若要在某一字段前插入一个新字段，右键单击该字段，在弹出的快

33、捷菜单中选择【插入列】命令； 若要删除某字段，右键单击该字段,在弹出的快捷菜单中选择【删除列】命令。 修改完成后，点击快捷菜单栏中【保存】图标，所进行的修改操作立即生效。2使用T-SQL语句修改表结构使用T-SQL语句修改表结构包括：修改指定列的属性、增加列、删除列等操作，语法格式如下：修改指定列的属性：ALTER TABLE 表名 ALTER COLUMN 列名 更改后新的数据类型 ( 精度 , 小数位 ) NULL | NOT NULL 增加列ALTER TABLE 表名 ADD 列定义 ,n删除列ALTER TABLE 表名 DROP COLUMN 列名 ,n 【案例4-3】 在stu数

34、据库中，修改class（班级）表中c_name(班级名称)字段的数据类型为varchar(30),不允许为空。T-SQL语句如下：USE stuGOALTER TABLE classALTER COLUMN c_name varchar(30) NOT NULLGO【案例4-4】 在stu数据库中，为department （系部）表增加d_memo(系部描述) 列，字段的数据类型为varchar(50),允许为空。T-SQL语句如下：USE stuGOALTER TABLE departmentADD d_memo varchar(50) NULLGO【案例4-5】 在stu数据库中，删除te

35、acher （教师）表中t_memo(教师信息备注)。T-SQL语句如下：USE stuGOALTER TABLE teacherDROP COLUMN t_memoGO4.4.2 数据表的删除在实际数据库的管理应用过程中，由于各种原因，有时对一些不需要的表，要进行删除操作，一旦执行删除表的操作，该表的数据结构、记录、约束、索引等相关信息将全部删除。在SQL Server 2005中，可以通过SQL Server Management Studio中对象资源管理器来执行删除表的操作，也可以使用T-SQL语句完成，下面详细介绍两种方法。使用SQL Server Management Studio

36、删除表使用表设计器删除表的操作方法及步骤如下：（1）启动SQL Server Management Studio，在【对象资源管理器】窗口中，展开【数据库】节点，选择相应的数据库，如【stu】。（2）展开【stu】节点，再展开【表】节点，右键单击要删除的表，在弹出的快捷菜单中选择【删除】命令，在弹出的对话框中选择【确定】按钮即可完成该表的删除。删除操作完成后，该表的所有信息将被永久删除。2使用T-SQL语句删除表T-SQL中的DROP TABLE语句来实现，详细语法如下：DROP TABLE表名,.表名【案例4-6】 在stu数据库中，删除course（课程）、speciality(专业)表。T-SQL语句如下：USE stuGODROP TABLE course, specialityGO4.5案例应用1 创建表在Company数据库中创建如下结构的表：员工表：employee表：字段名数据类型长度是否为空员工编号char10否员工姓名varchar15否性别char2是出生日期datetime是部门编号char10否create table employee (员工编号char(10) not null , 员工姓名varchar(15) not null, 性别