数据处理

使用   DML语言：

                           向表中插入数据  insert into...values();     insert  into..select...from...where...

                           更新表中数据      update ...set...where...

                           从表中删除数据  delete from...where

可以 rollback

控制事务

事务：   由完成若干项工作的DML语句组成的

--插入数据：

--- 顺序一一对应的

insert into emp2 
values(1001,'AA',sysdate,10000);

insert into emp2
values(1003,'CC',to_date('1993-12-23','yyyy-mm-dd'),null);

insert into emp2(last_name,employee_id, hire_date)
values('EE',1004,sysdate);

注意： 空值需要用null来代替

            有非空约束的一定要附上值

--  从其他表中拷贝数据

insert into emp2(employee_id, hire_date ,last_name,salary)
select employee_id,hire_date,last_name,salary 
from employees
where department_id = 80;

-- 更新表中数据  update

update emp2 
set  salary = 12000
where employee_id = 179;

注意：如果省略where所有的数据都将被更新

 

-- 更新114号员工的工作和工资使其与205号员工相同（后面的where一定不能忘了）

update employees1 
set job_id=(select job_id 
            from  employees1
            where employee_id = 205
            ),
    salary = (select salary 
            from  employees1
            where employee_id = 205)
where employee_id = 114;

EMPLOYEE_ID JOB_ID         SALARY
----------- ---------- ----------
        114 AC_MGR          12000 
        205 AC_MGR          12000 

-- 删除数据 delete from

-- 从employee1表中删除部门名称中Public 字段的部门id

delete from employees1
where department_id = (
                    select department_id 
                    from departments
                    where department_name like '%Public%'
);

事务：一组逻辑操作单元使数据从一种状态变换到另一种状态

数据库事务由一下部门组成：一个或多个DML语句

                                               一个DDL语句

                                               一个DCL语句

DCL:

rollback回滚到最近的一次commit后

savepoint

rollback to savepoint

提交或回滚前的数据状态:

其他用户不能看到当前用户所做的改变，直到当前用户结束任务

DML语句所涉及到的行被锁定，其他用户不能操作

练习：

-- 1.更改 108 员工的信息: 使其工资变为所在部门中的最高工资, job 变为公司中平均工资最低的 job

-- 有个avg(salary) 组函数得用 group by job_id

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(salary))
                                       from employees
                                       group by job_id)   
     )
where employee_id = 108;

--2.删除 108 号员工所在部门中工资最低的那个员工.

delete from employees
where salary  = (
                select min(salary)
                from employees
                where department_id = (
                                     select department_id
                                     from employees
                                     where employee_id = 108
                                  )                           
       )
 and    department_id = (
                      select department_id
                      from employees
                      where employee_id = 108
                    );

优化：

delete from employees e
where salary  = (
                select min(salary)
                from employees
                where department_id = e.department_id                           
       )
 and   department_id = (
                      select department_id
                      from employees
                      where employee_id = 108
                    );