10.VFPSQL查询、多表程序.ppt

1、Visual Foxpro 6.0,程序设计基础,要点： 1.掌握SQL查询 2.掌握基于单表、多表的表单程序,SQL查询与多表程序,SQL语言的应用,SQL语言简介,SQL（Structured Query Language）语言是一种十分重要的标准数据库语言。SQL语言至1986年成为国际标准语言以后，各数据库软件开发商纷纷推出SQL的关系数据库管理系统产品或接口软件，许多应用广泛的关系数据库管理系统也都增加了DBMS功能，支持标准SQL语言，从而使未来的数据库环境连接为一个统一的整体。,它不是数据库管理系统，也不是应用软件开发语言，它只能用于对数据库中数据的操作。 SQL语言有两种使用方

2、式，即可以作为自含式语言，在数据库管理系统中独立使用，又可以作为嵌入式语言，嵌入许多高级语言（如C、FORTRAN、COBOL）中使用。,SQL语言的特点 1SQL语言是一种一体化的语言 SQL语言集数据定义、数据操纵、数据查询和数据控制的功能于一体，语言风格统一，可以独立完成数据库的全部操作。,2SQL语言是一种高度非过程化的语言。 它没有必要一步步告诉计算机“如何”去做，而只需要描述清楚用户要“做什么”，SQL语言就可以将要求交给系统，自动完成全部工作。它的大多数语句都是独立执行、与上下文无关。,3语言简洁，易学易用,SQL的命令动词 SQL功能 命令动词 数据查询 SELECT 数据定义

3、 CREATE、DROP、ALTER 数据操纵 INSERT、UPDATE、DELETE 数据控制 GRANT、REVOKE,1、数据定义命令 CREATE TABLE-SQL（建立表结构） ALTER TABLE-SQL（修改表结构） DROP TABLE-SQL（删除表） 删除表的SQL语句： DROP TABLE 表名,（见教科书99页）,2、数据操纵命令 INSERT-SQL（插入数据） DELETE-SQL（删除数据） UPDATE-SQL（更新数据）,（见教科书124、128、129-130页）,3、SQL数据查询 在数据库中对数据的操作，很多时侯是查询，因此，数据查询是数据库的核

4、心操作。而在SQL语言中，查询语言中只有一条查询命令，即SELECT语句。,（见教科书160-176页）,1)基本的SELECT语句 格式： SELECT , FROM 数据库名称！ 功能：从指定表或视图中查询全部记录的指定字段值。,（见教科书166页）,【例6-14】查询显示读者表中所有的读者信息。,表单Form1的Init事件代码如下： thisform.grid1.recordsourcetype=4 thisform.grid1.recordsource=”select * from 读者”,【例6-15】查询显示读者表中所有读者的读者编号、读者姓名、性别，并将结果保存在表文件“dzx

5、x.dbf“中。,表单Form1的Init事件代码如下： select 读者编号,读者姓名,性别 ; from 读者 into table dzxx thisform.grid1.recordsourcetype=1 thisform.grid1.recordsource=dzxx,使用INTO DBF|TABLE 将查询结果存放到永久表中。,2)唯一性查询 唯一性查询指对于要显示的结果，若有重复出现时，则只显示重复行中的一行。这可通过在基本SELECT语句中加入DISTINCT关键字的方法实现。若不加此关键字，则显示全部行。,【例6-16】查询图书表中所有的出版社，相同的出版社只显示一个。,

6、表单Form1的Init事件代码如下： thisform.grid1.recordsourcetype=4 thisform.grid1.recordsource=select distinct; 出版社 from 图书,3)带条件的查询 在SELECT语句中，可以通过WHERE子句为查询规定条件WHERE子句的格式： WHERE AND 功能：查询指定表或视图中满足查询条件的记录。其中可以是关系表达式，也可以是逻辑表达式，下表列出了组成常用的运算符。,LIKE子句中可以用通配符：下划线“_”，表示任何一个字符；百分号符“%”，表示一串字符。,【例6-17】查询图书表中书名包含“程序”二字的图

7、书信息。,表单Form1的Init事件代码如下： thisform.grid1.recordsourcetype=4 thisform.grid1.recordsource=“select * ； from 图书 where 书名 like %程序% ,【例6-18】查询图书表中图书的价格在28到30之间的图书。,表单Form1的Init事件代码如下： thisform.grid1.recordsourcetype=4 thisform.grid1.recordsource=“select * ； from 图书 where 价格 between 28 and 30,【例6-19】在借阅表中查

