VFP第8部分(SQL查询语句).ppt

1、Visual Foxpro 6.0,程序设计基础,重庆工商大学计算机学院计算机基础教研室,要点： 1.掌握SQL （Structured Query Language）查询 2.掌握基于单表、多表的表单程序,第十二讲 SQL查询及表单程序,SQL语言的特点 1.SQL语言是一种一体化的语言 SQL语言集数据定义（DDL）、数据操纵（DML）、数据管理（DCL）的功能于一体，可以独立完成数据库的全部操作，包括定义关系模式、录入数据及建立数据库、查询、更新、维护数据、数据库的重新构造、数据库安全性等一系列操作的要求。 2.SQL语言是一种高度非过程化的语言。每个语句的操作对象是一个或多个关系，同时

2、结果也往往是一个关系。 3.语言简洁，易学易用，只用了9个动词：CREATE、DROP、ALTER、SELECT、INSERT、UPDATE、DELETE、GRANT 和REVOKE。 易学易用 4.以同一种语法结构提供两种使用方式,既是自含式语言，又是嵌入式语言。,1、数据定义命令 CREATE TABLE-SQL（建立表结构） ALTER TABLE-SQL（修改表结构） DROP TABLE-SQL（删除表） 删除表的SQL语句： DROP TABLE 表名,（见教科书99页）,【例】创建一个表STUD（学生信息表），它由以下字段组成：学号 （C，7）；姓名（C，8）；性别（C，2）；出

3、生日期 （D）；籍贯（C，4）；系别代号（C，2）；家庭地址（C，50）；是否团员 （L）；备注 （M）。 CREATE TABLE STUD(学号 C(10), 姓名 C(10), 性别 C(2), 出生日期 D, 籍贯 C(10), 系别代号 C(2), 家庭地址 C(50), 是否团员 L, 备注 M) 参考实验指导书P141 注意：primary key 、check、error、foreign key、tag、references等短语的用法和含义。,建立表结构,【格式】ALTER TABLE ,修改表结构,ALTER TABLE SC,ADD 成绩 N(3 ),alter 成绩 N

4、(4),drop 成绩,Rename 成绩 to 考试成绩,方法一： 打开表设计器后修改（数据表已经打开）,方法二： SQL命令（数据表关闭）,【例】 把表dab.dbf中的学号字段宽度改为10，增加字段入学总分N(5,1)。 alter table dab alter 学号 c(10) add 入学总分 n(5,1),2、数据操纵命令 INSERT-SQL（插入数据） DELETE-SQL（删除数据） UPDATE-SQL（更新数据）,（见教科书124、128、129-130页）,【格式1】INSERT INTO VALUES （） 【格式2】INSERT INTO FROM ARRAY |

5、 FROM MEMVAR 【功能】在指定的表文件末尾追加一条记录。格式1用表达式表中的各表达式值赋值给中的相应的各字段。格式2用数组或内存变量的值赋值给表文件中各字段。 【说明】如果某些字段名在INTO子句中没有出现，则新记录在这些字段名上将取空值（或默认值）。但必须注意的是，在表定义说明了NOT NULL的字段名不能取空值。,插入记录,：指定表文件中的字段，缺省时，按表文件字段的顺序依次赋值。 ：指定要追加的记录各个字段的值。 【例 】在表文件STUD的末尾追加三条记录。 *用表达式方式追加第一条记录* INSERT INTO STUD(学号,姓名,性别,出生日期,籍贯,家庭地址,是否团员)

6、 VALUES (2051114,李建国,男, 2002-09-28,四川,.t.),值的类型，顺序，个数要和上面的字段一致,*用表达式方式追加第二条记录* INSERT INTO STUD VALUES (2051129,刘丽敏,女,2001-09-28,吉林,0530,吉林省长春市解放大路10号,.t.,),值的类型，顺序，要和表中的字段一致,DIMENSION DATA5 DATA(1)=2051113 DATA(2)=李宁 DATA(3)=女 DATA(4)=2005-06-25 DATA(5)=江西 INSERT INTO STUD FROM ARRAY DATA,*用数组方式追加第

