数据库操作及查询统计

第五章 数据库操作技术 5.1 数据库基本操作 5.1.1 结构显示和表的访问 5.1.2 记录操作 5.2 数据库可视化操作 5.2.1 记录的浏览与编辑修改 5.2.2 备注字段和通用字段的操作 5.3 排序与索引 5.3.1 排序操作 5.3.2 索引操作 5.4 查询命令 5.4.1 顺序查询 5.4.2 索引查询 内容目录 5.5 多表的操作 5.5.1 工作区 5.5.2 表的关联 5.5.3 表的联接 5.6 统计命令 5.6.1 计数命令 5.6.2 求和命令 5.6.3 求平均值命令 5.6.4 汇总命令 5.7 表的复制 5.7.1 表的复制 5.7.2 表结构的复制 5.8 SELECT-SQL查询 5.9 视图 （ *） 内容目录 （ 8） 掌握对表中数据的四种统计命令（ SUM、 AVERAGE、 COUNT、 TOTAL）使用方法。 (9)掌握 SELECT查询语句的引用，能灵活根据数据查询要求组 合 WHERE、 HAVING、 ORDER BY、 GROUP BY等子句并能使用 INSERT INTO、 DELETE FROM、 UPDATE SET语句。 第 5章 数据库操作技术 5.1.1 表结构显示和表的访问 1、表结构显示 格式： LIST|DISPLAY STRUCTURE TO PRINT PROMPT|TO FILE NOCONSOLE 说明：（ 1） TO PRINT PROMPT:列表结果输入打印机， 后跟 PROMPT 则显示打印机设置对话框。 （ 2） TO FILE ： 列表结果保存在文件中。 （ 3） NOCONSOLE： 列表结果不在屏幕上显示。 （ 4） LIST显示的内容是连续的，而 DISPLAY是分屏 显示 5.1 数据库基本操作 第 5章 数据库操作技术 例： 显示 XSCJ数据库 STUDENT表的结构。 在命令窗口中输入： OPEN DATABASE e:vfp6dataxscj.dbc USE student LIST STRUCTURE 第 5章 数据库操作技术 屏幕上显示 student.dbf的表结构： 第 5章 数据库操作技术 2、数据库结构显示 格式： LIST|DISPLAY DATABASE 例： 显示 XSCJ数据库的结构。 输入 命令： OPEN DATABASE e:vfp6dataxscj.dbc LIST DATABASE 第 5章 数据库操作技术 第 5章 数据库操作技术 3、表的打开与关闭 只有打开表后，才能对表进行操作。表关闭时数据 会自动存盘。 命令打开或关闭表 打开表： USE EXCLUSIVE|SHARED 关闭表： USE 菜单方式打开表 (工作区概念放在本章第 5节介绍 ) 第 5章 数据库操作技术 说明： 1）打开一个表，就自动关闭该工作区原已打开的表， 一个工作区同一时间只能打开一个表。 2）表刚打开时， 记录指针 指向第一条记录。 3）表操作完后应及时关闭。 4) 只有以 独占方式 打开才能对表的结构和记录内容 修改。 或在打开表文件对话框中选定独占复选框。 第 5章 数据库操作技术 关闭表的其它方法： 其它命令： 命令 功能 CLEAR ALL 关闭所有表，释放内存变量及用户定义 的菜单和窗口（但不释放系统变量） CLOSE ALL 关闭所有数据库和表 CLOSE DATABASE ALL 关闭当前数据库及其中的表 自由表 CLOSE TABLES ALL 关闭当前数据库中的表 自由表 ，但不 关闭数据库 退出 VFP即关闭表 第 5章 数据库操作技术 5.1.2 记录操作 表记录的基本操作有显示、修改、插入、删除、替换。 1、记录显示 格式： LIST|DISPLAY FIELDS FOR WHILE TO PRINTER PROMPT | TO FILE 功能： 连续显示记录 说明： 记录范围 ， 有 NEXT、 ALL、 REST和 RECORD记录号子 句； 第 5章 数据库操作技术 【 示例 】 显示学生记录 。 USE student LIST SC.课程号 ,SC.成绩 第 5章 数据库操作技术 5.5.3 表的联接 格式： JOIN WITH | TO FOR FIELDS NOOPTIMIZE 功能： 按照 FOR子句规定的联接条件，将当前工作区 中的表与另一个以 或 表示的工作区 中的表进行联接，从而 产生一个新表 。 第 5章 数据库操作技术 【 示例 】 将表 SC和表 COURSE联接为一个新表 SCB， 要求 包含 学号 ， 课程名 和 成绩 等 3个字段 。 CLOSE ALL SELECT 1 USE course SELECT 2 USE sc JOIN WITH course TO scb FIELDS 学号 ,; course.课程名 ,成绩 FOR course.课程号 =课程号 USE scb BROWSE 第 5章 数据库操作技术 5.6 统计命令 5.6.1 计数命令 格式： COUNT FOR WHILE TO 功能： 计算 指定范围内满足条件的 记录数 。 VFP提供 5种命令来支持统计功能 。 【 示例 】 ： 统计数学成绩不及格人数 use sc count for 成绩 60 .and.课程号 =“002” to rs ? “ 数学成绩不及格人数： ” ， rs 第 5章 数据库操作技术 5.6.2 求和命令 格式： SUM FOR WHILE TO | ARRAY 功能： 在打开的表中，对 的各个表达式分别 求和。 【 示例 】 根据 course.dbf求所有课程的总学分。 use course SUM 学分 to xf ? “ 课程总学分为： ” ， xf 第 5章 数据库操作技术 5.6.3 求平均值命令 格式： AVERAGE FOR WHILE TO | ARRAY 功能： 在打开的表中，对 中的各个表达式分 别求平均值。 【 示例 】 根据 sc.dbf求 98001号学生所有课程的平均成绩。 use sc AVERAGE 成绩 to pjcj for 学号 =“98001” ? “98001 号学生的平均成绩为： ” ， pjcj 第 5章 数据库操作技术 5.6.4 汇总命令 格式： TOTAL TO ON FIELDS FOR WHILE 功能： 分别 对 值相同的记录的数值型字段值求和 ， 并将 结果存入一个新表 。 一组关键字值相同的记录在新表中 产生一个记录 ；对于非数值型字段，只将关键字值相同的第 一个记录的字段值放入该记录。 说明： （ 1） 指排序字段或索引关键字，即 当前表 必须是有序的 ，否则不能汇总。 （ 2） FIELDS子句的 指出要汇总的字段。若缺 省，则对表中所有数值型字段汇总。 第 5章 数据库操作技术 【 示例 】 对宾馆收银表 SY， 按楼层对其数值字段进行汇总 。 USE SY INDEX ON 楼层 TO SYI SET INDEX TO SYI LIST Record# 楼层 房号 姓名 其它应收 房费 实际金额 1 二楼 2010 刘炎 48.00 250.00 298.00 2 二楼 2020 周敏 50.00 300.00 350.00 3 二楼 2030 郭洁 45.00 500.00 545.00 4 三楼 3001 郭丹丹 80.00 650.00 730.00 5 三楼 3008 于驰 50.00 450.00 500.00 6 三楼 3007 罗贵先 45.00 500.00 545.00 TOTAL ON 楼层 TO NSUM FIELDS 其它应收 ,房费 ,实际金额 USE NSUM LIST Record#楼层 房号 姓名 其它应收 房费 实际金额 1 二楼 2010 刘炎 143 1050.00 1193.00 2 三楼 3001 郭丹丹 175 1600.00 1775.00 第 5章 数据库操作技术 5.7 表的复制 5.7.1 表与表结构的复制 1、任意类型文件的复制 格式： COPY FILE TO 示例： COPY FILE student.DBF TO student1.DBF COPY FILE student.FPT TO student1.FPT 第 5章 数据库操作技术 2、 数据表文件的复制 (*) 格式： COPY TO FIELDS FOR WHILE TYPE . 示例 1： USE student COPY TO student3 fields 学号 ， 姓名 FOR 所在系 =“CS” USE student3 LIST 示例 2： USE student copy to STUtxt type sdf copy to STUtxt1 type delimited with blank copy to STUxls xls type STUtxt.txt type STUtxt1.txt 第 5章 数据库操作技术 5.7.2 表结构的复制 格式： COPY STRUCTURE TO FIELDS 示例 1： USE student COPY STRU TO student4 fields 学号 ,姓名 ,所在系 USE student4 LIST STRU 第 5章 数据库操作技术 5.8 SELECT-SQL查询 SELECT-SQL查询是从 SQL语言移植过来的 查询命令 ， 具 有强大的 单表与多表查询 功能 。 SQL是一种结构化数据库查询语言，它是一种使用你选 择的标准从数据库记录中选择某些记录的方法。其发音为 “ sequel” 或 “ S-Q-L” 。 VFP支持在命令窗口直接使用 SELECT-SQL命令，也可通 过 “ 查询设计器 ” 窗口来设计查询、生成查询文件，然后运 行定制的查询。 第 5章 数据库操作技术 SQL主要几个命令动词： SELECT、 CREATE、 DROP、 ALTER、 INSERT、 UPDATE、 DELETE CREAT TABLE （ （ ， ), ） ALTER TABLE ADD|ALTER COLUMN ,（ ， ) INSERT INTO 表名 （字段名 1，字段名 2， ） VALUE (表达式 1，表达式 2， ) DROP TABLE UPDATE SET , DELETE FROM WHERE 第 5章 数据库操作技术 5.8.1 用 SELECT-SQL命令直接查询 1、 SELECT-SQL命令的格式 SELECT ALL | DISTINCT. AS ， . AS FROM FORCE！ INNER | LEFTOUTER | RIGHTOUTER | FULLOUTER JOIN ！ ON INTO |TO FILEADDITIVE |TO PRINTER PROMPT|TO SCREEN PREFERENCE NOCONSOLEPLAINNOWAIT WHERE AND AND|OR AND|OR GROUP BY ， HAVING UNION ALL ORDER BY ASC|DESC， ASC|DESC TOP PERCENT SELECT说明要查询的语句 FROM说明要查询的数据来自哪个（些）表 WHERE说明查询条件 GROUP BY对查询结果进行分组，用于分组汇总 HAVING须跟 GROUP BY使用，限定分组的条件 ORDER BY 对查询结果进行排序 第 5章 数据库操作技术 SELECT-SQL命令的 JOIN子句 ： 内部联接 INNER JOIN 左联接 LEFT OUTER JOIN 外部联接： 右联接 RIGHT OUTER JOIN 完全联接 FULL OUTER JOIN 第 5章 数据库操作技术 2、 SELECT-SQL命令 查询示例 (1) 单表查询示例 【 示例 -1】 显示 student.dbf表中所有学生的学号、姓名、 性别（ 从一个表中选取三个字段） 。 SELECT 学号 ,姓名 ,性别 FROM student 【 示例 -2】 显示 student.dbf表中所有 “ CS” 系学生的学号、姓 名、性别、所在系，其中以 “ 系 ” 作为列标题替代 “ 所在系 ” 。 SELECT 学号 ,姓名 ,性别 ,所在系 as 系 FROM student; where 所在系 =“CS” 第 5章 数据库操作技术 【 示例 -3】 对 sc.dbf先按课号升序再按成绩降序排序，并 将结果存入新表 sc1.dbf。 SELECT 学号 ,课程号，成绩 FROM sc ; ORDER BY 课程号 ,成绩 DESC; INTO TABLE sc1 第 5章 数据库操作技术 【 示例 -4】 显示 sc.dbf中各课程平均成绩（按课程分组，对成 绩求平均）。 select 课程号 ,avg(成绩 ) as 平均成绩 ; from sc group by 课程号 第 5章 数据库操作技术 【 示例 -5】 找出学生考试成绩之和大于 200分的学生 ， 并将 结果存入数组 SZSC。 执行程序 第 5章 数据库操作技术 (2)多表查询示例（联接查询） 【 示例 -6】 仅查询学号为 98002的学生的成绩 ， 同时显示其 学号 、 姓名 、 课程号和成绩 。 解一： SELECT student.学号 ,student.姓名 ,sc.课程号 ,sc.成绩 ; FROM sc INNER JOIN student ON student.学号 =sc.学号 ; WHERE student.学号 =98002 第 5章 数据库操作技术 解二： SELECT student.学号 ,student.姓名 ,sc.课程号 ,sc.成绩 FROM student,sc; WHERE student. 学号 =sc. 学号 AND student. 学号 =98002 第 5章 数据库操作技术 【 示例 -7】 试统计学生修读课程门数，要求： （ 1） student.学号后三位小于 006； （ 2）显示姓名与修读门数； （ 3）显示结果按修读门数降序排列。 解一： SELECT student.姓名 ,COUNT(SC.学号 ) AS 修读门数 ; FROM student INNER JOIN sc ON student.学号 =sc.学号 ; WHERE right(student.学号 ,3)“006”; GROUP BY SC.学号； ORDER BY 2 DESCENDING 第 5章 数据库操作技术 解二： SELECT a.姓名 ,COUNT(b.学号 ) AS 修读门数 ; FROM student a,sc b; WHERE a.学号 =b.学号 and right(a.学号 ,3)006; GROUP BY b.学号 ; ORDER BY 2 DESCENDING 第 5章 数据库操作技术 【 示例 -8】 查询不及格的学生的成绩，同时显示其学号、姓名、 课程名和成绩。 注意：本查询涉及 student,course和 sc三个表 SELECT a.学号 ,a.姓名 ,b.课程名 ,c.成绩 ; FROM student a,course b,sc c; WHERE a.学号 =c.学号 AND c.课程号 =b.课程号 AND c.成绩 60 第 5章 数据库操作技术 5.8.2 用查询设计器建立查询 可生成一条 SELECT-SQL语句，并保存在查询文件 (.QPR)中 。 1、查询设计器 （ 1）上部窗格：显示已打开的表。 （ 2）下部窗格 字段选项卡 联接选项卡： 筛选选项卡 排序依据选项卡 分组依据选项卡 杂项选项卡 第 5章 数据库操作技术 2、查询设计器的操作 打开查询设计器、设置查询、执行查询 、保存查询 【 例 3-23】 试用查询设计器来查询学号为 98002的学生的成绩， 同时显示其学号、姓名、课程号和成绩。 查询文件的命令方式打开： MODIFY QUERY 【 示例 】 试通过查询设计器来查询学号为 98008的学生的成绩 ，显示其学号、姓名、课程号、课程名和成绩，并按课程号降 序排列。 第 5章 数据库操作技术 3、查询菜单的几个命令 (1) 查看 SQL 用于显示由查询操作产生的 SELECT-SQL命令。显示出来 的命令为只供阅读，不能编辑，但可通过剪贴板复制和粘贴。 (2) 查询去向 选定 “ 查询去向 ” 命令即出现如图 5-49所示的查询去向 对话框，其中共包括 7个按钮，表示查询结果不同的输出类型。 (3)运行查询 执行查询并输出结果。 第 5章 数据库操作技术 使查询结果可用于 Microsoft图形：在命令窗口执行命令 _GENGRAPH=“ VFPWIZARDSWZGRAPH.APP” 命令中的 _GENGRAPH为系统变量， WZGRAPH.APP应用程序由 VFP提供。该命 令一次性执行后就能使 “ 查询去向 ” 对话框中的 “ 图形 ” 按钮变成可用， 否则该按钮不能被激活。 说明： （ 1）图形向导对话框中含有柱形图、折形图、圆饼图等多种统计图供用户 挑选。 （ 2）为输出图形通常需选取两个以上表达式，并且至少要有一个数值表达 式。出于构图的需要，用数字表达式表示幅度，另一个表达式则表示坐标 轴分点。 （ 3）输出的图形将存入扩展名为 .SCX的表单文件，文件主名可由用户定义 。表单文件用 DO FORM命令来执行，执行后即显示图形。图形还可在表单设 计器修改。 （ 4）用户也可直接调用 WZGRAPH.APP应用程序来打开图形向导对话框。 （ 5）用户也可直接使用图形向导，此时不显式使用 WZGRAPH.APP应用程序 ，但对于查询结果图形输出须先运行查询文件，对于表数据图形输出可直 接使用向导。 *5.8.3 查询结果的图形处理 第 5章 数据库操作技术 5.9.1 概念 *5.9 视图 视图又称为 SQL视图，是一个定制的虚拟表，是对已有数 据库表（基表）的数据进行重新组织，本身并不存储数据，但用 户通过视图可以对基表的数据进行修改更新。 可分为本地视图、远程视图。 视图和查询的对照： 相同： 查询和视图都是无表文件的表 ,一般称为 “ 虚表 ” 不同 ： 更新视图可以更新基表，查询不能更新基表； 视图可以作为数据环境的数据源，查询则不行。 视图是数据库的一部分，视图可引用远程表。 第 5章 数据库操作技术 5.9.2 视图的创建 使用视图设计器 选中数据库，选择 “ 本地视图 ” ，选 “ 新建 ” 视图设计器与查询设计器的不同：增加了一个 “ 更新 ” 选项 卡 使用命令 格式： CREATE SQL VIEW AS 5.9.3 更新选项卡 在 “ 表 ” 下拉列表框中设置可更新的表。 在 “ 字段名 ” 列表中设置 “ 可更新字段 ” 在 “ 发送 SQL更新 ” 复选框中，设置是否允许对基表更新。 5.9.4 视图的使用 视图一经定义，就成为数据库的组成部分，可以像数据库 表一样接受用户的查询。 本章重点掌握 : （ 1) 表的访问操作；记录的添加、插入、编辑修改、显示、定 位、删除各恢复、过滤等操作；掌握备注型数据和通用型数据的 输入方法；表结构的复制。 （ 2） 用编辑方式显示和修改数据，用浏览方式显示和修改数据。 （ 3） 控制字段和记录的访问，训练数据表中记录的定位、插入、 添加、删除和恢复等操作。 （ 4) 按字段值顺序排序。 （ 5） 懂得用命令创建索引并使用索引。 （ 6） 掌握顺序查询（ LOCAT FOR）和索引查询（ FIND和 SEEK） 的方法。 （ 7） 掌握多工作区数据操作方法，建立表间的关联和联接。 第 5章 数据库操作技术 第 四 章 表 的 基 本 操 作 课堂作业 ： 1、 设 1号工作区上已打开表别名为 ” ZGGZ” 的表文件 ， 当前工作区为 2 号区 ， 以 下不能使 1号工作区成为主工作区的命令是 （ ） 。 A. SELECT 1 B.SELECT 0 C.SELECT A D.SELECT ZGGZ 2、 以下赋值正确的是 （ ） 。 A.STORE 8 TO X,Y B.STORE 8,9 TO X,Y C.X=8,Y=9 D.X,Y=8 3、 设 M=” 15”, N=”M” ， 执行命令 ？ &N+” 05” 的值是 （ ） 。 A.1505 B.20 C.M05 D.出错信息 4、 设当前表文件中有逻辑型字段 “ 婚否 ” ， 若要显示所有未婚记录的数据 ， 应 使用的命令 是 （ ） 。 A、 .DISPLAY FOR 婚否 =.F. B、 LIST FOR .NOT. 婚否 C、 DISPLAY FOR 婚否 .T. D、 LIST FOR 婚否 =“F”