oracle

第一課：用戶端

        1. sql plus(用戶端），指令行直接輸入：sqlplus，然後按提示輸入使用者名，密碼。

        2. 從開始程式運作:sqlplus，是圖形版的sqlplus.

        3. [url]http://localhost:5560/isqlplus[/url]

        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 ,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;

        顯示:

        -------------------

        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

       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

       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

        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

       然後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