slide1
Download
Skip this Video
Download Presentation
第三章 自由表( Free Table )

Loading in 2 Seconds...

play fullscreen
1 / 155

第三章 自由表( Free Table ) - PowerPoint PPT Presentation


  • 64 Views
  • Uploaded on

第三章 自由表( Free Table ). 3.1 表结构的创建和修改. 3.2 表 数据的输入. 3.3 表的打开、关闭、显示和定位. 3.4 记录数据 的修改和删除. 3.5 表 的排序、索引和查询. 3.6 表 的计算、汇总. 3.7 多表的 关联、连接和更新. 3.8 其它 有关命令操作. 3.9 结构化查询语言 SQL. 第三章 自由表( Free Table ). 本章学习 VFP 的自由表文件 .DBF 的建立和操作,分为二种方式来学习:

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' 第三章 自由表( Free Table )' - quintessa-haley


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

第三章 自由表(Free Table)

3.1 表结构的创建和修改

3.2 表数据的输入

3.3 表的打开、关闭、显示和定位

3.4 记录数据的修改和删除

3.5 表的排序、索引和查询

3.6 表的计算、汇总

3.7 多表的关联、连接和更新

3.8 其它有关命令操作

3.9 结构化查询语言SQL

slide2

第三章 自由表(Free Table)

本章学习VFP的自由表文件.DBF 的建立和操作,分为二种方式来学习:

菜单方式——用鼠标选择菜单项进行数据的管理和使用,即面 向对象的操作方法

命令方式——在命令窗口输入命令语句进行数据的管理和使用

slide3

3.1 表文件结构的建立和修改

  • 一、表的结构的建立
  • 结构(字段)
  • 表文件
          • 数据(记录)
  • 建立表文件必须先定义好表的结构,即定义字段的三个参数:
  • 1.字段名——由汉字、字母、0—9的数字、“—”下划线组成。 如:学号、NAME、XH-1等。
  • 2.字段类型:
  • (1)字符型(Character)字段(C):存贮所有能打印的ASCⅡ字符及空格符及所有汉字系统的可打印的汉字和各种符号,即任何文本(Text)。
  • (2)数值(Numeric)型字段(N):存贮可参与加减乘除运算的0—9的数值。
slide4

(3)逻辑型(Logical)字段(L):存放.真.或.假.两个逻辑值。输入T(t)或Y(y)为真,输入F(f)或N(n)为假。如,姓别:男或女,婚姻:真或假。(3)逻辑型(Logical)字段(L):存放.真.或.假.两个逻辑值。输入T(t)或Y(y)为真,输入F(f)或N(n)为假。如,姓别:男或女,婚姻:真或假。

(4)日期型(Date)字段(D):存贮日期数据。VFP规定输入和显示的默认格式为YYYYMMDD,对应年月日(年取4位)。可用SET DATE等设置命令改变为YY.MM.DD等多种格式。日期型字段可进行日期运算。

(5)备注型(Memo)字段(M):用以存贮大量的文字信息,表文件中如果包含有备注字段,则除了该表表文件外,还有一个与表文件同名扩展名“.FPT”的备注型文件,用来存放备每一条记录的注型字段的内容,而备注型字段本身只存放其内容在备注型文件中的地址。因此备注型字段除了能进行编辑、显示和打印外,不能进行其它操作。它是不定长度的长字符串输入,如:报告、文章、简介、说明等。

(6)通用型(General)字段(G):它类似一个备注型字段,但主要用来支持Microsoft的对象链接和嵌入OLE(Object linking and embedding)。通用数据型字段的内容也存贮在所分配的.FPT备注文件中,它可以包括任何类型的链接或嵌入的对象。

slide5

比如,可以链接或嵌入任何文本、图象、照片、声音、图表、二进制文件或由支持OLE的应用程序所建立的其它对象进入通用数据型字段。比如,可以链接或嵌入任何文本、图象、照片、声音、图表、二进制文件或由支持OLE的应用程序所建立的其它对象进入通用数据型字段。

(7)日期时间型(Date Time)字段(T):既可以存贮日期,也可以存贮时间或者两者兼有的值。其值是以“yyyymmddhhmmss”字符格式存贮的,即年月日时分秒的形式。可用设置命令设置格式。

(8)浮点数值型(Float)字段(F):或叫浮动型字段,与N型字段相同,是数值类型,以ASCⅡ码形式表示。主要用于科学运算应用程序(因它要求精确运算)。以IEEE(Institute of Electrical and Electronics Engineers)所描述的二进制浮点表示型式存贮

(9)双精度数据型(Double)字段(B):双精度比值型数据(Numeric) 精确。Double数据型是真正的浮点数(双精度的),一般用于科学计算。

slide6

(10)整数型 (Integer) 字段(I):使用没有小数值的整数。

(11)货币型(Currency)字段(Y):表示货币量的数据而代替用数值型(N)数据表示货币。它的值的范围是-2337203685477.5808至22337203685477.5807。值的前面应有美元符号“$” 。如$388.88等。

(12)二进制 (binary)字符型字段:存放想维持的、不用通过代码页转变的任何字符数据。它的值的范围是任何字符。

(13)二进制(Binary)备注型字段:存放想维持的、不用通过代码页转变的任何备注字段数据。它的范围仅受现存内存限制。

slide7

3.字段宽度——字段中能够存放的最大字符数或数字的最大位数 字符型字段不得大于254个字符,否则用备注型字段存贮。 数字型字段(N)的宽度=整数位数+小数位数+1(小数点)

逻辑型字段宽度为1,

日期型、日期时间型、货币型、双精度数据型字段宽度为8 备注型、通用型、整数型以及二进制备注型(Binary)字段宽 度为4。

浮点数值型字段(F)的宽度=整数位数+小数位数+1(小数点)二进制字符型(Binary)为1到254个字节。

4.小数位:字段的类型是数字型(N)和浮点型(F),就须给出小数位数。

slide8

二、建立表结构的命令

格式:CREATE [<文件名>|?]

功能:建立一个新的表文件,文件的扩展名是.DBF。

如:CREATE STU.DBF

进入表设计器,可定义表的结构(三个参数)了。见P40。

slide9

例1:建立文档管理系统的表结构,DF.DBF

字段名 类型 宽度 小数位

(1)登录号 字符型(C) 9

(2)登录时间 日期型(D) 8

(3)关键词 字符型(C) 11

(4)行文者号 字符型(C) 10

(5)文档名 字符型(C) 50

(6)行文者名 字符型(C) 0

(7)行文地 字符型(C) 50

(8)登出刊物 字符型(C) 20

(9)行文时间 日期型(D) 8

(10)页数 数字型(N) 3

(11)涉及金额 数字型(N) 11 4

(12)处理情况 字符型(C) 50

(13)摘要 备注型(M)4

slide10

方法:1、在命令窗口键入create DF.DBF

2、用鼠标单击:文件菜单笺(主菜单)—新建(弹出的子菜单)—表(对话框)—新文件。

slide11

例2:建立学生成绩档案STU2.DBF。字段有学号,姓名,性别,体育,高数,计算机基础,英语,平均成绩,总分,排名,备注。例2:建立学生成绩档案STU2.DBF。字段有学号,姓名,性别,体育,高数,计算机基础,英语,平均成绩,总分,排名,备注。

用二种方式来建立:(1)窗口用命令建立 (2)用菜单方式建立

实际练习1

slide12

三、表结构的修改命令

格式:MODIFY STRUCTURE

功能:显示表文件结构,并允许修改此表的结构。

练习:修改表文件STU2.DBF。

MODIFY STRU

实际练习2

slide13

3.2 表数据的输入

一、建立表结构时输入

建立表结构后,VFP系统会提示:“现在输入数据记录吗?”,如果选择是“Y”,系统则打开一个表窗口,在此窗口中,显示出表文件记录的全部字段,依次输入这些字段的数据,记录就存在了。当完成输入时,用鼠标单击此窗口右上角带×的按钮关闭窗口,便可退出完成,该表文件的数据就以记录的形式保存。

