create table tb_rn_tmp(
rn_cd varchar2(12) not null,
rn varchar2(15) not null
);
create table tb_subway_statn_tmp(
subway_statn_no char(6) not null,
ln_nm varchar2(50) not null,
statn_nm varchar2(50) not null,
constraint pk_tb_statn_tmp primary key(subway_statn_no)
);
create table tb_subway_statn_tk_gff_tmp(
subway_statn_no char(6) not null,
std_ym char(6) not null,
begin_time char(4) not null,
end_time char(4) not null,
tk_gff_se_cd varchar2(6) not null,
tk_gff_cnt number(15) not null,
constraint pk_tb_subway_statn_tk_gff_tmp primary key
(subway_statn_no,std_ym,begin_time,end_time,tk_gff_se_cd)
);
create table member(
id varchar2(10) primary key,
name varchar2(50) not null
);
insert into member values('hkd','홍길동');
select * from member;
insert into member values('hkt','홍길돈');
rollback; --취소
commit; -- 확정
create table board(
bno number primary key,
-- id varchar2(10) not null references member(id),
id varchar2(10) not null,
title varchar(100) not null,
constraint fk_board_id
foreign key(id)
references member(id)
);
insert into board values(1,'hkd','sql이 뭔가요?');
select * from board;
insert into board values(2, 'hkt', 'RDB가 뭔가요?');
commit;
---------------------------------------------------------
-- 이미 생성된 테이블에 참조키 추가
alter table tb_subway_statn_tk_gff_tmp
add constraint fk_tb_subway_statn_tk_gff_tmp1
foreign key (subway_statn_no)
references tb_subway_statn_tmp(subway_statn_no);
alter table tb_subway_statn_tmp add (oper_yn char(1)); -- 컬럼추가
alter table tb_subway_statn_tmp modify (oper_yn number(1)); -- 컬럼수정
alter table tb_subway_statn_tmp drop column oper_yn; --컬럼삭제
----------------------------------------------------------
select * from board;
truncate table board; -- 데이터 영구삭제. rollback안됨
rollback;
drop table board;
select * from board;
-- insert , update, delete 후 commit하지 않고
-- create 문을 실행해도 자동으로 commit 됩니다. => 주의
-- create 문 실행전에 commit or rollback 먼저 실행.
insert into tb_subway_statn_tmp( subway_statn_no, ln_nm, statn_nm) values('000032','2호선','강남');
insert into tb_subway_statn_tmp values('000033','2호선','서울대입구'); -- 컬럼생략하면 모든 컬럼을 다 쓴것과 같다.
commit;
select * from tb_subway_statn_tmp;
create table insertTest(
name varchar2(50) primary key,
salary number null
);
insert into insertTest values('홍길동', 5000000); -- 컬럼명을 생략하면 모든 컬럼을 다 적은 것과 같음.
insert into insertTest values('이순신', null); -- salary에 null 입력
insert into insertTest(name) values('왕건'); -- salary에 null이 자동으로 입력됨
commit;
select * from insertTest;
