DAY5

Markยท2022๋…„ 5์›” 10์ผ
0
post-thumbnail

๐Ÿ‘ฉ๐Ÿปโ€๐Ÿ’ป ํŒจ์ŠคํŠธ์บ ํผ์Šค ๊ตญ๋น„์ง€์›๊ณผ์ • 'SQL๋กœ ์‹œ์ž‘ํ•˜๋Š”
๋ฐ์ดํ„ฐ ๋ถ„์„ ์ฒซ๊ฑธ์Œ' ๊ฐ•์˜ ๋‚ด์šฉ ์ •๋ฆฌ๋ฅผ ๋ชฉ์ ์œผ๋กœ ๊ฐœ์ธ ๊ณต๋ถ€์ฐจ ์ž‘์„ฑํ•œ ๊ธ€์ž…๋‹ˆ๋‹ค.
โœ๏ธ ์ˆ˜๊ฐ• ์ค‘์ธ ๊ต์œก๊ณผ์ • : https://fastcampus.co.kr/b2g_kdc_sql

1. ๋ฐ์ดํ„ฐ ์ค„์„ธ์šฐ๊ธฐ (์ˆœ์„œ๋ฅผ ์ •ํ•ด ์›ํ•˜๋Š” ๋ฐ์ดํ„ฐ ๊ฐ€์ ธ์˜ค๊ธฐ)

ORDER BY

  • ๊ฐ€์ ธ์˜จ ๋ฐ์ดํ„ฐ๋ฅผ ์ •๋ ฌํ•ด์ฃผ๋Š” ํ‚ค์›Œ๋“œ
  • ORDER BY [์ปฌ๋Ÿผ ์ด๋ฆ„]
  • ์ž…๋ ฅํ•œ [์ปฌ๋Ÿผ ์ด๋ฆ„]์˜ ๊ฐ’์„ ๊ธฐ์ค€์œผ๋กœ row๋ฅผ ์ •๋ ฌ
  • ๊ธฐ๋ณธ ์ •๋ ฌ์€ ์˜ค๋ฆ„์ฐจ์ˆœ
  • ์˜ค๋ฆ„์ฐจ์ˆœ ์ •๋ ฌ : ORDER BY [์ปฌ๋Ÿผ ์ด๋ฆ„] = ORDER BY [์ปฌ๋Ÿผ ์ด๋ฆ„] ASC(์˜ค๋ฆ„์ฐจ์ˆœ)
  • ๋‚ด๋ฆผ์ฐจ์ˆœ ์ •๋ ฌ : ORDER BY [์ปฌ๋Ÿผ ์ด๋ฆ„] DESC
  • ์—ฌ๋Ÿฌ ์ปฌ๋Ÿผ ์ •๋ ฌ : ORDER BY [์ปฌ๋Ÿผ 1], [์ปฌ๋Ÿผ 2]
  • ์ปฌ๋Ÿผ ๋ฒˆํ˜ธ๋กœ๋„ ์ •๋ ฌ ๊ฐ€๋Šฅํ•จ (์ปฌ๋Ÿผ ๋ฒˆํ˜ธ๋Š” SELECT ์ ˆ์˜ ์ปฌ๋Ÿผ ์ด๋ฆ„์˜ ์ˆœ์„œ๋ฅผ ์˜๋ฏธํ•จ)
SELECT [์ปฌ๋Ÿผ ์ด๋ฆ„]
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]
WHERE ์กฐ๊ฑด์‹
ORDER BY [์ปฌ๋Ÿผ ์ด๋ฆ„] ASC;
SELECT [์ปฌ๋Ÿผ ์ด๋ฆ„]
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]
WHERE ์กฐ๊ฑด์‹
ORDER BY [์ปฌ๋Ÿผ ์ด๋ฆ„] DESC;
SELECT [์ปฌ๋Ÿผ1],[์ปฌ๋Ÿผ2], [์ปฌ๋Ÿผ3]
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]
WHERE ์กฐ๊ฑด์‹
ORDER BY 3 DESC, 4;
  • 3 : SELECT ์ ˆ์— 3๋ฒˆ์งธ ์ปฌ๋Ÿผ์„ ์˜๋ฏธํ•จ
  • 4 : SELECT ์ ˆ์— 4๋ฒˆ์งธ ์ปฌ๋Ÿผ์„ ์˜๋ฏธํ•จ

ย 

2. ๋ฐ์ดํ„ฐ ์ˆœ์œ„ ๋งŒ๋“ค๊ธฐ

