第 5 章 查询与视图

1. 第5章 查询与视图 辽东学院·信息技术学院 崔晓斐

2. 本章基本内容 • 5.1 SQL查询 • 5.2 创建查询 • 5.3 创建视图 信息技术学院 崔晓斐 制作

3. 5.1 SQL查询 查询种类： • 查找：locate for ， seek • 只对单个表查找符合条件的记录 • 显示：list/display for • 只对单个表显示符合条件的记录 • 例：对学生信息表，学生成绩表查询女生的学号，姓名，及各科成绩 • 有两种方法可以对多表进行查询 • 利用vf提供的查询设计器查询 • 利用SQL查询命令查询 信息技术学院 崔晓斐 制作

4. 5.1.1 SQL语言概述 SQL是结构化查询语言Structured Query Language的缩写。作为标准的关系数据库语言的地位更加巩固，大多数数据库供应商纷纷采用SQL作为其产品的检索语言。 SQL语言具有如下特点： （1）一体化。 （2）非过程化。 （3）简洁。 （4）语法结构统一。 （5）支持视图数据结构。 信息技术学院 崔晓斐 制作

5. SQL语言对基本表和视图进行查询和其他操作，基本表和视图都是关系SQL语言对基本表和视图进行查询和其他操作，基本表和视图都是关系 • 基本表是本身独立存在的表 • 视图是从一个或几个基本表导出的表，它本身不独立存储在数据库中，即数据库中只存放视图的定义而不存放视图对应的数据，这些数据仍存放在导出视图的基本表中，因此视图是一个虚表 • 视图在概念上与基本表等同，用户可以在视图上再定义视图 信息技术学院 崔晓斐 制作

6. 5.1.2 SQL的查询功能 SQL的查询命令也称作SELECT-SQL命令，用于从数据库中检索特定的记录，是SQL语言的核心。 SELECT命令的完整语法格式如下： SELECT [ALL | DISTINCT][TOP <数值表达式>[PERCENT]] [别名.] <SELECT表达式> [AS <列名>] FROM [FORCE][数据库名!]<表名> [<本地名>] [[INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] JOIN <数据库名>!]<表名>[<本地名>][ON <联接条件> …] [[INTO<目标>]| [TO FILE <文件名> [ADDITIVE] | TO PRINTER [PROMPT] | TO SCREEN]] [WHERE <联接条件> [AND <联接条件> ...][AND | OR <筛选条件> [AND | OR <联接条件>...]]] [GROUP BY <组表达式> [,<组表达式> ...]][ HAVING <筛选条件>] [ORDER BY <关键字表达式>[ASC | DESC] [,<关键字表达式> [ASC | DESC] ...]] 信息技术学院 崔晓斐 制作

7. 查询功能是SQL主要功能，可以同时查询多个表，使用Select命令查询查询功能是SQL主要功能，可以同时查询多个表，使用Select命令查询 • Select [All /Distinct] <字段名1>|<函数>[<字段名2>…] From <基本表名/视图名>[<,基本表名/视图名>]… [Where <条件表达式> ] [Group By <分组字段名>[Having <条件表达式>] ] [Order By <列名2> [Asc/Desc] ] [into table <表名>][to file <文本文件名>] • < >项是不可缺省的，[ ]项可根据需要省略 • All：查询结果是表的全部记录 • Distinct：查询结果是不包含重复行的记录集 • * ： 可以表示所有字段 信息技术学院 崔晓斐 制作

8. 简单查询 • 基于一个表的查询 • 由select和from构成的无条件查询 或 含有where的条件查询 • 例： • 查询“学生信息”表中所有学生信息 • 查询“学生信息”表中所有女生记录 • 查询“学生信息”表中所有住校的男生记录 Select * from 学生信息 Select * from 学生信息 where 性别=“女” Select * from 学生信息 where 性别=“男” and 住校否=.t. 信息技术学院 崔晓斐 制作

9. 查询“学生信息”表中的姓名、学号的记录 • 查询“学生成绩表”中所有学生的学号 • 查询“学生信息”表中1987年出生的学生的姓名、性别和是否住校 Select 学号,姓名 from 学生信息 Select distinct(学号) from 学生成绩 Select 姓名,性别,住校否 from 学生信息 where ； year(出生日期)=1987 信息技术学院 崔晓斐 制作

