创建数据库表与约束课件

第二章创建数据库表与约束第二章创建数据库表与约束1内容提要2.1表相关的几个基本概念2.2建立数据库表2.3建立表间关系2.4建立检查约束2.5导入和导出数据2.6删除表内容提要2.1表相关的几个基本概念22.1表相关的几个基本概念一、数据完整性数据完整性是要求数据库中的数据具有准确性。准确性是通过数据库表的设计和约束来实现的。SQLServer提供了以下4种类型的约束（Constraint）：1、实体完整性约束实体完整性约束要求表中的每一行数据都反映不同的实体，不能在表中存在完全相同的数据行。实现方法：索引、唯一约束、主键约束或标识列属性。2.1表相关的几个基本概念一、数据完整性32、域完整性约束域完整性约束是给定列的输入有效性。实现方法：限制数据类型、检查约束、输入格式、外键约束、默认值、非空约束。

例如：学生的成绩若定义为整数类型则不能输入带小数的分数；学生选修的课程必须是课程表中实际存在的课程。2、域完整性约束43、引用完整性

在输入或删除数据行时，引用完整性约束来保持表之间已经定义的关系。在强制引用完整性时，SQLServer禁止用户进行下列操作：（1）当主表中没有关联的记录时，将记录添加到相关表中。（2）更改主表中的值导致相关表中的记录孤立。（3）从主表中删除记录，但仍存在与该记录匹配的相关记录。实现方法：通过主键和外键之间的引用关系来实现。3、引用完整性5学号姓名性别年龄……200901李爽女19200902吴小兰女18200903张丽娟女19200904赵华男20学生表：（主表）学号课程号成绩……200901180200901575200902190200903260………………成绩表：（子表）学号姓名性别年龄……200901李爽女19200902吴小兰64、自定义完整性约束

