一：字符函数

  1. #进阶四：常见函数
  2. /*
  3. 函数：类似于java中的学过的“方法”，
  4. 为了解决某个问题，将编写的一系列命令集合封装在一起，对外仅仅暴露方法名，供外部使用。
  5.     1.自定义方法（函数）
  6.     2.调用方法（函数）
  7.         叫什么：函数名
  8.         干什么：函数功能
  9. 常见函数：
  10.     1.字符函数
  11.     2.数学函数
  12.     3.日期函数
  13.     4.流程控制函数
  14. */
  16. #一：字符函数
  17.    #1.拼接字符---CONCAT
  18.    SELECT CONCAT('hello"',first_name,last_name) '备注' FROM employees;
  20.    #2.获取字节长度---LENGTH
  21.    SELECT LENGTH('hello,郭襄');
  23.    #3.获取字符长度---CHAR_LENGTH
  24.    SELECT CHAR_LENGTH('hello,郭襄');
  26.    #4.截取子串---SUBSTR
  27.    /*
  28.     SUBSTR(str,起始索引，截取的字符长度)
  29.     注意：sql中起始索引从1开始！！！！！！！！
  30.    */
  31.    SELECT SUBSTR('周杰伦和方文山',1,3);#返回结果：周杰伦
  32.    SELECT SUBSTR('周杰伦和方文山',5);#返回结果：方文山
  34.    #5.获取字符第一次出现的索引---INSTR
  35.    SELECT INSTR('我已分不清那是友情，还是错过的爱情','情');#返回结果：9
  37.    #6.去前后指定的字符，默认是去空格---TRIM
  38.    SELECT TRIM('   周 杰 伦  ');#周 杰 伦
  39.    SELECT TRIM('x' FROM 'xxxxxxxxx周xx杰xx伦xxxxxxx');#周xx杰xx伦
  41.    #7：左填充/右填充---LPAD/RPAD
  42.    SELECT LPAD('周杰伦',10,'a');#aaaaaaa周杰伦
  43.    SELECT RPAD('周杰伦',6,'b');#周杰伦bbb
  45.    #8.变大写/变小写---UPPER/LOWER
  46.    #案例：查询员工表的姓名，姓首字母大写
  47.    SELECT UPPER(SUBSTR(first_name,1,1)) 
  48.    FROM employees;
  50.    #9.比较两个字符的大小---STRCMP
  51.    SELECT STRCMP('aaa','abc');#返回结果：-1
  52.    /*
  53.     返回结果：按照顺序从前往后比，当比到a<b时，就不再比较a和c了
  54.     前面比后面大，返回1
  55.     前面比后面小，返回-1
  56.     一样，返回0
  57.    */
  59.    #10.截取子串---LEFT/RIGHT
  60.    SELECT LEFT('周杰伦',1);#周
  61.    SELECT RIGHT('周杰伦',2);#杰伦

二：数学函数

  1. #数学函数
  2. #1.绝对值---ABS
  3. SELECT ABS(-2.5);#2.5
  5. #2.向上取整---CEIL
  6. SELECT CEIL(1.08);#2
  8. #3.向下取整---FLOOR
  9. SELECT FLOOR(1.08);#1
  11. #4.四舍五入---ROUND
  12. SELECT ROUND(1.23456);#1
  13. SELECT ROUND(1.23456,2);#1.23
  15. #5.截断---TRUNCATE
  16. SELECT TRUNCATE(1.123456,0);#1
  17. SELECT TRUNCATE(1.123456,1);#1.1
  19. #6.取余---MOD(被除数的正负决定了余数的正负)
  20. SELECT MOD(10,3);#1
  21. SELECT MOD(-10,3);#-1
  22. SELECT MOD(10,-3);#1
  23. SELECT MOD(-10,-3);#-1

三：日期函数

  1. #三：日期函数
  2. #1.获取当前日期和时间---NOW
  3. SELECT NOW();
  5. #2.获取当前日期---CURDATE
  6. SELECT CURDATE();
  8. #3.获取当前时间---CURTIME
  9. SELECT CURTIME();
  11. #4.获取两个日期之差---DATEDIFF
  12. SELECT DATEDIFF('1999-9-9','2020-12-13');#-7766
  13. SELECT DATEDIFF('2020-12-13','1999-9-9');#7766
  15. #5.日期转换---DATE_FORMAT
  16. SELECT DATE_FORMAT('1999-9-9','%Y年%m月%d日 %H小时%i分钟%s秒');#1999年09月09日 00小时00分钟00秒
  18. #6，按指定的格式解析字符串为日期---
  19. SELECT STR_TO_DATE('1/15 1998','%m/%d %Y');#1998-01-15

四：流程控制函数

  1. #四：流程控制函数
  2. #1.IF函数
  3. SELECT IF(100>9,'好','坏');#好
  4. SELECT IF(100<9,'好','坏');#坏
  5.     #案例：如果有奖金，则先是最终奖金，如果没有，则现实0
  6.     SELECT IF(commission_pct IS NULL,0,salary*12*commission_pct)
  7.     FROM employees;
  9. #2.CASE函数
  10.     #情况1：类似于switch语句，可以实现等值判断
  11.     /*
  12.     case 表达式
  13.     when 值1 then 结果1
  14.     when 值2 then 结果2
  15.     ……
  16.     else 结果n
  17.     end
  18.     */
  19.     #案例一：部门编号是30，工资变为2倍，部门编号是60，工资变为3倍，否则不变，显示部门部门编号、新工资、旧工资
  20.     SELECT department_id,salary,
  21.     CASE department_id
  22.     WHEN 30 THEN salary*2
  23.     WHEN 60 THEN salary*3
  24.     ELSE salary
  25.     END
  26.     AS '新工资'
  27.     FROM employees;
  28.     #情况2：类似于多重IF语句，可以实现区间判断(无表达式)
  29.      /*
  30.     case
  31.     when 值1 then 结果1
  32.     when 值2 then 结果2
  33.     ……
  34.     else 结果n
  35.     end
  36.     */
  37.     #案例二：如果工资大于20000，显示A，如果工资大于15000，显示B，工资大于10000，显示C，否则显示D
  38.     SELECT salary,
  39.     CASE
  40.     WHEN salary>20000 THEN 'A'
  41.     WHEN salary>15000 THEN 'B'
  42.     WHEN salary>10000 THEN 'C'
  43.     ELSE 'D'
  44.     END
  45.     AS 'grade'
  46.     FROM employees;

五：分组函数

  1. #进阶五：分组函数
  2. /*
  3. 说明：分组函数往往用于实现将一组数据进行统计计算，最终得到一个值，又称为聚合函数或统计函数
  4.     分组函数清单：
  5.         sum(字段名):求和
  6.         avg(字段名):求平均数
  7.         max(字段名):求最大值
  8.         min(字段名):求最小值
  9.         count(字段名):计算非空字段值的个数
  10. */
  11. #案例一：查询员工信息表中所有员工的工资和、工资平均值、最高工资、最低工资、有工资的个数
  12. SELECT SUM(salary),AVG(salary),MAX(salary),MIN(salary),COUNT(salary)
  13. FROM employees;
  15. #count的补充介绍
  16.   #1.统计结果集的行数
  17.    SELECT COUNT(*) FROM employees;#专门统计表中有多少行，避免了非空的干扰
  18.    SELECT COUNT('1') FROM employees;#count("XXX"),在表中插入一个常量列，统计表中有多少行，效果和count(*)一样。
  19.   #2.搭配distinct实现去重的统计
  20.    #案例：查询有员工的部门个数
  21.    SELECT COUNT(DISTINCT department_id)
  22.    FROM employees;