第4章数据库中表的建立

第4章数据库中表的建立

4.1数据表概述4.2数据类型4.3创建数据表4.4修改数据表4.5删除数据表4.6数据完整性与约束4.7数据库关系图4.8使用数据表4.9临时表4.10分区表4.1数据表概述

4.1.1关系型数据表

SQLServer是关系型的数据库管理系统。所谓关系型数据库是指数据库内数据的组织模型是关系模型。关系模型的特点可以用一张二维表格来描述，表由行和列两部分组成。表是数据库中最重要的对象，是存储数据的地方，是一种结构化的文件，可用来存储一些特定数据类型的数据。表的形式如下图：4.1.4SQLServer2008数据表类型

在SQLServer2008中，共有四种类型的数据表：系统表、普通表、临时表、分区表。系统表是由SQLServer系统提供的，用于存放系统运行信息的数据表，例如有关服务器配置、数据库选项等信息都保存在系统表中。普通表，也即用户表，是由用户创建的，用于存储用户数据的数据表。普通表是用户使用SQLServer存储和管理数据的对象，用户数据保存在普通表中。临时表，是因用户、应用程序或者系统运行需要临时创建的数据表。该数据表只能临时保存在临时数据库tempdb中，当用户断开连接或者SQLServer服务重启或停止时，临时表会丢失。临时表根据用户使用权限的不同，可以划分成为两大类：全局临时表和本地临时表。全局临时表在创建之后，所有用户和连接都可访问；本地临时表，只能供创建它的用户或连接访问。分区表。分区表是一种特殊的数据表，用于将大型数据表分割成多个较小数据表，以提高数据管理性能的场合4.2数据类型

在SQLServer2008中提供的系统内置数据类型，共有37种，可以分为：数值型数据类型、字符型数据类型、日期型数据类型、货币型数据类型、二进制型数据类型、程序用数据类型和其他数据类型等七大类。1.字符数据类型

字符数据类型可以用来存储各种字母、数字符号和特殊符号。

Char：其定义形式为char（n），每个字符和符号占用一个字节的存储空间。

Varchar：其定义形式为varchar（n）。用varchar数据类型可以存储长达8K个字符的可变长度字符串。以上n从1－8000。以下n从1－4000。用于存储用两个字节才能存储的双字节字符。如：汉字。

Nchar：其定义形式为nchar（n）。

Nvar(char|max)：其定义形式为nvarchar（n）。

Max表示最大存储大小为231-1（1073741832）个字符。2.整型数据类型整型数据类型是最常用的数据类型之一，它主要用来存储整数值，可以直接进行数据运算，而不必使用函数转换。

int（integer）：int（或integer）数据类型可以存储从-231（-2147483648）到231-1（2147483647）范围之间的所有正负整数(4个字节)。

Smallint：可以存储从-215（-32768）到215-1（32768）范围之间的所有正负整数(2个字节)。

Tinyint：可以存储从0到255范围之间的所有正整数(1个字节)。

Bigint：与int相类似，但存储的范围更大，存储范围在-263（-9223372036854775808）至263（9223372036854775807）之间，占用8个字节的存储空间。适用于存储长度超过int范围的整型数据。3.浮点数据类型浮点数据类型用于存储十进制小数。

Real：可以存储正的或者负的十进制数值，最大可以有7位精确位数。

Float：可以精确到第15位小数，其范围从-1.79E-308到1.79E+308。

Decimal和numeric：Decimal数据类型和numeric数据类型完全相同，它们可以提供小数所需要的实际存储空间，但也有一定的限制，可以用2到17个字节来存储从-1038-1到1038-1之间的数值。4.日期和时间数据类型Date：用于存储日期的数据类型，其范围从0001年1月1日至9999年12月31日，需占用10个字节的存储空间。数据格式为YYYY-MM-DD，不包含具体的时间。

Datetime：用于存储日期和时间的结合体。它可以存储从公元1753年1月1日零时起到公元9999年12月31日23时59分59秒之间的所有日期和时间。