8、询借阅了书号为bj080001、bj060022的读者编号、书号及借阅日期。,表单Form1的Init事件代码如下： thisform.grid1.recordsourcetype=4 thisform.grid1.recordsource=“select ； 读者编号,书号,借阅日期 from 借阅； where 书号 in(bj080001,bj060022)”,4)排序查询 通过ORDER BY子句可以实现查询结果的排序输出，允许按一列或多列排序。 格式： ORDER BY ASC|DESC,ASC|DESC 其中，ASC表示升序排序（缺省方式），DESC表示降序排序。,【例6-20】按

9、办证日期先后顺序显示读者表中的读者信息。,表单Form1的Init事件代码如下： thisform.grid1.recordsourcetype=4 thisform.grid1.recordsource=“select * ； from 读者 order by 办证日期,【例6-21】在借阅表中按读者编号从大到小的顺序，读者编号相同时按借阅日期的先后顺序显示读者编号、书号及借阅日期。,表单Form1的Init事件代码如下： thisform.grid1.recordsourcetype=4 thisform.grid1.recordsource=“select ； 读者编号,书号,借阅日期

10、from 借阅； order by 读者编号 desc,借阅日期 asc,【例6-22】显示图书表中数量最多的两种图书信息。,表单Form1的Init事件代码如下： thisform.grid1.recordsourcetype=4 thisform.grid1.recordsource=“select top 2； * from 图书 order by 数量 desc,使用TOP PERCENT短语显示部分结果。注意：TOP短语要与ORDER BY短语同时使用才有效。 【例】显示价格最低的20%图书的信息。 SELECT * TOP 20 PERCENT FROM 图书 ORDER BY 价

11、格,5)带计算函数的查询 在SELECT语句中，使用SQL语言提供的一些查询计算函数，可以增强查询功能。基本的查询计算函数的格式及功能如下表所示。,【例6-23】计算图书表中图书的最高价格和最低价格。,表单Form1的Init事件代码如下： thisform.grid1.recordsourcetype=4 thisform.grid1.recordsource=“select ； max(价格),min(价格) from 图书,【例6-24】统计图书表中图书的总数量。,表单Form1的Init事件代码如下： thisform.grid1.recordsourcetype=4 thisform

12、.grid1.recordsource=“select ； sum(数量) from 图书,【例6-25】计算图书表中图书的平均价格，保存在数组DJ中。,表单Form1的Init事件代码如下： select avg(价格) from 图书 into array dj thisform.label1.caption=”图书的平均价格； 为:”+ str(dj(1),5,2)+元,使用INTO ARRAY 将查询结果存放到数组中。,6)用别名输出列标题（字段名） 将列名用含义更明确的别名输出，便于理解，可以通过AS关键字实现。 格式： AS 功能：将查询输出的列名以“列别名”输出。,将【例6-23

13、】中显示结果的列标题改为最高 单价和最低单价,运行结果如图6.38所示,其 SQL语句改为: select max(价格) as 最高单价,min(价格) as 最低单价 from 图书,7)分组查询 通过GROUP BY子句可以实现分组查询。 格式： GROUP BY , HAVING 其中，可以是表的字段名、字段函数名或标识列的数值型表达式；HAVING 子句进一步限定分组的条件。,在select语句中，where子句是对记录的限定，而Having短语是对分组的限定，where、group by及Having可以同时出现在一个select语句中。三者在select中出现的顺序为：Where

14、Group by-Having。,记录1 记录2 记录3 记录4 记录5 记录6,记录2 记录4 记录5 记录6,记录2 记录5 记录4 记录6,记录2 记录5,where,限定,Group by,分组,Having,限定,【例6-26】计算图书表中每个出版社的图书数量。,表单Form1的Init事件代码如下： thisform.grid1.recordsourcetype=4 thisform.grid1.recordsource=“ select ； 出版社,sum(数量) as 数量 from 图书; group by 出版社,【例6-27】在借阅表中统计每本图书的借阅次数。,表单For

