SQL第二天

函数
单行函数
数值函数round trunc
字符函数upper lower
日期函数add_months...
一般函数nvl，decode
转换函数to_char to_date to_number
多行函数（组函数）
avg 平均值 number
sum 求和   number
count 计数 number char date
max 最大值 number char date
min 最小值 number char date




date类型
c1 date
世纪 年 月 日 时 分 秒
session 会话 sql命令在session中运行
alter session set nls_date_format =
           'yyyy mm dd hh  2  24:mi:ss';


日期格式敏感


日期运算 
date+number date-number
date-date


数值函数 round 四舍五入 trunc 截取
日期函数
add_months(sysdate,6)-->date
select add_months(sysdate,6) from dual;
month_between(sysdate,hiredate)-->number
last_day(sysdate)-->date 月末日期


数据类型转换
隐式数据类型转换:系统自动调用转换函数
显式数据类型转换:用户自己调用转换函数


写where子句做严格数据类型匹配，避免对表中的某列做隐式转换


字符转日期 to_date,表达日期值，函数返回类型日期
日期转字符 to_char（date,fmt),处理日期值（获得年信息），函数处理的参数的数据类型是日期
数值转字符 to_char（number,fmt)
字符转数值 to_number


group by子句 分组
select from
where
group by


from-->where-->group by-->select


group by 列名，列名称为组标识
若有group by子句，select子句之后只能跟组标识和组函数，其它列不允许。
若没有group by子句，select子句后有一个组函数其它的必须也是组函数


having子句 过滤组
from-->where-->group by-->having--> select


where和having的比较
共同点：实现的过滤，执行在select之前
区别
where子句过滤的是记录，可以跟单行函数，可以跟任意列名，执行在having之前，不可以跟组函数。
having子句过滤的是组，可以跟多行函数，可以跟组标识，执行在where之后，不可以跟单行函数以及任意列名（除组标识外）


order by 对select计算出的结果集排序
执行和书写顺序在最后一个
排序 升序 asc（缺省）降序 desc
order by 列名,表达式,函数,列别名,列位置
from-->where-->group by-->having-->select-->order by()


多表查询
ename,location
from emp_hiloo dept_hiloo


join操作
cross join(交叉连接)
笛卡尔积
select t1.c1,t2.c2
from t1 cross join t2


inner join（内连接） 匹配
from t1 join t2
on t1.c2 = t2.c1
内连接的结果集的生成
nested loop 、hash join、 sort merge
t1 驱动表 t2 匹配表
北京地区有哪些员工
select e.ename,d.location
from emp_hiloo e join dept_hiloo d
on e.deptno = d.deptno
and d.location = 'beijing'


from(确定哪些表)-->and-->join on-->select


zhangwuji在哪个地区上班？




















null值的讨论
1组函数处理的是所有的非空值，count(*)统计的是所有记录的数量;当所有的值都为null时,count返回0,其它函数返回null,count(*)返回记录数.
2group by后面的列有null值，将所有的null值分为一组
3order by 列名，若有null值，asc时null在最后，desc时null在最前。




课堂练习
1 列出3月份入职的员工?
select ename,hiredate
where to_char(hiredate,'mm') = '03'


where to_char(hiredate,'fmmm') = '3'
fm去掉前导0或去掉两边的空格


where to_char（hiredate,'mm') = 3
'03' = 3  字符类型-->数值类型
to_number('03') = 3


where salary = '3000'
where salary = to_number('3000')


2十分钟之后
select sysdate+1/144 from dual;


3 10部门员工的工资10%，
  20部门员工的工资20%，其他部门不动
select ename,salary,
       case when deptno = 10 then salary*1.1
            when deptno = 20 then
salary*1.2
       else
            salary
       end new_sal
from emp_hiloo
若没有else，当条件不匹配时，表达式返回null值。


select ename,salary,
       decode(deptno,10,salary*1.1,
                     20,salary*1.2,
                        salary) new_sal
from emp_hiloo


select ename,salary,
       decode(deptno,10,salary*1.1,
                     20,salary*1.2) new_sal
from emp_hiloo
如果deptno不匹配10和20，函数的返回值为null
4 有奖金员工的奖金的平均值，和，最大值，最小值，个数
5 所有人的奖金的平均值
  select sum(bonus)/count(*) 
  from emp_hiloo
  select avg(nvl(bonus,0)) 
  from emp_hiloo
6 不同奖金的个数？
7 各个部门的平均工资？
8 每种奖金有多少人？
  select bonus,count(empno)
  from emp_hiloo
  group by bonus
9各个部门不同职位的人数？
 select deptno,title,count(empno)
 from emp_hiloo
 group by deptno,title
10列出10部门的平均工资，只显示平均工资
  列出10部门的平均工资，显示部门号，平均工资
  select round(avg(salary))
  from emp_hiloo
  where deptno = 10;


  select max(deptno),round(avg(salary))
  from emp_hiloo
  where deptno = 10


  select deptno,round(avg(salary))
  from emp_hiloo
  where deptno = 10
  group by deptno
11哪些部门的平均工资高于5000
  select deptno,round(avg(salary))
  from emp_hiloo
  group by deptno
  having round(avg(salary)) > 5000
12列出员工的名字以及奖金,分别升序和降序排列 
  select ename,bonus
  from emp_hiloo
  order by bonus desc;
13 列出员工名字,部门号,工资,显示顺序部门号从小到大,同一部门按工资从大到小
  select ename,deptno,salary
  from emp_hiloo
  order by deptno,salary desc
14每种职位里工资大于1000的员工数，结果集要求员工数多于1个，按员工数降序排列
select job,count(empno)
from emp_hiloo
where salary > 1000
group by job
having count(empno) > 1
order by 2 desc
15zhangwuji在哪个地区上班？
select e.ename,d.location
from emp_hiloo e join dept_hiloo d
on e.deptno = d.deptno
and e.ename = 'zhangwuji'
16 各个部门的平均工资，列出部门名称，平均工资
select d.dname,round(avg(e.salary))
from emp_hiloo e join dept_hiloo d
on e.deptno = d.deptno
group by d.dname 


select max(d.dname),round(avg(e.salary))
from emp_hiloo e join dept_hiloo d
on e.deptno = d.deptno
group by d.deptno


e.empno e.ename e.salary ,,,e.deptno 
1001 zhangwuji 10000 10
1002 liucangsong 8000
1003 liyi 9000
1004 guofurong   5000


d.deptno d.dname d.location
10 developer beijing
10 developer beijing
10 developer beijing
10 developer beijing
课后练习
1工资小于3000的涨10%，在[3000,5000]涨5%
其他人不变。
2beijing地区有哪些职位？
3各个地区不同职位的人数，要求人数多于1人