Visual FoxPro程序设计(等级考试版)：第4章 关系型数据库标准语言SQL

第4章关系型数据库标准语言SQL48学时内容提要4.1SQL概述4.2查询功能4.3操作功能4.4定义功能4.5小型案例实训4.6学习加油站4.7上机实验4.8习题4.1SQL概述

SQL具有以下特点:SQL是一种一体化的语言，它包含数据定义、数据查询、数据操纵和数据控制等方面的功能，可以完成数据库活动中的全部工作。SQL是一种高度非过程化的语言，它没有必要一步步地告诉计算机“如何”去做，只需清楚用户要“做什么”，SQL就可以将要求交给系统，自动完成全部工作。SQL非常简洁。SQL可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用。表4-1SQL命令动词

SQL功能命令动词数据查询SELECT数据定义CREATE、DRZG、ALTER数据操纵INSERT、UPDATE、DELETE数据控制GRANT、REVOKE4.2查询功能

4.2.1创建SELECT命令可以在以下3个区域中创建SELECT命令。在命令窗口中。在VFP程序中。在查询设计器中。4.2.2简单查询

简单查询是针对单个表的查询，由SELECT和FROM短语构成无条件查询或由SELECT、FROM和WHERE短语构成条件查询。其基本格式是：

SELECTselect_ItemFROMDatabaseName如果要去掉查询结果中的重复值，只需要指定DISTINCT短语：

SELECTDISTINCTselect_ItemFROMDatabaseName此外还可以用WHERE短语指定查询条件，查询条件可以是任意复杂的逻辑表达式：

SELECTselect_ItemFROMDatabaseNameWHEREFilterCondition

例4.1

从zg关系中检索所有的工资值。

SELECT工资FROMzg

结果显示如图4-2所示。图4-2检索所有的工资值例4.2

检索工资多于1220元的职工号。SELECT职工号FROMzgWHERE工资>1220结果显示如图4-3所示。图4-3检索工资结果4.2.3简单的连接查询

简单的连接查询是一类基于多个表的查询，与嵌套查询的区别是要查询的结果可以出自多个表，而嵌套查询的结果是基于一个表。一个数据库中的多个表之间一般都存在某种内在联系，它们共同提供有用的信息。前面的查询都是针对一个表进行的。如果一个查询同时涉及两个以上的表，则称为连接查询。其基本格式是：SELECTselect_Item1,select_Item2FROMDatabaseName1,DatabaseName2;WHEREJoinCondition

当FROM之后的多个关系中含有相同的属性名时，必须用关系前缀指明属性所属的关系，“.”前面的是关系名，后面是属性名。例4.4

找出工作在面积大于340的仓库的职工号和职工所在的城市。SELECT职工号,城市FROMck,zg;WHERE(面积>340)AND(ck.仓库号=zg.仓库号)结果显示如图4-5所示。4.2.4嵌套查询

嵌套查询所要求的结果出自一个关系，但相关的条件却涉及多个关系。例如，当检索关系X中的元组时，它的条件依赖于相关的关系Y中的元组的属性值，这时使用SQL的嵌套查询功能将非常方便。其格式为：SELECTselect_Item1FROMDatabaseName1WHEREFilterCondition1IN;SELECTselect_Item2FROMDatabaseName2WHEREFilterCondition2)例4.5

哪些城市至少有一个仓库的职工工资为1220元？这个查询可以使用如下的嵌套查询：SELECT城市FROMckWHERE仓库号IN;(SELECT仓库号FROMzgWHERE工资=1220)结果显示如图4-6所示。4.2.5几个特殊运算符

SQLSELECT有如下几个特殊运算符：BETWEEN…AND：在“…和…之间”，并且包括两个边界值。LIKE：字符串匹配运算符，通配符“%”表示0个或多个字符，另外还有一个通配符“_”(下划线)表示一个字符。!=：在SQL中“不等于”用“!=”表示，另外还可以用否定运算符NOT写出等价的命令。例4.7

查询工资在1220～1270元之间的职工所有信息。SELECT*FROMzgWHERE工资BETWEEN1220AND12704.2.6排序

SQLSELECT命令可以将查询结果排序，实现该排序的短语是ORDERBY。具体格式如下：ORDERBYOrder_Item[ASC|DESC][,Order_Item[ASC|DESC]…]

根据列的数据对查询结果进行排序，每个Order_Item都必须对应查询结果中的一列。可以按升序(ASC)或降序(DESC)排序，可以按一列或多列排序。例4.9

按zg表中的工资值升序查找出所有职工信息。SELECT*FROMzgORDERBY工资4.2.7简单的计算查询

事实上，SQL不仅具有一般的检索能力，而且还有计算方式的检索；SQL不仅可以查询表中数据，而且还可以计算表中的数据。用于计算检索的函数有如下几个。COUNT：计算列中选定项的数目，计算查询输出的行数。SUM：计算列中数值的和。AVG：计算列中数值的平均值。MAX：确定列中的最大值。MIN：确定列中的最小值。这些函数可以在SELECT短语中对查询结果进行计算。例4.10

查询gys表中所在地的数目。SELECTCOUNT(DISTINCT地址)FROMgys4.2.8分组与计算查询在SELECT-SQL命令中，常用GROUPBY子句对查询结果进行分组。其短语的格式如下：GROUPBYGroupColumn[,GroupColumn…][HAVINGFilterCondition]按列的值对查询结果的行进行分组，GroupColumn可以是常规的表字段名，也可以是一个包含SQL字段函数的字段名，还可以是一个数值表达式，指定查询结果表中的列位置。可以按一列或多列分组，还可以用HAVING进一步限定分组的条件。在分组查询时，有时要求分组满足某个条件时才检索，这时可以用HAVING子句来限定分组。HAVING子句总是跟在GROUPBY子句之后，而不可以单独使用。HAVING子句和WHERE子句不矛盾，在查询中是先用WHERE子句限定元组，然后进行分组，最后再用HAVING子句进行分组。4.2.9利用空值查询

由于SQL支持NULL，所以可以利用空值进行查询。NULL值具有以下特点。等价于没有任何值。与0、空格和空字符串不同。排序时具有最大的优先权。可以用于计算和大多数的函数中。NULL值不改变变量或字段的数据类型。NULL值会影响命令、函数、表达式的执行。例4.16

查询已经确定供应商号的dgd信息。SELECT*FROMdgdWHERE供应商号ISNOTNULL4.2.10别名与自连接查询

经常需要使用关系名做前缀，才能实现连接操作，有时这样显得很麻烦。因此，SQL允许在FROM短语中为关系名定义别名。格式为：<关系名><别名>例如：SELECT供应商名FORMgys,dgd,zg,ck;WHERE地址="北京"AND城市="北京";ANDgys.供应商号=dgd.供应商号;ANDdgd.职工号=zg.职工号;ANDzg.仓库号=ck.仓库号;在上述查询中，使用别名会更简单。下面是使用别名的同样的连接查询语句。SELECT供应商名FORMgysA,dgdB,zgC,ckD;WHERE地址="北京"AND城市="北京";ANDA.供应商号=B.供应商号;ANDB.职工号=C.职工号;4.2.11内外层相互关联的嵌套查询

支前的嵌套查询都是外层查询依赖于内层查询的结果，而内层查询与外层查询无关。事实上，有时也需要内、外层相关的查询，这时内层查询的条件需要外层查询提供值，而外层查询的条件需要内层查询的结果。4.2.12使用量词和谓词的查询

