《SQLServer配套资料（第3版）》-模块5 表的创建与管理

重庆航天职业技术学院/detail/235897295数据库应用（SQLServer）第5章表的创建与管理能根据项目逻辑设计使用SSMS和T-SQL创建表

。能根据项目逻辑设计中完整性规则使用SSMS和T-SQL设置表的主键、外键和约束等

。能根据项目逻辑设计创建并管理关系图

。第5章表的创建与管理任务设计：5.1认识表5.2使用SSMS创建、修改和删除表5.3使用T-SQL语句创建、修改和删除表5.4数据表中添加、修改、删除数据行5.5创建索引5.6创建关系图5.7任务训练第5章表的创建与管理5.1任务1：创建数据库理解SQLServer2019数据表的基本概念1熟练掌握表的设计步骤

2任务目标：5.1任务1：创建数据库在为一个数据库设计表之前，应该完成需求分析，确定概念模型，将概念模型转换为关系模型，关系模型中的每一个关系对应数据库的一个表。5.1.1表的构成列：每一列用来保存关系的属性，也称为字段。行：每一行用来保存关系的元组，也称为数据行或记录，是数据对象的一个实例。例如：教务管理系统EDUC中的学生Student表，如图5-2所示。表是数据库对象，用于存储实体集和实体间联系的数据。SQLServer2019表主要由行和列构成。5.1.2表的类型

1.分区表当表很大时，可以水平地把数据分割成一些单元，放在同一个数据库的多个文件组中。用户可以通过分区快速地访问和管理数据的某部分子集而不是整个数据表，从而便于管理表和索引。2.临时表临时表是临时建立的、不能永久存在的表。分为局部临时表和全局临时表。局部临时表只是对一个数据库实例的一次连接中的创建者是可见的。在用户断开数据库的连接时，局部临时表就被删除。全局临时表对所有的用户和连接都是可见的，并且只有所有的用户都断开临时表相关的表时，全局临时表才会被删除。3.系统表系统表用来用来保存一些服务器配置信息数据，用户不能直接查看和修改系统表，只有通过专门的管理员连接才能查看和修改。不同版本的数据库系统的系统表一般不同，在升级数据库系统时，一些应用系统表的应用可能需要重新改写。5.1.3表的数据类型提示：char(或nchar)用于保存单据票号、身份证号码这类固定（或者比较固定）长度的数据，如果保存的数据长度小于定义的长度，则会在保存的数据后面填充空格。所以它始终占用与字段定义长度相同的存储空间。varchar(或nvarchar)用于保存公司名称、地址信息这类长度不固定的数据，它占用的存储空间根据实际保存的数据长度确定。text（或ntext）用于保存前面四种数据类型无法存储的数据。由于它的数据一般不存储在数据行中，在数据处理和检索时，要从另外的地址读取数据，所以它的效率在几种字符类型中是最低的。使用date数据类型只存储一个日期，或者time数据类型只存储一个时间值。在新的数据类型中时间部分现在支持的精度可以达到100纳秒。如果有需要存储日期需要与SQLServer的时区保持一致，可以使用DATETIMEOFFSET数据类型。SQLServer2019的数据类型很丰富，表5-2列出了常用的数据类型。（详见P106）5.1.4表的完整性体现

1.实体完整性实体完整性将行定义为特定表的唯一实体。可以通过设定主键（PrimaryKey）、单值约束（Unique）、唯一索引（UniqueIndex）、标识列（IdentityColumn）和非空值约束（NOTNULL）等多种方法实现。2.域完整性域完整性要求表的数据在某一特定的允许范围内。可以使用数据类型（DataType）、默认值（Default）、检查（Check）、外键（ForeignKey）、空或非空（NULL/NOTNULL）等多种约束方法实现。3.参照完整性参照完整性是用来维护相关数据表之间数据一致性的手段。可以使用外键（ForeignKey）、检查（Check）、触发器（Trigger）和存储过程（StoredProcedure）来实现。4.用户自定义完整性用户自定义完整性由用户根据实际应用中的需要自行定义。可以创建表时使用约束（Constraint）、触发器（Trigger）、存储过程（StoredProcedure）和用户定义函数（Function）来实现。5.1.5表的设计