*备注型字段的数据输入方法

二、追加记录命令 APPEND

命令格式:APPEND [BLANK]

作用:在当前表的尾部(无论表中有无记录)追加新一条记录(或一条空记录)。

--正在操作的表。

当前表

slide14

三、在表中插入记录命令INSERT

格式:INSERT [BEFORE] [BLANK]

功能:在表文件中插入一条新录。

四、用BROWSE命令追加数据记录

格式:BROWSE

作用:打开了浏览窗口,可增加、修改、查阅记录。

实际练习3

slide15

3.3 表的打开、关闭、显示和定位

一、打开和关闭表文件命令

格式1:USE [<文件名>]

功能:打开<文件名>指定的表文件,该表如有备注型字段,则自动同时打开相应的备注文件。

格式2:USE

功能:关闭当前打开的表文件。

格式3: close all

功能: 关闭所有打开的文件。

slide16

二、表的显示和列表

建立表后,用户根据需要经常要对表进行各种查询显示和打印。

1.显示表的记录

LIST命令

格式:LIST [OFF] [<范围>][[FIELDS]<表达式表>]

[WHILE<条件>][FOR<条件>][TO PRINT|TO FILE<文件>]

功能:连续显示表文件的内容。

说明:用户可根据自己的需要确定上面的任选项,组合LIST命令的各种功能、形式。

选项功能:

表示不显示记录号

[OFF]

slide17

指定对哪些记录进行操作。 范围包括:

RECORD n 第几号记录

NEXT n 当前记录开始的几个记录(n可是一个表达式)

REST 自当前记录开始至文件末尾的所有记录。

ALL 所有的记录。不选[<范围>]则隐含范围为ALL。

[<范围>]

每个表达式可以是字段名表,也可以是一般的表达式,但之间用“,”分隔。

[[FIELDS]<表达式>]

从当前记录开始查找,当出现第一个不满足条件的记录时则停止查找。这种方式一般用于经过排序或索引的表文件。

WHILE<条件>]

slide18

从<范围>内的第一个记录开始, 显示所有条件满足的 记录 ,直到范围内的记录搜索完毕(与WHILE<条件>]的区别)。

注:<条件>是逻辑表达式,它只有真和假两个值,条件满足为真(.T.),条件不满足为假(,F.)。

[FOR<条件>]

[TO PRINT]:在打印机上打印出来。

[TO FILE<文件>]:将结果输出到用<文件>指定的磁盘文件。

例如1:查看STU1表中所有计算机基础成绩大于或等于80分的学生的信息

LIST ALL FOR 计算机基础>=80

slide21

例如4:查看STU2表中所有就读方式为统招的男生的学号,姓名。例如4:查看STU2表中所有就读方式为统招的男生的学号,姓名。

slide22

DISPLAY命令

命令格式:DISPLAY [OFF][<范围>][[FIELDS]<表达式>][WHILE<条件>][FOR<条件>][TO PRINT|TO FILE<文件>]

DISPLAY 与LIST命令功能不同点:

(1) VFP的DISPLAY命令每显示一个窗口的信息暂停一次。按任意键继续显示,而LIST显示不暂停。

(2) DISPLAY命令省略范围选择项时,仅显示当前记录,而LIST命令则显示所有记录。

例3:显STU2表的第4条记录的内容。

slide23

2.显示表的结构

格式:LIST STRUCTURE [TO PPRINT|TO FILE<文件>]

菜单方式:显示——表设计器(并可修改)

3.显示表工作状态

格式:LIST STATUS [TO PRINT|TO FILE<文件>]

slide25

三、记录的定位

打开表时,指针总是指向第一条记录。但可随着操作指针要移动,或用命令移动指针,改变当前记录。

定位命令是最直接的一种。即用GOTO命令或SKIP命令移动指针。

格式1:GO[TO] [ RECORD <n>] 定位到记录号为n的记录

GO[TO] TOP 定位到第1条记录

GO[TO] BOTTOM 定位到最后一条记录

slide26

例5:

USE stu1

GO BOTTOM

DISPLAY

slide27

GO TOP

DISPLAY

GO 3

disp

slide28

格式2:SKIP [±n]

功能:以当前记录为基准前移(-)或后移(+)N条记录。

说明:不选任选项,则默认n=+1。

注意:SKIP命令是相对移动,GOTO命令是绝对移动。

SKIP命令上移不能超过首记录,下移不能超过末记录。

slide30

3.4 记录数据的修改和删除

一、修改表数据

1、 EDIT修改命令

格式:EDIT

功能:显示需修改的记录。

例7

slide31

2、浏览修改命令BROWSE

格式:BROWSE[FIELDS<字段名表>][LOCK<数值表达式>][FREEZE<字段名>]

功能:BROWSE可打开一个全屏幕编辑窗口, 可浏览、修改、删除、追加记录。

说明:

[LOCK<数值表达式>]指定屏幕左边锁定字段的个数,当屏幕左右平移时,这些锁定的字段保持在原来的位置不动,便于用户对照。

[FREEZE <字段名>] 使光标冻结在这个字段上,用户只能修改该字段的数据,其它字段只能显示不能修改,提高了数据的安全性和操作效率。

slide32

[FOR<表达式>]按指定条件进行判断,当<表达式>的值为真时,方可显示记录。[FOR<表达式>]按指定条件进行判断,当<表达式>的值为真时,方可显示记录。

例 8:

use STU1

browse lock 1 freeze 数学

slide34

3、表运算替代命令

格式:REPLACE [<范围>] <字段名1> WITH <表达式1>[,<字段2> WITH <表达式2>[ADDITIVE]…][FOR<条件>][WHILE<条件>]

功能:用表达式的值代替命令中与之相对应的字段的内容,对满足<条件>和<范围>的记录可成批自动、快速地修改。

例1:use stu1

slide35

4、修改通用(General)字段

VFP支持微软公司的Windows对象链接和嵌入(OLE),MODIFY GENERAL命令可在任何活动表的当前记录编辑、修改装有OLE对象通用(General)字段。

格式:MODIFY GENERAL<通用字段>

菜单操作:直接用鼠标双击通用(General)字段, 打开了指定的通用字段的编辑窗口,用户可进行修改等操作.

slide36

二、删除表记录

1、给记录加删除标记

格式:DELETE [<范围>][WHILE<条件>][FOR<条件>]

功能:给要删除的记录作删除标记。

说明:本命令只给记录作删除标记.记录末删除,仍在表中.

命令操作时“*”即为删除标记,用菜单编辑窗口活动时,窗口中记录左边的方块钮,当为黑色时即是加了删除标记, 白色为恢复记录.

slide39

2、恢复加删除标记的记录

格式:RECALL [<范围>] [FOR<条件>] [WHILE<条件>]

功能:取消记录的删除标志。

slide40

3、彻底删除

格式:PACK

功能:删除当前表中加有删除标记的所有记录。

注:从当前表中删除记录后不可恢复。

slide41

4、表记录全部删除命令ZAP

格式:ZAP [IN<工作区号>|<表别名>]

功能:将所有的记录从表中删去,只留下表的结构。

说明:执行ZAP命令等于先执行DELETE ALL,然后再执行PACK。

slide42

以上的所有操作都可在浏览表后,操作主菜单中“表”进行。以上的所有操作都可在浏览表后,操作主菜单中“表”进行。

slide43

3.5 表的排序、索引和查询

物理整理

一、表的排序:对表记录进行物理整理,生成一个新表文件。

格式:SORT TO <文件名> ON <字段名1> [/A][/D][/C][,<字段名2>[/A][/C][/D]]… [<范围>] [FIELDS<字段表>] [FOR<条件>] [WHILE<条件>]

功能:按关键字段的值从小到大(升序/A)或从大到小(降序/D)将表的记录重新排列,生成一个新的表文件(.DBF)。

说明:(1)新表文件的结构与原表结构一样。