7、三条记录*,删除记录,【格式】DELETE FROM WHERE 【功能】从指定的表中删除满足WHERE子句条件的所有记录。如果在DELETE语句中没有WHERE子句，则该表中的所有记录都将被删除。 【说明】这里的删除是逻辑删除，即在删除的记录前加上一个删除标记“*”。 【例】删除STUD表中所有性别为男的记录。 DELETE FROM STUD WHERE 性别=“男”,【格式】UPDATE SET = ，= WHERE 【功能】更新指定表文件中满足WHERE条件子句的数据。其中SET子句用于指定列和修改的值，WHERE用于指定更新的行，如果省略WHERE子句，则表示表中所有行。 【说明】更

8、新操作又称为修改操作。,更新记录命令,Update sale set 超额业绩量= 一季度+二季度+三季度+四季度-指标,例：计算超额业绩量,默认更新全部记录,例：计算xscj.dbf的总成绩和平均成绩,update xscj set 总成绩=英语+计算机+数学，平均成绩=总成绩/3,3、SQL数据查询 在数据库中对数据的操作，很多时侯是查询，因此，数据查询是数据库的核心操作。而在SQL语言中，查询语言中只有一条查询命令，即SELECT语句。,基本查询语句 【格式】SELECT * | distinct FROM 【功能】无条件查询。 【说明】ALL：表示显示全部查询记录，包括重复记录。 DI

9、STINCT：表示显示无重复结果的记录。 【例】显示图书表中的所有记录。 SELECT * FROM 图书 命令中的*表示输出显示所有的字段，数据来源是图书表，表中的内容以浏览方式显示。 【例】显示图书表中的所有的书号及与之对应的作者，同时能去除重名。 SELECT DISTINCT 书号,作者 FROM 图书,注意:SELECT 命令中的选项，不仅可以是字段名，还可以是表达式，也可以是一些函数，SELECT命令可操纵的函数很多。 【例】 将所有的图书表价格四舍五入到个位，只显示书号、作者和价格。 SELECT 书号,作者,ROUND(价格,0) AS 零售价; FROM 图书 注意：这个结果

10、不影响数据库表中的结果，只是在输出时通过函数计算输出。 【例】 求出所有图书的价格平均值。 SELECT AVG(价格) AS 平均价 FROM 图书 由以上两例可见，直接使用Visual FoxPro提供的各种SQL函数在输出时进行计算，便可得到相应的输出结果。,带条件（WHERE）的查询语句,【格式】SELECT ALL | DISTINCT FROM WHERE 【功能】从一个表中查询满足条件的数据。 【说明】由一系列用AND 或 OR 连接的条件表达式组成，条件表达式的格式可以是以下几种： SQL支持的关系运算符如下： 、！、,几个特殊关系表达式的意义和使用方法: （1）。 （2）。

11、（3）ALL（） （4） ANY | SOME （） （5） NOT BETWEEN AND （6）NOT EXISTS （） （7） NOT IN （8） NOT IN （） （9） NOT LIKE ,LIKE子句中可以用通配符：下划线“_”，表示任何一个字符；百分号符“%”，表示一串字符。,【例】 列出非西南大学出版社的图书名单。 SELECT * FROM 图书 ; WHERE 出版社西南大学出版社 以上命令的功能等同于： SELECT * FROM 图书 ; WHERE 出版社 != 西南大学出版社 或 SELECT * FROM 图书 ; WHERE NOT( 出版社 = 西南大学

12、出版社) 【例】 列出西南大学出版社和清华大学出版社的图书名单。 SELECT * FROM 图书； WHERE 出版社 IN(西南大学出版社,清华大学出版社) 以上命令的功能等同于： SELECT * FROM 图书； WHERE 出版社=西南大学出版社 OR 出版社=清华大学出版社 注意:IN和( )之间没有空格,【例】 列出价格在30到100之间的图书书号，作者，价格。 SELECT 书号,作者,价格 FROM 图书; WHERE 价格=30 AND 价格=100 在以上命令的功能等同于： SELECT 书号,作者,价格 FROM 图书; WHERE 价格 BETWEEN 30 AND

