(尚硅谷) Orcale 数据处理DML(INSERT/DELETE/UPDATE) DCL :COMMOT ROLLBACK SAVE

<span style="font-size:18px;">第8节数据处理DML(INSERT/DELETE/UPDATE) DCL :COMMOT ROLLBACK SAVEPOINT
--1添加 inert
create table emp (
      id number(10),
      name varchar2(20),
      email varchar2(20),
      salary number(8,2)
)
select * from emp
1)一条一条的添加(insert into ..values(..))
insert into emp 
values(1001,'陈军','chenj@126.com',8000);

insert into emp 
values(1002,'孙兆国',null ,9000);

insert into emp (name , id , salary , email )
values ('赵龙' ,1003 ,9000 ,'zaolomh@qq.com' );

--添加时，为指定列时 ， 相应的列值为null
insert into emp(name , id )
values ('高强',1004)

create table emp1
as 
select employee_id id, last_name name ,department_id dep_id
from employees
where 1=2

select * from emp1
--不能添加成功因为emp的name有not null 约束
insert into emp1 (id , dept_id )
values (1001 , 10);

select * from employees
where 1=2

--基于现有的表， 导入数据'
select * from emp1
insert into emp1
select employee_id , last_name , department_id 
from employees
where salary < 10000;

--根据脚本添加数据
select * from emp

insert into emp
values(&id,'&name','&email',&salary);

--2修改update 表明
commit;

rollback;

select * from emp

update emp
set email = 'suny@126.com';

update emp 
set email = 'suny@122.com'
where id =1001;

update emp
set salary = salary +100;

--3删除delet from 表明where ..
delete emp
--where id = 1004
where name = '高强';
select * from emp;

delete from emp;

rollback

--练习
--更新 114号员工的工作和工资使其与205号员工相同。
select employee_id , job_id , salary 
from employees
where employee_id IN(114 , 205)

create table emp2
as 
select * from employees

select * from emp2

update emp2
set job_id = (
             select job_id 
             from emp2
             where employee_id = 205
           ), salary = (
                          select salary
                          from emp2
                          where employee_id = 105
                       )
where employee_id = 114

--调整 (与employee_id 为200的员工job_id相同的员工)
--的department_id为  (employee_id为100的员工的department_id)。
update emp2
set department_id = (
                    select department_id
                    from emp2
                    where employee_id = 200
                    )
where job_id = (
               select job_id 
               from employees
               where employee_id = 200
             )

----在DML操作中的一些注意点
update emp2
set department_id = 600
where employee_id = 110

select * from departments

select * from departments

select employee_id , manager_id
from employees

delete from emp2 
where employee_id = 108

----DCL:commit rollback savepoint
create table emp3
as
select employee_id , last_name , hire_date 
from employees
where department_id IN (80,90)

select * from emp3

delete from emp3
where employee_id = 100

commit;

delete from emp3
where employee_id = 101;

savepoint A;

delete from emp3
where employee_id = 102

savepoint B;

delete from emp3
where employee_id = 145;

rollback to savepoint B;

rollback;
</span>

--更改 108 员工的信息: 
--使其工资变为所在部门中的最高工资,
-- job 变为公司中平均工资最低的 job
update emp2
set salary = (
              select employee_id
              from employees
              where salary = (
                              select min (salary)
                              from employees
                              where department_id = 100
                             )
              ) , job_id = (
                           select job_id 
                           from employees
                           group by job_id 
                           having avg (salary) = (
                                                 select min(avg(salary))
                                                 from employees
                                                 group by job_id
                                                 )
                           )
where employee_id = 108


-- 删除 108 号员工所在部门中工资最低的那个员工.
--方法一
delete from emp2
where employee_id IN (
                     select employee_id
                     from employees
                     where salary = (
                                     select min(salary)
                                     from employees
                                     where department_id  = (
                                                              select department_id 
                                                              from employees
                                                              where employee_id = 100
                                                            )
                                    )
                     )
                     and department_id = (
                                         select department_id
                                         from employees
                                         where employee_id = 100
                                         )

--方法二
delete from emp2
where department_id = (
                      select department_id
                      from employees
                      where employee_id = 100
                      )
and salary = (
             select min(salary)
             from employees
             where department_id = (
                                 select department_id 
                                 from employees
                                 where employee_id = 100
                                   )
             )