Oracle-04
数据查询语言---DQL
1、查询语句中函数的使用
单行函数：每一行数据会返回一个结果
1）数字函数
* round（数字，保留到小数点后指定位数）：用于对数字进行四舍五入
select round(123.456,2) from dual; //保留小数点后两位
* trunc（数字，截取到小数点后指定的位数）：用于截取数据，若第二个参数没有，默认为0
select trunc(123.456,1) from dual; //123.4，截取到小数点后面一位
2）日期函数
* sysdate：返回系统当前的时间
select sysdate from dual;
* months_between(d1,d2)：计算这两个日期之间相隔的月份
eg:计算员工入职多少个月
select ename,trunc(months_between(sysdate,hiredate)) months from emp_xu;
* add_months(date,number)：在date时间增加number个月
eg:计算四个月之后的时间
select add_months(sysdate,4) from dual;//当前时间加上4个月
select add_months(sysdate,-4) from dual;//“-4”代表当前时间4个月之前的月份
* last_day(date)：当前月份的最后一天
eg:计算上个月(5月份)最后一天
select last_day(add_months(sysdate,-1)) from dual;
3）转换函数
a. 常用日期格式
* yyyy：四位的年----2018
* year：全拼的年----twenty eightteen或2018年
* mm：两位的月份---06
* month：全拼的月---June或6月
* mon：简拼的月
* dd：表示两位的天---05
* day：表示全拼的星期---Tuesday或星期二
* dy：简拼的星期---Tue或星期二
* am/pm：上午/下午
b. to_char(日期数据,指定的格式)：将日期数据按照指定格式转换成字符数据
select to_char(sysdate,'month') from dual;
select to_char(sysdate,'yyyy-mon-dd day hh24:mi:ss') from dual;//显示年月日，星期，时分秒
c. to_date(字符数据,指定格式)：将字符数据按照指定格式转换成日期数据
select to_date('2018-06-05','DD-MON-YY') from dual;//报格式不匹配错误
select to_date('2018-06-05','yyyy-mm-dd') from dual;
4）其他函数
* coalesce(参数列表)：目的是返回参数列表中第一个非空参数值，参数列表中最后一个值通常是常量，coalesce(d1,d2,d3,...,100)
eg:计算员工的年终奖(要求：bonus不为null,年终奖就是bonus;bonus为null,年终奖是salary*0.05;bonus和salary都是null,年终奖就是100)
select ename,bonus,salary,coalesce(bonus,salary*0.05,100) money from emp_xu;
* case...when：表示数据的分支
eg:根据员工的职位来计算加薪之后的薪水(要求:若职位是‘Analyst’加薪20%;若职位是'Programmer'加薪5%;若职位是'Clerk'加薪2%;其他职位薪水不变)
select ename,salary,position,case position when 'Analyst' then salary*1.2 when 'Programmer' then salary*1.05
when 'Clerk' then salary*1.02 else salary end new_salary from emp_xu;//在case...when中间是没有逗号的，else相当于java中的default，end表示语句的结束
* decode(判断条件,匹配1,值1,匹配2,值2...默认值)：作用和case..when是一样的
select ename,salary,position,decode(position,'Analyst',salary*1.2,'Programmer',salary*1.05,'Clerk',salary*1.02,salary) new_salary from emp_xu;
2、组函数：多行数据返回一个结果（如求平均成绩）
1）count()：求记录数，能够处理任何数据类型，忽略空值
eg:根据员工号统计有多少条记录
select count(empno) from emp_xu; //13
select count(position) from emp_xu; //12，忽略了null空值
select count(*) from emp_xu; //13，'*'不忽略空值的
2）sum()/avg()：求和/求平均
注：只能处理数值类型的数据，忽略空值
eg：计算员工的薪水总和
select sum(salary) from emp_xu;
eg:计算员工的人数、薪水总和、平均薪水
select count(*),sum(salary),avg(salary) from emp_xu;//有一个salary 为null，最终计算平均会忽略掉
select count(*),sum(salary),avg(nvl(salary,0)) from emp_xu;//需要加空值处理函数，nvl()
3）min()/max()：最小值/最大值（能够处理任何数据类型）
eg:计算员工的最高薪水、最低薪水
select min(salary),max(salary) from emp_xu;//salary为空会被忽略掉
总结：
a. count/sum/avg/max/min：如果函数写列名默认是忽略空值的
b. count(*)不忽略空值：sum/avg针对数值类型操作
c. max/avg：针对全部的数据类型操作