15、m1的Init事件代码如下： thisform.grid1.recordsourcetype=4 thisform.grid1.recordsource=“ select ； 书号,count(*) as 借阅次数 from 借阅; group by 书号,8)多表联接查询 SELECT语句可以实现对Visual FoxPro 6.0的4种联接类型的查询： 内部联接（Inner Join）:只有2个表的字段都满足联接条件时，才将此记录选入查询结果中。 左联接(Left Outer Join)：联接条件左边表中的记录都包含在查询结果中，而右边表中的记录只有满足联接条件时，才选入查询结果中。 右联

16、接（Right Outer Join）：与左联接正好相反。 完全联接（Full Join）：无论2个表中的记录是否满足联接条件，都将全部记录选入查询结果中。,掌握该种联接,通常有3种方法实现多表联接查询，一是在查询命令中指定联接类型；二是在查询命令中使用WHERE子句；三是使用嵌套查询。 （1）指定联接类型实现多表间的联接查询 格式：SELECT FROM INNER JOIN JOIN ON ON WHERE 功能：通过指定的联接类型建立多表间的联接 注意：若SELECT后要查询的列名（字段名）在2个表中都有，则必须采用“表名.字段名”，若字段名唯一，则可仅写出字段名。,（2）用WHERE子

17、句实现多表间的联接查询：用WHERE子句实现多表间的联接查询时，无须直接指明联接类型（隐含是内部联接），只需把联接条件直接写入WHERE子句即可。,（3）使用嵌套查询实现多表间的联接查询：在SQL语言中，由SELECT、FROM、WHERE语句组成一个查询块。嵌套查询就是将第2个查询块放在第1个查询块的WHERE条件中，形成外层(第1个)查询包含内层(第2个)查询的嵌套查询。 外层查询也称为主查询、父查询，内层查询也称为下层查询、子查询。系统对嵌套查询的处理过程是先做子查询，在子查询的基础上再做主查询。 在子查询中还可以再包含更下层的查询，从而形成层嵌套查询。,【例6-28】查询至少被借阅了2

18、次的图书的书号及书名。,方法1: 表单Form1的Init事件代码如下： thisform.grid1.recordsourcetype=4 thisform.grid1.recordsource=“select ; 图书.书号,书名 from 图书,借阅 ; where 图书.书号=借阅.书号 group ； by图书.书号 having count(*)=2,select 图书.书号,书名 from 图书 a,借阅 b; where a.书号=b.书号 group ； by a.书号 having count(*)=2,方法2: 表单Form1的Init事件代码如下： thisform.g

19、rid1.recordsourcetype=4 thisform.grid1.recordsource=“select; 图书.书号,书名 from 图书 join 借阅 ; on 图书.书号=借阅.书号 group by; 图书.书号 having count(*)=2,方法3: 表单Form1的Init事件代码如下： thisform.grid1.recordsourcetype=4 thisform.grid1.recordsource=“select 书号,； 书名 from 图书 where 书号 in ; (select 书号 from 借阅 group by ； 书号 havin

20、g count(*)=2),【例6-29】输入图书编号，查询借阅该图书的读者姓名、单位、联系电话。,方法1: “查询”按钮的Click事件代码如下： bh=alltrim(thisform.text1.value) thisform.grid1.recordsourcetype=4 thisform.grid1.recordsource= select ； 读者姓名,单位,联系电话 from 读者,； 借阅 where 读者.读者编号=； 借阅.读者编号 and 书号=bh,方法2: “查询”按钮的Click事件代码如下： bh=alltrim(thisform.text1.value) th

21、isform.grid1.recordsourcetype=4 thisform.grid1.recordsource=“select ； 读者姓名,单位,联系电话 from 读者 ; join 借阅 on 读者.读者编号=； 借阅.读者编号 where 书号=bh,方法3: “查询”按钮的Click事件代码如下： bh=alltrim(thisform.text1.value) thisform.grid1.recordsourcetype=4 thisform.grid1.recordsource=“select ； 读者姓名,单位,联系电话 from 读者 ; where 读者编号 in

22、 ； (select 读者编号 from 借阅 where； 书号=bh),【例6-30】输入读者编号，查询该读者的姓名、单位、借阅的图书的书名及借阅日期。,方法1: “查询”按钮的Click事件代码如下： bh=alltrim(thisform.text1.value) thisform.grid1.recordsourcetype=4 thisform.grid1.recordsource=“select ； 读者姓名,单位,书名,借阅日期 from ； 读者,借阅, 图书 where 读者.读者编号=； 借阅.读者编号 and 借阅.书号=； 图书.书号 and 读者.读者编号=bh,方

