SQL Basic

SQL BASIC 기본 교육 자료 #

SQL은 Sturctured Query Langugage의 약자로 데이터베이스의 데이터를 관리하기 위해 사용하는 언어. 자료의 검색과 관리, 스키마의 생성과 수정, 객체의 접근 조정 및 관리의 기능 제공. ANSI SQL 기본 문법은 원래 어떤 데이터베이스와도 호환이 가능해야 하나 통상 Vendor들이 표준을 잘지키지않는 편이라 기본적인 수준에서만 호환이 가능. NoSQL 계열의 데이터베이스에서는 사용되지 않음. (다음은 오라클 기준임)

Live SQL #

Oracle 가입
Oracle Live SQL 접속
“Start Coding Now” Live SQL 실행

Oracle Live SQL

Hello World! #

select 'Hello World!' from dual;

DDL / DML / DCL #

조회를 위한 예제 테이블이 작성을 위해 DDL, DML, DCL 개념 필요.

ddl (data definition language) ...

데이터 정의 언어로 데이터베이스에 오브젝트를 생성,삭제,변경 및 제거하는 명령문

create : 데이터베이스에 오브젝트를 생성 drop : 데이터베이스에 오브젝트를 삭제 alter : 데이터베이스에 오브젝트를 변경 truncate : 테이블에서 데이터를 삭제

dml (data manipulation languge) ...

데디터 조작 언어로 데이터베이스 테이블에서 데이터를 질의,생성,수정 및 삭제하는 질의문

select : 테이블에서 데이터를 검색 insert : 테이블에 데이터를 생성 update : 테이블에서 데이터를 변경 delete : 데이블에서 데이터를 삭제

dcl (data control language) ...

데이터 제어 언어로 데이터베이스의 권한을 관리하는데 사용하는 명령문

grant : 데이터베이스 사용자에게 특정 권한을 부여 revoke : 데이터베이스 사용자에게 특정 권한을 제거

Table Creation #

table creation ...

create table my_table_1
(
    column_1 varchar2(10),
    column_2 number,
    column_3 date
)
;

insert into my_table_1 (column_1,column_2,column_3) values ('홍길동',23,sysdate);
insert into my_table_1 (column_1,column_2,column_3) values ('김철수',19,sysdate);
insert into my_table_1 (column_1,column_2,column_3) values ('이영희',32,sysdate);
insert into my_table_1 (column_1,column_2,column_3) values ('박민호',22,sysdate);
insert into my_table_1 (column_1,column_2,column_3) values ('김종철',45,sysdate);
insert into my_table_1 (column_1,column_2,column_3) values ('김철수',36,sysdate);
insert into my_table_1 (column_1,column_2,column_3) values ('신영신',32,sysdate);
insert into my_table_1 (column_1,column_2,column_3) values ('최철호',11,sysdate);

데이터 생성 결과를 조회

query

select  *
from    my_table_1
;

result

COLUMN_1	COLUMN_2	COLUMN_3
홍길동	23	10-OCT-20
김철수	19	10-OCT-20
이영희	32	10-OCT-20
박민호	22	10-OCT-20
김종철	45	10-OCT-20
김철수	36	10-OCT-20
신영신	32	10-OCT-20
최철민	11	10-OCT-20

Download CSV 8 rows selected.

Select / From #

select / from ...

-- 주석입니다.
select * from my_table_1;

-- 컬럼명을 지정합니다.
select column_1,column_3 from my_table_1;

Where / And #

where / and ...

-- where
select * from my_table_1 where column_2 = 32;

-- where / 부등호 1
select * from my_table_1 where column_2 <= 30;

-- where / 부등호 2
select * from my_table_1 where column_2 >= 30;

-- where / 부등호 3
select * from my_table_1 where column_2 > 30;

-- where / 부등호 4
select * from my_table_1 where column_2 < 30;

-- where / and
select * from my_table_1 where column_2 > 10 and column_2 < 40;

-- where / and / 문자열 1
select * from my_table_1 where column_1 = '홍길동' and column 2 = 23;

