第03章 SQL的数据定义和完整性约束

第3章

SQL的数据定义和完整性约束StructuredQueryLanguage本章要点SQL概述SQL的发展历史SQL的特点SQL数据库的体系结构SQLServer介绍数据定义

数据库的定义与删除

基本表的定义、修改和删除

索引的建立和删除

SQL的数据操纵和完整性约束的作用插入数据和完整性约束的作用删除数据和完整性约束的作用修改数据和完整性约束的作用3.1 SQL概述3.1.1SQL的发展历史SQL(StructuredQueryLanguage)结构化查询语言1974年IBM圣约瑟实验室的Boyce和Chamberlin为关系数据库管理系统设计的一种查询语言当时称为SEQUEL语言，后简称为SQL首先在IBM

公司的关系数据库系统SystemR上实现。1986年被美国国家标准局（ANSI-AmericanNaturalStandardInstitute）批准为关系数据库语言的美国标准1987年国际标准化组织（ISO-InternationalOrganizationforStandardization）通过这一标准SQL的发展历史（续）有关标准SQL/86：ANSI和ISO的第一个标准，；SQL/89：定义了模式定义、数据操作和事务处理；SQL/92(也称为SQL2)：描述了一种增强功能的SQL，包括模式操作，动态创建和SQL语句动态执行、网络环境支持等增强特性。；SQL/99(也称为SQL3)：主要特点在于抽象数据类型的支持，为新一代对象关系数据库提供了标准。SQL/2003：包含了XML相关内容，自动生成列值(columnvalues)；SQL/2006：定义了SQL与XML(包含XQuery)的关联应用；现状大部分DBMS产品都支持SQL，支持程度不同成为操作数据库的主流语言3.1.2SQL的特点1.综合统一可以独立完成数据库生命周期中的全部活动定义关系模式、录入数据以建立数据库、查询、更新、维护、数据库重构、数据库安全控制等一系列操作要求。集数据查询、数据操纵、数据定义和数据控制功能为一体语言风格统一，每种操作只需一种操作符。SQL的特点（续）2.以同一种语法结构提供两种使用方式具有交互式（自含式）和嵌入式两种使用方式。交互式SQL用户可直接键入SQL命令对数据库进行操作一般DBMS都提供联机交互工具适合非计算机专业人员使用嵌入式SQL能将SQL语句嵌入到高级语言（宿主语言）使应用程序充分利用SQL访问数据库的能力、宿主语言的过程处理能力适合程序员设计程序时使用SQL的特点（续）3.高度非过程化高度非过程化的语言SQL只要提出“做什么”，无须了解存取路径。存取路径的选择以及SQL的操作过程由系统自动完成。减轻用户负担，而且有利于提高数据独立性SQL的特点（续）4.语言简洁、易学易用核心功能只有9个动词，语法简单，接近英语。SQL语言的动词SQL功能动词数据定义CREATE，DROP，ALTER数据查询SELECT数据操纵INSERT，UPDATE，DELETE数据控制GRANT，REVOKE3.1.3SQL数据库的体系结构SQL语言支持关系数据库的三级模式结构用户外模式模式内模式SQL基本表1视图1视图2基本表2基本表3基本表4存储文件1存储文件3存储文件2SQL的基本概念（续）基本表基本表是模式的基本内容，是实际存储在数据库中的表，对应一个实际存在的关系。视图视图是外模式的基本单位从一个或几个基本表导出的表数据库中只存放视图的定义而不存放视图对应的数据视图是一个虚表存储文件存储文件是内模式的基本单位每个存储文件与外部存储器上一个物理文件对应。物理结构是任意的，对用户透明SQL的基本概念（续）基本表和存储文件的关系一个基本表对应一个或多个存储文件一个存储文件可以存放在一个或多个基本表一个基本表可以有若干个索引，索引同样存放在存储文件中。3.2SQLServer介绍3.2.1SQLServer的发展历史最初是由Microsoft、Sybase和Ashton-Tate三家公司共同开发的1988年，移植到OS/2上1992年，移植到了Windows

