第8章数据库表的高级操作教学课件.ppt

《第8章数据库表的高级操作教学课件.ppt》由会员分享，可在线阅读，更多相关《第8章数据库表的高级操作教学课件.ppt（42页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、1 8.1 限定条件的查询限定条件的查询 8.2 使用使用GROUP BY子句的查询子句的查询 8.3 使用聚合函数的查询使用聚合函数的查询 8.4 使用使用ORDER BY子句的查询子句的查询 8.5 使用使用UNION组合多个运算结果组合多个运算结果23 SELECT语句中的语句中的WHERE子句是用来控制结子句是用来控制结果集的记录构成。可以在果集的记录构成。可以在WHERE子句中指定一子句中指定一系列查询条件，而只有这些满足条件的记录集才系列查询条件，而只有这些满足条件的记录集才可以用来构造结果集。可以用来构造结果集。 WHERE子句中的查询或限定条件如下：子句中的查询或限定条件如下：

2、 比较运算符（如比较运算符（如=、）。）。 范围说明（范围说明（BETWEEN和和NOT BETWEEN）。）。 可选值列表（可选值列表（IN、NOT IN）。）。 模式匹配（模式匹配（LIKE 和和 NOT LIKE）。）。 上述条件的逻辑组合（上述条件的逻辑组合（AND 、OR 和和 NOT）。）。4 1比较查询条件比较查询条件 比较查询条件的表达式由比较的双方和比比较查询条件的表达式由比较的双方和比较运算符组成。系统将根据查询条件的真假来较运算符组成。系统将根据查询条件的真假来决定某一条记录是否满足该查询条件，最后满决定某一条记录是否满足该查询条件，最后满足该查询条件的记录才会出现在最终

3、的结果集足该查询条件的记录才会出现在最终的结果集中。注意：中。注意：texttext、ntextntext和和imageimage数据类型不能数据类型不能同比较运算符组合成查询条件。同比较运算符组合成查询条件。【例例8.18.1】在在“读者读者”表中检索年龄不大于表中检索年龄不大于2020岁的岁的读者姓名。读者姓名。 程序代码如下程序代码如下: : USE TSGL USE TSGL SELECT SELECT 姓名姓名 FROM FROM 读者读者 WHERE WHERE 年龄年龄=2020 AND 20 AND 年龄年龄2525 OR 25 OR 年龄年龄20207图图8-1 例例8.3程

4、序运行结果程序运行结果8 3列表查询条件列表查询条件 包含列表查询条件的查询将返回所有与列表中任意一个值包含列表查询条件的查询将返回所有与列表中任意一个值匹配的记录，通常使用匹配的记录，通常使用ININ关键字（包含）来指定列表查关键字（包含）来指定列表查询条件。列表中的项目之间必须使用逗号分隔。询条件。列表中的项目之间必须使用逗号分隔。【例例8.48.4】在在“读者读者”表检索专业是计算机应用或自动控制表检索专业是计算机应用或自动控制的读者姓名。的读者姓名。程序代码如下：程序代码如下：USE TSGLUSE TSGLSELECT SELECT 姓名姓名, ,专业专业 FROM FROM 读者读

