songining
nginx-gunicorn-flask ๋„์ปค ์—ฐ๋™

docker-compose.yml version: '3' services: backend: build: context: . dockerfile: ./backend/Dockerfile expose: - 5000 ports: - "5000:5000" links: - mongo_db:mongo_db depends_on: - mongo_db #restart: always volumes: - ./backend/flask_app:/usr/src/app command: gunicorn -w 1 -b 0.0.0.0:5000 wsgi:app frontend: build: context: . dockerfile: ./frontend/Dockerfile expose: - 3000 ports: - "3000:3000" vol..

AWS EC2 ์›น์„œ๋ฒ„ ๊ตฌ์ถ•ํ•˜๊ธฐ

๋‚˜์ค‘์„ ์œ„ํ•ด ์“ฐ๋Š” ์„œ๋ฒ„ ๊ตฌ์ถ• ์ˆœ์„œ 1. aws ๊ฐ€์ž… 2. ec2 ์ธ์Šคํ„ด์Šค ์ƒ์„ฑ ๋ฐ ํƒ„๋ ฅ์  ip์™€ ์—ฐ๊ฒฐ (ubuntu free tier๋กœ ์ƒ์„ฑ) 3. ์ธ๋ฐ”์šด๋“œ ๊ทœ์น™ 80(๊ธฐ๋ณธ) ํฌํŠธ๋ฒˆํ˜ธ ์ถ”๊ฐ€(0.0.0.0) 3. ๋ณด์•ˆ๊ทธ๋ฃน ์ธ๋ฐ”์šด๋“œ ๊ทœ์น™์— ํฌํŠธ๋ฒˆํ˜ธ 9999 ์ถ”๊ฐ€ (์‚ฌ์šฉ์ž ์ง€์ • TCP) 4. ์ž์‹ ์˜ pem ํด๋” ๊ฒฝ๋กœ์—์„œ chmod 400 songyi.pem ssh -i rlathddl.pem ubuntu@์ง€์ •๋œ ip์ฃผ์†Œ -> ec2 ์„œ๋ฒ„ ์—ฐ๊ฒฐ 5. docker run -d -p 9999:80 --name apacheweb httpd = host pc (์ž์‹ ์˜ pc) ์—์„œ 9999ํฌํŠธ๋กœ ์ ‘์†์„ ํ•˜๋ฉด apacheweb2 ์ปจํ…Œ์ด๋„ˆ์˜ ํฌํŠธ 80์œผ๋กœ forwarding ํ•ด์ฃผ๊ฒ ๋‹ค๋Š” ์˜๋ฏธ 6. File Zilla ์„ค์น˜ +..

๊ณผ๋ชฉII SQL ๊ธฐ๋ณธ ๋ฐ ํ™œ์šฉ 2์žฅ - 7์ ˆ DCL
SQLD 2021. 5. 25. 22:37

DCL - ์œ ์ €๋ฅผ ์ƒ์„ฑํ•˜๊ณ  ๊ถŒํ•œ์„ ์ œ์–ดํ•  ์ˆ˜ ์žˆ๋Š” DCL ๋ช…๋ น์–ด๊ฐ€ ์žˆ๋‹ค. ์‹œ์Šคํ…œ ๊ถŒํ•œ - ORACLE์˜ DBA ๊ถŒํ•œ์„ ๊ฐ€์ง€๊ณ  ์žˆ๋Š” SYSTEM ์œ ์ €๋กœ ์ ‘์†ํ•˜๋ฉด ์œ ์ € ์ƒ์„ฑ๊ถŒํ•œ์„ ๋‹ค๋ฅธ ์œ ์ €์—๊ฒŒ ๋ถ€์—ฌํ•  ์ˆ˜ ์žˆ๋‹ค. CONN SYSTEM/MANAGER -> ์—ฐ๊ฒฐ๋˜์—ˆ๋‹ค. GRANT CREATE USER TO SCOTT; -> SCOTT์—๊ฒŒ ์œ ์ € ์ƒ์„ฑ ๊ถŒํ•œ ๋ถ€์—ฌ CONN SCOTT/TIGER -> ์—ฐ๊ฒฐ๋˜์—ˆ๋‹ค. CREATE USER PJS IDENTIFIED BY KOREA7; -> KOREA7 ๋น„๋ฐ€๋ฒˆํ˜ธ๋ฅผ ๊ฐ€์ง„ ์œ ์ € PJS ์ƒ์„ฑ SESSION ์ƒ์„ฑ ๊ถŒํ•œ - ์œ ์ €๊ฐ€ ๋กœ๊ทธ์ธ์„ ํ•˜๊ธฐ ์œ„ํ•ด์„œ๋Š” CREATE SESSION ๊ถŒํ•œ์„ ๋ถ€์—ฌ๋ฐ›์•„์•ผ ํ•œ๋‹ค. CONN SYSTESM/MANAGER GRANT CREATE SESSION TO ..