与子查询有关的运算符除了IN和NOTIN运算符之外，还有<表达式><比较运算符>[ANY|ALL|SOME](子查询)和[NOT]EXISTS(子查询)ANY、ALL和SOME是量词，其中ANY和SOME是同义词，在进行比较运算时只要子查询中有一行使结果为真，则结果就为真；而ALL则要求子查询中的所有行都使结果为真时，结果才为真。EXIT是谓词，EXISTS或NOTEXISTS是用来检查在子查询中是否有结果返回(即存在元组或不存在元组)。例4.18

查询有哪些仓库中至少有一名职工的仓库信息。SELECT*FROMckWHEREEXISTS;(SELECT*FROMzgWHEREzg.仓库号=ck.仓库号)4.2.13超连接查询

超连接是基于多个表的查询。在新的SQL标准中还支持两个新的关系连接运算符，它们与原来的等值连接和自然连接不同。原来的连接是只有满足连接条件，相应的结果才会出现在结果表中；而这两个新的连接运算是，首先保证一个表中满足条件的元组都在结果表中，然后将满足连接条件的元组与另一个表的元组进行连接，不满足连接条件的则应将来自另一表的属性值置为空值。在一般的SQL中，超连接运算符是“*=”和“=*”。其中“*=”称为左连接，含义是在结果表中包含第一个表中满足条件的所有记录；如果结果表是在连接条件上匹配的元组，则第二个表返回相应值，否则第二个表返回空值。“=*”称为右连接，含义是在结果表中包含第二个表中满足条件的所有记录；如果结果表是在连接条件上匹配的元组，则第一个表返回相应值，否则第一个表返回空值。VisualFoxPro有专门的连接运算语法格式，它支持超连接查询。与连接有关的语法格式如下。SELECT…FROMTableINNER|LEFT|RIGHT|FULLJOINTabONJoinConditionWHERE例4.19

全连接，也就是除了满足条件的记录出现在查询结果外，两个表中不满足条件的记录也出现在查询结果中。SELECTck.仓库号,ck.面积,zg.职工号,zg.工资;FROMckFULLJOIN职工;ONck.仓库号=zg.仓库号4.2.14集合的并运算

SQL支持集合的并运算，即可以将两个SELECT语句的查询结果通过并运算合并成一个查询结果。为了进行并运算，要求两个查询结果具有相同的字段个数，并且对应字段的值要出自同一个值域。UNION子句语法如下：

UNION[ALL]SELECTCommand要组合多个仍用UNION子句，可以使用括号。ALL防止UNION删除组合结果中重复的行。使用UNION将多个查询结果合并起来，形成一个完整的查询结果时，系统会自动去掉重复的元组。

UNION子句遵守下列规则。不能使用UNION来组合子查询。两个SELECT命令的查询结果中的列数必须相同。两个SELECT查询结果中的对应列必须有相同的数据类型和宽度。只有最后的SELECT中可以包含ORDERBY子句，并且必须按顺序指出所输出的列。如果父表中的记录在子表中找不到对应记录，则这个记录不包含到查询结果中。外部连接可以把父表中的所有记录与父表中某个记录相匹配的子表记录一起放到查询结果中。要在VFP中创建外部连接，需要嵌套使用SELECT命令。例4.20

查询结果是城市为北京和西安的仓库信息。SELECT*FROMckWHERE城市="北京";UNION;SELECT*FROMckWHERE城市="西安"4.2.15VisualFoxPro中SQLSELECT的几个特殊选项在符合查询条件的所有记录中，选择指定数量或百分比的记录，只显示前几项记录。(1)使用TOPnExpr[PERCENT]短语非常有用，其中nExpr是数字表达式，当不使用PERCENT时，nExpr是1～32767间的整数，说明显示前几条记录；当使用PERCENT时，nExpr是0.01～99.99间的实数，说明显示结果中前百分之几的记录。需要注意的是，TOP短语要与ORDERBY短语同时使用才有效。(2)将查询结果存放到数组中可以使用INTOARRAYArrayName短语将查询结果存放到数组中，ArrayName可以是任意的数组变量名。一般将存放查询结果的数组作为二维数组来使用，每行一条记录，每列对应于查询结果的一列。查询结果存放在数组中，可以非常方便地在程序中使用。(3)将查询结果存放在临时文件夹中使用短语INTOCURSORCursorName可以将查询结果存放到临时数据库文件中，其中CursorName是临时文件名，该短语产生的临时文件是一个只读的.dbf文件，当查询结束后该临时文件是当前文件，可以像一般的.dbf文件一样使用(当然是只读)，当关闭文件时该文件将自动删除。

