mysql 单行函数_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_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;