VisualFoxpro6.0程序设计基础SQL查询与多表程序

1、Visual Foxpro 6.0程序设计基础 SQL查询与多表程序要点： 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、REVOKE1、数据定义命令 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数据查询 在数据库中对数据的操作，很多时侯是查询，因此，数据查询是数据库的核心操作。而在S

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

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

6、.grid1.recordsourcetype=4thisform.grid1.recordsource=select distinct; 出版社 from 图书3)带条件的查询 在SELECT语句中，可以通过WHERE子句为查询规定条件WHERE子句的格式：WHERE AND 功能：查询指定表或视图中满足查询条件的记录。其中可以是关系表达式，也可以是逻辑表达式，下表列出了组成常用的运算符。运算符类别运算符实例关系运算符=、=、=、成绩=60逻辑运算符NOT、 AND、 OR成绩=60 AND成绩70字符串匹配运算符LIKE姓名 LIKE “王% 范围运算符BETWEEN AND成绩BETWE

7、EN 60 AND 70空值运算符IS NULL成绩 IS NULL集合运算符IN、NOT IN专业班级 IN (“计算机”,”英语”) LIKE子句中可以用通配符：下划线“_”，表示任何一个字符；百分号符“%”，表示一串字符。【例6-17】查询图书表中书名包含“程序”二字的图书信息。表单Form1的Init事件代码如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=“select * ； from 图书 where 书名 like %程序% 【例6-18】查询图书表中图书的价格在28到30之间的图书。表单Form1的

8、Init事件代码如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=“select * ； from 图书 where 价格 between 28 and 30【例6-19】在借阅表中查询借阅了书号为bj080001、bj060022的读者编号、书号及借阅日期。表单Form1的Init事件代码如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=“select ； 读者编号,书号,借阅日期 from 借阅； where 书号 in(bj08000

9、1,bj060022)” 4)排序查询 通过ORDER BY子句可以实现查询结果的排序输出，允许按一列或多列排序。 格式： ORDER BY ASC|DESC,ASC|DESC 其中，ASC表示升序排序（缺省方式），DESC表示降序排序。【例6-20】按办证日期先后顺序显示读者表中的读者信息。表单Form1的Init事件代码如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=“select * ； from 读者 order by 办证日期【例6-21】在借阅表中按读者编号从大到小的顺序，读者编号相同时按借阅日期的先后

10、顺序显示读者编号、书号及借阅日期。表单Form1的Init事件代码如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=“select ； 读者编号,书号,借阅日期 from 借阅； order by 读者编号 desc,借阅日期 asc【例6-22】显示图书表中数量最多的两种图书信息。表单Form1的Init事件代码如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=“select top 2； * from 图书 order by 数量 de

11、sc使用TOP PERCENT短语显示部分结果。注意：TOP短语要与ORDER BY短语同时使用才有效。 【例】显示价格最低的20%图书的信息。SELECT * TOP 20 PERCENT FROM 图书 ORDER BY 价格 5)带计算函数的查询 在SELECT语句中，使用SQL语言提供的一些查询计算函数，可以增强查询功能。基本的查询计算函数的格式及功能如下表所示。函数的格式函数功能COUNT(*)计算记录条数SUM(字段名)求字段名所指定字段值的总和AVG(字段名)求字段名所指定字段的平均值MAX(字段名)求字段名所指定字段的最大值MIN(字段名)求字段名所指定字段的最小值【例6-23

12、】计算图书表中图书的最高价格和最低价格。表单Form1的Init事件代码如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=“select ； max(价格),min(价格) from 图书【例6-24】统计图书表中图书的总数量。表单Form1的Init事件代码如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=“select ； sum(数量) from 图书【例6-25】计算图书表中图书的平均价格，保存在数组DJ中。表单Form1的Init

13、事件代码如下：select avg(价格) from 图书 into array djthisform.label1.caption=”图书的平均价格； 为:”+ str(dj(1),5,2)+元使用INTO ARRAY 将查询结果存放到数组中。 6)用别名输出列标题（字段名） 将列名用含义更明确的别名输出，便于理解，可以通过AS关键字实现。 格式： AS 功能：将查询输出的列名以“列别名”输出。将【例6-23】中显示结果的列标题改为最高单价和最低单价,运行结果如图所示,其SQL语句改为:select max(价格) as 最高单价,min(价格) as 最低单价 from 图书 7)分组查询

14、 通过GROUP BY子句可以实现分组查询。 格式： GROUP BY , HAVING 其中，可以是表的字段名、字段函数名或标识列的数值型表达式；HAVING 子句进一步限定分组的条件。 在select语句中，where子句是对记录的限定，而Having短语是对分组的限定，where、group by及Having可以同时出现在一个select语句中。三者在select中出现的顺序为：WhereGroup by-Having。记录1记录2记录3记录4记录5记录6记录2记录4记录5记录6记录2记录5记录4记录6记录2记录5where限定Group by分组Having限定【例6-26】计算图书

15、表中每个出版社的图书数量。表单Form1的Init事件代码如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=“ select ； 出版社,sum(数量) as 数量 from 图书; group by 出版社【例6-27】在借阅表中统计每本图书的借阅次数。表单Form1的Init事件代码如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=“ select ； 书号,count(*) as 借阅次数 from 借阅; group by 书号 8

