字符函数
其他函数
控制函数
- null在数学运算中的处理：ifnull
- 用case when 还是 if?
取整函数：trunc,round,ceil,floor,sign,abs
分组函数：sum,avg,count,min,max
日期时间函数
- 查询日期时间
- 提取时间
+加号的作用
计算函数
分组函数

字符函数

字符截取：SUBSTRING，SUBSTRING_INDEX

left、right截取固定长度
1、left（name,4）截取左边的 4 个字符：SELECT LEFT(201809,4) 年结果：2018
2、right（name,2）截取右边的 2 个字符：SELECT RIGHT(201809,2) 月份结果：09
substring指定开始位置固定长度
3、SUBSTRING(name,5,3) 截取 name 这个字段从第五个字符开始只截取之后的 3 个字符
SELECT SUBSTRING('成都融资事业部',5,3)结果：事业部
4、SUBSTRING(name,3) 截取 name 这个字段从第三个字符开始，之后的所有个字符
SELECT SUBSTRING('成都融资事业部',3)结果：融资事业部
5、SUBSTRING(name, -4) 截取 name 这个字段的第 4 个字符位置（倒数）开始取，直到结束
SELECT SUBSTRING('成都融资事业部',-4)结果：资事业部
6、SUBSTRING(name, -4，2) 截取 name 这个字段的第 4 个字符位置（倒数）开始取，只截取之后的 2 个字符
SELECT SUBSTRING('成都融资事业部',-4,2)结果：资事
注意：我们注意到在函数 substring(str,pos, len) 中， pos 可以是负值，但 len 不能取负值。
substring_index指定分隔符和位置
7、substring_index(‘www.baidu.com’, ‘.’, 2) 截取第二个 ‘.’ 之前的所有字符
SELECT substring_index('www.baidu.com', '.', 2)结果：www.baidu
8、substring_index(‘www.baidu.com’, ‘.’, -2) 截取第二个 ‘.’ （倒数）之后的所有字符
SELECT substring_index('www.baidu.com', '.', -2)结果：baidu.com
9、SUBSTR(name, 1, CHAR_LENGTH(name)-3) 截取 name 字段，取除 name 字段后三位的所有字符
SELECT SUBSTR('成都融资事业部', 1, CHAR_LENGTH('成都融资事业部')-3)结果：成都融资

原文链接：https://www.cnblogs.com/duanc/archive/2018/04/09/8760372.html
https://blog.csdn.net/album_gyd/article/details/81365573

字符拼接：concat,concat_ws,group_concat

CONCAT()
作用：将多个结果作为字符串拼接在一起
语法：CONCAT(str1,str2...)
注意：如果说要拼接的字符串中有一个是null，那么返回结果就是null。所以，我们通常配合使用IFNULL
CONCAT_WS()
作用：将多个结果拼接在一起，使用指定的分隔符
语法：CONCAT_WS(separator, str1, str2, ...)
注意：

如果说要拼接的字符串中有null，返回结果也不会是null
分隔符为NULL，则返回结果为NULL
如果参数中存在NULL，则会被忽略
可以对NULL进行判断，并用其它值进行替换

组内拼接元素：group_concat
作用：将多行的字符串按分组整合成一个字符串，必须配合group使用
语法：group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
注意：如果我们不指定分隔符，该语法默认是使用逗号分隔的
```
SELECT
group_concat( DISTINCT `a`.`区域` ORDER BY `a`.`区域` ASC SEPARATOR ',' ) AS `区域`,
FROM table_name `a`
GROUP BY `a`.`分组列`
```
数字转字符：str,convert,cast
str: 适用于整数，不适用于浮点数（不推荐）
convert(目标类型，要转换的数字)
cast(要转换的数字 as 目标类型)

declare @score float;
set @score=1.6;
select convert(varchar(5),@score);

declare @score float;
set @score=1.6;
select cast(@score as varchar(5));

字符替换：replace

在 mysql 中某些字段比如，url 地址，需要将 http 替换为 https，无需编写程序，只需要执行 update 语句即可

