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

1、4.1.3 SQL语言功能概述SQL功能功能命令动词命令动词数据查询数据查询SELECT数据定义数据定义CREATE、DROP、ALTER数据操纵数据操纵INSERT、UPDATE、DELETE数据控制数据控制GRANT、REVOKE四部分：数据定义功能、数据控制功能、数据查询功能和数据操纵功能。4.2 SQL的数据类型 数值型 字符串型 日期时间型 货币型数值型准确型 整数Bigint:8字节，Int：4字节Smallint：2字节，Tinyint：1字节 Bit：1位，存储1或0小数Numeric（p,q）或Decimal（p,q），其中：p为数字位长度，q：小数位长度。近似型 Float

2、：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 80

3、00。注：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

4、.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只能是列级完整性约

5、束；其他约束均可在表级完整性约束处定义。注意以下几点：第一，如果CHECK约束是定义多列之间的取值约束，则只能在表级完整性约束处定义；第二，如果表的主码由多个列组成，则也只能在表级完整性约束处定义，并将主码列用括号括起来，即：PRIMARY KEY（列1，列2 ）；第三，如果在表级完整性约束处定义外码，则“FOREIGN KEY()”部分不能省。约束定义 列取值非空约束 NOT NULL例：sname char(10)NOT NULL约束定义（续）表主码约束表主码约束 在定义列时定义主码（仅用于单列主码）列定义 PRIMARY KEY例：SNO char(7)PRIMARY KEY 在定义完列

6、时定义主码（用于单列或多列主码）PRIMARY KEY（）例：PRIMARY KEY(SNO)PRIMARY KEY(SNO，CNO)约束定义（续）外码引用约束外码引用约束 指明本表外码列引用的表及表中的主码列。FOREIGN KEY（）REFERENCES（）例：FOREIGN KEY（sno）REFERENCES 学生表（sno）约束定义（续）默认值约束默认值约束 格式：DEFAULT 默认值 例：定义系的默认值为“计算机系”。DEFAULT 计算机系约束定义（续）CHECKCHECK约束约束 格式：CHECK(约束表达式)例：定义成绩大于等于0。CHECK(grade=0)约束定义（续）

7、UNIQUEUNIQUE约束约束 在列级约束定义（仅用于单列约束）列定义 UNIQUE例：SNAME char(7)UNIQUE 在表级约束定义（用于单列或多列组合约束）UNIQUE（）例：UNIQUE(SNO，CNO)用于限制在一个列中不能有重复的值。用于限制在一个列中不能有重复的值。用在事实上具有惟一性的属性列上，比如每用在事实上具有惟一性的属性列上，比如每个人的身份证号码、驾驶证号码等均不能有个人的身份证号码、驾驶证号码等均不能有重复值。重复值。注意：注意：允许有一个空值；允许有一个空值；在一个表中可以定义多个在一个表中可以定义多个UNIQUE约束；约束；可以在一个列或多个列上定义可以在

8、一个列或多个列上定义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 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)

9、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 test4.3.2 修改表结构 在定义完表之后，如果需求有变化，比如添加列、删除列或修改列定义，可以使

10、用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 A

11、LTER COLUMN XKLB char(6)示例 例4删除Course表的Period列。ALTER TABLE Course DROP COLUMN Period 指指数数据的正确性、有效性和相容性据的正确性、有效性和相容性 然而大多然而大多数数情情况况下，我下，我们们所提到的完整性，所提到的完整性，实际实际是指完整性是指完整性规则规则为为保保证证完整性，完整性，数数据据应该满应该满足的足的约约束束条条件，又件，又称为称为完整性完整性约约束束完整性的原义完整性的原义关系模型中的完整性关系模型中的完整性 关系完整性关系完整性 在关系模型中的地位在关系模型中的地位 三要素之一（关系三要素之一

12、（关系,关系完整性关系完整性,关系操作）关系操作）三种类型三种类型 实体完整性；实体完整性；参照完整性；参照完整性；用户定义完整性；用户定义完整性；三种关系完整性规则三种关系完整性规则 解决现实世界的三个问题解决现实世界的三个问题 如何保证一个实体是可识别（区分）的如何保证一个实体是可识别（区分）的 方法：实体完整性方法：实体完整性 如何保证能够从一个实体找到另一个相关联的实体，而不如何保证能够从一个实体找到另一个相关联的实体，而不会出现找不到的情况会出现找不到的情况 方法：参照完整性方法：参照完整性 如何保证用于描述实体的属性，其取值是合理的如何保证用于描述实体的属性，其取值是合理的 方法：

