#进阶4：常见函数
/*
调用： select 函数名（实参列表） 【from 表】
分类： 1.单行函数，；
2.分组函数，做统计使用，
*/
/*
常用函数：
1.字母函数：
length
concat
substr
instr
upper
lower
trim
lpad
rpad
replace
2.数学函数：
round
ceil
floor
truncate
mod
3.日期函数：
now
curdate
curtime
year
month
monthname
day
hour
mimute
second
str_to_date
date_format
4.其他函数：
version
database
user
5.流程控制函数：
if case
*/
#一、字母函数：
#1.length 获取参数值的字节个数
SELECT
last_name,
LENGTH(last_name) AS 字节个数
FROM
employees ;
#2.concat 拼接字符串
SELECT
CONCAT(last_name, '_', first_name) AS 'name'
FROM
employees ;
#3.substr/substring 切片，字符串的子串
SELECT SUBSTR('i love you',8); #索引从1开始，从第8个字符至最后
SELECT SUBSTR('i love you',3,4); #从第3个字符选择4个长度子串
##案例：姓名中首字符大写，其他字符小写然后拼接显示
SELECT
CONCAT(
UPPER(SUBSTR(last_name, 1, 1)),
'_',
LOWER(SUBSTR(last_name, 2))
) AS 'Name'
FROM
employees ;
#4.instr
SELECT INSTR('mysql is so fun!','fun'); #第一个子串出现的索引位置，若不存在返回0
#5.upper 字符全变大写
SELECT
UPPER(last_name)
FROM
employees ;
#6.lower 字符全变小写
SELECT
LOWER(last_name)
FROM
employees ;
#7.trim 消除前后的空格或指定字符，默认为消除空格
##消除空格
SELECT
TRIM(' mysal '),
LENGTH(TRIM(' mysal ')) AS out_put ;
##消除指定字符
SELECT
TRIM('a' FROM 'aaaaaaaaaamysalaaaaaaa'),
LENGTH(TRIM('a' FROM 'aaaaaaaaaamysalaaaaaaa')) AS out_put ;
#8.lpad 用指定字符填空左边，达到指定长度字符
SELECT LPAD('lpad',10,'Ha');
##若指定长度小于原字符串长度，会出现类似切片结果
SELECT LPAD('lpad',3,'Ha');
#9.rpad 用指定字符填空左边，达到指定长度字符
SELECT RPAD('rpad',10,'Ha');
##若指定长度小于原字符串长度，会出现类似切片结果
SELECT RPAD('rpad',3,'Ha');
#10.replace 替换指定字符或字符串（全部）
SELECT 'mysql is good tool!' ,
REPLACE('mysql is good tool!','mysql','python');
#二、数学函数
#round 四舍五入
SELECT ROUND(1.55);
SELECT ROUND(1.55,1);#默认进为整数，可选择保留小数点后几位
#ceil 向上取整，>=原数值的最小整数
SELECT CEIL(-1.02);
SELECT CEIL(9.1);
#floor 向下取整，<=原数值的最大整数
SELECT FLOOR(-1.02);
SELECT FLOOR(9.1);
#truncate 截取，保留小数点后几位
SELECT TRUNCATE(3.141592653,3);
SELECT TRUNCATE(3.141592653,5);
#mod 取余
SELECT MOD(10,3);
#三、日期函数：
#now 当前日期+时间
SELECT NOW();
#curdate 当前日期
SELECT CURDATE();
#curtime 当前时间
SELECT CURTIME();
#year month day 当前年/月/日/时/分/秒
#hour mimute second 实参需要为日期时间
SELECT YEAR(NOW());
SELECT HOUR(NOW());
#monthname 月份名 英文 实参需要为日期时间
SELECT MONTHNAME(NOW());
#str_to_date 字符串转化为标准日期格式
SELECT STR_TO_DATE('3-2-1996','%m-%d-%Y');
#案例：查询入职日期为1992-4-3的员工信息
SELECT *
FROM employees
WHERE hiredate='1992-4-3';
#非标准格式日期‘4-3 1992’
SELECT *
FROM employees
WHERE hiredate=STR_TO_DATE('4-3 1992','%m-%d %Y');
#date_format 标准格式日期转换为字符
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日');
SELECT DATE_FORMAT('1992-2-2','%Y年%m月%d日');
#四、其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
#五、流程控制函数
#1.if函数 if else 效果
##语法：if(条件？true结果，false结果)
SELECT IF(10>5,1,0);
#案例：
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'Crying','laughing')
FROM employees;
#2.case 函数
/*
使用一： switch-case效果 （等值判断）
case 选择条件
when 常量1 then 要显示值1或语句1【;(语句加; 值不加)】
when 常量2 then 要显示值2或语句2【;】
when 常量3 then 要显示值3或语句3【;】
......
else 要显示值n或语句n【;】
end
使用二： if elseif ...效果 （区间判断）
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 原工资,last_name,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
ORDER BY salary DESC;
/*用法二案例：查询员工工资,要求：
若工资大于20000，显示A级别
若工资大于15000，显示B级别
若工资大于10000，显示C级别
否则，显示D级别
*/
SELECT salary,last_name,department_id,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees
WHERE department_id BETWEEN 10 AND 110
ORDER BY salary ASC;
#====================Test=====================
#1.显示系统时间
SELECT NOW();
SELECT DATE_FORMAT(NOW(),'%y/%m/%d/%h/%i/%s') AS Newformat;
#2.查询员工号，姓名，工资，以及工资提高百分之20后的结果
SELECT employee_id,last_name,salary,salary*1.2 AS 'new salary'
FROM employees;
#3.将员工姓名按首字母排序，并写出姓名长度升序
SELECT
last_name,
SUBSTR(last_name, 1, 1) AS 首字母,
LENGTH(last_name) AS 长度
FROM
employees
ORDER BY 首字母,长度;
#4.做一个查询，产生下面结果
SELECT CONCAT(last_name,' earns ',salary,' monthly, but wants ',salary*3) AS DreamSalary
FROM employees;
#5.使用case-when 完成job等级
/*
job grade
AD_PRES A
ST_MAN B
IT_PROG C
*/
SELECT last_name,job_id,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
ELSE 'D'
END AS job_grade
FROM employees;
#
#
#二、分组函数
/*
功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：
sum 求和、avg 平均值、 max 最大值、 min 最小值、count 计算个数
特点：
1、sum、avg 一般用于处理数值型
max、min、count 可以处理任何类型
2、以上分组函数都忽略null值
3、可以和distinct搭配实现 先去重再运算
4、count函数的单独介绍
一般使用count(*)用作统计行数，count(1)也可以统计行数
5、和分组函数一同查询的有字段限制，要求是group by后的字段
*/
#案例：
SELECT SUM(salary),AVG(salary) FROM employees;
SELECT MAX(last_name),MIN(first_name),COUNT(commission_pct) FROM employees;
SELECT SUM(salary),SUM(DISTINCT salary) FROM employees;
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;
SELECT AVG(commission_pct),last_name FROM employees;#失败，只显示了一行
#=======================Test================================
#1.查询公司员工工资的最大值，最小值，平均值，总和
SELECT
MAX(salary) AS 最大值,
MIN(salary) AS 最小值,
ROUND(AVG(salary),2) AS 平均值,
SUM(salary) AS 总和
FROM
employees ;
#2.查询员工表中的最大入职时间和最小入职时间的相差天数（datediff）
SELECT
MAX(hiredate),MIN(hiredate),
DATEDIFF(MAX(hiredate), MIN(hiredate)) AS maxdiff
FROM
employees ;
##扩展一下：活了多少天？
SELECT
DATEDIFF(NOW(), '1996-3-25');
SELECT
DATEDIFF(NOW(), '1995-9-25');
#3.查询部门编号为90的员工个数
SELECT
COUNT(*)
FROM
employees
WHERE department_id = 90 ;
##
SELECT
*
FROM
employees
WHERE department_id = 90 ;
/*
调用： select 函数名（实参列表） 【from 表】
分类： 1.单行函数，；
2.分组函数，做统计使用，
*/
/*
常用函数：
1.字母函数：
length
concat
substr
instr
upper
lower
trim
lpad
rpad
replace
2.数学函数：
round
ceil
floor
truncate
mod
3.日期函数：
now
curdate
curtime
year
month
monthname
day
hour
mimute
second
str_to_date
date_format
4.其他函数：
version
database
user
5.流程控制函数：
if case
*/
#一、字母函数：
#1.length 获取参数值的字节个数
SELECT
last_name,
LENGTH(last_name) AS 字节个数
FROM
employees ;
#2.concat 拼接字符串
SELECT
CONCAT(last_name, '_', first_name) AS 'name'
FROM
employees ;
#3.substr/substring 切片，字符串的子串
SELECT SUBSTR('i love you',8); #索引从1开始，从第8个字符至最后
SELECT SUBSTR('i love you',3,4); #从第3个字符选择4个长度子串
##案例：姓名中首字符大写，其他字符小写然后拼接显示
SELECT
CONCAT(
UPPER(SUBSTR(last_name, 1, 1)),
'_',
LOWER(SUBSTR(last_name, 2))
) AS 'Name'
FROM
employees ;
#4.instr
SELECT INSTR('mysql is so fun!','fun'); #第一个子串出现的索引位置，若不存在返回0
#5.upper 字符全变大写
SELECT
UPPER(last_name)
FROM
employees ;
#6.lower 字符全变小写
SELECT
LOWER(last_name)
FROM
employees ;
#7.trim 消除前后的空格或指定字符，默认为消除空格
##消除空格
SELECT
TRIM(' mysal '),
LENGTH(TRIM(' mysal ')) AS out_put ;
##消除指定字符
SELECT
TRIM('a' FROM 'aaaaaaaaaamysalaaaaaaa'),
LENGTH(TRIM('a' FROM 'aaaaaaaaaamysalaaaaaaa')) AS out_put ;
#8.lpad 用指定字符填空左边，达到指定长度字符
SELECT LPAD('lpad',10,'Ha');
##若指定长度小于原字符串长度，会出现类似切片结果
SELECT LPAD('lpad',3,'Ha');
#9.rpad 用指定字符填空左边，达到指定长度字符
SELECT RPAD('rpad',10,'Ha');
##若指定长度小于原字符串长度，会出现类似切片结果
SELECT RPAD('rpad',3,'Ha');
#10.replace 替换指定字符或字符串（全部）
SELECT 'mysql is good tool!' ,
REPLACE('mysql is good tool!','mysql','python');
#二、数学函数
#round 四舍五入
SELECT ROUND(1.55);
SELECT ROUND(1.55,1);#默认进为整数，可选择保留小数点后几位
#ceil 向上取整，>=原数值的最小整数
SELECT CEIL(-1.02);
SELECT CEIL(9.1);
#floor 向下取整，<=原数值的最大整数
SELECT FLOOR(-1.02);
SELECT FLOOR(9.1);
#truncate 截取，保留小数点后几位
SELECT TRUNCATE(3.141592653,3);
SELECT TRUNCATE(3.141592653,5);
#mod 取余
SELECT MOD(10,3);
#三、日期函数：
#now 当前日期+时间
SELECT NOW();
#curdate 当前日期
SELECT CURDATE();
#curtime 当前时间
SELECT CURTIME();
#year month day 当前年/月/日/时/分/秒
#hour mimute second 实参需要为日期时间
SELECT YEAR(NOW());
SELECT HOUR(NOW());
#monthname 月份名 英文 实参需要为日期时间
SELECT MONTHNAME(NOW());
#str_to_date 字符串转化为标准日期格式
SELECT STR_TO_DATE('3-2-1996','%m-%d-%Y');
#案例：查询入职日期为1992-4-3的员工信息
SELECT *
FROM employees
WHERE hiredate='1992-4-3';
#非标准格式日期‘4-3 1992’
SELECT *
FROM employees
WHERE hiredate=STR_TO_DATE('4-3 1992','%m-%d %Y');
#date_format 标准格式日期转换为字符
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日');
SELECT DATE_FORMAT('1992-2-2','%Y年%m月%d日');
#四、其他函数
SELECT VERSION();
SELECT DATABASE();
SELECT USER();
#五、流程控制函数
#1.if函数 if else 效果
##语法：if(条件？true结果，false结果)
SELECT IF(10>5,1,0);
#案例：
SELECT last_name,commission_pct,IF(commission_pct IS NULL,'Crying','laughing')
FROM employees;
#2.case 函数
/*
使用一： switch-case效果 （等值判断）
case 选择条件
when 常量1 then 要显示值1或语句1【;(语句加; 值不加)】
when 常量2 then 要显示值2或语句2【;】
when 常量3 then 要显示值3或语句3【;】
......
else 要显示值n或语句n【;】
end
使用二： if elseif ...效果 （区间判断）
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 原工资,last_name,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
ORDER BY salary DESC;
/*用法二案例：查询员工工资,要求：
若工资大于20000，显示A级别
若工资大于15000，显示B级别
若工资大于10000，显示C级别
否则，显示D级别
*/
SELECT salary,last_name,department_id,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END AS 工资级别
FROM employees
WHERE department_id BETWEEN 10 AND 110
ORDER BY salary ASC;
#====================Test=====================
#1.显示系统时间
SELECT NOW();
SELECT DATE_FORMAT(NOW(),'%y/%m/%d/%h/%i/%s') AS Newformat;
#2.查询员工号，姓名，工资，以及工资提高百分之20后的结果
SELECT employee_id,last_name,salary,salary*1.2 AS 'new salary'
FROM employees;
#3.将员工姓名按首字母排序，并写出姓名长度升序
SELECT
last_name,
SUBSTR(last_name, 1, 1) AS 首字母,
LENGTH(last_name) AS 长度
FROM
employees
ORDER BY 首字母,长度;
#4.做一个查询，产生下面结果
SELECT CONCAT(last_name,' earns ',salary,' monthly, but wants ',salary*3) AS DreamSalary
FROM employees;
#5.使用case-when 完成job等级
/*
job grade
AD_PRES A
ST_MAN B
IT_PROG C
*/
SELECT last_name,job_id,
CASE job_id
WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
ELSE 'D'
END AS job_grade
FROM employees;
#
#
#二、分组函数
/*
功能：用作统计使用，又称为聚合函数或统计函数或组函数
分类：
sum 求和、avg 平均值、 max 最大值、 min 最小值、count 计算个数
特点：
1、sum、avg 一般用于处理数值型
max、min、count 可以处理任何类型
2、以上分组函数都忽略null值
3、可以和distinct搭配实现 先去重再运算
4、count函数的单独介绍
一般使用count(*)用作统计行数，count(1)也可以统计行数
5、和分组函数一同查询的有字段限制，要求是group by后的字段
*/
#案例：
SELECT SUM(salary),AVG(salary) FROM employees;
SELECT MAX(last_name),MIN(first_name),COUNT(commission_pct) FROM employees;
SELECT SUM(salary),SUM(DISTINCT salary) FROM employees;
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;
SELECT AVG(commission_pct),last_name FROM employees;#失败，只显示了一行
#=======================Test================================
#1.查询公司员工工资的最大值，最小值，平均值，总和
SELECT
MAX(salary) AS 最大值,
MIN(salary) AS 最小值,
ROUND(AVG(salary),2) AS 平均值,
SUM(salary) AS 总和
FROM
employees ;
#2.查询员工表中的最大入职时间和最小入职时间的相差天数（datediff）
SELECT
MAX(hiredate),MIN(hiredate),
DATEDIFF(MAX(hiredate), MIN(hiredate)) AS maxdiff
FROM
employees ;
##扩展一下：活了多少天？
SELECT
DATEDIFF(NOW(), '1996-3-25');
SELECT
DATEDIFF(NOW(), '1995-9-25');
#3.查询部门编号为90的员工个数
SELECT
COUNT(*)
FROM
employees
WHERE department_id = 90 ;
##
SELECT
*
FROM
employees
WHERE department_id = 90 ;
京公网安备 11010502036488号