用户根据具体应用来定义特定的规则。如定义学生年龄必须在10到30之间。实现方法：定义规则、存储过程或触发器。4、自定义完整性约束7二、主键(PrimaryKey)和外键(ForeignKey)1、主键表中有一列或几列组合的值能用来唯一地标识表中的每一行，这样的一列或者多列的组合叫做表的主键。如：学号可以作为学生表的主键，课程号可以作为课程表的主键，（学号，课程号）作为成绩表的主键（组合键）说明：（1）一个表只能有一个主键，主键约束确保了表中的行是唯一的。（2）表中可以没有主键，但是通常情况下应当为表设置一个主键。二、主键(PrimaryKey)和外键(ForeignK8思考：在主键列输入的数值，允许为空吗?一个表可以有多个主键吗?在一个学校数据库中，如果一个学校内允许重名的学员，但是一个班级内不允许学员重名，可以组合班级和姓名两个字段一起来作为主键吗？思考：92、外键简单地说，就是“子表”中对应于“主表”的列，在子表中称为外键或者引用键。它的值要求与主表的主键或者唯一键相对应，外键用来强制引用完整性。例如在成绩表中，学号为外键。一个表可以有多个外键。2、外键102.2建立数据库表一、创建数据库表在SQLServerManagementStudio中，展开“studentdb”数据库节点，然后在“表”上右击选择“新建表”，在出现的表设计器中可以输入表中各列的信息，如列名、数据类型、是否允许空，如下图所示：2.2建立数据库表一、创建数据库表11图2-1在SQLServerManagementStudio中创建students表图2-1在SQLServerManagementS12输完各列信息以后，单击保存按钮，输入表名称：students。在对象资源管理器中展开“表”节点即可看到students已经存在。这只是建立了表的框架，还需要输入记录数据。方法：在students上右击选择“打开表”，依次输入各条记录。在students表中，我们用到了nchar和datetime两种数据类型，在SQLServer中提供了丰富的数据类型供我们选择。下面介绍一下常用的数据类型。输完各列信息以后，单击保存按钮，输入表名称：s13二、SQLServer中常用的数据类型1、数字数据类型数据类型说明bigint从-263~263-1的整型数据，存储大小为8个字节int从-231~231-1的整型数据，存储大小为4个字节Smallint从-215~215-1的整型数据，存储大小为2个字节tinyint从0~255的整型数据，存储大小为1个字节bit只能包括0、1或NULL，可以用bit数据类型代表TRUE或FALSE、YES或NO。二、SQLServer中常用的数据类型数据类型说明big14数据类型说明decimal[(p[,s])]带定点精度和小数位数的数值数据类型。p表示精度，指定小数点左边和右边可以存储的十进制数字的最大个数。精度必须是1到38之间的整数。s表示小数位数，指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从0到p之间的整数，默认小数位数为0。numeric[(p[,s])]同decimal[(p[,s])]float[(n)]从-1.79E+308~1.79E+308之间的浮点数字数据。n为用于存储科学计数法float数尾数的位数，同时指示其精度和存储大小。n必须为1~53之间的值。real从-3.40E+308~3.40E+308之间的浮点数字数据。存储大小为4字节。数据类型说明decimal[(p[,s])]带定点精度和小数152、文本数据类型可以存储由任意字母、符号或数字字符组成的数据。根据字符长度可以分为固定长度、可变长度两种，根据字符定义方法又分为非Unicode数据类型和Unicode数据类型两种。Unicode（统一码）数据类型用两个字节表示一个字符。它几乎可以表示世界上所有的字符集。具体说明如下：2、文本数据类型16数据类型说明char(n)固定长度的非Unicode字符数据，n必须是一个1~8000之间的整数。varchar(n)可变长度的非Unicode字符数据，n必须是一个1~8000之间的整数。text可变长度的非Unicode长文本数据，最大长度为231-1个字符。nchar(n)固定长度的Unicode字符数据，n必须是一个1~4000之间的整数。nvarchar(n)可变长度的Unicode字符数据，n必须是一个1~4000之间的整数。ntext可变长度的Unicode长文本数据，最大长度为230-1个字符。数据类型说明char(n)固定长度的非Unicode字符数据173、日期和时间类型由有效的日期或时间组成。例如：“4/01/9812:15:00:00:00PM”、”1:28:29:15:01AM8/17/98”。日期和时间类型又分为两种：数据类型说明datetime能表示从1753年1月1日到9999年12月31日的日期和时间数据，精确度为百分之三秒（3.33毫秒）。用两个4字节整数存储，第一个4字节存储日期，第二个4字节存储时间。smalldatetime能表示从1900年1月1日到2079年6月6日的日期和时间数据，精确到分钟。用两个2字节整数存储，第一个2字节存储日期，第二个2字节存储时间。3、日期和时间类型数据类型说明datetime能表示从175184、货币数据类型数据类型说明money介于-263~263-1之间，精确到小数点后面4位数字，存储大小为8个字节。smallmoney介于-214748.3648~+214748.3647之间，精确到小数点后面4位数字，存储大小为4个字节。4、货币数据类型数据类型说明money介于-263~263-195、二进制数据类型二进制字符串由十六进制数表示。数据类型说明Binary(n)固定长度的n个字节二进制数，n必须从1~8000，存储空间为n个字节。Varbinary(n)可变长度的n个字节二进制数，n必须从1~8000，存储空间为实际输入数据长度+2个字节。image可以用来存储超过8KB的可变长度的二进制数据，如MicrosoftWord文档，MicrosoftExcel电子表格、包含位图的图像、GIF文件和JPEG文件。5、二进制数据类型数据类型说明Binary(n)固定长度的n20思考：在创建表时下列列一般定义为何种数据类型？姓名、地址、电话号码等：年龄：出生日期：照片：薪水：字符型整型日期时间型image货币型思考：在创建表时下列列一般定义为何种数据类型？字符型整型日期21三、建立主键建立主键的方法为：选择要建立主键的列（如果主键为组合键，可以按住ctrl键单击选中各列），右击选择“设置主键”。如果在定义表时未定义主键，以后可以在表名上右击选择“修改”，进行设置。三、建立主键22四、设置默认值有时候，对某项数据进行输入的时候，它总是存在一个默认值，例如女子学院中的学员性别通常应该是“女”。四、设置默认值23五、标识列在有些情况下，存储的信息中很难找到不重复的信息作为列的主键，SQLServer提供了一个“标识列”，特意对列进行区分，标识列本身没有具体的意义。标识列的实现方式如下：（1）如果一列的数据属于数字类型（例如整数），则可以把该列定义为标识列。（2）定义成标识列后，还需要分别指定“标识种子”和“标识地增量”，默认值都是1。五、标识列24（3）定义了标识列以后，在每次输入数据的时候，该列随数据行的增加而自动增加数字，并且不会重复，第一次的数字就是“标识种子”值，以后每次按照“标识递增量”增加数值。标识列通常也被定义为主键，通常说的“自动编号”就是指标识列的数字自动增加。（3）定义了标识列以后，在每次输入数据的时候，该列随数据行的252.3建立表间关系建立表间关系实际上就是实施引用完整性约束，建立主表和子表关系。前面已经创建了students表,主键为sno。在studentdb数据库中再创建lessons表和scores表。这两个表的定义如下：lessons(lno,lname,credit),主键为lnoscores(sno,lno,score),主键为(sno,lno)2.3建立表间关系建立表间关系实际上就是实施引26创建数据库表与约束课件27可以按以下步骤建立students和scores这两张表的关系：（1）在scores表的设计窗口中右击，选择“关系”，单击“添加”按钮。（2）单击“表和列规范”最右侧的小按钮，在显示的对话框中选择主表为student，主键字段为sno，对应的外键表scores的字段为sno。（3）单击确定并保存表结构，这样它们之间的主-外键关系就建立了。可以按以下步骤建立students和scores28要查看表之间的关系，可以在数据库中选择“数据库关系图”选项，然后根据提示信息新建数据库关系图。还可以用下面的方法建立主-外键关系，如lessons表和scores表之间：在新建的关系图中添加lessons表，然后将scores表的lno列拖动到lessons表的lno列上松手，在出现的对话框中进行设置。要查看表之间的关系，可以在数据库中选择“数据库29三个表之间的关系图如下所示：三个表之间的关系图如下所示：302.4建立检查约束检查约束也叫CHECK约束，用于定义列中可接受的数据或者格式，例如scores表中的score数值应当为0~100，如果不满足要求应当不允许输入。方法：在设计scores表时，右击选择“CHECK约束”，在出现的对话框中单击“添加”按钮，添加一个新的约束。然后单击“表达式”右侧的小按钮，在弹出的“CHECK约束表达式”对话框中输入：0<=scoreandscore<=100单击“确定”按钮后保存表，该约束建立。可以打开scores表输入或修改数据进行验证。2.4建立检查约束检查约束也叫CHECK约束，用于定义列中31思考：1、如何定义约束来检查邮件地址（SEmail）输入是否有效？SEmailLIKE‘%@%’2、如何定义约束来检查年龄（Sage）是否在10~50之间？Sage>=10andSage<=50思考：SEmailLIKE‘%@%’2、如何定义约束来检323、要求学号（sno）必须为10位数字，且以2009打头：snolike‘2009[0-9][0-9][0-9][0-9][0-9][0-9]’3、要求学号（sno）必须为10位数字，且以2009打头：332.5导入和导出数据有时候，需要把存储在数据库中的数据导出保存成文本文件或Excel文件，或者把Excel文件中的数据导入到数据库表中，这时候就可以使用数据库的导入/导出向导。

方法：右击数据库，选择“任务”中的“导出数据”选项，在向导中选择数据库，单击“下一步”；输入文件名称并确定文件相关选项；选择是否用一条查询语句进行复制；选择表名并设置文本文件的格式；确定是否立即运行，是否保存该转换设置，单击“下一步”一直到完成。2.5导入和导出数据有时候，需要把存储在数据342.6删除表对于不再使用的数据表，可以删除以释放磁盘空间，选择该表，右击选择“删除”选项即可。对于建立主外键关系的表，如果要删除主表，则首先要删除相关的子表以保证数据的引用完整性。另外，删除表一定要谨慎，不然会造成有用的数据丢失。2.6删除表对于不再使用的数据表，可以删除以释放磁盘空间，35本章总结创建数据库表需要确定表的列名、数据类型、是否允许为空，还需要确定主键、必要的默认值、标识列和检查约束。创建表的过程是规定数据列属性的过程，同时也是实施数据完整性（包括实体完整性、引用完整性和域完整性）的过程。本章总结创建数据库表需要确定表的列名、数据类型、是否允许为空36如果建立了主表和子表的关系，则：子表中的相关项目数据，在主表中必须存在主表中相关项的数据更改了，则子表对应的数据项也必须随之更改在删除子表之前，不能够删除主表如果建立了主表和子表的关系，则：37第二章创建数据库表与约束第二章创建数据库表与约束38内容提要2.1表相关的几个基本概念2.2建立数据库表2.3建立表间关系2.4建立检查约束2.5导入和导出数据2.6删除表内容提要2.1表相关的几个基本概念392.1表相关的几个基本概念一、数据完整性数据完整性是要求数据库中的数据具有准确性。准确性是通过数据库表的设计和约束来实现的。SQLServer提供了以下4种类型的约束（Constraint）：1、实体完整性约束实体完整性约束要求表中的每一行数据都反映不同的实体，不能在表中存在完全相同的数据行。实现方法：索引、唯一约束、主键约束或标识列属性。2.1表相关的几个基本概念一、数据完整性402、域完整性约束域完整性约束是给定列的输入有效性。实现方法：限制数据类型、检查约束、输入格式、外键约束、默认值、非空约束。

例如：学生的成绩若定义为整数类型则不能输入带小数的分数；学生选修的课程必须是课程表中实际存在的课程。2、域完整性约束413、引用完整性

在输入或删除数据行时，引用完整性约束来保持表之间已经定义的关系。在强制引用完整性时，SQLServer禁止用户进行下列操作：（1）当主表中没有关联的记录时，将记录添加到相关表中。（2）更改主表中的值导致相关表中的记录孤立。（3）从主表中删除记录，但仍存在与该记录匹配的相关记录。实现方法：通过主键和外键之间的引用关系来实现。3、引用完整性42学号姓名性别年龄……200901李爽女19200902吴小兰女18200903张丽娟女19200904赵华男20学生表：（主表）学号课程号成绩……200901180200901575200902190200903260………………成绩表：（子表）学号姓名性别年龄……200901李爽女19200902吴小兰434、自定义完整性约束

用户根据具体应用来定义特定的规则。如定义学生年龄必须在10到30之间。实现方法：定义规则、存储过程或触发器。4、自定义完整性约束44二、主键(PrimaryKey)和外键(ForeignKey)1、主键表中有一列或几列组合的值能用来唯一地标识表中的每一行，这样的一列或者多列的组合叫做表的主键。如：学号可以作为学生表的主键，课程号可以作为课程表的主键，（学号，课程号）作为成绩表的主键（组合键）说明：（1）一个表只能有一个主键，主键约束确保了表中的行是唯一的。（2）表中可以没有主键，但是通常情况下应当为表设置一个主键。二、主键(PrimaryKey)和外键(ForeignK45思考：在主键列输入的数值，允许为空吗?一个表可以有多个主键吗?在一个学校数据库中，如果一个学校内允许重名的学员，但是一个班级内不允许学员重名，可以组合班级和姓名两个字段一起来作为主键吗？思考：462、外键简单地说，就是“子表”中对应于“主表”的列，在子表中称为外键或者引用键。它的值要求与主表的主键或者唯一键相对应，外键用来强制引用完整性。例如在成绩表中，学号为外键。一个表可以有多个外键。2、外键472.2建立数据库表一、创建数据库表在SQLServerManagementStudio中，展开“studentdb”数据库节点，然后在“表”上右击选择“新建表”，在出现的表设计器中可以输入表中各列的信息，如列名、数据类型、是否允许空，如下图所示：2.2建立数据库表一、创建数据库表48图2-1在SQLServerManagementStudio中创建students表图2-1在SQLServerManagementS49输完各列信息以后，单击保存按钮，输入表名称：students。在对象资源管理器中展开“表”节点即可看到students已经存在。这只是建立了表的框架，还需要输入记录数据。方法：在students上右击选择“打开表”，依次输入各条记录。在students表中，我们用到了nchar和datetime两种数据类型，在SQLServer中提供了丰富的数据类型供我们选择。下面介绍一下常用的数据类型。输完各列信息以后，单击保存按钮，输入表名称：s50二、SQLServer中常用的数据类型1、数字数据类型数据类型说明bigint从-263~263-1的整型数据，存储大小为8个字节int从-231~231-1的整型数据，存储大小为4个字节Smallint从-215~215-1的整型数据，存储大小为2个字节tinyint从0~255的整型数据，存储大小为1个字节bit只能包括0、1或NULL，可以用bit数据类型代表TRUE或FALSE、YES或NO。二、SQLServer中常用的数据类型数据类型说明big51数据类型说明decimal[(p[,s])]带定点精度和小数位数的数值数据类型。p表示精度，指定小数点左边和右边可以存储的十进制数字的最大个数。精度必须是1到38之间的整数。s表示小数位数，指定小数点右边可以存储的十进制数字的最大个数。小数位数必须是从0到p之间的整数，默认小数位数为0。numeric[(p[,s])]同decimal[(p[,s])]float[(n)]从-1.79E+308~1.79E+308之间的浮点数字数据。n为用于存储科学计数法float数尾数的位数，同时指示其精度和存储大小。n必须为1~53之间的值。real从-3.40E+308~3.40E+308之间的浮点数字数据。存储大小为4字节。数据类型说明decimal[(p[,s])]带定点精度和小数522、文本数据类型可以存储由任意字母、符号或数字字符组成的数据。根据字符长度可以分为固定长度、可变长度两种，根据字符定义方法又分为非Unicode数据类型和Unicode数据类型两种。Unicode（统一码）数据类型用两个字节表示一个字符。它几乎可以表示世界上所有的字符集。具体说明如下：2、文本数据类型53数据类型说明char(n)固定长度的非Unicode字符数据，n必须是一个1~8000之间的整数。varchar(n)可变长度的非Unicode字符数据，n必须是一个1~8000之间的整数。text可变长度的非Unicode长文本数据，最大长度为231-1个字符。nchar(n)固定长度的Unicode字符数据，n必须是一个1~4000之间的整数。nvarchar(n)可变长度的Unicode字符数据，n必须是一个1~4000之间的整数。ntext可变长度的Unicode长文本数据，最大长度为230-1个字符。数据类型说明char(n)固定长度的非Unicode字符数据543、日期和时间类型由有效的日期或时间组成。例如：“4/01/9812:15:00:00:00PM”、”1:28:29:15:01AM8/17/98”。日期和时间类型又分为两种：数据类型说明datetime能表示从1753年1月1日到9999年12月31日的日期和时间数据，精确度为百分之三秒（3.33毫秒）。用两个4字节整数存储，第一个4字节存储日期，第二个4字节存储时间。smalldatetime能表示从1900年1月1日到2079年6月6日的日期和时间数据，精确到分钟。用两个2字节整数存储，第一个2字节存储日期，第二个2字节存储时间。3、日期和时间类型数据类型说明datetime能表示从175554、货币数据类型数据类型说明money介于-263~263-1之间，精确到小数点后面4位数字，存储大小为8个字节。smallmoney介于-214748.3648~+214748.3647之间，精确到小数点后面4位数字，存储大小为4个字节。4、货币数据类型数据类型说明money介于-263~263-565、二进制数据类型二进制字符串由十六进制数表示。数据类型说明Binary(n)固定长度的n个字节二进制数，n必须从1~8000，存储空间为n个字节。Varbinary(n)可变长度的n个字节二进制数，n必须从1~8000，存储空间为实际输入数据长度+2个字节。image可以用来存储超过8KB的可变长度的二进制数据，如MicrosoftWord文档，MicrosoftExcel电子表格、包含位图的图像、GIF文件和JPEG文件。5、二进制数据类型数据类型说明Binary(n)固定长度的n57思考：在创建表时下列列一般定义为何种数据类型？姓名、地址、电话号码等：年龄：出生日期：照片：薪水：字符型整型日期时间型image货币型思考：在创建表时下列列一般定义为何种数据类型？字符型整型日期58三、建立主键建立主键的方法为：选择要建立主键的列（如果主键为组合键，可以按住ctrl键单击选中各列），右击选择“设置主键”。如果在定义表时未定义主键，以后可以在表名上右击选择“修改”，进行设置。三、建立主键59四、设置默认值有时候，对某项数据进行输入的时候，它总是存在一个默认值，例如女子学院中的学员性别通常应该是“女”。四、设置默认值60五、标识列在有些情况下，存储的信息中很难找到不重复的信息作为列的主键，SQLServer提供了一个“标识列”，特意对列进行区分，标识列本身没有具体的意义。标识列的实现方式如下：（1）如果一列的数据属于数字类型（例如整数），则可以把该列定义为标识列。（2）定义成标识列后，还需要分别指定“标识种子”和“标识地增量”，默认值都是1。五、标识列61（3）定义了标识列以后，在每次输入数据的时候，该列随数据行的增加而自动增加数字，并且不会重复，第一次的数字就是“标识种子”值，以后每次按照“标识递增量”增加数值。标识列通常也被定义为主键，通常说的“自动编号”就是指标识列的数字自动增加。（3）定义了标识列以后，在每次输入数据的时候，该列随数据行的622.3建立表间关系建立表间关系实际上就是实施引用完整性约束，建立主表和子表关系。前面已经创建了students表,主键为sno。在studentdb数据库中再创建lessons表和scores表。这两个表的定义如下：lessons(lno,lname,credit),主键为lnoscores(sno,lno,score),主键为(sno,lno)2.3建立表间关系建立表间关系实际上就是实施引63创建数据库表与约束课件64可以按以下步骤建立students和scores这两张表的关系：（1）在scores表的设计窗口中右击，选择“关系”，单击“添加”按钮。（2）单击“表和列规范”最右侧的小按钮，在显示的对话框中选择主表为student，主键字段为sno，对应的外键表scores的字段为sno。（3）单击确定并保存表结构，这样它们之间的主-外键关系就建立了。可以按以下步骤建立students和scores65要查看表之间的关系，可以在数据库中选择“数据库关系图”选项，然后根据提示信息新建数据库关系图。还可以用下面的方法建立主-外键关系，如lessons表和scores表之间：在新建的关系图中添加lessons表，然后将scores表的lno列拖动到lessons表