MySQL中单行函数locate_MySQL基础--常见函数之单行函数

一、常见函数

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

好处：

①隐藏了实现细节

②提高了代码的重用性

调用：

SELECT 函数名(实参列表) [from 表];

特点：

①叫什么(函数名)

②干什么(函数功能)

分类：

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

功能：做处理使用

②分组函数：

功能：做统计使用，又称为统计函数、聚合函数或组函数

二、字符函数

1.length：获取参数值的字节数

SELECT LENGTH('join');#4

SELECT LENGTH('张三丰');#9

SHOW VARIABLES LIKE '%char%';#查看字符集

2.concat：拼接字符串

案例：用_将姓和名连接起来：

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

3.upper、lower

SELECT UPPER('join');#JOIN

SELECT LOWER('join');#join

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

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

4.substr、substring

注意：索引从1开始

1.两个参数：截取从指定索引处后面所有字符

SELECT SUBSTR('我想吃掉你的胰脏',7) out_put;#胰脏

2.三个参数：截取从指定索引处指定字符长度的字符

SELECT SUBSTR('我想吃掉你的胰脏',3,3) out_put;#吃掉你

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

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

5.instr

返回字串第一次出现的索引，如果找不到返回0

SELECT INSTR('红红火火恍恍惚惚恍恍惚惚','恍恍') AS out_put;#5

6.trim

去除字符串两边的字符

SELECT TRIM(' 白小九 ') AS out_put;

SELECT TRIM('a' FROM 'aaaa白aaa小九aaaaaaaaa') AS out_put;#白aaa小九

7.lpad

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

SELECT LPAD('花花啊',10,'*')AS out_put;#*******花花啊

SELECT LPAD('花花啊',1,'*')AS out_put;#花

8.rpad

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

SELECT RPAD('花花啊',12,'ww')AS out_put;#花花啊wwwwwwwww

9.replace

SELECT REPLACE('我喜欢你啊','啊','吗')AS output;#我喜欢你吗

三、数学函数

1.round：四舍五入

SELECT ROUND(1.65);#2

SELECT ROUND(1.4);#1

SELECT ROUND(-1.45);#-1

SELECT ROUND(-1.5);#-2

SELECT ROUND(1.567,2);#1.57，第二个参数表示小数点后保留的参数

2.ceil

向上取整,返回大于等于该参数的最小整数

SELECT CEIL(1.52);#2

SELECT CEIL(1.0001);#2

SELECT CEIL(-1.02);#-1

3.floor

向下取整，返回小于等于该参数的最小整数

SELECT FLOOR(-9.999);#-10

4.truncate

截断(第二个参数表示小数点后保留的位数)

SELECT TRUNCATE(1.665,1);#1.6

5.mod：取余

MOD(a,b):a-a/bb

eg：MOD(-10,-3) : -10-(-10)/(-3)(-3) = -1

SELECT MOD(10,3);#1

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

四、日期函数

参数为日期类型，用来对日期进行处理或返回日期

1.now

返回当前系统日期+时间

SELECT NOW();

2.curdate

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

SELECT CURDATE();

3.curtime

返回当前时间

SELECT CURTIME();

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

SELECT YEAR(NOW()) 年;

SELECT YEAR('2000-1-1') 年;

SELECT YEAR(hiredate) 年 FROM employees;

SELECT MONTH(NOW()) 月;#5

SELECT MONTHNAME(NOW()) 月;#May

5.str_to_date

将日期格式的字符转换成指定格式的日期

SELECT STR_TO_DATE('1-1-2010','%m-%d-%Y');#2010-01-01

SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d');#1998-03-02

格式符

功能

%Y

四位的年份

%y

两位的年份

%m

月份(01，02…11，12)

%c

月份(1，2…11，12)

%d

日(01，02…)

%H

小时(24小时制)

%h

小时(12小时制)

%i

分钟(00，01…59)

%s

秒(00，01…59)

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

SELECT * FROM employees WHERE hiredate = '1992-4-3';

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

6.date_format:将日期转换成字符

SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;#20年05月20日

案例：查询有奖金的员工名和入职日期(XX月/XX日 XX年)

SELECT last_name,DATE_FORMAT(hiredate,'%m月/%d日 %Y年') 入职日期

FROM employees

WHERE commission_pct IS NOT NULL;

四、其他函数

version：查看版本号

database：查看当前数据库

user：代表当前用户

五、流程控制函数

1.if函数：if else的效果

if(expr1,expr2,expr3)

expr1代表条件表达式，结果为true或false

结果为true的话返回expr2的值

结果为false的话返回expr3的值

SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金，呜呜呜','有奖金，蛤蛤蛤')备注

FROM employees;

2.case函数(控制结构)

使用一：类似于switch case的效果

语法：

CASE 要判断的字段或者表达式#等值判断

WHEN 常量1 THEN 要显示的值1或语句1;

WHEN 常量2 THEN 要显示的值2或语句2;

......

ELSE 要显示的值n或语句n;

END

案例：查询员工的工资，要求：如果部门号=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;

使用二：类似于多重if(if-else if-else)

CASE #区间判断

WHEN 条件1 THEN 要显示的值1或语句1;

WHEN 条件2 THEN 要显示的值2或语句2;

......

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;

六、案例

1.查询员工号，姓名，工资以及工资提高20%之后的结果

SELECT employee_id,last_name,salary,salary*1.2 "new salary";

FROM employees;

2.将员工的姓名按首字母排序，并写出姓名的长度(length)

SELECT LENGTH(last_name) 长度,SUBSTR(last_name,1,1) 首字符,last_name

FROM employees;

3.做一个查询，产生下面的结果

earns monthly but wants

Drearm Salary

King earns 24000 monthly but wants 72000

SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3) AS "Dream Salary"

FROM employees

WHERE salary=24000;

4.使用case-when，按照下面的条件：

job

grade

AD_PRES

A

ST_MAN

B

IT_PROG

C

SA_REP

D

ST_CLEARK

E

SELECT job_id AS job,

CASE job_id

WHEN 'AD_PRES' THEN 'A'

WHEN 'ST_MAN' THEN 'B'

WHEN 'IT_PROG' THEN 'C'

WHEN 'SA_REP' THEN 'D'

WHEN 'ST_CLEARK' THEN 'E'

END AS Grade

FROM employees;