mysql 常用函数总结

拼搏现实的明天。 2023-10-05 11:29 25阅读 0赞

## 一、**各种函数总结**： ##

### **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), 这里星期一是星期的第一

mysql 常用函数总结

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关 mysql 常用函数总结

相关 mysql 常用聚合函数总结

相关 MySQL常用函数大全（总结篇）

相关 mysql常用函数

相关 MySQL常用函数总结

相关 MySQL常用函数

相关 Mysql常用函数总结

相关 MySQL常用函数

相关 MySQL：常用函数

相关 mysql常用函数