聚合函数：求平均，求和，总个数，最大值，最小值

avg(),sum(),count(),max(),min()

# 返回值为列表当中的第一个非 NULL值，在没有非NULL 值得情况下返回值为 NULL
# SELECT COALESCE(NULL,NULL,NULL);
# 若expr 为IN列表中的任意一个值，则其返回值为 1 , 否则返回值为0。
# select 3 in (23,4,5,2,3,5);
# INTERVAL(N,N1,N2,N3,...) 假如N < N1，则返回值为0；假如N < N2 ，则返回值为1，N<N3返回值2；假如N 为NULL，则返回值为 -1 。
# 在有两个或多个参数的情况下， 返回值为最小 (最小值) 参数。用一下规则将自变量进行对比：
# SELECT LEAST(34.0,3.0,5.0,767.0);
# select case when sal<1000 then '农民' when sal>2000 then '土豪' else '地主家的傻儿砸' end '角色',sal from emp;
# 如果expr1 = expr2 成立，那么返回值为NULL，否则返回值为 expr1。这和CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END相同。
# select nullif(expr1,expr2);
#返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。或许有一个或多个参数。 SELECT CONCAT('My', 'S', 'QL');输出MYSQL

# group by 分组，order by 排序，默认降序desc 升序asc
# DAYOFWEEK(date):返回日期date的星期索引(1=星期天，2=星期一, …7=星期六)。
# WEEKDAY(date):返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。
# DAYOFMONTH(date):返回date的月份中的日期，在1到31范围内。
# DAYOFYEAR(date):返回date在一年中的日数, 在1到366范围内。
# MONTH(date):返回date的月份，范围1到12。
# DAYNAME(date):返回date的星期名字。
# MONTHNAME(date) :返回date的月份名字。
# QUARTER(date):返回date一年中的季度，范围1到4。
/*WEEK(date,first):对于星期天是一周的第一天的地方，有一个单个参数，返回date的周数，
范围在0到52。2个参数形式WEEK()允许你指定星期是否开始于星期天或星期一。如果第二个参数是0，
星期从星期天开始，如果第二个参数是1，从星期一开始。
select WEEK('2009-02-20');
select WEEK('2009-02-20',0);
select WEEK('2009-02-20',1); */
# YEAR(date):返回date的年份，范围在1000到9999。
# HOUR(time):返回time的小时，范围是0到23。select HOUR('10:05:03');
# MINUTE(time):返回time的分钟，范围是0到59。
# SECOND(time):回来time的秒数，范围是0到59。
# FORMAT(2.356,2)返回的结果将会是2.36；FORMAT(2.353,2)返回的结果将会是2.35。

# 查询工资在2000-5000之间的员工姓名，工资 between ... and ...的用法
select emp.`ename`,emp.sal from emp where sal between 2000 and 5000;
# 查询员工姓名中第三个字符为‘A’的员工的姓名，职位，工资，部门号
select ename,job,sal,deptno from emp where ename like '__A%';

# SOME和ANY、all、in的使用
/*select * from emp where sal > any (查询的结果集)
当查找关键字为SOME或ANY时，根据查询结果可以得出只要满足设定条件的最小值均可返回结果。
在这个案例中只要所有salary大于职位中最低的，该字段就会被返回。
另外可以得出的是使用ANY和SOME关键字的时候功能是一致的，在实际运用中使用ANY偏多一些，有的资料中说SOME是ANY的别名。
select * from emp where sal > all (查询的结果集) 返回的结果为大于查询结果集中的最大值的结果。小于则相反
select * from emp where sal in (查询的结果集) 返回的结果为查询结果集中包含的值。
*/

