（精品课件）数据库操作及查询统计

1、1,数据库操作技术,2,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 索引查询,内容目录,3,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 表结构的复制

2、5.8 SELECT-SQL查询 5.9 视图 （*）,内容目录,4,（8）掌握对表中数据的四种统计命令（SUM、AVERAGE、COUNT、TOTAL）使用方法。 (9)掌握SELECT查询语句的引用，能灵活根据数据查询要求组合WHERE、HAVING、ORDER BY、GROUP BY等子句并能使用INSERT INTO、DELETE FROM、UPDATE SET语句。,5.1.1 表结构显示和表的访问,1、表结构显示,格式：LIST|DISPLAY STRUCTURE TO PRINT PROMPT|TO FILE NOCONSOLE 说明：（1）TO PRINT PROMPT:列表结

3、果输入打印机，后跟 PROMPT 则显示打印机设置对话框。 （2） TO FILE ：列表结果保存在文件中。 （3） NOCONSOLE：列表结果不在屏幕上显示。 （4）LIST显示的内容是连续的，而DISPLAY是分屏显示,5.1 数据库基本操作,例：显示XSCJ数据库STUDENT表的结构。 在命令窗口中输入： OPEN DATABASE e:vfp6dataxscj.dbc USE student LIST STRUCTURE,屏幕上显示student.dbf的表结构：,2、数据库结构显示,格式：LIST|DISPLAY DATABASE 例：显示XSCJ数据库的结构。 输入 命令： O

4、PEN DATABASE e:vfp6dataxscj.dbc LIST DATABASE,3、表的打开与关闭,只有打开表后，才能对表进行操作。表关闭时数据会自动存盘。 命令打开或关闭表 打开表：USE EXCLUSIVE|SHARED 关闭表：USE 菜单方式打开表 (工作区概念放在本章第5节介绍),说明： 1）打开一个表，就自动关闭该工作区原已打开的表，一个工作区同一时间只能打开一个表。 2）表刚打开时，记录指针指向第一条记录。 3）表操作完后应及时关闭。 4) 只有以独占方式打开才能对表的结构和记录内容修改。 或在打开表文件对话框中选定独占复选框。,关闭表的其它方法： 其它命令：,退出V

5、FP即关闭表,5.1.2 记录操作,表记录的基本操作有显示、修改、插入、删除、替换。,1、记录显示,格式：LIST|DISPLAY FIELDS FOR WHILE TO PRINTER PROMPT | TO FILE 功能：连续显示记录 说明： 记录范围，有NEXT、ALL、REST和RECORD记录号子句；,【示例】显示学生记录。 USE student LIST SC.课程号,SC.成绩,5.5.3 表的联接,格式：JOIN WITH | TO FOR FIELDS NOOPTIMIZE 功能：按照FOR子句规定的联接条件，将当前工作区中的表与另一个以或表示的工作区中的表进行联接，从而

6、产生一个新表。,【示例】将表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.6 统计命令,5.6.1 计数命令,格式：COUNT FOR WHILE TO 功能：计算指定范围内满足条件的记录数。,VFP提供5种命令来支持统计功能。,【示例】 ：统计数学成绩不及格人数 use sc count for

7、成绩60 .and.课程号=“002” to rs ? “数学成绩不及格人数：”，rs,5.6.2 求和命令,格式：SUM FOR WHILE TO | ARRAY 功能：在打开的表中，对的各个表达式分别 求和。,【示例】 根据course.dbf求所有课程的总学分。 use course SUM 学分 to xf ? “课程总学分为：”，xf,5.6.3 求平均值命令,格式：AVERAGE FOR WHILE TO | ARRAY 功能：在打开的表中，对中的各个表达式分 别求平均值。,【示例】根据sc.dbf求98001号学生所有课程的平均成绩。 use sc AVERAGE 成绩 to p

8、jcj for 学号=“98001” ? “98001号学生的平均成绩为：”，pjcj,5.6.4 汇总命令,格式：TOTAL TO ON FIELDS FOR WHILE 功能：分别对值相同的记录的数值型字段值求和，并将结果存入一个新表。一组关键字值相同的记录在新表中产生一个记录；对于非数值型字段，只将关键字值相同的第一个记录的字段值放入该记录。 说明：（1） 指排序字段或索引关键字，即当前表必须是有序的，否则不能汇总。（2）FIELDS子句的指出要汇总的字段。若缺省，则对表中所有数值型字段汇总。,【示例】 对宾馆收银表SY，按楼层对其数值字段进行汇总。 USE SY INDEX ON 楼层

9、 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

10、LIST Record#楼层房号姓名其它应收 房费 实际金额 1二楼2010刘炎 143 1050.001193.00 2三楼3001郭丹丹 175 1600.001775.00,5.7 表的复制,5.7.1 表与表结构的复制,1、任意类型文件的复制,格式：COPY FILE TO 示例：COPY FILE student.DBF TO student1.DBF COPY FILE student.FPT TO student1.FPT,2、 数据表文件的复制(*),格式：COPY TO FIELDS FOR WHILE TYPE.,示例1：USE student COPY TO studen

11、t3 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.7.2 表结构的复制,格式：COPY STRUCTURE TO FIELDS ,示例1：USE student COPY STRU TO student4 fields 学号,姓名,所在系 USE student4 LIST STRU

