๐Ÿ’ป ๊ณต๋ถ€ (IT)/๐Ÿ‘ฉ๐Ÿป‍๐Ÿ’ป ์ฝ”๋”ฉ

[MySQL] Level 2 ๋ฌธ์ œ

zoo-it 2021. 4. 1. 00:58

 

 

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

 

NAME TYPE NULLABLE
ANIMAL_ID VARCHAR(N) FALSE
ANIMAL_TYPE VARCHAR(N) FALSE
DATETIME DATETIME FALSE
INTAKE_CONDITION VARCHAR(N) FALSE
NAME VARCHAR(N) TRUE
SEX_UPON_INTAKE VARCHAR(N) FALSE

 

1) ์ตœ์†Ÿ๊ฐ’ ๊ตฌํ•˜๊ธฐ

๋™๋ฌผ ๋ณดํ˜ธ์†Œ์— ๊ฐ€์žฅ ๋จผ์ € ๋“ค์–ด์˜จ ๋™๋ฌผ์€ ์–ธ์ œ ๋“ค์–ด์™”๋Š”์ง€ ์กฐํšŒํ•˜๋Š” SQL๋ฌธ์„ ์ž‘์„ฑํ•ด์ฃผ์„ธ์š”.
SELECT datetime
  FROM animal_ins
 ORDER BY datetime
 LIMIT 1;

๋‚ ์งœ์ˆœ์œผ๋กœ ์˜ค๋ฆ„์ฐจ์ˆœ ์ •๋ ฌํ•˜๋ฉด ๊ฐ€์žฅ ์˜ค๋ž˜๋œ ๋‚ ์งœ๋ถ€ํ„ฐ ๋ฐ์ดํ„ฐ๊ฐ€ ์ถœ๋ ฅ๋จ

๊ทธ ๋ฐ์ดํ„ฐ ์ค‘ ๊ฐ€์žฅ ์ฒซ๋ฒˆ์งธ ๋ฐ์ดํ„ฐ๋ฅผ ์ถœ๋ ฅํ•˜์—ฌ ๊ฒฐ๊ณผ ๋„์ถœ !

 

 

2) ๋™๋ฌผ ์ˆ˜ ๊ตฌํ•˜๊ธฐ

๋™๋ฌผ ๋ณดํ˜ธ์†Œ์— ๋™๋ฌผ์ด ๋ช‡ ๋งˆ๋ฆฌ ๋“ค์–ด์™”๋Š”์ง€ ์กฐํšŒํ•˜๋Š” SQL๋ฌธ์„ ์ž‘์„ฑํ•ด์ฃผ์„ธ์š”.
SELECT COUNT(*) AS count
  FROM animal_ins;

 

 

3) ์ค‘๋ณต ์ œ๊ฑฐํ•˜๊ธฐ

๋™๋ฌผ ๋ณดํ˜ธ์†Œ์— ๋“ค์–ด์˜จ ๋™๋ฌผ์˜ ์ด๋ฆ„์€ ๋ช‡ ๊ฐœ์ธ์ง€ ์กฐํšŒํ•˜๋Š” SQL๋ฌธ์„ ์ž‘์„ฑํ•ด์ฃผ์„ธ์š”.
์ด๋•Œ ์ด๋ฆ„์ด NULL์ธ ๊ฒฝ์šฐ๋Š” ์ง‘๊ณ„ํ•˜์ง€ ์•Š์œผ๋ฉฐ ์ค‘๋ณต๋˜๋Š” ์ด๋ฆ„์€ ํ•˜๋‚˜๋กœ ์นฉ๋‹ˆ๋‹ค.
SELECT COUNT(DISTINCT(name)) AS count
  FROM animal_ins
 WHERE name IS NOT NULL;

์ด๋ฆ„์ด NULL์ธ ๊ฒฝ์šฐ๋ฅผ ์ œ์™ธํ•˜๊ณ  DISTINCT ์ ˆ์„ ์ด์šฉํ•˜์—ฌ ์ค‘๋ณต ์ œ๊ฑฐ

 

 

 