article thumbnail
๊ณผ๋ชฉII SQL ๊ธฐ๋ณธ ๋ฐ ํ™œ์šฉ 2์žฅ - 6์ ˆ WINDOW FUNCTION
SQLD 2021. 5. 25. 21:14

WINDOW FUNCTION - ๋ถ€๋ถ„์ ์ด๋‚˜๋งˆ ํ–‰๊ณผ ํ–‰๊ฐ„์˜ ๊ด€๊ณ„๋ฅผ ์‰ฝ๊ฒŒ ์ •์˜ํ•˜๊ธฐ ์œ„ํ•ด ๋งŒ๋“  ํ•จ์ˆ˜. - ์œˆ๋„์šฐ ํ•จ์ˆ˜๋ฅผ ํ™œ์šฉํ•˜๋ฉด ๋ณต์žกํ•œ ํ”„๋กœ๊ทธ๋žจ์„ ํ•˜๋‚˜์˜ SQL๋ฌธ์žฅ์œผ๋กœ ์‰ฝ๊ฒŒ ํ•ด๊ฒฐํ•  ์ˆ˜ ์žˆ๋‹ค. - WINDOW ํ•จ์ˆ˜๋Š” ๋‹ค๋ฆ„ ํ•จ์ˆ˜์™€ ๋‹ฌ๋ฆฌ ์ค‘์ฒฉํ•ด์„œ ์‚ฌ์šฉํ•˜์ง€๋Š” ๋ชปํ•˜์ง€๋งŒ, ์„œ๋ธŒ์ฟผ๋ฆฌ์—์„œ๋Š” ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๋‹ค. 1.๊ทธ๋ฃน ๋‚ด ์ˆœ์œ„(RANK) ๊ด€๋ จ ํ•จ์ˆ˜ - RANK, DENSE_RANK, ROW_NUMBER ํ•จ์ˆ˜ 2.๊ทธ๋ฃน ๋‚ด ์ง‘๊ณ„(AGGREGATE) ๊ด€๋ จ ํ•จ์ˆ˜ - SUM, MAX, MIN, AVG, COUNT ํ•จ์ˆ˜ 3.๊ทธ๋ฃน ๋‚ด ํ–‰ ์ˆœ์„œ ๊ด€๋ จ ํ•จ์ˆ˜ - FIRST_VALUE, LAST_VALUE, LAG, LEAD ํ•จ์ˆ˜ 4.๊ทธ๋ฃน ๋‚ด ๋น„์œจ ๊ด€๋ จ ํ•จ์ˆ˜ - CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPOR..

article thumbnail
๊ณผ๋ชฉII SQL ๊ธฐ๋ณธ ๋ฐ ํ™œ์šฉ 2์žฅ - 5์ ˆ GROUP FUNCTION
SQLD 2021. 5. 25. 16:16

