第四章关系数据库标准语言SQL(2)

第四章关系数据库标准语言SQL——查询功能（2）制作人：目录上一页下一页退出本节导学复习回顾：Sqlselect语句基本格式简单查询、联接查询、嵌套查询、简单计算查询特点本节任务：分组与计算查询空值（null）查询别名与自联接查询内外层互相关嵌套查询量词与谓词查询超联接查询几个特殊选项小结作业Select……From……基于一个关系的查询，即单表查询基于多个关系的查询，即多表查询。必须有联接条件结果出自一个关系，所涉及的条件出自另一个关系。对表中记录进行统计计算，计算函数：Count()Sum()、Avg()、Max()Min()疑问：有一张学生信息表，现需要分别统计一下各班总人数，我们该怎么办？目录上一页下一页退出GROUPBY短语的格式：GROUPBY字段名1[，字段名2…][HAVING条件表达式]作用：进行分组查询，可以按一列或多列分组，还可以用HAVING进一步限定分组的条件。实例：例1:求每个仓库的职工的平均工资。select仓库号,avg(工资)from职工groupby仓库号例2:求至少有两个职工的每个仓库的平均工资。select仓库号,count(*),avg(工资)from职工groupby仓库号;havingcount(*)=2注：Having子句和Where子句不矛盾，先用Where子句限定元组，然后分组，最后再用Having子句限定分组。一、分组与计算查询目录上一页下一页退出随堂练习：1、检索出所有仓库的平均面积seleavg(面积)from仓库2、检索出每个仓库中工资多于1220元的职工个数Sele仓库号,count(*)from职工;where工资1220groupby仓库号思考：练习2中能否用hanving限定条件？一、分组与计算查询目录上一页下一页退出二、利用空值查询回顾：空值的概念？•“空”值：不同数据类型的“空”值，有不同的规定。•数值型：0即为“空”•字符型：空格、空串等•NULL空值：不是一个确定的值,表示没有值或还没有确定值，所以不能用“=”这样的运算符进行比较例3:找出尚未确定供应商的订购单.select*from订购单where供应商号isnull练习:列出已经确定了供应商的订购单信息.select*from订购单where供应商号isnotnull不能用“=”号目录上一页下一页退出观看以下实例：Sele供应商名from供应商，订购单，职工，仓库；where地址=‘北京’and城市=‘北京’；and供应商.供应商号=订购单.供应商号；and订购单.职工号=职工.职工号；and职工.仓库号=仓库.仓库号三、别名与自联接查询联接条件这个实例中，联接条件有三个，均使用相应的关系名作前缀，显得很麻烦。为此，SQL允许在From短语中为关系名取别名，格式如下：Sele……from关系名别名……上题可改为：Sele供应商名from供应商S，订购单P，职工E，仓库W;where地址=‘北京’and城市=‘北京’;andS.供应商号=P.供应商号andP.职工号=E.职工号;andE.仓库号=W.仓库号目录上一页下一页退出自联接查询自联接：同一关系（表）与其自身进行得联接例：查询雇员关系中上一级经理及其所领导的雇员名单雇员号雇员姓名经理E3赵涌E4钱潮E3E6孙洁E3E8李渌E61:n递归分析：在此关系每一个经理对应（带领）若干雇员，即经理与雇员之间存在递归关系。可把此关系看作两个关系，分别为经理关系S，雇员关系E。目录上一页下一页退出答案：SeleS.雇员姓名,’领导‘,E.雇员姓名from雇员S,雇员E；whereS.雇员号=E.经理结果：赵涌领导钱潮赵涌领导孙洁孙洁领导李渌自联接查询目录上一页下一页退出四、内外层互相关嵌套查询回顾：前面所学此的嵌套查询中外层查询依赖于内层查询的结果，而内层查询与外层查询无关。新知识：外层查询的条件由内层查询的结果提供，内层查询的条件需要外层查询提供值例:在订购单表中加入一个新字段总金额，查询每个职工所经手的具有最高总金额的定购单信息。Sele*from订购单outwhere总金额=；（Selemax(总金额)from订购单inner;whereout.职工号=inner.职工号）解析:外层查询向内层查询提供职工号，内层查询根据此职工号检索出此职工经手的最高总金额，并将此结果与外层查询中的同一元组总金额进行比较，相等选择此元组，反之不选。目录上一页下一页退出五、量词与谓词查询回顾：前面所学的嵌套查询中用到的运算符有：集合运算符——in,notin关系运算符——=，!=，,等新知识：量词any、all、some谓词exists、notexists1）ANY和SOME：功能相同，都表示某个值2）ALL：所有值（任何值）3）一般前面是比较运算符，后面是子查询模块例：检索有职工的工资大于或等于WH1仓库中任何一名职工工资的仓库号Seledist仓库号from职工where工资=any;(sele工资from职工where仓库号=‘WH1’）目录上一页下一页退出1）EXISTS或NOTEXISTS的返回值：.T..F.2）作用：用来检查子查询中是否存在元组，它本身没有任何运算或比较。当筛选条件包括EXISTS时，只要子查询不为空集，筛选的条件就为真。例：检索至少有一个职工的仓库信息Sele*from仓库where仓库号IN;(Sele仓库号from职工）或Sele*from仓库whereexists;(Sele*from职工where仓库号=仓库.仓库号）五、量词与谓词查询目录上一页下一页退出六、超联接查询回顾：简单联接查询中只有满足联接条件的元组才能出现在结果中。超(外)联接查询：首先保证一个表中满足条件的元组都在结果表中，然后将满足联接条件的元组与另一个表的元组进行联接，不满足联接条件的则将来自另一表的属性值置为空（NULL）联接查询语法格式：SELECT……FROM[DatabaseName!]Table[[INNER|LEFT[OUTER]|RIGHT[OUTER]|FULL[OUTER]JOINDatabaseName!]Table[ONJoinCondition…]目录上一页下一页退出说明：1)INNERJOIN(JOIN):普通联接或内部联接2)LEFT[OUTER]JOIN：左联接3)RIGHT[OUTER]JOIN：右联接4)FULL[OUTER]JOIN：全联接5)ONJoinCondition：联接条件例1内部联接：满足联接条件的元组出现在结果表中检索有职工的仓库信息及所包含的职工信息SELE仓库.仓库号，城市，面积，职工号，工资;FROM仓库，职工WHERE仓库.仓库号=职工.仓库号或SELE仓库.仓库号，城市，面积，职工号，工资;FROM仓库JOIN职工；ON仓库.仓库号=职工.仓库号六、超联接查询目录上一页下一页退出例2左联接：除满足联接条件的元组出现在查询结果中以外，第一个表中不满足条件得元组也出现查询结果中。检索所有仓库信息以及所包含的职工信息SELE仓库.仓库号，城市，面积，职工号，工资;FROM仓库LEFTJOIN职工；ON仓库.仓库号=职工.仓库号结果：WH1北京370E31210WH1北京370E71250WH2上海500E11220WH2上海500E41250WH3广州200E61230WH4武汉400NULLNULL六、超联接查询目录上一页下一页退出六、超联接查询例3右联接：除满足联接条件的元组出现在查询结果中以外，第二个表中不满足条件得元组也出现查询结果中。检索所有职工信息以及所在仓库信息SELE仓库.仓库号，城市，面积，职工号，工资;FROM仓库RIGHTJOIN职工；ON仓库.仓库号=职工.仓库号假设职工表中有职工：WH8E81200结果：WH2上海500E11220WH1北京370E31210WH2上海500E41250WH3广州200E61230WH1北京370E71250NULLNULLNULLE81220目录上一页下一页退出六.超联接查询例4全联接：除满足联接条件的元组出现在查询结果中以外，两个表中不满足条件得元组也出现查询结果中。检索所有职工信息以及所有仓库信息SELE仓库.仓库号，城市，面积，职工号，工资;FROM仓库FULLJOIN职工；ON仓库.仓库号=职工.仓库号结果:WH1北京370E31210WH1北京370E71250WH2上海500E11220WH2上海500E41250WH3广州200E61230WH4武汉400NULLNULLNULLNULLNULLE81220目录上一页下一页退出七、集合的并运算•1）命令格式：•SelectcommandUNION[ALL]selectcommand•2）作用：将两个查询的结果连接起来。•3）说明：①默认情况下（无ALL），结果并，排除重复行②有ALL：结果并保留重复行③UNION不用于子查询④两个查询结果列数应相同⑤查询结果中的对应列应类型相同、宽度相等。•例查询选0001或0002号课程的学生的学号•select学号from选课where课程号=‘0001’;union；select学号from选课where课程号=‘0002’目录上一页下一页退出八、SQLSELECT几个特殊选项（1）显示部分结果Topn[percent]说明：n是数字表达式，当不用percent时，n是1至32767间的整数，说明显示前几条记录；当用percent时，n是0.01至0.09间的实数，说明显示结果中前百分之几的纪录。此top短语必须与orderby短语同时使用才有效例1显示工资最高的三位职工的信息Sele*top3from职工orderby工资desc例2显示工资最低的30%职工的信息Sele*top30percentfrom职工orderby工资目录上一页下一页退出(2)保存查询结果默认情况下，sqlselect查询结果输出到浏览窗口中，若想把查询结果存放在别的地方需要如下子句：•INTO子句•1）格式：INTODestination•2）Destination子句的格式•ARRAYarrayname:保存在数组中，此数组为二维数组，依次存放结果表中对应记录。•CURSORtemporaryTable：保存在临时文件中。此文件为只读的dbf文件，查询结束后此临时文件为当前文件，当关闭文件时该文件自动删除。•Table|DBFtablename：保存在永久表（dbf)中八、SQLSELECT几个特殊选项目录上一页下一页退出•TO子句1）格式：TODestination2）Destination子句的格式•FILEFILENAME[ADDITIVE]:保存在文本文件（.TXT)中.若使用ADDITIVE，将结果追加到原文件的尾部，否则将覆盖原有文件。•PRINTER[PROMPT]：将结果输出到打印机。若使用PROMPT，在开始打印之前打开打印机设置对话框八、SQLSELECT几个特殊选项目录上一页下一页退出保存查询结果举例例1检索所有职工信息并将结果存放在数组M中Sele*from职工intoARRAYMM(1,1)存放第一条记录的第一个字段值WH1M(1,2)存放第一条记录的第二个字段值北京M(2,3)存放第二条记录的第三个字段值500例2查询学生表中所有男学生的学号和姓名，将结果写入临时表ST1中。select学号，姓名from学生where性别=‘男’；intoCURSORST1八、SQLSELECT几个特殊选项目录上一页下一页退出保存查询结果举例例3检索工资最高的三位职工信息并将其结果保存在表highsal中Sele*top3from职工orderby工资desc;intotablehighsal例4查询成绩表中高数成绩大于90的学生的学号和姓名，将结果写入文本文件（.txt)ST2中。select学号，姓名from成绩where高