3 常见函数

一、字符函数

1、CONCAT　　拼接函数

CONCAT(str1,str2,...)

2、LENGTH　　获取字节长度

PS：字节以及字符与编码方式（ASCII、UTF-8等）有关

LENGTH(str)

举例：
SELECT LENGTH('SS');

查询结果为2

3、CHAR_LENGTH　　获取字符长度

CHAR_LENGTH(str)

举例：
SELECT CHAR_LEGTH('SS');

结果为2

4、SUBSTRING　　截取子串

（1）SUBSTR(str,pos)    --从pos位置开始，一直截取到最后，字符第一位索引为1

举例：
SELECT SUBSTR('ssasdf',2);

查询结果为sasdf


（2）SUBSTR(str,pos,len)    --从pos位置开始，截取len个字符

举例：
SELECT SUBSTR('ssasdf',2,2);

查询结果为sa


（3）SUBSTR(str FROM pos)    --从pos位置开始截取

举例：
SELECT SUBSTR('ssasdf' FROM 4);

查询结果为sdf

（4）SUBSTR(str FROM pos FOR len)    --从pos位置开始截取len个字符

举例：
SELECT('ssasdf' FROM 2 FOR 3)  
  
查询结果为sas

5、INSTR　　获取子字符串第一次出现的索引

INSTR(str,substr)

举例： 
INSTR('ssasdf','a');

查询结果为3

6、TRIM　　去除前后指定的字符，默认是去除空格

TRIM([remstr FROM] str)

举例：
SELECT TRIM('s' from 'ssasdf');

查询结果为asdf

7、LPAD/RPAD　　左填充/右填充函数

LPAD(str,len,padstr)    --str是第一个字符串，len是结果字符串的长度，padstr是一个填充字符串。如果str的长度没有len那么长，则使用padstr左填充；如果str的长度大于len，则截断；RPAD同理
RPAD(str,len,padstr)

举例：
SELECT LPAD('asdfg',10,'o');

查询结果为oooooasdfg

SELECT LPAD('asdfg',2,'o');

查询结果为as

8、UPPER/LOWER　　大写/小写函数

UPPER(str)    --将str转换为大写
LOWER(str)    --将str转换为小写

举例：
SELECT UPPER('aSdfg');

查询结果为ASDFG


SELECT LOWER('aSdfg');

查询结果为asdfg

9、STRCMP　　比较大小

STRCMP(exp1,exp2)    --比较两个字符串，如果这两个字符串相等返回0，如果第一个参数是根据当前的排序小于第二个参数顺序返回-1，否则返回1

举例：
SELECT STRCMP('aaa','aaa');    --结果为0

SELECT STRCMP('asdfg','aaa');    --结果为1

SELECT STRCMP('asdfg','ast');    --结果为-1

10、LEFT/RIGHT　　截取子串

LEFT(str,len)
RIGHT(str,len)

举例：
SELECT LEFT('asdfg',10);    --结果为asdfg

SELECT RIGHT('asdfg',3);    --结果为dfg

 11、REPLACE　　替换

REPLACE(string_expression,string_pattern,string_replacement)

--参数含义
string_expression是要搜索的字符串表达式。string_expression可以是字符或二进制数据类型。
string_pattern是要查找的字符串。string_pattern可以是字符或者二进制数据类型，但不能是空字符串。
string_replacement是替换字符串。string_replacement可以是字符或二进制数据类型。


--返回类型
如果其中的一个输入参数数据类型为nvarchar，则返回nvarchar；否则REPLACE返回varchar。
如果任何一个参数为NULL，则返回NULL。


简单来说，REPLACE(String,from_str,to_str)即：将String中所有出现的from_str替换为to_str。
比如，REPLACE('abcdefgabc','abc','xxx')，输入的字符串为abcdefgabc，结果为xxxdefgxxx

  

二、数学函数

1、ABS　　绝对值

ASB(x)

举例：
SELECT ABS(-100);    --结果为100

2、CEIL　　向上取整，返回>=该参数的最小整数

CEIL(x)

举例：
SELECT CEIl(-1.5);    --结果为-1

SELECT CEIl(1.5);    --结果为2

3、FLOOR　　向下取整，返回<=该参数的最大整数

FLOOR(x)

举例：
SELECT FLOOR(-1.5);    --结果为-2

SELECT FLOOR(1.5);    --结果为2

4、ROUND　　四舍五入

