6、Mysql查询（函数）

一、概要

函数是一种有零个或多个参数并且有一个返回值的程序,函数主要分为两大类单行函数,多行函数(聚合函数)

二、单行函数

1、定义

单行函数: 对每一个函数应用在表的记录中时，只能输入一行结果，返回一个结果。

2、分类

字符函数
数值函数
日期函数
转换函数
通用函数

2.3、字符函数

1、UPPER

说明

将输入的字符串变为大写返回；

作用

在一般的使用之中，用户输入数据的时候不关心数据本身存放的是大写还是小写

语法
```
upper（列 | 字符串）
```

示例代码
1、将abcd改变成大写

SELECT UPPER('abcd') FROM dual

2、查询姓名为simth的员工信息

SELECT *
FROM  emp
WHERE ename = UPPER('smith');

2、LOWER

说明

将输入的字符串变为小写返回

语法格式
```
LOWER(字符串 | 列)
```
示例代码
1、将员工姓名转化成小写显示
```
SELECT lower(ename)
FROM  emp
```

3、REPLACE

说明
```
字符串进行替换
```

语法格式

REPLACE(字符串 | 列,   被替换的字符串，  用来替换的字符串)

示例代码
使用字母'*'替换掉姓名中的所有字母's'
```
SELECT REPLACE(ename,'S','*') FROM emp
```

4、LENGTH

说明
```
求出字符串的长度
```
语法格式
```
LENGTH(字符串 | 列)
```
示例代码
查询出每个雇员姓名的长度
```
SELECT LENGTH(ename)
FROM emp;
```

5、INITCAP

说明
```
首字母大写
```
语法格式
```
INITCAP(字符串 | 列)
```
示例代码
将员工的姓名全部大写字母开头
```
SELECT initcap(ename)
FROM emp;
```

6、SUBSTR（注意：从0和1开始截取都是从字符串的第一位开始）

说明

字符串截取,开始点可以是正也可以是负,如果是负表示从后面开始截取 ,如果长度不写,默认截取到末尾

语法格式

SUBSTR(字符串 | 列，开始点, 长度)

示例代码
1、从开始点一直截取到结尾

SELECT SUBSTR('abcdefg',2) from  dual; --bcdefg

2、从开始点截取到结束点，截取部分内容

SELECT SUBSTR('abcdefg',2,4) from  dual;--bcde

3、要求截取每个雇员姓名的后2个字母

SELECT ename,SUBSTR(ename,LENGTH(ename)-1) FROM emp;
等价于
SELECT ename,SUBSTR(ename,-2) FROM emp;

2.4、数值函数

1、ROUND

说明
```
四舍五入的操作 默认保留0位
```

语法格式

ROUND(数字 | 列 [,保留小数的位数])

示例代码

SELECT  ROUND(100.12),
ROUND(100.12 ,1) ,
ROUND(-100.56),
ROUND(-100.56,1),
ROUND(-100.567123,3)
FROM DUAL;
100           100.1           -101           -100.6             -100.567

2、TRUNCATE

说明
```
舍弃指定位置的内容
```

语法格式

TRUNC(数字 | 列 [,保留小数的位数])

示例代码

SELECT TRUNC(903.53567),TRUNC(-903.53567), TRUNC(903.53567,2), TRUNC(-90353567,-1)
FROM dual;
903              -903             903.53           -90353560

3、MOD

说明
```
取余数
```
语法
```
MOD(数字 1，数字2)
```
示例代码
```
SELECT MOD(10,3) FROM dual
```

2.5、日期函数

1、NOW：

说明

返回服务器的当前日期和时间(fsp指定小数秒的精度，取值0--6)
格式 ‘YYYY-MM-DD HH:MM:SS’或者‘YYYYMMDDHHMMSS’

语法格式
```
NOW([fsp])
```

示例代码

-- now()的显示格式是‘YYYY-MM-DD HH:MM:SS’
select now();
-- now()+0的显示格式是‘YYYYMMDDHHMMSS’
select now()+0;
-- 指定小数秒的精度  2018-04-19 01:55:46.658198
select now(6);