5、者 WHERE WHERE 专业专业 IN(IN(计算机应用计算机应用, , 自动控制自动控制)也可以不使用也可以不使用ININ关键字进行查询，如：关键字进行查询，如：USE TSGLUSE TSGLSELECT SELECT 姓名姓名, ,专业专业FROM FROM 读者读者WHERE WHERE 专业专业 = = 计算机应用计算机应用 OR OR 专业专业 = = 自动控制自动控制 9 4模式查询条件模式查询条件 模式查询条件通常用来返回符合某种格式的模式查询条件通常用来返回符合某种格式的所有记录，可以使用所有记录，可以使用LIKELIKE或或NOT LIKENOT LIKE关键字来指关键

6、字来指定模式查询条件。定模式查询条件。LIKELIKE关键字使用通配符来表示关键字使用通配符来表示字符串需要的匹配模式，如表字符串需要的匹配模式，如表8-18-1所示。所示。LIKELIKE关关键字的使用格式举例如表键字的使用格式举例如表8-28-2所示。所示。【例例8.58.5】在在“读者读者”表中检索专业以表中检索专业以“计算机计算机”开头的读者姓名。开头的读者姓名。程序代码如下程序代码如下: :USE TSGLUSE TSGLSELECT SELECT 姓名姓名, ,专业专业FROM FROM 读者读者WHERE WHERE 专业专业 LIKE LIKE 计算机计算机%10通配符含义%由

7、零个或者更多字符组成的任意字符串_任意单个字符用于指定范围，例如a-h，表示a到h范围内的任何单个字符用于指定范围，例如a-h，表示a到h范围以外的任何单个字符表表8-1 通配符及其含义通配符及其含义Like格式检索范围Like Me%查询以字母Me开头的所有字符串（如Mengyue）Like %ing查询以字母ing结尾的所有字符串（如ming、string）Like %en%将查询在任何位置包含字母en的所有字符串（如meng、green）Like _engyue将查询以字母engyue结尾的所有7个字母的名称（如mengyue）Like B-King将查询以字符串ing结尾，以从B到K任

8、何字母开头的所有名称Like Md%建查询以字母M开头，并且第二个字母不是d的所有名称表表8-2 LIKE关键字的使用格式举例关键字的使用格式举例11 5逻辑运算符逻辑运算符 除了前面已经提到的查询条件外，还需要使除了前面已经提到的查询条件外，还需要使用逻辑运算符才能组成完整的查询条件。逻辑运用逻辑运算符才能组成完整的查询条件。逻辑运算符有算符有ANDAND、OROR、NOTNOT。其中，。其中，ANDAND、OROR用于连接用于连接WHEREWHERE语句中的查询条件，语句中的查询条件，NOTNOT用于反转查询条件用于反转查询条件的结果。的结果。【例例8.68.6】在在“读者读者”表中检索专

9、业以表中检索专业以“计算机计算机”开头并且性别为男的读者姓名。开头并且性别为男的读者姓名。程序代码如下程序代码如下: :USE TSGLUSE TSGLSELECT SELECT 姓名姓名, ,专业专业FROM FROM 读者读者WHERE WHERE 专业专业 Like Like 计算机计算机% AND % AND 性别性别=男男 12 GROUP BY 子句的主要作用是子句的主要作用是可以将数据记录设置的条件分成多个可以将数据记录设置的条件分成多个组，而且只有使用了组，而且只有使用了GROUP BY 子子句，句，SELECT子句中所使用的聚合函子句中所使用的聚合函数才会起作用。数才会起作用

10、。GROUP BY 子句关子句关键字后面将跟着用于分组的字段名称键字后面将跟着用于分组的字段名称列表，这个列表将决定查询结果集分列表，这个列表将决定查询结果集分组的依据和顺序。在最终的结果集中，组的依据和顺序。在最终的结果集中，分组列表包含字段的每个非重复值只分组列表包含字段的每个非重复值只存在一条记录。存在一条记录。13【例例8.78.7】在在“读者读者”表中检索每个专业的表中检索每个专业的平均年龄。平均年龄。程序代码如下程序代码如下: :USE TSGLUSE TSGLSELECT SELECT 专业专业, AVG(, AVG(年龄年龄) AS ) AS 平均年龄平均年龄 FROM FRO

11、M 读者读者 GROUP BY GROUP BY 专业专业 在查询分析器的在查询分析器的“查询脚本编辑器查询脚本编辑器”中中输入并运行该程序，运行结果如图输入并运行该程序，运行结果如图8-28-2所示。所示。14图图8-2 例例8.7程序运行结果程序运行结果15 也可以在也可以在GROUP BYGROUP BY关键字后使用多个字段名称关键字后使用多个字段名称作为分组字段，这样系统将根据这些字段的先后顺作为分组字段，这样系统将根据这些字段的先后顺序，对结果集进行更加详细的分组。序，对结果集进行更加详细的分组。【例例8.88.8】在在“读者读者”表中检索每个专业的男生表中检索每个专业的男生和女生的

12、平均年龄。和女生的平均年龄。程序代码如下程序代码如下: :USE TSGLUSE TSGLSELECT SELECT 专业专业, ,性别性别,AVG(,AVG(年龄年龄)AS )AS 平均年龄平均年龄 FROM FROM 读者读者GROUP BY GROUP BY 专业专业, ,性别性别 在查询分析器的在查询分析器的“查询脚本编辑器查询脚本编辑器”中输入并中输入并运行该程序，运行结果如图运行该程序，运行结果如图8-38-3所示。所示。16图图8-3 例例8.8程序运行结果程序运行结果17聚合函数包括聚合函数包括SUM、AVG、COUNT、COUNT(*)、MAX、MIN。它们的作用是在查询结果

13、集中生成汇总值。它们的作用是在查询结果集中生成汇总值。除了除了COUNT(*)外，其他汇总函数都处理单个字段中全部外，其他汇总函数都处理单个字段中全部符合条件的值以生成一个结果集。这些汇总函数都可以应符合条件的值以生成一个结果集。这些汇总函数都可以应用于数据表中的所有记录，汇总函数的语法及其功能如表用于数据表中的所有记录，汇总函数的语法及其功能如表8-3所示。所示。函数语法说明功能SUM（表达式）返回数值表达式中所有值的和AVG（表达式）返回数值表达式中所有值的平均值COUNT（表达式）返回数值表达式中值的个数COUNT（*）返回选定的行数MAX（表达式）返回表达式中的最大值MIN（表达式）返

14、回表达式中的最小值表表8-318 8.3.1 SUM函数函数 聚合函数聚合函数SUM的功能是返回数值表达的功能是返回数值表达式中所有值的和。用来求和的表达式通常式中所有值的和。用来求和的表达式通常是字段名称或包含字段名称的表达式。是字段名称或包含字段名称的表达式。 使用使用SUM函数时，应注意以下几点：函数时，应注意以下几点： 运算时，运算时，SUM将忽略求和对象中的空值。将忽略求和对象中的空值。 可以同时使用可以同时使用DISTINCT关键字，以便在关键字，以便在求和之前去掉重复值。求和之前去掉重复值。 SUM函数只能对数值类型的字段使用。函数只能对数值类型的字段使用。19【例例8.98.9

15、】在在“图书图书”表中，以分类号检索表中，以分类号检索各类书籍的单价总和。各类书籍的单价总和。程序代码如下程序代码如下: :USE TSGLUSE TSGLSELECT SELECT 分类号分类号,SUM(,SUM(单价单价) AS ) AS 单价总和单价总和FROM FROM 图书图书 GROUP BY GROUP BY 分类号分类号 在查询分析器的在查询分析器的“查询脚本编辑器查询脚本编辑器”中中输入并运行该程序，运行结果如图输入并运行该程序，运行结果如图8-48-4所示。所示。20图图8-4 例例8.9程序运行结果程序运行结果21 8.3.2 AVG函数函数 聚合函数聚合函数AVGAVG

16、的功能是返回组中值的的功能是返回组中值的平均值。用来求平均值的表达式，通常是平均值。用来求平均值的表达式，通常是字段名称或包含字段名称的表达式。字段名称或包含字段名称的表达式。 使用使用AVGAVG函数时，应注意以下几点：函数时，应注意以下几点： 运算时，运算时，AVGAVG将忽略运算对象中的空值。将忽略运算对象中的空值。 可以同时使用可以同时使用DISTINCTDISTINCT关键字，以便在关键字，以便在运算之前去掉重复值。运算之前去掉重复值。 AVGAVG函数只能对数值类型的字段使用。函数只能对数值类型的字段使用。22【例例8.108.10】在在“图书图书”表中，按分类号检表中，按分类号检

17、索各类图书的单价平均值。索各类图书的单价平均值。程序代码如下程序代码如下: :USE TSGLUSE TSGLSELECT SELECT 分类号分类号,AVG(,AVG(单价单价) AS ) AS 平均单价平均单价FROM FROM 图书图书 GROUP BY GROUP BY 分类号分类号23 8.3.3 MAX函数函数 聚合函数聚合函数MAXMAX的功能是返回表达式中的功能是返回表达式中最高值。用来选取最高值的表达式通常是最高值。用来选取最高值的表达式通常是字段名称或包含字段名称的表达式。字段名称或包含字段名称的表达式。 除了可以从数值类型的字段中选取最除了可以从数值类型的字段中选取最大值

18、外，大值外，MAXMAX函数另外一个常用的功能是从函数另外一个常用的功能是从字符类型的字段中选取最大值。字符类型的字段中选取最大值。 使用使用MAXMAX函数时，应注意以下两点：函数时，应注意以下两点： 运算时，运算时，MAXMAX将忽略运算对象中的空值。将忽略运算对象中的空值。 不能使用不能使用MAXMAX函数从函数从bitbit、texttext、imageimage数数据类型的字段中选取最大值。据类型的字段中选取最大值。24【例例8.118.11】在在“读者读者”表中，按性别查找年龄最大的值。表中，按性别查找年龄最大的值。程序代码如下程序代码如下: :USE TSGLUSE TSGLSE

19、LECT SELECT 性别性别,MAX(,MAX(年龄年龄) AS ) AS 最大年龄最大年龄FROM FROM 读者读者 GROUP BY GROUP BY 性别性别 在查询分析器的在查询分析器的“查询脚本编辑器查询脚本编辑器”中输入并运行该中输入并运行该程序，运行结果如图程序，运行结果如图8-58-5所示。所示。【例例8.128.12】在在“读者读者”表中，按专业检索出姓名按照字表中，按专业检索出姓名按照字母顺序排在最后的读者姓名。母顺序排在最后的读者姓名。程序代码如下。程序代码如下。USE TSGLUSE TSGLSELECT SELECT 专业专业,MAX(,MAX(姓名姓名) as

20、 ) as 姓名姓名 FROM FROM 读者读者 GROUP BY GROUP BY 专业专业 25图图8-5 例例8.11程序运行结果程序运行结果26 8.3.4 MIN函数函数 聚合函数聚合函数MINMIN的功能是返回表达式中的功能是返回表达式中的最小值。用来选取最小值的表达式通常的最小值。用来选取最小值的表达式通常是字段名称或包含字段名称的表达式。是字段名称或包含字段名称的表达式。 除了可以从数值类型的字段中选取最除了可以从数值类型的字段中选取最小值外，小值外，MINMIN函数另外一个常用的功能是从函数另外一个常用的功能是从字符类型的字段中选取最小值。字符类型的字段中选取最小值。 使用

21、使用MINMIN函数时，应注意以下两点：函数时，应注意以下两点： 运算时，运算时，MINMIN将忽略运算对象中的空值。将忽略运算对象中的空值。 不能使用不能使用MINMIN函数从函数从bitbit、texttext、imageimage数数据类型的字段中选取最小值。据类型的字段中选取最小值。27【例例8.138.13】在在“图书图书”表中，按书名检索图书中表中，按书名检索图书中单价最小的记录。程序代码如下单价最小的记录。程序代码如下: :USE TSGLUSE TSGLSELECT SELECT 书名书名,MIN(,MIN(单价单价) as ) as 最小单价最小单价 FROM FROM 图书

22、图书 GROUP BY GROUP BY 书名书名【例例8.148.14】在在“读者读者”表中，按专业检索姓名按表中，按专业检索姓名按照字母顺序排在最前边的读者姓名。程序代码如下照字母顺序排在最前边的读者姓名。程序代码如下: :USE TSGLUSE TSGLSELECT SELECT 专业专业,MIN(,MIN(姓名姓名) as ) as 姓名姓名FROM FROM 读者读者 GROUP BY GROUP BY 专业专业288.3.5 COUNT函数和函数和COUNT(*)函数函数 聚合函数聚合函数COUNTCOUNT的功能是返回表达式中值的个的功能是返回表达式中值的个数，其表达式通常是字段

23、名称。数，其表达式通常是字段名称。 聚合函数聚合函数COUNT(COUNT(* *) )的功能是返回符合条件的的功能是返回符合条件的记录条数。记录条数。 这两个函数的主要区别如下：这两个函数的主要区别如下： COUNTCOUNT函数将忽略对象中的空值，而函数将忽略对象中的空值，而COUNT(COUNT(* *) )函函数则将所有符合条件的记录都计算在内。数则将所有符合条件的记录都计算在内。 使用使用COUNTCOUNT函数可以同时使用可选关键字函数可以同时使用可选关键字DISTINCTDISTINCT去掉重复值，而使用去掉重复值，而使用COUNT(COUNT(* *) )函数时则函数时则不可以

24、。不可以。 不能使用不能使用COUNTCOUNT函数来计算定义为函数来计算定义为texttext和和imageimage数数据类型的字段个数，但是可以使用据类型的字段个数，但是可以使用COUNT(COUNT(* *) )函数。函数。29【例例8.158.15】在在“借阅借阅”表中，检索出表中，检索出借书日期在借书日期在20032003年年1212月月1 1日之前的所有日之前的所有记录。记录。程序代码如下程序代码如下: :USE TSGL USE TSGL SELECT COUNT(SELECT COUNT(* *) AS ) AS 记录数记录数FROM FROM 借阅借阅 WHERE WHER

25、E 借书日期借书日期 = 12/1/2003= 12/1/200330【例例8.168.16】在在“借阅借阅”表中，检索出借书日期在表中，检索出借书日期在20032003年年1212月月1 1日之前的读者人数。日之前的读者人数。具体代码如下具体代码如下: :USE TSGLUSE TSGLSELECT COUNT(DISTINCT SELECT COUNT(DISTINCT 借书证号借书证号) AS) AS读者数读者数FROM FROM 借阅借阅 WHERE WHERE 借书日期借书日期=12/1/2003=12/1/2003 在查询分析器的在查询分析器的“查询脚本编辑器查询脚本编辑器”中输入

26、并中输入并运行该程序，运行结果如图运行该程序，运行结果如图8-68-6所示。所示。 从上面两个例子可以看出：从上面两个例子可以看出：COUNTCOUNT函数所获得函数所获得的数据精确而有针对性，而的数据精确而有针对性，而COUNT(COUNT(* *) )函数在使用时函数在使用时相对简单。相对简单。31图图8-6 例例8.16程序运行结果程序运行结果32 ORDER BY子句将根据查询结果中子句将根据查询结果中的一个字段或多个字段对查询结果进行排的一个字段或多个字段对查询结果进行排序，这种排序的顺序可以是升序的（使用序，这种排序的顺序可以是升序的（使用ASC关键字），也可以是降序的（使用关键字

27、），也可以是降序的（使用DESC关键字）。如果没有指定排序的顺关键字）。如果没有指定排序的顺序是升序还是降序，系统将默认为升序。序是升序还是降序，系统将默认为升序。当然，在当然，在ORDER BY子句中可以指定不子句中可以指定不止一个字段，在这种情况下，系统将根据止一个字段，在这种情况下，系统将根据ORDER BY子句中指定的排序字段的顺子句中指定的排序字段的顺序对查询结果进行排序。序对查询结果进行排序。33【例例8.178.17】在在“读者读者”表中检索每个专业表中检索每个专业的学生信息，并按年龄由小到大进行输出。的学生信息，并按年龄由小到大进行输出。程序代码如下程序代码如下: :USE T

28、SGLUSE TSGLSELECT SELECT 姓名姓名, ,专业专业, ,年龄年龄 FROM FROM 读者读者 ORDER BY ORDER BY 专业专业, ,年龄年龄 在查询分析器的在查询分析器的“查询脚本编辑器查询脚本编辑器”中中输入并运行该程序，运行结果如图输入并运行该程序，运行结果如图8-78-7所示。所示。34图图8-7 例例8.17程序运行结果程序运行结果35【例例8.188.18】在在“读者读者”表中，首先将表中，首先将中间结果集按照所属专业名称升序排列，中间结果集按照所属专业名称升序排列，然后按照年龄大小进行降序输出。然后按照年龄大小进行降序输出。程序代码如下程序代码如

29、下: :USE TSGLUSE TSGLSELECT SELECT * * FROM FROM 读者读者 ORDER BY ORDER BY 专业专业, ,年龄年龄 DESCDESC36 UNION（并）运算符可用来将两个或多个（并）运算符可用来将两个或多个SELECT语句的查询结果组合成一个结果集。语句的查询结果组合成一个结果集。 使用使用UNION运算符组合两个查询结果集的基运算符组合两个查询结果集的基本规则如下：本规则如下： 所有查询中的列数和列的顺序必须相同。所有查询中的列数和列的顺序必须相同。 数据类型必须兼容。数据类型必须兼容。UNION运算符的基本语法格式：运算符的基本语法格式：

30、select_statement UNION ALL select_statement其中：其中：select_statement 是是SELECT 查询语句。查询语句。37【例例8.198.19】使用使用UNIONUNION将将“读者读者”表和表和“学生学生”表中的人员组合输出。表中的人员组合输出。程序代码如下程序代码如下: :USE TSGLUSE TSGLSELECT SELECT 姓名姓名, ,性别性别 FROM FROM 读者读者UNION UNION SELECT SELECT 姓名姓名, ,性别性别 FROM FROM 学生学生 在查询分析器的在查询分析器的“查询脚本编辑器查询脚

31、本编辑器”中中输入并运行该程序，运行结果如图输入并运行该程序，运行结果如图8-88-8图图8-108-10所示。所示。38图图8-8 读者表中的记录读者表中的记录图图8-9 学生表中的记录学生表中的记录39图图8-10 读者表和学生表的合并读者表和学生表的合并40【例例8.208.20】在在XJGLXJGL（学籍管理）数据库中使用（学籍管理）数据库中使用UNIONUNION运算符查询运算符查询XSBXSB表（学生表）中表（学生表）中“计算机计算机”系系或或“自动化自动化”系学生的学号、姓名和所在系。系学生的学号、姓名和所在系。程序代码如下：程序代码如下： USE XJGL USE XJGL S

32、ELECT XH,XM,XI SELECT XH,XM,XI FROM XSB FROM XSB WHERE XI= WHERE XI=计算机系计算机系 UNION UNION SELECT XH,XM,XI SELECT XH,XM,XI FROM XSB FROM XSB WHERE XI= WHERE XI=自动化系自动化系 在查询分析器的在查询分析器的“查询脚本编辑器查询脚本编辑器”中输入并中输入并运行该程序，数据库中表数据与运行结果如图运行该程序，数据库中表数据与运行结果如图8-118-11和图和图8-128-12所示。所示。41图图8-11 XSB表中的记录表中的记录42图图8-12 例例8.20程序运行结果程序运行结果