ORACLE學習-7.資料處理

標籤：color   rac   miss   tab   pop   列表   刪除語句   art   account   

資料處理是指使用sql的DDL語句，對錶中的資料進行增刪改查。

插入操作

INSERT INTO    table [(column [, column...])]VALUES        (value [, value...]);

向表中插入空值

隱式方式：在列名表中省略該欄位

INSERT INTO    departments (department_id,                           department_name    )VALUES        (30, ‘Purchasing‘);

顯示方式：在values子句中執行null值

INSERT INTO    departmentsVALUES        (100, ‘Finance‘, NULL, NULL);

插入指定的值

--記錄當前系統的時間和日期INSERT INTO employees (employee_id,                  first_name, last_name,                  email, phone_number,                 hire_date, job_id, salary,                  commission_pct, manager_id,                 department_id)VALUES           (113,                  ‘Louis‘, ‘Popp‘,                  ‘LPOPP‘, ‘515.124.4567‘,                  SYSDATE, ‘AC_ACCOUNT‘, 6900,                  NULL, 205, 100);

從其他表中拷貝

--不必書寫 VALUES 子句。 --子查詢中的值列表應與 INSERT 子句中的列名對應INSERT INTO emp2 SELECT * FROM employeesWHERE department_id = 90；INSERT INTO sales_reps(id, name, salary, commission_pct)SELECT employee_id, last_name, salary, commission_pctFROM   employeesWHERE  job_id LIKE ‘%REP%‘;

更新資料

UPDATE        tableSET        column = value [, column = value, ...][WHERE         condition];

在update中使用子查詢

--更新 114號員工的工作和工資使其與205號員工         相同UPDATE   employeesSET      job_id  = (SELECT  job_id                     FROM    employees                     WHERE   employee_id = 205),          salary  = (SELECT  salary                     FROM    employees                     WHERE   employee_id = 205) WHERE    employee_id    =  114;

刪除語句

DELETE FROM      table[WHERE      condition];

在delete中使用子查詢

--從emp1表中刪除dept1部門名稱中含Public字元的部門idDELETE FROM emp1WHERE  department_id =                       (SELECT department_id                        FROM   dept1                        WHERE  department_name LIKE ‘%Public%‘);

資料庫事物

 

ORACLE學習-7.資料處理