第04章-4数据表的创建

1、1第第4章章 数据表的创建数据表的创建 2第第4章章 数据表的创建数据表的创建 l4.1 数据表的建立数据表的建立l4.2 数据表的修改数据表的修改34.1 数据表的建立数据表的建立 l4.1.1 数据表创建实例数据表创建实例l4.1.2 数据类型数据类型 l4.1.3 数据完整性数据完整性l4.1.4 数据表创建数据表创建l4.1.4 关系的类型关系的类型l4.1.6 特殊类型表特殊类型表 44.1.1 数据表创建实例数据表创建实例l在在SQL Server中，可以通过中，可以通过SQL Server Management Studio的图形界面或的图形界面或Transact-SQL语句实现

2、数据表的创建，语句实现数据表的创建，下面将分别介绍。下面将分别介绍。l例如，在教学数据库中创建如下例如，在教学数据库中创建如下3张数据表，学生表张数据表，学生表(student)、课程表、课程表(course)、选课表、选课表(s_c)，其表结构如，其表结构如表表6.6、表、表6.7和表和表6.8所示。所示。54.1.1 数据表创建实例数据表创建实例64.1.1 数据表创建实例数据表创建实例l1. 在图形界面下创建数据表l(1) 在在SQL Server Management Studio中的对象资源管中的对象资源管理器中，右击理器中，右击 “teaching”数据库下的数据库下的【表表】项，

3、在弹出项，在弹出的快捷菜单中选择的快捷菜单中选择【新建表新建表】命令，出现如图命令，出现如图4.1所示的表所示的表设计器界面。设计器界面。l(2) 在图在图4.1中的列名下依次输入列名，在数据类型下选择中的列名下依次输入列名，在数据类型下选择数据类型，并选择各个列是否允许空值，也可在下面的列属数据类型，并选择各个列是否允许空值，也可在下面的列属性对话框中修改某列的属性，出现如图性对话框中修改某列的属性，出现如图4.2所示的学生数据所示的学生数据表结构。表结构。l(3) 选中选中SNO所在的列，单击工具栏上的所在的列，单击工具栏上的【设置主键设置主键】按钮按钮设置主键。设置主键。74.1.1 数

4、据表创建实例数据表创建实例l图图4.1 表设计器界面表设计器界面 84.1.1 数据表创建实例数据表创建实例l图图4.2 学生表结构界面学生表结构界面 94.1.1 数据表的创建数据表的创建 l图图4.3 设置设置CHECK约束界面约束界面 104.1.1 数据表创建实例数据表创建实例l(4) 单击工具栏上的单击工具栏上的【管理管理CHECK约束约束】按钮，设置用户按钮，设置用户定义约束，如图定义约束，如图4.3所示。所示。l(5) 设置好设置好CHECK约束后单击约束后单击【关闭关闭】按钮，返回表设计按钮，返回表设计器界面。器界面。l(6) 单击工具栏中的单击工具栏中的【保存保存】按钮，出现

5、如图按钮，出现如图4.4所示的所示的【选择名称选择名称】对话框，在该框中输入表名对话框，在该框中输入表名“student”，然，然后单击后单击【确定确定】按钮。按钮。l(7) 这时，在这时，在【对象资源管理器对象资源管理器】中的中的【表表】项就会出现用项就会出现用户表户表student。l(8) 根据以上步骤，依次建立课程表根据以上步骤，依次建立课程表(course)和选课表和选课表(s_c)。如图。如图4.5和图和图4.6所示。所示。114.1.1 数据表创建实例数据表创建实例l图图4.4 【选择名称选择名称】对话框对话框 124.1.1 数据表创建实例数据表创建实例l图图4.5 建立课程表

6、建立课程表(course)界面界面 134.1.1 数据表创建实例数据表创建实例l图图4.6 建立选课表建立选课表(s_c)界面界面 144.1.1 数据表创建实例数据表创建实例l在该对话框中单击在该对话框中单击【添加添加】按钮，再单击右侧窗口中按钮，再单击右侧窗口中“表和表和列规范列规范”右侧的按钮，出现如图右侧的按钮，出现如图4.8所示的所示的【表和列表和列】对话对话框。框。l在该对话框中选择主键表为在该对话框中选择主键表为student，选择主键为，选择主键为SNO，然，然后单击后单击【确定确定】按钮，返回如图按钮，返回如图4.7所示的所示的【外键关系外键关系】对对话框，再单击话框，再单

