实验四 SQL结构查询语句的运用.doc

实验四 SQL结构查询语句的运用一、 实验目的：1 熟悉SQL的特点2 熟悉使用SELECT语句的格式及各参数选项3 熟练掌握简单查询4 熟练掌握连接查询5 熟练掌握嵌套查询二、实验内容及步骤（一）SELECTSQL命令格式SELECT ALL | DISTINCT . AS ,.AS FROM FORCE ! INNER | LEFT OUTER | RIGHTOUTER | FULLOUTER JOIN ! ON INTO | TO FILE ADDITIVE | TO PRINT PROMPT | TO SCREEN PREFERENCE NOCONSOLEPLAINNOWAITWHERE AND AND | OR AND | OR GROUP BY ,组表达式HAVINGUNION ALL ORDER BY ASC | DESC,ASC | DESCTOP PERCENT说明:（1）ALL表示查询出的记录中包括重复记录,这是缺省值;DISTINCT则表示查询出的记录中不包括重复记录。（2） . AS ：可以是字段名，也可以包括用户自定义函数。是字段所在的表名，用于指定输出时使用的列标题，可以不同于字段名。（3）SELECT 表达式可用一个*号来表示，此时指定所有的字段。（4）FROM 子名及其选项：用于指定查询的表与联接类型。（5）FORCE：严格按照指定的联接条件来联接表，避免VFP因进行联接优化而降低查询速度。（6） ! ：对于非当前数据库，用“ ! ”来指定该数据库中的表。是表的暂用名，取了本地名后，本命令中该表只可使用这个名字。（7）INNER | LEFT OUTER | RIGHTOUTER | FULLOUTER：指定两表联接时的联接类型，联接类型一共有四种。联接类型意义Inner Join(内部联接)只有满足联接条件的记录包含在结果中Left Outer Join(左联接)左表某记录与右表所有记录比较字段值，若有满足联接条件的，则产生一个真实值记录；若都不满足，则产生一个含.NULL.值的记录。直至左表所有记录都比较完。Right Outer Join(右联接)右表某记录与左表所有记录比较字段值，若有满足联接条件的，则产生一个真实值记录；若都不满足，则产生一个含.NULL.值的记录。直至右表所有记录都比较完。Full Join(完全联接)先按右联接比较字段值，再按左联接比较字段值。不列入重复记录。其中OUTER表示外部联接，既允许满足联接条件的记录，又允许不满足联接条件的记录。（8）JOIN：用于联接其左右两个所指的表。（9）INTO ：用于指定查询结果的输出去向，默认查询结果显示在浏览窗口中。目标有3种选项。目标输出形式ARRAY 查询结果输出到数组CURSOR查询结果输出到临时表DBF查询结果输出到表（10）TO FILE ADDITIVE：输出到指定的文本文件，并取代原文件内容。ADDITIVE表示只添加新数据，不清除原文件的内容。（11） TO PRINT PROMPT：输出到打印机。PROMPT表示打印前先显示打印确认框。（12）TO SCREEN ：输出到屏幕。（13）PREFERENCE：用于记载浏览窗口听配置参数，再次使用该子句时可用引用此配置。（14）NOCONSOLE：禁止将输出送往屏幕。（15）PLAIN：输出时省略字段名。（16）NOWAIT：显示浏览窗口后程序继续往下执行。（17）WHERE：若已用ON子句指定了联接条件，WHERE子句中只能指定筛选条件，表示在已按联接条件产生的记录中筛选记录。也可以省去JOIN子名，一次性地在WHERE子句中指定联接条件和筛选条件。（18）GROUP BY：对记录按值分组，常用于分组统计。（19）HAVING：当含有GROUP BY 子句时，HAVING 子句可用作记录查询的限制条件；无GROUP BY子句时HAVING子句的用用如同WHERE子句。（20）UNION：在SELECT-SQL命令中可以用UNION子句嵌入另一个SELECT-SQL命令，使这两个命令的查询结果合并输出，但输出字段的类型和宽度必须一致。（21）ORDER BY：指定查询结果中记录按排序，默认升序。只可以是字段，或表示查询结果中列的位置的数字。选项ASC表示升序，DESC表示降序。（22）TOP PERCENT：TOP必须与ORDER BY 同时使用。表示在符合条件的记录中选取的记录数，范围132767，排序后并列的若干记录只计一个。含PERCENT选项时，表示百分比，记录数为小数时自动取整。（23）表达式是由如下运符所组成的。查询条件符号算术运算符=，,=,!=,逻辑运算符AND,OR,NOT集合成员判断IN关系运算UNION,INTERSECT,EXCEPT量词EXISTS,ALL,SOME,UNIQUE库函数AVG,MIN,MAX,SUM,COUNT字符匹配LIKE（二）查询的具体使用：（举例为上一节所建的订货管理数据库中的表）表结构及之间的联系如下如示：仓库仓库号城市面积订购单职工号供应商号订购单号订购日期仓库号职工号工资职工供应商号供应商名地址供应商1 简单查询 （单表查询）（1）无条件查询：例1： 从仓库表中查询所的有城市。Select 城市 from 仓库例2：查询订购单表中的所有记录。Select * from 订购单例3：从职工表中查询所有的工资值，并将显示出的字段更名为实发工资。Select 工资 as 实发工资 from 职工（2）有条件查询例1：查询哪些仓库有工资多于1230元的职工。Select distinct 仓库号 from 职工 where 工资1230例2：查询在北京的供应名称。SELECT 供应商名 FROM 供应商 WHERE 地址=北京例3：查询在仓库”WH1” 或”WH2”工作的，并且工资少于1250元的职工号。Select 职工号 from 职工 where 工资1230) and (职工.仓库号=仓库.仓库号)例2：查询向S4供应商发出订购单的仓库所在的城市。SELECT DIST 城市 FROM 仓库,职工,订购单 WHERE 供应商号=S4 AND 订购单.职工号=职工.职工号 AND 职工.仓库号=仓库.仓库号例3：查询工作在面积大于400的仓库的职工号以及他们所在的城市。Select 职工号,城市 from 仓库,职工 where (面积400) and (职工.仓库号=仓库.仓库号)3.嵌套查询（多表）例1：查询哪些城市至少有一个仓库的职工工资为1250元。Select 城市 from 仓库 where 仓库号 in (select 仓库号 from 职工 where 工资=1250)例2:查询向供应商S3发出过订购单的职工的职工号。SELECT 职工号,仓库号 FROM 职工 WHERE 职工号 IN (SELE 职工号 FROM 订购单 WHERE 供应商号=S3)例3：查询与职工E4挣同样工资的所有职工。Select 职工号 from 职工 where 工资=(select 工资 from 职工 where 职工号=E4)4.几个特殊的运算符例1：查询工资在1220元到1240元范围内的职工信息。Select * from 职工 where 工资 between 1220 and 1240例2：从供应商关系中查询出供应商名为公司的全部信息。Select * from 供应商 where 供应商名 like %公司例3：查询地址不在北京的全部供应商信息。Select * from 供应商 where 地址!=北京5排序例1：按职工工资升序查询全部职工信息。Select * from 职工 order by 工资例2：查询全部职工信息，先按仓库号排序，仓库号相同的，再按工资降序排列。Select * from 职工 order by 仓库号,工资 desc6.简单的计算查询例1：从供应商关系中，查询供应商所在地的数目。Select count(*) from 供应商例2：查询出所有仓库的平均面积。SELECT AVG(面积) FROM 仓库例3： 查询北京和上海的仓库职工的工资总和。Select sum(工资) from 职工 where 仓库号 in (select 仓库号 from 仓库 where 城市=北京 or 城市=上海)例4：查询在WH2仓库工作的职工的最高工资值。Select max(工资) from 职工 where 仓库号=WH2例5：查询在WH2仓库工作的职工的最低工资值。Select min(工资) from 职工 where 仓库号=WH27.分组与计算查询例1：查询每个仓库的职工的平均工资。Select 仓库号,avg(工资) from 职工 group by 仓库号例2：查询每个仓库中工资多于1220元的职工所在的仓库号及职工个数。SELECT 仓库号,COUNT(*) FROM 职工 WHERE 工资1220 GROUP BY 仓库号例3：查询至少有两个职工的每个仓库的平均工资。Select 仓库号,count(*),avg(工资) from 职工 group by 仓库号 having count(*)=28.利用空值查询例1：查询尚未确定供应商的订购单。Select * from 订购单 where 供应商号 is null例2：查询已确定供应商的订购单。Select * from 订购单 where 供应商号 is not null9.别名与内外层相关嵌套查询例1：查询地址与城市都在北京的供应商名。Select 供应商名 from 供应商 S，订购单 P，职工 E，仓库 W where 地址=北京And 城市=北京and S.供应商号=P.供应商号 and P.职工号=E.职工号 and E.仓库号=W.仓库号例2：查询工资低于本仓库平均工资的职工信息。SELECT * FROM 职工 A WHERE 工资=any (select 工资 from 职工 where 仓库号=WH1)例3：查询职工工资大于或等WH1仓库中所有职工工资的仓库号。Select distinct 仓库号 from 职工 where 工资=all (select 工资 from 职工 where 仓库号=WH1)11.超联接查询例1：用内部联接查询出仓库号，城市，面积，职工号，工资信息。Select 仓库.仓库号,城市,面积,职工号,工资 from 仓库 join 职工 on 仓库.仓库号=职工.仓库号例2：用左联接查询出仓库号，城市，面积，职工号，工资信息。Select 仓库.仓库号,城市,面积,职工号,工资 from 仓库 left join 职工 on 仓库.仓库号=职工.仓库号例3：用右联接查询出仓库号，城市，面积，职工号，工资信息。Select 仓库.仓库号,城市,面积,职工号,工资 from 仓库 right join 职工 on 仓库.仓库号=职工.仓库例4：用全联接查询出仓库号，城市，面积，职工号，工资信息。Select 仓库.仓库号,城市,面积,职工号,工资 from 仓库 full join 职工 on 仓库.仓库号=职工.仓库号12.集合的并运算例1：查询城市为北京和上海的仓库信息。Select * from 仓库 where 城市=北京 union Select * from 仓库 where 城市=上海13SQL-SELECTK 中的几个特殊选项例1：查询工资最高的三位职工的信息。Select * top 3 from 职工 order by 工资 desc例2：查询工资最低的那30%的职工信息。Select * top 30 percent from 职工 orde