vf课件第4章查询与视图

1、第第4章章 查询与视图查询与视图上篇上篇 语言基础语言基础主要内容：主要内容：lSQL语言的特点和功能语言的特点和功能lSQL-SELECT命令的语法和功能命令的语法和功能l查询设计器进行查询的过程查询设计器进行查询的过程l视图设计器进行查询的过程视图设计器进行查询的过程本章要求：本章要求：l了解了解SQL语言的特点和功能语言的特点和功能l掌握掌握SELECT语句的语法和功能语句的语法和功能l了解查询和视图的异同了解查询和视图的异同l了解查询设计器和视图设计器进行查询的过程了解查询设计器和视图设计器进行查询的过程4.1 SQL语言概述语言概述4.1.1 SQL 语言的特点语言的特点lSQL(

2、(Structured Query Language) )结构化查询语言，是关系数据库管理系统的一种标准查结构化查询语言，是关系数据库管理系统的一种标准查询语言，由于功能强大、命令简洁，被广泛应用于各大询语言，由于功能强大、命令简洁，被广泛应用于各大数据库类管理软件。数据库类管理软件。l数据库中的数据很多时侯都是为了查询，因此数据查数据库中的数据很多时侯都是为了查询，因此数据查询是数据库的核心操作。而在询是数据库的核心操作。而在SQL语言中，只有一条查语言中，只有一条查询命令，即询命令，即SELECT语句。语句。 SQL是一种高度非过程化的语言。是一种高度非过程化的语言。 SQL是一种一体化的

3、语言。是一种一体化的语言。 SQL语言简洁，易学易用。语言简洁，易学易用。 SQL是面向集合的操作方式。是面向集合的操作方式。 SQL以同一种语法结构提供两种使用方式。以同一种语法结构提供两种使用方式。4.1.1 SQL语言的特点语言的特点 数据定义数据定义DDL( (Data Definition Language) ) CREATE、ALTER、DROP 数据操纵数据操纵DML( (Data Manipulation Language) ) INSERT、DELETE、UPDATE 数据控制数据控制DCL( (Data Control Language) ) GRANT、REVOKE 数据

4、查询数据查询DQL( (Data Query Language) ) SELECT 4.1.2 SQL语言的功能语言的功能4.2 SELECT- SQL查询查询4.2.1 SELECT语句的语法语句的语法SELECT ALL | DISTINCT & 方式方式. AS & 显示显示FROM & 相关库相关库INTO | TO FILE & 查询去向查询去向WHERE | & 关联筛选关联筛选GROUP BY , & 分组统计分组统计HAVING & 筛选筛选ORDER BYASC|DESC, ASC|DESC & 排序排序格式：格

5、式：SELECT ALL|DISTINCT AS 列名列名 FROM 说明：说明：l ALL：表示显示全部查询记录，包括重复记录。表示显示全部查询记录，包括重复记录。l DISTINCT：表示显示无重复结果的记录。表示显示无重复结果的记录。例例4.1 查询所有学生信息。查询所有学生信息。 SELECT * FROM 学生基本资料表学生基本资料表 说明：命令中的说明：命令中的*表示输出所有的字段。表示输出所有的字段。例例4.2 列出所有学生姓名，去掉重名。列出所有学生姓名，去掉重名。 SELECT DISTINCT 姓名姓名 FROM 学生基本资料表学生基本资料表4.2.2 单表查询语句单表查询