4) ๊ณ ์–‘์ด์™€ ๊ฐœ๋Š” ๋ช‡ ๋งˆ๋ฆฌ ์žˆ์„๊นŒ

๋™๋ฌผ ๋ณดํ˜ธ์†Œ์— ๋“ค์–ด์˜จ ๋™๋ฌผ ์ค‘ ๊ณ ์–‘์ด์™€ ๊ฐœ๊ฐ€ ๊ฐ๊ฐ ๋ช‡ ๋งˆ๋ฆฌ์ธ์ง€ ์กฐํšŒํ•˜๋Š” SQL๋ฌธ์„ ์ž‘์„ฑํ•ด์ฃผ์„ธ์š”.
์ด๋•Œ ๊ณ ์–‘์ด๋ฅผ ๊ฐœ๋ณด๋‹ค ๋จผ์ € ์กฐํšŒํ•ด์ฃผ์„ธ์š”.
SELECT animal_type
     , COUNT(*) AS count
  FROM animal_ins
 GROUP BY animal_type
 ORDER BY animal_type;

๋™๋ฌผ ์ข…(animal_type) ๋ณ„๋กœ ๊ทธ๋ฃนํ™” ํ•ด์ค€ ํ›„,

์˜ค๋ฆ„์ฐจ์ˆœ ์ •๋ ฌ ์กฐ๊ฑด์„ ์ด์šฉํ•˜์—ฌ ๊ณ ์–‘์ด๊ฐ€ ๊ฐ•์•„์ง€๋ณด๋‹ค ๋จผ์ € ์ถœ๋ ฅ๋˜๊ฒŒ ํ•จ ('c'at → 'd'og)

 

 

 

5) NULL ์ฒ˜๋ฆฌํ•˜๊ธฐ

์ž…์–‘ ๊ฒŒ์‹œํŒ์— ๋™๋ฌผ ์ •๋ณด๋ฅผ ๊ฒŒ์‹œํ•˜๋ ค ํ•ฉ๋‹ˆ๋‹ค.
๋™๋ฌผ์˜ ์ƒ๋ฌผ ์ข…, ์ด๋ฆ„, ์„ฑ๋ณ„ ๋ฐ ์ค‘์„ฑํ™” ์—ฌ๋ถ€๋ฅผ ์•„์ด๋”” ์ˆœ์œผ๋กœ ์กฐํšŒํ•˜๋Š” SQL๋ฌธ์„ ์ž‘์„ฑํ•ด์ฃผ์„ธ์š”.
์ด๋•Œ ํ”„๋กœ๊ทธ๋ž˜๋ฐ์„ ๋ชจ๋ฅด๋Š” ์‚ฌ๋žŒ๋“ค์€ NULL์ด๋ผ๋Š” ๊ธฐํ˜ธ๋ฅผ ๋ชจ๋ฅด๊ธฐ ๋•Œ๋ฌธ์—
์ด๋ฆ„์ด ์—†๋Š” ๋™๋ฌผ์˜ ์ด๋ฆ„์€ "No name"์œผ๋กœ ํ‘œ์‹œํ•ด ์ฃผ์„ธ์š”.
SELECT animal_type
     , IFNULL(name, 'No name') AS name
     , sex_upon_intake 
  FROM animal_ins
 ORDER BY animal_id;

MySQL์—์„œ ์ปฌ๋Ÿผ์˜ ๊ฐ’์ด NULL์ธ ๊ฒฝ์šฐ๋ฅผ ์ฒ˜๋ฆฌํ•ด์ฃผ๋Š” ํ•จ์ˆ˜์—๋Š”

IFNULL, COALESCE ๋“ฑ์˜ ํ•จ์ˆ˜๊ฐ€ ์กด์žฌํ•จ

 

์ด ๋ฌธ์ œ์—์„œ๋Š” ์ด๋ฆ„ ์ปฌ๋Ÿผ์ด NULL ๊ฐ’์ผ ๊ฒฝ์šฐ์—

