mysql常见函数之-单行函数

一、常见函数

mysql中的常见函数类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露的方法名
好处：

1、隐藏了实现细节
2、提高代码的重用性
语法：select 函数名（实参列表）【from表】
特点：
函数名（叫什么）
函数功能（干什么）
功能：做统计使用、又称为统计函数、聚合函数、组函数
分类：
1、单行函数
如：concat 、length、ifnull等
2、分组函数
如：max min avg sum count

单行函数

字符函数
1、length 获取参数值的字节个数

SELECT LENGTH ('emily'); 5

SELECT LENGTH ('张三hahahha'); 13
utf-8下，一个中文占3个字符，一个英文占1个字符
SHOW VARIABLES LIKE '%char%';   

2、concat 拼接字符串

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

3、upper lower
upper：将字符变大写，lower：将字符变小写

SELECT CONCAT(UPPER('haha'),'_',LOWER('HeLLo')) ;

4、substr 截取从指定索引处后面所有字符
注：索引是从1开始的

eg:截取从指定索引处后的所有字符

select substr('刘备字玄德',4) output;  玄德

eg:截取从指定索引处开始指定字符长度的字符

SELECT SUBSTR('刘备字玄德',1,2) output;  刘备

eg:姓名中首字符大写，其他字符小写然后用_拼接显示出来

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) output
FROM employees;

5、instr 返回子串第一次出现的索引，如果找不到返回0

SELECT INSTR('我是一只小小小鸟','小') output; 5

6、trim 去除前后空格或者字符

SELECT LENGTH(TRIM ('   张三   ')) AS output;  张三
SELECT TRIM('a',FROM 'aaaaaa张aaa三aaaa')AS output;    张aaa三
SELECT TRIM('aa',FROM 'aaaaaaa张aaa三aaaaa')AS output;    a张aaa三a

7、lpad 用指定的字符实现左填充指定长度

select lpad ('诸葛亮',16,'孔明');
孔明孔明孔明孔明孔明孔明孔诸葛亮

8、rpad用指定的字符实现右填充指定长度

SELECT RPAD ('诸葛亮',16,'孔明');
诸葛亮孔明孔明孔明孔明孔明孔明孔

9、replace 替换

SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏')AS out_put;
张无忌爱上了赵敏

数学函数
1、round 四舍五入

SELECT ROUND(1.65);  2 
SELECT ROUND(1.45);  1
SELECT ROUND(1.65,1); 1.7
SELECT ROUND(-1.65);  -2 
SELECT ROUND(-1.45); -1
SELECT ROUND(1.55);  -2

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

select ceil (1.52);     2
SELECT CEIL (1.002);  2
SELECT CEIL (1.00);  1
SELECT CEIL (-1.52); -1
SELECT CEIL (-1.02);  -1

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

SELECT FLOOR (9.99);    9
SELECT FLOOR (-9.99);  -10

4、truncate 截断

SELECT TRUNCATE(1.659,1);    1.6

5、mod 取余
mod(a,b) : a-a/bb
mod(-10/-3): -10-(10)/(-3)(-3)

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

6、rand:获取随机数，返回0-1之间的小数

select  rand(); 

日期函数
1、now 返回当前系统时间

SELECT NOW();    2020-07-29  17：32:30

2、curdate 返回当前系统日期，不包含时间

SELECT CURDATE();  2020-07-29

3、curtime 返回当前系统时间，不包含日期

select curtime();   17:35:28

4、获取指定的年、月、日、时、分、秒
YEAR :年
MONTH:月
DAY：日
HOUR：时
MINUTE:分
SECOND:秒
monthname: 以英文形式返回月

select year(now()) 年;  2020年

5、str_to_date 将字符通过指定的格式换成日期

SELECT STR_TO_DATE('2020-7-6','%Y-%c-%d')  2020-07-06

eg:查询入职日期为1992-4-3的员工信息

SELECT * FROM employees
WHERE hiredate=STR_TO_DATE('4-3 1992','%c-%d %Y');

6、date_formate 将日期转换成字符

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日');  02年07月29日

eg：查询有奖金的员工名和入职日期为（xx年xx月xx日）

SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %y年') 入职日期
FROM employees
WHERE commission_pct IS NOT NULL;

7、datediff 返回两个日期相差的天数

SELECT DATEDIFF(NOW(),'1994-7-6');  算一下自己距离今天活了多少天了：9521

其他函数
1、select version(); 查看数据库版本
2、select database();查看当前数据名
3、select user(); 查看当前用户 root
4、password(‘aaa’);返回字符的密码形式
5、md5(‘字符’); 返回该字符的md5加密形式

流程控制函数 case
1、类似java中的switch case
case函数情况1

case:要判断的变量、字段或表达式
when 常量1 then 要显示的值1或语句
when 常量2 then 要显示的值2或语句
else 要显示的值n或语句n
end

eg:查询员工的工资，要求
部门号=30，显示的工资为原工资的1.1倍
部门号=40，要显示的工资为原工资的1.2倍
部门号=50，要显示的工资为原工资的1.3倍
其他部门，显示的工资为原工资

SELECT salary 原始工资，department_id,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary 
END AS 新工资
FROM employees;

case 函数情况2
类似java中的if else if else

case
when 条件1 then 要显示的值1或者语句
when 条件2 then 要显示的值2或者语句
....
else 要显示的值n或语句n
end

eg:查询员工的工资情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则，显示D级别

SELECT salary 
CASE 
WHEN salary > 20000 THEN 'A'
WHEN salary > 15000 THEN 'B'
WHEN salary > 10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees;