oracle语句

个人学习笔记

查询:

select * from tb_project_plan;

添加字段:
alter table tb_project_node add (mould_number varchar2(45));

插入新的值:
insert into tb_project_node (mould_number,id) values ('模号1',2);

修改值(原来为空值):
update tb_project_node set mould_number='模号1' where project_node='node_1';

根据id删除某行:
delete from tb_project_node where id=2;

根据相同字段查询2张表的所有字段值:
select * from tb_project_plan,tb_project_node where tb_project_plan.mould_number=tb_project_node.mould_number;

根据主键和外键查询所需要的字段的值:

select a.*,b.project_node from tb_project_plan a,tb_project_node b where a.mould_number=b.mould_number;

(根据百度结果本想用来查询主键,不太清楚干什么的,但根据查询结果没看懂有什么作用)
select * from user_cons_columns c where c.table_name='tb_project_plan';

删除原来主键约束:
alter table tb_project_plan drop constraint SYS_C0057245;

添加新的主键约束:
alter table tb_project_plan add constraint pk_tb_project_plan primary key(mould_number);

关联外键:
alter table tb_project_node add constraint fk_tb_project_node foreign key(mould_number) references tb_project_plan(mould_number);

添加日期字段格式(执行完后要提交当前事物):

insert into tb_project_node values (2,0.3,to_date('2017-3-2','yyyy-mm-dd'),to_date('2017-3-4','yyyy-mm-dd'),'node_3','模号2');

让某个字段自增:

第一步:

CREATE SEQUENCE seq_project_plan--序列号
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加，不循环
nocache;

第二步:
SELECT seq_project_plan.NEXTVAL FROM DUAL;.

第三步:

CREATE OR REPLACE TRIGGER tb_project_plan_ID_TRIGGER--触发器
BEFORE INSERT ON tb_project_plan FOR EACH ROW WHEN (new.id is null) --指定某个表,某个字段
begin
select seq_project_plan.nextval into:new.id from dual;
end;

关联表查询:

select a.*,b.project_node from tb_project_plan a,tb_project_node b where a.mould_number=b.mould_number and a.mould_number='模号1';

select a.*,b.project_node from tb_project_plan a left join tb_project_node b on a.mould_number=b.mould_number where a.mould_number='模号1';.

把一个字段的值赋值给另一个字段:

update tb_project_subtech set days = days_temp;
alter table tb_project_subtech drop(days);

字段重新命名:
alter table tb_project_subtech rename column days_temp to days;