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';