北京邮电大学软件工程研究生的课程-数据库设计开发-0-3

1.3SQL自从20世纪80年代以来，SQL就是一个通用的、功能极强的关系数据库语言。现在，SQL语言正从关系形式（ANSISQL-92标准）转向对象-关系形式（ANSISQL-99标准，1999年颁布）。SQL语言是1974年由Boyce和Chamberlin提出。1986年10月美国国家标准局(ANSl)批准了SQL作为关系数据库语言的美国标准。同年公布了SQL标准文本(简称SQL-86)。1987年6月国际标准化组织(ISO)也采纳了此标准。1989年，美国国家标准局（ANSI）采纳了新的规范SQL-89标准，取代SQL-86，同时SQL-89标准也被国际标准化组织(ISO)采纳。1992年，ANSI/ISO颁布了SQL2版本，标准的名称为SQL-92。SQL-92分称几个顺序级别：从代表SQL-89最小扩展集的“Entry”到“Intermediate”和“Full”。完成于己于1999年的SQL-99具有更加高级的特征（包括对象-关系特性），亦称SQL3。郭文明2003.06.051.3SQL主要的几个数据库生产厂商并不可能完全遵守SQL-99（以及更老的SQL-92）。我们一般更加关注在产品中已经实现的SQL-99特征。

1.3.1SQL特点1.3.2数据定义1.3.2.1SQL模式的创建和删除1.3.2.2SQL提供的基本数据类型1.3.2.3定义、删除与修改基本表

1.3.2.4建立与删除索引1.3.2.5视图的创建和删除1.3.3数据查询1.3.3.1简单查询

1.3.3.2子查询1.3.3.3UNION运算和FORALL条件1.3.3.4高级SQL语法1.3.3.5集合函数1.3.3.6行分组

1.3.4数据更新1.3.4.1插入数据

1.3.4.2修改数据1.3.4.3删除数据郭文明2003.06.051.3.1SQL特点1)综合统一

2)高度非过程化

3)面向集合的操作方式

4)以同一种语法结构提供两种使用方式5)语言简捷，易学易用

和关系代数相比，就查询能力而言，SQL并没有根本的改进，在关系代数查询方面的经验可以成为用SQL来实现查询的良好借鉴。在构造查询时SQL的select语句比关系代数要灵活.郭文明2003.06.051.3.2数据定义SQL的数据定义功能包括对模式(Schema)、表(关系，Table)、视图(View)和索引(Index)的创建、删除和修改操作。如下表所示。操作对象操作方式创建删除修改模式CREATESCHEMADROPSCHEMA表CREATETABLEDROPTABLEALTERTABLE视图CREATEVIEWDROPVIEW索引CREATEINDEXDROPINDEX郭文明2003.06.051.3.2.1SQL模式的创建和删除在SQL-99中，模式是表、索引及其他数据库对象的集合。模式名通常是一个用户名。在CoreSQL-99和当前大多数产品中，当用户的数据库帐户建立时，其模式在用户名之后给出，他们不能再建立其他模式。SQL-99的扩展特性允许用户建立附加模式，一个SQL模式由模式名和模式拥有者的用户名或账号来确定。SQL模式的创建可用CREATE语句实现，其句法如下：

CREATESCHEMA<模式名>AUTHORIZATION<用户名>

目前只有DB2UDB允许用户建立附加模式。其它产品中模式用用户名替代。

郭文明2003.06.051.3.2.1SQL模式的创建和删除在SQL中还有一个“目录”概念。目录是SQL环境中所有模式的集合。包含数据库中定义的对象的信息的表，由系统维护。ORACLE叫数据字典，DB2UDB叫目录表，INFORMIX叫系统目录。目录表在建立数据库时建立，用户不能更新，但DBA可以用select获取这些信息。当一个SQL模式及其所属的基本表、视图等元素都不需要时，可以用DROP语句撤消这个SQL模式。DROP语句的句法如下：