Smalldatetime：与datetime数据类型类似，但其日期时间范围较小，它存储从1900年1月1日到2079年6月6日内的日期。datetime2(n)：与datetime相类似，不同之处是datetime2秒的小数部分精度更高，存储范围更大为：0001年1月1日至9999年12月31日，秒数可以精确到小数点后7位.n表示精度长度：0--7。4.日期和时间数据类型

datetimeoffset(n)：用于存储与日期和时区相关的日期、时间数据。存储的日期时间数据，需要转化成为UTC（CoordinatedUniversalTime-协调世界时）值的时间，即需要根据时区关系进行换算。如要存储北京时间2010-01-0110：00：00需换算存储为2010-01-0118：00：00，该类型的格式为YYYY-MM-DDhh:mm:ss[.nnnnnnn][+|_]hh:mm。占用的存储空间也会因n的取值不同而不同，在26至34个字节之间。

time(n)：是一种专用于存储时间的数据类型，与datetime相比不同之处在于没有日期值。格式为hh:mm:ss[.nnnnnnn]，占用的存储空间因n的不同，范围为3至5个字节。5.货币数据类型Money：用于存储货币值，存储在money数据类型中的数值以一个正数部分和一个小数部分存储在两个4字节的整型值中，存储范围为-922337213685477.5808到922337213685477.5808，精度为货币单位的万分之一。

Smallmoney：与money数据类型类似，但其存储的货币值范围比money数据类型小，其存储范围为-214748.3468到214748.3467。6.文本和图片数据类型

Text：用于存储大量文本数据，其容量理论上为1到231-1（2147483647）个字节，但实际应用时要根据硬盘的存储空间而定,如：超过8K的大字符串,如HTML文档的全部内容。

Ntext：与text数据类型类似，存储在其中的数据通常是直接能输出到显示设备上的字符，显示设备可以是显示器、窗口或者打印机。(Unicode字符--统一的字符编码标准.采用双字节对字符进行编码

)Image：用于存储照片、目录图片或者图画，其理论容量为231-1（2147483647）个字节，（作为一组二进制的数据流存储）。如MicrosoftWord文档、MicrosoftExcel电子表格、包含位图的图像、图形交换格式(GIF)文件和联合图像专家组(JPEG)文件。

7.二进制数据类型Binary：其定义形式为binary(n)，数据的存储长度是固定的，即n+4字节，当输入的二进制数据长度小于n时，余下部分填充0。

Varbinary：其定义形式为varbinary(n)，数据的存储长度是变化的，它为实际所输入数据的长度加上4字节。其它含义同binary。

varbinary(max)：与varbinary相似，只是最大存储长度可以超过8000字节，达231-1（1073741832），适合代替image类型来存储图像、Word文档、应用程序等二进制型数据。8.位数据类型Bit：称为位数据类型，其数据有两种取值：0和1，长度为1字节。一般用于保存用来表示逻辑值的数据.如,是否会员,是否是新消息等.9.程序用数据类型hierarchyid：SQLServer2008新增的一种用于存储层次化结构型数据的数据类型。对于商品目录、组织机构等具有层次化结构的数据，采用hierarchyid来存储，可以利用hierarchyid提供的函数，非常方便地实现数据的存储和节点搜索。geometry：一种用于存储平面几何对象（平面球）的数据类型，如点、多边形、曲线等11种几何度量中的一种。Geography：用于存储GPS等全球定位类型的地理数据（椭圆球），以纬度和经度为度量来存储。9.程序用数据类型XML：用于存放整个XML文档或者部分片段。Cursor：这是一种变量或存储过程的输出参数使用的数据类型，也称为游标。Cursor提供了一种逐行处理查询数据的功能。用cursor定义的变量只能用于定义游标和与游标有关的语句，不能在表设计时使用。Table：用于存储对表或者视图处理后的结果集的数据类型。这种数据类型使得变量可以存储一个表，从而使函数或过程返回查询结果更加方便、快捷。sql_variant：是一种允许存储多个不同类型数据值的数据类型，除了varchar(max)、nvarchar(max)、text、image、sql_variant、sql_variant(max)、xml、ntext、rowversion等之外的数据类型都可以存储。10.其他数据类型