10. 联接查询 • 是一种基于多个关系的查询，必须有联接条件 • 查询学生的姓名，性别，各科成绩 • 查询国际贸易专业的李兰兰的计算机成绩 select 姓名,性别,大学英语,计算机,大学语文,政治 from 学生信息,学生成绩 where 学生信息.学号=学生成绩.学号 select 姓名,专业,计算机 from 学生信息,学生成绩 where学生信息.学号=学生成绩.学号 and 姓名=“李兰兰” and 专业=“国际贸易” 信息技术学院 崔晓斐 制作

11. 嵌套查询 • 是查询语句中还包含一个查询语句，特点：查询结果包含的字段在一个表中，而查询条件涉及到另一个表 • 子查询结果一般是一个集合，因此常用带有IN谓词的子查询 • 例：查询所有国际贸易专业的学生的学号和计算机成绩。 • 分步操作： • ① 确定“国际贸易”专业的学生的学号 • SELECT 学号 FROM 学生信息 WHERE 专业=“国际贸易” • ② 查询所有在“国际贸易”专业学习的学生学号和成绩。 • SELECT 学号,计算机 FROM 学生成绩 WHERE 学号 IN (“上面查询的结果”) 信息技术学院 崔晓斐 制作

12. 构造嵌套查询，SQL语句如下： SELECT 姓名,计算机 FROM 学生成绩 WHERE 学号 IN (SELECT 学号 FROM 学生信息 WHERE 专业='国际贸易') 例：查询所有不是国际贸易专业的学生的学号和计算机成绩。 SELECT 姓名,计算机 FROM 学生成绩 WHERE 学号 NOT IN (SELECT 学号 FROM 学生信息 WHERE 专业='国际贸易') 信息技术学院 崔晓斐 制作

13. 例：查询与陈琳所学专业相同的其他学生信息 • ①查询陈琳所学专业 • Select 专业 from 学生信息 where 姓名=“陈琳” • ②查询“国际贸易”专业的学生 • Select * from 学生信息 where 专业=“国际贸易” • ①+ ② • Select * from 学生信息 where 专业 IN （Select 专业 from 学生信息 where 姓名=“陈琳” ） 信息技术学院 崔晓斐 制作

14. 几个特殊运算符 • [NOT] IN • 查询姓名是徐和杨的学生 • SELECT * FROM 学生信息 WHERE 姓名 IN (“张”,“李”) • [NOT]BETWEEN….AND… • 查询计算机成绩在75-99之间的学生各科成绩 • SELECT * FROM 学生成绩 WHERE 计算机 BETWEEN 75 AND 99 • [NOT] LIKE “%/_匹配文字” • %代替多个字符，_代替一个字符 • 查询所有姓张的记录 • SELECT * FROM 学生信息 WHERE 姓名 LIKE “张%” 信息技术学院 崔晓斐 制作

15. 计算查询 • Count（*） --Avg （列名） • Count（列名） --Sum （列名） • Max （列名） --Min （列名） • 例：求”学生信息”表中男生总人数，并将字段名更名为“男生人数” • 统计”学生成绩”中的学生人数 • 统计“学生成绩”中各科平均成绩 select count (*) from 学生信息 where 性别="男" select count (distinct 学号) from 学生成绩 select avg(大学英语), avg(计算机), avg(大学语文), avg(政治) from 学生成绩 信息技术学院 崔晓斐 制作

16. 分组查询 GROUP BY <组表达式>子句 对记录按<组表达式>值分组，常用于分组统计。 HAVING子句 当含有GROUP BY子句时，HAVING子句表示对同组记录的筛选条件； • 排序 ORDER BY<表达式>子句 指定查询结果中记录按<表达式>排序，默认升序。<表达式>只能是字段，或表示查询结果中列位置的数字。选项ASC表示升序，DESC表示降序。 例如，按照计算机成绩升序显示学生的各科成绩： SELECT * FROM 学生成绩 ORDER BY 计算机 DESC 信息技术学院 崔晓斐 制作

