1、创建一个表，同时插入一些数据
create table empTemp as select * from emp where sal>1000;
2、清空表数据
truncate table empTemp;
3、删除表
drop table empTemp;
4、增加表的字段
alter table empTemp add tempName varchar2(50);
5、修改字段
alter table empTemp tempName varchar2(10);
alter table empTemp column rename tempName to tempName2;
6、删除字段
alter tabel empTemp drop column tempName2;
7、重命名表
rename empTemp to empTemp_Table;
8、insert 语句
insert into empTemp(id,name) select id,name from emp where age>18;
9、update语句
update empTemp set(name,age)=(select name,age from emp where id='1');
10、牛X的merge,联想到了hibernate
create tabel temp_t1(id varchar2(100) not NULL PRIMARY key,name varchar2(50) NOT Null);
create tabel temp_t2(id varchar2(100) not NULL PRIMARY key,name varchar2(50) NOT Null);
insert into temp_t1(id,name) values('1','test1');
insert into temp_t1(id,name) values('2','test2');
merge into temp_t2
using temp_t1
on(temp_t1.id=temp_t2.id)
when matched then
update set temp_t2.name=temp_t1.name
when not matched then
insert(id name) values(temp_t1.id,temp_t1.name);
二、约束
PRIMARY key:主键约束，不能有重复值，不能为空
not null:非空约束
unique:不能重复，但是可以有多个null值
check:插入值的约束 age number check(age between 0 and 18 ),
三、外键
fid NUMBER REFERENCES jf_applymain(fid) ON DELETE cascade,
或
CONSTRAINT temp_fid_fk FOREIGN KEY(fid) REFERENCES jf_applymain(fid);
四、已经建立好的表如何使用如上约束，stu(id,name);
--增加主键
ALTER TABLE stu ADD CONSTRAINT stu_id_pk PRIMARY KEY(ID);
--增加唯一约束
ALTER TABLE stu ADD CONSTRAINT stu_name_unique unique(NAME);
--增加其他约束
ALTER TABLE stu ADD CONSTRAINT stu_age_check check(age BETWEEN 0 AND 18);
--增加主外键约束，带练级删除
ALTER TABLE stu ADD CONSTRAINT stu_id_pk PRIMARY KEY(ID) REFERENCES jf_applymain(fid) ON DELETE CASCADE;
--删除约束
ALTER TABLE stu DROP CONSTRAINT stu_id_pk;
ALTER TABLE stu DROP CONSTRAINT stu_name_unique;
--启用约束
ALTER TABLE stu ENABLE CONSTRAINT stu_name_unique;
--禁用约束
ALTER TABLE stu DISABLE CONSTRAINT stu_name_unique;