create table stu_user (id varchar2(36),name varchar2(36),sale number(10,3));
create table testFunc (id varchar2(36),name varchar2(36),sale number(10,3));
insert into testFunc (id,name,sale) values ('1','jack',3400);
insert into testFunc (id,name,sale) values ('2','jack',2400);
insert into testFunc (id,name,sale) values ('3','jack',1400);
select * from stu_user;
select * from testFunc;
-- 新增字段
alter table testFunc add (add1 varchar2(10));
-- 更新字段
update testFunc set add1 = null;
-- 修改字段
alter table testFunc modify(add1 varchar(2));
-- 重命名字段
alter table testFunc rename column add1 to add2;
-- 删除字段
alter table testFunc drop column add2
-- 删除表
drop table stu_user;
-- 删除表数据
truncate table stu_user;
-- 删除指定记录
delete stu_user where id = '3';
-- insert into 从一张表导入到另一张表
insert into stu_user select * from testFunc;
-- update select
update stu_user s set sale = (select sale from testFunc t where t.id = s.id);
-- minus 两个查询进行数据比较,字段名要一致
select * from testFunc
minus
select * from stu_user;
-- merge into 要保证后一个查询有数据 ,
MERGE INTO stu_user T1
USING (SELECT * FROM testFunc) T2 on (T1.ID=T2.ID)-- 是否满足on的条件,若满足,则执行1,不满足则执行2
WHEN MATCHED THEN UPDATE SET T1.name=t2.name --1.更新、删
WHEN NOT MATCHED THEN INSERT (T1.ID, T1.name,t1.sale) VALUES (t2.id,t2.name,t2.sale);--2.更新、删除、插入