16、)多表联接查询 SELECT语句可以实现对的4种联接类型的查询：内部联接（Inner Join）:只有2个表的字段都满足联接条件时，才将此记录选入查询结果中。左联接(Left Outer Join)：联接条件左边表中的记录都包含在查询结果中，而右边表中的记录只有满足联接条件时，才选入查询结果中。右联接（Right Outer Join）：与左联接正好相反。完全联接（Full Join）：无论2个表中的记录是否满足联接条件，都将全部记录选入查询结果中。掌握该种联接 通常有3种方法实现多表联接查询，一是在查询命令中指定联接类型；二是在查询命令中使用WHERE子句；三是使用嵌套查询。 （1）指定联接

17、类型实现多表间的联接查询 格式：SELECT FROM INNER JOIN JOIN ON ON WHERE 功能：通过指定的联接类型建立多表间的联接 注意：若SELECT后要查询的列名（字段名）在2个表中都有，则必须采用“表名.字段名”，若字段名唯一，则可仅写出字段名。（2）用WHERE子句实现多表间的联接查询：用WHERE子句实现多表间的联接查询时，无须直接指明联接类型（隐含是内部联接），只需把联接条件直接写入WHERE子句即可。（3）使用嵌套查询实现多表间的联接查询：在SQL语言中，由SELECT、FROM、WHERE语句组成一个查询块。嵌套查询就是将第2个查询块放在第1个查询块的WH

18、ERE条件中，形成外层(第1个)查询包含内层(第2个)查询的嵌套查询。 外层查询也称为主查询、父查询，内层查询也称为下层查询、子查询。系统对嵌套查询的处理过程是先做子查询，在子查询的基础上再做主查询。 在子查询中还可以再包含更下层的查询，从而形成层嵌套查询。【例6-28】查询至少被借阅了2次的图书的书号及书名。方法1:表单Form1的Init事件代码如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=“select ; 图书.书号,书名 from 图书,借阅 ; where 图书.书号=借阅.书号 group ； by

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

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

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

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

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

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

25、thisform.label2.caption=请输入要查询的+lx“查询”按钮的Click事件代码如下：xx=alltrim(thisform.text1.value)DO CASE 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 messag

26、ebox(请选择查询类型)ENDCASEthisform.grid1.recordsourcetype=1thisform.grid1.recordsource=cx使用INTO CURSOR 将查询结果存放到临时表中。ex1、有如下表所示的数据表：外币名称外币代码现钞买入价基准价现钞卖出价美元12821.6555827.4500825.9500英镑141171.43001176.50001204.0500欧元15877.5325895.5600886.2686法郎18585.5500600.4888604.6500姓名外币代码持有数量张三121000张三141300张三151500李芳142

27、000李芳181500王林141800王林151200刘剑122000刘剑151200刘剑181500外币表.dbf持有者.dbf（1）计算出刘剑所持有的全部外币相当于人民币的价值数量，写出正确的SQL命令。注意：某种外币相当于人民币数量的计算公式：人民币价值数量= 该种外币的现钞买入价* 该种外币持有数量SELECT SUM(现钞买入价*持有数量) AS ; 人民币价值 FROM 持有者,兑换 ; WHERE 兑换.外币代码=持有者.外币代码 ; AND 姓名=刘剑外币名称外币代码现钞买入价基准价现钞卖出价美元12821.6555827.4500825.9500英镑141171.430011

28、76.50001204.0500欧元15877.5325895.5600886.2686法郎18585.5500600.4888604.6500姓名外币代码持有数量张三121000张三141300张三151500李芳142000李芳181500王林141800王林151200刘剑122000刘剑151200刘剑181500外币表.dbf持有者.dbf（2）将兑换表中，美元和英镑的基准价上调0.05%，写出正确的SQL命令。UPDATE 兑换SET 基准价=基准价*1.05 ; WHERE 外币名称=美元 ; OR外币名称=英镑外币名称外币代码现钞买入价基准价现钞卖出价美元12821.65558

29、27.4500825.9500英镑141171.43001176.50001204.0500欧元15877.5325895.5600886.2686法郎18585.5500600.4888604.6500姓名外币代码持有数量张三121000张三141300张三151500李芳142000李芳181500王林141800王林151200刘剑122000刘剑151200刘剑181500外币表.dbf持有者.dbf（3）删除“持有者”表中所有外币名称为“欧元”的记录，写出正确的SQL命令。DELETE FROM 持有者 WHERE 外币代码 IN; (SELECT 外币代码 FROM 兑换 ; WHERE 外币名称=欧元)外币名称外币代码现钞买入价基准价现钞卖出价美元12821.6555827.4500825.9500英镑141171.43001176.50001204.0500欧元15877.5325895.5600886.2686法郎18585.5500600.4888604.6500姓名外币代码持有数量张三121000张三141300张三151500李芳142000李芳181500王林141800王林151200刘剑122000刘剑151200刘剑181500外币表.dbf持有者.dbf（4）查询持有外币种类在3种以上