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

《SQL数据库创建表与完整性约束课件.ppt》由会员分享，可在线阅读，更多相关《SQL数据库创建表与完整性约束课件.ppt（68页珍藏版）》请在三一办公上搜索。

1、4.1.3 SQL语言功能概述,四部分：数据定义功能、数据控制功能、数据查询功能和数据操纵功能。,4.1.3 SQL语言功能概述SQL功能命令动词数据查询SE,4.2 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字节,数值型准确型,字符串型,普通编码字符串类型统一字符编码字符串类型二进制

2、字符串类型,字符串型普通编码字符串类型,普通编码字符串类型,Char（n）:定长存储，n=8000 Varchar（n）：不定长存储（按实际长度存储），长度最大不超过n , n=8000 注：n 为字符个数Text：存储大于8000字节的文本,普通编码字符串类型Char（n）:定长存储，n=8000,统一字符编码字符串类型,nchar（n）:定长存储，n=4000 nvarchar（n）：不定长存储，长度最大不超过n , n=4000 ntext：存储大于8000字节的文本特点：每个字符占两个字节,统一字符编码字符串类型nchar（n）:定长存储，n=40,二进制字符串类型,Binary(n)

3、：固定长度，n = 8000。Varbinary(n)：可变长度，n 8000 。注：n为二进制数据的字节数image：大容量、可变长二进制字符数据，可用于存储文件。,二进制字符串类型Binary(n)：固定长度，n = 80,日期时间型,Datetime：8字节，年月日时分秒毫秒（例：2001/08/03 10:30:00.000 ） SmallDateTime：4字节，年月日时分 （例： 2001/08/03 10:30:00 ）日期、时间的输入格式,日期时间型Datetime：8字节，年月日时分秒毫秒,货币类型,Money： 8 个字节，精确到货币单位的千分之十。Smallmoney：

4、4 个字节，精确到货币单位的千分之十。限制到小数点后 4 位。可以带有适当的货币符号。例如，100 英镑可表示为 100。,货币类型Money： 8 个字节，精确到货币单位的千分之十。,4.3 基本表的定义、删除及修改,4.3.1 基本表的定义与删除 4.3.2 修改表结构,4.3 基本表的定义、删除及修改 4.3.1 基本表的定义与,4.3.1 基本表的定义与删除,1定义基本表 使用SQL语言中的CREATE TABLE语句实现，其一般格式为：CREATE TABLE （ 列级完整性约束定义, 列级完整性约束定义 , 表级完整性约束定义 ）,4.3.1 基本表的定义与删除1定义基本表,在列级

5、完整性约束定义处可以定义的约束,NOT NULL：限制列取值非空。DEFAULT：给定列的默认值。UNIQUE：限制列取值不重。CHECK：限制列的取值范围。PRIMARY KEY：指定本列为主码。FOREIGN KEY：定义本列为引用其他表的外码。使用形式为：FOREIGN KEY（） REFERENCES （）,在列级完整性约束定义处可以定义的约束NOT NULL：限制列,几点说明,NOT NULL和DEFAULT只能是列级完整性约束；其他约束均可在表级完整性约束处定义。注意以下几点：第一，如果CHECK约束是定义多列之间的取值约束，则只能在表级完整性约束处定义；第二，如果表的主码由多个列

6、组成，则也只能在表级完整性约束处定义，并将主码列用括号括起来，即：PRIMARY KEY（列1，列2 ）；第三，如果在表级完整性约束处定义外码，则“FOREIGN KEY ()”部分不能省。,几点说明NOT NULL和DEFAULT只能是列级完整性约束,约束定义, 列取值非空约束 NOT NULL例：sname char(10) NOT NULL,约束定义 列取值非空约束,约束定义（续）, 表主码约束在定义列时定义主码（仅用于单列主码）列定义 PRIMARY KEY例： SNO char(7) PRIMARY KEY在定义完列时定义主码（用于单列或多列主码）PRIMARY KEY （）例： P

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

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

