【MYSQL 基础篇笔记】函数基础——单行函数

目录

MYSQL常见函数——单行函数

一、字符函数

1、CONCAT(str1,str2,...) 拼接字符

2、LENGTH(str) 获取字节长度

3、CHAR_LENGTH(str)获取字符长度

4、SUBSTR(str,起始字符位置,截取字符长度)  截取子串

5、INSTR(str,substr)获取字符第一次出现的索引

6、TRIM([remstr FROM] str)去除前后指定字符，默认去空格

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

8、UPPER/LOWER 变大写/变小写

9、STRCMP(expr1,expr2)比较两个字符的大小

10、LEFT/RIGHT 截取子串,返回字符串最左/最右的n个字符

11、substring_index()函数用来截取字符串

二、数学函数

1、ABS(X)绝对值

2、CEIL 向上取整

3、FLOOR(X)向下取整

4、ROUND(X,D)四舍五入

5、TRUNCATE(X,D)截断

6、MOD 取余

三、日期函数

1、NOW 当期日期及时间

2、CURDATE 当前日期

3、CURTIME 当前时间

4、DATEDIFF 两个日期之差（显示天数）

5、DATE_FORMAT(date,format)将日期转换成指定格式的字符串

6、STR_TO_DATE(str,format)按指定格式解析字符串为日期类型

7、DAYOFWEEK(date)返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)

8、 WEEKDAY(date)返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)

9、DAYOFMONTH(date)返回date的月份中日期，在1到31范围内

 10、DAYOFYEAR(date)返回date在一年中的日数, 在1到366范围内

11、MONTH(date)返回date的月份，范围1到12

12、DAYNAME(date)返回date的星期名字

13、MONTHNAME(date)返回date的月份名字

14、QUARTER(date)返回date一年中的季度，范围1到4。

15、WEEK(date)返回date的周数，范围在0到52

 16、WEEK(date,first)

17、YEAR(date)返回date的年份，范围在1000到9999。

18、HOUR(time)返回time的小时，范围是0到23。

 19、MINUTE(time)返回time的分钟，范围是0到59

20、SECOND(time)回来time的秒数，范围是0到59。

 21、PERIOD_ADD(P,N)

 22、PERIOD_DIFF(P1,P2)

四、流程控制函数

1、IF函数

2、CASE 函数

---

MYSQL常见函数——单行函数

• 函数分类：自定义方法函数、调用方法函数
• 常见函数：字符函数、数学函数、日期函数、流程控制函数

一、字符函数

1、CONCAT(str1,str2,...) 拼接字符

SELECT CONCAT('hello,',first_name,last_name) FROM employees;

2、LENGTH(str) 获取字节长度

SELECT LENGTH('hello,你好');
#utf8中一个汉字3个字节

3、CHAR_LENGTH(str)获取字符长度

SELECT CHAR_LENGTH('hello,你好');
#一个汉字占一个字节

4、SUBSTR(str,起始字符位置,截取字符长度)  截取子串

SELECT SUBSTR('可爱的小海豚',1,3);#结果为：可爱的
SELECT SUBSTR('可爱的小海豚',4,3);#结果为：小海豚

5、INSTR(str,substr)获取字符第一次出现的索引

SELECT INSTR('逐渐消失的穿山甲，请不要再捕杀穿山甲','穿山甲');#结果为：6

6、TRIM([remstr FROM] str)去除前后指定字符，默认去空格

SELECT TRIM('   蜻   蜓  ') AS a;  #蜻   蜓
SELECT TRIM('x'FROM 'xxxx蜻 xxx 蜓xx');  #蜻 xxx 蜓

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

SELECT LPAD('小海豚',4,'m');  #m小海豚
SELECT RPAD('you',6,'z');  #youzzz
SELECT RPAD('小海豚',1,'a');  #小    

8、UPPER/LOWER 变大写/变小写

SELECT UPPER(SUBSTR(first_name,1,1)) FROM employees;#取第一个字符的大写字符
SELECT LOWER(SUBSTR(first_name,2)) FROM employees; #取第二个开始之后所有字符的小写
SELECT UPPER(first_name) FROM employees;#取所有大写字符

9、STRCMP(expr1,expr2)比较两个字符的大小

SELECT STRCMP('ABC','AAA');

10、LEFT/RIGHT 截取子串,返回字符串最左/最右的n个字符

SELECT LEFT('小海豚',2)

11、substring_index()函数用来截取字符串

substring_index(str,delim,count)
    str:要处理的字符串
    delim:分隔符
    count:计数

示例：

如 str=www.wiki.com

则 substring_index(str,'.',1) 处理的结果是：www

substring_index(str,'.',2) 得到的结果是：www.wiki

也就是说，如果count是正数，那么就是从左往右数，第N个分隔符的左边的全部内容，

相反，如果是负数，那么就是从右边开始数，第N个分隔符右边的所有内容。

如：

substring_index(str,'.',-2) 得到的结果为：http://wikibt.com

如果要中间的的 wiki 怎么办？

很简单的，需要从两个方向截取：

先截取从右数第二个分隔符的右边的全部内容，再截取从左数的第一个分隔符的左边的全部内容：

　　substring_index(substring_index(str,'.',-2),'.',1);

二、数学函数

1、ABS(X)绝对值

SELECT ABS(-2.4);

2、CEIL 向上取整

SELECT CEIL(1.09);

3、FLOOR(X)向下取整

