第4章 关系型数据库标准语言SQL

VisualFoxPro程序设计（等级考试版）第4章关系型数据库标准语言SQLVisualFoxPro程序设计（等级考试版）内容提要4.1SQL概述4.2查询功能4.3操作功能4.4定义功能4.5典型例题解析4.6小型案例实训4.7本章小结4.8习题VisualFoxPro程序设计（等级考试版）4.1SQL概述SQL具有以下特点:SQL是一种一体化的语言，它包含数据定义、数据查询、数据操纵和数据控制等方面的功能，可以完成数据库活动中的全部工作。SQL是一种高度非过程化的语言，它没有必要一步步地告诉计算机“如何”去做，只需清楚用户要“做什么”，SQL就可以将要求交给系统，自动完成全部工作。SQL非常简洁。SQL可以直接以命令方式交互使用，也可以嵌入到程序设计语言中以程序方式使用。VisualFoxPro程序设计（等级考试版）表4-1SQL命令动词SQL功能命令动词数据查询SELECT数据定义CREATE、DRZG、ALTER数据操纵INSERT、UPDATE、DELETE数据控制GRANT、REVOKEVisualFoxPro程序设计（等级考试版）4.2查询功能4.2.1创建SELECT命令可以在以下3个区域中创建SELECT命令。在命令窗口中。在VFP程序中。在查询设计器中。VisualFoxPro程序设计（等级考试版）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检索所有的工资值VisualFoxPro程序设计（等级考试版）例4.2检索工资多于1220元的职工号。SELECT职工号FROMzgWHERE工资1220结果显示如图4-3所示。图4-3检索工资结果VisualFoxPro程序设计（等级考试版）4.2.3简单的连接查询简单的连接查询是一类基于多个表的查询，与嵌套查询的区别是要查询的结果可以出自多个表，而嵌套查询的结果是基于一个表。一个数据库中的多个表之间一般都存在某种内在联系，它们共同提供有用的信息。前面的查询都是针对一个表进行的。如果一个查询同时涉及两个以上的表，则称为连接查询。其基本格式是：SELECTselect_Item1,select_Item2FROMDatabaseName1,DatabaseName2;WHEREJoinCondition当FROM之后的多个关系中含有相同的属性名时，必须用关系前缀指明属性所属的关系，“.”前面的是关系名，后面是属性名。例4.4找出工作在面积大于340的仓库的职工号和职工所在的城市。SELECT职工号,城市FROMck,zg;WHERE(面积340)AND(ck.仓库号=zg.仓库号)结果显示如图4-5所示。VisualFoxPro程序设计（等级考试版）4.2.4嵌套查询嵌套查询所要求的结果出自一个关系，但相关的条件却涉及多个关系。例如，当检索关系X中的元组时，它的条件依赖于相关的关系Y中的元组的属性值，这时使用SQL的嵌套查询功能将非常方便。其格式为：SELECTselect_Item1FROMDatabaseName1WHEREFilterCondition1IN;SELECTselect_Item2FROMDatabaseName2WHEREFilterCondition2)例4.5哪些城市至少有一个仓库的职工工资为1220元？这个查询可以使用如下的嵌套查询：SELECT城市FROMckWHERE仓库号IN;(SELECT仓库号FROMzgWHERE工资=1220)结果显示如图4-6所示。VisualFoxPro程序设计（等级考试版）4.2.5几个特殊运算符SQLSELECT有如下几个特殊运算符：BETWEEN…AND：在“…和…之间”，并且包括两个边界值。LIKE：字符串匹配运算符，通配符“%”表示0个或多个字符，另外还有一个通配符“_”(下划线)表示一个字符。!=：在SQL中“不等于”用“!=”表示，另外还可以用否定运算符NOT写出等价的命令。例4.7查询工资在1220～1270元之间的职工所有信息。SELECT*FROMzgWHERE工资BETWEEN1220AND1270VisualFoxPro程序设计（等级考试版）4.2.6排序SQLSELECT命令可以将查询结果排序，实现该排序的短语是ORDERBY。具体格式如下：ORDERBYOrder_Item[ASC|DESC][,Order_Item[ASC|DESC]…]根据列的数据对查询结果进行排序，每个Order_Item都必须对应查询结果中的一列。可以按升序(ASC)或降序(DESC)排序，可以按一列或多列排序。例4.9按zg表中的工资值升序查找出所有职工信息。SELECT*FROMzgORDERBY工资VisualFoxPro程序设计（等级考试版）4.2.7简单的计算查询事实上，SQL不仅具有一般的检索能力，而且还有计算方式的检索；SQL不仅可以查询表中数据，而且还可以计算表中的数据。用于计算检索的函数有如下几个。COUNT：计算列中选定项的数目，计算查询输出的行数。SUM：计算列中数值的和。AVG：计算列中数值的平均值。MAX：确定列中的最大值。MIN：确定列中的最小值。这些函数可以在SELECT短语中对查询结果进行计算。例4.10查询gys表中所在地的数目。SELECTCOUNT(DISTINCT地址)FROMgysVisualFoxPro程序设计（等级考试版）4.2.8分组与计算查询在SELECT-SQL命令中，常用GROUPBY子句对查询结果进行分组。其短语的格式如下：GROUPBYGroupColumn[,GroupColumn…][HAVINGFilterCondition]按列的值对查询结果的行进行分组，GroupColumn可以是常规的表字段名，也可以是一个包含SQL字段函数的字段名，还可以是一个数值表达式，指定查询结果表中的列位置。可以按一列或多列分组，还可以用HAVING进一步限定分组的条件。在分组查询时，有时要求分组满足某个条件时才检索，这时可以用HAVING子句来限定分组。HAVING子句总是跟在GROUPBY子句之后，而不可以单独使用。HAVING子句和WHERE子句不矛盾，在查询中是先用WHERE子句限定元组，然后进行分组，最后再用HAVING子句进行分组。VisualFoxPro程序设计（等级考试版）4.2.9利用空值查询由于SQL支持NULL，所以可以利用空值进行查询。NULL值具有以下特点。等价于没有任何值。与0、空格和空字符串不同。排序时具有最大的优先权。可以用于计算和大多数的函数中。NULL值不改变变量或字段的数据类型。NULL值会影响命令、函数、表达式的执行。例4.16查询已经确定供应商号的dgd信息。SELECT*FROMdgdWHERE供应商号ISNOTNULLVisualFoxPro程序设计（等级考试版）4.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.职工号;VisualFoxPro程序设计（等级考试版）4.2.11内外层相互关联的嵌套查询支前的嵌套查询都是外层查询依赖于内层查询的结果，而内层查询与外层查询无关。事实上，有时也需要内、外层相关的查询，这时内层查询的条件需要外层查询提供值，而外层查询的条件需要内层查询的结果。VisualFoxPro程序设计（等级考试版）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.仓库号)VisualFoxPro程序设计（等级考试版）4.2.13超连接查询超连接是基于多个表的查询。在新的SQL标准中还支持两个新的关系连接运算符，它们与原来的等值连接和自然连接不同。原来的连接是只有满足连接条件，相应的结果才会出现在结果表中；而这两个新的连接运算是，首先保证一个表中满足条件的元组都在结果表中，然后将满足连接条件的元组与另一个表的元组进行连接，不满足连接条件的则应将来自另一表的属性值置为空值。在一般的SQL中，超连接运算符是“*=”和“=*”。其中“*=”称为左连接，含义是在结果表中包含第一个表中满足条件的所有记录；如果结果表是在连接条件上匹配的元组，则第二个表返回相应值，否则第二个表返回空值。“=*”称为右连接，含义是在结果表中包含第二个表中满足条件的所有记录；如果结果表是在连接条件上匹配的元组，则第一个表返回相应值，否则第一个表返回空值。VisualFoxPro有专门的连接运算语法格式，它支持超连接查询。与连接有关的语法格式如下。SELECT…FROMTableINNER|LEFT|RIGHT|FULLJOINTabONJoinConditionWHEREVisualFoxPro程序设计（等级考试版）例4.19全连接，也就是除了满足条件的记录出现在查询结果外，两个表中不满足条件的记录也出现在查询结果中。SELECTck.仓库号,ck.面积,zg.职工号,zg.工资;FROMckFULLJOIN职工;ONck.仓库号=zg.仓库号VisualFoxPro程序设计（等级考试版）4.2.14集合的并运算SQL支持集合的并运算，即可以将两个SELECT语句的查询结果通过并运算合并成一个查询结果。为了进行并运算，要求两个查询结果具有相同的字段个数，并且对应字段的值要出自同一个值域。UNION子句语法如下：UNION[ALL]SELECTCommand要组合多个仍用UNION子句，可以使用括号。ALL防止UNION删除组合结果中重复的行。使用UNION将多个查询结果合并起来，形成一个完整的查询结果时，系统会自动去掉重复的元组。UNION子句遵守下列规则。不能使用UNION来组合子查询。两个SELECT命令的查询结果中的列数必须相同。两个SELECT查询结果中的对应列必须有相同的数据类型和宽度。只有最后的SELECT中可以包含ORDERBY子句，并且必须按顺序指出所输出的列。如果父表中的记录在子表中找不到对应记录，则这个记录不包含到查询结果中。外部连接可以把父表中的所有记录与父表中某个记录相匹配的