7、击【添加添加】按钮添加按钮添加CNO外键，同时可以在外键，同时可以在“标识标识”中更改外键的名称，如图中更改外键的名称，如图4.9所示。所示。154.1.1 数据表创建实例数据表创建实例l图图4.7 【外键关系外键关系】对话框对话框 164.1.1 数据表创建实例数据表创建实例l图图4.8 【表和列表和列】对话框对话框 174.1.1 数据表创建实例数据表创建实例l图图4.9 【外键关系外键关系】对话框对话框 184.1.1 数据表创建实例的总结数据表创建实例的总结l在设计数据库时，必须先确定数据库所需的表、每个表中数在设计数据库时，必须先确定数据库所需的表、每个表中数据的类型以及可以访问每个

8、表的用户。据的类型以及可以访问每个表的用户。在创建表及其对象之在创建表及其对象之前，要先确定表的下列特征：前，要先确定表的下列特征： 表中的列数，每一列中数据的类型和长度表中的列数，每一列中数据的类型和长度(如果必要如果必要)。 哪些列允许空值。哪些列允许空值。 是否要使用以及何处使用约束、默认设置和规则。是否要使用以及何处使用约束、默认设置和规则。 所需索引的类型，哪里需要索引，哪些列是主键，哪些是外所需索引的类型，哪里需要索引，哪些列是主键，哪些是外键。键。 194.1.2 数据类型数据类型 l设计表时首先要执行的操作之一是为每个列指定数据类型。设计表时首先要执行的操作之一是为每个列指定数

9、据类型。数据类型定义了各列允许使用的数据值。通过下列方法之一数据类型定义了各列允许使用的数据值。通过下列方法之一可以为列指定数据类型：可以为列指定数据类型： l使用使用 SQL Server 2005 系统数据类型。系统数据类型。l创建基于系统数据类型的别名数据类型。创建基于系统数据类型的别名数据类型。l1. 系统数据类型l在在 SQL Server 2005 中，每个列、局部变量、表达式和参中，每个列、局部变量、表达式和参数都具有一个相关的数据类型。数据类型是一种属性，用于数都具有一个相关的数据类型。数据类型是一种属性，用于指定对象可保存的数据的类型：整数数据、字符数据、货币指定对象可保存的

10、数据的类型：整数数据、字符数据、货币数据、日期和时间数据、二进制字符串等。数据、日期和时间数据、二进制字符串等。 204.1.2 数据类型数据类型 lSQL Server 提供系统数据类型集，该类型集定义了可与提供系统数据类型集，该类型集定义了可与 SQL Server 一起使用的所有数据类型。一起使用的所有数据类型。(1) 精确数字类型精确数字类型bigint、int、smallint、tinyint 取值范围及存储字节数取值范围及存储字节数见。见。 bit 可以取值为可以取值为 1、0 或或 NULL 的整数数据类型。的整数数据类型。decimal与与numeric 带固定精度和小数位数的

11、数值数据类带固定精度和小数位数的数值数据类型。型。money和和smallmoney 代表货币或货币值的数据类型。代表货币或货币值的数据类型。 (2) 近似数字类型近似数字类型float和和real 用于表示浮点数值数据的大致数值数据类型。用于表示浮点数值数据的大致数值数据类型。 (3) 日期和时间类型日期和时间类型 datetime和和smalldatetime 用于表示某天的日期和时间用于表示某天的日期和时间的数据类型。的数据类型。214.1.2 数据类型数据类型 224.1.2 数据类型数据类型 234.1.2 数据类型数据类型 l(4) 字符串类型字符串类型char 和和 varcha

12、r 固定长度或可变长度的字符数据类型。固定长度或可变长度的字符数据类型。text长度可变的非长度可变的非Unicode 数据，最大长度为数据，最大长度为 230 - 1 个字符个字符 。 l(5) Unicode字符串类型字符串类型(2 字节的模式字节的模式 (65,536) )nchar 和和 nvarchar 字符数据类型字符数据类型(nchar 长度固定，长度固定，nvarchar 长度可变长度可变)和和 Unicode 数据使用数据使用 Unicode UCS-2 字符集。字符集。ntext长度可变的长度可变的 Unicode 数据，最大长度为数据，最大长度为 230 - 1 个字符个

