Banner

My Tech Blog (๋ถ„๋ฅ˜ ์ „์ฒด๋ณด๊ธฐ)

์˜ค๋Š˜์˜ ๋ช…์–ธ
" ์ธ์ƒ์ด๋ž€ ๊ฒฐ์ฝ” ๊ณตํ‰ํ•˜์ง€ ์•Š๋‹ค. ์ด ์‚ฌ์‹ค์— ์ต์ˆ™ํ•ด์ ธ๋ผ. "
- ๋นŒ ๊ฒŒ์ด์ธ  (๋งˆ์ดํฌ๋กœ์†Œํ”„ํŠธ ์ฐฝ์—…์ž)
๐Ÿ“‘ 1. ๋ฌธ์ œ์„ค๋ช…โŒ 2. ์‹คํŒจํ•œ ์‹œ๋„์ฝ”๋“œ๋Š” ์ž‘๋™ํ•˜์ง€๋งŒ ์ •๋‹ต ์ฒ˜๋ฆฌ X์ด์œ : CAR_ID ์ค‘๋ณต๋จSELECT A.Car_idFROM Car_rental_company_car A JOIN Car_rental_company_rental_history B ON A.Car_id = B.Car_idWHERE A.Car_type = '์„ธ๋‹จ' AND B.Start_date BETWEEN '2022-10-01' AND '2022-10-31'ORDER BY A.Car_id DESC;โญ 3. ์ •๋‹ต์ฝ”๋“œCAR_ID ์ค‘๋ณต์ด ์—†์–ด์•ผ ํ•˜๋ฉฐ -> DISTINCT๋Œ€์—ฌ ๊ธฐ๋ก์ด ์žˆ๋Š” -> ON A.CAR_ID = B.CAR_IDSELECT DISTINCT(A.Car_id)FROM ..
๐Ÿ“‘ 1. ๋ฌธ์ œ์„ค๋ช…โญ 2. ์ •๋‹ต์ฝ”๋“œ์ถœ๊ณ ์ผ์ด 2022-05-01 ๋ณด๋‹ค ์ž‘๊ฑฐ๋‚˜ ๊ฐ™์œผ๋ฉด '์ถœ๊ณ ์™„๋ฃŒ'๋Šฆ์œผ๋ฉด ์ถœ๊ณ  ๋Œ€๊ธฐ, ์—†์œผ๋ฉด ์ถœ๊ณ  ๋ฏธ์ •์ด๊ณ  ํ•ด๋‹น ์ปฌ๋Ÿผ๋ช…์€ ์ถœ๊ณ ์—ฌ๋ถ€๋ผ๋Š” ์ด๋ฆ„์œผ๋กœ ์„ค์ •์–ด๋ ค์šด ๋ถ€๋ถ„์€ ์—†๋Š” ์‰ฌ์šด ๋ฌธ์ œ๐ŸฅณSELECT Order_id, Product_id, DATE_FORMAT(Out_date,'%Y-%m-%d') AS Out_date, CASE WHEN Out_date '2022-05-01' THEN '์ถœ๊ณ ๋Œ€๊ธฐ' ELSE '์ถœ๊ณ ๋ฏธ์ •'END AS ์ถœ๊ณ ์—ฌ๋ถ€FROM Food_orderORDER BY Order_id;
๐Ÿ“‘ 1. ๋ฌธ์ œ์„ค๋ช…โญ 2. ์ •๋‹ต์ฝ”๋“œ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_INSORDER BY ANIMAL_ID; ๐Ÿ˜Š 3. TMIANSI SQL ํ‘œ์ค€์—์„œ๋Š” ALIAS ์— ์ž‘์€๋”ฐ์˜ดํ‘œ๋ฅผ ์‚ฌ์šฉํ•˜๋Š” ๊ฒƒ์ด ํ—ˆ์šฉ๋˜์ง€ ์•Š์ง€๋งŒ ์จ๋„ ์ƒ๊ด€์€ ์—†๋‹ค. (๋‚˜๋Š” ๋”ฐ์˜ดํ‘œ ์ผ์Œ)SELECT Animal_id, name, CASE WHEN Sex_upon_intake LIKE '%Neutered%' ..
โœ… ์ด๋Ÿฐ๊ฑฐ๋Š” ๋ญ˜๋กœ ๋งŒ๋“œ๋Š”๊ฑด์ง€ ํ”„๋กœ๊ทธ๋žจ ์ฐพ๊ธฐ1. ERD Diagram ์ž‘์„ฑ ํˆด - dbdiagram.io 2. ์•„ํ‚คํ…์ฒ˜ ๊ตฌ์กฐ๋„ ์ž‘์„ฑ ํˆด - draw.io โœ… SQL 3๋ฌธ์ œ ํ›„ ํฌ์ŠคํŒ…โœ… ์ž๋ฐ” 2๋ฌธ์ œ ํ›„ ํฌ์ŠคํŒ…โœ… 25-01-18 ๋‹ค๋ฏธ ๊ฒฐํ˜ผ์‹ ๊ฐ€๊ธฐโœ… ๋ Œ์ฆˆ, ๋ฐฉํ–ฅ์ œ, ๋น—, ์•ˆ๊ฒฝ๋‹ฆ์ด, ์ƒดํ‘ธ, ์•ˆ์ •๊ธฐโœ… 24-01-19 ์˜ค์ „์— ํ™”์žฅ์‹ค ์ฒญ์†Œ, ๋นจ๋ž˜, ์“ฐ๋ ˆ๊ธฐ ๋ฒ„๋ฆฌ๊ธฐโœ… 2์‹œ ์ „๊นŒ์ง€ AWS ๋“ฃ๊ณ  ์ •๋ฆฌ16. IAM MFA ์‹ค์Šต 17. AWS ์•ก์„ธ์Šค ํ‚ค, CLI ๋ฐ SDK 18. Windows์—์„œ AWS CLI ์„ค์ • 19. Mac OS X์—์„œ AWS CLI ์„ค์ • 20. Linux์—์„œ AWS CLI ์„ค์ • 21. AWS CLI ์‹ค์Šต22. AWS CloudShell: Region ๊ฐ€์šฉ์„ฑ - ํ•œ๊ตญ์—์„œ ์‚ฌ์šฉ๊ฐ€๋Šฅํ•จ23. AWS ํด๋ผ์šฐ๋“œ์‰˜โœ…..
๐Ÿ“‘ 1. ๋ฌธ์ œ์„ค๋ช…โŒ ์‹คํŒจํ•œ ์‹œ๋„์ด๋ฆ„์˜ ๋Œ€์†Œ๋ฌธ์ž๋ฅผ ๊ตฌ๋ณ„ํ•˜์ง€ ์•Š๋Š”๋‹ค๋Š” ๋ถ€๋ถ„์ด  ๋ฌธ์ œ์˜ ํ•ต์‹ฌ 1์ฐจ ์‹œ๋„ ์‹คํŒจWHERE ์ ˆ์—์„œ `LOWER(NAME)` ํ•ด ์ฃผ์—ˆ์œผ๋‚˜ ํ‹€๋ฆผ์ฟผ๋ฆฌ๋Š” ๋Œ์•„๊ฐ€๊ณ  ๊ฒฐ๊ณผ๋Š” ๋‚˜์˜ค์ง€๋งŒ ๊ฒฐ๊ตญ WHERE์ ˆ์—์„œ LOWER()๊ณผ LIKE๋ฅผ ํ•จ๊ป˜ ์“ด ๊ฒƒ์€ ๋ถˆํ•„์š”ํ•œ ์ฒ˜๋ฆฌ๋ฅผ ํ•˜๋Š” ๊ฒƒ์ด๋‹ค. MySQL์—์„œ LIKE ์—ฐ์‚ฐ์ž๋Š” ๊ธฐ๋ณธ์ ์œผ๋กœ ๋Œ€์†Œ๋ฌธ์ž๋ฅผ ๊ตฌ๋ณ„ํ•˜์ง€ ์•Š๋Š”๋‹ค. SELECT ANIMAL_ID, NAMEFROM ANIMAL_INSWHERE LOWER(ANIMAL_TYPE) = 'dog' AND LOWER(NAME) LIKE '%el%'ORDER BY NAME DESC; 2์ฐจ ์‹œ๋„ ์‹คํŒจ์ด๋ฒˆ์—๋Š” ORDER BY์ ˆ์— LOWER() ํ•จ์ˆ˜๋ฅผ ์จ์„œ ์ •๋ ฌํ–ˆ๋”๋‹ˆ ํ‹€๋ ธ๋‹ค๊ณ  ํ•จ.WHERE ์ ˆ์ด๋ž‘ ์ •๋ ฌ์ด๋ž‘ ๋ณ„๊ฐœ๋‹ˆ๊นŒ ์ •๋ ฌ์—๋Š” LOW..
ยท AWS
IAM Policies StructureIAM JSON ํŒŒ์ผ์€ AWS์—์„œ IAM(Identity and Access Management) ์ •์ฑ…์„ ์ •์˜ํ•  ๋•Œ ์‚ฌ์šฉ๋˜๋Š” ํŒŒ์ผ ํ˜•์‹์ด๋‹ค. ์ด ํŒŒ์ผ์€ JSONํ˜•์‹์œผ๋กœ ์ž‘์„ฑ๋˜๋ฉฐ, ์‚ฌ์šฉ์ž, ๊ทธ๋ฃน, ์—ญํ• (Role) ๋“ฑ์— ๋Œ€ํ•œ  ๊ถŒํ•œ(permissions)์„ ์„ค์ •ํ•˜๋Š” ๋ฐ ์“ฐ์ธ๋‹ค.  IAM JSON StructureIAM JSON ํŒŒ์ผ์€ AWS ๋ฆฌ์†Œ์Šค์— ๋Œ€ํ•ด ํ—ˆ์šฉ(Allow)ํ•˜๊ฑฐ๋‚˜ ๊ฑฐ๋ถ€(Deny)ํ•  ์ž‘์—…์„ ๋ช…์‹œํ•˜๋Š” ์ •์ฑ…(policy)์„ ์ •์˜ํ•œ๋‹ค.์ด๋ฅผ ํ†ตํ•ด ์–ด๋–ค ์‚ฌ์šฉ์ž๊ฐ€ ์–ด๋–ค AWS ์„œ๋น„์Šค์—์„œ ์–ด๋–ค ์ž‘์—…์„ ์ˆ˜ํ–‰ํ•  ์ˆ˜ ์žˆ๋Š”์ง€๋ฅผ ์ œ์–ดํ•œ๋‹ค.JSON ํŒŒ์ผ ์˜ˆ์‹œ JSON ํŒŒ์ผ ํ•„๋“œConsists ofโ€ข Version: policy language version, always incl..
- ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค ๊ตฌ๋ช…๋ณดํŠธ ๋ฌธ์ œ ํ’€๊ธฐ(๊ทธ๋ฆฌ๋”” ์•Œ๊ณ ๋ฆฌ์ฆ˜)- AWS IAM ์ •์ฑ… ์‹ค์Šต- (MySQL) ํŠน์ • ์˜ต์…˜์ด ํฌํ•จ๋œ ์ž๋™์ฐจ ๋ฆฌ์ŠคํŠธ ๊ตฌํ•˜๊ธฐ- (MySQL) ์ž๋™์ฐจ ๋Œ€์—ฌ ๊ธฐ๋ก์—์„œ ์žฅ๊ธฐ/๋‹จ๊ธฐ ๋Œ€์—ฌ ๊ตฌ๋ถ„ํ•˜๊ธฐ โญ AWS ๊ฐ•์˜ ๋“ค์„๋•Œ ๋ฃจํŠธ๊ณ„์ •์ด๋ž‘ IAM ๊ณ„์ • ๋™์‹œ์— ๋„์šฐ๋Š” ๋ฒ•- ํฌ๋กฌ ์ฐฝ์—์„œ ๋ฃจํŠธ ๊ณ„์ •์œผ๋กœ ๋กœ๊ทธ์ธ ํ•œ ์ƒํƒœ์—์„œ ์ƒˆ์ฐฝ์—์„œ ์‹œํฌ๋ฆฟ๋ชจ๋“œ ๋„์›Œ์„œ Sign-in URL for IAM users in this account ๋กœ๊ทธ์ธ โญ ํŒŒ์›Œํฌ์ธํŠธ์—์„œ ํฌ์ธํ„ฐ๋กœ ์Šฌ๋ผ์ด๋“œ ๋„˜๊ธฐ๊ธฐ ์„ค์ •USB ๋ฆฌ์‹œ๋ฒ„ ํฌํŠธ์— ์‚ฝ์ž… or ๋ธ”๋ฃจํˆฌ์Šค ํŽ˜์–ด๋ง์ƒ๋‹จ ๋ฉ”๋‰ด - [์Šฌ๋ผ์ด๋“œ ์‡ผ] - [์Šฌ๋ผ์ด๋“œ ์‡ผ ์„ค์ •] - ๋ฐœํ‘œ์ž ๋ณด๊ธฐ ์‚ฌ์šฉ ์ฒดํฌ์ฒดํฌ๋ฅผ ํ™œ์„ฑํ™”ํ•ด์„œ ๋ฐœํ‘œ์ž ํ™”๋ฉด๊ณผ ๊ด€๊ฐ ํ™”๋ฉด์„ ๋ถ„๋ฆฌํ•˜๋Š” ๊ฒƒ์ž„์–ด์ œ ์ ์‹ฌ์‹œ๊ฐ„์— ์‚ฐ์— ์˜ฌ๋ผ๊ฐ”๋”๋‹ˆ ๋‹ค๋ฆฌ๊ฐ€ ์•„ํ”„๋‹ค์šด๋™ํ™” ์ƒˆ๊ฑฐ..
๐Ÿ“‘ 1. ํŠน์ • ์˜ต์…˜์ด ํฌํ•จ๋œ ์ž๋™์ฐจ ๋ฆฌ์ŠคํŠธ ๊ตฌํ•˜๊ธฐ ๋ฌธ์ œCAR_RENTAL_COMPANY_CAR ํ…Œ์ด๋ธ”์—์„œ '๋„ค๋น„๊ฒŒ์ด์…˜' ์˜ต์…˜์ด ํฌํ•จ๋œ ์ž๋™์ฐจ ๋ฆฌ์ŠคํŠธ๋ฅผ ์ถœ๋ ฅํ•˜๋Š” SQL๋ฌธ์„ ์ž‘์„ฑํ•ด์ฃผ์„ธ์š”. ๊ฒฐ๊ณผ๋Š” ์ž๋™์ฐจ ID๋ฅผ ๊ธฐ์ค€์œผ๋กœ ๋‚ด๋ฆผ์ฐจ์ˆœ ์ •๋ ฌํ•ด์ฃผ์„ธ์š”. โญ ์ •๋‹ต์ฝ”๋“œSELECT * FROM CAR_RENTAL_COMPANY_CARWHERE OPTIONS LIKE '%๋„ค๋น„๊ฒŒ์ด์…˜%'ORDER BY CAR_ID DESC;๐Ÿ“‘ 2. ์ž๋™์ฐจ ๋Œ€์—ฌ ๊ธฐ๋ก์—์„œ ์žฅ๊ธฐ/๋‹จ๊ธฐ ๋Œ€์—ฌ ๊ตฌ๋ถ„ํ•˜๊ธฐ ๐Ÿ“Œ ํ‹€๋ฆฐ์ฝ”๋“œSELECT HISTORY_ID, CAR_ID, DATE_FORMAT(START_DATE, '%Y-%m-%d'), DATE_FORMAT(END_DATE, '%Y-%m-%d'), CASE WHEN DATEDIFF(END_DATE..
์ƒ๋‹จ์œผ๋กœ