oracle 笔记 II 之DML:数据操作语言

 DML:Data Manipulation Language 数据操作语言
包括：CRUD

1. insert语句
(1) 从其它表中复制数据,实现方法:在insert 语句中加入查询语句

insert into sales_reps(id,name,salary,commission_pct)  select employee_id,last_name,salary,commission_pct
from employees where job_id like '%rep';

(2) update中使用子查询
 update employees set job_id =  (select job_id from employees where employee_id = 205),
                               salary =  (select salary from employees where employee_id = 205)
           where employee_id = 114;

   如：更新114号员工的工作和工资使其与205号员工相同
   update employees set job_id =  (select job_id from employees where employee_id = 205),
      salary = (select salary from employees where employee_id = 205)
      where employee_id = 114

   再看一个问题，仔细体会解决步骤：
    更改 108 员工的信息: 使其 工资变为所在部门中的最高工资，job变为公司中平均工资最低的job
     分析：
 第1 步 首先查询 108 所在部门的最高工资是多少
    select max(salary) from employees where department_id =
  ( select department_id from employees where employee_id = 108)
 第 2 步 查询公司中平均工资最低的 job_id
     select job_id from employees group by job_id having avg(salary) =
     (select min(avg_sal) from(select avg(salary) avg_sal from employees  group by job_id) )
 第 3 步 实现更新
          update employees set salary = (  select max(salary) from employees 
             where department_id = (select department_id from employees where employee_id = 108),
             job_id = ( select job_id from employees group by job_id
             having avg(salary) =(select min(avg_sal) from(select avg(salary)avg_sql from employees
             group by job_id ) ) )
   where job_id = 108  

 

(3)在delete 中使用子查询
  eg: 删除 108 号员工所在部门的工资最低的员工
     delete from employees where salary =(select min(salary) from employees where
  department_id = (select department_id from employees where employee_id = 108 ) )