-- where / and / 문자열 2
select * from my_table_1 where column_1 != '홍길동';

-- where / and / 문자열 3
select * from my_table_1 where column_1 <> '홍길동';

-- where / and / 문자열 / like 1
select * from my_table_1 where column_1 like '김%';

-- where / and / 문자열 / like 2
select * from my_table_1 where column_1 like '%철%';

-- where / and / 문자열 / like 3
select * from my_table_1 where column_1 like '%철';

-- where / and / 문자열 / like 4
select * from my_table_1 where column_1 like '김%철';

-- where / and / 문자열 / like 5
select * from my_table_1 where column_1 not like '%철';

-- where / and / between
select * from my_table_1 where column_2 between 22 and 32;

-- where / and / in 
select * from my_table_1 where column_1 in ('이영희','김철수');

-- where / and / not in 
select * from my_table_1 where column_1 not in ('이영희','김철수');

-- where / and / in / or
select * from my_table_1 where column_1 in ('이영희','김철수') or column_2 = 22;

-- where / and / or / and 1
select * from my_table_1 where column_1 in ('이영희','김철수') or column_2 = 22 and column_1 like '%수';

-- where / and / or / and 2
select * from my_table_1 where (column_1 in ('이영희','김철수') or column_2 = 22) and column_1 like '%수';

dual / + - * / #

dual / operatios ...

-- dual / + - * /
select 2+3, 5-2, 2*3, 8/2 from dual;

-- mod, power, sign, cos, tan
select mod(7,3),power(2,3),sign(1),cos(1),tan(1) from dual;

-- number, text, null, number+null, date
select 1, 'TEXT', null, 10+2+null, sysdate from dual;

-- number+null = null
select 10+2+null from dual;

-- number+text(number) = number
select 10+2+'3' from dual;

-- number+text : error
select 10+2+'A' from dual;

-- lower/upper
select 'Case',lower('Case'),upper('Case') from dual;

count / ditinct / alias / lower / upper #

count / ditinct / alias / lower / upper ...

-- count
select count(*) from my_table_1;

-- distinct
select distinct column_1 from my_table_1;

-- distinct / count 
select count(distinct column_1),count(column_1) from my_table_1;

-- distinct / count / alias
select count(distinct column_1) col1,count(column_1) col2,count(tab.column_1) col3 from my_table_1 tab;

from / nested from / with as #

from / nested from / with as ...

-- from 1
select  column_1,column_2,column_2+10 column_3
from    my_table_1
where   column_1 in ('홍길동','김철수','이영희')
;

-- nested from
select  *
from    (
        select  column_1,column_2,column_2+10 column_3
        from    my_table_1
        where   column_1 in ('홍길동','김철수','이영희')
        )
;

-- with 1
with    tab_1 as
(
select  column_1,column_2,column_2+10 column_3
from    my_table_1
where   column_1 in ('홍길동','김철수','이영희')
)
select  *
from    tab_1
;

-- with 2
with    tab_1 as
(
select  column_1,column_2,column_2+10 column_3
from    my_table_1
where   column_1 in ('홍길동','김철수','이영희')
)
,       tab_2 as
(
select  column_1,column_2,column_2+10 column_3
from    my_table_1
where   column_2 <= 30
)
select  *
from    tab_2
;

-- with 3
with    tab_1 as
(
select  column_1,column_2,column_2+10 column_3
from    my_table_1
where   column_1 in ('홍길동','김철수','이영희')
)
,       tab_2 as
(
select  column_1,column_2,column_2+10 column_3
from    my_table_1
where   column_2 <= 30
)
select  *
from    (
        select  *
        from    tab_2
        where   column_1 like '%철%'
        )
;

union all / union / intersect / minus #

union all / union / intersect / minus ...


-- union all
with    tab_1 as (select column_1,column_2,column_2+10 column_3 from my_table_1 where column_1 in ('홍길동','김철수','이영희'))
,       tab_2 as (select column_1,column_2,column_2+10 column_3 from my_table_1 where column_2 <= 30)
select  *
from    tab_1
union all
select  *
from    tab_2
;

