第3章 表的创建和管理.docx

1、第3章 表的创建和管理第3章 SQL Server 2005表的创建和管理第1节 SQL Server 2005表的基本知识1.1 什么是表？关系数据库中，每一个关系就是一张表。表是关系模型中表示实体的方式，是用来组织和存储数据、具有行列结构的数据库对象。在为一个数据库设计表之前，应该完成需求分析，确定概念模型，将概念模型转换为关系模型，关系模型中的每一个关系对应数据库中的一个表。1.2 表的特点表具有下列一些基本特点：代表实体、由行和列组成、列名在一个表中是唯一的、行和列的顺序是不重要等。代表实体，有唯一的名称来确定实体。表由行和列组成，行也叫记录，元组。每一行都是对某个实体完成的描述。例如

2、在上面的学生表中，SID是2005216006这行记录就表示了郝莎同学的完整信息。一条记录就是该实体的一个实例。表中的每个列，也叫字段，都是对该实体的某种属性的描述。如birthday字段描述了所有同学的生日。表中行和列的顺序是任意的。列名在一个表中是唯一的。即在一个表中不能有名称相同的两个或两个以上的列；但在同一个数据库中的不同表中，可以使用相同的列名。列名在一个表中的唯一性是SQL Server 2005强制实现的；而行在一个表中的唯一性是由主键约束来强制实现的。即不能存在相同的主键，这意味着两个完全相同的实体，区别不开。1.3 表的类型SQL Server 2005把表分为：普通表、分区

3、表、临时表和系统表。普通表是基本表，其他类型的表都是有特殊用途的表，是在特殊应用环境下，为了提高系统的使用效率而派生的表。1. 普通表即标准表。就是数据库中存储数据的表。最常用，最基本的表。2. 分区表原理：当表很大时，可以水平地把数据分割成一些单元，放在同一个数据库的多个文件组中，从而实现对单元中数据的并行访问，提高效率。应用环境：当表的数据很大，而且这些数据经常被不同的使用方式访问，则应当建立分区表。优点是可通过分区快速地访问和管理数据的某部分子集而不是整个数据表，从而便于管理大表和索引；可并行访问数据。3. 临时表概念：临时创建，不能永久存在的表。两种临时表：本地(局部)临时表和全局临时

4、表。本地临时表只是对一个数据库实例的一次连接中的创建者是可见的只对创建者可见。在用户断开数据库的连接时，本地临时表就被删除。全局临时表创建后对所有的用户和连接都是可见的，并且只有所有的用户都断开临时表相关的表时，全局临时表才会被删除。4. 系统表概念：系统表用来保存一些服务器配置、数据库设置、用户和表对象的描述等系统信息。特点：用户不能直接操作系统表，只有DBA才能使用。1.4 表的完整性体现 主键约束体现实体完整性，即主键各列不能为空且主键作为行的唯一标识，主键作为实体的唯一标识。 外键约束体现参照完整性 默认值和规则等体现用户定义的完整性1.5 表的数据类型数据类型系统数据类型应用说明二进

5、制存储二进制数据image图像、视频、音乐Binary(n)标记或标记组合数据,n的范围：18000，n二进制位数varbinary(n)同上（变长）精确数字精确整数bigint长整数-263263-1int整数-231231-1smallint短整数-215215-1tinyint更小的整数0255,每个值仅占一个字节精确小数Decimal(p,s)小数，p: 精度，最大数字位数,取值：138,默认18，则decimal数据类型的取值范围：-1038+1-1038-1。s: 最大小数位数。如decimal(10,2)表示可存储精度为10、小数位数是2的数据。numeric(p,s)同上近似数

6、字float(n)浮点1.79E3081.79E308real3.40E383.40E38字符char(n)定长字符型，用户自定义长度n，最长可达8000字符。若实际字符少于n，则空格填充;若多于，则截断。varchar(n)变长字符型，字符长度可达8000字符，varchar类型的存储空间，岁存储在表列中的每一个数据的字符数的不同而变化。降低存储空间浪费text变长文本型，存储字符长度大于8000的变长字符Unicode当数据库中存储的数据可能涉及多种语言时使用该类型nchar(n)unicode字符（双倍空间）nvarchar(n)unicode字符（双倍空间）ntextunicode字符

7、（双倍空间）日期和时间Datetime1753-1-19999-12-31（12:00:00）smalldatetime1900-1-12079-6-6货币Money-263263-1（保留小数点后四位）smallmoney-231231-1（保留小数点后四位）特殊bit0/1，判定真或假Timestamp自动生成的惟一的二进制数，修改该行时随之修改，反应修改记录的时间cursor只用于与定义游标和使用游标有关的语句中xml用来保存整个XML文档。table类似临时表，可用于存储结果集。只用在定义类型为table的局部变量和用户定义函数的返回值uniqueidentifier全局惟一标识（GU

