ํ‹ฐ์Šคํ† ๋ฆฌ ๋ทฐ

#1์—์„œ๋Š” ํ•ต์‹ฌ์ ์ธ ํ‚ค์›Œ๋“œ๋ฅผ ์ •๋ฆฌํ•˜๊ณ 
#2์—์„œ๋Š” ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค SQL ๊ณ ๋“์  kit Level 3~4 ๋ฌธ์ œ ๋ช‡๊ฐœ๋ฅผ ๋‹ค๋ฃจ๋ฉด์„œ ์ž์ฃผ ์‚ฌ์šฉํ•˜๋Š” ํ•จ์ˆ˜๋“ค์„ ์ •๋ฆฌํ•ด๋ณด๋ ค๊ณ  ํ•œ๋‹ค.

๊ฐ„๋‹จํ•˜๊ฒŒ ํ‚ค์›Œ๋“œ๋“ค์„ ์ •๋ฆฌํ•˜๊ณ  ๋งˆ์ง€๋ง‰์— ์ด๋“ค์„ ์ ํ•ฉํ•˜๊ฒŒ ์‚ฌ์šฉํ•˜๋Š” ๋ฒ•์„ ์ •๋ฆฌํ•  ๊ฒƒ์ด๋‹ค.

ORDER BY 

SELECT *
FROM ํ…Œ์ด๋ธ”๋ช…
ORDER BY ์ปฌ๋Ÿผ๋ช… (ASC)

ํŠน์ • ์ปฌ๋Ÿผ์„ ๊ธฐ์ค€์œผ๋กœ ์ •๋ ฌ.
๊ธฐ๋ณธ๊ฐ’์€ ์˜ค๋ฆ„์ฐจ์ˆœ(ASC) ์ •๋ ฌ

SELECT *
FROM ํ…Œ์ด๋ธ”๋ช…
ORDER BY ์นผ๋Ÿผ1, ์นผ๋Ÿผ2 DESC

์œ„ ๋ฐฉ์‹์ฒ˜๋Ÿผ ์—ฌ๋Ÿฌ ์นผ๋Ÿผ์— ์ ์šฉํ•  ์ˆ˜๋„ ์žˆ๋‹ค.

 

GROUP BY, HAVING

SELECT id, COUNT(*)
FROM member
GROUP BY id
HAVING id > 3

ํŠน์ • ์นผ๋Ÿผ์„ ๊ธฐ์ค€์œผ๋กœ ๊ทธ๋ฃนํ™”ํ•œ๋‹ค.
์ฃผ๋กœ ์ง‘๊ณ„ํ•จ์ˆ˜(COUNT(), SUM())์™€ ํ•จ๊ป˜ ์‚ฌ์šฉํ•˜๋ฉด ์œ ์šฉํ•˜๊ณ , OUTER JOIN ์‹œ ํŠน์ • ๊ฐ’์ด NULL์ธ ์นผ๋Ÿผ์„ ์ฐพ๊ธฐ์— ์œ ์šฉํ•˜๋‹ค.

HAVING์„ ์ด์šฉํ•˜๋ฉด ๊ทธ๋ฃนํ™”ํ•˜๋Š” ๊ณผ์ •์—์„œ ํŠน์ • ์กฐ๊ฑด์„ ๊ฑธ ์ˆ˜ ์žˆ๋‹ค.
์œ„ ์ฟผ๋ฆฌ์—์„œ๋Š” id ๊ฐ’์ด 3๋ณด๋‹ค ํฐ ๋ฐ์ดํ„ฐ๋ฅผ ๋ถˆ๋Ÿฌ์˜ค๋Š” ๊ฒƒ์ฒ˜๋Ÿผ ์‚ฌ์šฉ๊ฐ€๋Šฅํ•˜๋‹ค.

์•„๋ž˜ ๋ฌธ์ œ๋Š” ์ด์— ์•„์ฃผ ์ ํ•ฉํ•œ ์˜ˆ์‹œ์ด๋‹ค.

programmers.co.kr/learn/courses/30/lessons/59412

 

์ฝ”๋”ฉํ…Œ์ŠคํŠธ ์—ฐ์Šต - ์ž…์–‘ ์‹œ๊ฐ ๊ตฌํ•˜๊ธฐ(1)

ANIMAL_OUTS ํ…Œ์ด๋ธ”์€ ๋™๋ฌผ ๋ณดํ˜ธ์†Œ์—์„œ ์ž…์–‘ ๋ณด๋‚ธ ๋™๋ฌผ์˜ ์ •๋ณด๋ฅผ ๋‹ด์€ ํ…Œ์ด๋ธ”์ž…๋‹ˆ๋‹ค. ANIMAL_OUTS ํ…Œ์ด๋ธ” ๊ตฌ์กฐ๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™์œผ๋ฉฐ, ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME๋Š” ๊ฐ๊ฐ ๋™๋ฌผ์˜ ์•„์ด๋””, ์ƒ๋ฌผ

programmers.co.kr

 

JOIN, ON

๋‹ค์ˆ˜ ํ…Œ์ด๋ธ” ์กฐํšŒ์— ์‚ฌ์šฉ๋œ๋‹ค. ์ฃผ๋กœ ์—ฌ๋Ÿฌ ํ…Œ์ด๋ธ” ๊ฐ„์˜ ๋น„๊ต(ํ•ฉ, ์ฐจ์ด)ํ•˜๋Š”๋ฐ ์œ ์šฉํ•˜๋‹ค.