(2)选项>[/A]是升序,[/D]是降序。如有[/C]选是指按字典排列,大小写一样:0——9,Aa——Zz;如无[/C]选是指按ASC‖码顺序排列:0——9,A——Z,a——z。

slide44

(3)使用排序表文件时,必先打开。如添加了新记录,必须重新排序,生成一个新表文件。(3)使用排序表文件时,必先打开。如添加了新记录,必须重新排序,生成一个新表文件。

例1:

slide46

二、表的索引:对表记录进行逻辑整理,生成一个索引文件。二、表的索引:对表记录进行逻辑整理,生成一个索引文件。

1、索引的类型

逻辑整理

. IDX文件:只包含一单个索引。 压缩文件,选COMPACT]

类型 非压缩文件

. CDX文件 (复合索引文件) : 包含多个索引,每个索引叫

一个索引标识。 结构复合索引文件,其文件名与表名相同,打 开表时自动打开。

独立复合索引文件

slide47

2、索引文件的建立

建立.IDX索引文件:

格式1: INDEX ON <关键字表达式> TO <文件名> [COMPACT] [ADDITIVE]

功能:对当前表中各记录<关键字表达式>的值的大小,以升序进行记录逻辑上的重新排序,并将逻辑顺序存入TO后指定的索引文件名的索引文件中。

例2:

slide48

建立.CDX索引文件

格式2:INDEX ON <关键字表达式> TAG <标识名> [OF<文件名>] [FOR <条件>]

功能:对当前表根据关键字表达式的值以升序进行记录的逻辑排序,并将逻辑顺序存入与表文件名同名或[OF<文件名>]所指的复合索引文件的一个标识中。

slide51

说明:

(1)备注字段不能作关键字。

(2)格式2中无[OF<文件名>],则生成一个结构复合索引文件, TAG <标识名> 存入与表文件名相同的.CDX索引文件中。如有[OF<文件名>] , TAG <标识名> 则存入<文件名>所指的独立复合索引文件中。

(3) <关键字表达式> 即索引可按一个或多个字段进行索引,但与排序不一样,需写为表达式。

如: INDEX ON 姓名+学号 TO A2

INDEX ON STR(计算机基础)+STR(数学) TO A3

(4)[ADDITIVE],当建立索引文件时,保留已打开的索引文件仍为打开

slide52

使用表设计器(Table Designer)来生成索引

方法:打开表;再选择显示+表设计器菜单,单击索引标识卡,出现其对话框;然后在索引名文本框中输入该索引标识的名字,索引名框左边按钮决定升序或降序(对整个索引表达式而言);按拖最左边的箭头钮,可改变索引标识列出的顺序;类型下拉表中可选择索引类型(Primary 主索引、Candidate侯选、Unigue唯一等,表达式框可输入索引表达式(Expression);筛选框可形成筛选条件的索引。

注:1、使用表设计器(Table Designer)生成的索引文件为结构复合索引文件(.CDX)。

2、打开或新建的索引文件在表设计器中才可见。

操作练习

slide53

3、索引文件和索引标识的使用

索引文件必须依赖表文件,操作才有意义。

(1)表与索引文件同时打开的命令

格式1: USE <表文件名> INDEX <索引文件名表>

格式2: USE <表文件名> [ORDER [TAG] <索引文件或标识>]

例如:USE STU2 INDEX A2

USE STU2 INDEX BB ORDER TAG XB

USE STU2 ORDER TAG XB

USE STU2 INDEX STU2 ORDER TAG XH

功能:打开表同时打开索引文件。

slide54

说明:(1)<索引文件名表>中第一个索引文件是控制(主)索引文件,表中记录指针按第一个索引文件定位。说明:(1)<索引文件名表>中第一个索引文件是控制(主)索引文件,表中记录指针按第一个索引文件定位。

(2) [ORDER [TAG]<索引文件或标识>]是改变控制索引文件或标识。

slide55

(2)单独打开索引文件

格式1:SET INDEX TO [<索引文件名表>文件名>|[TAG]<复合索引文件名>|<索引标识名>[OF<复合索引文件名>]]]

例如:

SET INDEX TO A1,A2 && A1 是主索引文件

SET INDEX TO BB

SET INDEX TO BB TAG XBOF BB

SET INDEX TO STU2 TAG XB

SET INDEX TO BB TAG XB,XH

常用:

常用:

SET INDEX TO <索引文件名>

SET INDEX TO <索引文件名>TAG<索引标识名>

slide56

可以用下列命令改变主索引,而不需重新选择索引顺序。 可以用下列命令改变主索引,而不需重新选择索引顺序。

格式2:SET ORDER TO [<数值表达式>|<索引文件名>|[TAG]<索引标识名>[OF<复合索引文件名>][IN<工作区名>] [ASCENDING|DESCENDING]]

例如:

SET ORDER TO A2 (或SET ORDER TO 2) && A2 是主索引文件

SET ORDER TO TAG XB OF BB

SET ORDER TO TAG XB

常用:

SET ORDER TO <索引文件名>

SET ORDER TOTAG <索引标识名>[OF<复合索引文件名>]

slide57

四、表的查寻

1、检索命令 FIND

格式:FIND <“字符串”>|<字符串>

功能:在一个已经建立了索引文件的表中,定位到关键字中的内容与命令行中字符串相同的第一个记录。

说明:查询的<字符串>的字段须在建立索引文件时包含的关键字(字段)中。

2、检索命令 SEEK

格式:SEEK <表达式>

说明:SEEK命令比FIND命令功能更强,可以是一个表达式,且可是C、N、D型。

slide59

3、一般查寻命令LOCATE和CONTINUE

查寻命令LOCATE

格式:LOCATE [<范围>] [FOR<条件>] [WHILE<条件>] 功能:把当前表指针定位到符合指定条件的第一个记录上。

说明:[<范围>]选择项为指定查找记录的范围。

继续查寻命令CONTINUE

格式:CONTINUE

slide60

例如:use stu-1

locate all for 数学>=80

disp 姓名,数学

continue

循环disp 姓名,数学

continue

disp 姓名,数学

...

slide61

3.6 表的计算、汇总

VFP不但能对表的排序、索引、查寻、修改等操作进行管理,而且还能统计、汇总表以及用报表文件将结果存贮和打印出来。

一、表的计算命令

1、计数命令

格式:COUNT[<范围>][FOR<条件>][WHILE<条件>][TO<内存变量名>]

功能:统计当前表文件中符合条件的记录数。

例3.32 (P74):在Ffile表文件(见P63表3-4)中,统计出工资大于12000的讲师的记录数,存M1中。

USE Ffile

COUNT FOR 职称="讲师".AND.工资>12000.00 TO M1

? “M1=”,M1

M1=2

slide62

2、求和命令

格式:SUM [<范围>][数值<表达式>][TO <内存变量名表>|TO ARRAY <数组名>][FOR <条件>][WHILE<条件>][NOOPTIMIZE]

功能:对当前表中选中记录的全部或指定的数值字段或由指定字段组成的数值表达式累加求和。

P74例4.33:求Ffile表文件中讲师的平均工资和平均年龄。

USE Ffile

SUM ALL 工资,年龄 TO G1,G2 FOR 职称="讲师"

?“工资总和 =”,G1

工资总和 = 45222.00

?“ 年龄总和=”, G2

年龄总和= 84

COUNT ALL FOR 职称="讲师" TO N

? “平均工资=”,G1/N, “平均年龄=”,G2/N

平均工资= 22611.00 平均年龄= 42

slide63

3、求平均值命令AVERAGE

格式:AVERAGE[<范围>][<数值表达式>][TO <内存变量名表>|TO ARRAY <数组名>] [FOR<条件>][WHILE<条件>][NOOPTIMIZE]

功能:对当前表中选中记录的全部或部分数值型字段及其组成的表达式求平均值并显示,且可存入内存变量名表或一维数组中。

例4.34:求Ffile表中讲师的平均工资和平均年龄(题目与上例相同).

USE Ffile

