初识MySQL之常见函数的使用

控制流程函数

IF(expr1,expr2,expr3)

如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，
则 IF()的返回值为expr2; 否则返回值则为 expr3。
IF() 的返回值为数字值或字符串值，
具体情况视其所在语境而定。

具体操作

SELECT IF(1>2,2,3);

    -> 3

SELECT IF(1<2,'yes ','no');

    -> 'yes'

SELECT IF(STRCMP('test','test1'),'no','yes');

    -> 'no'

IFNULL(expr1,expr2)

如果 expr1 不为 NULL，
则IFNULL()的返回值为expr1；
否则返回值为expr2。
IFNULL（）的返回值为数字或字符串，
具体情况取决于其所使用的语境。

具体操作

SELECT IFNULL(1,0);

	-> 1

SELECT IFNULL(NULL,2);

	-> 2

SELECT IFNULL(1/0,3);

	-> 3

SELECT IFNULL(1/0,'hello');

	-> 'hello'

字符串函数

CONCAT(str1,str2)

返回结果为连接参数产生的字符串。
如有任何一个参数为NULL，
则返回值为NULL。
允许有一个参数或多个参数。
如果所有参数均为非二进制字符串，
则结果为非二进制字符串。
如果自变量中含有任一二进制字符串，
则结果为一个二进制字符串。

具体操作

SELECT CONCAT('My','S','QL');

 	-> 'MySQL'

SELECT CONCAT('My',NULL,'QL');

 	-> NULL

LPAD(str,len,padstr)

返回字符串str，
其左边由字符串padstr填补到len字符长度。
加入str的长度大于len，
则返回值被缩短至len字符

具体操作

SELECT LPAD('he',4,'#');

 	-> '##he'

SELECT LPAD('he',1,'#');

 	-> 'h'

LENGTH(str1,str2)

返回结果为字符串str的长度，
单位为字节。
一个多字节字符算作多字节。
这意味着对一个包含5个2字节字符的字符串，
LENGTH()的返回值为10，
而CHAR_LENGTH()的返回值则为5。

具体操作

SELECT LENGTH('text');

 	-> 4

日期函数

DATE(expr)

提取日期或时间日期表达式expr中的日期部分。

具体操作

SELECT DATE('2021-07-14 11:45:04');

 	-> '2021-07-14'

DATEDIFF(expr,expr2)

DATEDIFF()返回起始时间expr和结束时间expr2之间的天数。
expr和expr2位日期或date-and-time表达式。
计算中只用到这些值的日期部分。

具体操作

SELECT DATEDIFF('2021-07-14 11:45:04'，‘2021-07-15’);

 	-> -1

DATE_ADD(date,INTERVAL expr type) DATE_SUB(date,INTERVAL expr type)

这些函数执行日期运算。
date是一个DATETIME或DATE值，
用来指定起始时间。
expr是一个表达式，
用来指定从起始日期添加或减去的时间间隔值。
expr是一个字符串，
对于负值的时间间隔，
它可以以一个’—‘开头。
type为关键词，
它指示了表达式被解释的方式。

type的值可以是MICROSECOND SECOND MINUTE HOUR DAY WEEK MONTH QUARTER YEAR SECOND_MICROSECOND MINUTE_MICROSECOND MINUTE_SECOND HOUR_MICROSECOND HOUR_SECOND HOUR_MINUTE DAY_MICROSECOND DAY_SECOND DAY_MINUTE DAY_HOUR YEAR_MONTH

具体操作

SELECT DATE_ADD("2017-06-15", INTERVAL 10 DAY);    

	-> 2017-06-25

SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL 15 MINUTE);
	
	-> 2017-06-15 09:49:21

SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);
	
	->2017-06-15 06:34:21

SELECT DATE_ADD("2017-06-15 09:34:21", INTERVAL -3 HOUR);
	
	->2017-04-15

DATE_FORMAT(date,format)

根据format 字符串安排date 值的格式。

以下说明符可用在format字符串中：