DROPSCHEMA<模式名>[CASCADE|RESTRICT]郭文明2003.06.051.3.2.2SQL提供的基本数据类型SQL-99ORACLEINFORMIXDB2UDB说明Cchar(n)char(n)n<=4000char(n)n<=32767char(n)n<=254定长字符型chararray[n+1]varchar(n)varchar(n)varchar2(n)varchar(n)varchar(n)变长字符型chararray[n+1]numeric(p,d)decimal(p,d)numeric(p,d)decimal(p,d)number(p,d)numeric(p,d)decimal(p,d)numeric(p,d)decimal(p,d)定点数，由p位数字(不包括符号、小数点)小数点后面有d位数字无smallintsmallintsmallintsmallint短整数shortintintegerintegerintegerinteger长整数int,longintrealrealrealreal浮点数floatdoubleprecision,float,float(n)doubleprecision,number,floatfloat(n)doubleprecision,floatdoubleprecision,double,float,float(n)取决于机器精度的双精度浮点数至少为n位精度double郭文明2003.06.051.3.2.3定义、删除与修改基本表建立数据库最重要的一步就是定义一些基本表。SQL语言使用CREATETABLE语句定义基本表，一般格式如下：

CREATETABLE<表名>(<列名><数据类型>[列级完整性约束条件][，<列名><数据类型>[列级完整性约束条件]]…[，<表级完整性约束条件>])；例:CREATETABLEStudent(SnoCHAR(5)PRIMARYKEY,SnameCHAR(8)NOTNULL,SageSMALLINTCHECK(SageBETWEEN17AND22),SsexCHAR(2)CHECK(SsexIN(’男’,’女’)),SdeptCHAR(20)DEFAULT(‘软件学院’));创建学生表:Sno为主键(非空唯一),Sname非空,Sage在17到20之间取值,Ssex只能取‘男’或‘女’,Sdept默认值为‘软件学院’.郭文明2003.06.051.3.2.3定义、删除与修改基本表实际使用时要有用户ID和密码,进入交互式环境，才能完成数据库操作。修改基本表

ALTERTABLE<表名>[ADD<新列名><数据类型>[完整性约束]][DROP<完整性约束名>][MODIFY<列名><数据类型>];其中<表名>是要修改的基本表，ADD子句用于增加新列和新的完整性约束条件，DROP子句用于删除指定的完整性约束条件，MODIPY子句用于修改原有的列定义，包括修改列名和数据类型。郭文明2003.06.051.3.2.3定义、删除与修改基本表删除基本表

DROPTABLE<表名>基本表一旦删除，表中的数据、此表上建立的索引和视图都将自动被删除掉。因此执行删除基本表的操作一定要格外小心。

注意：有的系统，如Oracle，删除基本表后建立在此表上的视图定义仍然保留在数据字典中。但是，当用户引用时就报错。郭文明2003.06.051.3.2.4建立与删除索引索引的功能表现在以下3方面。(1)使用索引可以明显地加快数据查询的速度(2)使用索引可保证数据的唯一性(3)使用索引可以加快连接速度建立索引的原则

(1)索引的建立和维护由DBA和DBMS完成(2)大表应当建索引，小表则不必建索引(3)对于一个基本表，不要建立过多的索引(4)根据查询要求建索引郭文明2003.06.051.3.2.4建立与删除索引CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<asc|desc>[，<列名>[<asc|desc>]])；

如果数据增加删改频繁，系统会花费许多时间来维护索引。这时，可以删除一些不必要的索引。DROPINDEX<索引名>；

郭文明2003.06.051.3.2.5视图的创建和删除视图是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制。视图一经定义，就可以和基本表一样被查询、被删除，我们也可以在一个视图之上再定义新的视图，但对视图的更新(增加、删除、修改)操作则有一定的限制。

CREATVIEW<视图名>[(列名>[,<列名>]...)]AS<子查询>[WITHCHECKOPTION]；其中子查询可以是任意复杂的SELECT语句，但通常不允许含有ORDERBY子句和DISTINCT短语。WITHCHECKOPTION表示对视图进行UPDATE，INSERT和DELETE操作时要保证更新、插入或删除的行满足视图定义中的谓词条件(即子查询中的条件表达式)。

郭文明2003.06.051.3.2.5视图的创建和删除DROPVIEW<视图名>；

