VF二级培训讲义 (SQL查询、查询设计器、SQL数据表的操作、视图设计器).doc

VF二级培训讲义 (SQL查询、查询设计器、SQL数据表的操作、视图设计器).doc 重庆市普通高校专转本统一选拔考试 系数据库语言SQL 第三部分 关系数据库标准语言SQL 第三部分 关系数据库标准语言SQL 一、 SQL概述 1.1 SQL的概念:SQL(Structured Query Language)是结构化查询语言的简称，它引领着关系数据库的发展方 向，是未来关系数据库发展的标准。 1.2 SQL的特点: , 一体化 , 高度非过程化 , 非常简洁 , 直接以命令方式交互使用 1.3 SQL的主要命令: , 数据查询:SELECT , 数据定义:CREATE、DROP、ALTER , 数据操纵:INSERT、UPDATE、DELETE 二、 使用命令建立SQL查询 2.1查询的概念:查询是从指定的表或视图中提取满足条件的记录，然后按照想得到的输出类型定向输出查询 结果。 2.2 SQL查询语句格式: , SELECT : 要查询的数据(字段) , FROM :数据来源(要查询的数据来自哪个或哪些表) , WHERE :查询需要满足的条件 , GROUP BY :对查询的数据进行分组 , ORDER BY :对查询结果进行排序 2.3 SQL简单查询: 1.在当前数据库当前表中查询某一数据 基本格式:SELECT Select_Item FROM Table 其中:Select_Item :字段名列表 Table :数据表名称 例2.1:从职工关系中检索所有工资值。 FROM 职工 实现语句:SELECT 工资 2.在指定数据库指定表中查询某一数据 基本格式:SELECT [Alias.]Select_Item FROM [DatabaseName!]Table 例题2.2:从订货管理数据库中的职工数据表中检索所有工资值。 实现语句:SELECT 职工.工资 FROM 订货管理数据库!职工 3.在当前数据库当前表中查询所有数据 基本格式:SELECT * FROM Table 例题2.3:从当前数据库中当前数据表中检索所有数据。 实现语句:SELECT * FROM 职工 4.在当前数据库当前表中查询所有数据(不允许内容重复) 基本格式:SELECT [ALL | DISTINCT] Select_Item FROM Table 例题2.4:从当前数据库中当前数据表中检索所有不重复的工资数据。 ((( 实现语句:SELECT DISTINCT 工资 FROM 职工 重庆市普通高校专转本统一选拔考试 系数据库标准语言SQL 5.在当前数据库当前表中进行条件查询 基本格式:SELECT Select_Item FROM Table WHERE JoinCondition 例题2.5:在当前数据库当前表中，查询工资大于1210元职工的仓库号 Select 仓库号 from 职工 where 工资>1210 实现语句:SELECT 仓库号 FROM 职工 WHERE 工资>1210 例题2.6:查询在仓库“WH1”或“WH2”工作，并且工资少于1250元的职工工号 实现语句:SELECT 职工号 FROM 职工 WHERE 工资<1250 AND (仓库号=”WH1” OR 仓库号=”WH2”) 完整格式: SELECT [ALL|DISTINCT] [Alias.]Select_Item |* FROM [DatabaseName!]Table [WHERE JoinCondition] 2.4 SQL联接查询: 联接查询的含义:联接查询是一种基于多个关系的查询，即将多个数据表中数据构成一个查询集合，并在其中查询出符合要求的记录。 例题2.7:找出工资多于1230元的职工号和他们所在的城市。 实现语句:SELECT 职工号,城市 FROM 职工,仓库 WHERE 工资>1230 AND (职工.仓库号=仓库.仓库号) 特别注意: , 职工.仓库号=仓库.仓库号 是连接条件 , 如果在FROM之后有两个关系，则这两个关系之间肯定有一种联系，否则无法构成检索表达式 例题2.8:找出工作在面积大于400的仓库的职工号以及这些职工工作所在的城市。 实现语句:SELECT 职工号,城市 FROM 职工,仓库 WHERE 面积>400 AND (职工.仓库号=仓库.仓库号) 2.5 SQL嵌套查询: 例题2.9:哪些城市至少有一个仓库的职工工资为1250元, 实现语句:SELECT 城市 FROM 仓库 WHERE 仓库号 in (SELECT 仓库号 FROM 职工 WHERE 工资=1250) 例题2.10:查询所有职工的工资都多于1210元的仓库信息。 ((( 实现语句:SELECT * FROM 仓库 WHERE 仓库号 NOT IN (SELECT 仓库号 FROM 职工 WHERE 工资<=1210) 例题2.11:找出和职工E4挣同样工资的所有职工。 实现语句:SELECT 职工号 FROM 职工 WHERE 工资=(SELECT 工资 FROM 职工 WHERE 职工号=”E4”) 2.6 SQL几个特殊运算符: , BETWEEN …AND :…和…之间 , LIKE:字符串匹配运算符 例题2.12:检索出工资在1220元到1240元范围内的职工信息。 实现语句:SELECT * FROM 职工 WHERE 工资 BETWEEN 1220 AND 1240 例题2.13:从供应商关系中检索出全部公司的信息，不要工厂或其他供应商的信息。 实现语句:SELECT * FROM 供应商 WHERE 供应商名 LIKE “%公司” 通配符: , % :表示任意多个字符(含0个) , _ :表示一个字符 2.7 SQL排序: 基本格式:ORDER BY Order_Iten [ASC|DESC] [,Order_Iten [ASC|DESC]…] 例题2.14:按职工的工资值升序检索出全部职工信息。 重庆市普通高校专转本统一选拔考试 系数据库标准语言SQL 实现语句:SELECT * FROM 职工 ORDER BY 工资 注意:默认为升序 例题2.15:先按仓库号排序，再按工资排序并输出全部职工信息。 实现语句:SELECT * FROM 职工 ORDER BY 仓库号,工资 2.8 SQL计算查询: 1.用于检索的函数: COUNT()—计数 SUM()—求和 AVG()—计算平均值 MAX()—求最大值 MIN()—求最小值 2.例子: 例题2.16:找出供应商所在地的数目。 实现语句:SELECT COUNT(DISTINCT 地址) FROM 供应商 例题2.17:求出供应商数据表中记录数目。 实现语句:SELECT COUNT(*) FROM 供应商 例题2.18:求支付的工资总数。 实现语句:SELECT SUM(工资) FROM 职工 例题2.19:求北京和上海的仓库职工的工资总和。 实现语句:SELECT SUM(工资) FROM 职工 WHERE 仓库号 IN (SELECT 仓库号 FROM 仓库 WHERE 城市=” ” OR 城市=”上海”) 北京 例题2.20:求所有职工的工资都多于1210元的仓库的平均面积。 实现语句:SELECT AVG(面积) FROM 仓库 WHERE 仓库号 NOT IN (SELECT 仓库号 FROM 职工 WHERE 工 资<=1210) 例题2.21:求在WH2仓库工作的职工的最高工资值。 实现语句:SELECT MAX(工资) FROM 职工 WHERE 仓库号=”WH2” 2.9 SQL分组与计算查询: 基本格式:GROUP BY GroupColumn [,GroupColumn …] 例题2.22:求每个仓库的职工的平均工资。 实现语句:SELECT 仓库号,AVG(工资) FROM 职工 GROUP BY 仓库号 2.10 SQL的几个特殊选项: 1. 将查询结果存放到数组中: 语句格式:INTO ARRAY ArrayName 例题:将查询到的职工放到数组TEMP中。 SELECT * FROM 职工 INTO ARRAY temp 2. 将查询结果存放到临时文件中 语句格式:INTO CURSOR CursorName 临时文件是一只读的DBF文件，可以像一般的DBF文件一样使用，但只读。当关闭该文件时该文件会自 动删除。 例题:将查询到的职工放到DBF文件TEMP中。 SELECT * FROM 职工 INTO CURSOR temp 3. 将查询结果存放到永久表中 语句格式:INTO DBF|TABLE TableName 例题:将查询到的职工放到DBF文件TEMP中。 SELECT * FROM 职工 INTO DBF temp 重庆市普通高校专转本统一选拔考试 系数据库标准语言SQL 4. 将查询结果存放到文本文件中 语句格式:TO FILE FileName 例题:将查询到的职工放到文本文件TEMP中。 SELECT * FROM 职工 TO FILE temp 5. 将查询结果直接输出到打印机中 [PROMPT] 语句格式:TO PRINT 例题:SELECT * FROM 职工 TO PRINT 三、 利用查询设计器查询 3.1 查询设计器: 1.建立查询的方法: , 使用“项目管理器”建立查询; , 使用“新建”对话框建立查询; , 使用命令CREATE QUERY命令建立查询; , 通过直接编辑.qpr文件建立。 2.查询设计器: , 字段:选择需要显示的字段; , 联接:编辑联接条件; , 筛选:指定查询条件; , 排序依据:用于指定排序字段; , 分组依据:用于分组; , 杂项:指定是否要重复记录及列在前面的记录，与DISTINCT及TOP对应。 3.2 查询设计器的局限性:利用查询设计器不能够设计出所有的查询，如:嵌套查询、查询去向到数组等。 3.3 运行查询: 1.查询运行方法: , 使用“项目管理器”运行; , 使用命令运行: 格式:DO QueryFile 注意:使用命令运行查询时，必须给出查询文件的扩展名qpr，如:DO abc.qpr 如果不给出扩展名(DO abc)，VFP将把命令解释为 DO abc.prg，即执行程序文件。 2(查询去向: , 浏览:在“浏览(BROWSE)”窗口显示; , 临时表:命名的临时文件; , 表:命名表; , 图形:统计图; , 屏幕:屏幕直接输出(类似LIST); , 报表:报表文件.frx , 标签:标签文件.lbx 四、 数据表记录的SQL操作: 4.1 插入: 标准格式:INSERT INTO dbf_name[(fname1[,fname2,…])] VALUES(eExoression1[,eExoression2,…]) 重庆市普通高校专转本统一选拔考试 系数据库标准语言SQL VFP特殊格式:INSERT INTO dbf_name FROM ARRAY ArrayName|FROM MEMVAR 例题4.1:往订购单关系中插入元组(”E7”,”S4”,”OR01”,2001/05/25)。 实现语句:INSERT INTO 订购单 VALUE(”E7”,”S4”,”OR01”,{^2001-05-25}) 例题4.2:往订购单关系中插入元组(”E7”,”OR01”)。 实现语句:INSERT INTO 订购单(职工号,订购单号) VALUE(”E7”,”OR01”) 注意: , 用INSERT命令在数据表中插入数据时，该数据表事先可以不必打开; , 当数据表设置了“主索引”或“候选索引”时，不能用以前的INSERT与APPEND来添加记录，只能使 用INSERT INTO添加。(实际有出入) 4.2 更新: 格式: UPDATE TableName SET Column_Name1=eExoression1[,Column_Name2=eExoression2…] [WHERE Condition] 例题3.4:给WH1仓库的职工提高10%的工资。 实现语句:UPDATE 职工SET 工资=工资+工资*0.1 WHERE 仓库号=”WH1” 等价语句:replace工资 with工资*1.1 for仓库号=”WH1” 4.3 删除: 格式:DELETE FROM TableName [WHERE Condition] 例题3.5:删除仓库关系中仓库号值是WH2的元组。 实现语句:DELETE FROM 仓库 WHERE 仓库号=”WH2” 注意:在VFP中，SQL DELETE命令同样是逻辑删除，如果要物理删除记录同样需要使用PACK命令。 五、 数据表结构的SQL操作: 5.1 表定义: 1.建立数据表简单操作 基本格式:CREATE TABLE|DBF TableName [Name LongTableName] | [FREE] (FieldName1 FieldType[(nFieldWidth[,nPrecision])]…) 其中:TableName:新建数据表的表名称 FieldName :字段名 FieldType :用字母表示的字段类型 NFieldWidth:字段总宽度 NPrecision:小数位数 FREE:建立自由表 例题5.1:用SQL命令建立“订货管理1”数据库，然后建立“仓库1”数据表。 实现语句:CREATE DATABASE 订货管理1 CREATE TABLE 仓库1 (仓库号 C(5),城市 C(10),面积 I) 2.数据表字段有效性设置方法 ((( 基本格式:CREATE TABLE|DBF TableName1 [Name LongTableName] [FREE] (FieldName1 FieldType[(nFieldWidth[,nPrecision])] [CHECK lExpression [ERROR cMessageText1]] …) 重庆市普通高校专转本统一选拔考试 系数据库标准语言SQL 例题5.2:用SQL命令建立“订货管理1”数据库，然后建立“仓库1”数据表，并定义面积字段的有效 性规则为[面积必须大于0]。 实现语句:CREATE DATABASE 订货管理1 CREATE TABLE 仓库1 (仓库号 C(5),城市 C(10),面积 I ; CHECK(面积>0) ERROR “面积必须大于0”) 数据表字段默认值设置方法 3.((( 基本格式:CREATE TABLE|DBF [] [FREE] TableName1Name LongTableName (FieldName1 FieldType[(nFieldWidth[,nPrecision])] [DEFAULT eExpression] …) 例题5.3:建立数据表“仓库1”，并将“仓库1”数据表的“面积”字段的默认值设置为200。 实现语句:CREATE TABLE 仓库1(仓库号 C(5),城市 C(10),面积 I DEFAULT 200) 4.数据表字段主关键字或候选索引设置方法 ((((((((( 基本格式:CREATE TABLE|DBF TableName1 [Name LongTableName] [FREE] (FieldName1 FieldType[(nFieldWidth[,nPrecision])] [PRIMARY KEY | UNIQUE] …) 注意:UNIQUE在此处是候选索引 例题5.4:建立数据表“仓库1”，并将“仓库号”字段设置为主关键字。 实现语句:CREATE TABLE 仓库1(仓库号 C(5) PRIMARY KEY,城市 C(10),面积 I) 5.2 表删除: 基本格式:DROP TABLE | DBF TableName 例题5.5:删除数据表“仓库1”。 实现语句:DROP TABLE 仓库1 5.3 表结构修改: 1.添加新字段、修改已有字段并设置字段级相关规则: ((( 基本格式: ALTER TABLE TableName ADD | ALTER [COLUMN] FieldName FieldType[(nFieldWidth[,nPrecision])] [NULL | NOT NULL] &&字段是否允许为空 [CHECK [ERROR ]] lExpression cMessageText&&设置字段的有效性规则 [DEFAULT eExpression] &&设置字段的有效性规则 [PRIMARY KEY | UNIQUE] &&设置字段索引类型 例题5.6:为订购单1表增加一个货币类型的总金额字段。 实现语句:ALTER TABLE 订购单1 ADD 总金额 Y CHECK 总金额>0 ERROR “总金额应该大于0” 例题5.7:将订购单1表的订购单号字段的宽度由原来的5改为6。 实现语句:ALTER TABLE 订购单1 ALTER 订购单号 C(6) 2.定义、修改、删除字段级有效性规则和默认值定义: ((( 基本格式: 重庆市普通高校专转本统一选拔考试 系数据库标准语言SQL ALTER TABLE TableName ALTER [COLUMN] FieldName NULL | NOT NULL &&字段是否允许为空 ALTER TABLE TableName ALTER [COLUMN] FieldName SET CHECK lExpression [ERROR cMessageText] &&设置字段的有效性规则 ALTER TABLE TableName ALTER [COLUMN] FieldName DROP CHECK &&删除字段的有效性规则 ALTER TABLE TableName ALTER [COLUMN] FieldName SET DEFAULT eExpression &&设置字段的默认值 ALTER TABLE TableName ALTER [COLUMN] FieldName DROP DEFAULT &&删除字段的默认值 例题5.8:修改(定义)总金额字段的有效性规则。 实现语句:ALTER TABLE 订购单1 ALTER 总金额 SET CHECK 总金额>100 ERROR “总金额应该大于100” 例题5.9:删除总金额字段的有效性规则。 实现语句:ALTER TABLE 订购单1 ALTER 总金额 DROP CHECK 3.删除字段、修改字段名，定义、修改、删除表一级的有效性规则: ((( 基本格式: ALTER TABLE TableName DROP [COLUMN] FieldName &&删除字段 ALTER TABLE TableName RENAME [COLUMN] FieldName1 TO FieldName2 &&修改字段名 ALTER TABLE TableName SET CHECK lExpression [ERROR cMessageText] &&设置表的有效性规则 ALTER TABLE TableName DROP CHECK &&删除表的有效性规则 例题5.10:将订购单1表的总金额字段名改为金额。 实现语句:ALTER TABLE 订购单1 RENAME 总金额 TO金额 例题5.11:删除订购单1金额字段。 实现语句:ALTER TABLE 订购单1 DROP 金额 六、 视图: 6.1 视图的命令操作: 1.视图的定义格式: 格式:CREATE VIEW ViewName AS SelectStatement 其中:SelectStatement :查询语句，用于限定视图中的数据 2.从单个表派生出视图:构成视图的内容来源于单个表。 例题6.1:利用“职工”数据表，建立包含职工号与仓库号的视图。 实现语句:CREATE VIEW ew AS SELECT 职工号,仓库号 FROM 职工 注:视图一旦定义，就可以和基本表一样进行各种查询与修改。 3.从多个表派生出视图: 例题6.2:利用“职工”和“仓库”数据表，建立包含职工号、仓库号、城市、面积、工资的视图。 实现语句:CREATE VIEW ew AS; SELECT 职工号,职工.仓库号,城市,面积,工资 FROM 职工,仓库 WHERE 仓库.仓库号=职工.仓库号 注:视图一旦定义，就可以和基本表一样进行各种查询与修改。 4.删除视图: 基本格式:DROP VIEW ViewName 例题6.3:删除视图ew。 实现语句:DROP VIEW dw 重庆市普通高校专转本统一选拔考试 系数据库标准语言SQL 5.关于视图的说明: ,视图可以更新; ,视图不含真正的数据，仅是原表的一个窗口; ,在视图中可以进行查询操作，但视图的插入、更新操作有一定的限制; ,如果一个视图来源于单个表，则可以进行插入与更新操作，不能进行删除操作; ,如果一个视图来源于多个表，则插入、更新与删除操作都不能进行。 ,可以从一个表或多个相关联的表中提取有用信息; ,可以用来更新表，并将结果永久保存在磁盘上; ,只有在包含视图的数据库打开时，才能使用视图。 6.2 建立视图: 1(建立视图的方法: , 使用“项目管理器”建立视图; , 使用“新建”对话框建立视图; , 使用命令CREATE VIEW命令打开视图设计器建立视图; , 通过直接使用命令CREATE VIEW … AS … 建立。 2.视图设计器与查询设计器的区别: ,视图建立后直接保存在数据库中，在磁盘上没有相应文件，查询要形成.qpr文件; ,视图可以更新，查询不能更新(除非重新查询); ,查询有“查询去向”，视图没有。 视图的分类: 6.3 ,本地视图:使用当前数据库中的表建立的视图。 ,远程视图:使用非当前数据库中的表建立的视图。