VFP第10部分-SQL语言的应用

1、Visual Foxpro 6.0,程序设计基础,重庆工商大学计算机学院计算机基础教研室,作业中的问题,、thisform.text1.value=n 、n=thisform.text1.value 、thisform.text2.caption=s,课堂即时作业，做完即交,编程：输出斐波拉契数列（Fibonacci数列 ）前10项,第十讲 SQL语言的应用,SQL语言简介 SQL语言的功能 数据定义 数据查询 数据操纵 数据控制 实例分析,内容提要,SQL语言简介,SQL（Structured Query Language）语言是一种十分重要的标准数据库语言。SQL语言至1986年成为国际标

2、准语言以后，各数据库软件开发商纷纷推出SQL的关系数据库管理系统产品或接口软件，许多应用广泛的关系数据库管理系统也都增加了DBMS功能，支持标准SQL语言，从而使未来的数据库环境连接为一个统一的整体。,它不是数据库管理系统，也不是应用软件开发语言，它只能用于对数据库中数据的操作。 SQL语言有两种使用方式，即可以作为自含式语言，在数据库管理系统中独立使用，又可以作为嵌入式语言，嵌入许多高级语言（如C、FORTRAN、COBOL）中使用。,SQL语言的特点 1SQL语言是一种一体化的语言 SQL语言集数据定义、数据操纵、数据查询和数据控制的功能于一体，语言风格统一，可以独立完成数据库的全部操作，

3、。,2SQL语言是一种高度非过程化的语言。 它没有必要一步步告诉计算机“如何”去做，而只需要描述清楚用户要“做什么”，SQL语言就可以将要求交给系统，自动完成全部工作。它的大多数语句都是独立执行、与上下文无关。,3语言简洁，易学易用,SQL的命令动词 SQL功能 命令动词 数据查询 SELECT 数据定义 CREATE、DROP、ALTER 数据操纵 INSERT、UPDATE、DELETE 数据控制 GRANT、REVOKE,SQL语言的功能,1、数据定义命令 CREATE TABLE-SQL（建立表结构） ALTER TABLE-SQL（修改表结构） DROP TABLE-SQL（删除表）

4、,主教材P100例6.1、例6.2、例6.3、例6.4、例6.5,实验教材P20实验9任务1任务2,2、数据操纵（修改）命令 INSERT-SQL（插入数据） DELETE-SQL（删除数据） UPDATE-SQL（更新数据）,主教材P112例6.21、例6.22、例6.23、例6.24,实验教材P20实验9任务3,3、SQL数据查询 SQL的核心为查询。SQL查询命令也称为SELECT命令，其基本形式为SELECTFROMWHERE查询块组成，多个查询块可以嵌套执行。,1)基本的SELECT语句 格式： SELECT , FROM 数据库名称！ 基本查询基于单个表，由SELECT 和FROM

5、短语构成无条件查询，或由SELECT、FROM和WHILE短语构成条件查询。,【例】查询dab中所有学生。 SELECT * FROM dab ,【例】 查询dab中“姓名”、“性别”字段。 SELECT 姓名,性别 FROM dab,结果将仅显示如图中的第2列、第3列数据。,其中“*”是通配符，表示显示所有字段，该命令等价于： SELECT 学号,姓名,性别,出生日期,专业班级 FROM dab,2)唯一性查询 唯一性查询指要显示的结果，若有重复出现时，只显示重复行中的一行。这可通过在基本SELECT语句中加入DISTINCT关键字的方法实现。若不加此关键字，则显示全部行。,【例】查询dab

6、中“专业班级”字段，要求数据重复时，仅显示相重复的一条记录。 SELECT 专业班级 FROM dab DISTINCT,结果如图所示。,3)带条件的查询 在SELECT语句中，可以通过WHERE子句为查询规定条件 WHERE子句的格式： WHERE AND 功能：查询指定表或视图中满足查询条件的记录。其中可以是关系表达式，也可以是逻辑表达式，下表列出了组成常用的运算符。,LIKE子句中可以用通配符：下划线“_”，表示任何一个字符；百分号符“%”，表示一串字符。,【例】 查询dab中的男生学号、姓名、性别。 SELECT 学号,姓名,性别 FROM dab WHERE 性别=男 ,结果如下图所

7、示。,【例】 查询dab中计算机1班的男生姓名。 SELECT 姓名 FROM dab WHERE 性别=男 AND 专业班级=计算机1班 【例】 查询xkcjb中成绩在7090分范围内的学生。 SELECT * FROM xkcjb WHERE 成绩 BETWEEN 70 AND 90 ,结果是： 张广田,结果如图所示。等价于： SELECT * FROM xkcjb WHERE 成绩=70 AND 成绩=90,【例】 查询dab中所有姓“张”的学生。 SELECT * FROM dab WHERE 姓名 LIKE 张%,结果如下图所示。,通配符百分号“%”表示0个或多个字符， “_”下划线