rowversion：用于存储由SQLServer产生的可标注数据行唯一性的二进制数据。每次行数据发生变化时，该值也会发生变化，用于反映修改的记录。在早先的版本中该数据类型对应的是timestamp。

Uniqueidentifier：用于存储一个16字节长的二进制数据类型，它是SQLServer根据计算机网络适配器地址和CPU时钟产生的唯一号码而生成的全局唯一标识符代码（GloballyUniqueIdentifier，简写为GUID）。4.2.2用户自定义数据类型

在SQLServerManagementStudio创建用户自定义数据类型的操作步骤如下：1、在“对象资源管理器”窗口，选择“数据库”→“可编程性”→“类型“→“用户定义数据类型”，右键单击之，选择“新建用户定义数据类型”，弹出“新建用户定义数据类型”对话框。2、在“新建用户定义数据类型”对话框中，“名称”项输入“PostCode”，“数据类型”选择“varchar”，指定长度为“6”，选中“允许NULL值”，表示允许使用此数据类型定义的列可以不输入数据。3、单击“确定”，保存自定义的数据类型。这样在“用户自定义数据类型”节点下，可以看到新创建的数据类型，在当前数据库中，可以像使用系统数据类型一样使用。4.2.2用户自定义数据类型创建用户自定义数据类型的TSQL语句需要调用系统存储过程“sp_addtype”，其语法如下：sp_addtype[@typename=]type,[@phystype=]'system_data_type'[,[@nulltype=]'null_type'];各参数所代表的含义如下：@typename，自定义的数据类型的名称，在数据库中不能与已有数据类型名称重复。@phystype，现有的系统数据类型，用于作为自定义数据类型的基础。@nulltype，是否允许该数据类型保存NULL值。Execsp_addtypeaddress,'varchar(80)','notnull'删除自定义的数据类型删除用户自定义数据类型的TSQL语句需要调用系统存储过程“sp_droptype”，其语法如下：

sp_droptypetype_nameexecsp_droptypeaddress其运行结果如下：（1row(s)affected）（0row(s)affected）Typehasbeendropped.4.3创建数据表

4.3.1使用SSMS创建数据表

DEMODEMO4.3.1使用SSMS创建数据表4.3创建数据表4.3.2使用TSQL创建数据表

在SQLServer2008中创建数据表的TSQL语句是“CREATETABLE”，基本语法如下：CREATETABLE

[database_name.[owner].|owner.]table_name略为当前库

({<column_definition>|column_name[AS]computed_column_expression|<table_constraint>}[，…n])

[ON{filegroup|DEFAULT}][TEXTIMAGE_ON：{filegroup|DEFAULT}]<column_definition>::={column_namedata_type}创建表的各参数的说明如下：database_name：用于指定在其中创建表的数据库名称。owner：用于指定新建表的所有者的用户名。table_name：用于指定新建的表的名称。column_name：用于指定新建表的列的名称。computed_column_expression：用于指定计算列的列值的表达式。该列是通过服务器计算产生，不能对其赋值，也不能添加PRIMARYKEY，UNIQUE，DEFAULT，FOREIGN

KEYON{filegroup|DEFAULT}：用于指定存储表的文件组名。TEXTIMAGE_ON：用于指定text、ntext和image列的数据存储的文件组。data_type：用于指定列的数据类型。DEFAULT：用于指定列的缺省值。constant_expression：用于指定列的缺省值的常量表达式。IDENTITY：用于指定列为标识列。当新的数据行插入表中时，系统为该列提供一个唯一的递增数值。Seed：用于指定标识列的初始值。Increment：用于指定标识列的增量值。NOTFORREPLICATION：用于指定列的IDENTITY属性,在把从其它表中复制的数据插入到表中时不发生作用，即不重新生成列值，使得复制的数据行保持原来的列值。ROWGUIDCOL：用于指定列为全球唯一鉴别行号列。COLLATE：用于指定表使用的校验方式(排序规则)。column_constraint和table_constraint：用于指定列约束和表约束。4.3.2使用TSQL创建数据表