视图删除后视图的定义将从数据字典中删除。但是由该视图导出的其他视图定义仍在数据字典中，不过该视图已失效。用户使用时会出错，要用DROPVIEW语句将它们一一删除。DBMS执行CREATEVIEW语句的结果只把视图的定义存入数据字典，并不执行其中的SELECT。在关系数据库中，并不是所有的视图都是可更新的,因为有些视图的更新不能的有意义的转换成对基本表的更新。行列子集视图是可更新的。各个DBMS对视图的更新有自己的规定。郭文明2003.06.051.3.3数据查询数据查询是数据库的核心操作。SQL语言的数据查询只有一条SELECT语句:

SELECT[ALL|DISTINCT]{*|<目标列表达式[[as]别名]>[，<目标列表达式[[as]别名]>]...}

FROM<表名或视图名>[，<表名或视图名>]…[WHERE<条件表达式>][GROUPBY<列名>[，<列名>][HAVING<条件表达式>]]][ORDERBY<列名>[ASC|DESC][，<列名>[ASC|DESC]]…];郭文明2003.06.051.3.3数据查询Select语句的执行过程可以理解为：首先，对FROM子句中的所有表做关系乘积接着，删除不满足WHERE子句的行根据GROUPBY子句对剩余的行进行分组然后删除不满足HAVING子句的组求出SELECT子句选择列表的表达式的值若有关键词DISTINCT存在，则删除重复的行Select中的标识符：一般的SQL标识符是大小写无关的。实际上SQL在解释以前会把它们转化为大写形式。一个标识符必须以一个字母打头，EntrySQL-92和CoreSQL-99将一个标识符字节数限制在18个以内。郭文明2003.06.051.3.3数据查询表达式可以是数值表达式、字符串表达式和日期表达式等。

数值表达式由常数、表属性、算术运算符、算术函数所组成。字符串表达式由常数、表属性、字符串运算符、字符串函数所组成。日期表达式由常数、表属性、日期运算符、日期函数所组成WHERE中使用谓词来表示条件。一般情况谓词运算结果为TRUE或FALSE,但如果遇到空值时,可能为UNKNOWN。SQL查询的一个争议点即：对于同一个查询会存在众多不同的构造方法。郭文明2003.06.051.3.3ORACLE、INFORMIX、DB2UDB

中的一些数学函数名称描述结果abs(n)mod(n,b)sqrt(n)

n的绝对值，n为数值型n被b除后得到的余数。n,b为整数n的平方根，n为整数或浮点数

另外，三角函数、指数函数、对数函数、幂函数和round(n)数值类型整数浮点郭文明2003.06.051.3.3一些标准的和特定产品的串处理函数SQL-99中的描述ORACLEDB2UDBINFORMIX返回串长度(整数个字符)CHAR_LENGTH(str)返回子串,从m个开始取n个SUBSTRING（str）FORMmFOR(n)返回去掉左或右空格后得到的包含空格的串TRIM（[[LEADING|TRAILING|BOTH][SET]FROM]str）返回子串str2在str1中位置，如果指定n,则从n开始POSITION（str1INstr2）字母小写LOWER（str）字母大写UPPER（str）length(str)substr(str,m[,n])trim([[leading|trailing|both][set]from]str),ltrim(str[,set]),rtrim(str[,set])instr(str1,str2[,n])lower(str)upper(str)length(str)substr(str,m[,n])ltrim(str);rtrim(str)posstr(str1,str2[,n])lcase(str)ucase(str)length(str),char_length(str)substr(str,m[,n])，substring(strfrommforn)trim([[leading|trailing|both][set]from]str);lower(str)upper(str)郭文明2003.06.051.3.3SQL中的标准谓词谓词

形式

例子比较谓词BETWEEN谓词量化谓词IN谓词

EXISTS谓词ISNULL谓词LIKE谓词expr1θ{expr2|(subquery)}expr1[NOT]BETWEENexpr2andexpr3exprθ[SOME|ANY|ALL](subquery)expr[NOT]IN(subquery)