NT平台上1993年，SQLServer4.21995年，发布了SQLServer6.01996年，推出了SQLServer6.51998年，发布

SQLServer7.02000年，发布了第一个企业级数据库系统——SQLServer20002005年，发布了新一代数据库产品——SQLServer20052008年，SQLServer2008正式发布2012年，正式发布SQLServer2012最新的版本为2014和即将发布的20163.2.2Transact-SQL（T-SQL）1．数据类型3.3数据定义数据定义语句SQL语言的数据定义功能包括对数据库、基本表、视图、索引等数据库对象的创建和删除。操

作

对

象操

作

方

式创

建删

除修

改数据库CREATEDATABASEDROPDATABASE

基本表CREATETABLEDROPTABLEALTERTABLE视图CREATEVIEWDROPVIEW

索引CREATEINDEXDROPINDEX

表3.8SQL的数据定义语句3.3.1

数据库的定义与删除一个SQL数据库由数据库名和数据库拥有者的用户名或账号来确定，并包含数据库中每一个数据对象（基本表、视图、索引等）的定义。定义一个SQL数据库，就是定义一个存储空间。1.数据库的定义语法CREATEDATABASE<数据库名><其他参数>【例3.1】下面语句定义了一个SQL数据库。CREATEDATABASESaleProduct2．数据库的删除语法DROP

DATABASE<数据库名><其他参数>【例3.2】删除SQL数据库SaleProduct。DROPDATABASESaleProduct注意如果数据库当前正在使用，则无法删除该数据库3.3.2基本表的定义、删除与修改1.

基本表的定义语法CREATETABLE<基本表名>(<列名1><列数据类型>[列完整性约束][,<列名2><列数据类型>[列完整性约束]

…][,表级完整性约束])说明：其中，“<>”中的内容是必选项，“[]”中的内容是可选项。同一数据库中基本表名不重复同一基本表中列名不能重复

【例3.3】常用完整性约束

NULL|NOTNULL：该列值可以为空，或者不能为空。

UNIQUE：唯一性约束，说明该列取值必须唯一。

PRIMARYKEY：主码约束，说明该列为基本表的主码。

FOREIGNKEY：外码约束，说明表之间的参照关系。

CHECK：域完整性约束，说明该列的取值需要满足的约束条件。

DEFAULT：默认值设置，表示该列在未定义时的默认取值。可用如下语句创建客户表Customer。CREATETABLECustomer(

custIDvarchar(5)PRIMARYKEY,

custNamevarchar(20)NOTNULLUNIQUE,

custCityvarchar(10),

custPhonevarchar(13))【例3.3】（续）也可为约束命名：CREATETABLECustomer(

custIDvarchar(5)constraintPK_Customer

PRIMARYKEY,

custName

varchar(20)NOTNULLconstraintUQ_Customer

UNIQUE,

custCity

varchar(10),

custPhone

varchar(13))【例3.4】可用如下语句创建商品表Product。

CREATETABLEProduct(

pdID

varchar(5)constraintPK_ProductPRIMARYKEY,

pdName

varchar(20)NOTNULL,

pdSpec

varchar(10),

pdModelvarchar(10),

pdSupplier

varchar(20)NOTNULL,

pdPrice

numeric(10,2)DEFAULT0

CHECK(pdPrice>=0),

pdStockSize

intDEFAULT0

CHECK(pdStockSize>=0))【例3.4】(续)Check约束也可以在定义基本表时定义成表级约束:

CREATETABLEProduct(

pdID

varchar(5)constraintPK_ProductPRIMARYKEY,

pdName

varchar(20)NOTNULL,

pdSpec

varchar(10),

pdModelvarchar(10),

pdSupplier

varchar(20)NOTNULL,

pdPrice

numeric(10,2)DEFAULT0,

pdStockSize

intDEFAULT0,

CHECK(pdPrice>=0),

CHECK(pdStockSize>=0))【例3.5】可用如下语句创建订单表Orders。