设计表时需要确定如下内容：表中需要那些列，每一列是何种数据类型。那些列作为主键。那些列作为外键。那些列可以为空。是否需要在列上使用约束、默认值和规则。需要使用什么样的索引。5.2任务2：使用SSMS创建、修改和删除表理解创建表的数据模型1熟练掌握SSMS创建、修改和删除表的方法2任务目标：5.2任务2：使用SSMS创建、修改和删除表在使用SSMS进行数据表的创建之前，需要确定关系模型，关系模型中的每一个关系对应数据库的一个表。使用SSMS方式时，以教务管理系统“EDUC”学生选课子系统为例介绍创建表的方法。其数据模型如下：5.2任务2：使用SSMS创建、修改和删除表创建主键约束创建唯一性约束

创建外键约束创建检查约束

保存表的定义创建表5.2.1创建表1.定义表的列【例5-1】在教务管理数据库“EDUC”中创建学生表“Student”、课程表“Course”和选课表“SC”。(详见P110)5.2.1创建表2.创建主键约束【例5-2】创建表“Student”中的“SID”、表“Course”中的“CID”、表“SC”中的“SID，CID”为表的主键。(详见P111)5.2.1创建表3．创建唯一性约束【例5-3】假设在表“Course”中不允许有相同的课程名，则可为列“Cname”创建唯一性约束。（详见Ｐ112）5.2.1创建表4.创建外键约束【例5-4】将外键表“SC”中的“SID”和“CID”列设置为外键。其中“SID（学号）”参照主键表“Student”中的主键“SID（学号）”，以确保在表“SC”中“SID”列中输入的值与表“Student”中的主键“SID”列中的值匹配。其中“CID（课程号）”参照主键表“Course”中的主键“CID（课程号）”，以确保在表“SC”中“CID”列中输入的值与表“Course”中的主键“CID”列中的值匹配。（详见Ｐ112）5.2.1创建表5.创建CHECK（检查）约束【例5-5】为表“Student”中的“Sex（性别）”列设置CHECK约束，以确保在此列上输入数据的正确性。（详见Ｐ112）5.2.1创建表6．定义标识列用标识列可以实现数据实体完整性。SQLServer中的标识列也可称标识符列，习惯上又叫自增列，该列具有以下3种特点。列的数据类型为不带小数的数值类型。在进行插入数据操作时，该列的值由系统按一定规律生成，不允许空值。列值不重复，具有唯一标识表中一行的作用，从而可以实现表的实体完整性。创建一个标识列，要指定以下4个内容。指定数据类型。设置是标识。设置标识种子，指表中第一行的值，默认值为1。设置标识增量，表示相邻两个标识值之间的增量，默认值为1。（详见Ｐ112）5.2.2修改表在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，然后展开“表”节点，用右键单击要修改的表，从弹出的菜单中选择“修改”命令，进入【表设计器】，即可进行表定义的修改，其方法同创建表。5.2.3删除表

有时需要删除表，如要实现新的设计或释放数据库空间。删除表时，表的结构定义、数据、全文索引、约束和索引约束都永久地从数据库中删除。

