「干货」SQL之DML语句总结（数据库dml语句）

【十六】DML语句总结

16.1 Insert语句的基本类型

1）单表一次插入一行，有values关键字。

2）单表一次插入多行，使用子查询，没有values关键字。

3）多表插入，又分Insert all和Insert first两种形式。

16,2单表单行插入

1）SQL> create table a (id int,name char(10) default 'aaa');

name列指定了default值;

2）SQL> insert into a values(1,'abc');

表a后没有所选列，values必须指定所有字段的值;

3）SQL> insert into a values(2,default);

同上，name字段用default占位;

4）SQL> insert into a values(3,null);

表a后没有所选列，name字段用null占位;

5）SQL> insert into a (id) values(4);

未选定的字段如果指定了default,则以default的值代替null;

6）SQL> insert into (select id from a) values (5);

这种形式本质同上，只不过表a的形式以结果集代之;

7）SQL> insert into a values(6,(select dname from dept where deptno=10)); 

values里的某列使用subquery引用另一个表的数据。

注意几点：

1）不符合约束条件的insert不能成功；

2）default不但可以用于insert语句, 也可以用于update语句；

3）values后面只能跟返回单行单列的子查询。

SQL> insert into a values(select deptno,dname from dept where deptno=10);

报错：ORA-00936: 缺失表达式

更正如下：

SQL> insert into a values((select deptno from dept where deptno=10), (select dname from dept where deptno=10));
SQL> commit;

8）insert WITH CHECK OPTION的用法

SQL> insert into (select id from a where id<100 WITH CHECK OPTION) values (20);
SQL> rollback;
SQL> insert into (select id from a where id<100 WITH CHECK OPTION) values (101);

报错: ORA-01402: view WITH CHECK OPTION where-clause violation

SQL>insert into (select id from a where id<100) values (120);

没有with check option约束，where后面的条件形同虚设。

16.3单表一次插入多行

语法：主句去掉了values选项，使用select子查询；

SQL> create table b as select * from a where 1>2;

建立一个空表b，结构来自a表：

SQL> insert into b select * from a where name='aaa';

插入的是结果集，注意没有values选项；

SQL> insert into b(id) select id from a where id in(1,3);

使用子查询（结果集）插入，对位。

16.4多表插入

16.4.1作用：

一条INSERT语句可以完成向多张表的插入任务（Multitable insert），有两种形式：insert all与insert first。

准备测试环境：

1.创建表T并初始化测试数据，此表作为数据源。

create table t (x number(10), y varchar2(10));
insert into t values (1,'a');
insert into t values (2,'b');
insert into t values (3,'c');
insert into t values (4,'d');
insert into t values (5,'e');
insert into t values (6,'f');
commit;

2.创建表T1和T2，作为我们要插入数据的目标表。

SQL>create table t1 as select * from t where 0=1;
SQL>create table t2 as select * from t where 0=1;

16.4.1第一种多表插入方法INSERT ALL

SQL>insert all into t1 into t2 select * from t; （无条件insert all)

12 rows created.

这里之所以显示插入了12条数据，实际上表示在T1表中插入了6条，T2表插入了6条，一共是12条数据。（不分先后，各插各的）

SQL>rollback;
SQL> insert all when x>=3 then into t1 when x>=2 then into t2 select * from t; （有条件insert all)。

16.4.2第二种多表插入方法INSERT FIRST

1）清空表T1和T2

SQL> truncate table t1;
SQL> truncate table t2;

2）完成INSERT FIRST插入

SQL> insert first when x>=3 then into t1 when x>=2 then into t2 select * from t; (有条件insert first)

处理逻辑是这样的，首先检索T表查找X列值大于等于3的数据插入到T1表，然后将前一个查询中出现的数据排除后再查找T表，找到X列值大于等于2的数据再插入到T2表，注意INSERT FIRST的真正目的是将同样的数据只插入一次。

3）小伙伴们，一起来验证T1和T2表中被插入的数据：

SQL> select * from t1;
SQL> select * from t2;

大家结果是怎么样的呢？

the end ！！！

@jackman 共筑美好！