8、表示任意一个字符,【例】 查询xkcjb中选修课程号为“001”的学生。 SELECT * FROM xkcjb WHERE 课程号 IN (001) 或： SELECT * FROM xkcjb WHERE 课程号 =001 ,【例】查询学分大于5的课程信息。 select * from 课程 where 学分5,4)排序查询 通过ORDER BY子句可以实现查询结果的排序输出，允许按一列或多列排序。 格式： ORDER BY ASC|DESC,ASC|DESC 其中，ASC表示升序排序（缺省方式），DESC表示降序排序。,【例】 按课程的学分升序查询出全部课程信息。 SELECT * FR

9、OM kcb ORDER BY 学分,结果按学分从低到高显示。,【例】 按学号升序，相同学号再按成绩降序查询出全部学生成绩情况。 SELECT * FROM xkcjb ORDER BY 学号,成绩 DESC ,结果如下图所示。,5)带计算函数的查询 在SELECT语句中，使用SQL语言提供的一些查询计算函数，可以增强查询功能。基本的查询计算函数的格式及功能如下表所示。,【例】 求所有课程的总学分。 SELECT SUM(学分) FROM kcb 【例】 求学生总人数。 SELECT COUNT(*) FROM dab ,结果是： Sum_学分 31,结果是： Cnt 12,【例】 求学生平均

10、年龄。 SELECT AVG(YEAR(DATE()-YEAR(出生日期) FROM dab 【例】 求选修课程的最高成绩和最低成绩。 SELECT MAX(成绩) ,MIN(成绩) FROM xkcjb ,结果是： Avg_exp_1 22.33,结果是： Max_成绩 Min_成绩 100.0 38.0,6)用别名输出列标题（字段名） 将列名用含义更明确的别名输出，便于理解，可以通过AS关键字实现。 格式： AS 功能：将查询输出的列名以列别名输出。其中，列别名若为常字串，则无须加定界符,【例】求选修课程的最高成绩和最低成绩。 SELECT MAX (成绩) AS 最高成绩,MIN(成绩)

11、 AS 最低成绩; FROM xkcjb,结果是： 最高成绩 最低成绩 100.0 38.0,7)分组查询 通过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 记录

12、4 记录5 记录6,记录2 记录5 记录4 记录6,记录2 记录5,where,限定,Group by,分组,Having,限定,【例】求各门课程的平均成绩。 SELECT 课程号,AVG(成绩)； FROM xkcjb GROUP BY 课程号 ,【例】求平均成绩在80分以上的各课程的课程号与平均成绩。 select 课程号,avg(成绩)from xkcjb; group by 课程号 having avg(成绩)80 ,结果如图所示。,【例】求至少选修了2门课程的学生的信息。 SELECT dab.学号,姓名,课程号 FROM dab,xkcjb WHERE dab.学号=xkcjb.学

13、号; GROUP BY dab.学号 HAVING COUNT(*)=2 ,结果如图所示。,8)使用空值进行查询 【例】假设在xkcjb中2004级的学生还没有考试，则成绩为空。试找出尚未考试的选课信息。 update xkcjb set 成绩=null where left(学号,4)= 2004 update xkcjb set 重考成绩=null where left(学号,4)= 2004,注：首先在表设计器中设置“成绩”和“重考成绩”字段允许为空值null,sele * from xkcjb where 成绩 is null,注意：查询空值时要使用IS NULL，而用=NULL是无效

14、的，因为空值不是一个确定的值，不能用“=”这样的运算符进行比较。不为空值应用 IS NOT NULL，不能用！=NULL。,sele * from xkcjb where 成绩 is null and 重考成绩 is null,sele * from xkcjb where 成绩 is not null,)多表联接查询 SELECT语句可以实现对Visual FoxPro 6.0的4种联接类型的查询： 内部联接（Inner Join）:只有2个表的字段都满足联接条件时，才将此记录选入查询结果中。 左联接(Left Outer Join)：联接条件左边表中的记录都包含在查询结果中，而右边表中的记

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

16、若字段名唯一，则可仅写出字段名。,【例】显示dab和xkcjb中成绩不及格的学生姓名、学号、课程号及成绩。 SELECT dab.学号, 姓名,课程号,成绩 FROM xkcjb; INNER JOIN dab ON dab.学号=xkcjb.学号; WHERE 成绩60,结果如图所示。,（2）用WHERE子句实现多表间的联接查询：用WHERE子句实现多表间的联接查询时，无须直接指明联接类型（隐含是内部联接），只需把联接条件直接写入WHERE子句即可。 【例】显示dab和xkcjb中成绩不及格的学生姓名、学号、课程号及成绩。 SELECT dab.学号,姓名,课程号,成绩 FROM xkcjb