在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，然后展开“表”节点，用右键单击要删除的表，从弹出的菜单中选择“删除”命令或按“Delete”键。提示：如果要删除通过外键和主键约束相关的外键表和主键表，则必须首先删除外键表。如果要删除外键约束中引用的主键表而不删除外键表，则必须删除外键表的外键约束。5.2任务3：使用T-SQL语句创建、修改和删除表理解创建表的数据模型1熟练掌握T-SQL创建、修改和删除表的方法2任务目标：5.3.1创建表1．使用CREATETABLE命令创建表其语法格式如下：CREATETABLE表名（列名1数据类型列级完整性约束，列名2数据类型列级完整性约束，……列名n数据类型列级完整性约束，表级完整性约束1，……表级完整性约束n）功能：CREATETABLE命令为表定义各列的名字、数据类型和完整性约束。5.3.1创建表其中，列级完整性约束如下：DEFAULT常量表达式：默认值约束。NULL/NOTNULL：空值/非空值约束。UNIQUE：单值约束。PRIMARYKEY：主键约束，等价非空和单值约束。REFERENCES父表名（主键）：外键约束。CHECK（逻辑表达式）：检查约束。其中，表级完整性约束如下：UNIQUE（列名1，列名2，……列名n）：多个列名单值约束。PRIMARYKEY（列名1，列名2，……列名n）：多个列名组合主键约束。FOREIGNKEY（外键）REFERENCES主键表（主键）：多个列名组合外键约束。CHECK（逻辑表达式）：含有多个列名逻辑表达式的检查约束。5.3.1创建表2．应用举例【例5-7】图书管理系统的数据模型如下，创建其相应的表及其约束。在图书管理系统的数据库“Library”中，创建读者表“Reader”、读者类型表“ReaderType”、图书表“Book”和借阅表“Borrow”。5.3.1创建表2．应用举例【例5-7】图书管理系统的数据模型如下，创建其相应的表及其约束。在图书管理系统的数据库“Library”中，创建读者表“Reader”、读者类型表“ReaderType”、图书表“Book”和借阅表“Borrow”。5.3.1创建表在SSMS窗口中，单击工具栏中的“新建查询”按钮，分别在【查询编辑器】中输入以下代码。之后单击“SQL编辑器”工具栏中的“执行”按钮。（1）创建读者类型表“ReadType”USELibraryGOCREATETABLEReaderType--读者类型表(TypeIDintnotNUllPRIMARYKEY,--读者类型编号，主键Typenamechar(8)NULL,--读者类型名称LimitNumintNUll,--限借数量LimitDaysintNUll --限借天数)5.3.1创建表（2）创建读者表“Reader”USELibraryGOCREATETABLEReader--读者表(RIDchar(10)notNUllPRIMARYKEY,--读者编号，主键Rnamechar(8)NULL,--读者姓名TypeIDintNUll,--读者类型LendnumintNUll,--已借数量Foreignkey(TypeID)referencesReaderType(TypeID)ondeletenoaction--外键、不级联删除 )5.3.1创建表（3）创建图书表“Book”USELibraryGOCREATETABLEBook--图书表(BIDchar(9)notNUllPRIMARYKEY,---图书编号，主键Bnamevarchar(42)NULL,--书名Authorvarchar(20)NUll,--作者PubCompvarchar(28)NUll,--出版社PubdatedatetimeNull,--出版日期Pricedecimal(7,2)Nullcheck(Price>0),--价格，检查约束Classchar(10)NULL--所属类别)5.3.1创建表(4)创建图书借阅表“Borrow”USELibraryGOCREATETABLEBorrow--借阅表(RIDchar(10)notNUll--设置外键，级联删除Foreignkeyreferencesreader(RID)ondeletecascade,BIDchar(9)notnull-设置外键，不级联删除ForeignkeyreferencesBook(BID)ondeletenoaction,LendDatedatetime2（0）notnulldefault(getdate()),--借书日期，默认当前日期ReturnDatedatetime2（0）null,--还书日期PrimaryKey(RID,BID,LendDate)--设置组合键为主键)5.3.2修改表使用ALTERTABLE修改表定义，其语法格式如下：ALTERTABLE表名（ALTERCOLUMN列名列定义，ADD列名数据类型约束，…DROP列名，…ADDCONSTRAINT约束名约束，…DROPCONSTRAINT约束名，…）ALTERTABLE语句的各选项说明如下：ALTERCOLUMN列名列定义：修改列定义。ADD列名数据类型约束：增加新的列。DROP列名：删除已有的列。ADDCONSTRAINT约束名约束：添加约束。DROPCONSTRAINT约束名：删除已有的约束。其中，列定义包括列的数据类型和完整性约束。5.3.2修改表1．修改属性【例5-8】把表“Book”中“Bname”的类型“varchar(42)”改为“varchar(40)”。USELibraryGOaltertablebookaltercolumnBnamevarchar(40)notnullGO5.3.2修改表2．添加或删除列【例5-9】为表“Reader”新增字段邮件地址。USELibraryGOaltertableReaderaddE_mailvarchar(50)nullcheck(E_maillike'%@%')GO【例5-10】为表“Book”新增字段“ISBN”。USELibraryGOaltertableBookaddISBNvarchar(17)nullGO【例5-11】删除表“Book”中列“ISBN”(删除后可再创建)。USELibraryGOaltertableBookdropcolumnISBNGO5.3.2修改表3．添加或删除约束【例5-12】为表“Borrow”添加主键约束。USELibraryGOaltertableBorrowaddconstraintPK_BHprimarykey(RID,BID,LendDate)GO【例5-13】为表“Borrow”删除主键约束（先删除原创建时的主键约束）。USELibraryGOaltertableborrowdropconstraintPK_BHGO