23、法2: “查询”按钮的Click事件代码如下： bh=alltrim(thisform.text1.value) thisform.grid1.recordsourcetype=4 thisform.grid1.recordsource= select ； 读者姓名,单位,书名,借阅日期 from ； 读者 join 借阅 join 图书 on ； 借阅.书号=图书.书号 on ； 读者.读者编号=借阅.读者编号 ； where 读者.读者编号=bh,【例6-10】按图书编号查询图书信息。,“查询”按钮的Click事件代码如下： bh=alltrim(thisform.text1.value)

24、 thisform.grid1.recordsourcetype=4 thisform.grid1.recordsource=select * from 图书 where 书号=bh,【例6-11】以多种方式查询读者信息，若有多条记录，按性别降序排列。,表单Form1的Load事件代码如下： public lx 组合框Combo1的InteractiveChange事件代码如下： lx=bo1.value thisform.label2.caption=请输入要查询的+lx,“查询”按钮的Click事件代码如下： xx=alltrim(thisform.text1.value) DO CASE

25、 CASE lx=读者编号 select * from 读者 where 读者编号=xx into cursor cx CASE lx=读者姓名 select * from 读者 where 读者姓名=xx into cursor cx CASE lx=性别 select * from 读者 where 性别=xx order by 性别 desc； into cursor cx OTHERWISE messagebox(请选择查询类型) ENDCASE thisform.grid1.recordsourcetype=1 thisform.grid1.recordsource=cx,使用INT

26、O CURSOR 将查询结果存放到临时表中。,ex1、有如下表所示的数据表：,外币表.dbf,持有者.dbf,（1）计算出刘剑所持有的全部外币相当于人民币 的价值数量，写出正确的SQL命令。 注意：某种外币相当于人民币数量的计算公式： 人民币价值数量= 该种外币的现钞买入价* 该种外币持有数量,SELECT SUM(现钞买入价*持有数量) AS ; 人民币价值 FROM 持有者,兑换 ; WHERE 兑换.外币代码=持有者.外币代码 ; AND 姓名=刘剑,外币表.dbf,持有者.dbf,（2）将兑换表中，美元和英镑的基准价上调0.05%， 写出正确的SQL命令。,UPDATE 兑换SET 基

27、准价=基准价*1.05 ; WHERE 外币名称=美元 ; OR外币名称=英镑,外币表.dbf,持有者.dbf,（3）删除“持有者”表中所有外币名称为“欧元”的记录， 写出正确的SQL命令。,DELETE FROM 持有者 WHERE 外币代码 IN; (SELECT 外币代码 FROM 兑换 ; WHERE 外币名称=欧元),外币表.dbf,持有者.dbf,（4）查询持有外币种类在3种以上人员的姓名，及持有 的种类数量，并按种类数量升序排列，数量相同时，则 按姓名降序排列，写出正确的SQL命令。,SELECT 姓名,COUNT(*) AS 外币种类 FROM ; 持有者 GROUP BY 姓

28、名 HAVING ; COUNT(*)=3 ORDER BY ; 外币种类 ,姓名 DESC,ex2：设有两个数据表：职工表ZGB（工号C,姓名C）；奖金表JJB（工号C，基本工资N，奖金N），请将下面的SQL语句补充完整。 （1）查询基本工资在500以下的职工姓名。 SELECT 姓名 FROM ZGB WHERE 工号 IN； （SELECT FORM JJB WHERE ） （2）在JJB表中，只显示奖金最高的前3条记录。 SELECT * 3 FROM JJB ORDER BY 奖金 DESC,工号 基本工资500 TOP,ex3：设有学生选课表SC（学号，课程号，成绩），用SQL语言

29、检索每门课程的课程号及平均分的语句是： select 课程号, (1) from sc (2),（1）avg（成绩） （2）group by 课程号,ex4：商品数据库中含有两个表：商品表和销售表，结构如下： 商品：商品编号 C(6)，商品名称 C(20)，销售价 N(12，2) 销售：流水号 C(6)，销售日期 D，商品编号 C(6)，销售量 N(8,2) 用SQL命令实现查询2007年10月20日所销售的各种商品的名称、销售量和销售总额，并按销售量从小到大排序的语句是： SELECT 商品名称, sum(销售量)，SUM（ ）AS 销售总额 FROM 商品，销售; WHERE 商品.商品编号=销售.商品编号 销售日期=2007-10-20 GR