第4章 视图、查询与SQL语句练习一 视图与查询
【实验目的】
1．使用视图向导创建本地视图
2．利用视图设计器创建视图
3．利用查询向导创建查询
【知识要点】
一、视图
1．视图是观察表记录的一个窗口，使用视图可以将表中一组记录提出来组成一个虚拟表，还可改变这些记录的值，并将更新结果送回原表。
视图依赖于某数据库中的表，只有打开此数据库，才能创建和使用视图。
视图有本地视图和远程视图两类。
2．使用本地视图向导创建本地视图
(1) 执行“工具／向导／全部”命令，在“向导选取”对话框中选择“本地视图向导”。
(2) 打开数据库文件，在“数据库设计器”窗口中执行“新建／视图”命令，单击“向导”按钮，进入“本地视图向导”窗口。
（3）使用本地视图向导
① 从“字段选取”窗口中选定一个表及所需字段，将其添加到“选定字段”框中
② 从“筛选记录”窗口中指定记录输出的条件
③ 从“排序记录”窗口中确定视图中记录显示的顺序
④ 从“限制记录”窗口中选择视图中记录显示的数目
⑤ 单击“完成”钮，输入视图名
3．使用远程视图向导创建远程视图（即用当前数据库之外的数据源建立视图）
调用远程视图向导和创建远程视图的方法与创建本地视图差不多，只是在创建时要先选择有效的数据源，它可以是在机器上注册的ODBC（开放式数据库连接）数据源或用连接设计器设计定义的连接。
4．使用视图设计器创建视图(要先打开一个数据库文件)
(1) 执行“文件／新建／视图”命令，单击“新建文件”按钮，进入“视图设计器”窗口。
① 从“添加表或视图”对话框中选定表(或视图)将其添加到“视图设计器”中。可以通过“其它”钮，选择自由表。
②“联接”选项（联接两个表）：
若添加了两个表，系统会自动给出两表相应字段，建立联接条件
③“字段”选项（指定输出的字段）,
从“可用字段”框中选择字段－“添加”到“选定字段”框
④“筛选”选项（指定记录输出的条件）,
在“字段名”栏中选择被筛选的字段（不能选通用型或备注型）
在“条件”栏中选定用于比较的类型
在“实例”栏中输入比较条件
（若查字符型数据时，要忽略大小写）选中“大小写”
（对于多个条件）在“逻辑”栏中添加AND或OR－（若用逻辑取反操作，即与条件相反的操作）选中“否”
⑤“排序依据”选项（确定记录输出时的顺序）：
从“选定字段”框中选择字段－“添加”到“排序条件”框－在“排序选项”框中确定升（降）序
⑥“分组依据”选项（按某字段(即某特征)分类，然后对字段中特征相同的数据进行统计计算，如求和、平均值、个数、最大值、最小值等）,
在(“字段”选项的“函数与表达式”框中）输入用于对某字段计算的函数表达式－“添加”到“选定字段”框（则多一个存放计算结果的字段）；
在(“分组依据”选项）“可用字段”框中选择用于分组的字段－“添加”到“分组字段”框中；
在“分组依据”选项的“满足条件”栏中）输入对字段计算的条件（类似“筛选”设置）
⑦,更新条件”选项（决定视图中修改数据传回数据源中表的方法和要求）：
在“字段名”框中选定可更新的字段(“钥匙”表示关键字段，最好不要更新；“铅笔”表示可更新的字段)
选择“发送SQL更新”项(否则修改内容不会传回数据源)
⑧“杂项”选项（对查询记录输出作限制）：
选“无重复记录”，则不输出重复记录
选“查询交叉表”，则对三个字段，用列头、行头、数据顶的形式显示查询结果
选“列在前面的记录”，可确定输出记录的个数
5．运行视图（一定要先打开相关的数据库或表）
(1) USE 视图名
BROWSE
(2) 双击数据库设计器中视图的图标
(3) 单击“视图设计器”工具栏中“运行”钮
6．修改视图(修改视图要在“视图设计器”中 进行，因此要先打开相关的数据库)
(1) 右击视图图标，选“修改”
(2) MODIFY VIEW 视图名二、查询
1．查询可从指定表或视图中找出符合条件的记录。它与视图不同的地方在于查询可作为一个文件单独存在，而视图只能存在于数据库中；查询的数据只能观看不能修改，而视图中数据可以修改并传回到原数据源中。
2．启动“查询设计器”
,文件/新建”－（类型）选“查询”－“新建文件”－选定表或视图－“添加”到“查询设计器”
3．建立查询：（用“查询设计器”建立查询的操作与用“视图设计器”建立视图类似，它还少一个“更新条件”）
（1）“联接”选项（联接两个表）：
（2）“字段”选项（指定输出的字段）,
（3）“筛选”选项（指定记录输出的条件）,
（4）“排序依据”选项（确定记录输出时的顺序）：
（5）“分组依据”选项（按某字段(即某特征)分类，然后对字段中特征相同的数据进行统计计算，如求和、平均值、个数、最大值、最小值等）,
（6）“杂项”选项（对查询记录输出作限制）：
4．使用查询
（1）（“查询设计器”）“查询/运行查询”
（2）“程序/运行”－选定查询文件－“运行”
（3）DO <查询文件名.QPR >
【实验内容】
１．用本地视图向导为“学生表”建立本地视图“学生表视图”，视图中包含“学号”、“班级”、“姓名”、“性别”、“入学成绩”五个字段内容，并要求只显示男生的记录
2．用视图设计器为“学生表”建立本地视图“学生视图2”，视图中包含“学号”、“班级”、“姓名”、“性别”、“入学成绩”五个字段内容，并要求只显示男生的记录
3．用查询向导建立查询“学生成绩查询”，以查询Xsda和Xscj表中数学系，且没有补考的学生的学号，姓名，系别，各科课程的考试成绩。
4．用“查询设计器”为“学生表”和“成绩表”这两个表建立查询“学生成绩查询2”，以显示计算机班男生的学号、班级、姓名、入学成绩、课程号、成绩
【高手进阶】
1．用“查询设计器”为“学生表”和“成绩表”这两个表建立查询“学生成绩查询3”，以查询各班学生考试平均成绩
2．用“查询设计器”为“学生表”建立姓名、班级和入学成绩的交叉表查询。
【综合测试】( 5分钟 )
根据“学生表”和“成绩表”，建立一个多表查询，完成查询“课程号”为1011的学生成绩信息。
【实验过程】
１．用本地视图向导为“学生表”建立本地视图“学生表视图”，视图中包含“学号”、“班级”、“姓名”、“性别”、“入学成绩”五个字段内容，并要求只显示男生的记录。
［解答］使用视图向导创建视图，要先打开一个数据库文件。
（1）启动本地视图向导执行“工具／向导／全部”命令，在“向导选取”对话框中选择“本地视图向导”。
［或：打开数据库文件，在“数据库设计器”窗口中执行“新建／视图”命令，单击“向导”按钮，进入“本地视图向导”窗口］
(2) 选定数据库“学生成绩管理”中的“学生表”，从“字段选取”窗口中选定所需字段，将其添加到“选定字段”框中
（3）从“筛选记录”窗口中指定记录输出的条件［没有就不指定。这里条件为显示男生］
（4）从“排序记录”窗口中确定视图中记录显示的顺序［没有就不指定。这里按学号升序排列］
(5) 从“限制记录”窗口中选择视图中记录显示的数目(若无特殊要求，就取默认值)
（6）单击“完成”钮，输入视图名“学生视图”，单击“确定”钮。则可将此视图保存到当前数据库中。
2．用视图设计器为“学生表”建立本地视图“学生视图2”，视图中包含“学号”、“班级”、“姓名”、“性别”、“入学成绩”五个字段内容，并要求只显示男生的记录。
［解答］使用视图设计器创建视图，要先打开一个数据库文件。
(1) 执行“文件／新建／视图”命令，单击“新建文件”按钮，进入“视图设计器”窗口。
① 从“添加表或视图”对话框中选定表(或视图)将其添加到“视图设计器”中。这时还可以通过“其它”钮，选择自由表。
②“联接”标签（联接两个表用的，这里只有一个表，因此不必管它）：
③“字段”标签（指定输出的字段）,
从“可用字段”框中选择“学号”、“班级”、“姓名”、“性别”、“入学成绩”五个字段 →逐一“添加”到“选定字段”框
④“筛选”标签（指定记录输出的条件：只显示男生）,
在“字段名”栏中选择被筛选的字段：“性别”（不能选通用型或备注型）
在“条件”栏中选定用于比较的类型：“=”
在“实例”栏中输入比较条件：“男”
⑤“排序依据”标签（确定记录输出时的顺序）：
从“选定字段”框中选择字段：如“学号”－“添加”到“排序条件”框－在“排序选项”框中确定升（降）序
⑥“分组依据”标签（按某字段(即某特征)分类，然后对字段中特征相同的数据进行统计计算，如求和、平均值、个数、最大值、最小值等。这里不用，因此不理它）,
⑦“更新条件”选项（决定视图中修改数据传回数据源中表的方法和要求）：
1）“表”框：指定对哪些表可以更新
2）“字段名”框,（设置关键字段）单击此框中字段旁的“关键”（钥匙）列
（指定可更新字段）单击此框中字段旁的“更新”（笔）列
3）选“发送SQL更新”：决定是否更新源表中数据，至少要设置一个关键字段
4）“使用更新”框,（确定如何进行更新）
选“SQL DELETE 然后 INSERT”：先删除原记录，再插入新记录选“SQL UPDATE”：直接修改原始记录
5）“SQL WHERE 子句包括”框：（在多用户环境中使用。更新记录前，检测是否其他用户正在修改）
选“关键字段”：若源表中关键字段已改，则不能更新
选“关键字段和可更新字段”：若源表中关键字段和可更新字段已改，则不能更新
选“关键字段和已修改字段”：若源表中任何字段已改，则不能更新从“钥匙”栏选定“学号”这个关键字段；在“铅笔”栏选定可更新的字段，如“姓名”、“性别”、“入学总分”等。注意还要选定“发送SQL更新”项。
⑧“杂项”标签（对查询记录输出作限制。取默认值，也是不理）：
⑨ 用“文件∕保存”命令（或Ctri + W）保存视图，取名“学生视图2”
⑩ 运行视图：双击数据库设计器中视图的图标、或单击“视图设计器”工具栏中“运行”钮。
3．用查询向导建立查询“学生成绩查询”，以查询Xsda和Xscj表中数学系，且没有补考的学生的学号，姓名，系别，各科课程的考试成绩
［解答］
（1）启动“查询设计器”，添加Xsda和Xscj表；
（2）从“字段选取”选项中选择学号、系别、姓名、高数、大语、邓论、英语、计算机等字段；
（3）在“为表建立关系”选项中为两表建立联接：在步骤2中按“添加”钮；
（4）选取匹配字段：因要显示两表中相应记录的值，故选取“仅包含匹配的行“项；
（5）从“筛选记录”选项中指定记录输出的条件,系别＝数学系
（6）从“排序记录”选项中指定按学号升序显示
（7）在“限制记录”选项中，取默认值；
（8）按“完成”钮，保存查询文件“学生成绩查询”；
（9）运行查询，用“程序/运行”－选定查询文件“学生成绩查询”－按“运行”钮（或在命令窗口中输入：DO 学生成绩查询.QPR。一定要带扩展名.QPR）
4．用“查询设计器”为“学生表”和“成绩表”这两个表建立查询“学生成绩查询2”，以显示计算机班男生的学号、班级、姓名、入学成绩、课程号、成绩
［解答］
(1) 执行“文件／新建／查询”命令，单击“新建文件”按钮，进入“查询设计器”窗口。
① 在查询设计器中添加两个表：学生表和成绩表，并建立联接；
② 在“字段”标签中，添加要显示的字段：学号、班级、姓名、入学成绩、课程号、成绩；
③ 在“筛选”标签中，确定显示条件：数学系男生(注意：要按下“逻辑”下的“AND”项)
④ 对“排序依据”、“分组依据”和“杂项”，可以暂不理会；
⑤ 保存：用“文件∕保存”或Ctri + W，保存查询文件为“学生成绩查询2”；
⑥ 运行查询：用“程序/运行”－选定查询文件“学生成绩查询2”－按“运行”钮（或在命令窗口中输入：DO 学生成绩查询2.QPR
【高手进阶解答】
1．用“查询设计器”为“学生表”和“成绩表”这两个表建立查询“学生成绩查询3”，以查询各班学生考试平均成绩
［解答］此题目要用“分组论据”。按某字段(即某特征)分类，然后对字段中特征相同的数据进行统计计算，如求和、平均值、计数、最大值、最小值等，它分为以下三步,
①（“字段”选项的“函数与表达式”框中）输入用于对某字段计算的函数表达式－“添加”到“选定字段”框（则多一个存放计算结果的字段）
②（“分组依据”选项）在,可用字段”框中选择用于分组的字段－“添加”到“分组字段”框
③（“分组依据”选项的“满足条件”栏中）输入对字段计算的条件（类似“筛选”的设置）
建立查询时在查询设计器中添加两个表、在“字段”标签中，添加要显示的字段（只要一个字段：班级）、在“筛选”标签中，确定显示条件（这里没有条件）、“排序依据”、“分组依据”和“杂项”等与上一题的操作基本一样；
1）在“分组依据”标签中，指定按班级分组（将“班级”添加到“分组字段”框中；
2）在“字段”选项的“函数与表达式”框中，输入用于对成绩表的成绩字段计算的函数表达式“AVG(成绩)”，按“添加”钮，将其添加到“选定字段”框（则在显示时会多一个存放计算平均成绩的字段）
完成后保存运行（因没有对字段进行计算的条件，所以“分组依据”选项的“满足条件”栏中可不输入筛选条件）。
2．用“查询设计器”为“学生表”建立姓名、班级和入学成绩的交叉表查询。
（1）打开“查询设计器”，向设计器添加“学生表”。
（2）在“字段”标签中，添加要显示的字段：姓名、班级、入学成绩。
注：只能添加三个字段，第一字段为“行”数据源、第二字段为“列”数据源、第三字段为“数据”数据源。
（3）在“杂项”标签中，选择“交叉数据表”项
（4）完成后保存运行，结果如下图。
【综合测试解答】
根据“学生表”和“成绩表”，建立一个多表查询，完成查询“课程号”为1011的学生成绩信息。
［解答］
（1）打开“查询设计器”。将“学生表”、“成绩表”加入到查询设计器。并建立联接；
（2）将学生表中的学号、姓名、班级和成绩表中的课程号、成绩等字段添加到“可用字段”中；
（3）在“筛选”选项卡中设置筛选条件：成绩表的课程号＝1011；
（4）在“排序依据”选项卡中，选择“成绩表,成绩”以“降序”方式添加到,排序条件”中。
（5）此题不需要“分组依据”，“杂项”取默认值；
（6）单击鼠标右键，选择“运行查询”，在“浏览”窗口中显示查询结果。
练习二 SQL查询功能（一）
【实验目的】
掌握SQL查询功能语句SELECT的操作；
熟练掌握单表查询；
熟练掌握带条件查询；
熟练掌握多表连接查询。
【知识要点】
一、关系数据库标准语言SQL
SQL是一个一体化、非过程化、功能强大的关系型数据库应用语言。它包括查询、定义（创建、删除和修改表）、操纵（插入、更新和删除记录）和控制四个方面的功能。
SQL功能
命令动词
数据查询
SELECT
数据定义
CREATE、DROP、ALTER
数据操纵
INSERT、UPDATE、DELETE
数据控制
GRANT、REVOKE
二、SQL查询：
查询是SQL的核心，SQL查询命令称为SELECT命令
1．单表查询
（1）基本查询语句（无条件查询）
SELECT [ALL/DISTINCT] 字段名表 FROM 表名
说明：
用“字段名1，字段名2，…”来指定要显示的字段、若用“*”代替，则表示选取所有字段；
有DISTINCT，则不显示相同的记录、用ALL，则显示全体记录，包括重复记录；
用“FROM 表名”指明要从哪个（些）表中查询数据。
（2）带条件（WHERE）的查询语句
SELECT [ALL/DISTINCT] 字段名表 FROM 表名 WHERE 条件式说明：
用“条件式”选择满足条件的记录，使用条件是查询的关键。
2．多表连接查询
SELECT [ALL/DISTINCT] 字段名表 FROM 表1[,表2…] WHERE 条件式说明：在条件式中一定要输入两表联接条件：“表1.某字段=表2.某字段”
要求这几个表间至少有一个相同的字段，这个相同字段用来作连接条件
【实验内容】
一、SQL查询语句SELECT
1．单表查询
（在“成绩表”中）
（1）显示表中的所有记录；
（2）显示表中的学号、课程号、成绩三个字段；
（3）查询表中有多少个学号（即不显示相同的学号）；
（4）查询表中成绩不及格的学生记录；
（在“学生表”表中）
（5）查询表中85年以后出生，且入学成绩大于560分的学生记录；
（6）查询表中85年出生的学生的学号，姓名，出生日期；
2．多表连接查询 ［注意连接条件的使用］
（1）从学生表和成绩表中查询学生记录
（2）从学生表和成绩表中查询成绩不及格的学生姓名、课程号、成绩
（3）2002年计算机等级考试［二级（数据库）上机考试试卷（有修改）］
1．在某商场管理系统中，以下2个数据表反映一个月内有关手机销售、库存情况。
（1）商品月库存表（Stockll.dbf）:
商品代码 品名 型号 单价 库存数量
2011 诺基亚 N8250 2250.00 16
2120 诺基亚 3310 990.00 18
4025 摩托罗拉 8088 2090.00 12
3122 爱立信 T29 1380.00 14
5203 西门子 2118 1470.00 18
（2）销售明细表（Sell.dbf）:
商品代码 日期 销售数量 营业员 销售金额
2120 2001-11-12 5 方虹
4025 2001-11-12 6 方虹
3122 2001-11-13 4 李之和
2120 2001-11-14 10 张广河显示营业员方虹本月销售商品、数量（这两个表已经做好，在配套材料“表”文件夹中）。
二、SQL综合练习：
1,下列语句的作用是什么？上机操作后给出答案。
（1）SELECT * FROM 学生表
（2）SELECT 学号,姓名,出生日期 FROM 学生表 WHERE 出生日期;
BETWEEN CTOD("01/01/84") AND CTOD("12/31/84")
（3）SELECT 姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND 成绩<60
（4）SELECT 学生表.学号,班级,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 成绩表.学号=学生表.学号 AND 成绩>60 ;
AND ( 班级="计算机" OR 班级="金融")
2.用数据库命令做的题，现在用SQL语句来做：
（1）顺序查询,
① 在学生表中找出85年出生的学生记录
SET TALK OFF
USE 学生表
LOCATE FOR SUBSTR(DTOC(出生日期),7,2) =”85”
DO WHILE NOT EOF()
DISPLAY
CONTINUE
ENDDO
（2）建立关联,
① 将１区中的当前表“Xsda”与２区中的表“Xscj”按关键字段“学号”建立关联，并浏览学号、姓名和英语及计算机的成绩
( 注意：要先在不同的区中打开这两个表，并要对子表建立索引－对关键字“学号”)
SELECT 1 && 指定工作区1为当前工作区
USE Xsda && 在工作区1中打开Xsda表
SELECT 2 && 指定工作区２为当前工作区
USE Xscj && 在工作区２中打开Xscj表
INDEX ON 学号 TAG XH && 为成绩表（对学号）建立索引（名叫XH）
SELECT 1 && 指定工作区1为当前工作区
SET RELATION TO 学号 INTO 2
&& 按“学号”对２区（的成绩表）建立关联
BROWSE FIELDS 学号,姓名,B.英语,B.计算机
&& 子表记录前要加前缀：“别名-->”或“别名．”
【高手进阶】
嵌套查询：
将一个查询的结果，作为另一个查询的条件，这种查询中带查询，就是嵌套查询。
例1：从学生表和成绩表中查学生迪来的成绩和课程号
［解答］SELECT 课程号,成绩 FROM 成绩表 WHERE 学号= ;
(SELECT 学号 FROM 学生表 WHERE 姓名=”迪来”)
先从内层查询“SELECT 学号 FROM 学生表 WHERE 姓名="迪来"”，查出“迪来”的“学号”是05011013 ；然后从外层查询“SELECT 课程号,成绩 FROM 成绩表 WHERE 学号 = 05011013”,查学号是05011013的学生的课程号和成绩。
（1）从Xsda表中，查询所有与“汪洋”籍贯相同的学生姓名
（2）如果不用嵌套查询，可不可以？
例2：从学生表和成绩表中查询成绩不及格的学生姓名。
［解答］SELECT 姓名 FROM 学生表 WHERE 学号 IN (SELECT 学号 FROM 成绩表 WHERE 成绩<60)
内层查询“SELECT 学号 FROM 成绩表 WHERE 成绩<60”，查出成绩不及格的学生的学号，由于这些学号不只一个，所以不能用等号，而必须采用谓词IN，IN的含义是属于，即可等于这许多学号中的任一个，则外层查询就是查询这些学号的学生的姓名。
（1）从学生表和成绩表中查询计算机班的学生的各科成绩。
（2）从学生表和成绩表中查询0502系科学生的学号，课程号和成绩。
2．从键盘输入学号，在“成绩表”、“学生表”表中查询显示该学号学生的姓名及各门课程的成绩。
以前数据库命令，先建立关联，再查询（见下面程序）。现在用SQL语句，该如何写？
SET TALK OFF
SELECT 1
USE 学生表
INDEX ON 学号 TAG Xx
SELECT 2
USE 成绩表
SET RELATION TO 学号 INTO A
ACCEPT,请输入学生学号：” TO Xh
LOCATE FOR 学号 = Xh
DO WHILE NOT EOF()
DISPLAY 学号,学生表.姓名,课程号,成绩
CONTINUE
ENDDO
RETURN
【综合测试】( 5分钟 )
在学生表和成绩表中查找姓名为“王二春”的记录，若他有一科的成绩大于80分，则显示他的学号、姓名、课程号和成绩。
【实验过程】
一、SQL查询语句SELECT
1．单表查询
（在“成绩表”中）
（1）显示表中的所有记录；
［操作］SELECT * FROM 成绩表
（2）显示表中的学号、课程号、成绩三个字段；
［操作］SELECT 学号,课程号,成绩 FROM 成绩表
或：SELECT * FROM 成绩表
（3）查询表中有多少个学号（即不显示相同的学号）
［操作］SELECT DISTINCT 学号 FROM 成绩表
（4）查询表中成绩不及格的学生记录
［操作］SELECT * FROM 成绩表 WHERE 成绩<60
（在“学生表”表中）
（5）查询表中85年以后出生，且入学成绩大于560分的学生记录
［操作］SELECT * FROM 学生表 ;
WHERE 入学成绩>560 AND YEAR(出生日期)>1985
或：
SELECT * FROM 学生表 ;
WHERE 入学成绩>560 AND 出生日期>CTOD("12/31/85")
（6）查询表中85年出生的学生的学号、姓名、出生日期
［操作］SELECT 学号,姓名,出生日期 FROM 学生表 ;
WHERE 出生日期>= CTOD("01/01/85") AND 出生日期 <= CTOD("12/31/85")
或：
SELECT 学号,姓名,出生日期 FROM 学生表 WHERE YEAR(出生日期)= 1985
2．多表连接查询
（1）从学生表和成绩表中查询学生记录
［操作］SELECT * FROM 学生表,成绩表 WHERE 学生表.学号=成绩表.学号或（用数据库操作命令）：
SELECT 1
USE 成绩表
INDEX ON 学号 TAG XH
SELECT 2
USE 学生表
SET RELATION TO 学号 INTO A
LIST 学号,姓名,A.课程号,A.成绩
（2）从学生表和成绩表中查询成绩不及格的学生姓名、课程号、成绩
［操作］SELECT 姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND 成绩<60
或（用数据库操作命令）：
SELECT 1
USE 学生表
INDEX ON 学号 TAG XH
SELECT 2
USE 成绩表
SET RELATION TO 学号 INTO A
LIST A.姓名,课程号,成绩 FOR 成绩<60
（3）2002年计算机等级考试［二级（数据库）上机考试试卷（有修改）］
1.在某商场管理系统中，以下2个数据表反映一个月内有关手机销售、库存情况。
（1）商品月库存表（Stockll.dbf）,（注：这两个表已经做好，在“表”文件夹中）
商品代码 品名 型号 单价 库存数量
2011 诺基亚 N8250 2250.00 16
2120 诺基亚 3310 990.00 18
4025 摩托罗拉 8088 2090.00 12
3122 爱立信 T29 1380.00 14
5203 西门子 2118 1470.00 18
（2）销售明细表（Sell.dbf）:
商品代码 日期 销售数量 营业员 销售金额
2120 2001-11-12 5 方虹
4025 2001-11-12 6 方虹
3122 2001-11-13 4 李之和
2120 2001-11-14 10 张广河显示营业员方虹本月销售商品、数量
［操作］SELECT 营业员,日期,品名,销售数量 FROM Stockll,Sell ;
WHERE Stockll.商品代码= Sell.商品代码 AND 营业员 =“方虹”
或（用数据库操作命令）,
SET TALK OFF
SET SAFETY OFF
SET DATE YMD
CLEAR
SELECT 1
USE Stockll
INDEX ON 商品代码 TO SP
SELECT 2
USE Sell
SET RELATION TO 商品代码 INTO A
LIST 营业员,日期,A->品名,销售数量 FOR 营业员 =“方虹”
二、SQL综合练习：
1,下列语句的作用是什么？上机操作后给出答案。
（1）SELECT * FROM 学生表
［解答］显示学生表中的所有记录
（2）SELECT 学号,姓名,出生日期 FROM 学生表 WHERE 出生日期;
BETWEEN CTOD("01/01/84") AND CTOD("12/31/84")
［解答］显示学生表中出生日期在84年的学生的学号，姓名和出生日期
（3）SELECT 姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND 成绩<60
［解答］从学生表和成绩表中查询成绩不及格的学生姓名、课程号、成绩
（4）SELECT 学生表.学号,班级,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 成绩表.学号=学生表.学号 AND 成绩>60 ;
AND ( 班级="计算机" OR 班级="金融")
［解答］从学生表和成绩表中，查询并显示成绩在60分以上的计算机班或金融班学生的学号、班级、课程号及成绩
2,用数据库命令做的题，现在用SQL语句来做：
（1）顺序查询,
① 在学生表中找出85年出生的学生记录
SET TALK OFF
USE 学生表
LOCATE FOR YEAR(出生日期)=1985
DO WHILE NOT EOF()
DISPLAY
SKIP
ENDDO
RETURN
［操作］SELECT * FROM 学生表 WHERE YEAR(出生日期) = 1985
（2）多表联结,
① 将１区中的当前表“Xsda”与２区中的表“Xscj”按关键字段“学号”建立关联，并浏览学号、姓名和英语及计算机的成绩
( 注意：要先在不同的区中打开这两个表，并要对子表建立索引－对关键字“学号”)
SELECT 1 && 指定工作区1为当前工作区
USE Xscj && 在工作区1中打开Xscj表
INDEX ON 学号 TAG XH && 为成绩表（对学号）建立索引（名叫XH）
SELECT 2 && 指定工作区2为当前工作区
USE Xsda && 在工作区2中打开Xsdaj表
SET RELATION TO 学号 INTO A
&& 按“学号”对1区（的成绩表）建立关联
BROWSE FIELDS 学号,姓名,A.英语,A.计算机
&& 子表记录前要加前缀：“别名-->”或“别名．”
［操作］SELECT Xsda.学号,姓名,英语,计算机 FROM Xsda,Xscj ;
WHERE Xsda.学号 = Xscj.学号
【高手进阶解答】
1．嵌套查询：
将一个查询的结果，作为另一个查询的条件，这种查询中带查询，就是嵌套查询。
例1：从学生表和成绩表中查学生迪来的成绩和课程号
［解答］SELECT 课程号,成绩 FROM 成绩表 WHERE 学号 = ;
(SELECT 学号 FROM 学生表 WHERE 姓名=”迪来”)
先从内层查询“SELECT 学号 FROM 学生表 WHERE 姓名="迪来"”，查出“迪来”的“学号”是05011013 ；然后从外层查询“SELECT 课程号,成绩 FROM 成绩表 WHERE 学号 = 05011013”,查学号是05011013的学生的课程号和成绩。
（1）从Xsda表中，查询所有与“汪洋”籍贯相同的学生姓名
［解答］
SELECT 姓名 FROM Xsda ;
WHERE 籍贯 = (SELECT 籍贯 FROM Xsda WHERE 姓名="汪洋")
（2）如果不用嵌套查询，可不可以？
［解答］先查出汪洋的籍贯是“重庆”
SELECT 籍贯 FROM Xsda WHERE 姓名="汪洋"
再用一句语句查籍贯是“重庆”的学生姓名
SELECT 姓名 FROM Xsda WHERE 籍贯 = "重庆"
例2：从学生表和成绩表查询成绩不及格的学生姓名。
［解答］
SELECT姓名 FROM 学生表 WHERE 学号 IN ;
(SELECT 学号 FROM 成绩表 WHERE 成绩<60)
内层查询“SELECT 学号 FROM 成绩表 WHERE 成绩<60”，查出成绩不及格的学生的学号，由于这些学号不只一个，所以不能用等号，而必须采用谓词IN，IN的含义是属于，即可等于这许多学号中的任一个，则外层查询就是查询这些学号的学生的姓名。
（1）从学生表和成绩表中查询计算机班的学生的各科成绩。
［解答］
SELECT 学号,课程号,成绩 FROM 成绩表 WHERE 学号 IN ;
(SELECT 学号 FROM 学生表 WHERE 班级="计算机")
（2）从学生表和成绩表中查询0502系科学生的学号，课程号和成绩。
［解答］
SELECT学号,课程号,成绩 FROM 成绩表 WHERE 学号 IN ;
(SELECT 学号 FROM 学生表 WHERE SUBSTR(学号,1,4)= "0502")
2．从键盘输入学号，在“成绩表”、“课程表”表中查询显示该学号学生的各门课程的成绩。
［解答］
ACCEPT,请输入学生学号：” TO Xh
SELECT 成绩表.学号,姓名,课程号,成绩 FROM 成绩表,学生表 ;
WHERE 成绩表.学号 = 学生表.学号 AND成绩表.学号 = Xh
【综合测试解答】
在学生表和成绩表中查找姓名为“王二春”的记录，若他有一科的成绩大于80分，则显示他的学号、姓名、课程号和成绩。
［解答］
（1） SELECT 学生表.学号,姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND 姓名="王二春" AND 成绩>80
［这程序只查询王二春80分以上的记录］
（2）SELECT 学生表.学号,姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND 姓名="王二春" AND ;
成绩表.学号 IN(SELECT 学号 FROM 成绩表 WHERE 成绩>80)
［从内查询中查出所有成绩在80分以上的学号，在外查询中若王二春的学号在其中的话，则显示他的所有科目和成绩（含80分以下的）］
（3）ACCEPT,请输入学生姓名：” TO Xm
SELECT 学生表.学号,姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND 姓名=Xm AND ;
成绩表.学号 IN(SELECT 学号 FROM 成绩表 WHERE 成绩>80)
［这程序可查任意输入姓名的满足条件（只要有一科在80分以上）的学生所有记录。王双春三科成绩均在80分以下，你看看能不能显示他的记录］
练习三 SQL查询功能（二）
【实验目的】
1．熟练掌握SQL查询功能语句SELECT的操作；
2．熟练掌握简单计算功能；
3．熟练掌握几个特殊的运算符。
【知识要点】
一、关系数据库标准语言SQL
3．简单的计算查询
SQL可以计算方式进行查询，常用的计算函数有：
① COUNT：计数
② SUM：求和
③ AVG：求平均值
④ MAX：求最大值
⑤ MIN：求最小值
4．几个特殊的运算符
① BETWEEN……AND …… 表示在“……和……之间”
② LIKE 表示与……匹配，用于“模糊”查询
它有两种匹配方式：“＿”（下划线）表示一个字符；“%”表示任意多个字符
5．排序子句
ORDER BY 字段 ［ASC/DESC］ 将查询结果排序输出
将记录按升序（ASC）［默认］或降序（DESC）排序
【实验内容】
一、SQL查询语句SELECT
1．简单的计算查询
（1）查询学生表中男学生的人数
（2）查询成绩表中课程号为1021的成绩总和
（3）查询Xscj表中计算机成绩在80分以上的人数、总分和平均分
（4）显示Xsda表中姓“张”学生的平均年龄
（5）从Xsda表和Xscj表查询英语系学生计算机考试的总分、平均分、最高分和参考人数
[要用Xsda表，取系别、Xscj表，取成绩]
2．几个特殊的运算符 ① BETWEEN……AND …… ；② LIKE ；③ ORDER BY
（1）查询学生表中83年出生的学生的学号，姓名，出生日期
（2）查询学生表中83到84年出生的学生的学号、姓名、出生日期
（3）查询学生表中入学成绩在500到600分的学生
（4）显示学生表中姓“王”的学生
（5）显示学生表中叫“王X”的学生
（6）显示学生表中叫“王XX”的学生
（7）显示学生表中姓名中含“王”字的学生（比较一下，这四题有何不同？理解%与_的区别）
（8）按入学成绩分数升序显示学生表的学生记录
（9）按入学成绩分数降序显示学生表中学生的学号、姓名和入学成绩记录
（10）按入学成绩分数降序显示学生表中500分以上的学生的学号、姓名和入学成绩二、SQL综合练习：
1．综合运用SELECT语句
（1）从学生表和成绩表中查询84年出生的学生的学号、姓名、出生日期、入学成绩、课程号和考试成绩
（2）从学生表和成绩表中查询姓张的学生的学号、姓名、入学成绩
（3）从学生表和成绩表中查询成绩在80到90分的学生的学号、姓名、课程号和成绩
（4）查询并显示学生表和成绩表中05级各个学生的学号、姓名、课程号及成绩
（5）查询并显示学生表和成绩表中“信息工程系”的各个学生的学号、姓名、各科课程的成绩（学号中第3到5位是011，则表示该生是“信息工程系”的）
（6）查询并显示学生表和成绩表中姓张和姓李的学生的学号、姓名、各科课程号及成绩
(7) 从学生表查询所有记录的学号、姓名、班级、入学成绩等内容，并按入学成绩降序排列
(8) 从学生表查询入学成绩在480分以上的学号、姓名、性别、团员否、入学成绩等内容，并按入学成绩升序排列
(9) 用两种方法从成绩表查询成绩在85至95分之间学号、课程号、成绩等内容，并按成绩降序排列
（10）从学生表和成绩表中查询姓王的学生的人数和考试平均成绩
2．下列语句的作用是什么？
（1）SELECT * FROM 学生表 WHERE 姓名 LIKE "李_"
（2）SELECT * FROM 学生表 ORDER BY 入学成绩 DESC
（3）SELECT 姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号= 成绩表.学号 AND 成绩<60
（4）SELECT * FROM 学生表 WHERE 姓名 LIKE "%李% "
（5）SELECT 学号,课程号,成绩 FROM 成绩表 ;
WHERE 成绩>80 ORDER BY 成绩
3．以前的题，现在用SQL语句来做：
（1）顺序查询,
在Xsda表中找出姓“张”的大于20岁的男生记录
USE Xsda
LOCATE ALL FOR SUBSTR(姓名,1,2) =”张” AND 年龄>20 AND 性别=”男”
DISPLAY
CONTINUE
DISPLAY
……
（2）数据统计：
① 统计学生表中入学成绩的最大值、最小值、总和、平均值
USE 学生表
CALCULATE MAX(入学成绩),MIN(入学成绩),SUM(入学成绩),AVG(入学成绩)
② 统计成绩表中1021课程的最大值、最小值、总和、平均值
USE 成绩表
CALCULATE MAX(成绩),MIN(成绩),SUM(成绩),AVG(成绩) ;
FOR 课程号=”1021”
【高手进阶】
1．从学生表查询所有记录的学号、姓名、班级、入学成绩等内容，并按班级入学成绩升序排列，同班同学按入学成绩降序排列
2．查询 Xscj表中计算机成绩在80分以上的人数和总分，并使用AS指定列标题
3．分组查询：GROUP BY子句将查询的结果进行分组，可进行分组汇总。
例1：从学生表中按班级统计各班学生的人数。
［解答］SELECT COUNT(*) FROM 学生表 GROUP BY 班级
（1）上题中，如果同时要显示班级名称，如何做？
（2）在成绩表中，按课程号统计各科的课程号、总分、平均分和参考人数
【自我测试】( 5分钟 )
1．在学生表中统计姓“张”学生的人数。
2．从学生表和成绩表中查询姓张的学生的人数和考试平均成绩。
【实验过程】
一、SQL查询语句SELECT
1．简单的计算查询
（1）查询学生表中男学生的人数
［解答］SELECT COUNT(学号) FROM 学生表 WHERE 性别=”男”
或：
SELECT COUNT(*) FROM 学生表 WHERE 性别= "男"
注：COUNT(*)是COUNT的特殊形式，用于统计WHERE子句中逻辑表达式的记录的个数。
（2）查询成绩表中课程号为1021的成绩总和
［解答］SELECT SUM(成绩) FROM 成绩表 WHERE 课程号=”1021”
（3）查询 Xscj表中计算机成绩在80分以上的人数、总分和平均分
［解答］SELECT COUNT(*),SUM(计算机),AVG(计算机) FROM Xscj WHERE 计算机>=80
（4）显示 Xsda表中姓“张”学生的平均年龄
［解答］SELECT AVG(年龄) FROM Xsda WHERE SUBSTR(姓名,1,2)="张"
（5）从Xsda表和Xscj表查询英语系学生计算机考试的总分、平均分、最高分和参考人数
[要用Xsda表取系别、Xscj表取成绩]
［解答］SELECT COUNT(*),SUM(计算机),AVG(计算机),MAX(计算机) FROM Xsda,Xscj;
WHERE Xsda.学号= Xscj.学号 AND 系别= "英语系"
2．几个特殊的运算符 ① BETWEEN……AND …… ；② LIKE ；③ ORDER BY
（1）查询学生表中83年出生的学生的学号，姓名，出生日期
［解答］SELECT 学号,姓名,出生日期 FROM 学生表 ;
WHERE 出生日期 BETWEEN CTOD("01/01/83") AND CTOD("12/31/83")
（2）查询学生表中83到84年出生的学生的学号、姓名、出生日期
［解答］SELECT 学号,姓名,出生日期 FROM 学生表 ;
WHERE YEAR(出生日期) BETWEEN 1983 AND 1984
（3）查询学生表中入学成绩在500到600分的学生
［解答］SELECT * FROM 学生表 WHERE 入学成绩>500 AND 入学成绩<600
或：（比较一下，两者有何不同？）
SELECT * FROM 学生表 WHERE 入学成绩 BETWEEN 500 AND 600
（4）显示学生表中姓“王”的学生
［解答］SELECT * FROM 学生表 WHERE SUBSTR(姓名,1,2)="王"
或：SELECT * FROM 学生表 WHERE 姓名 LIKE "王%"
（5）显示学生表表中叫“王X”的学生
［解答］SELECT * FROM 学生表 WHERE 姓名 LIKE "王_"
（6）显示学生表表中叫,王XX”的学生
［解答］SELECT * FROM 学生表 WHERE 姓名 LIKE "王__"
（7）显示学生表表中姓名中含,王”字的学生（比较一下，这四题有何不同？理解%与_的区别）
［解答］SELECT * FROM 学生表 WHERE 姓名 LIKE "%王%"
（8）按入学成绩分数升序显示学生表的学生记录
［解答］SELECT * FROM 学生表 ORDER BY 入学成绩 ASC
（9）按入学成绩分数降序显示学生表中学生的学号、姓名和入学成绩记录
［解答］SELECT 学号,姓名,入学成绩 FROM 学生表 ORDER BY 入学成绩 DESC
（10）按入学成绩分数降序显示学生表中500分以上的学生的学号、姓名和入学成绩
［解答］ SELECT 学号,姓名,入学成绩 FROM 学生表 ;
ORDER BY 入学成绩 DESC WHERE 入学成绩>500
二、SQL综合练习：
1．综合运用SELECT语句
（1）从学生表和成绩表中查询84年出生的学生的学号、姓名、出生日期、入学成绩、课程号和考试成绩
［解答］SELECT 学生表.学号,姓名,出生日期,入学成绩,课程号,成绩 ;
FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 ;
AND 出生日期 BETWEEN CTOD("01/01/84") AND CTOD("12/31/84")
（2）从学生和成绩表中查询姓张的学生的学号、姓名、入学成绩
［解答］SELECT 学生表.学号,姓名,入学成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND SUBSTR(姓名,1,2) = "张"
或：
SELECT 学生表.学号,姓名,入学成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND 姓名 LIKE "张%"
（3）从学生表和成绩表中查询成绩在80到90分的学生的学号、姓名、课程号和成绩
［解答］SELECT 学生表.学号,姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND 成绩 BETWEEN 80 AND 90
（4）查询并显示学生表和成绩表中05级各个学生的学号、姓名、课程号及成绩
［解答］SELECT 学生表.学号,姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND 学生表.学号 LIKE "05%"
或：
SELECT 学生表.学号,姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND SUBSTR(学生表.学号,1,2)= "05"
（5）查询并显示学生表和成绩表中“信息工程系”的各个学生的学号、姓名、各科课程的成绩（学号中第3到5位是011，则表示该生是“信息工程系”的）
［解答］SELECT 学生表.学号,姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND 学生表.学号 LIKE "__011%"
或：
SELECT 学生表.学号,姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND SUBSTR(学生表.学号,3,3)= "011"
（6）查询并显示学生表和成绩表中姓张和姓李的学生的学号、姓名、各科课程号及成绩
［解答］SELECT 学生表.学号,姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 ;
AND ( 姓名 LIKE "张%" OR 姓名 LIKE "李%" )
(7) 从学生表查询所有记录的学号、姓名、班级、入学成绩等内容，并按入学成绩降序排列
［解答］SELECT 学号,姓名,班级,入学成绩 FROM 学生表 ORDER BY 入学成绩 DESC
(8) 从学生表查询入学成绩在480分以上的学号、姓名、性别、团员否、入学成绩等内容，并按入学成绩升序排列
［解答］SELECT 学号,姓名,性别,团员否,入学成绩 FROM 学生表 ;
WHERE 入学成绩>=480 ORDER BY 入学成绩
(9) 用两种方法从成绩表查询成绩在85至95分之间学号、课程号、成绩等内容，并按成绩降序排列
［解答］
① SELECT 学号,课程号,成绩 FROM 成绩表 ;
WHERE 成绩>=85 AND 成绩<=95 ORDER BY 成绩 DESC
② SELECT 学号,课程号,成绩 FROM 成绩表 ;
WHERE 成绩 BETWEEN 85 AND 95 ORDER BY 成绩 DESC
（10）从学生表和成绩表中查询姓王的学生的人数和考试平均成绩
［解答］SELECT COUNT(*),AVG(成绩) FROM 学生表,成绩表 ;
WHERE 学生表.学号= 成绩表.学号 AND姓名 LIKE "王%"
2．下列语句的作用是什么？
（1）SELECT * FROM 学生表 WHERE 姓名 LIKE "李_"
［解答］显示学生表中叫“李X”的学生
（2）SELECT * FROM 学生表 ORDER BY 入学成绩 DESC
［解答］显示学生表的学生记录，并按入学成绩分数降序显示
（3）SELECT 姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号= 成绩表.学号 AND 成绩<60
［解答］从学生表和成绩表中查询考试成绩不及格的学生姓名、课程号和考试成绩
（4）SELECT * FROM 学生表 WHERE 姓名 LIKE "%李% "
［解答］显示学生表表中姓名中有“李”字的学生
（5）SELECT 学号,课程号,成绩 FROM 成绩表 ;
WHERE 成绩>80 ORDER BY 成绩
［解答］按考试成绩升序显示成绩表中80分以上学生的学号、课程号和成绩
3．以前的题，现在用SQL语句来做：
（1）顺序查询,
在学生表中找出姓“张”的大于20岁的男生记录
USE Xsda
LOCATE ALL FOR SUBSTR(姓名,1,2) =”张” AND 年龄>20 AND 性别=”男”
DISPLAY
CONTINUE
DISPLAY
……
［解答］
SELECT * FROM Xsda ;
WHERE 性别 = "男" AND 年龄 > 20 AND 姓名 LIKE "张%"
（2）数据统计：
① 统计学生表中入学成绩的最大值、最小值、总和、平均值
USE 学生表
CALCULATE MAX(入学成绩),MIN(入学成绩),SUM(入学成绩),AVG(入学成绩)
［解答］
SELECT MAX(入学成绩),MIN(入学成绩),SUM(入学成绩),AVG(入学成绩) FROM 学生表
② 统计成绩表中1021课程的最大值、最小值、总和、平均值
USE 成绩表
CALCULATE MAX(成绩),MIN(成绩),SUM(成绩),AVG(成绩) ;
FOR 课程号=”1021”
［解答］
SELECT MAX(成绩),MIN(成绩),SUM(成绩),AVG(成绩) FROM 成绩表;
WHERE 课程号=”1021”
【高手进阶解答】
1．从学生表查询所有记录的学号、姓名、班级、入学成绩等内容，并按班级入学成绩升序排列，同班同学按入学成绩降序排列
［解答］
SELECT 学号,姓名,班级,入学成绩 FROM 学生表 ORDER BY 班级，入学成绩 DESC
2．查询 Xscj表中计算机成绩在80分以上的人数和总分，并使用AS指定列标题
［解答］短语AS，可以指定输出的列标题，使输出更容易被人理解
SELECT COUNT(*) AS 人数,SUM(计算机) AS 总分 FROM Xscj WHERE 计算机>=80
3．分组查询：GROUP BY子句将查询的结果进行分组，可进行分组汇总。
例1：从学生表中按班级统计各班学生的人数。
［解答］SELECT COUNT(*) FROM 学生表 GROUP BY 班级
（1）上题中，如果同时要显示班级名称，如何做？
［解答］
SELECT 班级,COUNT(*) FROM 学生表 GROUP BY 班级
（2） 在成绩表中，按课程号统计各科的课程号、总分、平均分和参考人数
［解答］
SELECT 课程号,SUM(成绩) AS 总分,AVG(成绩) AS 平均分,COUNT(*) AS 参考人数;
FROM 成绩表 GROUP BY 课程号
【自我测试解答】( 5分钟 )
1．在学生表中统计姓“张”学生的人数。
［解答］SELECT COUNT(*) FROM 学生表 WHERE 姓名 LIKE "张%"
2．从学生表和成绩表中查询姓张的学生的人数和考试平均成绩
［解答］SELECT COUNT(*),AVG(成绩) FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND 姓名 LIKE "张%"
练习四 SQL定义功能
【实验目的】
1．掌握SQL定义功能语句的操作；
2．熟练掌握表的定义（CREATE）；
3．熟练掌握表的删除（DROP）；
4．熟练掌握表结构的修改、添加和修改字段（ALTER）。
【知识要点】
表的定义：创建一个新表
CREATE TABLE 表名（字段名 类型［，字段名 类型］……）
说明：
（1）常用数据类型有字符型：C(n)；:数值型：N(n,d)；日期型：D(默认为8位)；逻辑型：L(默认为1位)等；
（2）建表前应对此表的结构、内容，做到心中有数，即清楚要有几列、取什么名字、什么类型、宽度多大；
（3）在建立表之前，应先建立一个数据库。在此库中定义表。
建立数据库：CREATE DATABASE 数据库名
（4）以前的数据库操作命令：CREATE 表名 → 进入“表设计器”
2．表的删除：删除一个表
DROP TABLE 表名说明：
（1）直接从磁盘上删除表名所对应的.dbf文件；
（2）如果表是数据库中的表，则从数据库中删除此表（对数据库中的表，应先打开数据库，再删除表，否则以后会出现错误信息）。
3．表结构的修改：类似“表设计器”的操作
（1）添加∕修改字段
ALTER TABLE 表名 ADD/ALTER 字段名 类型（宽度［,小数］）
说明：
① 可添加（ADD）字段或修改字段（ALTER），字段名后应带类型和宽度；
② 不能修改字段名和删除字段。
（2）删除字段、更改字段名
ALTER TABLE 表名 DROP 字段名 RENAME 字段名1 TO 字段名2
说明：
① DROP 字段名 从指定表中删除指定字段。
② RENAME 字段名1 TO 字段名2 从指定表中将字段名1改为字段名2
③ 或,
ALTER TABLE 表名 DROP 字段名 删除字段
ALTER TABLE 表名 RENAME 字段名1 TO 字段名2 修改字段名
注：表结构修改的操作在第3章中修改表结构中是用“表设计器”和命令“MODIFY STRUCTURE”进行的
【实验内容】
二、SQL定义语句
1．表的定义（CREATE）
（1）创建“学生表A”：学号（C，8）、姓名（C，6）、、入学成绩（N，3）
（2）在D盘根目录下创建“学生表B”：学号（C，8）、姓名（C，6）、入学成绩（N，5，1）、简历（M）、相片（G）
（3）在D盘根目录下先创建一个数据库“学生管理”，在此数据库中创建“学生表C”：学号（C，8）、姓名（C，6）、出生日期（D）、入学成绩（N，5，1）
2．表的删除：（DROP）
（1）删除D 盘根目录中的“学生表A”
（2）删除D 盘根目录的数据库“学生管理”中的“学生表C”
3．表结构的修改（ALTER）
（1）添加∕修改字段
1）添加字段
① 在D盘根目录的“学生表B”中添加一个N（2）类型的字段：年龄
② 在D盘根目录的“学生表B”中添加两个字段：班级(C，8)、团员否(L)
2）修改字段
① 将D盘根目录的“学生表B”中姓名字段的宽度由原来的6改为8
② 将D盘根目录的“学生表B”中学号字段的类型改成N型、宽度改为6（1位小数）
（2）删除字段、更改字段名
1）删除字段
① 将Xscj表中的“等级”字段删除
② 将D盘根目录的“学生表B”的“年龄”字段删除
2）更改字段名
① 将Xscj表中的“平均”字段改名为“平均分”
② 将D盘根目录的“学生表B”中字段“相片”改名为“像片”
4．综合练习
（1）在D盘根目录中创建如下表的“学生表A”（见P43）
字段名
字段类型
字段宽度
小数点
学号
字符型
8
姓名
字符型
8
性别
字符型
2
出生日期
日期型
8
团员否
逻辑型
1
入学成绩
数值型
6
2
班级
字符型
10
照片
通用型
4
简历
备注型
4
（2）为“学生表A”中添加一个字符型字段“系别”（宽度：10）
（3）将“学生表A”的字段“出生日期”改成“出生年月”
（4）将“学生表A”的“入学成绩”字段的宽度改为3，小数去掉
（5）将“学生表A”的“团员否”字段改为字符型，宽度改为2
（6）将“学生表A”的“班级”字段删除
（7）删除“学生表A”
【高手进阶】
（1）在D盘根目录中创建如下表的“学生表B”
字段名
字段类型
字段宽度
学号
字符型
7
专业
字符型
4
姓名
字符型
6
性别
字符型
2
出生年月
日期型
8
团员
逻辑型
1
（2）用“INSERT”命令为“学生表B”添加如下三条记录学号
专业
年级
姓名
性别
出生年月
团员
9103001
通讯
91
张佳
女
09/23/82
.T.
9101016
软件
91
陈莉
男
11/03/83
.T.
9102015
应用
91
王磊
男
04/26/82
.F.
注：用“INSERT”命令可对当前表插入记录
INSERT INTO 表名 VALUES(对应列名1的值,...,对应列名n的值)
【自我测试】( 5分钟 )
（1）在D盘根目录中创建如下表的“学生表C”
字段名
字段类型
字段宽度
小数点
学号
字符型
6
班级
字符型
6
姓名
字符型
6
出生年月
日期型
8
入学成绩
数值型
5
2
（2）为“学生表C”中添加一个字符型字段“性别”（宽度：2）
（3）将“学生表C”的“班级”字段删除
【实验过程】
二、SQL定义语句
1．表的定义（CREATE）
（1）创建“学生表A”：学号（C，8）、姓名（C，6）、、入学成绩（N，3）
［解答］
CREATE TABLE 学生表A (学号 C(8),姓名 C(6),出生日期 D,入学成绩 N(3))
注意：
字段名后加一空格再输入数据类型后用括号输入宽度，系统默认的宽度就不输入；
所有字段内容用一括号括起紧接在表名的后面。
（2）在D盘根目录下创建“学生表B”：学号（C，8）、姓名（C，6）、入学成绩（N，5，1）、简历（M）、相片（G）
［解答］
CREATE TABLE D:\学生表B (学号 C(8),姓名 C(6),入学成绩 N(5,1),简历 M,相片 G)
（3）在D盘根目录下先创建一个数据库“学生管理”，在此数据库中创建“学生表C”：学号（C，8）、姓名（C，6）、出生日期（D）、入学成绩（N，5，1）
［解答］
CREATE DATABASE D:\学生管理 先建立数据库
CREATE TABLE 学生表C (学号 C(8),姓名 C(6),出生日期 D,入学成绩 N(5,1))
2．表的删除（DROP）
（1）删除D 盘根目录中的“学生表A”
［解答］
DROP TABLE D:\学生表A
（2）删除D 盘根目录的数据库“学生管理”中的“学生表C”
［解答］
OPEN DATABASE D:\学生管理 先打开数据库
DROP TABLE 学生表C
另类删除：可通过Windows的删除命令进行删除。但对数据库中的表最好移出去再删除。
3．表结构的修改（ALTER）
（1）添加∕修改字段
1）添加字段
① 在D盘根目录的“学生表B”中添加一个N（2）类型的字段：年龄
ALTER TABLE D:\学生表B ADD 年龄 N(2)
注：字段名后加一空格再带类型和宽度
② 在D盘根目录的“学生表B”中添加两个字段：班级(C，8)、团员否(L)
［解答］
ALTER TABLE D:\学生表B ADD 班级 C(8) ADD 团员否 L
注：添加两个字段，要用两个ADD语句
2）修改字段（类型和宽度）
① 将D盘根目录的“学生表B”中姓名字段的宽度由原来的6改为8
［解答］
ALTER TABLE D:\学生表B ALTER 姓名 C(8)
注：表名后用ADD 字段名 类型(宽度)是添加一个字段；
用ALTER字段名 类型(宽度)可修改字段的类型、宽度，但不能修改字段名。
② 将D盘根目录的“学生表B”中学号字段的类型改成N型、宽度改为6（1位小数）
［解答］
ALTER TABLE D:\学生表B ALTER 学号 N(6,1)
注：① 只要直接写入新的字段类型或宽度即可；② 不能修改字段名和删除字段。
（2）删除字段、更改字段名
1）删除字段
① 将Xscj表中的“等级”字段删除
［解答］
ALTER TABLE Xscj DROP 等级
② 将D盘根目录的“学生表B”的“年龄”字段删除
［解答］
ALTER TABLE D:\学生表B DROP 年龄
2）更改字段名
① 将Xscj表中的“平均”字段改名为“平均分”
［解答］
ALTER TABLE Xscj RENAME 平均 TO 平均分
② 将D盘根目录的“学生表B”中字段“相片”改名为“像片”
［解答］
ALTER TABLE D:\学生表B RENAME 相片 TO像片
4．综合练习
（1）在D盘根目录中创建如下表的“学生表A”（见P43）
字段名
字段类型
字段宽度
小数点
学号
字符型
8
姓名
字符型
8
性别
字符型
2
出生日期
日期型
8
团员否
逻辑型
1
入学成绩
数值型
6
2
班级
字符型
10
照片
通用型
4
简历
备注型
4
［解答］
CREATE TABLE 学生表A (学号 C(8),姓名 C(8),性别 C(2),出生日期 D,;
团员否 L,入学成绩 N(6,2),班级 C(10),照片 G,简历 M)
（2）为“学生表A”中添加一个字符型字段“系别”（宽度：10）
［解答］
ALTER TABLE 学生表A ADD 系别 C(10)
（3）将“学生表A”的字段“出生日期”改成“出生年月”
［解答］
ALTER TABLE 学生表A RENAME 出生日期 TO 出生年月
（4）将“学生表A”的“入学成绩”字段的宽度改为3，小数去掉
［解答］
ALTER TABLE 学生表A ALTER 入学成绩 N(3)
（5）将“学生表A”的“团员否”字段改为字符型，宽度改为2
［解答］
ALTER TABLE 学生表A ALTER 团员否 C(2)
（6）将“学生表A”的“班级”字段删除
［解答］
ALTER TABLE 学生表A DROP 班级
（7）删除“学生表A”
［解答］
DROP TABLE 学生表A
【高手进阶解答】
（1）在D盘根目录中创建如下表的“学生表B”
字段名
字段类型
字段宽度
学号
字符型
7
专业
字符型
4
姓名
字符型
6
性别
字符型
2
出生年月
日期型
8
团员
逻辑型
1
［解答］
CREATE TABLE 学生表B (学号 C(7),专业 C(4),姓名 C(6),性别 C(2),;
出生年月 D,团员 L
（2）用“INSERT”命令为“学生表B”添加如下三条记录学号
专业
年级
姓名
性别
出生年月
团员
9103001
通讯
91
张佳
女
09/23/82
.T.
9101016
软件
91
陈莉
男
11/03/83
.T.
9102015
应用
91
王磊
男
04/26/82
.F.
INSERT INTO 学生表B VALUES(“9103001”,”通讯”,”91”,”张佳”,”女”,{^1982-09-23},.T.)
INSERT INTO 学生表B VALUES(“9101016”,”软件”,”91”,”陈莉”,”男”,{^1983-11-03},.T.)
INSERT INTO 学生表B VALUES(“9102015”,”应用”,”91”,”王磊”,”男”,{^1982-04-26},.F.)
注：一条命令只能添加一条记录
【自我测试解答】( 5分钟 )
（1）在D盘根目录中创建如下表的“学生表C”
字段名
字段类型
字段宽度
小数点
学号
字符型
6
班级
字符型
6
姓名
字符型
6
出生年月
日期型
8
入学成绩
数值型
5
2
［解答］
CREATE TABLE 学生表C (学号 C(6),班级 C(6),姓名 C(6),;
出生年月 D,入学成绩 N(5,2)
（2）为“学生表C”中添加一个字符型字段“性别”（宽度：2）
［解答］
ALTER TABLE 学生表C ADD 性别 C(2)
（3）将“学生表C”的“班级”字段删除
［解答］
ALTER TABLE 学生表C DROP 班级
练习五 SQL操作功能
【实验目的】
1．掌握SQL操作功能语句的操作；
2．熟练掌握表记录的更新（UPDATE）；
3．熟练掌握表记录的插入（INSERT）；
4．熟练掌握表记录的删除（DETELE）。
【知识要点】
1．表记录的更新：更新满足条件的记录的字段值，类似替换命令REPLACE
UPDATE 表名 SET 字段名1 = 表达式1 [,字段名2 = 表达式2，… ]；
WHERE 条件式说明：
（1）该语句一次可更新多个字段的值；
（2）不使用WHERE子句，则更新所有记录；
（3）此语句类似REPLACE命令。
2．表记录的插入：按指定字段名顺序在表末尾插入用表达式表示字段值的一条记录
INSERT INTO 表名 ［字段名1,字段名2,……] VALUES［表达式1,表达式2，……］
说明：
（1）［字段名1,字段名2,……]是用来指定表中的（部分）字段名；［表达式1,表达式2，……］为指定字段的具体记录值，它要与前面的字段名一一对应；
（２）若无［字段名1,字段名2,……]，则是对所有字段追加记录，这时的记录必须与表结构中的字段名一一对应；
（3）此命令是用以编程方式在表末尾追加一条记录。
3．表记录的删除：为表中满足条件的记录作删除标记
DELETE FORM 表名 WHERE 条件式说明：
（1）删除表中满足条件的记录。若无条件项，则删除所有记录；
（2）此语句类似DELETE命令，只对记录作删除标记；
（3）要彻底删除记录，必须使用PACK命令（也可用RECALL命令撤消标记）。
【实验内容】
三、SQL操作语句
1．表记录更新（UPDATE）
修改数据后,用“查询”语句“SELECT * FROM 表名”观看修改后的结果
（1）计算Xscj表的总分
（2）在Xscj表中将总分在330分以上（含330）的等级设置为合格，330分以下的为不合格。
（3）将Xscj表中学号为“10100116”的“计算机”成绩增加20分
（4）将Xscj表中“英语”成绩＞80分的增加10%
（5）将成绩表中，学“1011”号课及学“2020”号课的，成绩增加10分
（6）将成绩表中学号为“05031003”的“3002”号课成绩增加20分
（7）将成绩表中学号3、4位是“02”的“成绩”增加20分
（8）将学生表中班级是“金融”的前面加上“经贸”二字
（9）将学生表中学生“王大发”的入学成绩增加10分，并把性别改为“女”
2．表记录的插入（INSERT）
（1）在成绩表插入下面两条记录学号
课程号
成绩
05011001
1031
91
05021012
2020
76
（2）在学生表插入下面两条记录学号
班级
姓名
性别
出生日期
团员否
05031015
会计
张三风
女
03/28/86
.T.
05021018
金融
段誉
男
04/01/85
.F.
3．表记录的删除（DELETE）此语句仅对记录作删除标记
（1）删除学生表中入学成绩低于500分的男生记录
（2）从Xsda表中删除计算机系的重庆学生的记录。
（3）将Xscj表中“计算机”成绩在60到79分之间的记录删除
（4）将学生表中姓“张”的学生记录删除
（5）将学生表中不是“团员”的学生记录删除
4．综合运用：06计算机等级考试模拟试卷［二级（数据库）上机考试］
1、建立一个图书交易管理数据库，文件名为SJY.DBC，并输入数据（30分）该数据库有两个数据表：书籍表SJ.DBF和交易表JY.DBF。其数据形式如下：
书号
书名
2030
计算机原理
4644
离散数学
6426
软件工程
3535
高等数学
3376
经济法
3679
大学语文
4636
大学英语
5675
基础会计
书籍表SJ.DBF
交易表JY.DBF
书号
已售
定价
册数
总金额
2030
.T.
21.20
134
4644
.F.
17.60
128
6426
.T.
30.00
69
3535
.F.
18.80
306
3376
.T.
22.30
238
3679
.T.
19.00
112
4636
.F.
25.40
168
5675
.F.
18.50
280
要求：将数据库(SJY.DBC)中的SJ.DBF和JY.DBF两表以”书号”建立一对多的永久关系。[注：已售为真(,T.)，即此书已销售 ]
2、操作数据库表。要求将相关命令保留到程序文件CQEEC61.PRG中（40分）。
注：使用VFP数据表操作命令或者VFP-SQL命令，一个任务可由若干个命令组成，请用注释语句标注题号。
（1）根据股份和册数计算每次交易的总金额，并将计算结果写入JY.DBF表中；
（2）查询已经交易的股票信息，要求显示书名，册数，股份及交易总金额，已经交易的为.T.
（3）按已售汇总册数和交易总金额，并显示已售和汇总后的册数和交易总金额
（4）将JY.DBF中还没有交易的股票逻辑删除。
3、程序填空，要求输入并完善程序，程序文件名为PROG061.PRG（30分）
注：在程序开头，必须用注释语句输入学校名，准考证号，姓名以下程序的功能是能反复输入书号（按‘Q’键退出），并输出该书籍的销售情况，格式为：“书名，已售，销售总金额”最后输出该书籍已经销售的总数量和总金额。
程序代码：
SELECT 1
USE SJ
INDEX ON 书号 TAG SH
SELECT 2
USE JY
SET RELATION TO 书号 INTO A
X=.T.
DO WHILE X
ACCEPT "请输入书号：" TO DM
IF UPPER(ALLTRIM(DM))="Q"
①
LOOP
ENDIF
"书名 已售 册数 销售金额"
SCAN FOR ②
A.书名,已售,册数,总金额
ENDSCAN
CALCULATE ③ TO S1,S2 FOR NOT 已售 AND 书号=DM
"该书籍已经销售的总数量 总金额"
S1,S2
ENDDO
CLOSE ALL
SET TALK ON
RETURN
【高手进阶】
（1）将学生表中学生“章立新”的各科成绩全更新为0（要用两个表，从学生表中找章立新的学号，从成绩表中将此学号的成绩更新为0）
（2）将学生表中学生“迪来”的各科成绩从成绩表中删去
【自我测试】( 5分钟)
对Xsda表和Xscj表，使用VFP-SQL命令和VFP数据表操作命令，完成下列操作。
（1）显示学号是“101”开头的学生学号、姓名、性别和年龄；
（2）计算学生的总分和平均分，并显示结果；
（3）计算英语和计算机的总分和平均分，并显示结果；
（4）显示计算机系学生的高数、英语、计算机成绩及这三科的总分。
【实验过程】
三、SQL操作语句
1．表记录更新（UPDATE）
修改数据后,用“查询”语句“SELECT * FROM 表名”观看修改后的结果
（1）计算Xscj表的总分
［解答］
UPDATE Xscj SET 总分 = 高数+大语+邓论+英语+计算机
或：（使用VFP数据表操作命令。注意看看有什么地方不同）
USE Xscj
REPLACE 总分 WITH高数+大语+邓论+英语+计算机 ALL
（2）在Xscj表中将总分在330分以上（含330）的等级设置为合格，330分以下的为不合格。
［解答］
UPDATE Xscj SET 等级="合格" WHERE 总分 >= 330
UPDATE Xscj SET 等级="不合格" WHERE 总分 < 330
[因条件不一样，所以用两条更新语句]
（3）将Xscj表中学号为“10100116”的“计算机”成绩增加20分
［解答］
UPDATE Xscj SET 计算机=计算机+20 WHERE 学号=”10100116”
（4）将Xscj表中“英语”成绩＞80分的增加10%
［解答］
UPDATE Xscj SET 英语=英语*1.1 WHERE 英语>80
（5）将成绩表中，学“1011”号课及学“2020”号课的，成绩增加10分
［解答］
UPDATE 成绩表 SET 成绩=成绩+10 WHERE 课程号=”1011” or 课程号=”2020”
（6）将成绩表中学号为“05031003”的“3002”号课成绩增加20分
［解答］
UPDATE 成绩表 SET 成绩=成绩+20 WHERE 学号=”05031003” and 课程号=”3002”
（7）将成绩表中学号3、4位是“02”的“成绩”增加20分
［解答］
UPDATE 成绩表 SET 成绩=成绩+20 WHER 学号LIKE,__02%”
或：UPDATE 成绩表 SET 成绩=成绩+20 WHERE SUBSTR(学号,1,2)=”02”
（8）将学生表中班级是“金融”的前面加上“经贸”二字
UPDATE 学生表 SET 班级=”经贸金融” WHERE 班级=”金融”
（9）将学生表中学生“王大发”的入学成绩增加10分，并把性别改为“女”
［解答］
UPDATE 学生表 SET 入学成绩=入学成绩+10,性别=”女” WHERE 姓名=”王大发”
2．表记录的插入（INSERT）
（1）在成绩表插入下面两条记录学号
课程号
成绩
05011001
1031
91
05021012
2020
76
［解答］注意：一条命令只能插入一条记录
INSERT INTO 成绩表 VALUES(“05011001”,”1031”,91)
INSERT INTO 成绩表 VALUES(“05021012”,”2020”,76)
（2）在学生表插入下面两条记录学号
班级
姓名
性别
出生日期
团员否
05031015
会计
张三风
女
03/28/86
.T.
05021018
金融
段誉
男
04/01/85
.F.
［解答］注意：不是对表中所有字段进行操作
INSERT INTO 学生表(学号,班级,姓名,性别,出生日期,团员否) ;
VALUES(“05031015”,”会计”,”张三风”,”女”,{^1986-03-28},.T.)
INSERT INTO 学生表(学号,班级,姓名,性别,出生日期,团员否) ;
VALUES(“05021018”,”金融”,”段誉”,”男”,CTOD(“04/01/85”),.F.)
注意：对字段名要用括号括起；字段内容的顺序要与字段名对齐；两种不同的日期语句
3．表记录的删除（DELETE）此语句仅对记录作删除标记
（1）删除学生表中入学成绩低于500分的男生记录
［解答］
DELETE FROM 学生表 WHERE 入学成绩<500 AND 性别="男"
或：（使用VFP数据表操作命令）
USE 学生表
DELETE FOR 入学成绩<500 AND 性别="男"
（2）从Xsda表中删除计算机系的重庆学生的记录。
［解答］
DELETE FROM 学生表 WHERE 系别 ="计算机系" AND 籍贯="重庆"
（3）将Xscj表中“计算机”成绩在60到79分之间的记录删除
［解答］
DELETE FROM Xscj WHERE 计算机 BETWEEN 60 AND 79
或：DELETE FROM Xscj WHERE 计算机>=60 and 计算机<=79
（4）将学生表中姓“张”的学生记录删除
［解答］
DELETE FROM 学生表 WHERE 姓名 LIKE,张%”
或：DELETE FROM 学生表 WHERE SUBSTR(姓名,1,2) =,张”
（5）将学生表中不是“团员”的学生记录删除
［解答］
DELETE FROM 学生表 WHERE 团员=.F.
或：DELETE FROM 学生表 WHERE NOT 团员注：“NOT 团员”表示不是团员；或用“团员=.F.”（“团员”字段为假）表示
4．综合运用,06计算机等级考试模拟试卷［二级（数据库）上机考试］
1、建立一个图书交易管理数据库，文件名为SJY.DBC，并输入数据（30分）
该数据库有两个数据表：书籍表SJ.DBF和交易表JY.DBF。其数据形式如下：
书号
书名
2030
计算机原理
4644
离散数学
6426
软件工程
3535
高等数学
3376
经济法
3679
大学语文
4636
大学英语
5675
基础会计
书籍表SJ.DBF
交易表JY.DBF
书号
已售
定价
册数
总金额
2030
.T.
21.20
134
4644
.F.
17.60
128
6426
.T.
30.00
69
3535
.F.
18.80
306
3376
.T.
22.30
238
3679
.T.
19.00
112
4636
.F.
25.40
168
5675
.F.
18.50
280
要求：将数据库(SJY.DBC)中的SJ.DBF和JY.DBF两表以”书号”建立一对多的永久关系,[注：已售为真(,T.)，即此书已销售 ]
［解答］
两表已建好，自己在数据库(SJY.DBC)中建一对多的永久关系（在数据库中，先建索引，父表要设为主索引，然后用鼠标拖索引标记到子表）
2、操作数据库表。要求将相关命令保留到程序文件CQEEC61.PRG中（40分）。
注：使用VFP数据表操作命令或者VFP-SQL命令，一个任务可由若干个命令组成，请用注释语句标注题号。
（1）根据股份和册数计算每次交易的总金额，并将计算结果写入JY.DBF表中；
［解答］
SQL语句：
UPDATE JY SET 总金额 = 册数*定价
VFP数据表操作命令
USE JY
REPLACE ALL 总金额 WITH 册数*定价
（2）查询已经交易的股票信息，要求显示书名，册数，股份及交易总金额，已经交易的为.T.
［解答］
SQL语句：
SELECT 书名,册数,定价,总金额 FROM SJ,JY ;
WHERE SJ.书号=JY.书号 AND 已售
VFP数据表操作命令
SELECT 1
USE SJ
INDEX ON 书号 TAG DM
SELECT 2
USE JY
SET RELATION TO 书号 INTO SJ
LIST SJ.书名,册数,定价,总金额 FOR 已售 OFF
（3）按已售汇总册数和交易总金额，并显示已售和汇总后的册数和交易总金额
［解答］
SQL语句：
SELECT 已售,SUM(册数),SUM(总金额) ;
FROM JY GROUP BY 已售
VFP数据表操作命令
USE JY
INDEX ON 已售 TAG LX
TOTAL ON 已售 TO HZ
USE HZ
LIST 已售,册数,总金额 OFF
（4）将JY.DBF中还没有交易的股票逻辑删除。
［解答］
SQL语句：
DELETE FROM JY WHERE NOT 已售
[注：“NOT 已售”，表示已售为假(,F.)，即此书没销售]
VFP数据表操作命令
USE JY
DELETE FOR NOT 已售
3、程序填空，要求输入并完善程序，程序文件名为PROG0621.PRG（30分）
注：在程序开头，必须用注释语句输入学校名，准考证号，姓名以下程序的功能是能反复输入书号（按‘Q’键退出），并输出该股票的交易情况，格式为：“书名，已售，交易总金额”最后输出该股票已经交易的总数量和总总金额。
程序代码：
［解答］
① X=.F,
② 书号=DM
③ SUM(册数),SUM(总金额)
【高手进阶解答】
（1）将学生表中学生“章立新”的各科成绩全更新为0（要用两个表，从学生表中找章立新的学号，从成绩表中将此学号的成绩更新为0）
［解答］
UPDATE 成绩表 SET 成绩= 0 WHERE 学号 = ;
(SELECT学号 FROM 学生表 WHERE 姓名= "章立新")
（2）将学生表中学生“迪来”的各科成绩从成绩表中删去
［解答］
DELETE FROM 成绩表 WHERE 学号 = ;
(SELECT 学号 FROM 学生表 WHERE 姓名="迪来")
【自我测试解答】( 5分钟 )
对Xsda表和Xscj表，使用VFP数据表操作命令和VFP-SQL命令，完成下列操作。
（1）显示学号是“101”开头的学生学号、姓名、性别和年龄；
［解答］
SQL语句：
SELECT 学号,姓名,性别,年龄 FROM Xsda WHERE 学号 LIKE "101%"
VFP数据表操作命令
USE Xsda
LIST学号,姓名,性别,年龄 FOR SUBSTR(学号,1,3) = "101"
（2）计算学生的总分和平均分，并显示结果；
［解答］
SQL语句：
UPDATE Xscj SET 总分=高数+大语+邓论+英语+计算机,平均=总分/5
注：也可写成两句，分别计算
VFP数据表操作命令
USE Xscj
REPLACE 总分 WITH 高数+大语+邓论+英语+计算机,平均 WITH 总分/5 ALL
（3）计算英语和计算机的总分和平均分，并显示结果；
［解答］
SQL语句：
SELECT SUM(英语),SUM(计算机),AVG(英语),AVG(计算机) FROM Xscj
VFP数据表操作命令
USE Xscj
SUM 英语,计算机 TO F1,F2
AVERAGE 英语,计算机TO E1,E2
或：CALCULATE SUM(英语),SUM(计算机),AVG(英语),AVG(计算机) TO F1,F2,E1,E2
" 英语总分 计算机总分 英语平均分 计算机平均分"
F1,F2,E1,E2
注意：这两个总分和平均分是不同的，（3）题是对字段计算（按列），而（2）题是对每条记录计算（按行）。它们所用的命令是完全不同的。
（4）显示计算机系学生的高数、英语、计算机成绩及这三科的总分。
［解答］
SQL语句：
SELECT 系别,姓名,高数,英语,计算机 FROM Xsda,Xscj ;
WHERE Xsda.学号 = Xscj.学号 AND 系别=,计算机系”
VFP数据表操作命令
SELECT 1
USE Xsda
INDEX ON 学号 TAG XH
SELECT 2
USE Xscj
SET RELATION TO 学号 INTO A
LIST FIELDS A.系别,A.姓名,高数,英语,计算机FOR A.系别=“计算机系”
CLOSE ALL
RETURN
【实验目的】
1．使用视图向导创建本地视图
2．利用视图设计器创建视图
3．利用查询向导创建查询
【知识要点】
一、视图
1．视图是观察表记录的一个窗口，使用视图可以将表中一组记录提出来组成一个虚拟表，还可改变这些记录的值，并将更新结果送回原表。
视图依赖于某数据库中的表，只有打开此数据库，才能创建和使用视图。
视图有本地视图和远程视图两类。
2．使用本地视图向导创建本地视图
(1) 执行“工具／向导／全部”命令，在“向导选取”对话框中选择“本地视图向导”。
(2) 打开数据库文件，在“数据库设计器”窗口中执行“新建／视图”命令，单击“向导”按钮，进入“本地视图向导”窗口。
（3）使用本地视图向导
① 从“字段选取”窗口中选定一个表及所需字段，将其添加到“选定字段”框中
② 从“筛选记录”窗口中指定记录输出的条件
③ 从“排序记录”窗口中确定视图中记录显示的顺序
④ 从“限制记录”窗口中选择视图中记录显示的数目
⑤ 单击“完成”钮，输入视图名
3．使用远程视图向导创建远程视图（即用当前数据库之外的数据源建立视图）
调用远程视图向导和创建远程视图的方法与创建本地视图差不多，只是在创建时要先选择有效的数据源，它可以是在机器上注册的ODBC（开放式数据库连接）数据源或用连接设计器设计定义的连接。
4．使用视图设计器创建视图(要先打开一个数据库文件)
(1) 执行“文件／新建／视图”命令，单击“新建文件”按钮，进入“视图设计器”窗口。
① 从“添加表或视图”对话框中选定表(或视图)将其添加到“视图设计器”中。可以通过“其它”钮，选择自由表。
②“联接”选项（联接两个表）：
若添加了两个表，系统会自动给出两表相应字段，建立联接条件
③“字段”选项（指定输出的字段）,
从“可用字段”框中选择字段－“添加”到“选定字段”框
④“筛选”选项（指定记录输出的条件）,
在“字段名”栏中选择被筛选的字段（不能选通用型或备注型）
在“条件”栏中选定用于比较的类型
在“实例”栏中输入比较条件
（若查字符型数据时，要忽略大小写）选中“大小写”
（对于多个条件）在“逻辑”栏中添加AND或OR－（若用逻辑取反操作，即与条件相反的操作）选中“否”
⑤“排序依据”选项（确定记录输出时的顺序）：
从“选定字段”框中选择字段－“添加”到“排序条件”框－在“排序选项”框中确定升（降）序
⑥“分组依据”选项（按某字段(即某特征)分类，然后对字段中特征相同的数据进行统计计算，如求和、平均值、个数、最大值、最小值等）,
在(“字段”选项的“函数与表达式”框中）输入用于对某字段计算的函数表达式－“添加”到“选定字段”框（则多一个存放计算结果的字段）；
在(“分组依据”选项）“可用字段”框中选择用于分组的字段－“添加”到“分组字段”框中；
在“分组依据”选项的“满足条件”栏中）输入对字段计算的条件（类似“筛选”设置）
⑦,更新条件”选项（决定视图中修改数据传回数据源中表的方法和要求）：
在“字段名”框中选定可更新的字段(“钥匙”表示关键字段，最好不要更新；“铅笔”表示可更新的字段)
选择“发送SQL更新”项(否则修改内容不会传回数据源)
⑧“杂项”选项（对查询记录输出作限制）：
选“无重复记录”，则不输出重复记录
选“查询交叉表”，则对三个字段，用列头、行头、数据顶的形式显示查询结果
选“列在前面的记录”，可确定输出记录的个数
5．运行视图（一定要先打开相关的数据库或表）
(1) USE 视图名
BROWSE
(2) 双击数据库设计器中视图的图标
(3) 单击“视图设计器”工具栏中“运行”钮
6．修改视图(修改视图要在“视图设计器”中 进行，因此要先打开相关的数据库)
(1) 右击视图图标，选“修改”
(2) MODIFY VIEW 视图名二、查询
1．查询可从指定表或视图中找出符合条件的记录。它与视图不同的地方在于查询可作为一个文件单独存在，而视图只能存在于数据库中；查询的数据只能观看不能修改，而视图中数据可以修改并传回到原数据源中。
2．启动“查询设计器”
,文件/新建”－（类型）选“查询”－“新建文件”－选定表或视图－“添加”到“查询设计器”
3．建立查询：（用“查询设计器”建立查询的操作与用“视图设计器”建立视图类似，它还少一个“更新条件”）
（1）“联接”选项（联接两个表）：
（2）“字段”选项（指定输出的字段）,
（3）“筛选”选项（指定记录输出的条件）,
（4）“排序依据”选项（确定记录输出时的顺序）：
（5）“分组依据”选项（按某字段(即某特征)分类，然后对字段中特征相同的数据进行统计计算，如求和、平均值、个数、最大值、最小值等）,
（6）“杂项”选项（对查询记录输出作限制）：
4．使用查询
（1）（“查询设计器”）“查询/运行查询”
（2）“程序/运行”－选定查询文件－“运行”
（3）DO <查询文件名.QPR >
【实验内容】
１．用本地视图向导为“学生表”建立本地视图“学生表视图”，视图中包含“学号”、“班级”、“姓名”、“性别”、“入学成绩”五个字段内容，并要求只显示男生的记录
2．用视图设计器为“学生表”建立本地视图“学生视图2”，视图中包含“学号”、“班级”、“姓名”、“性别”、“入学成绩”五个字段内容，并要求只显示男生的记录
3．用查询向导建立查询“学生成绩查询”，以查询Xsda和Xscj表中数学系，且没有补考的学生的学号，姓名，系别，各科课程的考试成绩。
4．用“查询设计器”为“学生表”和“成绩表”这两个表建立查询“学生成绩查询2”，以显示计算机班男生的学号、班级、姓名、入学成绩、课程号、成绩
【高手进阶】
1．用“查询设计器”为“学生表”和“成绩表”这两个表建立查询“学生成绩查询3”，以查询各班学生考试平均成绩
2．用“查询设计器”为“学生表”建立姓名、班级和入学成绩的交叉表查询。
【综合测试】( 5分钟 )
根据“学生表”和“成绩表”，建立一个多表查询，完成查询“课程号”为1011的学生成绩信息。
【实验过程】
１．用本地视图向导为“学生表”建立本地视图“学生表视图”，视图中包含“学号”、“班级”、“姓名”、“性别”、“入学成绩”五个字段内容，并要求只显示男生的记录。
［解答］使用视图向导创建视图，要先打开一个数据库文件。
（1）启动本地视图向导执行“工具／向导／全部”命令，在“向导选取”对话框中选择“本地视图向导”。
［或：打开数据库文件，在“数据库设计器”窗口中执行“新建／视图”命令，单击“向导”按钮，进入“本地视图向导”窗口］
(2) 选定数据库“学生成绩管理”中的“学生表”，从“字段选取”窗口中选定所需字段，将其添加到“选定字段”框中
（3）从“筛选记录”窗口中指定记录输出的条件［没有就不指定。这里条件为显示男生］
（4）从“排序记录”窗口中确定视图中记录显示的顺序［没有就不指定。这里按学号升序排列］
(5) 从“限制记录”窗口中选择视图中记录显示的数目(若无特殊要求，就取默认值)
（6）单击“完成”钮，输入视图名“学生视图”，单击“确定”钮。则可将此视图保存到当前数据库中。
2．用视图设计器为“学生表”建立本地视图“学生视图2”，视图中包含“学号”、“班级”、“姓名”、“性别”、“入学成绩”五个字段内容，并要求只显示男生的记录。
［解答］使用视图设计器创建视图，要先打开一个数据库文件。
(1) 执行“文件／新建／视图”命令，单击“新建文件”按钮，进入“视图设计器”窗口。
① 从“添加表或视图”对话框中选定表(或视图)将其添加到“视图设计器”中。这时还可以通过“其它”钮，选择自由表。
②“联接”标签（联接两个表用的，这里只有一个表，因此不必管它）：
③“字段”标签（指定输出的字段）,
从“可用字段”框中选择“学号”、“班级”、“姓名”、“性别”、“入学成绩”五个字段 →逐一“添加”到“选定字段”框
④“筛选”标签（指定记录输出的条件：只显示男生）,
在“字段名”栏中选择被筛选的字段：“性别”（不能选通用型或备注型）
在“条件”栏中选定用于比较的类型：“=”
在“实例”栏中输入比较条件：“男”
⑤“排序依据”标签（确定记录输出时的顺序）：
从“选定字段”框中选择字段：如“学号”－“添加”到“排序条件”框－在“排序选项”框中确定升（降）序
⑥“分组依据”标签（按某字段(即某特征)分类，然后对字段中特征相同的数据进行统计计算，如求和、平均值、个数、最大值、最小值等。这里不用，因此不理它）,
⑦“更新条件”选项（决定视图中修改数据传回数据源中表的方法和要求）：
1）“表”框：指定对哪些表可以更新
2）“字段名”框,（设置关键字段）单击此框中字段旁的“关键”（钥匙）列
（指定可更新字段）单击此框中字段旁的“更新”（笔）列
3）选“发送SQL更新”：决定是否更新源表中数据，至少要设置一个关键字段
4）“使用更新”框,（确定如何进行更新）
选“SQL DELETE 然后 INSERT”：先删除原记录，再插入新记录选“SQL UPDATE”：直接修改原始记录
5）“SQL WHERE 子句包括”框：（在多用户环境中使用。更新记录前，检测是否其他用户正在修改）
选“关键字段”：若源表中关键字段已改，则不能更新
选“关键字段和可更新字段”：若源表中关键字段和可更新字段已改，则不能更新
选“关键字段和已修改字段”：若源表中任何字段已改，则不能更新从“钥匙”栏选定“学号”这个关键字段；在“铅笔”栏选定可更新的字段，如“姓名”、“性别”、“入学总分”等。注意还要选定“发送SQL更新”项。
⑧“杂项”标签（对查询记录输出作限制。取默认值，也是不理）：
⑨ 用“文件∕保存”命令（或Ctri + W）保存视图，取名“学生视图2”
⑩ 运行视图：双击数据库设计器中视图的图标、或单击“视图设计器”工具栏中“运行”钮。
3．用查询向导建立查询“学生成绩查询”，以查询Xsda和Xscj表中数学系，且没有补考的学生的学号，姓名，系别，各科课程的考试成绩
［解答］
（1）启动“查询设计器”，添加Xsda和Xscj表；
（2）从“字段选取”选项中选择学号、系别、姓名、高数、大语、邓论、英语、计算机等字段；
（3）在“为表建立关系”选项中为两表建立联接：在步骤2中按“添加”钮；
（4）选取匹配字段：因要显示两表中相应记录的值，故选取“仅包含匹配的行“项；
（5）从“筛选记录”选项中指定记录输出的条件,系别＝数学系
（6）从“排序记录”选项中指定按学号升序显示
（7）在“限制记录”选项中，取默认值；
（8）按“完成”钮，保存查询文件“学生成绩查询”；
（9）运行查询，用“程序/运行”－选定查询文件“学生成绩查询”－按“运行”钮（或在命令窗口中输入：DO 学生成绩查询.QPR。一定要带扩展名.QPR）
4．用“查询设计器”为“学生表”和“成绩表”这两个表建立查询“学生成绩查询2”，以显示计算机班男生的学号、班级、姓名、入学成绩、课程号、成绩
［解答］
(1) 执行“文件／新建／查询”命令，单击“新建文件”按钮，进入“查询设计器”窗口。
① 在查询设计器中添加两个表：学生表和成绩表，并建立联接；
② 在“字段”标签中，添加要显示的字段：学号、班级、姓名、入学成绩、课程号、成绩；
③ 在“筛选”标签中，确定显示条件：数学系男生(注意：要按下“逻辑”下的“AND”项)
④ 对“排序依据”、“分组依据”和“杂项”，可以暂不理会；
⑤ 保存：用“文件∕保存”或Ctri + W，保存查询文件为“学生成绩查询2”；
⑥ 运行查询：用“程序/运行”－选定查询文件“学生成绩查询2”－按“运行”钮（或在命令窗口中输入：DO 学生成绩查询2.QPR
【高手进阶解答】
1．用“查询设计器”为“学生表”和“成绩表”这两个表建立查询“学生成绩查询3”，以查询各班学生考试平均成绩
［解答］此题目要用“分组论据”。按某字段(即某特征)分类，然后对字段中特征相同的数据进行统计计算，如求和、平均值、计数、最大值、最小值等，它分为以下三步,
①（“字段”选项的“函数与表达式”框中）输入用于对某字段计算的函数表达式－“添加”到“选定字段”框（则多一个存放计算结果的字段）
②（“分组依据”选项）在,可用字段”框中选择用于分组的字段－“添加”到“分组字段”框
③（“分组依据”选项的“满足条件”栏中）输入对字段计算的条件（类似“筛选”的设置）
建立查询时在查询设计器中添加两个表、在“字段”标签中，添加要显示的字段（只要一个字段：班级）、在“筛选”标签中，确定显示条件（这里没有条件）、“排序依据”、“分组依据”和“杂项”等与上一题的操作基本一样；
1）在“分组依据”标签中，指定按班级分组（将“班级”添加到“分组字段”框中；
2）在“字段”选项的“函数与表达式”框中，输入用于对成绩表的成绩字段计算的函数表达式“AVG(成绩)”，按“添加”钮，将其添加到“选定字段”框（则在显示时会多一个存放计算平均成绩的字段）
完成后保存运行（因没有对字段进行计算的条件，所以“分组依据”选项的“满足条件”栏中可不输入筛选条件）。
2．用“查询设计器”为“学生表”建立姓名、班级和入学成绩的交叉表查询。
（1）打开“查询设计器”，向设计器添加“学生表”。
（2）在“字段”标签中，添加要显示的字段：姓名、班级、入学成绩。
注：只能添加三个字段，第一字段为“行”数据源、第二字段为“列”数据源、第三字段为“数据”数据源。
（3）在“杂项”标签中，选择“交叉数据表”项
（4）完成后保存运行，结果如下图。
【综合测试解答】
根据“学生表”和“成绩表”，建立一个多表查询，完成查询“课程号”为1011的学生成绩信息。
［解答］
（1）打开“查询设计器”。将“学生表”、“成绩表”加入到查询设计器。并建立联接；
（2）将学生表中的学号、姓名、班级和成绩表中的课程号、成绩等字段添加到“可用字段”中；
（3）在“筛选”选项卡中设置筛选条件：成绩表的课程号＝1011；
（4）在“排序依据”选项卡中，选择“成绩表,成绩”以“降序”方式添加到,排序条件”中。
（5）此题不需要“分组依据”，“杂项”取默认值；
（6）单击鼠标右键，选择“运行查询”，在“浏览”窗口中显示查询结果。
练习二 SQL查询功能（一）
【实验目的】
掌握SQL查询功能语句SELECT的操作；
熟练掌握单表查询；
熟练掌握带条件查询；
熟练掌握多表连接查询。
【知识要点】
一、关系数据库标准语言SQL
SQL是一个一体化、非过程化、功能强大的关系型数据库应用语言。它包括查询、定义（创建、删除和修改表）、操纵（插入、更新和删除记录）和控制四个方面的功能。
SQL功能
命令动词
数据查询
SELECT
数据定义
CREATE、DROP、ALTER
数据操纵
INSERT、UPDATE、DELETE
数据控制
GRANT、REVOKE
二、SQL查询：
查询是SQL的核心，SQL查询命令称为SELECT命令
1．单表查询
（1）基本查询语句（无条件查询）
SELECT [ALL/DISTINCT] 字段名表 FROM 表名
说明：
用“字段名1，字段名2，…”来指定要显示的字段、若用“*”代替，则表示选取所有字段；
有DISTINCT，则不显示相同的记录、用ALL，则显示全体记录，包括重复记录；
用“FROM 表名”指明要从哪个（些）表中查询数据。
（2）带条件（WHERE）的查询语句
SELECT [ALL/DISTINCT] 字段名表 FROM 表名 WHERE 条件式说明：
用“条件式”选择满足条件的记录，使用条件是查询的关键。
2．多表连接查询
SELECT [ALL/DISTINCT] 字段名表 FROM 表1[,表2…] WHERE 条件式说明：在条件式中一定要输入两表联接条件：“表1.某字段=表2.某字段”
要求这几个表间至少有一个相同的字段，这个相同字段用来作连接条件
【实验内容】
一、SQL查询语句SELECT
1．单表查询
（在“成绩表”中）
（1）显示表中的所有记录；
（2）显示表中的学号、课程号、成绩三个字段；
（3）查询表中有多少个学号（即不显示相同的学号）；
（4）查询表中成绩不及格的学生记录；
（在“学生表”表中）
（5）查询表中85年以后出生，且入学成绩大于560分的学生记录；
（6）查询表中85年出生的学生的学号，姓名，出生日期；
2．多表连接查询 ［注意连接条件的使用］
（1）从学生表和成绩表中查询学生记录
（2）从学生表和成绩表中查询成绩不及格的学生姓名、课程号、成绩
（3）2002年计算机等级考试［二级（数据库）上机考试试卷（有修改）］
1．在某商场管理系统中，以下2个数据表反映一个月内有关手机销售、库存情况。
（1）商品月库存表（Stockll.dbf）:
商品代码 品名 型号 单价 库存数量
2011 诺基亚 N8250 2250.00 16
2120 诺基亚 3310 990.00 18
4025 摩托罗拉 8088 2090.00 12
3122 爱立信 T29 1380.00 14
5203 西门子 2118 1470.00 18
（2）销售明细表（Sell.dbf）:
商品代码 日期 销售数量 营业员 销售金额
2120 2001-11-12 5 方虹
4025 2001-11-12 6 方虹
3122 2001-11-13 4 李之和
2120 2001-11-14 10 张广河显示营业员方虹本月销售商品、数量（这两个表已经做好，在配套材料“表”文件夹中）。
二、SQL综合练习：
1,下列语句的作用是什么？上机操作后给出答案。
（1）SELECT * FROM 学生表
（2）SELECT 学号,姓名,出生日期 FROM 学生表 WHERE 出生日期;
BETWEEN CTOD("01/01/84") AND CTOD("12/31/84")
（3）SELECT 姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND 成绩<60
（4）SELECT 学生表.学号,班级,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 成绩表.学号=学生表.学号 AND 成绩>60 ;
AND ( 班级="计算机" OR 班级="金融")
2.用数据库命令做的题，现在用SQL语句来做：
（1）顺序查询,
① 在学生表中找出85年出生的学生记录
SET TALK OFF
USE 学生表
LOCATE FOR SUBSTR(DTOC(出生日期),7,2) =”85”
DO WHILE NOT EOF()
DISPLAY
CONTINUE
ENDDO
（2）建立关联,
① 将１区中的当前表“Xsda”与２区中的表“Xscj”按关键字段“学号”建立关联，并浏览学号、姓名和英语及计算机的成绩
( 注意：要先在不同的区中打开这两个表，并要对子表建立索引－对关键字“学号”)
SELECT 1 && 指定工作区1为当前工作区
USE Xsda && 在工作区1中打开Xsda表
SELECT 2 && 指定工作区２为当前工作区
USE Xscj && 在工作区２中打开Xscj表
INDEX ON 学号 TAG XH && 为成绩表（对学号）建立索引（名叫XH）
SELECT 1 && 指定工作区1为当前工作区
SET RELATION TO 学号 INTO 2
&& 按“学号”对２区（的成绩表）建立关联
BROWSE FIELDS 学号,姓名,B.英语,B.计算机
&& 子表记录前要加前缀：“别名-->”或“别名．”
【高手进阶】
嵌套查询：
将一个查询的结果，作为另一个查询的条件，这种查询中带查询，就是嵌套查询。
例1：从学生表和成绩表中查学生迪来的成绩和课程号
［解答］SELECT 课程号,成绩 FROM 成绩表 WHERE 学号= ;
(SELECT 学号 FROM 学生表 WHERE 姓名=”迪来”)
先从内层查询“SELECT 学号 FROM 学生表 WHERE 姓名="迪来"”，查出“迪来”的“学号”是05011013 ；然后从外层查询“SELECT 课程号,成绩 FROM 成绩表 WHERE 学号 = 05011013”,查学号是05011013的学生的课程号和成绩。
（1）从Xsda表中，查询所有与“汪洋”籍贯相同的学生姓名
（2）如果不用嵌套查询，可不可以？
例2：从学生表和成绩表中查询成绩不及格的学生姓名。
［解答］SELECT 姓名 FROM 学生表 WHERE 学号 IN (SELECT 学号 FROM 成绩表 WHERE 成绩<60)
内层查询“SELECT 学号 FROM 成绩表 WHERE 成绩<60”，查出成绩不及格的学生的学号，由于这些学号不只一个，所以不能用等号，而必须采用谓词IN，IN的含义是属于，即可等于这许多学号中的任一个，则外层查询就是查询这些学号的学生的姓名。
（1）从学生表和成绩表中查询计算机班的学生的各科成绩。
（2）从学生表和成绩表中查询0502系科学生的学号，课程号和成绩。
2．从键盘输入学号，在“成绩表”、“学生表”表中查询显示该学号学生的姓名及各门课程的成绩。
以前数据库命令，先建立关联，再查询（见下面程序）。现在用SQL语句，该如何写？
SET TALK OFF
SELECT 1
USE 学生表
INDEX ON 学号 TAG Xx
SELECT 2
USE 成绩表
SET RELATION TO 学号 INTO A
ACCEPT,请输入学生学号：” TO Xh
LOCATE FOR 学号 = Xh
DO WHILE NOT EOF()
DISPLAY 学号,学生表.姓名,课程号,成绩
CONTINUE
ENDDO
RETURN
【综合测试】( 5分钟 )
在学生表和成绩表中查找姓名为“王二春”的记录，若他有一科的成绩大于80分，则显示他的学号、姓名、课程号和成绩。
【实验过程】
一、SQL查询语句SELECT
1．单表查询
（在“成绩表”中）
（1）显示表中的所有记录；
［操作］SELECT * FROM 成绩表
（2）显示表中的学号、课程号、成绩三个字段；
［操作］SELECT 学号,课程号,成绩 FROM 成绩表
或：SELECT * FROM 成绩表
（3）查询表中有多少个学号（即不显示相同的学号）
［操作］SELECT DISTINCT 学号 FROM 成绩表
（4）查询表中成绩不及格的学生记录
［操作］SELECT * FROM 成绩表 WHERE 成绩<60
（在“学生表”表中）
（5）查询表中85年以后出生，且入学成绩大于560分的学生记录
［操作］SELECT * FROM 学生表 ;
WHERE 入学成绩>560 AND YEAR(出生日期)>1985
或：
SELECT * FROM 学生表 ;
WHERE 入学成绩>560 AND 出生日期>CTOD("12/31/85")
（6）查询表中85年出生的学生的学号、姓名、出生日期
［操作］SELECT 学号,姓名,出生日期 FROM 学生表 ;
WHERE 出生日期>= CTOD("01/01/85") AND 出生日期 <= CTOD("12/31/85")
或：
SELECT 学号,姓名,出生日期 FROM 学生表 WHERE YEAR(出生日期)= 1985
2．多表连接查询
（1）从学生表和成绩表中查询学生记录
［操作］SELECT * FROM 学生表,成绩表 WHERE 学生表.学号=成绩表.学号或（用数据库操作命令）：
SELECT 1
USE 成绩表
INDEX ON 学号 TAG XH
SELECT 2
USE 学生表
SET RELATION TO 学号 INTO A
LIST 学号,姓名,A.课程号,A.成绩
（2）从学生表和成绩表中查询成绩不及格的学生姓名、课程号、成绩
［操作］SELECT 姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND 成绩<60
或（用数据库操作命令）：
SELECT 1
USE 学生表
INDEX ON 学号 TAG XH
SELECT 2
USE 成绩表
SET RELATION TO 学号 INTO A
LIST A.姓名,课程号,成绩 FOR 成绩<60
（3）2002年计算机等级考试［二级（数据库）上机考试试卷（有修改）］
1.在某商场管理系统中，以下2个数据表反映一个月内有关手机销售、库存情况。
（1）商品月库存表（Stockll.dbf）,（注：这两个表已经做好，在“表”文件夹中）
商品代码 品名 型号 单价 库存数量
2011 诺基亚 N8250 2250.00 16
2120 诺基亚 3310 990.00 18
4025 摩托罗拉 8088 2090.00 12
3122 爱立信 T29 1380.00 14
5203 西门子 2118 1470.00 18
（2）销售明细表（Sell.dbf）:
商品代码 日期 销售数量 营业员 销售金额
2120 2001-11-12 5 方虹
4025 2001-11-12 6 方虹
3122 2001-11-13 4 李之和
2120 2001-11-14 10 张广河显示营业员方虹本月销售商品、数量
［操作］SELECT 营业员,日期,品名,销售数量 FROM Stockll,Sell ;
WHERE Stockll.商品代码= Sell.商品代码 AND 营业员 =“方虹”
或（用数据库操作命令）,
SET TALK OFF
SET SAFETY OFF
SET DATE YMD
CLEAR
SELECT 1
USE Stockll
INDEX ON 商品代码 TO SP
SELECT 2
USE Sell
SET RELATION TO 商品代码 INTO A
LIST 营业员,日期,A->品名,销售数量 FOR 营业员 =“方虹”
二、SQL综合练习：
1,下列语句的作用是什么？上机操作后给出答案。
（1）SELECT * FROM 学生表
［解答］显示学生表中的所有记录
（2）SELECT 学号,姓名,出生日期 FROM 学生表 WHERE 出生日期;
BETWEEN CTOD("01/01/84") AND CTOD("12/31/84")
［解答］显示学生表中出生日期在84年的学生的学号，姓名和出生日期
（3）SELECT 姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND 成绩<60
［解答］从学生表和成绩表中查询成绩不及格的学生姓名、课程号、成绩
（4）SELECT 学生表.学号,班级,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 成绩表.学号=学生表.学号 AND 成绩>60 ;
AND ( 班级="计算机" OR 班级="金融")
［解答］从学生表和成绩表中，查询并显示成绩在60分以上的计算机班或金融班学生的学号、班级、课程号及成绩
2,用数据库命令做的题，现在用SQL语句来做：
（1）顺序查询,
① 在学生表中找出85年出生的学生记录
SET TALK OFF
USE 学生表
LOCATE FOR YEAR(出生日期)=1985
DO WHILE NOT EOF()
DISPLAY
SKIP
ENDDO
RETURN
［操作］SELECT * FROM 学生表 WHERE YEAR(出生日期) = 1985
（2）多表联结,
① 将１区中的当前表“Xsda”与２区中的表“Xscj”按关键字段“学号”建立关联，并浏览学号、姓名和英语及计算机的成绩
( 注意：要先在不同的区中打开这两个表，并要对子表建立索引－对关键字“学号”)
SELECT 1 && 指定工作区1为当前工作区
USE Xscj && 在工作区1中打开Xscj表
INDEX ON 学号 TAG XH && 为成绩表（对学号）建立索引（名叫XH）
SELECT 2 && 指定工作区2为当前工作区
USE Xsda && 在工作区2中打开Xsdaj表
SET RELATION TO 学号 INTO A
&& 按“学号”对1区（的成绩表）建立关联
BROWSE FIELDS 学号,姓名,A.英语,A.计算机
&& 子表记录前要加前缀：“别名-->”或“别名．”
［操作］SELECT Xsda.学号,姓名,英语,计算机 FROM Xsda,Xscj ;
WHERE Xsda.学号 = Xscj.学号
【高手进阶解答】
1．嵌套查询：
将一个查询的结果，作为另一个查询的条件，这种查询中带查询，就是嵌套查询。
例1：从学生表和成绩表中查学生迪来的成绩和课程号
［解答］SELECT 课程号,成绩 FROM 成绩表 WHERE 学号 = ;
(SELECT 学号 FROM 学生表 WHERE 姓名=”迪来”)
先从内层查询“SELECT 学号 FROM 学生表 WHERE 姓名="迪来"”，查出“迪来”的“学号”是05011013 ；然后从外层查询“SELECT 课程号,成绩 FROM 成绩表 WHERE 学号 = 05011013”,查学号是05011013的学生的课程号和成绩。
（1）从Xsda表中，查询所有与“汪洋”籍贯相同的学生姓名
［解答］
SELECT 姓名 FROM Xsda ;
WHERE 籍贯 = (SELECT 籍贯 FROM Xsda WHERE 姓名="汪洋")
（2）如果不用嵌套查询，可不可以？
［解答］先查出汪洋的籍贯是“重庆”
SELECT 籍贯 FROM Xsda WHERE 姓名="汪洋"
再用一句语句查籍贯是“重庆”的学生姓名
SELECT 姓名 FROM Xsda WHERE 籍贯 = "重庆"
例2：从学生表和成绩表查询成绩不及格的学生姓名。
［解答］
SELECT姓名 FROM 学生表 WHERE 学号 IN ;
(SELECT 学号 FROM 成绩表 WHERE 成绩<60)
内层查询“SELECT 学号 FROM 成绩表 WHERE 成绩<60”，查出成绩不及格的学生的学号，由于这些学号不只一个，所以不能用等号，而必须采用谓词IN，IN的含义是属于，即可等于这许多学号中的任一个，则外层查询就是查询这些学号的学生的姓名。
（1）从学生表和成绩表中查询计算机班的学生的各科成绩。
［解答］
SELECT 学号,课程号,成绩 FROM 成绩表 WHERE 学号 IN ;
(SELECT 学号 FROM 学生表 WHERE 班级="计算机")
（2）从学生表和成绩表中查询0502系科学生的学号，课程号和成绩。
［解答］
SELECT学号,课程号,成绩 FROM 成绩表 WHERE 学号 IN ;
(SELECT 学号 FROM 学生表 WHERE SUBSTR(学号,1,4)= "0502")
2．从键盘输入学号，在“成绩表”、“课程表”表中查询显示该学号学生的各门课程的成绩。
［解答］
ACCEPT,请输入学生学号：” TO Xh
SELECT 成绩表.学号,姓名,课程号,成绩 FROM 成绩表,学生表 ;
WHERE 成绩表.学号 = 学生表.学号 AND成绩表.学号 = Xh
【综合测试解答】
在学生表和成绩表中查找姓名为“王二春”的记录，若他有一科的成绩大于80分，则显示他的学号、姓名、课程号和成绩。
［解答］
（1） SELECT 学生表.学号,姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND 姓名="王二春" AND 成绩>80
［这程序只查询王二春80分以上的记录］
（2）SELECT 学生表.学号,姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND 姓名="王二春" AND ;
成绩表.学号 IN(SELECT 学号 FROM 成绩表 WHERE 成绩>80)
［从内查询中查出所有成绩在80分以上的学号，在外查询中若王二春的学号在其中的话，则显示他的所有科目和成绩（含80分以下的）］
（3）ACCEPT,请输入学生姓名：” TO Xm
SELECT 学生表.学号,姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND 姓名=Xm AND ;
成绩表.学号 IN(SELECT 学号 FROM 成绩表 WHERE 成绩>80)
［这程序可查任意输入姓名的满足条件（只要有一科在80分以上）的学生所有记录。王双春三科成绩均在80分以下，你看看能不能显示他的记录］
练习三 SQL查询功能（二）
【实验目的】
1．熟练掌握SQL查询功能语句SELECT的操作；
2．熟练掌握简单计算功能；
3．熟练掌握几个特殊的运算符。
【知识要点】
一、关系数据库标准语言SQL
3．简单的计算查询
SQL可以计算方式进行查询，常用的计算函数有：
① COUNT：计数
② SUM：求和
③ AVG：求平均值
④ MAX：求最大值
⑤ MIN：求最小值
4．几个特殊的运算符
① BETWEEN……AND …… 表示在“……和……之间”
② LIKE 表示与……匹配，用于“模糊”查询
它有两种匹配方式：“＿”（下划线）表示一个字符；“%”表示任意多个字符
5．排序子句
ORDER BY 字段 ［ASC/DESC］ 将查询结果排序输出
将记录按升序（ASC）［默认］或降序（DESC）排序
【实验内容】
一、SQL查询语句SELECT
1．简单的计算查询
（1）查询学生表中男学生的人数
（2）查询成绩表中课程号为1021的成绩总和
（3）查询Xscj表中计算机成绩在80分以上的人数、总分和平均分
（4）显示Xsda表中姓“张”学生的平均年龄
（5）从Xsda表和Xscj表查询英语系学生计算机考试的总分、平均分、最高分和参考人数
[要用Xsda表，取系别、Xscj表，取成绩]
2．几个特殊的运算符 ① BETWEEN……AND …… ；② LIKE ；③ ORDER BY
（1）查询学生表中83年出生的学生的学号，姓名，出生日期
（2）查询学生表中83到84年出生的学生的学号、姓名、出生日期
（3）查询学生表中入学成绩在500到600分的学生
（4）显示学生表中姓“王”的学生
（5）显示学生表中叫“王X”的学生
（6）显示学生表中叫“王XX”的学生
（7）显示学生表中姓名中含“王”字的学生（比较一下，这四题有何不同？理解%与_的区别）
（8）按入学成绩分数升序显示学生表的学生记录
（9）按入学成绩分数降序显示学生表中学生的学号、姓名和入学成绩记录
（10）按入学成绩分数降序显示学生表中500分以上的学生的学号、姓名和入学成绩二、SQL综合练习：
1．综合运用SELECT语句
（1）从学生表和成绩表中查询84年出生的学生的学号、姓名、出生日期、入学成绩、课程号和考试成绩
（2）从学生表和成绩表中查询姓张的学生的学号、姓名、入学成绩
（3）从学生表和成绩表中查询成绩在80到90分的学生的学号、姓名、课程号和成绩
（4）查询并显示学生表和成绩表中05级各个学生的学号、姓名、课程号及成绩
（5）查询并显示学生表和成绩表中“信息工程系”的各个学生的学号、姓名、各科课程的成绩（学号中第3到5位是011，则表示该生是“信息工程系”的）
（6）查询并显示学生表和成绩表中姓张和姓李的学生的学号、姓名、各科课程号及成绩
(7) 从学生表查询所有记录的学号、姓名、班级、入学成绩等内容，并按入学成绩降序排列
(8) 从学生表查询入学成绩在480分以上的学号、姓名、性别、团员否、入学成绩等内容，并按入学成绩升序排列
(9) 用两种方法从成绩表查询成绩在85至95分之间学号、课程号、成绩等内容，并按成绩降序排列
（10）从学生表和成绩表中查询姓王的学生的人数和考试平均成绩
2．下列语句的作用是什么？
（1）SELECT * FROM 学生表 WHERE 姓名 LIKE "李_"
（2）SELECT * FROM 学生表 ORDER BY 入学成绩 DESC
（3）SELECT 姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号= 成绩表.学号 AND 成绩<60
（4）SELECT * FROM 学生表 WHERE 姓名 LIKE "%李% "
（5）SELECT 学号,课程号,成绩 FROM 成绩表 ;
WHERE 成绩>80 ORDER BY 成绩
3．以前的题，现在用SQL语句来做：
（1）顺序查询,
在Xsda表中找出姓“张”的大于20岁的男生记录
USE Xsda
LOCATE ALL FOR SUBSTR(姓名,1,2) =”张” AND 年龄>20 AND 性别=”男”
DISPLAY
CONTINUE
DISPLAY
……
（2）数据统计：
① 统计学生表中入学成绩的最大值、最小值、总和、平均值
USE 学生表
CALCULATE MAX(入学成绩),MIN(入学成绩),SUM(入学成绩),AVG(入学成绩)
② 统计成绩表中1021课程的最大值、最小值、总和、平均值
USE 成绩表
CALCULATE MAX(成绩),MIN(成绩),SUM(成绩),AVG(成绩) ;
FOR 课程号=”1021”
【高手进阶】
1．从学生表查询所有记录的学号、姓名、班级、入学成绩等内容，并按班级入学成绩升序排列，同班同学按入学成绩降序排列
2．查询 Xscj表中计算机成绩在80分以上的人数和总分，并使用AS指定列标题
3．分组查询：GROUP BY子句将查询的结果进行分组，可进行分组汇总。
例1：从学生表中按班级统计各班学生的人数。
［解答］SELECT COUNT(*) FROM 学生表 GROUP BY 班级
（1）上题中，如果同时要显示班级名称，如何做？
（2）在成绩表中，按课程号统计各科的课程号、总分、平均分和参考人数
【自我测试】( 5分钟 )
1．在学生表中统计姓“张”学生的人数。
2．从学生表和成绩表中查询姓张的学生的人数和考试平均成绩。
【实验过程】
一、SQL查询语句SELECT
1．简单的计算查询
（1）查询学生表中男学生的人数
［解答］SELECT COUNT(学号) FROM 学生表 WHERE 性别=”男”
或：
SELECT COUNT(*) FROM 学生表 WHERE 性别= "男"
注：COUNT(*)是COUNT的特殊形式，用于统计WHERE子句中逻辑表达式的记录的个数。
（2）查询成绩表中课程号为1021的成绩总和
［解答］SELECT SUM(成绩) FROM 成绩表 WHERE 课程号=”1021”
（3）查询 Xscj表中计算机成绩在80分以上的人数、总分和平均分
［解答］SELECT COUNT(*),SUM(计算机),AVG(计算机) FROM Xscj WHERE 计算机>=80
（4）显示 Xsda表中姓“张”学生的平均年龄
［解答］SELECT AVG(年龄) FROM Xsda WHERE SUBSTR(姓名,1,2)="张"
（5）从Xsda表和Xscj表查询英语系学生计算机考试的总分、平均分、最高分和参考人数
[要用Xsda表取系别、Xscj表取成绩]
［解答］SELECT COUNT(*),SUM(计算机),AVG(计算机),MAX(计算机) FROM Xsda,Xscj;
WHERE Xsda.学号= Xscj.学号 AND 系别= "英语系"
2．几个特殊的运算符 ① BETWEEN……AND …… ；② LIKE ；③ ORDER BY
（1）查询学生表中83年出生的学生的学号，姓名，出生日期
［解答］SELECT 学号,姓名,出生日期 FROM 学生表 ;
WHERE 出生日期 BETWEEN CTOD("01/01/83") AND CTOD("12/31/83")
（2）查询学生表中83到84年出生的学生的学号、姓名、出生日期
［解答］SELECT 学号,姓名,出生日期 FROM 学生表 ;
WHERE YEAR(出生日期) BETWEEN 1983 AND 1984
（3）查询学生表中入学成绩在500到600分的学生
［解答］SELECT * FROM 学生表 WHERE 入学成绩>500 AND 入学成绩<600
或：（比较一下，两者有何不同？）
SELECT * FROM 学生表 WHERE 入学成绩 BETWEEN 500 AND 600
（4）显示学生表中姓“王”的学生
［解答］SELECT * FROM 学生表 WHERE SUBSTR(姓名,1,2)="王"
或：SELECT * FROM 学生表 WHERE 姓名 LIKE "王%"
（5）显示学生表表中叫“王X”的学生
［解答］SELECT * FROM 学生表 WHERE 姓名 LIKE "王_"
（6）显示学生表表中叫,王XX”的学生
［解答］SELECT * FROM 学生表 WHERE 姓名 LIKE "王__"
（7）显示学生表表中姓名中含,王”字的学生（比较一下，这四题有何不同？理解%与_的区别）
［解答］SELECT * FROM 学生表 WHERE 姓名 LIKE "%王%"
（8）按入学成绩分数升序显示学生表的学生记录
［解答］SELECT * FROM 学生表 ORDER BY 入学成绩 ASC
（9）按入学成绩分数降序显示学生表中学生的学号、姓名和入学成绩记录
［解答］SELECT 学号,姓名,入学成绩 FROM 学生表 ORDER BY 入学成绩 DESC
（10）按入学成绩分数降序显示学生表中500分以上的学生的学号、姓名和入学成绩
［解答］ SELECT 学号,姓名,入学成绩 FROM 学生表 ;
ORDER BY 入学成绩 DESC WHERE 入学成绩>500
二、SQL综合练习：
1．综合运用SELECT语句
（1）从学生表和成绩表中查询84年出生的学生的学号、姓名、出生日期、入学成绩、课程号和考试成绩
［解答］SELECT 学生表.学号,姓名,出生日期,入学成绩,课程号,成绩 ;
FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 ;
AND 出生日期 BETWEEN CTOD("01/01/84") AND CTOD("12/31/84")
（2）从学生和成绩表中查询姓张的学生的学号、姓名、入学成绩
［解答］SELECT 学生表.学号,姓名,入学成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND SUBSTR(姓名,1,2) = "张"
或：
SELECT 学生表.学号,姓名,入学成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND 姓名 LIKE "张%"
（3）从学生表和成绩表中查询成绩在80到90分的学生的学号、姓名、课程号和成绩
［解答］SELECT 学生表.学号,姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND 成绩 BETWEEN 80 AND 90
（4）查询并显示学生表和成绩表中05级各个学生的学号、姓名、课程号及成绩
［解答］SELECT 学生表.学号,姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND 学生表.学号 LIKE "05%"
或：
SELECT 学生表.学号,姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND SUBSTR(学生表.学号,1,2)= "05"
（5）查询并显示学生表和成绩表中“信息工程系”的各个学生的学号、姓名、各科课程的成绩（学号中第3到5位是011，则表示该生是“信息工程系”的）
［解答］SELECT 学生表.学号,姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND 学生表.学号 LIKE "__011%"
或：
SELECT 学生表.学号,姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND SUBSTR(学生表.学号,3,3)= "011"
（6）查询并显示学生表和成绩表中姓张和姓李的学生的学号、姓名、各科课程号及成绩
［解答］SELECT 学生表.学号,姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 ;
AND ( 姓名 LIKE "张%" OR 姓名 LIKE "李%" )
(7) 从学生表查询所有记录的学号、姓名、班级、入学成绩等内容，并按入学成绩降序排列
［解答］SELECT 学号,姓名,班级,入学成绩 FROM 学生表 ORDER BY 入学成绩 DESC
(8) 从学生表查询入学成绩在480分以上的学号、姓名、性别、团员否、入学成绩等内容，并按入学成绩升序排列
［解答］SELECT 学号,姓名,性别,团员否,入学成绩 FROM 学生表 ;
WHERE 入学成绩>=480 ORDER BY 入学成绩
(9) 用两种方法从成绩表查询成绩在85至95分之间学号、课程号、成绩等内容，并按成绩降序排列
［解答］
① SELECT 学号,课程号,成绩 FROM 成绩表 ;
WHERE 成绩>=85 AND 成绩<=95 ORDER BY 成绩 DESC
② SELECT 学号,课程号,成绩 FROM 成绩表 ;
WHERE 成绩 BETWEEN 85 AND 95 ORDER BY 成绩 DESC
（10）从学生表和成绩表中查询姓王的学生的人数和考试平均成绩
［解答］SELECT COUNT(*),AVG(成绩) FROM 学生表,成绩表 ;
WHERE 学生表.学号= 成绩表.学号 AND姓名 LIKE "王%"
2．下列语句的作用是什么？
（1）SELECT * FROM 学生表 WHERE 姓名 LIKE "李_"
［解答］显示学生表中叫“李X”的学生
（2）SELECT * FROM 学生表 ORDER BY 入学成绩 DESC
［解答］显示学生表的学生记录，并按入学成绩分数降序显示
（3）SELECT 姓名,课程号,成绩 FROM 学生表,成绩表 ;
WHERE 学生表.学号= 成绩表.学号 AND 成绩<60
［解答］从学生表和成绩表中查询考试成绩不及格的学生姓名、课程号和考试成绩
（4）SELECT * FROM 学生表 WHERE 姓名 LIKE "%李% "
［解答］显示学生表表中姓名中有“李”字的学生
（5）SELECT 学号,课程号,成绩 FROM 成绩表 ;
WHERE 成绩>80 ORDER BY 成绩
［解答］按考试成绩升序显示成绩表中80分以上学生的学号、课程号和成绩
3．以前的题，现在用SQL语句来做：
（1）顺序查询,
在学生表中找出姓“张”的大于20岁的男生记录
USE Xsda
LOCATE ALL FOR SUBSTR(姓名,1,2) =”张” AND 年龄>20 AND 性别=”男”
DISPLAY
CONTINUE
DISPLAY
……
［解答］
SELECT * FROM Xsda ;
WHERE 性别 = "男" AND 年龄 > 20 AND 姓名 LIKE "张%"
（2）数据统计：
① 统计学生表中入学成绩的最大值、最小值、总和、平均值
USE 学生表
CALCULATE MAX(入学成绩),MIN(入学成绩),SUM(入学成绩),AVG(入学成绩)
［解答］
SELECT MAX(入学成绩),MIN(入学成绩),SUM(入学成绩),AVG(入学成绩) FROM 学生表
② 统计成绩表中1021课程的最大值、最小值、总和、平均值
USE 成绩表
CALCULATE MAX(成绩),MIN(成绩),SUM(成绩),AVG(成绩) ;
FOR 课程号=”1021”
［解答］
SELECT MAX(成绩),MIN(成绩),SUM(成绩),AVG(成绩) FROM 成绩表;
WHERE 课程号=”1021”
【高手进阶解答】
1．从学生表查询所有记录的学号、姓名、班级、入学成绩等内容，并按班级入学成绩升序排列，同班同学按入学成绩降序排列
［解答］
SELECT 学号,姓名,班级,入学成绩 FROM 学生表 ORDER BY 班级，入学成绩 DESC
2．查询 Xscj表中计算机成绩在80分以上的人数和总分，并使用AS指定列标题
［解答］短语AS，可以指定输出的列标题，使输出更容易被人理解
SELECT COUNT(*) AS 人数,SUM(计算机) AS 总分 FROM Xscj WHERE 计算机>=80
3．分组查询：GROUP BY子句将查询的结果进行分组，可进行分组汇总。
例1：从学生表中按班级统计各班学生的人数。
［解答］SELECT COUNT(*) FROM 学生表 GROUP BY 班级
（1）上题中，如果同时要显示班级名称，如何做？
［解答］
SELECT 班级,COUNT(*) FROM 学生表 GROUP BY 班级
（2） 在成绩表中，按课程号统计各科的课程号、总分、平均分和参考人数
［解答］
SELECT 课程号,SUM(成绩) AS 总分,AVG(成绩) AS 平均分,COUNT(*) AS 参考人数;
FROM 成绩表 GROUP BY 课程号
【自我测试解答】( 5分钟 )
1．在学生表中统计姓“张”学生的人数。
［解答］SELECT COUNT(*) FROM 学生表 WHERE 姓名 LIKE "张%"
2．从学生表和成绩表中查询姓张的学生的人数和考试平均成绩
［解答］SELECT COUNT(*),AVG(成绩) FROM 学生表,成绩表 ;
WHERE 学生表.学号=成绩表.学号 AND 姓名 LIKE "张%"
练习四 SQL定义功能
【实验目的】
1．掌握SQL定义功能语句的操作；
2．熟练掌握表的定义（CREATE）；
3．熟练掌握表的删除（DROP）；
4．熟练掌握表结构的修改、添加和修改字段（ALTER）。
【知识要点】
表的定义：创建一个新表
CREATE TABLE 表名（字段名 类型［，字段名 类型］……）
说明：
（1）常用数据类型有字符型：C(n)；:数值型：N(n,d)；日期型：D(默认为8位)；逻辑型：L(默认为1位)等；
（2）建表前应对此表的结构、内容，做到心中有数，即清楚要有几列、取什么名字、什么类型、宽度多大；
（3）在建立表之前，应先建立一个数据库。在此库中定义表。
建立数据库：CREATE DATABASE 数据库名
（4）以前的数据库操作命令：CREATE 表名 → 进入“表设计器”
2．表的删除：删除一个表
DROP TABLE 表名说明：
（1）直接从磁盘上删除表名所对应的.dbf文件；
（2）如果表是数据库中的表，则从数据库中删除此表（对数据库中的表，应先打开数据库，再删除表，否则以后会出现错误信息）。
3．表结构的修改：类似“表设计器”的操作
（1）添加∕修改字段
ALTER TABLE 表名 ADD/ALTER 字段名 类型（宽度［,小数］）
说明：
① 可添加（ADD）字段或修改字段（ALTER），字段名后应带类型和宽度；
② 不能修改字段名和删除字段。
（2）删除字段、更改字段名
ALTER TABLE 表名 DROP 字段名 RENAME 字段名1 TO 字段名2
说明：
① DROP 字段名 从指定表中删除指定字段。
② RENAME 字段名1 TO 字段名2 从指定表中将字段名1改为字段名2
③ 或,
ALTER TABLE 表名 DROP 字段名 删除字段
ALTER TABLE 表名 RENAME 字段名1 TO 字段名2 修改字段名
注：表结构修改的操作在第3章中修改表结构中是用“表设计器”和命令“MODIFY STRUCTURE”进行的
【实验内容】
二、SQL定义语句
1．表的定义（CREATE）
（1）创建“学生表A”：学号（C，8）、姓名（C，6）、、入学成绩（N，3）
（2）在D盘根目录下创建“学生表B”：学号（C，8）、姓名（C，6）、入学成绩（N，5，1）、简历（M）、相片（G）
（3）在D盘根目录下先创建一个数据库“学生管理”，在此数据库中创建“学生表C”：学号（C，8）、姓名（C，6）、出生日期（D）、入学成绩（N，5，1）
2．表的删除：（DROP）
（1）删除D 盘根目录中的“学生表A”
（2）删除D 盘根目录的数据库“学生管理”中的“学生表C”
3．表结构的修改（ALTER）
（1）添加∕修改字段
1）添加字段
① 在D盘根目录的“学生表B”中添加一个N（2）类型的字段：年龄
② 在D盘根目录的“学生表B”中添加两个字段：班级(C，8)、团员否(L)
2）修改字段
① 将D盘根目录的“学生表B”中姓名字段的宽度由原来的6改为8
② 将D盘根目录的“学生表B”中学号字段的类型改成N型、宽度改为6（1位小数）
（2）删除字段、更改字段名
1）删除字段
① 将Xscj表中的“等级”字段删除
② 将D盘根目录的“学生表B”的“年龄”字段删除
2）更改字段名
① 将Xscj表中的“平均”字段改名为“平均分”
② 将D盘根目录的“学生表B”中字段“相片”改名为“像片”
4．综合练习
（1）在D盘根目录中创建如下表的“学生表A”（见P43）
字段名
字段类型
字段宽度
小数点
学号
字符型
8
姓名
字符型
8
性别
字符型
2
出生日期
日期型
8
团员否
逻辑型
1
入学成绩
数值型
6
2
班级
字符型
10
照片
通用型
4
简历
备注型
4
（2）为“学生表A”中添加一个字符型字段“系别”（宽度：10）
（3）将“学生表A”的字段“出生日期”改成“出生年月”
（4）将“学生表A”的“入学成绩”字段的宽度改为3，小数去掉
（5）将“学生表A”的“团员否”字段改为字符型，宽度改为2
（6）将“学生表A”的“班级”字段删除
（7）删除“学生表A”
【高手进阶】
（1）在D盘根目录中创建如下表的“学生表B”
字段名
字段类型
字段宽度
学号
字符型
7
专业
字符型
4
姓名
字符型
6
性别
字符型
2
出生年月
日期型
8
团员
逻辑型
1
（2）用“INSERT”命令为“学生表B”添加如下三条记录学号
专业
年级
姓名
性别
出生年月
团员
9103001
通讯
91
张佳
女
09/23/82
.T.
9101016
软件
91
陈莉
男
11/03/83
.T.
9102015
应用
91
王磊
男
04/26/82
.F.
注：用“INSERT”命令可对当前表插入记录
INSERT INTO 表名 VALUES(对应列名1的值,...,对应列名n的值)
【自我测试】( 5分钟 )
（1）在D盘根目录中创建如下表的“学生表C”
字段名
字段类型
字段宽度
小数点
学号
字符型
6
班级
字符型
6
姓名
字符型
6
出生年月
日期型
8
入学成绩
数值型
5
2
（2）为“学生表C”中添加一个字符型字段“性别”（宽度：2）
（3）将“学生表C”的“班级”字段删除
【实验过程】
二、SQL定义语句
1．表的定义（CREATE）
（1）创建“学生表A”：学号（C，8）、姓名（C，6）、、入学成绩（N，3）
［解答］
CREATE TABLE 学生表A (学号 C(8),姓名 C(6),出生日期 D,入学成绩 N(3))
注意：
字段名后加一空格再输入数据类型后用括号输入宽度，系统默认的宽度就不输入；
所有字段内容用一括号括起紧接在表名的后面。
（2）在D盘根目录下创建“学生表B”：学号（C，8）、姓名（C，6）、入学成绩（N，5，1）、简历（M）、相片（G）
［解答］
CREATE TABLE D:\学生表B (学号 C(8),姓名 C(6),入学成绩 N(5,1),简历 M,相片 G)
（3）在D盘根目录下先创建一个数据库“学生管理”，在此数据库中创建“学生表C”：学号（C，8）、姓名（C，6）、出生日期（D）、入学成绩（N，5，1）
［解答］
CREATE DATABASE D:\学生管理 先建立数据库
CREATE TABLE 学生表C (学号 C(8),姓名 C(6),出生日期 D,入学成绩 N(5,1))
2．表的删除（DROP）
（1）删除D 盘根目录中的“学生表A”
［解答］
DROP TABLE D:\学生表A
（2）删除D 盘根目录的数据库“学生管理”中的“学生表C”
［解答］
OPEN DATABASE D:\学生管理 先打开数据库
DROP TABLE 学生表C
另类删除：可通过Windows的删除命令进行删除。但对数据库中的表最好移出去再删除。
3．表结构的修改（ALTER）
（1）添加∕修改字段
1）添加字段
① 在D盘根目录的“学生表B”中添加一个N（2）类型的字段：年龄
ALTER TABLE D:\学生表B ADD 年龄 N(2)
注：字段名后加一空格再带类型和宽度
② 在D盘根目录的“学生表B”中添加两个字段：班级(C，8)、团员否(L)
［解答］
ALTER TABLE D:\学生表B ADD 班级 C(8) ADD 团员否 L
注：添加两个字段，要用两个ADD语句
2）修改字段（类型和宽度）
① 将D盘根目录的“学生表B”中姓名字段的宽度由原来的6改为8
［解答］
ALTER TABLE D:\学生表B ALTER 姓名 C(8)
注：表名后用ADD 字段名 类型(宽度)是添加一个字段；
用ALTER字段名 类型(宽度)可修改字段的类型、宽度，但不能修改字段名。
② 将D盘根目录的“学生表B”中学号字段的类型改成N型、宽度改为6（1位小数）
［解答］
ALTER TABLE D:\学生表B ALTER 学号 N(6,1)
注：① 只要直接写入新的字段类型或宽度即可；② 不能修改字段名和删除字段。
（2）删除字段、更改字段名
1）删除字段
① 将Xscj表中的“等级”字段删除
［解答］
ALTER TABLE Xscj DROP 等级
② 将D盘根目录的“学生表B”的“年龄”字段删除
［解答］
ALTER TABLE D:\学生表B DROP 年龄
2）更改字段名
① 将Xscj表中的“平均”字段改名为“平均分”
［解答］
ALTER TABLE Xscj RENAME 平均 TO 平均分
② 将D盘根目录的“学生表B”中字段“相片”改名为“像片”
［解答］
ALTER TABLE D:\学生表B RENAME 相片 TO像片
4．综合练习
（1）在D盘根目录中创建如下表的“学生表A”（见P43）
字段名
字段类型
字段宽度
小数点
学号
字符型
8
姓名
字符型
8
性别
字符型
2
出生日期
日期型
8
团员否
逻辑型
1
入学成绩
数值型
6
2
班级
字符型
10
照片
通用型
4
简历
备注型
4
［解答］
CREATE TABLE 学生表A (学号 C(8),姓名 C(8),性别 C(2),出生日期 D,;
团员否 L,入学成绩 N(6,2),班级 C(10),照片 G,简历 M)
（2）为“学生表A”中添加一个字符型字段“系别”（宽度：10）
［解答］
ALTER TABLE 学生表A ADD 系别 C(10)
（3）将“学生表A”的字段“出生日期”改成“出生年月”
［解答］
ALTER TABLE 学生表A RENAME 出生日期 TO 出生年月
（4）将“学生表A”的“入学成绩”字段的宽度改为3，小数去掉
［解答］
ALTER TABLE 学生表A ALTER 入学成绩 N(3)
（5）将“学生表A”的“团员否”字段改为字符型，宽度改为2
［解答］
ALTER TABLE 学生表A ALTER 团员否 C(2)
（6）将“学生表A”的“班级”字段删除
［解答］
ALTER TABLE 学生表A DROP 班级
（7）删除“学生表A”
［解答］
DROP TABLE 学生表A
【高手进阶解答】
（1）在D盘根目录中创建如下表的“学生表B”
字段名
字段类型
字段宽度
学号
字符型
7
专业
字符型
4
姓名
字符型
6
性别
字符型
2
出生年月
日期型
8
团员
逻辑型
1
［解答］
CREATE TABLE 学生表B (学号 C(7),专业 C(4),姓名 C(6),性别 C(2),;
出生年月 D,团员 L
（2）用“INSERT”命令为“学生表B”添加如下三条记录学号
专业
年级
姓名
性别
出生年月
团员
9103001
通讯
91
张佳
女
09/23/82
.T.
9101016
软件
91
陈莉
男
11/03/83
.T.
9102015
应用
91
王磊
男
04/26/82
.F.
INSERT INTO 学生表B VALUES(“9103001”,”通讯”,”91”,”张佳”,”女”,{^1982-09-23},.T.)
INSERT INTO 学生表B VALUES(“9101016”,”软件”,”91”,”陈莉”,”男”,{^1983-11-03},.T.)
INSERT INTO 学生表B VALUES(“9102015”,”应用”,”91”,”王磊”,”男”,{^1982-04-26},.F.)
注：一条命令只能添加一条记录
【自我测试解答】( 5分钟 )
（1）在D盘根目录中创建如下表的“学生表C”
字段名
字段类型
字段宽度
小数点
学号
字符型
6
班级
字符型
6
姓名
字符型
6
出生年月
日期型
8
入学成绩
数值型
5
2
［解答］
CREATE TABLE 学生表C (学号 C(6),班级 C(6),姓名 C(6),;
出生年月 D,入学成绩 N(5,2)
（2）为“学生表C”中添加一个字符型字段“性别”（宽度：2）
［解答］
ALTER TABLE 学生表C ADD 性别 C(2)
（3）将“学生表C”的“班级”字段删除
［解答］
ALTER TABLE 学生表C DROP 班级
练习五 SQL操作功能
【实验目的】
1．掌握SQL操作功能语句的操作；
2．熟练掌握表记录的更新（UPDATE）；
3．熟练掌握表记录的插入（INSERT）；
4．熟练掌握表记录的删除（DETELE）。
【知识要点】
1．表记录的更新：更新满足条件的记录的字段值，类似替换命令REPLACE
UPDATE 表名 SET 字段名1 = 表达式1 [,字段名2 = 表达式2，… ]；
WHERE 条件式说明：
（1）该语句一次可更新多个字段的值；
（2）不使用WHERE子句，则更新所有记录；
（3）此语句类似REPLACE命令。
2．表记录的插入：按指定字段名顺序在表末尾插入用表达式表示字段值的一条记录
INSERT INTO 表名 ［字段名1,字段名2,……] VALUES［表达式1,表达式2，……］
说明：
（1）［字段名1,字段名2,……]是用来指定表中的（部分）字段名；［表达式1,表达式2，……］为指定字段的具体记录值，它要与前面的字段名一一对应；
（２）若无［字段名1,字段名2,……]，则是对所有字段追加记录，这时的记录必须与表结构中的字段名一一对应；
（3）此命令是用以编程方式在表末尾追加一条记录。
3．表记录的删除：为表中满足条件的记录作删除标记
DELETE FORM 表名 WHERE 条件式说明：
（1）删除表中满足条件的记录。若无条件项，则删除所有记录；
（2）此语句类似DELETE命令，只对记录作删除标记；
（3）要彻底删除记录，必须使用PACK命令（也可用RECALL命令撤消标记）。
【实验内容】
三、SQL操作语句
1．表记录更新（UPDATE）
修改数据后,用“查询”语句“SELECT * FROM 表名”观看修改后的结果
（1）计算Xscj表的总分
（2）在Xscj表中将总分在330分以上（含330）的等级设置为合格，330分以下的为不合格。
（3）将Xscj表中学号为“10100116”的“计算机”成绩增加20分
（4）将Xscj表中“英语”成绩＞80分的增加10%
（5）将成绩表中，学“1011”号课及学“2020”号课的，成绩增加10分
（6）将成绩表中学号为“05031003”的“3002”号课成绩增加20分
（7）将成绩表中学号3、4位是“02”的“成绩”增加20分
（8）将学生表中班级是“金融”的前面加上“经贸”二字
（9）将学生表中学生“王大发”的入学成绩增加10分，并把性别改为“女”
2．表记录的插入（INSERT）
（1）在成绩表插入下面两条记录学号
课程号
成绩
05011001
1031
91
05021012
2020
76
（2）在学生表插入下面两条记录学号
班级
姓名
性别
出生日期
团员否
05031015
会计
张三风
女
03/28/86
.T.
05021018
金融
段誉
男
04/01/85
.F.
3．表记录的删除（DELETE）此语句仅对记录作删除标记
（1）删除学生表中入学成绩低于500分的男生记录
（2）从Xsda表中删除计算机系的重庆学生的记录。
（3）将Xscj表中“计算机”成绩在60到79分之间的记录删除
（4）将学生表中姓“张”的学生记录删除
（5）将学生表中不是“团员”的学生记录删除
4．综合运用：06计算机等级考试模拟试卷［二级（数据库）上机考试］
1、建立一个图书交易管理数据库，文件名为SJY.DBC，并输入数据（30分）该数据库有两个数据表：书籍表SJ.DBF和交易表JY.DBF。其数据形式如下：
书号
书名
2030
计算机原理
4644
离散数学
6426
软件工程
3535
高等数学
3376
经济法
3679
大学语文
4636
大学英语
5675
基础会计
书籍表SJ.DBF
交易表JY.DBF
书号
已售
定价
册数
总金额
2030
.T.
21.20
134
4644
.F.
17.60
128
6426
.T.
30.00
69
3535
.F.
18.80
306
3376
.T.
22.30
238
3679
.T.
19.00
112
4636
.F.
25.40
168
5675
.F.
18.50
280
要求：将数据库(SJY.DBC)中的SJ.DBF和JY.DBF两表以”书号”建立一对多的永久关系。[注：已售为真(,T.)，即此书已销售 ]
2、操作数据库表。要求将相关命令保留到程序文件CQEEC61.PRG中（40分）。
注：使用VFP数据表操作命令或者VFP-SQL命令，一个任务可由若干个命令组成，请用注释语句标注题号。
（1）根据股份和册数计算每次交易的总金额，并将计算结果写入JY.DBF表中；
（2）查询已经交易的股票信息，要求显示书名，册数，股份及交易总金额，已经交易的为.T.
（3）按已售汇总册数和交易总金额，并显示已售和汇总后的册数和交易总金额
（4）将JY.DBF中还没有交易的股票逻辑删除。
3、程序填空，要求输入并完善程序，程序文件名为PROG061.PRG（30分）
注：在程序开头，必须用注释语句输入学校名，准考证号，姓名以下程序的功能是能反复输入书号（按‘Q’键退出），并输出该书籍的销售情况，格式为：“书名，已售，销售总金额”最后输出该书籍已经销售的总数量和总金额。
程序代码：
SELECT 1
USE SJ
INDEX ON 书号 TAG SH
SELECT 2
USE JY
SET RELATION TO 书号 INTO A
X=.T.
DO WHILE X
ACCEPT "请输入书号：" TO DM
IF UPPER(ALLTRIM(DM))="Q"
①
LOOP
ENDIF
"书名 已售 册数 销售金额"
SCAN FOR ②
A.书名,已售,册数,总金额
ENDSCAN
CALCULATE ③ TO S1,S2 FOR NOT 已售 AND 书号=DM
"该书籍已经销售的总数量 总金额"
S1,S2
ENDDO
CLOSE ALL
SET TALK ON
RETURN
【高手进阶】
（1）将学生表中学生“章立新”的各科成绩全更新为0（要用两个表，从学生表中找章立新的学号，从成绩表中将此学号的成绩更新为0）
（2）将学生表中学生“迪来”的各科成绩从成绩表中删去
【自我测试】( 5分钟)
对Xsda表和Xscj表，使用VFP-SQL命令和VFP数据表操作命令，完成下列操作。
（1）显示学号是“101”开头的学生学号、姓名、性别和年龄；
（2）计算学生的总分和平均分，并显示结果；
（3）计算英语和计算机的总分和平均分，并显示结果；
（4）显示计算机系学生的高数、英语、计算机成绩及这三科的总分。
【实验过程】
三、SQL操作语句
1．表记录更新（UPDATE）
修改数据后,用“查询”语句“SELECT * FROM 表名”观看修改后的结果
（1）计算Xscj表的总分
［解答］
UPDATE Xscj SET 总分 = 高数+大语+邓论+英语+计算机
或：（使用VFP数据表操作命令。注意看看有什么地方不同）
USE Xscj
REPLACE 总分 WITH高数+大语+邓论+英语+计算机 ALL
（2）在Xscj表中将总分在330分以上（含330）的等级设置为合格，330分以下的为不合格。
［解答］
UPDATE Xscj SET 等级="合格" WHERE 总分 >= 330
UPDATE Xscj SET 等级="不合格" WHERE 总分 < 330
[因条件不一样，所以用两条更新语句]
（3）将Xscj表中学号为“10100116”的“计算机”成绩增加20分
［解答］
UPDATE Xscj SET 计算机=计算机+20 WHERE 学号=”10100116”
（4）将Xscj表中“英语”成绩＞80分的增加10%
［解答］
UPDATE Xscj SET 英语=英语*1.1 WHERE 英语>80
（5）将成绩表中，学“1011”号课及学“2020”号课的，成绩增加10分
［解答］
UPDATE 成绩表 SET 成绩=成绩+10 WHERE 课程号=”1011” or 课程号=”2020”
（6）将成绩表中学号为“05031003”的“3002”号课成绩增加20分
［解答］
UPDATE 成绩表 SET 成绩=成绩+20 WHERE 学号=”05031003” and 课程号=”3002”
（7）将成绩表中学号3、4位是“02”的“成绩”增加20分
［解答］
UPDATE 成绩表 SET 成绩=成绩+20 WHER 学号LIKE,__02%”
或：UPDATE 成绩表 SET 成绩=成绩+20 WHERE SUBSTR(学号,1,2)=”02”
（8）将学生表中班级是“金融”的前面加上“经贸”二字
UPDATE 学生表 SET 班级=”经贸金融” WHERE 班级=”金融”
（9）将学生表中学生“王大发”的入学成绩增加10分，并把性别改为“女”
［解答］
UPDATE 学生表 SET 入学成绩=入学成绩+10,性别=”女” WHERE 姓名=”王大发”
2．表记录的插入（INSERT）
（1）在成绩表插入下面两条记录学号
课程号
成绩
05011001
1031
91
05021012
2020
76
［解答］注意：一条命令只能插入一条记录
INSERT INTO 成绩表 VALUES(“05011001”,”1031”,91)
INSERT INTO 成绩表 VALUES(“05021012”,”2020”,76)
（2）在学生表插入下面两条记录学号
班级
姓名
性别
出生日期
团员否
05031015
会计
张三风
女
03/28/86
.T.
05021018
金融
段誉
男
04/01/85
.F.
［解答］注意：不是对表中所有字段进行操作
INSERT INTO 学生表(学号,班级,姓名,性别,出生日期,团员否) ;
VALUES(“05031015”,”会计”,”张三风”,”女”,{^1986-03-28},.T.)
INSERT INTO 学生表(学号,班级,姓名,性别,出生日期,团员否) ;
VALUES(“05021018”,”金融”,”段誉”,”男”,CTOD(“04/01/85”),.F.)
注意：对字段名要用括号括起；字段内容的顺序要与字段名对齐；两种不同的日期语句
3．表记录的删除（DELETE）此语句仅对记录作删除标记
（1）删除学生表中入学成绩低于500分的男生记录
［解答］
DELETE FROM 学生表 WHERE 入学成绩<500 AND 性别="男"
或：（使用VFP数据表操作命令）
USE 学生表
DELETE FOR 入学成绩<500 AND 性别="男"
（2）从Xsda表中删除计算机系的重庆学生的记录。
［解答］
DELETE FROM 学生表 WHERE 系别 ="计算机系" AND 籍贯="重庆"
（3）将Xscj表中“计算机”成绩在60到79分之间的记录删除
［解答］
DELETE FROM Xscj WHERE 计算机 BETWEEN 60 AND 79
或：DELETE FROM Xscj WHERE 计算机>=60 and 计算机<=79
（4）将学生表中姓“张”的学生记录删除
［解答］
DELETE FROM 学生表 WHERE 姓名 LIKE,张%”
或：DELETE FROM 学生表 WHERE SUBSTR(姓名,1,2) =,张”
（5）将学生表中不是“团员”的学生记录删除
［解答］
DELETE FROM 学生表 WHERE 团员=.F.
或：DELETE FROM 学生表 WHERE NOT 团员注：“NOT 团员”表示不是团员；或用“团员=.F.”（“团员”字段为假）表示
4．综合运用,06计算机等级考试模拟试卷［二级（数据库）上机考试］
1、建立一个图书交易管理数据库，文件名为SJY.DBC，并输入数据（30分）
该数据库有两个数据表：书籍表SJ.DBF和交易表JY.DBF。其数据形式如下：
书号
书名
2030
计算机原理
4644
离散数学
6426
软件工程
3535
高等数学
3376
经济法
3679
大学语文
4636
大学英语
5675
基础会计
书籍表SJ.DBF
交易表JY.DBF
书号
已售
定价
册数
总金额
2030
.T.
21.20
134
4644
.F.
17.60
128
6426
.T.
30.00
69
3535
.F.
18.80
306
3376
.T.
22.30
238
3679
.T.
19.00
112
4636
.F.
25.40
168
5675
.F.
18.50
280
要求：将数据库(SJY.DBC)中的SJ.DBF和JY.DBF两表以”书号”建立一对多的永久关系,[注：已售为真(,T.)，即此书已销售 ]
［解答］
两表已建好，自己在数据库(SJY.DBC)中建一对多的永久关系（在数据库中，先建索引，父表要设为主索引，然后用鼠标拖索引标记到子表）
2、操作数据库表。要求将相关命令保留到程序文件CQEEC61.PRG中（40分）。
注：使用VFP数据表操作命令或者VFP-SQL命令，一个任务可由若干个命令组成，请用注释语句标注题号。
（1）根据股份和册数计算每次交易的总金额，并将计算结果写入JY.DBF表中；
［解答］
SQL语句：
UPDATE JY SET 总金额 = 册数*定价
VFP数据表操作命令
USE JY
REPLACE ALL 总金额 WITH 册数*定价
（2）查询已经交易的股票信息，要求显示书名，册数，股份及交易总金额，已经交易的为.T.
［解答］
SQL语句：
SELECT 书名,册数,定价,总金额 FROM SJ,JY ;
WHERE SJ.书号=JY.书号 AND 已售
VFP数据表操作命令
SELECT 1
USE SJ
INDEX ON 书号 TAG DM
SELECT 2
USE JY
SET RELATION TO 书号 INTO SJ
LIST SJ.书名,册数,定价,总金额 FOR 已售 OFF
（3）按已售汇总册数和交易总金额，并显示已售和汇总后的册数和交易总金额
［解答］
SQL语句：
SELECT 已售,SUM(册数),SUM(总金额) ;
FROM JY GROUP BY 已售
VFP数据表操作命令
USE JY
INDEX ON 已售 TAG LX
TOTAL ON 已售 TO HZ
USE HZ
LIST 已售,册数,总金额 OFF
（4）将JY.DBF中还没有交易的股票逻辑删除。
［解答］
SQL语句：
DELETE FROM JY WHERE NOT 已售
[注：“NOT 已售”，表示已售为假(,F.)，即此书没销售]
VFP数据表操作命令
USE JY
DELETE FOR NOT 已售
3、程序填空，要求输入并完善程序，程序文件名为PROG0621.PRG（30分）
注：在程序开头，必须用注释语句输入学校名，准考证号，姓名以下程序的功能是能反复输入书号（按‘Q’键退出），并输出该股票的交易情况，格式为：“书名，已售，交易总金额”最后输出该股票已经交易的总数量和总总金额。
程序代码：
［解答］
① X=.F,
② 书号=DM
③ SUM(册数),SUM(总金额)
【高手进阶解答】
（1）将学生表中学生“章立新”的各科成绩全更新为0（要用两个表，从学生表中找章立新的学号，从成绩表中将此学号的成绩更新为0）
［解答］
UPDATE 成绩表 SET 成绩= 0 WHERE 学号 = ;
(SELECT学号 FROM 学生表 WHERE 姓名= "章立新")
（2）将学生表中学生“迪来”的各科成绩从成绩表中删去
［解答］
DELETE FROM 成绩表 WHERE 学号 = ;
(SELECT 学号 FROM 学生表 WHERE 姓名="迪来")
【自我测试解答】( 5分钟 )
对Xsda表和Xscj表，使用VFP数据表操作命令和VFP-SQL命令，完成下列操作。
（1）显示学号是“101”开头的学生学号、姓名、性别和年龄；
［解答］
SQL语句：
SELECT 学号,姓名,性别,年龄 FROM Xsda WHERE 学号 LIKE "101%"
VFP数据表操作命令
USE Xsda
LIST学号,姓名,性别,年龄 FOR SUBSTR(学号,1,3) = "101"
（2）计算学生的总分和平均分，并显示结果；
［解答］
SQL语句：
UPDATE Xscj SET 总分=高数+大语+邓论+英语+计算机,平均=总分/5
注：也可写成两句，分别计算
VFP数据表操作命令
USE Xscj
REPLACE 总分 WITH 高数+大语+邓论+英语+计算机,平均 WITH 总分/5 ALL
（3）计算英语和计算机的总分和平均分，并显示结果；
［解答］
SQL语句：
SELECT SUM(英语),SUM(计算机),AVG(英语),AVG(计算机) FROM Xscj
VFP数据表操作命令
USE Xscj
SUM 英语,计算机 TO F1,F2
AVERAGE 英语,计算机TO E1,E2
或：CALCULATE SUM(英语),SUM(计算机),AVG(英语),AVG(计算机) TO F1,F2,E1,E2
" 英语总分 计算机总分 英语平均分 计算机平均分"
F1,F2,E1,E2
注意：这两个总分和平均分是不同的，（3）题是对字段计算（按列），而（2）题是对每条记录计算（按行）。它们所用的命令是完全不同的。
（4）显示计算机系学生的高数、英语、计算机成绩及这三科的总分。
［解答］
SQL语句：
SELECT 系别,姓名,高数,英语,计算机 FROM Xsda,Xscj ;
WHERE Xsda.学号 = Xscj.学号 AND 系别=,计算机系”
VFP数据表操作命令
SELECT 1
USE Xsda
INDEX ON 学号 TAG XH
SELECT 2
USE Xscj
SET RELATION TO 学号 INTO A
LIST FIELDS A.系别,A.姓名,高数,英语,计算机FOR A.系别=“计算机系”
CLOSE ALL
RETURN
