SQL查询

第5章关系数据库标准语言SQL易学良13087300765群号码：81883685.1SQL概述与查询功能SQL是StructuredQueryLanguage(结构化查询语言)的缩写。查询是SQL语言的重要组成部分，但不是全部，SQL还包含数据定义、数据操作和数据控制功能等部分。(一)SQL概述SQL的查询命令是SELECT命令，VFP的SQLSELECT命令的其常用格式如下：SELECT［ALL｜DISTINCT］［〈别名〉］〈选项〉［AS〈显示列名〉］［,［〈别名〉］〈选项〉［AS〈显示列名〉…］］FROM〈表名1〉［〈别名1〉］［,〈表名2〉［〈别名2〉…］］［WHERE…］[GROUPBY…］［HAVING…］［UNION[ALL]…］［ORDERBY…］(二)查询功能图5-1本节用到的四个表基本格式：select…from…或者select…from…where…§1简单查询[例5-1]从职工关系中检索仓库WH2的所有工资值。select工资from职工where仓库号=“WH2”如果结果中有重复值，并且要去掉的话，可以用disctinct短语：selectdistinct工资from职工where仓库号=“WH2”[例5-2]检索仓库关系中的所有元组。select*from仓库其中“*”是通配符，表示所有属性（字段）。§1简单查询联接是关系的基本操作之一，联接查询是一种基于多个关系的查询。[例5-3]找出工作在面积大于400的仓库的职工号以及这些职工工作所在的城市。§2简单的联接查询select职工号,城市from仓库,职工;where面积400and职工.仓库号=仓库.仓库号如果查询所要求的结果出自一个关系，但相关的条件却涉及多个关系，则可以利用嵌套查询来完成。[例5-4]哪些城市至少有一个仓库的职工的工资为1250。select城市from仓库where仓库号in;(select仓库号from职工where工资=1250)这里的in相当于集合运算Є。表否定，即集合的不属于可以使用notin§3简单嵌套查询[例5-5]找出和职工E4挣同样工资的所有职工。select职工号from职工where工资=;(select工资from职工where职工号=“E4”)Between…and和like的用法：[例5-6]检索出工资在（或不在）1220元到1240元范围内的职工信息。select*from职工where工资between1220and1240select*from职工where工资notbetween1220and1240[例5-7]从供应商关系中检索出全部公司的信息（不要工厂或其他供应商的信息）select*from供应商where供应商名like“%公司”其中like是字符串匹配运算符，通配符“%”表示0个或多个字符，通配符“_”（下划线）表示一个字符。§4几个特殊运算符[例5-8]先按仓库号升序排序、再按工资降序排序检索出全部职工的信息。select*from职工orderby仓库号asc,工资descOrderby是对最终的查询结果排序，不可以在子查询中使用该短语。§5排序用于计算检索的函数有：(1)count:计数；(2)sum：求和；(3)avg:计算平均值；(4)max:求最大值；(5)min:求最小值。这些函数可以用在select短语中对查询结果进行计算。[例5-9]求WH2仓库的职工人数、工资总数、平均工资、最高工资和最低工资。Selectcount()as人数,sum(工资)as工资合计,;avg(工资)as平均工资,max(工资)as最高工资,;min(工资)as最低工资from职工where仓库号=“WH2”§6简单的计算查询Groupby[having]的用法：[例5-10]求每个仓库的平均工资。select仓库号,avg(工资)from职工groupby仓库号[例5-11]求至少有两个职工的每个仓库的平均工资。select仓库号,count(),avg(工资)from职工groupby;仓库号havingcount()=2§7分组与计算查询[例5-12]找出尚未确定供应商的订购单。select*from订购单where供应商号isnull空值不是一个确定的值，不能用“=null”，要用“isnull”；不是空值用“isnotnull”§8利用空值查询为了说明联接，我们在职工关系中加入“姓名”和“经理”两个字段：职工(仓库号,职工号,姓名,工资,经理)其中“职工号”和“经理”两个属性出自同一个值域，同一个元组的这两个属性是“上、下级”关系。[例5-13]根据新的职工关系列出上一级经理及其职员（被其领导）的清单。selects.姓名,“领导”,e.姓名from职工s,职工e;wheres.职工号=e.经理§9自联接查询嵌套查询可以是内、外层互相关的查询。这时内层查询的条件需要外层查询提供值，而外层查询的条件需要内层查询的结果。例如在订购单表中加入一个新字段“总金额”，说明完成该订购单所应付出的总金额数，新的订购单表的结构如下：订购单(职工号,供应商号,订购单号,订购日期,总金额)[例5-14]列出每个职工经手的具有最高总金额的订购单信息。selectout.职工号,out.供应商号,out.订购单号,out.订购日期,;out.总金额from订购单outwhere总金额=;(selectmax(总金额)from订购单inner1;whereout.职工号=inner1.职工号)§10内外层互相嵌套查询嵌套查询中还可以有如下两种形式：表达式比较运算符[any|all|some](子查询)和[not]exists(子查询)Any、all和some是量词；Exists是谓词，exists或notexists是用来检查在子查询中是否有结果返回。§11使用量词和谓词的嵌套查询[例5-15]检索那些仓库中还没有职工的仓库的信息。select*from仓库wherenotexists;(select*from职工where仓库号=仓库.仓库号)[例5-16]检索那些仓库中至少已经有一个职工的仓库的信息。select*from仓库whereexists;(select*from职工where仓库号=仓库.仓库号)[例5-17]检索有职工的工资大于或等于WH1仓库中任何一名职工的工资的仓库号。selectdistinct仓库号from职工where工资=any;(select工资from职工where仓库号=“WH1”)[例5-18]检索有职工的工资大于或等于WH1仓库中所有职工的工资的仓库号。selectdistinct仓库号from职工where工资=all;(select工资from职工where仓库号=“WH1”)超联接是首先保证一个表中满足条件的元组都在结果表中，然后将满足联接条件的元组与另一个表的元组进行联接，不满足联接条件的则将来自另一表的属性值设为空值。语法格式如下：select……fromtable|left|right|fulljointableonjoinconditionwhere……§12超联接查询[例5-19]普通联接，即只有满足联接条件的记录才出现在查询结果中。select仓库.仓库号,城市,面积,职工号,工资;from仓库join职工on仓库.仓库号=职工.仓库号其中from子句部分和下列两中形式等价：from仓库innerjoin职工on仓库.仓库号=职工.仓库号或from仓库,职工onwhere仓库.仓库号=职工.仓库号[例5-20]左联接，即除满足联接条件的记录出现在查询结果中，第一个表中不满足联接条件的记录也出现在查询结果中。select仓库.仓库号,城市,面积,职工号,工资;from仓库leftjoin职工on仓库.仓库号=职工.仓库号[例5-21]右联接，即除满足联接条件的记录出现在查询结果中，第二个表中不满足联接条件的记录也出现在查询结果中。select仓库.仓库号,城市,面积,职工号,工资;from仓库rightjoin职工on仓库.仓库号=职工.仓库号[例5-22]全联接，即除满足联接条件的记录出现在查询结果中，两个表中不满足联接条件的记录也出现在查询结果中。select仓库.仓库号,城市,面积,职工号,工资;from仓库fulljoin职工on仓库.仓库号=职工.仓库号Join联接格式在联接多个表时的书写方法要特别注意：例如下面是一个基于四个关系的联接查询：select仓库.仓库号,城市,供应商名,地址;from供应商join订购单join职工join仓库;on职工.仓库号=仓库.仓库号;on订购单.职工号=职工.职工号;on供应商.供应商号=订购单.供应商号集合的并(union)运算，可以将两个select语句的查询结果合并成一个查询结果。但必须要求两个查询结果具有相同的字段个数，并且对应字段的值要出自同一个值域。[例5-23]如下语句的结果是城市为北京和上海的仓库信息。select*from仓库where城市=“北京”;union;select*from仓库where城市=“上海”§13集合的并运算(1)只显示前几项记录短语：topnexpr[percent]（必须与orderby连用）[例5-24]显示工资最高的三位职工的信息，即按降序排列，只显示前3条记录。select*top3from职工orderby工资desc[例5-25]显示工资最低的那30%职工的信息，即按升序排序、只显示前30%的记录。select*top30percentfrom职工orderby工资§13vfpsqlselect的几个特殊选项（2）将查询结果存放到数组中短语：intoarrayarrayname[例5-26]如下语句将查询到的职工信息存放到数组tmp中。select*from职工intoarraytmp（3）将查询结果存放在临时文件中短语：intocursorcursorname该短语产生的临时文件是一个只读的DBF文件，当查询结束后该文件是当前文件，可以像一般的表文件一样使用（当然是只读），当关闭文件时该文件自动删除。[例5-27]如下语句将查询到的职工信息存放到临时DBF文件tmp中。select*from职工intocursortmp（4）将查询结果存放到永久表中短语：intodbf|tabletablename[例5-28]将查询结果存放到表highsal中可以使用如下语句。select*from职工intotablehighsal（5）将查询结果存放到文件中短语：tofilefilename[additive]将查询结果放到文本文件中，默认扩展名为.txt，如有additive，将查询结果追加到文件的尾部。[例5-29]如下语句将查询结果以文本的形式存储到文本文件tmp.txt中。select*from职工tofiletmp5.2SQL操作功能SQL的操作功能包括数据的插入、更新和删除三个方面的内容。命令格式：（1）、insertintodbf_name[(字段名1,字段名2,…)]values(表达式1,表达式2,…)（2）、insertintodbf_namefromarrayarrayname|frommemvar§1插入操作[例5-30]以下命令都是往订购单关系中插入一条记录：insertinto订购单values(“E7”,”S4”,”OR01”,{^2006-09-25})insertinto订购单(职工号,订购单号)values(“E7”,”OR01”)[例5-31]假设定义了一数组arr1(5),其中arr1(1)=“E8”,arr1(2)=“S5”,arr1(3)=“OR02”,arr1(4)={^2006-03-12},arr1(5)=4000执行如下命令可以把数组的值插入到表中：insertinto订购单fromarrayarr1注意：VFP要求数组中各元素与表中各字段顺序对应，如果数组中元素的数