AVERAGE ALL 工资,年龄 TO N1,N2 FOR 职称="讲师"

? “平均工资=”,N1, “平均年龄=”,N2

平均工资= 22611.00 平均年龄= 42

slide64

二、表的汇总命令

格式:TOTAL TO <文件名> ON <关键字>[FIELDS <字段名表>][<范围>][FOR<条件>] [WHILE<条件>][NOOPTIMIZE]

功能:对当前表中选中记录的全部或部分数值型字段按关键字值相同的记录进行汇总,并将其结果放入<文件名>指定的表文件中。

说明:1.使用TOTAL命令前必须对当前表按<关键字>进行过排序或者索引。

2.生成的汇总文件的结构与表文件结构相同,数据部分只包含当前表文件与汇总文件相匹配的那些字段的值,且对其中关键字值相同的记录的数值型字段求和。

slide65

在实际运用中,用户常常需要将表的数值字段进行分类统计,比如宾馆的收银表管理中需要按楼层对房费、其它应收费和实际金额等字段进行汇总,且将汇总结果放到另一个表(汇总表)中去,以便打印报表。 在实际运用中,用户常常需要将表的数值字段进行分类统计,比如宾馆的收银表管理中需要按楼层对房费、其它应收费和实际金额等字段进行汇总,且将汇总结果放到另一个表(汇总表)中去,以便打印报表。

例3.35:对宾馆收银表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

slide66

TOTAL ON 楼层 TO NSUM

USE NSUM

LIST

Record# 楼层 房号 姓名 其它应收 房费 实际金额

1 二楼 2010 刘炎 143 1050.00 1193.00

2 三楼 3001 郭丹丹175 1600.00 1775.00

slide67

3.7 多表的关联、连接和更新

1、 多对多关系、多对一关系及其它关系

(1)多对多关系

例如学校开设选修课,每一个学生可以选修几门课程,而每门课程又可以被多个学生选学,教务处在管理这些数据时建立一个表JW.DBF,其表结构和部分记录如P77:

从表中可知存在“多对多”的关系,有大量的数据冗余。需要将上表分离成两个“多对一”关系的数据表。一个表为多门课程对一个学生,另一个表为多个学生对一门课程。

(2)多对一关系(或一对多关系)

第一个“一”表为学生表STUDENT,第二个“一”表为课程表LESSON,“多”表为联系表LINK。

对上三个表需要找出使它们有相互联系的字段(称为关键字段),STUDENT表确定为学号字段,LESSON表确定为课程号字段。而LINK表确定为学号字段与STUDENT表联系、确定为课程号字段与LESSON表联系。

slide68

2、不同工作区中表的操作

在VFP中一般可以同时打开255个表(实际上是受内存限制,内存大还可更多),为使它们之间互不干扰,系统允许开辟32767个工作区。

(1) 常用的前10个工作区:工作区号为1至10,

工作区名用A至J表示,(工作区11—32767 中指定的别名是w11到w32767)。

(2) 一个工作区只能打开一个表。同一个表也只能在一个工作区中打开。

(3)选择工作区命令

格式:SELECT <工作区号/ 工作区名>|<别名>

作用:激活工作区

slide69

如: select a

use stu1

select b

use stu2

select 1

list

use

说明:当VFP等启动时,系统默认的工作区是1号工作区,所以到目前为止表操作均在1号工作区中进行。用户可以使用子句<工作区号>中的数值指定工作区号。

(4)<别名>是在打开表文件时由ALIAS任选项指定的名字,: 格式:USE <表文件名> ALIAS <别名>

如果未用ALIAS任选项,则表文件就是它的别名,也可以用工作区名作为已在该工作区中打开的表文件的别名。接上例:

slide70

sele 3

use stu1 alias s1

sele stu2

List

sele s1

List

close all

(5)使用非当前工作区中的表

格式: <工作区名 / 工作区 号/ 别名>.<字段名>

或<工作区名 / 工作区 号/ 别名>—><字段名>

功能:显示指定<工作区名 / 工作区 号/ 别名> 非当前工作区 中打开的表的<字段名>的内容。

说明:在不同工作区中打开的表都有其相应的记录指针,当前表记录指针的移动不会影响其它工作区中表记录指针的变化

slide71

例3.36:有两个“一对一”的表:学生成绩表STU1和学生档案表STU2。例3.36:有两个“一对一”的表:学生成绩表STU1和学生档案表STU2。

SELE 1

USE STU1

SELE 3

USE STU2

SELE 1

LIST NEXT 3 姓名,C->性别,C->出生日期,C->就读方式

Record# 姓名 C->性别 C->出生日期 C->就读方式

1 李蓉 女 78/05/20 统招

2 张天明 女 78/05/20 统招

3 王晓云 女 78/05/20 统招

从显示结果看出,非当前工作区(C区)的记录指针未随主工作区指针的移动而移动,故发生了张冠李戴的情况。

slide72

要解决这个问题就必须要在两个表之间建立关联,即两个表建立了指针的逻辑关系。在实际的表系统中常常要管理多个表,所以在对多重表的操作中使用关联、连接、更新,设置和存贮表之间的临时关系是十分重要的。 要解决这个问题就必须要在两个表之间建立关联,即两个表建立了指针的逻辑关系。在实际的表系统中常常要管理多个表,所以在对多重表的操作中使用关联、连接、更新,设置和存贮表之间的临时关系是十分重要的。

一、关联

格式:SET RELATION [TO <关键字表达式>|<数字表达式> INTO <别名>][,TO <关键字表达式>|<数字表达式> INTO <别名>]…ADDITIVE

功能:把当前工作区(主工作区)中的表文件与〈别名〉所指定的辅工作区中的表文件通过〈表达式〉建立关联。

slide73

说明:

(1)命令中的〈关键字表达式〉其值必须是两个表文件共同有的字段,它是两个表文件建立关系的条件。

(2)命令中若使用关键字表达式,则〈别名〉表文件必须在该关键字表达式上建索引文件并打开。

(3)命令中若用数值表达式,则两个表文件是按照记录号相关联的。

(4)如果〈别名〉表文件中没有找到匹配的记录,指针将定位在文件尾,EOF()=.T.;

(5)VEP允许主工作区同时和多个辅工作区建立关联,即A可以关联B,A又可以关联C,……等等;

(6)格式中如有[ADDITIVE]任选项,表示本命令建立关联时仍然保留该区与其它工作区已经建立的关联。

(7)命令若缺省任选项(SET RELATION TO ),则将中断关联。

slide74

例题1:

SELE 1

USE STU1

INDEX ON 学号 TO XH1

SET INDEX TO XH1

SELE 3

USE STU2

INDEX ON 学号 TO XH

SET INDEX TO XH

SELE 1

SET RELATION TO 学号 INTO C

LIST NEXT 3 姓名,C->性别,C->出生日期,C->就读方式

Record# 姓名 C->性别 C->出生日期 C->就读方式

1 李蓉 女 78/05/20 统招

2 张天明 男 77/09/07 统招

3 王晓云 男 78/11/11 统招

slide75

例题2:

SELE 1

USE STU1

SELE 2

USE STU2

SELE 1

SET RELATION TO RECNO() INTO B

LIST NEXT 3 姓名,C->性别,C->出生日期,C->就读方式

Record# 姓名 C->性别 C->出生日期 C->就读方式

1 李蓉 女 78/05/20 统招

2 张天明 男 77/09/07 统招

3 王晓云 男 78/11/11 统招

思考题:如以平均成绩作为关键字建关联,将可能会出现什么情况?

slide77

用VFP的数据工作区窗口实现上述操作

单击窗口+数据工作期菜单。

1.工作区的选择

在数据工作期窗口中单击可选择工作区,用打开(Open)对话框打开一个表。

2.设置表的关联

数据工作期窗口提供了两个表间建立关联的操作,但要求要建立关系的两个表必须具有“多对一”关系,且“多”表所在的工作区必须置为当前工作区,设置关联的“一”表在非当前工作区,“一”表必须对关键字进行索引。