๋‹ค๋ฅธ ๋ฌธ์ž์—ด๋กœ ์น˜ํ™˜ํ•ด์ฃผ์–ด์•ผํ•˜๋ฏ€๋กœ INFULL ํ•จ์ˆ˜ ์‚ฌ์šฉ !

 

  • IFNULL : ํ•ด๋‹น ์ปฌ๋Ÿผ์˜ ๊ฐ’์ด NULL์ผ ๋•Œ, ๋‹ค๋ฅธ ๊ฐ’์œผ๋กœ ์ถœ๋ ฅํ•ด์ฃผ๋Š” ํ•จ์ˆ˜

SELECT IFNULL(์ปฌ๋Ÿผ๋ช…, "NULL์ผ ๊ฒฝ์šฐ ๋Œ€์ฒด ๊ฐ’" FROM ํ…Œ์ด๋ธ”๋ช…;

 

  • COALESCE : ์ง€์ •ํ•œ ํ‘œํ˜„์‹๋“ค ์ค‘ NULL์ด ์•„๋‹Œ ์ฒซ ๋ฒˆ์งธ ๊ฐ’์„ ๋ฐ˜ํ™˜ํ•˜๋Š” ํ•จ์ˆ˜

SELECT COALESCE(์ปฌ๋Ÿผ๋ช…1, ์ปฌ๋Ÿผ๋ช…1์ด NULL์ธ ๊ฒฝ์šฐ ๋Œ€์ฒด ๊ฐ’) FROM ํ…Œ์ด๋ธ”๋ช…;

       ์ด ๋•Œ, ํ•˜๋‚˜์˜ ํ•„๋“œ ๊ฐ’ ์™ธ์—๋„ ์—ฌ๋Ÿฌ ์ปฌ๋Ÿผ์„ ์ง€์ •ํ•˜์—ฌ ์—ฐ์†์ ์œผ๋กœ๋„ ํ™œ์šฉํ•  ์ˆ˜ ์žˆ์Œ

SELECT COALESCE(์ปฌ๋Ÿผ๋ช…1, ์ปฌ๋Ÿผ๋ช…2, ์ปฌ๋Ÿผ๋ช…3) FROM ํ…Œ์ด๋ธ”๋ช…;

 

 

 

6) ๋™๋ช… ๋™๋ฌผ ์ˆ˜ ์ฐพ๊ธฐ

๋™๋ฌผ ๋ณดํ˜ธ์†Œ์— ๋“ค์–ด์˜จ ๋™๋ฌผ ์ด๋ฆ„ ์ค‘ ๋‘ ๋ฒˆ ์ด์ƒ ์“ฐ์ธ ์ด๋ฆ„๊ณผ ํ•ด๋‹น ์ด๋ฆ„์ด ์“ฐ์ธ ํšŸ์ˆ˜๋ฅผ ์กฐํšŒํ•˜๋Š” SQL๋ฌธ์„ ์ž‘์„ฑํ•ด์ฃผ์„ธ์š”.
์ด๋•Œ ๊ฒฐ๊ณผ๋Š” ์ด๋ฆ„์ด ์—†๋Š” ๋™๋ฌผ์€ ์ง‘๊ณ„์—์„œ ์ œ์™ธํ•˜๋ฉฐ, ๊ฒฐ๊ณผ๋Š” ์ด๋ฆ„ ์ˆœ์œผ๋กœ ์กฐํšŒํ•ด์ฃผ์„ธ์š”.
SELECT name
     , COUNT(*) AS count
  FROM animal_ins
 WHERE name IS NOT NULL
 GROUP BY name
HAVING COUNT(name) >= 2
 ORDER BY name;

์ด๋ฆ„์ด ์กด์žฌํ•˜๋Š” ๋™๋ฌผ์— ๋Œ€ํ•ด์„œ (WHERE name IS NOT NULL)

์ด๋ฆ„๋ณ„ ๊ทธ๋ฃน์„ ์ง€์–ด์ค€ ๋’ค ์ด๋ฆ„๊ณผ ์นด์šดํŠธ ์ถœ๋ ฅ !

 