其它相同的还有

CURRENT_TIMESTAMP
CURRENT_TIMESTAMP()
LOCALTIMESTAMP
LOCALTIMESTAMP()
LOCALTIME
LOCALTIME()

2、CURTIME

说明

返回当前时间，只包含时分秒(fsp指定小数秒的精度，取值0--6)

语法格式
```
CURTIME([fsp])
```

示例代码

select curtime()
# 01:55:47
select curtime()+0
# 163301
select curtime(2)
# 01:55:47.90

2、CURDATE

说明

返回当前日期，只包含年月日

语法格式
```
CURDATE()
```
示例代码
```
select curdate()
select curdate()+0
```

3、选取日期时间的各个部分

说明

日期、时间、年、季度、月、日、小时、分钟、秒、微秒

示例代码

SELECT now(),date(now()); -- 日期
SELECT now(),time(now()); -- 时间
SELECT now(),year(now()); -- 年
SELECT now(),quarter(now()); -- 季度
SELECT now(),month(now()); -- 月
SELECT now(),week(now()); -- 周
SELECT now(),day(now()); -- 日
SELECT now(),hour(now()); -- 小时
SELECT now(),minute(now()); -- 分钟
SELECT now(),second(now()); -- 秒
SELECT now(),microsecond(now()); -- 微秒

2.6、转换函数

转换函数将值从一种数据类型转换为另外一种数据类型

1、date_format(date, format)

说明

把日期和数字转换为制定格式的字符串。format是格式化字符串

语法格式
```
date_format(date, format)
```

示例代码
对日期的处理

select date_format(now(), '%Y-%m-%d');
-- 2017-07-24
select date_format(now(), '%Y-%m-%d %H:%i:%s');
-- 2017-07-24 15:03:44

附表

	值	含义
秒	%S %s	两位数字形式的秒（ 00,01, ..., 59）
分	%i	两位数字形式的分（ 00,01, ..., 59）
小时	%H (%h %I)	24小时制， 12小时制
天	%d	两位数字表示月中天数（01,02, ...,31）
月	%m	两位数字表示月份（01,02, ...,12）
年	%Y %y	四位数字表示的年份（2017,2018...）两位数字表示的年份（15,16...）

2、str_to_date(str, format)

说明
```
将一个字符串变为DATE型数据
```

语法格式

str_to_date(str,[,format]) --注意：格式要和字符串日期的格式一致才能解析成功。

示例代码
将字符串日期转化为年月日

select str_to_date('2018-12-25', '%Y-%m-%d')

将字符串日期+时间转化成年与日时分秒

select str_to_date('2018-12-25 13:25:59', '%Y-%m-%d %H:%i:%s')
-- 注意年是大写‘Y’，小时也必须是大写‘H’ (如果其他为大写，则得到结果为null)

2.7、通用函数

1、ifnull

主要针对null值的处理，null加任何值等于null

说明

如果X为空，返回value，否则返回VALUE

语法格式

ifnull(col|字符串,VALUE) --相当于三则运算

示例代码
对工资是2000元以下的员工，如果没发奖金，每人奖金100元
```
SELECT ename,job,sal,ifnull(comm,100) FROM emp WHERE sal<2000;
```

三、分组函数

1、什么叫分组函数

对一组（多行）数据的处理计算并返回一行数据

聚合函数也叫分组函数，有的也叫集合函数，它的数据源一般来自多组数据，但返回的时候一般是一组数据，聚合函数对一组行中的某个列执行计算并返回单一的值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用,所以有的时候也把其称之为分组函数

2、分类

函数名称	返回值（结果）类型	说明
SUM(DISTINCT \| ALL 列名)	数值	对所有数值求和
COUNT(DISTINCT \| ALL 列名) 或者 COUNT(*)	数值	计数，求数据行数
MAX(DISTINCT \| ALL 数值日期列)	数值	求最大值
MIN(DISTINCT \| ALL 数值日期列)	数值	求最小值
AVG(DISTINCT \| ALL 数值列)	数值	求平均值

