SQL函数

1.通用函数

1.Group by函数

作用：分组函数，根据一个或多个列对结果集进行分组，GROUP BY语句可与集合函数（COUNT，MAX，MIN，SUM，AVG）一起使用。having是分组后进行条件筛选，类似select where 结构

语法: group by 字段1，字段2 having 条件

例子：

（1）按单字段分组

#查询每个部门的员工数量
SELECT department_id,COUNT(1) 
FROM employees 
GROUP BY department_id ;

（2）按多字段分组

#查询每个部门，每个职务的平均工资
SELECT AVG(salary),department_id,job_id 
FROM employees 
GROUP BY department_id,job_id;

（3）按函数分组

#按名字长度分组，并筛选出数量大于5的名字长度
SELECT COUNT(1) c,LENGTH(last_name) len_name 
FROM employees 
GROUP BY len_name 
HAVING c>5;

注意：

1.查询内容中的字段，必须要出现在分组列表中，也就是select的字段必须出现在group by后面，集合函数里的字段就不需要了。

2.多个列分组情况下，分别以每个字段的不同组合分组。

例子： item=数学 sex=1 和 item=数学 sex=2 就会分到不同组中，以此达到计算和目的，会依次按照顺序分，先把第一个字段相同的划分为一组，再这些相同的字段中，再查找第二个字段相同的划分为一组。

2.concat函数

作用：是将括号中的字符串连接起来，但是有一定的规则就是：在连接字符串的时候，只要其中一个是NULL,那么将返回NULL！

语法：concat(字符串1,字符串2,…)

例子：SELECT * FROM USER name like concat(’%’, #{name}, ‘%’)

3.sum函数

作用：用于计算某一字段中所有行的数值之和

语法：sum(字段)

例子：select sum(user_id) from user

PS：

该字段数据得是数值，最好是数字类型的；字符串类型也可以，但在sqlserver得用cast(字段 as int)转换，Oracle和mysql就无需转换；

sum 求和时会对 null 进行过滤，不计算；

如果在没有返回行中使用 sum() 函数，sum 函数的返回值为 null，不是 0；

4.count函数

作用：统计查询结果中的行数

语法：count(字段名)

例子：select count(user_id) from user

PS：

count 会忽略掉 NULL 的结果，所以 count(字段名) 这样使用的话，如果字段中包含为null的结果，将导致查询结果不准确。所以如果要查询结果行数可以使用count(1)和count(*)

count(1)和count(*)的区别：count(1)比count(*)效率更高

5.max函数

作用：统计一列中的最大值。NULL 值不包括在计算中

语法：max(字段)

例子：select max(user_id) from user

PS：

max函数可用于字符型数据，对字符型数据的最大值，是按照首字母由A～Z的顺序排列，越往后，其值越大。当然，对于汉字则是按照其全拼拼音排列的，若首字符相同，则比较下一个字符，以此类推；

max函数也可用于日期时间类型的数据，其大小排列就是日期时间的早晚，越早认为其值越小；

max函数忽略NULL值。但是，如果在该列中，所有行的值都是NULL，则函数将返回NULL值;

6.min函数

作用：统计一列中的最小值。NULL 值不包括在计算中

语法：min(字段)

例子：select min(user_id) from user

PS：

min函数可用于字符型数据，对字符型数据的最大值，是按照首字母由A～Z的顺序排列，越往后，其值越大。当然，对于汉字则是按照其全拼拼音排列的，若首字符相同，则比较下一个字符，以此类推；

min函数也可用于日期时间类型的数据，其大小排列就是日期时间的早晚，越早认为其值越小；

min函数忽略NULL值。但是，如果在该列中，所有行的值都是NULL，则函数将返回NULL值;

7.avg函数

作用：统计数值列的平均值。NULL 值不包括在计算中

语法：avg(字段)

例子：select avg(user_id) from user

2.oracle函数

1.to_char函数

作用：是将日期转换成字符串

语法：to_char(字段名, ’字符串格式’)

例子：SELECT * FROM USER where to_char(create_time,‘yyyy-MM-dd HH24:mi:ss’) like ‘%20%’

2.to_date函数

作用：是将字符串转换为数据库中的日期类型

语法：to_date(字符串,’字符串格式’)

例子：SELECT * FROM USER where create_time = to_date(‘2020-08-08’,‘yyyy-MM-dd’)

3.to_number函数

作用：是将字符串以fmt格式转换为数字

语法：to_number(字符串,‘格式’)。如不加格式，可直接转换为数字，前提是必须是数字字符串。

函数中有很多预定义的固定格式：

格式值	含义
9	代表一个数字
0	强迫0显示
$	显示美元符号
L	强制显示一个当地的货币符号
.	显示一个小数点
,	显示一个千位分隔符号

例子：

1.SELECT * FROM USER where id = to_number(‘5’);

2.SELECT * FROM USER where id = to_number(‘RMB23’,’L99′);

4.nvl函数

作用：判断是否为null，不为null值进行处理

语法：NVL(a1,a2) 如果a1为null，返回a2，否则返回a1；

​ NVL2(a1,a2,a3) 语法：如果a1不为null，返回a2；a1为null，返回a3；

例子：select NVL(sum(user_id),0) from user

3.mysql函数

1.ifnull函数

作用：判断是否为null，不为null值进行处理

语法：ifnull(expr1,expr2) 如果expr1不是null，ifnull()返回expr1，否则它返回expr2；

​ if(expr1,expr2,expr3)如果expr1不是null， if()返回expr2， 否则 if() 返回expr3；

例子：select IFNULL(sum(user_id),0) from user

2.TIMESTAMPDIFF函数

作用：计算两个日期的差值
语法：TIMESTAMPDIFF（时间单位，参数1，参数2）
可以设置参数，可以精确到天（DAY），小时（HOUR），分钟（MINUTE），秒（SECOND），毫秒（FRAC_SECOND（低版本不支持，可以用秒，再乘1000）），单位为第一个参数设置的值
例子：

    --相差1天
　　select TIMESTAMPDIFF(DAY, '2018-03-20 23:59:00', '2015-03-22 00:00:00');
　　--相差49小时
　　select TIMESTAMPDIFF(HOUR, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
　　--相差2940分钟
　　select TIMESTAMPDIFF(MINUTE, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
　　--相差176400秒
　　select TIMESTAMPDIFF(SECOND, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
　　--查询出用户在某天通话时间大于15分钟的记录
    SELECT * FROM 表 where TIMESTAMPDIFF(MINUTE, 开始时间字段, 结束时间字段) >15;
    --日期提前五分钟提醒的数据
    SELECT * FROM 表 where TIMESTAMPDIFF(MINUTE, 规定日期字段, sysdate()) >5;

3.DATEDIFF函数

作用：计算两个日期的天数，返回值为相差的天数，int类型，不能定位到时分秒，固定天数为单位
语法：DATEDIFF（参数1，参数2）
注意：参数1-参数2为正，所以参数1为结束时间，参数2为开始时间

   --查询七天内的数据
   SELECT * FROM 表 where DATEDIFF(sysdate(),开始时间字段) <7;

4.sql常见问题

1.字段为日期格式，模糊搜索不可用 like ‘数据%’，必须先把字段转化为字符串，再使用like模糊搜索

如在Oracle数据库中： to_char(create_time,‘yyyy-MM-dd HH24:mi:ss’) like ‘%20%’