SQL函数

我就是我 2022-12-08 04:21 141阅读 0赞

# 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(字符串,‘格式’)。如不加格式，可直接转换为数字，前提是必须是数字字符串。

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

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

例子：

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%’