6、语句格式：格式：SELECT ALL|DISTINCT AS FROM WHERE 功能：功能：从一个表中查询满足条件的数据。从一个表中查询满足条件的数据。4.2.3 条件查询条件查询运算符运算符含义含义举例举例=、=、=、!=、比较大小比较大小 成绩成绩=80AND、OR、NOT多重条件多重条件 成绩成绩85 AND 成绩成绩85 多重条件查询多重条件查询例例4.5 查询选修查询选修004课程且成绩大于课程且成绩大于90分或成绩小于分或成绩小于85分分的学生成绩信息。的学生成绩信息。 SELECT * FROM 成绩表成绩表; WHERE 课程号课程号= 004 AND( (成绩成绩90 O

7、R 成绩成绩85) )例例4.6 查询学生基本资料表中土木工程专业或会计学专业查询学生基本资料表中土木工程专业或会计学专业学生的学号、姓名、性别和年龄信息。学生的学号、姓名、性别和年龄信息。 SELECT 学号学号, ,姓名姓名, ,性别性别, ,; YEAR( (DATE()()-YEAR( (出生日期出生日期) ) AS 年龄年龄; FROM 学生基本资料表学生基本资料表; WHERE 专业专业= 土木工程土木工程 OR 专业专业= 会计学会计学 指定范围查询指定范围查询格式：格式：NOT BETWEEN AND 说明：说明：的值必须小于的值必须小于的值。的值。例例4.7 查询成绩大于等于

8、查询成绩大于等于85分且成绩小于等于分且成绩小于等于90分的学生分的学生的学号和课程号信息。的学号和课程号信息。 SELECT 学号学号,课程号课程号 FROM 成绩表成绩表; WHERE 成绩成绩 BETWEEN 85 AND 90等价于等价于 SELECT 学号学号,课程号课程号 FROM 成绩表成绩表; WHERE 成绩成绩=85 AND 成绩成绩=90 确定集合查询确定集合查询说明：说明：IN操作查询字段值属于某指定集合的记录，操作查询字段值属于某指定集合的记录，NOTIN操作查询字段值不属于某指定集合的记录。操作查询字段值不属于某指定集合的记录。例例4.8 在成绩表中检索课程号是在成

9、绩表中检索课程号是001、002和和006的学生成的学生成绩信息。绩信息。 SELECT * FROM 成绩表成绩表; WHERE 课程号课程号 IN (001 , 002 , 006)等价于等价于 SELECT * FROM 成绩表成绩表; WHERE 课程号课程号= 001 OR 课程号课程号= 002 ; OR 课程号课程号= 006 部分匹配查询部分匹配查询格式：格式： NOT LIKE 说明：说明：字段类型必须是字符型，字符串常量的字符可包括如字段类型必须是字符型，字符串常量的字符可包括如下两个符号：下两个符号：%表示表示0个或多个字符，个或多个字符，_表示任意一个字符。表示任意一个

10、字符。注意：注意：在在VFP系统中，一个汉字用一个字符系统中，一个汉字用一个字符“_”表示。表示。例例4.9 在学生基本资料表中检索姓张的学生信息。在学生基本资料表中检索姓张的学生信息。 SELECT * FROM 学生基本资料表学生基本资料表; WHERE 姓名姓名 LIKE 张张% 等价于等价于 SELECT * FROM 学生基本资料表学生基本资料表; WHERE 张张 $ 姓名姓名思考：查询姓名中第二个字是思考：查询姓名中第二个字是“宇宇”的学生信息。的学生信息。 空值查询空值查询l 在在SELECT语句中，使用语句中，使用IS NULL或或IS NOT NULL来查来查询某个字段的值

11、是否为空值。询某个字段的值是否为空值。l IS不能用等号不能用等号 = 代替。代替。4.2.4 统计查询统计查询l SQL提供了计数、求和、求平均值、求最大值、求最提供了计数、求和、求平均值、求最大值、求最小值函数，以完成数据统计和汇总工作。小值函数，以完成数据统计和汇总工作。l 函数中如果使用函数中如果使用DISTINCT，计算时可取消指定列中，计算时可取消指定列中的重复值。的重复值。 函函 数数功功 能能 AVG( () ) 求求值的平均值值的平均值 COUNT( () 统计记录的个数统计记录的个数 MIN( () ) 求求值的最小值值的最小值 MAX( () ) 求求值的最大值值的最大值

12、 SUM( () ) 求求值的和值的和例例4.10 计算成绩表中所有成绩的最高分、最低分和平均计算成绩表中所有成绩的最高分、最低分和平均分。分。 SELECT MAX( (成绩成绩) ) AS 最高分最高分,; MIN( (成绩成绩) ) AS 最低分最低分,AVG( (成绩成绩) ) AS 平均分平均分; FROM 成绩表成绩表例例4.11 统计选修了课程的学生人数。统计选修了课程的学生人数。 SELECT COUNT( (DISTINCT 学号学号) ) AS 人数人数; FROM 成绩表成绩表4.2.4 统计查询统计查询格式：格式：SELECT ALL | DISTINCT FROM W

13、HERE GROUP BY , HAVING 功能：对查询结果分组。功能：对查询结果分组。说明：说明： 可以是字段名和可以是字段名和SQL函数表达式，也可以是函数表达式，也可以是列序号列序号( (最左边为最左边为1) )。 若分组后还要按照一定的条件进行筛选，则使用若分组后还要按照一定的条件进行筛选，则使用HAVING子句。子句。4.2.5 分组查询分组查询例例4.12 统计选修各门课程的学生人数。统计选修各门课程的学生人数。 SELECT 课程号课程号,COUNT( (学号学号) ) AS 人数人数; FROM 成绩表成绩表 GROUP BY 课程号课程号例例4.13 查询每个学生的平均成绩

14、。查询每个学生的平均成绩。 SELECT 学号学号, AVG( (成绩成绩) ) AS 平均成绩平均成绩; FROM 成绩表成绩表 GROUP BY 学号学号例例4.14 求选修课程超过或等于求选修课程超过或等于2门的学生的学号。门的学生的学号。 SELECT 学号学号,COUNT( (课程号课程号) ) AS 课程门数课程门数; FROM 成绩表成绩表; GROUP BY 学号学号 HAVING COUNT( (课程号课程号) )=24.2.5 分组查询分组查询4.2.6 排序查询排序查询格式：格式：SELECT ALL | DISTINCT FROM WHERE GROUP BY , HA

15、VING ORDER BY ASC|DESC, 功能：对查询结果进行排序。功能：对查询结果进行排序。说明：说明： 可以是字段名，也可以是数字。数字是可以是字段名，也可以是数字。数字是表的列序号，第表的列序号，第1列为列为1，以此类推。，以此类推。 ASC表示排序方式为升序，表示排序方式为升序，DESC表示排序方式为降序表示排序方式为降序,默认为升序。默认为升序。 在排序的基础上可以使用在排序的基础上可以使用TOP N PERCENT子句限制输子句限制输出的记录行数，其中出的记录行数，其中N是数值型表达式，取值范围是数值型表达式，取值范围132767，表示显示前，表示显示前N个记录；含个记录；含

16、PERCENT选项时，表示显示前选项时，表示显示前面百分之面百分之N个记录，个记录，N的取值范围的取值范围1100。例例4.15 将成绩表的信息按学生学号升序，课程号降序排列。将成绩表的信息按学生学号升序，课程号降序排列。 SELECT * FROM 成绩表成绩表; ORDER BY 学号学号,课程号课程号 DESC例例4.16 查询成绩表中选修查询成绩表中选修004课程成绩最高的前三位同学。课程成绩最高的前三位同学。 SELECT TOP 3 * FROM 成绩表成绩表; WHERE 课程号课程号=004 ORDER BY 成绩成绩 DESC4.2.6 排序查询排序查询4.2.7 内连接查询

17、内连接查询l在一个查询语句中同时涉及到两个或两个以上的表时，这在一个查询语句中同时涉及到两个或两个以上的表时，这种查询称为连接查询（也称为多表查询）。在多表之间查询种查询称为连接查询（也称为多表查询）。在多表之间查询必须处理表与表之间的连接关系。必须处理表与表之间的连接关系。l表的连接分为：表的连接分为：内连接和外连接内连接和外连接。l内连接查询格式有两种：内连接查询格式有两种：格式格式1：SELECT FROM , WHERE AND格式格式2：SELECT FROM INNER JOIN ON WHERE 例例4.17 查询选修查询选修004课程的学生姓名、课程号和成绩信息课程的学生姓名、

18、课程号和成绩信息分析：分析：姓名可以从姓名可以从“学生基本资料表学生基本资料表”中查得，课程号和中查得，课程号和成绩只能从成绩只能从“成绩表成绩表”中查得，因此本例涉及中查得，因此本例涉及“学生基本学生基本资料表资料表”和和“成绩表成绩表”两张表，它们的公共字段是两张表，它们的公共字段是“学号学号”。方法方法1：SELECT 姓名姓名,课程号课程号,成绩成绩; FROM 学生基本资料表学生基本资料表,成绩表成绩表; WHERE 学生基本资料表学生基本资料表.学号学号=成绩表成绩表.学号学号; AND 课程号课程号= 004 4.2.7 内连接查询内连接查询方法方法2：SELECT 姓名姓名,课

19、程号课程号,成绩成绩; FROM 学生基本资料表学生基本资料表 INNER JOIN 成绩表成绩表; ON 学生基本资料表学生基本资料表.学号学号=成绩表成绩表.学号学号; WHERE 课程号课程号= 004 4.2.7 内连接查询内连接查询例例4.18 查询选修查询选修C语言课程的学生学号、姓名、成绩信息语言课程的学生学号、姓名、成绩信息，并将结果按成绩降序排列。，并将结果按成绩降序排列。方法方法1：SELECT 成绩表成绩表.学号学号,姓名姓名,成绩成绩; FROM 学生基本资料表学生基本资料表,成绩表成绩表,课程表课程表; WHERE 学生基本资料表学生基本资料表.学号学号=成绩表成绩表

20、.学号学号; AND 成绩表成绩表.课程号课程号=课程表课程表.课程号课程号; AND 课程名课程名=C语言语言 ; ORDER BY 成绩成绩 DESC4.2.7 内连接查询内连接查询方法方法2：SELECT 成绩表成绩表.学号学号, 姓名姓名, 成绩表成绩表.成绩成绩;FROM 学生基本资料表学生基本资料表 INNER JOIN 成绩表成绩表;INNER JOIN 课程表课程表 ON 成绩表成绩表.课程号课程号=课程表课程表.课程号课程号; ON 学生基本资料表学生基本资料表.学号学号=成绩表成绩表.学号学号;WHERE 课程名课程名=C语言语言 ;ORDER BY 成绩表成绩表.成绩成绩

21、 DESC4.2.7 内连接查询内连接查询例例4.19 查询选修了课程的学生姓名和平均成绩信息，并查询选修了课程的学生姓名和平均成绩信息，并将结果按平均成绩升序排列。将结果按平均成绩升序排列。方法方法1：SELECT 姓名姓名,AVG(成绩成绩) AS 平均成绩平均成绩; FROM 学生基本资料表学生基本资料表,成绩表成绩表; WHERE 学生基本资料表学生基本资料表.学号学号=成绩表成绩表.学号学号; GROUP BY 姓名姓名 ORDER BY 平均成绩平均成绩方法方法2：SELECT 姓名姓名,AVG(成绩成绩) AS 平均成绩平均成绩; FROM 学生基本资料表学生基本资料表 INNE

22、R JOIN 成绩表成绩表; ON 学生基本资料表学生基本资料表.学号学号=成绩表成绩表.学号学号; GROUP BY 姓名姓名 ORDER BY 24.2.7 内连接查询内连接查询1. 查询的设计步骤查询的设计步骤 启动启动“查询设计器查询设计器”创建查询。创建查询。 在在“查询设计器查询设计器”窗口中添加表。窗口中添加表。 选择查询结果中需要的字段。选择查询结果中需要的字段。 设置查询记录的条件。设置查询记录的条件。 设置排序及分组条件来组织查询结果。设置排序及分组条件来组织查询结果。 选择查询输出类别选择查询输出类别,可以是报表、表文件、浏览窗口等。可以是报表、表文件、浏览窗口等。 运行

23、查询。运行查询。 保存查询，保存查询，查询文件的扩展名为查询文件的扩展名为.QPR。4.3 用查询设计器建立查询用查询设计器建立查询常用的启动查询设计器的方法有两种：常用的启动查询设计器的方法有两种： 选择选择“文件文件|新建新建”菜单项，或单击常用工具栏上的菜单项，或单击常用工具栏上的“新新建建”按钮，打开按钮，打开“新建新建”对话框，然后选择对话框，然后选择“查询查询”单单选按钮选按钮并单击并单击“新建文件新建文件”按钮。按钮。 用用CREATE QUERY命令打开查询设计器建立查询。命令打开查询设计器建立查询。例例4.20 查询选修大学英语课程成绩在查询选修大学英语课程成绩在8590分之

24、间的学分之间的学生，查询结果包括姓名、性别、成绩和年龄生，查询结果包括姓名、性别、成绩和年龄4个字段，并个字段，并按成绩降序排列。按成绩降序排列。2. 查询设计器查询设计器l 查询的运行查询的运行方法：方法： 在系统菜单中，选择在系统菜单中，选择“查询查询”| “运行查询运行查询”命令。命令。 在在“查询设计器查询设计器”窗口中单击鼠标右键，选择快捷窗口中单击鼠标右键，选择快捷菜单中的菜单中的“运行查询运行查询”命令。命令。 在命令窗口中键入在命令窗口中键入 DO l 查询的修改查询的修改方法：方法： 选择选择“文件文件”|“打开打开”命令，在命令，在“打开打开”对话框中，对话框中，选选择所要

25、修改的查询文件，单击择所要修改的查询文件，单击“确定确定”按钮。按钮。 在命令窗口中键入在命令窗口中键入 MODIFY QUERY 3. 查询的运行与修改查询的运行与修改1. 视图的概念视图的概念视图是一个从基本表中导出的逻辑虚表，视图没有自己的数视图是一个从基本表中导出的逻辑虚表，视图没有自己的数据实体。视图中的数据仍存放在导出视图的基本表中，因此据实体。视图中的数据仍存放在导出视图的基本表中，因此视图是一个虚表。视图不能独立存在，视图从属于数据库视图是一个虚表。视图不能独立存在，视图从属于数据库。2. 视图与查询的主要区别视图与查询的主要区别l 功能不同：功能不同：视图可以更新字段内容并返

26、回源表，而查询文视图可以更新字段内容并返回源表，而查询文件中的记录数据不能被修改。件中的记录数据不能被修改。l 从属不同：从属不同：视图不是一个独立的文件而从属于某一个数据视图不是一个独立的文件而从属于某一个数据库。查询是一个独立的文件，它不从属于某一个数据库。库。查询是一个独立的文件，它不从属于某一个数据库。4.4 用视图设计器创建视图用视图设计器创建视图l 输出去向不同：输出去向不同：视图只能输出到表中，而查询可以选择视图只能输出到表中，而查询可以选择多种去向，如表、图表、报表、标签、窗口等形式。多种去向，如表、图表、报表、标签、窗口等形式。l 使用方式不同：使用方式不同：视图只有所属的数据库被打开时，才能视图只有所属的数据库被打开时，才能使用。而查询文件可在命令窗口中执行。使用。而查询文件可在命令窗口中执行。l 访问范围不同：访问范围不同：视图可以访问本地数据源和远程数据源，视图可以访问本地数据源和远程数据源，查询只能访问本地数据源。查询只能访问本地数据源。2. 视图与查询的主要区别视图与查询的主要区别启动启动“视图设计器