4.6 操作數據庫

視頻講解：光盤\TM\lx\4\操作數據庫．mp4

使用SQL語句操作數據庫，除了查詢操作之外，還包括完成插入、更新和刪除等數據操作。后3種數據操作使用的SQL語言也稱為數據操縱語言（Data Manipulation Language, DML），它們分別對應INSERT、DELETE和UPDATE三條語句。在Oracle 11G中，DML除了包括上面提到的3種語句之外，還包括TRUNCATE、CALL、LOCKTABLE和MERGE等語句。本節主要對INSERT、UPDATE、DELETE、TRUNCATE常用DML語句進行介紹。

4.6.1 插入數據（INSERT語句）

插入數據就是將數據記錄添加到已經存在的數據表中，Oracle數據庫通過INSERT語句來實現插入數據記錄。該語句既可以實現向數據表中一次插入一條記錄，也可以使用SELECT子句將查詢結果集批量插入數據表。

使用INSERT語句有以下注意事項：

當為數字列增加數據時，可以直接提供數字值，或者用單引號引住。

當為字符列或日期列增加數據時，必須用單引號引住。

當增加數據時，數據必須要滿足約束規則，并且必須為主鍵列和NOT NULL列提供數據。

當增加數據時，數據必須與列的個數和順序保持一致。

1．單條插入數據

單條插入數據是INSERT語句最基本的用法，其用法格式如下：

        INSERT INTO table_name [(column_name1[, column_name2]…)]
        VALUES(express1[, express2]…)

table_name：表示要插入的表名。

column_name1和column_name2：指定表的完全或部分列名稱。如果指定多個列，那么列之間用逗號分開。

express1和express2：表示要插入的值列表。

當使用INSERT語句插入數據時，既可以指定列列表，也可以不指定列列表。如果不指定列列表，那么在VALUES子句中必須為每個列提供數據，并且數據順序必須與表列順序完全一致。如果指定列列表，則只需要為相應列提供數據。下面同時以實例來說明增加單行數據的方法。

（1）使用列列表增加數據

在INSERT語句的幾種使用方式中，最常用的形式是在INSERT INTO子句中指定添加數據的列，并在VALUES子句中為各個列提供一個值。

【例4.83】 在dept表中，使用INSERT語句添加一條記錄，具體代碼及運行結果如下：

        SQL> insert into dept(deptno, dname, loc)
          2  values(88, 'design', 'beijing');


        已創建1行。

在上面的示例中，INSERT INTO子句中指定添加數據的列，既可以是數據表的全部列，也可以是部分列。在指定部分列時，需要注意不許為空（NOT NULL）的列必須被指定出來，并且在VALUES子句中的對應賦值也不許為NULL，否則系統顯示“無法將NULL插入”的錯誤信息提示。例如，修改上面的例子，在INSERT INTO子句不指定deptno列（通過desc dept命令可以看到該列是NOT NULL的），將出現如圖4.75所示的錯誤提示。

圖4.75 不許為空的錯誤提示

（2）不使用列列表增加數據

在向表的所有列添加數據時，也可以省略INSERT INTO子句后面的列表清單，使用這種方法時，必須根據表中定義的列的順序，為所有的列提供數據。用戶可以使用DESC命令來查看表中定義列的順序。

【例4.84】 在HR模式下，使用desc命令查看jobs表的結構和列的定義順序，然后使用insert語句插入一條記錄，具體代碼及運行結果如下：

        SQL> connect hr/hr
        已連接。
        SQL> desc jobs;
         名稱                              是否為空？           類型
         -----------------------------------------            --------       

         JOB_ID                            NOT NULL            VARCHAR2(10)
         JOB_TITLE                         NOT NULL            VARCHAR2(35)
         MIN_SALARY                                            NUMBER(6)
         MAX_SALARY                                            NUMBER(6)


        SQL> insert into jobs values('PRO', ’程序員’,5000,10000);


        已創建1行。

數據庫工程師在設計數據表時，為了保證數據的完整性和唯一性，除了需要設置某些列不許為空的約束條件外，還會設置其他一些約束條件。例如，在jobs表中，為了保證表中每條記錄的唯一性，為JOB_ID列定義了主鍵約束條件，這就要求該列的值不允許重復，對于上面的示例代碼，再次嘗試運行，將出現如圖4.76所示的錯誤提示。

圖4.76 主鍵重復的錯誤提示

上面這種情況的解決辦法就是必須重新換一個與現有JOB_ID的值不重復的值。

（3）使用特定格式插入日期值

當增加日期數據時，默認情況下日期值必須匹配于日期格式和日期語言；否則在插入數據時會增加錯誤信息。如果希望使用習慣方式插入日期數據，那么必須使用TO_DATE函數進行轉換。

