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,salary*1.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 <salary*3> 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';