数据unicode编码转换器

第3章表和数据完整性表是SQLServer数据库中最重要的数据对象,也是构建高性能数据库的基础.
在程序开发与应用过程中,创建数据库的目的是存储、管理和返回数据,而表是存储数据的基本单元.
数据表设计的优劣将影响磁盘空间使用效率、数据处理时内存的利用率以及数据的查询效率.
而数据完整性则是保证表中数据正确与完整的关键.
本章将介绍各种数据类型的特点和用途,数据表的创建、修改、管理与数据格式转换,以及实现数据完整性的方法和基本操作.
3.
1SQLServer2005的数据类型数据库中的所有数据都存放在数据表中,数据表按行与列的格式组织.
在创建列时,要为列指定列名、数据类型等属性.
本节将对SQLServer2005中的数据类型作一些简单说明.
数据类型是数据的一种属性,决定数据存储的空间和格式.
正确选择数据类型可以为数据库的设计和管理奠定良好的基础,对数据的存储和查询等操作也有着重要的影响.
为数据库对象选择数据类型时,可以为对象定义4个属性.
(1)对象包含的数据种类.
(2)所存储值占有的空间(字节数)和数值范围.
(3)数值的精度(仅适用于数值类型).
(4)数值的小数位数(仅适用于数值类型).
SQLserver2005提供的多种数据类型可以归纳为下列类别:数值类型、字符类型、日期时间类型、货币类型和其他数据类型,下面详细介绍各种数据类型.
3.
1.
1数值类型数值类型根据其所存储数据的精确与否,分为精确数值类型和近似数值类型.
1.
精确数值类型精确数值类型用来存储没有小数位的整数或定点小数.
使用任何算术运算符都可以操作这些数据类型中存储的数值,而不需要进行任何特殊处理.
表3-1列出了SQLServer支持的精确数值类型.
表和数据完整性65第3章表3-1精确数值类型类别数据类型字节数取值范围作用bigint8–2E63~2E63–1存储非常大的正负整数int4–2E31~2E31–1存储正负整数smallint2–32768~32767存储正负整数Integertinyint10~255存储小范围的正整数decimal(p,s)5~17–10E38+1~10E38–1最大可存储38位十进制数Exactnumericnumeric(p,s)5~17–10E38+1~10E38–1可以与decimal交换使用定点小数数据类型,用于存储小数,可具体分为numeric与decimal.
二者的存储长度与精度有关.
使用时,必须指明小数位数和精确度,如numeric(7,2)表示精确度为7.
在这组数据类型中,int和decimal是最常用的数据类型.
2.
近似数值类型近似数字数据类型可以存储十进制值,用于表示浮点数据.
由于浮点数据是近似值,此类型的数据不一定有精确的表示,可具体分为float和real两种.
而float或real数据类型中存储的数据只能精确到数据类型定义中指定的精度,不能保证小数点右边的所有数字都被正确存储.
例如,如果把1.
000908077存储在一个定义为float(8)的数据类型中,则该列只能保证精确地返回1.
000908.
SQLServer2005存储数据时对小数点右边的数进行四舍五入.
因此,在涉及该数据类型的计算时,会出现舍入误差.
只有在精确数据类型不够大,不能存储数值时,才可以考虑使用float.
表3-2列出了SQLServer支持的近似数字数据类型.
表3-2近似数字数据类型数据类型字节数取值范围作用float(p)4/8–2.
23E308~2.
23E308存储大型浮点数,超过十进制数据类型的容量real4–3.
4E38~3.
4E38仍有效,为满足SQL-92标准,已经被float替换了3.
1.
2字符类型字符类型是用于存储字符型数据的.
每种字符数据类型使用一个或两个字节存储每个字符,具体取决于该数据类型使用ASCII编码还是Unicode编码.
ASCII编码要求用8个二进制位来表示字母的范围.
ASCII字符串可以用来存储一个字符型数据序列,可具体分为char、varchar、text3种.
其中char为固定长度,varchar为可变长度,text可用于存储大量字符.
Unicode标准使用两个字节来表示每个字符.
Unicode字符串可以用来存储一个字符型数据序列.
在Unicode标准中,包括了以各种字符集定义的全部字符.
在SQLserver2005中,Unicode数据以nchar、nvarchar和ntext数据类型存储.
定义一个字符数据类型时,指定该列允许存储的最大字节数.
SQLServer2005数据库应用与开发(第二版)66例如,char(10)最多可以存储10个字符,每个字符要求使用1个字节的存储空间,而nchar(10)最多也可以存储10个字符,但是每个Unicode字符要求使用2个字节的存储空间.
表3-3列出了SQLServer支持的字符数据类型.
表3-3字符数据类型数据类型字节数字符数作用char(n)1~8000最多8000个字符固定宽度的ASCII数据类型varchar(n)1~8000最多8000个字符固定宽度的ASCII数据类型varchar(max)最大2GB最多1073741824个字符可变宽度的ASCII数据类型text最大2GB最多1073741824个字符可变宽度的ASCII数据类型nchar(n)2~8000最多4000个字符固定宽度的Unicode数据类型nvarchar(n)2~8000最多4000个字符可变宽度的Unicode数据类型nvarchar(max)最大2GB最多536870912个字符可变宽度的Unicode数据类型ntext最大2GB最多536870912个字符可变宽度的Unicode数据类型varchar(max)和nvarchar(max)数据类型,同时结合了text/ntext数据类型和varchar/nvarch数据类型的功能,最多可以存储2GB数据,并且对操作或者使用它们的函数没有任何限制.
3.
1.
3日期时间类型日期时间类型,用于存储日期和时间数据,可具体分为datetime与smalldatetime两种类型.
datetime和smalldatetime数据类型在计算机内部是作为整数存储的.
datetime数据类型存储为一对4字节整数,它们一起表示自1753年1月1日午夜12点钟经过的毫秒数.
smalldatetime数据类型存储为一对2字节整数,它们一起表示自1900年1月1日午夜12点钟经过的分钟数.
表3-4列出了SQLServer2005支持的日期和时间数据类型.
表3-4日期和时间数据类型日期类型字节数取值范围作用datetime8从1753-1-1到9999-12-31,精度为3.
33毫秒存储大型日期时间值smalldatetime4从1900-1-1到2079-12-31,精度为1分钟存储较小范围日期时间值例如,有效的日期和时间数据包括"4/01/9812:15:00:00:00pm"和"1:28:29:15:01am8/17/98".
前一个数据类型是日期在前,时间在后;后一个数据类型是时间在前,日期在后.
在SQLserver2005中日期的格式可以自己设定.
3.
1.
4货币类型货币数据类型旨在存储精确到4个小数位的货币值.
表3-5列出了SQLServer支持的货币数据类型.
表和数据完整性67第3章表3-5货币数据类型数据类型字节数取值范围作用money8–922337203685477.
5808~922337203685477.
5807存储大型货币值smallmoney4–214748.
3648~214748.
3647存储小型货币值3.
1.
5其他数据类型1.
二进制数据类型有很多时候需要存储二进制数据.
因此,SQLServer2005提供了3种二进制数据类型,允许在一个表中存储各种数量的二进制数据.
表3-6列出了SQLServer支持二进制数据类型.
表3-6二进制数据类型数据类型字节数作用binary(n)1~8000存储固定大小的二进制数据varbinary(n)1~8000存储可变大小的二进制数据varbinary(max)最多2GB存储可变大小的二进制数据image最多2GB存储可变大小的二进制数据二进制数据类型基本上用来存储SQLServer2005中的文件.
binary/varbinary数据类型用来存储小文件,如一组4KB或6KB文件的数据.
varbinary(max)数据类型可以存储与image数据类型相同大小的数据,并且可以使用它执行所有可以用binary/varbinary数据类型执行的操作和函数.
2.
特殊数据类型SQLServer2005还提供了7种特殊数据类型,包括cursor、sql_variant、timestamp、table、uniqueidentifier与XML.
timestamp用于表示SQLserver活动的先后顺序,以二进制投影的格式表示.
timestamp数据与插入数据或者日期、时间都没有关系.
bit由1或者0组成,当表示"真"或者"假"、on或者off时,使用bit数据类型.
uniqueidentifier由16字节的十六进制数字组成,表示一个全局唯一的.
当表的记录行要求唯一时,GUID是非常有用的.
例如,在客户标识号列使用这种数据类型可以区别不同的客户.
表3-7描述了这些特殊数据类型.
表3-7特殊数据类型数据类型作用bit存储0、1或null.
用于基本"标记"值.
TRUE被转换为1,而FALSE被转换为0timestamp一个自动生成的值.
一个表只能有一个timestamp列,并在插入或修改行时被设置到数据库时间戳uniqueidentifier一个16位GUID,用来全局标识数据库、实例和服务器中的一行sql_variant可以根据其中存储的数据改变数据类型.
最多存储8000字节cursor供声明游标的应用程序使用,包含一个可用于操作的游标的引用,不能在表中使用table用来存储随后进行的处理的结果集.
该数据类型不能用于列.
该数据类型的唯一使用时机是在触发器、存储过程和函数中声明表变量时XML存储一个XML文档,最大容量为2GBSQLServer2005数据库应用与开发(第二版)68需要说明的是,sql_variant数据类型是SQLServer2005新增的数据类型,是一种危险的数据类型,该数据类型允许在声明一个列或变量时,不必决定用它存储哪种类型的数据.
3.
用户自定义数据类型SQLServer2005允许用户根据自己的需要自定义数据类型(UDT),并可以用此数据类型来声明变量或列.
自定义类型提供了一种可以将更能清楚地说明对象中值的类型的名称应用于数据类型的机制,这使程序员或数据库管理员能够更容易地理解用该数据类型定义的对象的用途.
用户定义的数据类型基于在SQLserver2005中提供的数据类型.
在SQLServer2005的实践过程中,基本数据类型已经能够满足需要了,除非特别需要,不必使用用户自定义数据类型.
3.
2表的创建与维护3.
2.
1表的类型在SQLServer2005的系统中,可以按照不同的标准对表进行分类.
1.
按照表的用途分类(1)系统表:用于维护SQLServer2005服务器和数据库正常工作的只读数据表.
系统表存在于各个数据库中,由DBMS系统自动维护.
(2)用户表:由用户自己创建的、用于各种数据库应用系统开发的表.
(3)已分区表:已分区表是将数据水平划分为多个单元的表,这些单元可以分布到数据库中的多个文件组中.
在维护整个集合的完整性时,使用分区可以快速而有效地访问或管理数据子集,从而使大型表或索引更易于管理.
2.
按照表的存储时间分类(1)永久表:包括SQLServer2005的系统表和用户数据库中创建的数据表,该类表除非人工删除,否则一直存储在介质中.
(2)临时表:临时表是临时使用的表结构.
临时表分为全局的临时表和局部临时表,并且可以由任何用户创建.
所有的临时表都是在tempdb数据库中创建的.
局部临时表只有创建该表的用户在用来创建该表的连接中可见.
局部临时表关联的连接被关闭时,局部临时表将自动删除.
全局临时表在创建后,对于任何连接都是可见的,当引用该表的用户都与SQLServer实例断开时,该全局临时表自动删除.
如果服务器关闭,则所有临时表会被清空、关闭.
通过使用CREATETABLE命令并在表名前添加一个字符(#),可以创建局部临时表.
3.
2.
2表的创建创建数据表有两种方法:一种是在SQLServerManagementStudio中创建数据表,另一种是利用Transact-SQL语句创建数据表.
下面以前面创建的教务管理数据库teaching中的表为例,介绍表的创建方法和步骤.
表和数据完整性69第3章1.
在SQLServerManagementStudio中创建数据表首先以创建如表3-8所示的学生信息表student表结构为例,说明如何利用SQLServerManagementStudio为数据库teaching创建SQLServer2005的表,具体有以下几个步骤.
表3-8student表结构列序号列名类型取值说明列含义1studentnonchar(10)主键学生学号2snamenchar(8)否学生姓名3sexnchar(1)否性别4birthdaydatetime否出生日期5classnonchar(6)否班级编号6pointsmallint否入学成绩7phonenchar(12)否电话8E-mailnchar(20)否电子信箱(1)启动SQLServerManagementStudio,在"对象资源管理器"中,展开要新建表的数据库teaching.
(2)右击"表"项,在弹出的快捷菜单中选择"新建表"命令,如图3-1所示.
图3-1选择"新建表"命令(3)在弹出的如图3-2所示的"表设计器"窗口中,依次输入"列名"、"数据类型"及"允许空"等选项.
①列名:输入学生学号名studentno.
②数据类型与列长度:在下拉框中选择nchar(10),如果默认列长度不合适,还可以修改列长度.
③允许空:不选择,表示将来表中的studentno列值不允许空值出现.
(4)依次类推,设置其他列的名称、数据类型、列长度和允许空等选项,并单击"保存"按钮,如图3-3所示.
SQLServer2005数据库应用与开发(第二版)70图3-2创建表图3-3设置完成的表结构(5)右击studentno列,在弹出的快捷菜单中选择"设置主键"命令,或者单击"设置主键"按钮来设置主键,如图3-4所示,设置主键为studentno.
(6)设置完毕后,单击"保存"按钮.
在弹出的对话框中输入表名student后,单击"确定"按钮,即完成了创建表的操作.
表和数据完整性71第3章图3-4设置主键2.
利用Transact-SQL语句创建数据表使用Transact-SQL语句CREATETABLE命令也可以创建数据表.
CREATETABLE的语法格式如下:CREATETABLE[database_name.
[schema_name].
|schema_name.
]table_name({|}[n])[ON{partition_schema_name(partition_column_name)|filegroup|"default"}][{TEXTIMAGE_ON{filegroup|"default"}}][;]下面说明上述格式中的参数.
(1)database_name:为在其中创建表的数据库的名称.
database_name必须指定现有数据库的名称.
如果未指定,则database_name默认为当前数据库.
(2)schema_name:新表所属架构的名称.
(3)table_name:新表的名称.
表名必须遵循标识符规则.
(4):主要用于设置数据表列的属性.
(5):用于定义计算列.
(6):用于设置数据表约束,指同时针对多个列设置约束.
(7)ON{|filegroup|"default"}:指定存储表的分区架构或文件组.
(8)TEXTIMAGE_ON{filegroup|"default"}:用于指示text、ntext、image、XML、varchar(max)、nvarchar(max)或varbinary(max)列存储在指定文件组的关键字.
下面通过几个例题进一步解释利用CREATETABLE命令创建表的相关选项的含义.
【例3-1】利用CREATETABLE命令建立课程信息表course,表结构如表3-9所示.
SQLServer2005数据库应用与开发(第二版)72表3-9course表结构列序号列名类型取值说明列含义1coursenonchar(6)主键课程编号2cnamenchar(20)否课程名称3typenchar(8)否类别4periodtinyint否学时5creditnumeric(4,1)否学分利用CREATETABLE语句在数据库teaching建立课程信息表course的程序代码如下:CREATETABLEteaching.
dbo.
course(coursenonchar(6)NOTNULL,cnamenchar(20)NULL,typenchar(8)NULL,periodtinyintNULL,creditnumeric(4,1)NULL,CONSTRAINTPK_coursePRIMARYKEYCLUSTERED(CoursenoASC))ON[PRIMARY]上述程序中,在创建course时,还为表设置了主键,由此可以看出利用命令方式创建表与可视化方式是一致的.
其中:①PK_course表示创建主键时的索引名称,可以是任意标识符;②CLUSTERED项表示聚集索引类型;③ASC表示按courseno值升序方式排列数据,若是DESC则表示降序.
【例3-2】利用CREATETABLE命令建立学生分数表score,表结构如表3-10所示.
该表中主键由两个列构成.
表3-10score表结构列序号列名类型取值说明列含义1studentnonchar(10)学号2coursenonchar(6)主键课程编号3usuallynumeric(6,2)否平时成绩4finalnumeric(6,2)否期末成绩利用CREATETABLE语句在数据库teaching建立学生分数表score的程序代码如下:CREATETABLEdbo.
score(studentnonchar(10)NOTNULL,coursenonchar(6)NOTNULL,usuallynumeric(6,2)NULL,finalnumeric(6,2)NULL,CONSTRAINTPK_scorePRIMARYKEYCLUSTERED(studentnoASC,coursenoASC))表和数据完整性73第3章其中:①没有指定表所在的文件组,系统会将表创建到默认文件组;②主键由两个列构成,数据将按照"studentno+courseno"的方式排列,即先排序studentno项数据,studentno项数据相同的再按值排序,其形式如图3-5所示.
图3-5数据排序示例【例3-3】利用CREATETABLE命令建立教师信息表teacher,表结构如表3-11所示.
表3-11teacher表结构列序号列名类型取值说明列含义1teachernonchar(6)主键教师编号2tnamenchar(8)否教师姓名3majornchar(10)否专业4profnchar(10)是职称5departmentnchar(12)否院系部门利用CREATETABLE语句在数据库teaching建立教师信息表teacher的程序代码如下:CREATETABLEdbo.
teacher(teachernonchar(6)NOTNULL,tnamenchar(8)NULL,majornchar(10)NULL,SQLServer2005数据库应用与开发(第二版)74profnchar(10)NOTNULL,departmentnchar(12)NULL,CONSTRAINTPK_teacherPRIMARYKEYCLUSTERED(teachernoASC))其中:①NULL(空值)表示数值未知,并不是数字"0"或字符"空格",比较两个空值或空值与其他任何类型值的结果为空值;②NOTNULL(不允许空值)表示数据列中不允许空值出现,这样可以确保数据列中必须包含有意义的值,对于数据列中设置"不允许空值",在向表中输入数据时,就必须输入一个值,否则该行数据将不会被收入表中.
3.
创建数据表的脚本代码利用CREATETABLE命令创建表和利用可视化方式创建表,实现的功能基本是一样的.
只要表结构创建完成,就可以查看表的脚本代码.
【例3-4】创建表结构如表3-12所示的班级信息表class.
然后查看该表的有关CREA-TETABLE命令脚本信息.
表3-12class表结构列序号列名类型取值说明列含义1classnonchar(6)主键班级编号2classnamenchar(12)否班级名称3departmentnchar(12)否院系部门4monitornchar(8)否联系人按照前面介绍的可视化方式创建表class,如图3-6所示,然后在SQLServerManagementStudio中展开"资源管理器"|"数据库"|teaching|class子目录,在class上右击,在弹出的快捷菜单中,选择"编写表脚本为"|"CREATE到"|"新查询编辑器窗口"命令,即可在打开的查询编辑器窗口中显示代码如下:SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE[dbo].
[class]([classno][nchar](6)COLLATEChinese_PRC_CI_ASNOTNULL,[classname][nchar](12)COLLATEChinese_PRC_CI_ASNULL,[department][nchar](12)COLLATEChinese_PRC_CI_ASNULL,[monitor][nchar](8)COLLATEChinese_PRC_CI_ASNULL,CONSTRAINT[PK_class]PRIMARYKEYCLUSTERED([classno]ASC)WITH(IGNORE_DUP_KEY=OFF)ON[PRIMARY])ON[PRIMARY]表和数据完整性75第3章图3-6创建表class【例3-5】为了完善teaching数据库的表间联系,创建表结构如表3-13所示的纽带表teach_class.
然后查看该表的有关CREATETABLE命令脚本信息.
表3-13teach_class表结构列序号列名类型取值说明列含义1teachernonchar(6)教师编号2classnonchar(6)班级编号3coursenonchar(6)主键课程编号程序代码如下:SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE[dbo].
[teach_class]([teacherno][nchar](6)COLLATEChinese_PRC_CI_ASNOTNULL,[classno][nchar](6)COLLATEChinese_PRC_CI_ASNOTNULL,[courseno][nchar](6)COLLATEChinese_PRC_CI_ASNOTNULL,CONSTRAINT[PK_teach_class]PRIMARYKEYCLUSTERED([teacherno]ASC,[classno]ASC,[courseno]ASC)WITH(IGNORE_DUP_KEY=OFF)ON[PRIMARY])ON[PRIMARY]SQLServer2005数据库应用与开发(第二版)764.
为数据表输入数据为数据表输入数据的方式有多种,常见的有通过命令方式添加行数据,也可以通过程序实现表数据的添加.
这里以student表为例介绍直接在可视化方式下录入表数据的步骤,其他表的数据录入可以参考一下.
下面介绍为student表输入数据的步骤.
(1)启动SQLServerManagementStudio窗口,展开"资源管理器"|"数据库"|teaching子目录,右击student表,在弹出的快捷菜单中,选择"打开表"命令.
(2)进入如图3-7所示的数据输入界面,依次按照表结构的要求为每一列输入数据.
每输入完一行,系统会自动进入下一行的输入状态.
在输入过程中,要针对不同的数据类型输入合法的数据.
如果输入不合规则的数据,系统将不接受,需要重新输入该行数据.
图3-7输入数据例如,日期时间型数据必须是现实中使用的数据,而不能输入像2009-02-30这样的数据.
数值型数据不能输入字母等.
(3)对student表输入数据完毕,则界面如图3-8所示.
单击"保存"按钮,即可完成数据的输入过程.
图3-8输入数据结束(4)如果需要添加数据,重复上述过程即可.
其他表的数据输入可以依此类推.
表和数据完整性77第3章3.
2.
3数据浏览如果需要查看数据库中表的数据可以通过查询窗口和命令等多种方式实现.
1.
在查询窗口中浏览表数据现以teacher表为例介绍在查询窗口中浏览表数据的步骤.
(1)启动SQLServerManagementStudio窗口,展开"资源管理器"|"数据库"|teaching子目录,右击teacher表,在弹出的快捷菜单中,选择"打开表"命令.
(2)然后进入如图3-9所示的浏览数据窗口,可以发现该窗口与数据输入的窗口是一样的.
图3-9浏览数据表teacher2.
利用Transact-SQL语句在查询窗口中浏览表数据(1)启动SQLServerManagementStudio窗口,单击"新建查询"按钮.
(2)在"查询设计器"窗口中输入一条查询语句,代码如下:useteachinggoselect*fromcourse(3)单击"执行"按钮,可以发现course表的相关数据显示出来,如图3-10所示.
图3-10浏览数据表courseSQLServer2005数据库应用与开发(第二版)783.
2.
4表结构的修改在数据表创建完成后,有时需要对表中的结构进行修改.
在SQLServerManagementStudio中可以修改表结构,利用Transact-SQL语句也可以修改表结构.
1.
在SQLServerManagementStudio中修改表结构(1)启动SQLServerManagementStudio后,在对象资源管理器中展开其中的树形目录,找到要修改结构的数据表.
(2)若要修改数据表名,则右击数据表,在弹出的快捷菜单中选择"重命名"命令.
(3)若要对表中的列进行插入、删除等操作,同样右击数据表,在弹出的快捷菜单中选择"修改"命令,此时会出现"表设计器"窗口.
若想在某一列前插入另一列,则右击此列,在弹出的快捷菜单中选择"插入列"命令,如图3-11所示,并输入要插入的列名与类型.
若要删除某个列,只需在弹出的快捷菜单中选择"删除列"命令即可.
图3-11修改表结构(4)若要修改列数据类型,在"表设计器"窗口中,直接单击要修改的在"数据类型"项处修改.
同样,可修改数据表的索引、约束.
(5)若要修改数据表属性,在"表设计器"窗口中,单击"属性"按钮,在弹出的对话框中进行修改,如图3-12所示.
图3-12修改表属性表和数据完整性79第3章2.
利用Transact-SQL语句修改表结构利用Transact-SQL语句ALTERTABLE的命令,可以更改、添加或删除列和约束,从而修改表的定义.
ALTERTABLE的语法格式如下:ALTERTABLE[database_name.
[schema_name].
|schema_name.
]table_name{ALTERCOLUMNcolumn_name--要修改的列名{[type_schema_name.
]type_name[({precision[,scale]|max|xml_schema_collection})]--修改后的数据类型[COLLATEcollation_name]--设置排序规则[NULL|NOTNULL]|{ADD|DROP}{ROWGUIDCOL|PERSISTED}--添加或删除ROWGUIDCOL|[WITH{CHECK|NOCHECK}]ADD{||}[,.
.
.
n]|DROP--删除{[CONSTRAINT]constraint_name--删除约束[WITH(--设置聚集约束选项[,.
.
.
n])]|COLUMNcolumn_name--删除列}[,.
.
.
n]}[;]下面说明上述格式中的主要参数.
(1)database_name:要在其中创建表的数据库的名称.
(2)schema_name:表所属架构的名称.
(3)table_name:要更改的表的名称.
(4)ALTERCOLUMN:指定要更改命名列.
(5)column_name:要更改、添加或删除的列的名称.
(6)[type_schema_name.
]type_name:更改后的列的新数据类型或添加的列的数据类型.
(7)precision:指定的数据类型的精度.
(8)scale:指定数据类型的小数位数.
(9)max:仅应用于varchar、nvarchar和varbinary数据类型,以便存储231–1个字节的字符、二进制数据以及Unicode数据.
SQLServer2005数据库应用与开发(第二版)80(10)xml_schema_collection:仅应用于XML数据类型,以便将XML架构与类型相关联.
(11)COLLATE:指定更改后的列的新排序规则.
(12)NULL|NOTNULL:指定列是否可接受空值.
(13)|{ADD|DROP}{ROWGUIDCOL|PERSISTED}:指定在指定列中添加或删除ROWGUIDCOL属性.
(14)WITHCHECK|WITHNOCHECK:指定表中的数据是否用新添加的或重新启用的FOREIGNKEY或CHECK约束进行验证.
(15)ADD:指定添加一个或多个列定义、计算列定义或者表约束.
(16)DROP{[CONSTRAINT]constraint_name|COLUMNcolumn_name}:指定从表中删除constraint_name或column_name.
可以列出多个列或约束.
(17)WITH:指定设置一个或多个删除聚集约束选项.
列是修改表结构的主要对象,数据表的每一列都有一组属性,如名称、长度、数据类型、精度、小数位数等.
列的所有属性构成列的定义.
【例3-6】在test01数据库中创建一个新表student1,然后修改其列属性.
程序代码如下:CREATETABLEstudent1(column_gradeint)--创建新表GOEXECsp_helpstudent1--查看表的信息GOALTERTABLEstudent1ADDcolumn_classVARCHAR(20)NULL--添加列GOEXECsp_helpstudent1GOALTERTABLEstudent1DROPCOLUMNcolumn_grade--删除列GOEXECsp_helpstudent1GO该程序中先创建了表student1,其中含有列column_grade(年级),然后添加一个允许空值且没有通过DEFAULT定义的列column_class(班级),新列中各行的值均为NULL,最后又删除了列column_classs,在每个修改后,都要用sp_help语句查询表student.
【例3-7】修改test01表中student1列的column_class数据类型和名称.
程序代码如下:Usetest01表和数据完整性81第3章GOALTERTABLEstudent1ALTERCOLUMNcolumn_classchar(20)NOTNULL--修改数据类型GOEXECsp_rename'student1.
column_class','st_class'--修改列名GO其中:sp_rename函数可以更改当前数据库中用户创建对象的名称.
3.
2.
5表数据的修改表的数据输入后,可以直接展开"数据库"|"表"子目录,选择要修改数据的表,右击该表,在弹出的快捷菜单中选择"打开表"命令,然后在窗体中直接修改表的数据即可.
还可以通过3种Transact-SQL语句:INSERT、UPDATE和DELETE进行数据的添加、更新和删除操作,利用这3种语句可维护和修改表的数据.
下面介绍这3种语句的部分内容.
1.
利用INSERT语句输入数据INSERT语句的语法格式如下:INSERT[TOP(expression)[PERCENT]][INTO]{|rowset_function_limited[WITH([.
.
.
n])]}{[(column_list)][]{VALUES({DEFAULT|NULL|expression}[,.
.
.
n])|derived_table|execute_statement}}|DEFAULTVALUES[;]下面说明上述格式中的主要参数.
(1)TOP(expression)[PERCENT]:指定将插入的随机行的数目或百分比.
(2)INTO:一个可选的关键字,可以将它用在INSERT和目标表之间.
(3):通常是表或视图的名称.
(4)rowset_function_limited:行集函数限制.
通常为OPENQUERY或OPENROW-SET函数所使用.
(5)WITH():指定目标表所允许的一个或多个表提示.
(6)(column_list):要在其中插入数据的一列或多列的列表.
必须用括号将column_list括起来,并且用逗号进行分隔.
(7):将插入行作为插入操作的一部分返回.
SQLServer2005数据库应用与开发(第二版)82(8)VALUES:引入要插入的数据值的列表.
对于column_list或表中的每个列,都必须有一个数据值.
(9)DEFAULT:强制数据库引擎加载为列定义的默认值.
【例3-8】向teaching数据库的score表中添加数据.
程序代码如下:INSERTINTOscore(usually,courseno,final,studentno)VALUES(79,'c05109',91,'0937221508')INSERTINTOscoreVALUES('0824113307','c05127',93,78)程序中第1种方式,列出了表的列名,顺序与表结构不一致,添加值也按指定列对应的顺序添加.
第2种方式,没有列出表列名,值顺序与表结构一致,同样可以为表插入数据.
2.
利用UPDATE语句更新表数据UPDATE语句的基本语法格式如下:UPDATE[TOP(expression)[PERCENT]]{|rowset_function_limited[WITH([.
.
.
n])]}SET{column_name={expression|DEFAULT|NULL}}[,.
.
.
n][][FROM{}[,.
.
.
n]][WHERE{}][;]下面说明上述格式中的主要参数.
(1)TOP(expression)[PERCENT]:指定要更新的行数或行百分比.
(2)WITH():指定目标表允许的一个或多个表提示.
(3)SET:指定要更新的列或变量名称的列表.
(4)column_name:包含要更改的数据的列.
column_name必须已存在于table_orview_name中.
(5)expression:返回单个值的变量、文字值、表达式或嵌套SELECT语句(加括号).
expression返回的值替换column_name或@variable中的现有值.
(6)DEFAULT:指定用为列定义的默认值替换列中的现有值.
(7):在UPDATE操作中,返回更新后的数据或基于更新后的数据的表达式.
(8)FROM:指定将表、视图或派生表源用于为更新操作提供条件.
(9)WHERE:指定条件来限定所更新的行.
表和数据完整性83第3章(10):为要更新的行指定需满足的条件.
UPDATE可以更改表或视图中单行、行组或所有行的数据值.
引用某个表或视图的UPDATE语句每次只能更改一个基表中的数据.
【例3-9】更改teaching数据库的score表中的学号为0824113307、课程号为c05127的期末成绩修改为87.
程序代码如下:UPDATEscoreSETfinal=87WHEREstudentno='0824113307'ANDcourseno='c05127'其中UPDATE语句只修改了一行,因为列studentno和courseno的组合建立了表的主键.
【例3-10】为数据库test01中表student1输入3行数据,然后将列st_class的值全部改为jsj1012.
程序代码如下:INSERTINTOstudent1VALUES('jixie0809')INSERTINTOstudent1VALUES('huag0902')INSERTINTOstudent1VALUES('txun0912')GOUPDATEstudent1SETst_class='jsj1012'GO程序中由于UPDATE语句中没有设定WHERE条件,运行时将表student1中的列st_class的值全部更新为jsj1012.
3.
利用DELETE语句删除表中数据DELETE语句的基本语法格式如下:DELETE[TOP(expression)[PERCENT]][FROM]{|rowset_function_limited[WITH([.
.
.
n])]}[][FROM[,.
.
.
n]][WHERE{}][;]下面说明上述格式中的主要参数.
(1)FROM:可选的关键字,可用在DELETE关键字与目标table_or_view_name或rowset_function_limited之间.
SQLServer2005数据库应用与开发(第二版)84(2)WITH():指定目标表所允许的一个或多个表提示.
需要有WITH关键字和括号.
(3):将已删除行或这些行表达式作为DELETE操作的一部分返回.
(4)FROM:指定附加的FROM子句.
(5)WHERE:指定用于限制删除行数的条件.
如果没有提供WHERE子句,则DELETE删除表中的所有行.
【例3-11】删除数据库test01中表student1的列st_class的值为jsj1012的行.
程序代码如下:DELETEFROMstudent1WHEREst_class='jsj1012'程序执行后,删除了列st_class的值为jsj1012的所有行.
4.
利用TruncateTable语句删除表中数据Transact-SQL语言也支持利用TruncateTable语句删除表中数据.
TruncateTable语句从一个表中删除所有行的速度要快于DELETE.
TruncateTable语句的格式为:TruncateTabletable_name若要删除表中的所有行,则TruncateTable语句是一种快速、无日志记录的方法.
TruncateTable语句只记录整个数据页的释放.
3.
2.
6删除表若不再需要使用某个数据表时,可考虑将其删除.
可以在SQLServerManagementStudio中删除数据表,也可以利用Transact-SQL语句删除数据表.
1.
在SQLServerManagementStudio中删除数据表在SQLServerManagementStudio中删除表的步骤有以下几步.
(1)启动SQLServerManagementStudio,连接到本地数据库实例.
(2)在对象资源管理器中,展开树形目录,选取要删除的数据表.
右击该表名,在弹出的快捷菜单中选择"删除"命令.
在弹出的"删除对象"对话框中,会出现要删除的表,可单击"确定"按钮.
例如,删除数据库test01中的表student1,如图3-13所示.
(3)如果出现"删除失败"的消息,那表示目前不能删除该数据表,原因可能是该数据表正在被使用,或与其他表存在约束关系.
此时可在"删除对象"对话框中,单击"显示依赖关系"按钮,在弹出的"依赖关系"对话框中,可看到该表的依赖关系.
若存在依赖关系,则数据表不能被删除,除非先删除依赖于该数据表的关系.
2.
利用Transact-SQL语句删除数据表利用Transact-SQL语句DROPTABLE就可删除数据表定义及表的所有数据、索引、触发器、约束和指定的权限,其语法格式如下:DROPTABLE[database_name.
[schemaname]表和数据完整性85第3章|schema_name.
]table_name[,.
.
.
n][;]图3-13删除表student1下面说明上述格式中的主要参数.
(1)database_name:是要在其中创建表的数据库的名称;(2)schema_name:是表所属架构的名称;(3)table_name:是要删除的表的名称.
需要注意的是,不能使用DROPTABLE删除被FOREIGNKEY约束引用的表.
必须先删除引用FOREIGNKEY约束或引用表.
如果要在同一个DROPTABLE语句中删除引用表以及包含主键的表,则必须先列出引用表.
可以在任何数据库中删除多个表.
如果一个要删除的表引用了另一个也要删除的表的主键,则必须先列出包含该外键的引用表,然后再列出包含要引用的主键的表.
下面通过例题对本节内容作进一步介绍.
【例3-12】在数据库test01中创建表stud,为表添加、删除列和行,再删除该表.
程序代码如下:USEtest01GO--创建表studCREATETABLEstud(Studentnonchar(10)NOTNULL,Snamenchar(8)NULL,Sexnchar(1)NULL,AgeintNULL,SQLServer2005数据库应用与开发(第二版)86Classnonchar(6)NULL)--向表stud中添加数据insertintostud(Studentno,Sname,Sex,Age,Classno)values('0922130018','李文平','女',19,'08计本01')insertintostudvalues('0922130028','王海平','男',19,'08计本02')insertintostudvalues('0922130038','邓文平','女',18,'09计本01')--向表中添加和删除列altertablestudadddepartmentchar(20)altertablestudaddunitchar(20)altertablestuddropcolumnunit--更新表中的数据updatestudsetSname='华银峰'whereSname='王海'updatestudsetdepartment='计算机学院'--删除表中的所有数据TruncateTablestud--删除表Droptablestud【例3-13】局部临时表的创建与数据输入.
程序代码如下:usetempdbgoCREATETABLE#TempTable(studentIDint,FullNamenchar(10),telephonenchar(12))INSERTINTO#TempTableVALUES(1,'张何仁','13113689545')INSERTINTO#TempTableVALUES(2,'卢彬笋','13962562901')INSERTINTO#TempTableVALUES(3,'和冰','15782695447')--利用select语句浏览临时表select*from#temptable程序运行结果如下:studentIDFullNametelephone1张何仁131136895452卢彬笋139625629013和冰15782695447(3行受影响)--查看局部临时表的有关信息表和数据完整性87第3章usetempdbGOEXECsp_help#temptable其中:(1)EXEC是用于执行存储过程的命令,在此可以省略;(2)sp_help是最常用的查看数据库对象信息的存储过程.
3.
3数据的完整性管理数据库及其对象是SQLServer2005的主要任务之一.
在使用数据库的过程中,数据的正确与完整直接影响数据库使用质量.
例如,在student表中,性别列sex的值应该是"男"或者"女",学号列studentno的值长度应该为10位,如果在实际的录入数据的过程中,没有一些约束与检测机制,用户就可能录入不符合要求的数据,那么将导致数据不正确.
数据不正确,程序功能无论怎样完善,也无法得到正确的结果.
在创建数据库时,利用数据完整性是解决这些问题的重要方法.
数据完整性是指数据的精确性和可靠性,是为防止数据库中存在不符合语义规定的数据,防止因错误信息的输入、输出而造成无效的操作或错误信息而提出的,数据完整性在数据库管理系统中十分重要.
3.
3.
1数据完整性的类型数据完整性对于数据来说有两个方面的含义,即正确和相容.
根据数据完整性所作用的数据库对象和范围不同,可以将其分为以下几类.
(1)域(Domain)完整性.
域就是指表中的列,域完整性要求列的数值具有正确的类型、格式和有效值范围,并确定是否允许有空值.
通常使用有效性检查强制域完整性,也可以通过限定列中允许的数据类型、格式或有效值范围来强制数据完整性.
域完整性的常见实现机制有默认值(Default)、检查(Check)、外键(ForeignKey)、数据类型(DataType)和规则(Rule).
(2)实体(Entity)完整性.
实体对应的是行,实体完整性要求表中的每一行具有唯一的标识.
现实中,如人的指纹、身份证号等,都是用于标识人与人之间的区别的,是唯一的标识.
而在数据库中,如student表中的列studentno被设为主键,则会保证每个学生只有一个学号,而且是唯一的.
实体完整性的实现机制有主键(PrimaryKey)、唯一码(UniqueKey)、唯一索引(UniqueIndex)和标识列(IdentityColumn).
(3)引用完整性.
引用完整性是指两个表的主键与外键之间定义的数据完整性,将确保主键和外键的关系.
引用完整性可以保证两个引用表间的数据的一致性,如student表与score表之间依靠studentno列建立引用完整性,可以保证每个学生的信息与成绩的一致,而不会出现张冠李戴的错误.
还可以禁止从表中插入被引用表中不存在的关键字的行,如给一个本来就"没有此人"的学生输入成绩.
实现引用完整性的实现机制有外键(ForeignKey)、检查(Check)、触发器(Trigger)和存储过程(StoredProcedure).
SQLServer2005数据库应用与开发(第二版)88(4)用户定义完整性.
用户可以根据其应用环境的不同,对数据库设置一些特殊的约束条件,反映某一具体应用所涉及的数据必须满足的语句要求.
SQLServer2005提供了定义和检验这类完整性的机制,用户定义完整性使用户可以定义不属于其他任何完整性分类的特定业务规则.
用户定义完整性的实现机制有规则(Rule)、触发器(Trigger)和存储过程(StoredProcedure)及创建数据表时的所有约束(Constraint).
3.
3.
2约束约束(Constraint)是定义关于列中允许值的规则,是强制实施完整性的标准机制.
SQLServer2005通过5种约束可以定义自动强制实施数据库完整性的方式.
1.
SQLServer2005支持的约束类型(1)NOTNULL约束:列的为空性决定表中的行是否可为该列包含空值.
出现NULL通常表示值未知或未定义.
(2)PRIMARYKEY约束:标识具有唯一标识表中行的值的列或列集.
在一个表中,不能有两行具有相同的主键值.
不能为主键中的任何列输入NULL值.
每个表都应有一个主键.
如果为表指定了PRIMARYKEY约束,则SQLServer2005数据库引擎将通过为主键列创建唯一索引来强制数据的唯一性.
因此,所选的主键必须遵守创建唯一索引的规则.
(3)FOREIGNKEY约束:外键用于建立和加强两个表数据之间的连接的一列或多列.
通过定义FOREIGNKEY约束来创建外键可以标识并强制实施表间的关系.
在外键引用中,当一个表的列被引用作为另一个表的主键值的列时,就在两表之间创建了连接,这个列就成为第二个表的外键.
FOREIGNKEY约束还可以定义为引用另一表的UNIQUE约束.
FOREIGNKEY约束可以包含空值.
(4)UNIQUE约束:强制实施列集中值的唯一性.
表中的任何两行都不能有相同的列值.
另外,主键也强制实施唯一性,但主键不允许NULL作为一个唯一值,而UNIQUE约束可以输入NULL值.
(5)CHECK约束:通过限制可放入列中的值来强制实施域完整性.
CHECK约束指定逻辑表达式来检测输入的相关列值,若输入列值使得计算结果为FALSE,则该行被拒绝添加.
可以在一个表中为每列指定多个CHECK约束.
2.
在SQLServerManagementStudio中创建约束(1)创建NOTNULL约束.
在SQLServerManagementStudio中选择表,利用选择"修改"命令,在弹出的窗体中,对表中的列的"允许空"项进行选择即可.
(2)创建PRIMARYKEY约束.
在SQLServerManagementStudio中选择表,利用选择"修改"命令,在弹出的窗体中,右击表中被选择的列,在弹出的快捷菜单中选择"设置主键"命令即可.
(3)创建FOREIGNKEY约束.
下面以score表为例介绍创建FOREIGNKEY约束的步骤.
①在SQLServerManagementStudio中选择表score,右击该表,在弹出的快捷菜单中选择"修改"命令后弹出窗体,在窗体中单击"关系"按钮,如图3-14所示.
表和数据完整性89第3章图3-14创建外键约束②在弹出的"外键关系"对话框中,单击"添加"按钮,然后单击"表和列规范"后的按钮,如图3-15所示.
图3-15创建外键关系③在弹出的"表和列"对话框中,选择主键表student和外键表score及共有的列studentno.
如图3-16所示,单击"确定"按钮,外键约束创建完毕.
图3-16选择表和列④若展开表score的"键"项,可以查看外键约束到FK_score_student1.
SQLServer2005数据库应用与开发(第二版)90(4)创建UNIQUE约束.
下面介绍在表score中创建UNIQUE约束的步骤.
①在SQLServerManagementStudio中选择表score,右击该表,在弹出的快捷菜单中选择"修改"命令后弹出窗体,在窗体中单击"管理索引和键"按钮,如图3-17所示.
图3-17创建唯一约束②在弹出的"索引/键"对话框中,单击"添加"按钮,然后选择"是唯一的"后的列表框按钮,如图3-18所示.
选择"是"选项,单击"关闭"按钮即可.
图3-18"索引/键"对话框(5)创建CHECK约束.
在表student中创建CHECK约束的步骤有以下几步.
①在SQLServerManagementStudio中选择表student,右击该表,在弹出的快捷菜单中选择"修改"命令后弹出窗体,在窗体中单击"管理Check约束"按钮,如图3-19所示.
图3-19创建CHECK约束表和数据完整性91第3章②在弹出的"CHECK约束"对话框中,单击"添加"按钮,然后单击"表达式"后的按钮,如图3-20所示.
图3-20"CHECK约束"对话框③在弹出的"CHECK约束表达式"对话框中,输入表达式"sex='男'orsex='女'".
如图3-21所示.
单击"确定"按钮,CHECK约束创建完毕.
利用可视化方式还可以添加各种约束,参看创建的步骤即可实现.
约束创建完成,可以在资源管理器中,通过展开具体表的"键"或"约束"节点查看、修改和输出脚本等.
3.
利用Transact-SQL语句创建或修改约束创建约束可以使用CREATETABLE或ALTERTABLE语句完成.
使用CREATETABLE语句表示在创建表的时候定义约束,使用ALTERTABLE语句表示在已有的表中添加约束.
即使表中已经有了数据,也可以在表中增加约束.
定义约束时,既可以把约束放在一个列上,也可以把约束放在多个列上.
如果把约束放在一个列上,该约束称为列级约束,因为它只能由约束所在的列引用.
如果把约束放在多个列上,该约束称为表级约束,这时可以由多个列来引用该约束.
当定义约束或修改约束的定义时,应该考虑当在表上增加约束时,SQLServer2005系统将检查表中的数据是否与约束冲突.
当创建约束时,可以指定约束的名称.
否则,系统将提供一个复杂的、系统自动生成的名称.
对于一个数据库来说,约束名称必须是唯一的.
一般来说,约束的名称应该按照这种格式:约束类型简称_表名_列名_代号.
利用Transact-SQL语句创建或修改约束的语法格式如下:::=[CONSTRAINTconstraint_name]--定义约束[WITH{CHECK|NOCHECK}]--修改约束ADD图3-21设置CHECK约束表达式SQLServer2005数据库应用与开发(第二版)92{n]|DROP{[CONSTRAINT]constraint_name|COLUMNcolumn_name}[,.
.
.
n]|{CHECK|NOCHECK}CONSTRAINT{ALL|constraint_name[,.
.
.
n]}下面通过例题来介绍如何利用Transact-SQL语句创建或修改约束.
【例3-14】为数据库teaching中的班级表class的列classno创建PRIMARYKEY约束,并将其中的classname、department、monitor的"允许空"修改为NOTNULL.
程序代码如下:ALTERTABLEclassADDCONSTRAINTPK_classPRIMARYKEYCLUSTERED(classnoASC)GOALTERTABLEclassALTERCOLUMNclassnamenchar(12)NOTNULL--修改数据类型GOALTERTABLEclassALTERCOLUMNdepartmentnchar(12)NOTNULLGOALTERTABLEclassALTERCOLUMNmonitornchar(8)NOTNULLGO通过代码将各列的"允许空"修改为NOTNULL,并为列classno创建PRIMARYKEY约束.
而且SQLServer2005自动为PRIMARYKEY约束的列建立一个索引.
该索引可以为聚集索引,也可以为非聚集索引,默认情况下使用的是聚集索引.
【例3-15】为数据库teaching中的成绩表score的两个列usually和final添加CHECK约束,限定其值在0~100范围内.
程序代码如下:ALTERTABLEscoreADDCONSTRAINTCK_usuallyCHECK(usually>=0andusually=0andfinal=0ANDfinal=0",并绑定规则到列sellnum.
(4)利用Transact-SQL语句先删除表booksales中销售时间在2009年以前的记录.
再删除全部记录,然后删除该表.
(5)练习如何利用导出向导将表book转换成Excel表.