์ด๋•Œ GROUP BY์— ํ•ด๋‹นํ•˜๋Š” ์กฐ๊ฑด์„ ์ฃผ๊ธฐ ์œ„ํ•ด์„œ WHERE ์ ˆ์ด ์•„๋‹Œ HAVING ์ ˆ ์‚ฌ์šฉ !

 

  • WHERE   vs   HAVING : WHERE ์ ˆ์—์„œ๋Š” ์ง‘๊ณ„ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•  ์ˆ˜ ์—†์ง€๋งŒ HAVING ์ ˆ์—์„œ๋Š” ์‚ฌ์šฉ ๊ฐ€๋Šฅ

 

 

7) ์ž…์–‘ ์‹œ๊ฐ ๊ตฌํ•˜๊ธฐ

๋ณดํ˜ธ์†Œ์—์„œ๋Š” ๋ช‡ ์‹œ์— ์ž…์–‘์ด ๊ฐ€์žฅ ํ™œ๋ฐœํ•˜๊ฒŒ ์ผ์–ด๋‚˜๋Š”์ง€ ์•Œ์•„๋ณด๋ ค ํ•ฉ๋‹ˆ๋‹ค.
09:00๋ถ€ํ„ฐ 19:59๊นŒ์ง€, ๊ฐ ์‹œ๊ฐ„๋ณ„๋กœ ์ž…์–‘์ด ๋ช‡ ๊ฑด์ด๋‚˜ ๋ฐœ์ƒํ–ˆ๋Š”์ง€ ์กฐํšŒํ•˜๋Š” SQL๋ฌธ์„ ์ž‘์„ฑํ•ด์ฃผ์„ธ์š”.
์ด๋•Œ ๊ฒฐ๊ณผ๋Š” ์‹œ๊ฐ„๋Œ€ ์ˆœ์œผ๋กœ ์ •๋ ฌํ•ด์•ผํ•ฉ๋‹ˆ๋‹ค.
SELECT HOUR(datetime)
     , COUNT(*) AS count
  FROM animal_outs
 WHERE HOUR(datetime) >= 9
   AND HOUR(datetime) <= 19
 GROUP BY HOUR(datetime) 
 ORDER BY HOUR(datetime);โ€‹

 

๋‚ ์งœ ๋ฐ์ดํ„ฐ์—์„œ ์‹œ๊ฐ„ ๋ฐ์ดํ„ฐ๋งŒ ์ถ”์ถœํ•˜๊ธฐ ์œ„ํ•ด HOUR ํ•จ์ˆ˜ ์‚ฌ์šฉ !

 

  • YEAR(๋‚ ์งœ) : ์—ฐ๋„ ์ถ”์ถœ

  • MONTH(๋‚ ์งœ) : ์›” ์ถ”์ถœ

  • DAY(๋‚ ์งœ) : ์ผ ์ถ”์ถœ

  • HOUR(๋‚ ์งœ) : ์‹œ๊ฐ„ ์ถ”์ถœ

  • MINUTE(๋‚ ์งœ) : ๋ถ„ ์ถ”์ถœ

  • SECOND(๋‚ ์งœ) : ์ดˆ ์ถ”์ถœ

 

 

8) ๋ฃจ์‹œ์™€ ์—˜๋ผ ์ฐพ๊ธฐ

๋™๋ฌผ ๋ณดํ˜ธ์†Œ์— ๋“ค์–ด์˜จ ๋™๋ฌผ ์ค‘ ์ด๋ฆ„์ด Lucy, Ella, Pickle, Sabrina, Mitty์ธ
๋™๋ฌผ์˜ ์•„์ด๋””์™€ ์ด๋ฆ„, ์„ฑ๋ณ„ ๋ฐ ์ค‘์„ฑํ™” ์—ฌ๋ถ€๋ฅผ ์กฐํšŒํ•˜๋Š” SQL๋ฌธ์„ ์ž‘์„ฑํ•ด์ฃผ์„ธ์š”.