SELECT FLOOR(1.09);

4、ROUND(X,D)四舍五入

SELECT ROUND(3.566,2)

5、TRUNCATE(X,D)截断

SELECT TRUNCATE(1.78998643,2);

6、MOD 取余

SELECT MOD(-10,3);
SELECT -10%-3;

三、日期函数

1、NOW 当期日期及时间

SELECT NOW();

2、CURDATE 当前日期

SELECT CURDATE();

3、CURTIME 当前时间

SELECT CURTIME();

4、DATEDIFF 两个日期之差（显示天数）

SELECT DATEDIFF('2020-2-2','1992-3-4');

5、DATE_FORMAT(date,format)将日期转换成指定格式的字符串

SELECT DATE_FORMAT('1990-7-23','%Y年%m月%d日 %H小时%i分钟%s秒');
SELECT DATE_FORMAT(hiredate,'%Y年%m月%d日 %H小时%i分钟%s秒') FROM employees;

6、STR_TO_DATE(str,format)按指定格式解析字符串为日期类型

SELECT STR_TO_DATE('3-15 1998','%m-%d %Y');

7、DAYOFWEEK(date)返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)

select DAYOFWEEK('1998-02-03')

8、 WEEKDAY(date)返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)

select WEEKDAY('1997-10-04 22:23:00');

9、DAYOFMONTH(date)返回date的月份中日期，在1到31范围内

select DAYOFMONTH('1998-02-03');

 10、DAYOFYEAR(date)返回date在一年中的日数, 在1到366范围内

select DAYOFYEAR('1998-02-03');

11、MONTH(date)返回date的月份，范围1到12

select MONTH('1998-02-03');

12、DAYNAME(date)返回date的星期名字

select DAYNAME("1998-02-05");
-> 'Thursday'

13、MONTHNAME(date)返回date的月份名字

select MONTHNAME("1998-02-05");
-> 'February'

14、QUARTER(date)返回date一年中的季度，范围1到4。

elect QUARTER('98-04-01');
-> 2

15、WEEK(date)返回date的周数，范围在0到52

对于星期天是一周的第一天的地方，有一个单个参数，返回date的周数，范围在0到52。

select WEEK('1998-02-20');
-> 7

 16、WEEK(date,first)

2个参数形式WEEK()允许你指定星期是否开始于星期天或星期一。
如果第二个参数是0，星期从星期天开始，
如果第二个参数是1，从星期一开始。

select WEEK('1998-02-20',0);
-> 7

select WEEK('1998-02-20',1);
-> 8

17、YEAR(date)返回date的年份，范围在1000到9999。

elect YEAR('98-02-03');
-> 1998

18、HOUR(time)返回time的小时，范围是0到23。

select HOUR('10:05:03');
-> 10

 19、MINUTE(time)返回time的分钟，范围是0到59

elect MINUTE('98-02-03 10:05:03');
-> 5

20、SECOND(time)回来time的秒数，范围是0到59。

elect SECOND('10:05:03');
-> 3

 21、PERIOD_ADD(P,N)

增加N个月到阶段P(以格式YYMM或YYYYMM)。以格式YYYYMM返回值。注意阶段参数P不是日期值。

select PERIOD_ADD(9801,2);
-> 199803

 22、PERIOD_DIFF(P1,P2)

返回在时期P1和P2之间月数，P1和P2应该以格式YYMM或YYYYMM。注意，时期参数P1和P2不是日期值

select PERIOD_DIFF(9802,199703);
-> 11

23、

四、流程控制函数

1、IF函数

SELECT IF(100>9,'好','坏');
#如果有奖金，则显示奖金，无奖金则显示为0
SELECT IF(commission_pct IS NULL,0,salary*12*commission_pct) 奖金
FROM employees;

2、CASE 函数

• 简单case函数：

如果某个简单表达式的值与测试表达式的值相等，则返回第一个与之匹配的WHEN子句所对应的结果表达式的值。
如果所有简单表达式的值与测试表达式的值都不相等，
若指定了ELSE子句,则返回ELSE子句中指定的结果表达式的值；
若没有指定ELSE子句，则返回NULL。

CASE 测试表达式

WHEN 常量 THEN 结果表达式

WHEN 常量 THEN 结果表达式

[ELSE  结果表达式]

END

• 搜索case函数

按从上到下的书写顺序计算每个WHEN子句的布尔表达式。
返回第一个取值为TRUE的布尔表达式所对应的结果表达式的值。
如果没有取值为TRUE的布尔表达式，
则当指定了ELSE子句时,返回ELSE子句中指定的结果；
如果没有指定ELSE子句，则返回NULL。

CASE 

WHEN 布尔表达式 THEN 结果表达式

WHEN 布尔表达式 THEN 结果表达式

[ELSE  结果表达式]

END

#情况1：实现等值判断
#案例：若部门编号是30，工资显示为2倍，若部门编号是50，工资显示为3倍，若部门编号是60，工资显示为4倍,其他不变
SELECT department_id,salary,
CASE department_id
	WHEN 30 THEN salary*2
	WHEN 50 THEN salary*3
	WHEN 60 THEN salary*4
	ELSE salary
END  AS NEW_SALARY
FROM employees;

#情况2：实现区间判断
#案例：若工资大于2万，显示级别为A，若工资大于1.5万，显示级别为B，若工资大于1万，显示级别为C，否则显示为D
SELECT salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END 级别
FROM employees;