3、 SUM(求总和)

1、说明

ALL表示对所有值求和
DISTINCT表示只对不同值求和(相同值只取一次)

2、示例代码

计算雇员姓名为 'SMITH'和 'ALLEN' 两个人的基本薪资和
```
SELECT SUM(sal)
FROM emp
WHERE ename IN('SMITH','ALLEN');
```

4、 COUNT(统计行数)

1、说明

ALL对所有记录，数组做统计（默认）
DISTINCT只对不同值统计(相同值只取一次)
COUNT(DISTINCT | ALL 列名) ---------会忽略null值进行统计
或者 COUNT(*) --------------------------------不需要考虑null值问题（开发使用较多）

2、示例代码

显示emp表中的总条数据
```
SELECT COUNT(*)
FROM emp
```
统计 emp 职位类型的个数。
```
SELECT COUNT(DISTINCT job)
FROM emp;
```

统计 emp 职位为 SALESMAN 的雇员个数

SELECT COUNT(*)
FROM emp
WHERE job='SALESMAN';

统计 emp 中有佣金的雇员的个数
```
SELECT COUNT(comm)
FROM emp;
```

5、 MAX(求最大值)

1、说明

ALL表示对所有的值求最大值（默认）
DISTINCT表示对不同的值求最大值,相同的只取一次

2、示例代码

查询所有雇员中最高的薪资
```
SELECT　MAX(sal)
FROM emp;
```
显示所有工资不同的员工中工资最高的
```
SELECT MAX(DISTINCT SAL)
FROM EMP;
```

6、 AVG(求平均值)

1、说明

ALL表示对所有的值求平均值（默认）
DISTINCT表示对不同的值求最大值,相同的只取一次

2、示例代码

求所有员工工资的平均值
```
SELECT  (sal)
FROM  emp;
```
求不重复的员工工资的平均值
```
SELECT  AVG(DISTINCT sal)
FROM  emp;
```

7、MIN(求最小值)

1、说明

ALL表示对所有的值求最小值（默认）
DISTINCT表示对不同的值求最小值,相同的只取一次

2、示例代码

员工编号最小值
```
SELECT min(empno)
FROM emp
```
查询工资最低的
```
SELECT MIN(sal)
FROM emp
```

查询年薪最低的

SELECT * ,MIN((ifnull(comm,0) + sal)* 12 )
FROM emp

2.7 、注意事项

分组函数只能出现在select 列、having、order by子句中(不能出现在where后面)
当使用分组函数时，除了函数count(*)外，其他分组函数都会忽略NULL行。

2.8、单行函数和聚合函数的区别

单行函数操作时，根据函数的功能同时处理一行数据，返回每一行的处理结果；
聚合函数同时对分组后的一组行进行操作，返回分组后各组的处理结果

四、分组统计查询

1、概要

Group By语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。它的作用是通过一定的规则将一个数据集划分成若干个小的区域，然后针对若干个小区域进行数据处理。如果在查询的过程中需要按某一列的值进行分组,以统计该组内数据的信息时,就要使用group by子句。不管select是否使用了where子句都可以使用group by子句group by子句一定要与分组函数结合使用,否则没有意义。

2、语法格式

----语句------------------------------------------------------------执行顺序-----
SELECT [DISTINCT] * | 列名称 [别名] , 列名称 [别名] ,... | 统计函数   4、确定查询列
FROM 数据表 [别名] , 数据表 [别名] ,...                              1、数据来源
[WHERE 条件(s)]                                                    2、过滤数据行
[GROUP BY 分组字段, 分组字段, ...]                                   3、执行分组操作
[ORDER BY 字段 [ASC | DESC] , 字段 [ASC | DESC] ,...]               5、数据排序

3、示例代码

查询每个部门的人数

SELECT deptno ,COUNT(*)
FROM emp
GROUP BY deptno;

显示每个部门员工的平均工资

SELECT deptno ,AVG(sal) 平均工资
FROM emp
GROUP BY deptno;

显示各个部门员工的工资+奖金