์ด๋•Œ ๊ฒฐ๊ณผ๋Š” ์•„์ด๋”” ์ˆœ์œผ๋กœ ์กฐํšŒํ•ด์ฃผ์„ธ์š”.
SELECT animal_id
     , name
     , sex_upon_intake
  FROM animal_ins
 WHERE name IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
 ORDER BY animal_id;

์—ฌ๋Ÿฌ ๊ฐ’์„ ํฌํ•จํ•ด์•ผ ํ•˜๊ธฐ ๋•Œ๋ฌธ์—

OR ์ ˆ์„ ์‚ฌ์šฉํ•˜์ง€ ์•Š๊ณ  IN ์—ฐ์‚ฐ์ž๋ฅผ ์‚ฌ์šฉํ•ด ์กฐ๊ฑด์„ ๋ถ€์—ฌ -

 

 

 

9) ์ด๋ฆ„์— el์ด ๋“ค์–ด๊ฐ€๋Š” ๋™๋ฌผ ์ฐพ๊ธฐ

๋ณดํ˜ธ์†Œ์— ๋Œ์•„๊ฐ€์‹  ํ• ๋จธ๋‹ˆ๊ฐ€ ๊ธฐ๋ฅด๋˜ ๊ฐœ๋ฅผ ์ฐพ๋Š” ์‚ฌ๋žŒ์ด ์ฐพ์•„์™”์Šต๋‹ˆ๋‹ค.
์ด ์‚ฌ๋žŒ์ด ๋งํ•˜๊ธธ ํ• ๋จธ๋‹ˆ๊ฐ€ ๊ธฐ๋ฅด๋˜ ๊ฐœ ์ด๋ฆ„์— 'el'์ด ๋“ค์–ด๊ฐ„๋‹ค๊ณ  ํ•ฉ๋‹ˆ๋‹ค.
๋™๋ฌผ ๋ณดํ˜ธ์†Œ์— ๋“ค์–ด์˜จ ๋™๋ฌผ ์ด๋ฆ„ ์ค‘, ์ด๋ฆ„์— 'EL'์ด ๋“ค์–ด๊ฐ€๋Š” ๊ฐœ์˜ ์•„์ด๋””์™€ ์ด๋ฆ„์„ ์กฐํšŒํ•˜๋Š” SQL๋ฌธ์„ ์ž‘์„ฑํ•ด์ฃผ์„ธ์š”.
์ด๋•Œ ๊ฒฐ๊ณผ๋Š” ์ด๋ฆ„ ์ˆœ์œผ๋กœ ์กฐํšŒํ•ด์ฃผ์„ธ์š”.
๋‹จ, ์ด๋ฆ„์˜ ๋Œ€์†Œ๋ฌธ์ž๋Š” ๊ตฌ๋ถ„ํ•˜์ง€ ์•Š์Šต๋‹ˆ๋‹ค.
SELECT animal_id
     , name
  FROM animal_ins
 WHERE name LIKE '%EL%'
   AND animal_type = 'Dog'
 ORDER BY name;

 

 

10) ์ค‘์„ฑํ™” ์—ฌ๋ถ€ ํŒŒ์•…ํ•˜๊ธฐ

๋ณดํ˜ธ์†Œ์˜ ๋™๋ฌผ์ด ์ค‘์„ฑํ™”๋˜์—ˆ๋Š”์ง€ ์•„๋‹Œ์ง€ ํŒŒ์•…ํ•˜๋ ค ํ•ฉ๋‹ˆ๋‹ค.
์ค‘์„ฑํ™”๋œ ๋™๋ฌผ์€ SEX_UPON_INTAKE ์ปฌ๋Ÿผ์— 'Neutered' ๋˜๋Š” 'Sprayed'๋ผ๋Š” ๋‹จ์–ด๊ฐ€ ๋“ค์–ด์žˆ์Šต๋‹ˆ๋‹ค.
๋™๋ฌผ์˜ ์•„์ด๋””์™€ ์ด๋ฆ„, ์ค‘์„ฑํ™” ์—ฌ๋ถ€๋ฅผ ์•„์ด๋”” ์ˆœ์œผ๋กœ ์กฐํšŒํ•˜๋Š” SQL๋ฌธ์„ ์ž‘์„ฑํ•ด์ฃผ์„ธ์š”.
์ด๋•Œ ์ค‘์„ฑํ™”๊ฐ€ ๋˜์–ด์žˆ๋‹ค๋ฉด 'O', ์•„๋‹ˆ๋ผ๋ฉด 'X'๋ผ๊ณ  ํ‘œ์‹œํ•ด์ฃผ์„ธ์š”.
SELECT animal_id
     , name
     , CASE
        WHEN sex_upon_intake LIKE '%Neutered%'
          OR sex_upon_intake LIKE '%Spayed%'
        THEN 'O'
        ELSE 'X'
       END AS ์ค‘์„ฑํ™”
  FROM animal_ins
 ORDER BY animal_id;