RANK

  • ๋ฐ์ดํ„ฐ๋ฅผ ์ •๋ ฌํ•ด ์ˆœ์œ„๋ฅผ ๋งŒ๋“ค์–ด ์ฃผ๋Š” ํ•จ์ˆ˜
  • RANK() OVER (ORDER BY [์ปฌ๋Ÿผ ์ด๋ฆ„])
  • ํ•ญ์ƒ ORDER BY์™€ ํ•จ๊ป˜ ์‚ฌ์šฉ
  • ๊ณต๋™ ์ˆœ์œ„๊ฐ€ ์žˆ์œผ๋ฉด ๋‹ค์Œ ์ˆœ์„œ๋กœ ๊ฑด๋„ˆ ๋œ€
  • SELECT์ ˆ์— ์‚ฌ์šฉํ•˜๋ฉฐ ์ •๋ ฌ๋œ ์ˆœ์„œ์— ์ˆœ์œ„๋ฅผ ๋ถ™์ธ ์ƒˆ๋กœ์šด ์ปฌ๋Ÿผ์„ ๋งŒ๋“ฌ
    • ์‹ค์ œ ๋ฐ์ดํ„ฐ์—๋Š” ์˜ํ–ฅ์„ ์ฃผ์ง€ ์•Š์Œ
SELECT [์ปฌ๋Ÿผ ์ด๋ฆ„], RANK() OVER (ORDER BY [์ปฌ๋Ÿผ ์ด๋ฆ„] ASC OR DESC)
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]
WHERE ์กฐ๊ฑด์‹

DENSE_RANK

  • ๊ณต๋™ ์ˆœ์œ„๊ฐ€ ์žˆ์–ด๋„ ๋‹ค์Œ ์ˆœ์œ„๋ฅผ ๋›ฐ์–ด ๋„˜์ง€ ์•Š์Œ
SELECT [์ปฌ๋Ÿผ ์ด๋ฆ„], DENSE_RANK() OVER (ORDER BY [์ปฌ๋Ÿผ ์ด๋ฆ„] ASC OR DESC)
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]
WHERE ์กฐ๊ฑด์‹

ROW_NUMBER

  • ๊ณต๋™ ์ˆœ์œ„๋ฅผ ๋ฌด์‹œํ•จ (๊ฐ๊ฐ ์œ ์ผํ•œ ์ˆœ์œ„๋ฅผ ๊ฐ€์ง€๊ฒŒ ๋จ)
SELECT [์ปฌ๋Ÿผ ์ด๋ฆ„], ROW_NUMBER() OVER (ORDER BY [์ปฌ๋Ÿผ ์ด๋ฆ„] ASC OR DESC)
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]
WHERE ์กฐ๊ฑด์‹

ย 

3. ๋ฌธ์žํ˜• ๋ฐ์ดํ„ฐ ์ •๋ณตํ•˜๊ธฐ

ํ•จ์ˆ˜ ํŠน์ง•

  • ํ•จ์ˆ˜ ์ด๋ฆ„(ํ•จ์ˆ˜๋ฅผ ์ ์šฉํ•  ๊ฐ’ ๋˜๋Š” ์ปฌ๋Ÿผ ์ด๋ฆ„)
  • ๊ฒฐ๊ณผ ๊ฐ’์„ ์ƒˆ๋กœ์šด ์ปฌ๋Ÿผ์œผ๋กœ ๋ฐ˜ํ™˜

LOCATE ํ•จ์ˆ˜

  • ๋ฌธ์ž๊ฐ€ ์—ฌ๋Ÿฌ ๊ฐœ๋ผ๋ฉด ๊ฐ€์žฅ ๋จผ์ € ์ฐพ์€ ๋ฌธ์ž์˜ ์œ„์น˜๋ฅผ ๊ฐ€์ ธ์˜จ๋‹ค.
  • ์ฐพ๋Š” ๋ฌธ์ž๊ฐ€ ์—†์„ ๊ฒฝ์šฐ 0์„ ๊ฐ€์ ธ์˜จ๋‹ค.
SELECT [์ปฌ๋Ÿผ ์ด๋ฆ„] LOCATE('์ฐพ์„ ๋ฌธ์ž', ์ปฌ๋Ÿผ ์ด๋ฆ„)
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]

SUBSTRING ํ•จ์ˆ˜

  • ์ž…๋ ฅํ•œ ์ˆซ์ž๊ฐ€ ๋ฌธ์ž์—ด์˜ ๊ธธ์ด๋ณด๋‹ค ํฌ๋‹ค๋ฉด ์•„๋ฌด๊ฒƒ๋„ ๊ฐ€์ ธ์˜ค์ง€ ์•Š์Œ
