九乡新闻网魂斗罗中的水下8关:查询精解
来源：百度文库 编辑：九乡新闻网 时间：2024/05/06 14:00:01
5.1SQL语言
——只有9个动词（select , drop, alter, create, insert, update ,delete, grant, revoke )
5.2.1 基本查询
【格式】SELECT [ALL | DISTINCT] <字段列表> FROM <表> [ORDER BY <字段名> [ASC/DESC] [,<字段> [ASC/DESC]],...]
【功能】无条件查询，将记录显示在浏览窗口中。
【说明】ALL： 显示全部查询记录，包括重复记录。
DISTINCT： 显示无重复结果的记录，将重复记录只保留一条。
字段列表：字段1，字段2 AS 别名，函数或表达式 AS 列名，......查询结果的输出列；如果将表中所有字段都选择输出，则用*代替；在函数或表达式中还可以用一些专用统计函数
FROM 表：设定查询的来源数据
ORDER BY：按指定的字段值大小对结果记录进行排序
SQL命令中常用的统计函数
AVG（字段名）——平均值 MIN（字段名）——最小值 MAX（字段名）——最大值 SUM（字段名）——和 COUNT（*或字段名）——满足条件的记录总数，字段名可以不写。
能进行统计的字段必须是表示数值的数据类型。用了统计函数后，视情况会将统计的记录压缩成一条或多条。
学号
姓名
性别
系名
籍贯
出生年月
是否党员
0207390101
刘中雨
女
信息管理系
北京
80/12/1
是
0207390103
林天力
男
信息管理系
南京
82/1/3
是
0207390104
王 平
男
信息管理系
北京
82/3/26
否
0207490101
章闻天
男
电子商务
无锡
81/6/4
否
0207390102
闻书敏
女
信息管理系
苏州
81/7/28
是
0207490104
于小丽
女
电子商务
南京
80/8/10
否
【例1】显示XS（学生信息数据表）中的所有记录。
SELECT * FROM XS
命令中的*表示输出显示所有的字段，数据来源是XS表
【例2】显示XS（学生信息数据表）中的所有同学的学号及与之对应的姓名，同时能去除重名。
SELECT DISTINCT 学号，姓名 FROM XS
【例3】按成绩从大到小显示CJ表中的所有记录，并将成绩一项乘以0.7。
SELECT XH，KCDH，CJ*0.7 AS 成绩 FROM CJ order by cj DESC
【例4】统计CJ表中所有同学的总成绩，平均成绩和总人数。。
SELECT sum(cj.cj) as 总成绩,avg(cj.cj) as 平均成绩, COUNT(*) AS RS FROM CJ
课堂思考题
【题1】显示XS表中所有的系名。
SELECT distinct ximing FROM XS
【题2】显示XS表中所有的班级号。
SELECT distinct substr(xh,1,8) as 班级号
FROM XS ORDER BY 1
5.2.2 带条件（WHERE）的查询语句
【格式】SELECT [ALL|DISTINCT |TOP N] <字段列表>
FROM <表> [WHERE <条件表达式> ]
ORDER BY 字段列表
【功能】从一个表中查询满足条件的数据。
【说明】TOP N ：显示满足条件的前几项
<条件表达式>由一系列用AND 或 OR 连接的条件表达式组成， 条件表达式可以为字段（表达式） 运算符 表达式（字段）的形式，其中可以使用以下常用的运算符
SQL 命令中常用的 运算符
操作符 比较关系 举例
= 相等 Xs.ximing= " 金融 "
= = 完全相等 Xs.ximing = = " 金融 "
LIKE 不精确匹配 Xs.xh LIKE " 03073901% "
> 、 >= 大于（等于） 分数 . 成绩 >60
<= 、 < 小于（等于） 分数 . 成绩 <=60
betwee n 指定字段在两实 例之间 Cj between 45 and 80
in 指定字段必须与 用逗号隔开的实 例集合中的一个 相匹配 cj in (50,60,70)
示例
【例1】显示XS表中所有男生记录的学号，姓名和性别
SELECT XH，XM，XB FROM XS WHERE XB="男"
【例2】显示XS表中出生日期在85年之间的学生的学号，姓名，出生日期。
SELECT XH，XM，CSRQ FROM XS ；
WHERE CSRQ BETWEEN {01/01/85} AND {12/31/85}
SELECT XH，XM，CSRQ FROM XS; *//另一种方法
WHERE year(CSRQ)=85
【例3】显示XS表中姓李的女学生的学号，姓名，出生日期。
SELECT XH，XM，CSRQ FROM XS;
WHERE XM LIKE "李%" AND XB="女"
【例4】查询CJ表中成绩名列前5名的学生的学号，成绩。
SELECT TOP 5 XH，CJ FROM CJ ;
ORDER BY CJ DESC
【例5】统计XS表中03073901班的人数。
Select count(*) as 03073901班人数 from xs； (*//SUBSTR（XH,1，8）=" 03073901")
where xh like ‘03073901%‘
SQL 命令中的分组与计算查询
【格式】SELECT [ALL | DISTINCT |TOP N] <字段列表>
FROM <表> [WHERE <条件>
[GROUP BY <分组字段列表>...]
[HAVING <过滤条件>]
[ORDER BY <排序项> [ASC | DESC]
分组就是将一组类似（根据分组字段的值）的记录压缩成一个结果记录，这样就可以完成基于一组记录的计算。其结果可以类比于唯一索引。[过滤条件] 对分组的结果根据条件(可以是来自于字段列表项中的选项，也可以是一个统计函数)进行记录组的过滤。注意：用于分组的可用字段不一定是已选定输出的字段列表中的一个字段，但分组字段不能是一个计算过的字段，如统计函数。 排序项必须是已选定输出的字段列表中的字段
Group by ximing
学号
姓名
性别
系名
籍贯
出生年月
是否党员
0207390101
刘中雨
女
信息管理系
北京
80/12/1
是
0207390103
林天力
男
信息管理系
南京
82/1/3
是
0207390104
王 平
男
信息管理系
北京
82/3/26
否
0207490101
章闻天
男
电子商务
无锡
81/6/4
否
0207390102
闻书敏
女
信息管理系
苏州
81/7/28
是
0207490104
于小丽
女
电子商务
南京
80/8/10
否
根据系名的值该学生表分为两种类型的记录，一个是系名为 " 信息 管理系 " 的记录，一个是系名为 " 电子商务 " 的记录，分组的结果是 两条记录：第 2 条和 4 条记录
示例
【例 1 】显示 XS 表中各班的总人数。
SELECT XIMING ，
COUNT(*) AS 总人数 ;
FROM XS GROUP BY XIMING
【例2】显示CJ表中平均分>80的同学
SELECT CJ.XH FROM CJ GROUP BY XH;
HAVING AVG(CJ)>80
ORDER BY 1
【例3】按学号从低到高，显示CJ表中每个学生所学课程数目，成绩总分，平均分
Select cj.xh,count(*) as 课程数目，
sum(cj.cj) as 成绩总分，avg(cj.cj) as 平均分
from cj
group by cj.xh
Order by xh
【例4】查询JS表中各系科教师的人数、工资总额和平均工资，并按平均工资降序排列。
SELECT XIMING,COUNT(*) AS 人数,SUM(ZGZ) AS 工资总额,AVG(zgz) as 平均工资 from js;
Group by ximing order by 4
*//?查询信息管理系教师的人数、工资总额和平均工资
SELECT XIMING,COUNT(*) AS 人数,SUM(ZGZ) AS 工资总额,AVG(zgz) as 平均工资 from js;
where ximing="信息管理系";
Group by ximing having ximing="信息管理系" order by 4
【例5】查询JS表中系平均工资>500元的各系科教师的人数、工资总额和平均工资，并按平均工资降序排列，查询结果中必须包括系名、系人数、工资总额和平均工资四列。
SELECT XIMING,COUNT(*) AS 人数,SUM(ZGZ) AS 工资总额,AVG(zgz) as 平均工资 from js;
Group by ximing having 平均工资>500;
order by 4 ????
5.2.3 SQL的复杂查询
1．连接查询
【说明】在一个数据库中的多个表之间一般都存在着某些联系，在一个查询语句中同时涉及到两个或两个以上的表时，这种查询称之为连接查询（也称为多表查询）。在多表之间查询必须处理表与表之间的连接关系。
【例1】查询并显示各个学生的学号，姓名，各科成绩。
SELECT xs.xh，xs.xm，cj.kcdh，cj.cj ;
FROM xs，cj ;
WHERE xs.xh=cj.xh
【例2】显示成绩在80至90之间的学号，课程名和成绩。
SELECT cj.xh，kc.kcm，cj.cj
FROM cj,kc
WHERE cj.kcdh=kc.kcdh and
cj.cj BETWEEN 80 AND 90
2.示例
例1】查询并显示各个学生的学号，所学课程及课程成绩。
SELECT CJ.XH，KC.KCM，CJ.CJ ;
FROM KC,CJ ;
WHERE KC.KCDH=CJ.KCDH
内连接方式
SELECT CJ.XH，KC.KCM，CJ.CJ ;
FROM KC INNER JOIN CJ ON KC.KCDH=CJ.KCDH
【例2】显示计算机系学生的课程代号为"A001"的考试成绩。
SELECT XS.XH，XS.XIMING，XS.XM,cJ.KCDH，CJ.CJ;
FROM XS INNER JOIN CJ ON XS.XH=CJ.XH;
wHERE XS.XIMING="计算机系" AND CJ.KCDH="A001"
【例3】查询王林同学所选课程的课程数
Select xs.xm,count(*) from xs inner join cj on xs.xh=cj.xh where xs.xm=‘王林'?
【例4】显示CJ表中每门课程的课程名，学时，必修课情况和该门课程的最高分，最低分和平均分
Select kc.kcm,kc.kss,max(cj.cj),min(cj.cj),avg(cj.cj)
from kc inner join cj on kc.kcdh=cj.kcdh
group by kc.kcdh
【例5】查询有不及格课程成绩的同学的学号和姓名，有多门课程不及格的学生只显示一次
Select distinct cj.xh,xs.xm ；
from xs inner join cj on xs.xh=cj.xh；
where cj.cj<60；
order by xs.xh
或 Select distinct cj.xh,xs.xm from xs.cj；
where xs.xh=cj.xh and cj.cj<60 order by xs.xh
【例6】查询信息管理系中所有成绩总分在480分以上，并且各门课程的成绩不低于70分的学生。要求在查询结果中包含学生的学号、姓名、总分、平均分和最低分，并按总分从高到低排列。
Select xs.xh,xs.xm,xum(cj.cj) as 总分,avg(cj.cj) as 平均成绩，min(cj.cj) as 最低分；
from xs inner join cj on xs.xh=cj.xh ；
where xs.ximing=‘信息管理系' ；
group by xs.xh；
having 总分>=480 and 最低分>=70；
order by 3 desc????
课堂思考题 查询选修人数超过40人的课程名，学分，必修课情况和该门课程的最高分和最低分，并按最高分从低到高排序。
Select kc.kcm,kc.xf,kc.bxk,max(cj.cj),min(cj.cj);
from kc inner join cj on kc.kcdh=cj.kcdh;
group by kc.kcdh having count(*)>40 order by 4
列出所有选修学分超过21个学分（考试成绩超过65分的该门课程就可以获得学分）的学生的学号以及所选修的总学分。
Select cj.xh,sum(kc.xf);
from kc inner join cj on kc.kcdh=cj.kcdh where cj.cj>=65;
group by cj.xh having sum(kc.xf)>21 order by 2
查询选修人数超过40人的课程名，学分，必修课情况和该门课程的最高分和最低分，并按最高分从低到高排序。
Select kc.kcm,kc.xf,kc.bxk,max(cj.cj),min(cj.cj);
from kc inner join cj on kc.kcdh=cj.kcdh;
group by kc.kcdh having count(*)>40 order by 4
列出所有选修学分超过21个学分（考试成绩超过65分的该门课程就可以获得学分）的学生的学号以及所选修的总学分。
Select cj.xh,sum(kc.xf);
from kc inner join cj on kc.kcdh=cj.kcdh where cj.cj>=65;
group by cj.xh having sum(kc.xf)>21 order by 2
3．查询去向
默认情况下，查询输出到一个浏览窗口 INTO ARRAY 数组名：将查询结果保存到一个数组中。 INTO CURSOR < 临时表名>：将查询结果保存到一个临时表中。 TO SCREEN：将查询结果保在屏幕上显示。
SELECT-SQL 命令主要组成部分
定义数据源 指定数据源表 FROM 子句
确定源表间的联 接 INNER JOIN ... ON ... 子句
定义结果 筛选源表记录 WHERE 子句
指定输出字段 字段、函数和表达式的列 表或 *
指定输出类型 INTO 子句和 TO 子句
定义记录的分组 GROUP BY 子句
指定结果顺序 ORDER BY 子句
筛选结果记录 HAVING 子句
指定有无重复记 录 ALL/DISTINCT
指定结果的范围 TOP nExpr[PERCENT]
习题:
针对于教学管理信息系统，进行以下查询：
1.查询男教师和女教师的工资总和以及平均工资??
2.统计9501班的男同学的人数??
3.查询65年以前出生的女教师的姓名、工龄??
4.显示JS表中每个教师的工号、姓名、出生日期和开始工作年份（提示：开始工作年份=当前年份-工龄）；
5.显示CJ表中，课程代号为"02"的学生成绩的前三名；
6.显示JS表中各系科教师的人数，工资总额和平均工资。
7.统计选修'VFP5'的学生总数?
8.查询课程名为'VFP5'的学时，必修课情况和该门课程的最高分、最低分和平均分
9.查询每个教师所任课程的课程代号、课程名和学分数以及工号和专业代号??
答案:
1.Select js.xh,sum(js.jbgz) as 总工资,avg(js.jbgz) as 平均工资 from jxsj!js group by js.xb
2.Select count(*) as 男同学人数 from xs where xs.xh like ‘9501%' and xs.xb=‘男'
3.Select js.xm,js.gl,js.csrq from jxsj!js where year(js.csrq)<1965
4.Select kc.kcm,count(*) as 选修人数 from kc inner join cj on kc.kcdh=cj.kcdh where kc.kcm='VFP5'
或Select kc.kcm,count(*) from kc,cj where kc.kcdh=cj.kcdh .and. kc.kcm='VFP5'
5.Select kc.kcm,kc.kss,max(cj.cj),min(cj.cj),avg(cj.cj)
from kc inner join cj on kc.kcdh=cj.kcdh
group by kc.kcdh having kc.kcm='VFP5'
或Select kc.kcm,kc.kss,max(cj.cj),min(cj.cj),avg(cj.cj)from kc inner join cj on kc.kcdh=cj.kcdh where kc.kcm='VFP5' group by kc.kcdh
6.Select rk.gh,rk.zydh,kc.kcm,kc.kss,kc.bxk from rk inner join kc on kc.kcdh=rk.kcdh
3．查询去向
默认情况下，查询输出到一个浏览窗口INTO ARRAY 数组名：将查询结果保存到一个数组中。INTO CURSOR < 临时表名>：将查询结果保存到一个临时表中。TO SCREEN：将查询结果保在屏幕上显示。
SELECT-SQL 命令主要组成部分
定义数据源 指定数据源表 FROM 子句
确定源表间的联 接 INNER JOIN ... ON ... 子句
定义结果 筛选源表记录 WHERE 子句
指定输出字段 字段、函数和表达式的列 表或 *
指定输出类型 INTO 子句和 TO 子句
定义记录的分组 GROUP BY 子句
指定结果顺序 ORDER BY 子句
筛选结果记录 HAVING 子句
指定有无重复记 录 ALL/DISTINCT
指定结果的范围 TOP nExpr[PERCENT]
习题:
针对于教学管理信息系统，进行以下查询：
1.查询男教师和女教师的工资总和以及平均工资??
2.统计9501班的男同学的人数??
3.查询65年以前出生的女教师的姓名、工龄??
4.显示JS表中每个教师的工号、姓名、出生日期和开始工作年份（提示：开始工作年份=当前年份-工龄）；
5.显示CJ表中，课程代号为"02"的学生成绩的前三名；
6.显示JS表中各系科教师的人数，工资总额和平均工资。
7.统计选修'VFP5'的学生总数?
8.查询课程名为'VFP5'的学时，必修课情况和该门课程的最高分、最低分和平均分
9.查询每个教师所任课程的课程代号、课程名和学分数以及工号和专业代号??
答案:
1.Select js.xh,sum(js.jbgz) as 总工资,avg(js.jbgz) as 平均工资 from jxsj!js group by js.xb
2.Select count(*) as 男同学人数 from xs where xs.xh like ‘9501%' and xs.xb=‘男'
3.Select js.xm,js.gl,js.csrq from jxsj!js where year(js.csrq)<1965
7.Select kc.kcm,count(*) as 选修人数 from kc inner join cj on kc.kcdh=cj.kcdh where kc.kcm='VFP5'
或Select kc.kcm,count(*) from kc,cj where kc.kcdh=cj.kcdh .and. kc.kcm='VFP5'
8.Select kc.kcm,kc.kss,max(cj.cj),min(cj.cj),avg(cj.cj)
from kc inner join cj on kc.kcdh=cj.kcdh
group by kc.kcdh having kc.kcm='VFP5'
或Select kc.kcm,kc.kss,max(cj.cj),min(cj.cj),avg(cj.cj)from kc inner join cj on kc.kcdh=cj.kcdh where kc.kcm='VFP5' group by kc.kcdh
9. Select rk.gh,rk.zydh,kc.kcm,kc.xfs,kc.kcdh from rk inner join kc on kc.kcdh=rk.kcdh
5.3 查询设计器及查询文件
——一个用来保存SQL语句的文件（*。QPR），查询文件的运行结果是一个基于表和视图的动态的数据集合。
5.3.1 查询文件的设计过程
在创建查询文件时，通常可以遵循以下六个步骤来创建查询。
（1）用"查询向导"或"查询设计器"创建查询。
（2）选择在查询结果中需要的字段。
（3）设置查询记录的条件。
（4）设置排序及分组条件来组织查询结果。
（5）选择查询输出类别，可以是报表、表文件、图表、浏览窗口和表文件等。
（6）运行此查询。
5.3.2 利用"查询向导"创建查询
利用"查询向导"创建查询的步骤如下：
（1）进入"查询向导"
① 选择【工具】|【向导】|【查询】命令。
② 选择【文件】|【新建】命令，进入"新建"对话框，选择"查询"单选按钮，单击"向导"按钮。
③ 在"项目管理器"窗口中，选择"数据"选项卡，选中"查询"，单击"新建"按钮，出现"新建查询"对话框，单击"查询向导" 。
（2）选择查询结果中需要的字段
（3）设置查询条件
（4）设置排序字段
（5）设置记录输出范围
（6）保存查询
5.3.3 利用"查询设计器"创建查询
进入"查询设计器"窗口
（1） 选择【文件】│【新建】命令，进入"新建"对话框，选择"查询"单选按钮，单击"新建"按钮；
（2） 在"项目管理器"窗口中，选择"数据"选项卡，选中"查询"文件类型，单击"新建"按钮，出现"新建查询"对话框，单击"新建查询"按钮
"查询设计器工具栏"各按钮的功能如下：
添加数据库表。
移去数据库表。
添加数据库表间的联接。
显示SQL窗口。
最大化上部分窗口。
确定查询去向
使用查询设计器创建查询
使用 " 查询设计器 " 创建前面 " 查询向导 " 创建查询例子，具体步骤如下：
（ 1 ）进入 " 查询设计器 " 窗口，添加 stud 表 ，如图所示。
（2 ）选择查询需要的字段
（ 3 ）设置查询条件
（ 4 ）保存查询
5.3.4 查询的运行与修改
1．查询的运行
运行查询的方法有以下5种：
① 在"查询设计器"窗口中，选择【查询】|【运行查询】命令。
② 在"查询设计器"窗口中，右击"查询设计器"窗口，选择快捷菜单中的【运行查询】命令。
③ 选择【程序】|【运行】命令。弹出"运行"对话框，在对话框中，选择所要运行的查询文件，单击"运行"按钮。
④ 在"项目管理器"窗口中，选择要运行的查询文件，单击右边的"运行"按钮。
⑤ 在"命令"窗口中，键入 DO <查询文件名> 。例如，DO 查询1.qpr。
2．查询的修改
修改可以用以下3种方法：
① 在"项目管理器"窗口中，选择要修改的查询文件，单击右边的"修改"按钮,进入"查询设计器"窗口中修改。
② 选择【文件】|【打开】命令，在"打开"对话框中，选择所要修改的查询文件，单击"确定"按钮，进入"查询设计器"窗口中修改。
③ 在命令窗口中，键入 MODIFY QUERY <查询文件名>。
3．查询去向的设置
单击"查询设计器"工具栏中的"查询去向"按钮或在系统菜单中单击【查询】|【查询去向】命令，弹出"查询去向"对话框，如图5-15 所示。其中共包含7个查询去向，各项的含义见表5-1
5.4.1 视图简介
视图是一个定制的虚拟逻辑表，视图中只存放相应的数据逻辑关系，并不保存表的记录内容，但可以在视图中改变记录的值，然后将更新记录返回到源表。
视图与查询在功能上有许多相似之处，但又有各自特点，主要区别如下：
功能不同：视图可以更新字段内容并返回源表，而查询文件中的记录数据不能被修改。 从属不同：视图不是一个独立的文件而从属于某一个数据库。查询是一个独立的文件，它不从属于某一个数据库。 访问范围不同：视图可以访问本地数据源和远程数据源，而查询只能访问本地数据源。 输出去向不同：视图只能输出到表中，而查询可以选择多种去向，如表、图表、报表、标签、窗口等形式。 使用方式不同：视图只有所属的数据库被打开时，才能使用。而查询文件可在命令窗口中执行。
5.4.2 本地视图的创建
1）视图向导
2）视图设计器
3）命令方式：CREATE SQL VIEW <视图名> [REMOTE] AS
示例：CREATE SQL VIEW ST1 AS SELECT * FROM 教学管理!学生表
5.4.3 视图的打开、关闭及显示
1）命令方式：USE [<视图名>]
BROWSE
说明：使用USE命令前，应先打开包含此视图的数据库。
示例：OPEN DATABASE 教学管理
USE 视图2
BROWSE
USE
2）菜单方式："数据库"菜单的"浏览"选项
程序设计:
命令方式 MODIFY COMMAND 〈程序文件名〉
菜单方式 打开 " 文件 " 菜单，选择 " 新建 " ；在 " 新建 " 窗口，选择 " 程序 " ，再选择 " 新建文件 "
程序文件的保存 程序文件的默认扩展名是 .PRG 。
程序文件的执行 Do 程序文件名 [ 运行 ] 按钮 或 ！
例 1 建立程序 CX ，功能为：查找 f:\jxgl 目录下的 XS 学生表中有无姓名为 " 王林 " 的同学，有，则将其姓名、学号显示在 VFP 的主屏幕上。
clear
Set talk off
Set defaut to f:\jxgl Sele 1
Use xs
Locate for xm= " 王林 "
found()
xm,xh
Use
Set talk on
return
——只有9个动词（select , drop, alter, create, insert, update ,delete, grant, revoke )
5.2.1 基本查询
【格式】SELECT [ALL | DISTINCT] <字段列表> FROM <表> [ORDER BY <字段名> [ASC/DESC] [,<字段> [ASC/DESC]],...]
【功能】无条件查询，将记录显示在浏览窗口中。
【说明】ALL： 显示全部查询记录，包括重复记录。
DISTINCT： 显示无重复结果的记录，将重复记录只保留一条。
字段列表：字段1，字段2 AS 别名，函数或表达式 AS 列名，......查询结果的输出列；如果将表中所有字段都选择输出，则用*代替；在函数或表达式中还可以用一些专用统计函数
FROM 表：设定查询的来源数据
ORDER BY：按指定的字段值大小对结果记录进行排序
SQL命令中常用的统计函数
AVG（字段名）——平均值 MIN（字段名）——最小值 MAX（字段名）——最大值 SUM（字段名）——和 COUNT（*或字段名）——满足条件的记录总数，字段名可以不写。
能进行统计的字段必须是表示数值的数据类型。用了统计函数后，视情况会将统计的记录压缩成一条或多条。
学号
姓名
性别
系名
籍贯
出生年月
是否党员
0207390101
刘中雨
女
信息管理系
北京
80/12/1
是
0207390103
林天力
男
信息管理系
南京
82/1/3
是
0207390104
王 平
男
信息管理系
北京
82/3/26
否
0207490101
章闻天
男
电子商务
无锡
81/6/4
否
0207390102
闻书敏
女
信息管理系
苏州
81/7/28
是
0207490104
于小丽
女
电子商务
南京
80/8/10
否
【例1】显示XS（学生信息数据表）中的所有记录。
SELECT * FROM XS
命令中的*表示输出显示所有的字段，数据来源是XS表
【例2】显示XS（学生信息数据表）中的所有同学的学号及与之对应的姓名，同时能去除重名。
SELECT DISTINCT 学号，姓名 FROM XS
【例3】按成绩从大到小显示CJ表中的所有记录，并将成绩一项乘以0.7。
SELECT XH，KCDH，CJ*0.7 AS 成绩 FROM CJ order by cj DESC
【例4】统计CJ表中所有同学的总成绩，平均成绩和总人数。。
SELECT sum(cj.cj) as 总成绩,avg(cj.cj) as 平均成绩, COUNT(*) AS RS FROM CJ
课堂思考题
【题1】显示XS表中所有的系名。
SELECT distinct ximing FROM XS
【题2】显示XS表中所有的班级号。
SELECT distinct substr(xh,1,8) as 班级号
FROM XS ORDER BY 1
5.2.2 带条件（WHERE）的查询语句
【格式】SELECT [ALL|DISTINCT |TOP N] <字段列表>
FROM <表> [WHERE <条件表达式> ]
ORDER BY 字段列表
【功能】从一个表中查询满足条件的数据。
【说明】TOP N ：显示满足条件的前几项
<条件表达式>由一系列用AND 或 OR 连接的条件表达式组成， 条件表达式可以为字段（表达式） 运算符 表达式（字段）的形式，其中可以使用以下常用的运算符
SQL 命令中常用的 运算符
操作符 比较关系 举例
= 相等 Xs.ximing= " 金融 "
= = 完全相等 Xs.ximing = = " 金融 "
LIKE 不精确匹配 Xs.xh LIKE " 03073901% "
> 、 >= 大于（等于） 分数 . 成绩 >60
<= 、 < 小于（等于） 分数 . 成绩 <=60
betwee n 指定字段在两实 例之间 Cj between 45 and 80
in 指定字段必须与 用逗号隔开的实 例集合中的一个 相匹配 cj in (50,60,70)
示例
【例1】显示XS表中所有男生记录的学号，姓名和性别
SELECT XH，XM，XB FROM XS WHERE XB="男"
【例2】显示XS表中出生日期在85年之间的学生的学号，姓名，出生日期。
SELECT XH，XM，CSRQ FROM XS ；
WHERE CSRQ BETWEEN {01/01/85} AND {12/31/85}
SELECT XH，XM，CSRQ FROM XS; *//另一种方法
WHERE year(CSRQ)=85
【例3】显示XS表中姓李的女学生的学号，姓名，出生日期。
SELECT XH，XM，CSRQ FROM XS;
WHERE XM LIKE "李%" AND XB="女"
【例4】查询CJ表中成绩名列前5名的学生的学号，成绩。
SELECT TOP 5 XH，CJ FROM CJ ;
ORDER BY CJ DESC
【例5】统计XS表中03073901班的人数。
Select count(*) as 03073901班人数 from xs； (*//SUBSTR（XH,1，8）=" 03073901")
where xh like ‘03073901%‘
SQL 命令中的分组与计算查询
【格式】SELECT [ALL | DISTINCT |TOP N] <字段列表>
FROM <表> [WHERE <条件>
[GROUP BY <分组字段列表>...]
[HAVING <过滤条件>]
[ORDER BY <排序项> [ASC | DESC]
分组就是将一组类似（根据分组字段的值）的记录压缩成一个结果记录，这样就可以完成基于一组记录的计算。其结果可以类比于唯一索引。[过滤条件] 对分组的结果根据条件(可以是来自于字段列表项中的选项，也可以是一个统计函数)进行记录组的过滤。注意：用于分组的可用字段不一定是已选定输出的字段列表中的一个字段，但分组字段不能是一个计算过的字段，如统计函数。 排序项必须是已选定输出的字段列表中的字段
Group by ximing
学号
姓名
性别
系名
籍贯
出生年月
是否党员
0207390101
刘中雨
女
信息管理系
北京
80/12/1
是
0207390103
林天力
男
信息管理系
南京
82/1/3
是
0207390104
王 平
男
信息管理系
北京
82/3/26
否
0207490101
章闻天
男
电子商务
无锡
81/6/4
否
0207390102
闻书敏
女
信息管理系
苏州
81/7/28
是
0207490104
于小丽
女
电子商务
南京
80/8/10
否
根据系名的值该学生表分为两种类型的记录，一个是系名为 " 信息 管理系 " 的记录，一个是系名为 " 电子商务 " 的记录，分组的结果是 两条记录：第 2 条和 4 条记录
示例
【例 1 】显示 XS 表中各班的总人数。
SELECT XIMING ，
COUNT(*) AS 总人数 ;
FROM XS GROUP BY XIMING
【例2】显示CJ表中平均分>80的同学
SELECT CJ.XH FROM CJ GROUP BY XH;
HAVING AVG(CJ)>80
ORDER BY 1
【例3】按学号从低到高，显示CJ表中每个学生所学课程数目，成绩总分，平均分
Select cj.xh,count(*) as 课程数目，
sum(cj.cj) as 成绩总分，avg(cj.cj) as 平均分
from cj
group by cj.xh
Order by xh
【例4】查询JS表中各系科教师的人数、工资总额和平均工资，并按平均工资降序排列。
SELECT XIMING,COUNT(*) AS 人数,SUM(ZGZ) AS 工资总额,AVG(zgz) as 平均工资 from js;
Group by ximing order by 4
*//?查询信息管理系教师的人数、工资总额和平均工资
SELECT XIMING,COUNT(*) AS 人数,SUM(ZGZ) AS 工资总额,AVG(zgz) as 平均工资 from js;
where ximing="信息管理系";
Group by ximing having ximing="信息管理系" order by 4
【例5】查询JS表中系平均工资>500元的各系科教师的人数、工资总额和平均工资，并按平均工资降序排列，查询结果中必须包括系名、系人数、工资总额和平均工资四列。
SELECT XIMING,COUNT(*) AS 人数,SUM(ZGZ) AS 工资总额,AVG(zgz) as 平均工资 from js;
Group by ximing having 平均工资>500;
order by 4 ????
5.2.3 SQL的复杂查询
1．连接查询
【说明】在一个数据库中的多个表之间一般都存在着某些联系，在一个查询语句中同时涉及到两个或两个以上的表时，这种查询称之为连接查询（也称为多表查询）。在多表之间查询必须处理表与表之间的连接关系。
【例1】查询并显示各个学生的学号，姓名，各科成绩。
SELECT xs.xh，xs.xm，cj.kcdh，cj.cj ;
FROM xs，cj ;
WHERE xs.xh=cj.xh
【例2】显示成绩在80至90之间的学号，课程名和成绩。
SELECT cj.xh，kc.kcm，cj.cj
FROM cj,kc
WHERE cj.kcdh=kc.kcdh and
cj.cj BETWEEN 80 AND 90
2.示例
例1】查询并显示各个学生的学号，所学课程及课程成绩。
SELECT CJ.XH，KC.KCM，CJ.CJ ;
FROM KC,CJ ;
WHERE KC.KCDH=CJ.KCDH
内连接方式
SELECT CJ.XH，KC.KCM，CJ.CJ ;
FROM KC INNER JOIN CJ ON KC.KCDH=CJ.KCDH
【例2】显示计算机系学生的课程代号为"A001"的考试成绩。
SELECT XS.XH，XS.XIMING，XS.XM,cJ.KCDH，CJ.CJ;
FROM XS INNER JOIN CJ ON XS.XH=CJ.XH;
wHERE XS.XIMING="计算机系" AND CJ.KCDH="A001"
【例3】查询王林同学所选课程的课程数
Select xs.xm,count(*) from xs inner join cj on xs.xh=cj.xh where xs.xm=‘王林'?
【例4】显示CJ表中每门课程的课程名，学时，必修课情况和该门课程的最高分，最低分和平均分
Select kc.kcm,kc.kss,max(cj.cj),min(cj.cj),avg(cj.cj)
from kc inner join cj on kc.kcdh=cj.kcdh
group by kc.kcdh
【例5】查询有不及格课程成绩的同学的学号和姓名，有多门课程不及格的学生只显示一次
Select distinct cj.xh,xs.xm ；
from xs inner join cj on xs.xh=cj.xh；
where cj.cj<60；
order by xs.xh
或 Select distinct cj.xh,xs.xm from xs.cj；
where xs.xh=cj.xh and cj.cj<60 order by xs.xh
【例6】查询信息管理系中所有成绩总分在480分以上，并且各门课程的成绩不低于70分的学生。要求在查询结果中包含学生的学号、姓名、总分、平均分和最低分，并按总分从高到低排列。
Select xs.xh,xs.xm,xum(cj.cj) as 总分,avg(cj.cj) as 平均成绩，min(cj.cj) as 最低分；
from xs inner join cj on xs.xh=cj.xh ；
where xs.ximing=‘信息管理系' ；
group by xs.xh；
having 总分>=480 and 最低分>=70；
order by 3 desc????
课堂思考题 查询选修人数超过40人的课程名，学分，必修课情况和该门课程的最高分和最低分，并按最高分从低到高排序。
Select kc.kcm,kc.xf,kc.bxk,max(cj.cj),min(cj.cj);
from kc inner join cj on kc.kcdh=cj.kcdh;
group by kc.kcdh having count(*)>40 order by 4
列出所有选修学分超过21个学分（考试成绩超过65分的该门课程就可以获得学分）的学生的学号以及所选修的总学分。
Select cj.xh,sum(kc.xf);
from kc inner join cj on kc.kcdh=cj.kcdh where cj.cj>=65;
group by cj.xh having sum(kc.xf)>21 order by 2
查询选修人数超过40人的课程名，学分，必修课情况和该门课程的最高分和最低分，并按最高分从低到高排序。
Select kc.kcm,kc.xf,kc.bxk,max(cj.cj),min(cj.cj);
from kc inner join cj on kc.kcdh=cj.kcdh;
group by kc.kcdh having count(*)>40 order by 4
列出所有选修学分超过21个学分（考试成绩超过65分的该门课程就可以获得学分）的学生的学号以及所选修的总学分。
Select cj.xh,sum(kc.xf);
from kc inner join cj on kc.kcdh=cj.kcdh where cj.cj>=65;
group by cj.xh having sum(kc.xf)>21 order by 2
3．查询去向
默认情况下，查询输出到一个浏览窗口 INTO ARRAY 数组名：将查询结果保存到一个数组中。 INTO CURSOR < 临时表名>：将查询结果保存到一个临时表中。 TO SCREEN：将查询结果保在屏幕上显示。
SELECT-SQL 命令主要组成部分
定义数据源 指定数据源表 FROM 子句
确定源表间的联 接 INNER JOIN ... ON ... 子句
定义结果 筛选源表记录 WHERE 子句
指定输出字段 字段、函数和表达式的列 表或 *
指定输出类型 INTO 子句和 TO 子句
定义记录的分组 GROUP BY 子句
指定结果顺序 ORDER BY 子句
筛选结果记录 HAVING 子句
指定有无重复记 录 ALL/DISTINCT
指定结果的范围 TOP nExpr[PERCENT]
习题:
针对于教学管理信息系统，进行以下查询：
1.查询男教师和女教师的工资总和以及平均工资??
2.统计9501班的男同学的人数??
3.查询65年以前出生的女教师的姓名、工龄??
4.显示JS表中每个教师的工号、姓名、出生日期和开始工作年份（提示：开始工作年份=当前年份-工龄）；
5.显示CJ表中，课程代号为"02"的学生成绩的前三名；
6.显示JS表中各系科教师的人数，工资总额和平均工资。
7.统计选修'VFP5'的学生总数?
8.查询课程名为'VFP5'的学时，必修课情况和该门课程的最高分、最低分和平均分
9.查询每个教师所任课程的课程代号、课程名和学分数以及工号和专业代号??
答案:
1.Select js.xh,sum(js.jbgz) as 总工资,avg(js.jbgz) as 平均工资 from jxsj!js group by js.xb
2.Select count(*) as 男同学人数 from xs where xs.xh like ‘9501%' and xs.xb=‘男'
3.Select js.xm,js.gl,js.csrq from jxsj!js where year(js.csrq)<1965
4.Select kc.kcm,count(*) as 选修人数 from kc inner join cj on kc.kcdh=cj.kcdh where kc.kcm='VFP5'
或Select kc.kcm,count(*) from kc,cj where kc.kcdh=cj.kcdh .and. kc.kcm='VFP5'
5.Select kc.kcm,kc.kss,max(cj.cj),min(cj.cj),avg(cj.cj)
from kc inner join cj on kc.kcdh=cj.kcdh
group by kc.kcdh having kc.kcm='VFP5'
或Select kc.kcm,kc.kss,max(cj.cj),min(cj.cj),avg(cj.cj)from kc inner join cj on kc.kcdh=cj.kcdh where kc.kcm='VFP5' group by kc.kcdh
6.Select rk.gh,rk.zydh,kc.kcm,kc.kss,kc.bxk from rk inner join kc on kc.kcdh=rk.kcdh
3．查询去向
默认情况下，查询输出到一个浏览窗口INTO ARRAY 数组名：将查询结果保存到一个数组中。INTO CURSOR < 临时表名>：将查询结果保存到一个临时表中。TO SCREEN：将查询结果保在屏幕上显示。
SELECT-SQL 命令主要组成部分
定义数据源 指定数据源表 FROM 子句
确定源表间的联 接 INNER JOIN ... ON ... 子句
定义结果 筛选源表记录 WHERE 子句
指定输出字段 字段、函数和表达式的列 表或 *
指定输出类型 INTO 子句和 TO 子句
定义记录的分组 GROUP BY 子句
指定结果顺序 ORDER BY 子句
筛选结果记录 HAVING 子句
指定有无重复记 录 ALL/DISTINCT
指定结果的范围 TOP nExpr[PERCENT]
习题:
针对于教学管理信息系统，进行以下查询：
1.查询男教师和女教师的工资总和以及平均工资??
2.统计9501班的男同学的人数??
3.查询65年以前出生的女教师的姓名、工龄??
4.显示JS表中每个教师的工号、姓名、出生日期和开始工作年份（提示：开始工作年份=当前年份-工龄）；
5.显示CJ表中，课程代号为"02"的学生成绩的前三名；
6.显示JS表中各系科教师的人数，工资总额和平均工资。
7.统计选修'VFP5'的学生总数?
8.查询课程名为'VFP5'的学时，必修课情况和该门课程的最高分、最低分和平均分
9.查询每个教师所任课程的课程代号、课程名和学分数以及工号和专业代号??
答案:
1.Select js.xh,sum(js.jbgz) as 总工资,avg(js.jbgz) as 平均工资 from jxsj!js group by js.xb
2.Select count(*) as 男同学人数 from xs where xs.xh like ‘9501%' and xs.xb=‘男'
3.Select js.xm,js.gl,js.csrq from jxsj!js where year(js.csrq)<1965
7.Select kc.kcm,count(*) as 选修人数 from kc inner join cj on kc.kcdh=cj.kcdh where kc.kcm='VFP5'
或Select kc.kcm,count(*) from kc,cj where kc.kcdh=cj.kcdh .and. kc.kcm='VFP5'
8.Select kc.kcm,kc.kss,max(cj.cj),min(cj.cj),avg(cj.cj)
from kc inner join cj on kc.kcdh=cj.kcdh
group by kc.kcdh having kc.kcm='VFP5'
或Select kc.kcm,kc.kss,max(cj.cj),min(cj.cj),avg(cj.cj)from kc inner join cj on kc.kcdh=cj.kcdh where kc.kcm='VFP5' group by kc.kcdh
9. Select rk.gh,rk.zydh,kc.kcm,kc.xfs,kc.kcdh from rk inner join kc on kc.kcdh=rk.kcdh
5.3 查询设计器及查询文件
——一个用来保存SQL语句的文件（*。QPR），查询文件的运行结果是一个基于表和视图的动态的数据集合。
5.3.1 查询文件的设计过程
在创建查询文件时，通常可以遵循以下六个步骤来创建查询。
（1）用"查询向导"或"查询设计器"创建查询。
（2）选择在查询结果中需要的字段。
（3）设置查询记录的条件。
（4）设置排序及分组条件来组织查询结果。
（5）选择查询输出类别，可以是报表、表文件、图表、浏览窗口和表文件等。
（6）运行此查询。
5.3.2 利用"查询向导"创建查询
利用"查询向导"创建查询的步骤如下：
（1）进入"查询向导"
① 选择【工具】|【向导】|【查询】命令。
② 选择【文件】|【新建】命令，进入"新建"对话框，选择"查询"单选按钮，单击"向导"按钮。
③ 在"项目管理器"窗口中，选择"数据"选项卡，选中"查询"，单击"新建"按钮，出现"新建查询"对话框，单击"查询向导" 。
（2）选择查询结果中需要的字段
（3）设置查询条件
（4）设置排序字段
（5）设置记录输出范围
（6）保存查询
5.3.3 利用"查询设计器"创建查询
进入"查询设计器"窗口
（1） 选择【文件】│【新建】命令，进入"新建"对话框，选择"查询"单选按钮，单击"新建"按钮；
（2） 在"项目管理器"窗口中，选择"数据"选项卡，选中"查询"文件类型，单击"新建"按钮，出现"新建查询"对话框，单击"新建查询"按钮
"查询设计器工具栏"各按钮的功能如下：
添加数据库表。
移去数据库表。
添加数据库表间的联接。
显示SQL窗口。
最大化上部分窗口。
确定查询去向
使用查询设计器创建查询
使用 " 查询设计器 " 创建前面 " 查询向导 " 创建查询例子，具体步骤如下：
（ 1 ）进入 " 查询设计器 " 窗口，添加 stud 表 ，如图所示。
（2 ）选择查询需要的字段
（ 3 ）设置查询条件
（ 4 ）保存查询
5.3.4 查询的运行与修改
1．查询的运行
运行查询的方法有以下5种：
① 在"查询设计器"窗口中，选择【查询】|【运行查询】命令。
② 在"查询设计器"窗口中，右击"查询设计器"窗口，选择快捷菜单中的【运行查询】命令。
③ 选择【程序】|【运行】命令。弹出"运行"对话框，在对话框中，选择所要运行的查询文件，单击"运行"按钮。
④ 在"项目管理器"窗口中，选择要运行的查询文件，单击右边的"运行"按钮。
⑤ 在"命令"窗口中，键入 DO <查询文件名> 。例如，DO 查询1.qpr。
2．查询的修改
修改可以用以下3种方法：
① 在"项目管理器"窗口中，选择要修改的查询文件，单击右边的"修改"按钮,进入"查询设计器"窗口中修改。
② 选择【文件】|【打开】命令，在"打开"对话框中，选择所要修改的查询文件，单击"确定"按钮，进入"查询设计器"窗口中修改。
③ 在命令窗口中，键入 MODIFY QUERY <查询文件名>。
3．查询去向的设置
单击"查询设计器"工具栏中的"查询去向"按钮或在系统菜单中单击【查询】|【查询去向】命令，弹出"查询去向"对话框，如图5-15 所示。其中共包含7个查询去向，各项的含义见表5-1
5.4.1 视图简介
视图是一个定制的虚拟逻辑表，视图中只存放相应的数据逻辑关系，并不保存表的记录内容，但可以在视图中改变记录的值，然后将更新记录返回到源表。
视图与查询在功能上有许多相似之处，但又有各自特点，主要区别如下：
功能不同：视图可以更新字段内容并返回源表，而查询文件中的记录数据不能被修改。 从属不同：视图不是一个独立的文件而从属于某一个数据库。查询是一个独立的文件，它不从属于某一个数据库。 访问范围不同：视图可以访问本地数据源和远程数据源，而查询只能访问本地数据源。 输出去向不同：视图只能输出到表中，而查询可以选择多种去向，如表、图表、报表、标签、窗口等形式。 使用方式不同：视图只有所属的数据库被打开时，才能使用。而查询文件可在命令窗口中执行。
5.4.2 本地视图的创建
1）视图向导
2）视图设计器
3）命令方式：CREATE SQL VIEW <视图名> [REMOTE] AS
示例：CREATE SQL VIEW ST1 AS SELECT * FROM 教学管理!学生表
5.4.3 视图的打开、关闭及显示
1）命令方式：USE [<视图名>]
BROWSE
说明：使用USE命令前，应先打开包含此视图的数据库。
示例：OPEN DATABASE 教学管理
USE 视图2
BROWSE
USE
2）菜单方式："数据库"菜单的"浏览"选项
程序设计:
命令方式 MODIFY COMMAND 〈程序文件名〉
菜单方式 打开 " 文件 " 菜单，选择 " 新建 " ；在 " 新建 " 窗口，选择 " 程序 " ，再选择 " 新建文件 "
程序文件的保存 程序文件的默认扩展名是 .PRG 。
程序文件的执行 Do 程序文件名 [ 运行 ] 按钮 或 ！
例 1 建立程序 CX ，功能为：查找 f:\jxgl 目录下的 XS 学生表中有无姓名为 " 王林 " 的同学，有，则将其姓名、学号显示在 VFP 的主屏幕上。
clear
Set talk off
Set defaut to f:\jxgl Sele 1
Use xs
Locate for xm= " 王林 "
found()
xm,xh
Use
Set talk on
return