๋‹ค์ค‘ ์กฐ๊ฑด์„ ์‚ฌ์šฉํ•˜๊ธฐ ์œ„ํ•ด CASE WHEN Function ์‚ฌ์šฉ !

 

 

 

11) DATETIME์—์„œ DATE๋กœ ํ˜• ๋ณ€ํ™˜

ANIMAL_INS ํ…Œ์ด๋ธ”์— ๋“ฑ๋ก๋œ ๋ชจ๋“  ๋ ˆ์ฝ”๋“œ์— ๋Œ€ํ•ด, ๊ฐ ๋™๋ฌผ์˜ ์•„์ด๋””์™€ ์ด๋ฆ„, ๋“ค์–ด์˜จ ๋‚ ์งœ๋ฅผ ์กฐํšŒํ•˜๋Š” SQL๋ฌธ์„ ์ž‘์„ฑํ•ด์ฃผ์„ธ์š”.
์ด๋•Œ ๊ฒฐ๊ณผ๋Š” ์•„์ด๋”” ์ˆœ์œผ๋กœ ์กฐํšŒํ•ด์•ผ ํ•ฉ๋‹ˆ๋‹ค.
SELECT animal_id
     , name
     , DATE_FORMAT(datetime, '%Y-%m-%d') AS ๋‚ ์งœ
  FROM animal_ins
 ORDER BY animal_id;

MySQL์—์„œ๋Š” DATE_FORMAT์„ ์‚ฌ์šฉํ•˜๋ฉด ๋‚ ์งœ ํ˜•์‹์„ ์„ค์ •ํ•  ์ˆ˜ ์žˆ์Œ

 

  • DATE_FORMAT(๋‚ ์งœ, ํ˜•์‹) : ๋‚ ์งœ๋ฅผ ์ง€์ •ํ•œ ํ˜•์‹์œผ๋กœ ์ถœ๋ ฅ

๊ตฌ๋ถ„๊ธฐํ˜ธ ์—ญํ•  ๊ตฌ๋ถ„๊ธฐํ˜ธ ์—ญํ• 
%Y 4์ž๋ฆฌ ๋…„๋„ %m ์ˆซ์ž ์›” (๋‘์ž๋ฆฌ)
%y 2์ž๋ฆฌ ๋…„๋„ %c ์ˆซ์ž ์›” (ํ•œ์ž๋ฆฌ๋Š” ํ•œ์ž๋ฆฌ)
%M ๊ธด ์›” (์˜๋ฌธ) %d ์ผ์ž (๋‘์ž๋ฆฌ)
%b ์งง์€ ์›” (์˜๋ฌธ) %e ์ผ์ž (ํ•œ์ž๋ฆฌ๋Š” ํ•œ์ž๋ฆฌ)
%W ๊ธด ์š”์ผ (์˜๋ฌธ) %l ์‹œ๊ฐ„ (12์‹œ๊ฐ„)
%a ์งง์€ ์š”์ผ (์˜๋ฌธ) %H ์‹œ๊ฐ„ (24์‹œ๊ฐ„)
%i ๋ถ„ %r hh:mm:ss AM, PM
%T hh:mm:SS %S ์ดˆ