[NOT]EXISTS(subquery)colnameIS[NOT]NULLcloname[NOT]LIKEval[ESCAPEval]p.price>(subquery)c.discntbetween10.0and12.0c.discnt>=all(subquery)pidin(selectpidfromorders)exist(select*…)c.discntisnullcnamelike‘A%’郭文明2003.06.051.3.3.1简单查询例：检索定货记录中所有pid值：selectpidfromorders--结果中有重复的pidselectdistinctpidfromorders--结果中pid唯一select缺省为all,distinct没有出现时允许重复行，缺省情况不遵守行唯一性规则。例：在orders表上生成每笔业务的利润profit(收入减去60%的成本、顾客的折扣以及代理商的酬金):selectordno,x.cid,x.aid,x.pid,.40*(x.qty*p.price)-.01*(c.discnt+a.percent)*(x.qty*p.price)asprofitfromordersasx,customerasc,agentsasa,productsaspwherec.cid=x.cidanda.aid=x.aidandp.pid=x.pid;乘积×

投影π

选择σ

郭文明2003.06.051.3.3.1简单查询注：1）SQL-99规定FROM子句中执行连接运算，但大多数产品通过笛卡尔积运算并且在WHERE子句中包含表示参与连接的列值相等的条件来模拟连接运算，具体实现方法(执行计划，查询优化)各不相同。2）FROM中AS被省略，SQL仍能识别表别名。ORACLE和INFORMIX使用别名或表别名，DB2UDB使用相关名。

3）列表达式的列名可以通过AS指明，ORACLE中称列别名，INFORMIX中称显示标签，DB2UDB中简单称为列名。如果没有AS，ORACLE中将完整表达式文本作为列名。郭文明2003.06.051.3.3.2子查询每个Select查询都会生成一张表，但我们不能像关系代数表达式那样任意将一个Select语句嵌入另一个Select语句。这是SQL与关系代数的一个很重要的不同点。例如：from子句中不能出现select(SQL-99标准已去掉该限制，但数据库产品中并未完全实现)，where子句中显然可以出现select。出现在另一个select语句之内的select语句形式称为子查询。一个子查询能以许多种方式出现在另一个select语句的WHERE子句条件中。郭文明2003.06.051.3.3.2子查询1）IN谓词(NOTIN)例:求通过住在北京或上海的代理商订货的顾客的姓名和折扣.selectcname,discntfromcustomerswherecidin(selectcidfromorderswhereaidin(selectaidfromagentswherecityin(‘北京’,’上海’)))；例:求由住在北京的顾客和住在北京的代理商组成的所有订货ordno.selectordnofromorderswhere(cid,aid)in(selectcid,aidfromcustomersc,agentsawherec.city=‘北京’anda.city=‘北京’);以上两例为不相关查询:内层子查询独立于外层的select.例:找出订购了产品p05的顾客的名字.selectdistinctcnamefromcustomerswhere‘p05’in(selectpidfromorderswherecid=customers.cid)本例为相关查询:子查询使用外层select语句提供的数据.SQL-99允许,有些系统不允许.郭文明2003.06.051.3.3.2子查询2)量化比较谓词:exprθ[SOME|ANY|ALL](subquery)

θ为比较运算符<,<=,=,<>,>,>=。SOME和ANY含义相同，SOME是最新版本的推崇形式。例：找出佣金百分率最小的代理商aid。selectaidfromagentswherepercent<=all(selectpercentfromagents);例：求出满足以下条件的顾客cid:该顾客的discnt小于任一住在北京的顾客的discnt.错误：selectcidfromcustomerwherediscnt<any(selectdiscntfromcustomerswherecity=‘北京’);正确:selectcidfromcustomerwherediscnt<all(selectdiscntfromcustomerswherecity=‘北京’);注意any不是任意郭文明2003.06.051.3.3.2子查询3)EXISTS谓词:EXISTS(Subquery)为真当且仅当子查询返回一个非空集合;NOTEXISTS(Subquery)为真当且仅当返回集合为空.