如下图

slide79

二、生成.VUE视图文件

在使用“数据工作期”窗口时,选择“文件”菜单中的“另存为”,将其窗口存储为视图文件

slide80

三、不同工作区中表的连接

不同工作区的两个表以字段为单位的纵向连接。

格式:JOIN WITH <别名> TO <文件名> [FIELDS <字段名表>] FOR <条件>

功能:主工作区的表和<别名>所指的辅工作区的表,按要求的条件、指定的字段进行连接,建立一个新的表文件,扩展名为.DBF。

注意:格式中的FOR<条件>是必须的。

例3.42: 将STU1与STU2连接成新表文件STU4。

SELE 1

USE STU1

SELE 2

USE STU2

JOIN WITH A TO STU4 FIELDS 学号,A->姓名,性别,A->计算机基础 FOR 学号=A->号

slide81

USE STU4

      • LIST
  • Recorde# 学号 A->姓名 性别 A->计算机基础
  • 1 962101 李蓉 女 90.00
  • 2 962102 张天明 男 87.00
  • 3 962103 王晓云 男 80.00
  • 4 962104 王小菲 女 94.00
  • 5 962105 孙力 男 76.00

四、更新

用REPLAC命令可以更新一个表中的数据,但操作不太方便。本节将介绍用非当前工作区表的内容去替换或修改当前工作区中表的内容。

格式:UPDATE ON <关键字> FROM <别名> REPLACE <字段名> WITH <表达式> [,<字段名> WITH <表达式>…] [RANDOM]

功能:用<别名>所指定的表(源表)的数据更新当前工作区表的数据(目标表)。

slide82

注意:UPDATE的执行过程:当前表的第一条记录按照 <关键字>对<别名>表中的每一条记录进行比较,若相等则用<表达式>的值更新目标表中<字段名>的数据。若有多条记录与之相等,只对第一条相等的记录发生作用。然后主区数据表的记录指针向下移一条记录,重复上述操作,直到主工作区表全部记录都被处理完毕。

例题5:用STU1表中的平均成绩来更新STU2表的平均分。

SELE 1

USE STU1

SELE 2

USE STU2

INDEX ON 学号 TO XH

SET INDEX TO XH

UPDATE ON 学号 FROM A REPLACE 平均分 WITH A->平均分

LIST

演示程序3-2

slide83

3.8 其它有关命令操作

一、关闭命令

格式:CLOSE ALL

功能:关闭所有工作区中的表文件,同时关闭所有索引文件和屏幕格式文件,并使1号工作区成为当前工作区。

二、复制表文件

1、表文件的复制

格式1:COPY TO <文件名> [<范围>][FIELDS<字段名表>][WHILE< 条件>][FOR<条件>]

功能:将当前表文件内容全部或部分地复制到新文件中。

说明:(1)对已打开的表文件进行复制。(2)<字段名表>中可 包括备注型字段。

slide84

Use stu1

Copy to stu3

Use stu3

list

格式2:COPY FILE <源文件名> TO <新文件名>

功能:对文件进行整体复制。

说明:

(1)对未打开的表文件进行整体复制,文件可以是各类型文件。

(2)若源文件有备注文件时必须再使用copy命令复制与该表文件相应的备注型文件。

slide85

2、表结构的复制

格式:COPY STRUCTURE TO <文件名> [FIELDS<字段名表>]

功能:将当前表结构的全部或部分复制到指定<文件名>中去

Use stu1

Copy structure to stu4 fislas 姓名,学号计,算机基础

Use stu4

list

slide86

三、数据结合文件

格式:APPEND FROM <文件名> [FIELDS<字段名表>][WHILE<条件>][FOR<条件>]

功能:将文件名指定的表文件内容追加到当前表的未尾。例:

slide87

3.9

结构化查询语言SQL

slide88
3.9.1 SQL语言概述

3. 9.2 数据查询

3. 9.3 数据定义

3. 9.4 数据操纵

slide89
3. 9.1 SQL语言概述

SQL是结构化查询语言(Structured Query Language)的缩写。SQL语言来源于20世纪70年代IBM的一个被称为SEQUEL的研究项目。20世纪80年代,SQL由ANSI (美国国家标准协会)进行了标准化,被确定为关系型数据库管理系统的标准语言。

目前流行的关系数据库管理系统,如Oracle、Sybase、SQL Server、Visual FoxPro等都采用了SQL语言标准。并且很多数据库系统都对SQL语句进行了再开发和扩展,从而使未来的数据库环境连接为一个统一的整体。 SQL语言提供了访问关系型数据库的标准方法 。

SQL语言也可在过程化语言编写的数据库应用程序中使用(被称为嵌入式SQL)。

slide90
SQL语言具有如下特点:

(1)一体化语言。SQL提供了一系列完整的数据定义、数据查询、数据操纵、数据控制等方面的功能。用SQL可以实现数据库生命周期中的全部活动,包括数据库和表结构的定义、修改,实现表中数据的增加、修改、删除、查询和维护,数据库重构、数据库安全控制等一系列操作要求。

注:由于Visual FoxPro自身在安全控制方面的缺陷,所以它没有提供数据控制功能。

slide91

表 3-1 SQL的基本功能和命令

SQL功能

命令

数据定义语言

(用于创建、修改或删除数据表)

CREATE、ALTER、DROP

数据查询语言

(用于查询数据)

SELECT

数据操纵语言

(用于插入、修改、删除数据)

INSERT、UPDATE、DELETE

数据控制语言

(控制用户对数据表的访问权限等)

GRANT、REVOKE

slide92
(2)高度非过程化。SQL和其他数据操作语言不同,SQL是一种非过程性语言,它不必一步步地告诉计算机“如何”去做,用户只需说明做什么操作,而不用说明怎样做,不必了解数据存储的格式及SQL命令的内部,就可方便地对关系数据库进行操作。(2)高度非过程化。SQL和其他数据操作语言不同,SQL是一种非过程性语言,它不必一步步地告诉计算机“如何”去做,用户只需说明做什么操作,而不用说明怎样做,不必了解数据存储的格式及SQL命令的内部,就可方便地对关系数据库进行操作。
slide93
(3)语言简洁。虽然SQL的功能很强大,但语法却很简单,只有为数不多的几条命令。初学者经过短期的学习就可以使用SQL进行数据库的存取等操作,因此,易学易用是它的最大特点。(3)语言简洁。虽然SQL的功能很强大,但语法却很简单,只有为数不多的几条命令。初学者经过短期的学习就可以使用SQL进行数据库的存取等操作,因此,易学易用是它的最大特点。
slide94

表 3-1 SQL的基本功能和命令

SQL功能

命令

数据定义语言

(用于创建、修改或删除数据表)

CREATE、ALTER、DROP

数据查询语言

(用于查询数据)

SELECT

数据操纵语言

(用于插入、修改、删除数据)

INSERT、UPDATE、DELETE

数据控制语言

(控制用户对数据表的访问权限等)

GRANT、REVOKE

slide95
(4)统一的语法结构对待不同的工作方式。 SQL语言可以直接以命令方式交互使用,也可以嵌入到程序设计语言中以程序方式使用。现在很多数据库应用开发工具都将SQL语言直接融入到自身的语言之中,使用起来更方便,Visual FoxPro就是如此。这些使用方式为用户提供了灵活的选择余地。
slide96
注:①在书写的时候,如果命令太长,可以用“;”号换行。

②VFP中SQL命令的使用方式(与其它 VFP命令相同)。

即:可以在VFP的命令窗口、程序(.PRG)或表单(.SCX)的事件程序中使用。

slide97
SQL虽然在各种数据库产品中得到了广泛的支持,但迄今为止,它只是一种建议标准,各种数据库产品中所实现的SQL在语法、功能等方面均略有差异。 SQL虽然在各种数据库产品中得到了广泛的支持,但迄今为止,它只是一种建议标准,各种数据库产品中所实现的SQL在语法、功能等方面均略有差异。