13、100 【例】列出所有的姓王的读者名单。 SELECT * FROM 读者 WHERE 读者姓名 LIKE 王% 以上命令的功能等同于： SELECT * FROM 读者 WHERE 读者姓名=王,【例】 列出所有还书日期为空值的读者编号和书号。 SELECT 读者编号,书号 FROM 借阅 WHERE 还书日期 IS NULL 在以上命令中，使用了运算符IS NULL，该运算符是测试字段值是否为空值，在查询时用“字段名IS NOTNULL”的形式，而不能写成“字段名=NULL”或“字段名！=NULL”,【练习】显示读者表中所有男生记录的读者编号，姓名和性别字段值。 SELECT 读者编号,读

14、者姓名,性别 FROM 读者 ； WHERE 性别=男“ 【练习】显示读者表中办证日期在09年的读者的编号，姓名，办证日期。 SELECT 读者编号,读者姓名,办证日期 FROM 读者 ； WHERE 办证日期 BETWEEN 2009-01-01 AND 2009-12-31,【练习】显示读者表中姓李的读者的编号，姓名，单位。 SELECT 读者编号,读者姓名,单位 ； FROM 读者； WHERE 读者姓名 LIKE 李% 【练习】显示读者表中女读者的编号，姓名，联系电话。将“联系电话”字段的标题显示为“座机” SELECT 读者编号,读者姓名,联系电话 AS 座机 FROM 读者 WHE

15、RE 性别=女,显示部分查询结果,TOP nExpr:显示输出前nExpr个记录 TOP nExpr PERCENT :显示输出前百分之nExpr个记录 【例】显示图书表中价格前5个记录。 SELE * TOP 5 FROM 图书 ORDER BY 价格 【例】显示图书表中价格前50%的记录。 SELE * TOP 50 PERCENT FROM 图书 ORDER BY 价格,1.排序输出(ORDER BY ) 【例】按办证年份降序,如果办证年份相同，再按读者编号升序显示读者表中的读者编号，姓名，办证年份。 SELECT 读者编号,读者姓名, year(办证日期) AS 办证年份; FROM

16、读者; ORDER BY 办证年份 DESC,读者编号 ASC,查询结果处理,注意：TOP短语要与ORDER BY短语同时使用才有效。,注意：order by 后面不能将函数作为排序字段，需对这样的字段要起别名，再对别名排序,或直接用该函数在显示信息的位数，如3,2.重定向输出(INTO) 默认情况下，查询输出到一个浏览窗口，用户在“SELECT”语句中可使用INTO|TO FILE|TO SCREEN| TO PRINTER子句选择查询去向： 1)INTO ARRAY 数组名：将查询结果保存到一个数组中。 SELE * TOP 2 FROM 读者 ORDER BY 办证日期 into arr

17、ay a display memor like a 注意：数组是一个二维数组，并且一行保存一个记录，一列对应一个属性。 2)INTO CURSOR ： 将查询结果保存到一个临时表中。 SELE * TOP 2 FROM 读者; ORDER BY 读者编号 into CURSOR A 3)TO SCREEN：将查询结果保在屏幕上显示。 SELE * TOP 2 FROM 读者 ORDER BY 读者编号 to screen,4)TO PRINTER：将查询结果送打印机打印。 SELE * TOP 2 FROM 读者 ORDER BY 读者编号 ; to PRINT 5)TO FILEADDITI

