3.4 小白也能掌握的高级查询技巧——高级查询

  MySQL提供了众多功能强大、方便易用的高级查询功能，例如函数的运算、正则表达式等。灵活地使用高级查询能够极大提高数据库查询效率，并完成复杂的业务需求。本节将介绍MySQL中函数的运算和正则表达式查询方法，包括：数学函数、字符串函数、日期和时间函数、条件判断函数、正则表达式查询。

3.4.1 函数的运算

  函数也称为函数关系式，假定输入参数值A，函数关系式会返回一个特定的关系值B。MySQL中提供了大量的函数，借助函数对数据进行处理可以满足各种复杂的业务需求。本节将使用样例表emp介绍数学函数、字符串函数、日期和时间函数、条件判断函数的主要作用和使用方法。

（1）数学函数
  数学函数主要用来处理数值型数据，日常业务中经常会遇到求均值、求总数的问题，这时就要借助数学函数。
【例】查询emp表中每个部门的部门人数、平均薪资、最大薪资、最小薪资、薪资合计，输入及输出如下：

函数的作用：

【例】查询emp表中每个部门的人均津贴，输入及输出如下：

字段中包含null空值的处理方法：由于comm员工津贴中包含null空值，而使用平均值函数会直接跳过空值记性计算，所以将导致计算结果失真。因此需要利用coalesce()函数对空值进行转化，从而使得平均值函数能够读取该值并进行计算。

（2）字符串函数
  字符串函数主要用来处理字符型数据，日常业务中若遇到截取字符、替换字符、合并字符串、数值转字符等问题都需要借助字符串函数，MySQL中常用字符串函数实例如下：
① 截取字符串
【例】查询emp表中每个员工姓名的首字母，输入及输出如下：

② 替换字符串
【例】将emp表中为manager的职位名称替换为leader，输入及输出如下：

③ 合并字符串
【例】将emp表中员工姓名和员工职位合并到一个字段中，输入及输出如下：

小贴士： concat()不仅能够合并字符串，还能够合并字符串与数值，合并后的新字段为字符串类型。
【例】将emp表中员工编号、员工姓名、员工职位合并到一个字段中。

④ 数值转字符
【例】将emp表中员工编号数值型字段转为字符型字段，输入及输出如下：

（3）日期和时间函数
  日期和时间函数主要用来处理日期和时间值，日常业务中若遇到查询当前日期、查询年月日、加减日期等问题都需要借助日期和时间函数，MySQL中常用日期和时间函数实例如下：
【例】获取系统当前日期和时间，输入及输出如下：

【例】查询emp表中hiredate雇佣时间的年、月和日，输入及输出如下：

【例】将emp表中hiredate雇佣时间增加1天，输入及输出如下：

小贴士：date_add()函数也能用于减少日期。
【例】将emp表中hiredate雇佣时间减少1天，输入及输出如下：

（4）条件判断函数
  条件判断函数也称为控制流程函数，程序根据不同条件执行不同的流程，MySQL中条件判断函数包括if、ifnull和case。
① if函数
  if函数的基本形式为if(express,d1,d2)，若表达式express成立则返回值为d1，否则返回值为d2。
【例】新建一个字段名为薪资状态，薪资大于20000的员工设定为“较高”，否则为“正常”，输入及输出如下：

② ifnull函数
  ifnull函数的基本形式为ifnull(d1,d2)，若d1不为空，则返回值为d1，否则返回值为d2。
【例】新建一个字段名为津贴状态，津贴为空的员工显示津贴状态为0，输入及输出如下：

③ case函数
case函数的基本形式为

case when express1 then d1 [when express2 then d2] [else dn] end

若表达式express1成立则返回值为d1，若表达式express2成立则返回值为d2，依次类推，最后返回值为dn。
【例】新建一个字段名为薪资状态，薪资大于20000的员工设定为“较高”，薪资介于8000到20000之间为“正常”，否则为“较低”，输入及输出如下：

3.4.2 正则表达式

问题A：正则表达式的含义及作用是什么？
回答A：正则表达式（Regular Expression）又称规则表达式，是计算机科学的一个概念。正则表达式通常被用来检索或替换那些符合某个规则的文本内容。例如从文本文件中提取特有信息或者替换敏感词等。

问题B：正则表达式常用匹配字符有哪些？
回答B：MySQL中正则表达式常用匹配字符如下：

问题C：如何使用正则表达式查询？
回答C：MySQL中使用regexp作为正则表达式查询语句的固定语法格式，基本SQL语法格式为：

select column_name from table_name where column_name regexp 查询条件;

【例】查询emp表中员工姓名开头为a的数据，输入及输出如下：

【例】查询emp表中员工姓名结尾为a的数据，输入及输出如下：

【例】查询emp表中员工姓名包含an的数据，输入及输出如下：

【例】查询emp表中员工姓名不包含an的数据，输入及输出如下：

【例】查询emp表中员工姓名包含a或n的数据，输入及输出如下：

练习

1、查询emp表中每个职位的职位人数、平均薪资、平均津贴、最大薪资、最小薪资、薪资合计。
2、新建一个字段名为雇佣状态，将emp表中1990年以后雇佣的员工设定为“新员工”，1990年以前的员工为“老员工”。
3、查询emp表中员工职位包含sale的数据。

练习答案

1、第一题的语句如下：

2、第二题的语句如下：

3、第三题的语句如下：