SELECT [์ปฌ๋Ÿผ ์ด๋ฆ„] SUBSTRING(์ปฌ๋Ÿผ ์ด๋ฆ„, ๋ฐ˜ํ™˜์‹œํ‚ฌ ์‹œ์ž‘ ์ˆซ์ž) -- ์ปฌ๋Ÿผ ์ด๋ฆ„์—์„œ n๋ฒˆ์งธ ๋ฌธ์ž๋ถ€ํ„ฐ ๋ฐ˜ํ™˜
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]

RIGHT, LEFT ํ•จ์ˆ˜

SELECT [์ปฌ๋Ÿผ ์ด๋ฆ„] RIGHT(์ปฌ๋Ÿผ ์ด๋ฆ„, ์˜ค๋ฅธ์ชฝ์—์„œ ๊ฐ€์ ธ์˜ฌ ๋ฌธ์ž), LEFT(์ปฌ๋Ÿผ ์ด๋ฆ„, ์™ผ์ชฝ์—์„œ ๊ฐ€์ ธ์˜ฌ ๋ฌธ์ž) -- ์ปฌ๋Ÿผ ์ด๋ฆ„์—์„œ ์˜ค๋ฅธ์ชฝ/์™ผ์ชฝ์—์„œ n๋ฒˆ์งธ ๋ฌธ์ž๊นŒ์ง€ ๋ฐ˜ํ™˜
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]

UPPER, LOWER ํ•จ์ˆ˜

  • UPPER : ์„ค์ •ํ•œ ์ปฌ๋Ÿผ์˜ ๊ฐ’๋“ค์„ ๋Œ€๋ฌธ์ž๋กœ ๋ฐ”๊ฟ” ๋ฐ˜ํ™˜
  • LOWER : ์†Œ๋ฌธ์ž๋กœ ๋ฐ”๊ฟ” ๋ฐ˜ํ™˜
SELECT [์ปฌ๋Ÿผ ์ด๋ฆ„] UPPER(์ปฌ๋Ÿผ ์ด๋ฆ„), LOWER(์ปฌ๋Ÿผ ์ด๋ฆ„)
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]

LENGTH ํ•จ์ˆ˜

  • ๋ฌธ์ž์—ด์˜ ๊ธ€์ž์ˆ˜๋ฅผ ๋ฐ˜ํ™˜
SELECT [์ปฌ๋Ÿผ ์ด๋ฆ„] LENGTH(์ปฌ๋Ÿผ ์ด๋ฆ„)
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]

CONCAT ํ•จ์ˆ˜

  • ๋ฌธ์ž์—ด์„ ํ•ฉ์น˜๋Š” ํ•จ์ˆ˜
  • CONCAT(โ€ABCโ€, โ€œDEFโ€)
SELECT [์ปฌ๋Ÿผ ์ด๋ฆ„] CONCAT(LEFT('์ปฌ๋Ÿผ ์ด๋ฆ„' ,1), RIGHT('์ปฌ๋Ÿผ ์ด๋ฆ„', 1)) --์™ผ์ชฝ์—์„œ 1๊ธ€์ž, ์˜ค๋ฅธ์ชฝ์—์„œ 1๊ธ€์ž๋ฅผ ๊ฐ€์ ธ์˜ค๊ณ  2๋ฌธ์ž๋ฅผ ํ•ฉ์ณ์คŒ 
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]

REPLACE ํ•จ์ˆ˜

  • ๋ฌธ์ž๋ฅผ ๋‹ค๋ฅธ ๋ฌธ์ž๋กœ ๋ฐ”๊ฟˆ
SELECT [์ปฌ๋Ÿผ ์ด๋ฆ„] REPLACE('์ปฌ๋Ÿผ ์ด๋ฆ„', '','_') -- ๊ณต๋ฐฑ์„ '_' ๋ฌธ์ž๋กœ ๋ฐ”๊ฟ”์คŒ
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]

ย 

4. ์ˆซ์žํ˜• ๋ฐ์ดํ„ฐ ์ •๋ณตํ•˜๊ธฐ

ABS ํ•จ์ˆ˜

  • ์ˆซ์ž์˜ ์ ˆ๋Œ€๊ฐ’ ๋ฐ˜ํ™˜
  • ABS(์ˆซ์ž)
  • FLOAT ๋ฐ์ดํ„ฐ ํƒ€์ž…์€ ์ž…๋ ฅ ๊ฐ’์˜ ๊ทผ์‚ฌ์น˜๋ฅผ ์ €์žฅํ•œ๋‹ค.