-- 语法
UPDATE `表名` SET `字段名` = REPLACE (`字段名`, '待替换字符串', '替换成此字符串');
-- 实例
UPDATE `app_product` SET `product_url` = REPLACE (`product_url`, 'http:', 'https:');

执行完成，即可将 http，替换成 https，这里最好添加上后面的 “:”，缩小替换范围，防止误替换。
Length
concat
substr
instr
Trim
upper
lower
Lpad
rpad
replace

日期时间函数
curdate
curtime
Now
Year
month
monthname
Day
hour
minute
second
str_to_date
date_format

其他函数

Version
Database
user

控制函数

null在数学运算中的处理：ifnull

当参与数学运算的字段有一个字段值为null时，则数学运算的结果都为null，为了避免这种情况，可以使用ifnull函数对可能值为null的字段单独处理：ifnull(字段名,0)

用case when 还是 if?

区别：

if函数只能用作单个条件判断，case when函数可以用作多个条件判断。
在使用case when函数的时候一定要加上else，否则不满足case when的其它数据都将被null值所替代

测试：

2500条记录case when: 耗时1.67-1.81s
2500条记录if: 耗时1.651.81s

使用建议：

从简洁考虑，尽量使用if函数替代case when函数，如果必须使用case when函数，一定要考虑else的情况。
取整函数：trunc,round,ceil,floor,sign,abs

trunc（value,precision）按精度 (precision) 截取某个数字, 不进行舍入操作。
round（value,precision）根据给定的精度 (precision) 输入数值。
ceil (value) 产生大于或等于指定值（value）的最小整数。
floor（value）与 ceil（）相反，产生小于或等于指定值（value）的最小整数。
sign(value) 与绝对值函数 ABS（）相反。ABS（）给出的是值的量而不是其符号，sign(value) 则给出值的符号而不是量。
Truncate

Mod
实例：对应结果

CEIL(23.33): 24
FLOOR(23.33): 23
ROUND(23.33)： 23
TRUNC(23.33)： 23
SIGN(-23.33)： -1

分组函数：sum,avg,count,min,max
分组函数用于统计，也称聚合函数、组函数
sum 求和：
avg 平均：
count 计数：
min 最小：
max 最大：

注：

sum avg一般用于处理数值型，count min max可以处理任何类型
以上函数都忽略NULL
与distinct搭配去重运算：sum(distinct 字段)
分组函数应用字段一般是group by后面的字段

count的详细介绍：
count(字段)
count(*)：一行任意一字段不为NULL则被统计
count(1)：统计行数，相当于加了一列常量值再计数
效率：

MYISAM count(*)效率高
INNODB count(*)和count(1)的效率差不多，比count(字段)要高一些
日期时间函数
日期转时间戳：
日期转五位数：timestampdiff(day,’1899-12-30’,NOW())
计算两个日期相隔天数：timestampdiff(day,’1899-12-30’,NOW())

查询日期时间

参考文：https://blog.csdn.net/ls1645/article/details/79118464
时间类型

date : 参数是年月日格式，between ‘2021-05-24’ and ‘2021-05-31’
datetime/timestamp : 参数是年月日时分秒格式，between ‘2021-05-24 00:00:00’ and ‘2021-05-31 23:59:59’

对于datetime的查询

