12/14 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. ANSI-SQL. 기준(종류가 많은)이 되는 테이블이 오른쪽에 있을 때. 
select board.bno,member.id, member.name, board.title
from board right outer join member
on board.id=member.id;
-- right outer join. 오라클문법
select board.bno,member.id, member.name, board.title
from board, member
where board.id(+)=member.id;

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

-- full outer join.ANSI-SQL 양쪽 테이블 모두 모든 종류 검색.여기서는 left outer join과 같은 결과.
-- 오라클문법에는 없음.
select board.bno,member.id, member.name, board.title
from member full outer join board
on board.id=member.id;


-- self join. 테이블 하나로 자신과 조인. alias를 다르게 해서 구현
-- 테이블의 컬럼값이 계층형으로 구성될 때 부모값을 구할 때 사용
select t1.INDUTY_CL_CD,t1.INDUTY_CL_NM,t1.UPPER_INDUTY_CL_CD,t2.INDUTY_CL_NM
from TB_INDUTY_CL t1 , TB_INDUTY_CL t2
where t1.UPPER_INDUTY_CL_CD=t2.INDUTY_CL_CD
order by t1.INDUTY_CL_CD;

-- union. select한 결과들을 한 set로 합쳐서 출력.
create table asia_sales(
    car_name varchar2(50) primary key,
    ea number not null
);
insert into asia_sales values('소나타',20);
insert into asia_sales values('그랜져',30);
commit;
create table america_sales(
    car_name varchar2(50) primary key,
    ea number not null
);
insert into america_sales values('제네시스GV80',10);
insert into america_sales values('엘란트라',70);
insert into america_sales values('소나타',20);
commit;

select car_name, ea  from asia_sales
union 
select car_name, ea from america_sales;

-- union all은 중복된 행을 모두 출력
select car_name, ea  from asia_sales
union all
select car_name, ea from america_sales;

-- minus. 차집합.
select car_name, ea  from asia_sales
minus
select car_name, ea from america_sales;


-- insersect. 교집합
select car_name, ea  from asia_sales
intersect
select car_name, ea from america_sales;


-- group by. 종류별로 count(),sum(),max(),min(),avg()를 구할 때 사용
select INDUTY_CL_SE_CD, count(*)
from TB_INDUTY_CL
group by INDUTY_CL_SE_CD;

select POPLTN_SE_CD, sum(POPLTN_CNT)
from TB_POPLTN
group by POPLTN_SE_CD
order by POPLTN_SE_CD;

-- having. group by 한 결과에서 filtering할 때 사용
select INDUTY_CL_SE_CD, count(*) as counts
from TB_INDUTY_CL
group by INDUTY_CL_SE_CD
having count(*)<10;

-- exists. 쿼리결과가 있으면 true, 없으면 false
-- subquery의 결과가 있을 때 바깥쪽쿼리의 결과 데이터가 나옴
select *
from TB_INDUTY_CL
where
exists (select * from TB_INDUTY_CL_SE where INDUTY_CL_SE_CD ='ICS001');

-- create table as select 테이블의 데이터를 검색해서 다른 테이블에 insert함
-- 새로운 테이블을 생성해서 insert.
create table TB_INDUTY_CL_SE_COPY2
as
select *
from TB_INDUTY_CL_SE;

select * from TB_INDUTY_CL_SE_COPY2;

-- 데이터 없이 테이블 구조만 복사하기
create table TB_INDUTY_CL_SE_COPY3
as
select *
from TB_INDUTY_CL_SE
where 1<>1;

select * from TB_INDUTY_CL_SE_COPY3;

select INDUTY_CL_SE_CD,INDUTY_CL_SE_NM into TB_INDUTY_CL_SE_COPY4
from TB_INDUTY_CL_SE;


-- insert into select. 테이블의 데이터를 검색해서 다른 테이블에 insert함
-- 기존 테이블에 insert.
create table TB_INDUTY_CL_SE_COPY(
    INDUTY_CL_SE_CD varchar2(6) primary key,
    INDUTY_CL_SE_NM varchar2(50) not null
);
insert into TB_INDUTY_CL_SE_COPY
select * from TB_INDUTY_CL_SE;

commit;

select * from TB_INDUTY_CL_SE_COPY;

-- case . if ~ els if ~ 와 유사
select ADSTRD_CD, POPLTN_CNT,
    case
        when POPLTN_CNT > 1000 then '인구가 많습니다'
        when POPLTN_CNT = 1000 then '인구가 1000명입니다'
        when POPLTN_CNT <= 1000 then '인구가 적습니다'
    end as result
