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

4.1.3SQL语言功能概述SQL功能命令动词数据查询SELECT数据定义CREATE、DROP、ALTER数据操纵INSERT、UPDATE、DELETE数据控制GRANT、REVOKE四局部：数据定义功能、数据控制功能、数据查询功能和数据操纵功能。4.2SQL的数据类型数值型字符串型日期时间型货币型数值型准确型整数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<=8000Varchar〔n〕：不定长存储〔按实际长度存储〕，长度最大不超过n,n<=8000注：n为字符个数Text：存储大于8000字节的文本统一字符编码字符串类型nchar〔n〕:定长存储，n<=4000nvarchar〔n〕：不定长存储，长度最大不超过n,n<=4000ntext：存储大于8000字节的文本特点：每个字符占两个字节二进制字符串类型Binary(n)：固定长度，n<=8000。Varbinary(n)：可变长度，n<＝8000。注：n为二进制数据的字节数image：大容量、可变长二进制字符数据，可用于存储文件。日期时间型Datetime：8字节，年月日时分秒毫秒 〔例：‘2001/08/0310:30:00.000’〕

SmallDateTime：4字节，年月日时分〔例：‘2001/08/0310:30:00’〕日期、时间的输入格式货币类型Money：8个字节，精确到货币单位的千分之十。Smallmoney：4个字节，精确到货币单位的千分之十。限制到小数点后4位。可以带有适当的货币符号。例如，100英镑可表示为£100。4.3根本表的定义、删除及修改4.3.1根本表的定义与删除4.3.2修改表结构4.3.1根本表的定义与删除1．定义根本表使用SQL语言中的CREATETABLE语句实现，其一般格式为：CREATETABLE<表名>〔<列名><数据类型>[列级完整性约束定义]{,<列名><数据类型>[列级完整性约束定义］…}[,表级完整性约束定义]〕在列级完整性约束定义处可以定义的约束NOTNULL：限制列取值非空。DEFAULT：给定列的默认值。UNIQUE：限制列取值不重。CHECK：限制列的取值范围。PRIMARYKEY：指定本列为主码。FOREIGNKEY：定义本列为引用其他表的外码。使用形式为：［FOREIGNKEY〔<外码列名>〕］REFERENCES<外表名>〔<外表列名>〕几点说明NOTNULL和DEFAULT只能是列级完整性约束；其他约束均可在表级完整性约束处定义。注意以下几点：第一，如果CHECK约束是定义多列之间的取值约束，那么只能在表级完整性约束处定义；第二，如果表的主码由多个列组成，那么也只能在表级完整性约束处定义，并将主码列用括号括起来，即：PRIMARYKEY〔列1｛[，列2]…｝〕；第三，如果在表级完整性约束处定义外码，那么“FOREIGNKEY(<外码列名>)”局部不能省。约束定义

①列取值非空约束<列名><类型>NOTNULL

例：snamechar(10)NOTNULL约束定义〔续〕②表主码约束在定义列时定义主码〔仅用于单列主码〕列定义PRIMARYKEY例：SNOchar(7)PRIMARYKEY在定义完列时定义主码〔用于单列或多列主码〕PRIMARYKEY〔<列名序列>〕 例：PRIMARYKEY(SNO)PRIMARYKEY(SNO，CNO)约束定义〔续〕③外码引用约束指明本表外码列引用的表及表中的主码列。

[FOREIGNKEY〔<本表列名>〕]REFERENCES<外表名>〔<外表主码列名>〕例：FOREIGNKEY〔sno〕REFERENCES学生表〔sno〕 约束定义〔续〕④默认值约束格式：DEFAULT默认值例：定义系的默认值为“计算机系”。

DEFAULT‘计算机系’约束定义〔续〕⑤CHECK约束格式：CHECK(约束表达式)例：定义成绩大于等于0。

CHECK(grade>=0)约束定义〔续〕⑥UNIQUE约束在列级约束定义〔仅用于单列约束〕列定义UNIQUE例：SNAMEchar(7)UNIQUE在表级约束定义〔用于单列或多列组合约束〕UNIQUE〔<列名序列>〕 例：UNIQUE(SNO，CNO)用于限制在一个列中不能有重复的值。用在事实上具有惟一性的属性列上，比方每个人的身份证号码、驾驶证号码等均不能有重复值。注意：允许有一个空值；在一个表中可以定义多个UNIQUE约束；可以在一个列或多个列上定义UNIQUE约束。创立学生表CREATETABLEStudent(Snochar(7)PRIMARYKEY,Snamechar(10)NOTNULL,Ssexchar(2)CHECK(Ssex='男'ORSsex='女'),SagetinyintCHECK(Sage>=15ANDSage<=45),Sdeptchar(20)DEFAULT'计算机系')创立课程表CREATETABLECourse(Cnochar(10)NOTNULL,Cnamechar(20)NOTNULL,CcredittinyintCHECK(Ccredit>0),SemestertinyintCHECK(Semester>0),PeriodintCHECK(Period>0),PRIMARYKEY(Cno))

创立SC表CREATETABLESC(Snochar(7)NOTNULL,Cnochar(10)NOTNULL,Gradetinyint,CHECK(Grade>=0andGrade<=100),PRIMARYKEY(Sno,Cno),FOREIGNKEY(Sno)REFERENCESStudent(Sno),FOREIGNKEY(Cno)REFERENCESCourse(Cno))删除表当确信不再需要某个表时，可以将其删除删除表时会将与表有关的所有对象一起删掉，包括表中的数据。删除表的语句格式为：DROPTABLE<表名>{[,<表名>]…}例：删除test表的语句为：DROPTABLEtest4.3.2修改表结构在定义完表之后，如果需求有变化，比方添加列、删除列或修改列定义，可以使用ALTERTABLE语句实现。ALTERTABLE语句可以对表添加列、删除列、修改列的定义、定义主码、外码，也可以添加和删除约束。修改表结构语法ALTERTABLE<表名>[ALTERCOLUMN<列名><新数据类型>]|[ADD[COLUMN]<列名><数据类型>|[DROPCOLUMN<列名>]|[ADDPRIMARYKEY〔列名[，…n]〕]|[ADDFOREIGNKEY〔列名〕REFERNECES表名〔列名〕]例如例2．为SC表添加“修课类别”列，此列的定义为：XKLBchar(4)ALTERTABLESCADDXKLBchar(4)NULL

例如例3．将新添加的XKLB的类型改为char(6)。ALTERTABLESCALTERCOLUMNXKLBchar(6)例如例4．删除Course表的Period列。ALTERTABLECourseDROPCOLUMNPeriod

指数据的正确性、有效性和相容性然而大多数情况下，我们所提到的完整性，实际是指完整性规那么为保证完整性，数据应该满足的约束条件，又称为完整性约束完整性的原义关系模型中的完整性

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

实体完整性目的：保证每个元组〔所代表的实体〕是可标识〔区分〕的规那么：元组在主码的每个属性上取唯一值，且不能为空意义：关系往往对应一个实体集，这时关系中的每个元组对应一个实体现实世界中的实体是可区分的，关系中的元组也必须是可区分的主码起到唯一标识〔区分〕每个元组的作用，所以它必须是唯一的，同时也是非空的三种关系完整性规那么

三种关系完整性规那么要点：如果一个关系的主码由多个属性构成，那么每个属性都不能取空值三种关系完整性规那么参照完整性复习：外部码外部码定义：关系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〔运行时〕完整性规那么的检查：通过专门的“完整性子系统”主码约束规那么：主码值不允许空，也不允许出现重复定义：在创立或修改关系的定义语句中，用PRIMARYKEY关键字声明主码的同时，即定义了一个主码约束createtable关系名(……[,primarykey(属性{,属性})]……)主码约束例：createtable选修(学号VARCHAR(10),课程号VARCHAR(10),……,primarykey(学号,课程号),……)唯一约束规那么：对指定的一个属性或属性组合，不允许出现重复值定义：在创立或修改关系的定义语句中，用Unique关键字声明createtable关系名(……[,unique(属性{,属性})]……)主码约束例：createtable学生(……,身份证号VARCHAR(15),……,unique(身份证号),……)外部码约束规那么：外部码要么取空值，要么取对应的某个主码值定义：在创立或修改关系的定义语句中，用FOREIGNKEY关键字声明一个外部码的同时，即定义了一个外部码约束createtable表名(……{,foreignkey(属性名{,属性名})references关系名(属性名{,属性名})ondelete<参照动作>onupdate<参照动作>}参照动作说明当某个主码值被删除/更新时〔这个主码值在被参照关系中〕，如何处理对应的外部码值〔这些外部码值在参照关系中〕RESTRICT方式：仅当没有任何对应的外码值时，才可以删除/更新这个主码值，否那么系统拒绝执行此操作CASCADE方式：连带将所有对应的外码值一块删除/更新〔删除外码值，实际上就是将所在的元组删除掉〕SETNULL方式：将所有对应的外码值设为空值外部码约束外部码约束

CASCADE方式例如：当主码值被删除/更新时，连带删除/更新对应的外码值外部码约束

RESTRICT方式例如：仅当没有任何对应的外码值时，才可以删除/更新主码值，否那么系统拒绝执行此操作外部码约束

外部码约束

外部码约束

SETNULL方式例如：主码值被删除/更新时，将对应的外码值设为空值外部码约束

外部码约束例：createtable学生(学号VARCHAR(4),姓名……,primarykey(学号),……)createtable选修(……,foreignkey(学号)references学生(学号)ondeleterestrictonupdatecascade,……,)外部码约束外部码约束非空约束

非空约束规那么：属性值不允许取空值定义：在定义关系的语句中，声明某个属性不能取空值〔notnull〕，即定义了一个非空约束createtable关系名(……,属性名域[default缺省值][notnull]……,)非空约束例：createtable学生(……,姓名VARCHAR(20)notnull,……,)完整性约束命名子句CONSTRAINT<完整性约束条件名>

[PRIMARYKEY|GOREIGNKEY短语|CHECK短语]例：对雇员表和工作表添加主码约束ALTERTABLE雇员表ADDCONSTRAINTPK_EMPPRIMARYKEY〔雇员编号〕ALTERTABLE工作表ADDCONSTRAINTPK_JOBPRIMARYKEY〔工