《数据库技术与应用》课件-项目3：数据表与数据完整性

认知数据表课程简介2通过本课程的学习，掌握数据表的基本概念、基本组成、列（字段）数据类型、列（字段）的属性及数据完整性与约束。主要学习内容：一．数据表的基本概念二．数据表的基本组成三．列（字段）数据类型四．列（字段）的属性数据表的基本概念3在数据库设计过程中，关系模型中的每一个关系对应数据库中的一个基本表（简称基表或表），SQLServer提供对表的定义和操作方法是对关系模型的具体实现。在SQLServer中，表是一种很重要的数据库对象，是组成数据库的基本元素，用于存储实体集和实体之间联系的数据。数据库中的表主要由列和行构成，每一列用来保存关系的属性，称为字段。每一行用来保存关系的元组，称为数据行或记录。数据表的基本组成4（1）列（Column）：即属性列，用户必须指定表的列名称和数据类型。（2）主键（PK）：可以由一个列字段，也可以由多个列字段组成，它可以唯一地标识表中的一行，用以实现数据的实体完整性约束。（3）外键（FK）：表示了两个关系表之间的相关联系，由表中列字段或列字段组合组成，它不是本表的主键，但可以是另一个表（主键表）中的主键，用以实现数据的参照完整性约束。数据表的基本组成5（4）Check约束：用一个逻辑表达式限制用户输入列值在指定的范围，用以实现数据的用户定义完整性约束。（5）触发器（Trigger）：是用户定义的事务命令的集合，当对某表中的数据进行插入、更新或删除时这组命令就会自动执行，可用来确保数据的完整性，用以实现数据的用户定义完整性约束。（6）索引（Index）：是根据指定表的某些列建立起来的顺序，提供了快速访问数据的途径且可监督表的数据，使其索引所指向的列中的数据不重复。主键默认为聚集索引。列（字段）数据类型6SqlServer提供的系统数据类型有以下几大类，共25种。SqlServer会自动限制每个系统数据类型的取值范围，当插入数据库中的值超过了数据允许的范围时，SqlServer就会报错。1.整数数据类型用于存储整数，是SqlServer中常用的数据类型之一，包含以下4类：（1）bigint类型：每个bigint类型数据存储在8个字节中，其中一个二进制位表示符号位，其它63个二进制位表示长度和大小。列（字段）数据类型7（2）int类型：每个int类型数据存储在4个字节中,其中一个二进制位表示符号位，其它31个二进制位表示长度和大小。（3）smallint类型：每个smallint类型数据占用了两个字节的存储空间，其中一个二进制位表示整数值的正负号，其它15个二进制位表示长度和大小。（4）tinyint类型：每个tinyint类型数据占用了一个字节的存储空间，可以表示0~255范围内的所有整数。列（字段）数据类型82.浮点数据类型：用于存储十进制小数，表示浮点数据的大致数值，包含以下3类：(1)

real类型：每个real类型的数据占用4个字节的存储空间，可以存储正的或者负的十进制数值。(2)

float[(n)]类型：如果不指定数据类型float的长度，它占用8个字节的存储空间。float数据类型可以写成float(n)的形式，n为指定float数据的精度。(3)

