查询精解

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