from TB_POPLTN;
-- 정확히 같은 것만 비교
select INDUTY_CL_CD,INDUTY_CL_NM,INDUTY_CL_SE_CD,
    case INDUTY_CL_SE_CD
        when 'ICS001' then '대'
        when 'ICS002' then '중'
        when 'ICS003' then '소'
    end as depth
from TB_INDUTY_CL;

-- decode(). 오라클 전용 함수. case 문 같은 역할
select INDUTY_CL_CD,INDUTY_CL_NM,INDUTY_CL_SE_CD,
      decode(INDUTY_CL_SE_CD,'ICS001','대',
                            'ICS002','중',
                            '소') as depth     
from TB_INDUTY_CL;

-- is null. null값인 것을 찾을 때 사용. = null 이 아님.
select * from TB_INDUTY_CL
where UPPER_INDUTY_CL_CD is null;

select * from TB_INDUTY_CL
where UPPER_INDUTY_CL_CD = null; -- 결과없음

-- subquery. 서브쿼리
-- 쿼리 안쪽에 또 다른 쿼리가 존재 하는 형태
-- 안쪽 쿼리가 먼저 실행 된 후 바깥쪽 쿼리가 실행됨

select *
from TB_POPLTN
where POPLTN_SE_CD in (select POPLTN_SE_CD  from TB_POPLTN_SE where POPLTN_SE_CD<>'T') ;

select a.*, b.*
from (select * from asia_sales) a inner join (select * from america_sales) b -- from절 view, inline view
on a.car_name=b.car_name;


-- 연관관계 서브쿼리. join과 비슷하게 동작. 일반적으로 조인보다 속도가 느림.
-- 안쪽쿼리의 테이블과 바깥쪽 쿼리의 테이블이 서로 관련된 것을 찾는다.
-- 바깥쪽 쿼리 테이블의 한 행씩 안쪽 쿼리에서 사용됨. 한 행씩 처리 하는 작업이 완료되면 바깥쪽 쿼리의 나머지 부분이 실행됨
-- 연관관계 서브쿼리는 대부분 조인으로 구현 가능. 조인으로 구현 가능한 경우는 조인 사용 권장.
select ADSTRD_CD,STD_YM,POPLTN_SE_CD,
    (
        select POPLTN_SE_NM from TB_POPLTN_SE t1 where t1.POPLTN_SE_CD=t2.POPLTN_SE_CD
    ) as 성별
from TB_POPLTN t2;  
--조인으로 변경하면...
select t2.ADSTRD_CD, t2.STD_YM, t2.POPLTN_SE_CD, t1.POPLTN_SE_NM
from TB_POPLTN t2,TB_POPLTN_SE t1
where t1.POPLTN_SE_CD=t2.POPLTN_SE_CD;

-- 서브쿼리를 이용한 게시판 paging처리
-- drop table board;
-- sequence. 일련번호 자동 생성. 게시판글번호 자동생성시 사용
create sequence seq_board; -- 1부터 하나씩 증가

insert into board values(seq_board.nextval,'hkd','sql이 뭔가요?');
insert into board values(seq_board.nextval,'hkt','sql이 뭔가요?');

commit;
select * from board;

insert into board
select seq_board.nextval, id, title from board;

select count(*) from board;

select * from board;
select rownum, bno from board; -- rownum은 행번호를 매겨주는 함수

-- 1page
select  X.*
from (
    select rownum as rnum, A.*
    from (
        select *
        from board
        order by bno
        ) A
    where rownum <= 10) X
where X.rnum >= 1;

-- 2page
select  X.*
from (
    select rownum as rnum, A.*
    from (
        select *
        from board
        order by bno
        ) A
    where rownum <= 20) X
where X.rnum >= 11;

-- mysql
-- select * from board limit 0,10;

-- 쿼리옵티마이저(Query Optimizer).쿼리최적화기
-- 쿼리가 컴파일되서 실행될 때, 결과를 얻기 위한 가장 좋은 방법을 찾아 실행함.


-- to_char(). 문자열 변환 함수
select to_char(sysdate, 'YYYY-MM-DD') from dual;
select to_char(sysdate, 'YYYY"년"MM"월"DD"일"') from dual;
select to_char(sysdate, 'q') from dual; -- 분기

select distinct STD_YM from TB_POPLTN;

-- index
-- 검색속도 향상을 위해서 사용.
-- 책의 index(색인)와 비슷한 원리.
-- 데이터를 sort해서 저장함. 옆에 rowid를 같이 저장. rowid를 통해서 데이터에 직접 접근
-- 개발자와 관리자의 협업(?)이 필요.
-- 데이터가 적을 경우에는 index가 효과 없음.