Chapter 12 数据的维护

Chapter 12 数据的维护

数据维护包括了DML (Data Manipulating Language) 数据操作语言和事务控制(Transaction Control).

create table emp_dml
as
select *
from emp;

select *
from emp_dml;

create table dept_dml
as
select *
from dept;

select * from dept_dml;

insert into dept_dml (deptno, dname, loc) values (66,'美容','煤球胡同');


select * from dept_dml;

insert into dept_dml (deptno, dname, loc) values (77,'订货','浪莎市');

insert into dept_dml (deptno, dname, loc) values (88,'','牛街');

insert into dept_dml (deptno, dname, loc) values (44,null,'牛街');

insert into dept_dml (deptno, loc) values (33,'公关');

commit;

insert into emp_dml (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values(7800,'童铁蛋','保安', 7900, sysdate, 666,77,66);

select * from emp_dml where empno = 7800;


commit;

create table sales (code, name,salary,commission)
as
select empno,ename,sal,comm
from emp;

select * from sales;

truncate table sales;

create table sales (code, name,salary,commission)
as
select empno,ename,sal,comm
from emp
where job like 'SALE%';


create table sales (code, name,salary,commission)
as
select empno,ename,sal,comm
from emp;


insert into emp_dml (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values(&id,'&name','&job', 7900, to_date('&hiredate','YYYY MM DD'), 666,77,66);

accept id prompt '请输入员工的工号:'
accept name prompt '请输入员工的名字:'
accept job prompt '请输入员工的职位:'
accept hiredate prompt '请输入员工的雇佣日期(如: 2001 03 08):'

insert into emp_dml (empno, ename, job, mgr, hiredate, sal, comm, deptno)
values(&id,'&name','&job', 7900, to_date('&hiredate','YYYY MM DD'), 666,77,66);

update emp_dml
set sal = sal + 0.9;

select empno,ename,sal,comm
from emp_dml;


update emp_dml
set sal = 1000
where ename = '童铁蛋';

commit;

select * from salgrade;

update emp_dml
set sal = (select losal
             from salgrade
             where grade = 1)
where sal < (select losal
             from salgrade
             where grade = 1)

commit;

select * from emp_dml;

update emp_dml
set (job, sal) = (select job, sal
                  from emp_dml
                  where empno = 7369)
where job = '保安';

commit;

select * from emp_dml;

select *
from emp_dml
where (job='MANAGER') or (job = 'SALESMAN')
order by sal;

delete from emp_dml
where (job='MANAGER' and sal > 2500)
or (job = 'SALESMAN' and sal > 1300)

commit;

delete from emp_dml
where (job='MANAGER' and sal < 2500)
or (job = 'SALESMAN' and sal > 1300)

delete emp_dml;


delete from emp_dml
where deptno =
(
      select deptno
      from dept_dml
      where dname = upper('美容')
);


12.15 什么是Oracle 数据库的事务（Transactions）


12.16
update emp_dml
set deptno = 88
where deptno = 66;

select empno, ename, deptno
from emp_dml
where deptno > 30;

set autocommit on;