oracle更新(insert delete update)
①insert
create table people(
id number primary key,
name varchar2(10) not null,
status varchar2(3)
);
create sequence people_seq minvalue 1 maxvalue 99999 start with 1 increment by 1;
--向people表插入数据
insert into people values(people_seq.nextval,'张三','ACT');
insert into people values(people_seq.nextval,'李四','ACT');
select * from people;
--使用子查询向people表插入数据
insert into people select employee_id,employee_name,status from t_employee where employee_id>2;
alter table people modify status default 'ACT'
insert into people(id,name,status) values(people_seq.nextval,'测试默认',default);
②delete(oracle允许delete时省略from关键字)
delete people where id=11;
③update
④truncate 截断数据
特点:
1.truncate 属于DDL范畴,delete 属于DML范畴
2.truncate是删除表中所有的数据,delete可以根据条件删除一条或者多条记录
3.truncate截断后不能rollback,delete后可以回滚
适用场景:清空表中测试数据时。
--示例
create table people_emp as select * from people;
select * from people_emp;
truncate table people;
数据提交与回滚
回滚:数据库将DML执行的语句存储在回滚段中
1.执行DML操作后,手动rollback,回滚事务
2.事务执行失败后,自动rollback
注意:commit后,DML操作无法再回滚。
提交
1.手动commit
2.
select * from people;
insert into people values(people_seq.nextval,'测试回滚',default);
①insert
create table people(
id number primary key,
name varchar2(10) not null,
status varchar2(3)
);
create sequence people_seq minvalue 1 maxvalue 99999 start with 1 increment by 1;
--向people表插入数据
insert into people values(people_seq.nextval,'张三','ACT');
insert into people values(people_seq.nextval,'李四','ACT');
select * from people;
--使用子查询向people表插入数据
insert into people select employee_id,employee_name,status from t_employee where employee_id>2;
alter table people modify status default 'ACT'
insert into people(id,name,status) values(people_seq.nextval,'测试默认',default);
②delete(oracle允许delete时省略from关键字)
delete people where id=11;
③update
④truncate 截断数据
特点:
1.truncate 属于DDL范畴,delete 属于DML范畴
2.truncate是删除表中所有的数据,delete可以根据条件删除一条或者多条记录
3.truncate截断后不能rollback,delete后可以回滚
适用场景:清空表中测试数据时。
--示例
create table people_emp as select * from people;
select * from people_emp;
truncate table people;
数据提交与回滚
回滚:数据库将DML执行的语句存储在回滚段中
1.执行DML操作后,手动rollback,回滚事务
2.事务执行失败后,自动rollback
注意:commit后,DML操作无法再回滚。
提交
1.手动commit
2.
select * from people;
insert into people values(people_seq.nextval,'测试回滚',default);