SELECT *
FROM A
LEFT (OUTER) JOIN B
ON A.ID = B.ID
WHERE B.ID IS NULL

์œ„ ์ฟผ๋ฆฌ๋Š” A์—๋Š” ์žˆ์ง€๋งŒ, B์—๋Š” NULL์ธ ๊ฐ’๋“ค์„ ๊ฐ€์ ธ์˜จ๋‹ค. ์•„๋ž˜ ๋ฌธ์ œ๊ฐ€ ์•„์ฃผ ์ ํ•ฉํ•œ ์˜ˆ์‹œ์ด๋‹ค.
์ฐธ๊ณ ๋กœ OUTER๋Š” ๊ธฐ๋ณธ๊ฐ’์œผ๋กœ ๋“ค์–ด๊ฐ„๋‹ค.

LEFT, RIGHT๋Š” ์–ด๋Š์ชฝ ํ…Œ์ด๋ธ”์„ ๊ธฐ์ค€์œผ๋กœ ํ• ์ง€๋ฅผ ๊ฒฐ์ •ํ•œ๋‹ค.
์œ„ ์ฟผ๋ฆฌ์—์„  LEFT๋ฉด A, RIGHT๋ฉด B์ด๋‹ค. 
์ด๋ฅผ ์•„์ฃผ ์ž˜ ์„ค๋ช…ํ•ด์ฃผ๋Š” ๊ทธ๋ฆผ์„ ์•„๋ž˜์— ์ฒจ๋ถ€ํ–ˆ๋‹ค.

programmers.co.kr/learn/courses/30/lessons/59042

 

์ฝ”๋”ฉํ…Œ์ŠคํŠธ ์—ฐ์Šต - ์—†์–ด์ง„ ๊ธฐ๋ก ์ฐพ๊ธฐ

ANIMAL_INS ํ…Œ์ด๋ธ”์€ ๋™๋ฌผ ๋ณดํ˜ธ์†Œ์— ๋“ค์–ด์˜จ ๋™๋ฌผ์˜ ์ •๋ณด๋ฅผ ๋‹ด์€ ํ…Œ์ด๋ธ”์ž…๋‹ˆ๋‹ค. ANIMAL_INS ํ…Œ์ด๋ธ” ๊ตฌ์กฐ๋Š” ๋‹ค์Œ๊ณผ ๊ฐ™์œผ๋ฉฐ, ANIMAL_ID, ANIMAL_TYPE, DATETIME, INTAKE_CONDITION, NAME, SEX_UPON_INTAKE๋Š” ๊ฐ๊ฐ ๋™๋ฌผ์˜ ์•„์ด๋””

programmers.co.kr

 

https://imgur.com/gallery/8u7fc

โ—์ค‘์š”

ํ‚ค์›Œ๋“œ๋“ค์„ ์ •๋ฆฌํ–ˆ์ง€๋งŒ ์ด๋ฅผ ์ˆœ์„œ๋Œ€๋กœ ์ž˜ ์‚ฌ์šฉํ•˜๋Š” ์ „๋žต๋„ ์ค‘์š”ํ•˜๋‹ค.
์ด๋Š” ์ฟผ๋ฆฌ ์ž‘์„ฑ ๋ฌธ๋ฒ• ์ˆœ์„œ์™€ ์‹คํ–‰ ์ˆœ์„œ์— ๋”ฐ๋ผ ๊ฒฐ์ •๋œ๋‹ค.

์ฟผ๋ฆฌ ์ž‘์„ฑ ๋ฌธ๋ฒ• ์ˆœ์„œ

  1. SELECT
  2. FROM
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. ORDER BY

์‹คํ–‰ ์ˆœ์„œ

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY

ํŠน์ดํ•œ ์ ์€ SELECT์˜ ์ˆœ์„œ๊ฐ€ ๋ฐ”๋€๋‹ค๋Š” ๊ฒƒ์ด๋‹ค. ์ด๋Š” MEMBER AS M๊ณผ ๊ฐ™์ด ๋ณ„์นญ(Alias) ์‚ฌ์šฉ ์‹œ ์ฃผ์˜ํ•ด์•ผํ•˜๋Š”๋ฐ
์‹คํ–‰์ˆœ์„œ๊ฐ€ WHERE > SELECT ์ด๋ฏ€๋กœ SELECT M.NAME AS N FROM MEMBER M WHERE N = 'MOKHS' ์™€ ๊ฐ™์€ ์ฟผ๋ฆฌ๋Š” ์‚ฌ์šฉ ๋ถˆ๊ฐ€๋Šฅํ•˜๊ณ  ์‹คํ–‰ ์‹œ ์—๋Ÿฌ๋ฅผ ๋ฐœ์ƒ์‹œํ‚จ๋‹ค.

์ด ์ •๋„๋ฉด ๊ธฐ๋ณธ์ ์ธ ํ‹€์€ ์žกํžŒ ๊ฒƒ ๊ฐ™๊ณ , #2์—์„œ ๋””ํ…Œ์ผ์„ ์žก์•„๋ณด์ž.

๋ฐ˜์‘ํ˜•