EX1CREATETABLE[dbo].订单表(

orderIDintIDENTITY(1,1)NOTNULL,

ordertimedatetimeNULLDEFAULT

Getdate(),

customerIDintNOTNULL,

StatusbitNOTNULLDEFAULT(0),

shiptimedatetimeNULL)EX2：创建一个雇员信息表其SQL语句的程序清单如下：

CREATETABLEemployee(numberintnotnullIDENTITY,

namevarchar(20)NOTNULL,

sexchar(2)NULL,

birthdaydatetimenull,

hire_datedatetimeNOTNULL

DEFAULT(getdate()),

professional_titlevarchar(10)null,

salarymoneynull,

memontextnull)其余例子见P70-72，例4-1,4-2。EX3:其SQL语句的程序清单如下：

CREATETABLEdbo.stud_info(stud_idintCONSTRAINTperid_chkNOTNULLPRIMARYKEY,

namenvarchar(5)NOTNULL,

birthdaydatetime,

gendernchar(1),

addressnvarchar(20),

telcodechar(12),

zipcodechar(6)

CONSTRAINTzip_chkCHECK(zipcode

LIKE'[0-9][0-9][0-9][0-9][0-9][0-9]'),

DeptcodetinyintCONSTRAINTDeptcode_chk

CHECK(Deptcode<100),

salarymoneyDEFAULT500)EX4:其SQL语句的程序清单如下：

CREATETABLEtsing_DB1.dbo.stud_score(yearintNOTNULL,Stud_idintNOTNULL,math_scorenumeric(4,1)CHECK(math_score>=0andmath_score<=100),engl_scorenumeric(4,1)CHECK(engl_score>=0andengl_score<=100),comp_scorenumeric(4,1)CHECK(comp_score>=0andcomp_score<=100),CONSTRAINTpk_chkPRIMARYKEY(year,stud_id))EX5:带有参照性约束的表的创建：被参照表：CREATETABLEtsing_DB1.dbo.device_manage(dev_idvarchar(15)

CONSTRAINTpd_chkNOTNULLPRIMARYKEY,

dev_namevarchar(20)NOTNULL,

lab_idvarchar(20)NOTNULL,

dev_qtyint,

unit_pricemoney,

addressnvarchar(20),

supply_idvarchar(15))EX6:带有参照性约束的表的创建：参照表：CREATETABLEtsing_DB1.dbo.device_use(experiment_namevarchar(20)NOTNULLPRIMARYKEY,

experiment_labvarchar(20),

experiment_datedatetime,

stud_idint,

dev_idvarchar(15)

CONSTRAINTfk_chkREFERENCES

device_manage(dev_id))4.4修改数据表

4.4.1使用SSMS修改数据表

1、在“对象资源管理器”窗口中，展开服务器、数据库节点，找到要修改的数据表。2、右击要修改的数据表，在右键菜单中选择“设计”。3、在打开的“表设计器”窗口中，可以对列名、数据类型等进行修改。4、如要添加新的列，可以单击列列表底部的空白行，输入新的列名、数据类型，设置是否允许为NULL等。5、修改完毕后，可以单击工具栏的“保存”按钮，保存修改后的数据表。4.4修改数据表

4.4.2使用TSQL修改数据表

1、使用TSQL添加新列添加新列使用的TSQL语句为“ALTERTABLE”，基本语法如下：ALTERTABLEtable_nameADDColumn_name[Default<value>][NOTNULL][IDENTITY][UNIQUE]各参数含义如下：table_name，待修改的数据表的名称。Column_name，添加的新列的名称。Default<value>，默认值，可选项。NOTNULL，是否允许为空。IDENTITY，是否作为标识列，一个表中只能有一个标识列。UNIQUE，是否创建唯一约束。4.4修改数据表

例如，要在“客户数据表（Customers）”中添加一个新列：CustomerType，数据类型为varchar(20)，默认值为“个人客户”，则修改表的语句如下：ALTERTABLE客户数据表ADDCustomerTypevarchar(20)NOTNULLDefault('个人用户')4.4修改数据表2、使用TSQL修改现有列