13、字符 。l(6) 二进制字符串类型二进制字符串类型binary 和和 varbinary 固定长度或可变长度的固定长度或可变长度的binary 数据数据类型。类型。 image长度可变的二进制数据，从长度可变的二进制数据，从 0 到到 231-1 个字个字节。节。 。244.1.2 数据类型数据类型 l(7) 其他数据类型其他数据类型ltable 一种特殊的数据类型，用于存储结果集以进行后续一种特殊的数据类型，用于存储结果集以进行后续处理。处理。lcursor 这是变量或存储过程这是变量或存储过程 OUTPUT 参数的一种数据类参数的一种数据类型，这些参数包含对游标的引用。型，这些参数包含对游

14、标的引用。lTimestamp 公开数据库中自动生成的惟一二进制数字的数公开数据库中自动生成的惟一二进制数字的数据类型。使用某一行中的据类型。使用某一行中的 timestamp 列可以很容易地确定列可以很容易地确定该行中的任何值自上次读取以后是否发生了更改。如果对行该行中的任何值自上次读取以后是否发生了更改。如果对行进行了更改，就会更新该时间戳值。进行了更改，就会更新该时间戳值。 luniqueidentifier 公开数据库中自动生成的惟一二进制公开数据库中自动生成的惟一二进制数字的数据类型。数字的数据类型。254.1.2 数据类型数据类型 l2. 别名数据类型 别名类型基于别名类型基于 S

15、QL Server 2005 中的系统数据类型。当多中的系统数据类型。当多个表必须在一个列中存储相同类型的数据，而必须确保这些个表必须在一个列中存储相同类型的数据，而必须确保这些列具有相同的数据类型、长度和为空性时，可以使用别名类列具有相同的数据类型、长度和为空性时，可以使用别名类型。型。 CREATE TYPE SSN FROM varchar(11) NOT NULL ; 264.1.3 数据完整性数据完整性 l数据完整性数据完整性是指列中每个事件都有是指列中每个事件都有正确的数据值正确的数据值,数据值必数据值必须位于正确的域中。须位于正确的域中。 l引用完整性引用完整性指示指示表之间的关

16、系表之间的关系得到正确维护。一个表中的数得到正确维护。一个表中的数据只应指向另一个表中的现有行，不应指向不存在的行。据只应指向另一个表中的现有行，不应指向不存在的行。l1. 空值 列可以接受空值，也可以拒绝空值。在数据库中，列可以接受空值，也可以拒绝空值。在数据库中，NULL 是是一个特殊值，表示未知值的概念。一个特殊值，表示未知值的概念。NULL 不同于空字符或不同于空字符或 0。实际上，空字符是一个有效的字符，实际上，空字符是一个有效的字符，0 是一个有效的数字。是一个有效的数字。NULL 只是表示此值未知这一概念。只是表示此值未知这一概念。NULL 也不同于零长度也不同于零长度字符串。字

17、符串。 274.1.3 数据完整性数据完整性 l2. 约束约束 通过约束可以定义通过约束可以定义 SQL Server 2005 Database Engine 自动自动强制实施数据库完整性的方式。强制实施数据库完整性的方式。 约束优先于使用约束优先于使用 DML 触发器、规则和默认值。另外，查询优化触发器、规则和默认值。另外，查询优化器也使用约束定义生成高性能的查询执行计划。器也使用约束定义生成高性能的查询执行计划。l(1) 约束类约束类 SQL Server 2005 支持下列约束类：支持下列约束类： NOT NULL 指定列不接受指定列不接受 NULL 值。值。 CHECK 约束通过限制

18、可放入列中的值来强制实施域完整性。约束通过限制可放入列中的值来强制实施域完整性。 UNIQUE 约束强制实施列集中值的惟一性。约束强制实施列集中值的惟一性。 PRIMARY KEY 约束标识具有惟一标识表中行的值的列或列集。约束标识具有惟一标识表中行的值的列或列集。 FOREIGN KEY 约束标识并强制实施表之间的关系。约束标识并强制实施表之间的关系。284.1.3 数据完整性数据完整性 l(2) 列约束和表约束列约束和表约束 列约束指定为列定义的一部分，并且只应用于该列。列约束指定为列定义的一部分，并且只应用于该列。 表约束的声明与列定义无关，可以应用于表中多个列。当一表约束的声明与列定义