SELECT [์ปฌ๋Ÿผ ์ด๋ฆ„] ABS(์ปฌ๋Ÿผ ์ด๋ฆ„) 
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]

CEILING, FLOOR ํ•จ์ˆ˜

  • CEILING(์ˆซ์ž) : ์ˆซ์ž๋ฅผ ์ •์ˆ˜๋กœ ์˜ฌ๋ฆผํ•ด์„œ ๋ฐ˜ํ™˜
  • FLOOR(์ˆซ์ž) : ์ˆซ์ž๋ฅผ ์ •์ˆ˜๋กœ ๋‚ด๋ฆผํ•ด์„œ ๋ฐ˜ํ™˜
SELECT [์ปฌ๋Ÿผ ์ด๋ฆ„] CEILING(์ปฌ๋Ÿผ ์ด๋ฆ„), FLOOR(์ปฌ๋Ÿผ ์ด๋ฆ„)
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]

ROUND, TRUNCATE ํ•จ์ˆ˜

  • ROUND(์ˆซ์ž, ์ž๋ฆฟ์ˆ˜) : ์ˆซ์ž๋ฅผ ์†Œ์ˆ˜์  ์ž๋ฆฟ์ˆ˜๊นŒ์ง€ ๋ฐ˜์˜ฌ๋ฆผํ•ด์„œ ๋ฐ˜ํ™˜
  • TRUNCATE(์ˆซ์ž, ์ž๋ฆฟ์ˆ˜) : ์ˆซ์ž๋ฅผ ์†Œ์ˆ˜์  ์ž๋ฆฟ์ˆ˜๊นŒ์ง€ ๋ฒ„๋ฆผํ•ด์„œ ๋ฐ˜ํ™˜
  • ์ž๋ฆฟ์ˆ˜์— 0์ž…๋ ฅ ์‹œ ์†Œ์ˆ˜์  ์—†์ด ์ •์ˆ˜๋งŒ ๋ฐ˜ํ™˜
  • ์ž๋ฆฟ์ˆ˜๋ฅผ ๋ฐ˜ํ™˜ ํ•˜์ง€ ์•Š์„ ๊ฒฝ์šฐ ์ž๋ฆฟ์ˆ˜ ๋ถ€๋ถ„์€ ์ƒ๋žตํ•ด๋„ ๋จ
SELECT [์ปฌ๋Ÿผ ์ด๋ฆ„] ROUND(์ปฌ๋Ÿผ ์ด๋ฆ„, ์ž๋ฆฟ์ˆ˜), TRUNCATE(์ปฌ๋Ÿผ ์ด๋ฆ„, ์ž๋ฆฟ์ˆ˜)
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]

POWER ํ•จ์ˆ˜

  • POWER(์ˆซ์žA,์ˆซ์žB) : ์ˆซ์žA์˜ ์ˆซ์žB ์ œ๊ณฑ ๋ฐ˜ํ™˜
SELECT [์ปฌ๋Ÿผ ์ด๋ฆ„] POWER(์ปฌ๋Ÿผ ์ด๋ฆ„, 2) -- ์ปฌ๋Ÿผ ์ด๋ฆ„์— ํ•ด๋‹นํ•˜๋Š” ๊ฐ’(์ˆซ์ž)์— ์ œ๊ณฑํ•˜์—ฌ ๋ฐ˜ํ™˜ 
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]

MOD ํ•จ์ˆ˜

  • MOD(์ˆซ์žA,์ˆซ์žB) : ์ˆซ์žA๋ฅผ ์ˆซ์žB๋กœ ๋‚˜๋ˆˆ ๋‚˜๋จธ์ง€ ๋ฐ˜ํ™˜
SELECT [์ปฌ๋Ÿผ ์ด๋ฆ„] MOD(์ปฌ๋Ÿผ ์ด๋ฆ„, 2) -- ์ปฌ๋Ÿผ ์ด๋ฆ„์— ํ•ด๋‹นํ•˜๋Š” ๊ฐ’(์ˆซ์ž)์— 2๋กœ ๋‚˜๋ˆˆ ๋‚˜๋จธ์ง€๋ฅผ ๋ฐ˜ํ™˜
FROM [ํ…Œ์ด๋ธ” ์ด๋ฆ„]

ย 

5. ๋‚ ์งœํ˜• ๋ฐ์ดํ„ฐ ์ •๋ณตํ•˜๊ธฐ

