MySQL（五）—— 数据处理函数（单行处理函数）

MySQL数据库系列内容的学习目录$\to$老杜带你学MySQL学习系列内容汇总。

5. 数据处理函数

  数据处理函数又被称为单行处理函数。单行处理函数的特点：一个输入对应一个输出。
  和单行处理函数相对的是：多行处理函数。（多行处理函数特点：多个输入，对应1个输出！）

5.1 lower 转换小写

• 查询员工，将员工姓名全部转换成小写：select lower(ename) as ename from emp;
   
   
    14个输入，最后还是14个输出，这是单行处理函数的特点。

5.2 upper 转换大写

• 查询 job 为 manager 的员工：select * from emp where job=upper('manager');
   

5.3 substr 取子串

  substr 取子串：substr(被截取的字符串， 起始下标，截取的长度)

  语句：select substr(ename, 1, 1) as ename from emp;，查询结果如下图所示。

  查询姓名以 M 开头所有的员工姓名。

• 第一种方式：模糊查询
  select ename from emp where ename like 'M%';
   
   
• 第二种方式：使用substr函数
  select ename from emp where substr(ename, 1, 1)=upper('m');
   
  

5.4 concat函数 进行字符串的拼接

• 将员工编号和姓名拼接起来：select concat(empno,ename) from emp;
   

5.5 length 取长度

• 取得员工姓名长度为 5 的：select length(ename), ename from emp where length(ename)=5;
   
   
• 取得员工姓名的长度：select length(ename) enamelength , ename from emp;
   
  

5.6 trim 去空格

   trim 会去首尾空格，不会去除中间的空格。

• 取得工作岗位为 manager 的所有员工：select * from emp where job=trim(upper('manager '));
     
• select * from emp where ename = ' KING';
     
• select * from emp where ename = trim(' KING');
   
  

5.7 str_to_date 将字符串转换成日期

• 查询 1981-02-20 入职的员工（第一种方法，与数据库的格式匹配上）：select * from emp where HIREDATE='1981-02-20';
   
   
• 查询 1981-02-20 入职的员工（第二种方法，将字符串转换成 date 类型）
  select * from emp where HIREDATE=str_to_date('1981-02-20','%Y-%m-%d');
 
 
select * from emp where HIREDATE=str_to_date('02-20-1981','%m-%d-%Y');
   
  

  str_to_date 可以将字符串转换成日期，具体格式 str_to_date (字符串，匹配格式)。

5.8 date_format 格式化日期

• 查询 1981-02-20 以后入职的员工，将入职日期格式化成 yyyy-mm-dd hh:mm:ss：select empno, ename, date_format(hiredate, '%Y-%m-%d %H:%i:%s') as hiredate from emp;
   
  

   select date_format(now(),'%Y-%m-%d %H %i %s');，now() 获得当前时间。
   日期格式的说明：%Y：代表 4 位的年份
                %y：代表 2 位的年份
                %m：代表月， 格式为(01……12)
                %c：代表月， 格式为(1……12)
                %H：代表小时，格式为(00……23)
                %h： 代表小时，格式为(01……12)
                %i： 代表分钟，格式为(00……59)
                %r：代表 时间，格式为 12 小时(hh:mm:ss [AP]M)
                %T：代表 时间，格式为 24 小时(hh:mm:ss)
                %S：代表 秒，格式为(00……59)
                %s：代表 秒，格式为(00……59)

5.9 format 设置千分位

• 查询员工薪水加入千分位：select empno, ename, Format(sal, 0) from emp;
   
   
• 查询员工薪水加入千分位和保留两位小数：select empno, ename, Format(sal, 2) from emp;
   
  

5.10 round 四舍五入

• 四舍五入（保留到个位）：select round(123.56);
   
   
• 四舍五入（保留1个小数）：select round(123.56,1);
   
  

  语句：select 字段 from 表名;
  例如，select ename from emp;

  select后面直接跟“字面量/字面值”：select 'abc' from emp;

  select 'abc' as bieming from emp;

  select abc from emp;

  这样肯定报错，因为会把abc当做一个字段的名字，在emp表中找abc字段。

  1000 被当做一个字面量/字面值：select 1000 as num from emp;
 

  结论：select后面可以跟某个表的字段名（可以等同看做变量名），也可以跟字面量/字面值（数据）。

• 保留整数位：select round(1236.567, 0) as result from emp;
• 保留1个小数：select round(1236.567, 1) as result from emp;
• 保留2个小数：select round(1236.567, 2) as result from emp;
• 保留到十位：select round(1236.567, -1) as result from emp;
• 保留到百位：select round(1236.567, -2) as result from emp;

5.11 rand()生成随机数

• 生成随机数：select rand();
   
   
• 随机抽取记录数：select * from emp order by rand() limit 2;
 
 
order by 必须写上。

5.12 case … when … then ……else …end

• 如果 job 为 MANAGERG 薪水上涨 10%，如果 job 为 SALESMAN 工资上涨 50%：select empno, ename, job, sal, case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 end as newsal from emp;
   
   
  其他的工资不动，需要添加 else：select e.*,sal ,case job when 'salesman' then sal*1.1 when 'clerk' then sal*1.2 else sal end as new_sal from emp e;
 
 
e.*：取 emp 表所有的字段。

5.13 ifnull可以将null转化成一个具体值

  ifnull是空处理函数，专门处理空的。例如，select ifnull(comm,0) from emp;，如果 comm 为 null 就替换为 0。

  在所有数据库当中，只要有NULL参与的数学运算，最终结果就是NULL。
  为了防止计算结果出现 NULL，建议先使用 ifnull 空值处理函数预先处理。
  ifnull函数用法：ifnull(数据, 被当做哪个值)，如果“数据”为NULL的时候，把这个数据结构当做哪个值。

• 计算年薪：年薪 = (月薪 + 月补助) * 12
  select empno,ename,sal, (sal + comm) * 12 as yearsal from emp;
   
   
  有结果为NULL的，因为有NULL参与运算，所以需要使用 ifnull 空值处理函数预先处理。当补助为NULL的时候，将补助当做0。
  select empno,ename,sal,(sal+ifnull(comm,0))*12 as yearsal from emp;