第3章_数据定义与完整性约束的实现

1、数据定义与完整性约束讲解：许志英 1.SQL 的表定义和完整性定义功能 2.SQL数据操作与完整性约束的作用SQL的数据定义功能 基本表的定义 视图的定义 索引的定义CREATE TABLEDROP TABLEALTER TABLE CREATE VIEWDROP VIEWCREATE INDEXDROP INDEX基本表定义：CREATE TABLE ( , ) 数据类型 为列指定数据类型及其数据宽度； 关系数据库支持非常丰富的数据类型，不同的数据库管理系统支持的数据类型基本是一样的，右表列出了常用的数据类型。 列级完整性约束 用于定义列或字段一级的完整性约束，一般包括： NOT NULL和

2、NULL约束 PRIMARY KEY约束 UNIQUE约束 FOREIGN KEY约束 DEFAULT定义 CHECK约束 表级完整性约束表级完整性约束 用于定义表一级的完整性约束，一般包括：PRIMARY KEY约束（复合属性构成的主关键字说明）FOREIGN KEY约束（外部关键字及参照关系说明）CHECK约束（同时涉及到多个属性的域完整性约束） 其它参数其它参数 不是SQL的标准选项，一般用于与物理存储有关的说明，不同的数据库管理系统定义的方式肯定不同，另外该项参数一般也不是必需的。 建立表的例子 仓库仓库职工职工供应商供应商订购单订购单使用“仓储订货”数据库 create databa

3、se 仓储订货 on ( name =order_dat, filename=F:dataorder_dat.mdf, size=3mb, maxsize=300mb, filegrowth=5% ) log on ( name=order_log, filename=F:dataorder_log.ldf, size=3mb, maxsize=300mb, filegrowth=5% )例：建立仓库表CREATE TABLE 仓库( 仓库号 CHAR(6) PRIMARY KEY, 城市 CHAR(10), 面积 INT CHECK (面积 0) )例：建立职工表CREATE TABLE 职