๋ฐ์ดํ„ฐ ๋ถ„์„ ํ•จ์ˆ˜ (AGGREGATE FUNCTIONS) - GROUP FUNCTION์˜ ํ•œ ๋ถ€๋ถ„์ด๋ฉฐ, GROUP AGGREGATE FUNCTION์ด๋ผ๊ณ ๋„ ๋ถ€๋ฅผ ์ˆ˜ ์žˆ๋‹ค. COUNT,SUM,AVG,MAX,MIN์™ธ ๊ฐ์ข… ์ง‘๊ณ„ ํ•จ์ˆ˜๋“ค์ด ํฌํ•จ๋˜์–ด ์žˆ๋‹ค. (GROUP FUNCTIONS) - ๊ทธ๋ฃนํ•จ์ˆ˜๋กœ๋Š” ์ง‘๊ณ„ ํ•จ์ˆ˜ ์ œ์™ธํ•˜๋ฉด, ROLLUPํ•จ์ˆ˜, CUBE ํ•จ์ˆ˜, GROUPING SETS ํ•จ์ˆ˜๊ฐ€ ์žˆ๋‹ค. - ROLLUP์€ GROUP BY์˜ ํ™•์žฅ๋œ ํ˜•ํƒœ๋กœ ๋ณ‘๋ ฌ๋กœ ์ˆ˜ํ–‰์ด ๊ฐ€๋Šฅ. (๊ณ„์ธต์  ๋ถ„๋ฅ˜๋ฅผ ํฌํ•จํ•˜๊ณ  ์žˆ๋Š” ๋ฐ์ดํ„ฐ ์ง‘๊ณ„์— ์ ํ•ฉ) - CUBE ๋Š” ๊ฒฐํ•ฉ๊ฐ€๋Šฅํ•œ ๋ชจ๋“  ๊ฐ’์— ๋Œ€ํ•˜์—ฌ ๋‹ค์ฐจ์›์ ์ธ ์ง‘๊ณ„๋ฅผ ์ƒ์„ฑํ•˜๊ฒŒ ๋˜๋ฏ€๋กœ ROLLUP์— ๋น„ํ•ด ๋‹ค์–‘ํ•œ ๋ฐ์ดํ„ฐ๋ฅผ ์–ป๋Š” ์žฅ์ ์ด ์žˆ์ง€๋งŒ, ์‹œ์Šคํ…œ์— ๋ถ€ํ•˜๋ฅผ ๋งŽ์ด ์ฃผ๋Š” ๋‹จ์ ์ด ์žˆ๋‹ค. - GROUPING ..

article thumbnail
๊ณผ๋ชฉII SQL ๊ธฐ๋ณธ ๋ฐ ํ™œ์šฉ 2์žฅ - 4์ ˆ ์„œ๋ธŒ์ฟผ๋ฆฌ
SQLD 2021. 5. 25. 00:40

