ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • [SQL] SELECT, SELECT LIST, WHERE, NULL
    Computer Science/DB 2023. 4. 5. 21:46
    728x90

    ๐Ÿ‘€ ์œ ์šฉํ•œ ์ •๋ณด

    ๐Ÿ’กํ”ผ๋“œ๋ฐฑ Tip

    Client - Server ์ดํ•ด

    ๐Ÿ’ก
    Connection๊ณผ Session - Client๊ฐ€ Server์— login์‹œ, Connection ์—ฐ๊ฒฐ - ์„œ๋ฒ„๋Š” ํ•ด๋‹น ํด๋ผ์ด์–ธํŠธ์— ๋Œ€ํ•œ ์ƒํƒœ ์ •๋ณด ์ €์žฅ (Session ๊ฐ’ ์ €์žฅ) - ๋ฉ”๋ชจ๋ฆฌ ํ• ๋‹น ์„ธ์…˜ ์—ฐ๊ฒฐ ์ดํ›„, - Client๋Š” Server(DBMS)์— SQL์„ ์š”์ฒญ์„ ์ „์†ก - Server(DBMS)๋Š” ํ•ด๋‹น ์š”์ฒญ์„ ์ฒ˜๋ฆฌ ํ›„ ์‘๋‹ต ์„ธ์…˜ ์—ฐ๊ฒฐ ์ข…๋ฃŒ - logout๋  ๋•Œ DBMS ์„œ๋ฒ„๋Š” ํ•ด๋‹น ์„ธ์…˜์˜ ๋ฉ”๋ชจ๋ฆฌ ํ• ๋‹น ํ•ด์ œ

    ๐Ÿ’ก
    * ๋ชจ๋“  ์„œ๋ฒ„๋Š” Port Listener๋ฅผ ๊ฐ€์ง„๋‹ค. * DBMS์˜ ์ž…์žฅ์—์„œ WAS๋Š” Client์ด๋ฏ€๋กœ, ๋™์ผํ•œ Port Listen ๊ณผ์ •์„ ๊ฑฐ์ณ Connection๊ณผ Session์„ ๊ฐ€์ง„๋‹ค. * ๋™์ผํ•œ Port์— ์—ฌ๋Ÿฌ ์„œ๋น„์Šค๋ฅผ ๊ฐ€์งˆ ๋•Œ, ์„œ๋น„์Šค๋ช…์„ ํ†ตํ•ด ๊ตฌ๋ณ„ํ•  ์ˆ˜ ์žˆ๋‹ค.


    ๐Ÿ“˜ SELECT

    SQL Developer

    ๐Ÿ’ก
    Oracle ็คพ์—์„œ ๋ฌด๋ฃŒ๋กœ ์ œ๊ณตํ•˜๋Š” Java ๊ธฐ๋ฐ˜ GUI IDE(Integrated Development Environment: ํ†ตํ•ฉ๊ฐœ๋ฐœํ™˜๊ฒฝ)๋ฅผ ๊ฐ–์ถ˜ SQL ๊ฐœ๋ฐœ(๊ฐœ๋ฐœ์ž,๋ถ„์„๊ฐ€) ๋ฐ ๊ด€๋ฆฌํˆด(DBA : Database Administrator,Tuner)

    ๐Ÿ’ก
    DESC ํ…Œ์ด๋ธ”๋ช… : ํ…Œ์ด๋ธ”์˜ ๊ตฌ์กฐ ์ •๋ณด ๋ณด๊ธฐ - ์ปฌ๋Ÿผ๋ช…, ๋ฐ์ดํ„ฐ ํƒ€์ž…, ๋ฐ์ดํ„ฐ ๊ธธ์ด๋ฅผ ๋ณด์—ฌ์คŒ SELECT * FROM TAB; : ๋‚ด๊ฐ€ ์‚ฌ์šฉ ๊ฐ€๋Šฅํ•œ ํ…Œ์ด๋ธ”์˜ ๋ชฉ๋ก ๋ณด๊ธฐ

    SQL ๋ช…๋ น์–ด ๋ถ„๋ฅ˜์™€ SELECT

    QUERY

    ๐Ÿ’ก
    โ‘  ์งˆ์˜์–ด : ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์— ์ €์žฅ๋œ(ํ…Œ์ด๋ธ”) ๋ฐ์ดํ„ฐ๋ฅผ ์กฐํšŒ ํ•˜๋Š” ๋ช…๋ น์–ด - SELECT โ‘ก SQL

    ๐Ÿ“˜ SELECT LIST

    ๐Ÿ’ก
    SELECT * FROM TAB;
    • ๋‚ด๊ฐ€ ์‚ฌ์šฉ ๊ฐ€๋Šฅํ•œ ๋ชจ๋“  ํ…Œ์ด๋ธ” ๋ฆฌ์ŠคํŠธ๋ฅผ ์กฐํšŒํ•˜๋Š” SQL
    • ๋‚ด๊ฐ€ ~ DBMS์— Connection(์ ‘์†)ํ•œ ์‚ฌ์šฉ์ž(Schema) ์‚ฌ์šฉ๊ฐ€๋Šฅํ•œ ~ ์†Œ์œ ํ•œ(Owner) ํ…Œ์ด๋ธ” + ๊ถŒํ•œ(Privilege)์„ ๋ถ€์—ฌ๋ฐ›์€(granted) ํ…Œ์ด๋ธ”

    DESC[RIBE] EMP

    โ‘  ์ปฌ๋Ÿผ ์ด๋ฆ„ , ํ…Œ์ด๋ธ”๋‚ด์—์„œ ์ปฌ๋Ÿผ ์ •์˜ ์ˆœ์„œ โ‘ก ์ปฌ๋Ÿผ ๋ฐ์ดํ„ฐ ํƒ€์ž… ex) VARCHAR2(๋ฌธ์ž),NUMBER(์ˆซ์ž),DATE(๋‚ ์งœ) โ‘ข ๋ฐ์ดํ„ฐ ๊ธธ์ด โ‘ฃ NULL ํ—ˆ์šฉ์—ฌ๋ถ€

    ๐Ÿ’ก
    SELECT LIST
    • SELECT ์™€ FROM์‚ฌ์ด์— ์กฐํšŒ(Projection)๋ฅผ ์›ํ•˜๋Š” COLUMN(๋“ค)์„ ์ง€์ •

    โ‘  SELECT EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM EMP; โ‘ก SELECT * FROM EMP;

    ๐Ÿ’ก
    ๋ชจ๋“  ์ปฌ๋Ÿผ ๋ช…์‹œ : ๋” ๋ช…๋ฃŒํ•˜๊ฒŒ ํ‘œ์‹œ ๊ฐ€๋Šฅ * : ํ…Œ์ด๋ธ” ๋‚ด ์ •์˜๋œ ์ˆœ์„œ๋Œ€๋กœ ํ‘œ์‹œ๋œ๋‹ค. SQL๋ฌธ์€ ๋ชจ๋“  ์ปฌ๋Ÿผ์„ ๋ช…์‹œํ•˜์—ฌ ์ถœ๋ ฅํ•˜๋Š” ๊ฒƒ์ด ๋” ์ข‹์€ ๋ฐฉ์‹์˜ SQL๋ฌธ์ด๋‹ค.
    ๐Ÿ’ก
    ์ปฌ๋Ÿผ ๊ตฌ๋ถ„์ž ์ฝค๋งˆ(,) , SQL ๋ฌธ์žฅ ์ข…๊ฒฐ์ž(;)

    โ‘ข SELECT EMPNO, ENAME, HIREDATE FROM EMP;

    โ‘ฃ SELECT SAL, JOB, EMPNO, ENAME FROM EMP;

    • ์ปฌ๋Ÿผ ์กฐํšŒ ์ˆœ์„œ ์ž„์˜๋กœ ๋ณ€๊ฒฝ ๊ฐ€๋Šฅ

    โ‘ค SELECT EMPNO, EMPNO, EMPNO, ENAME, JOB, SAL FROM EMP;

    • ๋™์ผํ•œ ์ปฌ๋Ÿผ ์—ฌ๋Ÿฌ๋ฒˆ ์กฐํšŒ ๊ฐ€๋Šฅ

    โ‘ฅ SELECT EMPNO, SAL, 2022, 'Happy New Year' FROM EMP;

    • ํ™‘๋”ฐ์˜ดํ‘œ(๏ผ‡) = ๋‹จ์ผ์ธ์šฉ๋ถ€ํ˜ธ = single quotation mark
    • 2022 : ์ˆซ์ž ๋ฐ์ดํ„ฐ , 'Happy New Year' : ๋ฌธ์ž ๋ฐ์ดํ„ฐ
    • ํ…Œ์ด๋ธ”๋‚ด์— ์กด์žฌํ•˜์ง€ ์•Š์ง€๋งŒ SELECT์‹œ ์ž„์˜์ ์œผ๋กœ ๋ฐ์ดํ„ฐ๋ฅผ ๋งŒ๋“ค์–ด์„œ ์กฐํšŒ ๊ฐ€๋Šฅ

    ์‚ฐ์ˆ  ์—ฐ์‚ฐ์ž

    ๐Ÿ’ก
    SQL ์ฟผ๋ฆฌ์— ์‚ฐ์ˆ  ์—ฐ์‚ฐ์ž๊ฐ€ ์‚ฌ์šฉ ๊ฐ€๋Šฅ ํ•จ์œผ๋กœ์จ, ๊ณ„์‚ฐ ์ฒ˜๋ฆฌ๋ฅผ ์„œ๋ฒ„์— ์œ„์ž„ ํ•  ์ˆ˜ ์žˆ๋‹ค. โ†’ ๋ฐ์ดํ„ฐ ๊ฐ€๊ณต ๋ฐ ๊ณ„์‚ฐ ์ฒ˜๋ฆฌ๋ฅผ ์„œ๋ฒ„(DBMS)์— ์œ„์ž„ํ•จ์œผ๋กœ, ํด๋ผ์ด์–ธํŠธ ๋‹จ์˜ ์„ฑ๋Šฅ ๊ฐœ์„ ๊ณผ ์ฝ”๋“œ๊ฐ€ ๊ฐ„๊ฒฐํ•ด์ง„๋‹ค!! * ํ•จ์ˆ˜ ์ฒ˜๋ฆฌ๋„ ๊ฐ€๋Šฅํ•จ
    ๐Ÿ’ก
    ์‚ฐ์ˆ  ์—ฐ์‚ฐ์ž ์‚ฌ์šฉ
    • SQL ๊ตฌ๋ฌธ๋‚ด์—์„œ ์‚ฐ์ˆ (์‚ฌ์น™)์—ฐ์‚ฐ( *, /, +, - )์ž ์‚ฌ์šฉ ๊ฐ€๋Šฅ
    • CLIENT/SERVER์  ๊ด€์  ?
    • NUMBER ์™€ DATE ํƒ€์ž…์— ์‚ฌ์šฉ โ†’ ๊ฐ™์€ ํ˜•ํƒœ๋กœ ์ €์žฅ๋จ. (DATE ํƒ€์ž… ์‚ฐ์ˆ  ์—ฐ์‚ฐ ๊ฐ€๋Šฅ)
    • ์—ฐ์‚ฐ์ž ์šฐ์„ ์ˆœ์œ„ : ( ) > NOT > ๋น„๊ต์—ฐ์‚ฐ์ž > SQL์—ฐ์‚ฐ์ž > AND > OR > ์‚ฐ์ˆ ์—ฐ์‚ฐ์ž

      โ‡’ ์—ฐ์‚ฐ์ž ์šฐ์„ ์ˆœ์œ„ ํ™œ์šฉ๋ณด๋‹ค ๋ช…๋ฃŒํ•˜๊ฒŒ ํ‘œ๊ธฐํ•˜๋Š” ๊ฒƒ์ด ๋” ์ค‘์š”ํ•˜๋‹ค.

    โ‘  SELECT ENAME,SAL, SAL*12, HIREDATE, HIREDATE-7, HIREDATE + 100 FROM EMP;

    • SAL : NUMBER TYPE, HIREDATE : DATE TYPE
    • ์ •์ˆ˜, ๋‚ ์งœ ๋ฐ์ดํ„ฐ์— ๋Œ€ํ•œ ์—ฐ์‚ฐ ์ฒ˜๋ฆฌ ๊ฐ€๋Šฅ

    โ‘ก SELECT SAL, SAL+300*12,(SAL+300)*12 FROM EMP;

    • ์—ฐ์‚ฐ์ž ์šฐ์„ ์ˆœ์œ„์— ๋Œ€ํ•œ ํ˜ผ๋™์ด ์˜ค์ง€ ์•Š๋„๋ก ๋ช…๋ฃŒํ•˜๊ฒŒ ๊ด„ํ˜ธ ํ‘œ๊ธฐํ•˜์ž

    โ‘ข SELECT ENAME,COMM,COMM+300 FROM EMP;

    • ๊ธฐ์กด ์ปค๋ฏธ์…˜(COMM)์— 300์„ ๋”ํ•ด์„œ ๋ณด๋„ˆ์Šค ์ง€๊ธ‰, ์›ํ•˜๋Š” ๊ฒฐ๊ณผ๊ฐ€ ์•ˆ๋‚˜์˜ค๋Š” ์ด์œ ๋Š” โ†’ null ๊ฐ’์— ๋Œ€ํ•œ ์—ฐ์‚ฐ ๋ถˆ๊ฐ€

    Alias (๊ฐ€๋ช…, ๋ณ„์นญ)

    ๐Ÿ’ก
    Alias ๋Š” Table Alias์™€ Column Alias๋กœ ๊ตฌ์„ฑ Column Alias 1) AS โ†’ ANSI - SQL ํ‘œ์ค€ 2) 3) โ€œ โ€œ
    ๐Ÿ’ก
    - Table Alias : ์ผ๋ฐ˜์ ์œผ๋กœ๋Š” SELF JOIN ์‹œ์— ์‚ฌ์šฉ ex) SELECT E.EMPNO,E.ENAME FROM EMP E;

    - Column Alias : Column Heading(์ปฌ๋Ÿผ ๋ ˆ์ด๋ธ”)์„ ์˜๋ฏธ์žˆ๋Š” ๋‹ค๋ฅธ ์ด๋ฆ„์œผ๋กœ ์žฌ์ •์˜ โ‘  COLUMN์˜ ์˜๋ฏธ ๋ช…๋ฃŒ์„ฑ โ‘ก DB ํ”„๋กœ๊ทธ๋žจ ๊ฐœ๋ฐœ์‹œ Code์˜ ๋ช…๋ฃŒ์„ฑ & ์ปฌ๋Ÿผ์ œ์–ด(๊ณ„์‚ฐ์‹์—์„œ ์œ ์šฉ) โ‘ข ๋Œ€๋ฌธ์ž ํ‘œํ˜„

    โ‘ฃ SELECT ENAME, SAL+12 ,SAL*12 as annual_salary FROM EMP;

    โ‘ค SELECT ENAME, MGR Manager, SAL*12 as annual_SAL, COMM+300 "Special Bonus"

    FROM EMP;

    • ์ปฌ๋Ÿผ Alias 3๊ฐ€์ง€ ํ‘œํ˜„๋ฐฉ์‹(๊ณต๋ฐฑ๋ฌธ์ž, AS, โ€œ~โ€) ์ค‘ ๊ฐ€์žฅ ๋ช…๋ฃŒํ•œ ๋ฐฉ์‹์€?, โ€œ~โ€์€ ํŠน์ˆ˜๋ฌธ์ž, ๊ณต๋ฐฑ๋ฌธ์ž, ๋Œ€์†Œ๋ฌธ์ž ๊ตฌ๋ถ„์ด ํ•„์š”ํ•œ ๊ฒฝ์šฐ ์‚ฌ์šฉ

    โ‘ฅ SELECT ENAME, COMM+300 ๋ณด๋„ˆ์Šค, COMM+300 AS "Special Bonus" FROM EMP; * ํ•œ๊ธ€ alias ๊ฐ€๋Šฅ

    ๐Ÿ’ก
    ์ถ”ํ›„ Join ์—ฐ์‚ฐ ์ค‘์š”

    ๋ฌธ์ž์—ด ๊ฒฐํ•ฉ ์—ฐ์‚ฐ์ž (Concatenation operator)

    ๐Ÿ’ก
    โ‘  SELECT ENAME||JOB FROM EMP;
    • | : ์ˆ˜์ง์„  ๊ธฐํ˜ธ(Vertical bar), ํŒŒ์ดํ”„ ๋ฌธ์ž , OR ์—ฐ์‚ฐ

    โ‘ก SELECT DNAME||' ๋ถ€์„œ๋Š” '||LOC||' ์ง€์—ญ์— ์œ„์น˜ํ•ฉ๋‹ˆ๋‹ค.' as LOC FROM DEPT;

    • ๋ช…๋ชฉํ˜• ์ž๋ฃŒ๋ฅผ ๋ฌธ์žฅ์œผ๋กœ

    โ‘ข SELECT ename||'''s JOB is '||job as job_list FROM emp;

    • ํ™‘๋”ฐ์˜ดํ‘œ(๏ผ‡) = ๋‹จ์ผ์ธ์šฉ๋ถ€ํ˜ธ = single quotation mark
    • Oracle DBMS ๋ฌธ์ž ๋ฐ์ดํ„ฐ ํ‘œํ˜„ : 'Happy New Year'
    • SCOTT's JOB is ANALYST vs SCOTT"s JOB is ANALYST

    โ‘ฃ SELECT sal, sal*100, sal || '00', to_char(sal)||'00' FROM EMP;

    • sal||'00' : ์ˆซ์ž ํƒ€์ž…๊ณผ ๋ฌธ์ž ํƒ€์ž… ๊ฒฐํ•ฉ, ๊ฒฐ๊ณผ ํƒ€์ž…์€? โ†’ ๋ฌธ์ž ํƒ€์ž… (์™ผ์ชฝ ์ •๋ ฌ)

    • Data type Conversion - Implicit Conversion(์•”์‹œ์ , ์ž๋™์œผ๋กœ) ex) sal || '00' - Explicit Conversion (๋ช…์‹œ์ , ์ˆ˜๋™์œผ๋กœ) ex) to_char(sal) || '00'
    • ์ข‹์€๋ฐฉ์‹์˜ SQL Coding์€? - Explicit Conversion ๋ช…์‹œ์  ํ˜•๋ณ€ํ™˜์ด ๋” ์ข‹์€ ๋ฐฉ์‹์ด๋‹ค. - ์•”์‹œ์  ์ž๋™ ํ˜•๋ณ€ํ™˜์€ DBMS ๋ฒ„์ „์— ๋”ฐ๋ผ์„œ ์˜ค๋ฅ˜ ์œ ๋ฐœ ๋ฐ ๊ฐ€๋…์„ฑ์— ์ข‹์ง€ ๋ชปํ•˜๋‹ค.

    DUAL โ†’ Dummy Table

    ๐Ÿ’ก
    sys ๊ณ„์ • ์†Œ์œ ์˜ Dummy Table๋กœ ์‹ค์ œ ํ…Œ์ด๋ธ”๋กœ๋ถ€ํ„ฐ ๋ฐ์ดํ„ฐ๋ฅผ ๊ฐ€์ ธ์˜ค๋Š” ๊ฒƒ์ด ์•„๋‹Œ function, calculation์„ ์ˆ˜ํ–‰ํ•˜๊ธฐ ์œ„ํ•ด์„œ ์‚ฌ์šฉํ•˜๋Š” ์ž‘๊ณ  ๊ฐ€๋ฒผ์šด ํ…Œ์ด๋ธ” ( 1 row, 1 column) โ‘ค desc dual // describe dual , ์‚ฌ์ „์  ์˜๋ฏธ : ๋‘˜์˜(์ด์ค‘์˜) ?

    SELECT * FROM DUAL;

    โ‘ฅ SELECT sysdate FROM dual; // system์˜ ํ˜„์žฌ date(๋‚ ์งœ์™€ ์‹œ๊ฐ„)์„ ๋ฆฌํ„ดํ•˜๋Š” ํ•จ์ˆ˜

    ๐Ÿ’ก
    ์„œ๋ฒ„ ์‹œ๊ฐ„์„ ๊ธฐ์ค€์œผ๋กœ ํ•œ ์‹œ๊ฐ„์„ ์‚ฌ์šฉํ•˜๊ธฐ ์œ„ํ•ด ์ž์ฃผ ์‚ฌ์šฉ๋œ๋‹ค.

    โ‘ฆ SELECT 202512345, to_char(202512345,'999,999,999'), to_char(2025*12345,'999,999,999') as cal FROM dual;

    SELECT 202512345, to_char(202512345123123,'999,999,999'), to_char(2025*12345,'999,999,999') as cal FROM dual;

    • ํฌ๋ฉง๋ณด๋‹ค ๋” ํฐ ์ˆ˜๋ฅผ ๋„ฃ์„ ์‹œ ์ •์ƒ ํ‘œ๊ธฐ๋˜์ง€ ์•Š๋Š”๋‹ค.
    ๐Ÿ’ก
    date : ๋‚ ์งœ / ์‹œ๊ฐ„์˜ ์˜๋ฏธ๋ฅผ ๊ฐ€์ง„๋‹ค.

    ๐Ÿ“˜ WHERE

    ๐Ÿ’ก
    ์›ํ•˜๋Š” ROW(๋ ˆ์ฝ”๋“œ) ๋ฅผ ์กฐํšŒํ•˜๋Š” ์กฐ๊ฑด์ ˆ
    ๐Ÿ’ก
    โ‘  SELECT * FROM EMP WHERE DEPTNO = 10;

    โ‘ก SELECT DEPTNO, ENAME, SAL, JOB FROM EMP WHERE SAL > 2000;

    โ‘ข SELECT DEPTNO, ENAME, SAL, JOB FROM EMP WHERE DEPTNO = 10 AND SAL > 2000;

    โ‘ฃ SELECT DEPTNO, ENAME, SAL, JOB FROM EMP WHERE DEPTNO = 10 OR SAL > 2000;

    โ‘ค SELECT DEPTNO, SAL, JOB FROM EMP WHERE DEPTNO = 10 AND SAL > 2000 OR JOB='MANAGER';

    SELECT DEPTNO, SAL, JOB FROM EMP WHERE (DEPTNO = 10 AND SAL > 2000) OR JOB='MANAGER';

    SELECT DEPTNO, SAL, JOB FROM EMP WHERE DEPTNO = 10 AND (SAL > 2000 OR JOB='MANAGER');

    • AND์™€ OR์˜ ์—ฐ์‚ฐ์ž ์šฐ์„ ์ˆœ์œ„ ํ™•์ธ, ์ข‹์€ ๋ฐฉ์‹์˜ SQL ์ฝ”๋”ฉ?

    โ‡’ AND๊ฐ€ ์šฐ์„ ์ˆœ์œ„๊ฐ€ ๋” ๋†’๋‹ค!!!

    โ‡’ Oracle DBMS ๋‚ด์˜ Optimizer(์ตœ์ ํ™”๊ธฐ)๊ฐ€ OR๋ณด๋‹ค AND๋ฅผ ๋จผ์ € ์‹คํ–‰ํ•œ๋‹ค.

    A : 1000๊ฐœ, B : 1๊ฐœ

    A AND B โ‡’ 1๊ฑด

    A OR B โ‡’ 1000๊ฑด

    ๐Ÿ’ก
    AND๋ฅผ ๋จผ์ € ์ฒ˜๋ฆฌํ•  ๊ฒฝ์šฐ๊ฐ€ OR๋ฅผ ๋จผ์ € ์ฒ˜๋ฆฌํ•˜๋Š” ๊ฒฝ์šฐ๋ณด๋‹ค ์ฒ˜๋ฆฌ๋Ÿ‰์ด ํ›จ์”ฌ ํšจ์œจ์ ์ด๋‹ค.

    โ‘ฅ SELECT DEPTNO, ENAME, SAL, JOB FROM EMP WHERE JOB = 'manager';

    • ์ด์œ ๋Š”?

    โ‡’ manager๊ฐ€ ์†Œ๋ฌธ์ž ๋ฌธ์ž์—ด์ด๋ฏ€๋กœ

    โ‘ฆ SELECT DEPTNO,ENAME,JOB FROM EMP WHERE 1=1;

    โ‘ง SELECT DEPTNO,ENAME,JOB FROM EMP WHERE 1=2;

    โ‘จ SELECT DEPTNO, ENAME, SAL, JOB FROM EMP WHERE (DEPTNO,JOB,MGR) = ((10,'MANAGER',7839));

    ๐Ÿ“˜ NULL

    ๐Ÿ’ก
    ์‚ฌ์ „์  ์˜๋ฏธ : ๊ฐ’์ด ์ •์˜๋˜์ง€ ์•Š์€, ์กด์žฌํ•˜์ง€ ์•Š๋Š” - Data : ๋ฐ์ดํ„ฐ๊ฐ€ ์กด์žฌํ•˜์ง€ ์•Š๋Š” ์ƒํƒœ (๊ฒฐ์ธก์น˜ : Missing Value) * NULL โ‰  Zero * NULL โ‰  blank space (โ€™ โ€™) โ‡’ ASCII ์ฝ”๋“œ 0->48, โ€˜ โ€˜ ->32 , null-> 00 , - Oracle DBMS: NULL์„ ํ‘œํ˜„ํ•˜๊ธฐ ์œ„ํ•ด ๋‚ด๋ถ€์— 1 Byte ์‚ฌ์šฉ, Trailing NULL์ธ ๊ฒฝ์šฐ ์ €์žฅํ•˜์ง€ ์•Š๋Š”๋‹ค.

    1. NULL์€ ์—ฐ์‚ฐ ๋ถˆ๊ฐ€! (NULL ๊ณผ ์‚ฐ์ˆ ์—ฐ์‚ฐ)

    ๐Ÿ’ก
    SELECT 300/0 FROM dual; // โ€œdivisor is equal to zero"

    SELECT 300+400, 300+NULL, 300/NULL FROM dual; // NULL ์—ฐ์‚ฐ ๊ฒฐ๊ณผ๋Š”?

    SELECT ENAME, SAL, COMM, COMM+SAL*0.3 as bonus FROM EMP; // ์‹ค์ˆ˜ํ•˜๊ธฐ ์‰ฌ์šดโ€ฆํ•ด๊ฒฐ์ฑ…์€โ€ฆ.? โ‡’ NULL ์ œ์–ด ํ•จ์ˆ˜ ์‚ฌ์šฉ

    2. NULL์€ ๋น„๊ต ๋ถˆ๊ฐ€!

    ๐Ÿ’ก
    // null์ด ์žˆ๋Š” column ๋น„๊ต์—ฐ์‚ฐ // null์€ ์ผ๋ฐ˜์ ์ธ ๋น„๊ต์—ฐ์‚ฐ์ž๋กœ ๋น„๊ต ๋ถˆ๊ฐ€! SELECT ENAME,SAL,COMM FROM EMP WHERE COMM > -1;
    • -1 ๋ณด๋‹ค ํฐ ๊ฒƒ์— null์€ ํฌํ•จ๋˜์ง€ ์•Š๋Š”๋‹ค.

    SELECT ENAME,SAL,COMM FROM EMP WHERE COMM = null;

    • comm = null ์ธ ๊ฒƒ์ด ์ •์ƒ ๋น„๊ต๋˜์ง€ ๋ชปํ•จ.

    SELECT ENAME,SAL,COMM FROM EMP WHERE COMM <> null;

    • comm โ‰  null ์ธ ๊ฒƒ์ด ์ •์ƒ ๋น„๊ต๋˜์ง€ ๋ชปํ•จ

    // is ์„ ํ†ตํ•œ null ๊ฐ’ ๋น„๊ต ๊ฐ€๋Šฅ

    // is null : null์ธ ์กฐ๊ฑด SELECT ENAME,SAL,COMM FROM EMP WHERE COMM is null;

    // is not null ์„ ํ†ตํ•œ null ๊ฐ’์ด ์•„๋‹Œ ์กฐ๊ฑด SELECT ENAME,SAL,COMM FROM EMP WHERE COMM is not null;

    ๐Ÿ’ก
    SQL Developer์—์„œ๋Š” NULL์„ ํ‘œ๊ธฐํ•ด์ค€๋‹ค. ํ•˜์ง€๋งŒ, ์‹ค์ œ DBMS์—์„œ๋Š” NULL ํ‘œ๊ธฐ ๊ฐ’์ด ์•„๋‹Œ ์—†๋Š” ๊ฐ’์ด ๋“ค์–ด์žˆ๋Š” ๊ฒƒ์„ ์œ ์˜ํ•˜์ž.

    3. ์ œ์–ด ๋ถˆ๊ฐ€ (NULL์„ ํ•จ์ˆ˜์— ์ ์šฉ ๋ถˆ๊ฐ€)

    ๐Ÿ’ก
    SELECT ENAME, length(ENAME), COMM, length(COMM) FROM EMP;
    • ๋‚ด์žฅํ•จ์ˆ˜ length()์— null์ด ํฌํ•จ๋œ๋‹ค๋ฉด, null์ด ์ถœ๋ ฅ๋˜์–ด ๋ฒ„๋ฆผ

    SELECT sal, comm, abs(sal-comm)+300 FROM emp;

    • ๋งˆ์ฐฌ๊ฐ€์ง€๋กœ null์— ๋Œ€ํ•ด ์‚ฐ์ˆ  ์—ฐ์‚ฐ์ด ๋ถˆ๊ฐ€๋Šฅํ•˜๊ณ , ๋‚ด์žฅ ํ•จ์ˆ˜ abs()์— null๊ฐ’์„ ๋Œ€์ž…ํ•  ๊ฒฝ์šฐ, null์ด ์ถœ๋ ฅ๋œ๋‹ค.
    • length() : ๋ฌธ์ž์—ด์˜ ๊ธธ์ด ๋ฆฌํ„ด
    • length(COMM) - ์ˆซ์žํ˜• โ‡’ to_char(comm)
      • 0 โ†’ โ€˜0โ€™
      • NULL โ†’ NULL

    4. NULL ์ œ์–ด ํ•จ์ˆ˜

    ๐Ÿ’ก
    SELECT COMM, NVL(COMM,0), DECODE(COMM, NULL, 0, COMM) AS NVL_SIMUL FROM EMP;
    • NVL() ์„ ํ†ตํ•ด NULL ๊ฐ’์„ 0์œผ๋กœ ๋Œ€์ฒดํ–ˆ๋‹ค.

    SELECT concat('Commission is ',COMM), 'Commission is '||COMM FROM EMP; -- NULL ๋ฌด์‹œ

    select count(sal) as sal_cnt, count(comm) as comm_cnt, sum(comm) as comm_sum from emp;

    • NULL ๊ฐ’์€ count()์™€ sum()๋˜์ง€ ๋ชปํ–ˆ๋‹ค.


    • ๋‹จ์ผํ–‰ ํ•จ์ˆ˜(Single Row Function) ex) length, abs
    • ๊ทธ๋ฃนํ–‰ ํ•จ์ˆ˜(Group Row Function) ex) count, sum
    • NVL() : NULL๊ฐ’ ๋Œ€์ฒด
    • DECODE() : SQL ๋ฌธ์žฅ๋‚ด if๋ฌธ ์—ญํ• ์„ ํ•œ๋‹ค.
      • DECODE(COMM, NULL, 0, COMM) โ†’ if(COMM == NULL) return 0 else: return COMM;


    ๐Ÿ“๊ณผ์ œ

    1. Client/Server ๊ตฌ์กฐ๋„ ๋ฐ ์ตœ์‹  ์‚ฌ๋ก€ ๋ฌธ์„œ ์ •๋ฆฌ

    ๐Ÿ’ก
    ํด๋ผ์šฐ๋“œ ์ปดํ“จํŒ… ํ™œ์šฉ ํด๋ผ์šฐ๋“œ ์„œ๋น„์Šค ์ œ๊ณต ์—…์ฒด์˜ ์„œ๋ฒ„์™€ ํด๋ผ์ด์–ธํŠธ๊ฐ€ ์„œ๋น„์Šค ์ด์šฉ

    2. SQL ์ฃผ์š” ํŠน์ง• 4๊ฐ€์ง€ ์•”๊ธฐ ๋ฐ ๊ตฌ๋‘ ์„ค๋ช…

    ๐Ÿ’ก
    โ‘  ๊ด€๊ณ„ํ˜• DBMS์— ์ ‘๊ทผํ•˜๋Š” ์œ ์ผํ•œ ์–ธ์–ด

    โ‘ก ANSI/ISO-SQL

    • ๋ฏธ๊ตญ ์‚ฐ์—… ํ‘œ์ค€ํ™” ๊ธฐ๊ตฌ (ANSI: American National Standards Institute) Ex) SQL-86 , SQL-89, SQL:1999 , SQL:2008, SQL:2011, SQL:2016
    • SQL์˜ ๊ตญ์ œ ํ‘œ์ค€ ๋•๋ถ„์—, ์–ด๋– ํ•œ DBMS์—์„œ๋„ ํ†ต์šฉ์ ์œผ๋กœ ๊ฐ™์€ SQL ๋ฌธ๋ฒ•์œผ๋กœ ์‚ฌ์šฉ์ด ๊ฐ€๋Šฅํ•˜๋‹ค.

    โ‘ข English-Like

    • SQL ๋ช…๋ น์–ด์˜ ๋ฌธ๋ฒ•์ ์ธ ๊ตฌ์กฐ ๋ฐ ์˜๋ฏธ โ†’ ์˜์–ด์™€ ์œ ์‚ฌํ•œ ํŠน์ง•์ด ์žˆ๋‹ค.
    • SQL ๋ช…๋ น์–ด๋Š” ๋Œ€-์†Œ๋ฌธ์ž๋ฅผ ๊ตฌ๋ถ„ํ•˜์ง€ ์•Š๋Š”๋‹ค (Case-Insensitive)
    โ—
    ํ•˜์ง€๋งŒ, ์‚ฌ๋‚ด ํ‘œ์ค€ ๊ฐœ๋ฐœ์ง€์นจ๊ณผ, SQL ์ž‘์„ฑ ์ง€์นจ ์ปจ๋ฒค์…˜์„ ์ง€์ผœ์•ผ ํ•œ๋‹ค!

    โ‘ฃ ๋น„์ ˆ์ฐจ์  ์–ธ์–ด (Non-Procedural Language)

    • ๊ตฌ์กฐ์ (Structured) & ์ง‘ํ•ฉ์ (Set-based) & ์„ ์–ธ์ (Declarative)

    3. SQL ๋ช…๋ น์–ด ๋ถ„๋ฅ˜ํ‘œ ์•”๊ธฐ ๋ฐ ๊ตฌ๋‘ ์„ค๋ช…

    ๐Ÿ’ก
    1. DDL(Data Definition Language) - CREATE, ALTER, DROP, TRUNCATE 2. DCL(Data Control Language) - GRANT, REVOKE 3. TCL(Transaction Control Language) - COMMIT, ROLLBACK, SAVEPOINT 4. Query - SELECT 5. DML(Data Manipulation Language) - INSERT, UPDATE, DELETE, MERGE * MERGE = UPDATE + INSERT

    4. SELECT ๋ฌธ๋ฒ•๊ตฌ์กฐ๋ฅผ Oracle ๋ฉ”๋‰ด์–ผ์—์„œ ๊ฒ€์ƒ‰ํ›„ ๋ฌธ์„œ ์ •๋ฆฌ

    • select syntax diagram ํ•ด์„
      Database SQL Language Reference
      https://docs.oracle.com/database/121/SQLRF/statements_10002.htm#SQLRF01702
      ๐Ÿ’ก
      oracle select syntax 12c ๊ณต์‹ ๋ฌธ์„œ

      SELECT Syntax Diagram

      <๋„ํ˜•์˜ ์˜๋ฏธ>

      • ์‚ฌ๊ฐํ˜• : ๋ช…๋ น์–ด ๋˜๋Š” ํ‚ค์›Œ๋“œ์ด๋ฉฐ ๋Œ€๋ฌธ์ž๋กœ ํ‘œ๊ธฐ.
      • ํƒ€์›ํ˜• : ์ธ์ž (parameter)
      ๐Ÿ’ก
      select::= (SELECT ๋ฌธ์˜ ์‹œ์ž‘) 1. subquery ๋ฌธ ์ด๋™ 2. for_update_clause : ํŠธ๋žœ์žญ์…˜์ด ์™„๋ฃŒ๋  ๋•Œ๊นŒ์ง€ ๋‹ค๋ฅธ ํŠธ๋žœ์žญ์…˜์ด ํ•ด๋‹น ํ–‰์„ ์ˆ˜์ •ํ•˜์ง€ ๋ชปํ•˜๋„๋ก ํ•˜๊ธฐ ์œ„ํ•ด ํ…Œ์ด๋ธ”์˜ ํ–‰์„ ์ž ๊ทธ๋Š”๋ฐ ์‚ฌ์šฉ

      ๐Ÿ’ก
      subquery::= (์„œ๋ธŒ ์ฟผ๋ฆฌ ๋ฌธ์˜ ์‹œ์ž‘) 1. ์ฟผ๋ฆฌ ๋ธ”๋Ÿญ ์ด๋™ 2. ์„œ๋ธŒ ์ฟผ๋ฆฌ ๊ตฌ์กฐ 3. ๋งˆ์ง€๋ง‰ ์„œ๋ธŒ์ฟผ๋ฆฌ ๊ด„ํ˜ธ ์ฒ˜๋ฆฌ โ†’ ์ดํ›„ order_by, row_limiting ์„ ํƒ

      ๐Ÿ’ก
      query_block::= ์ผ๋ฐ˜์  ์ฟผ๋ฆฌ๋ฌธ ์‹œ์ž‘

      ๐Ÿ’ก
      select_list::= ์กฐํšŒํ•  ์ปฌ๋Ÿผ ๋‚˜์—ด

    5. EMP ํ…Œ์ด๋ธ”์—์„œ ์‚ฌ๋ฒˆ,์ด๋ฆ„,์ง๋ฌด,๊ธ‰์—ฌ,๋ถ€์„œ๋ฒˆํ˜ธ ๋ฐ์ดํ„ฐ ์‚ฌ์ด์— ๊ตฌ๋ถ„์ž,๋ฅผ ์‚ฝ์ž…ํ•˜๋Š” ์˜ˆ์ œ SQL ์ž‘์„ฑ

    • CSV(Comma-Separated Values) ํŒŒ์ผ์—์„œ ์‚ฌ์šฉํ• ์ˆ˜ ์žˆ๋Š” ๋ฐ์ดํ„ฐ ํฌ๋งท ์ถœ๋ ฅ
    SELECT EMPNO || ',' || ENAME || ',' || JOB || ',' || SAL || ',' || DEPTNO FROM EMP;

    6. SYS ๊ณ„์ •, SYSTEM ๊ณ„์ • ์ด๋ž€?

    • ๊ฐ„๋‹จํ•˜๊ฒŒ 1~2์ค„
    Oracle ServerLinux
    ๊ด€๋ฆฌ์žsys, system (DBA ์—ญํ• )root
    ์‚ฌ์šฉ์žscott, DA08~
    ๐Ÿ’ก
    ๋ชจ๋“  ์„œ๋ฒ„๋Š” ์ผ๋ฐ˜ ์‚ฌ์šฉ์ž์™€ ๊ด€๋ฆฌ์ž ๊ณ„์ •์œผ๋กœ ๊ถŒํ•œ์ด ๋‚˜๋ˆ„์–ด์ ธ ์žˆ๋‹ค.

    ๐Ÿ’ก
    Oracle DBMS์—์„œ ๋ฏธ๋ฆฌ ์ •์˜๋œ ๋‘ ๊ฐœ์˜ ๊ธฐ๋ณธ ๊ด€๋ฆฌ์ž ๊ณ„์ •์ด๋‹ค. 1. SYS ๊ณ„์ • - ์ตœ์ƒ์œ„ ๊ด€๋ฆฌ์ž ๊ณ„์ •์œผ๋กœ SYS ๊ณ„์ •์€ Oracle ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ๋‚ด๋ถ€ ๊ตฌ์กฐ์™€ ๋ฉ”ํƒ€๋ฐ์ดํ„ฐ์— ๋Œ€ํ•œ ์•ก์„ธ์Šค ๊ถŒํ•œ์„ ๊ฐ€์ง„ ๊ณ„์ •์œผ๋กœ, ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ์„ค์น˜ํ•˜๊ณ  ๊ตฌ์„ฑํ•˜๋Š” ๋ฐ ํ•„์š”ํ•œ ๋‹ค์–‘ํ•œ ์ž‘์—…์„ ์ˆ˜ํ–‰ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์ผ๋ฐ˜ ์‚ฌ์šฉ์ž์—๊ฒŒ๋Š” ์ ‘๊ทผํ•  ์ˆ˜ ์—†๋Š” ์‹œ์Šคํ…œ ๋ทฐ ๋ฐ ํ…Œ์ด๋ธ”์„ ํฌํ•จํ•˜์—ฌ, Oracle ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์— ํ•„์ˆ˜์ ์ธ ๊ฐ์ฒด๋ฅผ ์ƒ์„ฑํ•˜๊ณ  ์œ ์ง€๋ณด์ˆ˜ํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋ฉ๋‹ˆ๋‹ค. 2. SYSTEM ๊ณ„์ • - ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ๊ด€๋ฆฌ์ž ๊ณ„์ •์ด๋‹ค. SYSTEM ๊ณ„์ •์€ Oracle ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค์˜ ์ผ๋ฐ˜ ๊ด€๋ฆฌ ์ž‘์—…์— ์‚ฌ์šฉ๋˜๋Š” ๊ณ„์ •์œผ๋กœ, ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ์šด์˜ํ•˜๊ณ  ๊ด€๋ฆฌํ•˜๋Š” ๋ฐ ํ•„์š”ํ•œ ๊ถŒํ•œ์„ ๊ฐ€์ง€๊ณ  ์žˆ์Šต๋‹ˆ๋‹ค. ์‹œ์Šคํ…œ ๊ด€๋ฆฌ ์ž‘์—…, ๋ฐฑ์—… ๋ฐ ๋ณต๊ตฌ, ๊ถŒํ•œ ๊ด€๋ฆฌ ๋“ฑ์„ ์ˆ˜ํ–‰ํ•˜๋Š” ๋ฐ ์‚ฌ์šฉ๋ฉ๋‹ˆ๋‹ค. ์ผ๋ฐ˜์ ์œผ๋กœ, SYSTEM๊ณ„์ •์€ SYS๊ณ„์ •๊ณผ ์œ ์‚ฌํ•œ ๊ถŒํ•œ์„ ๊ฐ€์ง€์ง€๋งŒ, SYS ๊ณ„์ •๋ณด๋‹ค๋Š” ๋‚ฎ์€ ๊ถŒํ•œ ์ˆ˜์ค€์„ ๊ฐ–์Šต๋‹ˆ๋‹ค. ๋ณด์•ˆ์ƒ์˜ ์ด์œ ๋กœ, SYS์™€ SYSTEM ๊ณ„์ •์€ ์ผ๋ฐ˜์ ์œผ๋กœ ๊ฐœ๋ณ„ ์‚ฌ์šฉ์ž๊ฐ€ ์‚ฌ์šฉํ•˜๋Š” ์ผ๋ฐ˜ ๊ณ„์ •์œผ๋กœ๋Š” ์ ‘๊ทผํ•  ์ˆ˜ ์—†์Šต๋‹ˆ๋‹ค. ๋Œ€์‹ , ํŠน์ˆ˜ํ•œ ๊ถŒํ•œ์„ ๊ฐ€์ง„ ์‹œ์Šคํ…œ ๊ด€๋ฆฌ์ž๋“ค๋งŒ์ด ์ด๋Ÿฌํ•œ ๊ณ„์ •์— ์•ก์„ธ์Šคํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

    7. sysdate ํ•จ์ˆ˜์—์„œ ์‹œ๊ฐ„์ด ๋‚˜ํƒ€๋‚˜์ง€ ์•Š๋Š” ์ด์œ ?

    ๐Ÿ’ก
    sysdate๋Š” ๋‚ ์งœ์™€ ์‹œ๊ฐ„์„ ๋ชจ๋‘ ์ €์žฅ๋˜์–ด ์žˆ์ง€๋งŒ, ๊ธฐ๋ณธ ์ถœ๋ ฅ ํฌ๋ฉง์ด ๋‚ ์งœ๋งŒ์„ ๋ฆฌํ„ดํ•˜๋„๋ก ๋˜์–ด์žˆ๋‹ค.

    • ์‹œ์Šคํ…œ์˜ ํ˜„์žฌ ์‹œ๊ฐ„,๋ถ„,์ดˆ,1/100์ดˆ ํ‘œํ˜„ํ•˜๋Š” SQL ์ž‘์„ฑ
    ๐Ÿ’ก
    sysdate๋Š” ์ดˆ๊นŒ์ง€๋งŒ ํ‘œ๊ธฐ. ์ดˆ ์†Œ์ˆ˜์  ์ดํ•˜ systimestamp ์ด์šฉ
    ๐Ÿ’ก
    Oracle์—์„œ ์‹œ๊ฐ„(๋ถ„) ํ‘œ๊ธฐ ์‹œ, MI ์ž„์„ ์ฃผ์˜ - MM : ๋‘์ž๋ฆฌ ๊ฐ’์˜ ์›” - MI : ๋ถ„(0~59)
    SELECT TO_CHAR(systimestamp, 'HH24:MI:SS.FF2') FROM DUAL;

    • ์‹œ์Šคํ…œ์˜ ํ˜„์žฌ ์‹œ๊ฐ„,๋ถ„,์ดˆ,1/1000์ดˆ ํ‘œํ˜„ํ•˜๋Š” SQL ์ž‘์„ฑ
    SELECT TO_CHAR(systimestamp, 'HH24:MI:SS.FF3') FROM DUAL;



    Uploaded by N2T

    728x90
    ๋ฐ˜์‘ํ˜•

    ๋Œ“๊ธ€

Keydi's Tistory