# 1.查询emp表，显示薪水大于2000，且工作类别是MANAGER的员工的姓名，职位，工资，部门号
select e.`ename`,e.`job`,e.`sal`,e.`deptno`
from emp e
where e.`job`='MANAGER' and e.`sal`>2000;
# 2.查询emp表，显示年薪大于30000，工作类别不是MANAGER的员工的姓名，职位，工资，部门号
select e.`ename`,e.`job`,e.`sal`,e.`deptno`
from emp e
where e.`job`!='MANAGER' and e.`sal`>3000;
# 3.查询emp表， 显示薪水在1500到3000之间，且工作类别以“M”开头的员工的姓名，职位，工资，部门号
select e.`ename`,e.`job`,e.`sal`,e.`deptno`
from emp e
where e.`job` like 'M%' and e.`sal` between 1500 and 3000;
# 4.查询emp表，显示奖金金为空并且部门号为20或30的员工的姓名，职位，工资，奖金，部门号
select e.`ename`,e.`job`,e.`sal`,e.`coomm`,e.`deptno`
from emp e
where e.`coomm` is null and e.`deptno` in (20,30);
# 5.查询emp表，显示奖金不为空或者部门号为20的员工的姓名，职位，工资，奖金，部门号，要求按照工资降序排列
select e.`ename`,e.`job`,e.`sal`,e.`coomm`,e.`deptno`
from emp e
where e.`coomm` is not null or e.`deptno`=20
order by e.`sal` desc;
# 6.查询emp表，显示年薪大于30000工作类别不是MANAGER，且部门号不是10或40的员工的姓名，职位，工资，部门号，要求按照雇员姓名进行排列
select e.`ename`,e.`job`,e.`sal`,e.`coomm`,e.`deptno`
from emp e
where (e.`sal`*12)>30000 and e.`job`!='MANAGER' and e.`deptno` not in (10,40)
order by e.`ename`;
# 单行函数
# 字符函数
# upper('x')全部全换为大写
select UPPER('abcd');
# lower（） 全部转换为小写
select LOWER(ename) from emp;
# length（）返回字符的个数
select LENGTH(ename) from emp;
# ifnull（x,value）如果x为空，则返回value，否则返回x
select IFNULL(coomm,0) from emp;
# replace（x,char1,char2)将x中的char1换成char2
select REPLACE('china','a','ese') from dual;
# substring(x,start,[length])从x的start位置开始返回长度为length的子字符串
select SUBSTRING('people',2,4);
# 当start为负数时，如-2，从x的右边往左数两位开始返回。
select SUBSTRING('people',-2,2);
# lpad(x,length,char)从x的左边使用char将x补齐到长度为length,若length小于x的长度，则从左往右去length的长度字符串返回
select LPAD('black',10,'s');
# rpad(x,length,char)从x的右边使用char将x补齐到长度为length
select RPAD('black',10,'s');
# left(x,length)从x的左边返回length个字符
select LEFT('black',3);
# right(x,length)从x的右边返回length个字符
select RIGHT('black',3);
# concat(str1,str2)将str1和str2连接
select CONCAT('bla','ck');
# instr(str,char)返回char在str中第一次出现的位置,从一开始计数
select INSTR('black','a');

#.在hello的左右两边各添加5个’*’,返回‘*****hello*****’（三种方法实现）
select RPAD(LPAD('hello',10,'*'),15,'*');
select LPAD(CONCAT('hello','*****'),15,'*');
select RPAD('*',15,CONCAT('*****','hello'));
# select concat('*****',CONCAT('hello','*****'));
select CONCAT('*****','hello','*****');
#.从字符串‘abcdefghijklmn’中返回后三位字符，并转换为大写LMN
select UPPER(RIGHT('abcdefghijklmn',3));
#.查询名字为5个字符的员工的姓名，工资，奖金(奖金为空则显示为0)、职位、部门号
select e.`ename` '姓名',e.`sal` '工资',IFNULL(e.`coomm`,0) '奖金',e.`job` '职位',e.`deptno` '职位'
from emp e
where LENGTH(e.`ename`)=5;
#.查询姓名中不含‘A’的员工的编号、姓名、职位、部门号，where方式1：where !(instr(e.`ename`,'A')>0);，后面是2
select e.`empno` '编号',e.`ename` '姓名',e.`job` '职位',e.`deptno` '部门号'
from emp e
where INSTR(e.`ename`,'A')=0;
#.显示将员工姓名的第一个字符去掉后的字符串，如‘ALLEN’显示为‘LLEN’
select e.`ename`, SUBSTRING(e.`ename`,2) from emp e
#.使用员工姓名的第一个字符，从左边将员工姓名补齐到长度为10
select LPAD(e.`ename`,10,LEFT(e.`ename`,1)) from emp e

