OCP学习笔记-007 SQL语言之二：DML
2. DML – Data Manipulation Language
数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括insert、delete、udpate和select等。(增添改查）
事务：A transaction consists of a collection of DML statements that form a logical unit of work.
DML的主要语句如下：
| 命令 | 功能 |
|---|---|
| insert | 在表中添加新行 |
| update | 修改已存在的行 |
| delete | 删除已存在的行 |
| truncate | 删除表中所有的行 |
| commit | 让修改永久生效 |
| savepoint | 回滚到保存的一个点 |
| rollback | 撤销数据修改 |
| for update | 在使用select查询某行数据时，跟这个语句会将行加锁 |
2.1 Insert
语法结构：
INSERT INTO table [(column [,column ...)]
VALUES (value [, value ...]);
第一种方式：根据表的列对应名称，直接一一填入对应的数据
09:35:11 SYS@orcl>insert into emp values (7777,'BRUCE','TECH',7839,'30-JUN-85',2500,null,10);
1 row created.
09:37:59 SYS@orcl>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- -------------------- ---------- --------- ---------- ---------- ----------
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-MAR-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 ANALIST 7566 19-MAR-87 3000 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
7777 BRUCE TECH 7839 30-JUN-85 2500 10
15 rows selected.
第二种方式：直接指明列名然后给值，不需要的列可以直接不用赋值，允许为空的列都可以不用赋值
09:38:09 SYS@orcl>insert into emp (empno,ename,job,sal) values (7979,'LUCY','CLERK',1500);
1 row created.
09:40:55 SYS@orcl>select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- -------------------- ---------- --------- ---------- ---------- ----------
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-MAR-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 ANALIST 7566 19-MAR-87 3000 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 0
