MySQL常用函数

常见函数

概念：类似于java的方法，将一组逻辑语句封装在方法体总，对外暴露方法名

好处：隐藏了实现细节，提高代码的重用性

调用：select 函数名（实参列表） [from 表]

分类：

1. 单行函数：concat、length、ifnull
2. 分组函数：做统计使用，又称为统计函数，聚合函数、组函数

---

字符函数

1. length() 获取参数值的字节个数
select length('jjjj');
输出:4

select length('雷佳音hhh);#一个汉字占三个字节
输出:12

2. conccat() 拼接字符串
select concat('last_name', '   ',' first_name');
输出:last_naem first_name

3. upper()、lower()
select upper('hhh');#变大写
输出：HHH

select lower('HHH');#变小写
输出：hhh

select concat(upper('last_name'), '_', lower('first_NAME')) from employees;
输出：LAST_NAME_first_name

4. substr()、substring()
索引从1开始
截取从指定索引处后面所有字符
select substr('hello world', 7);
输出：world

截取从指定索引处指定字符长度的字符
select substr('hello world', 1, 3);
输出：hel

5. instr()
返回子串第一次出现的索引，如果找不到返回0
select instr('hello world', 'l');
输出：3

6.trim()去掉开头和结尾的字符串
select trim('aa' from 'aaaaaaaaaaahello aaaaaaaaaa worldaaaaaaaaaaaaa') AS 'out';
输出：ahello aaaaaaaaaa worlda

7.lpad() 用指定的字符实现左填充至指定长度
select lpad('hello', 10 ,'@');
输出：@@@@@hello

select lpad('hello', 2 ,'@');
输出：he

8.rpad() 用指定的字符实现右填充至指定长度
select rpad('hello', 10 ,'@!');
输出：hello@!@!@
select rpad('hello', 2 ,'@');#依然是从左开始截取
输出：he

9.replace 替换
select replace('hello worldworldworld', 'world', 'china');
输出：hello chinachinachina

---

数学函数

1. round 四舍五入
select round(1,45);         1
select round(1.567, 2);   1.6

2.ceil 向上取整，返回>=该参数的最小整数
select ceil(1.02);  2

3.floor 向下取整，返回<=该参数的最大整数
select Floor(-9.99);  -10

4.truncate 截断,取小数点后一位
select truncate(1.6999, 1); 1.6

5.mod 取余 a-a/b*b
select mod(-10,3)   -1

---

日期函数

1. now() 返回当前系统日期+时间
select now();

2.curdate() 返回当前系统日期，不包含时间
select curdate();

3.curtime() 返回当前时间，不包含日期
select CURTIME();

4.可以获取指定的部分，年、月、日、小时、分钟、秒
select year(now()) 年;
select year('1990-2-2') 年;
select distinct year(hiredate) year from employees order by year desc;


5. str_to_date: 将字符格式的字符转换成指定格式的日期
STR_TO_DATE('9-13-1999', '%m-%d-%Y') 
1999-09-13
6. date_format: 将日期转换成字符
data_format('2018/6/6', '%Y年%m月%d日')

---

其他函数

select version();
select database();
select user();

---

流程控制函数

1， if() 
select if(10 < 5, 'big', 'small');
small

select last_name,if(commission_pct is null, 'have', 'not') form employees;

2. case() switch case 效果
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
...
else 要显示的值1或语句1;
end
select case department_id
when 30 then 1.1
when 40 then 1.2
when 50 then 1.3
else 0.0
end as result
from employees;

---

分组函数

功能； 用作统计使用，又称为聚合函数或统计函数或组函数

分类：sum 求和， avg 平均值， max 最大值， min 最小值， count 统计数量

特点：

1. sum， avg一般用于处理数值型

   max，min，count可以处理任何类型

2. 以上分组函数都忽略null值

3. 可以和distinct搭配实现去重操作

4. 和分组函数一同查询的字段要求是group by后的字段

count()
select count(*) from employees;
select count(1) from employees;

效率：
MYISAM存储引擎下，count（*）的效率高
INNODB存储引擎下，count（*）和count（1）的效率差不多，但比count（字段）效率高
所以一般使用count（*）统计行数