SELECT deptno,SUM(sal + IFNULL(comm,0))
FROM emp
GROUP BY deptno;

按照部门编号分组，求出每个部门的人数，平均工资(要求截取2位)(配合单行函数使用)
```
SELECT deptno, COUNT(empno), ROUND(AVG(sal),2)
FROM emp
GROUP BY deptno;
```
按照职位分组，求出每个职位的最高和最低工资(单字段分组)
```
SELECT job, MAX(sal), MIN(sal)
FROM emp
GROUP BY job;
```
查询每个部门的每种岗位的平均工资和最低工资
```
SELECT AVG(sal), MIN(sal)
FROM emp
GROUP BY job;
```

先统计出各个职位(job)的平均工资(AVG),再统计平均工资最高的工资(分组函数嵌套)

SELECT MAX(AVG(sal))
FROM emp
GROUP BY job
注意:分组函数允许嵌套，但是嵌套之后的分组函数的查询之中不能再出现任何的其他字段

查询每个岗位的总工资但不包括'SALESMAN'岗位(配合Where使用)
```
SELECT
FROM emp
WHERE name !='SALESMAN'
```
按部门、不同的职位，统计员工的工资总额 (多字段统计)
```
SELECT deptno, job, sum(sal)
FROM emp
GROUP BY deptno, job;
```
查询各个部门中相同职位的员工人数并且按部门编号排序(多字段统计排序)
```
SELECT DEPTNO, JOB,COUNT(*)
FROM  emp
GROUP BY deptno,job
ORDER BY deptno;
```

4、注意事项

GROUP BY后不可以接列的别名（根据执行顺序分析就知道了）
```
SELECT  deptno dn ,AVG(sal)
FROM emp
GROUP BY dn;  --错误
```

GROUP BY 后不能接数字

SELECT  deptno dn ,AVG(sal)
FROM emp
GROUP BY 1;   --错误

GROUP BY 后不可以接select后没有的列名

SELECT  deptno dn ,AVG(sal)
FROM emp
GROUP BY job;

如果一个SELECT中使用了分组函数，任何不在分组函数中的列(表达式)必须要在GROUP BY中
```
SELECT  job ,deptno dn ,AVG(sal) --deptno列group by 后面没有，使用会报错
FROM emp
GROUP BY job;
```
笔记：3和4总结为一句话

1、在select中出现的列名必须在group by 中出现，否则，其他列名只能在分组函数中使用;而在group by 中出现的字段不一定要在select中出现
group by之前可以使用where过滤数据,因为where是在分组之前起作用的，(执行顺序分析) ----废话

5、使用HAVING过滤分组

1、说明

首先对数据行进行分组。
把所得到的分组应用到分组函数中。
最后显示满足having条件的记录
作用：在分组之后再过滤掉不符合条件的分组

2、与where的区别

只有having里面可以使用分组函数，where中不允许出现分组函数
相同作用——都是根据条件过滤数据；不同的是where是在分组之前过滤数据，having是分组之后过滤分组数据。
原则：能在where里过滤的数据就不要在having里面去过滤

3、语法格式

----语句-----------------------------------------------------------执行顺序---------
SELECT [DISTINCT] * | 列名称 [别名] , 列名称 [别名] ,... | 统计函数   5、确定查询列
FROM 数据表 [别名] , 数据表 [别名] ,...                              1、数据来源
[WHERE 条件(s)]                                                     2、过滤数据行
[GROUP BY 分组字段, 分组字段, ...]    [HIAVING 过滤分组]              3、执行分组操作
[HAVING 条件(s)]                                                    4、过滤分组数据
[ORDER BY 字段 [ASC | DESC] , 字段 [ASC | DESC] ,...]               6、数据排序

4、示例代码

查询部门的员工人数大于五部门编号

SELECT deptno,COUNT(*)
FROM emp
GROUP BY deptno
HAVING COUNT(*)> 5;

查询部门工资总和大于10000的部门编号

SELECT deptno, SUM(sal)
FROM emp
GROUP BY deptno
HAVING SUM(sal)>10000;