CREATETABLEOrders(

orderID

varchar(16)PRIMARYKEY,

orderDate

dateNOTNULL,

custID

varchar(5)NOTNULL

FOREIGNKEYREFERENCESCustomer(custID)

ONDELETEcascade

ONUPDATEcascade,

orderAddress

varchar(40)NOTNULL,

orderAmount

numeric(10,2)NOTNULL)外码除了级联CASCADE之外，还可以选择如下处理方式。

NOACTION：说明禁止删除或更新被参照记录，这也是默认的处理方式。SETNULL：说明将参照记录的对应属性列值设置为空值NULL。SETDEFAULT：说明将参照记录的对应属性列值设置为默认值。【例3.5】(续)外码约束还可以定义成表级约束:

CREATETABLEOrders(

orderID

varchar(16)PRIMARYKEY,

orderDate

dateNOTNULL,

custID

varchar(5)NOTNULL,

orderAddress

varchar(40)NOTNULL,

orderAmount

numeric(10,2)NOTNULL,

FOREIGNKEY(custID)

REFERENCESCustomer(custID) ONDELETEcascade ONUPDATEcascade)【例3.6】可用如下语句创建订单明细表OrderDetail。

CREATETABLEOrderDetail(

orderID

varchar(16)NOTNULL,

pdID

varchar(5)NOTNULL,

pdSellPrice

numeric(10,2)NOTNULL,

quantity

intNOTNULL,

PRIMARYKEY(orderID,pdID),

FOREIGNKEY(orderID)REFERENCESOrders(orderID),

FOREIGNKEY(pdID)REFERENCESProduct(pdID)

ONUPDATEcascade)2．基本表的修改一般格式：ALTERTABLE<表名>[ALTERCOLUMN<列名><列参数>[,…]][ADD[<新列名><列数据类型>[列完整性约束][,…]]|[新的完整性约束][,…]][DROP[CONSTRAINT<完整性约束>][,…]|[COLUMN<列名>][,…]]【例3.7】将Product表中的属性列pdName的数据类型改为varchar(30)。ALTERTABLEProduct ALTERCOLUMNpdName

varchar(30)更改某些数据类型可能导致相关数据自动更改。【例3.8】将Product表中的属性列pdName的数据类型改为varchar(2)。ALTERTABLEProduct ALTERCOLUMNpdName

varchar(2)语句操作就会中止原因在于“将截断字符串或二进制数据”【例3.9】在Product表中增加一个列：pdMemo（备注），数据类型为varchar(100)。ALTERTABLEProduct

ADDpdMemo

varchar(100)【例3.10】在Customer表中增加默认值约束：custCity(所在城市)，其默认值为“北京”。ALTERTABLECustomer ADDCONSTRAINT

DF_custCity

DEFAULT'北京'forcustCity【例3.11】删除Customer表中的完整性约束DF_custCity。ALTERTABLECustomer DROPCONSTRAINT

DF_custCity

3．基本表的删除基本格式为：DROPTABLE<基本表名1>[,……]说明只有在没有视图或约束引用该基本表时，才能删除【例3.12】删除OrderDetail表。DROPTABLEOrderDetail3.3.3索引的建立和删除建立索引的目的加快查询速度为表设置索引要付出代价：增加了数据库的存储空间在插入和修改数据时要花费较多的时间索引分类聚集索引是指表中行的物理顺序与键值的逻辑（索引）顺序相同一个表只能包含一个聚集索引非聚集索引1.建立索引的原则在哪些列上可以创建索引？在经常需要搜索的列上，可以加快搜索的速度；在作为主码的列上，强制该列的唯一性和组织表中数据的排列结构；在经常用在连接的列上，这些列主要是一些外码，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。1.建立索引的原则（续）在哪些列上不能创建索引？对于那些在查询中很少使用或者参考的列不应该创建索引。对于那些只有很少数据值的列也不应该增加索引。对于那些定义为text,image和bit数据类型的列不应该增加索引。当修改性能远远大于检索性能时，不应该创建索引。2.创建索引语法格式：CREATE[UNIQUE][CLUSTERED]

INDEX

<索引名>

ON