SQLServer2008允许修改现有列的数据类型、数据长度和默认值等，修改现有列的语法如下：ALTERTABLEtable_nameALTERColumnColumn_namenew_data_type各参数含义如下：table_name，待修改的数据表的名称。Column_name，修改的列的名称。new_data_type，列的新数据类型。ALTERTABLE客户数据表ALTERColumnShipAddressvarchar(200)例如，要将“客户数据表（Customers）”中的“ShipAddress”的数据类型修改为varchar（200），4.4修改数据表3、使用TSQL删除现有列删除现有列的语句如下：ALTERTABLEtable_nameDROPColumnColumn_name[，...n]各参数含义如下：table_name，待修改的数据表的名称。Column_name，待删除的列的名称。例如，要“订单细节表（Orderdetail）”中删除“SunTotal”列，则语句如下：ALTERTABLE订单细节表DROPColumnSunTotalEX：创建、修改一个雇员信息表其SQL语句的程序清单如下：createtableemployees(idchar(8)primarykeynamechar(20)notnull,departmentchar(20)null,memochar(30)nullageintnull,)altertableemployeesaddsalaryintnullaltertableemployeesdropcolumnagealtertableemployeesaltercolumnmemovarchar(200)null例见P74例4-34.4修改数据表4、关于计算列计算列在数据表中是一种特殊的列，它不需要指定数据类型，其值来源于其他列的表达式计算，这些表达式可以是函数、常量或表中的其它列。默认情况下，计算列并不会将数据值实际存储在数据表中，只是在需要时，如通过查询语句获取时，才会重新计算表达式来获取值。因此，计算列可算是一种虚拟的列。计算列可以在表创建时，与参与表达式的其他列一起定义；也可以通过添加新列时添加进来。例如，以下语句可以为“订单细节表（Orderdetail）”新增一个名称为“SunTotal”的计算列，其数值取自列“Sale_unitprice”与“sales”的乘积。ALTERTABLE订单细节表ADDSubTotalAsSale_unitprice*sales4.5删除数据表

4.5.1使用SSMS删除数据表

1、在SQLServerManagementStudio中，展开“对象资源管理器”的服务器、数据库节点，在数据库节点中展开“表”节点。2、选中要删除的数据表，右击该数据表，然后在右键菜单中选择“删除”,弹出“删除的对象”对话框。3、在“要删除的对象”列表中可以查看待删除数据表的信息，如果当前其他程序正在使用该数据表，则可以在“消息”列中查看到相关的信息。单击“确定”，可以完成对数据表的删除。4.5删除数据表4.5.2使用TSQL删除数据表

删除数据表的TSQL语句是“DROPTABLE”，语法规则相当简单，如下所示：DROPTABLEtable_name[，...n]如要删除一个数据表，可以执行以下代码：DROPTABLE客户数据表如果要一次删除多个数据表，可以执行如下代码：DROPTABLE客户数据表,订单细节表4.6使用数据表

创建完成后的数据表可以用来保存用户数据，也可以编辑和修改数据。这些用户数据在数据表中可以长期存在，除非人为删除或者出现系统故障，造成数据丢失。右键单击表名，选择“编辑前200行”，则打开数据编辑界面，可以：1、输入数据2、编辑数据3、删除数据4.7临时表

临时表是一种特殊的数据表。与普通表不同，临时表只能临时存在，在创建临时表的用户断开连接或者SQLServer服务停止、重启后就会丢失。另外，临时表统一存放在系统数据库tempdb，也与普通表一般存放在特定的用户数据库中不同。临时表一般用于存放一些临时性的数据。比如，有些数据需要联接多个数据表，并且在应用程序中需要多次使用时，那么这些数据可以保存为临时表，用户访问临时表即可获取需要的数据。从而，可以避免多次重复地生成相同数据，增加服务器负荷。临时表根据使用范围的不同，可以分为局部临时表和全局临时表。局部临时表只能供创建者使用，全局临时表可在生命周期内供所有连接使用。4.7.1创建临时表

