数据库系统及应用（第5版）课件 第6章 SQL数据定义与完整性约束的实现

SQL的架构和定义1.什么是架构2.

定义架构主要内容1.什么是架构(schema)架构：是一个逻辑上的概念，是数据库中一组用户对象的逻辑集合。通过架构（schema）管理对象。产品模式订购模式销售模式2.定义架构定义架构的命令CREATESCHEMAschema_name[AUTHORIZATIONowner_name]架构通常由数据库管理员创建，使用短语AUTHORIZATIONowner_name可以指定架构的管理者（默认是创建者）。删除架构的命令DROPSCHEMAschema_name只有在架构为空，即架构中不包含任何对象时才可以删除架构。架构的使用（对象的引用格式）030201每一个数据库对象都在某个架构下，为此引用对象的基本格式是

[<架构名>.]<对象名>例如创建表的命令格式是CREATETABLE[<架构名>.]<表名>(<列定义或描述>)再如查询的命令格式是SELECT…FROM[<架构名>.]<表名>…默认架构030201在引用对象时默认架构名可以省略系统预定义的默认架构是dbo例如：如下命令将在默认架构下创建表：CREATETABLE<表名>(<列定义或描述>)在CREATEUSER命令中可以为用户指定默认架构定义三个架构架构仓储仓库表库存表订货供应商表订购单表订购表基础职工表器件表保管表供应表CREATESCHEMA仓储CREATESCHEMA订货CREATESCHEMA基础定义表及其完整性约束

CREATETABLE命令的基本格式计算列的定义主要内容列的定义表级约束1.CREATETABLE命令的基本格式一个表由若干列构成，在SQLServer中可以定义实列(简称列，column_definition)定义虚列(即计算列，computed_column_definition)理解：<column_definition><computed_column_definition><table_constraint>CREATETABLE[schema_name].table_name({<column_definition>|<computed_column_definition>}[<table_constraint>][,...n])2.列的定义(<column_definition>)基本格式column_name<data_type>[NULL|NOTNULL][[CONSTRAINTconstraint_name]PRIMARYKEY|UNIQUE|[FOREIGNKEY]REFERENCES[schema_name.]referenced_table_name[(ref_column)][ONDELETE{NOACTION|CASCADE|SETNULL|SETDEFAULT}][ONUPDATE{NOACTION|CASCADE|SETNULL|SETDEFAULT}]|CHECK(logical_expression)][DEFAULTconstant_expression]CREATETABLE[schema_name].table_name({<column_definition>|<computed_column_definition>}[<table_constraint>][,...n])SQLServer的数据类型分类数据类型备注数字类型整数bigint,integer(或int),smallint,tinyint非整数定点数字decimal(p,s)或numeric(p,s)浮点数字float,real货币类型money,smallmoney字符类型ASCIIchar(n),varchar(n),varchar(max)需用单引号Unicodenchar(n),nvarchar(n),nvarchar(max)日期和时间类型datetime,smalldatetime需用单引号二进制类型binary,varbinary,varbinary(max)特殊类型cursor,sql_variant,table,XML2.列的定义(<column_definition>)column_name<data_type>[NULL|NOTNULL][[CONSTRAINTconstraint_name]

PRIMARYKEY|UNIQUE|[FOREIGNKEY]REFERENCES[schema_name.]referenced_table_name[(ref_column)][ONDELETE{NOACTION|CASCADE|SETNULL|SETDEFAULT}][ONUPDATE{NOACTION|CASCADE|SETNULL|SETDEFAULT}]|CHECK(logical_expression)][DEFAULTconstant_expression]CREATETABLE[schema_name].table_name({<column_definition>|<computed_column_definition>}[<table_constraint>][,...n])PRIMARYKEY和UNIQUE约束的区别？如何实现候选关键字约束？问3.计算列的定义column_nameAScomputed_column_expression[

PERSISTED[NOTNULL]][[CONSTRAINTconstraint_name]{PRIMARYKEY|UNIQUE}|[FOREIGNKEY]REFERENCESreferenced_table_name[(ref_column)][ONDELETE{NOACTION|CASCADE}][ONUPDATE{NOACTION}]|CHECK(logical_expression)]CREATETABLE[schema_name].table_name({<column_definition>|<computed_column_definition>}[<table_constraint>][,...n])为什么ONDELETE和ONUPDATE少了一些内容？问如果某个完整性约束与多个列相关，则这样的完整性约束不能定义在单个列上，这时候就需要表级完整性约束。单个列上的完整性约束也可以用表级完整性约束的形式来定义。在定义表时，需要先定义被参照表，后定义参照表。4.表级约束仓储数据库例1：定义仓库表要求：仓库表属于仓储架构，包含:

