Oracle學習總結

1、基本概念

Oracle資料庫： 是一個檔案系統，是物理概念
例項： 在Oracle的資料庫中可有多個例項，通常我們只用一個例項（orcl）
使用者： 一個例項下有多個使用者
表空間： 一個例項下有多個表空間，表空間是邏輯概念，一個表空間對應一個或者多個物理儲存檔案（.dbf、.ora）
使用者和表空間的關係： 一個使用者有一個預設的表空間，一個表空間可以為多個使用者作為預設表空間，使用者和使用者之間的資料是隔離的，資料不會混

Oracle中使用者的概念相當於MySQL中database的概念

2、命令列常見操作

（1）登陸
       sqlplus 使用者名稱/密碼 【as sysdba】

（2）檢視當前資料庫使用者
       show user

（3）使用者的切換
       conn 使用者名稱/密碼 【as sysdba】

（4）檢視使用者下所有的表
       select * from tab;

（5）檢視使用者下所有的表
       select * from tab;

（6）在sys使用者查詢scott使用者下的dpet表（sys的許可權必須很大才能跨使用者查詢）
       select * from scott.dpet;

3、常見操作

   很多用法和MySQL差不多

（1）數字與日期函式
       select round(12.789, 2) from dual; --四捨五入取小數點後面兩位
       select trunc(12.789, 2) from dual; --取小數點後面2位
       select mod(10, 3) from dual; --取餘數

       select ename, trunc((sysdate - hiredate) / 7) from emp; --現在到入職的週數
       select ename, trunc(months_between(sysdate, hiredate)) from emp; --現在到入職的月數

（2）轉換函式
       select to_char(sysdate, ‘yyyy-mm-dd HH24:mi:ss’) from dual; --時間轉換
       select to_char(sysdate, ‘fmyyyy-mm-dd’) from dual; --時間轉換,去掉前導零
       select to_date(‘1994-01-09’, ‘yyyy-mm-dd’) from dual;–字串轉時間

       select to_char(sal, ‘$99,999’) from emp; --數字格式化

（3）通用函式
       select ename, sal*12+ nvl(comm, 0) from emp; --nvl處理null值

       select ename, decode(job, ‘ANALYST’, ‘分析員’,
                                                   ‘MANAGER’, ‘管理員’,
                                                   ‘其他人員’)
                                                from emp; --類似於java中的switch…case…，前三個引數是必須的

       select ename, case when job = ‘ANALYST’ then ‘分析員’
                                        when job = ‘MANAGER’ then ‘管理員’
                                     else ‘其他人員’
                               end
                              from emp; --和decode功能差不多

（4）外連線（左右連線）
     兩張表做連線查詢時其中一張表要查詢全量資料（不會因為另一張表的資料的關聯而篩選掉）
     在兩張表關聯的時候，非全量表的欄位後面加上 (+) 就可以做外連線查詢
        select * from dept d, emp e where d.deptno = e.deptno(+); --全量表在左端，叫左連線，反之亦然

    --表連線
    select e1.ename as 職員, e2.ename as 上級, e1.sal as 薪資, s.grade as 薪資 from     dept d, emp e1, emp e2, salgrade s
        where e1.deptno = d.deptno(+)
            and e1.mgr = e2.empno(+)
            and e1.sal between s.losal and s.hisal;

    select * from emp e join dept d on e.deptno = d.deptno;

     – sql1999的外連線查詢（重點）
        select * from emp e left join dept d on e.deptno = d.deptno;
        select * from emp e right join dept d on e.deptno = d.deptno;
        select * from emp e inner join dept d on e.deptno = d.deptno;

（3）分組
     select job, count(*) c
     from emp
     where ename <> null
     group by job
     having c > 3
     order by c desc

（5）子查詢
     in 的執行效率比較低，可以用 exists() 代替，exists子查詢一般要和外側查詢關聯的
     select * from dept t where exists (select * from emp e where e.deptno = t.deptno); --查詢有員工的部門

（6）union 與 union all
     union: 兩個集合合併時會去重
     union: 把兩個集合做並集時不會去重