8、ID），十六进制数字，由网卡/处理器ID以及时间信息产生，用法同上用户自定义用户自行命名用户可创建自定义的数据类型1.6 表的设计设计表时需要确定如下内容： 表中需要的列以及每一列的类型（必要时还要有长度） 列是否可以为空 是否需要在列上使用约束、默认值和规则 需要使用什么样的索引 哪些列作为主键第2 节 创建表在Microsoft SQL Server 2005系统中，可用CREATE TABLE语句创建表，也可用可视化的SQL Server Management Studio图形工具创建表。2.1使用SSMS创建表在【对象资源管理器】窗口展开“数据库”节点展开所选择的具体数据库节点右击“表

9、”节点选择“新建表”命令进入表设计器进行表的定义。例如：在山大分校教务管理WHEDUC中的选课数据库中创建学生表Student, 课程表Course, 选课表SC。主键PRIMARY KEY 外键FOREIGN KEY候选键CANDICATE KEY唯一性 UNIQUE 教务管理中的选课数据模型为：Student(SID,Sname,Sex,Birthday,Specialty) PK:SIDCourse(CID, Cname,Credit）PK:CIDSC(SID, CID,Grade) PK:SID, CID FK:SID和CID(1)在【对象资源管理器】窗口中，展开“数据库”下的WHED

10、UC节点，右击“表”节点，选择“新建表”命令，进入表设计器，在表设计器的第一列中输入列名，第二列选择数据类型，第三列选择是否为空；例如：表Student：表Course：表SC：2.2 使用T-SQL语句创建表格式：CREATE TABLE表名（列名1 数据类型 列级完整性约束，列名 2 数据类型 列级完整性约束， 列名n 类型 约束，表级完整性约束，）；约束：实现表的完整性 DEFAULT 常量表达式：默认值约束。 NULL/NOT NULL：空值/非空值约束。 UNIQUE：单值约束。 PRIMARY KEY：主键约束，等价非空、单值。 REFERENCES 父表名 (主键)：外键约束。C

11、HECK (逻辑表达式)：检查约束。创建表的语法格式有很多选项，非常复杂，可以通过例子和联机帮助逐步了解。例如：在图书管理系统中的数据库（Library）中，创建读者表（Reader）, 读者类型表（ReaderType），图书表（Book）和借阅表（Borrow）。图书管理系统的数据模型：ReaderType(TypeID, Typename,LimitNum, LimitDays)PK: TypeIDReader (RID,Rname,TypeID,Lendnum)PK：RID FK：TypeIDBook (BID,Bname,Author, PubComp, PubDate,Price)

12、PK：BIDBorrow (RID, BID,LendDate, ReturnDate) PK：RID, BID, LendDate FK：RID和BID(2)创建读者类型表ReaderTypeCREATE TABLE ReaderType(TypeID int NOT NULL primary key,-类型编号,主键Typename char(8) NULL,-类型名称LimitNum int NULL,-限借数量LimitDays int NULL -借阅期限)(3)创建读者表ReaderUSE LibraryGOCREATE TABLE Reader(RID char(10) NOT

13、NULL PRIMARY KEY,-读者编号，主键Rname char(8) NULL,-读者姓名TypeID int NULL,-读者类型Lendnum int NULL ,-已借数量FOREIGN KEY(TypeID) REFERENCES ReaderType(TypeID) ON DELETE NO ACTION, -外键，不级联删除)(4)创建图书表BookUSE LibraryGOCREATE TABLE Book(BID char(9) PRIMARY KEY,-图书编号，主键Bname varchar(42) NULL,-图书书名Author varchar(20) NULL

14、,-作者PubComp varchar(28) NULL,-出版社PubDate datetime NULL,-出版日期Price decimal(7,2) NULL CHECK (Price0)-定价，检查约束)(5)创建图书借阅表Borrow (RID, BID,LendDate, ReturnDate)USE LibraryGOCREATE TABLE Borrow(RID char(10) NOT NULL -读者编号外键FOREIGN KEY REFERENCES Reader(RID) ON DELETE CASCADE, /*删除主表记录时不级联删除子表相应记录*/BID cha

15、r(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 产品

16、总汇(产品名称 nvarchar(20) NOT NULL,数量 int NOT NULL,单价 decimal(10,2) NOT NULL,单一产品总价值 AS 数量*单价)说明：在单一产品总价值列中没有指定数据类型，其值是数量*单价。AS为关键字。一般该列为虚拟列，不物理存储。如要将该列物理存储，如下：CREATE TABLE 产品总汇1(产品名称 nvarchar(20) NOT NULL,数量 int NOT NULL,单价 decimal(10,2) NOT NULL,单一产品总价值 AS 数量*单价 PERSISTED)2.3.2 创建临时表若创建临时表，在表名前加“”或“”。“

17、”表示创建的是本地临时表，只能由创建者使用。“”表示创建的是全局临时表，该表在生存周期内由所有用户使用。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 parti

18、tion_function_name ( input_parameter_type )AS RANGE LEFT | RIGHT FOR VALUES ( boundary_value ,.n ) ; 功能：在当前数据库中创建一个函数，该函数可根据指定列的值将表或索引的各行映射到分区。使用 CREATE PARTITION FUNCTION 是创建已分区表或索引的第一步。参数：partition_function_name 是分区函数的名称。分区函数名称在数据库内必须唯一，并且符合标识符的规则。input_parameter_type 是用于分区的列的数据类型。当用作分区列时，除 text、n

19、text、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 或 CR

20、EATE INDEX 语句中指定的一个分区列。boundary_value 是可以引用变量的常量表达式。这包括用户定义类型变量，或函数以及用户定义函数。它不能引用 Transact-SQL 表达式。boundary_value 必须与 input_parameter_type 中提供的数据类型相匹配或者可隐式转换为该数据类型，并且如果该值的大小和小数位数与 input_parameter_type 中相应的值的大小和小数位数不匹配，则在隐式转换过程中该值不能被截断。.n 指定 boundary_value 提供的值的数目，不能超过 999。所创建的分区数等于 n + 1。不必按顺序列出各值。如

21、果值未按顺序列出，则数据库引擎将对它们进行排序、创建函数并返回一个警告，说明未按顺序提供值。如果 n 包括任何重复的值，则数据库引擎将返回错误。LEFT | RIGHT指定当间隔值由 数据库引擎 按升序从左到右排序时，boundary_value ,.n 属于每个边界值间隔的哪一侧（左侧还是右侧）。如果未指定，则默认值为 LEFT。3 CREATE PARTITION SCHEME创建分区方案语法：CREATE PARTITION SCHEME partition_scheme_nameAS PARTITION partition_function_name ALL TO ( file_gro

22、up_name | PRIMARY ,.n ) ; 功能：在当前数据库中创建一个将已分区表或已分区索引的分区映射到文件组的方案。已分区表或已分区索引的分区的个数和域在分区函数中确定。必须首先在 CREATE PARTITION FUNCTION 语句中创建分区函数，然后才能创建分区方案。参数：partition_scheme_name 分区方案的名称。分区方案名称在数据库中必须是唯一的，并且符合标识符规则。partition_function_name 使用分区方案的分区函数的名称。分区函数所创建的分区将映射到在分区方案中指定的文件组。partition_function_name 必须已经存

23、在于数据库中。单个分区不能同时包含 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

24、_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_f

25、unction_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)说明：创建

26、一个名称是NumberPF的分区函数，该函数把INT类型的列中的数据分成5个区，即=10，between 10 and =100,between 100 and =1000,between 1000 and 10000的5个区。CREATE PARTITION SCHEME NumberPSAS PARTITION NumberPFTO(NumFG1, NumFG2, NumFG3, NumFG4, NumFG5,)说明：对NumberPF分区函数创建一个NumberPS分区方案，5个分区分别放在NumFG1, NumFG2, NumFG3, NumFG4, NumFG5上。其中，使用AS P

27、ARTITION子句指定分区函数名称。分区完成后，则创建分区表。创建分区表需要在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 表名

28、(ALTER COLUMN 列名 列定义，ADD 列名1 类型 约束，DROP 列名）注意：列定义包括列的数据类型和完整性约束例子：1修改属性例如：把表Book 中PubComp 的类型varchar(28)改为varchar(30)。USE LibraryGOALTER TABLE Book ALTER COLUMN PubComp varchar(30) NOT NULLGO2添加或删除列例1：为表Book添加ISBN列。国际标准书号由ISBN冠头，后接以下四段10位数字，每两部分之间以水平线或斜线隔开。如：ISBN 7-115-08612-5USE LibraryGOALTER TABL

29、E Book ADD ISBN varchar(13) NULLGO例2：为表Reader添加邮件地址。USE LibraryGOALTER TABLE Reader ADD E-mail varchar(50) NULL CHECK(E-mail like %)GO例3：为表Reader删除邮件地址。USE LibraryGOALTER TABLE Reader DROP COLUMN E-mail GO说明：必须先删除其上的约束。3添加或删除约束例1：为表Borrow添加主键约束（假设还没有创建）。USE LibraryGOALTER TABLE Borrow ADD PRIMARY KE

30、Y(RID,BID,LendDate) GO例2：为表Borrow删除主键约束。USE LibraryGOALTER TABLE Borrow DROP PRIMARY KEY (RID,BID,LendDate)GO第4节 删除表4.1使用SSMS删除表在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，展开“表”节点，右键要删除的表，选择“删除”命令或按下“DELETE”键。4.2使用T-SQL语句删除表DROP TABLE 表名例如：先随便在数据库Library中建一个表Test，然后删除。USE LibraryGODROP TABLE Test第5节 记录操作 5.1插入记录5.1.1使用SSMS添加记录在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，展开“表”节点，右键要插入纪录的表，选择