说明符	说明
%a	工作日的缩写名称 (Sun…Sat)
%b	月份的缩写名称 (Jan…Dec)
%c	月份，数字形式(0…12)
%D	带有英语后缀的该月日期 (0th, 1st, 2nd, 3rd, …)
%d	该月日期, 数字形式 (00…31)
%e	该月日期, 数字形式(0…31)
%f	微秒 (000000…999999)
%H	小时(00…23)
%h	小时(01…12)
%I	小时 (01…12)
%i	分钟,数字形式 (00…59)
%j	一年中的天数 (001…366)
%k	小时 (0…23)
%l	小时 (1…12)
%M	月份名称 (January…December)
%m	月份, 数字形式 (00…12)
%p	上午（AM）或下午（ PM）
%r	时间 , 12小时制 (小时hh:分钟mm:秒数ss 后加 AM或PM)
%S	秒 (00…59)
%s	秒 (00…59)
%T	时间 , 24小时制 (小时hh:分钟mm:秒数ss)
%U	周 (00…53), 其中周日为每周的第一天
%u	周 (00…53), 其中周一为每周的第一天
%V	周 (01…53), 其中周日为每周的第一天 ; 和 %X同时使用
%v	周 (01…53), 其中周一为每周的第一天 ; 和 %x同时使用
%W	工作日名称 (周日…周六)
%w	一周中的每日 (0=周日…6=周六)
%X	该周的年份，其中周日为每周的第一天, 数字形式,4位数;和%V同时使用
%x	该周的年份，其中周一为每周的第一天, 数字形式,4位数;和%v同时使用
%Y	年份, 数字形式,4位数
%y	年份, 数字形式 (2位数)
%%	‘%’文字字符

具体操作

SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日');

	->'2021年07月14日'
	
SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');

    -> 'Saturday October 1997'

NOW()

返回当前日期和时间

具体操作

SELECT NOW()

	-> 2021-07-14 12:54:55

YEAR(d)

返回年份	

具体操作

SELECT YEAR("2017-06-15");

	-> 2017

MONTH(d)

返回日期d中的月份值，1 到 12	

具体操作

SELECT MONTH('2011-11-11 11:11:11')
	
	->11

DAY(d)

返回日期值 d 的日期部分	

具体操作

SELECT DAY("2017-06-15");  

	-> 15

聚合函数（组函数）

聚合函数对一组值执行计算，并返回单个值。 除了 COUNT(*) 外，聚合函数都会忽略 Null 值。 聚合函数经常与 SELECT 语句的 GROUP BY 子句一起使用。

聚合函数	说明
SUM()	返回总和
MAX()	返回最大值
MIN()	返回最小值
AVG()	返回平均值
COUNT()	返回个数

语法

SELECT 聚合函数(列名) FROM 表名;

具体操作

-- 公司中员工的薪资总额
SELECT SUM(SAL) FROM EMP;

-- 薪资最大值
SELECTMAX(SAL) FROM EMP;

-- 薪资最小值
SELECT MIN(SAL) FROM EMP;

-- 薪资平均值
SELECT AVG(SAL) FROM EMP;

分组

-- 分组查询是指使⽤ GROUP BY 语句对查询信息进⾏分组，相同数据作为⼀组
SELECT 字段 1,字段 2... FROM 表名 GROUP BY 分组字段 [HAVING 条件];

具体操作

-- 每个部门的额最高薪资是多少 - 以部门来分组
SELECT MAX(SAL) , DEPTNO FROM EMP GROUP BY DEPTNO;

注意：跟组函数同时出现的字段，必须跟在group by 子句后

having 与 where 的区别

子句	作用
where	1. 对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，即先过滤再分组 2. where 后⾯不可以使⽤聚合函数
having	1. having ⼦句的作⽤是筛选满⾜条件的组，即在分组之后过滤数据，即先分组再过滤 2. having 后⾯可以使⽤聚合函数

分页 - LIMIT

SELECT *|字段列表 [as 别名] FROM 表名 [WHERE ⼦句][GROUP BY ⼦句][HAVING ⼦句] [ORDER BY ⼦句][LIMIT ⼦句];

具体操作

SELECT * FROM EMP;
SELECT * FROM EMP LIMIT 0,2; -- 1
SELECT * FROM EMP LIMIT 2,2; -- 2
SELECT * FROM EMP LIMIT 4,2; -- 3
-- oracle 的分页 需要使用 rownum：自动将查询结果给添加隐藏编号 只能从1开始

注：(page -1)*pageSIze,pageSize