MySQL学习笔记4-常见函数（单行函数和分组函数）

#四、常见函数1-单行函数
/*
语法：select 函数名（实参列表） [from 表];
分类：1.单行函数，如contact，length，ifnull等；
      2.分组函数：统计
        <1>字符函数
*/

#一.字符函数
#1.length()函数
select length('abc');
select length('值得abc');

show variables like '%char%';

#2.concat 函数
select concat(last_name, '_', first_name) from employees;

#3.upper,lower
select upper('kkk');
select lower('AAA');

#4.substr,索引从1开始,字符长度
select substr('阳光彩虹小白马',5) out_put;
select substr('阳光彩虹小白马',1,2) out_put;

#5.instr,返回子串第一次出现的索引，如果找不到返回0
select instr('阳光彩虹小白马','小白马') output;

#6.trim去前后空格
select trim('     zzz    ') as output;
select trim('a' from 'aaaaa     zzz   aaaaaa ') as output;

#7.lpad用指定的字符
select lpad('王小小', 8,  '*') as output;

#8.rpad用指定的字符
select rpad('王小小', 8,  '*') as output;

#9.repalce
select replace('aaabcdzzzmmhfg', 'z', 'y') as output;

#二、数学函数
#1.round 四舍五入
select round(-1.6);
select round(2.376, 2);

#2.ceil向上取整
select ceil(-1.52);

#3.floor 向下取整alter
select floor(-9.6);

#4.truncate截断
select truncate(1.65, 1);

#5.mod mod(a,b) a-a/b*b
select 10%3;
select mod(10,3);

#三、日期函数
#1. now函数
select now();
select curdate();
select curtime();
select year(now());
select month(now());
select monthname(now());

#2.str_to_date
select str_to_date('1992-3-8', '%Y-%c-%d') as output;

select * from employees where hiredate = '4-3 1992';
select * from employees where hiredate = str_to_date('4-3 1992', '%c-%d %Y');

#date_format
select date_format(now(), '%y年%m月%d日') as output;
select last_name, date_format(hiredate, '%m月%d日%y年') 入职日期 from employees where commission_pct is not null;

#四、其他函数
select version();
select database();
select user();

#五、流程控制函数
#1.if函数
select if(10>5, 'y', 'n') output;
select last_name,commission_pct, if(commission_pct is null, 'y', 'n') output from employees;
#2.case函数1
/*
语法：case 要判断的字段或表达式
	  when 常量1 then 要显示的值1或语句1
	  when 常量2 then 要显示的值2或语句2
      ...
      else
		要显示的值n或语句n
	  end
*/
/*案例：查询员工工资，要求
部门号=30，显示的工资为1.1倍；
部门号=40，显示的工资为1.2倍；
部门号=50，显示的工资为1.3倍；
其他部门，显示的工资为原工资alter*/
select salary 原始工资, department_id,
case department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end as 新工资
from employees;

#3.case函数2-多重if
/*
语法：case 
	  when 条件1 then 要显示的值1或语句1
	  when 条件2 then 要显示的值2或语句2
      ...
      else
		要显示的值n或语句n
	  end
*/
/*案例：查询员工工资alter
如果工资>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;

#五、常见函数2-分组函数
/* 功能：用于统计使用，又称为聚合函数或统计函数或组函数
   分类：sum,avg,max,min,count
   特点：<1>sum,avg只能是数值型；max,min,sum任何类型
         <2>以上分组函数都忽略null值
         <3>可以和distinct搭配实现去重运算
         <4>count(*)用于统计行数
         <5>和分组函数一同查询的字段要求是group by后的字段
*/
select sum(salary) from employees;
select avg(salary) from employees;
select min(salary) from employees;
select max(salary) from employees;
select min(salary) from employees;
select sum(salary),avg(salary),max(salary),min(salary),min(salary) from employees;
select sum(distinct salary), sum(salary) from employees;

#count详细介绍
select count(*) from employees;
select count(1) from employees;
select count('崔健') from employees;
/*效率
存储引擎MYISAM下，count(*)的效率高；
存储引擎INNODB下，count(*)和count(1)的效率差不多，比count(字段)要高一些；*/

select datediff(max(hiredate), min(hiredate)) as diffrence from employees;