SQL方言 :不同的数据库管理系统厂商既遵循了标准SQL语言规定的基本操作,又在标准SQL语言的基础上进行了扩展和增强。

本章讲述Visual FoxPro 中SQL的语法、功能与应用。

slide98

3. 9.2 数据查询

数据查询是数据库中最常见的操作,数据查询也是SQL的核心。

通过使用 SELECT-SQL语句,可以完全控制查询结果以及结果的存放位置。

SELECT-SQL语句是SQL语言中最重要、使用最频繁的一条语句,也是最复杂的一条语句。

slide100
【命令格式】 

SELECT [ALL|DISTINCT]

[<别名>.]<选项>[AS <显示列名>][,[<别名>.]<选项>[AS <显示列名>]…]

FROM [<数据库名!]<表名>[[AS] <本地别名>]

[[INNER | LEFT [OUTER] | RIGHT[OUTER]|FULL [OUTER]

JOIN <数据库名>!]<表名>[[AS]<本地别名>][ON <联接条件>…]

[[INTO <目标>|[TO FILE<文件名>][ADDITIVE]

|TO PRINTER [PROMPT]|TO SCREEN]]

[PREFERENCE <参照名>][NOCONSOLE][PLAIN][NOWAIT]

[WHERE <联接条件1>[AND <联接条件2>…]

[AND|OR <过滤条件1>[AND|OR <过滤条件2>…]]]

[ORDER BY <排序选项1>[ASC|DESC][,<排序选项2>[ASC|DESC]…]]

[GROUP BY <分组列名1>[,<分组列名2>…]]

[HAVING <过滤条件>]

[UNION[ALL]SELECT命令]

slide101
其中主要短语的含义如下:
  • SELECT:说明要查询的数据。
  • FROM:要查询的数据来自哪个或哪些表。
  • WHERE:查询条件,即选择记录的条件。
  • ORDER BY:使数据进行排序,排列查询结果。
  • GROUP BY: 用于对查询结果进行分组,可以利用它进行分组汇总。
  • HAVING:跟随GROUP BY 使用,用来限定分组必须满足的条件。
slide102
注:SELECT语句的执行过程为:根据WHERE子句的连接和检索条件,从FROM子句指定的基本表或视图中选取满足条件的元组,再按照SELECT子句中指定的列表达式,选出元组中的属性值形成结果表。如果有GROUP子句,则将查询结果按照指定<列名>相同的值进行分组;如果GROUP子句后有HAVING短语,则只输出满足HAVING条件的分组;如果有ORDER子句,查询结果还要按照指定<列名>的值进行排序。注:SELECT语句的执行过程为:根据WHERE子句的连接和检索条件,从FROM子句指定的基本表或视图中选取满足条件的元组,再按照SELECT子句中指定的列表达式,选出元组中的属性值形成结果表。如果有GROUP子句,则将查询结果按照指定<列名>相同的值进行分组;如果GROUP子句后有HAVING短语,则只输出满足HAVING条件的分组;如果有ORDER子句,查询结果还要按照指定<列名>的值进行排序。

SELECT命令,它的基本形式是:SELECT…FROM…WHERE…。数据查询命令可以实现数据库的任何查询。为清楚起见我们下面分别进行介绍。

slide103
3. 9.2.1 基本查询(无条件查询)

【命令格式】 

SELECT [ALL|DISTINCT]

[<别名>.]<选项>[AS <显示列名>],

[<别名>.]<选项>[AS <显示列名>…]

FROM <表名1>[别名1>][,<表名2>[别名2>…]

【说明】

①“*”可以用来表示某一个数据表中的所有字段。

②ALL:选出的记录中包括重复记录,这是缺省值。

③ DISTINCT:表示输出无重复结果的记录。

④当选择多个数据表中的字段时,可使用别名来区分不同的表。

⑤显示列名用于指定输出时使用的列标题,可以不同于字段名。

⑥选项可以是字段名、表达式或函数。

⑦表名代表要查询的表。

slide104
【例】 列出学生名单。

OPEN DATABASE D:学生管理

SELECT * FROM 学生

命令中的*表示输出所有字段,数据来源是学生表,所有内容以浏览方式显示。

【例】列出所有学生姓名,去掉重名。

SELECT DISTINCT 姓名 ;

AS 学生名单 FROM 学生

slide105

表 3-2 查询计算函数的格式及功能

注:SELECT 命令中的选项,不仅可以是字段名,还可以是表达式,也可以是一些函数。即可在SELECT短语中对查询结果进行计算。

slide106

【例】 求出所有学生的入学成绩平均分。

SELECT AVG(入学成绩) AS;

″入学成绩平均分″ FROM 学生

slide107
3. 9.2.2 带条件查询

当要在数据表中找出满足某些条件的行时,则需使用WHERE子句指定查询条件。

其格式是:

WHERE <条件表达式>

注:执行时,把条件为真的记录反馈回来,对条件为假的那些记录信息,则什么也不执行。

slide109

注: 可用NOT运算符来设计否定条件。

【例】 求出湖南籍学生入学成绩平均分。

SELECT 籍贯, AVG(入学成绩) ;

AS ″入学成绩平均分″ FROM 学生;

WHERE 籍贯=″湖南″

【例】 列出江苏籍和贵州籍的学生名单。

SELECT 学号,姓名,籍贯 FROM 学生;

WHERE 籍贯 IN (″江苏″,″贵州″)

以上命令的功能等同于:

SELECT 学号,姓名,籍贯 FROM 学生 ;

WHERE 籍贯=″江苏″ OR 籍贯=″贵州″

slide110
【例】 列出入学成绩在560分到650分之间的学生名单。

SELECT 学号,姓名,入学成绩 FROM 学生;WHERE 入学成绩 BETWEEN 560 AND 650

在以上命令的功能等同于:

SELECT 学号,姓名,专业 FROM 学生 WHERE 入学成绩>=560 AND 入学成绩<=650

【例】列出所有的姓赵的学生名单。

SELECT 学号,姓名 FROM 学生 ;

WHERE 姓名 LIKE ″赵%″

slide111
注:
  • 尽管在查询设计器窗口中做了许多工作,实际上只生成了一句SELECT命令。在熟悉了SQL命令之后,可不必借助于查询设计器而直接在程序中书写SELECT命令。
  • 在程序中通过动态地生成SELECT命令,可以处理更复杂的实际应用。例如:常用的组合查询的实现就必须使用SELECT命令。
slide116
3. 9.2.3 嵌套查询

有时候一个SELECT命令无法完成查询任务,需要一个子SELECT的结果作为条件语句的条件,即需要在一个SELECT命令的WHERE子句中出现另一个SELECT命令,这种查询称为嵌套查询。 WHERE子句中出现的查询块称为子查询,包含子查询的语句称为父查询。

通常把仅嵌入一层子查询的SELECT命令称为单层嵌套查询,把嵌入子查询多于一层的查询称为多层嵌套查询。

注:Visual FoxPro只支持单层嵌套查询。

slide117
1. 返回单值的子查询

此类查询的子查询中只返回一条记录。

CREATE TABLE 课程(课程号 C(5),课程名 C(20))

CREATE TABLE 选课(学号 C(5),课程号 C(5),成绩 I【例】列出选修“数据库原理”的所有学生的学号。

SELECT 学号 FROM 选课 WHERE 课程号=;

(SELECT 课程号 FROM 课程 WHERE 课程名=″数据库原理″)

上述SQL语句执行的是两个过程,首先在课程表中找出“数据库原理”的课程号(比如“01”),然后再在选课表中找出课程号等于“01”的记录,列出这些记录的学号。

slide118
2. 返回一组值的子查询

若某个子查询返回值不止一条记录,则必须指明在WHERE子句中应怎样使用这些返回值。通常使用条件ANY(或SOME)、ALL和IN。

slide119
(1)ANY运算符的用法

CREATE TABLE 选课(学号 C(5),课程号 C(5),成绩 I )

【例】 列出选修“01”课的学生中成绩比选修“02”的最低成绩高的学生的学号和成绩。

SELECT 学号,成绩 FROM 选课 ;

WHERE 课程号=″01″ AND 成绩>ANY;

(SELECT 成绩 FROM 选课;

WHERE 课程号=″02″)

该查询做两件事:先找出选修“02”课的所有学生的成绩(比如说结果为90和52),然后在选修“01”课的学生中选出其成绩高于选修“02”课的任何一个学生的成绩(即高于52分)的那些学生信息。

slide120
(2)ALL运算符的用法

【例】列出选修“01”课的学生的学号和成绩,这些学生的成绩比选修“02”课的最高成绩还要高。

SELECT 学号,成绩FROM 选课 ;

WHERE 课程号=″01″ ;

AND 成绩>ALL ;

(SELECT 成绩 FROM 选课 ;

WHERE 课程号=″02″)

该查询的含义是:先找出选修“02”课的所有学生的成绩(比如说结果为90和52),然后再在选修“01”课的学生中选出其成绩中高于选修“02”课的所有成绩(即高于90分)的那些学生。

slide121
(3)IN运算符的用法

CREATE TABLE 课程(课程号 C(5),课程名 C(20))

CREATE TABLE 选课(学号 C(5),课程号 C(5),成绩 I )

【例】 列出选修“数据库原理”或“软件工程”的所有学生的学号。

SELECT 学号 FROM 选课 WHERE 课程号 IN;

(SELECT 课程号 FROM 课程 ;

WHERE 课程名=″数据库原理″;

OR 课程名=″软件工程″)

IN是属于的意思,等价于“=ANY”,即等于子查询中任何一个值。

注:嵌套查询是基于多个表的查询。这类查询所要求的结果出自一个表,但相关的条件却涉及多个表。

slide122
3. 9.2.4 多表查询

在一个表中进行查询,一般说来是比较简单的,而在多表之间查询就比较复杂,必须处理表和表间的联接关系。使用SELECT命令进行多表查询是很方便的。

1. 等值联接

等值联接是按对应字段的共同值将一个表中的记录与另一个表中的记录相联接。

【例】输出所有学生的成绩单,要求给出学号、姓名、课程号、课程名和成绩。

SELECT a.学号, a.姓名, b.课程号, c.课程名, b.成绩;FROM 学生 a, 选课 b, 课程 c;

WHERE a.学号=b.学号 AND b.课程号=c.课程号

slide123
2. 非等值联接

【例】 列出选修“02”课的学生中,成绩大于学号为“200109”的学生该门课成绩的那些的学号和成绩。

SELECT a.学号, a.成绩 FROM 选课a, 选课b;

WHERE a.成绩>b.成绩AND a.课程号=b.课程号;AND b.课程号=″02″AND b.学号=″200109″

slide124
3. 9.2.5 查询结果处理

1. 排序输出(ORDER)

SELECT的查询结果是按查询过程中的自然顺序给出的,因此查询结果通常无序,如果希望查询结果有序输出,需要下面的子句配合:

ORDER BY <排序选项1> [ASC | DESC]

[,<排序选项2>[ASC | DESC]…]

【说明】

①排序选项可以是字段名,也可以是数字。字段名必须是主SELECT子句的选项。数字是表的列序号,表中第一个字段的列序号为1。

② ASC指定的排序项按升序排列(默认为升序) ,DESC指定的排序项按降序排列。允许按一列或多列排序。 

slide125

【例】 按入学成绩降序列出入学成绩在550分到650分之间的学生名单。

SELECT 学号,姓名,入学成绩 FROM 学生; WHERE 入学成绩 BETWEEN 550 AND 650; ORDER BY 入学成绩 DESC 

slide126
2. 重定向输出(INTO)

默认的查询结果显示在浏览窗口中。

查询结果也可以重定输出方向,其子句格式是: 

[INTO <目标>]

| [TO FILE<文件名>[ADDITIVE]

| TO PRINTER]

slide127
【说明】

①“<目标>” 有如下3种形式:

ARRAY <数组名>:将查询结果存到指定数组名的内存变量数组中。一般将存放查询结果的数组作为二维数组来使用,每行一条记录,每列对应于查询结果一列。查询结果存放在数组中,可以非常方便地在程序中使用。

CURSOR <临时表>:将输出结果存到一个临时表(游标),这个表的操作与其他表一样,不同的是,当关闭临时表时该临时表将自动删除。比如一些复杂的汇总可能需要分阶段完成,需要根据几个中间结果再汇总等,这时利用该短语存放中间结果就非常合适。

DBF <表>|TABLE <表>:将查询结果存放到永久表中(dbf文件)。

slide128
②“TO FILE <文件名>[ADDITIVE]”

将查询结果存放到文本文件中(默认扩展名是txt) 。如果使用ADDITIVE选项,结果将追加在原文件的尾部,否则将覆盖原有文件。

③“TO PRINTER”

将查询结果直接输出到打印机。

slide129
【例】 将前例的查询结果保存到test1.txt文本文件中。

SELECT 学号,姓名,入学成绩 FROM 学生;

WHERE 入学成绩 BETWEEN 550 AND 650;

ORDER BY 入学成绩 DESC TO FILE test1

slide130
3. 输出合并(UNION)

输出合并是指将两个查询结果进行集合并操作,其子句格式是:

[UNION [ALL] <SELECT命令>]

【说明】

其中ALL表示结果全部合并。若没有ALL,则重复的记录将被自动取掉。合并的规则是:

(1)不能合并子查询的结果。

(2)两个SELECT命令必须输出同样的列数。

(3)两个表各相应列出的数据类型必须相同,数字和字符不能合并。

(4)仅最后一个<SELECT命令>中可以用ORDER BY子句,且排序选项必须用数字说明。

slide131
【例】 列出选修“01”或“02”课程的所有学生的学号。

SELECT 学号 FROM 成绩 ;

WHERE 课程号=″01″;

UNION SELECT 学号 FROM 成绩;

WHERE 课程号=″02″

slide132
4. 分组统计与筛选

查询结果分组的格式是:

GROUP BY <分组选项1>[,<分组选项2>…]

其中<分组选项>可以是字段名,SQL函数表达式,也可以是列序号。

筛选条件格式是:

HAVING <筛选条件表达式>

HAVING子句与GROUP BY子句连用,用来指定每一分组内应满足的条件。

slide133
注:①当在一个SQL查询中同时使用WHERE子句、GROUP BY子句和HAVING子句时,其顺序是WHERE,GROUP BY,HAVING。

②WHERE子句作用于表或视图,从中选择满足条件的元组;HAVING子句作用于分组,选择满足条件的分组。 即:WHERE子句是用来指定表中各行所应满足的条件,而HAVING子句是用来指定每一分组所满足的条件。

slide134
【例】 分别统计男女人数。

SELECT 性别,COUNT(性别) FROM 学生; GROUP BY 性别;

【例】分别统计至少有两个学生的每门课程的平均成绩。

SELECT 课程号 ,AVG(成绩) FROM 选课;

GROUP BY 课程号 HAVING COUNT(*)>=2

slide135

【例】 在课程“C120”、“C140”、“C150”和“C160”中查询学生平均成绩在80分以上课程的学生的平均分(同时显示课程号)。

SELECT 课程号,AVG(成绩) AS 平均分;

FROM 选课;

WHERE 课程号 IN ("c120","c140","c150","c160");

GROUP BY 课程号;

HAVING AVG(成绩)>=80

注意: WHERE子句在选课表中筛选出课程号为“C120”、“C140”、“C150”和“C160”的记录,GROUP BY子句按课程号的值分组,具有相同课程号的记录为一组,对每一组的成绩使用函数AVG进行计算,最后得到平均成绩在80分以上的课程学生的平均分。

slide136

3. 9.3 数据定义

3. 9.3.1 建立表结构

【命令格式】

CREATE TABLE <表名>[FREE]

(<字段名1> <类型>(<宽度>[,<小数位数])

[NULL|NOT NULL]

[CHECK <条件表达式1>[ERROR<出错显示信息>]]

[DEFAULT <表达式1>][PRIMARY KEY | UNIQUE]

[,<字段名2> … ]) ……)

slide137

【说明】 

表名:要建立的表的名称。

FREE:如果当前已经打开一个数据库,这里所建立的新表会自动加入该数据库,除非使用参数“FREE”说明该新表作为一个自由表不加入当前数据库。若没有打开的数据库,该参数无意义。

字段名1、字段名2、……:所要建立的新表的字段名,在语法格式中,两个字段名之间的语法成分都是对一个字段的属性说明,包括:

类型——说明字段类型。

宽度及小数位数——字段宽度及小数位数。

NULL、NOT NULL——该字段是否允许“空值”,其默认值为NULL,即允许“空”值。

CHECK 条件表达式——用来检测字段的有效性,这是实行数据库的一种完整性检查。

slide138

ERROR 出错显示信息——当完整性检查有错误,即条件表达式的值为假时的提示信息。应当注意,当为一个表的某个字段建立了实行完整性检测的条件表达式后,在对该数据表输入数据时,系统会自动检测所输入的字段值是否使条件表达式为假,当有一个数据使其为假时,系统自动显示这里所提示的出错信息。

DEFAULT 表达式——为一个字段指定的默认值。

PRIMARY KEY——指定该字段为关键字段,非数据库表不能使用该参数。

UNIQUE——指定该字段为一个侯选关键字段。注意,指定为关键或侯选关键的字段都不允许出现重复值,这称为对字段值的唯一性约束。

slide139

【例】创建学生基本情况数据表。

CREATE TABLE 学生;

( 学号 C (8) PRIMARY KEY NOT NULL;

姓名 C (8) NOT NULL, ;

性别 C (2) DEFAULT ‘男’ CHECK 性别=’男’; OR 性别=’女’ERROR ‘性别必须是男或女’,;

出生年月D CHECK(出生年月<={^1990-01;

-01} AND 出生年月>={^1970-01-01},;

入校总分 N (3) ,;

三好生L;

特长M,;

照片G )

slide141

3. 9.3.2 删除表

【命令格式】 

DROP TABLE <表名>

【例】 删除表“学生”。

DROP TABLE 学生

【说明】

DROP TABLE命令直接从磁盘上删除所指定的表文件。如果指定的表文件是数据库中的表并且相应的数据库是当前数据库,则从数据库中删除了表。否则虽然从磁盘上删除了表文件,但是记录在数据库文件中的信息却没有删除,此后会出现错误提示。所以要删除数据库中的表时,最好应使数据库是当前打开的数据库,在数据库中进行操作。

slide142

3. 9.3.3 修改表结构

SQL语言使用ALTER TABLE命令来完成修改表结构的功能。该命令主要有两种格式。

【命令格式1】

ALTER TABLE <表名>

[ADD /ALTER[COLUMN]

<字段名> <字段类型>[<字段宽度> [,小数位]

[NULL/NOT NULL]

[CHECK <表达式> [ERROR <提示信息>]]

[DEFAULT <表达式>]

[PRIMARY KEY/UNIQUE]

【说明】

① ADD用于增加新列。

② ALTER用于修改某列。

slide143

【例】在“学生”表中增加字段“总分”的有效性规则为“总分>=500”当违反规则时,提示信息“总分必须大于500”。【例】在“学生”表中增加字段“总分”的有效性规则为“总分>=500”当违反规则时,提示信息“总分必须大于500”。

ALTER TABLE 学生;

ALTER COLUMN 总分 N(3) ;

CHECK(总分>=500);

ERROR “总分必须大于500”

slide144
【命令格式2】

ALTER TABLE <表名>

[DROP[COLUMN] <字段名>

[SET DEFAULT <表达式>] [DROP DEFAULT]

[SET CHECK <表达式> [ERROR <提示信息>]] [DROP CHECK]

[ADD PRIMARY KEY <字段名>] [DROP PRIMARY KEY]

[ADD UNIQUE<字段名>] [DROP UNIQUE TAG <索引名> ]

[RENAME COLUMN <旧字段名> TO <新字段名>]

slide145
【说明】

① DROP用于删除列、默认值、有效性规则、主键、候选索引。

② SET 用于为已有的字段设置默认值和有效性规则。

③ ADD 用于添加主索引和候选索引。

④ RENAME 用于修改表中的一个字段名。

【例】将“学生”表中“入校总分”字段改名为“高考成绩”。

ALTER TABLE 学生;

RENAME COLUMN 入校总分 TO 高考成绩

slide146

3. 9.4 数据操纵

SQL语言的数据操纵功能包括对表中数据的增加、删除和更新操作。

3. 9.4.1 插入记录

Visual FoxPro的SQL插入命令有两种格式:

【命令格式1】

INSERT INTO <表名>

[(字段名1[<字段名2>[,…]])]

VALUES(<表达式1>[,<表达式2>[,…]])

slide147
【说明】

①该命令在指定的表尾添加一条新记录,其值为VALUES后面表达式的值。

②当需要插入表中所有字段的数据时,表名后面的字段名可以缺省,但插入数据的格式及顺序必须与表的结构完全吻合;若只需要插入表中某些字段的数据,就需要列出插入数据的字段名,当然相应表达式的数据位置应与之对应。

slide148
【例】 向学生表中添加记录。

INSERT INTO 学生 ;

VALUES(″231002″,″王亮″,;

″男″,{^1988-09-10},.T.,;

″上海″,610,″″,″″)

INSERT INTO 学生(学号,姓名);

VALUES(″ 231002 ″,″王亮″)

slide149
【命令格式2】

INSERT INTO <表名>

FROM ARRAY <数组名> |FROM MEMVAR]

【说明】

该命令在指定的表尾添加一条新记录,其值来自于数组或内存变量。

slide150
【例】利用该数组向学生表中添加记录。

数组A(5)中各元素的值分别是:

A(1)=″231013″,

A(2)=″张阳″,

A(3)=″女″,

A(4)={^1988-09-10},

A(5)=.T. 

INSERT INTO 学生 FROM ARRAY A

注:当一个表定义了主索引或候选索引后,由于相应的字段具有关键字的特性,即不能为空,所以只能用INSERT INTO命令插入记录。FoxPro插入命令INSERT是先插入一条空记录,然后再输入各字段的值,由于关键字字段不允许为空,所以使用以前的方法就不能成功地插入记录。

slide151
3. 9.4.2 删除记录

在Visual FoxPro中,DELETE可以为指定的数据表中的记录加删除标记。

【命令格式】

DELETE FROM [<数据库名>!] <表名> [WHERE <条件表达式> ]

【说明】

该命令从指定表中根据指定的条件逻辑删除记录。

slide152
【例】 将“学生”表所有男生的记录逻辑删除。

DELETE FROM 学生 WHERE 性别=″男″注:上述删除命令只是加删除标记,并没有从物理上删除。只有执行了PACK命令,逻辑删除的记录才真正地从物理上删除。逻辑删除的记录还可以用RECALL命令取消删除。

slide153
3. 9.4.3 更新记录

更新记录的命令是UPDATE,此命令也可以对用SELECT语句选择出的记录进行数据更新。

【命令格式】 

UPDATE [<数据库名>!]<表名>

SET<字段名1>=<表达式1>[,<字段名2>=<表达式2>…] [WHERE<逻辑表达式>]

【说明】

对表中的一行或多行记录的某些列值进行修改。如果不使用WHERE子句,则更新全部记录。

slide154
【例】 将“学生”表中胡敏杰学生的籍贯改为广东。

UPDATE 学生 SET 籍贯=′广东′ ;

WHERE 姓名=′胡敏杰′

【例】 所有男生的各科成绩加20分

UPDATE 选课 SET 成绩=成绩+20;

WHERE 学号IN;

(SELECT 学号 FROM 学生 ;

WHERE 性别=′男′)

ad