vf数据库讲义.docx
《vf数据库讲义.docx》由会员分享，可在线阅读，更多相关《vf数据库讲义.docx（18页珍藏版）》请在冰豆网上搜索。
vf数据库讲义
第三章数据库
一、数据库（.DBC）
1．创建数据库：
CREATEDATABASE数据库名
2．打开数据库：
OPENDATABASE数据库名
3．打开数据库设计器：
MODIFYDATABASE数据库名
4．修改数据库：
MODIFYDATABASE数据库名
5．删除数据库：
DELETEDATABASE数据库名
6．关闭数据库：
CLOSEDATABASE
二、表（.DBF）（数据库表和自由表）
数据库表：
属于某个数据库的表称为数据库表。
数据库打开的状态下创建的表为数据库表
自由表：
不属于任何数据库的表称为自由表。
1．创建表：
CREATE表名
◆字段名：
数据库表最多128个字符，自由表最多10个字符。
以字母或汉字开头，不能有空格。
◆字段名类型：
字符型（C），数值型（N），货币型（Y），日期型（D,8个字节），日期时间型（T,8个字节），逻辑型（L,1个字节），浮点型（F），整型（I,4个字节），备注型（M,4个字节，指向文件的指针，存放个人简历等文本），通用型（G，4个字节，存放电子表格等）
2．打开表：
USE表名
3．打开表设计器：
MODIFYSTRUCTURE
4．添加表：
ADDTABLE表名
5．移去表：
REMOVETABLE表名
6．删除表：
DROPTABLE表名
7．关闭表：
USE
三、表结构的操作
1．增加或删除字段：
2．修改字段类型和宽度：
3．如果只读状态，命令CLOSEALL
四、表内容的操作
1．浏览记录：
BROW
LIST
2．增加记录：
APPEND（表尾追加）
INSERT（插入）
3．修改记录：
REPLACE字段名WITH表达式FOR条件
例如：
USE学生
REPLACE年龄WITH年龄＋10FOR学号=”001”
REPLACE年龄WITH20FOR学号=”002”
REPLACE性别WITH“女”FOR姓名=”小王”
REPLACEALL年龄WITH年龄＋10
4．删除记录：
（1）逻辑删除：
（置删除标记）DELETEFOR条件（如果不用FOR指定条件，则删除当前一条记录。
）
USE学生
DELETEFOR年龄<20
（2）恢复逻辑删除：
RECALLFOR条件（对逻辑删除的记录可以恢复）
USE学生
RECALLFOR年龄<20
（3）物理删除：
PACK（在逻辑删除的基础上彻底删除，不可恢复）
USE学生
DELETEFOR学号=”001”
PACK
（4）清空记录：
ZAP（清空表中全部记录，表结构仍然存在）
5．显示记录：
DISPLAY（显示当前一条记录）
LIST（显示全部记录）
DISPLAYALL（显示所有记录）
DISPLAYFOR性别=”女”
LISTFOR性别=”女”
6．记录指针定位：
（1）绝对指针定位：
GOTOP
GOBOTTOM
GO2
（2）移动指针：
SKIP（默认向下移动一条）
SKIP2
SKIP-1
（3）条件定位：
LOCATEFOR将指针定位到满足条件的第一条记录上，如果没有满足条件的记录，则指向文件结束位置，即文件尾；继续查找使用CONTINUE命令。
例如：
LOCATEFOR性别=”男”
DISPLAY
CONTINUE
索引（INDEX）
1．索引的类型
（1）主索引（PRIMARYKEY）：
主关键字
a）保证记录的唯一性；b）不允许出现重复值和空值；
c）一个表只能创建一个主索引；d）只有数据库表可以创建主索引。
（2）候选索引（CANDIDATE）
a））保证记录的唯一性；b）不允许出现重复值和空值；
c）一个表可以创建多个候选索引；d）数据库表和自由表都可以创建候选索引。
（3）普通索引（REGULAR）
（4）惟一索引（UNIQUE）
2．建立索引
（1）表设计器创建
（2）命令创建：
USE学生
INDEXON学号TAG学号
INDEXON学号TAG学号CANDIDATE
（3）删除索引：
DELETETAG学号
DELETETAGALL
数据完整性（数据库表）
1．实体完整性：
用主索引或候选索引来保证记录的唯一性，称为实体完整性。
2．域完整性：
通过定义字段数据类型、字段宽度和字段有效性规则等实现数据的域完整性。
例如：
性别
规则：
性别=”男”OR性别=”女”（性别$”男女”）
信息：
”性别必须是男或女”
默认值：
”男”
例如：
成绩
规则：
成绩>=60AND成绩<=100
信息：
”成绩在60－100之间”
默认值：
80
注意：
规则是逻辑表达式，信息是字符表达式，默认值与字段类型一致。
3．参照完整性：
父表和子表之间的相互制约规则称为参照完整性。
更新：
级联，限制，忽略
删除：
级联，限制，忽略
插入：
限制，忽略
级联：
限制：
忽略：
工作区：
最小工作区号是1，最大32767，0是未使用的工作区
第四章SQL语言（结构化查询语言STRUCTUREDQUERYLANGUAGE）
功能：
数据查询：
SELECT
数据操纵：
INSERT,UPDATE,DELETE
数据定义：
CREATE,ALTER,DROP
一、查询功能（核心SELECT）
基本结构：
SELECT结果FROM表WHERE条件
◆SELECT说明要查询的数据
◆FROM说明要查询的数据来自哪些表
◆WHERE说明查询条件
◆GROUPBY对结果进行分组
◆HAVING短语必须跟随GROUPBY使用，用来限定分组的条件
◆ORDERBY短语用来对查询的结果进行排序
仓库：
职工：
仓库号城市面积仓库号职工号工资
WH1北京370WH2E11220
WH2上海500WH1E31210
WH3广州200WH2E41250
WH4武汉400WH3E61230
WH1E71250
订购单：
供应商：
职工号供应商号订购单号订购日期供应商号供应商名地址
E3S7OR672001/06/23S3振华电子厂西安
E1S4OR732001/07/08S4华通电子公司北京
E7S4OR762001/05/25S6607厂郑州
E6NULLOR77NULLS7爱华电子厂北京
E3S4OR792001/06/13
E1NULLOR80NULL
E3NULLOR90NULL
E3S3OR912001/07/13
1．简单查询（一个表）
简单查询由SELECT和FROM短语构成（无条件查询）或由SELECT、FROM和WHERE短语构成（条件查询）。
结构：
SELECT结果FROM表WHERE条件
1查找城市：
SELECT城市FROM仓库
查找城市和面积：
SELECT城市，面积FROM仓库
查找所有信息：
SELECT*FROM仓库
该命令是从仓库中检索出全部数据。
其中“*”是通配符，表示所有字段。
查找工资：
SELECT工资FROM职工
清除重复记录：
SELECTDISTINCT工资FROM职工
②条件：
检索仓库WH2的所有工资
SELECT工资FROM职工WHERE仓库号=”WH2”
检索工资多于1230的职工信息
SELECT*FROM职工WHERE工资>1230（如果工资类型错误，则操作类型不匹配）
检索工资大于1220且工资小于1250的职工信息
SELECT*FROM职工WHERE工资>1220AND工资<1250
3与函数结合：
检索仓库号以W开头的仓库信息
SELECT*FROM仓库WHERELEFT（仓库号，1）=”W”
检索仓库号尾数为2的仓库信息
SELECT*FROM仓库WHERESUBSTR（仓库号，3，1）=”2”
检索在2001年的订购单信息
SELECT*FROM订购单WHEREYEAR（订购日期）=2001
4多个条件：
检索在WH1或WH2工作且工资小于1250的职工号
SELECT职工号FROM职工WHERE（仓库号=”WH1”OR仓库号=”WH2”）AND工资<1250
或者：
SELECT职工号FROM职工WHERE仓库号IN（“WH1”,”WH2”）AND工资<1250
2．连接查询（两个或多个表）
连接是关系的基本操作之一，连接查询是一种基于多个关系的查询。
结构：
SELECT结果FROM表1，表2WHERE表1.相同字段=表2.相同字段AND条件
1检索工资大于1230的职工所在的城市及工资
这里所要求检索的信息分别出自职工和仓库两个表，这样的检索肯定是基于多个关系的，这样的查询一般用连接查询来实现。
SELECT城市，工资FROM仓库，职工WHERE仓库.仓库号＝职工.仓库号AND工资>1230
SELECT仓库.仓库号，城市，工资FROM仓库，职工WHERE仓库.仓库号＝职工.仓库号AND
工资>1230
注意：
（1）这里，仓库.仓库号＝职工.仓库号是连接条件
（2）如果字段不唯一（两个表中有相同字段），一定要限定表名。
例如：
仓库.仓库号，“.”是从属关系，前面是表名，后面是字段名。
（3）换行用分号“；”续行
②查找工作在面积大于400的仓库的职工号和所在的城市
SELECT职工号，城市FROM仓库，职工WHERE仓库.仓库号＝职工.仓库号AND面积>400
③多个表：
SELECT*FROM仓库，职工，订购单WHERE仓库.仓库号＝职工.仓库号AND职工.职工号＝订购单.职工号AND订购单.供应商号＝供应商.供应商号AND工资>1200
3．嵌套查询
①一个表
如果查询所要求的结果出自一个关系，但相关的条件却涉及多个关系，则可以嵌套查询来完成。
结构：
SELECT结果FROM表WHERE字段=/>/<（SELECT字段FROM表WHERE条件）
查找工资大于平均工资的职工信息
SELECT*FROM职工WHERE工资>（SELECTAVG（工资）FROM职工）
查找和职工E4挣同样工资的职工
SELECT*FROM职工WHERE工资＝（SELECT工资FROM职工WHERE职工号＝”E4”）
②两个表
结构：
SELECT结果FROM表1WHERE相同字段IN（SELECT相同字段FROM表2WHERE条件）
哪些城市至少有一个仓库的职工的工资为1250元
连接：
SELECT城市FROM仓库，职工WHERE仓库.仓库号＝职工.仓库号AND工资＝1250
嵌套：
SELECT城市FROM仓库WHERE仓库号IN（SELECT仓库号FROM职工WHERE工资＝1250）
查找在上海工作的职工的工资
SELECT工资FROM职工WHERE仓库号IN（SELECT职工号FROM仓库WHERE城市＝”上海”）
查找没有职工的仓库信息（否定）
SELECT*FROM仓库WHERE仓库号NOTIN（SELECT仓库号FROM职工）
4．特殊运算符
①BETWEEN。
。
。
AND
检索工资在1220元到1240元的职工信息
这个查询的条件是值在什么范围之内，显然可以使用BETWEEN…AND
SELECT*FROM职工WHERE工资BETWEEN1220AND1250
等价于：
SELECT*FROM职工WHERE工资>=1220AND工资<=1250
否定：
SELECT*FROM职工WHERE工资NOTBETWEEN1220AND1250
等同于：
SELECT*FROM职工WHERE工资<1220OR工资>1250
②LIKE运算符通配符%代表0个或多个字符，_代表一个字符
查找职工号以E开头的信息
SELECT*FROM职工WHERE职工号LIKE“E%”
SELECT*FROM职工WHERE仓库号LIKE“%2”
检索全部公司的信息
SELECT*FROM供应商WHERE供应商名LIKE“%公司”
检索供应商名中带有“电“的信息
SELECT*FROM供应商WHERE供应商名LIKE“%电%”
③NOT
检索地址不是北京的供应商信息
SELECT*FROM供应商WHERE地址!
=”北京”
SELECT*FROM供应商WHERENOT地址=”北京”
5．排序ORDERBY
在SQL语句中使用ORDERBY短语对查询结果排序，并可以用ASC指出按升序排序（默认），用DESC指出按降序排序，可以按一列或多列排序。
单项排序：
SELECT*FROM仓库ORDERBY面积（默认升序）
SELECT*FROM职工ORDERBY工资DESC（降序）
多项排序：
SELECT*FROM职工ORDERBY工资，仓库号DESC（先按工资升序，再按仓库号降序）
SELECT*FROM职工ORDERBY仓库号DESC,工资，职工号
6．简单的计算查询
SQL不仅具有一般的检索能力，而且还可以直接对检索结果进行计算，比如检索职工的平均工资、检索某个仓库中职工的最高工资值等。
用于计算检索的函数有：
COUNT（）：
计数；SUM（）：
求和；AVG（）：
平均；MAX（）：
最大值；MIN（）：
最小值
求所有职工的平均工资
SELECTAVG（工资）FROM职工
虚字段的用法：
AS，在结果中会出现虚字段一项
SELECTAVG（工资）AS平均工资FROM职工
求所有职工的工资总和
SELECTSUM（工资）FROM职工
求WH2仓库的最高工资
SELECTMAX（工资）FROM职工WHERE仓库号=”WH2”
求WH1仓库的工资总和
SELECTSUM（工资）FROM职工WHERE仓库号=”WH1”
求职工表中的职工个数
SELECTCOUNT（职工号）FROM职工
求职工表中的仓库个数
SELECTCOUNT（DISTINCT仓库号）FROM职工
求所有元组个数
SELECTCOUNT（*）FROM职工
7.分组GROUPBY（每个、各组、按、以。
。
为单位）
利用GROUPBY子句进行分组计算查询使用得非常广泛。
可以按一列或多列分组，还可以用HAVING进一步限定分组的条件。
①求每个仓库的职工信息
SELECT*FROM职工GROUPBY仓库号
注意：
分组后各组取最后一条结果
②求每个仓库的职工的平均工资
SELECT仓库号，AVG（工资）FROM职工GROUPBY仓库号
注意：
按照什么字段分组，结果中要有分组的字段。
③GROUPBY子句与WHERE子句的先后顺序
SELECT*FROM职工WHERE仓库号=”WH1”GROUPBY仓库号
注意：
GROUPBY子句一般跟在WHERE子句之后，没有WHERE子句时跟在FROM之后。
在分组查询时，有时要求分组满足某个条件时才检索，这时可以用HAVING子句来限定分组。
④求至少有两个职工的每个仓库的平均工资。
（对分组进行限定用HAVING子句）
SELECT仓库号，AVG（工资）FROM职工GROUPBY仓库号HAVINGCOUNT（职工号）>=2
注意：
对分组进行进一步限定用HAVING子句，HAVING字句总是跟着GROUPBY子句之后，不可以单独使用。
HAVING子句和WHERE子句不矛盾，在查询中先用WHERE，再用GROUPBY,最后用HAVING
⑤分组GROUPBY与排序ORDERBY的顺序
SELECT*FROM职工GROUPBY仓库号ORDERBY工资DESC
注意：
先分组后排序
⑥虚字段的用法：
检索每个仓库中的平均工资，并且按平均工资降序排列
方法一：
SELECT仓库号，AVG（工资）FROM职工GROUPBY仓库号ORDERBY2
方法二：
SELECT仓库号，AVG（工资）AS平均工资FROM职工GROUPBY仓库号ORDERBY平均工资
注意：
函数不能用来直接排序、分组或比较，必须用虚字段，2表示结果中第二个字段，AS虚字段后查询结果中会出现平均工资这个字段，因此可以识别。
8.空值查询（NULL）
找出尚未确定供应商的订购单。
SELECT*FROM订购单WHERE供应商号ISNULL
注意：
查询空值时要使用ISNULL，而=NULL是无效的，因为空值不是一个确定的值，所以不能用=。
不是空值使用ISNOTNULL
SELECT*FROM订购单WHERE供应商号ISNOTNULL
9.别名查询
SELECT职工号，面积FROM职工Z，仓库CWHEREZ.仓库号=C.仓库号
10.量词和谓词的嵌套查询
量词：
ANY、SOME、ALL，其中ANY和SOME是同义词，在比较运算时只要子查询中有一行能使结果为真，则结果就为真；而ALL则要求子查询中的所有行都使结果为真时，结果才为真。
谓词：
EXISTS、NOTEXISTS是用来检查在子查询中是否有结果返回。
①检索有职工的工资大于或等于WH1仓库中任何一名职工的工资的仓库号。
这个查询可以用ANY或SOME量词
SELECT仓库号FROM职工WHERE工资>=ANY（SELECT工资FROM职工WHERE仓库号=”WH1”）
等价于：
SELECT仓库号FROM职工WHERE工资>=（SELECTMIN（工资）FROM职工WHERE仓库号=”WH1”）
②检索有职工的工资大于或等于WH1仓库中所有职工的工资的仓库号。
这个查询可以使用ALL量词
SELECT仓库号FROM职工WHERE工资>=ALL（SELECT工资FROM职工WHERE仓库号=”WH1”）
等价于：
SELECT仓库号FROM职工WHERE工资>=（SELECTMAX（工资）FROM职工WHERE仓库号=”WH1”）
③检索哪些仓库中还没有职工的仓库信息。
这里的查询是没有职工或不存在职工，所以可以使用谓词NOTEXISTS：
SELECT*FROM仓库WHERENOTEXISTS（SELECT*FROM职工WHERE仓库号=仓库.仓库号）
等价于：
SELECT*FROM仓库WHERE仓库号NOTIN（SELECT仓库号FROM职工）
④检索哪些仓库中至少已经有一个职工的仓库信息。
SELECT*FROM仓库WHEREEXISTS（SELECT*FROM职工WHERE仓库号=仓库.仓库号）
等价于：
SELECT*FROM仓库WHERE仓库号IN（SELECT仓库号FROM职工）
注意：
EXISTS和NOTEXISTS只是判断子查询中是否或有没有结果返回，它本身并没有任何运算或比较。
11.超连接查询
SQL还支持更广泛的关系连接运算，我们称之为超连接。
原来的连接是只有满足连接条件，相应的结果才会出现在结果表中；而超连接是首先保证一个表中满足条件的元组都出现在结果中，然后将满足条件的元组与另一个表的元组进行连接，不满足连接条件的则将用来自另一表的属性值置为空值。
INNERJOIN等价于JOIN，为内连接或普通连接。
LEFTJOIN为左连接，即第一个表中的记录不管是否满足连接条件将都在查询结果中出现，第二个表中不满足连接条件的记录对应为NULL。
RIGHJION为右连接，即第二个表中的记录不管是否满足连接条件将都在查询结果中出现，第一个表中不满足连接条件的记录对应为NULL。
FULLJOIN为全连接，即两个表中的记录不管是否满足连接条件都将出现在结果中，不满足连接条件的记录对应为NULL。
1普通连接，即只有满足连接条件的记录才出现在结果中。
SELECT*FROM仓库JOIN职工ON仓库.仓库号=职工.仓库号
等价于：
SELECT*FROM仓库INNERJOIN职工ON仓库.仓库号=职工.仓库号
SELECT*FROM仓库，职工WHERE仓库.仓库号=职工.仓库号
2左连接，即除了满足连接条件的记录出现在结果中外，第一个表中不满足连接条件的记录也出现在结果中。
SELECT*FROM仓库LEFTJOIN职工ON仓库.仓库号=职工.仓库号
③右连接，即除了满足连接条件的记录出现在结果中外，第二个表中不满足连接条件的记录也出现在结果中。
SELECT*FROM仓库RIGHTJOIN职工ON仓库.仓库号=职工.仓库号
④全连接，即除了满足连接条件的记录出现在结果中外，两个表中不满足连接条件的记录都出现在结果中。
SELECT*FROM仓库FULLJOIN职工ON仓库.仓库号=职工.仓库号
注意：
JOIN连接格式在连接多个表时的书写方法要特别注意。
在这种格式中，JOIN和ON的顺序是很重要的，特别要注意JOIN的顺序要和ON的顺序（相应的连接条件）正好相反。
例如下面是一个基于四个关系的连接查询。
SELECT*FROM供应商JOIN订购单JOIN职工JOIN仓库ON职工.仓库号=仓库.仓库号ON订购单.职工号=职工.职工号ON供应商.供应商号=订购单.供应商号
12.集合的并运算（UNION）
SQL支持集合的并运算，运算符UNION，即可以将两个SELECT语句的查询结果通过并运算合并成一个查询结果。
为了进行并运算，要求这样的两个查询结果具有相同的字段个数，并且对应字段的值要出自同一个值域（相同的数据类型和取值范围）。
例：
如下语句的结果是城市为北京和上海的仓库信息。
SELECT*FROM仓库WHERE城市=”北京”UNIONSELECT*FROM仓库WHERE城市=”上海”
13.显示部分结果
查询时可以不显示全部满足条件的记录，而只显示前几个记录，这时可以使用TOP短语，TOP短语要与ORDERBY短语同时使用才有效。
例：
显示工资最高的三位职工的信息。
即按降序排序，只显示前3条记录。
SELECT*TOP3FROM职工ORDERBY工资DESC
显示工资最低的30%的职工信息。
即按升序排序，只显示前30%的记录。
SELECT*TOP30%FROM职工ORDERBY工资
14
升级会员 