MySql常见函数

常见函数

select 函数 [from table]

分类:

一:单行函数:concat,length,ifnull等

1.字符函数:

length:获取字节长度

SELECT LENGTH('length');
SELECT LENGTH('长度');
SELECT LENGTH('1');
SHOW VARIABLES LIKE '%char%';#### 汉字utf-8占3个长字节,jdk占2个字节;

concat:拼接字符串

SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees WHERE LENGTH(first_name)<=4;

upper,lower:变大小写

SELECT UPPER('Jons'),LOWER('Jons');

substr:截取字符串

注意:字符串下标从1开始

有4种重载(str,pos) (str from pos) (str,pos,len) (str from pos for len)

SELECT SUBSTR('123456',2),SUBSTR('123456',2,3);

instr(str,substr)返回substr子串在str中的位置,下标从1开始,没找到返回0;

SELECT INSTR('12345','34');
SELECT INSTR('12345','3456');

trim 从字符串两边开始去除子串,遇到不能去除的就停止.

SELECT TRIM('1' FROM '12121');

lpad (str,len,padstr)将字符串补位成一定长度,用padstr补位;如果给定长度len<length(str) 会截取前面len长度的str

SELECT LPAD('89',9,'123456');
SELECT LPAD('1234',2,'123456');

rpad 从字符串右边开始补位,与上面相似

SELECT RPAD('12',10,'345');

replace(str,from_str,to_str) 替换

SELECT REPLACE('111011','11','22');

2.数学函数

round四舍五入 可以添加第2个参数表示小数点后保留多少位

SELECT ROUND(1.3),ROUND(-1.3),ROUND(1.2225,3);

ceil向上取整 返回>=传入值的最小整数

SELECT CEIL(1.3),CEIL(-1.3),CEIL(2.0);

floor向下取整 返回<=传入值的最大整数

SELECT FLOOR(1.3),FLOOR(-1.3),FLOOR(2.0);

truncate (X,N)保留N位小数,没有小数不添加0

SELECT TRUNCATE(1.222,2);
SELECT TRUNCATE(1,2);

mod取余 等同于% a-a/b*b

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

3.日期函数

now 返回系统日期+时间

SELECT NOW();

curdate 返回系统日期

SELECT CURDATE();

curtime 返回系统时间

SELECT CURTIME();

获取指定的年,月,日,小时,分,秒;

SELECT YEAR(NOW()) 年, MONTH(NOW()) 月,DAY(NOW()) 日,HOUR(NOW()) 小时,MINUTE(NOW()) 分,SECOND(NOW()) 秒;
SELECT YEAR(hiredate)  年 FROM employees;
SELECT MONTHNAME('1999-2-1') 月;
SELECT DAYNAME('1999-2-1') 星期;

str_to_date(str,format)

SELECT STR_TO_DATE('9-13-2001','%m-%d-%Y') 日期;

date_format(date,format)

SELECT DATE_FORMAT('2001-9-13','%Y年%m月%d日') 日期;
-- https://i.loli.net/2021/10/26/eNEXdnVzoS9AaQU.png   

4.其他函数

SELECT VERSION();
SELECT DATABASE();
SELECT USER();

5.流程控制函数

①if(expr1,expr2,expr3) 类似于三元运算符 expr1?expr2:expr3

SELECT IF(10>5,1,0);

②case 要判断的表达式 when 常量 then 执行操作

第一种使用情况

SELECT salary AS 原本工资,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*1.4
END AS 新工资
FROM employees;


SELECT 
CASE 1*3
WHEN 1 THEN 1
WHEN 2 THEN 2
ELSE 3
END

第二种使用情况

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

二:分组函数:

1.简单使用

SELECT SUM(salary) 所有员工工资,MAX(salary) 最高工资,MIN(salary) 最低工资, TRUNCATE(AVG(salary),1) 工资平均值 ,COUNT(salary) 员工个数 FROM employees;

2.对于null值,函数不会进行计算.会忽略null,下面可以验证

SELECT COUNT(`commission_pct`) ,SUM(commission_pct),AVG(commission_pct) ,7.8/35 FROM employees;
SELECT commission_pct FROM employees WHERE commission_pct IS NOT NULL;

3.和distinct搭配使用

SELECT SUM(salary) 工资,SUM(DISTINCT salary) 去重后总工资,COUNT(salary) 员工人数,COUNT(DISTINCT salary) 去重后人数 FROM employees;

4.count详细重载

SELECT COUNT(*) FROM employees;-- 1行里面只要有1列不为null就算   
SELECT COUNT(1) FROM employees;-- 多了1列1,行不变

5.和分组函数一起查询的命令有行数限制

SELECT SUM(salary),employee_id FROM employees;-- employee_id 只能显示1行