(์„œ๋ธŒ์ฟผ๋ฆฌ์˜ ์œ„์น˜) WHERE ์ ˆ : NESTED SUBQUERY FROM ์ ˆ : INLINE VIEW SELECT์ ˆ ์™ธ : SCALAR SUBQUERY - ์„œ๋ธŒ์ฟผ๋ฆฌ๋ฅผ ๋ฌธ๋ฒ•์ ์œผ๋กœ ๊ตฌ๋ถ„ํ•˜๋Š” ๊ฐ€์žฅ ์‰ฌ์šด ๋ฐฉ๋ฒ•์€ SQL๋ฌธ์ด ๊ด„ํ˜ธ๋กœ ๋ฌถ์—ฌ์ ธ ์žˆ์œผ๋ฉด ์„œ๋ธŒ์ฟผ๋ฆฌ์ด๋‹ค. - ์„œ๋ธŒ์ฟผ๋ฆฌ๊ฐ€ ๋ฉ”์ธ์ฟผ๋ฆฌ์— ํฌํ•จ๋˜๋Š” ์ข…์†์ ์ธ ๊ด€๊ณ„์ด๋‹ค. - ์„œ๋ธŒ์ฟผ๋ฆฌ๋Š” ๋ฉ”์ธ์ฟผ๋ฆฌ์˜ ์ปฌ๋Ÿผ์„ ๋ชจ๋‘ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ์ง€๋งŒ ๋ฉ”์ธ์ฟผ๋ฆฌ๋Š” ์„œ๋ธŒ์ฟผ๋ฆฌ์˜ ์ปฌ๋Ÿผ์„ ์‚ฌ์šฉํ•  ์ˆ˜ ์—†๋‹ค. (JAVA์˜ ์ƒ์†๊ณผ ๊ฐ™์€ ๊ฐœ๋…) - ์„œ๋ธŒ์ฟผ๋ฆฌ์—์„œ๋Š” ORDER BY ์‚ฌ์šฉํ•˜์ง€ ๋ชปํ•จ. ์„œ๋ธŒ์ฟผ๋ฆฌ์™€ ์กฐ์ธ ์„œ๋ธŒ์ฟผ๋ฆฌ์™€ ์กฐ์ธ์„ ๋…ผ๋ฆฌ์ ์œผ๋กœ ๊ตฌ๋ถ„ํ•˜๋Š” ๊ฐ€์žฅ ์ข‹์€ ๋ฐฉ๋ฒ•์€ ๋‘ ๊ฐœ์˜ ํ…Œ์ด๋ธ” ์œ„์น˜๋ฅผ ๋ฐ”๊พธ์–ด๋ณด๋Š” ๊ฒƒ์ด๋‹ค. ์กฐ์ธ์€ ๋‘ ๊ฐœ์˜ ํ…Œ์ด๋ธ” ์œ„์น˜๋ฅผ ๋ฐ”๊พธ์–ด ๋ณด๋”๋ผ๋„ ๊ฐ™์€ ๊ฒฐ๊ณผ๊ฐ€ ๋‚˜์˜ค๋ฉฐ, ์„œ๋ธŒ์ฟผ๋ฆฌ์˜ ๊ฒฝ์šฐ๋Š” ์ฃผ์ข…์˜ ๊ด€๊ณ„์ด๋ฏ€๋กœ ์ผ๋ฐ˜..

article thumbnail
๊ณผ๋ชฉII SQL ๊ธฐ๋ณธ ๋ฐ ํ™œ์šฉ 2์žฅ - 3์ ˆ ๊ณ„์ธตํ˜• ์งˆ์˜์™€ ์…€ํ”„ ์กฐ์ธ
SQLD 2021. 5. 24. 21:01