13、用户定义完整性方法：用户定义完整性三种关系完整性规则三种关系完整性规则 实体完整性实体完整性 目的：保证每个元组（所代表的实体）是可标识目的：保证每个元组（所代表的实体）是可标识（区分）的（区分）的 规则：元组在主码的每个属性上取唯一值，且不规则：元组在主码的每个属性上取唯一值，且不能为空能为空 意义：意义：关系往往对应一个实体集，这时关系中的每个元关系往往对应一个实体集，这时关系中的每个元组对应一个实体组对应一个实体 现实世界中的实体是可区分的，关系中的元组也现实世界中的实体是可区分的，关系中的元组也必须是可区分的必须是可区分的 主码起到唯一标识（区分）每个元组的作用，所主码起到唯一标识（区

14、分）每个元组的作用，所以它必须是唯一的，同时也是非空的以它必须是唯一的，同时也是非空的三种关系完整性规则三种关系完整性规则 三种关系完整性规则要点：三种关系完整性规则要点：如果一个关系的主码由多个属性构成，那如果一个关系的主码由多个属性构成，那么每个属性都不能取空值么每个属性都不能取空值三种关系完整性规则三种关系完整性规则 参照完整性参照完整性复习：外部码复习：外部码 外部码外部码 定义：关系定义：关系R1中的单个属性或属性集合，对中的单个属性或属性集合，对应另一个关系应另一个关系R2的主码的主码R1称为参照关系，称为参照关系，R2称为被参照关系称为被参照关系 来源：由联系集转化而来，反映实体

15、（元组）来源：由联系集转化而来，反映实体（元组）之间的联系之间的联系复习：外部码复习：外部码三种关系完整性规则三种关系完整性规则 参照完整性参照完整性 目的：保证外码引用的是一个有效的主码目的：保证外码引用的是一个有效的主码 规则：如果关系规则：如果关系R的外部码对应关系的外部码对应关系S的主码，则的主码，则R每个元组在每个元组在 外部码上的取值必须满足：外部码上的取值必须满足：或者等于空值或者等于空值 或者等于某个对应的主码值（或者等于某个对应的主码值（S某个元组的主码值）某个元组的主码值）意义：意义：在被参照关系在被参照关系S中，中，一个主码值代表所在的元组（一个实体）一个主码值代表所在的

16、元组（一个实体）在参照关系在参照关系R中，元组的外部码中，元组的外部码 要么取空值，表示不和任何要么取空值，表示不和任何S元组（实体）存在联系元组（实体）存在联系 要么取要么取S的某个主码值，表示表示联系是存在的，且涉及的是的某个主码值，表示表示联系是存在的，且涉及的是 这个主码值所代表的这个主码值所代表的S元组（实体）元组（实体）如果违反以上两种情况，表示联系涉及了不存在的实体，这是如果违反以上两种情况，表示联系涉及了不存在的实体，这是不允许的不允许的思考 Thinking：根据外部码码取值值的可能性，我们们在定义义外部码码的属属性类类型时时，应应遵循什么么原则则?三种关系完整性规则三种关系

17、完整性规则 用户定义完整性用户定义完整性 目的：保证实体属性的取值有效，等等目的：保证实体属性的取值有效，等等 规则：用户根据具体的应用环境定义规则：用户根据具体的应用环境定义 例如例如 年龄的取值范围为年龄的取值范围为0到到200，性别只能是，性别只能是“男男”或或“女女”职工编号是职工编号是4位整数位整数 意义：意义：反映了现实世界的真实情况，即实体的属性必须满反映了现实世界的真实情况，即实体的属性必须满足要满足这些条件，反映了程序编制的要求足要满足这些条件，反映了程序编制的要求思考 Thinking 什么么操作会会破坏关关系完整性。查询查询操作，还还是修改操作?课堂练习课堂练习 依次执行

18、如下操作，哪些能够成功？依次执行如下操作，哪些能够成功？1零件关系：零件关系：添加添加(3,绿绿,null)2供应商关系：供应商关系：添加添加(null,四化四化,广州广州)3供应商关系：供应商关系：添加添加(E,北电北电,广州广州)4零件关系：零件关系：修改修改(2,白白,A)为为(2,黑黑,F)5供应商关系：供应商关系：删除删除(A,红星红星,北京北京)6零件关系：零件关系：修改修改(3,蓝蓝,B)为为(3,蓝蓝,E)完整性控制概述完整性控制概述 完整性控制完整性控制 目的：保证数据的正确性、有效性和相容性目的：保证数据的正确性、有效性和相容性 由由DBMS负责，负责，包括包括两方面两方面

