oracle学习笔记

第一课：客户端   
        1. Sql Plus(客户端），命令行直接输入：sqlplus，然后按提示输入用户名，密码。   
        2. 从开始程序运行:sqlplus，是图形版的sqlplus.   
        3. http://localhost:5560/isqlplus   
          
        Toad：管理， PlSql Developer:   
  
第二课：更改用户   
        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 '%$a%' 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 * 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  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);   
         
       每个部门的平均薪水的等级.   
       分析:首先求平均薪水(当成表),把平均薪水和另外一张表连接.   
         
         
第十四课: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 ,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 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 9t.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 max(sal) from emp where empno not in    
     (select distinct mgr from emp where mgr is not null);    
       
    select ename 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);       
     )    
    
第二十四课    
    备份与恢复    
    DOS环境下:exp导出,imp导入    
    --drop user yugang cascade;    
    --cd \    
    --cd temp    
    =-delete *.*    
    --exp    
    --create user yugang indentified by yugang default tablespace users quota 10M on         users    
    --grant create session,create table,create view to yugang    
    --imp the data    
    第一次输入的用户名密码为:yugang/yugang    
    第二次输入的用户名密码为:导出数据的用户的用户名和密码    
    --rollback    
    create table emp2 as select * from emp;    
  
第二十五课:rownum    
    rownum是在Oracle中在表的后面加的一个尾字段,并且只能使用诸如rownum<n或rownum<=n这样的        形式,不能与>或>=相连接    
  
    1.求薪水值最高的前5条记录.    
    select ename,sal from emp order by sal desc where sal <= 5;    
    2.求薪水值最高的第6~10条记录.    
    select ename,sal from    
     (    
       select ename,sal,rownum r from    
         (select ename,sal from emp order by sal desc)    
     )    
    where r >=6 and r <= 10;    
  
  
第二十六课:homework_dml_transaction    
    SQL面试题    
    有三张表S、C、SC    
    S(SNO、SNAME)代表（学号、姓名）    
    C(CNO、CNAME、CTEACHER)代表（课号、课名、老师）    
    SC(SNO、CNO、SCGRADE)代表（学号、课号、成绩）    
    1.求出没选过郭富城老师的所有学生姓名    
    2.列出2门以上（含2门）不及格的学生姓名及平均成绩    
    3.既学过1号课程又学过2号课程的所有学生姓名    
    1.select sname from s join sc on (s.sno = sc.sno) join c on (c.cno = sc.cno)    
    where c.cteacher <> '郭富城';    
    2.select sname from s where sno in    
     (    
       select sno from sc where scgrade < 60 group by sno having count(*) >= 2    
     )    
    3.select sname from s where sno in    
     (    
       select sno from sc where cno=1 and sno in    
         (select distinct sno from sc where cno=2)    
     )    
  
    事务transaction代表一组不可分割的操作，要么全部执行，要么全部不执行，    
    transaction起始于一条DML语句，结束于commit;语句，或者是DCL、DDL语句，在事务未提交前       
    可以通过rollback回滚事务，正常退出事务会自动提交，非正常退出事务会自动回滚。    
  
第二十七课：create table    
    create table student    
    (    
    id number(6),    
    name varchar2(20),    
    sex number(1),    
    age number(3),    
    sdate date,    
    grade number(2) default 1,    
    class number(4),    
    email varchar2(50)    
    );