Oracle的基本文法和操作

標籤：use   模糊查詢   group   左外串連   length   檢索   dql   建立使用者   特殊   

ORACLE中的幾大基本操作指令及其含義

   資料定義語言 (Data Definition Language)（DDL）：用於建立、修改、刪除資料庫的對象（對列操作）

      1.CREATE:建立表或其他對象的結構

      2.ALTER:修改表或其他對象的結構

      3.DROP:刪除表或其他對象的結構

      4.TRUNCATE:刪除表資料，保留表結構

  資料操作語言（DML）：用於修改表中的資料（對行操作）

      1.INSERT:將資料插入到資料表中

      2.UPDATE:跟新資料表中已經存在的資料

      3.DELETE:刪除資料表中的資料

 事務控制語言（TCL）：用來維護資料一致性的語句

        1.COMMIT:提交，確認已經進行的資料改變

         2.ROLLBACK:復原，取消已經進行的資料改變

 資料查詢語言（DQL）：用來查詢需要的資料

        1.SELECT:查詢

資料控制語言（DCL）：用於執行許可權的授予和收回操作

    1.GRANT：用於給使用者和角色許可權的授予

    2.REVOKE:用於使用者和角色許可權的收回

    3.CREATE USER:建立使用者

 

常用的Oracle語句：

 建立一張表：

                      CREATE  TABLE employee(

                                   id  NUMBER(6),

                                   name  VARCHAR2(20),

                                   gender CHAR(1),

                                    salery NUMBER(6,2)

                              )

 查看錶結構：

                DESC  table_name;

修改表名：

                RENAME  old_name  TO  new_name;

增加列：

            給表（emp）增加一列hiredate,並設定預設值為當前日期

             ALTER TABLE   emp   ADD(

                      hiredate   DATE   DEFAULT  sysdate );

刪除列：

             刪除表emp的列hiredate

            ALTER TABLE emp  DROP(hiredate)

 

修改列：

             修改表emp的列job，並增加預設值的設定

            ALTER  TABLE  emp  

            MODIFY(job  VARCHAR2(20)   DEFAULT   ‘CLERK‘)

插入一條記錄：

               INSERT  INTO emp(id , name , job , salary)

               VALUES(200 , ‘jack‘ , ‘SALEMAN‘ ,5500)

更改表中的資料：

        更改職員ROSE的薪水為8500

        UPDATE  emp SET salary = 8500  WHERE name = ‘ROSE‘

刪除表中的資料：(如果沒有WHERE子句，則全表的資料都會被刪除)

         刪除emp中職位為空白的員工的記錄

        DELETE FROM empWHERE job is null

      刪除全表記錄:

          DELETE FROM emp (速度慢，可以回退)

          TRUNCATE TABLE emp（速度快，立即執行，不可回退）

字串函數：

            CONCAT(char1 , char 2)  等價於串連操作字元 “||”

                       SELECT   CONCAT(CONCAT(ename , ‘ ：‘) ， sal)

                        多個字串串連，使用||更直觀

                        SELECT ename  || ‘  ：‘|| sal FROM emp;

              LENGTH(char1):返回字串char1的長度

                         SELECT ename ,LENGTH(ename)  FROM emp

             UPPER(char)用於將字串中每個字元轉換為大寫形式

             LOWER(char)用於將字串中每個字元轉換為大寫形式

             INITCAP(char)用於將字串中每個單詞的首字母大寫，其餘字元小寫，單詞之間需要用空格或非字元分隔開

            TRIM(c2 FROM C1)從c1的前後截去c2

             LTRIM( c1 ,c2) 從c1的左邊截去c2

             RTRIM(c1 , c2)從c1的右邊截去c2

             LPAD、RPAD:補位函數，用於自字串char1的左邊或右邊用char2補足到n位，char2可以重複多次

             LPAD(char1, n , char2) 左補位函數

             RPAD(char1, n , char2) 有補位函數

