第3章 表的创建和管理.docx
《第3章 表的创建和管理.docx》由会员分享，可在线阅读，更多相关《第3章 表的创建和管理.docx（23页珍藏版）》请在冰豆网上搜索。
第3章表的创建和管理
第3章SQLServer2005表的创建和管理
第1节SQLServer2005表的基本知识
1.1什么是表？
关系数据库中，每一个关系就是一张表。
表是关系模型中表示实体的方式，是用来组织和存储数据、具有行列结构的数据库对象。
在为一个数据库设计表之前，应该完成需求分析，确定概念模型，将概念模型转换为关系模型，关系模型中的每一个关系对应数据库中的一个表。
1.2表的特点
表具有下列一些基本特点：
代表实体、由行和列组成、列名在一个表中是唯一的、行和列的顺序是不重要等。
代表实体，有唯一的名称来确定实体。
表由行和列组成，行也叫记录，元组。
每一行都是对某个实体完成的描述。
例如在上面的学生表中，SID是2005216006这行记录就表示了郝莎同学的完整信息。
一条记录就是该实体的一个实例。
表中的每个列，也叫字段，都是对该实体的某种属性的描述。
如birthday字段描述了所有同学的生日。
表中行和列的顺序是任意的。
列名在一个表中是唯一的。
即在一个表中不能有名称相同的两个或两个以上的列；但在同一个数据库中的不同表中，可以使用相同的列名。
列名在一个表中的唯一性是SQLServer2005强制实现的；而行在一个表中的唯一性是由主键约束来强制实现的。
即不能存在相同的主键，这意味着两个完全相同的实体，区别不开。
1.3表的类型
SQLServer2005把表分为：
普通表、分区表、临时表和系统表。
普通表是基本表，其他类型的表都是有特殊用途的表，是在特殊应用环境下，为了提高系统的使用效率而派生的表。
1.普通表
即标准表。
就是数据库中存储数据的表。
最常用，最基本的表。
2.分区表
原理：
当表很大时，可以水平地把数据分割成一些单元，放在同一个数据库的多个文件组中，从而实现对单元中数据的并行访问，提高效率。
应用环境：
当表的数据很大，而且这些数据经常被不同的使用方式访问，则应当建立分区表。
优点是可通过分区快速地访问和管理数据的某部分子集而不是整个数据表，从而便于管理大表和索引；可并行访问数据。
3.临时表
概念：
临时创建，不能永久存在的表。
两种临时表：
本地（局部）临时表和全局临时表。
本地临时表只是对一个数据库实例的一次连接中的创建者是可见的只对创建者可见。
在用户断开数据库的连接时，本地临时表就被删除。
全局临时表创建后对所有的用户和连接都是可见的，并且只有所有的用户都断开临时表相关的表时，全局临时表才会被删除。
4.系统表
概念：
系统表用来保存一些服务器配置、数据库设置、用户和表对象的描述等系统信息。
特点：
用户不能直接操作系统表，只有DBA才能使用。
1.4表的完整性体现
Ø主键约束体现实体完整性，即主键各列不能为空且主键作为行的唯一标识，主键作为实体的唯一标识。
Ø外键约束体现参照完整性
Ø默认值和规则等体现用户定义的完整性
1.5表的数据类型
数据类型
系统数据类型
应用说明
二进制
存储二进制数据
image
图像、视频、音乐
Binary[（n）]
标记或标记组合数据,n的范围：
1～8000，n二进制位数
varbinary[（n）]
同上（变长）
精确
数字
精确整数
bigint
长整数-263～263-1
int
整数-231～231-1
smallint
短整数-215～215-1
tinyint
更小的整数0～255,每个值仅占一个字节
精确小数
Decimal[（p[,s]）]
小数，p:
精度，最大数字位数,取值：
1～38,默认18，则decimal数据类型的取值范围：
-1038+1-1038-1。
s:
最大小数位数。
如decimal（10,2）表示可存储精度为10、小数位数是2的数据。
numeric[（p[,s]）]
同上
近似数字
float[（n）]
浮点－1.79E＋308～1.79E＋308
real
－3.40E＋38～3.40E＋38
字符
char[（n）]
定长字符型，用户自定义长度n，最长可达8000字符。
若实际字符少于n，则空格填充;若多于，则截断。
varchar[（n）]
变长字符型，字符长度可达8000字符，varchar类型的存储空间，岁存储在表列中的每一个数据的字符数的不同而变化。
降低存储空间浪费
text
变长文本型，存储字符长度大于8000的变长字符
Unicode
当数据库中存储的数据可能涉及多种语言时使用该类型
nchar[（n）]
unicode字符（双倍空间）
nvarchar[（n）]
unicode字符（双倍空间）
ntext
unicode字符（双倍空间）
日期和时间
Datetime
1753-1-1～9999-12-31（12:
00:
00）
smalldatetime
1900-1-1～2079-6-6
货币
Money
-263～263-1（保留小数点后四位）
smallmoney
-231～231-1（保留小数点后四位）
特殊
bit
0/1，判定真或假
Timestamp
自动生成的惟一的二进制数，修改该行时随之修改，反应修改记录的时间
cursor
只用于与定义游标和使用游标有关的语句中
xml
用来保存整个XML文档。
table
类似临时表，可用于存储结果集。
只用在定义类型为table的局部变量和用户定义函数的返回值
uniqueidentifier
全局惟一标识（GUID），十六进制数字，由网卡/处理器ID以及时间信息产生，用法同上
用户自定义
用户自行命名
用户可创建自定义的数据类型
1.6表的设计
设计表时需要确定如下内容：
Ø表中需要的列以及每一列的类型（必要时还要有长度）
Ø列是否可以为空
Ø是否需要在列上使用约束、默认值和规则
Ø需要使用什么样的索引
Ø哪些列作为主键
第2节创建表
在MicrosoftSQLServer2005系统中，可用CREATETABLE语句创建表，也可用可视化的SQLServerManagementStudio图形工具创建表。
2.1使用SSMS创建表
在【对象资源管理器】窗口－>展开“数据库”节点－>展开所选择的具体数据库节点－>右击“表”节点－>选择“新建表”命令－>进入表设计器－>进行表的定义。
例如：
在山大分校教务管理WHEDUC中的选课数据库中创建学生表Student,课程表Course,选课表SC。
主键PRIMARYKEY外键FOREIGNKEY候选键CANDICATEKEY
唯一性UNIQUE
教务管理中的选课数据模型为：
Student（SID,Sname,Sex,Birthday,Specialty）
PK:
SID
Course（CID,Cname,Credit）
PK:
CID
SC（SID,CID,Grade）
PK:
SID,CID
FK:
SID和CID
（1）在【对象资源管理器】窗口中，展开“数据库”下的WHEDUC节点，右击“表”节点，选择“新建表”命令，进入表设计器，在表设计器的第一列中输入列名，第二列选择数据类型，第三列选择是否为空；
例如：
表Student：
表Course：
表SC：
2.2使用T-SQL语句创建表
格式：
CREATETABLE表名
（列名1数据类型列级完整性约束，
列名2数据类型列级完整性约束，
…
列名n类型约束，
表级完整性约束，…
）；
约束：
实现表的完整性
DEFAULT常量表达式：
默认值约束。
NULL/NOTNULL：
空值/非空值约束。
UNIQUE：
单值约束。
PRIMARYKEY：
主键约束，等价非空、单值。
REFERENCES父表名（主键）：
外键约束。
CHECK（逻辑表达式）：
检查约束。
创建表的语法格式有很多选项，非常复杂，可以通过例子和联机帮助逐步了解。
例如：
在图书管理系统中的数据库（Library）中，创建读者表（Reader）,读者类型表（ReaderType），图书表（Book）和借阅表（Borrow）。
图书管理系统的数据模型：
ReaderType（TypeID,Typename,LimitNum,LimitDays）
PK:
TypeID
Reader（RID,Rname,TypeID,Lendnum）
PK：
RIDFK：
TypeID
Book（BID,Bname,Author,PubComp,PubDate,Price）
PK：
BID
Borrow（RID,BID,LendDate,ReturnDate）
PK：
RID,BID,LendDateFK：
RID和BID
（2）创建读者类型表ReaderType
CREATETABLEReaderType
（
TypeIDintNOTNULLprimarykey,--类型编号,主键
Typenamechar（8）NULL,--类型名称
LimitNumintNULL,--限借数量
LimitDaysintNULL--借阅期限
）
（3）创建读者表Reader
USELibrary
GO
CREATETABLEReader
（
RIDchar（10）NOTNULLPRIMARYKEY,--读者编号，主键
Rnamechar（8）NULL,--读者姓名
TypeIDintNULL,--读者类型
LendnumintNULL,--已借数量
FOREIGNKEY（TypeID）REFERENCESReaderType（TypeID）
ONDELETENOACTION,--外键，不级联删除
）
（4）创建图书表Book
USELibrary
GO
CREATETABLEBook
（
BIDchar（9）PRIMARYKEY,--图书编号，主键
Bnamevarchar（42）NULL,--图书书名
Authorvarchar（20）NULL,--作者
PubCompvarchar（28）NULL,--出版社
PubDatedatetimeNULL,--出版日期
Pricedecimal（7,2）NULLCHECK（Price>0）--定价，检查约束
）
（5）创建图书借阅表Borrow（RID,BID,LendDate,ReturnDate）
USELibrary
GO
CREATETABLEBorrow
（
RIDchar（10）NOTNULL--读者编号外键
FOREIGNKEYREFERENCESReader（RID）ONDELETECASCADE,
/*删除主表记录时不级联删除子表相应记录*/
BIDchar（15）NOTNULL--图书编号外键
FOREIGNKEYREFERENCESBook（BID）ONDELETENOACTION,
/*删除主表记录时级联删除子表相应记录*/
LendDatedatetimeNOTNULLDEFAULT（getdate（））,
/*借期，默认值为当前日期*/
ReturnDatedatetimeNULL,--还期
primarykey（RID,BID,LendDate）--表级约束，主键
）
2.3创建特殊表
2.3.1创建带计算列的表
有些列可以指定数据类型和属性，有些列可以不用输入，而是通过其他列的计算得到。
CREATETABLE产品总汇（
产品名称nvarchar（20）NOTNULL,
数量intNOTNULL,
单价decimal（10,2）NOTNULL,
单一产品总价值AS数量*单价）
说明：
在单一产品总价值列中没有指定数据类型，其值是数量*单价。
AS为关键字。
一般该列为虚拟列，不物理存储。
如要将该列物理存储，如下：
CREATETABLE产品总汇1（
产品名称nvarchar（20）NOTNULL,
数量intNOTNULL,
单价decimal（10,2）NOTNULL,
单一产品总价值AS数量*单价PERSISTED）
2.3.2创建临时表
若创建临时表，在表名前加“＃”或“＃＃”。
“＃”表示创建的是本地临时表，只能由创建者使用。
“＃＃”表示创建的是全局临时表，该表在生存周期内由所有用户使用。
CREATETABLE##产品总汇（
产品名称nvarchar（20）NOTNULL,
数量intNOTNULL,
单价decimal（10,2）NOTNULL,
单一产品总价值AS数量*单价PERSISTED）
2.3.3创建分区表
1步骤为：
创建分区函数，指定如何分区－>创建分区方案，指定分区函数的分区在文件组上的位置。
－>最后创建使用分区方案。
2CREATEPARTITIONFUNCTION创建分区函数：
语法：
CREATEPARTITIONFUNCTIONpartition_function_name（input_parameter_type）
ASRANGE[LEFT|RIGHT]
FORVALUES（[boundary_value[,...n]]）
[;]
功能：
在当前数据库中创建一个函数，该函数可根据指定列的值将表或索引的各行映射到分区。
使用CREATEPARTITIONFUNCTION是创建已分区表或索引的第一步。
参数：
partition_function_name
是分区函数的名称。
分区函数名称在数据库内必须唯一，并且符合标识符的规则。
input_parameter_type
是用于分区的列的数据类型。
当用作分区列时，除text、ntext、image、xml、timestamp、varchar（max）、nvarchar（max）、varbinary（max）、别名数据类型或CLR用户定义数据类型外，所有数据类型均有效。
实际列（也称为分区列）是在CREATETABLE或CREATEINDEX语句中指定的。
boundary_value
为使用partition_function_name的已分区表或索引的每个分区指定边界值。
如果boundary_value为空，则分区函数使用partition_function_name将整个表或索引映射到单个分区。
只能使用CREATETABLE或CREATEINDEX语句中指定的一个分区列。
boundary_value是可以引用变量的常量表达式。
这包括用户定义类型变量，或函数以及用户定义函数。
它不能引用Transact-SQL表达式。
boundary_value必须与input_parameter_type中提供的数据类型相匹配或者可隐式转换为该数据类型，并且如果该值的大小和小数位数与input_parameter_type中相应的值的大小和小数位数不匹配，则在隐式转换过程中该值不能被截断。
...n
指定boundary_value提供的值的数目，不能超过999。
所创建的分区数等于n+1。
不必按顺序列出各值。
如果值未按顺序列出，则数据库引擎将对它们进行排序、创建函数并返回一个警告，说明未按顺序提供值。
如果n包括任何重复的值，则数据库引擎将返回错误。
LEFT|RIGHT
指定当间隔值由数据库引擎按升序从左到右排序时，boundary_value[,...n]属于每个边界值间隔的哪一侧（左侧还是右侧）。
如果未指定，则默认值为LEFT。
3CREATEPARTITIONSCHEME创建分区方案
语法：
CREATEPARTITIONSCHEMEpartition_scheme_name
ASPARTITIONpartition_function_name
[ALL]TO（{file_group_name|[PRIMARY]}[,...n]）
[;]
功能：
在当前数据库中创建一个将已分区表或已分区索引的分区映射到文件组的方案。
已分区表或已分区索引的分区的个数和域在分区函数中确定。
必须首先在CREATEPARTITIONFUNCTION语句中创建分区函数，然后才能创建分区方案。
参数：
partition_scheme_name
分区方案的名称。
分区方案名称在数据库中必须是唯一的，并且符合标识符规则。
partition_function_name
使用分区方案的分区函数的名称。
分区函数所创建的分区将映射到在分区方案中指定的文件组。
partition_function_name必须已经存在于数据库中。
单个分区不能同时包含FILESTREAM和非FILESTREAM文件组。
ALL
指定所有分区都映射到在file_group_name中提供的文件组，或映射到主文件组（如果指定了[PRIMARY]。
如果指定了ALL，则只能指定一个file_group_name。
file_group_name|[PRIMARY][,...n]
指定用来持有由partition_function_name指定的分区的文件组的名称。
file_group_name必须已经存在于数据库中。
如果指定了[PRIMARY]，则分区将存储于主文件组中。
如果指定了ALL，则只能指定一个file_group_name。
分区分配到文件组的顺序是从分区1开始，按文件组在[,...n]中列出的顺序进行分配。
在[,...n]中，可以多次指定同一个file_group_name。
如果n不足以拥有在partition_function_name中指定的分区数，则CREATEPARTITIONSCHEME将失败，并返回错误。
如果partition_function_name生成的分区数少于文件组数，则第一个未分配的文件组将标记为NEXTUSED，并且出现显示命名NEXTUSED文件组的信息。
如果指定了ALL，则单独的file_group_name将为该partition_function_name保持它的NEXTUSED属性。
如果在ALTERPARTITIONFUNCTION语句中创建了一个分区，则NEXTUSED文件组将再接收一个分区。
若要再创建一个未分配的文件组来拥有新的分区，请使用ALTERPARTITIONSCHEME。
在file_group_name[1,...n]中指定主文件组时，必须像在[PRIMARY]中那样分隔PRIMARY，因为它是关键字。
例：
CREATEPARTITIONFUNCTIONNunberPF（INT）
ASRANGELEFTFORVALUES（10,100,1000,10000）
说明：
创建一个名称是NumberPF的分区函数，该函数把INT类型的列中的数据分成5个区，即<=10，between10and<=100,between100and<=1000,between1000and<=10000,>10000的5个区。
CREATEPARTITIONSCHEMENumberPS
ASPARTITIONNumberPF
TO（NumFG1,NumFG2,NumFG3,NumFG4,NumFG5,）
说明：
对NumberPF分区函数创建一个NumberPS分区方案，5个分区分别放在NumFG1,NumFG2,NumFG3,NumFG4,NumFG5上。
其中，使用ASPARTITION子句指定分区函数名称。
分区完成后，则创建分区表。
创建分区表需要在CREATETABLE语句后，加ON关键字指定分区方案名称和分区列。
CREATETABLENumPT
（
IDINT,
NumberINT,
NDateDATETIME,
NPersonVARCHAR（32）,
）
ONNumberPF（Number）
第3节修改表
3.1使用SSMS修改表
在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，展开“表”节点，右键要修改的表，选择“修改”命令，进入表设计器即可进行表的定义的修改。
3.2使用T-SQL语句修改表
格式：
ALTERtable表名
（ALTERCOLUMN列名列定义，
ADD列名1类型约束，
DROP列名
…
）
注意：
列定义包括列的数据类型和完整性约束
例子：
1修改属性
例如：
把表Book中PubComp的类型varchar（28）改为varchar（30）。
USELibrary
GO
ALTERTABLEBook
ALTERCOLUMNPubCompvarchar（30）NOTNULL
GO
2添加或删除列
例1：
为表Book添加ISBN列。
国际标准书号由ISBN冠头，后接以下四段10位数字，每两部分之间以水平线或斜线隔开。
如：
ISBN7-115-08612-5
USELibrary
GO
ALTERTABLEBook
ADDISBNvarchar（13）NULL
GO
例2：
为表Reader添加邮件地址。
USELibrary
GO
ALTERTABLEReader
ADDE-mailvarchar（50）NULLCHECK（E-maillike'%@%'）
GO
例3：
为表Reader删除邮件地址。
USELibrary
GO
ALTERTABLEReader
DROPCOLUMNE-mail
GO
说明：
必须先删除其上的约束。
3添加或删除约束
例1：
为表Borrow添加主键约束（假设还没有创建）。
USELibrary
GO
ALTERTABLEBorrow
ADDPRIMARYKEY（RID,BID,LendDate）
GO
例2：
为表Borrow删除主键约束。
USELibrary
GO
ALTERTABLEBorrow
DROPPRIMARYKEY（RID,BID,LendDate）
GO
第4节删除表
4.1使用SSMS删除表
在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，展开“表”节点，右键要删除的表，选择“删除”命令或按下“DELETE”键。
4.2使用T-SQL语句删除表
DROPTABLE表名
例如：
先随便在数据库Library中建一个表Test，然后删除。
USELibrary
GO
DROPTABLETest
第5节记录操作
5.1插入记录
5.1.1使用SSMS添加记录
在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，展开“表”节点，右键要插入纪录的表，选择
升级会员 