<表名>(<列名>[<次序>][,<列名>[<次序>]]…)说明：UNIQUE：规定索引的每一个索引值只对应于表中唯一的记录CLUSTER：聚簇索引，<次序>：可选ASC（升序）或DESC（降序）。若不指定，默认为升序。索引的排列方式：先以第一个列名值排序；该列值相同的记录，则按下一列名排序。【例3.13】在Customer表的属性列custPhone上创建一个唯一索引。CREATEUNIQUEINDEXidx_uq_custPhone

onCustomer(custPhone)唯一值索引对于已含重复值的属性列不能建UNIQUE索引对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束注意索引建立以后，系统在存取数据时会自动选择合适的索引作为存取路径，用户不必也不能显式地选择索引。3.删除索引一般格式：DROPINDEX<索引名>

ON

<基本表名>注意不适用于通过定义

PRIMARYKEY或

UNIQUE约束创建的索引。若要删除该约束和相应的索引，可使用带有

DROPCONSTRAINT子句的ALTERTABLE。【例3.14】删除Customer表的索引idx_uq_custPhone。DROPINDEXidx_uq_custPhoneONCustomer3.4SQL的数据操纵和完整性约束的作用3.4.1插入数据和完整性约束的作用插入单个元组的INSERT的语句格式为：INSERTINTO<基本表名>[(<列名1>,<列名2>,…,<列名n>)]

VALUES(<列值1>,<列值2>,…,<列值n>)说明:<基本表名>指定要插入新元组的表的名字新元组的<列名1>，<列名2>，……，<列名n>其值分别对应为<列值1>，<列值2>，……，<列值n>，其他列值为空。若列名序列省略，则表示在VALUES后的元组值中提供插入元组的每个分量的值，分量的顺序和关系模式中列名的顺序一致。【例3.15】向客户表中插入一个客户记录('907','林巍','北京'，)。INSERTINTOCustomer

VALUES('907','林巍','北京',)说明:列名序列省略，则表示在VALUES后的元组值中提供插入元组的每个分量的值，分量的顺序和关系模式中列名的顺序一致。【例3.16】向客户表中插入一个客户记录('905','王力','大连'，)。INSERTINTOCustomer

VALUES('905','王力','大连',)运行结果：操作失败分析原因：违反了

PRIMARYKEY约束【例3.17】【例3.17】向客户表中插入一个客户记录，客户编号为“904”，客户名称为“张松林”。INSERTINTOCustomer(custID,custName)

VALUES('904','张松林')运行结果：操作失败分析原因：违反了UNIQUE约束【例3.18】在订单明细表OrderDetail中插入一个订单明细记录('D201508201002006','11205',47,2)。INSERTINTOOrderDetailVALUES('D201508201002006','11205',47,2)运行结果：操作失败分析原因：违反了FOREIGNKEY约束3.4.2删除数据和完整性约束的作用用来删除表中的一行或多行记录语法格式:DELETEFROM<表名>

[WHERE<条件>]【例3.19】删除Customer中客户名称为“张小宁”的客户的基本信息。DELETEFROMCustomer WHEREcustName='张小宁'【例3.20】删除Product中商品编号为“11206”的商品信息。DELETEFROMProduct WHEREpdID='11206'运行结果：操作失败违反参照引用关系3.4.3

修改数据和完整性约束的作用用来对表中一行或多行中的某些值进行修改语法格式:

UPDATE<表名>

SET<列名>=<表达式>[,<列名>=<表达式>]

[WHERE<条件>]【例3.21】将客户名称为“里奇”的所在城市改为“深圳”。UPDATECustomer SETcustCity='深圳' WHEREcustName='里奇'【例3.22】将商品表中所有商品的价格调高10%。UPDATEProduct SETpdPrice=pdPrice*1.1【例3.23】将客户表中客户编号为“902”的客户编号改为“910”。UPDATECustomer SETcustID='910' WHEREcustID='902'运行结果：Customer中客户编号为“902”的客户编号成功改为“910”。同时订单表Orders中所有引用“902”的元组的custID也同时更新为“91