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

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年成为国际标准语言以后，各数据库软件开发商纷纷推出SQL的关系数据库管理系统产品或接口软件，许多应用广泛的关系数据库管理系统也都增加了DBMS功能，支持标准SQL语言，从而使未来的数据库环境连接为一个统一的整体。,它不是数据库管理系统，也不是应用软件开发语言，它只能用于对数据库中数据的操作。SQL语言有两种使用方式，即可以作为自含式语言，在数据库管理系统中独立使用，又可以作为嵌入式语言，嵌入许多高级语言（如C、FORTRAN、COBOL）中使用。,SQL语言的特点1SQL语言是一种一体化的语言 SQL语言集数据定义、数据操纵、数据查询和数据控制的功能于一体，语言风格统一，可以独立完成数据库的全部操作，。,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（删除表）,主教材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短语构成无条件查询，或由SELECT、FROM和WHILE短语构成条件查询。,【例】查询dab中所有学生。SELECT*FROM dab,【例】查询dab中“姓名”、“性别”字段。SELECT 姓名,性别 FROM dab,结果将仅显示如图中的第2列、第3列数据。,其中“*”是通配符，表示显示所有字段，该命令等价于：SELECT 学号,姓名,性别,出生日期,专业班级 FROM dab,2)唯一性查询 唯一性查询指要显示的结果，若有重复出现时，只显示重复行中的一行。这可通过在基本SELECT语句中加入DISTINCT关键字的方法实现。若不加此关键字，则显示全部行。,【例】查询dab中“专业班级”字段，要求数据重复时，仅显示相重复的一条记录。SELECT 专业班级 FROM dab DISTINCT,结果如图所示。,3)带条件的查询 在SELECT语句中，可以通过WHERE子句为查询规定条件WHERE子句的格式：WHERE AND 功能：查询指定表或视图中满足查询条件的记录。其中可以是关系表达式，也可以是逻辑表达式，下表列出了组成常用的运算符。,LIKE子句中可以用通配符：下划线“_”，表示任何一个字符；百分号符“%”，表示一串字符。,【例】查询dab中的男生学号、姓名、性别。SELECT 学号,姓名,性别 FROM dab WHERE 性别=男,结果如下图所示。,【例】查询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个或多个字符，“_”下划线表示任意一个字符,【例】查询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*FROM kcb ORDER BY 学分,结果按学分从低到高显示。,【例】按学号升序，相同学号再按成绩降序查询出全部学生成绩情况。SELECT*FROM xkcjb ORDER BY 学号,成绩 DESC,结果如下图所示。,5)带计算函数的查询 在SELECT语句中，使用SQL语言提供的一些查询计算函数，可以增强查询功能。基本的查询计算函数的格式及功能如下表所示。,【例】求所有课程的总学分。SELECT SUM(学分)FROM kcb【例】求学生总人数。SELECT COUNT(*)FROM dab,结果是：Sum_学分31,结果是：Cnt12,【例】求学生平均年龄。SELECT AVG(YEAR(DATE()-YEAR(出生日期)FROM dab【例】求选修课程的最高成绩和最低成绩。SELECT MAX(成绩),MIN(成绩)FROM xkcjb,结果是：Avg_exp_122.33,结果是：Max_成绩 Min_成绩100.0 38.0,6)用别名输出列标题（字段名）将列名用含义更明确的别名输出，便于理解，可以通过AS关键字实现。格式：AS 功能：将查询输出的列名以列别名输出。其中，列别名若为常字串，则无须加定界符,【例】求选修课程的最高成绩和最低成绩。SELECT MAX(成绩)AS 最高成绩,MIN(成绩)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记录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.学号;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是无效的，因为空值不是一个确定的值，不能用“=”这样的运算符进行比较。不为空值应用 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)：联接条件左边表中的记录都包含在查询结果中，而右边表中的记录只有满足联接条件时，才选入查询结果中。右联接（Right Outer Join）：与左联接正好相反。完全联接（Full Join）：无论2个表中的记录是否满足联接条件，都将全部记录选入查询结果中。,通常有3种方法实现多表联接查询，一是在查询命令中显示地指定联接类型；二是在查询命令中使用WHERE子句；三是使用嵌套查询。（1）指定联接类型实现多表间的联接查询 格式：SELECT FROM INNER|LEFT|RIGHT|FULL JOIN ON WHERE 功能：通过指定的联接类型建立多表间的联接 注意：若SELECT后要查询的列名（字段名）在2个表中都有，则必须采用“表名.字段名”，若字段名唯一，则可仅写出字段名。,【例】显示dab和xkcjb中成绩不及格的学生姓名、学号、课程号及成绩。SELECT dab.学号,姓名,课程号,成绩 FROM xkcjb;INNER JOIN dab ON dab.学号=xkcjb.学号;WHERE 成绩60,结果如图所示。,（2）用WHERE子句实现多表间的联接查询：用WHERE子句实现多表间的联接查询时，无须直接指明联接类型（隐含是内部联接），只需把联接条件直接写入WHERE子句即可。【例】显示dab和xkcjb中成绩不及格的学生姓名、学号、课程号及成绩。SELECT dab.学号,姓名,课程号,成绩 FROM xkcjb,dab;WHERE dab.学号=xkcjb.学号 AND 成绩60,结果如图所示。,（3）使用嵌套查询实现多表间的联接查询：在SQL语言中，由SELECT、FROM、WHERE语句组成一个查询块。嵌套查询就是将第2个查询块放在第1个查询块的WHERE条件中，形成外层(第1个)查询包含内层(第2个)查询的嵌套查询。外层查询也称为主查询、父查询，内层查询也称为下层查询、子查询。系统对嵌套查询的处理过程是先做子查询，在子查询的基础上再做主查询。在子查询中还可以再包含更下层的查询，从而形成层嵌套查询。,【例】查询女生学生选课信息，包括学号，课程号，成绩。本例的查询条件含另一个表的属性，可用嵌套查询。select*from 选课;where 学号 in(select 学号;from 学生;where 性别=女),嵌套查询是一类基于多个关系的查询，此类查询所要求的结果出自一个关系，但相关的条件却涉及多个关系。,注意：此处不能用“=”代替“in”，因为内层查询结果是一个集合。如果内层查询结果是一个值则可用“=”,【例】查询成绩在90分及以上的学生姓名。SELECT 姓名 FROM dab WHERE 学号 IN；(SELECT 学号 FROM xkcjb WHERE 成绩=90),结果是蜂雪燕和张伟。,【例】显示dab.DBF和xkcjb.DBF中成绩不及格的学生姓名、学号、课程号及成绩。,方法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.学号 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短语同时使用才有效。【例】显示学分最低的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中。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 部门,姓名,职称,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 jsxx,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)，销售量 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语句。查询每个部门年龄最长者的信息,要求得到的信息包括部门名和最长者的出生日期。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)，课程号 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(图书编号,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)=PB.SELECT*FROM 订单 WHERE LEFT(订购单号,1)=PC.SELECT*FROM 订单 WHERE P$订购单号D.SELECT*FROM 订单 WHERE RIGHT(订购单号,1)=P,在Visual Foxpro的查询设计器中“排序依据”选项卡对应的SQL短语是（）A）INTO B）ORDER BYC）WHERED）GROUP BY,视图设计器选项卡与查询设计器中选项卡几乎一样，只是视图设计器中的选项卡比查询设计器中的选项卡多一个（）A）字段B）排序依据C）联接D）更新条件,在教师表jsb.dbf中查询“出生日期”的年份在1970年到1980年间的教师信息，应输入命令（）A）SELECT*FROM jsb WHERE 1970YERAR(出生日期)1980B）SELECT 信息FROM jsb WHERE YEAR(出生日期)BETWEEN 1980 AND 1970C）SELECT*FROM jsb WHERE YEAR(出生日期)BETWEEN 1970 AND 1980D）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）查询结果 C）查询条件 D）查询视图,已知数据表“职工.DBF”（职工号，姓名，）和“工资.DBF”（职工号，基本工资，津贴，奖金，扣款），要查询职工实发工资的SQL命令是（）。A)SELECT 姓名,(基本工资+津贴+奖金-扣款)AS 实发工资 FROM 工资 B)SELECT 姓名,(基本工资+津贴+奖金-扣款)AS 实发工资 FROM 工资 WHERE 职工.职工号=工资.职工号 C)SELECT 姓名,(基本工资+津贴+奖金-扣款)AS 实发工资 FROM 工资,职工 WHERE 职工.职工号=工资.职工号 D)SELECT 姓名,(基本工资+津贴+奖金-扣款)AS 实发工资FROM 工资 JOIN 职工 WHERE 职工.职工号=工资.职工号,数据表STUD.DBF中有成绩等字段，执行SQL 命令“SELECT*FROM STUD INTO DBF XX ORDER BY 成绩”之后，将（）。A）会提示命令出错B）会生成一个按“成绩”字段升序排序的表文件XX.DBFC）会生成一个按“成绩”字段降序排序的表文件XX.DBFD）在屏幕的浏览窗口中显示一个按“成绩”字段排序的结果,SQL命令：Create Table 学生表(姓名 C(20)，性别 L，年级 C(4)，专业班级 C(20)可以在当前数据库中创建一个名为“学生表”的数据表。在SQL SELECT语句中，实现字符串匹配的运算符是“LIKE”。在SQL查询语言中，TOP 短语必须与ORDER BY 短语配对使用，但ORDER BY 短语可以单独使用。SQL查询命令可以为输出的记录进行排序。（）若只有HAVING 短语而无GROUP BY短语，则HAVING短语相当于WHERE短语。（）,课堂即时作业，做完即交,（1）正确修改每个学生的“成绩合格否”字段，并填入学生成绩表CJ.DBF中。规定笔试成绩60或上机成绩为“缺考”或“不及格”，则该字段值为.F.，否则为.T.；（2）在学生成绩表CJ.DBF中，查询并显示成绩合格学生的考号、姓名、笔试成绩和上机成绩；（3）逻辑删除学生成绩表CJ.DBF中，上机考试缺考的考生；,学生信息表（XS.DBF）,学生成绩表（CJ.DBF）,第10次上机 准备,熟悉vfp环境，默认路径的设置实验15 任务1（1）（2）（3）实验15 任务2,