【例4.85】 使用特定格式插入日期值，具體代碼及運行結果如下：

        SQL> insert into emp (empno, ename, job, hiredate)
          2  values1356, ('MARY', 'CLERK',
          3  to_date('1983-10-20', 'YYYY-MM-DD');
        已創建1行。

（4）使用DEFAULT提供數據

從Oracle Database 9i開始，當增加數據時，可以使用DEFAULT提供數值。當指定DEFAULT時，如果列存在默認值，則會使用其默認值；如果列不存在默認值，則自動使用NULL。

【例4.86】 使用DEFAULT提供數據，具體代碼如下：

        SQL> insert into dept values(60, 'MARKET', DEFAULT);
        SQL> select * from dept where deptno = 60;

運行結果如圖4.77所示。

圖4.77 使用DEFAULT提供數據

（5）使用替代變量插入數據

如果經常需要給某表插入數據，那么為了避免輸入錯誤，可以將INSERT語句放到SQL腳本，并使用替代變量為表插入數據。如果經常需要為emp表插入數據，那么為了避免輸入錯誤，可以使用SQL腳本插入數據。腳本loademp.sql及運行實例如下：

【例4.87】 編寫腳本loademp.sql，使用此腳本插入數據，具體代碼如下：

首先編寫腳本文件loademp.sql，代碼如下：

        accept no prompt ’請輸入雇員號：'
        accept name prompt ’請輸入雇員名：'
        accept title prompt ’請輸入雇員崗位：'
        accept d_no prompt ’請輸入部門號：'
        INSERT INTO emp (empno, ename, job, hiredate, deptno)
        values(&no, '&name', '&title', SYSDATE, &d_no);

腳本文件如圖4.78所示。

圖4.78 編寫腳本loademp.sql

然后利用此腳本文件進行數據插入，代碼如下：

        SQL> @c:\loademp

運行結果如圖4.79所示。

圖4.79 利用腳本文件插入數據

2．批量插入數據

INSERT語句還有一種強大的用法，就是可以一次向表中添加一組數據，也就是批量插入數據。用戶可以使用SELECT語句替換掉原來的VALUES子句，這樣由SELECT語句提供添加的數值。其語法格式如下：

        INSERT INTO table_name [(column_name1[, column_name2]…)] selectSubquery

table_name：表示要插入的表名稱。

column_name1和column_name2：表示指定的列名。

selectSubquery：任何合法的SELECT語句，其所選列的個數和類型要與語句中的column對應。

【例4.88】 在HR模式下，創建一個與jobs表結構類似的表jobs_temp，然后將jobs表中最高工資額（max_salary）大于10000的記錄插入到新表jobs_temp中，具體代碼及運行結果如下（實例位置：光盤\TM\sl\4\15）。

        SQL>  create table jobs_temp(
          2  job_id varchar2(10)primary key,
          3  job_title varchar2(35)not null,
          4  min_salary number(6),
          5  max_salary number(6));


        表已創建。


        SQL>  insert into jobs_temp
          2   select*from jobs
          3   where jobs.max_salary>10000;


        已創建9行。

從上面的運行結果可以看出，使用INSERT語句和SELECT語句的組合可以一次性向指定的數據表中插入多條記錄（這里是9條記錄）。需要注意的是，在使用這種組合語句實現批量插入數據時，INSERT INTO子句指定的列名可以與SELECT子句指定的列名不同，但它們之間的數據類型必須是兼容的，即SELECT語句返回的數據必須滿足INSERT INTO表中列的約束。

互動練習：把一個表的所有列插入到另一個表中。

4.6.2 更新數據（UPDATE語句）

如果表中的數據不正確或不符合需求，那么就需要對其進行修改。Oracle數據庫通過UPDATE語句來實現修改現有的數據記錄。

在更新數據時，更新的列數可以由用戶自己指定，列與列之間用逗號（,）分隔；更新的條數可以通過WHERE子句來加以限制，使用WHERE子句時，系統只更新符合WHERE條件的記錄信息。UPDATE語句的語法格式如下：

        UPDATE table_name
        SET {column_name1=express1[, column_name2=express2...]
         | (column_name1[, column_name2…])=(selectSubquery)}
        [WHERE condition]

table_name：表示要修改的表名。

column_name1和column_name2：表示指定要更新的列名。

selectSubquery：任何合法的SELECT語句，其所選列的個數和類型要與語句中的column對應。

condition：篩選條件表達式，只有符合篩選條件的記錄才被更新。

使用UPDATE語句有以下注意事項：

當更新數字列時，可以直接提供數字值，或者用單引號引住。

當更新字符列或日期列時，必須用單引號引住。

當更新數據時，數據必須要滿足約束規則。

當更新數據時，數據必須與列的數據類型匹配。

1．更新單列數據

當更新單列數據時，set子句后只需要提供一個列。

【例4.89】 在SCOTT模式下，把emp表中雇員名為SCOTT的記錄的工資調整為2460，具體代碼及運行結果如下：

        SQL> update emp
          2  set sal=2460
          3  where ename='SCOTT';

運行結果如圖4.80所示。

圖4.80 更新單列數據

2．更新多列數據

當使用update語句修改表行數據時，既可以修改一列，也可以修改多列。當修改多列時，列之間用逗號分開。

【例4.90】 在SCOTT模式下，把emp表中職務是銷售員（SALESMAN）的記錄的工資上調20%，具體代碼及運行結果如下：

        SQL> update emp
          2  set sal=sal*1.2
          3  where job='SALESMAN';


        已更新4行。

上面的代碼中，UPDATE語句更新記錄的數量通過WHERE子句實現控制的，這里限制只更新銷售員的工資，若取消WHERE子句的限制，則系統會將emp表中所有人員的工資都上調20%。

3．更新日期列數據

當更新日期列數據時，數據格式要與日期格式和日期語言匹配；否則會顯示錯誤信息。如果希望使用習慣方式指定日期值，那么可以使用TO_DATE函數進行轉換。

【例4.91】 在SCOTT模式下，把emp表中雇員編號為7788的入職時間進行調整，入職時間變為1984年1月1日，具體代碼如下：

        SQL> update emp
          2  set hiredate=TO_DATE('1984/01/01', 'YYYY/MM/DD')
          3  where empno=7788;

運行結果如圖4.81所示。

圖4.81 更新日期列數據

4．使用DEFAULT選項更新數據

當更新數據時，可以使用DEFAULT選項提供數據。使用此方式時，如果列存在默認值，則會使用默認值更新數據；如果列不存在默認值，則使用NULL。

【例4.92】 在SCOTT模式下，使用DEFAULT選項更新emp表中雇員名為“SCOTT”的崗位信息，具體代碼如下：

        SQL> select job from emp where ename = 'SCOTT';


        SQL> update emp
          2  set job=DEFAULT
          3  where ename='SCOTT';


        SQL> select job from emp where ename = 'SCOTT';

以上3條SQL語句的運行結果如圖4.82所示。

圖4.82 使用DEFAULT選項更新數據

5．使用子查詢更新數據

另外，同INSERT語句一樣，UPDATE語句也可以與SELECT語句組合使用來達到更新數據的目的。

【例4.93】 在SCOTT模式下，把emp表中工資小于2000的雇員工資調整為管理者的平均工資水平，具體代碼及運行結果如下：

        SQL> update emp
          2  set sal=(select avg(sal)
          3  from emp where job='MANAGER')
          4  where sal<2000;


        已更新6行。

需要注意的是，在將UPDATE語句與SELECT語句組合使用時，必須保證SELECT語句返回單一的值，否則會出現錯誤提示，導致更新數據失敗。

4.6.3 刪除數據（DELETE語句和TRUNCATE語句）

Oracle系統提供了向數據庫添加記錄的功能，同時也提供了從數據庫刪除記錄的功能。從數據庫中刪除記錄可以使用DELETE語句和TRUNCATE語句，但這兩種語句還是有很大區別的，下面分別進行講解。

1.DELETE語句

DELETE語句用來刪除數據庫中的所有記錄和指定范圍的記錄，若要刪除指定范圍的記錄，同UPDATE語句一樣，要通過WHERE子句進行限制，其語法格式如下：

        DELETE FROM table_name
        [WHERE condition]

table_name：表示要刪除記錄的表名。

condition：篩選條件表達式，是個可選項，當該篩選條件存在時，只有符合篩選條件的記錄才被刪除掉。

刪除滿足條件的數據：當使用DELETE語句刪除數據時，通過指定WHERE子句可以刪除滿足條件的數據。

【例4.94】 在HR模式下，刪除jobs表中職務編號（job_id）是“PRO”的記錄，具體代碼及運行結果如下：

        SQL> delete from jobs where job_id='PRO';


        已刪除1行。

上面的代碼中，DELETE語句刪除記錄的數量是通過WHERE子句實現控制的，這里限制只刪除職務編號（job_id）是“PRO”的記錄，若取消WHERE子句的限制，則系統會將jobs表中所有人員的記錄都刪除。

刪除表的所有數據：當使用DELETE刪除表的數據時，如果不指定WHERE子句，那么會刪除表的所有數據。

【例4.95】 刪除emp表中所有數據，具體代碼及運行結果如下：

        SQL> delete from emp;


        已刪除5行。

2.TRUNCATE語句

如果用戶確定要刪除表中的所有記錄，那么除了可以使用DELETE語句之外，還可以使用TRUNCATE語句，而且Oracle本身也建議使用TRUNCATE語句。

使用TRUNCATE語句刪除表中的所有記錄要比DELETE語句快得多。這是因為使用TRUNCATE語句刪除數據時，它不會產生回滾記錄。當然，執行了TRUNCATE語句的操作也就無法使用ROLLBACK語句撤銷。

【例4.96】 在HR模式下，使用TRUNCATE語句清除自定義表jobs_temp中的所有記錄，具體代碼及運行結果如下：

        SQL> truncate table jobs_temp;


        表被截斷。


        SQL> select * from jobs_temp;


        未選定行

另外，需要補充說明的是，在TRUNCATE語句中還可以使用REUSE STORAGE關鍵字或DROP STORAGE關鍵字，前者表示刪除記錄后仍然保存記錄所占用的空間，后者表示刪除記錄后立即回收記錄占用的空間。默認情況下TRUNCATE語句使用DROP STORAGE關鍵字。