临时表只能通过TSQL语句来创建，无法在SQLServerManagementStudio中新建。创建临时表的语句与普通表的创建基本相同，唯一不同是数据表名称前要添加“#”。一个“#”表示创建的是局部临时表，“##”表示创建的是全局临时表。以下代码创建了一个名称为“#订单细节表”临时表，无论该段代码在哪个“数据库”上执行，所创建的临时表都保存在tempdb数据库中。CREATETABLE[dbo].#订单细节表

(

DetailIDintPRIMARYKEYIDENTITY(1,1)NOTNULL,

orderidintNOTNULL,

productidintNOTNULL,

Sale_unitpricedecimal(10,2)NOTNULL

)4.7.2使用临时表

临时表也无法在SQLServerManagementStudio中打开和查看，要在临时表中输入数据可以采用TSQL语句来实现。如要在局部临时表输入数据可以采用以下代码：insertinto#订单细节表(orderid,productid,Sale_unitprice)values(1,1,1.1)使用分区表的主要目的，是为了改善大型表以及具有各种访问模式的表的可伸缩性和可管理性。分区一方面可以将数据分为更小、更易管理的部分，为提高性能起到一定的作用；另一方面，对于如果具有多个CPU的系统，分区可以是对表的操作通过并行的方式进行，这对于提升性能是非常有帮助的。创建分区表的步骤：1、创建分区函数。分区函数是对数据进行分区的依据，分区函数定义了按分区列的值，将数据行映射到分区的机制。2、创建分区方案。分区方案根据分区函数，将不同数据分区映射到不同的文件组中。通过文件组中数据文件在硬盘中物理位置的不同，实现将数据分别存储到不同的硬盘或分区中，从而可以进一步提高存储的效率和系统性能。3、创建分区表。根据分区方案的要求创建分区表，今后数据存储时会按照分区函数的设定分区存放。╳4.8分区表

创建分区函数CREATEPARTITIONFUNCTIONmyRangePF1(int)ASRANGELEFTFORVALUES(1,100,1000)创建分区方案（架构）

CREATEPARTITIONSCHEMEmyRangePS1ASPARTITIONmyRangePF1TO(test1fg,test2fg,test3fg,test4fg)创建分区表CREATETABLE[dbo].订单表_RANG(orderIDintIDENTITY(1,1)NOTNULL,ordertimedatetimeNULLDEFAULTGetdate(),customerIDintNOTNULL,StatusbitNOTNULLDEFAULT(0),shiptimedatetimeNULL)ONORDERPS(ordertime))

╳4.8分区表

4.9ＴＳＱＬ表的编辑４.９.１插入数据向表中添加新的记录或在记录中插入部分字段的数据。

INSERT语句语法：INSERT[INTO]table_name[(column1,column2…)]VALUES(value1,value2…)1.整行数值插入例1:下面的例子将向tsing_DB1中的stud_info表插入10行记录。Usetsing_DB1goINSERTdbo.stud_infoValues(0811,'张源','12-12-19','男','北京市海淀区',

'64572345','100080',3,260)GoINSERTstud_infoValues(970890,’赵明’,’08/05/1979’,’1’,’上海市浦东区’,,’201700’,2,260)GoINSERTstud_infoValues(‘970801,’王刚’,’1/5/1980’,’1’,’天津市’,,’430000’,3,260)Go….例2:表device_manage中第一个字段与表device_use中的最后一个字段是参照性约束的关系，被参照的表为device_manage,在表device_use中定义了参照性约束，其输入方法如下：首先向device_manage表中插入4行记录Usetsing_DB2goINSERTdevice_manageValues(‘0789,’分光计’,’光学实验室’,20,5000,’北京光学仪器厂’)GoINSERTdevice_manageValues(‘0284’,’高能电子管’,’电子实验室’,50,900,’中科院电子所’)GoINSERTdevice_manageValues(‘0394,’硅酸盐晶片’,’材料实验室’,1000,200,’清华大学材料系’)Go………………