仓库号（主关键字）城市面积（仓库的面积值是大于0的整数）CREATETABLE仓储.仓库(

仓库号

CHAR(6) ,

城市

CHAR(10),

面积

INT )CHECK(面积

>0)PRIMARYKEY,例2：定义职工表要求：职工表属于基础架构，包含：仓库号（参照仓库表的仓库号字段值,外键）职工号（主关键字）姓名工资（1000到5000之间，默认是1200班组长（参照本关系的职工号属性）CREAETABLE基础.职工(仓库号CHAR(6)CONSTRAINTref_wh FOREIGNKEYREFERENCES仓储.仓库(仓库号),职工号

CHAR(8)PRIMARYKEY,姓名

CHAR(10),工资

numeric(8,2)CHECK(工资>=1000AND工资<=5000)DEFAULT1200,班组长CHAR(8)FOREIGNKEYREFERENCES基础.职工(职工号))例3：定义器件表要求：器件表属于基础架构，包含：器件号（主关键字）器件名称规格（其中器件名称和规格的组合具有候选关键字的性质）单价CREATETABLE基础.器件(器件号

CHAR(6)PRIMARYKEY,器件名称

CHAR(20)NOTNULL,规格

CHAR(16)NOTNULL,单价

SMALLMONEY,UNIQUE(器件名称,规格))

在一个表中只能定义一个主关键字，其他具有候选关键字特征的字段应该定义非空值和唯一性约束。表级约束例4：定义库存表库存表属于仓储架构，包含：仓库号（参照仓库关系的仓库号）器件号（参照器件关系的器件号）数量（库存数量应该大于等于0）其中，主关键字由仓库号和器件号构成CREATETABLE仓储.库存(仓库号CHAR(6)FOREIGNKEYREFERENCES仓储.仓库,器件号

CHAR(6)FOREIGNKEYREFERENCES基础.器件,数量

INTCHECK(数量>=0),PRIMARYKEY(仓库号,器件号))21例5：定义供应商表要求：供应商表属于订货架构，它包含：供应商号（主关键字）供应商名地址CREATETABLE订货.供应商(

供应商号CHAR(5)PRIMARYKEY,

供应商名CHAR(20),

地址CHAR(20))22例6：定义订购单表要求：订购单表属于订货架构，它包含：订购单号（主关键字）经手人（非空，参照职工关系的职工号）供货方（参照供应商关系的供应商号）订购日期（默认值是系统的当前日期）金额CREATETABLE订货.订购单(订购单号CHAR(5)PRIMARYKEY,经手人

CHAR(8)NOTNULLFOREIGNKEYREFERENCES基础.职工(职工号),供货方

CHAR(5)NULLFOREIGNKEYREFERENCES订货.供应商(供应商号),订购日期DATETIMEDEFAULTgetdate(),金额

MONEYNULL)23例7：定义订购明细表要求：订购明细表属于订货架构，它包含：订购单号（参照订购单关系的订购单号）序号（大于等于1）器件号（参照器件表的器件号）单价数量（大于等于0）。其中主关键字由订购单号和序号共同构成CREATETABLE订货.订购明细(订购单号

CHAR(5)FOREIGNKEYREFERENCES订货.订购单ONDELETECASCADEONUPDATECASCADE,序号

SMALLINTCHECK(序号>=1),器件号CHAR(6)FOREIGNKEYREFERENCES基础.器件,单价

SMALLMONEY,数量

INTCONSTRAINTnumCHECK(数量>=0),PRIMARYKEY(订购单号,序号))插入操作及其完整性约束插入语句主要内容插入操作涉及约束SQL插入语句INSERT常用格式：INSERTINTO[schema_name.]table_name[(column_list)]VALUES({expression|DEFAULT|NULL}[,...n])1234schema_name指出模式名table_name指出表名column_list给出插入操作所涉及列的列表（默认是表的全部列）{expression|DEFAULT|NULL}[,...n]给出对应于column_list的各个列的值例：插入一条仓库记录。其中仓库号：WH1

；城市：北京；面积：500。INSERTINTO仓储.仓库VALUES('WH1','北京',500)或INSERTINTO仓储.仓库(仓库号,城市,面积)VALUES('WH1','北京',500)CREATETABLE仓储.仓库(仓库号CHAR(6)PRIMARYKEY,城市CHAR(10),面积INTCHECK(面积>0))仓库仓库号城市面积WH1北京5001.插入职工记录例：插入一条职工记录。其中仓库号：WH1；职工号：E2；姓名：王月；工资：1220；该职工没有直接领导（班组长）。INSERTINTO基础.职工VALUES('WH1','E2','王月',1220,NULL)CREATETABLE基础.职工(仓库号CHAR(6)CONSTRAINTref_whFOREIGNKEYREFERENCES仓储.仓库(仓库号),职工号CHAR(8)PRIMARYKEY,姓名

CHAR(10),工资

numeric(8,2)CHECK(工资>=1000AND

工资<=5000)DEFAULT1200,班组长

CHAR(8)FOREIGNKEYREFERENCES基础.职工(职工号))2.插入职工记录例：插入一条职工记录。其中仓库号为：WH2；职工号为：E1；姓名为：吴臣；工资为默认值（DEFAULT）；班组长为：E2。INSERTINTO基础.职工VALUES('WH2','E1','吴臣',DEFAULT,'E2')或INSERTINTO基础.职工(仓库号,职工号,姓名,班组长)VALUES('WH2','E1','吴臣','E2')2.插入职工记录CREATETABLE