๊ณ„์ธตํ˜• ์งˆ์˜ - ๊ณ„์ธตํ˜• ๋ฐ์ดํ„ฐ๋ž€ ๋™์ผ ํ…Œ์ด๋ธ”์— ๊ณ„์ธต์ ์œผ๋กœ ์ƒ์œ„์™€ ํ•˜์œ„ ๋ฐ์ดํ„ฐ๊ฐ€ ํฌํ•จ๋œ ๋ฐ์ดํ„ฐ๋ฅผ ๋งํ•œ๋‹ค. - ์—”ํ„ฐํ‹ฐ๋ฅผ ์ˆœํ™˜ ๊ด€๊ณ„ ๋ฐ์ดํ„ฐ ๋ชจ๋ธ๋กœ ์„ค๊ณ„ํ•  ๊ฒฝ์šฐ ๊ณ„์ธตํ˜• ๋ฐ์ดํ„ฐ๊ฐ€ ๋ฐœ์ƒํ•œ๋‹ค. ์ˆœํ™˜๊ด€๊ณ„ ๋ฐ์ดํ„ฐ ๋ชจ๋ธ์˜ ์˜ˆ๋กœ๋Š” ์กฐ์ง๋„, ์‚ฌ์›, ๋ฉ”๋‰ด๋“ฑ์ด ์žˆ๋‹ค. ORACLE ๊ณ„์ธตํ˜• ์งˆ์˜ SELECT ์นผ๋Ÿผ๋ช….. FROM ํ…Œ์ด๋ธ”๋ช… WHERE ์กฐ๊ฑด START WITH ์กฐ๊ฑด -> ๊ณ„์ธต ๊ตฌ์กฐ ์ „๊ฐœ์˜ ์‹œ์ž‘ ์œ„์น˜๋ฅผ ์ง€์ •ํ•˜๋Š” ๊ตฌ๋ฌธ CONNECT BY PRIOR ์กฐ๊ฑด; -> ๋‹ค์Œ์— ์ „๊ฐœ๋  ์ž์‹ ๋ฐ์ดํ„ฐ๋ฅผ ์ง€์ •ํ•˜๋Š” ๊ตฌ๋ฌธ "PRIOR ์ž์‹ = ๋ถ€๋ชจ" ํ˜•ํƒœ๋ฅผ ์‚ฌ์šฉํ•˜๋ฉด ๊ณ„์ธต ๊ตฌ์กฐ์—์„œ ๋ถ€๋ชจ -> ์ž์‹ ๋ฐฉํ–ฅ์œผ๋กœ ๋‚ด๋ ค๊ฐ€๋Š” ์ˆœ๋ฐฉํ–ฅ์œผ๋กœ ์ „๊ฐœ๋œ๋‹ค. "PRIOR ๋ถ€๋ชจ = ์ž์‹" ํ˜•ํƒœ๋ฅผ ์‚ฌ์šฉํ•˜๋ฉด ๊ณ„์ธต ๊ตฌ์กฐ์—์„œ ์ž์‹ -> ๋ถ€๋ชจ ๋ฐฉํ–ฅ์œผ๋กœ ์˜ฌ๋ผ๊ฐ€๋Š” ์—ญ๋ฐฉํ–ฅ์œผ๋กœ ์ „๊ฐœ๋œ๋‹ค..

article thumbnail
๊ณผ๋ชฉ II SQL ๊ธฐ๋ณธ ๋ฐ ํ™œ์šฉ 2์žฅ - 2์ ˆ ์ง‘ํ•ฉ ์—ฐ์‚ฐ์ž
SQLD 2021. 5. 24. 19:49

