mysql 常用函数总结

一、各种函数总结：

1、聚合函数：

count() 、sum()、avg()、max()、min()

2、算数函数

1. 绝对值： ABS(数值字段)
2. 求余 ： MOD(被除数，除数)
3. 四舍五入： ROUND(对象数值，保留小数位数)

3、字符串函数

1. 拼接：字符串1 || 字符串2
2. 字符串长度：LENGTH(字符串)
3. 小写转换：LOWER(字符串)
4. 大写转换：UPPER(字符串)
5. 字符串替换：REPLACE(对象字符串,替换前的字符串,替换后的字符串)。 原字符串发生变化。
6. 字符串截取：Mysql版本：SUBSTRING(对象字符串 FROM  截取的起始位置 FOR  截取的字符数)；Oracle版本：SUBSTR(对象字符串, 截取的起始位置 ，截取的字符数)。 原字符串不变。截取的起始位置 ：是指第几个开始！从1开始算。

4、日期、时间函数

• 当前日期：CURRENT_DATE   格式为：’2019-06-18’
• 当前时间：CURRENT_TINE   格式为：‘23:54:43’
• 当前日期和时间：CURRENT_TIMESTAMP、current_timestamp()、now() 、sysdate()格式为：‘2019-06-18 23:56:00’   

sysdate() 日期时间函数跟 now() 类似，不同之处在于：now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值。如：select now(), sleep(3), sysdate();

 

• 截取日期元素：EXTRACT(日期元素 from 日期)

如：Select  extract(year  from  CURRENT_TIMESTAMP)  as  year  from  test;

• 日期、时间转换为字符串函数：date_format(date,format)、 time_format(time,format) 能够把一个日期/时间转换成各种各样的字符串格式。

如：select date_format('2008-08-08 22:23:01', '%Y%m%d%H%i%s');

• 字符串转换为日期函数： str_to_date(str,format) 。

如：select str_to_date('08.09.2008 08:09:30', '%m.%d.%Y %h:%i:%s')

• （时间、秒） 转换函数：time_to_sec(time)、sec_to_time(seconds)

如：

      select time_to_sec('01:00:05'); -- 3605
      select sec_to_time(3605); -- '01:00:05'

• unix时间戳 转换 日期 函数：from_unixtime(unix_timestamp)、from_unixtime(unix_timestamp,format)

• 时间戳（timestamp）转换、增、减函数：

  timestamp(date) -- date to timestamp
  timestamp(dt,time) -- dt + time
  timestampadd(unit,interval,datetime_expr) --
  timestampdiff(unit,datetime_expr1,datetime_expr2) --

• 日期 转换 时间戳 函数：unix_timestamp()、unix_timestamp(date)
• 日期时间计算函数：date_add()、date_sub()、datediff(date1,date2)、timediff(time1,time2)

如：为日期加上（date_add）、或 减去（date_sub）一个时间间隔

select date_add(now(), interval 1 day); -- add 1 day
select date_add(now(), interval 1 hour); -- add 1 hour
select date_add(now(), interval 1 minute); -- ...
select date_add(now(), interval 1 second);
select date_add(now(), interval 1 microsecond);
select date_add(now(), interval 1 week);
select date_add(now(), interval 1 month);
select date_add(now(), interval 1 quarter);
select date_add(now(), interval 1 year);

select date_add(now(), interval '1 01:15:30' day_second);
select date_add(now(), interval '01:15:30' hour_second);

日期、时间相减函数：datediff(date1,date2), timediff(time1,time2)

5、转换函数

1. 类型转换：CAST(转换前的值 AS  想要转换的数据类型)

如：

Select  cast(‘0001’  as  integer)  as int_tool;

6、窗口函数

也称为：OLAP函数。即OneLine  Analytical Processing 意思是对数据库数据进行实时分析处理。

该功能为标准sql 功能，但mysql现在还不支持，包括mysql5.7。

能作为窗口函数使用的函数：

1. 能作为窗口函数使用的聚合函数：count() 、sum()、avg()、max()、min()。
2. Rank 、dense_rank、row_number等专用窗口函数。
   1. 语法：<窗口函数>  over  ( [partition by 列清单]  order by 排序用列清单)

注：

• [] 中的内容可以省略。
• 窗口函数拥有 分组、排序功能。
• partition by  设定范围。分组后的记录集合称为 窗口。

1. order by  指定按照那一列、何种顺序排序。

例如：

根据商品类型，按价格从低到高排序：

Select product_name, product_type, sale_price

Rank()  over (partition  by  product_type  order by  sale_price )  as ranking From product;

• 专用窗口函数种类：

Rank ：计算排序时，如果存在相同位次的记录，则会跳过之后的位次。

