第5章 创建和维护数据库表

1、第第5章章 创建和维护数据创建和维护数据 库表库表 计算机系 胡骏 5.1 定义表结构定义表结构 l定义表：指表中应该包含有哪些字段 字段的数据类型 字段宽度 该表与哪些其他表产生关系（外键） 定义表需要明确的任务定义表需要明确的任务 这个表包含的数据的类型 表中需要设置哪些字段 哪些字段应确定为主键或外键 字段是否为空 是否使用约束 是否需要建立索引 5.2 创建与管理表结构创建与管理表结构 lSQL Server 2000中，一个数据库中最多可以 创建20亿个表，每个表最多可以定义1024个 字段，每行最多可以存储8060个字节。 5.2.1 创建表结构创建表结构 1.使用企业管理器创建

2、略 2.使用T-SQL语言创建 CREATE TABLE 学生 ( 学号 char(10) NOT NULL PRIMARY KEY, 姓名 char(8) NULL, 性别 char(2) NULL, 年龄 int NULL, 系 varchar(20) NULL ) 5.2.2 重命名表重命名表 1.使用企业管理器 略 2.使用存储过程sp_rename Sp_rename old_table_name, new_table_name 5.2.2 重命名表重命名表 例：把数据库中电子商务成绩表名称改为 dzswcjb Exec sp_rename 电子商务成绩表, dzswcjb Sp_r

3、ename除了可以修改表对象以外还可以修 改其他对象。 例：修改视图的名称 exec sp_rename 电子商务成绩视图, 视图 Sp_rename 用法扩展用法扩展 还可以修改表中字段的名称以及数据库的名称。 例：把pubs数据库中图书表的第一个字段的名 称“图书编号”修改为“编号” sp_rename 图书编号, 编号, column 例：把TSGL数据库的名称改为“图书” sp_rename TSGL, 图书, database 5.2.3 修改表字段修改表字段 1.使用企业管理器修改表字段 右击要修改的表选择设计表设计表 2.使用T-SQL语句 ALTER TABLE table A

4、DD COLUMN column_name datatype| ALTER COLUMN table column_name newdatatype| DROP COLUMN column_name 5.2.3 修改表字段修改表字段 1.给读者表增加一个年龄字段 ALTER TABLE 读者 ADD COLUMN 年龄 int null 2.改变读者表中备注的数据类型，变为可变字符型，长 度为200 ALTER TABLE 读者 ALTER COLUMN 备注 VARCHAR(200) 3.删除读者表中地址字段 ALTER TABLE 读者 DROP COLUMN 地址 注意：注意： 具有下列

5、特征的列不能被删除： （1）用于索引； （2）用于CHECK、FOREIGN KEY、UNIQUE 或PRIMARY KEY约束； （3）与DEFAULT 定义关联或绑定到某一默认 对象； （4）绑定到规则； 5.3.1 查看表查看表 1.查看表属性 右击要查看的表选 择“设计表” 使用SP_HELP exec sp_help 图书 5.3.1 查看表查看表 2.查看数据表中的数据 使用企业管理器查看 返回所有行：显示全部记录 返回首行：相当于SELECT命 令中的TOP参数 查询：类似于ACCESS中查询 界面 查询按钮查询按钮 使用使用T-SQL语言查看语言查看 使用T-SQL语言查看 注

6、意: 若两个用户都定义了“读者”表 select * from sa.读者 select * from bb.读者 若在两个数据库的中两个表名称一样 select * from pubs.读者 select * from tsgl.读者 5.3.2 向表中添加记录向表中添加记录 1.使用企业管理器向表中添加记录 略 2.使用T-SQL语言向表中添加记录 使用使用T-SQL语言向表中添加记录语言向表中添加记录 Insert into table_name (column_list) Values(default | null |expression) Default 参数只有在对应字段设定默认约