一般利用INTOCURSOR短语存放一些临时结果，比如一些复杂的汇总可能需要分段完成，需要根据几个中间结果再汇总等，这时利用该短语存放中间结果就非常合适。使用完后这些临时文件会自动删除。(4)将查询结果存放到永久表中使用短语INTODBF|TABLETableName可以将查询结果存放到永久表中(.dbf文件)。(5)将查询结果存放到文本文件中使用短语TOFILEFileName[ADDITIVE]可以将查询结果存放到文本文件中，其中FileName给出了文本文件名(默认是.TXT)，如果使用ADDITIVE则结果将追加在原文件的尾部，否则将覆盖原有文件。(6)将查询结果直接输出到打印机使用短语TOPRINTER[PROMPT]可以直接将查询结果输出到打印机，如果使用了PROMPT选型，在开始打印之前会弹出打印机设置对话框。4.3操作功能

4.3.1数据插入

VisualFoxPro支持两种SQL插入命令的格式，可以说第一种格式是标准格式，第二种格式是VisualFoxPro的特殊格式。第一种格式是：

INSERTINTOdbf_name[(fname1[,fname2,…])]VALUES(eExpression1[,eExpression2,…])命令说明：INSERTINTOdbf_name指定要追加记录的表名，dbf_name中可以包含路径，也可以是一个名称表达式。也可以用fname1[,fname2[,…]]来指定新记录的值所对应的字段名。VALUES(eExpression1[,eExpression2[,…]])指定新插入记录的字段值。第二种格式是：

INSERTINTOdbf_nameFROMARRAYArrayName|FROMMEMVAR

命令说明：FROMARRAYArrayName指定一个数组，数组中的数据将被插入到新记录中。FROMMEMVAR把内存变量的内容插入到与其同名的字段中。插入新记录后，指针指向新记录。4.3.2数据更新

SQL的数据更新命令UPDATE用于更新表中的记录。其格式如下：UPDATETableNameSETColumn_Name1=eExpression1[,Column_Name2=eExpression2……]WHERECondition命令说明：TableName指定要更新记录的表名。SETColumn_Name1=eExpression1[,Column_Name1=eExpression2…]指定要更新的列及这些列的新值。WHERECondition指定要更新记录的条件。

UPDATE命令的功能是修改指定表中满足WHERE子句条件的元组。其中SET子句用于指定修改方法，即用<表达式>的值取代相应的属性列值。如果省略WHERE子句，则表示要修改表中的所有元组。例如，将“选课”表中学生号为003号的学生成绩增加15分，应使用如下命令：UPDATE选课SET成绩=成绩+15WHERE学生号="003“4.3.3数据删除SQL从表中删除DELETE数据的命令格式如下：

DELETEFROMTableName[WHERECondition]

这里FROM指定从哪个表中删除数据，WHERE指定被删除的记录所满足的条件，如果不使用WHERE子句，则删除该表中的全部记录。使用DELETE命令删除的记录并不是物理删除，只是在记录上打上一个删除标记。如果要真正地从数据库中把相应的记录清除掉，还必须用PACK命令。打上删除标记的记录可以通过RECALL命令恢复。SQL中删除记录的格式如下：DELETEFROMTableNameWHERECondition命令说明：TableName指定要给其中的记录加删除标记的表。WHERECondition指定给某些记录做删除标记的条件。4.4定义功能

