MySQL数据库：第五章：常见函数

回退至Mysql数据库理论与实战
#进阶4：常见函数

函数：类似于java中的”方法“，将完成某个功能的一系列步骤封装起来，对外暴露一个名字，供外界调用
当我们学习别人定义好的方法（函数），只需要关心两件事：
①叫什么（函数名）
②干什么（函数功能）
sql中的函数：肯定有，并且仅有一个返回值
调用语法：
select 函数名(实参列表);
一、数学函数：
abs绝对值
mod取余
floor向下取整
truncate 截断
ceil向上取整
round四舍五入
注意：
sql中的round支持两个重载，规则：先按绝对值四舍五入，然后再添加正负
round(x):只保留整数部位
round(x,d):保留小数点后一位
java中Math.round只支持一个参数，规则：Math.round(x)等价于Math.floor(x+0.5)
Math.round(x)
rand随机数
二、字符函数
upper转换成大写
lower转换成小写
length获取字节长度
char_length获取字符长度
substr截取子串
trim去掉前后空格或字符
concat拼接
strcmp比较两个字符串
instr获取子串第一次出现的索引，如果找不到，返回0
注意：sql中起始索引，一般从1开始！
三、日期函数
now当前日期+时间
curdate当前日期
curtime当前时间
datediff两个日期天数差
date_format日期——>字符
str_to_date字符——>日期
四、流程控制函数
1、if函数
2、case结构
形式1：类似于switch
形式2：类似于多重if

#一、数学函数
#abs绝对值
SELECT ABS(-1.5);
#floor 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-1.5);
#ceil 向上取整，返回>=该参数的最小整数
SELECT CEIL(-1.5);
#round 四舍五入
SELECT ROUND(-1.56);
SELECT ROUND(-1.56,1);
#truncate 截断
SELECT TRUNCATE(1.67,1);
#rand随机数 0——1之间的小数[0,1)
SELECT RAND();
#mod取余
SELECT MOD(10,3);
SELECT 10%3;
#二、字符函数
#length字节长度
SELECT LENGTH(‘john你好’);
#char_length字符长度
SELECT CHAR_LENGTH(‘john你好’);
#upper转大写
SELECT UPPER(last_name) FROM employees;
#lower转小写
SELECT LOWER(last_name) FROM employees;
#concat拼接
SELECT CONCAT(first_name,last_name) FROM employees;
#substr截取子串

注意：起始索引从1开始
包含起始索引
substr(str,起始索引，截取字符的长度)：截取从起始索引（包含）开始，指定长度的子串
substr(str,起始索引)：截取从起始索引开始，一直到后面所有的子串

SELECT SUBSTR(‘hello,郭襄爱上了杨过’,1,5);
SELECT SUBSTR(‘hello,郭襄爱上了杨过’,12,2);
SELECT SUBSTR(‘hello,郭襄爱上了杨过’,12);
#练习：查询员工的姓名，其中姓首字符大写，其他字符小写。
#名前两个字符大写，去其他字符小写，中间用.拼接，最终起别名 ：姓名
SELECT UPPER(SUBSTR(last_name,1,2))
SELECT LOWER(SUBSTR(last_name,3))
SELECT CONCAT(UPPER(SUBSTR(first_name,1,1)),LOWER(SUBSTR(first_name,2)),’.’,UPPER(SUBSTR(last_name,1,2)),LOWER(SUBSTR(last_name,3))) 姓名
FROM employees;
#instr

类似于java中indexOf
功能：获取子串第一次出现的索引，如果找不到，返回0！

SELECT INSTR(‘郭襄张三丰郭襄爱上了郭襄’,‘郭小襄’);
#trim

trim(【substr from 】str)：去掉str前后的指定的子串。如果substr from省略，默认去掉空格

SELECT TRIM('哈' FROM '哈哈哈哈he哈llo张 三 丰哈哈哈哈') AS 备注;
#strcmp

功能：比较两个字符串大小，
如果前者比后者大，则返回1，
如果前者比后者小，则返回-1
如果相等，则返回0

SELECT STRCMP('hillo','hillo');

#三、日期函数

#获取当前日期+时间
SELECT NOW();
#只获取当前日期，不包含时间
SELECT CURDATE();
#只获取当前时间，不包含日期
SELECT CURTIME();
#获取两个日期之差(前面-后面的天数差)
SELECT DATEDIFF('2018-8-18','2018-8-9');
#将日期转换成指定格式的字符串
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s %W') 日期;
#将字符串解析成日期
SELECT STR_TO_DATE('2018-10-19 10:40:09 Friday','%Y-%m-%d %H:%i:%s %W') 日期;
#案例：将入职日期早于'09-09/2000'的员工信息
SELECT * FROM employees WHERE hiredate<STR_TO_DATE('09-09/2000','%m-%d/%Y');

#四、流程控制函数
#1、if函数
类似于java的三元运算符。
参数1：条件
参数2：条件成立，返回的值
参数3：条件不成立，返回的值

SELECT IF(LENGTH(last_name)>LENGTH(first_name),'名大','姓大') 备注,
last_name,
first_name
FROM employees;

#2、case结构
回顾java的switch case语句
switch(判断的变量){
case 常量值1:语句1；break;
case 常量值2:语句1；break;
case 常量值3:语句1；break;
…
default:语句n;break
}
特点：做等值判断

语法1：类似于switch case，实现等值判断
case 判断的字段或表达式
when 值1 then 显示的值1
when 值2 then 显示的值2
…
【else 显示的值n】
end

#案例：显示不同职位的新工资。AD_VP 3倍  IT_PROG 4倍 FI_MGR 5倍
SELECT salary 原工资,job_id 职位,
CASE job_id
WHEN 'AD_VP' THEN SALARY*3
WHEN 'IT_PROG' THEN SALARY*4
WHEN 'FI_MGR' THEN SALARY*5
#ELSE SALARY
END  新工资
FROM employees;

语法2：类似于多重if的效果，实现区间判断
case
when 条件1 then 显示的值1
when 条件2 then 显示的值2
…
【else 显示的值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 级别
FROM employees;

#1. 显示系统时间(注：日期+时间)
SELECT NOW();
#2. 查询员工号，姓名，工资，以及工资提高百分之 20%后的结果（new salary）
SELECT employee_id,last_name,salary,salary1.2 ‘new salary’
FROM employees;
#3. 将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT last_name,LENGTH(last_name) 长度
FROM employees
ORDER BY SUBSTR(last_name,1,1);
#4. 做一个查询，产生下面的结果
<last_name> earns monthly but wants <salary3>
Dream Salary
King earns 24000 monthly but wants 72000

SELECT CONCAT(last_name,' earns ',salary,' monthly but wants ',salary*3) '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,
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 Grade
FROM employees
WHERE last_name = 'k_ing';