19、 完整性规则的定义：通过完整性规则的定义：通过SQL（运行时）完整性规则的检查：通过专门的（运行时）完整性规则的检查：通过专门的“完整完整性子系统性子系统”主码约束主码约束 规则：主码值不允许空，也不允许出现重复规则：主码值不允许空，也不允许出现重复 定义：在创建或修改关系的定义语句中，用定义：在创建或修改关系的定义语句中，用PRIMARY KEY关键字声明主码的同时，即定义了一关键字声明主码的同时，即定义了一个主码约束个主码约束create table 关系名关系名(,primary key(属性属性,属性属性)主码约束主码约束 例：例：create table 选修选修(学号学号VARCH

20、AR(10),课程号课程号VARCHAR(10),primary key(学号学号,课程号课程号),)唯一约束唯一约束 规则：对指定的一个属性或属性组合，不允许出现重规则：对指定的一个属性或属性组合，不允许出现重复值复值 定义：在创建或修改关系的定义语句中，用定义：在创建或修改关系的定义语句中，用Unique关关键字声明键字声明create table 关系名关系名(,unique(属性属性,属性属性)主码约束主码约束 例：例：create table 学生学生(,身份证号身份证号VARCHAR(15),unique(身份证号身份证号),)外部码约束外部码约束 规则：外部码要么取空值，要么取对

21、应的某个主码值规则：外部码要么取空值，要么取对应的某个主码值 定义：在创建或修改关系的定义语句中，用定义：在创建或修改关系的定义语句中，用FOREIGN KEY关键字声明一个外部码的同时，即定义关键字声明一个外部码的同时，即定义了一个外部码约束了一个外部码约束create table 表名表名(,foreign key(属性名属性名,属性名属性名)references 关系名关系名(属性名属性名,属性名属性名)on delete on update 参照动作参照动作 说明当某个主码值被删除说明当某个主码值被删除/更新时（这个主码值在被更新时（这个主码值在被参照关系中），如何处理对应的外部码值（

22、这些外参照关系中），如何处理对应的外部码值（这些外部码值在参照关系中）部码值在参照关系中）RESTRICT 方式：仅当没有任何对应的外码值时，方式：仅当没有任何对应的外码值时，才可以删除才可以删除/更新这个主码值，否则系统拒绝执行此更新这个主码值，否则系统拒绝执行此操作操作 CASCADE 方式：连带将所有对应的外码值一块删方式：连带将所有对应的外码值一块删除除/更新（删除外码值，实际上就是将所在的元组删更新（删除外码值，实际上就是将所在的元组删除掉）除掉）SET NULL 方式：将所有对应的外码值设为空值方式：将所有对应的外码值设为空值外部码约束外部码约束外部码约束外部码约束 CASCADE

23、方式示例：当当主码值码值被删删除/更新时时，连带删连带删除/更新对应对应的外码值码值外部码约束外部码约束 RESTRICT方式示例：仅当没仅当没有任何对应对应的外码值时码值时，才可以删删除/更新主码值码值，否则则系统统拒绝执绝执行此操作外部码约束外部码约束外部码约束外部码约束外部码约束外部码约束 SET NULL方式示例：主码值被删除方式示例：主码值被删除/更新时，更新时，将对应的外码值设为空值将对应的外码值设为空值外部码约束外部码约束外部码约束外部码约束 例：例：create table 学生学生(学号学号VARCHAR(4),姓名姓名,primary key(学号学号),)create t

24、able 选修选修(,foreign key(学号学号)references 学生学生(学号学号)on delete restrict on update cascade,)外部码约束外部码约束外部码约束外部码约束非空约束非空约束 非空约束非空约束 规则：属性值不允许取空值规则：属性值不允许取空值 定义：在定义关系的语句中，声明某个属性不能取定义：在定义关系的语句中，声明某个属性不能取空值（空值（notnull），即定义了一个非空约束），即定义了一个非空约束create table 关系名关系名(,属性名域属性名域default 缺省值缺省值 not null ,)非空约束非空约束 例：例：c

25、reate 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（工作编号）完整性约束命名子句 添加UNIQUE约束的语法格式为：ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE（,n）例为雇员表

26、的“电话”列添加UNIQUE约束。ALTER TABLE 雇员表 ADD CONSTRAINT UK_SID UNIQUE（电话）完整性约束命名子句 添加DEFAULT约约束的语语法格式为为：ALTER TABLE 表名 ADD CONSTRAINT 约约束名 DEFAULT 默认值认值 FOR 列名 例定义义雇员员表的工资资的默认值为认值为1000。ALTER TABLE 雇员员 ADD CONSTRAINT DF_SALARYDEFAULT 1000 FOR 工资资完整性约束命名子句 用于限制列的取值在指定的范围内，使数据库中存放的值都是有意义的。系统在执行INSERT语句和UPDATE语句时自动检查CHECK约束。CHECK约束可约束同一个表中多个列之间的取值关系。添加CHECK约束的语法格式为：ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK（逻辑表达式）完整性约束命名子句 例1限制雇员的工资必须大于等于200。ALTER TABLE 雇员 ADD CONSTRAINT CHK_SalaryCHECK(工资=200)例2限制工资表的最低工资小于等于最高工资。ALTER TABLE 工作 ADD CONSTRAINT CHK_Job_Salary CHECK(最低工资=最高工资)完整性约束命名子句