decimal[(p[,s])]和numeric[(p[,s])类型：带固定精度和小数位数的数值数据类型。numeric在功能上等价于decimal。列（字段）数据类型93.字符数据类型用来存储各种字符，数字符号和特殊符号，也是SqlServer中最常用的数据类型之一。在使用字符数据类型时，需要在其前后加上英文单引号或者双引号，包含以下4类：(1)char(n)类型：当用char数据类型存储数据时，每个字符和符号占用一个字节存储空间，n表示所有字符所占的存储空间，如不指定n值，系统默认n为1。若输入数据的字符串长度小于n，则系统自动在其后添加空格来填满设定好的空间；若输入的数据过长，则会截掉其超出部分。列（字段）数据类型10(2)varhcar(n|max)类型： n为存储字符的最大长度，可根据实际存储的字符数改变存储空间。如varchcar(20),则对应的变量最多只能存储20个字符，不够20个字符的按实际存储。(3)nchar(n)类型：n个字符的固定长度Unicode字符数据。(4)nvarchar(n|max)类型：与varchar类似，存储可变长度Unicode字符数据。列（字段）数据类型114.日期和时间数据类型用于存储日期和时间数据，包含以下6类：(1)date类型：用来存储以字符串形式表示的日期数据，占用3个字节的空间。(2)time类型：用来存储以字符串形式记录一天的某个时间，，占用5个字节的空间。(3)datetime类型：用于存储时间和日期数据，占用8个字节的空间。(4)datetime2类型：datetime的扩展类型，其数据范围更大，默认的最小精度最高，并具有可选的用户定义的精度。列（字段）数据类型12(5)smalldatetime类型：与datetime类型相似，该类型数据占用4个字节的空间。smalldatetime只精准到分，而datetime则可精准到3.33毫秒。(6)datetimeoffset类型：用于定义一个采用24小时制与日期相组合并可识别时区的时间。存储该数据类型数据时默认占用10个字节大小的固定存储空间.列（字段）数据类型135.文本和图形数据类型用于存储文本与图形数据，包含以下3类：(1)text类型：用于存储文本数据，为长度可变的非Unicode数据。(2)ntext类型：与text类型作用相同，为长度可变的非Unicode数据。存储大小是所输入字符个数的两倍（以字节为单位）.(3)image类型：长度可变的二进制数据，用于存储图片，由系统根据数据的长度自动分配空间。列（字段）数据类型146.货币数据类型用于存储货币数据，包含以下2类：(1)money类型：用于存储货币值。money数据类型中整数部分包含19个数字，小数部分包含4个数字，因此money数据类型的精度是19，存储时占用8个字节的存储空间。(2)smallmoney类型：与money类型相似，占用4个字节存储空间。输入数据时在前面加上一个货币符号（如人民币为￥）。列（字段）数据类型157.位数据类型bit称为位数据类型，只取0或1为值，长度1字节。bit值经常当作逻辑值用于判断true(1)或false(0),输入非0值时系统将其替换为1。8.二进制数据类型用于存储二进制数据，包含以下2类：(1)binary(n)类型：长度为n个字节的固定长度二进制数据。(2)varbinary(n|max)类型：可变长度二进制数据。列（字段）的属性16列（字段）的属性一般包括以下三个内容：字段的为空性：列值可以接受空值NULL，也可以拒绝空值NOTNULL。IDENTITY属性：列值由系统自动生成数字，并可惟一标识表的每一行。默认值DEFAULT：当往表中插入数据时，用户如果不指定特定的值，则系统会将设置好的（列）默认值自动填入。创建数据表（结构）课程简介18通过本课程的学习，掌握通过SSMS可视化图形界面和T-SQL语句两种方法来创建数据表（结构）。

主要学习内容：一．创建数据表（结构）的定义二．使用SSMS可视化图形界面创建数据表（结构）三．使用T-SQL语句创建数据表（结构）创建数据表（结构）的定义19数据表是数据库中的重要对象，是存放数据的一种逻辑结构，由行记录和列字段组成。数据表的创建实际就是定义表的结构（包括数据完整性约束），创建过程需要确定表的名称、表中各列（字段）的名称、各列的数据类型（含长度）、能否为空值、默认值、主键、外键以及取值规则等信息，这些内容构成了表结构。在SQLServer中，可以通过SSMS界面和T-SQL语句两种方法来创建数据表（结构）。使用SSMS可视化图形界面创建数据表（结构）20【例】在数据库“BookManageDB”中创建各表。1.在SSMS界面的“对象资源管理器”中，展开数据库“BookManageDB”数据库节点，右键“表”节点，从弹出快捷菜单中选择【新建表】选项,如图所示，使用“表设计器”来创建数据表（结构）。使用SSMS可视化图形界面创建数据表（结构）212.创建“BookType”表（图书类型表），其中表结构的列（字段）信息如下：

列1：BookTypeID表示图书类型ID号，类型为int，主键，列值自动增量（标识种子为1，标识增量为1）；

列2：BookTypeName表示图书类型名称,类型为varchar(50)，允许为空值。使用SSMS可视化图形界面创建数据表（结构）22创建表的操作步骤，按图所示：（1）在【列名】栏下面输入对应列名称。（2）在【数据类型】栏下面进行单击，在出现的下拉列表中选择对应数据类型选项。（3）在【允许Null值】栏下面选择是否允许Null值，定义空值约束，实现数据的域完整性控制。使用SSMS可视化图形界面创建数据表（结构）23（4）需要设置主键列，在对应列名左侧出现的

时，单击右键，在出现的快捷菜单中选择【设置主键】选项。使用SSMS可视化图形界面创建数据表（结构）24（5）设置列值自动增量，在“表设计器”下方的“列属性”中展开【标识规范】项，选择【是标识】为“是”，再设置【标识增量】值和【标识种子】值。使用SSMS可视化图形界面创建数据表（结构）25创建一个标识列（也称为自增列），应指定以下4部分内容：

指定数据类型：一般列的数据类型为不带小数的数值类型。设置【是标识】为“是”。设置【标识种子】，指表中列的第一行的起始值，默认值为1。设置【标识增量】，表示相邻两个标识值之间的增量，默认值为1。创建标识列后，后期在对表中此列插入数据时，该列的值由系统按既定规律生成，不允许空值，列值不重复，具有唯一标识表中一行的作用，从而可以实现表的实体完整性。使用SSMS可视化图形界面创建数据表（结构）26（6）用右键单击“文档”窗口中的“表设计器”标签，在弹出的快捷菜单中选择“保存”命令，在弹出的“选择名称”对话框中输入表名“BookType”，再单击“确定”按钮。（7）根据以上的方法再初步创建数据库“BookManageDB”的其他各表。使用T-SQL语句创建数据表（结构）27使用T-SQL语句中的CREATETABLE语句也可以完成创建数据表（结构）的工作，其语法格式如下：使用T-SQL语句创建数据表（结构）28CREATETABLE表名(列名1数据类型列属性1[列级约束1],列名2数据类型列属性2[列级约束2],...列名n数据类型列属性n[列级约束n],[表级约束])使用T-SQL语句创建数据表（结构）29语法参数说明:CREATETABLE表名：创建指定表。列名n数据类型列属性n

[列级约束n]：表示定义各列结构信息。符号“，”：表结构中各列定义之间的间隔符。符号“（）”：表结构定义块。使用T-SQL语句创建数据表（结构）30列属性::=[IDENTITY[(seed,increment)][NULL|NOTNULL]（其中“::=”是“相当于”的意思）①[NULL|NOTNULL]：指定列的为空性，默认值为NULL。②[IDENTITY(seed,increment)]：指定为标识列，seed为标识种子，increment为递增量。使用T-SQL语句创建数据表（结构）31列级约束和表级约束：对于基本表的约束定义（比如主键约束、外键约束等）可分为列级约束定义和表级约束定义两种形式。①列级约束：也就是对于某一个特定列的约束定义，列级约束是行定义的一部分，直接跟在该列的其他定义之后，用空格分隔。②表级约束：表级约束的定义独立于列的定义，不包括在列定义中，通常用于对多个列一起进行约束，与列定义间用“，”分隔，定义时必须指事要约束的列名称。使用T-SQL语句创建数据表（结构）32【例】在数据库“BookManageDB”中创建各表（结构）。（1）“BookType”表创建使用T-SQL语句创建数据表（结构）33（2）编写好语句脚本后点击工具栏上的执行按钮，即可创建对应的表。依次通过CREATETABLE语句创建数据库“BookManageDB”的其他表。管理数据表课程简介35通过本课程的学习，掌握通过SSMS可视化图形界面和T-SQL语句两种方法管理数据表。

主要学习内容：一．使用SSMS可视化图形界面管理数据表。二．使用T-SQL语句管理数据表。使用SSMS可视化图形界面管理数据表361.查看数据表（结构）创建好数据表后，如果想查看数据表的列、主外键约束、触发器、索引等信息。可以在SSMS“对象资源管理器”中，展开指定库中的指定表节点后，如图所示，继续展开下面的“列”、“键”、“约束”、“触发器”、“索引”等节点即可查看。使用SSMS可视化图形界面管理数据表372.修改数据表（结构）数据表创建后，有时仍需要随时修改表结构，如：增删（列）字段、修改（列）字段数据类型、修改主键等。在SSMS“对象资源管理器”中，右键需要修改的表，在弹出快捷菜单中点击【设计】选项，进入“表设计器”界面，可以查看表结构或针对字段、类型以及是否非空等属性进行修改。使用SSMS可视化图形界面管理数据表383.删除数据表在“对象资源管理器”中,鼠标右击指定表，在弹出的快捷菜单中选择【删除】选项，在出现的“删除对象”窗口中进行确认删除，即可删除数据表。使用SSMS可视化图形界面管理数据表393.删除数据表删除表后，表的结构定义、数据、全文索引、约束和索引都永久地从数据库中删除。（注意：如果要删除通过主外键约束相关联的外键表和主键表，则必须首先删除外键表。如果要删除外键约束中引用的主键表而不删除外键表，则必须删除外键表的外键约束。）使用SSMS可视化图形界面管理数据表404.重命名数据表在“对象资源管理器”中选择指定表右击鼠标，在快捷菜单中选择【重命名】选项，即可重命名数据表。使用SSMS可视化图形界面管理数据表415.编写表的脚本在“对象资源管理器”中，在需要操作的数据表中右击鼠标，在快捷菜单中选择【编写表脚本为(S)”】选项，可以对表生成对应的脚本信息，这是SSMS自动帮我们生成的脚本信息，可以极大的节省编写脚本的时间。如下图所示，可以对表进行CREATE、DROP、DROP和CREATE、SELECT、INSERT、UPDATE、DELETE等命令操作。使用SSMS可视化图形界面管理数据表425.编写表的脚本使用T-SQL语句管理数据表431.使用ALTERTABLE语句修改表结构使用T-SQL语句中的ALTERTABLE语句可以完成修改数据表（结构）的定义,其各种修改语句语法及注释说明如下：

使用T-SQL语句管理数据表44ALTERTABLE表名ADD{列名数据类型约束|CONSTRAINT约束名}[,...n]--增加新的列或约束ALTERTABLE表名DROP{COLUMN列名|CONSTRAINT约束名}[,...n]--删除已有的列或约束ALTERTABLE表名ALTERCOLUMN列名列定义--修改列定义(包括列的数据类型和完整性约束)使用T-SQL语句管理数据表45(1)添加列或约束：【例】为表“Book”添加电子邮件地址列和CHECK约束。ALTERTABLEBookADDEmailvarchar(200)NULL--添加列EmailGO--此处的GO必不可少，必须上一句添加列完成之后，才能在下句中为其添加CHECK约束ALTERTABLEBookADDCONSTRAINTBook_EmailCHECK(EmailLIKE'%@%')--添加CHECK约束使用T-SQL语句管理数据表46(2)删除列或者约束【例】为表“Book”删除电子邮件地址列和约束。ALTERTABLEBookDROPEmail--删除列EmailGOALTERTABLEBookDROPBook_Email--删除CHECK约束使用T-SQL语句管理数据表47(3)修改列定义【例】将表“Book”中“BookName”的数据类型“varchar(200)”改为“varchar(30)”。USEBookManageDBGOALTERTABLEBookALTERCOLUMNBookNamevarchar(200)NOTNULL--修改列定义使用T-SQL语句管理数据表482.使用DROPTABLE语句删除表使用DROPTABLE语句删除表，其语法如下：DROPTABLE<表名>【例】删除图书类型表“BookType”。UseBookManageDBGoDROPTABLEBookTypeGo使用T-SQL语句管理数据表49

3.使用系统存储过程sp_rename语句重命名数据表使用系统存储过程sp_rename修改表名的语法格式如下：sp_rename'原表名','新表名'【例】将图书类型表“BookType”重命名为图书类型表“newBookType”。UseBookManageDBGosp_rename'BookType','newBookType'Go认知约束并使用约束来保证数据的完整性课程简介51通过本课程的学习，认知约束并使用约束来保证数据的完整性。

主要学习内容：一．约束的概念二．约束的分类三．数据完整性四．数据完整性与约束之间的关系约束的概念52约束（Constraint）是SQLServer提供的自动保持数据库完整性的一种方法。约束就是一种表数据输入的限制条件。约束名必须符合标识符的命名规则。为了便于管理约束，建议约束名的起名一般采用约束类型（简拼）及其具体完成任务的组合名称作为约束名。比如对图书类型表（“BookType”表）的主键约束命名，可使用PK_BookType（PK是primarykey的简拼，BookType是表名）。此命名可以说是一目了然。约束的分类53约束的分类，主要有5种：主键约束（primarykeyconstraint）唯一性约束（uniqueconstraint）默认值约束（defaultconstraint）检查约束（checkconstraint）外键约束（foreignkeyconstraint）约束的分类541.主键约束主键约束能够唯一地确定表中的每一条记录，主键约束列不能取空值。主键约束可以保证实体（行）的完整性，是最重要的一种约束。2.唯一性约束唯一性约束是专门针对非主键约束列的其他列的值能具有唯一性，以防止在这些列中输入重复的值。约束的分类55唯一约束与主键约束的区别：唯一性约束指定的列可以有NULL属性。但主键约束所在的列则不允许有NULL属性；一个表中可以包含多个唯一约束，但只能有一个主键约束；约束的分类563.默认值约束默认值约束主要用于确保域（列）完整性，它提供了一种为数据表中任何一列提供默认值的手段。4.检查约束检查约束（CHECK约束），实际就是验证字段输入内容的规则，表示一个字段的输入内容必须满足CHECK约束的条件，若不满足，则数据无法正常输入。可以对每个列设置CHECK约束。约束的分类575.外键约束两张表中如果有共同列（列的基本结构定义相同），并且共同列在其中一个表里是作为主键约束列存在的，那么在另一个表中可以把共同列设置为外键约束列，这样操作后，主键与外键约束就将两个表（主表与从表）关联起来，形成了主外键约束。主表是将此列作为主键约束列的表，从表是将此列作为外键约束的表。约束的分类58当建立起主从表关系后，两张表里的数据就具有了相关性，主表里没有的实体内容，从表里不可出现；主表里的实体不可随意删除，删除前要确认从表里还有没有与此实体关联的数据。外键约束也是一个非常重要的约束，它保证了关联表里的数据具有严格的关联性。数据完整性591.如图所示，数据完整性（主要）包含以下三类：数据完整性601)实体(行）完整性：保证表中的每一行数据在表中是唯一的。2)域（列）完整性：表中的列需满足某种特定的数据类型或约束（强制域完整性限制类型、限制格式、限制可能性值的范围）。3)参照完整性：在输入或删除记录时，包含主键的主表和包含外键的从表的数据应对应一致，保证表之间数据的一致性，防止数据丢失或无意义的数据在数据库中扩散。数据完整性与约束之间的关系61数据完整性与约束之间的关系，如表所示：数据完整性与约束之间的关系62由上表可以看出：保证域（列）完整性的约束可以采用默认值约束、检查约束，一个是设置列默认值，一个是设置列值规则，它们约束的对象就是列。保证实体（行）完整性的约束可以采用主键约束、唯一约束，它们均能保证了行值的唯一性，它们约束的对象就是行。保证表与表之间参考完整性的约束一般采用外键约束，它可以维护相关数据表之间数据一致性。数据表中数据的操作课程简介64通过本课程的学习，掌握通过SSMS可视化图形界面和T-SQL语句两种方法操作数据表中的数据。