17、,dab ; WHERE dab.学号=xkcjb.学号 AND 成绩60,结果如图所示。,（3）使用嵌套查询实现多表间的联接查询：在SQL语言中，由SELECT、FROM、WHERE语句组成一个查询块。嵌套查询就是将第2个查询块放在第1个查询块的WHERE条件中，形成外层(第1个)查询包含内层(第2个)查询的嵌套查询。 外层查询也称为主查询、父查询，内层查询也称为下层查询、子查询。系统对嵌套查询的处理过程是先做子查询，在子查询的基础上再做主查询。 在子查询中还可以再包含更下层的查询，从而形成层嵌套查询。,【例】查询女生学生选课信息，包括学号，课程号，成绩。 本例的查询条件含另一个表的属性，可

18、用嵌套查询。 select *from 选课; where 学号 in (select 学号; from 学生; where 性别=女 ) ,嵌套查询是一类基于多个关系的查询，此类查询所要求的结果出自一个关系，但相关的条件却涉及多个关系。,注意：此处不能用“=”代替“in”，因为内层查询结果是一个集合。如果内层查询结果是一个值则可用“=”,【例】查询成绩在90分及以上的学生姓名。 SELECT 姓名 FROM dab WHERE 学号 IN； (SELECT 学号 FROM xkcjb WHERE 成绩=90) ,结果是蜂雪燕和张伟。,【例】 显示dab.DBF和xkcjb.DBF中成绩不及格

19、的学生姓名、学号、课程号及成绩。,方法1：SELECT dab.学号,姓名,课程号,成绩 FROM 选课 INNER JOIN 学生 ON dab.学号=xkcjb.学号 WHERE 成绩60,方法2：SELECT a.学号,姓名,课程号,成绩 FROM xkcjb a,dab b WHERE a.学号= b.学号 AND 成绩60,有几种方法?,【例】 在dab.dbf,xkcjb.dbf和kcb.dbf中查询并显示各个学生的学号，姓名，成绩及课程名。,select a.学号, 姓名, 课程名, 成绩 ; from dab a，kcb b，xkcjb c ; where a.学号=c.学号

20、and b.课程号=c.课程号,教科书109页例6.16,【例】 查询成绩在90分及以上的学生姓名。,SELECT 姓名 FROM dab WHERE 学号 IN (SELECT 学号 FROM xkcjb WHERE 成绩=90),教科书106-107页例6.10、例6.11、例6.12、例6.13,教科书110页例6.17,10)查询中的几个特殊选项 (1)使用TOP PERCENT短语显示部分结果 【例】显示学分较高的2门课程的信息。 SELECT * TOP 2 FROM KCB ORDER BY 学分 DESC 注意：TOP短语要与ORDER BY短语同时使用才有效。 【例】显示学分

21、最低的20%课程的信息。 SELECT * TOP 20 PERCENT FROM KCB ORDER BY 学分,(2)使用INTO ARRAY 将查询结果存放到数组中。 【例】 查询学生档案信息并将结果放入数组TEMP中。 SELECT * FROM DAB INTO ARRAY TEMP (3)使用INTO CURSOR 将查询结果存放到临时表中。 【例】 查询课程表信息并将结果放入临时表TEMP中。 SELECT * FROM KCB INTO CURSOR TEMP (4)使用INTO DBF|TABLE 将查询结果存放到永久表中。 【例】 查询课程表信息并将结果放入永久表TEMP中

22、。 SELECT * FROM KCB INTO DBF TEMP,（5）TO FILEADDITIVE：将查询结果保存到文本文件中。如果带“ADDITIVE”关键字，查询结果以追加方式添加到指定的文件，否则，以新建或覆盖方式添加到指定的文件。 （6）TO SCREEN：将查询结果保在屏幕上显示。 （7）TO PRINTER：将查询结果送打印机打印。,教材107 条件运算符 any 和all的区别,实例分析,主教材P104P112,例6.6例6.20,作业中的问题,实验教材25实验十任务二(5) 查询各部门中工龄最长的教工的姓名、职称、工龄、实发工资、住房公积金,select 部门,姓名,职称

