oracle的笔记

第一课：客户端
        1. Sql Plus(客户端），命令行直接输入：sqlplus，然后按提示输入用户名，密码。
        2. 从开始程序运行:sqlplus，是图形版的sqlplus.
        3. http://localhost:5560/isqlplus
      
        Toad：管理， PlSql Developer:

       查看本数据库中的表：
        select table_name from user_tables;


第二课：更改用户
        1. sqlplus sys/bjsxt as sysdba
        2. alter user scott account unlock;(解锁)

第三课：table structure
      
        1. 描述某一张表：desc 表名
        2. select * from 表名
第四课：select 语句：

       1.计算数据可以用空表：比如：.select 2*3 from dual

       2.select ename,sal*12 annual_sal from emp;与select ename,sal*12 "annual sal" from emp;区别，加双引号保持原大小写。不加全变大写。
     
       3. select ename || 'abcd' 如果连接字符串中含有单引号，用两个单引号代替一个单引号。

第五课：distinct
        select deptno from emp;
        select distinct deptno from emp;

        select distinct deptno from emp;
        select distinct deptno ,job from emp
        去掉deptno,job两者组合的重复。更多的项，就是这么多项的组合的不重复组合。
第六课：Where
        select * from emp where deptno =10;
        select * from emp where deptno <>10;不等于10      
        select * from emp where ename ='bike';
        select ename,sal from emp where sal between 800 and 1500 (>=800 and <=1500)
        空值处理:
        select ename,sal,comm from emp where comm is not null;
        select ename,sal,comm from emp where ename not in ('smith','king','abc');
        select ename from emp where ename like '_A%';_代表一个字母,%代表0个或多个字母. 如果查询%
        可用转义字符.\%. 还可以用escape '$'比如:select ename from emp where ename like '%$%%' escape '$';

第七课: orderby
      
         select * from dept;
         select * from dept order by dept desc;(默认:asc)
         select ename,sal,deptno from emp order by deptno asc,ename desc;

第八课: sql function1:  
        select ename,sal*12 annual_sal from emp
        where ename not like '_A%' and sal>800
        order by sal desc;

        select lower(ename) from emp;

        select ename from emp
        where lower(ename) like '_a%';等同于
        select ename from emp where ename like '_a%' or ename like '_A%';

        select substr(ename,2,3) from emp;从第二字符截,一共截三个字符.
        select chr(65) from dual 结果为:A
        select ascii('a') from dual 结果为:65
        select round(23.652,1) from dual; 结果为: 23.7
        select round(23.652,-1) from dual; 20

        select to_char(sal,'$99_999_999') from emp;
        select to_char(sal,'L99_999_999') from emp;人民币符号,L:代表本地符号

        这个需要掌握牢:
        select birthdate from emp;
        显示为:
        BIRTHDATE
        ----------------
        17-12月-80
        ----------------

        改为:
        select to_char(birthdate,'YYYY-MM-DD HH:MI:SS') from emp;
      
        显示:
       
        BIRTHDATE
        -------------------
        1980-12-17 12:00:00
        -------------------
      
        select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual; //也可以改为:HH12
        TO_CHAR(SYSDATE,'YY
        -------------------
        2007-02-25 14:46:14
      

        to_date函数:
        select ename,birthdate from emp where birthdate > to_date('1981-2-20 12:34:56','YYYY-MM-DD HH24:MI:SS');
        如果直接写 birthdate>'1981-2-20 12:34:56'会出现格式不匹配,因为表中的格式为: DD-MM月-YY.

      
        select sal from emp where sal>888.88 无错.但
        select sal from emp where sal>$1,250,00;
        会出现无效字符错误.
        改为:
        select sal from emp where sal>to_number('$1.250.00','$9,999,99');
      
        把空值改为0
        select ename,sal*12+nvl(comm,0) from emp;
        这样可以防止comm为空时,sal*12相加也为空的情况.


第九课: Group function 组函数
        max,min,avg ,count,sum函数
      
        select to_char(avg(sal),'99999999,99') from emp;
      
      
        select round(avg(sal),2) from emp;
        结果:2073.21
        
        select count(*) from emp where deptno=10;
        select count(ename) from emp where deptno=10; count某个字段,如果这个字段不为空就算一个.
        select count(distinct deptno) from emp;
        select sum(sal) from emp;

第十课: Group by语句
      
        需求:现在想求,求每个部门的平均薪水.
        select avg(sal) from emp group by deptno;
        select deptno avg(sal) from emp group by deptno;
      
        select deptno,job,max(sal) from emp group by deptno,job;
      
       求薪水值最高的人的名字.
       select ename,max(sal) from emp;出错,因为max只有一个值,但等于max值的人可能好几个,不能匹配.
       应如下求:
       select ename from emp where sal=(select max(sal) from emp);

       Group by语句应注意,

       出现在select中的字段,如果没出现在组函数中,必须出现在Group by语句中.
      
  
  第十一课: Having 对分组结果筛选
     
       Where是对单条纪录进行筛选,Having是对分组结果进行筛选.
    
       select avg(sal),deptno from emp
       group by deptno
       having avg(sal)>2000;
     
       查询  工资大于1200雇员,按部门编号进行分组,分组后平均薪水大于1500,按工薪倒充排列的平均工资.
       select avg(sal) from emp
       where sal>1200
       group by deptno
       having avg(sal)>1500
       order by avg(sal) desc;
      
   第十二课:字查询
     
       谁挣的钱最多(谁:这个人的名字,  钱最多)
     
       select 语句中嵌套select 语句,可以在where,from后.
     
           
       问那些人工资,在平均工资之上.
     
       select ename,sal from emp where sal>(select avg(sal) from emp);



       查找每个部门挣钱最多的那个人的名字.
       //select ename ,deptno from emp where sal in(select max(sal) from ename group by deptno) 查询会多值（此答案有误）.

       select ename, sal ,deptno from emp
       where (deptno, sal) in
       (select deptno, max(sal) from emp group by deptno);

       应该如下:
     
       select  max(sal),deptno from emp group by deptno;当成一个表.语句如下:
       select ename, sal from emp join(select  max(sal) max_sal,deptno from emp group
       by deptno) t on(emp.sal=t.max_sal and emp.deptno=t.deptno);
     
       每个部门平均薪水的等级.
       分析:首先求平均薪水(当成表),把平均薪水和另外一张表连接.
      select deptno, avg_sal, grade from
      (select deptno, avg(sal) avg_sal from emp group by deptno) t
      join salgrade s on (t.avg_sal between s.losal and s.hisal);
     
      每个部门平均的薪水等级.
      select avg(grade) from
      (select deptno, ename, grade from
        emp join salgrade s
        on (emp.sal between s.losal and s.hisal)
      )t
      group by deptno;
     
第十四课:self_table_connection
     
       把某个人的名字以及他的经理人的名字求出来(经理人及这个人在表中同处一行)
     
       分析:首先求出这个人的名字,取他的编号,然后从另一张表与其相对应编号,然后找到经理的名字.
     
       select e1.ename ,e2.ename from emp e1,emp e2 where e1.mgr= e2.empno.
     
       empno编号和MGR都是编号.


第十15课: SQL1999_table_connections  
       
      select ename,dname,grade from emp e,dept d, sqlgrade s
      where e.deptno = d.deptno and e.sql between s.losal and s.hisal and
      job<>'CLERK';
    
      有没有办法把过滤条件和连接条件分开来? 出于这样考虑,Sql1999标准推出来了.有许多人用的还是
      旧的语法,所以得看懂这种语句.
    
    
    
      select ename,dname from emp,dept;(旧标准).
      select ename,dname from emp cross join dept;(1999标准)
     
      select ename,dname from emp,dept where emp.deptno=dept.deptno (旧)
      select ename,dname from emp join dept on(emp.deptno = dept.deptno); 1999标准.没有Where语句.
      select ename,dname from emp join dept using(deptno);等同上句,但不推荐使用.
    
      select ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal);
      join 连接语句, on过滤条件。连接，条件一眼分开。如果用Where语句较长时，连接语句和过滤语句混在一起。
    
      三张表连接：
      slect ename,dname, grade from
      emp e join dept d on(e.deptno=d.deptno)
      join salgrade s on(e.sal between s.losal and s.hisal)
      where ename not like '_A%';
      把每张表连接 条件不混在一起，然后数据过滤条件全部区分开来。读起来更清晰，更容易懂一点。
    
      select e1.ename,e2.ename from emp e1 join emp e2 on(e1.mgr = e2.emptno);

      左外连接：会把左边这张表多余数据显示出来。
      select e1.ename,e2,ename from emp e1 left join emp e2 on(e1.mgr =e2.empno);left 后可加outer
      右外连接：
    select ename,dname from emp e right outer join dept d on(e.deptno =d.deptno); outer可以取掉。
      
      即把左边多余数据，也把右边多余数据拿出来，全外连接。
      select ename,dname from emp e full join dept d on(e.deptno =d.deptno);


16-23 课：求部门平均薪水的等级

       A.求部门平均薪水的等级。

       select deptno,avg_sal,grade from
       (select deptno,avg(sal) avg_sal from emp group by deptno)t
       join salgrade s on(t.avg_sal between s.losal and s.hisal)

       B.求部门平均的薪水等级

       select deptno,avg(grade) from
       (select deptno,ename, grade from emp join salgrade s on(emp.sal between s.losal and
       s.hisal)) t
       group by deptno

       C.那些人是经理

       select ename from emp where empno in(select mgr from emp);
       select ename from emp where empno in(select distinct mgr from emp);

     
       D.不准用组函数，求薪水的最高值（面试题）

     
       select distinct sal from emp where sal not in(
       select distinct e1.sal from emp e1 join emp e2 on (e1.sal<e2.sal));
  

   
       E.平均薪水最高的部门编号

      
       select deptno,avg_sal from
       (select avg(sal)avg_sal,deptno from emp group by deptno)
       where avg_sal=
       (select max(avg_sal)from
       (select avg(sal) avg_sal,deptno from emp group by deptno)
       )
 

   
       F.平均薪水最高的部门名称

       select dname from dept where deptno=
      (
        select deptno from
        (select avg(sal)avg_sal,deptno from emp group by deptno)
        where avg_sal=
        (select max(avg_sal)from
        (select avg(sal) avg_sal,deptno from emp group by deptno)
        )
       )

      G.求平均薪水的等级最低的部门的部门名称
      
        组函数嵌套
        如：平均薪水最高的部门编号，可以E.更简单的方法如下：

        select deptno,avg_sal from
        (select avg(sal) avg_sal,deptno from emp group by deptno)
        where avg_sal =
        (select max(avg(sal)) from emp group by deptno)

      
        组函数最多嵌套两层
      
        分析：
        首先求
        1.平均薪水： select avg(sal) from group by deptno;

        2.平均薪水等级：  把平均薪水当做一张表，需要和另外一张表连接salgrade

        select  deptno,grade avg_sal from
          ( select deptno,avg(sal) avg_sal from emp group by deptno) t
        join salgrade s on(t.avg_sal between s.losal and s.hisal)

      
        上面结果又可当成一张表。
      
        DEPTNO    GRADE    AVG_SAL
      --------  -------  ----------
        30           3   1566.66667
        20           4   2175
        10           4   2916.66667

        3.求上表平均等级最低值
      

        select min(grade) from
        (
          select deptno,grade,avg_sal from
           (select deptno,avg(sal) avg_sal from emp group by deptno)t
          join salgrade s on(t.avg_sal between s.losal and s.hisa)
         )

        4.把最低值对应的2结果的那张表的对应那张表的deptno, 然后把2对应的表和另外一张表做连接。
  

      
select dname ,t1.deptno,grade,avg_sal from
   (
     select deptno,grade,avg_sal from
       (select deptno,avg(sal) avg_sal from emp group by deptno)t
     join salgrade s on(t.avg_sal between s.losal and s.hisal)
   ) t1
join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
   select min(grade) from
   (
     select deptno,grade,avg_sal from
       (select deptno,avg(sal) avg_sal from emp group by deptno) t
     join salgrade s on(t.avg_sal between s.losal and s.hisal)
   )
)

         结果如下：
       
        DNAME    DEPTNO     GRADE    AVG_SAL
      --------  -------  --------   --------
        SALES        30        3    1566.6667
   
       
       H: 视图（视图就是一张表，一个字查询）
      
       G中语句有重复，可以用视图来简化。
       conn sys/bjsxt as sysdba;
       grant create table,create view to scott;
       conn scott/tiger
       创建视图：

