第十章 数据库表

数据库表 10.1 创建数据库表 10.2 修改数据库表 10.3 使用约束 10.4 建立索引 10.5 删除数据库表

10.1 创建数据库表 数据库表的形式

创建数据库表的指令格式 CREATE TABLE 表名 例： （列名1 数据类型 { identity |NOT NULL| NULL }, … ） 例： Create table demotable (user_id numeric(10,0) identity, Name varchar(30) not NULL, Comments varchar(100) null)

10.1.1 表名 表名是惟一的 可以使用中文，也可以使用西文符号 最多64个字符 建立临时表 临时表的表名必须以#打头 10.1.1 表名 表名是惟一的 可以使用中文，也可以使用西文符号 最多64个字符 建立临时表 临时表的表名必须以#打头 仍然使用CREATE TABLE命令 例:Create table #demotable (user_id numeric(10,0) identity, Name varchar(30) not NULL)

10.1.2 列名 表中的每一列也称为字段，所以列名也叫字段名 字段名的命名方式基本同数据表名的命名 10.1.2 列名 表中的每一列也称为字段，所以列名也叫字段名 字段名的命名方式基本同数据表名的命名 字段名在数据表中要求是惟一的，但在整个数据库中可以重名

NULL：可以无值，默认值 NOT NULL：必须输入值 identity：计数器 每个表只能有一个identity列 不允许为空 使用整型或精确数型，受数据类型取值限制 用户不能修改identity的值

例10-1 确定期刊采编系统数据库中各数据表的表名 关系模式名 数据库表名 部门 mag_dept 人员 mag_emp 期刊 mag_info 稿件 mag_doc

10.1.3 数据类型 T-SQL中支持的、常用的数据类型 字符型 日期时间类型 数值类型 二进制型 位型 文本型 图像型 时间戳型

字符型 字符型变量应定义长度 允许定义的最大长度是255 有两种 输入字符型数据时必须用单引号将数据括起来 固定长度的char( )类型 可变长度的varchar( )类型 输入字符型数据时必须用单引号将数据括起来

日期时间类型 datetime和smalldatetime是用来存储日期和时间数据 比较内容 Datetime Smalldatetime 最小值 Jan 1,1753 Jan 1,1900 最大值 Dec 31,9999 Jun 6,2079 占用存储空间 8byte 4byte 精度 3.33毫秒 1分钟

整数类型 整数类型的比较 比较内容 Int Smallint Tinyint 最小值 -231 -215 最大值 231-1 215-1 最大值 231-1 215-1 255 占用存储空间 4byte 2byte 1byte

浮点数类型 比较内容 Float Real 最小值 -1.79E+308 -3.40E+38 最大值 1.79E+308 3.40E+38 占用存储空间 8Byte 4Byte 精度 最多15位 最多7位

精确数类型 精确数类型有两种形式 Decimal Numeric 与浮点类型数据的区别 可以自定义精度的位数 例 num_col(5,2)

货币型 货币类型的比较 比较内容 Money Smallmoney 最小值 -214,748.3648 最大值 214,748.3647 -922,337,203,685,477.5808 -214,748.3648 最大值 922,337,203,685,477.5807 214,748.3647 占用存储空间 8Byte 4Byte 精度 小数点后4位

位数据类型 位数据类型bit是一种逻辑数据类型 只有1和0两种数值 一般常用作true/false使用 定义bit列时，不允许为NULL，也不能建立索引 多个bit列可占用一个字节

文本型和图像型 文本类型text 图像类型image 每行可以存储2GB的二进制大型对象

时间戳型 时间戳型表示Timestamp 由系统自动赋值的一个计数器数据 记录了数据行的操作顺序 时间戳列的数据保持惟一，它可以惟一标识表中的列 时间戳列的值实际来自于事务日志

例10-2 确定期刊采编系统数据库中各数据表中字段的字段名和数据类型 数据表Mag_dept

数据表mag_emp

数据表Mag_info

数据表mag_doc

例10-3 建立期刊采编系统 数据库中各数据表 创建mag_dept表

创建mag_emp表

创建mag_info表

创建mag_doc表

10.2 修改数据库表 添加或删除列 修改列的属性 更改列名和表名

10.2.1 添加或删除列 增加列的语句格式 Alter table 表名 add 列名 数据类型 10.2.1 添加或删除列 增加列的语句格式 Alter table 表名 add 列名 数据类型 { identity |NOT NULL| NULL }，… 删除列的语句格式 drop column 列名

例10-4 向数据表mag_emp中添加一新属性ID（身份证号码），数据类型为字符型，固定长度18

例10-5 删除数据表mag_emp中属性ID

10.2.2 修改列的属性 命令格式 alter table 表名 alter column列名 数据类型 10.2.2 修改列的属性 命令格式 alter table 表名 alter column列名 数据类型 { identity |NOT NULL| NULL }

例10-6 将数据表mag_info中PubDate的数据类型更改为smalldatetime

10.2.3 更改列名和表名 更改列名 sp_rename ‘表名.原列名’, 新列名 更改表名 sp_rename 原表名 新表名