9、生表,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 Student (,创建课程表,CREATE TABLE Course ( Cno char(10) NOT NULL, Cname char(20) NOT

10、NULL, Ccredit tinyint CHECK (Ccredit 0), Semester tinyint CHECK (Semester 0), Period int CHECK (Period 0), PRIMARY KEY(Cno) ),创建课程表CREATE TABLE Course (,创建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 ), FO

11、REIGN KEY ( Sno ) REFERENCES Student ( Sno ), FOREIGN KEY ( Cno ) REFERENCES Course ( Cno ) ),创建SC表CREATE TABLE SC (,删除表,当确信不再需要某个表时，可以将其删除删除表时会将与表有关的所有对象一起删掉，包括表中的数据。删除表的语句格式为：DROP TABLE , 例：删除test表的语句为：DROP TABLE test,删除表当确信不再需要某个表时，可以将其删除,4.3.2 修改表结构,在定义完表之后，如果需求有变化，比如添加列、删除列或修改列定义，可以使用ALTER TABL

12、E语句实现。ALTER TABLE语句可以对表添加列、删除列、修改列的定义、定义主码、外码，也可以添加和删除约束。,4.3.2 修改表结构 在定义完表之后，如果需求有变化，比如,修改表结构语法,ALTER TABLE ALTER COLUMN | ADD COLUMN | DROP COLUMN | ADD PRIMARY KEY（列名 ， n ） | ADD FOREIGN KEY（列名） REFERNECES 表名（列名）,修改表结构语法ALTER TABLE ,示例,例2为SC表添加“修课类别”列，此列的定义为：XKLB char(4)ALTER TABLE SC ADD XKLB ch

13、ar(4) NULL,示例例2为SC表添加“修课类别”列，此列的定义为：XKLB,示例,例3将新添加的XKLB的类型改为 char(6)。ALTER TABLE SC ALTER COLUMN XKLB char(6),示例例3将新添加的XKLB的类型改为,示例,例4删除Course表的Period列。 ALTER TABLE Course DROP COLUMN Period,示例例4删除Course表的Period列。,指数据的正确性、有效性和相容性 然而大多数情况下，我们所提到的完整性，实际是指完整性规则为保证完整性，数据应该满足的约束条件，又称为完整性约束,完整性的原义,完整性的原义,

14、关系模型中的完整性,关系完整性在关系模型中的地位三要素之一（关系, 关系完整性, 关系操作）三种类型实体完整性；参照完整性；用户定义完整性；,关系模型中的完整性 关系完整性,三种关系完整性规则解决现实世界的三个问题如何保证一个实体是可识别（区分）的方法：实体完整性如何保证能够从一个实体找到另一个相关联的实体，而不会出现找不到的情况方法：参照完整性如何保证用于描述实体的属性，其取值是合理的方法：用户定义完整性,三种关系完整性规则,三种关系完整性规则,实体完整性目的：保证每个元组（所代表的实体）是可标识（区分）的规则：元组在主码的每个属性上取唯一值，且不能为空意义：关系往往对应一个实体集，这时关系

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

16、外部码,三种关系完整性规则,参照完整性目的：保证外码引用的是一个有效的主码规则：如果关系R的外部码对应关系S的主码，则R每个元组在外部码上的取值必须满足： 或者等于空值 或者等于某个对应的主码值（S某个元组的主码值）意义：在被参照关系S中， 一个主码值代表所在的元组（一个实体）在参照关系R中，元组的外部码要么取空值，表示不和任何S元组（实体）存在联系要么取S的某个主码值，表示表示联系是存在的，且涉及的是这个主码值所代表的S元组（实体）如果违反以上两种情况，表示联系涉及了不存在的实体，这是不允许的,三种关系完整性规则 参照完整性,思考,Thinking ：根据外部码取值的可能性，我们在定义外部码

17、的属性类型时，应遵循什么原则?,思考Thinking ：,三种关系完整性规则,用户定义完整性目的：保证实体属性的取值有效，等等规则：用户根据具体的应用环境定义例如年龄的取值范围为0到200，性别只能是“男”或“女”职工编号是4位整数意义：反映了现实世界的真实情况，即实体的属性必须满足要满足这些条件，反映了程序编制的要求,三种关系完整性规则用户定义完整性,思考,Thinking 什么操作会破坏关系完整性。查询操作，还是修改操作?,思考,课堂练习,依次执行如下操作，哪些能够成功？1零件关系： 添加(3, 绿, null)2供应商关系： 添加(null, 四化, 广州)3供应商关系： 添加(E, 北

18、电, 广州)4零件关系： 修改(2, 白, A) 为(2, 黑, F)5供应商关系： 删除(A, 红星, 北京)6零件关系： 修改(3, 蓝, B) 为(3, 蓝, E),课堂练习,完整性控制概述,完整性控制目的：保证数据的正确性、有效性和相容性由DBMS负责，包括两方面完整性规则的定义：通过SQL（运行时）完整性规则的检查：通过专门的“完整性子系统”,完整性控制概述,主码约束,规则：主码值不允许空，也不允许出现重复定义：在创建或修改关系的定义语句中，用PRIMARY KEY关键字声明主码的同时，即定义了一个主码约束create table 关系名( , primary key (属性, 属性

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

20、要么取对应的某个主码值 定义：在创建或修改关系的定义语句中，用FOREIGN KEY关键字声明一个外部码的同时，即定义了一个外部码约束create table 表名( , foreign key (属性名, 属性名) references 关系名(属性名, 属性名) on delete on update ,外部码约束规则：外部码要么取空值，要么取对应的某个主码值,参照动作说明当某个主码值被删除/更新时（这个主码值在被参照关系中），如何处理对应的外部码值（这些外部码值在参照关系中）RESTRICT 方式：仅当没有任何对应的外码值时，才可以删除/更新这个主码值，否则系统拒绝执行此操作CASCAD

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

22、新时，将对应的外码值设为空值,外部码约束SET NULL方式示例：主码值被删除/更新时，,外部码约束,外部码约束,外部码约束,例：create table 学生( 学号VARCHAR (4), 姓名, primary key (学号), )create table 选修( , foreign key (学号) references 学生(学号) on delete restrict on update cascade, ,),外部码约束例：,外部码约束,外部码约束,外部码约束,外部码约束,非空约束,非空约束规则：属性值不允许取空值定义：在定义关系的语句中，声明某个属性不能取空值（notnull

23、），即定义了一个非空约束create table 关系名( , 属性名域default 缺省值 not null , ),非空约束非空约束,非空约束,例：create table 学生(,姓名VARCHAR(20) not null,),非空约束例：,完整性约束命名子句,CONSTRAINT PRIMARY KEY |GOREIGN KEY 短语|CHECK短语 ,完整性约束命名子句CONSTRAINT ,例：对雇员表和工作表添加主码约束ALTER TABLE 雇员表 ADD CONSTRAINT PK_EMP PRIMARY KEY （雇员编号）ALTER TABLE 工作表 ADD CON

24、STRAINT PK_JOB PRIMARY KEY （工作编号）,完整性约束命名子句,例：对雇员表和工作表添加主码约束完整性约束命名子句,添加UNIQUE约束的语法格式为： ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE（ , n ）例为雇员表的“电话”列添加UNIQUE约束。ALTER TABLE 雇员表 ADD CONSTRAINT UK_SID UNIQUE（电话）,完整性约束命名子句,添加UNIQUE约束的语法格式为： 完整性约束命名子句,添加DEFAULT约束的语法格式为：ALTER TABLE 表名 ADD CONSTRAINT 约束名 DEFA

25、ULT 默认值 FOR 列名例定义雇员表的工资的默认值为1000。ALTER TABLE 雇员 ADD CONSTRAINT DF_SALARYDEFAULT 1000 FOR 工资,完整性约束命名子句,添加DEFAULT约束的语法格式为：完整性约束命名子句,用于限制列的取值在指定的范围内，使数据库中存放的值都是有意义的。系统在执行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( 最低工资 = 最高工资 ),完整性约束命名子句,例1限制雇员的工资必须大于等于200。完整性约束命名子句,SQL数据库创建表与完整性约束课件,SQL数据库创建表与完整性约束课件,