SQL Server实用基础教程第4章 表的创建和管理Word格式文档下载.docx
- 文档编号:20430464
- 上传时间:2023-01-22
- 格式:DOCX
- 页数:34
- 大小:637.25KB
SQL Server实用基础教程第4章 表的创建和管理Word格式文档下载.docx
《SQL Server实用基础教程第4章 表的创建和管理Word格式文档下载.docx》由会员分享,可在线阅读,更多相关《SQL Server实用基础教程第4章 表的创建和管理Word格式文档下载.docx(34页珍藏版)》请在冰豆网上搜索。
83846568
002
王生阁
客户部经理
朝阳区潘家园
62687553
003
张盈香
销售部经理
西城区白石桥
68847865
在SQLServer中,表的每一列都有一个与之相关的数据类型,并且每一种数据类型都有一定的特性。
例如,在表4-1中,“姓名”、“地址”等列的数据类型为字符型数据,并且用户可以将它们的数据长度进行定义,如定义“姓名”列的数据长度不超过20个字符,定义“地址”列的数据长度不超过50个字符等。
SQLServer中的表一共有两类,即永久表和临时表。
永久表都保存在数据库文件中,而临时表虽然与永久表很相似,但它们却是存储在tempdb数据库中的,而且当不再使用这些临时表时,它们会被自动删除。
在SQLServer中可以创建两种类型的临时表:
局部临时表和全局临时表。
局部临时表的名称以单个数字符号(#)开头,它们仅对当前的用户连接是可见的,当该用户与SQLServer2000实例断开连接时即被自动删除。
全局临时表的名称以数字符号(##)开头,它们在创建后即对任何用户都是可见的,只有在所有引用该表的用户从SQLServer2000实例断开连接时才被自动删除。
用户可以应用临时表来存储那些在永久存储前仍然需要进行处理的数据。
例如,用户可以把多个表的数据合并起来创建一个临时表,在当前的用户连接中访问这个临时表,这样就可以随时访问这些合并起来的数据,而不用再去引用各个数据库中的表。
4.2表的设计
要在一个数据库中创建表,必须先对要创建的表进行设计。
在为一个数据库设计表之前,应该考虑这样一个问题:
数据库中要存放哪些数据,这些数据如何划分到表中。
具体对于某一个表,在创建之前的设计中应注意以下几个方面的问题:
◆表的各列及每一列的数据类型(如果必要,还应注意列宽,也就是数据长度)。
◆哪些列允许空值。
◆是否要使用以及何时使用约束、默认设置或规则。
◆所需索引的类型,哪里需要索引,哪些列是主键,哪些是外键。
在表的设计中,要做的第一件事情就是确定这个表中都需要哪些列,因为这个问题的确定就等于确定了该表要存储的数据。
例如,要在上一节创建的Sales数据库中创建一个客户表Customers,则需要为该表设计包括客户姓名、电话、地址等信息的列。
此外,为了便于存储和检索,还需要为其设计一个客户编号列,用于区别重名的客户。
而如果要为Sales数据库创建一个存货表Goods和订单表Orders,则需要分别为它们设计一些不同的列,如为Goods表设计货品名、库存量、进货地和价格等列,为Orders表设计订单序号、下订单的客户、所订货品、订货数量和金额等列。
除了以上所述之外,设计表之前还要考虑为各个用户设计访问数据库或表的权限等,权限管理这部分内容将在以后的章节中进行具体介绍。
4.2.1数据类型
设计表时首先要为每列指定数据类型。
数据类型定义了各列所允许的数据值,可以用来强制实现数据的完整性。
若要为列指定数据类型,可以使用SQLServer系统提供的的基本数据类型,或使用基于这些系统数据类型所创建的用户定义的数据类型。
SQLServer系统提供的基本数据类型有二进制数据、字符型数据、Unicode数据、日期和时间数据、数字数据、货币数据和特殊数据等七种类型。
1、二进制数据类型
二进制数据由十六进制数表示,常用于存储图像等数据。
二进制数据包括Binary、Varbinary和Image三种类型。
◆Binary[(n)]是n位固定长度的二进制数据。
其中,n的取值范围为1~8000。
该类型所占存储空间大小是n+4字节,当输入的二进制数据的长度小于n时,其余部分填充为0。
◆Varbinary[(n)]是n位变长度的二进制数据。
其中,n的取值范围是从1到8000。
其中存储空间的大小为实际所输入数据的长度加上4字节。
◆Image类型也是一种变长二进制数据类型,它与Varbinary数据类型的区别是其最大长度为231–1字节。
2、字符型数据类型
字符数据由字母、符号和数字等组成。
在输入字符数据时,应将数据括在括号内。
字符数据包括Char、Varchar和Text三种类型。
◆Char[(n)]声明定长型字符列、变量或存储过程参数,其中n为其长度,取值范围是1~8000间的整数。
其存储长度固定。
◆Varchar[(n)]声明变长型字符列、变量或存储过程参数,其中n为其长度,取值范围是1~8000间的整数。
其存储长度随输入数据的实际长度变化,但不得超过n指定的值。
◆Text(文本)类型也是一种可变长度数据类型,在定义这种数据类型时不需要定义数据长度,SQLServer会根据数据的实际长度自动为其分配存储空间,Text数据的最大长度可达231-1个字符,它用于存储长度大于8KB的字符数据。
向Text数据列中插入数据时,应将数据引在单引号内。
3、Unicode数据类型
Unicode(统一字符编码标准)是SQLServer7.0以后版本中新增的数据类型,用于支持国际上的非英语语种,Unicode数据类型列能够存储各种Unicode标准字符集定义的所有字符。
每个Unicode字符的存储长度为2字节,所以Unicode数据类型所占用的存储空间为非Unicode数据类型的两倍。
Unicode数据也分nChar、nVarchar和nText三种类型。
◆nChar用于存放固定长度的n个Unicode数据,其最大长度为4000个字符。
◆nVarchar用于存放可变长度的n个Unicode数据,其最大长度也为4000个字符。
◆nText常用于存储长度大于4000的变长Unicode字符,其最大长度为230-1。
4、日期和时间数据类型
日期和时间型数据由有效的日期和时间组成。
这种数据类型分为datetime和smalldatetime两种,二者的主要差别在于它们的存储长度、所表示的时间范围和精度不同。
在输入日期和时间型数据时应将数据引在单引号内。
◆datetime数据类型,所存储的日期范围是从1753年1月1日开始,到9999年12月31日结束(每一个值都要求8个存储字节,表示时间的精度为1/300秒)。
◆smalldatetime数据类型,所存储的日期范围是从1900年1月1日开始,到2079年6月6日结束(每一个值都要求4个存储字节,表示时间的精度为分钟)。
5、数字数据类型
数字数据只包含数字,这些数字包括正数和负数、小数(浮点数)和整数。
数字数据可分为整数型、精确数值型和近似数值型三种类型。
(1)整数型数据有正整数和负整数组成。
在SQLServer2000中,整数存储的数据类型有Bigint、Int、Smallint和Tinyint。
◆Bigint:
它所表示的数据是长整数型,其存储大小为8个字节。
它可存储的数值范围为-263(-9223372036854775808)到263-1(9223372036854775807)。
◆Int或Integer:
它所表示的数据是整数型,其长度为4个字节。
它可存储的数值范围为-2147483648到2147483647;
相当于C语言中的长整数类型,范围为-231到231-1。
◆Smallint或Integer:
它所表示的数据是短整数型,其长度为2字节,相当于C语言中的Int类型,它可存储的数值范围为-32768到32767。
◆Tinyint:
它所表示的数据是微整数类型,其长度为1字节,表示无符号整数,其取值范围为0到255。
(2)精确数值型数据由整数和小数两部分组成,其数据精度保留到最低有效位,所以它能以完整的精度存储十进制数据。
精确数值型有decimal[(p[,s])]和numeric[(p[,s])]两种,在SQLServer中二者完全相同,提供这两种类型是为了保持与ANSI标准兼容。
其中的精度(P)是指数据中所存储的十进制数据位数总和,它包括整数部分和小数部分;
小数位数(S)是指小数点右边能存储的数字位数的最大值。
小数位数S应小于或等于精度P。
(3)近似数值型数据不能精确记录数据的精度,它们所保存数据的精度由二进制数字系统的精度决定。
由于该类型数据的这些特点,所以也被形象地称为“近视数值型数据”。
在SQLServer中,近似数值数据以float(浮点型)和real(实型)数据类型存储。
6、货币数据类型
货币数据可分为Money和Smallmoney两种类型。
◆Money型数据长度为8字节,货币数据的值介于-263(-922,337,203,685,477.5808)与263–1(+922,337,203,685,477.5807)之间,精确到货币单位的千分之十。
◆Smallmoney型数据长度为4字节,该类货币数据的值介于-214,748.3648与+214.748,3647之间,精确到货币单位的千分之十。
7、特殊数据类型
特殊数据包括不能用前面所述的二进制、字符、Unicode、日期和时间、数字和货币数据类型表示的数据。
SQLServer2000提供了如下几种特殊数据类型:
◆Bit(位数据类型):
其数据有两种取值,即0和1。
在输入0以外的其他值时,系统均把它们当作1看待。
位类型的数据长度为1字节,它常被作为逻辑变量使用。
◆Timestamp(时间戳数据类型):
它反映数据库中数据修改的相对顺序,以二进制的格式表示。
时间戳相当于一个单调上升的计数器,其值由系统自动赋予。
◆Uniqueidentifier(惟一标识符数据类型):
由16字节长的十六进制数字组成,表示一个全局惟一的标识号(GUID)。
当表中的记录行要求惟一时,GUID是非常有用的。
惟一标识符代码可以通过调用NEWID函数或其他SQLServer应用编程接口函数获得。
◆sql_variant:
是一种存储SQLServer所支持的各种数据类型(其中Text、nText、Timestamp和sql_variant除外)值的数据类型。
◆table:
是一种特殊的数据类型,存储供以后处理的结果集。
table数据类型只能用于定义table类型的局部变量或用户定义函数的返回值。
4.2.2文本和图像数据
文本和图像数据在SQLServer中是用Text、nText和Image数据类型来表示的。
在SQLServer中,Text、nText和Image三种数据类型是特殊的类型,它们所存储的数据量往往都很大,所以不能像表中其他类型的数据那样一行一行地被存放数据页中,而是经常被存储在专门的数据页中,在数据行的相应位置处只记录了指向这些数据的实际存储位置的指针。
如上所述,在SQLServer7.0以前的版本中,文本和图像数据都是与表中的其他数据分开存储的,数据行只包括一个16字节的文本指针,该指针指向一个树形的根节点,该树形由映射存储串片段的页的内部指针构成。
在SQLServer2000中,提供了将小型的文本和图像数据在行中存储的功能。
用户可为包含Text、nText或Image数据列的表设置textinrow选项,还可以指定textinrow选项限制,所设置的限制可从24字节到7000字节。
如果为包含Text、nText或Image数据列的表设置了textinrow选项,则在下列情况下,Text、nText或Image字符串可直接存储在数据行中:
◆字符串的长度比指定的长度限制短。
◆数据行中有足够的可用空间容纳字符串。
将小型的文本和图像数据存储在行中好处是:
当text、ntext或image字符串被存储在数据行中时,SQLServer就无须访问单独的页或页的集合来读写这些字符串。
这使得读写text、ntext或image列中的字符串就象读写varchar、nvarchar或varbinary字符串一样快。
在SQLServer2000中,用户可使用系统存储过程sp_tableoption来为数据表设置textinrow选项。
其语法格式如下:
sp_tableoption 表名,'
textinrow'
,'
ON/OFF'
例如,以下的语句将Sales数据库中的Goods表设置为启用textinrow选项:
sp_tableoption'
Goods'
'
ON'
用户也可以使用系统存储过程sp_tableoption为数据行中存储的Text、nText和Image字符串指定长度,其范围限制在24到7000字节之间(默认为256)。
范围值'
例如,将Sales数据库中的Goods表的textinrow选项的长度范围限制为1000,其具体代码如下:
1000'
4.2.3IDENTITY列
IDENTITY列即标识列,这是一个自动增长列。
每一个表中都可以设置一个标识列,它包含由系统产生的能够标识表中每一行数据的惟一序列值。
在表中定义一个标识列的好处是:
每向表中插入一条序号的记录时,SQLServer都会自动为其生成一个惟一的值作为序号,这样就可以避免人工添加序号所带来的冲突问题。
1、IDENTITY列的定义
将一个列作为表中的标识列,需要为该列定义IDENTITY属性,其格式如下:
IDENTITY[(seed,increment)]
在上面的格式代码中,seed指初始值,即表中的第一行数据的标识列的取值,默认为1;
increment指步长值,即每一个新标识值比上一个增长的幅度,其默认值也为1。
为一个列定义IDENTITY属性后,每当向表中插入一个包括了标识列的行时,SQLServer2000会自动将当前最新的标识值加上步长值作为该行的标识值,插入到表中。
在SQLServer2000中,使用IDENTITY列时,应该注意以下几方面的问题:
◆每张表只允许有一个IDENTITY列。
◆IDENTITY列不能更新。
◆IDENTITY列不允许有NULL值(空值)。
◆IDENTITY列中的数据必须是下列数据类型之一:
INT、SMALLINT、TINYINT、NUMERIC(小数为0)、DECIMAL(小数为0)。
2、IDENTITY列的应用
(1)引用IDENTITY列
IDENTITY列可以在SQL查询中被引用,以此代替该列的列名。
引用一个IDENTITY列,需要使用IDENTITYCOL关键字。
例如:
USESales
GO
SELECT*FROMCustomers
WHEREIDENTITYCOL=3
该语句的作用是在Sales数据库的Customers表中找出标识列的值为3的记录,这与直接使用该列的列名进行查找的效果一样。
用户可在查询分析器中将下面的语句输入,执行后看看效果:
WHERE客户号=3
使用两种语句进行查询,执行后的最终效果分别如图4-1和如图4-2所示。
图4-1 引用IDENTITY列查询 图4-2 使用列名进行查询
关于表中数据的查询,将在本章后面的内容中进行介绍。
(2)获取标识列的初始值和步长值
通过使用函数ident_seed(表名),用户可以获得表上标识列的seed(初始)值;
通过使用函数(表名),可以获得标识列的增量值(步长)。
要查询Sales数据库中Customers表的标识列的初始值和增量值,则可分别在查询分析器中输入下面的语句:
selectident_seed(‘Customers’)
selectident_incr(‘Customers’)
在查询分析器中执行后,结果窗格中的显示分别如图4-3和如图4-4所示。
图4-3查询标识列的初始值 图4-4查询标识列的增量值
由图可知,Customers表的标识列的初始值为1,其标识列的增量值也为1。
(3)获取标识列的最新标识值
用户可以使用@@identity全局变量得到最新的标识值,例如要获取Sales数据库中Customers表标识列的最新标识值,可用下面的语句:
select@@identityfromCustomers
在查询分析器中执行后,在结果窗格中可以看到其最新标识值。
尽管IDENTITY属性自动为表生成行号,但不同表的标识符列可以生成相同的行号。
这是因为IDENTITY属性只须在所使用的表上保持惟一。
如果应用程序需要生成在整个数据库或世界各地所有网络计算机的全部数据库中均为惟一的标识符列,请使用ROWGUIDCOL属性、uniqueidentifier数据类型和NEWID函数。
4.2.4数据完整性
所谓数据完整性就是指数据库中数据的正确性和一致性,利用数据的完整性约束,可以保证数据库中数据的质量。
因此,在进行表的设计时,一定要注意到对数据完整性的设计。
在关系数据库中,一般有以下三类完整性:
◆域完整性:
对具体一列上的数据的有效性限制。
◆实体完整性:
保证表中所有的行都是惟一的,以确保所有的记录都是可以区分的。
◆参照完整性:
这是对涉及两个或两个以上表的数据的一致性维护。
有关数据完整性的相关内容,将在以后用专门的章节进行介绍,在此不再赘述。
4.3创建表
创建表的方法有两种,一种方法是使用T-SQL(CREATETABLE)语句,另一种方法是使用企业管理器。
本节就来详细介绍这两种方法。
4.3.1使用CREATETABLE语句创建表
在SQLServer中,用户可以使用T-SQL语言的CREATETABLE语句来创建表。
CREATETABLE 表名
(列名1 数据类型等信息,
列名2 数据类型等信息,
列名3 数据类型等信息,
……)
下面就具体用CREATETABLE语句在Sales数据库中创建三个具体的表:
Customers表、Goods表和Orders表。
1、创建Customers(客户)表
Customers表是记录客户基本信息的表,正如本章开始时讲的那样,本表要记录的信息主要有客户姓名、职位、地址、电话等信息,也就是说该表要包括的列就是以上的这几种。
另外,该表还应有一个客户编号列,并且这个列被作为该表的标识列。
其具体代码如下:
CREATETABLECustomers(
客户号int
IDENTITY(1,1)
PRIMARYKEY,
姓名varchar(20)
NOTNULL,
公司varchar(50),
职位varchar(20),
地址varchar(100),
电话varchar(20)
UNIQUE
)
如果在Sales数据库中不存在Customers表,则在查询分析器中执行了上面的语句后,结果窗格中会给出“命令已成功完成”的提示信息,如图4-5所示。
否则,则会在结果窗格中显示出错信息,如图4-6所示。
图4-5完成Customers表的创建 图4-6显示出错信息
用户可能已经注意到,在上面的语句中包含了一些诸如IDENTITY(1,1)、PRIMARYKEY、NOTNULL、UNIQUE等关键字,这就是对表中各列进行完整性约束的一些词语。
其中,IDENTITY(1,1)表示所约束的列将作为本表的标识列,其初始值为1,增量值也为1;
PRIMARYKEY表示该列为主键,是主键约束;
NOTNULL表示被约束的列的数据不能为空值;
UNIQUE表示被约束的列的数据具有惟一性。
关于这些内容,在以后专门的章节中进行介绍,在此用户只需对其有个大致的了解即可。
2、创建Goods(存货)表
Goods表记录了货品的信息,在本章的开始也已经讲过,本表包括的列有货品名、库存量、进货地和价格。
另外,本表还应包括货号、货品描述等列,其中货号为本表的标识列。
创建Goods表的具体代码如下:
CREATETABLEGoods(
货号int
IDENTITY(1,1),
品名varchar(20)
CONSTRAINTPK_goodsnamePRIMARYKEY,
描述varchar(200),
库存量int,
进货地varchar(50),
价格money
在创建本表的代码中,“品名”列被设为该表的主键。
与Customers表不同,本次定义用的是命名约束。
使用命名约束,可以为任何一个列的任何一种约束明确地指定一个约束名。
对一个列使用命名约束的形式为:
CONSTRAINT约束名列约束
例如,在Customers表的“电话”列中,使用了惟一性约束(UNIQUE),如果要为该约束指定一个约束名UN_TEL,就应将其代码改为以下的语句:
CONSTRAINTUN_TELUNIQUE
约束名在一个表的范围内应该是惟一的。
3、创建Orders(订单)表
Orders表记录的是订单的信息,包括订单序号、下订单的客户、所订货品、订货数量和金额等列。
CREATETABLEOrders(
序号intidentity(1,1)
CONSTRAINTPK_ORDERIDPRIMARYKEY,
NOTNULL
FOREIGNKEY(客户号)
REFERENCESCustomers(客户号)
ONDELETENOACTION,
货品名称varchar(20)
NOT
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- SQL Server实用基础教程第4章 表的创建和管理 Server 实用 基础教程 创建 管理