MYSQL初阶学习笔记——进阶4：常见函数

#进阶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 ;