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 countries c

on(l.country_id = c.country_id)

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 ...;


多表查询示意图:

SQL 基础之子查询、多表插入、merge 语句、跟踪一段时间数据变化(二十)

多表插入作用如下:

  • 使用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


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

from employees

where employee_id > 200;


有条件INSERT ALL :示例

SQL 基础之子查询、多表插入、merge 语句、跟踪一段时间数据变化(二十)

有条件INSERT ALL

insert all

when hiredate <  ' 01-JAN-95 ' then

into emp_history values(EMPID,HIREDATE,SAL)

when comm is not null then

into emp_sales values(EMPID,COMM,SAL)

select employee_id empid, hire_date hiredate,

salary sal, commission_pct comm

from employees

SQL 基础之子查询、多表插入、merge 语句、跟踪一段时间数据变化(二十)

有条件INSERT FIRST

insert first

when salary < 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

from employees


旋转INSERT

将销售记录从非关系型数据库表中设置为关系格式

SQL 基础之子查询、多表插入、merge 语句、跟踪一段时间数据变化(二十)


insert all

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)

when matched then

update set

c.first_name = e.first_name,

c.last_name = e.last_name,

...

delete where (e.commission_pct is not null)

when not matched then

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;


merge into copy_emp3 c

using (select * from employees ) e

on (c.employee_id = e.employee_id)

when matched then

update set

c.first_name = e.first_name,

c.last_name = e.last_name,

...

delete where (e.commission_pct is not null)

when not matched then

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

from employees

versions between scn minvalue

and maxvalue

where last_name = 'Lorentz';





本文转自 yuri_cto 51CTO博客,原文链接:http://blog.51cto.com/laobaiv1/1910564,如需转载请自行联系原作者

上一篇:《Java编码指南:编写安全可靠程序的75条建议》—— 指南2:不要在客户端存储未经加密的敏感数据


下一篇:手绘稿如何1秒变身数据大屏?深度学习让人人成为可视化专家