18、VE：将查询结果保存到文本文件中。如果带“ADDITIVE”关键字，查询结果以追加方式添加到指定的文件，否则，以新建或覆盖方式添加到指定的文件。 SELE * TOP 2 FROM 读者 ORDER BY 读者编号; to FILE AA 6)TO DBF|TABLE TABLENAME存入永久表中。 SELE * TOP 2 FROM 读者 ORDER BY 读者编号; INTO DBF AA,【例】显示数量大于100和姓李的作者的信息。SELE 作者,价格,数量 FROM 图书 WHERE 作者 LIKE 王% ； UNION ； SELE 作者,价格,数量 FROM 图书 WHERE 数

19、量100 注意:两个查询的字段名和字段数要一致。,输出合并(并运算),COUNT 统计 SUM求和 AVG计算平均值 MAX求最大值 MIN求最小值,计算查询,【例】查询统计读者表中女生的个数 SELECT COUNT(*) FROM 读者 WHERE 性别=女 【例】计算图书表中的图书数量总数。 SELECT SUM(数量) FROM 图书 【例】计算图书表中的价格最大的数。 SELECT MAX(价格) FROM 图书 【例】计算图书表中的价格的平均数。 SELECT AVG(价格) FROM 图书,分组统计(GROUP BY )与筛选(HAVING) 通过GROUP BY子句可以实现分组

20、查询。 格式： 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 记录5,where,限定,Group by,分组,Having,限定,【例】计算图书

21、表中每个出版社的图书数量。 select 出版社,sum(数量) as 数量; from 图书 group by 出版社 【例】显示图书表中各出版社的图书平均价格。 sele 出版社,AVG(价格) ; from 图书 group by 出版社,【例】在借阅表中统计每本图书的借阅次数。 select 书号,count(*) as 借阅次数; from 借阅 group by 书号 【例】在借阅表中统计借阅次数大于2次的图书。 SELE 书号, COUNT(*) FROM 借阅 ; GROUP BY 书号 HAVING COUNT(*)=2,【例】写出实现下列题目要求的SQL命令。 1、查询出X

22、SCJ表中的学号，姓名，英语字段的相关信息，英语按照70%显示，并以别名ENGLISH显示该字段。,SELECT 学号,姓名,英语*0.7 AS ENGLISH FROM XSCJ,课堂作业（一）,4、将XSCJ表前4名的学号，姓名，英语字段的信息显示在屏幕上，并按照总成绩排序。,SELECT TOP 4 学号,姓名,英语 FROM XSCJ ； TO SCREEN order by 总成绩,2、查询出XSCJ表中的平均成绩大于60分的同学学号，姓名及平均成绩信息。,SELECT 学号,姓名,数学 FROM XSCJ ORDER BY 数学 DESC,SELECT 学号,姓名,英语+数学 AS

23、 总分 FROM XSCJ,3、显示XSCJ表的学号，姓名，数学字段的信息，并按照数学成绩降序排序。,7、将XSCJ表中姓王的同学的信息保存到数组A中。然后在屏幕上显示数组元素A(2),A(7)的信息。,SELECT * FROM XSCJ ； where 姓名= 王 INTO ARRAY A ? A(2),A(7),5、查询出XSCJ表中的姓名中带有“芳”字的同学学号，姓名及总成绩信息。,SELECT 学号,姓名,平均成绩 FROM XSCJ where 平均成绩 BETWEEN 60 and 80 ORDER BY 平均成绩 DESC,SELECT 学号,姓名, 总成绩 FROM XSCJ

24、 where 芳$姓名,6、显示XSCJ表的平均成绩在60到80分之间的同学学号，姓名，平均分的信息，并按照平均成绩降序排序。,10、将XSCJ表中各班级的英语平均成绩，并将该信息保存到新表temp中。,SELECT avg(英语) as 英语平均成绩 from xscj group by 班级号 into table temp,8、查询出XSCJ表中的所有同学的数学成绩的总和，并将该值保存到数组B中。,SELECT 学号,姓名,round(英语,0) AS 英语 FROM XSCJ,SELECT SUM(数学) FROM XSCJ INTO ARRAY B,9、查询出XSCJ表中的学号，姓名