-- union
with    tab_1 as (select column_1,column_2,column_2+10 column_3 from my_table_1 where column_1 in ('홍길동','김철수','이영희'))
,       tab_2 as (select column_1,column_2,column_2+10 column_3 from my_table_1 where column_2 <= 30)
select  *
from    tab_1
union all
select  *
from    tab_2
;

-- intersect
with    tab_1 as (select column_1,column_2,column_2+10 column_3 from my_table_1 where column_1 in ('홍길동','김철수','이영희'))
,       tab_2 as (select column_1,column_2,column_2+10 column_3 from my_table_1 where column_2 <= 30)
select  *
from    tab_1
intersect
select  *
from    tab_2
;
-- minus
with    tab_1 as (select column_1,column_2,column_2+10 column_3 from my_table_1 where column_1 in ('홍길동','김철수','이영희'))
,       tab_2 as (select column_1,column_2,column_2+10 column_3 from my_table_1 where column_2 <= 30)
select  *
from    tab_1
intersect
select  *
from    tab_2
;

join 1 / join 2 / join 3 / join 4 / join 5 #

join ...


-- join 1 
with    tab_1 as (select column_1,column_2,column_2+10 column_3 from my_table_1 where column_1 in ('홍길동','김철수','이영희'))
,       tab_2 as (select column_1,column_2,column_2+10 column_3 from my_table_1 where column_2 <= 30)
select  *
from    tab_1 a, tab_2 b
where   a.column_1 = b.column_1
;

-- join 2 
with    tab_1 as (select column_1,column_2,column_2+10 column_3 from my_table_1 where column_1 in ('홍길동','김철수','이영희'))
,       tab_2 as (select column_1,column_2,column_2+10 column_3 from my_table_1 where column_2 <= 30)
select  *
from    tab_1 a, tab_2 b
where   a.column_1 = b.column_1
and     a.column_2 = b.column_2
;

-- join 3
with    tab_1 as (select column_1,column_2,column_2+10 column_3 from my_table_1 where column_1 in ('홍길동','김철수','이영희'))
,       tab_2 as (select column_1,column_2,column_2+10 column_3 from my_table_1 where column_2 <= 30)
select  *
from    tab_1 a, tab_2 b
where   a.column_1 = b.column_1 (+)
and     a.column_2 = b.column_2 (+)
;

-- join 4
with    tab_1 as (select column_1,column_2,column_2+10 column_3 from my_table_1 where column_1 in ('홍길동','김철수','이영희'))
,       tab_2 as (select column_1,column_2,column_2+10 column_3 from my_table_1 where column_2 <= 30)
select  *
from    tab_1 a, tab_2 b
where   a.column_1 (+) = b.column_1
and     a.column_2 (+) = b.column_2
;

-- join 5
with    tab_1 as (select column_1,column_2,column_2+10 column_3 from my_table_1 where column_1 in ('홍길동','김철수','이영희'))
,       tab_2 as (select column_1,column_2,column_2+10 column_3 from my_table_1 where column_2 <= 30)
select  *
from    tab_1 a full outer join tab_2 b
on
(
a.column_1 = b.column_1 and
a.column_2 = b.column_2 
)
;

substr / length / replace / decode / instr / lpad / rapd / reg_exp #

substr / length / replace / decode / instr / lpad / rapd / reg_exp ...

select  column_1,
        substr(column_1,1,1) "성2",
        substr(column_1,2) "이름1",
        substr(column_1,2,2) "이름2",
        substr(column_1,-2) "이름3",
        length(column_1) "길이",
        replace(column_1,'길동','길수') "교체",
        decode(column_1,'이영희','요주의','김철수','악플러','김종철','유부남','-') "디코드",
        instr(column_1,'철') "찾기1",
        instr(column_1,'신',1) "찾기2",
        instr(column_1,'신',2) "찾기3",
        column_1,
        lpad(column_2,3,'0') "채우기1",
        rpad(column_2,3,'0') "채우기2",
        substr(column_1,1,1)||'씨_'||column_2||'세' "잇기",
        regexp_substr(column_1,'[^철]+',1,1) "정규식찾기1"