-- 今天  
select * from table1 where to_days(recordtime) <= to_days(now()); 
-- 昨天 
select * from table1 where to_days(NOW()) - TO_DAYS(recordtime) <= 1;
-- 近7天  
select * from table1 where date_sub(CURDATE(),INTERVAL 7 DAY) <= DATE(recordtime);  
-- 近30天  
SELECT * FROM table1 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(recordtime);
-- 本月  
SELECT * FROM table1 WHERE DATE_FORMAT( recordtime, '%Y%m' ) = DATE_FORMAT( CURDATE() , '%Y%m' );
-- 上一月  
SELECT * FROM table1 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( recordtime, '%Y%m' ) ) =1; 
-- 查询本季度数据  
select * FROM table1 where QUARTER(recordtime)=QUARTER(now()); 
-- 查询上季度数据  
select * FROM table1 where QUARTER(recordtime)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));  
-- 查询本年数据  
select * FROM table1 where YEAR(recordtime)=YEAR(NOW());  
-- 查询上年数据  
select * FROM table1 where year(recordtime)=year(date_sub(now(),interval 1 year));  
-- 查询距离当前现在6个月的数据  
select * FROM table1 where recordtime between date_sub(now(),interval 6 month) and now();  
-- 查询当前这周的数据  
SELECT * FROM table1 WHERE YEARWEEK(date_format(recordtime,'%Y-%m-%d')) = YEARWEEK(now());  
-- 查询上周的数据  
SELECT * FROM table1 WHERE YEARWEEK(date_format(recordtime,'%Y-%m-%d')) = YEARWEEK(now())-1;  
-- 查询上个月的数据   
select * FROM table1 where date_format(recordtime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m'); 
-- 查询当前月份的数据
select * FROM table1 where DATE_FORMAT(recordtime,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m');
select * FROM table1 where date_format(recordtime,'%Y-%m')=date_format(now(),'%Y-%m'); 
-- 查询指定时间段的数据
select * FROM table1 where recordtime between  '2017-1-1 00:00:00'  and '2018-1-1 00:00:00';   
select * FROM table1 where recordtime >='2017-1-1 00:00:00'  and recordtime < '2018-1-1 00:00:00';

获取系统当前时间：select CURDATE(); SELECT NOW();
获取时间差：PERIOD_DIFF() DATEDIFF(date1,date2) TIMEDIFF(time1,time2)，TIMESTAMPDIFF（）
日期加减：DATE_SUB() DATE_ADD() ADDDATE() ADDTIME() PERIOD_ADD(P,N)

UNIX 时间戳
: 每个时间对应了一个时间戳，该时间戳从 ‘1970-01-01 00:00:00’ 为0开始计时，每秒增加1

时间戳与传统时间互换函数 ```sql — 函数 UNIX_TIEMSTAMP(datetime) FROM_UNIXTIME(unixtime)

SELECT UNIX_TIMESTAMP(‘20010-03-01 00:00:00’) — 返回 1267372800 SELECT FROM_UNIXTIME(1267372800) — 返回 ‘2010-03-01 00:00:00’

select count(*) from sometable where datetimecolumn>=UNIX_TIMESTAMP(‘2010-03-01 00:00:00’) and
datetimecolumn<UNIX_TIMESTAMP(‘2010-03-02 00:00:00’)


- 时间戳优点：通过时间戳建立索引能大大提高效率，有利于快速查询
   - 查询时，把起点时间与结尾时间转换为 UNIX 时间再进行比较
   - 也可在调用程序中先转换为 UNIX 时间再传入 MySQL

日期函数：今天昨天
```sql
-- 今天日期+时间
select now() ;    --返回2018-04-25 17:33:21
-- 今天日期
select curdate();  --返回2018-04-25
-- 今天日期格式化
select date_format(curdate(),'%Y-%m-%d');  --返回2018-04-25

-- 昨天日期
select date_add(curdate(), interval -1 day)；  --返回2018-04-24
-- 昨天日期格式化
select date_add(date_format(curdate(),'%Y-%m-%d'), interval -1 day)；  2018-04-24
hive
日期增加和减少date_sub (string startdate, int days)
current_date() 当前日期
date_sub(current_date(),1) 昨天日期
select date_add('2017-01-31',2); --返回 2017-02-02
select date_sub('2017-02-02',2);  --返回 2017-01-31

提取时间

Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AMSelect CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06
Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06
Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06
Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06
Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46
Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM
Select CONVERT(varchar(100), GETDATE(), 10): 05-16-06
Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16
Select CONVERT(varchar(100), GETDATE(), 12): 060516
Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937
Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967
Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157
Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 101): 05/16/2006
Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16
Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006
Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006
Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006
Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006
Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006
Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM
Select CONVERT(varchar(100), GETDATE(), 110): 05-16-2006
Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
Select CONVERT(varchar(100), GETDATE(), 112): 20060516
Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513
Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547
Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49
Select CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49.700
Select CONVERT(varchar(100), GETDATE(), 126): 2006-05-16T10:57:49.827
Select CONVERT(varchar(100), GETDATE(), 130): 18 ???? ?????? 1427 10:57:49:907AM
Select CONVERT(varchar(100), GETDATE(), 131): 18/04/1427 10:57:49:920AM
-- 如此可以采用：
select CONVERT(VARCHAR(100),GETDATE(),111)
select CONVERT(VARCHAR(100),GETDATE(),23)
-- 当然可以通过 ：
Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46
Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967
Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547

+加号的作用

+号的作用：在mysql只有运算功能，没有字符拼接功能
连接两个字段：~~select 字段1+字段2 as 别名~~;此命令错误

select 100+90;两个操作数都为数值型，则做加法运算
select '123'+90;其中一方为字符型，试图将字符型数值转换为数值型，如果转换成功，则继续做加法运算，如果转换失败，则将字符型数值转换为0，再做加法运算
select null+10;只要其中一个为null，则结果肯定为null

计算函数
拼接字段：concat()函数——MySQL用法，其他SQL用 + 或 || 拼接
去掉空格：trim()、rtrim()、ltrim()函数
使用别名：关键字as，有了别名在客户机才可引用此列，就行真实存在；也可对列重命名。别名也称导出列。
执行算术计算：同数据类型的列，+ - \ ; 测试计算：select 32;
文本函数：
soundex()：模糊匹配发音相似
日期时间函数：
- 标准日期格式：yyyy-mm-dd
数值处理函数：
聚集函数：
- 聚集不同值：先使用distinct去重，但用于max()、min()时没有价值（意义）
- 取别名利于理解和使用
数字格式化：
- format(number, length)：保留几位小数
- lpad/rpad(value,length,fill_value)：给定位数，不足补充自定义字符
- 取整、取余：5 div 2 = 2，5 mod 2 = 1 ；mod(5,2)
- 四舍五入：round(1.5) = 2
  分组函数
搭配rollup,cube使用，用于辅助过滤汇总行
- grouping(col1)：如果col1为空，返回1，不为空，返回0
- grouping(A),grouping(B),grouping_id(A,B)：grouping_id的值取决于前两项，一个为1，一个为0，此项为3（十进制，二进制为10） ```sql select concat(column1,’-‘,column2) as column3_name from table1; select trim(column1) from table1; select column1column2 as column3 from table1; select 32; select now();

select from table1 where soundex(column1) = soundex(‘Y Lie’); — 匹配实例：Y Lee select from table1 where date = ‘2021-08-21’; — 不匹配含时间的数据，可用Date()处理 select * from table1 where date(date) = ‘2021-08-21’; — date()提取日期，time()提取时间

— 查询2005年9月的数据，不能用运算符 select from table1 where date(date) between ‘2005-09-1’ and ‘2005-09-30’; — 方法一：需要知道起始日期 select from table1 where year(date) = 2005 and month(date) = 9; — 方法二：不用记起始日期

select avg(column1) as avg_column1 from table1 where column2 = 1003; — avg()忽略列值为NULL的行 select count() as num from table1; — count()行计数，不忽略NULL行 select count(column1) as num from table1; — 具体列名，则忽略NULL行 select max(column1) as max_column1 from table1; — 一般用于数值或日期值，用于文本时（排序），返回最后一行，忽略NULL行 select min(column1) as min_column1 from table1; — 与max规则一样，用于文本返回最前面的行 select sum(column1) as sum_column1 from table1; — 忽略NULL行 select sum(column1column2) as sum_columns from table1; select sum(column1+column2) as sum_columns from table1; select avg(distinct column1) as avg_column1 from table1 where column2 = 1003; — 只对不同值取平均 select sum(column1) as sum_column1,avg(column1) as avg_column1 from table1;

```

📚 SQL 教程

【SQL】常见函数

字符函数

字符截取：SUBSTRING，SUBSTRING_INDEX

字符拼接：concat,concat_ws,group_concat

组内拼接元素：group_concat

数字转字符：str,convert,cast

字符替换：replace

其他函数

控制函数

null在数学运算中的处理：ifnull

用case when 还是 if?

取整函数：trunc,round,ceil,floor,sign,abs

分组函数：sum,avg,count,min,max

日期时间函数

查询日期时间

提取时间

+加号的作用

计算函数

分组函数