12/13 sql

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;

delete from insertTest; -- 모든 행 삭제

rollback;

---------------------------------------------------------------------
update tb_subway_statn_tmp set ln_nm='녹색선', statn_nm='강남스타일'
where subway_statn_no='000032';

select * from tb_subway_statn_tmp;

rollback;

commit;

delete from tb_subway_statn_tmp 
where subway_statn_no='000032';

-------------------------------

select induty_cl_nm, nvl(upper_induty_cl_cd,'없음') as cd
from tb_induty_cl
where induty_cl_se_cd ='ICS001';


-- distinct는 중복된 행을 제거. 모든 컬럼의 값이 같아야 중복된 것으로 봄
select distinct induty_cl_se_cd
from tb_induty_cl;

select count(*)
from tb_popltn;

select distinct popltn_se_cd, agrde_se_cd
from tb_popltn;

select induty_cl_nm as "업종코드 분류명", 
nvl(upper_induty_cl_cd,'없음') as "상위업종 분류코드"
from tb_induty_cl
where induty_cl_se_cd ='ICS001';

select 'abc' || 'x' || 'y' as result from dual; -- dual은 가상의 테이블

select subway_statn_no || '-' || statn_nm || '(' || ln_nm || ')' as "지하철역번호(노선명)"
from tb_subway_statn;

select 1+1 from dual;

select sysdate from dual;

-- order by 정렬
select * from member order by name asc; -- asc 오름차순

select * from member order by name desc; -- desc 내림차순

select * from tb_adres_cl order by adres_cl_nm desc;

-- 먼저 adres_cl_nm으로 정렬한 후, 같은 값들은 다시 adres_cl_se_cd로 정렬
select * from tb_adres_cl order by adres_cl_nm, adres_cl_se_cd desc;

-- and , or
select * from tb_adres_cl where adres_cl_cd < '1000' and adres_cl_se_cd='ACS002'; -- 두조건 모두 만족해야 검색됨
select * from tb_adres_cl where adres_cl_cd < '1000' or adres_cl_se_cd='ACS002'; -- 두조건 중 하나만 만족하면 검색됨 

-- not
select * from tb_adres_cl where not adres_cl_cd='11'; -- not은 부정연산자. '11'번이 아닌것만 검색
select * from tb_adres_cl where adres_cl_cd<>'11'; -- <> 같지않다
select * from tb_adres_cl where adres_cl_cd!='11'; -- != 같지않다. ms-sqlserver는 불가

select * from tb_adres_cl where adres_cl_cd>='11'; -- >= 크거나 같다. <= 작거나 같다.

-- max(), min()
select max(adres_cl_cd) from tb_adres_cl; -- max()는 최대값을 구함
select min(adres_cl_cd) from tb_adres_cl; -- min()는 최소값을 구함

-- count(), sum(), avg()
select count(*) from tb_adres_cl; -- cout(*)는 모든 행의 수를 구함. 20646
select count(upper_adres_cl_cd) from tb_adres_cl; -- count(컬럼명)은 컬럼의 값이 null이 아닌 것만 카운트. 20629
select sum(popltn_cnt) from tb_popltn; --sum()은 합계를 구함. null값을 제외하고 합계를 구함 
select avg(popltn_cnt) from tb_popltn; --avg()은 평균을 구함. null값은 제외하고 평균을 구함

-- drop table goods; 
create table goods(
    name varchar2(50) primary key,
    price number null
);
insert into goods values('노트북',1000000);
insert into goods values('데스크탑',700000);
insert into goods values('아이폰15',null);
commit;

select * from goods;

select max(price) from goods;
select min(price) from goods;
select count(*) from goods; -- 전체행의 수
select count(price) from goods; -- price의 값이 null인 것은 제외. 
select sum(price) from goods;
select avg(price) from goods; -- price의 값이 null인 것은 제외. 분모가 2
select avg(nvl(price,0)) from goods; -- nvl를 이용해서 price가 null인 경우 0으로 바꿔서 평균계산. 분모가 3

-- like . 키워드 검색시 사용
select * from tb_adres_cl where adres_cl_nm like '%잠실%'; -- '잠실'이 들어있는 모든 값을 검색
select * from tb_adres_cl where adres_cl_nm like '잠실%'; -- '잠실'로 시작하는 값을 검색
select * from tb_adres_cl where adres_cl_nm like '%잠실'; -- '잠실'로 끝나는 값을 검색

-- in
select * from tb_adres_cl where upper_adres_cl_cd in ('11','26'); -- 괄호안의 값을 모두 검색

-- between
select * from tb_popltn where popltn_cnt between 50 and 53 -- between 'a' and 'b'. 'a' ~ 'b'까지 검색
order by popltn_cnt;

-- inner join (기본조인). ANSI_SQL 표준문법
select t1.adres_cl_nm, t2.adres_cl_se_nm
from tb_adres_cl t1 inner join tb_adres_cl_se t2
on t1.adres_cl_se_cd=t2.adres_cl_se_cd;

-- inner join (기본조인). 오라클문법(?). 사실상 표준(?)
select t1.adres_cl_nm, t2.adres_cl_se_nm
from tb_adres_cl t1 , tb_adres_cl_se t2
where t1.adres_cl_se_cd=t2.adres_cl_se_cd;

select member.id, member.name, board.title
from member,board
where member.id=board.id;

-- table 3개 사용 조인. 오라클문법
select t1.statn_nm,t2.begin_time,t2.end_time,t3.tk_gff_se_nm
from TB_SUBWAY_STATN t1,
TB_SUBWAY_STATN_TK_GFF t2,
TB_TK_GFF_SE t3
where t1.SUBWAY_STATN_NO =t2.SUBWAY_STATN_NO
and t2.TK_GFF_SE_CD =t3.TK_GFF_SE_CD;
-- table 3개 사용 조인. ANSI-SQL 표준문법
select t1.statn_nm,t2.begin_time,t2.end_time,t3.tk_gff_se_nm
from TB_SUBWAY_STATN t1 inner join TB_SUBWAY_STATN_TK_GFF t2
on t1.SUBWAY_STATN_NO =t2.SUBWAY_STATN_NO
inner join TB_TK_GFF_SE t3
on t2.TK_GFF_SE_CD =t3.TK_GFF_SE_CD;

select * from member;
select * from board;

insert into member values('wg','왕건');
commit;

select board.bno, member.name, board.title
from board,member
where board.id=member.id;

-- right outer join. 기준(종류가 많은)이 되는 테이블이 오른쪽에 있을 때
select board.bno,member.id, member.name, board.title
from board right outer join member
on board.id=member.id;

-- left outer join. 기준(종류가 많은)이 되는 테이블이 왼쪽에 있을 때
select board.bno,member.id, member.name, board.title
from member left outer join board
on board.id=member.id;

-- full outer join. 양쪽 테이블 모두 모든 종류 검색. 여기서는 left outer join과 같은 결과.
select board.bno,member.id, member.name, board.title
from member full outer join board
on board.id=member.id;