SQL语句-DML语句的使用

数据操纵语言( DML )是SQL的核心部分。当你想要增加,更新或者删除数据库中的数据的时候,你可以通过执行DML语句来实现。一组DML语句的集合组成了一个逻辑工作单元称为事务。

想想银行业的数据库,当银行顾客将金钱从一个储蓄账户转移到一个核算帐户,该事务可能由三个独立的操作组成:减少储蓄帐户的金额,增加核算帐户的金额,在事务日志中记录该事务。Oracle服务器必须保证所有的这些SQL语句执行后帐户间数目的平衡。当某些事情阻止了事务中一个语句的执行,那么事务中其他语句必须被撤销。

DML的分类

  • 增 insert into
  • 删 delete from (对比truncate)
  • 改 update
  • 合并行 merge
  • 控制事务处理

insert

使用 INSERT 语句可在表中添加新行:

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

update

使用 UPDATE 语句修改表中的现有值:

UPDATE table
SET column = value [, column = value, ...]
[WHERE condition];

如果需要,可以一次更新多行。

delete

使用 DELETE 语句可以从表中删除现有行:

DELETE [FROM] table
[WHERE condition];

truncate

TRUNCATE 语句

  • 从表中删除所有行,使表为空并保留表结构不变
  • 是数据定义语言 (DDL) 语句而不是 DML 语句,无法轻易将其取消
  • 语法:

TRUNCATE TABLE table_name;

事务

  • commit 提交
  • rollback 回滚到事务开始之前
  • savepoint A 保存状态
  • rollback to savepoint A 回滚到状态A

操作实践

1. 向emp表中添加新行:

对所有列赋值

desc emp
insert into emp values (1,'Tom','CLERK',7698,to_date('yyyy-mm-dd','2016-08-25'),1450,null,30);

对指定的列赋值

insert into emp (empno,ename) values (2,'Jerry');

sql脚本inst.sql

insert into dept values (&deptno,upper('&dname'),upper('&loc'));

2. 子查询拷贝行

emp表中有奖金的员工存放在新创建的表bonus中
insert into bonus select ENAME,JOB,SAL,COMM from emp where comm>0;

3. 修改表中数据

smith工资涨百分之10
update emp set sal=sal*1.1 where ename='SMITH';

4. 向虚拟表中插入行

insert into (select empno,ename,deptno from emp where deptno=10)
values (2,'Alvin',20);

通过sql脚本来执行`ins10.sql`


```sql
insert into (select * from emp where deptno=10 with check option)
values (&empno,'&ename','&job',&mgr,'&hiredate',&sal,&comm,&deptno);

5. with check option 选项

设置with check option选项
SQL> insert into (select * from emp where deptno=10 with check option) values (901,'booboo2','dba',7782,sysdate,7000,8000,10);

1 row created.

SQL> select * from emp where deptno=10;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
900 booboo dba 7782 31-JUL-17 7000 8000 10
901 booboo2 dba 7782 31-JUL-17 7000 8000 10
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10

SQL> insert into (select * from emp where deptno=10 with check option) values (901,'booboo2','dba',7782,sysdate,7000,8000,20);
insert into (select * from emp where deptno=10 with check option) values (901,'booboo2','dba',7782,sysdate,7000,8000,20)
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

如果设置了该选项,那么插入的新记录,必须和where后面的匹配,比如案例中,where指定了部门为10,那么插入的也必须为10部门的记录。

6. merge合并行

  • 数据源:emp
  • 目标表:copy_emp
create table copy_emp as select * from emp where deptno=10;
  • matched–> 目标表中的主键值在数据源中被找到
  • not matched –> 数据源中主键在目标表中不存在
merge into copy_emp c
using emp e
on (c.empno=e.empno)
when matched then
update set
c.ename=e.ename,
c.job=e.job,
c.mgr=e.mgr,
c.hiredate=e.hiredate,
c.sal=e.sal,
c.comm=e.comm,
c.deptno=e.deptno
when not matched then
insert values
(e.empno,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.comm,
e.deptno);

操作记录

SQL> create table copy_emp as select * from emp where deptno=10;

Table created.

SQL> merge into copy_emp c
2 using emp e
3 on (c.empno=e.empno)
when matched then
update set
c.ename=e.ename,
c.job=e.job,
c.mgr=e.mgr,
c.hiredate=e.hiredate,
c.sal=e.sal,
c.comm=e.comm,
c.deptno=e.deptno
when not matched then
insert values
(e.empno,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.comm,
22 e.deptno);

16 rows merged.

SQL> select * from copy_emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
900 booboo dba 7782 31-JUL-17 7000 8000 10
901 booboo2 dba 7782 31-JUL-17 7000 8000 10
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30

16 rows selected.

源表进行了修改

SQL> insert into emp (empno,ename,sal) values (1,'Alvin',1400);

1 row created.

SQL> update emp set sal=1111 where empno=7788;

1 row updated.

目标表与源表不一致了

SQL> select * from copy_emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
900 booboo dba 7782 31-JUL-17 7000 8000 10
901 booboo2 dba 7782 31-JUL-17 7000 8000 10
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30

16 rows selected.

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
900 booboo dba 7782 31-JUL-17 7000 8000 10
901 booboo2 dba 7782 31-JUL-17 7000 8000 10
1 Alvin 1400
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 1111 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10


17 rows selected.

再次合并

merge into copy_emp c
using emp e
on (c.empno=e.empno)
when matched then
update set
c.ename=e.ename,
c.job=e.job,
c.mgr=e.mgr,
c.hiredate=e.hiredate,
c.sal=e.sal,
c.comm=e.comm,
c.deptno=e.deptno
when not matched then
insert values
(e.empno,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.comm,
e.deptno);

17 rows merged.

合并后与源表一致

SQL> select * from copy_emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
900 booboo dba 7782 31-JUL-17 7000 8000 10
901 booboo2 dba 7782 31-JUL-17 7000 8000 10
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7788 SCOTT ANALYST 7566 19-APR-87 1111 20
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
1 Alvin 1400

17 rows selected.

这是数据仓库的一些用法

修改目标表后再此合并

SQL> update copy_emp set sal=1499 where empno=1;

1 row updated.

SQL> select * from emp where empno=1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1 Alvin 1400

SQL> select * from copy_emp where empno=1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1 Alvin 1499

merge into copy_emp c
using emp e
on (c.empno=e.empno)
when matched then
update set
c.ename=e.ename,
c.job=e.job,
c.mgr=e.mgr,
c.hiredate=e.hiredate,
c.sal=e.sal,
c.comm=e.comm,
c.deptno=e.deptno
when not matched then
insert values
(e.empno,
e.ename,
e.job,
e.mgr,
e.hiredate,
e.sal,
e.comm,
e.deptno);

17 rows merged.

SQL> select * from emp where empno=1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1 Alvin 1400

SQL> select * from copy_emp where empno=1;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1 Alvin 1400