25、及英语字段的信息，要求将英语成绩保留到个位。,13、查询出XSCJ表中的各班数学成绩最高分，再将它们升序排序。,SELECT max(数学) from xscj group by 班级号 order by 1,11、查询出XSCJ表中的各班英语成绩最高分，将该信息保存到数组C中，然后显示它们的值。,SELECT count(*) from xscj group by 班级号 into Array D,SELECT max(英语) from xscj group by 班级号 into Array C ?C(1),C(2),12、查询出XSCJ表中的各班人数，将该信息保存到数组D中.,多表查询之

26、一等值联接 在一个表中进行查询，一般说来是比较简单的，而在多表之间查询就比较复杂，必须处理表和表间的联接关系。使用SELECT命令进行多表查询是很方便的。 1. 等值联接 等值联接是用where短语按对应字段的共同值将一个表中的记录与另一个表中的记录相联接。 【例】 输出所有读者的姓名，借书书号，借阅日期。 SELECT a.读者姓名,b.书号,b.借阅日期; FROM 读者 a, 借阅 b; WHERE a. 读者编号=b.读者编号,【例】列出男生的借书情况，要求列出书名、读者姓名、借阅日期。 SELECT a.读者姓名,c.书名,b.借阅日期; FROM 读者 a, 借阅 b, 图书 c

27、; WHERE a.读者编号=b.读者编号 and b.书号=c.书号; and a.性别=男,在SQL语句中，一个SELECTFROMWHERE语句称为一个查询块。将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询或子查询。 【例】显示借阅过书号为”bj060022”的读者姓名，单位，联系电话。(使用借阅,读者表) SELECT 读者姓名,单位，联系电话; FROM 读者; WHERE 读者编号 IN (SELECT 读者编号 FROM 借阅 WHERE 书号=“bj060022” ),多表查询之二嵌套查询,借阅过书号为”bj060022”的读者姓名，

28、单位，联系电话,1. 返回单值的子查询 【例】列出张珊珊的所借书的书号，借阅日期。 SELECT 书号,借阅日期 FROM 借阅 WHERE 读者编号=; (SELECT 读者编号 FROM 读者 WHERE 读者姓名=张珊珊),上述SQL语句执行的是两个过程，首先在读者表中找出“张珊珊”的读者编号（比如“d0001”），然后再在借阅表中找出读者编号等于“d0001”的记录，列出这些记录的书号，借阅日期。,2. 返回一组值的子查询 若某个子查询返回值不止一个，则必须指明在WHERE子句中应怎样使用这些返回值。通常使用条件ANY（或SOME）、ALL和IN。 （1）ANY运算符的用法 【例】列出

29、 姓“王” 的作者图书价格高的图书名字,价格。 SELECT 书名,价格 FROM 图书 WHERE 价格ANY； (SELECT 价格 FROM 图书 WHERE 作者=王) 该查询须做两件事：先找出姓“王” 的作者图书价格（比如说结果为26.9，31.6，33.0），然后再选出价格高于任何一个王姓作者图书价格（即高于26.9就可以）的图书。 相当于WHERE 价格26.9 or 价格31.6 or 价格33.0,（2）ALL运算符的用法 【例】 列出比所有姓“王” 的作者图书价格都高的图书名字,价格。 SELECT 书名,价格 FROM 图书 WHERE 价格ALL； (SELECT 价格

30、 FROM 图书 WHERE 作者=王) 该查询必须做两件事：先找出姓“王” 的作者图书价格（比如说结果为26.9，31.6，33.0），然后再选出价格高于所有王姓作者图书价格（即高于33.0才可以）的图书。 相当于WHERE 价格26.9 and 价格31.6 and 价格33.0,（3）IN运算符的用法 【例】列出 姓“王” 的作者图书价格高的图书名字,价格。 SELECT 书名,价格 FROM 图书 WHERE 价格 in ； (SELECT 价格 FROM 图书 WHERE 作者=王) IN是属于的意思，等价于“=ANY”，即等于子查询中任何一个值.,多表查询之三联接查询 SELECT