12、,5.8 SELECT-SQL查询,SELECT-SQL查询是从SQL语言移植过来的查询命令，具有强大的单表与多表查询功能。 SQL是一种结构化数据库查询语言，它是一种使用你选择的标准从数据库记录中选择某些记录的方法。其发音为“sequel”或“S-Q-L”。 VFP支持在命令窗口直接使用SELECT-SQL命令，也可通过 “查询设计器”窗口来设计查询、生成查询文件，然后运行定制的查询。,SQL主要几个命令动词：SELECT、CREATE、DROP、ALTER、INSERT、 UPDATE、DELETE CREAT TABLE （ （，),） ALTER TABLE ADD|ALTER COL

13、UMN ,（，) INSERT INTO 表名（字段名1，字段名2，） VALUE (表达式1，表达式2，) DROP TABLE UPDATE SET , DELETE FROM WHERE ,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 SCREENPREFERENCE NO

14、CONSOLEPLAINNOWAITWHERE 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 对查询结果进行排序,SELECT-SQL命令的JOIN子句： 内部联接 INNER JOIN 左联接 LEFT OUTER JOIN 外部联接： 右联接 RIGH

15、T OUTER JOIN 完全联接 FULL OUTER JOIN,2、SELECT-SQL命令查询示例,(1) 单表查询示例 【示例-1】显示student.dbf表中所有学生的学号、姓名、性别（从一个表中选取三个字段）。 SELECT 学号,姓名,性别 FROM student,【示例-2】显示student.dbf表中所有“CS”系学生的学号、姓名、性别、所在系，其中以“系”作为列标题替代“所在系”。 SELECT 学号,姓名,性别,所在系 as 系 FROM student; where 所在系=“CS”,【示例-3】对sc.dbf先按课号升序再按成绩降序排序，并将结果存入新表sc1.

16、dbf。 SELECT 学号,课程号，成绩 FROM sc ; ORDER BY 课程号,成绩 DESC; INTO TABLE sc1,【示例-4】显示sc.dbf中各课程平均成绩（按课程分组，对成绩求平均）。 select 课程号,avg(成绩) as 平均成绩; from sc group by 课程号,【示例-5】找出学生考试成绩之和大于200分的学生，并将结果存入数组SZSC。,执行程序,(2)多表查询示例（联接查询）,【示例-6】仅查询学号为98002的学生的成绩，同时显示其学号、姓名、课程号和成绩。,解一： SELECT student.学号,student.姓名,sc.课程号,

17、sc.成绩 ; FROM sc INNER JOIN student ON student.学号=sc.学号; WHERE student.学号=98002,解二： SELECT student.学号,student.姓名,sc.课程号,sc.成绩 FROM student,sc; WHERE student.学号=sc.学号 AND student.学号=98002,【示例-7】试统计学生修读课程门数，要求： （1）student.学号后三位小于006； （2）显示姓名与修读门数； （3）显示结果按修读门数降序排列。,解一： SELECT student.姓名,COUNT(SC.学号) AS

18、 修读门数; FROM student INNER JOIN sc ON student.学号=sc.学号; WHERE right(student.学号,3)“006”; GROUP BY SC.学号； ORDER BY 2 DESCENDING,解二： 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,【示例-8】查询不及格的学生的成绩，同时显示其学号、姓名、课程名和成绩。 注意：本查

19、询涉及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.8.2 用查询设计器建立查询,可生成一条SELECT-SQL语句，并保存在查询文件(.QPR)中。,1、查询设计器（1）上部窗格：显示已打开的表。（2）下部窗格 字段选项卡 联接选项卡： 筛选选项卡 排序依据选项卡 分组依据选项卡 杂项选项卡,2、查询设计器的操作 打开查询设计器、设置查询、执行查询 、保存查询 【例3-23】试用查询设计器来

20、查询学号为98002的学生的成绩，同时显示其学号、姓名、课程号和成绩。 查询文件的命令方式打开：MODIFY QUERY 【示例】试通过查询设计器来查询学号为98008的学生的成绩，显示其学号、姓名、课程号、课程名和成绩，并按课程号降序排列。,3、查询菜单的几个命令 (1) 查看SQL 用于显示由查询操作产生的SELECT-SQL命令。显示出来的命令为只供阅读，不能编辑，但可通过剪贴板复制和粘贴。 (2) 查询去向 选定“查询去向”命令即出现如图5-49所示的查询去向对话框，其中共包括7个按钮，表示查询结果不同的输出类型。 (3)运行查询 执行查询并输出结果。,*5.8.3 查询结果的图形处理

21、,5.9.1 概念,*5.9 视图,视图又称为SQL视图，是一个定制的虚拟表，是对已有数据库表（基表）的数据进行重新组织，本身并不存储数据，但用户通过视图可以对基表的数据进行修改更新。 可分为本地视图、远程视图。,视图和查询的对照： 相同： 查询和视图都是无表文件的表,一般称为“虚表” 不同： 更新视图可以更新基表，查询不能更新基表； 视图可以作为数据环境的数据源，查询则不行。 视图是数据库的一部分，视图可引用远程表。,5.9.2 视图的创建,使用视图设计器 选中数据库，选择“本地视图”，选“新建” 视图设计器与查询设计器的不同：增加了一个“更新”选项卡 使用命令 格式：CREATE SQL VIEW AS ,5.9.3 更新选项卡,在“表”下拉列表框中设置可更新的表。 在“字段名”列表中设置“可更新字段” 在“发送SQL更新”复选框