模糊查询和聚合函数.docx
- 文档编号:4081845
- 上传时间:2022-11-27
- 格式:DOCX
- 页数:11
- 大小:34.27KB
模糊查询和聚合函数.docx
《模糊查询和聚合函数.docx》由会员分享,可在线阅读,更多相关《模糊查询和聚合函数.docx(11页珍藏版)》请在冰豆网上搜索。
模糊查询和聚合函数
第八章模糊查询和聚合函数
本章工作任务
✓学生考试信息汇总统计
本章技能目标
✓使用LIKE、BETWEEN、IN进行模糊查询
✓使用聚合函数统计和汇总查询信息
学生考试信息汇总统计
本章简介
上一章介绍了如何在MySQL中编写基本的查询语句,包括条件查询、列别名,在查询中使用运算符、函数等。
接着上一章的内容,我们继续学习其他几种常用的查询,包括模糊查询、聚合函数、分组查询和多表连接查询。
本章首先讲解模糊查询和聚合函数的使用。
预习作业
请按以下步骤预习本章内容:
1.带着以下任务,阅读、学习本章的相关资料。
2.标注出本章看不懂或存在疑惑的部分。
3.整理、记录学习中的问题。
4.熟记本章的英文单词。
请到中心听课前,完成下面的作业。
1.背诵英文单词
(1)between:
(2)business:
(3)count:
(4)exchange:
(5)salary:
(6)education:
2.预习并回答以下问题
阅读本章内容,回答以下问题
(1)在MySQL中常用的通配符都有哪些?
分别代表什么含义?
(2)简述下列MySQL语句的含义。
SELECTCOUNT(*)FROMStudent
SELECTSUM(Score)FROMStudent
3.预习完成编码题
请使用MySQL语句编写代码,完成以下功能。
(1)查询Student表中所有姓李的学生信息。
(2)查询Student表中学生的总人数。
4.记录预习的问题
记录学习中遇到的问题,填写于下面的横线上,以便集中探讨解决
——————————————————————————————————————
——————————————————————————————————————
——————————————————————————————————————
8.1模糊查询
在使用模糊查询时,查询者对查询条件也是模糊的、大概的、不特别明确的。
例如,查询张姓学生的信息、查询分数在60-80分之间的考试成绩或者查询北京、上海、广州地区的学生,这种查询不是指定某个人的姓名、一个具体的分数或者某个固定的地区,这样的查询都属于模糊查询。
模糊查询可以使用LIKE关键字、通配符来进行。
前面章节学习过的ISNULL查询严格说也是一种模糊查询,模糊查询还有基于某个范围内的查询和在某些列举值内的查询。
首先来看什么是通配符。
8.1.1通配符
简单的讲,通配符是一类字符,它可以代替一个或多个真正的字符,查找信息时作为替代字符出现。
MySQL中的通配符必须与LIKE关键字一起使用,以完成特殊的约束或要求。
在MySQL中,提供了如表8-1所示的通配符。
表8-1通配符
通配符
解释
示例
符合条件的值
_
一个字符
ALIKE'C_'
CS、Cd等
%
任意长度的字符串
BLIKE'CO%'
CONST、COKE等
[]
括号中所指定范围内的一个字符
CLIKE'9W0[1-2]'
9W01或9W02
[^]
不在括号中所指定范围内的一个字符
DLIKE'9W0[^1-2]'
9W03或9W07等
8.1.2使用LIKE进行模糊查询
LIKE运算符用于匹配字符串或字符串的一部分。
由于该运算符只用于字符串,所以仅与字符数据类型(如char或varchar等)联合使用。
在数据更新、删除或者查询的时候,依然可以使用LIKE关键字进行匹配查找,例如,查找姓张的学生信息:
SELECT*FROMStudentWHEREStudentNameLIKE‘张%’
或者查询住址包含“北京”字样的学生信息:
SELECT*FROMStudentWHEREAddressLIKE‘%北京%’
8.1.3使用BETWEEN在某个范围内进行查询
使用关键字BETWEEN可以查找那些介于两个已知值之间的一组未知值。
要实现这种查找,必须知道查找的初值和终值,并且初值要小于等于终值,初值和终值用AND关键字分开。
例如,查询分数在60(含)到80(含)之间的信息如下:
SELECT*FROMResultWHEREStudentResultBEIWEEN60AND80
如果写成如下形式:
SELECT*FROMResultWHEREStudentResultBETWEEN80AND60
虽不会报语法错误,但也不会查询到任何信息。
此外,BETWEEN查询在查询日期范围的时候使用得比较多。
例如,查询不在2014年1月1号到2014年8月1号之间订购的图书列表:
SELECT*FROMSalesWHEREord_dateNOTBETWEEN'2014-1-1'AND'2014-8-1'
使用NOT来对限制条件进行“取反”操作。
8.1.4使用IN在列举值内进行查询
查询的值是指定的某些值之一,可以使用带列举值的IN关键字来进行查询。
将列举值放在圆括号里,用逗号分开。
例如,查询北京、广州或者上海的学生姓名:
SELECTStudentNameAS学生姓名FROMStudentWHEREAddress
IN('北京','广州','上海')ORDERBYAddress
同样可以把IN关键字和NOT关键字合起来使用,这样可以得到所有不匹配列举值的行。
提示:
列举值类型,必须与匹配的列具有相同的数据类型
技能训练
[上机练习1]
指导——使用模糊查询查询学生相关信息
训练要点
使用LIKE、BETWEEN、IN关键字进行模糊查询。
需求说明
→查询住址在“山东”的学生姓名、电话、住址。
→查询名称中含有“数据库”字样的科目名称、学时及所属年级,并按年级由低到高显示。
→查询电话中以“1387”开头的学生姓名、住址和电话。
→查询姓为“姜*”的学生学号、姓名和住址,其中*代表一个字。
→查询学号为S1101004的学生参加的科目编号为1、2、3的考试成绩信息。
→查询出生日期从1989-1-1到1995-12-31之间的学生信息。
难点分析
匹配一个字符的通配符和匹配多个字符的通配符的区别。
实现思路
(1)在MySQL中,新建一个查询窗口,选择数据库MySchool或在查询窗口中输入语句“USEMySchool"。
(2)住址为山东,并没有说明是山东什么地方,所以使用匹配多个字符的通配符,参考如下MySQL语句
SELECTStudentName,Phone,AddressFROMStudentWHEREAddressLIKE'%山东%’
提示:
如果写成“LIKE‘山东%’”可能会漏查数据
(3)按相同思路完成需求2、3。
(4)需求4是查询姓姜的单名的学生信息,需要使用匹配一个字符的通配符,参考如下
语句条件。
SELECTStudentNo,StudentName,AddressFROMStudentWHEREStudentNameLIKE'姜_'
(5)需求5的条件有两个:
学号为S1101004,并且科目编号为1、2、3。
可以使用AND连接两个条件,IN来限定科目,参考如下SQL语句条件。
WHEREStudentNo=‘S1101004’ANDSubjectIdIN(1,2,3)
(6)需求6查询两个日期值之间的信息,使用BETWEEN即可。
(7)保存SQL为“使用模糊查询学生相关信息.sql"文件。
8.2MySQL中的聚合函数
在查询中还会经常碰到取某些列的最大值、最小值、平均值等信息,有时候还需计算出究竟查询到多少行数据。
这个时候,查询的“统计数据”是用户比较关心的,这就涉及MySQL的另外一类内部函数——“聚合函数”。
聚合函数能够基于列进行计算,将多个值合并为一个值,其作用是对一组值进行计算,并返回计算后的值。
下面介绍MySQL提供的几个常用的聚合函数。
8.2.1SUM()函数
SUM()函数返回表达式中所有数值的总和,空值将被忽略。
SUM()函数只能用于数字类型的列不能够汇总字符、日期等其他数据类型。
例如,查询学生编号为S1101001的考试总分,可以使用如下查询。
SELECTSUM(StudentResult)AS学号为S1101001的学生总分FROMResultWHEREStudentNo=‘S1101001’
使用SUM()聚合函数进行汇总得到的查询结果如图8.1所示。
注意这种查询只返回一个数值,因此,不能够直接与可能返回多行的列一起使用来进行查询,例如:
SELECTSUM(StudentReslut)AS学号为S1101001的学生总分,SubjectIdAS科目编号FROMResultWHEREStudentNo=‘S1101001’
将报告错误信息。
但是,在一个查询中可以同时使用多个聚合函数。
8.2.2AVG()函数
AVG()函数返回表达式中所有数值的平均值,空值将被忽略。
AVG()函数也只能用于数字类型的列。
例如,成绩表中存在如下数据项,如图8.2所示。
图8.2学生成绩表中的数据
要查询学号为‘S1101001’学生的平均成绩,语句如下。
SELECTAVG(StudentResult)AS平均成绩FROMResultWHEREStudentNo=‘S1101001’
执行结果如图8.3所示。
图8.3使用AVG()聚合函数求平均值
8.2.3MAX()函数和MIN()函数
MAX()函数返回表达式中的最大值,MIN()函数返回表达式中的最小值,这两个函数同样都忽略任何空值,并且它们都可以用于数字型、字符型及日期/时间类型的列。
对于字符序列,MAX()函数查找排序序列的最大值。
而MIN()函数同理,返回排序序列的最小值。
例如,查询平均成绩、最高分、最低分的语句如下。
SELECTAVG(StudentResult)AS平均成绩,MAX(StudentResult)AS最高分,MIN(StudentResult)AS最低分FROMResultWHEREStudentNo='S1101001'
查询结果如图8.4所示。
图8.4使用MAX()和MIN()聚合函数求最大值和最小值
8.2.4COUNT()函数
COUNT()函数返回提供的组或记录集中的计数。
另外,也可以使用星号(*)作为COUNT的表达式,使用星号可以不必指定特定的列而计算所有的行数,当对所有的行进行计数时,则包括包含空值的行。
例如,查询学号为S1101002考试记录信息如下。
例如,查询学号位S1101002总记录数的语句如下。
SELECTCOUNT(*)AS总记录数FROMResultwhereStudentNo=’S1101002’
查询结果如图8.5所示。
例如,查询单列(Result)记录总数的语句如下。
SELECTCOUNT(StudentResult)AS分数记录数FROMResultwhereStudentNo=’S1101002’
查询结果如图8.6所示,单列统计总记录数时忽略了空值。
图8.5使用COUNT(*)聚合函数得到行数
图8.6使用COUNT(StudentResult)聚合函数
8.2.5技能训练
[上机练习2]
练习——查询汇总信息
训练要点
聚合函数的使用。
需求说明
→统计数据库中学生总人数。
→查询第一学期的总学时。
→查询学号为S1101004的学生第一学期考试的总成绩。
→查询学号为S1101004的学生第一学期所有考试的平均分。
→查询2013年3月22日科目“C#语言和数据库技术”的最高分、最低分、平均分
→查询2013年3月22日科目“C#语言和数据库技术”及格学生的平均分。
→查询所有参加“C#语言和数据库技术”科目考试的学生的平均分。
提示:
◆需求3、4中需要查询某学生在第一学期的考试成绩统计,首先需要知道第一学期的科目编号,然后使用聚合函数来进行计算。
◆需求5、6需要了解“c#语言和数据库技术”的科目编号。
本章总结
Ø通配符是一类字符,它可以代替一个或多个真正的字符,查找信息时作为替代字符出现。
Ø“_”和“%”分别匹配一个字符和多个字符。
Ø使用LIKE、BETWEEN、IN关键字,能够进行模糊查询。
Ø聚合函数能够对列进行计算,对于分析和统计非常有用。
Ø常用的聚合函数有SUM()、AVG()、MAX()、MIN()、COUNT()。
课后习题(Lesson8)
一、选择题
(所有题目均为不定项选择题,每题3分,共30分,将选中的选项用红色标注)
1.在MySQL聚合函数中,以下()用于返回表达式中所有值的总和
A.SUM
B.COUNT
C.AVG
D.MAX
2.在MySQL中,聚合函数能够基于列进行计算,以下关于聚合函数描述错误的是()
A.COUNT(*)返回组中项目的数量,这些项目不包括NULL值和重复项
B.MAX忽略任何空值,对于字符串,MAX查找排序序列的最高值
C.MIN忽略任何空值,对于字符串,MIN查找排序序列的最低值
D.SUM返回表达式中所有值的和,空值将被忽略
3.假设Sales表用于存储销售信息,SName列为销售人员姓名,SMoney列为销售额度,现在需要查询最大一笔销售额度是多少,则正确的查询语句是()
A.SELECTMAX(SMoney)FROMSalesWHEREMAX(SMoney)>0
B.SELECTSName,MAX(SMoney)FROMSalesWHERECOUNT(SName)>0
C.SELECTSName,MAX(SMoney)FROMSalesORDERBYSName,SMoney
D.SELECTMAX(SMoney)FROMSales
4.假设Users表中的TelNumber列存储电话号码信息,则查询不是以7开头的所有电话号码的查询语句是()
A.SELECTTelNumberFROMUsersWHERETelNumberISNOT‘%7’
B.SELECTTelNumberFROMUsersWHERETelNumberLIKE‘%7%’
C.SELECTTelNumberFROMUsersWHERETelNumberNOTLIKE‘7%’
D.SELECTTelNumberFROMUsersWHERETelNumberLIKE‘[1-6]%’
5.要查询一个班级中低于平均成绩的学生,需要使用到()
A.WHERE子句
B.ORDERBY子句
C.HAVING子句
D.聚合函数AVG
6.以下()不属于聚合函数
A.MAX
B.COUNT
C.CONVERT
D.MIN
二、简答题
(每题5分,共20分,答案用红色文字直接填写在标题下方)
1.通配符的作用是什么?
2.通配符“_”和“%”有什么区别?
3.分析如下查询语句,说出查询结果是什么。
SELECT*FROMProductWHEREProductNameLIKE‘[草鲶]鱼’
4.举例说出常用的聚合函数有哪几个,作用分别是什么?
三、编程题(每题20分,共60分)
1.在MySQL数据库中,房屋信息表的结构如下所示,现在需要查询如下信息,请编写SQL语句实现
a)房屋类型包含“一厅”的房屋信息
b)房主姓名为“于*玲”的房屋信息,其中*代表一个字
c)地理位置为“解放区”的出租信息
d)所有“一室一厅”出售的平均面积
e)房屋面积在100平米以上的出售房屋数量
f)地理位置为“解放区”的总销售房屋面积
房屋信息表HouseInfo结构
字段名称
字段说明
类型
长度
备注
HouseID
序号
Int
自动编号,主键
HouseType
房屋类型
varchar
30
如一室一厅
Area
面积
float
房屋建筑面积
LandLord
房主姓名
varchar
20
LandLordID
身份证号
varchar
18
ExchangeType
交易类型
varchar
2
只输入出租或出售
LandLordTel
联系电话
Varchar
20
Address
地理位置
varchar
50
2.在MySQL数据库中,雇员信息表的结构如下,现在需要查询如下信息,请编写SQL语句实现。
a)所有男员工的平均年龄
b)学历本科的员工信息
c)年龄超过25岁的员工平均工资
d)男、女员工最高工资和最低工资分别是多少
雇员信息表HouseInfo结构
字段名称
字段说明
类型
长度
备注
EmployeeID
员工号
Int
自动编号,主键
Name
姓名
varchar
30
不允许为空
Age
年龄
Int
默认为0,不允许为空
Sex
性别
char
2
默认为男,不允许为空
Education
学历
varchar
18
Job
职位
varchar
20
不允许为空
Salary
薪水
double
默认为0,不允许为空
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 模糊 查询 聚合 函数
![提示](https://static.bdocx.com/images/bang_tan.gif)