SQL第三天

1工资小于3000的涨10%，在[3000,5000]涨5%
其他人不变。
select ename,salary,
   case when salary < 3000
        then salary*1.1
      when salary between 3000 and 5000
      then salary*1.05
   else
           salary
   end new_sal
from emp_hiloo


2beijing地区有哪些职位？
select distinct e.job
from emp_hiloo e join dept_hiloo d
on e.deptno = d.deptno
and d.location = 'beijing'


3各个地区不同职位的人数，要求人数多于1人
select d.location,e.job,count(e.empno)
from emp_hiloo e join dept_hiloo d 
on e.deptno = d.deptno
group by d.location,e.job
having count(e.empno) > 1


连接
cross join 
inner join
等值连接
自连接
非等值连接
outer join
等值连接
自连接
非等值连接


内连接 匹配
等值连接 e.deptno = d.deptno
两张表有描述共同属性的列，两张表记录之间的关系
自连接（self join）
同一张表的记录之间有关系，通过给表起别名的方式，将其转成两张表的记录之间的关系。


ename ename
zhangwuji zhangsanfeng
liucangsong zhangwuji


emp_hiloo e 10条记录
emp_hiloo m 10条记录
inner join
e 9条记录（匹配）10-9条记录（不匹配） 
m 4条记录（匹配）10-4条记录（不匹配）


outer join（外连接）
t1是驱动表，表里的所有记录都在结果集中，一个都不能少
select
from t1 left join t2
on t1.c2 = t2.c1
外连接的结果集=内连接的结果集+t1表中不匹配的记录和t2表的null记录的组合


select
from t1 right join t2
on t1.c2 = t2.c1
t2是驱动表，表里的所有记录都在结果集中，一个都不能少
外连接的结果集=内连接的结果集+t2表中不匹配的记录和t1表的null记录的组合


select
from t1 full join t2
on t1.c2 = t2.c1
两张表中不匹配的记录都放入结果集
外连接的结果集=内连接的结果集+t1表中不匹配的记录和t2表的null记录的组合+t2表中不匹配的记录和t1表的null记录的组合


外连接的应用
1某张表的所有记录出现在结果集中。
2否定问题 把驱动表的所有不匹配的记录找出来。不匹配的记录指跟匹配表中的所有记录都不匹配


外连接+where 匹配表.pk列 is null


哪个部门没有员工
select e.ename,d.dname
from emp_hiloo e right join dept_hiloo d
on e.deptno = d.deptno
where e.empno is null


e 10条记录
d 4条记录
11=10+1


子查询
SQL后面跟的select语句是子查询
create table newtab
as
select * from oldtab;


非关联子查询
子查询中没有跟主查询的表建关联，即没将两个表的列写成表达式
先执行子查询（只执行一遍），当返回多条记录时系统会自动去重，接着执行主查询。


select ename,salary from emp_hiloo
where salary = 
             (select min(salary) 
              from emp_hiloo);


注意条件运算符的选择，若子查询返回多条记录，一定要用多值运算符。=，>是单值运算符，in，>all是多值运算符。


多列子查询
哪些员工的工资等于本部门的平均工资
select ename,salary
from emp_hiloo
where (deptno,salary) in
     (select deptno,round(avg(salary))
      from emp_hiloo
      group by deptno)




关联子查询
子查询中，子查询的表和主查询的表建关联。
关联子查询执行的遍数由主查询表的记录数决定。
select ename,salary
from emp_hiloo o
where salary > 
       (select round(avg(salary))
        from emp_hiloo i
        where i.deptno = o.deptno)


from后面可以跟子查询
from () 
emp_hiloo  
部门平均工资表（deptno，avgsal）
inner join
select e.ename,e.salary,a.asal
from emp_hiloo e join
 (select deptno,round(avg(salary)) asal
  from emp_hiloo 
  group by depnto) a
on e.deptno = a.deptno
and e.salary > a.asal


exists运算符 存在 布尔值
从主查询中读出第一条记录，按照关联条件在子查询的表里找匹配的记录，当找到第一条匹配的记录时，马上返回，该记录（主查询表）放入结果集中；当所有记录（子查询表）扫描一遍没有找到匹配记录，该记录（主查询表）被过滤掉。


哪些人是领导？
select ename from emp_hiloo o
where exists
       (select 1 from emp_hiloo i
        where o.empno = i.mgr)


哪些部门有员工
select dname
from dept_hiloo o
where exists
     (select 1 from emp_hiloo i
      where o.deptno = i.deptno)


not exists 不存在
从主查询中读出第一条记录，按照关联条件在子查询的表里找匹配的记录，当找到第一条匹配的记录时，马上返回，该记录（主查询表）被过滤掉；当所有记录（子查询表）扫描一遍没有找到匹配记录，该记录（主查询表）放入结果集中。