ROUND(x)       -- 四舍五入 
ROUND(x,D)    --对x四舍五入取精确到D位小数

举例：
ROUND(1.65);    --结果为2

ROUND(1.567,2);    --结果为1.57

5、TRUNCATE　　截断

TRUNCATE(x,D)    --只保留小数点后D位，不用四舍五入

举例：
SELECT TRUNCATE(1.6521,1);    --结果为1.6

 三、日期函数

1、NOW()/CURRENT_TIMESTAMP()　　返回当前日期+函数

SELECT NOW();；    --结果为2021-04-08 23:26:39

SELECT CURRENT_TIMESTAMP();    --结果为2021-04-08 23:28:21

2、CURDATE()/CURRENT_DATE()　　返回当前日期，不含时间

SELECT CURDATE();    --结果为2021-04-08

SELECT CURRENT_DATE();    --结果为2021-04-08

3、CURTIME()/CURRENT_TIME()　　返回当前时间，不含日期

SELECT CURTIME();    --结果为23:49:43

SELECT CURRENT_TIME();    --结果为23:49:43

4、DATEDIFF　　日期1-日期2

DATEDIFF(exp1,exp2)

举例：
SELECT DATEDIFF('2020-01-10','2021-01-01');    --结果为-357

5、DATE_FORMAT　　日期转换函数

PS：日期转换格式有很多中，后面会讲到

DATE_FORMAT(date,format)

举例：
SELECT DATE_FORMAT('2020-01-10','%Y年%m月%d日');    --结果为2020年01月10日

6、STR_TO_DATE　　按指定格式解析字符串为日期类型

STR_TO_DATE(str,format)

举例：
SELECT STR_TO_DATE('01-10-2020','%m-%d-%Y');    --结果为2020-01-10

 7、DAYNAME　　星期几

DAYNAME(date)

举例：
SELECT DAYNAME(NOW());    --结果Mondy

8、WEEKOFYEAR/WEEK　　第几周

WEEKOFYEAR(date)

举例：
SELECT WEEKOFYEAR(NOW());    --结果为15


WEEK(date[,mode])

举例：
SELECT WEEK(NOW());    --结果为15

PS：

在WEEK(date[,mode])函数中，mode为模式可选，如下，

 

 

 

四、流程控制函数

1、IF函数

语法：IF(exp1,exp2,exp3)

解释：如果exp1满足条件，则返回exp2的值，否则返回exp3

举例：
SELECT IF(10>9,'是','否');    --结果为是

2、CASE函数

第一种情况：

CASE 表达式
WHEN 值1 THEN 结果1
WHEN 值2 THEN 结果2
......
ELSE 结果n
END


第二种情况：

CASE
WHEN 条件1 THEN 结果1
WHEN 条件2 THEN 结果2
......
ELSE 结果n
END

 

五、分组函数（聚合函数）

1、SUM　　求和

SUM(exp)    --求和
SUM([distinct] exp)    --    可选择去重后求和

注意：SUM函数求和时忽略NULL值，且只能处理数值型

2、AVG　　求平均数

AVG([distinct] exp)

注意：AVG函数求平均数时忽略NULL值，且只能处理数值型

3、MAX　　求最大值

MAX(exp)
MAX([distinct] exp)

注意：MAX函数求最大值时忽略NULL值，且可用于处理任何类型

4、MIN　　求最小值

MIN(exp)
MIN([distinct] exp)

注意：MIN函数求最小值时忽略NULL值，且可用于处理任何类型

5、COUNT　　求非空字段个数 

COUNT(exp)
COUNT([distinct] exp,[exp...])

注意：COUNT函数求个数时忽略NULL值，即只求非空字段个数，且可用于处理任何类型

COUNT(*)　　--统计总记录条数（和某个字段无关）
COUNT(1)　　--统计行数

在这里我们补充下IFNULL(exp1,exp2)，如果exp1不为NULL，则IFNULL函数返回exp1，否则返回exp2。

另外，有一个典型错误，需要记住：

SELECT ename,sal
FROM emp
WHERE sal>avg(sal);    --    这是典型错误，分组函数不可直接使用在WHERE子句中


SELECT ename,sal
FROM emp
WHERE sal>(SELECT avg(sal) FROM emp);    --运用子查询嵌套语句

 

补充：分组关键字GROUP BY

	数据源	位置	关键字
分组前筛选	原始表	GROUP BY前面	WHERE
分组后筛选	分组后的结果表	GROUP BY后面	HAVING