4、工( 仓库号 CHAR(6) FOREIGN KEY REFERENCES 仓库, 职工号 CHAR(5) PRIMARY KEY, 工资 INT CHECK (工资 = 1000 AND 工资 = 1000 AND 工资 = 5000) DEFAULT 1200,PRIMARY KEY (仓库号,职工号)修改表结构命令ALTER TABLE ADD | DROP | DROP COLUMN | ALTER COLUMN 增加新的属性（字段） 往订购单关系中增加一个新属性“完成日期”ALTER TABLE 订购单 ADD 完成日期 DATETIME NULL修改属性的定义将订购单关系的完成日期

5、属性的数据类型修改为SMALLDATETIME ALTER TABLE 订购单 ALTER COLUMN 完成日期 SMALLDATETIME NULL 删除完整性约束 建立的列级或表级完整性约束可以删除，但是前提是在定义完整性约束时必须给出约束名称，否则不能删除。 设有：CREATE TABLE 职工(仓库号 CHAR(5) FOREIGN KEY (仓库号) REFERENCES 仓库(仓库号),职工号 CHAR(5) PRIMARY KEY,工资 INT CONSTRAINT salary CHECK (工资 = 1000 AND 工资 = 5000)DEFAULT 1200 )则可以：

6、 ALTER TABLE 职工 DROP CONSTRAINT salary删除属性 删除订购单关系中的“完成日期”属性：ALTER TABLE 订购单 DROP COLUMN 完成日期注意：有些系统的ALTER TABLE命令不允许删除属性，如果必须要删除属性，一般步骤是：先将旧表中的数据备份，然后删除旧表、并建立新表，最后将原来的数据恢复到新表中。 删除表命令格式：DROP TABLE 例：DROP TABLE 订购单数据操作与数据完整性 插入操作 删除操作 更新操作插入操作 SQL的插入语句是INSERT： INSERT INTO (,) VALUES(,) 插入仓库元组 仓库号 CHA

7、R(5) PRIMARY KEY 城市 CHAR(10) 面积 INT CHECK (面积 0) 实体完整性 用户定义完整性插入职工元组 仓库号 CHAR(5) FOREIGN KEY REFERENCES 仓库, 职工号 CHAR(5) PRIMARY KEY, 工资 INT CHECK (工资 = 1000 AND 工资 = 5000) DEFAULT 1200 实体完整性 参照完整性 用户定义完整性插入供应商元组 供应商号 CHAR(5) PRIMARY KEY 供应商名 CHAR(20) 地址 CHAR(20) 实体完整性插入订购单元组 职工号 CHAR(5) NOT NULL FOR

8、EIGN KEY REFERENCES 职工 供应商号 CHAR(5) NULL FOREIGN KEY REFERENCES 供应商 订购单号 CHAR(5) PRIMARY KEY 订购日期 DATETIME DEFAULT getdate() 实体完整性 参照完整性 用户定义完整性数据操作与数据完整性 插入操作 删除操作 更新操作删除操作 SQL的删除语句是DELETE： DELETE FROM FROM WHERE DELETE命令从指定的表中删除满足“逻辑表达式”条件的元组； 如果没有指定删除条件则删除表中的全部元组，所以在使用该命令时要格外小心； 删除的条件可以与其他的表相关（使用

9、可选的FROM指定）； DELETE命令只删除元组，它不删除表或表结构。删除订购单记录z不检查数据完整性删除供应商记录 检查参照完整性 空值删除 拒绝删除 级联删除z思考题如何实现删除操作时的数据完整性控制？删除职工记录 检查参照完整性 拒绝删除 级联删除z思考题如何实现删除操作时的数据完整性控制？数据操作与数据完整性 插入操作 删除操作 更新操作更新操作 SQL的更新语句是UPDATE： UPDATE SET = , = FROM WHERE UPDATE更新满足“逻辑表达式”条件的记录； 一次可以更新多个属性的值； 更新的条件可以与其他的表相关（使用FROM指定）； 如果没有指定更新条件则

10、更新表中的全部记录。查询与数据操作 将一个查询结果插入到指定的表 更新条件与另一个表相关联 删除条件与另一个表相关联插入操作 插入一个查询结果：INSERT INTO (,) 举例： INSERT INTO 订购单备份 SELECT * FROM 订购单更新操作 UPDATE SET = , = FROM WHERE z UPDATE更新满足“逻辑表达式”条件的记录；z 一次可以更新多个属性的值； z 更新的条件可以与其他的表相关（使用FROM指定）； z 如果没有指定更新条件则更新表中的全部记录。 更新操作举例 给“武汉”仓库的职工提高10%的工资 UPDATE 职工 SET 工资 = 工资

11、*1.10FROM 仓库WHERE 仓库.仓库号 = 职工.仓库号 AND 城市=武汉 删除操作 DELETE FROM FROM WHERE z DELETE命令从指定的表中删除满足“逻辑表达式”条件的元组；z 如果没有指定删除条件则删除表中的全部元组，所以在使用该命令时要格外小心； z 删除的条件可以与其他的表相关（使用可选的FROM指定）； z DELETE命令只删除元组，它不删除表或表结构。 删除操作举例 删除所在城市是上海的仓库的所有职工元组。 DELETE FROM 职工 FROM 仓库 WHERE 仓库.仓库号=职工.仓库号 AND 城市=上海几点注意 在执行插入、更新和删除操作

12、时可能会受到关系完整性的约束，这种约束可以保证数据库中的数据是正确的。 虽然视图可以像基本表一样进行各种查询，但是插入、更新和删除操作在视图上却有一定限制。因为视图是由基本表导出的，对视图的任何操作最后都落实在基本表上，这些操作不能违背定义在表上的完整性约束。 可以利用游标（Cursor）对表进行删除和更新操作，这部分内容将在稍后介绍。 索引定义的命令格式CREATE UNIQUE CLUSTERED INDEX ON ( ASC | DESC , ASC | DESC ) 普通索引普通索引 唯一（唯一（UNIQUE）索引）索引 聚集（聚集（CLUSTERED）索引）索引 普通索引 如果没有指

13、定UNIQUE或CLUSTERED等将建立普通索引。 在单个字段上建立普通索引：CREATE INDEX sup_idx ON 订购单(供货方)在多个字段上建立普通索引：CREATE INDEX sup_emp_idx ON 订购单(供货方,经手人 DESC)唯一索引唯一索引 通过指定UNIQUE则为表创建唯一索引（不允许存在索引值相同的两个元组）。 在CREATE TABLE命令中的UNIQUE约束将隐式创建唯一索引。 在仓库关系的城市属性上建立一个唯一索引： CREATE UNIQUE INDEX city_idx ON 仓库(城市) 聚集索引聚集索引 z通过指定CLUSTERED建立聚集

14、索引。（索引值与关系中元组的顺序物理相同 ）。 z在CREATE TABLE命令中的PRIMARY KEY约束将隐式创建聚集索引。 z一个表只允许建立一个聚集索引。 z如果在创建表时已经指定了主关键字，则不可以再创建聚集索引。 使用索引的原则： 不应该在一个表上建立太多的索引（一般不超过两到三个），索引能改善查询效果，但也耗费了磁盘空间，降低了更新操作的性能，因为系统必须花时间来维护这些索引。除了为数据的完整性而建立的唯一索引外，建议在表较大时再建立普通索引，表中的数据越多，索引的优越性才越明显。索引的删除命令格式： DROP INDEX 例：DROP INDEX 订购单.sup_emp_id

15、x视图视图的概念和作用视图的建立通过视图进行查询通过视图进行更新仓库号 城市 面积仓库号 职工号 工资仓库号 城市 职工号 工资仓库号 面积基本表视图视图的概念和作用 三层结构之外部层 关系 从基本表派生虚拟表 可以源自单个表或多个表 可以包含虚拟列 用SQL SELECT语句定义视图的概念和作用 增强了数据独立性概念数据独立性 安全屏蔽敏感数据 简化应用定义视图的命令 视图是根据对基本表的查询定义的，其命令格式如下：CREATE VIEW AS WITH CHECK OPTION通过视图操作的数据是否满足定义视图时的条件做检查例：定义视图emp_v1,使之只包含职工基本表的职工号、仓库号和姓

16、名字段。 create view emp_v1 as select 职工号,仓库号,姓名 from 职工insert into emp_v1 values(e13,WH1,郭天华)例：定义视图wh_v1,使之只包含城市在北京的仓库号和面积信息。 Create view wh_v1 as Select 仓库号，面积 From仓库 Where 城市北京Insert into wh_v1 values (WH9,777)基于多个表的视图 定义视图 wh_emp_v1，使之包含仓库号、城市、职工号和职工姓名信息。 create view wh_emp_v1 as select 仓库.仓库号,城市,职工号,姓名 from 职工 join 仓库 on 职工.仓库号 =仓库.仓库号insert into wh_emp_v1 values(wh11,杭州,E13,海燕)