主要学习内容：一．数据的操作一．使用SSMS可视化图形界面操作表数据三．使用T-SQL语句操作表数据数据的操作65在SQLServer2008中，创建好表结构后，接着就是往表中添加记录行、更新数据及删除记录行。在SQLServer中，可以通过SSMS可视化图形界面和T-SQL语句两种方法操作数据表中的数据。使用SSMS可视化图形界面操作表数据66【例】使用SSMS界面为“BookManageDB”数据库中的表“BookType”插入记录行、更新数据及删除记录行。1.插入记录行：如下图所示，在"对象资源管理器"窗口中，展开“数据库”节点→“BookManageDB”节点→“表”节点，右键单击表“BookType”，从快捷菜单中选择【编辑前200行】选项。在打开的"表编辑器"中即可依序添加各记录行，完成后，直接单击"表编辑器"所在的"文档"窗口右侧的“关闭”按钮即可。使用SSMS可视化图形界面操作表数据67使用SSMS可视化图形界面操作表数据682.更新数据在打开的"表编辑器"中直接更新需要更改的行数据内容即可。3.删除记录行如图所示，打开的"表编辑器"中，在要删除行的左侧箭头处单击鼠标右键，选择快捷菜单中的“删除”命令，并在弹出的删除对话框中确认删除，即可删除指定记录行。使用T-SQL语句操作表数据69使用T-SQL语句同样可以向数据表中插入记录行、删除记录行和更新数据。1.插入记录行使用INSERT...VALUES语句，可以完成记录行的插入。1）插入单行记录，其语法如下所示：INSERT[INTO]表名[(指定列名表)]VALUES(对应常量表)使用T-SQL语句操作表数据70语法说明：①指定列名表用于指定该数据表的列名，可以一列或多列，顺序可以与表的列顺序不同。如果指定多列，则必须用逗号分隔各列。在数据表中未被指定的列必须支持空值或者默认值，如果默认值存在，该列使用默认值，否则SQLServer会尝试为该列补上一个空值。②对应常量表是向表中插入行数据，如果提供的数据是多个，也必须用逗号分隔。使用T-SQL语句操作表数据71③对应常量表与列名表是一一对应的，所提供的数据与列数、数据类型和列表顺序必须一致。 ④当常量表中提供的数据与表的所有列在个数和顺序上完全一致时，列名表可以省略。使用T-SQL语句操作表数据72【例】为图书管理数据库“BookManageDB”中的表“BookType”插入一条记录行。(此处表“BookType”中的列“BookTypeID”暂时取消自动增