如： 1,1,1,4

Dense_rank：计算排序时，如果存在相同位次的记录，不会跳过之后的位次。

如：1,1,1,2

Row_number：唯一的连续位次。

如：1,2,3,4

• 窗口函数的适用范围

原则上窗口函数只能在select 子句中使用。

语法上：除了select 子句，order by子句，update 语句的set子句中也可以使用，但几乎没有业务示例。

7、Grouping运算符

根据商品类型分配，并得出各类商品的汇总价，以及所有商品合计总价。

方法1：

Select  product_type  as ‘合计’，sum（sale_price） from product

Union All

Select  product_type ，sum（sale_price） from product group by product_type ；

执行结果：

product_type       SUM

合计   11000

衣服   6000

办公品   5000

Grouping 预算符是标准sql的语法，包含以下三种：

ROLLUP ：同时得出合计、小计。

语法：Group  by  Rollup(列1，列2，...)

注意：

Mysql执行时，语法：Group  by 列1，列2，...  with Rollup;

grouping函数 区分null

Grouping(列1)  as  列1

区分分组记录中的null 和 原始数据的null.

分组后的null时，返回1。其他情况返回0.

8、Case表达式

Sql-92标准引入的。

语法：

    简单case表达式：

     Case sex

     When  ‘1’  then  ‘男’

     When  ‘2’  then  ‘女’

      Else ‘其他’end

   搜索case表达式：

        Case When  sex=‘1’  then  ‘男’

                 When  sex=‘2’  then  ‘女’

                  Else ‘其他’end

 

case表达式使用情况说明：

1. 将已有编号方式转换为新的方式并统计

SELECT  case pref_name

when '德岛' then '四国'

when '香川' then '四国'

when '爱媛' then '四国'

when '高知' then '四国'

when '福冈' then '九州'

when '佐贺' then '九州'

when '长崎' then '九州'

else '其他' end as district,SUM(population)

from poptbl

group by

 case pref_name

when '德岛' then '四国'

when '香川' then '四国'

when '爱媛' then '四国'

when '高知' then '四国'

when '福冈' then '九州'

when '佐贺' then '九州'

when '长崎' then '九州'

else '其他' end;

以上方式select 、group by 两处需要写一样的case表达式！

 

以下为简化写法，但违反sql标准，group by子句比select先执行。但mysql、postgresql支持该写法。

SELECT  case pref_name

when '德岛' then '四国'

when '香川' then '四国'

when '爱媛' then '四国'

when '高知' then '四国'

when '福冈' then '九州'

when '佐贺' then '九州'

when '长崎' then '九州'

else '其他' end as district,SUM(population)

from poptbl

group by district;

 

2、分类并多维度统计

 

统计各地区 男女人员数量：

SELECT

     pref_name,

    SUM(case when sex='1' then population else 0 end) as 男,

    SUM(case when sex='2' then population else 0 end) as 女

FROM

    poptbl2

GROUP BY

    pref_name;

 

9、mysql时间与字符串之间相互转换

• 时间转字符串

DATE_FORMAT(日期，格式字符串)

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');

• 字符串转时间

STR_TO_DATE(字符串，日志格式)

SELECT STR_TO_DATE('2019-01-20 16:01:45', '%Y-%m-%d %H:%i:%s');

• 时间转时间戳

select unix_timestamp(now());

• ​​​​​​​字符串转时间戳

select unix_timestamp('2019-01-20');  

• ​​​​​​​时间戳转字符串

select from_unixtime(1451997924,'%Y-%d');

• 日期格式如下：

%M 月，名字(January……December)  

%W 星期，名字(Sunday……Saturday)  

%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）  

%Y 年, 数字, 4 位  

%y 年, 数字, 2 位  

%a 缩写的星期，名字(Sun……Sat)  

%d 月份中的天数, 数字(00……31)  

%e 月份中的天数, 数字(0……31)  

%m 月, 数字(01……12)  

%c 月, 数字(1……12)  

%b 缩写的月份，名字(Jan……Dec)  

%j 一年中的天数(001……366)  

%H 小时(00……23)  

%k 小时(0……23)  

%h 小时(01……12)  

%I 小时(01……12)  

%l 小时(1……12)  

%i 分钟, 数字(00……59)  

%r 时间,12 小时(hh:mm:ss [AP]M)  

%T 时间,24 小时(hh:mm:ss)  

%S 秒(00……59)  

%s 秒(00……59)  

%p AM或PM  

%w 一个星期中的天数(0=Sunday ……6=Saturday ）  

%U 星期(0……52), 这里星期天是星期的第一天  

%u 星期(0……52), 这里星期一是星期的第一