基础.职工(仓库号CHAR(6)CONSTRAINTref_whFOREIGNKEYREFERENCES

仓储.仓库(仓库号),职工号

CHAR(8)PRIMARYKEY,姓名CHAR(10),工资numeric(8,2)CHECK(工资>=1000AND工资<=5000)DEFAULT1200,班组长

CHAR(8)FOREIGNKEYREFERENCES

基础.职工(职工号))303.插入订购单记录例：插入一条订购单记录。其中订购单号:OR67；经手人:E3；供货方:S7；订购日期：2011/06/23。INSERTINTO订货.订购单(订购单号,经手人,供货方,订购日期)VALUES('OR67','E3','S7','2011/06/23')CREATETABLE订货.订购单(订购单号CHAR(5)PRIMARYKEY,经手人

CHAR(8)NOTNULLFOREIGNKEYREFERENCES基础.职工(职工号),供货方CHAR(5)NULLFOREIGNKEYREFERENCES订货.供应商(供应商号),订购日期DATETIMEDEFAULTgetdate(),金额

MONEYNULL)注意日期型数据的常量表示，它是日期格式的字符串，系统会自动将其转换为日期时间型格式。插入操作涉及的

约束030201实体完整性约束参照完整性约束用户定义完整性约束1.插入仓库记录例：尝试执行如下命令：INSERTINTO仓储.仓库VALUES('WH1','天津',450)CREATETABLE仓储.仓库(仓库号CHAR(6)PRIMARYKEY,城市

CHAR(10),面积INTCHECK(面积

>0))违背实体完整性约束例：尝试执行如下命令：INSERTINTO基础.职工VALUES('WH7','E17','张扬',950,'E2')CREATETABLE基础.职工(仓库号CHAR(6)CONSTRAINTref_whFOREIGNKEYREFERENCES仓储.仓库(仓库号),职工号

CHAR(8)PRIMARYKEY,姓名

CHAR(10),工资

numeric(8,2)CHECK(工资>=1000AND工资<=5000)DEFAULT1200,班组长

CHAR(8)FOREIGNKEYREFERENCES基础.职工(职工号))违背参照完整性约束2.插入职工记录例：尝试执行如下命令：INSERTINTO基础.职工VALUES('WH1','E17','张扬',950,'E2')CREATETABLE基础.职工(仓库号CHAR(6)CONSTRAINTref_whFOREIGNKEYREFERENCES

仓储.仓库(仓库号),职工号CHAR(8)PRIMARYKEY,姓名

CHAR(10),工资

numeric(8,2)CHECK(工资>=1000AND工资<=5000)DEFAULT1200,班组长CHAR(8)FOREIGNKEYREFERENCES基础.职工(职工号))

违背CHECK约束2.插入职工记录3.插入器件记录例：尝试执行如下命令：INSERTINTO基础.器件VALUES('P17','内存','2GBDDR2800',120)CREATETABLE基础.器件(器件号CHAR(6)PRIMARYKEY,器件名称CHAR(20)NOTNULL,规格

CHAR(16)NOTNULL,单价

SMALLMONEY,UNIQUE(器件名称,规格))违背UNIQUE约束4.插入库存记录例：尝试缺少主属性的插入。尝试执行如下命令：INSERTINTO仓储.库存(仓库号,数量)VALUES('WH1',18)CREATETABLE仓储.库存(仓库号CHAR(6)FOREIGNKEYREFERENCES仓储.仓库,器件号CHAR(6)FOREIGNKEYREFERENCES基础.器件,数量

INTCHECK(数量>=0),PRIMARYKEY(仓库号,器件号))违背实体完整性约束4.插入库存记录例：尝试为主属性指定空值的插入。尝试执行如下命令：INSERTINTO仓储.库存VALUES('WH1',NULL,18)CREATETABLE仓储.库存(仓库号CHAR(6)FOREIGNKEYREFERENCES仓储.仓库,器件号CHAR(6)FOREIGNKEYREFERENCES基础.器件,数量

INTCHECK(数量>=0),PRIMARYKEY(仓库号,器件号))违背实体完整性约束5.插入订购明细记录CREATETABLE订货.订购明细(订购单号CHAR(5)FOREIGNKEYREFERENCES订货.订购单ONDELETECASCADEONUPDATECASCADE,序号

SMALLINTCHECK(序号>=1),器件号CHAR(6)FOREIGNKEYREFERENCES基础.器件,单价

SMALLMONEY,数量

INTCONSTRAINTnumCHECK(数量>=0),PRIMARYKEY(订购单号,序号))ALTERTABLE订货.订购明细ADD金额