create view v$_dept_avg_sal_info as
select deptno,grade,avg_sal from
( select deptno,avg(sal) avg_sal from emp group by deptno)t
join salgrade s on t.avg_sal between s.losal and s.hisal
     

    
       然后
       select * from v$_dept_avg-sal_info
     
       结果如下：
       DEPTNO      GRADE    AVG_SAL
      --------  -------  ----------
        30           3   1566.66667
        20           4   2175
        10           4   2916.66667

       然后G中查询可以简化成：

       select  dname,t1.deptno,grade,avg_sal from
       v$_dept_avg-sal_info t1
       join dept on9t1.deptno =dept.deptno)
       where t1.grade=
       (
 select min(grade) from v$_dept_avg-sal_info t1
       )

    
     求部门经理人中平均薪水最低的部门名称！

select dept.deptno, dept.dname, t.avg_sal from dept 
join 
(
  select deptno,avg_sal from  
    (select deptno, avg(sal) avg_sal from
      (select deptno, empno, ename, sal from emp
      where empno in (select distinct mgr from emp where mgr is not null)
      )group by deptno
    )
  where avg_sal=
  (select min(avg(sal)) from
    (select deptno, empno, ename, sal from emp
    where empno in (select distinct mgr from emp where mgr is not null)
    )group by deptno
  )
) t
on(dept.deptno = t.deptno)

24~
     24_比普通员工的最高薪水还要高的经理人名称

select deptno, empno, ename, sal from emp
where empno in (select distinct mgr from emp where mgr is not null)
and sal>
(select max(sal) from emp
where empno not in (select distinct mgr from emp where mgr is not null)
)