from    my_table_1
;

nvl / trim #

nvl / trim ...

with    data_list as
(
select ' data1 ' col1 from dual union all
select 'data2 ' col1 from dual union all
select ' data3' col1 from dual union all
select '  ' col1 from dual union all
select null col1 from dual
)
select  col1,
        length(col1) col1_len,
        trim(col1) col2,
        length(trim(col1)) col2_len,
        nvl(trim(col1),'-') col3,
        length(nvl(trim(col1),'-')) col3_len
from    data_list
;

order by #

order by asc/desc ...

select  *
from    my_table_1
order   by column_2 asc,column_1 desc,column_3

count / sum / avg / min / max / median /stddev / round / trunc / ceil / floor #

count / sum / avg / min / max / median /stddev / round / trunc / ceil / floor ...

select  count(column_1),
        sum(column_2),
        avg(column_2),
        min(column_2),
        max(column_2),
        median(column_2),
        stddev(column_2),
        round(avg(column_2)),
        round(stddev(column_2),2),
        trunc(avg(column_2)),
        ceil(avg(column_2)),
        floor(avg(column_2))
from    my_table_1
;

count / sum / avg / min / max / median /stddev / round / trunc / ceil / floor #

count / sum / avg / min / max / median /stddev / round / trunc / ceil / floor ...

select  substr(column_1,1,1),sum(column_2)
from    my_table_1
group   by substr(column_1,1,1)
;

select  substr(column_1,1,1),sum(column_2)
from    my_table_1
group   by substr(column_1,1,1)
having  sum(column_2) >= 30
;

select  *
from    my_table_1
where   substr(column_1,1,1) = '김'
;

select  substr(column_1,1,1),sum(column_2),avg(column_2)
from    my_table_1
where   substr(column_1,1,1) = '김'
group   by substr(column_1,1,1)
;

select  substr(column_1,1,1),sum(column_2),avg(column_2)
from    my_table_1
where   substr(column_1,1,1) = '김'
and     column_2 >= 20
group   by substr(column_1,1,1)
;

case when / to_char / to_date / add_months / last_day #

case when / to_char / to_date / add_months ...

select  column_1,column_2,
        case when column_1 = '홍길동' then '용의자'
             when column_1 = '박민호' and column_2 = 22 then '용의자'
             when column_1 = '신영신' then '용의자'
        else '일반시민' end column_x,
        case when column_1 = '홍길동' then column_2
             when column_1 = '박민호' and column_2 = 22 then column_2
             when column_1 = '신영신' then column_2
        else 0 end column_y,
        to_char(column_3,'yyyymmdd') date_to_char_01,
        to_char(column_3,'yy/mm/dd') date_to_char_02,
        to_char(column_3,'mm.dd.yy') date_to_char_03,
        to_char(column_3,'hh24:mi:ss') date_to_char_04,
        to_char(column_3,'D') date_to_char_05,
        to_date('20200315','yyyymmdd')-to_date('20200215','yyyymmdd') date_calc_01,
        to_date('20200315','yyyymmdd')+17 date_calc_02,
        trunc(to_date('20200315','yyyymmdd'),'IW') date_calc_03,
        add_months(to_date('20200331','yyyymmdd'),-1) date_calc_04,
        add_months(to_date('20200331','yyyymmdd'),+1) date_calc_05,
        last_day(to_date('20200205','yyyymmdd')) date_calc_06
from    my_table_1
;

window function #

window function ...

select  column_1,column_2,
        sum(column_2) over (partition by substr(column_1,1,1)) family_sum,
        sum(column_2) over (partition by substr(column_1,1,1) order by column_2) family_cum,
        sum(column_2) over (partition by substr(column_1,1,1) order by column_2 desc) family_dum
from    my_table_1
;