SQL 基礎之子查詢、多表插入、merge 語句、跟蹤一段時間資料變化（二十）

使用子查詢處理資料

可以使用子查詢中的資料操縱語言（DML）語句：

使用内嵌視圖檢索資料

從一張表向另一張表複制資料

基于另一張表的值更新表中資料

基于另一張表的值删除表中的行

使用子查詢作為資料源檢索資料

select department_name, city from departments

natural join (select l.location_id, l.city, l.country_id

from loc l

join countries c

on(l.country_id = c.country_id)

join regions using(region_id) where region_name = 'europe');

使用子查詢作為目标插入資料

insert into (select l.location_id, l.city, l.country_id from locations l

join regions using(region_id)

where region_name = 'europe')

values (3300, 'Cardiff', 'UK');

在 DML  語句中使用WITH CHECK OPTION 

WITH CHECK OPTION 關鍵字，禁止子查詢中行的更改。

顯示的預設功能概述

使用 DEFAULT 關鍵字設定字段預設值。

允許使用者控制什麼時候使用預設值應用到資料

可以在INSERT和UPDATE語句中顯式使用預設值

使用顯式的預設值

INSERT 與 DEFAULT：

insert into deptm3 (department_id, department_name, manager_id) values (300, 'engineering', default);

UPDATE 與 DEFAULT：

update deptm3 set manager_id = default where department_id = 10;

從另一張表中複制行

INSERT 語句的子查詢：

insert into sales_reps(id, name, salary, commission_pct)

select employee_id, last_name, salary, commission_pct

from employees

where job_id like '%REP%';

不使用 VALUES 子句

INSERT 子句與子查詢的列數、類型相比對

使用以下類型完成多表插入：

– 無條件 INSERT

– 旋轉 INSERT

– 有條件 INSERT ALL

– 有條件 INSERT FIRST

insert all

into target_a values(... , ... , ...)

into target_b values(... , ... , ...)

into target_c values(... , ... , ...)

select ...

from sourcetab

where ...;

多表查詢示意圖：

<a href="https://s4.51cto.com/wyfs02/M01/8F/38/wKiom1jX5cODE2nzAADCLi6us0w436.jpg" target="_blank"></a>

多表插入作用如下：

使用INSERT…SELECT語句插入行到多個表中，作為一個單一的DML語句。

資料倉庫系統中使用的多表INSERT語句将一個或多個操作的源資料寫入到一組目标表中。

它們提供顯着的性能改善：

      – 單個 DML 語句與多表 INSERT…SELECT 語句

      – 單個 DML 語句與使用 IF...THEN 文法完成多表插入

多表INSERT 語句的類型

以下是不同類型的多表 INSERT 語句：

 無條件 INSERT

 旋轉 INSERT

 有條件 INSERT ALL

 有條件 INSERT FIRST

多表 INSERT 文法

insert [conditional_insert_clause]

[insert_into_clause values_clause] (subquery)

有條件 INSERT 子句:

[ALL|FIRST]

[WHEN condition THEN] [insert_into_clause values_clause]

[ELSE] [insert_into_clause values_clause]

無條件 INSERT ALL

into sal_history values(empid,hiredate,sal)

into mgr_history values(empid,mgr,sal)

select employee_id empid, hire_date hiredate,

salary sal, manager_id mgr

where employee_id &gt; 200;

有條件INSERT ALL ：示例

<a href="https://s4.51cto.com/wyfs02/M01/8F/36/wKioL1jX5wiz0iMcAABqAzWKp2M786.jpg" target="_blank"></a>

有條件INSERT ALL

when hiredate &lt;  ' 01-JAN-95 ' then

into emp_history values(EMPID,HIREDATE,SAL)

when comm is not null then

into emp_sales values(EMPID,COMM,SAL)

salary sal, commission_pct comm

<a href="https://s3.51cto.com/wyfs02/M02/8F/38/wKiom1jX55-i7xUoAAC-0_pDMWg002.jpg" target="_blank"></a>

有條件INSERT FIRST

insert first

when salary &lt; 5000 then

into sal_low values (employee_id, last_name, salary)

when salary between 5000 and 10000 then

into sal_mid values (employee_id, last_name, salary)

else

into sal_high values (employee_id, last_name, salary)

select employee_id, last_name, salary

旋轉INSERT

将銷售記錄從非關系型資料庫表中設定為關系格式

<a href="https://s1.51cto.com/wyfs02/M00/8F/38/wKiom1jX6F7jhKb5AAB4z1D8yJo619.jpg" target="_blank"></a>

into sales_info values (employee_id,week_id,sales_MON)

into sales_info values (employee_id,week_id,sales_TUE)

into sales_info values (employee_id,week_id,sales_WED)

into sales_info values (employee_id,week_id,sales_THUR)

into sales_info values (employee_id,week_id, sales_FRI)

select employee_id, week_id, sales_MON, sales_TUE,

sales_WED, sales_THUR,sales_FRI

from sales_source_data;

限制條件

隻能對表執行多表插入語句，不能對視圖或物化視圖執行；

不能對遠端表執行多表插入語句；

不能使用表集合表達式；

不能超過999個目标列；

在RAC環境中或目标表是索引組織表或目标表上有BITMAP索引時，多表插入語句不能并行執行；

多表插入語句不支援執行計劃穩定性；

多表插入語句中的子查詢不能使用序列。

MERGE 語句

提供根據條件進行更新、插入、删除資料的功能

如果資料存在執行UPDATE，如果不存在則INSERT：

      – 避免單獨更新

      – 提高了性能和易用性

      – 非常适用于資料倉庫

MERGE  語句文法

使用MERGE語句，您可以根據條件插入，更新或删除表中的行

merge into table_name table_alias

using (table|view|sub_query) alias

on (join condition)

when matched then

update set

col1 = col1_val,

col2 = col2_val

when not matched then

insert (column_list)

values (column_values);

合并行：示例

插入或更新COPY_EMP3表中與EMPLOYEES相比對的行。

merge into copy_emp3 c

using (select * from employees ) e

on (c.employee_id = e.employee_id)

c.first_name = e.first_name,

c.last_name = e.last_name,

...

delete where (e.commission_pct is not null)

insert values(e.employee_id, e.first_name, e.last_name,

e.email, e.phone_number, e.hire_date, e.job_id,

e.salary, e.commission_pct, e.manager_id,

e.department_id);

合并行 示例

truncate table copy_emp3;

select * from copy_emp3;

insert values(e.employee_id, e.first_name, ...

跟蹤資料的變化

閃回版本查詢示例

select salary from employees3 where employee_id = 107;

update employees3 set salary = salary * 1.30 where employee_id = 107;

commit;

select salary from employees3 versions between scn minvalue and maxvalue where employee_id = 107;

VERSIONS BETWEEN 子句

select versions_starttime "start_date",

versions_endtime "end_date",

salary

versions between scn minvalue

and maxvalue

where last_name = 'Lorentz';

本文轉自 yuri_cto 51CTO部落格，原文連結：http://blog.51cto.com/laobaiv1/1910564，如需轉載請自行聯系原作者