VFP 课件 第4章 查询与视图.doc

第4章 查询与视图5.1.1 SQL概述：发展、特点SQL（Structured Query Languang,结构化查询语言）语言是集数据定义、数据查询、数据操纵和数据控制功能于一体的语言，1986年被美国国家标准局批准成为关系型数据库语言的标准。它只能用于对数据库中数据的操作。SQL语句最主要的功能就是查询功能。使用时无须打开表，只需将要连接的表、查询所需的字段、筛选记录的条件、记录分组的依据、排序的方式以及查询结果的显示方式，写在一条SQL语句中，就可以完成特定的工作。特点：(1)建立查询之前，源表处于开/关皆可；(2)查询命令运行之后：所用到的所有源表处于开状态，但活动工作区中是结果表；(3)建议：不用时关闭所有工作区。5.1.2 SELECT_SQL命令格式SQL语句创建查询使用的是Select语句。语句结构：P127Select ALL|DISTINCT From 数据源 Where 查询条件 Group by 分组关键字 Having 组提取条件 Order by 排序关键字ASC/DESC To file文件名|into 目标|to printer|to screen目标：into dbf into array into cursor 一、 单表查询。1、select 范围from (1)查询学生的全部信息。 select * from 学生信息(2)、查询每个学生的学号与姓名。 select 学号,姓名 from 学生信息(3)、显示表中所有专业select 专业 from 学生信息； select distinct 专业 from 学生信息 2、select as 列名 from (4)查询学生的姓名及出生年份，要求显示姓名和出生年份两个字段。select 姓名,year(出生日期) as 出生年份 from 学生信息(5)查询所有学生的平均身高,要求只显示平均身高一个字段. select avg(身高) as 平均身高 from 学生信息(6)查询并显示每个学生总分及学号、姓名字段select 学号,英语+计算机+大学语文+政治 as 总分,姓名 from 学生成绩二、 带筛选条件Where的单表查询。(Where 查询条件) 1、SELECT ALL | DISTINCT FROM ; WHERE 说明：由一系列用AND 或 OR 连接的条件表达式组成。(1)检索计算机成绩高于80分的所有记录。SELECT * FROM 学生成绩 WHERE 计算机80用WHERE短语指定了查询条件，查询条件可以是任意复杂的逻辑表达式。(2)检索计算机成绩高于80分而且英语成绩及格的所有记录。 SELECT * FROM 学生成绩 WHERE 计算机80 and 英语=60 (3)查询考试成绩不及格的学生的学号.(SC)select 学号 from 学生成绩 where 计算机=3五、查询结果的去向。(To file文件名|into 目标|to printer|to screen)1 查询学生的姓名、身高，按身高降序排列，结果保存到文本文件xs1中。SELECT 姓名,身高 FROM 学生信息 ORDER BY 身高 desc to file xs12 将上一题的结果保存到表文件xs2中。SELECT 姓名,身高 FROM 学生信息 ORDER BY 身高 desc into dbf xs23 将上一题的结果保存到数组R1中。SELECT 姓名,身高 FROM 学生信息 ORDER BY 身高 desc into ARRAY r1list memory like r1六、多表查询。(数据源部分)形式一(旧版本)：Select 输出项列表as 列名From 表1，表2Where 联接条件表达式 And 联接条件表达式使用多表查询时，表之间用“联接条件表达式”进行联接，联接条件的常用形式是“表1.字段名=表2.字段名”“别名.字段名=别名.字段名”1.查询学生总分情况,要求最后显示学号,姓名, 总分三个字段.SELECT SC.学号,姓名, 总分; FROM xsxx,xscj WHERE xsxx.学号=xscj.学号注意:若查询的某一列在多个表中出现,必须加表名限定.2. 检索计算机成绩和英语成绩都及格的所有记录，最后显示学号，姓名和计算机SELECT 学生信息.学号，姓名，计算机 FROM 学生成绩, 学生信息； WHERE 计算机=60 and 英语=60 And学生成绩.学号=学生信息.学号VFP6.0中可以使用下面的新查询格式进入多表查询:形式二(新版本)：Select 输出项列表AS 列名From 表1 联接类型 Join表2 On 联接条件表达式上面两题用新查询格式应是：1.Select xsxx.学号,xsxx.姓名, 总分;From xsxx join xscj;On xsxx.学号= xscj.学号2. Select 学生成绩.学号,学生成绩.姓名,计算机;From 学生成绩 join 学生信息 on 学生成绩.学号=学生信息.学号; WHERE 计算机=60 and 英语=60其中的“联接类型”有:Inner:内联接(为默认联接, NNER JOIN等价于JOIN，为普通联接。只显示表1与表2中相匹配的记录).Left:左联接,即使表2中没有相匹配的记录也将显示表1中的全部记录.Right:右联接, 即使表1中没有相匹配的记录也将显示表2中的全部记录.Full:全联接, 即两个表的记录不管是否满足联接条件都在目标表或查询表中出现。xs信息xs成绩Inner joinLeft join Right joinFull joinselect xs信息.学号,xs信息.姓名,英语 from xs信息 left join xs成绩 on xs信息.学号=xs成绩.学号七、嵌套查询 1查询英语成绩及格的学生的身高SELECT 身高 FROM xsxx ;WHERE 学号 in (sele 学号 from xscj where 英语=60)这个命令中含有两个SELECT-FROM-WHERE查询块，即内层查询块和外层查询块，这里IN相当于集合运算符。 2查询英语成绩都不及格的学生的身高(哪一个是正确的？)SELECT 身高 FROM xsxx ;WHERE 学号 not in (sele 学号 from xscj where 英语=60)SELECT 身高 FROM xsxx ;WHERE 学号 not in (sele 学号 from xscj where 英语=60); and 学号 in (sele 学号 from xscj) 其他：1.统计不重复记录统计“学生信息”表中专业数select count(distinct 专业) from 学生信息例题分析:1.已知考生文件夹中有数据库STSC，其中有数据库表STUD，SCORE和COURSE，三个表的结构如下所示： STUD（学号，姓名，性别，院系，出生日期） SCORE（学号，课程编号，成绩） COURSE（课程编号，课程名称，开课院系，开课学期）要求：首先在考生文件夹下编写一个程序PROG1。PRG，该程序的功能是检索每个学生的学号，姓名，平均成绩，选课门数和院系，并按平均成绩降序排列，同时将结果存放到新建立的表TEST1（学号C（8），姓名C（8），平均成绩N （16，2），院系C（6）中。答案:SELECT STUD.学号,STUD.姓名,AVG(成绩) AS 平均成绩,院系 FROM STUD,SCORE,COURSE ;WHERE STUD.学号=SCORE.学号 AND SCORE.课程编号=COURSE.课程编号;GROUP BY STUD.学号;ORDER BY 平均成绩 DESC ;INTO DBF TEST12. 在考生文件夹下有税务局收集的某商场一年的销售记录表”价格.DBF”和”销售.DBF”,表结构如下: 价格(货号,进价,售价)销售(货号,销售日期,销售数量)要求:首先在考生文件夹下编写一个程序PROG1.PRG,该程序的功能是计算每月应交税款,并将结果按月份排序后存放到新建立的”税务.DBF”表中.注:商场的税率为利润的17% 税务表结构:税务(月份N(3),税款N(20,4)答案:SELECT MONTH(销售日期) AS 月份,SUM(售价-进价)*销售数量*0.17) AS 税款 FROM 价格,销售WHERE 价格.货号=销售.货号;GROUP BY 月份 ORDER BY 月份 INTO DBF 税务常用SQL命令:1、 表结构的定义：CREATE TABLE|DBF ；( （字段宽度）NULL|NOT NULL)定义XS.DBF的表结构：包含字段和字段类型如下：学号C(6), 姓名 C(8), 出生日期 D, 入学成绩 N(5,1), 简历 MCREAT DBF XS(学号 C(6),姓名 C(8),出生日期 D,入学成绩 N(5,1),简历 M)CREAT TABLE XS(学号 C(6),姓名 C(8),出生日期 D,入学成绩 N(5,1),简历 M)2表结构的修改ALTER TABLE ADD|ALTER ; （字段宽度）NULL|NOT NULL 增加一个字段 ALTER TABLE XS ADD 住校否 L 修改字段名(重命名字段)ALTER TABLE XS RENAME 住校否 TO 是否住校修改字段宽度ALTER TABLE XS ALTER 姓名 C(10)删除字段ALTER TABLE XS DROP COLUMN 是否住校3.给表中的记录加删除标记(缺少条件默认修改全部记录)DELETE FROM DZ WHERE 性别=女DELETE FROM DZ 后面可以使用RECALL 恢复或者使用PACK 彻底删除带有删除标记的记录4.修改记录命令UPDATE SET =，=. ；WHERE UPDATE DZ SET 姓名=张学友 WHERE 姓名=李立 (缺少条件默认修改全部记录)相当于VFP的语句:REPLACE 姓名 WITH 张学友 FOR 姓名=李立例:给XSC