์ง‘ํ•ฉ์—ฐ์‚ฐ์ž ์‚ฌ์šฉํ•˜๊ธฐ ์œ„ํ•œ ์ œ์•ฝ์กฐ๊ฑด - SELECT ์ ˆ์˜ ์นผ๋Ÿผ ์ˆ˜๊ฐ€ ๋™์ผํ•˜๊ณ  SELECT ์ ˆ์˜ ๋™์ผ ์œ„์น˜์— ์กด์žฌํ•˜๋Š” ์นผ๋Ÿผ์˜ ๋ฐ์ดํ„ฐ ํƒ€์ž…์ด ์ƒํ˜ธ ํ˜ธํ™˜ ๊ฐ€๋Šฅํ•ด์•ผ ํ•œ๋‹ค. (๋ฐ˜๋“œ์‹œ ๋™์ผํ•œ ๋ฐ์ดํ„ฐ ํƒ€์ž…์ผ ํ•„์š”๋Š” ์—†์Œ) ์ข…๋ฅ˜ - UNION : ์—ฌ๋Ÿฌ๊ฐœ์˜ SQL๋ฌธ์˜ ๊ฒฐ๊ณผ์— ๋Œ€ํ•œ ํ•ฉ์ง‘ํ•ฉ์œผ๋กœ ๊ฒฐ๊ณผ์—์„œ ๋ชจ๋“  ์ค‘๋ณต๋œ ํ–‰์€ ํ•˜๋‚˜์˜ ํ–‰์œผ๋กœ ๋งŒ๋“ ๋‹ค. - UNION ALL : ์—ฌ๋Ÿฌ๊ฐœ์˜ SQL๋ฌธ์˜ ๊ฒฐ๊ณผ์— ๋Œ€ํ•œ ํ•ฉ์ง‘ํ•ฉ์œผ๋กœ ์ค‘๋ณต๋œ ํ–‰๋„ ๊ทธ๋Œ€๋กœ ๊ฒฐ๊ณผ๋กœ ํ‘œ์‹œ๋œ๋‹ค. ์—ฌ๋Ÿฌ ์งˆ์˜ ๊ฒฐ๊ณผ ์ƒํ˜ธ๋ฐฐํƒ€์ ์ผ ๋•Œ ๋งŽ์ด ์‚ฌ์šฉํ•œ๋‹ค. ๊ฐœ๋ณ„ SQL๋ฌธ์˜ ๊ฒฐ๊ณผ๊ฐ€ ์„œ๋กœ ์ค‘๋ณต๋˜์ง€ ์•Š๋Š” ๊ฒฝ์šฐ, UNION๊ณผ ๊ฒฐ๊ณผ ๋™์ผ - INTERSECT : ์—ฌ๋Ÿฌ๊ฐœ์˜ SQL๋ฌธ์˜ ๊ฒฐ๊ณผ์— ๋Œ€ํ•œ ๊ต์ง‘ํ•ฉ์ด๋‹ค. ์ค‘๋ณต๋œ ํ–‰์€ ํ•˜๋‚˜์˜ ํ–‰์œผ๋กœ ๋งŒ๋“ ๋‹ค. - EXCEPT : ์•ž์˜ SQL๋ฌธ์˜ ๊ฒฐ๊ณผ์—์„œ ๋’ค์˜ SQL..

article thumbnail
๊ณผ๋ชฉII SQL ๊ธฐ๋ณธ ๋ฐ ํ™œ์šฉ 2์žฅ - 1์ ˆ ํ‘œ์ค€ ์กฐ์ธ
SQLD 2021. 5. 24. 18:13

FROM ์ ˆ์˜ JOIN ํ˜•ํƒœ (์ถ”๊ฐ€๋œ ์„ ํƒ ๊ธฐ๋Šฅ์œผ๋กœ ํ…Œ์ด๋ธ” ๊ฐ„์˜ JOIN ์กฐ๊ฑด์„ FROM์ ˆ์—์„œ ๋ช…์‹œ์ ์œผ๋กœ ์ •์˜ํ•  ์ˆ˜ ์žˆ๊ฒŒ ๋˜์—ˆ๋‹ค.) -INNER JOIN -NATURAL JOIN -USIN ์กฐ๊ฑด์ ˆ -ON ์กฐ๊ฑด์ ˆ -CROSS JOIN -OUTER JOIN โ€ปSQL server์˜ ๊ฒฝ์šฐ ON ์กฐ๊ฑด์ ˆ๋งŒ ์ง€์›ํ•˜๊ณ  NATURAL JOIN๊ณผ USING ์„ ์ง€์›ํ•˜์ง€ ์•Š๋Š”๋‹ค. 1)INNER JOIN - ๋‚ด๋ถ€ JOIN์ด๋ผ๊ณ  ํ•˜๋ฉฐ JOIN ์กฐ๊ฑด์—์„œ ๋™์ผํ•œ ๊ฐ’์ด ์žˆ๋Š” ํ–‰๋งŒ ๋ฐ˜ํ™˜ํ•œ๋‹ค. - INNER JOIN ํ‘œ์‹œ๋Š” ๊ทธ๋™์•ˆ WHERE ์ ˆ์—์„œ ์‚ฌ์šฉํ•˜๋˜ JOIN ์กฐ๊ฑด์„ FROM์ ˆ์—์„œ ์ •์˜ํ•˜๊ฒ ๋‹ค๋Š” ํ‘œ์‹œ์ด๋ฏ€๋กœ ๋’ค์— ON, USING ๊ผญ ์จ์ฃผ์–ด์•ผ ํ•จ - ์˜ˆ์‹œ) SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME FROM..

