创建数据库表与约束.ppt

第二章 创建数据库表与约束 内容提要 2.1 表相关的几个基本概念 2.2 建立数据库表 2.3 建立表间关系 2.4 建立检查约束 2.5 导入和导出数据 2.6 删除表 2.1 表相关的几个基本概念 一、数据完整性 数据完整性是要求数据库中的数据具 有准确性。准确性是通过数据库表的设计和约 束来实现的。 SQL Server提供了以下4种类型的 约束（Constraint）： 1、实体完整性约束 实体完整性约束要求表中的每一行数 据都反映不同的实体，不能在表中存在完全相 同的数据行。 实现方法：索引、唯一约束、主键约 束或标识列属性。 2、域完整性约束 域完整性约束是给定列的输入有 效性。 实现方法：限制数据类型、检查 约束、输入格式、外键约束、默认值、 非空约束。 例如：学生的成绩若定义为整数 类型则不能输入带小数的分数；学生选 修的课程必须是课程表中实际存在的课 程。 3、引用完整性 在输入或删除数据行时，引用完整性约束 来保持表之间已经定义的关系。 在强制引用完整性时， SQL Server禁止用 户进行下列操作： （1）当主表中没有关联的记录时，将记录添加到 相关表中。 （2）更改主表中的值导致相关表中的记录孤立。 （3）从主表中删除记录，但仍存在与该记录匹配 的相关记录。 实现方法：通过主键和外键之间的引用关系 来实现。 学号姓名性别别年龄龄 200901李爽女19 200902吴小兰女18 200903张丽娟女19 200904赵华男20 学生表： （主表） 学号课课程号成绩绩 200901180 200901575 200902190 200903260 成绩表 ： （子表 ） 4、自定义完整性约束 用户根据具体应用来定义特定的 规则。如定义学生年龄必须在10到30之 间。 实现方法：定义规则、存储过程 或触发器。 二、主键(Primary Key)和外键(Foreign Key) 1、主键 表中有一列或几列组合的值能用来唯一 地标识表中的每一行，这样的一列或者多列的组 合叫做表的主键。 如：学号可以作为学生表的主键，课程 号可以作为课程表的主键，（学号，课程号）作 为成绩表的主键（组合键） 说明：（1）一个表只能有一个主键，主键约 束确保了表中的行是唯一的。 （2）表中可以没有主键，但是通常情况 下应当为表设置一个主键。 思考： 在主键列输入的数值，允许为空吗? 一个表可以有多个主键吗? 在一个学校数据库中，如果一个学校 内允许重名的学员，但是一个班级内不 允许学员重名，可以组合班级和姓名两 个字段一起来作为主键吗？ 2、外键 简单地说，就是“子表”中对应于 “主表”的列，在子表中称为外键或者引用 键。它的值要求与主表的主键或者唯一 键相对应，外键用来强制引用完整性。 例如在成绩表中，学号为外键。一个表 可以有多个外键。 2.2 建立数据库表 一、创建数据库表 在SQL Server Management Studio中，展开“studentdb”数据库节点 ，然后在“表”上右击选择“新建表”，在出 现的表设计器中可以输入表中各列的信 息，如列名、数据类型、是否允许空， 如下图所示： 图2-1 在SQL Server Management Studio中创建students表 输完各列信息以后，单击保存按钮 ，输入表名称：students。在对象资源管理 器中展开“表”节点即可看到students已经存 在。 这只是建立了表的框架，还需要输 入记录数据。方法：在students上右击选择“ 打开表”，依次输入各条记录。 在students表中，我们用到了 nchar和datetime两种数据类型，在SQL Server 中提供了丰富的数据类型供我们选择 。下面介绍一下常用的数据类型。 二、SQL Server 中常用的数据类型 1、数字数据类型 数据类型说明 bigint从-263263-1 的整型数据，存储大小为8个字 节 int从-231231-1 的整型数据，存储大小为4个字 节 Smallint从-215215-1 的整型数据，存储大小为2个字 节 tinyint从0255 的整型数据，存储大小为1个字节 bit只能包括0、1或NULL，可以用bit数据类型代 表TRUE或FALSE、YES或NO。 数据类型说明 decimal(p,s)带定点精度和小数位数的数值数据类型。p表示精 度，指定小数点左边和右边可以存储的十进制数 字的最大个数。精度必须是1到38之间的整数。s 表示小数位数，指定小数点右边可以存储的十进 制数字的最大个数。小数位数必须是从0到p之间 的整数，默认小数位数为0。 numeric(p,s)同decimal(p,s) float(n)从-1.79E+3081.79E+308之间的浮点数字数据。 n为用于存储科学计数法float数尾数的位数，同时 指示其精度和存储大小。n必须为 153之间的值 。 real从-3.40E+3083.40E+308之间的浮点数字数据。 存储大小为4字节。 2、文本数据类型 可以存储由任意字母、符号或数字字 符组成的数据。根据字符长度可以分为固定长 度、可变长度两种，根据字符定义方法又分为 非Unicode数据类型和Unicode数据类型两种 。 Unicode（统一码）数据类型用两个 字节表示一个字符。它几乎可以表示世界上所 有的字符集。 具体说明如下： 数据类型说明 char(n)固定长度的非Unicode字符数据，n必 须是一个18000之间的整数。 varchar(n)可变长度的非Unicode字符数据，n必 须是一个18000之间的整数。 text可变长度的非Unicode长文本数据， 最大长度为231-1个字符。 nchar(n)固定长度的Unicode字符数据，n必须 是一个14000之间的整数。 nvarchar(n) 可变长度的Unicode字符数据，n必须 是一个14000之间的整数。 ntext 可变长度的Unicode长文本数据，最 大长度为230-1个字符。 3、日期和时间类型 由有效的日期或时间组成。例如： “4/01/98 12:15:00:00:00 PM”、”1:28:29:15:01 AM 8/17/98”。日期和时间类型又分为两种： 数据类型说明 datetime能表示从1753年1月1日到9999年12 月31日的日期和时间数据，精确度为 百分之三秒（3.33毫秒）。用两个4字 节整数存储，第一个4字节存储日期 ，第二个4字节存储时间 。 smalldatetime能表示从1900年1月1日到2079年6月 6日的日期和时间数据，精确到分钟 。用两个2字节整数存储，第一个2字 节存储日期，第二个2字节存储时间 。 4、货币数据类型 数据类型说明 money介于-263263-1 之间，精确到小数点 后面4位数字，存储大小为8个字节 。 smallmoney 介于-214748.3648+214748.3647 之间，精确到小数点后面4位数字， 存储大小为4个字节。 5、二进制数据类型 二进制字符串由十六进制数表示。 数据类型说明 Binary(n)固定长度的n个字节二进制数，n必须从 18000，存储空间为n个字节。 Varbinary(n)可变长度的n个字节二进制数，n必须从 18000，存储空间为实际输 入数据长度 +2个字节。 image可以用来存储超过8KB的可变长度的二进 制数据，如Microsoft Word文档， Microsoft Excel电子表格、包含位图的图 像、GIF文件和JPEG文件。 思考：在创建表时下列列一般定义 为何种数据类型？ 姓名、地址、电话号码等： 年龄： 出生日期： 照片： 薪水： 字符型 整型 日期时间型 image 货币型 三、建立主键 建立主键的方法为：选择要建立 主键的列（如果主键为组合键，可以按 住ctrl键单击选中各列） ，右击选择“设 置主键”。 如果在定义表时未定义主键，以 后可以在表名上右击选择“修改”，进行设 置。 四、设置默认值 有时候，对某项数据进行输入 的时候，它总是存在一个默认值，例如 女子学院中的学员性别通常应该是“女”。 五、标识列 在有些情况下，存储的信息中很 难找到不重复的信息作为列的主键，SQL Server 提供了一个“标识列”，特意对列进 行区分，标识列本身没有具体的意义。标 识列的实现方式如下： （1）如果一列的数据属于数字类型（ 例如整数），则可以把该列定义为标识列 。 （2）定义成标识列后，还需要分别指 定“标识种子”和“标识地增量”，默认值都 是1。 （3）定义了标识列以后，在每次输 入数据的时候，该列随数据行的增加而 自动增加数字，并且不会重复，第一次 的数字就是“标识种子”值，以后每次按照 “标识递增量”增加数值。 标识列通常也被定义为主键， 通常说的“自动编号”就是指标识列的数字 自动增加。 2.3 建立表间关系 建立表间关系实际上就是实施引用 完整性约束，建立主表和子表关系。 前面已经创建了students表,主键 为sno。在studentdb数据库中再创建 lessons表和scores表。这两个表的定义如 下： lessons(lno, lname, credit) ,主键 为lno scores(sno, lno, score),主键为 (sno,lno) 可以按以下步骤建立students和 scores这两张表的关系： （1）在scores表的设计窗口中右击，选 择“关系”，单击“添加”按钮。 （2）单击“表和列规范”最右侧的小按钮 ，在显示的对话框中选择主表为student，主 键字段为sno，对应的外键表scores的字段为 sno。 （3）单击确定并保存表结构，这样它们 之间的主-外键关系就建立了。 要查看表之间的关系，可以在数 据库中选择“数据库关系图”选项，然后根 据提示信息新建数据库关系图。 还可以用下面的方法建立主-外 键关系，如lessons表和scores表之间： 在新建的关系图中添加lessons表 ，然后将scores表的lno列拖动到lessons 表的lno列上松手，在出现的对话框中进 行设置。 三个表之间的关系图如下所示： 2.4 建立检查约束 检查约束也叫CHECK约束，用于定义列中 可接受的数据或者格式，例如scores表中的 score数值应当为0100，如果不满足要求应 当不允许输入。 方法：在设计scores表时，右击选择 “ CHECK约束”，在出现的对话框中单击“添加” 按钮，添加一个新的约束。然后单击“表达式” 右侧的小按钮，在弹出的“CHECK约束表达式” 对话框中输入： 0=10 and Sage=50 3、要求学号（sno）必须为10位数 字，且以2009打头 ： sno like 20090-90-90-90-90-90-9 2.5 导入和导出数据 有时候，需要把存储在数据库中的数 据导出保存成文本文件或Excel文件，或者把 Excel文件中的数据导入到数据库表中，这时 候就可以使用数据库的导入/导出向导。 方法：右击数据库，选择“任务”中的“ 导出数据”选项，在向导中选择数据库，单击“ 下一步”；输入文件名称并确定文件相关选项 ；选择是否用一条查询语句进行复制；选择表 名并设置文本文件的格式；确定是否立即运行 ，是否保存该转换设置，单击“下一步”一直到 完成。 2.6 删除表 对于不再使用的数据表，可以删除以 释放磁盘空间，选择该表，右击选择“删 除”选项即可。 对于建立主外键关系的表，如果要删 除主表