oracle学习总结（二）

SQL语句关键字执行顺序： from--->where--->group by-->having-->select-->order by

简单的单表查询

----分组函数(使用group by 关键字)
--查询最高工资和员工数

select max(sal),count(*) from emp;

--查询不同部门的最高工资

select deptno,max(sal) from emp group by deptno order by deptno;

--查询不同工作岗位的员工数

select  job,count(*) from emp group by job; 

--查询不同部门的不同工作岗位的人数

select deptno,job,count(*) from emp group by deptno,job order by deptno;

--查询不同部门的不同工作岗位的并且人数大于1的信息

select deptno,job,count(*) from emp where count(*)>1 group by deptno,job; --错误(where不能用于多行函数筛选)

--查询部门号大于10的不同部门的不同工作岗位的人数

select deptno,job,count(*) from emp where deptno>10 group by deptno,job order by deptno;--可用于字段

-------使用having关键字进行分组后的筛选（必须结合分组来使用）

--查询不同部门的不同工作岗位的并且人数大于1的信息

select deptno,job,count(*) from emp  group by deptno,job having count(*)>1;

--查询部门号大于10的不同部门的不同工作岗位的人数
select deptno,job,count(*) from emp group by deptno,job having count(*)>1 and deptno>10 order by deptno;

单表(增加、删除、修改)

   1、增加
       --给dept部门表增加一条数据
       --全字段插入；字段名可省略
       select * from dept;
       insert into dept(deptno,dname,loc) values('88','boss','北京');
       insert into dept values('90','HUH','上海');
       --部分字段插入：字段名不可省略，主键和不能为空的字段必须赋值
       insert into dept(deptno,dname) values ('65','JUY');
  2、备份表数据
       create table dept_bak as (select * from dept);--全部备份
       create table dept_name as (select deptno,dname from dept);--部分字段备份
       select * from dept_bak;
       select * from dept_name;
       ---给备份表添加数据
       insert into dept_bak select * from dept where deptno=90; --全字段备份
       insert into dept_name  select loc from dept where deptno=90;--部分字段备份
 3、更新数据
       update dept set dname='FYYU',loc='西安' where deptno=88;
 4、删除数据
       delete from dept where deptno=88;--支持回滚rollback
       truncate table dept_bak; --不支持回滚rollback，但效率更高
       
多表联合查询
   ----sql92标准（易于书写，不易阅读）
   
   --笛卡尔积

   select * from emp,dept;

   --等值连接
       --查询员工的姓名，工作，薪水，部门编号,部门名称
       select e.ename,e.job,e.sal,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno;
   --非等值连接 
        --查询员工姓名，工作，薪资，薪资等级
        select * from salgrade;
        select e.ename,e.job,e.sal,s.grade from emp e,salgrade s where e.sal>s.losal and e.sal<=s.hisal;
  --自连接
       --查询员工姓名，工作，薪资，上级领导姓名
       select e.ename,e.job,e.sal,m.ename 上级领导 from emp e,emp m where e.mgr=m.empno order by e.sal;
     
 ---三表查询练习
          --查询员工姓名，工作，薪水，部门名称，城市名称
          select e.ename,e.job,e.sal,d.dname,c.cname from emp e,dept d,city c where e.deptno=d.deptno and d.loc=c.cloc ;
---外连接（左外连接，右外连接）
      --查询员工姓名，工作，薪资，部门，名称以及没有员工的部门信息
      select e.ename,e.job,d.deptno,d.dname from emp e,dept d where e.deptno(+)=d.deptno;--右外连接
      select e.ename,e.job,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno(+);--左外连接
      --查询员工薪水大于2000的员工姓名，工作，薪资，部门名称
      select e.ename,e.job,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno and e.sal>2000;

--sql99学习
  --交叉连接:
  select * from emp cross join dept cross join salgrade; --使用cross join 关键字
  --自然连接
  select * from emp natural join dept;
  --内连接  
  select * from emp inner join dept using (deptno); --using 关键字,必须是同名同值字段，必须用inner join连接
  select * from  dept d inner join city c on d.loc=c.cloc;--on关键字，必须是不同名但同值
  ----外连接
        --左外连接：left join
        select * from emp e left join dept d on e.deptno=d.deptno; 
        --右外连接:right join
        select * from emp e right join dept d on e.deptno=d.deptno; 
        --全外连接:full join
        select * from emp e full join dept d on e.deptno=d.deptno; 
        
  --三表联合查询
    --查询员工工资大于2000的员工姓名，工作，薪资，部门名称，城市名称
   --sql99写法(将每个点分行写，容易阅读)
    select * from emp e
    inner join dept d
    using(deptno)
    inner join city c
    on d.loc=c.cloc
    where e.sal>2000
    order by deptno;