19、无关，可以应用于表中多个列。当一个约束中必须包含多个列时，必须使用表约束。个约束中必须包含多个列时，必须使用表约束。294.1.3 数据完整性数据完整性l3. 默认值默认值 如果插入行时没有为列指定值，默认值则指定列中使用什么如果插入行时没有为列指定值，默认值则指定列中使用什么值。默认值可以是计算结果为常量的任何值，例如常量、内值。默认值可以是计算结果为常量的任何值，例如常量、内置函数或数学表达式。置函数或数学表达式。l4. 规则规则 一个列只能应用一个规则，但可以应用多个一个列只能应用一个规则，但可以应用多个 CHECK 约束。约束。CHECK 约束被指定为约束被指定为 CREATE TAB

20、LE 语句的一部分，而语句的一部分，而规则是作为单独的对象创建，然后绑定到列上。规则是作为单独的对象创建，然后绑定到列上。CREATE RULE range_rule AS range= 2 AND range =18 or AGE=25)l外键外键 CONSTRAINT c_name FOREIGN KEY CLUSTERED | NONCLUSTERED (column 1, 列列 2,) REFERENCES table_name(column 1, 列列 2,) ON DETELE NO ACTION|CASCADE|SET NULL|SET DEFAULT ON UPDATE NO

21、ACTION|CASCADE|SET NULL|SET DEFAULT 如在如在s_c表中：表中： FOREIGN KEY (SNO）REFERENCES student(SNO), FOREIGN KEY (CNO）REFERENCES course(CNO),364.1.4 数据表的创建数据表的创建 lON DELETE NO ACTION | CASCADE | SET NULL | SET DEFAULT 指定如果已创建表中的行具有引用关系，并且被引用行已从指定如果已创建表中的行具有引用关系，并且被引用行已从父表中删除，则对这些行采取操作。默认值为父表中删除，则对这些行采取操作。默认值

22、为 NO ACTION。 NO ACTION 数据库引擎将引发错误，并回滚对父表中数据库引擎将引发错误，并回滚对父表中相应行的删除操作。相应行的删除操作。 CASCADE 如果从父表中删除一行，则将从引用表中删如果从父表中删除一行，则将从引用表中删除相应行。除相应行。 SET NULL 如果父表中对应的行被删除，则组成外键的如果父表中对应的行被删除，则组成外键的所有值都将设置为所有值都将设置为 NULL。 SET DEFAULT 如果父表中对应的行被删除，则组成外如果父表中对应的行被删除，则组成外键的所有值都将设置为默认值。键的所有值都将设置为默认值。 374.1.4 数据表的创建数据表的创建

23、 lON UPDATE NO ACTION | CASCADE | SET NULL | SET DEFAULT 指定如果表中发生更改的行有引用关系，指定如果表中发生更改的行有引用关系，并且被引用行在父表中已更新，则这些行将发生什么操作。并且被引用行在父表中已更新，则这些行将发生什么操作。默认值为默认值为 NO ACTION。 NO ACTION 数据库引擎将引发错误，并回滚对父表中相数据库引擎将引发错误，并回滚对父表中相应行的更新操作。应行的更新操作。 CASCADE 如果在父表中更新了一行，则将在引用表中更如果在父表中更新了一行，则将在引用表中更新相应的行。新相应的行。 SET NULL

24、如果更新了父表中的相应行，则会将构成外键如果更新了父表中的相应行，则会将构成外键的所有值设置为的所有值设置为 NULL。若要执行此约束，外键列必须可为。若要执行此约束，外键列必须可为空值。空值。 SET DEFAULT 如果更新了父表中的相应行，则会将构成如果更新了父表中的相应行，则会将构成外键的所有值都设置为其默认值。外键的所有值都设置为其默认值。 384.1.4 数据表的创建数据表的创建 l语法394.1.5 表间关系的类型表间关系的类型实体用表实现，实体间的关系也用表实现，实体间的实体用表实现，实体间的关系也用表实现，实体间的关系有关系有3中情形：中情形：一对一关系：一对一关系：一种实体

25、型中的一个实体对应另一种实一种实体型中的一个实体对应另一种实体型中的一个实体，即表中的一条记录对应另一个体型中的一个实体，即表中的一条记录对应另一个表的一条记录。如病人与病床。表的一条记录。如病人与病床。一对多关系：一对多关系：一种实体型中的一个实体对应另一种实一种实体型中的一个实体对应另一种实体型中的多个实体，即表中的一条记录对应另一个体型中的多个实体，即表中的一条记录对应另一个表的多条记录。如班级与学生。表的多条记录。如班级与学生。多对多关系：多对多关系：一种实体型中的多个实体对应另一种实一种实体型中的多个实体对应另一种实体型中的多个实体，即表中的多条记录对应另一个体型中的多个实体，即表中

26、的多条记录对应另一个表的多条记录。如课程与学生形成的选课关系。表的多条记录。如课程与学生形成的选课关系。404.1.6 特殊类型表特殊类型表 l除了基本用户定义表的标准角色以外，除了基本用户定义表的标准角色以外，SQL Server 2005 还提供了下列类型的表，这些表在数还提供了下列类型的表，这些表在数据库中起着特殊的作用。据库中起着特殊的作用。l1. 临时表临时表 临时表有两种类型：本地表和全局表。在与首次创临时表有两种类型：本地表和全局表。在与首次创建或引用表时相同的建或引用表时相同的 SQL Server 实例连接期间，实例连接期间，本地临时表只对于创建者是可见的。当用户与本地临时表

27、只对于创建者是可见的。当用户与 SQL Server 实例断开连接后，将删除本地临时表。全实例断开连接后，将删除本地临时表。全局临时表在创建后对任何用户和任何连接都是可见局临时表在创建后对任何用户和任何连接都是可见的，当引用该表的所有用户都与的，当引用该表的所有用户都与 SQL Server 实例实例断开连接后，将删除全局临时表。断开连接后，将删除全局临时表。414.1.6 特殊类型表特殊类型表 l2. 系统表系统表 SQL Server 将定义服务器配置及其所有表的数据存储在一将定义服务器配置及其所有表的数据存储在一组特殊的表中，这组表称为系统表。除非通过专用的管理员组特殊的表中，这组表称为

28、系统表。除非通过专用的管理员连接连接(DAC，只能在，只能在 Microsoft 客户服务的指导下使用客户服务的指导下使用)，否则用户无法直接查询或更新系统表。否则用户无法直接查询或更新系统表。 例：视图例：视图sysobjects，在，在SQL SERVER 2000中以表的形中以表的形式提供给用户式提供给用户424.2 数据表的修改数据表的修改* l4.2.1 查看数据表查看数据表l4.2.2 修改数据表修改数据表l4.2.3 删除数据表删除数据表* 对用对用SQL语句实现数据表查看修改不做要求语句实现数据表查看修改不做要求 434.2.1 查看数据表查看数据表 l在数据库中创建表之后，可

29、能需要查找有关表属性的信息在数据库中创建表之后，可能需要查找有关表属性的信息(例如，列的名称、数据类型或其索引的性质例如，列的名称、数据类型或其索引的性质)，还可以显示，还可以显示表的依赖关系来确定哪些对象表的依赖关系来确定哪些对象(如视图、存储过程和触发器如视图、存储过程和触发器)是由表决定的。在更改表时，相关对象可能会受到影响。是由表决定的。在更改表时，相关对象可能会受到影响。l【例例4.2】返回有关所有对象的信息。返回有关所有对象的信息。l单击工具栏上单击工具栏上【新建查询新建查询】按钮，输入以下代码：按钮，输入以下代码：lUSE master;lGOlEXEC sp_help;lGOl

30、然后单击工具栏中的然后单击工具栏中的【执行执行】按钮，结果如图按钮，结果如图4.10所示。所示。444.2.1 查看数据表查看数据表 l图图4.10 查询结果查询结果 454.2.1 查看数据表查看数据表 l【例例4.3】返回教学数据库中学生表的信息。返回教学数据库中学生表的信息。 USE teaching; GO EXEC sp_help student; (EXEC sp_help student;) GO 结果如图结果如图4.11所示。所示。 464.2.1 查看数据表查看数据表 l图图4.11 查询结果查询结果 474.2.2 修改数据表修改数据表 l1. 使用图形工具修改数据表使用图形工具修改数据表 在对象资源管理器中，右击要向其添加列的表，再选择在对象资源管理器中，右击要向其添加列的表，再选择【修修改改】菜单项，进入表设计器，在表设计器中可以作以下修改。菜单项，进入表设计器，在表设计器中可以作以下修改。l(1) 添加列和其他对象添加列和其他对象 A