4.4.1表的定义在VisualFoxPro中可通过SQL语句的CREATETABLE命令来建立表。命令格式：CREATETABLE|DBFTableName1[NAMELongTableName][FREE](FieldName1FieldType[(nFieldWidth[,nPrecision])][NULL|NOTNULL][CHECKlExpression1[ERRORcMessageText1]][DEFAULTeExpression1][PRIMARYKEY|UNIQUE][REFERENCESTableName2[TAGTagName1]][NOCPTRANS][,FieldName2…][,PRIMARYKEYeExpression2TAGTagName2|,UNIQUEeExpression3TAGTagName3][,FOREIGNKEYeExpression4TAGTagName4[NODUP]REFERENCESTableName3[TAGTagName5]]

[,CHECKlExpression2[ERRORcMessageText2]])|FROMARRAYArrayName命令说明：CREATETABLE|DBFTableName1指定要创建的表的名称，TABLE和DBF选项作用相同。NAMELongTableName指定表的长名。FREE指定所创建的表不添加到数据库中。FieldName1FieldType[(nFieldWidth[,nPrecision])]分别指定字段名、字段类型、字段宽度和字段精度(小数位数)。NULL在字段中允许NULL值；NOTNULL在字段中不允许NULL值。CHECKlExpression1指定字段的有效性规则，lExpression1可以是用户自定义函数。ERRORcMessageText1指定当字段规则产生错误时，cMessageText1显示错误信息。DEFAULTeExpression1指定字段的默认值。PRIMARYKEY将此字段作为主索引，主索引标识名和字段名相同。UNIQUE将此字段作为一个候选索引，候选索引标识名和字段名相同。REFERENCESTableName2[TAGTagName1]指定与之建立永久关系的父表。PRIMARYKEYeExpression2TAGTagName2指定要创建的主索引。UNIQUEeExpression3TAGTagName3创建候选索引。FOREIGNKEY创建一个外部索引(非主索引)，并建立和父表的关系。REFERENCES指定与之建立永久关系的父表。CHECK指定表的有效性规则。FROMARRAYArrayName指定—个已存在的数组名称。4.4.2表的删除

VisualFoxPro可以通过SQL的DROPTABLE命令来删除表。命令格式：DROPTABLETableName命令说明：执行DROPTABLE命令应在当前数据库下，否则将从磁盘上删除表文件，但该表在数据库文件中的信息并没有删除，以后会出现错误提示。若要删除数据库中的表，最好先打开该数据库，在当前数据库中进行操作。TableName指定要移出并删除的表的名称。DROPTABLE直接从磁盘上删除table_name所对应的.dbf文件。如果table_name是数据库中的表并且相应的数据库是当前数据库，则从数据库中删除了表；否则虽然从磁盘上删除了.dbf文件，但是在数据库中(记录在.dbc文件中)的信息却没有删除，此后会出现错误提示。所以要输出数据库中的表时，最好在数据库中进行操作，即数据库是当前打开的数据库。4.4.3表结构的修改

在VisualFoxPro中可通过SQL的ALTERTABLE命令来修改表。格式1：ALTERTABLETableName1ADD|ALTER[COLUMN]FieldName1FieldType[(nFieldWidth[,nPrecision])][NULL|NOTNULL][CHECKlExpression1[ERRORcMessageText1]][DEFAULTeExpression1][PRIMARYKEY|UNIQUE][REFERENCETableName2[TAGTagName1]]命令说明：该格式可以添加(ADD)新的字段或修改(ALTER)已有的字段，它的句法基本可以与CREATETABLE的句法相对应。从命令格式可以看出，该格式可以修改字段的类型、宽度、有效性规则、错误信息、默认值，定义主关键字和联系等。但是不能删除字段名，不能删除字段，也不能删除已经定义的规则等。格式2：ALTERTABLE

TableName1ALTER[COLUMN]FieldName2[NULL|N