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

4.1.3 SQL语言功能概述SQL功能功能命令动词命令动词数据查询数据查询SELECT数据定义数据定义CREATE、DROP、ALTER数据操纵数据操纵INSERT、UPDATE、DELETE数据控制数据控制GRANT、REVOKE四部分：数据定义功能、数据控制功能、数据查询功能和数据操纵功能。SQL数据库创建表与完整性约束4.2 SQL的数据类型数值型 字符串型日期时间型货币型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字节SQL数据库创建表与完整性约束字符串型普通编码字符串类型统一字符编码字符串类型二进制字符串类型SQL数据库创建表与完整性约束普通编码字符串类型Char（n）:定长存储，n=8000 Varchar（n）：不定长存储（按实际长度存储），长度最大不超过n,n=8000 注：n 为字符个数Text：存储大于8000字节的文本SQL数据库创建表与完整性约束统一字符编码字符串类型nchar（n）:定长存储，n=4000 nvarchar（n）：不定长存储，长度最大不超过n,n=4000 ntext：存储大于8000字节的文本特点：每个字符占两个字节SQL数据库创建表与完整性约束二进制字符串类型Binary(n)：固定长度，n=8000。Varbinary(n)：可变长度，n 8000。注：n为二进制数据的字节数image：大容量、可变长二进制字符数据，可用于存储文件。SQL数据库创建表与完整性约束日期时间型日期时间型Datetime：8字节，年月日时分秒毫秒（例：2001/08/03 10:30:00.000）SmallDateTime：4字节，年月日时分 （例：2001/08/03 10:30:00）日期、时间的输入格式SQL数据库创建表与完整性约束货币类型Money：8 个字节，精确到货币单位的千分之十。Smallmoney：4 个字节，精确到货币单位的千分之十。限制到小数点后 4 位。可以带有适当的货币符号。例如，100 英镑可表示为 100。SQL数据库创建表与完整性约束4.3 基本表的定义、删除及修改 4.3.1 基本表的定义与删除 4.3.2 修改表结构 SQL数据库创建表与完整性约束4.3.1 基本表的定义与删除1定义基本表 使用SQL语言中的CREATE TABLE语句实现，其一般格式为：CREATE TABLE （列级完整性约束定义,列级完整性约束定义 ,表级完整性约束定义 ）SQL数据库创建表与完整性约束在列级完整性约束定义处可以定义的约束NOT NULL：限制列取值非空。DEFAULT：给定列的默认值。UNIQUE：限制列取值不重。CHECK：限制列的取值范围。PRIMARY KEY：指定本列为主码。FOREIGN KEY：定义本列为引用其他表的外码。使用形式为：FOREIGN KEY（）REFERENCES（）SQL数据库创建表与完整性约束几点说明NOT NULL和DEFAULT只能是列级完整性约束；其他约束均可在表级完整性约束处定义。注意以下几点：第一，如果CHECK约束是定义多列之间的取值约束，则只能在表级完整性约束处定义；第二，如果表的主码由多个列组成，则也只能在表级完整性约束处定义，并将主码列用括号括起来，即：PRIMARY KEY（列1，列2 ）；第三，如果在表级完整性约束处定义外码，则“FOREIGN KEY()”部分不能省。SQL数据库创建表与完整性约束约束定义 列取值非空约束 NOT NULL例：sname char(10)NOT NULLSQL数据库创建表与完整性约束约束定义（续）表主码约束表主码约束在定义列时定义主码（仅用于单列主码）列定义 PRIMARY KEY例：SNO char(7)PRIMARY KEY在定义完列时定义主码（用于单列或多列主码）PRIMARY KEY（）例：PRIMARY KEY(SNO)PRIMARY KEY(SNO，CNO)SQL数据库创建表与完整性约束约束定义（续）外码引用约束外码引用约束指明本表外码列引用的表及表中的主码列。FOREIGN KEY（）REFERENCES（）例：FOREIGN KEY（sno）REFERENCES 学生表（sno）SQL数据库创建表与完整性约束约束定义（续）默认值约束默认值约束格式：DEFAULT 默认值例：定义系的默认值为“计算机系”。DEFAULT 计算机系SQL数据库创建表与完整性约束约束定义（续）CHECKCHECK约束约束格式：CHECK(约束表达式)例：定义成绩大于等于0。CHECK(grade=0)SQL数据库创建表与完整性约束约束定义（续）UNIQUEUNIQUE约束约束在列级约束定义（仅用于单列约束）列定义 UNIQUE例：SNAME char(7)UNIQUE在表级约束定义（用于单列或多列组合约束）UNIQUE（）例：UNIQUE(SNO，CNO)SQL数据库创建表与完整性约束用于限制在一个列中不能有重复的值。用于限制在一个列中不能有重复的值。用在事实上具有惟一性的属性列上，比如每用在事实上具有惟一性的属性列上，比如每个人的身份证号码、驾驶证号码等均不能有个人的身份证号码、驾驶证号码等均不能有重复值。重复值。注意：注意：允许有一个空值；允许有一个空值；在一个表中可以定义多个在一个表中可以定义多个UNIQUEUNIQUE约束；约束；可以在一个列或多个列上定义可以在一个列或多个列上定义UNIQUEUNIQUE约束。约束。SQL数据库创建表与完整性约束创建学生表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)SQL数据库创建表与完整性约束创建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)SQL数据库创建表与完整性约束删除表当确信不再需要某个表时，可以将其删除删除表时会将与表有关的所有对象一起删掉，包括表中的数据。删除表的语句格式为：DROP TABLE ,例：删除test表的语句为：DROP TABLE testSQL数据库创建表与完整性约束4.3.2 修改表结构 在定义完表之后，如果需求有变化，比如添加列、删除列或修改列定义，可以使用ALTER TABLE语句实现。ALTER TABLE语句可以对表添加列、删除列、修改列的定义、定义主码、外码，也可以添加和删除约束。SQL数据库创建表与完整性约束修改表结构语法ALTER TABLE ALTER COLUMN|ADD COLUMN|DROP COLUMN|ADD PRIMARY KEY（列名，n ）|ADD FOREIGN KEY（列名）REFERNECES 表名（列名）SQL数据库创建表与完整性约束示例例2为SC表添加“修课类别”列，此列的定义为：XKLB char(4)ALTER TABLE SC ADD XKLB char(4)NULL SQL数据库创建表与完整性约束示例例3将新添加的XKLB的类型改为 char(6)。ALTER TABLE SC ALTER COLUMN XKLB char(6)SQL数据库创建表与完整性约束示例例4删除Course表的Period列。ALTER TABLE Course DROP COLUMN Period SQL数据库创建表与完整性约束 指数据的正确性、有效性和相容性指数据的正确性、有效性和相容性 然而大多数情况下，我们所提到的完整性，实际是指完整性然而大多数情况下，我们所提到的完整性，实际是指完整性规则规则为保证完整性，数据应该满足的约束条件，又称为完整性约束为保证完整性，数据应该满足的约束条件，又称为完整性约束完整性的原义完整性的原义SQL数据库创建表与完整性约束关系模型中的完整性关系模型中的完整性 关系完整性关系完整性在关系模型中的地位在关系模型中的地位三要素之一（关系三要素之一（关系,关系完整性关系完整性,关系操作）关系操作）三种类型三种类型实体完整性；实体完整性；参照完整性；参照完整性；用户定义完整性；用户定义完整性；SQL数据库创建表与完整性约束三种关系完整性规则三种关系完整性规则解决现实世界的三个问题解决现实世界的三个问题如何保证一个实体是可识别（区分）的如何保证一个实体是可识别（区分）的方法：实体完整性方法：实体完整性如何保证能够从一个实体找到另一个相关联的实体，而不如何保证能够从一个实体找到另一个相关联的实体，而不会出现找不到的情况会出现找不到的情况方法：参照完整性方法：参照完整性如何保证用于描述实体的属性，其取值是合理的如何保证用于描述实体的属性，其取值是合理的方法：用户定义完整性方法：用户定义完整性SQL数据库创建表与完整性约束三种关系完整性规则三种关系完整性规则实体完整性实体完整性目的：保证每个元组（所代表的实体）是可标识目的：保证每个元组（所代表的实体）是可标识（区分）的（区分）的规则：元组在主码的每个属性上取唯一值，且不规则：元组在主码的每个属性上取唯一值，且不能为空能为空意义：意义：关系往往对应一个实体集，这时关系中的每个元关系往往对应一个实体集，这时关系中的每个元组对应一个实体组对应一个实体现实世界中的实体是可区分的，关系中的元组也现实世界中的实体是可区分的，关系中的元组也必须是可区分的必须是可区分的主码起到唯一标识（区分）每个元组的作用，所主码起到唯一标识（区分）每个元组的作用，所以它必须是唯一的，同时也是非空的以它必须是唯一的，同时也是非空的SQL数据库创建表与完整性约束三种关系完整性规则三种关系完整性规则三种关系完整性规则要点：三种关系完整性规则要点：如果一个关系的主码由多个属性构成，那如果一个关系的主码由多个属性构成，那么每个属性都不能取空值么每个属性都不能取空值SQL数据库创建表与完整性约束三种关系完整性规则三种关系完整性规则参照完整性参照完整性SQL数据库创建表与完整性约束复习：外部码复习：外部码 外部码外部码定义：关系定义：关系R1R1中的单个属性或属性集合，对中的单个属性或属性集合，对应另一个关系应另一个关系R2R2的主码的主码R1R1称为参照关系，称为参照关系，R2R2称为被参照关系称为被参照关系来源：由联系集转化而来，反映实体（元组）来源：由联系集转化而来，反映实体（元组）之间的联系之间的联系SQL数据库创建表与完整性约束复习：外部码复习：外部码SQL数据库创建表与完整性约束三种关系完整性规则三种关系完整性规则 参照完整性参照完整性目的：保证外码引用的是一个有效的主码目的：保证外码引用的是一个有效的主码规则：如果关系规则：如果关系R R的外部码对应关系的外部码对应关系S S的主码，则的主码，则R R每个元组在每个元组在外部码上的取值必须满足：外部码上的取值必须满足：或者等于空值或者等于空值 或者等于某个对应的主码值（或者等于某个对应的主码值（S S某个元组的主码值）某个元组的主码值）意义：意义：在被参照关系在被参照关系S S中，中，一个主码值代表所在的元组（一个实体）一个主码值代表所在的元组（一个实体）在参照关系在参照关系R R中，元组的外部码中，元组的外部码要么取空值，表示不和任何要么取空值，表示不和任何S S元组（实体）存在联系元组（实体）存在联系要么取要么取S S的某个主码值，表示表示联系是存在的，且涉及的是的某个主码值，表示表示联系是存在的，且涉及的是这个主码值所代表的这个主码值所代表的S S元组（实体）元组（实体）如果违反以上两种情况，表示联系涉及了不存在的实体，这是如果违反以上两种情况，表示联系涉及了不存在的实体，这是不允许的不允许的SQL数据库创建表与完整性约束思考思考Thinking Thinking：根据外部码取值的可能性，我们在定义外部根据外部码取值的可能性，我们在定义外部码的属性类型时，应遵循什么原则码的属性类型时，应遵循什么原则?SQL数据库创建表与完整性约束三种关系完整性规则三种关系完整性规则用户定义完整性用户定义完整性目的：保证实体属性的取值有效，等等目的：保证实体属性的取值有效，等等规则：用户根据具体的应用环境定义规则：用户根据具体的应用环境定义例如例如年龄的取值范围为年龄的取值范围为0 0到到200200，性别只能是，性别只能是“男男”或或“女女”职工编号是职工编号是4 4位整数位整数意义：意义：反映了现实世界的真实情况，即实体的属性必须满反映了现实世界的真实情况，即实体的属性必须满足要满足这些条件，反映了程序编制的要求足要满足这些条件，反映了程序编制的要求SQL数据库创建表与完整性约束思考思考ThinkingThinking 什么操作会破坏关系完整性。查询操作，还什么操作会破坏关系完整性。查询操作，还是修改操作是修改操作?SQL数据库创建表与完整性约束课堂练习课堂练习 依次执行如下操作，哪些能够成功？依次执行如下操作，哪些能够成功？1 1零件关系：零件关系：添加添加(3,(3,绿绿,null),null)2 2供应商关系：供应商关系：添加添加(null,(null,四化四化,广州广州)3 3供应商关系：供应商关系：添加添加(E,(E,北电北电,广州广州)4 4零件关系：零件关系：修改修改(2,(2,白白,A),A)为为(2,(2,黑黑,F),F)5 5供应商关系：供应商关系：删除删除(A,(A,红星红星,北京北京)6 6零件关系：零件关系：修改修改(3,(3,蓝蓝,B),B)为为(3,(3,蓝蓝,E),E)SQL数据库创建表与完整性约束完整性控制概述完整性控制概述完整性控制完整性控制目的：保证数据的正确性、有效性和相容性目的：保证数据的正确性、有效性和相容性由由DBMSDBMS负责，负责，包括包括两方面两方面完整性规则的定义：通过完整性规则的定义：通过SQLSQL（运行时）完整性规则的检查：通过专门的（运行时）完整性规则的检查：通过专门的“完整完整性子系统性子系统”SQL数据库创建表与完整性约束主码约束主码约束规则：主码值不允许空，也不允许出现重复规则：主码值不允许空，也不允许出现重复定义：在创建或修改关系的定义语句中，用定义：在创建或修改关系的定义语句中，用PRIMARY KEYPRIMARY KEY关键字声明主码的同时，即定义了一关键字声明主码的同时，即定义了一个主码约束个主码约束create table create table 关系名关系名(,primary key primary key(属性属性,属性属性)SQL数据库创建表与完整性约束主码约束主码约束例：例：create table create table 选修选修(学号学号VARCHAR VARCHAR(10),(10),课程号课程号VARCHAR VARCHAR(10),(10),primary key primary key(学号学号,课程号课程号),),)SQL数据库创建表与完整性约束唯一约束唯一约束规则：对指定的一个属性或属性组合，不允许出现重规则：对指定的一个属性或属性组合，不允许出现重复值复值定义：在创建或修改关系的定义语句中，用定义：在创建或修改关系的定义语句中，用UniqueUnique关关键字声明键字声明create table create table 关系名关系名(,unique unique(属性属性,属性属性)SQL数据库创建表与完整性约束主码约束主码约束例：例：create table create table 学生学生(,身份证号身份证号VARCHAR VARCHAR(15),(15),unique unique(身份证号身份证号),),)SQL数据库创建表与完整性约束外部码约束外部码约束规则：外部码要么取空值，要么取对应的某个主码值规则：外部码要么取空值，要么取对应的某个主码值 定义：在创建或修改关系的定义语句中，用定义：在创建或修改关系的定义语句中，用FOREIGN KEYFOREIGN KEY关键字声明一个外部码的同时，即定关键字声明一个外部码的同时，即定义了一个外部码约束义了一个外部码约束create table create table 表名表名(,foreign key foreign key(属性名属性名,属性名属性名)references references 关系名关系名(属性名属性名,属性名属性名)on delete on delete on update on update SQL数据库创建表与完整性约束参照动作参照动作说明当某个主码值被删除说明当某个主码值被删除/更新时（这个主码值在被更新时（这个主码值在被参照关系中），如何处理对应的外部码值（这些外参照关系中），如何处理对应的外部码值（这些外部码值在参照关系中）部码值在参照关系中）RESTRICT RESTRICT 方式：仅当没有任何对应的外码值时，方式：仅当没有任何对应的外码值时，才可以删除才可以删除/更新这个主码值，否则系统拒绝执行此更新这个主码值，否则系统拒绝执行此操作操作CASCADE CASCADE 方式：连带将所有对应的外码值一块删方式：连带将所有对应的外码值一块删除除/更新（删除外码值，实际上就是将所在的元组删更新（删除外码值，实际上就是将所在的元组删除掉）除掉）SET NULL SET NULL 方式：将所有对应的外码值设为空值方式：将所有对应的外码值设为空值外部码约束外部码约束SQL数据库创建表与完整性约束外部码约束外部码约束CASCADECASCADE方式示例：当主码值被删除方式示例：当主码值被删除/更新更新时，连带删除时，连带删除/更新对应的外码值更新对应的外码值SQL数据库创建表与完整性约束外部码约束外部码约束SQL数据库创建表与完整性约束RESTRICTRESTRICT方式示例：仅当没有任何对应的外方式示例：仅当没有任何对应的外码值时，才可以删除码值时，才可以删除/更新主码值，否则系统更新主码值，否则系统拒绝执行此操作拒绝执行此操作外部码约束外部码约束SQL数据库创建表与完整性约束外部码约束外部码约束SQL数据库创建表与完整性约束外部码约束外部码约束SET NULLSET NULL方式示例：主码值被删除方式示例：主码值被删除/更新时，更新时，将对应的外码值设为空值将对应的外码值设为空值SQL数据库创建表与完整性约束外部码约束外部码约束SQL数据库创建表与完整性约束外部码约束外部码约束例：例：create table create table 学生学生(学号学号VARCHAR VARCHAR(4),(4),姓名姓名,primary key primary key(学号学号),),)create table create table 选修选修(,foreign key foreign key(学号学号)references references 学生学生(学号学号)on delete restrict on update cascade on delete restrict on update cascade,)SQL数据库创建表与完整性约束外部码约束外部码约束SQL数据库创建表与完整性约束外部码约束外部码约束SQL数据库创建表与完整性约束非空约束非空约束非空约束非空约束规则：属性值不允许取空值规则：属性值不允许取空值定义：在定义关系的语句中，声明某个属性不能取定义：在定义关系的语句中，声明某个属性不能取空值（空值（notnullnotnull），即定义了一个非空约束），即定义了一个非空约束create table create table 关系名关系名(,属性名域属性名域 default default 缺省值缺省值 not nullnot null ,)SQL数据库创建表与完整性约束非空约束非空约束例：例：create table create table 学生学生(,姓名姓名VARCHAR(20)not null,VARCHAR(20)not null,)SQL数据库创建表与完整性约束完整性约束命名子句CONSTRAINT PRIMARY KEY|GOREIGN KEY 短语|CHECK短语 SQL数据库创建表与完整性约束例：对雇员表和工作表添加主码约束ALTER TABLE 雇员表 ADD CONSTRAINT PK_EMP PRIMARY KEY（雇员编号）ALTER TABLE 工作表 ADD CONSTRAINT PK_JOB PRIMARY KEY（工作编号）完整性约束命名子句SQL数据库创建表与完整性约束添加UNIQUE约束的语法格式为：ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE（,n）例为雇员表的“电话”列添加UNIQUE约束。ALTER TABLE 雇员表 ADD CONSTRAINT UK_SID UNIQUE（电话）完整性约束命名子句SQL数据库创建表与完整性约束添加添加DEFAULTDEFAULT约束的语法格式为：约束的语法格式为：ALTER TABLE ALTER TABLE 表名表名 ADD CONSTRAINT ADD CONSTRAINT 约束名约束名 DEFAULT DEFAULT 默认值默认值 FOR FOR 列名列名例定义雇员表的工资的默认值为例定义雇员表的工资的默认值为10001000。ALTER TABLE ALTER TABLE 雇员雇员 ADD CONSTRAINT DF_SALARYADD CONSTRAINT DF_SALARYDEFAULT 1000 FOR DEFAULT 1000 FOR 工资工资完整性约束命名子句SQL数据库创建表与完整性约束用于限制列的取值在指定的范围内，使数据库中存放的值都是有意义的。系统在执行INSERT语句和UPDATE语句时自动检查CHECK约束。CHECK约束可约束同一个表中多个列之间的取值关系。添加CHECK约束的语法格式为：ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK（逻辑表达式）完整性约束命名子句SQL数据库创建表与完整性约束例1限制雇员的工资必须大于等于200。ALTER TABLE 雇员 ADD CONSTRAINT CHK_SalaryCHECK(工资=200)例2限制工资表的最低工资小于等于最高工资。ALTER TABLE 工作 ADD CONSTRAINT CHK_Job_Salary CHECK(最低工资=最高工资)完整性约束命名子句SQL数据库创建表与完整性约束SQL数据库创建表与完整性约束