例:求出既订购了产品p01有订购了产品p07的顾客cid.关系代数:πcid(σpid=’p01’(O))∩πcid(σpid=’p07’(O))selectdistinctcidfromordersxwherepid=‘p01’andexists(select*fromorderswherecid=x.cidandpid=‘p07’);或selectdistinctx.cidfromordersx,ordersywherex.pid=‘p01’andx.cid=y.cidandy.cid=‘p07’;EXISTS的查询一般能找到等价的其他查询形式。使用和不使用exists交运算郭文明2003.06.051.3.3.2子查询NOTEXISTS确实为我们带来了一些新的功能。notexists能被用来实现关系代数的MINUS运算。如果R和S是两个兼容表(属性相同A1…An),R-S用SQL计算:selectA1…AnfromRwherenotexists(select*fromSwhereS.A1=R.A1and………andS.An=R.An);

例:找出没有通过代理商a03订货的顾客cid.关系代数：πcid(O)—πcid(σaid=’a03’(O))

selectdistinctcidfromordersxwherenotexists(select*fromorderswherecid=x.cidandaid=‘a03’);差运算郭文明2003.06.051.3.3.3UNION运算和FORALL条件为了提供关系代数的∪运算，SQL使用UNION：subqueryUNION[ALL]subquery例：包含了顾客所在的或代理商所在或两者皆在的城市名单。

selectcityfromcustomersunionselectcityfromagents;或selectcityfromcustomersunionallselectcityfromagents;行不重复行重复∪运算郭文明2003.06.051.3.3.3UNION运算和FORALL条件SQL中没有等价的÷运算。如果面临的查询“要求被检索的对象集合必须符合‘所有’这类关键词的条件”(FORALL)时，关系代数要用到除运算。SQL中可以：1.表述要检索的候选对象的一个反例(至少一个对象不符合条件)，.并建立select语句(选出所有反例)；2.建立表示这类反例不存在的条件(notexists)；3.建立最终select。例：求通过住在北京的所有代理商订了货的顾客cid.1.反例：住在北京但没有为所求顾客c.cid订货的代理商：

select*fromagentswherea.city=‘北京’andnotexists(select*fromordersxwherex.cid=c.cidandx.aid=a.aid)2.反例不存在：notexists(反例)3.最终：selectc.cidfromcustomerswherenotexists(select*fromagentswherea.city=‘北京’andnotexists(select*fromordersxwherex.cid=c.cidandx.aid=a.aid));÷运算郭文明2003.06.051.3.3.3UNION运算和FORALL条件例：找出订购了所有被顾客c006订购的商品的顾客的cid.反例：被c006订购但没有被c.cid订购的商品：selectp.pidfromproductspwherep.pidin(selectpidfromordersxwherex.cid=‘c006’)andnotexists(select*fromordersywherey.pid=p.pidandy.cid=c.cid)

反例不存在：notexists(selectp.pidfromproductspwherep.pidin(selectpidfromordersxwherex.cid=‘c006’)andnotexists(select*fromordersywherey.pid=p.pidandy.cid=c.cid))

最终：selectcidfromcustomerswherenotexists(selectp.pidfromproductspwherep.pidin(selectpidfromordersxwherex.cid=‘c006’)andnotexists(select*fromordersywherey.pid=p.pidandy.cid=c.cid));÷运算被c006订购没有被c.cid订购郭文明2003.06.051.3.3.4高级SQL语法以下介绍的高级SQL运算符不是EntrySQL-92的部分,但几乎都属于SQL-99,目前产品中不一定支持，但可能出现在未来的数据库产品中。1)INTERSECT(∩)和EXCEPT(—)运算符

Subquery[UNION[ALL]|INTERSECT[ALL]|EXCEPT[ALL]Subquery]注：CoreSQL-99只有EXCEPT而没有EXCEPTALL。两个子查询从左到右的列类型是兼容的,即可以并、交、差。如类型char(5)和类型char(10)的列运算,结果为char(10).ORACLE提供UNION、UNIONALL、INTERSECT、MINUS(EXCEPT)。但不支持INTERSECTALL或MINUSALL。DB2UDB都支持。ALL考虑重复行及数目郭文明2003.06.051.3.3.4高级SQL语法例:(QUNIONALLQUNIONALLQ)INTERSECTALL(QUNIONALLQ)结果是(QUNIONALLQ),包含两个重复行。(QUNIONALLQUNIONALLQ)INTERSECT(QUNIONALLQ)结果是(Q),没有包含重复行。(QUNIONALLQUNIONALLQ)EXCEPTALL(QUNIONALLQ)结果是(Q),没有包含重复行。郭文明2003.06.051.3.3.4高级SQL语法2)连接形式

