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;