NVL(expr1 , expr2):如果expr1是NULL，則取expr2

NVL2(expr1 ，expr2，expr3):如果expr1不是NULL，則取expr2，如果expr1是NULL，返回expr3.

運算子：

        >，<，>=，<=，!=，<>，= ，AND， OR

[NOT]   LIKE：模糊查詢

        %：表示0到多個字元

        -：表示單個字元

[NOT]   IN(list)用來取出[不]符合列表範圍中的資料

BETWEEN  e1  AND  e2

      用來查詢符合e1到e2範圍條件的資料,包含e1和e2

IS NULL 和IS  NOT  NULL:判斷欄位中的資料是否為NULL

ANY  和  ALL:不能單獨使用需要配合單行比較符> 、 >=  、 <  、<=   一起使用

          > ANY:大於最小

            .......

DISTINCT:過濾掉重複的資料     用法：  DISTINCT   colum-name

ODER  BY : 按一定規則排序    ASC:升序排列，預設         DESC：  降序排列

彙總函式：

            MAX()    MIN()   :用來取得列或運算式的最大值或最小值

           AVG（） SUM（）：用來統計列或運算式的平均值和總和

          COUNT（）：用來計算表中的記錄條數，忽略NULL值

        彙總函式忽略NULL值，所以使用時一般會對空值使用NVL或NVL2進行處理

GROUP  BY :分組查詢，查詢的出來結果是以組進行操作後的資料

HAVING子句：用來對分組後的結果進一步限制，比如按部門分組後，得到每個部門的最高薪水，可以繼續限制輸出結果

                          必須跟在GROUP  BY後面，不能單獨存在

               ——查詢每個部門的最高薪水，只有薪水高於5000的記錄才被顯示出來——

             SELECT   deptno ,  MAX(sal)     FROM    emp

              GROUP BY   deptno   HAVING  MAX(sal) > 5000;

查詢語句執行順序：

  1.FROM 子句，執行順序為從後往前、從右至左

         資料量少的表盡量放在後面

2.WHERE子句：執行順序為自上而下，從右至左

           將能過濾掉最大數量記錄的條件寫在WHERE子句的最右

3.GROUP BY：執行順序為從左向右分組

           最好在GROUP BY之前使用WHERE將不需要的記錄在GROUP BY之前過濾掉

4.HAVING子句：消耗資源

          盡量避免使用，HAVING會在檢索出所有記錄之後才對結果進行過濾，需要排序等操作

5.SELECT子句：少使用*號，盡量取欄位名稱

              ORACLE在解析的過程中，通過查詢資料字典將*號一次轉換為所有的列名，消耗時間和記憶體

6.ORDER BY ：執行順序為從左至右排序，消耗資源

關聯查詢：

          內串連：返回所有滿足串連條件的記錄

                 SELECT  e.ename , d.dname

                 FROM  emp  e , dept  d

                 WHERE e.deptno = d.deptno;

               

                 SELECT e.ename , d.dname

                 FROM emp e    [INNER]   JOIN  dept d

                ON(e.deptno = d.deptno);

      左外串連：

                    emp表所為驅動表，以emp表為準

                         SELECT  e.ename , d.dname

                         FROM   emp e  LEFT [OUTER]  JOIN dept d

                        ON e.deptno = d.deptno;    

          

右外串連：

                    emp表所為驅動表，以emp表為準

                         SELECT  e.ename , d.dname

                         FROM   emp e  RIGHT   [OUTER]  JOIN   dept d

                        ON  e.deptno = d.deptno;    

全外串連：左外串連和有外串連的查詢結果的總和       

 

                       SELECT  e.ename , d.dname

                         FROM   emp e  FULL   OUTER JOINdept d

                        ON e.deptno = d.deptno; 

自串連：一種特殊的串連查詢，資料來源是一張表，即關聯關係來自於單表的多個列

                     

Oracle的基本文法和操作