Oralce-数据增删改及事务和point

插入数据

-- 单条插入
INSERT INTO emp(employee_id,last_name,hire_date,salary)
VALUES(200,'LQF',SYSDATE,10000)
-- 从已有表中筛选数据插入
INSERT INTO emp(employee_id,last_name,hire_date,salary)
SELECT employee_id,last_name,hire_date,salary
FROM employees e
WHERE e.department_id = 80

更新

UPDATE employees e
SET job_id = (
             SELECT e1.job_id
             FROM employees e1
             WHERE e1.employee_id = 205
             ),
e.salary = (
             SELECT e2.salary
             FROM employees e2
             WHERE e2.employee_id = 205 
           )
WHERE e.employee_id = 206;

事务

commit：提交

rollback：回滚

savepoint：保存当前数据库处于点

-- 删除一条记录
delete from employees where employee_id = 206;
-- 保存当前的状态A
savepoint A;
--再删除一条记录
delete from employees where employee_id = 202;
-- 保存当前的状态B
savepoint B;
--当回滚到点A时记录202是存在的
rollback to savepoint A;
--rollback会直接回到最初的202和206都存在的状态
rollback;