//MySQL核心技术// DQL语言的学习——常见函数（字符函数、数学函数、日期函数、流程控制函数）的用法

常见函数

1.函数：类似于java中学过的“方法”，
为了解决某个问题，将编写的一系列的命令集合封装在一起，对外仅仅暴露方法名，供外部调用
     1、自定义方法(函数)
     2、调用方法(函数)★
         叫什么  ：函数名
        干什么  ：函数功能
2.常见函数：
    字符函数
        concat
        substr
        length
        char_length
        upper
        lower
        trim
        left
        right
        lpad
        rpad
        instr
        strcmp

    数学函数
        abs
        ceil
        floor
        round
        truncate
        mod
        
    日期函数
        now 
        curtime
        curdate
        datediff
        date_format
        str_to_date
        
        流程控制函数
        if
        case
3.案例

字符函数

1、CONCAT 拼接字符

SELECT CONCAT('hello,',first_name,last_name)  备注 FROM employees;

2、LENGTH 获取字节长度

SELECT LENGTH('hello,郭襄');    #结果为12，因为UTF8中一个汉字的字节数为3个。

3、CHAR_LENGTH 获取字符个数
SELECT CHAR_LENGTH('hello,郭襄');     #结果为8

4、SUBSTRING 截取子串
/*
注意：起始索引从1开始！！！
substr(str,起始索引，截取的字符长度)
substr(str,起始索引)   #默认结果起始索引之后的字符
*/
SELECT SUBSTR('张三丰爱上了郭襄',1,3);     #结果为张三丰
SELECT SUBSTR('张三丰爱上了郭襄',7);     #结果为郭襄

5、INSTR 获取字符第一次出现的索引
SELECT INSTR('三打白骨精aaa白骨精bb白骨精','白骨精');  #结果为3

6、TRIM去前后指定的字符，默认是去空格
SELECT TRIM(' 虚  竹    ')  AS a;   #结果为虚   竹，去掉了左右的空格
SELECT TRIM('x' FROM 'xxxxxx虚xxx竹xxxxxxxxxxxxxxxxxx')  AS a;    #结果为虚xxx竹，去掉了前后指定的字符x

7、LPAD/RPAD  左填充/右填充
SELECT LPAD('木婉清',10,'a');   #结果为aaaaaaa木婉清，左填充lpad，使其为10个字符
SELECT RPAD('木婉清',10,'a');   #结果为木婉清aaaaaaa，右填充rpad，使其为10个字符

8、UPPER/LOWER  变大写/变小写
#案例：查询员工表的姓名，要求格式：姓首字符大写，其他字符小写，名所有字符大写，且姓和名之间用_分割，最后起别名“OUTPUT”
SELECT UPPER(SUBSTR(first_name,1,1)),first_name FROM employees;   #需要先用substr截取子串，截取姓的第一个字符，并把它大写
SELECT LOWER(SUBSTR(first_name,2)),first_name FROM employees;   #截取姓的其他字符，并将其小写
SELECT UPPER(last_name) FROM employees;    #将名字的所有字符小写

SELECT CONCAT(UPPER(SUBSTR(first_name,1,1)),LOWER(SUBSTR(first_name,2)),'_',UPPER(last_name)) "OUTPUT"
FROM employees;   最后将他们拼接起来，并起别名

9、STRCMP 比较两个字符大小
SELECT STRCMP('aec','abc');    #前面比后面大，结果为1，先比较第一个字符，大即为1，相同的话再比较下一个。
前者比后者小，结果为-1，如 aab，aca
两者一样大，结果为0。如aae，aae

10、LEFT/RIGHT  截取子串
SELECT LEFT('鸠摩智',1);    #左边截取指定长度子串，截取1个字符，即鸠
SELECT RIGHT('鸠摩智',2);  #右边截取指定长度子串，截取2个，即摩智

数学函数

1、ABS 绝对值
SELECT ABS(-2.4);
2、CEIL 向上取整  返回>=该参数的最小整数
SELECT CEIL(-1.09);  #结果为-1
SELECT CEIL(0.09); #结果为1
SELECT CEIL(1.00);  #结果为1

3、FLOOR 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-1.09); #结果为-2
SELECT FLOOR(0.09);#结果为0
SELECT FLOOR(1.00);#结果为1

4、ROUND 四舍五入
SELECT ROUND(1.8712345); #四舍五入小数点位数为0位，即1.8712345 
SELECT ROUND(1.8712345,2); #四舍五入保留小数点两位，即1.87

5、TRUNCATE 截断

SELECT TRUNCATE(1.8712345,1);  #即1.8，只要小数点后一位，后面直接舍去，不进行四舍五入

6、MOD 取余

SELECT MOD(-10,3);  #结果为-1

小技巧：余数的正负取决于被除数的符号

证明：
a%b = a-(INT)a/b*b
-10%3 = -10 - (-10)/3*3   = -1

SELECT -10%3;  #结果为-1
SELECT 10%3;#结果为1
SELECT -10%-3;#结果为-1
SELECT 10%-3;#结果为1

日期函数

1、NOW
SELECT NOW(); #获取当前日期和时间  2020-02-26 00:21:28

2、CURDATE

SELECT CURDATE(); #获取当前日期 2020-02-26

3、CURTIME
SELECT CURTIME();#获取当前时间  00:22:47

4、DATEDIFF
SELECT DATEDIFF('1998-7-16','2019-7-13');#获取时差，前面减去后面的天数

5、DATE_FORMAT

SELECT DATE_FORMAT('1998-7-16','%Y年%m月%d日 %H小时%i分钟%s秒') 出生日期; 

#指定日期格式 1998年07月16日 00小时00分钟00秒

SELECT DATE_FORMAT(hiredate,'%Y年%M月%d日 %H小时%i分钟%s秒')入职日期  
FROM employees;

#1992年April月03日 00小时00分钟00秒。。。。。。

6、STR_TO_DATE
SELECT * FROM employees
WHERE hiredate<STR_TO_DATE('3/15 1998','%m/%d %Y');  #按指定格式解析字符串为日期类型

流程控制函数

1、IF函数

SELECT IF(100>9,'好','坏'); #如果表达式1成立，显示表达式2，否则显示表达式3
#需求：如果有奖金，则显示最终奖金，如果没有，则显示0
SELECT IF(commission_pct IS NULL,0,salary*12*commission_pct)  奖金,commission_pct
FROM employees;

2、CASE函数

①情况1 ：类似于switch语句，可以实现等值判断
CASE 表达式
WHEN 值1 THEN 结果1
WHEN 值2 THEN 结果2
...
ELSE 结果n
END

案例：
部门编号是30，工资显示为2倍
部门编号是50，工资显示为3倍
部门编号是60，工资显示为4倍
否则不变

显示 部门编号，新工资，旧工资

SELECT department_id,salary,
CASE department_id
WHEN 30 THEN salary*2
WHEN 50 THEN salary*3
WHEN 60 THEN salary*4
ELSE
salary
END
newSalary  #起名为新工资
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
 AS  a
 FROM employees;   

案例

#1.将员工的姓名按首字母排序，并写出姓名的长度（length）

SELECT LENGTH(last_name) 长度
FROM employees
ORDER BY SUBSTR(last_name,1,1) ASC; #截取起始位置1，截取一个字符

#2. 做一个查询，产生下面的结果
/*
<last_name> earns <salary> 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;#即拼接字符串，

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