MySQL 常见函数 -------单行函数

一、字符函数

1.length

SELECT LENGTH(‘john’);
SELECT LENGTH(‘中国abc’);

2.concat

SELECT CONCAT(last_name,‘_’,first_name) AS 姓名 FROM employees;

3.upper、lower

SELECT UPPER(‘abc’);
SELECT LOWER(‘CDE’);

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

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

4.substr、substring

SELECT SUBSTR(‘李莫愁爱上了陆展元’,7) AS out_put ;
SELECT SUBSTR(‘李莫愁爱上了陆展元’,1,3) AS out_put; #从第一个开始截取，长度为3的字符串
#案例：姓名中首字符大写，其他字符小写然后用_拼接，显示出来
SELECT
CONCAT(UPPER(SUBSTR(last_name,1,1)),‘_’,LOWER(SUBSTR(last_name,2))) AS out_put
FROM
employees;

5.instr ：返回子字符串第一次出现的索引，若没有返回0

SELECT INSTR(‘杨不悔殷六侠爱上了殷六侠’,‘殷侠’) AS out_put;

6.trim :去除前后空格

SELECT LENGTH(TRIM(’ 张翠山 ')) AS out_put;
SELECT TRIM(‘a’ FROM ‘aaa张aa翠aa山aaaaa’) AS out_put;

7.lpad :实现左填充

SELECT LPAD(‘版数数’,10,‘*’) AS out_put;

8.rpad ：右填充

SELECT RPAD(‘哈哈’,10,‘qaz’) AS out_put;

9.replace :替换

SELECT REPLACE(‘账务考都是dshj收拾都是都是卡死都是’,‘都是’,‘哈哈’)AS out_put;

二、数学函数

round 四舍五入

SELECT ROUND(-1.3);
SELECT ROUND(-3.346,2);

ceil 向上取整,返回>=该参数的最小整数

SELECT CEIL(-2.20);

floor 想下取整,返回<=该参数的最大整数

SELECT FLOOR(2.88);

truncate 截断

SELECT TRUNCATE(1.8999,2);

mod 取余 mod(a,b)=a-a/b*b

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

三、日期函数

#now 返回当前系统日期+时间
SELECT NOW();

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

SELECT CURDATE();

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

SELECT CURTIME();

获取指定的部分：年、月、日、小时、分、秒

SELECT YEAR(NOW()) AS 年;
SELECT MONTHNAME(‘1998/9/8’) AS 月; #加了NAME后缀就是英文
SELECT DAYNAME(‘1992-2-2’) AS 日;
SELECT HOUR(NOW()) AS 小时;
SELECT MINUTE(NOW())AS 分;
SELECT SECOND(NOW()) AS 秒;、

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

SELECT STR_TO_DATE(‘1990-6-24’,‘%Y-%m-%d’)AS 日期;
SELECT * FROM employees WHERE hiredate=STR_TO_DATE(‘1992-4-3’,‘%Y-%c-%d’) AS 日期;

date_format 日期转字符

SELECT DATE_FORMAT(NOW(),‘%Y年%m月%d日’) AS 日期字符;
#查询有奖金的员工名和入职日期(xx月/xx日/xx年)
SELECT
CONCAT(last_name,first_name) AS 姓名,
DATE_FORMAT(hiredate,‘%m月%d日 %Y年’) AS 入职日期,
commission_pct AS 奖金
FROM
employees
WHERE
commission_pct IS NOT NULL;

四、其他函数

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

五、流程控制函数

1.if函数: if else 的效果

SELECT IF(10>5,‘大’,‘小’);

SELECT
*,
IF(ISNULL(commission_pct),‘有’,‘无’)AS 奖金有无
FROM employees;

2.case 函数的使用一：类似于switch

格式：
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 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;

3.case 函数的使用二：类似于 多重if

格式：
case
when 条件1 then 要显示的值1或表达式1
when 条件2 then 要显示的值2或表达式2
…
else
end

案例：查询员工的工资情况

工资>20000,显示A
工资>15000,显示B
工资>10000,显示C
其余,显示D。
---------------我是分割线--------------

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

练习题

1.显示系统时间（注：日期+时间）
SELECT NOW() AS 系统时间;

2.查询员工，姓名，工资，以及工资提高百分之20%后的结果(NEW salary)
SELECT CONCAT(last_name,first_name) AS 姓名 , salary AS 原始工资, salary*1.2 AS 新工资 FROM employees;

3.将员工的姓名按首字母排序,并写出姓名的长度(LENGTH)
SELECT LENGTH(last_name) AS 姓名长度 , SUBSTR(first_name,1,1)AS 首字母 ,last_name FROM employeesORDER BY 首字母 ASC;

4.做一个查询，产生下面的结果
<last_name> earns monthly but wants <salary*3>
Dream 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;

5.使用CASE-WHEN ，按照下面的条件：
job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
产生下面的结果
Last_name Job_id Grade
king AD_PRES A
---------------我是分割线--------------
SELECT
last_name,
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_CLERK’ THEN ‘E’
END AS grade
FROM
employees
WHERE
job_id=‘AD_PRES’;

函数总结：

1.字符函数：
length：获取长度
concat ：拼接字符
substr：截取指定字符
instr：找下标
trim ：去首尾空格
upper ：转大写
lower：转小写
lpad ：添加左边字符
rpad ：添加右边字符
replace： 替换

2.数学函数：
round ：四舍五入
ceil ：向上取整
floor ：向下取整
truncate： 截取
mod ：取余

3.日期函数：
now ：当前日期+时间
curdate： 日期
curtime ：时间
year
month
day
hour
minute
second
str_to_date： 字符转日期
date_format ：日期转字符

4.其他函数：
version
database
user

5.控制函数：
if
case