๊ณผ๋ชฉII SQL ๊ธฐ๋ณธ ๋ฐ ํ™œ์šฉ 1์žฅ - 8์ ˆ/9์ ˆ ORDER BY, JOIN
SQLD 2021. 5. 21. 23:09

8์ ˆ. ORDER BY ์ ˆ - ORDER BY ์ ˆ์—์„œ ์นผ๋Ÿผ๋ช…, ALIAS๋ช…, ์นผ๋Ÿผ ์ˆœ์„œ๋ฅผ ๊ฐ™์ด ํ˜ผ์šฉํ•˜๋Š” ๊ฒƒ๋„ ๊ฐ€๋Šฅํ•˜๋‹ค. - ๊ธฐ๋ณธ์ ์œผ๋กœ ์˜ค๋ฆ„์ฐจ์ˆœ ์ ์šฉ - ORACLE ์€ NULL ๊ฐ’์„ ๊ฐ€์žฅ ํฐ ๊ฐ’์œผ๋กœ ์ทจ๊ธ‰ํ•œ๋‹ค. ๋ฐ˜๋ฉด, SQL server ๋Š” ์ตœ์†Œ๊ฐ’์œผ๋กœ ๊ฐ„์ฃผํ•œ๋‹ค. - ORDER BY ์ ˆ์—๋Š” SELECT ๋ชฉ๋ก์— ๋‚˜ํƒ€๋‚˜์ง€ ์•Š์€ ๋ฌธ์žํ˜• ํ•ญ๋ชฉ์ด ํฌํ•จ๋  ์ˆ˜ ์žˆ๋‹ค. (์„œ๋ธŒ์ฟผ๋ฆฌ๋กœ ์ธํ•ด ์ปฌ๋Ÿผ์ด ์ œํ•œ๋˜์ง€ ์•Š๋Š” ๊ฒฝ์šฐ) - ์˜ˆ์‹œ) GROUP BY๋ฅผ ์‚ฌ์šฉํ•  ๊ฒฝ์šฐ GROUP BY ํ‘œํ˜„์‹(EX)์ง‘๊ณ„ํ•จ์ˆ˜,์‚ฌ์šฉํ•œ ์†์„ฑ..)์ด ์•„๋‹Œ ๊ฐ’์€ ๊ธฐ์ˆ  ๋  ์ˆ˜ ์—†๋‹ค. TOPN์ฟผ๋ฆฌ - SQL SERVER - ๋‘๊ฑด์ด์ƒ์˜ N ํ–‰์„ ๊ฐ€์ ธ์˜ค๊ณ  ์‹ถ์„ ๋•Œ๋Š” ROWNUM =N ์ด ์•„๋‹ˆ๋ผ ROWNUM ,

article thumbnail
๊ณผ๋ชฉII SQL ๊ธฐ๋ณธ ๋ฐ ํ™œ์šฉ 1์žฅ - 7์ ˆ GROUP BY, HAVING ์ ˆ
SQLD 2021. 5. 21. 21:15