哪些部门没有员工(否定问题)
select dname
from dept_hiloo o
where not exists
     (select 1 from emp_hiloo i
      where o.deptno = i.deptno)






课堂练习
1 列出员工名字和领导名字的对应关系
  不包含zhangsanfeng
  select  e.ename employee,
          m.ename manager
  from emp_hiloo e join emp_hiloo m
  on e.mgr = m.empno


  包含zhangsanfeng（用内连接实现）
  select  e.ename employee,
        decode(m.ename,e.ename,'boss'                         ,m.ename) manager
  from emp_hiloo e join emp_hiloo m
  on nvl(e.mgr,e.empno) = m.empno


  包含zhangsanfeng（用外连接实现）
select e.ename employee
       nvl(m.ename,'Boss') manager
from emp_hiloo e left join emp_hiloo m
on e.mgr = m.empno


zhangwuji zhangsanfeng
liucangsong zhangwuji
zhangsanfeng Boss


10 rows selected
2 列出所有的领导？
  他的empno会是某人的mgr
  select distinct m.ename
  from emp_hiloo e join emp_hiloo m
  on e.mgr = m.empno


3 哪些人是员工？哪些人不是领导？
select m.ename
from emp_hiloo e right join emp_hiloo m
on e.mgr = m.empno
where e.empno is null


right
15=9+(10-4) ()里表示匹配上的
(1001)3+1002+1003+1004+(1005)3+(1006)1+1007+(1008)2+1009+1011


left
10=9+(10-9)()里表示匹配不上的
1001+1002+1003+1004++1006+1007+1008+1009+1011+(1005)


4 哪些部门没有zhangwuji这个员工
select e.ename,d.dname
from emp_hiloo e right join dept_hiloo d
on e.deptno = d.deptno
and e.ename = 'zhangwuji'
where e.empno is null


在过滤匹配表的列时，若要在外连接之前过滤用and子句，在外连接之后过滤用where子句。


若过滤驱动表的列用where子句。


4=1+(4-1)


4 哪些员工的工资级别是3,5级？
select s.grade,e.ename,e.salary
from emp_hiloo e join salgrade_hiloo s
on e.salary between s.lowsal and s.hisal
and s.grade in (3,5)


5列出各个级别的人数，按级别排序（不包含0级）
select s.grade,count(e.empno)
from emp_hiloo e join salgrade_hiloo s
on e.salary between s.lowsal and s.hisal
group by s.grade
order by s.grade


6列出各个级别的人数，按级别排序（包含0级）
select s.grade,count(*)
from emp_hiloo e right join salgrade_hiloo s
on e.salary between s.lowsal and s.hisal
group by s.grade
order by s.grade


7谁的工资是最低的？
8哪些部门的平均工资比30部门的平均工资高？
select deptno,round(avg(salary))
from emp_hiloo
group by deptno
having round(avg(salary)) > 
select round(avg(salary))
from emp_hiloo
where deptno = 30


9哪些员工的工资比zhangwuji的工资高？所有>all max()
 select ename,salary
 from emp_hiloo
 where salary >all 
        (select salary
         from emp_hiloo
         where ename = 'zhangwuji')


比任意人高 >any min()
where salary >any
(select salary
         from emp_hiloo
         where ename = 'zhangwuji')


10哪些人是领导？
select ename
from emp_hiloo
where empno in
         (select mgr from emp_hiloo)
select distinct mgr from emp_hiloo
5rows selected 1001 1005 1006 1008 null


4 rows selected 1001 1005 1006 1008


11哪些人是员工？哪些人不是领导？
select ename
from emp_hiloo
where empno not in
         (select mgr from emp_hiloo)
no rows selected.


select ename
from emp_hiloo
where empno not in
         (select mgr from emp_hiloo
          where mgr is not null)
6 rows selected


为什么用内连接的否定形式不能解决否定问题
select m.ename
from emp_hiloo e join emp_hiloo m
on e.mgr <> m.empno
上述写法只要跟一个不匹配就认为满足条件


解决否定问题的核心是跟所有的都不匹配


m e
1001 zhangwuji   1001 zhangwuji 1005
1002 liucangsong 1001


11 哪些员工的工资等于本部门的平均工资
select ename,salary
from emp_hiloo
where (deptno,salary) in
     (select deptno,round(avg(salary))
      from emp_hiloo
      group by deptno)


12 哪些员工的工资比本部门的平均工资高？






课后练习
1zhangwuji的领导是谁？（内连接，非关联子查询）
2zhangwuji领导谁？（内连接，非关联子查询）
3哪个级别没有员工？
4哪些人是领导？ 3（join、in、exists）
5哪些人是员工？ （outer join 、not in 、not exists