oracle——笔记——DML 数据操作语句：insert 、update 、delete

create table emp2 ( empno number(4),
                    ename varchar2(10),
                    job varchar2(9),
                    mgr number(4),
                    hiredate date,
                    sal number(7,2),
                    comm number(7,2),
                    deptno number(2) 
                 );
-----------------------------------------------------------------------------------------------------------

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, ‘SMITH‘, ‘CLERK‘, 7902, to_date(‘17-12-1980‘, ‘dd-mm-yyyy‘), 800.00, null, 20);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, ‘ALLEN‘, ‘SALESMAN‘, 7698, to_date(‘20-02-1981‘, ‘dd-mm-yyyy‘), 1600.00, 300.00, 30);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, ‘WARD‘, ‘SALESMAN‘, 7698, to_date(‘22-02-1981‘, ‘dd-mm-yyyy‘), 1250.00, 500.00, 30);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, ‘JONES‘, ‘MANAGER‘, 7839, to_date(‘02-04-1981‘, ‘dd-mm-yyyy‘), 2975.00, null, 20);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, ‘MARTIN‘, ‘SALESMAN‘, 7698, to_date(‘28-09-1981‘, ‘dd-mm-yyyy‘), 1250.00, 1400.00, 30);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, ‘BLAKE‘, ‘MANAGER‘, 7839, to_date(‘01-05-1981‘, ‘dd-mm-yyyy‘), 2850.00, null, 30);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, ‘CLARK‘, ‘MANAGER‘, 7839, to_date(‘09-06-1981‘, ‘dd-mm-yyyy‘), 2450.00, null, 10);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, ‘SCOTT‘, ‘ANALYST‘, 7566, to_date(‘19-04-1987‘, ‘dd-mm-yyyy‘), 3000.00, null, 20);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, ‘KING‘, ‘PRESIDENT‘, null, to_date(‘17-11-1981‘, ‘dd-mm-yyyy‘), 5000.00, null, 10);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, ‘TURNER‘, ‘SALESMAN‘, 7698, to_date(‘08-09-1981‘, ‘dd-mm-yyyy‘), 1500.00, 0.00, 30);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, ‘ADAMS‘, ‘CLERK‘, 7788, to_date(‘23-05-1987‘, ‘dd-mm-yyyy‘), 1100.00, null, 20);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, ‘JAMES‘, ‘CLERK‘, 7698, to_date(‘03-12-1981‘, ‘dd-mm-yyyy‘), 950.00, null, 30);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, ‘FORD‘, ‘ANALYST‘, 7566, to_date(‘03-12-1981‘, ‘dd-mm-yyyy‘), 3000.00, null, 20);

insert into emp2 (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, ‘MILLER‘, ‘CLERK‘, 7782, to_date(‘23-01-1982‘, ‘dd-mm-yyyy‘), 1300.00, null, 10);

=============================================================================================================================

create table dept2 ( deptno number(2),
                     dname varchar2(14),
                     loc varchar2(13)
                   );

----------------------------------------------------

insert into dept2 (DEPTNO, DNAME, LOC)
values (10, ‘ACCOUNTING‘, ‘NEW YORK‘);

insert into dept2 (DEPTNO, DNAME, LOC)
values (20, ‘RESEARCH‘, ‘DALLAS‘);

insert into dept2 (DEPTNO, DNAME, LOC)
values (30, ‘SALES‘, ‘CHICAGO‘);

insert into dept2 (DEPTNO, DNAME, LOC)
values (40, ‘OPERATIONS‘, ‘BOSTON‘);

============================================================

create table salgrade2 ( grade number,
                         losal number,
                         hisal number
                       );

--------------------------------------------------

insert into salgrade2 (GRADE, LOSAL, HISAL)
values (1, 700, 1200);

insert into salgrade2 (GRADE, LOSAL, HISAL)
values (2, 1201, 1400);

insert into salgrade2 (GRADE, LOSAL, HISAL)
values (3, 1401, 2000);

insert into salgrade2 (GRADE, LOSAL, HISAL)
values (4, 2001, 3000);

insert into salgrade2 (GRADE, LOSAL, HISAL)
values (5, 3001, 9999);


========================================================

select * from emp2;

select * from dept2;

select * from salgrade2;

=====================================
select * from emp2;        --employee2 员工表
select * from dept2;       --department2 部门表
select * from salgrade2;   -- salary grade2 工资等级表

-----------------------------------------------------
emp2

empno    员工编号
ename    员工姓名
job      工作/工种
mgr      manager上级编号
hiredate 入职日期
sal      salary 工资
comm     奖金/津贴
deptno   部门编号
---------------------

dept2

deptno  部门号
dname   部门名称
loc     所在地
---------------------------
salgrade2

grade                   等级
losal lowest salary     最低工资
hisal high salary       最高工资

=============================================


 
DML 数据操作语句：insert  update delete

新增：insert into

   insert into 表名 values();

   insert into 表名(列) 子查询; 

   insert into emp94(eid,ename) select 1122,‘abc‘ from dual
                                union
                                select 1123,‘abd‘ from dual
                                union
                                select 1124,‘acd‘ from dual; 

   create table emp94(eid integer,
                      ename varchar(10),
                      birth date,
                      classno number(2));

   select * from emp94;

   insert into emp94(eid,classno) values(1234,11);
   insert into emp94(eid,classno) select empno,deptno from emp;  
   







更改： update


  update 表名 set 列名=值;
  update 表名 set 列名=值 where ……;
  update 表名 set 列1=值1,列2=值2,…… where ……;
  update 表名 set 列=子查询 where ……;
  update 表名 set 列1=子查询,列2=子查询,…… where ……;
 
  update emp2 set sal = 9000,deptno=40 where job = ‘CLERK‘;
 
把emp中，员工的工资翻倍 奖金在原奖金基础上+500 ：

  update emp2 set sal=sal*2,comm=nvl(comm,0)+500;
 
把SMITH 的工资改成与KING 一样：

update emp2 set sal = (select sal from emp2 where ename = ‘KING‘)
where ename = ‘SMITH‘;
 
把ALLEN 改成 与CLARK同部门：

update emp set deptno = (select deptno from emp where ename=‘CLARK‘)
where ename = ‘ALLEN‘;

把与BLAKE 同工作的员工的上级，改成SCOTT：

  update emp set mgr = SCOTT的empno
  where job = BLAKE的job;
  
把处于平均工资以下的员工，都调到BOSTON (部门的loc)：

  update emp set deptno = (select deptno from dept where loc=‘BOSTON‘)
  where sal < 平均工资;
  
  






  
  
删除:

 delete from 表名;             删除整张表的数据
 
 delete from 表名 where ……;    删除符合where条件的数据
 
 delete from emp2 where deptno = 10;
 
 select * from emp2;











数据库事务:


 事务由一句DDL语句或一组DML语句组成；
 
 DDL事务 create alter drop truncate
 
 DML事务 insert update delete
 
 
 DML事务，以执行第一句DML语句为开始，以手动commit/rollback为结束
 
 DDL事务，语句执行结果是自动提交commit，不能rollback
 
 DML事务---锁
 
 DML语句执行之后，commit之前：
 
 1）当前用户/当前窗口能够看到语句执行结果
 
 2）其他用户窗口看不到
 
 3）结果是可以回退的(rollback)
 
 4）数据状态，加锁的状态--资源争用
 
 ---锁的处理 sysdba
 update emp set sal = 9000 where deptno =10;
 
 select * from emp;