第5章表的操作与管理5.1数据类型在SQLServer2005中,每个列、局部变量、表达式和参数都有其各自的数据类型。指定对
象的数据类型相当于定义了该对象的四个特性：（1）对象所含的数据类型,如字符、整数或二进制数。（2）所存储值的长度或它的大小。
（3）数字精度（仅用于数字数据类型）。（4）小数位数（仅用于数字数据类型）。SQLServer提供系统数据类型集,定义了
可与SQLServer一起使用的所有数据类型；另外用户还可以使用Transact-SQL或.NET框架定义自己的数据类型,它是系
统提供的数据类型的别名。每个表可以定义至多250个字段,除文本和图像数据类型外,每个记录的最大长度限制为1962个字节。5.1
.1系统数据类型1、精确数字类型；2、近似数字类型；3、日期和时间类型；4、字符数据类型；5、Unicode字符数
据类型；6、二进制字符数据类型；7、其它数据类型。5.1.1系统数据类型1、精确数字类型(1)精确数字类型包括：
整数类型;Bit（位类型）;Decimal和Numeric（数值类型）;Money和SmallMoney（货币类型）。5
.1.1系统数据类型1、精确数字类型(2)整数类型：整数类型是最常用的数据类型之一,它主要用来存储数值,可以直接进行数据
运算,而不必使用函数转换。整数类型包括以下四类：（1）Bigint：Bigint数据类型可以存储从(-922337203685
4775808)到(9223372036854775807)范围之间的所有整型数据。每个Bigint数据类型值存储在8个字节中
。（2）Int（Integer）：Int（或integer）数据类型可以存储从（-2147483648）到（2147483647
）范围之间的所有正负整数。每个Int数据类型值存储在4个字节中。（3）Smallint：可以存储从（-32768）到32767范
围之间的所有正负整数。每个smallint类型的数据占用2个字节的存储空间。（4）Tinyint：可以存储从0~255范围之间的
所有正整数。每个tinyint类型的数据占用1个字节的存储空间。5.1.1系统数据类型1、精确数字类型(3)位数据类型
Bit称为位数据类型,其数据有两种取值：0和1,长度为1字节。在输入0以外的其他值时,系统均把它们当1看待。这种数据类型常作为逻
辑变量使用,用来表示真、假或是、否等二值选择。(4)Decimal数据类型和Numeric数据类型Decimal数据类型和N
umeric数据类型完全相同,它们可以提供小数所需要的实际存储空间,但也有一定的限制,可以用2~17个字节来存储-1038+1到1
038-1之间的固定精度和小数位的数字。也可以将其写为Decimal（p,s）的形式,p和s确定了精确的总位数和小数位。其中p表示
可供存储的值的总位数,默认设置为18；s表示小数点后的位数,默认设置为0。例如：decimal（10,5）,表示共有10位数,其中
整数5位,小数5位。5.1.1系统数据类型1、精确数字类型(5)货币数据类型货币数据类型包括Money和SmallM
oney两种：（1）Money：用于存储货币值,存储在money数据类型中的数值以一个正数部分和一个小数部分存储在两个4字节的整
型值中,存储范围为（-9223372136854775808）到（9223372136854775807）,精确到货币单位的千分之
十。（2）Smallmoney：与money数据类型类似,但范围比money数据类型小,其存储范围为-2147483468到21
47483467之间,精确到货币单位的千分之十。当为money或smallmoney的表输入数据时,必须在有效位置前面
加一个货币单位符号。5.1.1系统数据类型2、近似数字类型近似数字类型包括Real和Float两大类。（1）Real
：可以存储正的或者负的十进制数值,最大可以有7位精确位数。它的存储范围从-3.40E-38~3.40E+38。每个Real类型的
数据占用4个字节的存储空间。（2）Float：可以精确到第15位小数,其范围从-1.79E-308~1.79E+308。如果不指
定Float数据类型的长度,它占用8个字节的存储空间。Float数据类型也可以写为Float（n）的形式,n指定Float数据的
精度,n为1~15之间的整数值。当n取1~7时,实际上是定义了一个Real类型的数据,系统用4个字节存储它；当n取8~15时,系
统认为其是Float类型,用8个字节存储它。5.1.1系统数据类型3.日期和时间数据类型（1）Datetime：用
于存储日期和时间的结合体,它可以存储从公元1753年1月1日零时起~公元9999年12月31日23时59分59秒之间的所有日期和时
间,其精确度可达三百分之一秒,即3.33毫秒。Datetime数据类型所占用的存储空间为8个字节,其中前4个字节用于存储基于190
0年1月1日之前或者之后日期数,数值分正负,负数存储的数值代表在基数日期之前的日期,正数表示基数日期之后的日期,时间以子夜后的毫秒
存储在后面的4个字节中。当存储Datetime数据类型时,默认的格式是MMDDYYYYhh:mmA.M./P.M,当插入数
据或者在其他地方使用Datetime类型时,需要用单引号把它括起来。默认的时间日期是January1,190012:00A.
M。可以接受的输入格式如下：Jan41999、JAN41999、January41999、Jan19994、199
94Jan和1999Jan4。（2）Smalldatetime：与Datetime数据类型类似,但其日期时间范围较小,它
存储从1900年1月1日~2079年6月6日内的日期。SmallDatetime数据类型使用4个字节存储数据,SQLServer
2000用2个字节存储日期1900年1月1日以后的天数,时间以子夜后的分钟数形式存储在另外两个字节中,SmallDatetime
的精度为1分钟。5.1.1系统数据类型4.字符数据类型字符数据类型也是SQLServer中最常用的数据类型之一,它
可以用来存储各种字母、数字符号和特殊符号。在使用字符数据类型时,需要在其前后加上英文单引号或者双引号。（1）Char：其定义形式
为Char（n）,当用Char数据类型存储数据时,每个字符和符号占用一个字节的存储空间。n表示所有字符所占的存储空间,n的取值为1
~8000。若不指定n值,系统默认n的值为1。若输入数据的字符串长度小于n,则系统自动在其后添加空格来填满设定好的空间；若输入的数
据过长,将会截掉其超出部分。如果定义了一个Char数据类型,而且允许该列为空,则该字段被当作Varchar来处理。（2）Varc
har：其定义形式为Varchar（n）。用Char数据类型可以存储长达255个字符的可变长度字符串,和Char类型不同的是Var
char类型的存储空间是根据存储在表的每一列值的字符数变化的。例如定义Varchar（20）,则它对应的字段最多可以存储20个字符
,但是在每一列的长度达到20字节之前系统不会在其后添加空格来填满设定好的空间,因此使用Varchar类型可以节省空间。（3）Te
xt：用于存储文本数据,其容量理论上为1~231-1（2,147,483,647）个字节,但实际应用时要根据硬盘的存储空间而定。
5.1.1系统数据类型5.Unicode字符数据类型Unicode字符数据类型包括Nchar、Nvarchar、N
text三种：（1）Nchar：其定义形式为Nchar（n）。它与Char数据类型类似,不同的是Nchar数据类型n的取值为1~
4000。Nchar数据类型采用Unicode标准字符集,Unicode标准用两个字节为一个存储单位,其一个存储单位的容纳量就大大
增加了,可以将全世界的语言文字都囊括在内,在一个数据列中就可以同时出现中文、英文、法文等,而不会出现编码冲突。（2）Nvarch
ar：其定义形式Nvarchar（n）。它与Varchchar数据类型相似,Nvarchar数据类型也采用Unicode标准字符集
,n的取值范围为1~4000。（3）Ntext：与Text数据类型类似,存储在其中的数据通常是直接能输出到显示设备上的字符,显示
设备可以是显示器、窗口或者打印机。Ntext数据类型采用Unicode标准字符集,因此其理论上的容量为230-1（1,073,74
1,823）个字节。5.1.1系统数据类型6.二进制字符数据类型二进制数据类型包括Binary、Varbinary、
Image三种：（1）Binary：其定义形式为Binary（n）,数据的存储长度是固定的,即n+4个字节,当输入的二进制数据长
度小于n时,余下部分填充0。二进制数据类型的最大长度（即n的最大值）为8000,常用于存储图像等数据。（2）Varbinary：
其定义形式为Varbinary（n）,数据的存储长度是变化的,它为实际所输入数据的长度加上4字节。其他含义同Binary。（3）
Image：用于存储照片、目录图片或者图画,其理论容量为231-1（2,147,483,647）个字节。其存储数据的模式与Text
数据类型相同,通常存储在Image字段中的数据不能直接用Insert语句直接输入。5.1.1系统数据类型7.其它数据类
型（1）Sql_variant：用于存储除文本、图形数据和Timestamp类型数据外的其他任何合法的SQLServer数据。
此数据类型极大地方便了SQLServer的开发工作。（2）Table：用于存储对表或者视图处理后的结果集。这种新的数据类型使得
变量可以存储一个表,从而使函数或过程返回查询结果更加方便、快捷。（3）Timestamp：亦称时间戳数据类型,它提供数据库范围内
的惟一值,反应数据库中数据修改的相对顺序,相当于一个单调上升的计数器。当它所定义的列在更新或者插入数据行时,此列的值会被自动更新,
一个计数值将自动地添加到此Timestamp数据列中。如果建立一个名为“Timestamp”的列,则该列的类型将自动设为Times
tamp数据类型。（4）Uniqueidentifier：用于存储一个16字节长的二进制数据类型,它是SQLServer根据计
算机网络适配器地址和CPU时钟产生的全局惟一标识符代码（GloballyUniqueIdentifier,简写为GUID）。此
数字可以通过调用SQLServer的newid（）函数获得,在全球各地的计算机经由此函数产生的数字不会相同。（5）XML：
可以存储XML数据的数据类型。利用它可以将XML实例存储在字段中或者XML类型的变量中。注意存储在XML中的数据不能超过2GB。
（6）Cursor：这是变量或存储过程OUTPUT参数的一种数据类型,这些参数包含对游标的引用。使用Cursor数据类型创建
的变量可以为空。注意：对于CREATETABLE语句中的列,不能使用Cursor数据类型。5.1.2自定义数据类型
SQLServer允许用户自定义数据类型,用户自定义数据类型是建立在SQLServer系统数据类型基础上的,当用户定义一种数
据类型时,需要指定该类型的名称、建立在其上的系统数据类型以及是否允许为空等。SQLServer为用户提供了两种方法来创建自定义
数据类型：（1）使用SQLServer管理平台创建用户自定义数据类型；（2）利用系统存储过程创建用户自定义数据类型5.
1.2自定义数据类型（1）使用SQLServer管理平台创建用户自定义数据类型在SQLServer管理平台中,打开指定
的服务器和数据库项,如图5-1所示,选择并展开“程序→类型”项,接下来用右键单击“用户自定义数据类型”选项,从弹出的快捷菜单中选择
“新建”命令,出现用户定义的数据类型属性对话框,如图5-2所示。5.1.2自定义数据类型（2）利用系统存储过程创建用户自
定义数据类型系统存储过程sp_addtype为用户提供了用T_SQL语句创建自定义数据类型的途径,其语法形式如下：sp_
addtype[@typename=]type,[@phystype=]system_data_type[,[@n
ulltype=]‘null_type’][,[@owner=]‘owner_name’]5.1.2自定义数据类型
（2）利用系统存储过程创建用户自定义数据类型例5-1自定义一个地址（address）数据类型。程序清单如下：exe
csp_addtypeaddress,‘varchar（80）’,‘notnull’5.2表操作表是包含数据库中
所有数据的数据库对象。表定义为列的集合,数据在表中是按行和列的格式组织排列的,每行代表惟一的一条记录,而每列代表记录中的一个域。
5.2.1创建表5.2.2创建约束5.2.3修改表5.2.4查看表5.2.5删除表5.2.
1创建表利用SQLServer管理平台创建表在SQLServer管理平台中,展开指定的服务器和数据库,打开想要创建新表
的数据库,右击表对象,并从弹出的快捷菜单中选择“新建表”选项,如图5-4所示。在图5-4的对话框中,可以对表的结构进行更改,设置主
键及字段属性,使用SQLServer管理平台可以非常直观地修改数据库结构和添加数据。在表中任意行上右击,则弹出一个快捷菜单,如图
5-6所示。5.2.1创建表2.利用create命令创建表使用create命令创建表非常灵活,它允许对表设置几种不
同的选项,包括表名、存放位置和列的属性等。其完整语法形式如下：CREATETABLE[dat
abase_name.[owner].|owner.]table_name（{|co
lumn_nameAScomputed_column_expression|nt>}[,…n]）[ON{filegroup|DEFAULT}][TEXTIMAGE_ON{
filegroup|DEFAULT}]::={column_namedata
_type}?[COLLATE][[DEFAULTconstant_
expression]|[IDENTITY[（seed,increment）[NOTFORREPLIC
ATION]]]]?[ROWGUIDCOL][][...n]
5.2.1创建表2.利用create命令创建表其中,各参数的说明如下：database_name：用于指定所创建表的
数据库名称。owner：用于指定新建表的所有者的用户名。table_name：用于指定新建表的名称。column_name：
用于指定新建表的列名。computed_column_expression：用于指定计算列的列值表达式。ON{filegro
up|DEFAULT}：用于指定存储表的文件组名。TEXTIMAGE_ON：用于指定text、ntext和image
列的数据存储的文件组。data_type：用于指定列的数据类型。DEFAULT：用于指定列的默认值。constant_exp
ression：用于指定列的默认值的常量表达式、可以为一个常量或NULL或系统函数。IDENTITY：用于将列指定为标识列。Se
ed：用于指定标识列的初始值。Increment：用于指定标识列的增量值。NOTFORREPLICATION：用于指定列的I
DENTITY属性,在把从其他表中复制的数据插入到表中时不发生作用,即不生成列值,使得复制的数据行保持原来的列值。ROWGUID
COL：用于将列指定为全局惟一标识行号列（rowglobaluniqueidentifiercolumn）。COLLAT
E：用于指定表的校验方式。column_constraint和table_constraint：用于指定列约束和表约束。5.
2.1创建表2.利用create命令创建表例5-3创建了一个工人信息表,它包括工人编号、姓名、性别、出生日期、职位、
工资和备注信息。SQL语句的程序清单如下：CREATETABLEworker（numberchar(8)
notnull,namechar（8）NOTNULL,sexchar（2）NULL,b
irthdaydatetimenull,job_titlevarchar（10）null,sala
rymoneynull,memontextnull）5.2.3修改表当数据库中的表创建完成后,
可以根据需要改变表中原先定义的许多选项,以更改表的结构。用户可以增加、删除和修改列,增加、删除和修改约束,更改表名以及改变表的所有
者等。1、修改列属性修改列属性包括以下一些内容：（1）修改列的数据类型；（2）修改列的数据长度；（3）修改列的精
度；（4）修改列的小数位数；（5）修改列的为空性。5.2.3修改表2、添加和删除列?在SQLSe
rver2005中,如果列允许空值或对列创建DEFAULT约束,则可以将列添加到现有表中。将新列添加到表时,SQLServ
er2005数据库引擎在该列为表中的每个现有数据行插入一个值。因此,在向表中添加列时向列添加DEFAULT定义会很有用。如果新列
没有DEFAULT定义,则必须指定该列允许空值。数据库引擎将空值插入该列,如果新列不允许空值,则返回错误。反之,可以删
除现有表中的列,但具有下列特征的列不能被删除：（1）用于索引；（2）用于CHECK、FOREIGNKEY、UNIQUE或
PRIMARYKEY约束；（3）与DEFAULT定义关联或绑定到某一默认对象；（4）绑定到规则；（5）已注册支持全文；
（6）用作表的全文键。5.2.3修改表3、增加、修改和删除约束?（1）增加、修改和删除PRIMARYK
EY约束。（2）增加、修改和删除UNIQUE约束。（3）增加、修改和删除CHECK约束。（4）增加
、修改和删除DEFAULT约束。（5）增加、修改和删除FOREIGNKEY约束。（6）增加和修改标识符列。只
能为每个表创建一个标识符列和一个GUID列。5.2.3修改表例5-14创建一个雇员信息表,然后在表中
增加一个salary字段,删除表中的age字段,并且修改memo字段的数据类型。SQL语句的程序清单如下：createtab
leemployees（idchar（8）primarykey,namechar（20）notnull,
departmentchar（20）null,memochar（30）null,ageintnull
,）Use数据库名altertableemployeesaddsalaryintnull,
dropcolumnage,altercolumnmemovarchar（200）null5.2.4
查看表当在数据库中创建了表后,有时需要查看表的有关信息。比如表的属性、定义、数据、字段属性和索引等。尤其重要的是查看表内
存放的数据,另外有时需要查看表与其他数据库对象之间的依赖关系。1.查看表的定义,如图5-15,5-16所示。5.2.4查
看表2.查看表中存储的数据,如图5-17,5-18所示。5.2.4查看表3.查看表与其他数据库对象的依赖关
系,如图5-19所示。5.2.4查看表4.利用系统存储过程查看表的信息系统存储过程Sp_help可以提供指定数据库对
象的信息,也可以提供系统或者用户定义的数据类型的信息,其语法形式如下：sp_help[[@objname=]name]
例5-17（1）显示当前数据库中所有对象的信息；（2）显示表Person.Contact的信息。在SQLServer管理平台
的查询窗口中,它们对应的语句和运行结果如图5-20和图5-21所示5.2.5删除表1.利用管理平台删除表在SQLS
erver管理平台中,展开指定的数据库和表,右击要删除的表,从弹出的快捷菜单中选择“删除”选项,则出现除去对象对话框,如图5-25
所示。5.2.5删除表2.利用DROPTABLE语句删除表DROPTABLE语句可以删除一个表和表中的数据及其与
表有关的所有索引、触发器、约束、许可对象。DROPTABLE语句的语法形式如下：DROPTABLEtable_nam
e要删除的表如果不在当前数据库中,则应在table_name中指明其所属的数据库和用户名。在删除一个表之前要先删除与此表相关联的
表中的外部关键字约束。当删除表后,绑定的规则或者默认值会自动松绑。例5-18删除company数据库中的表employee。
程序如下：droptablecompany.dbo.employee插入数据Insertintostudents(学号,姓名,性别,籍贯,家庭住址,联系电话,出生日期,入学日期,所在系,专业名称,班级)Vlaues（002,’王五’,’男’,’天津’,’天津南开’,022123456,1984/01/01,2006/09/01,’计算机’,’信息管理’,’信息管理2班’）注意：插入字符串总含有数字字符以外的字符时，需要用引号将其括起来。。。插入数据insertcselectbirthday,telephone,name2fromb：两个表之间相互传输数据，表名可以相同也可以不同；插入的数据表中的值必须存在，即必须有数据；表和表之间列的数量和顺序必须相同，列的数据类型也要相同。插入数据插入数据插入数据修改数据updatestudentsSet班级=‘信息管理1班’Where学号=002删除数据deletestudentsWhere学号=002图5-1打开用户定义的数据类型窗口图5-2用户定义的数据类型属性对话框图5-4新建表对话框图5-6设置字段属性对话框图5-15选择表格属性对话框图5-16表格属性对话框图5-17选择打开表对话框图5-18显示表格数据对话框图5-19显示相关性对话框图5-20所有数据库对象显示窗口图5-21当前数据库对象显示窗口图5-25删除表对话框 |
|