5.3.2修改表3．添加或删除约束【例5-14】为表“Borrow”添加外键“RID”约束（先删除原创建时的外键约束）。USELibraryGOaltertableborrowaddconstraintFK_borrowforeignkey(RID)referencesReader(RID)GO【例5-15】为表“Book”添加价格大于零（Price>0）的检查约束。USELibraryGOaltertablebookaddconstraintck_book_prcheck(price>0)GO5.3.2修改表提示：定义check约束后，可以使用NOCHECKCONSTRAINT和CHECKCONSTRAINT子句是的CHECK约束失效和重新生效，详见下面代码：以下语句使上例CHECK约束失效：uselibraryaltertablebooknocheckconstraintck_book_pr以下语句使上例CHECK约束重新生效：uselibraryaltertablebookcheckconstraintck_book_pr5.3.3删除表使用DROPTABLE命令删除表，其语法格式如下：DROPTABLE表名【例5-16】在数据库中“Library”中创建一个表“test”，然后删除。USELibraryGODROPTabletest5.4任务4：数据表中添加、修改、删除数据行熟练掌握SSMS添加、修改和删除数据行的方法1熟练掌握T-SQL语句添加、修改和删除数据行的方法

2任务目标：5.4.1使用SSMS添加、修改、删除数据行

1．添加数据行在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，然后展开“表”节点，用右键单击要添加数据行的表，从弹出的菜单中选择“编辑前200行”命令，即可在编辑状态下添加数据行。【例5-17】使用SSMS为“EDUC”数据库的各表添加数据行，如图5-22、图5-23和图5-24所示。在【对象资源管理器】窗口中，展开“数据库”—“EDUC”—“表”节点，分别用右键单击表“Student”、“Course”和“SC”节点，从弹出的菜单中选择“编辑前200行”命令，即可在编辑状态下添加数据行。5.4.1使用SSMS添加、修改、删除数据行

5.4.1使用SSMS添加、修改、删除数据行

2．修改数据行当添加数据发生错误或者事物发生变化时，数据需要进行修改。在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，然后展开“表”节点，用右键单击要更新数据的表，在弹出的菜单中选择“编辑前200行”命令，即可更新数据。3．删除数据行如果表中的数据不再需要，可以将其删除以释放存储空间。在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，然后展开其中的“表”节点，用右键单击要删除的数据行的表，选择“编辑前200行”命令，再用右键单击要删除的行，选择快捷菜单中的“删除”命令即可删除数据行。5.4.1使用T-SQL添加、修改、删除数据行

1．添加数据行（1）使用INSERT…VALUES语句语法格式如下：INSERT[INTO]表名|视图名[(列表名)]VALUES（常量表）功能：使用包含VALUES子句的INSERT语句可以把数据行添加到表中。①插入一行所有列的值【例5-18】为表“ReadType”添加数据行。表中数据如图5-25所示。USELibraryGOinsertintoReaderTypeValues(1,'教师',10,90)insertintoReaderTypeValues(2,'职员',5,60)insertintoReaderTypeValues(3,'学生',3,30)GO5.4.1使用T-SQL添加、修改、删除数据行