17. Select 专业,count(学号) as 学生人数 from 学生信息 group by 专业 • 统计”学生信息”表中统计每个专业的学生人数 • 上题再按学生人数降序排序 • 统计哪些专业的学生人数在2人以上 Select 专业,count(学号) as 学生人数 from 学生信息 group by 专业 order by 学生人数 desc Select 专业 from 学生信息 group by 专业 having count(学号)>=2 信息技术学院 崔晓斐 制作

18. 查询输出去向 • 用于指定查询结果的输出去向，默认查询结果在浏览窗口中显示。 • 将查询结果存放到数组中 ：INTO ARRAY 数组名 • 将查询结果存放在临时文件：INTO CURSOR 临时表 • 将查询结果存放到永久表中：INTO DBF|TABLE 表名 • 将查询结果存放到文本文件中：TO FILE 文本文件名 • 将查询结果直接输出到打印机：TO PRINTER • 将查询结果直接输出到屏幕：TO SCREEN 信息技术学院 崔晓斐 制作

19. TOP子句显示部分结果 • TOP 数值 [percent] &&必须与order by命令联合使用 TOP子句必须与ORDER BY子句同时使用。<数值表达式>表示在符合条件的记录中选取的记录数，取值范围为1～32767，排序后并列的若干记录只计一个。含PERCENT选项时，<数值表达式>表示百分比，记录数为小数时自动取整，取值范围为0.01～99.99。 信息技术学院 崔晓斐 制作

20. 复 习 例： • 1.根据学生信息表建立SQL查询，查询每个学生的姓名、性别和出生日期，并按“性别”降序，将查询结果保存到表table1中。 • 2.建立一个SQL查询，查询政治成绩75分以上的学生的学号、姓名、性别和出生日期及各门课成绩，将查询结果保存到table2中。 信息技术学院 崔晓斐 制作

21. 5.1.3 SQL的定义功能 • 创建表（定义表结构） • Create Table <表名> （<字段名1> <数据类型> [（<宽度>[，<小数位数>]）] [，<字段名2 > <数据类型> [（<宽度>[，<小数位数>]）]…]） • 类型：字符型C，数值型N，整型I，浮点型F，日期型D，逻辑型L，备注型M，通用型G • 例1：创建一个新表“学生档案”，包含字段有学号C（10），姓名C（8），性别C（2），出生日期，专业C（20），并显示该表结构 Create Table 学生档案 ( 学号 C(10), 姓名 C(8), 性别 C(2), 出生日期 D, 专业 C(20)) 信息技术学院 崔晓斐 制作

22. 修改字段属性 • 用SQL语句修改表结构，无须用USE命令打开表 • Alter Table <表名> [Add | Alter [Cloumn] <字段名1>] 字段类型 ([字段宽度[,小数位]]) [Null | Not Null] [Drop [Column] <字段名2>] [Rename Column 字段名3 To 字段名4] • 例2：修改表“学生档案”的结构，将学号改为整型（4），姓名改为字符型（10） Alter Table 学生档案 Alter Column 学号 I(4) Alter 姓名 C(10) 信息技术学院 崔晓斐 制作

23. 删除字段 • 例3：在“学生档案”表中，删除“专业”字段 • 增加字段 • 例4：在“学生档案”表中，增加“班级”、“联系方式”两个字段 Alter Table 学生档案 Drop Column 专业 Alter Table 学生档案 Add 班级 C(16) Add 联系方式 C(12) 信息技术学院 崔晓斐 制作

24. 5.1.4 SQL记录操作功能 • 插入记录 • 标准格式：INSERT INTO 表名[(字段1 [,字段2,…])] VALUES (表达式1 [,表达式2,…]) • 例：在cj表中插入一条记录: (”010502”,”01”,77,88,99,66) • 特殊格式： 1. INSERT INTO 表名FROM ARRAY 数组名 2. INSERT INTO 表名FROM MEMVAR（与字段名同名的内存变量） Insert into cj (xsbh,xybh,gs,wy ,jsj,mz) values （”010502”, ”01”, 77,88,99,66） 信息技术学院 崔晓斐 制作

