常见函数
概念:类似于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_putFROMemployees;
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); #执行结果为1SELECT 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年') 入职日期FROMemployeesWHERE commission_pct IS NULL;
四、其他函数
SELECTVERSION(); #版本号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,CASEdepartment_idWHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSEsalaryEND AS新工资FROM employees;
使用②:类似于 多重if
CASE
WHEN 条件1 THEN要显示的值1或语句1WHEN 条件1 THEN要显示的值1或语句1
... ...ELSE要显示的值n或语句nEND
案例:查询员工的工资情况,如果工资>20000,显示A级别;如果工资>15000,显示B级别;如果工资>10000,显示C级别;否则,显示D级别
SELECTsalary,CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS工资级别FROM employees;