SQL数据库_创建表与完整性约束.ppt

4.1.3 sql语言功能概述,四部分：数据定义功能、数据控制功能、数据查询功能和数据操纵功能。,4.2 sql的数据类型,数值型 字符串型 日期时间型 货币型,数值型,准确型 整数 bigint: 8字节， int：4字节 smallint：2字节， tinyint：1字节 bit：1位，存储1或0 小数 numeric（p,q）或decimal（p,q）， 其中：p为数字位长度，q：小数位长度。 近似型 float：8字节 real：4字节,字符串型,普通编码字符串类型 统一字符编码字符串类型 二进制字符串类型,普通编码字符串类型,char（n）:定长存储，n=8000 varchar（n）：不定长存储（按实际长度存储），长度最大不超过n , n=8000 注：n 为字符个数 text：存储大于8000字节的文本,统一字符编码字符串类型,nchar（n）:定长存储，n=4000 nvarchar（n）：不定长存储，长度最大不超过n , n=4000 ntext：存储大于8000字节的文本 特点：每个字符占两个字节,二进制字符串类型,binary(n)：固定长度，n = 8000。 varbinary(n)：可变长度，n 8000 。 注：n为二进制数据的字节数 image：大容量、可变长二进制字符数据，可用于存储文件。,日期时间型,datetime：8字节，年月日时分秒毫秒 （例：2001/08/03 10:30:00.000 ） smalldatetime：4字节，年月日时分 （例： 2001/08/03 10:30:00 ） 日期、时间的输入格式,货币类型,money： 8 个字节，精确到货币单位的千分之十。 smallmoney： 4 个字节，精确到货币单位的千分之十。 限制到小数点后 4 位。 可以带有适当的货币符号。例如，100 英镑可表示为 100。,4.3 基本表的定义、删除及修改,4.3.1 基本表的定义与删除 4.3.2 修改表结构,4.3.1 基本表的定义与删除,1定义基本表 使用sql语言中的create table语句实现，其一般格式为： create table （ 列级完整性约束定义 , 列级完整性约束定义 , 表级完整性约束定义 ）,在列级完整性约束定义处可以定义的约束,not null：限制列取值非空。 default：给定列的默认值。 unique：限制列取值不重。 check：限制列的取值范围。 primary key：指定本列为主码。 foreign key：定义本列为引用其他表的外码。使用形式为： foreign key（） references （）,几点说明,not null和default只能是列级完整性约束； 其他约束均可在表级完整性约束处定义。 注意以下几点： 第一，如果check约束是定义多列之间的取值约束，则只能在表级完整性约束处定义； 第二，如果表的主码由多个列组成，则也只能在表级完整性约束处定义，并将主码列用括号括起来，即：primary key（列1，列2 ）； 第三，如果在表级完整性约束处定义外码，则“foreign key ()”部分不能省。,约束定义, 列取值非空约束 not null 例：sname char(10) not null,约束定义（续）, 表主码约束 在定义列时定义主码（仅用于单列主码） 列定义 primary key 例： sno char(7) primary key 在定义完列时定义主码（用于单列或多列主码） primary key （） 例： primary key(sno) primary key(sno，cno),约束定义（续）,外码引用约束,指明本表外码列引用的表及表中的主码列。, foreign key （） references （）,例： foreign key （sno） references 学生表（sno）,约束定义（续）,默认值约束 格式：default 默认值 例：定义系的默认值为“计算机系”。 default 计算机系,约束定义（续）,check约束 格式：check (约束表达式) 例：定义成绩大于等于0。 check ( grade = 0 ),约束定义（续）,unique约束 在列级约束定义（仅用于单列约束） 列定义 unique 例： sname char(7) unique 在表级约束定义（用于单列或多列组合约束） unique （） 例： unique (sno，cno),用于限制在一个列中不能有重复的值。 用在事实上具有惟一性的属性列上，比如每个人的身份证号码、驾驶证号码等均不能有重复值。 注意： 允许有一个空值； 在一个表中可以定义多个unique约束； 可以在一个列或多个列上定义unique约束。,创建学生表,create table student ( sno char ( 7 ) primary key, sname char ( 10 ) not null, ssex char (2) check (ssex = 男 or ssex = 女), sage tinyint check (sage = 15 and sage =45), sdept char (20 ) default 计算机系 ),创建课程表,create table course ( cno char(10) not null, cname char(20) not null, ccredit tinyint check (ccredit 0), semester tinyint check (semester 0), period int check (period 0), primary key(cno) ),创建sc表,create table sc ( sno char(7) not null, cno char(10) not null, grade tinyint, check (grade = 0 and grade = 100), primary key ( sno, cno ), foreign key ( sno ) references student ( sno ), foreign key ( cno ) references course ( cno ) ),删除表,当确信不再需要某个表时，可以将其删除 删除表时会将与表有关的所有对象一起删掉，包括表中的数据。 删除表的语句格式为： drop table , 例：删除test表的语句为： drop table test,4.3.2 修改表结构,在定义完表之后，如果需求有变化，比如添加列、删除列或修改列定义，可以使用alter table语句实现。 alter table语句可以对表添加列、删除列、修改列的定义、定义主码、外码，也可以添加和删除约束。,修改表结构语法,alter table alter column | add column | drop column | add primary key（列名 ， n ） | add foreign key（列名） referneces 表名（列名）,示例,例2为sc表添加“修课类别”列，此列的定义为：xklb char(4) alter table sc add xklb char(4) null,示例,例3将新添加的xklb的类型改为 char(6)。 alter table sc alter column xklb char(6),示例,例4删除course表的period列。 alter table course drop column period,指数据的正确性、有效性和相容性 然而大多数情况下，我们所提到的完整性，实际是指完整性 规则 为保证完整性，数据应该满足的约束条件，又称为完整性约束,完整性的原义,关系模型中的完整性,关系完整性 在关系模型中的地位 三要素之一（关系, 关系完整性, 关系操作） 三种类型 实体完整性； 参照完整性； 用户定义完整性；,三种关系完整性规则 解决现实世界的三个问题 如何保证一个实体是可识别（区分）的 方法：实体完整性 如何保证能够从一个实体找到另一个相关联的实体，而不会出现找不到的情况 方法：参照完整性 如何保证用于描述实体的属性，其取值是合理的 方法：用户定义完整性,三种关系完整性规则,实体完整性 目的：保证每个元组（所代表的实体）是可标识（区分）的 规则：元组在主码的每个属性上取唯一值，且不能为空 意义： 关系往往对应一个实体集，这时关系中的每个元组对应一个实体 现实世界中的实体是可区分的，关系中的元组也必须是可区分的 主码起到唯一标识（区分）每个元组的作用，所以它必须是唯一的，同时也是非空的,三种关系完整性规则,三种关系完整性规则要点： 如果一个关系的主码由多个属性构成，那么每个属性都不能取空值,三种关系完整性规则,参照完整性,复习：外部码,外部码 定义：关系r1中的单个属性或属性集合，对应另一个关系 r2的主码 r1称为参照关系， r2称为被参照关系 来源：由联系集转化而来，反映实体（元组）之间的联系,复习：外部码,三种关系完整性规则,参照完整性 目的：保证外码引用的是一个有效的主码 规则：如果关系r的外部码对应关系s的主码，则r每个元组在 外部码上的取值必须满足： 或者等于空值 或者等于某个对应的主码值（s某个元组的主码值） 意义： 在被参照关系s中， 一个主码值代表所在的元组（一个实体） 在参照关系r中，元组的外部码 要么取空值，表示不和任何s元组（实体）存在联系 要么取s的某个主码值，表示表示联系是存在的，且涉及的是 这个主码值所代表的s元组（实体） 如果违反以上两种情况，表示联系涉及了不存在的实体，这是不允许的,思考,thinking ： 根据外部码取值的可能性，我们在定义外部码的属性类型时，应遵循什么原则?,三种关系完整性规则,用户定义完整性 目的：保证实体属性的取值有效，等等 规则：用户根据具体的应用环境定义 例如 年龄的取值范围为0到200，性别只能是“男”或“女” 职工编号是4位整数 意义： 反映了现实世界的真实情况，即实体的属性必须满足要满足这些条件，反映了程序编制的要求,思考,thinking 什么操作会破坏关系完整性。查询操作，还是修改操作?,课堂练习,依次执行如下操作，哪些能够成功？ 1零件关系： 添加(3, 绿, null) 2供应商关系： 添加(null, 四化, 广州) 3供应商关系： 添加(e, 北电, 广州) 4零件关系： 修改(2, 白, a) 为(2, 黑, f) 5供应商关系： 删除(a, 红星, 北京) 6零件关系： 修改(3, 蓝, b) 为(3, 蓝, e),完整性控制概述,完整性控制 目的：保证数据的正确性、有效性和相容性 由dbms负责，包括两方面 完整性规则的定义：通过sql （运行时）完整性规则的检查：通过专门的“完整性子系统”,主码约束,规则：主码值不允许空，也不允许出现重复 定义：在创建或修改关系的定义语句中，用primary key关键字声明主码的同时，即定义了一个主码约束 create table 关系名 ( , primary key (属性, 属性) ),主码约束,例： create table 选修( 学号varchar (10), 课程号varchar (10), , primary key (学号, 课程号), ),唯一约束,规则：对指定的一个属性或属性组合，不允许出现重复值 定义：在创建或修改关系的定义语句中，用unique关键字声明 create table 关系名 ( , unique (属性, 属性) ),主码约束,例： create table 学生( , 身份证号varchar (15), , unique (身份证号), ),外部码约束,规则：外部码要么取空值，要么取对应的某个主码值 定义：在创建或修改关系的定义语句中，用foreign key关键字声明一个外部码的同时，即定义了一个外部码约束 create table 表名 ( , foreign key (属性名, 属性名) references 关系名(属性名, 属性名) on delete on update ,参照动作 说明当某个主码值被删除/更新时（这个主码值在被参照关系中），如何处理对应的外部码值（这些外部码值在参照关系中） restrict 方式：仅当没有任何对应的外码值时，才可以删除/更新这个主码值，否则系统拒绝执行此操作 cascade 方式：连带将所有对应的外码值一块删除/更新（删除外码值，实际上就是将所在的元组删除掉） set null 方式：将所有对应的外码值设为空值,外部码约束,外部码约束,cascade方式示例：当主码值被删除/更新时，连带删除/更新对应的外码值,外部码约束,restrict方式示例：仅当没有任何对应的外码值时，才可以删除/更新主码值，否则系统拒绝执行此操作,外部码约束,外部码约束,外部码约束,set null方式示例：主码值被删除/更新时，将对应的外码值设为空值,外部码约束,外部码约束,例： create table 学生 ( 学号varchar (4), 姓名, primary key (学号), ) create table 选修 ( , foreign key (学号) references 学生(学号) on delete restrict on update cascade, , ),外部码约束,外部码约束,非空约束,非空约束 规则：属性值不允许取空值 定义：在定义关系的语句中，声明某个属性不能取空值（notnull），即定义了一个非空约束 create table 关系名 ( , 属性名域default 缺省值 not null , ),非空约束,例： create table 学生 ( , 姓名varchar(20) not null, , ),完整性约束命名子句,constraint primary key |goreign key 短语|check短语 ,例：对雇员表和工作表添加主码约束 alter table 雇员表 add constraint pk_emp primary key （雇员编号） alter table 工作表 add constraint pk_job primary key （工作编号）,完