②插入一行部分列【例5-19】为表“Reader”添加读者“李茜”的部分信息，可以用同样的语句添加其它数据行。执行以下代码，表中数据如图5-26所示。USELibraryGOinsertReader(RID,Rname,TypeID,E_mail)Values('2001030002','李茜',2,'liqian@126.com')GO5.4.1使用T-SQL添加、修改、删除数据行1．添加数据行（2）使用INSERT…SELECT语句语法格式如下：INSERT表名SELECT查询语句功能：SELECT查询语句用于指定输入表的值，通过SELECT查询语句生成结果集，并将其添加到INSERT后指定的表中。【例5-20】使用INSERT……SELECT查询语句将图书表“Book”中计算机类的图书添加到新建的表“BookTest”中。5.4.1使用T-SQL添加、修改、删除数据行（1）在数据库“Library”中新建表“BookTest”。USELibraryGOCREATETABLEBookTest(BIDchar(9)notNUllPRIMARYKEY,Bnamevarchar(42)NULL,Authorvarchar(20)NUll,PubCompvarchar(28)NUll,PubdatedatetimeNull,Pricedecimal(7,2)Nullcheck(Price>0),ISBNvarchar(17)NULL,Classchar(10)NULL)GO（2）将图书表“Book”中计算机类的图书添加到新建的“BookTest”表中。insertBookTestselectBID,Bname,Author,Pubcomp,Pubdate,Price,ISBN,ClassfromBookwhereClass='教育类'5.4.1使用T-SQL添加、修改、删除数据行2．修改数据行使用UPDATESET命令修改表中的数据，其语法格式如下：UPDATE表名SET列名1=表达式1,…，列名n=表达式n[WHERE逻辑表达式]功能：对于UPDATE所指定的表，当满足WHERE子句后的条件时，SET子句为指定的列名附上“=”后表达式的值。5.4.1使用T-SQL添加、修改、删除数据行2．修改数据行【例5-21】将读者类型表“ReaderType”中学生的原借数量和原限借天数分别增加2本和5天。USELibraryGOupdatereadertypesetLimitNum=LimitNum+2,LimitDays=LimitDays+5wheretypename='学生'

执行以上语句，打开“ReaderType”表，可以看到表中学生的限借数量由3更新为5，限借天数由30更新为35，如图5-30所示。5.4.1使用T-SQL添加、修改、删除数据行2．修改数据行【例5-22】将读者类型表“ReaderType”中所有数据行的限借天数都增加10天。USELibraryGOupdatereadertypesetLimitDays=LimitDays+10

执行以上语句，打开“ReaderType”表，可以看到表中所有数据行的限借天数都增加了10，如图5-31所示。5.4.1使用T-SQL添加、修改、删除数据行2．修改数据行【例5-23】统计读者表中的已借书数量。USELibraryGOupdateReadersetLendnum=(selectcount(*)fromborrowwherereturnDateisnullANDReader.rid=borrow.rid)GO5.4.1使用T-SQL添加、修改、删除数据行3．删除数据行使用DELETE命令删除数据行，其语法格式如下：DELETE表名[WHERE逻辑表达式]功能：删除表中符合WHERE子句指定条件的数据行【例5-24】删除表“BookTest”中图书编号为“G448-01”的图书信息。

USELibraryGOdeleteBookTestwhereBID='G448-01'

【例5-25】删除表“BookTest”中的所有数据行。USELibraryGODeleteBookTest5.5任务5创建索引理解索引的基本概念

1熟练掌握SSMS、T-SQL创建和删除索引的方法2任务目标：5.5.1索引概述

用户对数据库最频繁的操作是进行数据查询。一般情况下，数据库在进行查询操作时需要对整个表进行数据搜索。当表中的数据很多时，搜索数据就需要很长时间造成服务器的资源浪费。为了提高检索数据的能力，数据库引入了索引机制。1．索引的基本概念表是用来存储数据和操作数据的逻辑结构，而索引是有效组织表数据的方式，它是由一列或多列按照一定的排列顺序组成。与书的索引类似，数据库中的索引可以使用户快速地找到表的信息。一方面，用户可以通过合理地创建索引提高数据库的查找速度，另一方面，索引也可以保证列的唯一性，从而确保表中的数据的完整性。5.5.1索引概述

2．索引的类型SQLSever提供了两种形式的索引：聚集索引（ClusteredIndex）和非聚集索引（NonclusteredIndex）。聚集索引根据键值对行进行排序，所以每个表只能有一个聚集索引。非聚集索引不根据键值对行进行排序，索引数据结构与数据行是分开的，其查找速度明显低于带聚集索引的表。5.5.1索引概述