23、, max(year(date()-year(工作日期) as 工龄,实发工资,住房公积金 from jsxx,jsgz where jsxx.工号=jsgz.工号 group by 部门,select 部门,姓名,职称,(year(date()-year(工作日期) as 工龄 from jsxx,sele 部门,max(year(date()-year(工作日期) as 工龄 from jsxx group by 部门 into cursor bmbl,select bmbl.部门,姓名,职称,(year(date()-year(工作日期) as 工龄 ,实发工资,住房公积金 from j

24、sxx,jsgz,bmbl where (jsxx.工号=jsgz.工号) and jsxx.部门=bmbl.部门 and (year(date()-year(工作日期)=bmbl.工龄,例：（0701）设有学生选课表SC（学号，课程号，成绩），用SQL语言检索每门课程的课程号及平均分的语句是： select 课程号, (1) from sc (2),（1）avg（成绩） （2）group by 课程号,例(0702)商品数据库中含有两个表：商品表和销售表，结构如下： 商品：商品编号 C(6)，商品名称 C(20)，销售价 N(12，2) 销售：流水号 C(6)，销售日期 D，商品编号 C(6

25、)，销售量 N(8,2) 用SQL命令实现查询2007年10月20日所销售的各种商品的名称、销售量和销售总额，并按销售量从小到大排序的语句是： SELECT 商品名称, sum(销售量)，SUM（ ）AS 销售总额 FROM 商品，销售; WHERE 商品.商品编号=销售.商品编号 销售日期=2007-10-20 GROUP BY 商品名称 ORDER BY ,销售价*销售量 and 2,例（0802）. 已知数据表“职工.DBF”（职工号，姓名，性别，出生日期，部门号，）和“部门.DBF”（部门号，部门名，部门经理）。请填空完成相应任务的SQL语句。 查询每个部门年龄最长者的信息,要求得到的

26、信息包括部门名和最长者的出生日期。 SELECT 部门名, （1） FROM 部门 JOIN 职工 ON 部门.部门号=职工.部门号 GROUP BY 部门名 查询有10名以上(含10名)职工的部门信息(部门名和职工人数),并按职工人数降序排列。 SELECT 部门名,COUNT(职工号) AS 职工人数 FROM 部门，职工WHERE 部门.部门号=职工.部门号 GROUP BY 部门名 HAVING COUNT(*)=10 ORDER BY （2）,（1）min(出生日期),（2）职工人数,例（0901）有数据表XS(学号 C(6)，姓名 C(8)等字段)和数据表SC(学号 C(6)，课程

27、号 C(3)，成绩 N(5，)等字段)，用SQL语言检索选修课程在3门以上（含3门）的学生的学号、姓名和平均成绩，并按平均成绩降序排序。请将下面的语句补充完整。 SELECT XS.学号,姓名,avg(成绩) AS (1) FROM XS,SC; WHERE XS.学号=SC.学号; GROUP BY XS.学号HAVING (2) 平均成绩 DESC,（1）平均成绩 （2）count(*)=3 order by,SELECT 姓名,地区,联系方式,图书编号,借阅日期 FROM 读者 A,借阅 B WHERE A.借书证号=B.借书证号 AND DATE()-借阅日期70 AND LEFT(图

28、书编号,1)=“IS” INTO DBF 特殊名单 SELECT地区,姓名,联系方式, DATE()-借阅日期-70 AS 超期天数 FROM 特殊名单ORDER BY 地区 TO SCREEN SELETCT 地区,COUNT(*) AS 人数 FROM 特殊名单 GROUP BY 地区 TO SCREEN,SQL的数据操作语句不包括（ ）。 A. INSERT B. UPDATE C. SELECT D. CHANGE,查询订购单号首字符是“P”的订单信息，应该使用命令( )。 A. SELECT * FROM 订单 WHERE HEAD(订购单号,1)=P B. SELECT * FRO

29、M 订单 WHERE LEFT(订购单号,1)=P C. SELECT * FROM 订单 WHERE P$订购单号 D. SELECT * FROM 订单 WHERE RIGHT(订购单号,1)=P,在Visual Foxpro的查询设计器中“排序依据”选项卡对应的SQL短语是（ ） A）INTO B）ORDER BY C）WHERED）GROUP BY,视图设计器选项卡与查询设计器中选项卡几乎一样，只是视图设计器中的选项卡比查询设计器中的选项卡多一个（ ） A）字段B）排序依据C）联接D）更新条件,在教师表jsb.dbf中查询“出生日期”的年份在1970年到1980年间的教师信息，应输入命

30、令（ ） A）SELECT * FROM jsb WHERE 1970YERAR(出生日期)1980 B）SELECT 信息FROM jsb WHERE YEAR(出生日期) BETWEEN 1980 AND 1970 C）SELECT * FROM jsb WHERE YEAR(出生日期) BETWEEN 1970 AND 1980 D） SELECT 信息 WHERE 1970YEAR(出生日期)1980 FROM jsb,下列有关SQL的错误叙述是（ ） A）SQL语句可以重新指定列的顺序 B）SQL语言能嵌入到程序设计语言中以程序方式使用 C）SQL语句中DISTINCT短语可省略选择字段包含重复数据的记录 D）SQL语言是一种高度过程化的语言,在SQL的查询语句中，实现投影操作的短语为（ ）。 SELECT B) FROM C) WHERE D) JOIN ON,SQL查询时，用WHERE子句指出的是 ( ) A）查询目标 B