7์ ˆ. GROUP BY, HAVING ์ ˆ ์ง‘๊ณ„ ํ•จ์ˆ˜(๋‹ค์ค‘ ํ–‰ ํ•จ์ˆ˜) - ์—ฌ๋Ÿฌ ํ–‰๋“ค์˜ ๊ทธ๋ฃน์ด ๋ชจ์—ฌ์„œ ๊ทธ๋ฃน๋‹น ๋‹จ ํ•˜๋‚˜์˜ ๊ฒฐ๊ณผ๋ฅผ ๋Œ๋ ค์ฃผ๋Š” ํ•จ์ˆ˜์ด๋‹ค. - SELECT, HAVING, ORDER BY ์ ˆ์— ์‚ฌ์šฉ๊ฐ€๋Šฅ - ํ…Œ์ด๋ธ” ์ „์ฒด ์ง‘๊ณ„๋ฅผ ์œ„ํ•ด GROUP BY ์ ˆ ์—†์ด๋„ ์ง‘๊ณ„ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๋‹ค. - ์ง‘๊ณ„ํ•จ์ˆ˜์ค‘ MAX,MIN,COUNT๋Š” ๋ฌธ์ž,๋‚ ์งœ ์œ ํ˜•์—๋„ ์ ์šฉ์ด ๊ฐ€๋Šฅํ•˜๋‹ค. COUNT(*) - NULL ๊ฐ’์„ ํฌํ•จํ•œ ํ–‰์˜ ์ˆ˜๋ฅผ ์ถœ๋ ฅํ•œ๋‹ค. COUNT(ํ‘œํ˜„์‹) - ํ‘œํ˜„์‹์˜ ๊ฐ’์ด NULL ๊ฐ’์ธ ๊ฒƒ์„ ์ œ์™ธํ•œ ํ–‰์˜ ์ˆ˜๋ฅผ ์ถœ๋ ฅํ•œ๋‹ค. SUM(ํ‘œํ˜„์‹) - NULL๊ฐ’์„ ์ œ์™ธํ•œ ํ•ฉ๊ณ„๋ฅผ ์ถœ๋ ฅํ•œ๋‹ค. AVG(ํ‘œํ˜„์‹) - NULL๊ฐ’์„ ์ œ์™ธํ•œ ํ‰๊ท ์„ ์ถœ๋ ฅํ•œ๋‹ค. MAX(ํ‘œํ˜„์‹) - ์ตœ๋Œ€๊ฐ’ ์ถœ๋ ฅ MIN(ํ‘œํ˜„์‹) - ์ตœ์†Œ๊ฐ’ ์ถœ๋ ฅ STDDEV(ํ‘œํ˜„..

article thumbnail
๊ณผ๋ชฉII SQL ๊ธฐ๋ณธ ๋ฐ ํ™œ์šฉ 1์žฅ - 5์ ˆ/6์ ˆ WHERE์ ˆ, FUNCTION
SQLD 2021. 5. 21. 20:35

5์ ˆ. WHERE์ ˆ WHERE์ ˆ ์—ฐ์‚ฐ์ž (SQL ์—ฐ์‚ฐ์ž) -BETWEEN A AND B ( NOT BETWEEN A AND B) -IN (list) ( NOT IN (list)) -LIKE '๋น„๊ต๋ฌธ์ž์—ด' -IS NULL ( IS NOT NULL) WHERE ์กฐ๊ฑด - CHAR ๋ณ€์ˆ˜๋‚˜ VARCHAR2 ๊ฐ™์€ ๋ฌธ์žํ˜• ํƒ€์ž…์„ ๊ฐ€์ง„ ์นผ๋Ÿผ์€ ์ธ์šฉ๋ถ€ํ˜ธ(์ž‘์€ ๋”ฐ์˜ดํ‘œ, ํฐ ๋”ฐ์˜ดํ‘œ)๋กœ ๋ฌถ์–ด์„œ ๋น„๊ต - LIKE EX) SELECT PLAYER_NAME ์„ ์ˆ˜์ด๋ฆ„ , POSITION ํฌ์ง€์…˜ , BACK_NO ๋ฐฑ๋„˜๋ฒ„ , HEIGHT ํ‚ค FROM PLAYER WHERE PLAYER_NAME LIKE '์žฅ%'; % : 0๊ฐœ์ด์ƒ์˜ ๋ฌธ์ž _ : 1๊ฐœ์ด์ƒ์˜ ๋‹จ์ผ ๋ฌธ์ž - NULL : ์•„์ง ์ •์˜๋˜์ง€ ์•Š์€ ๋ฏธ์ง€์˜ ๊ฐ’, '', 0 ๊ณผ๋Š” ๋‹ค๋ฅธ ๊ฐ’..