7、束才 能使用 INSERT的使用的使用 INSERT命令一般形式大家都会使用，在这里要介绍 的是批量插入数据的问题。 假设A表已经存在，现向A表中插入B表数据 INSERT INTO A SELECT * FROM B 假设A表已经存在，现向A表中插入B表某些想要的 数据 INSERT INTO A SELECT * FROM B WHERE INSERT中中DEFAULT参数的使用参数的使用 DEFAULT指的是向表中插入列定义的默认值， 也叫插入默认约束值 INSERT中中DEFAULT参数的使用参数的使用 在向上述表中添加记录的时候可以这样写： INSERT INTO 系部老师 VALU

8、ES(0305,NULL,NULL,NULL,DEFAULT) 系统会自动在国籍这个字段添加“中国” 5.3.4 修改表中的记录修改表中的记录 1.使用企业管理器 2.使用T-SQL语言 UPDATE table_name SET column_name=expression | default | null WHERE 5.3.5 删除表删除表 1.使用企业管理器 2.使用T-SQL语言删除 略 5.4 数据库中数据的完整性数据库中数据的完整性 l数据完整性是指：数据库在运行时，应防止输 入或输出不符合语义的错误数据，始终保持数 据的正确性。 l数据的完整性包含有以下几个重要特征。 数据类型

9、准确 数据的值满足范围 同一表格的数据不存在冲突 多个表格的数据不存在冲突 5.4.1 完整性概述完整性概述 1.实体完整性 不允许输入重复的记录 实现方法：设置索引、唯一性约束、主键 2.域完整性 保证数据库不包含无意义或不合理的值。 实现方法：默认值约束、检查约束、外键约束 5.4.1 完整性概述完整性概述 3.引用完整性 保证不同表之间的数据的合理性、一致性。 实现方法：外键约束。 4.用户自定义完整性 5.4.2 实现数据库中数据的完整性实现数据库中数据的完整性 l综上，实现数据完整性的方法就是使用约束。在SQL Server中的约束有如下几种： 1.主键约束 2.唯一性约束 3.检查

10、性约束 4.外键约束 5.默认约束 6.空值约束 7.级联引用完整性约束 5.4.3 主键约束主键约束 1.使用企业管理器设置主键约束 2.使用T-SQL语言设置主键约束 语法格式如下： CONSTRAINT constraint_name PRIMARY KEY CLUSTERED | NONCLUSTERD 注意注意1 CLUSTERED | NONCLUSTERED：分别为聚 集索引和非聚集索引 默认的时候主键字段上就是聚集索引 设定唯一性约束的时候，相应字段就是非聚 集索引 设定某个字段为主键约束设定某个字段为主键约束 CREATE TABLE 读者 ( 借书证号 int not nu

11、ll, 姓名 char(10) not null, 性别 char(10) not null, constraint 读者表约束 primary key clustered(借书证号) ) 注意注意2 l使用SQL语言创建表的时候，如果要指定多列 为主键语法如下： CREATE TABLE 学生 (学号 char(10) not null, 身份证号 char(10) not null primary key (学号,身份证号) ) 为已存在的表添加主键为已存在的表添加主键 ALTER TABLE 读者 ADD CONSTRIANT 约束 PRIMARY KEY CLUSTERED(借书证号)

12、 5.4.4 唯一性约束唯一性约束 UNIQUE约束（唯一性约束）用于指定一个劣 质或者多个列的组合值具有唯一性，以防止重 复值的输入。 5.4.4 唯一性约束唯一性约束 l使用唯一性约束要注意以下几个问题 唯一性约束用于非主键列（一列或多列） 一个表可以设置多个UNIQUE约束，而只能 设置一个主键 使用唯一性约束允许有空值 注意注意 l此处把主键排开，虽然 三人名称一样，但是每 个人的相关信息不同， 把三列组合起来就能唯 一区分每个人的相关信 息。 使用企业管理器创建唯一性约束使用企业管理器创建唯一性约束 使用使用T-SQL语言创建唯一性约束语言创建唯一性约束 CREATE TABLE 学