通用形式：FROMtablename[[AS]corr_name[,……]……]

SQL-99：FROM后面还可以是：一般形式：

tablename[[AS]corr_name[(colname[,colname……])]]子查询：

(subquery)[AS]corr_name[(colname[,colname……])]

显式连接：table1[INNER|{LEFT|RIGHT|FULL}[OUTER]]JOINtable2ONcondition

显然SQL-99中允许子查询出现在FROM子句中。以上三种形式在产品中并没有完全实现。使用时可以试用或帮助。可以是视图名ORACLE不能有AS允许为表的列重新命名允许是子查询,此时别名必须内连,左连,右连,外连连接条件郭文明2003.06.051.3.3.4高级SQL语法例：检索至少订购了一件价格低于0.5的商品的顾客姓名。

selectdistinctcnamefrom(ordersojoinproductspono.pid=p.pid)joincustomerscono.cid=c.cidwherep.price<0.5;注:ORACLE仅提供左连和右连,而且语法与标准SQL也不同:SELECT…FROMT1,T2WHERE[T1.c1[(+)]=T2.c2|T1.c1=T2.c2[(+)]]ANDconditionT2保留所有行与T1连不上的用NULL郭文明2003.06.051.3.3.5SQL中的集合函数SQL中称集合函数(setfunction),ORACLE称组函数(groupfunction),DB2UDB称列函数(columnfunction),INFORMIX称聚集函数(aggregatefunction).集合函数语法:SET_FUNCTION([ALL|DISTINCT]col)|COUNT(*)注:ALL时包括重复行，DISTINCT不包括重复行。WHERE子句比较操作中不能使用集合函数.如discnt<max(…)集合函数忽略了所有空值.集合函数不允许嵌套使用.如AVG(selectMAX(dollars)……)名称参数类型结果类型描述COUNT任意(*)数值出现次数SUM数值数值参数和AVG数值数值参数均值MAX字符,数值字符,数值最大值MIN字符,数值字符,数值最小值郭文明2003.06.051.3.3.6SQL中行的分组SQL报表功能:根据某些列值的共性把一个表所包含的全部行分成若干个子集,然后对每个子集执行集合函数.

例:打印每个代理商为顾客c002和c003订购产品及产品总数量.selecta.aid,aname,p.pid,pname,sum(qty)fromordersx,productsp,agentsawherex.pid=p.pidandx.aid=a.aidandx.cidin(‘c002’,’c003’)

groupbya.aid,a.aname,p.pid,p.pname;GROUPBY对象的列上的空值会被分在同一组里.郭文明2003.06.051.3.3.6SQL中行的分组如果要去掉分组后的某些行,不能用where,只能用HAVING子句.

例:求被至少两个顾客订购的产品pid.selectpidfromordersgroupbypidhavingcount(distinctcid)>=2;如果没有GROUPBY只有HAVING,则整个结果为一组.基本SQLselect语句的通用形式不允许集合函数的嵌套.但可以有变通的GROUPBY形式.例:求所有代理商的最大销售额的平均值.郭文明2003.06.051.3.3.6SQL中行的分组例:求所有代理商的最大销售额的平均值.错:selectavg(selectmax(dollars)fromordersgroupbyaid);对:SQL高级形式,ORACLE中可行:

selectavg(t.x)from(selectaid,max(dollars)asxfromordersgroupbyaid)t;常用作法:creatviewtas(selectaid,max(dollars)asxfromordersgroupbyaid);selectavg(t.x)fromt;郭文明2003.06.051.3.4SQL数据更新SQL中数据更新包括插入数据(Insert)、修改数据(Update)和删除数据(Delete)三条语句.

1.3.4.1插入数据

INSERTINT