第5章 SQL数据定义与完整性约束的实现(试讲)

1、第第5章章 数据定义完整性约束的实现数据定义完整性约束的实现 5.1 SQL的表定义和完整性定义功能（的表定义和完整性定义功能（重点掌握重点掌握 ） 5.2 SQL数据操作与完整性约束的作用（数据操作与完整性约束的作用（重点掌握重点掌握 ） 建立数据库（回顾第建立数据库（回顾第2章）章） 建立数据库的命令建立数据库的命令 CREATE DATABASE 仓储订货仓储订货 ON ( NAME = order_data, FILENAME = d:dataorderdat.mdf, SIZE = 10, MAXSIZE = 50, FILEGROWTH = 5 ) LOG ON ( NAME =

2、order_log, FILENAME = d:dataorderlog.ldf, SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB ) 5.1 SQL的表定义和完整性定义功能的表定义和完整性定义功能 定义表及其完整性约束定义表及其完整性约束 修改表结构的命令修改表结构的命令 3 一、定义表及其完整性约束一、定义表及其完整性约束 CREATE TABLE命令的基本格式命令的基本格式 列的定义列的定义 计算列的定义计算列的定义 表级约束表级约束 4 CREATE TABLE命令的基本格式命令的基本格式-P123 一个表由若干列构成一个表由若干列构成，在，在

