MySQL学习03-常见函数之单行函数

常见函数

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

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

　　调用：SELECT 函数名（实参列表）【FROM 表】；

　　特点：①叫什么（函数名）

　　　　　②干什么（函数功能）

　　分类：①单行函数，如concat、length、ifnull等

　　　　　②分组函数，做统计使用，又称为统计函数、聚合函数、组函数

单行函数包括字符函数、数字函数、日期函数、其他函数【补充】、流程控制函数【补充】

一、字符函数

1. length　　获取参数集的字节个数

SELECT LENGTH ('jone');
SELECT LENGTH('张三丰');

2. concat　　拼接字符串

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

3. upper、lower　　变大写、小写

SELECT UPPER('John');
SELECT lower（'JONE'）;

案例：将姓变大写，名变小写，然后拼接

SELECT CONCAT(UPPER(last_name),LOWER(first_name)) 姓名 FROM employees;

4.substr/substring

注意：索引从1开始

①截取从指定索引处后面所有字符

SELECT SUBSTR('李莫愁爱上了陆展元',7) out_out;　　#执行输出陆展元

②截取从指定索引处指定字符长度的字符

SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;　　#执行输出李莫愁

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

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

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

SELECT INSTR('杨不悔爱上了殷六侠','殷六侠') AS out_put;　　#执行输出7

6. trem

①去前后空格

SELECT LENGTH(TRIM('   张翠山   ')) AS out_put;    #执行结果为张翠山，字符长度为9

②去前后字母

SELECT TRIM('a' FROM 'aaa张aaa翠山aaa') AS out_put;    #执行结果为张aaa翠山

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

SELECT LPAD('殷素素',10,'*') AS out_put;    #执行结果为*******殷素素，填充到10个字符长度

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

SELECT RPAD('殷素素',10,'*') AS out_put;    #执行结果为殷素素*******，填充到10个字符长度

9. replace 替换

SELECT REPLACE('张无忌爱上了周芷若','周芷若','赵敏') AS out_put; 　　#执行结果为张无忌爱上了赵敏，多处替换

二、数学函数

1. round 四舍五入

SELECT ROUND(1.55);
SELECT ROUND(1.567,2);    #保留两位小数

2. ceil 向上取整（返回>=该参数的最小整数）

SELECT CEIL(1.02); #执行结果为2

3. floor 向下取整（返回<=该参数的最大整数）

SELECT FLOOR(-9.99);    #执行结果为-10

4.truncate 截断

SELECT TRUNCATE(1.6999,1);    #执行结果为1.6， 保留1位小数

5.mod 取余

MOD(a,b)： a-a/b*b

SELECT MOD(10,3);    #执行结果为1
SELECT 10%3;

三、日期函数

1. now 返回当前系统日期+时间

SELECT NOW();

2. curdata 返回当前系统日期，不包含时间

SELECT CURDATA();

3. curtime 返回当前时间，不包含日期

SELECT CURTIME();

4.获取指定的部分，年、月、日、小时、分钟、秒

SELECT YEAR(NOW()) 年；    #获取当前年份
SELECT YEAR('1998-1-1') 年；    #获取输入年份
SELECT YEAR(hiredata) 年 FROM employees;    #获取某字段的年份

　　①str_to_data:将字符通过指定格式转换成日期

SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;    #执行结果为1988-03-02

　　实例：查询入职日期为1992-4-3的员工信息

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

　　②data_format:将日期转换为字符

SELECT DATA_FORMAT(NOW(),'%y年%m月%d日') AS out_put; #执行结果为20年06月08日

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

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

四、其他函数

SELECT VERSION();    #版本号
SELECT DATABASE();    #查看当前库
SELECT USER();    #查看当前用户

五、流程控制函数

1. if函数：实现 if else 的效果

SELECT IF('10>5','大','小')；
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呵呵'，'有奖金，嘻嘻') 备注
FROM employees；

2. case函数 

使用①：switch case 的效果

CASE 要判断的字段或表达式
WHEN 常量1 THEN 要显示的值1或语句1；
WHEN 常量1 THEN 要显示的值1或语句1；
... ...
ELSE 要显示的值n或语句n;
END

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

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
END AS 新工资
FROM employees；

使用②：类似于 多重if

CASE
WHEN 条件1 THEN 要显示的值1或语句1
WHEN 条件1 THEN 要显示的值1或语句1
... ... 
ELSE 要显示的值n或语句n
END

案例：查询员工的工资情况，如果工资>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;