# 数字函数
#.floor(x)返回小于等于x的最大整数
select FLOOR(4.8);
#.ceiling(x)返回大于等于x的最小整数
select CEILING(4.8);
#.mod(x,y)返回x除以y的余数
select MOD(8,3);
#.round(x,y)返回对x精确到y位的结果，四舍五入
select ROUND(4.7);
select ROUND(4.76543,2);
select ROUND(4321.76543,-2); #4300
#.truncate(x,d)返回对x截取到y位的结果返回被舍去至小数点后D位的数字X。若D 的值为 0,
# 则结果不带有小数点或不带有小数部分。可以将D设为负数,若要截去(归零) X小数点左起第D位开始后面所有低位的值
select TRUNCATE(1.263,1);
select TRUNCATE(123.999,-1); #120
select TRUNCATE(1.999,0);
select TRUNCATE(-1.999,1);
select TRUNCATE(122,-2);
select TRUNCATE(10.28*100,0);

# 日期函数
# curdate()返回当前日期
select CURDATE();
# curtime()返回当前时间
select CURTIME();
# now()返回当前日期和时间
select NOW();
# last_day(x)返回日期x所在月份的最后一天的日期
select LAST_DAY(NOW());
# date_add(x,intervaln f)返回日期x加上间隔时间n的结果,f可以为day/month/year
select DATE_ADD(NOW(),interval 3 year);
# date_format(x,fmt)返回日期x按指定格式fmt格式化后的结果
select DATE_FORMAT(NOW(),'%Y%m%d');
# 查询1981
select e.ename, e.job,e.`hiredate` , DATE_FORMAT(e.`hiredate`,'%Y')
from emp e
where DATE_FORMAT(e.`hiredate`,'%Y')=1981 and DATE_FORMAT(e.`hiredate`,'%m')>6

# 查询6月份入职的员工的姓名，工资，入职日期、职位、部门号
select e.`ename`, e.`sal`, e.`hiredate`, e.`job`, e.`deptno`
from emp e
where DATE_FORMAT(e.`hiredate`,'%m')=6;
# 查询1981年下半年入职的员工的编号、姓名、职位、入职日期、部门号
select e.ename, e.job,e.`hiredate` , e.`deptno`
from emp e
where DATE_FORMAT(e.`hiredate`,'%Y')=1981;
# 查询在某个月倒数第三天入职的员工的姓名、职位、入职日期
select e.`ename`,e.`job`,e.`hiredate`
from emp e
where DATE_FORMAT(e.`hiredate`,'%d')>(DATE_FORMAT(LAST_DAY(e.`hiredate`),'%d')-3);

select case e.job
when 'ANALYST' then '分析人员'
when 'CLERK' then '书记'
when 'SALESMAN' then '销售'
when 'PRESTDENT' then '董事'
when 'MANAGER' then '经理'
end
from emp e;

# 查询20号部门的员工姓名、职位、工资、部门名称
select e.`ename`,e.`job`,e.`sal`,d.`dname`
from emp e inner join dept d on e.`deptno`=d.`deptno`
where d.`deptno`=20;
# 查询奖金为空的员工的姓名、工资、奖金(显示为0)、工资等级、部门号、部门名称
select e.empno,e.ename,e.sal,s.grade,d.loc
from emp e,dept d,salgrade s
where e.deptno = d.deptno
and e.sal between s.losal and s.hisal
order by sal asc;

# 查询员工姓名、部门号、部门名称，要求将没有员工的部门也显示出来

 有点乱，未完待续。。。