例10-7 数据库usedb1中建有数据表test，包含number、stu_name和class三个字段，请将class更名为class_no

例10-8 将数据库usedb1中数据表test更名为student

10.3 使用约束 约束的作用 实现数据的完整性 实体完整性 参照完整性 用户自定义完整性 约束的类型 建立约束 删除约束

参照完整性 关系DEPT 关系S 学号 姓名 S256 S257 S258 王丹 章华 李力 性别 系号 男 女 D23 D25 D30 系名 负责人 物理 机械 计算机 王娟 杨华 张天 关系S 关系DEPT

10.3.1 约束的类型 名称 作用 实现的完整性 Primary key 定义主键,保证主键列无重复值 实体完整性 Unique 10.3.1 约束的类型 名称 作用 实现的完整性 Primary key 定义主键,保证主键列无重复值 实体完整性 Unique 保证该列无重复值 Foreign key 定义外键,保证数据表间数据的一致性 参照完整性 Check 定义表中某些列的数据范围 自定义完整性 Default 为列的数据提供默认值

10.3.2 建立约束 定义约束时使用Create table语句 或使用Alter table语句 10.3.2 建立约束 定义约束时使用Create table语句 或使用Alter table语句 即可以在定义数据表的数据列时直接定义约束，也可以对已定义的数据表添加约束

使用Alter table语句添加约束的基本格式 Add [Constraint 约束名] 约束定义

使用Create table语句建立约束 Create table 表名 （列名1 数据类型 { identity |NOT NULL| NULL } [Constraint 约束名] 约束定义, 列名2 数据类型 { identity |NOT NULL| NULL } … [Constraint 约束名] 约束定义）

PRIMARY KEY 定义主键约束的基本格式为： [Constraint 约束名] primary key (列名1[，列名2，…])

例10-9 向数据表map_dept中 添加主键约束

例10-10 在数据库usedb2中建立数据表map_dept，同时定义主键约束

例10-11 在数据库usedb1中建立数据表grade，表中包括学号sno（char）、课程号cno（char）、成绩gmark（numeric）属性 主键由学号和课程号构成

UNIQUE 定义数据表中非主键的列在各行记录中不能出现相同的非空值 定义格式： [Constraint 约束名]

例10-12 向数据库usedb2中数据表mag_dept属性DepManager增加UNIQUE约束

FOREIGN KEY [Constraint 约束名] 定义外键的命令基本格式 [Constraint 约束名] foreign key (列名1[，列名2，…]) references 表名(列名1[，列名2，…])

例10-13 向数据库MagDb中数据表mag_emp的属性DepId添加 外键约束

例10-14 在数据库usedb2中建立数据表mag_emp，同时定义主键和外键

CHECK 定义约束check的命令基本格式 [Constraint 约束名] check（逻辑表达式）

例10-15 将数据库MagDb中数据表mag_emp的属性EmpAge取值范围限定在1-100之间（含1和100），并将此约束命名为ck_age

DEFAULT 定义约束default的命令基本格式为 [Constraint 约束名] default 常量表达式 for 字段名

例10-16 将数据库MagDb中数据表mag_emp的属性EmpRole的默认值指定为“编辑”

10.3.3 删除约束 删除约束的语句格式 Alter table 表名 Drop Constraint 约束名

例10-17 删除数据库usedb2中数据表mag_dept属性DepManager的UNIQUE约束

10.4 建立索引 索引的类型 建立索引 设计索引 删除索引

10.4.1 索引的类型 聚集索引（clustered） 非聚集索引（nonclustered） 是一种物理存储方式 10.4.1 索引的类型 聚集索引（clustered） 是一种物理存储方式 一个数据表只能建立一个聚集索引 非聚集索引（nonclustered） 是一种逻辑存储方式 索引的次序并不影响数据的物理存储顺序 一个数据表最多可以建立249个非聚集索引

10.4.2 建立索引 建立索引的语法 Create [unique] [clustered|nonclustered] 10.4.2 建立索引 建立索引的语法 Create [unique] [clustered|nonclustered] index 索引名 on 表名(列名1,列名2,…)

例10-18 对数据表mag_emp属性EmpAge建立非惟一的非聚集索引age_index

10.4.3 设计索引 创建索引时一般应遵循以下原则 经常要查找的列 经常要按顺序检索的列 经常用于多个数据表连接的列 10.4.3 设计索引 创建索引时一般应遵循以下原则 经常要查找的列 经常要按顺序检索的列 经常用于多个数据表连接的列 经常用于进行统计计算（如求极值、求和等）的列； 在查询条件中频繁使用的列 如果一个列中只有几个不同的数据值，或者被索引的列多于20个字节时，通常不应该建立索引

10.4.4 删除索引 删除索引的语法 Drop index 表名.索引名 10.4.4 删除索引 删除索引的语法 Drop index 表名.索引名 注意使用primary key约束和unique约束建立的索引不能删除

例10-19 删除索引age_index

10.5 删除数据库表 删除数据表的语法 Drop table 表名

例10-20 删除usedb2中数据表mag_dept