查询平均工资低于2000的部门号和它的平均工资

SELECT deptno,AVG(sal) a
FROM emp
GROUP BY deptno
HAVING avg(sal)>2000;

查询每个岗位的总工资并且不包括职位是'SALESMAN'岗位而且工资和大于5000
```
SELECT SUM(sal)
FROM emp
WHERE job!='SALESMAN'
GROUP BY job  HAVING SUM(sal)>5000
```

6、综合示例

查询非销售人员工作名称以及从事同一工作雇员的月工资的总和，并且要满足从事同一工作的雇员的月工资合计大于$5000，输出结果按月工资的合计升序排列
1、查询出所有的非销售人员的信息
```
SELECT * FROM emp WHERE job!=SALESMAN';
```
2、按照职位进行分组，并且使用SUM函数统计
```
SELECT job,SUM(sal)
FROM emp
WHERE job<>'SALESMAN'
GROUP BY job;
```
3、月工资的合计是通过统计函数查询的，所以现在这个对分组后的过滤要使用HAVING子句完成
```
SELECT job,SUM(sal)
FROM emp
WHERE job!='SALESMAN'
GROUP BY job
HAVING SUM(sal)>5000;
```
4、按照升序排列
```
SELECT job,SUM(sal) sum
FROM emp
WHERE job!='SALESMAN'
GROUP BY job
HAVING SUM(sal)>5000
ORDER BY sum ASC;
```

显示部门编号不是30的，的部门详细信息（部门编号、部门名称、部门人数、部门月薪资总和），并要求部门月工资总和大于8000，输出结果按部门月薪资的总和降序排列。

SELECT d.deptno,d.dname,COUNT(*) 人数,ifnull(SUM(e.sal),0) 月总收入
FROM dept d,emp e
WHERE d.deptno=e.deptno AND d.deptno!=30
GROUP BY d.deptno,d.dname
HAVING SUM(e.sal) >8000
ORDER BY SUM(e.sal) DESC;
或
select deptno,d.dname ,count(*) peonum,sum(e.sal) s
from dept d left join emp e using(deptno)  --注意:using()中的字段在使用时不能有前缀。
where deptno !=30
group by deptno ,d.dname
having sum(e.sal)>8000
order by s desc;

7、性能问题

能在where能过滤数据不要在having里过滤，A和B都能达到同样的目的，但是A性能相对好一些，因为A现将deptno=30的数据筛选出来，然后在将筛选的数据放入到临时表空间内进行分组；而B将全部的数据都读到临时表空间内，然后在临时表空间进行筛选数据，这样一来B就需要更大的临时表空间进行分组筛选，索引性能较差。

6、Mysql查询（函数）

一、概要

二、单行函数

1、定义

2、分类

2.3、字符函数

1、UPPER

2、LOWER

3、REPLACE

4、LENGTH

5、INITCAP

6、SUBSTR（注意：从0和1开始截取都是从字符串的第一位开始）

2.4、数值函数

1、ROUND

2、TRUNCATE

3、MOD

2.5、日期函数

1、NOW：

2、CURTIME

2、CURDATE

3、选取日期时间的各个部分

2.6、转换函数

1、date_format(date, format)

2、str_to_date(str, format)

2.7、通用函数

1、ifnull

三、分组函数

1、什么叫分组函数

2、分类

3、 SUM(求总和)

1、 说明

2、示例代码

4、 COUNT(统计行数)

1、 说明

2、 示例代码

5、 MAX(求最大值)

1、 说明

2、 示例代码

6、 AVG(求平均值)

1、 说明

2、 示例代码

7、MIN(求最小值)

1、 说明

2、 示例代码

2.7 、注意事项

2.8、单行函数和聚合函数的区别

四、分组统计查询

1、概要

2、语法格式

3、示例代码

4、注意事项

5、使用HAVING过滤分组

1、说明

2、与where的区别

3、语法格式

4、示例代码

6、综合示例

7、性能问题

1、说明

1、说明

2、示例代码

1、说明

2、示例代码

1、说明

2、示例代码

1、说明

2、示例代码