3、SQL Server 2005/2008中中可以可以 定义定义实列实列( (简称列，简称列，column_definition) 定义定义虚列虚列( (即计算列，即计算列，computed_column_definition) 理解：理解： CREATE TABLE table_name ( | ,.n ) 列的定义列的定义( () CREATE TABLE schema_name.table_name ( | ,.n ) column_name NULL | NOT NULL CONSTRAINT constraint_name PRIMARY KEY | UNIQUE | FOREIGN

4、KEY REFERENCES schema_name . referenced_table_name ( ref_column ) ON DELETE NO ACTION | CASCADE | SET NULL | SET DEFAULT ON UPDATE NO ACTION | CASCADE | SET NULL | SET DEFAULT | CHECK ( logical_expression ) DEFAULT constant_expression nPRIMARY KEY和和UNIQUE约束的区别？约束的区别？ 列名列名 数据类型数据类型 是否允许空值是否允许空值 主关键字约

5、束主关键字约束 唯一性约束唯一性约束 参照完整性约束参照完整性约束 域完整性约束域完整性约束 定义默认值定义默认值 计算列计算列的定义的定义 column_name AS computed_column_expression PERSISTED NOT NULL CONSTRAINT constraint_name PRIMARY KEY | UNIQUE | FOREIGN KEY REFERENCES referenced_table_name ( ref_column ) ON DELETE NO ACTION | CASCADE ON UPDATE NO ACTION | CHECK

6、( logical_expression ) CREATE TABLE table_name ( | ,.n ) 表级约束表级约束 如果某个如果某个完整性约束与完整性约束与多个列多个列相关相关，则这样，则这样 的完整性约束不能定义在单个列上，这时候的完整性约束不能定义在单个列上，这时候 就需要就需要表级表级完整性约束完整性约束。 单个列上的完整性约束也可以用表级完整性单个列上的完整性约束也可以用表级完整性 约束的形式来定义。约束的形式来定义。 在定义表时，需要先定义被参照表，然后定在定义表时，需要先定义被参照表，然后定 义参照表。义参照表。 仓储订货仓储订货数据库数据库 例例5-1：定义：定义

7、仓库仓库表表 CREATE TABLE 仓库仓库 ( 仓库号仓库号 CHAR(6) PRIMARY KEY, 城市城市 CHAR(10), 面积面积 INT CHECK (面积面积 0) ) 仓库表包含仓库表包含仓库号、城市和面积仓库号、城市和面积等等3个字段。个字段。 其中其中仓库号是主关键字仓库号是主关键字，仓库的面积值是，仓库的面积值是大于大于0 的整数。的整数。 例例5-2：定义：定义职工职工表表 11 职工表包含职工表包含仓库号、职工号、姓名、工资和班组仓库号、职工号、姓名、工资和班组 长长等等5个字段。其中个字段。其中职工号是主关键字职工号是主关键字，仓库号是外部仓库号是外部 关键

8、字关键字（参照仓库表的仓库号字段值），（参照仓库表的仓库号字段值），职工的工资职工的工资 在在1000到到5000之间之间（默认是默认是1200），班组长字段），班组长字段 值说明当前职工的班组长（直接领导）是谁（和职工值说明当前职工的班组长（直接领导）是谁（和职工 号字段的值域相同，参照本关系的职工号属性）。号字段的值域相同，参照本关系的职工号属性）。 例例5-2：定义：定义职工职工表表 CREATE TABLE 职工职工( 仓库号仓库号 CHAR(6) CONSTRAINT ref_wh FOREIGN KEY REFERENCES 仓库仓库(仓库号仓库号), 职工号职工号 CHAR(8)

9、 PRIMARY KEY, 姓名姓名 CHAR(10), 工资工资 numeric(8,2) CHECK (工资工资= 1000 AND 工资工资=0), PRIMARY KEY(仓库号仓库号,器件号器件号) 16 例例5-4：定义：定义库存库存表表 例例5-5：定义：定义供应商供应商表表 CREATE TABLE 供应商供应商( 供应商号供应商号 CHAR(5) PRIMARY KEY, 供应商名供应商名 CHAR(20), 地址地址 CHAR(20) 17 供应商表包含供应商表包含供应商号、供应商名和地址供应商号、供应商名和地址等等3个字个字 段。其中段。其中供应商号是主关键字供应商号是主

10、关键字。 例例5-6：定义：定义订购单订购单表。表。 18 订购单表包含订购单表包含订购单号、经手人、供货方、订购日期和订购单号、经手人、供货方、订购日期和 金额金额等等5个字段。其中个字段。其中订购单号是主关键字订购单号是主关键字；经手人经手人是负责是负责 该订购单的职工号，它该订购单的职工号，它参照职工关系的职工号字段参照职工关系的职工号字段，该字段，该字段 不允许为空值（强制联系）；不允许为空值（强制联系）；供货方供货方是接受该订购单的供应是接受该订购单的供应 商号，它商号，它参照供应商关系的供应商号参照供应商关系的供应商号字段，该字段可以为空字段，该字段可以为空 值（非强制联系）；值（

11、非强制联系）；订购日期的默认值是系统的当前日期订购日期的默认值是系统的当前日期。 例例5-6：定义：定义订购单订购单表。表。 CREATE TABLE 订购单订购单( 订购单号订购单号 CHAR(5) PRIMARY KEY, 经手人经手人 CHAR(8) NOT NULL foreign key REFERENCES 职工职工( 职工号职工号), 供货方供货方 CHAR(5) NULL foreign key REFERENCES 供应商供应商(供应供应 商号商号), 订购日期订购日期 DATETIME DEFAULT getdate(), 金额金额 MONEY NULL) 19 例例5-7

12、：定义：定义订购明细订购明细表。表。 20 订购明细表包含订购明细表包含订购单号、序号、器件号、单价和数量订购单号、序号、器件号、单价和数量 等等5个字段。其中主个字段。其中主关键字由订购单号和序号关键字由订购单号和序号共同构成；共同构成；订订 购单号字段参照订购单关系的订购单号购单号字段参照订购单关系的订购单号字段；字段；器件号参照器器件号参照器 件表的器件号件表的器件号字段；字段；序号为大于等于序号为大于等于1的整数；的整数；数量为大于数量为大于 等于等于0的整数。的整数。 例例5-7：定义：定义订购明细订购明细表。表。 CREATE TABLE 订购明细订购明细( 订购单号订购单号 CH

13、AR(5) FOREIGN KEY REFERENCES 订购订购单单 ON DELETE CASCADE ON UPDATE CASCADE, 序号序号 SMALLINT CHECK (序号序号=1), 器件号器件号 CHAR(6) FOREIGN KEY REFERENCES 器件器件, 单价单价 SMALLMONEY, 数量数量 INT CONSTRAINT num CHECK (数量数量=0), PRIMARY KEY (订购单号订购单号,序号序号) ) 21 课堂练习一课堂练习一 创建数据库创建数据库 1. 创建创建教学信息管理数据库教学信息管理数据库(jxgl)，其主数据文件名为，

14、其主数据文件名为 jxgl_data，初始大小为，初始大小为5M，每次增加，每次增加1M；日志文；日志文 件名为件名为jxgl_log，初始大小为，初始大小为2M，每次增加，每次增加10%。 数据库的物理文件保存在数据库的物理文件保存在d:sqlserver目录中。目录中。 课堂练习一课堂练习一 创建表创建表 2. 在在jxgl数据库中有数据库中有6个数据表，依次为：教师、课程、个数据表，依次为：教师、课程、 学生、院系、成绩、授课，请分别完成下列各题的学生、院系、成绩、授课，请分别完成下列各题的 操作。操作。 （1）使用使用SSMS创建创建“学生学生”表表，其表结构见表，其表结构见表1-1。

15、 课堂练习一课堂练习一 创建表创建表 2. 在在jxgl数据库中有数据库中有6个数据表，依次为：教师、课程、个数据表，依次为：教师、课程、 学生、院系、成绩、授课，请分别完成下列各题的操学生、院系、成绩、授课，请分别完成下列各题的操 作。作。 （2）使用使用SQL语句创建语句创建“院系院系”表表，其表结构见表，其表结构见表1-2。 课堂练习一课堂练习一 创建表创建表 2.（3）使用使用SQL语句语句SQL语句创建语句创建“课程课程”表表，定义，定义“课程编课程编 号号”字段为主键约束；字段为主键约束；“课程类别课程类别”字段的默认值为字段的默认值为“必修必修 课课”；定义；定义“学时学时”字段

16、的取值范围为：字段的取值范围为：1872学时；定义学时；定义 “课程名称课程名称”字段为唯一约束（约束为：字段为唯一约束（约束为：kc_unique）。其）。其 表结构见表表结构见表1-3。 课堂练习一课堂练习一 创建表创建表 2.（4）使用）使用SQL语句语句创建创建“成绩成绩”表表，定义，定义“学号学号”和和“课程编课程编 号号”字段为主键约束；以及分别定义字段为主键约束；以及分别定义“学号学号”字段和字段和“课程课程 编号编号”字段的外键约束。其表结构见表字段的外键约束。其表结构见表1-5。 二、修改表结构二、修改表结构 ALTER TABLE table_name ALTER COLU

17、MN column_name NULL | NOT NULL | ADD | | | DROP CONSTRAINT constraint_name | COLUMN column_name ALTER COLUMNALTER COLUMN修改已有列的定义，但是只能修改为兼容数据修改已有列的定义，但是只能修改为兼容数据 类型或重新定义是否允许空值；类型或重新定义是否允许空值； ADDADD新列、计算列或表级约束；新列、计算列或表级约束； DROPDROP删除约束或删除列。删除约束或删除列。 例例5-8：删除删除职工表上定义的职工表上定义的约束约束ref_wh。 ALTER TABLE 职工职工

18、 DROP CONSTRAINT ref_wh 28 如果在定义表和约束的时候没有用如果在定义表和约束的时候没有用 CONSTRAINT 短语指定约束名称，则短语指定约束名称，则 约束名是？约束名是？ 例例5-9：添加完整性约束。：添加完整性约束。 29 为为职工表的仓库号字段添加约束职工表的仓库号字段添加约束，说明该字段是说明该字段是 外部关键字外部关键字，同时规定当被参照记录被删除时，将参同时规定当被参照记录被删除时，将参 照记录的仓库号字段值置为空值照记录的仓库号字段值置为空值（即空值删除），（即空值删除），当当 被参照记录的仓库号修改时级联修改参照记录的仓库被参照记录的仓库号修改时级联

19、修改参照记录的仓库 号字段值（即级联更新）号字段值（即级联更新）。 例例5-9：添加完整性约束。：添加完整性约束。 ALTER TABLE 职工职工 ADD CONSTRAINT ref_wh FOREIGN KEY (仓库号仓库号) REFERENCES 仓库仓库(仓库号仓库号) ON DELETE SET NULL ON UPDATE CASCADE 30 例例5-10：增加和删除字段：增加和删除字段 为为订购明细表订购明细表增加一个增加一个完成日期字段完成日期字段： ALTER TABLE 订购明细订购明细 ADD 完成日期完成日期 datetime 31 删除删除刚为刚为订购明细表订购

20、明细表增加的完成日期字段：增加的完成日期字段： ALTER TABLE 订购明细订购明细 DROP COLUMN 完成日期完成日期 例例5-10：增加和删除字段。：增加和删除字段。 32 为为订购单表订购单表增加一个增加一个完成日期完成日期字段，默认值是空值字段，默认值是空值 NULL： ALTER TABLE 订购单订购单 ADD 完成日期完成日期 datetime DEFAULT NULL 例例5-11：增加计算字段：增加计算字段 ALTER TABLE 订购明细订购明细 ADD 金额金额 AS 单价单价*数量数量 33 为订购明细表为订购明细表增加一个计算字段增加一个计算字段，字段名为，

21、字段名为 金额金额，计算表达式为：，计算表达式为：单价单价*数量。数量。 三、删除表三、删除表 DROP TABLE table_name 例如：删除例如：删除“院系院系”表表 drop table drop table 院系院系 课堂练习二课堂练习二 修改表修改表 使用使用SQL语句语句修改修改“院系院系”表，给其增加一列表，给其增加一列“系网址系网址”， 其数据类型为字符串，字段大小为其数据类型为字符串，字段大小为50，可以为空，可以为空。 使用使用SQL语句语句删除删除“课程课程”表中的表中的唯一约束唯一约束kc_unique。 删除删除“院系院系”表刚增加的列表刚增加的列“系网址系网址

22、”。 给给“成绩成绩”表添加两列：平时成绩（整型），期末成绩表添加两列：平时成绩（整型），期末成绩(整整 型型)。 在在“成绩成绩”表中增加一个计算字段表中增加一个计算字段，字段名为总分，计算表，字段名为总分，计算表 达式为：平时成绩达式为：平时成绩*50%+期末成绩期末成绩*50%。 删除删除“课程课程”表。表。 5.2 SQL 数据操作与完整性约束的作用数据操作与完整性约束的作用 插入插入操作及其完整性约束操作及其完整性约束 删除删除操作及其完整性约束操作及其完整性约束 更新更新操作及其完整性约束操作及其完整性约束 36 仓仓库库订货订货数据库数据库 一、一、插入插入操作及其完整性约束操作

23、及其完整性约束 常用格式：常用格式： INSERT INTO table_name ( column_list ) VALUES(expression | DEFAULT | NULL ,.n ) table_name table_name 指出指出表名表名 column_listcolumn_list给出插入操作所涉及给出插入操作所涉及列的列表列的列表（默认是表（默认是表 的全部列）的全部列） expression | DEFAULT | NULL ,.n expression | DEFAULT | NULL ,.n 给出对应于给出对应于 column_listcolumn_list的的各

24、个列的值各个列的值 1. 向向仓库表仓库表插入一条记录插入一条记录 n仓库号仓库号 CHAR(5) PRIMARY KEY n城市城市 CHAR(10) n面积面积 INT CHECK (面积面积 0) 实体完整性约束实体完整性约束 用户定义完整性约束用户定义完整性约束 插入操作与完整性约束有无关系？插入操作与完整性约束有无关系？ 插入一条记录插入一条记录-实例演示实例演示 实例实例5-1：向仓库：向仓库表表插入插入一一条条记录。记录。 INSERT INTO 仓库仓库 VALUES(WH5,南昌南昌,800) 必须用必须用逗号逗号将各项数据分开，将各项数据分开，字符型字符型数据用数据用单引号

25、单引号括起来。括起来。 温馨提示：温馨提示：VALUES子句中子句中值的个数和类型应与值的个数和类型应与INTO子句子句 中属性列中属性列 相对应相对应。 插入一条记录插入一条记录-实例演示实例演示 实实例例5-2：能否将下列记录插入到仓库表？能否将下列记录插入到仓库表？ 则会报错：因插入的则会报错：因插入的“仓库号仓库号”属性值属性值违背违背了了实体完整实体完整性！性！ -即主键约束即主键约束 INSERT INTO 仓库仓库 VALUES(WH5,天津天津,450) 温馨提示：温馨提示：插入操作所插入的字段值应满足完整性约插入操作所插入的字段值应满足完整性约 束！束！ 4242 思思 考考

26、 题题 1 能否将下列能否将下列2条记录插入到仓库表中？条记录插入到仓库表中？ （1）INSERT INTO 仓库仓库 VALUES(WH6,长沙长沙,0) （2）INSERT INTO 仓库仓库 VALUES(WH6,长沙长沙) 4343 INSERT INTO (,) 子查询子查询 插入多条记录插入多条记录-批量增加批量增加 子查询中子查询中SELEC子句目标列子句目标列必须与必须与INT子句匹配子句匹配， 即值的个数和值的类型均要求一致。即值的个数和值的类型均要求一致。 插入多条记录插入多条记录-实例演示实例演示 实例实例5-3：对于每一个对于每一个订购单号订购单号，求出其，求出其总总

27、金额金额，并把结果存入到，并把结果存入到新表新表order_sum 中中。 插入多条记录插入多条记录-实例演示实例演示 解决办法：解决办法： （1）在仓库订货数据库中在仓库订货数据库中创建一个新表创建一个新表order_sum ，其中一列用于保存订购单号，另一列用于存放总金额，其中一列用于保存订购单号，另一列用于存放总金额 create table order_sum ( order_id char(5), sum_money smallmoney) 插入多条记录插入多条记录-实例演示实例演示 解决办法：解决办法： （2）对订购明细表按订购单号分组，把订购单号和对对订购明细表按订购单号分组，把

28、订购单号和对 应的总金额插入到新表应的总金额插入到新表order_sum中中。 insert into order_sum select 订购单号订购单号,sum(金额金额) from 订购明细订购明细 group by 订购单号订购单号 4747 思思 考考 题题 2 小明小明刚新建刚新建了一个教学信息系统（了一个教学信息系统（jxsk），其包含表），其包含表 对象有学生、课程、学生选课、教师、教师授课。对象有学生、课程、学生选课、教师、教师授课。 请问能否往学生选课表中插入一条记录？请问能否往学生选课表中插入一条记录？ SQL删除语句删除语句DELETE 常用格式：常用格式： DELETE

29、 FROMtable_name WHERE table_nametable_name指出从哪个表删除记录；指出从哪个表删除记录； WHEREWHERE用来用来指出删除记录的条件指出删除记录的条件， 默认是删除全部记录。默认是删除全部记录。 删除操作与完整性约束？删除操作与完整性约束？ 二、二、删除操作删除操作及其完整性约束及其完整性约束 例例5-4：删除删除订购明细表订购明细表中订购单号为中订购单号为OR91的的 记录。记录。 DELETE FROM 订购明细订购明细 WHERE 订购单号订购单号=OR91 49 删除记录删除记录-实例演示实例演示 例例5-5：删除订购单表中订购单号为删除订购

30、单表中订购单号为OR67的记录的记录 删除操作之前先查看订购单明细表中订购单号为删除操作之前先查看订购单明细表中订购单号为OR67 的明细记录。的明细记录。 DELETE 订购单订购单 WHERE 订购单号订购单号=OR67 50 删除记录删除记录-实例演示实例演示 51 如果有如果有OR67的订购明细记录？的订购明细记录？ 删除记录删除记录-实例演示实例演示 SQL更新记录的语句更新记录的语句UPDATE UPDATE table_name SET column_name = expression | DEFAULT | NULL ,.n WHERE table_nametable_name

31、给出要更新数据的表；给出要更新数据的表； SETSET短语说明短语说明要更新的列及其值要更新的列及其值； 参数参数“ ,.n ” ,.n ”说明一次可以更新多列。说明一次可以更新多列。 WHEREWHERE指定更新哪些记录，即用逻辑表达式指定更新哪些记录，即用逻辑表达式 指定更新条指定更新条 件。件。 更新操作与完整性约束？更新操作与完整性约束？ 3. 更新操作更新操作及其完整性约束及其完整性约束 思考思考 假设更新假设更新被参照表被参照表记录的主关键字，如记录的主关键字，如 果有参照记录将如何处理？具体怎么实果有参照记录将如何处理？具体怎么实 现？现？ 例例5-6：将职工将职工E11的仓库号

32、字段值设置为的仓库号字段值设置为WH5 UPDATE 职工职工 SET 仓库号仓库号=WH5 WHERE 职工号职工号=E11 如设置为如设置为WH6呢？能操作成功吗？呢？能操作成功吗？ 54 失败！违背参照完整性失败！违背参照完整性 更新操作更新操作-实例演示实例演示 加深对完整性约束和作用的理解加深对完整性约束和作用的理解 n插入操作与完整性约束插入操作与完整性约束 n删除操作与完整性约束删除操作与完整性约束 n更新操作与完整性约束更新操作与完整性约束 5656 外部数据的导入与导出外部数据的导入与导出-补充补充 一、导入外部一、导入外部数据库数据库，如，如Access 1）右键）右键-任

33、务任务-导入数据；导入数据； 2）数据源：选择）数据源：选择Access，文件名选定要导入的，文件名选定要导入的 Access数据库；数据库； 3）目标：选择）目标：选择SQL SERVER，指定已有数据库，指定已有数据库jxgl 4） 选定所有源表。选定所有源表。 二、导入外部二、导入外部数据数据，如，如Excel文件。文件。 5757 内部数据的备份与还原内部数据的备份与还原-补充补充 三、备份三、备份SQL Server数据库数据库 四、数据库还原（数据库四、数据库还原（数据库/文件和文件组文件和文件组） 1）右键整个数据库）右键整个数据库-还原数据库；还原数据库； 2）输入目标数据库的新数据库名称，并设置源设）输入目标数据库的新数据库名称，并设置源设 备；备； 3）选项：勾选覆盖现有数据库选项；选项：勾选覆盖现有数据库选项； 本章小结本章小结 如何进行表定义？如何进行表定义？ 如何实现完整性定义？如何实现完整性定义？ 完整性约束如何在完整性约束如何在SQL数据操作时发挥作用？数据操作时发挥作用？ 58 课堂练习三课堂练习三 数