五：常见函数及练习（字符函数、数学函数、日期函数、流程控制函数）

#进阶四：常见函数

/*

函数：类似与Java中学过的“方法”，将编写的一系列的命令集合封装在一起，对外仅仅暴露方法名，共外部的调用

1、自定义方法

2、调用方法（函数）

       叫什么：函数名

       干什么：函数功能

      常见函数：

               字符函数

               数学函数

               日期函数

               流程控制函数                        

*/

#一、字符函数

1、CONCAT 拼接字符

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

2、LENGTH 获取字节长度

     SELECT LENGTH('hello');
     SELECT LENGTH('hello,郭辰');   #一个函数占三个字节

3、CHAR_LENGTH 获取字符个数

 SELECT CHAR_LENGTH('hello,郭辰');  #逗号也是一个字符

4、SUBSTRING 截取子串

    /*

      注意：索引从1开始

       substr(str,起始索引，截取的字符长度)

        substr(str,起始索引)

   */

     SELECT SUBSTRING('张三丰和郭辰谁厉害',1,3);  #sql中的索引从1开始
     SELECT SUBSTRING('张三丰和郭辰谁厉害',5);　　

5、INSTR 获取字符第一次出现的索引

 SELECT INSTR('三打白骨精aa白骨精bb白骨精','白骨精');

6、TRIM 去前后指定的字符，默认是空格

     SELECT TRIM('   虚   竹     ') AS a; 
     SELECT TRIM('x' FROM 'xxxxxx虚xxx竹xxxxxxxxxxxx') AS a;   去掉前后指定字符x

7、LPAD/RPAD 左填充/右填充

     SELECT LPAD('木婉清',10,'a');
     SELECT RPAD('木婉清',10,'a');

8、UPPER/LOWER 变大写/变小写

#案例：查询员工表的姓名，要求格式：姓首字符大写，其他字符小写，

#      名所有字符大写，且姓和名之间用_分割，最后起别名“OUTPUT”     #04完

     SELECT UPPER(SUBSTR(`first_name`,1,1)),`first_name` FROM `employees`; #截取姓的第一个字符变大写
     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 比较两个字符的大小

      /*

        STRCMP(str1,str2)

         若str1<str2  则返回 1

         若str1>str2  则返回 -1

         若str1=str2  则返回 0

     */

      SELECT STRCMP('aab','abc');
      SELECT STRCMP('abb','aac');
      SELECT STRCMP('abc','abc');

10、LEFT/RIGHT 截取子串

         SELECT LEFT('鸠摩智',1);  左边截取1个字符
         SELECT RIGHT('鸠摩智',2);     从右边截取2个字符      　　

#二、数学函数

1、ABS 绝对值

     SELECT ABS(-2.4);

2、CEIL 向上取整,返回>=该参数的最小整数

     SELECT CEIL(1.09);
     SELECT CEIL(-1.09);

3、FLOOR 向下取整，返回<=该参数的最小整数

      SELECT FLOOR(1.09);
      SELECT FLOOR(-1.09);

4、ROUND 四舍五入

      SELECT ROUND(1.6523);
      SELECT ROUND(1.6553,2); #保留小数点后两位

5、TRUNCATE 截断 

     SELECT TRUNCATE(1.6553,2); #小数点后取两位

6、MOD 取余

     SELECT MOD(-10,3);  #被除数的正负决定了结果的正负
     SELECT -10%3;    #06完

#三、日期函数

1、NOW 获取当前日期和时间

     SELECT NOW();

2、CURDATE 获取当前日期

     SELECT CURDATE();

3、CURTIME 获取当前时间

    SELECT CURTIME();

4、DATEDIFF (data1,data2)  #data1-data2,查看两个日期之间相差多少天

    SELECT DATEDIFF('1998-8-7','2020-11-20');

5、DATE_FORMAT 数字转换成日期

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

 

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

    

6、STR_TO_DATE 按指定格式解析字符串为日期类型

   SELECT * FROM `employees`
   WHERE `hiredate`<STR_TO_DATE('3/15 1998','%m/%d %y');       　　

   

#四、流程控制函数

1、IF 函数（类似与Java中过的三元运算符）

SELECT IF(100>9,'好','坏');

#需求：如果有奖金，则显示最终奖金，如果没有，则显示0

SELECT IF(`commission_pct` IS NULL,0,'`salary`*12*`commission_pct`') 奖金,`commission_pct`
FROM `employees`;

2、CASE函数

情况一：

语法：

        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 AS newSalary
    FROM `employees`;

 

情况二：类似于多重IF语句，实现区间判断

语法：

         CASE

         WHEN 条件1 THEN 结果1

         WHEN 条件2 THEN 结果2

          ...

         ELSE 结果

         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 级别
FROM `employees`;

 

#练习

#1、显示系统时间（时间+日期）

      SELECT NOW();

#2、查询员工号，姓名，工资，以及工资提高百分之 20% 后的结果（new salary）

     SELECT `employee_id`,`last_name`,`salary`,`salary`*1.2 AS "new salary" FROM`employees`;

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

      SELECT LENGTH(`last_name`) 长度
      FROM `employees`
     ORDER BY SUBSTR(`last_name`,1,1) ASC;

#4、做一个查询，产生下面的结果

    /*

      <last_name> earns <salary> monthly but wants <salary*3>

         Dream Salary

       King earns 2400 montly but wants 72000

   */

     SELECT CONCAT(`last_name`,'earns',`salary`,'monthly but wants',`salary`*3)
     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
    AS Grade
    FROM `employees`;