25. 例：在cj表中插入一条记录，数据来源于数组a（a（1）=”020502”, a（2）=”01”, a（3）=“”, a（4）=67, a（5）=68，a（6）=69） 例：在cj表中插入一条记录，数据来源于特定的一组变量（xsbh=”030503”,xybh=”02”,jsj=88,） Insert into cj from array a Insert into cj from memvar 信息技术学院 崔晓斐 制作

26. 删除记录 • DELETE FROM 表名[WHERE 条件] 例： 在cj表中删除刚刚新增的最后一条记录 • 更新记录 • UPDATE 表名 SET 字段名l = 表达式l [ ,字段名2=表达式2…] WHERE 条件 • 例：在cj表中将“01经济学院”学生的计算机成绩增加10分 Delete from cj where recno()=13 Update cj set jsj=jsj+10 where xybh=”01” 信息技术学院 崔晓斐 制作

27. 视图定义 • 格式：CREATE VIEW 视图名AS 查询语句 • 例：创建基于多个表的视图：基于jbqk表、cj表和xy表，建立含有学生编号、姓名、学院和各科成绩的视图 • 例：删除视图：从xs数据库中删除视图st1 Create view st2 as select jbqk.xsbh,xm,xymc,gs,wy,jsj,mz from jbqk,cj,xy where jbqk.xsbh=cj.xsbh and cj.xybh=xy.xybh Drop view st1 信息技术学院 崔晓斐 制作

28. 5.2 visual foxpro 查询 • 查询：是从指定的表或视图中提取满足条件的记录，将结果定向输出到.qpr文件中 • 查询中的数据是只读的，也就是说查询不能更新数据源的数据。查询可以文件的形式保存起来，以便以后再次使用。查询文件的扩展名为.QPR • 建立查询 • 查询设计器：文件/新建/查询－新建文件 • 查询向导：文件/新建/查询－向导 • 运行查询 • 查询/运行 • 命令：do aaa.qpr (扩展名不能省略) • 查询修改 • 文件/打开/选择查询文件 信息技术学院 崔晓斐 制作

29. 5.3 Visual FoxPro 视图 • 视图是从一个或多个数据库中导出的“表”，是一个虚拟表，视图中的数据仍存放在原来的基本表中。修改视图里的数据实际上是在修改基表中的数据；而修改了基表数据，由基表产生的视图也会自动改变。视图不能独立存在，只有打开与视图相关的数据库才能创建和使用视图。 • 视图的表示形式和数据特性与数据库表一样，具有与数据库表一样的操作方法。 • 通过视图可以查询表，也可以更新表 1、创建本地视图——首先打开数据库，再创建视图 • 视图向导：文件/新建/视图－向导 • 视图设计器：文件/新建/视图－新建文件：：打开视图设计器 信息技术学院 崔晓斐 制作

30. 2、视图与数据更新 • 视图是根据基本表派生出来的，所以把它叫做虚拟表，但在Visual FoxPro中它已经不是操作基本表的窗口。在一次打开数据库和关闭数据库之间的一个活动周期内，视图和基本表已经成为两张表，使用视图时会在两个工作区分别打开视图和基本表，默认对视图的更新不反映在基本表中，对基本表的更新在视图中也得不到反映。但是当关闭数据库后视图中的数据将消失，当再次打开数据库时视图从基本表中重新检索数据。所以默认情况下，视图在打开时从基本表中检索数据，然后构成一个独立的临时表供用户使用。 • 为了通过视图能够更新基本表中的数据，需要在视图设计器中选中“发送SQL更新”。 信息技术学院 崔晓斐 制作

31. 习 题 • 建立一个查询文件名为gzcx，查询gzda表中应发工资小于2000记录的教师号，基本工资，应发工资，并按应发工资降序排列。 • 用本地视图设计器创建学生信息和学生成绩两表的视图，其中包含的字段有“姓名，学生成绩.学号，大学英语，计算机，政治”5个字段，英语，计算机，政治为可更新字段。 • 利用视图设计器在数据库中建立视图new_view，视图包括rsda表的全部字段（顺序同rsda中的字段）和全部记录，可通过建立的视图更新rsda表的职称字段的值。 信息技术学院 崔晓斐 制作