NOW, CURRENT_DATE, CURRENT_TIME ํ•จ์ˆ˜

  • ์ด ํ•จ์ˆ˜๋“ค์€ ์ž…๋ ฅ๊ฐ’์ด ํ•„์š” ์—†์Œ
  • NOW() : ํ˜„์žฌ ๋‚ ์งœ์™€ ์‹œ๊ฐ„ ๋ฐ˜ํ™˜
  • CURRENT_DATE() : ํ˜„์žฌ ๋‚ ์งœ ๋ฐ˜ํ™˜
  • CURRENT_TIME() : ํ˜„์žฌ ์‹œ๊ฐ„ ๋ฐ˜ํ™˜
SELECT NOW(), CURRENT_DATE(), CURRENT_TIME();

YEAR, MONTH, MONTHNAME ํ•จ์ˆ˜

  • YEAR(๋‚ ์งœ) : ๋‚ ์งœ์˜ ์—ฐ๋„ ๋ฐ˜ํ™˜
  • MONTH(๋‚ ์งœ) : ๋‚ ์งœ์˜ ์›”์„ ์ˆซ์ž๋กœ ๋ฐ˜ํ™˜
  • MONTHNAME(๋‚ ์งœ) : ๋‚ ์งœ์˜ ์›”์„ ์˜์–ด๋กœ ๋ฐ˜ํ™˜
SELECT NOW(),YEAR(NOW()),MONTH(NOW()),MONTHNAME(NOW());

DAYNAME, DAYOFMONTH, DAYOFWEEK, WEEK ํ•จ์ˆ˜

  • DAYNAME(๋‚ ์งœ) : ๋‚ ์งœ์˜ ์š”์ผ์„ ์˜์–ด๋กœ ๋ฐ˜ํ™˜
  • DAYOFMONTH(๋‚ ์งœ) : ๋‚ ์งœ์˜ ์ผ์„ ๋ฐ˜ํ™˜
  • DAYOFWEEK(๋‚ ์งœ): ๋‚ ์งœ์˜ ์š”์ผ์„ ์ˆซ์ž๋กœ ๋ฐ˜ํ™˜
  • WEEK(๋‚ ์งœ) : ๋‚ ์งœ๊ฐ€ ํ•ด๋‹น ์—ฐ๋„์— ๋ช‡ ๋ฒˆ์งธ ์ฃผ์ธ์ง€ ๋ฐ˜ํ™˜
SELECT NOW(),DAYNAME(NOW()),DAYOFMONTH(NOW()),DAYOFWEEK(NOW()),WEEK(NOW());

HOUR, MINUTE, SECOND ํ•จ์ˆ˜

  • HOUR(๋‚ ์งœ) : ์‹œ๊ฐ„์˜ ์‹œ ๋ฐ˜ํ™˜
  • MINUTE(๋‚ ์งœ) : ์‹œ๊ฐ„์˜ ๋ถ„ ๋ฐ˜ํ™˜
  • SECOND(๋‚ ์งœ) : ์‹œ๊ฐ„์˜ ์ดˆ ๋ฐ˜ํ™˜
SELECT NOW(),HOUR(NOW()),MINUTE(NOW()),SECOND(NOW());

DATE_FORMAT ํ•จ์ˆ˜

  • DATEFORMAT(๋‚ ์งœ/์‹œ๊ฐ„, ํ˜•์‹) : ๋‚ 
SELECT DATE_FORMAT('1990-01-01 00:00:00', '%Y๋…„ %m์›” %d์ผ %H์‹œ %i๋ถ„ %s์ดˆ);

DATEDIFF, TIMEDIFF ํ•จ์ˆ˜

  • DATEDIFF(๋‚ ์งœ1, ๋‚ ์งœ2) : ๋‚ ์งœ1๊ณผ ๋‚ ์งœ2์˜ ์ฐจ์ด ๋ฐ˜ํ™˜
  • TIMEDIFF(์‹œ๊ฐ„1, ์‹œ๊ฐ„2) : ์‹œ๊ฐ„1๊ณผ ์‹œ๊ฐ„2์˜ ์ฐจ์ด ๋ฐ˜ํ™˜
SELECT DATEDIFF('1990-01-01 00:00:00', '1990-01-01 00:00:00'),
			TIMEDIFF('1990-01-01 00:00:00', '1990-01-01 00:00:00');
profile
๊ฐœ์ธ ๊ณต๋ถ€ ์ •๋ฆฌ

0๊ฐœ์˜ ๋Œ“๊ธ€