3．创建索引原则索引的建立有利也有弊，建立索引可以提高查询速度，很过多建立索引会占很多的磁盘空间。所以在建立索引时，必须权衡利弊。一般在下列情况下适合建立索引：经常被查询搜索的列，如经常出现在WHERE子句的逻辑表达式中的列。在ODERBY子句中使用的列。外键或主键列。值唯一的列。而在下列情况下不适合建立索引。在查询中很少被引用的列。包含太多重复值的列。数据类型为bit、text、image等的列不能建立索引。5.5.2使用SSMS创建索引

1．使用SSMS创建索引【例5-26】在数据库“EDUC”中为学生表“Student”创建一个聚集的、唯一的复合索引“Studentindex”。索引键为列组合“SID”和“Sname”，均为升序排列。（1）在【对象资源管理器】窗口中，展开“数据库”节点，再展开具体数据库节点，然后展开具体的“表”节点，用右键单击其中的“索引”节点，从弹出的菜单中选择“新建索引”命令，如图5-35所示。（2）在打开的【新建索引】窗口中输入索引名，如“Studentindex”，在设置索引类型，如图5-36所示。（3）单击“添加”按钮，在打开的【从“dbo.Student”中选择列】窗口中选中要建索引的列，如|“SID”和“Sname”，如图5-37所示。完成后单击“确定”按钮，返回【新建索引】窗口。（4）在【新建索引】窗口中设置索引的排序顺序等，如图5-38所示。（5）单击“确定”按钮，完成索引设置。5.5.2使用SSMS创建索引

1．使用SSMS创建索引【例5-26】在数据库“EDUC”中为学生表“Student”创建一个聚集的、唯一的复合索引“Studentindex”。索引键为列组合“SID”和“Sname”，均为升序排列。（详见P128）（1）在【对象资源管理器】窗口中，展开“数据库”节点，再展开具体数据库节点，然后展开具体的“表”节点，用右键单击其中的“索引”节点，从弹出的菜单中选择“新建索引”命令，如图5-35所示。（2）在打开的【新建索引】窗口中输入索引名，如“Studentindex”，在设置索引类型，如图5-36所示。（3）单击“添加”按钮，在打开的【从“dbo.Student”中选择列】窗口中选中要建索引的列，如|“SID”和“Sname”，如图5-37所示。完成后单击“确定”按钮，返回【新建索引】窗口。（4）在【新建索引】窗口中设置索引的排序顺序等，如图5-38所示。（5）单击“确定”按钮，完成索引设置。5.5.2使用SSMS创建索引

2．使用SSMS删除索引在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，然后展开“表”节点下的“索引”节点，用右键单击要删除的索引，从弹出的菜单中选择“删除”命令或按Delete键即可删除索引，如图5-39所示。（详见P130）5.5.3使用T-SQL创建索引

1.使用T-SQL创建索引使用CREATEINDEXON命令创建索引，其语法格式如下：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEX索引名ON<表名或视图名>（列名[ASC|DESC][,…,n]）[WITH<索引项>|[,][FILLFACTOR=填充因子]|[,][DROP_EXISTING={ON|OFF}]…]选项说明UNIQUE指定创建的索引为唯一索引CLUSTERED|NONCLUSTERED指定创建的是聚集索引还是非聚集索引ASC|DESC确定某个具体的索引是升序还是降序，默认是升序（ASC）<索引项>可指定填充索引的内如节点行数、填充因子等。此处不详细介绍，可查看联机帮助DROP-EXISTING指定是否删除先前存在的并且与创建索引同名的索引5.5.3使用T-SQL创建索引

【例5-27】在数据库“EDUC”中为学生表“Student”创建一个不唯一、非聚集索引“Studentindex2”，索引键为“Sname”，升序排列。在SSMS窗口中，单击工具栏中的“新建查询”按钮，在【查询编辑器】中输入以下代码。USEEDUCGOcreatenonclusteredindexstudentindex2onstudent(SnameASC)

单击“SQL编辑器”工具栏中的“执行”按钮，运行成功