31、语句可以实现对Visual FoxPro 6.0的4种联接类型的查询： 内部联接（Inner Join）:只有2个表的字段都满足联接条件时，才将此记录选入查询结果中。 左联接(Left Outer Join)：联接条件左边表中的记录都包含在查询结果中，而右边表中的记录只有满足联接条件时，才选入查询结果中。 右联接（Right Outer Join）：与左联接正好相反。 完全联接（Full Join）：无论2个表中的记录是否满足联接条件，都将全部记录选入查询结果中。,掌握该种联接,联接查询 联接分为内部联接和外部联接。外部联接又分为左外联接、右外联接和全外联接。 1. 内部联接（Inner Jo

32、in） 只有2个表的字段都满足联接条件时，才将此记录选入查询结果中。,【例】列出图书的借阅情况，要求列出书号、书名、作者、借阅日期。 SELECT a.书号,b.书名,a.借阅日期,b.作者; FROM 借阅 a, 图书 b WHERE b.书号=a.书号 如果采用内部联接方式，则命令 SELECT a.书号,b.书名,a.借阅日期,b.作者; FROM 借阅 a inner join 图书 b on b.书号=a.书号 所得到的结果完全相同。,注意：书号为“bj080014”这条记录就没有出现,注意：若SELECT后要查询的列名（字段名）在2个表中都有，则必须采用“表名.字段名”，若字段名唯

33、一，则可仅写出字段名。,2.左联接 （Left Outer Join) 联接条件左边表中的记录都包含在查询结果中，而右边表中的记录只有满足联接条件时，才选入查询结果中。 ,SELECT b.书号,a.书名,b.借阅日期,a.作者; FROM 图书 a left join 借阅 b on b.书号=a.书号,注意：书号为“bj080014”这条记录就会出现,3.右联接（Right Outer Join）：与左联接正好相反。 4.完全联接（Full Join）：无论2个表中的记录是否满足联接条件，都将全部记录选入查询结果中。,ex1、有如下表所示的数据表：,外币表.dbf,持有者.dbf,（1）计

34、算出刘剑所持有的全部外币相当于人民币 的价值数量，写出正确的SQL命令。 注意：某种外币相当于人民币数量的计算公式： 人民币价值数量= 该种外币的现钞买入价* 该种外币持有数量,SELECT SUM(现钞买入价*持有数量) AS ; 人民币价值 FROM 持有者,兑换 ; WHERE 兑换.外币代码=持有者.外币代码 ; AND 姓名=刘剑,外币表.dbf,持有者.dbf,（2）将兑换表中，美元和英镑的基准价上调0.05%， 写出正确的SQL命令。,UPDATE 兑换SET 基准价=基准价*1.05 ; WHERE 外币名称=美元 ; OR外币名称=英镑,外币表.dbf,持有者.dbf,（3）

35、删除“持有者”表中所有外币名称为“欧元”的记录， 写出正确的SQL命令。,DELETE FROM 持有者 WHERE 外币代码 =; (SELECT 外币代码 FROM 兑换 ; WHERE 外币名称=欧元),外币表.dbf,持有者.dbf,（4）查询持有外币种类在3种以上人员的姓名，及持有 的种类数量，并按种类数量升序排列，数量相同时，则 按姓名降序排列，写出正确的SQL命令。,SELECT 姓名,COUNT(*) AS 外币种类 FROM ; 持有者 GROUP BY 姓名 HAVING ; COUNT(*)=3 ORDER BY ; 外币种类 ,姓名 DESC,ex2：设有两个数据表：职工表ZGB（工号C,姓名C）；奖金表JJB（工号C，基本工资N，奖金N），请将下面的S