SQLCookBook第四章学习日记13-CSDN博客

本文链接：https://blog.csdn.net/CCyutaotao/article/details/53259062

4.6一次向多个表中插入记录

问题：
要将一个查询中返回的行插入到多个目标表中。例如，要将表dept中的一些行插入到表dept_east、dept_west、dept_mid中。这三个表与表dept有着相同的结构（相同的列和数据类型），并且这三个表都是空的。

解决方案：此解决方案是要将查询的解雇插入到目标表中。与4.4不同，此问题有多个目标表。

Oracle
使用install all 或inset first语句。这两种方法除了关键字all与first不同外，其语法都相同。下面的语句使用了insert all命令来同时兼顾所有的目标表：

insert all  
    when loc in ('NEW YORK', 'BOSTON') then
        into dept_east (deptno, dname, loc) values (deptno, dname, loc)
    when loc = 'CHICAGO' then
        into dept_mid (deptno, dname, loc) values (deptno, dname,loc)
    else 
        into dept_west (deptno, dname, loc) values (deptno, dname, loc)
select deptno,dname,loc
    from dept

DB2
将所有的目标表用union all 构成一个内联视图，并以该内联视图作为insert into的目标。必须要在这些表中设置约束条件，以却白这些行插入到正确的表中。

create table dept_east
(
    deptno integer,
    dname varchar(10),
    loc varchar(10) check (loc in ('NEW YORK', 'BOSTON'))
)

create table dept_mid 
(
    deptno integer,
    dname varchar(10),
    loc varchar(10) check (loc = 'CHICAGO')
)

create table dept_west
(
    deptno integer,
    dname varchar(10),
    loc varchar(10) check (loc = 'DALLS')
)

insert into (
    select * from dept_west union all
    select * from dept_mid union all
    select * from dept_east 

) select * from dept

MySQL、PostgreSQL和SQL Server
在编写本书时，现有的版本都不支持这种多表插入操作

讨论：
Oracle
Oracle的多表插入操作用where-then-else子句，判断嵌套的select语句各结果行的目标表，并插入到相应的表中。虽然在本章的示例中，使用insert all和insert first 将产生同样的结果，但是这两者之间还是有一定的区别。insert first在遇到条件表达式为真的情况时，将会立即跳出where-then-else,而insert all即使前面的条件表达式为真，也会继续检查其他所有的条件，所以可以使用insert all命令来将同一行插入到多个表中。

DB2
DB2解决方案有些复杂。它需要所要插入数据的表有约束条件，以确保所有从子查询中返回的行将插入到正确的表中。解决方案所用的方法就是讲目标表用union all 定义成视图，并以它为插入目标。如果insert语句中各表之间的约束条件结果不唯一（也就是说多个表的约束条件结果相同）则insert语句就不知道把数据放在哪个表中，造成插入操作失败

MySQL、PostgreSQL、和SQL Server
到编写本书时，只有Oracle和DB2提供了用一条语句将查询结果返回的行插入到一个或多个表中的机制。

4.7阻止对某几列插入

问题
防止用户或是错误软件应用程序对某几列插入数据。例如，只允许某个程序向emp表中插入empno，ename，和job列

解决方案：
在表中创建一个视图，该视图只显示允许用户进行操作的列，强制所有的插入操作都通过该视图进行。

例如，创建一个只显示表emp中特定3列的视图：

create view new_emps as
    select empno, ename, job
        from emp

对只允许操作视图中三个字段的用户和程序，授权他们访问该视图，而不允许他们对表emp静心插入操作。用户可以将新的记录插入到视图new_emps中，从而创建emp表中新的记录，但是不能对视图所定义的三个字段之外的列进行操作。

讨论：
当对类似于本解决方案中的简单视图进行插入操作时，数据库服务器将插入操作转换到视图的基表。例如下面的插入语句：

insert into new_emps
    (empno ename, job)
        values (1, 'Jonathan', 'Editor')

会被转换为以下列的插入语句：

insert into emp
    (empno ename, job)
        values (1, 'Jonathan', 'Editor')

也可以对内联视图进行插入（当前只有Oracle支持此项操作）：

insert into 
    (select empno, ename, job
        from emp)
    values(1, 'Jonathan', 'Editor')

视图的插入操作是一个复杂的话题。除了最简单的视图之外，操作规则变得越来越复杂。如果想要用视图进行插入操作，必须要认真思考和理解相应数据库厂商的有关文档。

4.8在表中编辑操作

问题
要修改表中某些（或全部）行的值。例如，可能想要将部门20中所有员工的工资增加10%，下面的结果集显示了该部门员工的deptno ename 和sal字段

select deptno,ename,loc
    from emp
        where deptno = 20
        order by 1,3

要将所有的sal字段值增加10%

解决方案：

使用update语句来修改数据库表中已有行。例如：

update emp
    set sal  = sal*1.10
where deptno = 20

讨论：
当准备要对大量的数据进行更新时，用户可能需要先预览一下结果，此时可以使用一个seslect语句，该语句中包含想要放到set子句中的表达式。下面的select语句显示了工资增加10%后的结果：

select deptno,
    ename,
    sal as orig_sal,
    sal*.10 as amt_to_add,
    sal*1.10 as new_sal
from emp
where deptno =20
order by 1,5

增加的工资被分为两列：一列是显示与原工资的差额，而另一列显示增加后的工资。