vf第四章关系数据库标准语言SQL.ppt

第四章关系数据库标准语言SQL,SQL是结构化查询语言StructuredQueryLanguage的缩写。可以说查询是SQL语言的重要组成部分，但不是全部，SQL还包含数据定义、数据操纵和数据控制功能等部分。SQL已经成为关系数据库的标准数据语言，所以现在所有的关系数据库管理系统都支持SQL.,4.1SQL概述,SQL语言具有如下主要特点：SQL是一种一体化的语言，它包括了数据定义、数据查询、数据操纵和数据控制等方面的功能，它可以完成数据库活动中的全部工作。SQI语言是一种高度非过程化的语言，它没有必要一步步地告诉计算机“如何”去做，而只需要描述清楚用户要“做什么”，SQL语言就可以将要求交给系统，自动完成全部工作。SQL语言非常简洁。虽然SQL语言功能很强，但它只有为数不多的几条命令（见书p115）。SQL语言可在命令窗口或程序中交互使用。,4.2查询功能,回顾专门关系运算,选择：从关系中找出满足给定条件的元组的操作称为选择。投影：从关系模式中指定若干个属性组成新的关系称为投影。联接：将两个表按给定的连接条件，将第一个关系中的所有记录逐个与第二个关系的所有记录按条件进行连接(连接)，即选择两个关系在连接属性上满足条件的元组拼接成一个新的关系的运算。自然联接：当连接属性具有相同属性名，连接条件取相等条件时，去掉得复属性的等值连接称为自然连接。,SQL的核心是查询。SQL的查询命令也称作SELECT命令，它的基本形式由SELECT-FROM-WHERE查询块组成，多个查询块可以嵌套执行。VFP的SQLSELECT命令的语法格式如下：(见书p128)Sele字段名1,字段名2,from表名where条件.,SELECT说明要查询的数据；FROM说明要查询的数据来自哪个或哪些表，可以对单个表或多个表进行查询；WHERE说明查询条件，即选择元组的条件；GROUPBY短语用于对查询结果进行分组，可以利用它进行分组汇总；HAVING短语必须跟随GROUPBY使用，它用来限定分组必须满足的条件；ORDERBY短语用来对查询的结果进行排序。,421简单查询,基于单个表的查询，可以有简单的查询条件。这样的查询由SELECT和FROM短语构成无条件查询，或由SELECT、from、WHERE短语组成条件查询。例41从职工表中检索所有工资值。SELECT工资FROM职工SELECT职工号,工资FROM职工,要显示的字段名,从此表名中找,可以看到在结果中有重复值，如果要去掉重复值只需要指定DISTINCT短语：SELECTDISTINCT工资FROM职工DISTINCT短语的作用是去掉查询结果中的重复值。例42检索仓库关系中的所有元组。sele*from仓库*代表要显示的所有字段,例43检索工资多于1230元的职工号。Sele职工号from职工where工资1230WHERE短语指定了查询条件，查询条件可以是任意复杂的逻辑表达式。例4.4检索哪些仓库号有工资多于1210元的职工。SELEDIST仓库号from职工WHERE工资1210,例45给出在仓库“WHl”或“WH2”工作，并且工资少于1250元的职工号。SELECT职工号FROM职工；WHER工资2结果是：WHl21230WH221235HAVING子句总是跟在GROUPBY子句之后，不可以单独使用。,428利用空值查询,SQL支持空值，当然也可以利用空值进行查询。假设在订购单关系中，一名职工正在准备订购单，但尚未选定供应商，这样若把信息存入数据库，则供应商号和订购日期两个属性均为空值，在前面给出的订购单记录中有3个这样的记录。,例423找出尚未确定供应商的订购单。SELE*FROM订购单WHER供应商号ISNULL结果是：E6NUILOR77NULIE1NULLOR80NUIJLE3NUIJLOR90NUIL注意：查询空值时要使用ISNULL，而NULL是无效的，因为空值不是一个确定的值，所以不能用“”这样的运算符进行比较。,例424列出已经确定了供应商的订购单信息。SELE*FROM订购单WHER供应商号ISNOTNULL结果是：E3S7OR6720010623E1S4OR7320010728E7S4OR7620010525E3S4OR7920010613E3S3OR9120010713,429别名与自联接查询,在联接操作中，经常需要使用关系名作前缀，有时这样显得很麻烦。因此，SQL允许在FROM短语中为关系名定义别名，格式为：关系名别名比如，如下的联接语句是一个基于4个关系的联接查询，其中必须使用关系名作前缀：,sele供应商名from供应商,订购单,职工,仓库;where地址=北京AND城市=北京;and供应商.供应商号=订购单.供应商号;and订购单.职工号=职工.职工号;and职工.仓库号=仓库.仓库号,在上面的查询中，如果使用别名就会简单一些，如下是使用了别名的同样的联接查询语句：sele供应商名from供应商S,订购单P,职工E,仓库W;where地址=北京AND城市=北京;andS.供应商号=P.供应商号;andP.职工号=E.职工号;andE.仓库号=W.仓库号,4210内外层互相关嵌套查询,例426列出每个职工经手的具有最高总金额的订购单信息。SELEout.职工号,out.供应商号,out.订购单号,；out.订购日期,out.总金额；FROM订购单outWHERE总金额；(SELEMAX(总金额)FROM订购单innerl；WHEREout.职工号innerl.职工号),在这个查询中，外层查询和内层查询使用同一个关系，给它们分别指定别名out和innerl。外层查询提供out关系中每个元组的职工号值给内层查询使用；内层查询利用这个职工号值，确定该职工经手的具有最高总金额的订购单的总金额；随后外层查询再根据out关系的同一元组的总金额值与该总金额值进行比较，如果相等，则该元组被选择。,4211使用量词和谓词的查询,子查询有关的运算符，它们有以下两种形式：ANY|AIL|SOME(子查询)NOTEXISTS(子查询)ANY、ALI和SOME是量词，其中ANY和SOME是同义词，在进行比较运算时只要子查询中有一行能使结果为真，则结果就为真;而ALI则要求子查询中的所有行都使结果为真时，结果才为真。EXISTS是谓词，EXISTS或NOTEXISTS是用来检查在子查询中是否有结果返回，即存在元组或不存在元组。,例427检索那些仓库中还没有职工的仓库的信息。这里的查询是没有职工或不存在职工，所以可以使用谓词NOTEXISTS：SELECT*FROM仓库WHERENOTEXISTS；(SELE*FROM职工WHER仓库号仓库.仓库号)注意：这里的内层查询引用了外层查询的表，只有这样使用谓词EXISTS或NOTEXISTS才有意义。所以这类查询也都是内、外层互相关嵌套查询。以上的查询等价于：SELECT*FROM仓库WHERE仓库号NOTIN；(SELECT仓库号FROM职工),例428检索那些仓库中至少已经有一个职工的仓库的信息。SELECT*FROM仓库WHEREEXISTS；(SELE*FROM职工WHER仓库号=仓库.仓库号)它等价于：SEIECT*FROM仓库WHERE仓库号IN;(SELECT仓库号FROM职工)注意：NOTEXISTS只是判断子查询中是否有或没有结果返回，它本身并没有任何运算或比较。,例429检索有职工的工资大于或等于WHl仓库中任何一名职工工资的仓库号。这个查询可以使用ANY或SOME量词。Any表示任何一个;some表示若干.SELEDIST仓库号FRoM职工WHERE工资=ANY；(SELE工资FROM职工WHER仓库号=“WHl”)它等价于：SEIEDIST仓库号FROM职工WHER工资；(SELEMIN(工资)FROM职工WHER仓库号=“wHl”),例430检索有职工的工资大于或等于WHl仓库中所有职工工资的仓库号。这个查询使用ALL量词。SELEDIST仓库号FRoM职工WHER工资=ALL;(SELE工资FROM职工WHER仓库号=“WHl”)它等价于：SEIEDIST仓库号FROM职工WHER工资;(SELEMAX(工资)FROM职工WHERE仓库号=“WHl”),ANY、ALL谓词与集函数及IN谓词的等价转换关系,4212超联接查询,在一般SQL中超联接运算符是“*”和“=*”。其中“*”称为左联接，含义是在结果表中包含第一个表中满足条件的所有记录；如果有在联接条件上匹配的元组，则第二个表返回相应值，否则第二个表返回空值；而“*”称为右联接，含义是在结果表中包含第二个表中满足条件的所有记录；如果有在联接条件上匹配的元组，则第一个表返回相应值，否则第一个表返回空值。注意：VFP不支持超联接运算符“*”和“*”,VFP有专门的联接运算语法格式，它支持超联接查询，联接运算有关的语法格式如下：SELECTFROM表名INNER|LEFT|RIGHT|FULLJOIN表ON联接条件WHERE其中：INNERJOIN=JOIN,为普通的联接,在VFP称内部联接。LEFTJOIN为左联接。RIGHTJOIN为右联接。FULLJOIN可以称为全联接，即两个表中的记录不管是否满足联接条件将都在目标表或查询结果中出现，不满足联接条件的记录对应部分为NULL。,例4.31内部联接，即只有满足联接条件的记录才出现在查询结果中。SEIECT仓库.仓库号,城市,面积,职工号,工资；FROM仓库JOIN职工；ON仓库.仓库号职工.仓库号如下两种命令格式也是等价的：SELECT仓库.仓库号,城市,面积,职工号,工资；FROM仓库INNERJoin职工；ON仓库仓库号职工仓库号和SELECT仓库.仓库号,城市,面积,职工号,工资；FROM仓库,职工WHERE仓库.仓库号=职工.仓库号,例4.32左联接，即除满足联接条件的记录出现在查询结果中外，第一个表中不满足联接条件的记录也出现在查询结果中。SELE仓库,仓库号,城市,面积,职工号,工资；FROM仓库LEFTJOIN职工；ON仓库仓库号职工仓库号右联接:除满足联接条件的记录出现在查询结果中外，第二个表中不满足联接条件的记录也出现在查询结果中.全联接:两个表中不满足联接条件的记录也出现在查询结果中。,注意：VFP的SQLSELECT语句的联接格式只能实现两个表的联接，如果要实现多个表的联接，还需要使用标准格式。例如下面是一个基于4个关系的联接查询：SELECT仓库.仓库号,城市,供应商名,地址；FROM供应商，订购单，职工，仓库；WHERE供应商.供应商号订购单.供应商号；AND订购单.职工号职工.职工号；AND职工.仓库号仓库.仓库号,4213集合的并运算,SQL支持集合的并(UNION)运算，即可以将两个SEIECT语句的查询结果通过并运算合并成一个查询结果。为了进行并运算，要求这样的两个查询结果具有相同的字段个数，并且对应字段的值要出自同一个值域，即具有相同的数据类型和取值范围。,例如，如下语句的结果是城市为北京和上海的仓库信息：SELE*FROM仓库WHER城市=“北京”；UNION；SEIE*FROM仓库WHERE城市=“上海”,4.2.14VFP中SQLSELECT的几个特殊选项,1显示部分结果例435只显示前几项记录。有时只需要满足条件的前几个记录，这时使用TOPnExprPERCENT短语非常有用.其中是数字表达式，当不使用PERCENT时，nExpr是l至32767间的整数，说明显示前几个记录；当使用PERCENT时，nExpr是001至9999间的实数，说明显示结果中前百分之几的记录。需要注意的是TOP短语要与ORDERBY短语同时使用才有效。,例436显示工资最高的三位职工的信息。SELE*TOP3FROM职工ORDERBY工资DESC例437显示工资最低的那30职工的信息。SELE*TOP30PERCFROM职工ORDERBY工资,2将查询结果存放到数组中可以使用“INTOARRAY数组名”短语将查询结果存放到数组中。一般将存放查询结果的数组作为二维数组来使用，每行一条记录，每列对应于查询结果的一列。查询结果存放在数组中，可以非常方便地在程序中使用。如将查询到的职工信息存放在数组tmp中：SELECT*FROM职工INTOARRYtmptmp(1，1)存放的是第一条记录的仓库号字段值，tmp(1，3)存放的是第一条记录的工资字段值等。,3将查询结果存放在临时文件中使用短语INTOCURSORcursorName可以将查询结果存放到临时数据表文件中.其中cursorName是临时文件名，该短语产生的临时文件是一个只读的dbf文件，当查询结束后该临时文件是当前文件，可以像一般的dbf文件一样使用，但仅是只读。当关闭文件时该文件将自动删除。如将查询到的职工信息存放在临时dbf文件tmp中：。SELECT*FROM职工INTOCURSORtmp,4将查询结果存放到永久表中使用短语“INToDBF|TABLE表名”可以将查询结果存放到永久表中(dbf文件)。比如将例436的查询结果存放在表hh中可以使用如下语句：SELE*TOP3FROM职工INToTABLEhh;ORDERBY工资DESC,5将查询结果存放到文本文件中使用短语TOFILE文件名ADDITIVE可以将查询结果存放到文本文件中.其中文本文件名(默认扩展名是txt)，如果使用ADDI结果将追加在原文件的尾部，否则将覆盖原有文件。如将查询结果以文本的形式存在hnp.txt中：SELE*TOP3FROM职工TOFILEhnpORDERBY;工资DESC如果TO短语和INTO短语同时使用，则TO短语将会被忽略。6将查询结果直接输出到打印机使用短语TOPRINTERPROMPT可以直接将查询结果输出到打印机.,VFP在支持标准SQL方面还有一些限制和遗憾，例如不支持多层嵌套查询、不支持COMPUTE短语等。,43操作功能,SQL的操作功能是指对数据库中数据的操作功能，主要包括数据的插入、更新和删除三个方面的内容。431插入VFP支持两种SQL插入命令的格式，第一种格式是标准格式，第二种格式是VFP的特殊格式。第一种格式：INSERTINTO表名(字段1，字段2)VALUES(记录值1，记录值2，),第二种格式：INSERTINTO表名FROMARRAY数组名|FROMMEMVAR其中：FROMMEMVAR说明根据同名的内存变量来插入记录值，如果同名的变量不存在，那么相应的字段为默认值或空。,例如，往订购单关系中插入元组：(”E7”，”S4”，”OR01“，20010525)可用如下命令：INSERTINTo订购单；ALUES(”E7”,”s4”,”oR01”,2001-05-25)注意这里日期型数据的表示方式。如果供应商号尚未确定，则先只能插入两个属性的值:INSERTINTO订购单(职工号,订购单号);VALUES(”E7”，”OR01”),注意：当一个表定义了主索引或候选索引后，由于相应的字段具有关键字的特性，即不能为空，所以只能用此命令插入记录。FoxPro以前的插入命令(INSERT或APPEND)是先插入一条空记录，然后再输入各字段的值，由于关键字字段不允许为空，所以使用以前的方法就不能成功地插入记录。,432更新SQL的数据更新命令格式如下：UPDATE表名SET字段1=表达式1，字段2=表达式2，WHERE条件一般使用WHERE子句指定条件，以更新满足条件的一些记录的字段值，并且一次可以更新多个字段；如果不使用WHERE子句，则更新全部记录。例如，给WHl仓库的职工提高10的工资的命令：UPDATE职工SET工资工资*l.10；WHERE仓库号”WHl”又如，给所有学生的年龄增加1岁：UPDATE学生SET年龄年龄十1,433删除SQL从表中删除数据的命令格式如下：DELETEFROM表名WHERE条件这里FROM指定从哪个表中删除数据，WHERE指定被删除的记录所满足的条件，如果不使用WHERE子句，则删除该表中的全部记录。例如，要删除仓库关系中仓库号值是WH2的元组，可用命令：DELETEFROM仓库WHERE仓库号”WH2”注意：在VFP中SQLDELETE命令同样是逻辑删除记录，如果要物理删除记录需要继续使用PACK命令。,44定义功能,标准SQL的数据定义功能非常广泛，一般包括数据库的定义、表的定义、视图的定义、存储过程的定义、规则的定义和索引的定义等若干部分。,44.1表的定义,通过SQL的CREATETABLE命令建立表，相应的命令格式是：(P135)CREATETABLE|DBF表名简化形式:CREATETABLE表名(字段名1,类型(长度),字段名2,类型(长度).),例438用命令建立订货管理1数据库。CREATEDATA订货管理1建立仓库1表：CREATETABLE仓库1(;仓库号C(5)PRIMARYKEY，;城市C(10)，;面积ICHECK(面积0)ERROR”面积应该大于0!”)这里用的TABLE和DBF是等价的.在当前打开的订货管理1数据库中建立了仓库1表，其中仓库号是主关键字(PRIMARYKEY说明)，用CHECK为面积字段值说明了有效性规则(面积0)，用ERROR为该有效性规则说明了出错提示信息“面积应该大于01”。,例439用SQLCREATE命令建立职工l表。CREATETABLE职工1(仓库号C(5),;职工号C(5)PRIMARYKEY,;工资ICHECK(工资=1000AND工资0ERROR“总金额应该大于0！,例42将订购单1表的订购单号字段的宽度由原来的5改为6.ALTERTABLE订购单1ALTER订购单号C(6)从命令格式可以看出，该格式可以修改字段的类型、宽度、有效性规则、错误信息、默认值，定义主关键字和联系等；但是不能修改字段名，不能删除字段，也不能删除已经定义的规则等。,格式2：ALTERTABLE表ALTERCOLUMNNULL|NTNuLLSETDEFAULT表达式SETCHECK表达式ERROR提示信息DROPDEFAULTDROPCHECK从命令格式可以看出，该格式主要用于定义、修改和删除有效性规则和默认值定义。,例4.43修改”总金额”字段的有效性规则ALTERTABLE订购单1ALTER总金额SETCHECK总金额100ERROR“总金额应该大于100!”例4.44删除了”总金额”字段的有效性规则ALTERTABLE订购单1ALTER总金额DROPCHECK,格式3：ALTERTABLE表DROPCOLUMNfieldname3SETCHECKDROPCHECK该格式可以