13、生 ( 学号 int, 姓名 char(10) 身份证号 char(10) Constraint PK_xuehao primary key(学号) Constraint UK_shenfen UNIQUE(身份证号) ) 添加唯一性约束添加唯一性约束 ALTER TALBE 读者 ADD Constraint uk_xingming UNIQUE (姓名) 5.4.5 检查性约束检查性约束 l强制约束输入的数据在 某个范围之内。 5.4.5 检查性约束检查性约束 使用使用T-SQL创检查约束创检查约束 CONSTRAINT constraint_name Check 例子见书P105 5.4

14、.6 外键约束外键约束 l外键主要维护两个表之间的一致性。 l外键的就是通过将一个表中的主键所在的列包 含到另一个表中，这个列就是另一个表的外键。 5.4.6 外键约束外键约束 1.使用企业管理器创建外 键 使用使用T-SQL语言创建外键约束语言创建外键约束 Constraints constraints_name Foreign key (column_name) references ref_name 5.4.7 默认约束默认约束 略 查看约束的定义查看约束的定义 l使用系统存储过程查 看约束信息 第第5章补充知识：实现数据章补充知识：实现数据 完整性的另外三种方法完整性的另外三种方法 计

15、算机系 胡骏 规则规则 l规则的作用也是用来限 制取值范围的所以是 为了实现域完整性 使用企业管理器规则使用企业管理器规则 使用企业管理器规则使用企业管理器规则 使用企业管理器规则使用企业管理器规则 l创建好规则后，可双击 该规则，打开规则属性 窗口，完成添加后，点 击应用，确定即可。而 要解绑，则先从右边选 取某一列，然后点击删 除就解绑了 使用企业管理器规则使用企业管理器规则 使用使用T-SQL语言创建规则语言创建规则 CREATE RULE 规则名 AS 表达式 CREATE RULE 性别规则 AS 性别 in(男,女) CREATE RULE 出生日期规则 AS 出生日期 =1976

16、-01-01 and 出生日期=getday() 使用使用T-SQL语言绑定规则语言绑定规则 格式：EXEC sp_bindrule 规则名,表名.字段名 例子：EXEC sp_bindrule 性别规则, 学生基本 情况表.性别 使用使用T-SQL语言解绑规则语言解绑规则 格式：EXEC sp_unbindrule 表名.字段名 例子：EXEC sp_unbindrule 学生基本情况表.性 别 练习：练习： 使用T-SQL语言创建规则：使得系部代号在1， 2，3之中，然后绑定此规则到“专业.系部代 号”，最后解绑规则。 规则与规则与CHECK约束的联系与区别约束的联系与区别 lCHECK

17、约束比规则更简明，一个列只能应用一个规 则，但是却可以应用多个 CHECK 约束。CHECK约 束可作为CREATE TABLE 语句的一部分进行指定， 而规则以单独的对象创建，然后绑定到列上。 l两个的性能是相同的，只是CHECK约束比规则优先 级别高些 级联引用完整性约束级联引用完整性约束 l通过级联引用完整性约束，可以使删除或更新 外键所指向的键时，级联删除或更新外键所在 的行。在CREATE TABLE 和 ALTER TABLE 语句的 REFERENCES 子句中，可以定义级 联引用完整性约束。 级联引用完整性约束级联引用完整性约束 使用使用T-SQL实现级联引用完整性实现级联引用

18、完整性 约束约束 实现语法： ON DELETE CASCADE | NO ACTION 能够实现两个建立起外键的表中某一个表删除 一个数据，另一个表同时删除此数据 ON UPDATE CASCADE | NO ACTION 能够实现两个建立起外键的表中某一个表更新 数据，另一个表同时更新。 使用使用T-SQL实现级联引用完整性实现级联引用完整性 约束约束 ALTER TABLE 借阅（ ADD CONSTRAINT 约束1 FOREIGN KEY （借书证号）REFERENCES 读者（借书证号） ON DELETE CASCADE ON UPDATE CASCADE 标识列标识列 Identity l用标识列可以实现数据完整性。标识列可用 IDENTITY 属性建立，使应用程序开发人员得 以对表中所插入的第一行指定标识数字 （Identity Seed 属性），并确定要添加到种子 上的增量（Ident