AS单价*数量订购明细订购单号序号器件号单件数量金额5.插入订购明细记录例：插入订购明细关系的第一条记录。INSERTINTO

订货.订购明细VALUES('OR67',1,'P2',120,5,600)正确的插入命令是：INSERTINTO订货.订购明细VALUES('OR67',1,'P2',120,5)订购明细订购明细序号器件号单件数量金额OR671P21205600错误北京信息科技大学王晓波删除操作及其完整性约束1.删除语句2.删除操作涉及约束主要内容1.SQL删除语句DELETE常用格式：DELETE[FROM][schema_name.]table_name[WHERE<search_condition>]schema_name指出模式名；table_name指出从哪个表删除记录；WHERE<search_condition>用来指出删除记录的条件，默认是删除全部记录。例：删除订购明细表中订购单号为OR91的记录DELETEFROM订货.订购明细WHERE订购单号='OR91'或DELETE订货.订购明细WHERE订购单号='OR91'即关键词FROM可以省略。WHERE指定被删除记录满足的条件如果缺省WHERE短语将删除全部记录，所以在执行删除操作的时候要慎重。删除操作只与参照完整性有关，并且只有在删除被参照表的记录时才需要检查参照完整性，系统将根据定义参照完整性时确定的处理方法（拒绝删除、空值删除、默认值删除或级联删除）进行处理。2.删除操作涉及约束例：删除订购单表中订购单号为OR67的记录DELETE订货.订购单WHERE订购单号='OR67'45

成功！级联删除如果有‘OR67’的订购明细记录？例：删除器件表中器件号为P2的记录失败！拒绝删除DELETEFROM基础.器件WHERE器件号='P2'CREATETABLE仓储.库存(仓库号

CHAR(6)FOREIGNKEYREFERENCES仓储.仓库,器件号

CHAR(6)FOREIGNKEYREFERENCES基础.器件,数量INTCHECK(数量>=0),PRIMARYKEY(仓库号,器件号))例：删除仓库表中仓库号为WH4的记录DELETE仓储.仓库WHERE仓库号='WH4'成功！职工表中参照记录的仓库号值将被置为空值。ALTERTABLE基础.职工

ADDCONSTRAINTref_whFOREIGNKEY(仓库号)REFERENCES仓储.仓库(仓库号)ONDELETESETNULLONUPDATECASCADECREATETABLE仓储.库存(仓库号

CHAR(6)FOREIGNKEYREFERENCES仓储.仓库,器件号

CHAR(6)FOREIGNKEYREFERENCES基础.器件,数量INTCHECK(数量>=0),PRIMARYKEY(仓库号,器件号))例：删除仓库表中仓库号为WH3的记录DELETE仓储.仓库WHERE仓库号='WH3'

失败ALTERTABLE基础.职工

ADDCONSTRAINTref_whFOREIGNKEY(仓库号)REFERENCES仓储.仓库(仓库号)ONDELETESETNULLONUPDATECASCADECREATETABLE仓储.库存(仓库号

CHAR(6)FOREIGNKEYREFERENCES仓储.仓库,器件号

CHAR(6)FOREIGNKEYREFERENCES基础.器件,数量INTCHECK(数量>=0),PRIMARYKEY(仓库号,器件号))更新操作及其完整性约束更新语句主要内容更新操作涉及约束SQL更新语句UPDATE常用格式：UPDATE[schema_name.]table_nameSETcolumn_name={expression|DEFAULT|NULL}[,...n][WHERE<search_condition>]1234schema_name：指出模式名table_name：指出要更新数据的表SET短语说明要更新的列及其值；参数“[,...n]”说明一次可以更新多列。WHERE：指定更新哪些记录，即用逻辑表达式指定更新条件。5例：将仓库号为WH1的面积设置为800UPDATE仓储.仓库SET面积=800WHERE仓库号='WH1'仓库仓库号城市面积WH1北京500WH2上海370WH3广州300WH4武汉4001.更新仓库记录例：将职工E6的姓名设置为洪涛，班组长为E2。UPDATE基础.职工SET姓名=‘洪涛’，班组长=‘E2’WHERE职工号='E6'2.更新职工记录3.更新订购单记录例：将订购单号为OR79的订购单的订购日期设置为2011年10月6日UPDATE订货.订购单SET订购日期=‘2011/10/06’WHERE订购单号=‘OR79’注意日期格式3.更新订购单记录例：将订购单号为OR76的订购单的供货方字段值设置为空值