第1节 SQL Server 2005表的基本知识
1.1 什么是表？
关系数据库中，每一个关系就是一张表。表是关系模型中表示实体的方式，是用来组织和存储数据、具有行列结构的数据库对象。
在为一个数据库设计表之前，应该完成需求分析，确定概念模型，将概念模型转换为关系模型，关系模型中的每一个关系对应数据库中的一个表。
1.2 表的特点
表具有下列一些基本特点：代表实体、由行和列组成、列名在一个表中是唯一的、行和列的顺序是不重要等。
代表实体，有唯一的名称来确定实体。
表由行和列组成，行也叫记录，元组。每一行都是对某个实体完成的描述。例如在上面的学生表中，SID是2005216006这行记录就表示了郝莎同学的完整信息。一条记录就是该实体的一个实例。
表中的每个列，也叫字段，都是对该实体的某种属性的描述。如birthday字段描述了所有同学的生日。
表中行和列的顺序是任意的。
列名在一个表中是唯一的。即在一个表中不能有名称相同的两个或两个以上的列；但在同一个数据库中的不同表中，可以使用相同的列名。列名在一个表中的唯一性是SQL Server 2005强制实现的；而行在一个表中的唯一性是由主键约束来强制实现的。即不能存在相同的主键，这意味着两个完全相同的实体，区别不开。
1.3 表的类型
SQL Server 2005把表分为：普通表、分区表、临时表和系统表。
普通表是基本表，其他类型的表都是有特殊用途的表，是在特殊应用环境下，为了提高系统的使用效率而派生的表。 1. 普通表
即标准表。就是数据库中存储数据的表。最常用，最基本的表。 2. 分区表
原理：当表很大时，可以水平地把数据分割成一些单元，放在同一个数据库的多个文件组中，从而实现对单元中数据的并行访问，提高效率。
应用环境：当表的数据很大，而且这些数据经常被不同的使用方式访问，则应当建立分区表。
优点是可通过分区快速地访问和管理数据的某部分子集而不是整个数据表，从而便于管理大表和索引；可并行访问数据。 3. 临时表
概念：临时创建，不能永久存在的表。
两种临时表：本地(局部)临时表和全局临时表。本地临时表只是对一个数据库实例的一次连接中的创建者是可见的只对创建者可见。在用户断开数据库的连接时，本地临时表就被删除。全局临时表创建后对所有的用户和连接都是可见的，并且只有所有的用户都断开临时表相关的表时，全局临时表才会被删除。 4. 系统表
概念：系统表用来保存一些服务器配置、数据库设置、用户和表对象的描述等系统信息。
特点：用户不能直接操作系统表，只有DBA才能使用。 1.4 表的完整性体现
主键约束体现实体完整性，即主键各列不能为空且主键作为行的唯一标
识，主键作为实体的唯一标识。 外键约束体现参照完整性
默认值和规则等体现用户定义的完整性
1.5 表的数据类型
数据类型 二进制 存储二进制数据 系统数据类型 image Binary[(n)] varbinary[(n)] bigint 精确整数 int smallint tinyint 精确 数字 精确小数 numeric[(p[,s])] 近似数字 float[(n)] real char[(n)] Decimal[(p[,s])] 图像、视频、音乐 标记或标记组合数据,n的范围：1～8000，n二进制位数 同上（变长） 长整数-263～263-1 整数-231～231-1 短整数-215～215-1 更小的整数0～255,每个值仅占一个字节 小数，p: 精度，最大数字位数,取值：1～38,默认18，则decimal数据类型的取值范围：-1038+1-1038-1。s: 最大小数位数。如decimal(10,2)表示可存储精度为10、小数位数是2的数据。 同上 浮点－1.79E＋308～1.79E＋308 －3.40E＋38～3.40E＋38 定长字符型，用户自定义长度n，最长可达8000字符。若实际字符少于n，则空格填充;若多于，则截断。 字符 varchar[(n)] 变长字符型，字符长度可达8000字符，varchar类型的存储空间，岁存储在表列中的每一个数据的字符数的不同而变化。降低存储空间浪费 text Unicode 当数据库中存储的数据可能涉及多种语言时使用该类型 日期和时间 Datetime smalldatetime Money smallmoney bit Timestamp cursor 特殊 xml table uniqueidentifier 1753-1-1～9999-12-31（12:00:00） 1900-1-1～2079-6-6 -263～263-1（保留小数点后四位） -231～231-1（保留小数点后四位） 0/1，判定真或假 自动生成的惟一的二进制数，修改该行时随之修改，反应修改记录的时间 只用于与定义游标和使用游标有关的语句中 用来保存整个XML文档。 类似临时表，可用于存储结果集。只用在定义类型为table的局部变量和用户定义函数的返回值 全局惟一标识（GUID），十六进制数字，由网卡/处理器ID以及时间信息产生，用法同上 nchar[(n)] nvarchar[(n)] ntext 变长文本型，存储字符长度大于8000的变长字符 unicode字符（双倍空间） unicode字符（双倍空间） unicode字符（双倍空间） 应用说明 货币 用户自定义 用户自行命名 用户可创建自定义的数据类型 1.6 表的设计
设计表时需要确定如下内容：
表中需要的列以及每一列的类型（必要时还要有长度） 列是否可以为空
是否需要在列上使用约束、默认值和规则 需要使用什么样的索引 哪些列作为主键
第2 节 创建表
在Microsoft SQL Server 2005系统中，可用CREATE TABLE语句创建表，也可用可视化的SQL Server Management Studio图形工具创建表。 2.1使用SSMS创建表
在【对象资源管理器】窗口－>展开“数据库”节点－>展开所选择的具体数据库节点－>右击“表”节点－>选择“新建表”命令－>进入表设计器－>进行表的定义。
例如：在山大分校教务管理WHEDUC中的选课数据库中创建学生表Student, 课程表Course, 选课表SC。
主键PRIMARY KEY 外键FOREIGN KEY候选键CANDICATE KEY 唯一性 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语句创建表
格式：CREATE TABLE表名
（列名1 数据类型 列级完整性约束， 列名 2 数据类型 列级完整性约束，
„
列名n 类型 约束，
表级完整性约束，„ ）；
约束：实现表的完整性
DEFAULT 常量表达式：默认值约束。 NULL/NOT NULL：空值/非空值约束。 UNIQUE：单值约束。
PRIMARY KEY：主键约束，等价非空、单值。 REFERENCES 父表名 (主键)：外键约束。 CHECK (逻辑表达式)：检查约束。
创建表的语法格式有很多选项，非常复杂，可以通过例子和联机帮助逐步了解。
例如：在图书管理系统中的数据库（Library）中，创建读者表（Reader）, 读者类型表（ReaderType），图书表（Book）和借阅表（Borrow）。
图书管理系统的数据模型：
ReaderType(TypeID, Typename,LimitNum, LimitDays) PK: TypeID
Reader (RID,Rname,TypeID,Lendnum) PK：RID FK：TypeID
Book (BID,Bname,Author, PubComp, PubDate,Price) PK：BID
Borrow (RID, BID,LendDate, ReturnDate) PK：RID, BID, LendDate FK：RID和BID (2)创建读者类型表ReaderType CREATE TABLE ReaderType
(
TypeID int NOT NULL primary key,--类型编号,主键 Typename char(8) NULL,--类型名称 LimitNum int NULL,--限借数量 LimitDays int NULL --借阅期限 )
(3)创建读者表Reader USE Library GO
CREATE TABLE Reader (
RID char(10) NOT NULL PRIMARY KEY,--读者编号，主键 Rname char(8) NULL,--读者姓名 TypeID int NULL,--读者类型 Lendnum int NULL ,--已借数量
FOREIGN KEY(TypeID) REFERENCES ReaderType(TypeID) ON DELETE NO ACTION, --外键，不级联删除 )
(4)创建图书表Book USE Library GO
CREATE TABLE Book (
BID char(9) PRIMARY KEY,--图书编号，主键 Bname varchar(42) NULL,--图书书名 Author varchar(20) NULL,--作者 PubComp varchar(28) NULL,--出版社 PubDate datetime NULL,--出版日期
Price decimal(7,2) NULL CHECK (Price>0)--定价，检查约束 )
(5)创建图书借阅表Borrow (RID, BID,LendDate, ReturnDate) USE Library GO
CREATE TABLE Borrow (
RID char(10) NOT NULL --读者编号外键
FOREIGN KEY REFERENCES Reader(RID) ON DELETE CASCADE, /*删除主表记录时不级联删除子表相应记录*/ BID char(15) NOT NULL --图书编号外键
FOREIGN KEY REFERENCES Book(BID) ON DELETE NO ACTION, /*删除主表记录时级联删除子表相应记录*/ LendDate datetime NOT NULL DEFAULT(getdate()), /*借期，默认值为当前日期*/ ReturnDate datetime NULL,--还期
primary key(RID,BID,LendDate) --表级约束，主键 )
2.3 创建特殊表 2.3.1 创建带计算列的表
有些列可以指定数据类型和属性，有些列可以不用输入，而是通过其他列的计算得到。
CREATE TABLE产品总汇(
产品名称 nvarchar(20) NOT NULL, 数量int NOT NULL,
单价 decimal(10,2) NOT NULL, 单一产品总价值 AS 数量*单价)
说明：在单一产品总价值列中没有指定数据类型，其值是数量*单价。AS为关键字。一般该列为虚拟列，不物理存储。如要将该列物理存储，如下：
CREATE TABLE产品总汇(
产品名称 nvarchar(20) NOT NULL, 数量int NOT NULL,
单价 decimal(10,2) NOT NULL,
单一产品总价值 AS 数量*单价 PERSISTED) 2.3.2 创建临时表
若创建临时表，在表名前加“＃”或“＃＃”。“＃”表示创建的是本地临时表，只能由创建者使用。“＃＃”表示创建的是全局临时表，该表在生存周期内由所有用户使用。
CREATE TABLE ##产品总汇(
产品名称 nvarchar(20) NOT NULL, 数量int NOT NULL,
单价 decimal(10,2) NOT NULL,
单一产品总价值 AS 数量*单价 PERSISTED) 2.3.3 创建分区表
1步骤为：创建分区函数，指定如何分区－>创建分区方案，指定分区函数的分区在文件组上的位置。－>最后创建使用分区方案。 2 CREATE PARTITION FUNCTION创建分区函数：
语法：CREATE PARTITION FUNCTION partition_function_name
( input_parameter_type ) AS RANGE [ LEFT | RIGHT ]
FOR VALUES ( [ boundary_value [ ,...n ] ] ) [ ; ] 功能：在当前数据库中创建一个函数，该函数可根据指定列的值将表或索引的各行映射到分区。使用 CREATE PARTITION FUNCTION 是创建已分区表或索引的第一步。 参数：
partition_function_name
是分区函数的名称。分区函数名称在数据库内必须唯一，并且符合标识符的规则。
input_parameter_type 是用于分区的列的数据类型。当用作分区列时，除 text、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)、别名数据类型或 CLR 用户定义数据类型外，所有数据类型均有效。
实际列（也称为分区列）是在 CREATE TABLE 或 CREATE INDEX 语句中指定的。
boundary_value 为使用 partition_function_name 的已分区表或索引的每个分区指定边界值。如果 boundary_value 为空，则分区函数使用 partition_function_name 将整个表或索引映射到单个分区。只能使用 CREATE TABLE 或 CREATE INDEX 语句中指定的一个分区列。
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。
3 CREATE PARTITION SCHEME创建分区方案
语法：CREATE PARTITION SCHEME partition_scheme_name
AS PARTITION partition_function_name
[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ ,...n ] ) [ ; ] 功能：在当前数据库中创建一个将已分区表或已分区索引的分区映射到文件组的方案。已分区表或已分区索引的分区的个数和域在分区函数中确定。必须首先在 CREATE PARTITION FUNCTION 语句中创建分区函数，然后才能创建分区方案。 参数：
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 中指定的分区数，则 CREATE PARTITION SCHEME 将失败，并返回错误。
如果 partition_function_name 生成的分区数少于文件组数，则第一个未分配的文件组将标记为 NEXT USED，并且出现显示命名 NEXT USED 文件组的信息。如果指定了 ALL，则单独的 file_group_name 将为该 partition_function_name 保持它的 NEXT USED 属性。如果在 ALTER PARTITION FUNCTION 语句中创建了一个分区，则 NEXT USED 文件组将再接收一个分区。若要再创建一个未分配的文件组来拥有新的分区，请使用 ALTER PARTITION SCHEME。
在 file_group_name[ 1,...n] 中指定主文件组时，必须像在 [PRIMARY] 中那样分隔 PRIMARY，因为它是关键字。
例：CREATE PARTITION FUNCTION NunberPF(INT)
AS RANGE LEFT FOR VALUES(10,100,1000,10000)
说明：创建一个名称是NumberPF的分区函数，该函数把INT类型的列中的数据分成5个区，即<=10，between 10 and <=100,between 100 and <=1000,between 1000 and <=10000,>10000的5个区。
CREATE PARTITION SCHEME NumberPS AS PARTITION NumberPF
TO(NumFG1, NumFG2, NumFG3, NumFG4, NumFG5,)
说明：对NumberPF分区函数创建一个NumberPS分区方案，5个分区分别放在NumFG1, NumFG2, NumFG3, NumFG4, NumFG5上。其中，使用AS PARTITION子句指定分区函数名称。
分区完成后，则创建分区表。创建分区表需要在CREATE TABLE语句后，加ON关键字指定分区方案名称和分区列。
CREATE TABLE NumPT ( ID INT, Number INT, NDate DATETIME,
NPerson VARCHAR(32), )
ON NumberPF(Number)
第3节 修改表
3.1使用SSMS修改表
在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，展开“表”节点，右键要修改的表，选择“修改”命令，进入表设计器即可进行表的定义的修改。
3.2使用T-SQL语句修改表
格式：ALTER table 表名
(ALTER COLUMN 列名 列定义， ADD 列名1 类型 约束， DROP 列名 „ ）
注意：列定义包括列的数据类型和完整性约束 例子： 1修改属性
例如：把表Book 中PubComp 的类型varchar(28)改为varchar(30)。 USE Library GO
ALTER TABLE Book
ALTER COLUMN PubComp varchar(30) NOT NULL GO 2添加或删除列
例1：为表Book添加ISBN列。
国际标准书号由ISBN冠头，后接以下四段10位数字，每两部分之间以水平线或斜线隔开。如：ISBN 7-115-08612-5
USE Library
GO
ALTER TABLE Book
ADD ISBN varchar(13) NULL GO
例2：为表Reader添加邮件地址。 USE Library GO
ALTER TABLE Reader
ADD E-mail varchar(50) NULL CHECK(E-mail like '%@%') GO
例3：为表Reader删除邮件地址。 USE Library GO
ALTER TABLE Reader DROP COLUMN E-mail GO
说明：必须先删除其上的约束。 3添加或删除约束
例1：为表Borrow添加主键约束（假设还没有创建）。 USE Library GO
ALTER TABLE Borrow
ADD PRIMARY KEY(RID,BID,LendDate) GO
例2：为表Borrow删除主键约束。 USE Library GO
ALTER TABLE Borrow
DROP PRIMARY KEY (RID,BID,LendDate) GO
第4节 删除表
4.1使用SSMS删除表
在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体
数据库节点，展开“表”节点，右键要删除的表，选择“删除”命令或按下“DELETE”键。
4.2使用T-SQL语句删除表
DROP TABLE 表名
例如：先随便在数据库Library中建一个表Test，然后删除。 USE Library GO
DROP TABLE Test
第5节 记录操作
5.1插入记录
5.1.1使用SSMS添加记录
在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，展开“表”节点，右键要插入纪录的表，选择“打开表”命令，即可输入纪录值。
例如：为Library数据库的各表输入数据。 表ReaderType：
表Reader：
表Book：
表Borrow：
5.1.2使用T-SQL语句插入记录
INSERT [INTO]（表名|视图名）[列名表] VALUES（常量表） 1 插入一行所有列的值
USE Library GO
INSERT into Reader
VALUES('2005216001','赵成刚',3,2,'zhchg@sina.com') GO
2 插入一行的部分列
USE Library GO
INSERT Reader(RID,Rname,TypeID)
VALUES('2004060003','李亚茜',3) GO 5.2修改记录
5.2.1使用SSMS修改记录
在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体
数据库节点，展开“表”节点，右键要修改纪录的表，选择“打开表”命令，即可修改纪录值。
5.2.2使用T-SQL语句修改记录
UPDATE 表名 SET 列名1＝表达式, „
列名n＝表达式 where 逻辑表达式
例：把读者类型表ReaderType中学生的限借数量5本增加2本。 USE Library GO
UPDATE ReaderType SET LimitNum=LimitNum +2 WHERE Typename='学生' GO
例如：计算读者中的已借数量。 USE Library GO
UPDATE Reader SET Lendnum =(
SELECT COUNT(*) --从借阅表中统计出每个读者借书的册数 FROM Borrow
WHERE ReturnDate IS NULL AND Reader.RID=Borrow.RID) GO
说明：这里的查询语句SELECT将在第6章详细介绍。 5.3删除记录
5.3.1使用SSMS删除记录
在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，展开“表”节点，右键要修改纪录的表，选择“打开表”命令，右击要删除的行，选择“删除”命令即可删除纪录。 5.3.2使用T-SQL语句删除记录
DELETE 表名 WHERE 逻辑表达式
例如：删除Borrow表中RID为'2005216001'的读者的借书记录 USE Library GO
DELETE Reader
WHERE RID='2005216001' GO
例如：删除test表中的所有记录 USE Library GO
DELETE test
因篇幅问题不能全部显示，请点此查看更多更全内容