数据处理函数

B站老杜——MySQL入门

• 又被称为单行处理函数

• 特点：一个输入对应一个输出

• 和单行处理函数相对的是：多行处理函数（特点：多个输入，对应一个输出）

• 常见单行处理函数：

  // 1.lower() 转换小写 
  select lower(ename) from emp;
  
  // 2.upper() 转换大写
  select upper(ename) from emp;
  
  // 3.substr() 取子串( substr(被截取的字符串，起始下标，截取的长度) )
  select substr(ename, 1, 1) from emp;
  //找出员工名字第一个字母是A的员工信息
  //第一种：模糊查询
  select ename from emp where ename like 'A%';
  //第二种：substr()函数
  select ename from emp where substr(ename, 1, 1) = 'A';
  
  // 4.concat() 字符串拼接
  select concat(empno, ename) from emp;
  //员工中首字母大写为A的员工信息
  select concat(substr(ename,1,1),lower(substr(ename,2,length(ename)-1)) as result from emp;
    			
  // 5.length() 字符串长度
  select length(ename) enamelength from emp;
    			
  // 6.trim() 去空格
  select * from emp where ename = ' king'; 
    			
  // 7.str_to_date() 将字符串varchar类型转换为日期date类型
  //通常使用在插入insert方面，因为插入的时候需要一个日期类型的数据，
  //需要通过该函数将字符串转换成date。
  //如果提供的日期字符串是如下格式，str_to_date函数可以不需要
  insert into t_user(id,name,birth) value(1,'zhangsan','1990-10-01');
  str_to_date('字符串日期','日期格式')
  mysql的日期格式：
     %Y 年
     %m 月
     %d 日
     %h 时
     %i 分
     %s 秒
  insert into t_user(id,name,birth)
  value(1,'zhangsan',str_to_date('1990-01-10','%Y-%m-%d'));
  
  // 8.date_format 将date类型转换成具有一定格式的varchar字符串类型
  //date_format(日期类型数据, '日期格式');
  //这个函数可以将日期类型转换成特定格式的字符串.设置展示的日期格式
  select id,name,date_format(birth,'%m/%d/%Y') as birth from t_user;
    			
  // 9.format() 格式化数字
  select emp,format(sal, '$999.999') as sal from emp;
    			
  // 10.round() 四舍五入
  select round(1234.5677, 0) as result from emp;//保留整数位
  select round(1234.5677, 1) as result from emp;//保留一位小数
  select round(1234.5677, -1) as result from emp;//保留到十位
    			
  // 11.rand() 生成随机数
  select rand() from emp;
    			
  // 12.ifnull(数据, 被当作哪个值) 可以将null转换成一个具体值
  // 如果'数据'为null的时候，把这个数据结构当做哪个值
  // 空处理函数，专门处理空的
  // 计算每个员工的年薪
  // select ename, (sal + ifnull(comm, 0)) * 12 from emp;
  //13. case..when..then..when..then..else..end
  // 当员工的工作岗位是manager的时候，工资上调10%,当工作岗位是salesman的时候，工资上调50%(注意：不修改数据库，只是将查询结果显示进行修改)
  select ename,job,sal, (case job when 'manager' then sal*1.1 when 'salesmen' then sal*1.5 else sal end) as newsal from emp;
  //条件函数：if(x=n,a,b)：表示如果x=n，则返回啊，否则返回b
    			
  // 14. now() 函数，获取系统当前时间：时分秒信息
  

• 注意：

  • substr()中下标是从1开始的，没有0
  • 在所有数据库中，只要有null参与的数学运算，最终结果一定是null

分组函数（多行处理函数）

• 多行处理函数特点：输入多行，最终输出一行

• 常见多行处理函数（使用前先分组）

  // max() 最大值
  select max(sal) from emp;
  
  // min() 最小值
  select min(sal) from emp;
  
  // sum() 求和
  //计算工资和
  select sum(sal) from emp;
  
  // avg() 求平均和
  select avg(sal) from emp;
  
  // count() 统计
  select count(ename) from emp;
  

• 注意：

  • 分组函数在使用的时候必须先进行分组，然后才能使用
  • 如果没有对数据进行分组，则默认整张表为一个分组
  • 分组函数自动忽略null，不需要提前对null进行处理
  • 分组函数中count(*) 和count(具体字段)的区别
    • count(具体字段)：表示统计该字段下所有不为null的元素的总数
    • count(*)：统计表当中的总行数。（只要有一行数据，count则++）因为每一行记录不可能都为null，一行数据中有一列不为null，则这行数据就是有效的
  • 分组函数不能直接使用在where子句中
  • 所有的分组函数可以组合起来一起用

去除重复记录-distinct

• distinct只能出现在所有字段的最前面

  //表示两个字段联合起来去重
  select distinct job,deptno from emp;
  //统计工作岗位数量
  select count(distinct job) from emp;