Search

[MySQL] 3. SQL ๋ฌธ๋ฒ•

ํƒ€์ž…
์Šคํ„ฐ๋””
ํƒœ๊ทธ
MySQL
์ƒํƒœ
Published
์ƒ์„ฑ์ผ
2022/08/01 13:53
์ตœ์ข… ํŽธ์ง‘ ์ผ์‹œ
2023/02/27 04:10
์ƒ์œ„ ํ•ญ๋ชฉ
1 more property

๋ฐ์ดํ„ฐ ์กฐํšŒ(SELECT)

SELECT ์™€ ํ•จ๊ป˜ ์“ฐ์ด๋Š” ์ ˆ๋“ค

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

์‹ค์Šต

๋ชจ๋“  ์—ด SELECT

-- ์ „์ฒด ๋‹ค ๋ถˆ๋Ÿฌ์˜ค๊ธฐ SELECT * FROM customer;
SQL
๋ณต์‚ฌ
์ด 2661 ํ–‰
์•„๋ž˜ ์ƒ๋žต

WHERE์ ˆ๋กœ ์กฐ๊ฑด ๊ฑธ๊ธฐ

-- WHERE์ ˆ ์‚ฌ์šฉ SELECT * FROM customer WHERE gender = 'man';
SQL
๋ณต์‚ฌ
์ด 1133ํ–‰

count() ํ•จ์ˆ˜ ์‚ฌ์šฉ

-- COUNT(mem_no) : mem_no ํ•„๋“œ๋ฅผ ๊ฐ€์ง€๋Š” ํ–‰์˜ ๊ฐœ์ˆ˜ ๊ตฌํ•˜๊ธฐ SELECT COUNT(mem_no) AS ํšŒ์›์ˆ˜ FROM customer WHERE gender='man';
SQL
๋ณต์‚ฌ
count(*) : ๊ฒ€์ƒ‰ํ•œ ํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ํ–‰ ๊ฐœ์ˆ˜ ๊ตฌํ•˜๊ธฐ
count(field) : ํŠน์ • field ๋ฅผ ๋ช…์‹œํ•ด์„œ, ํ•ด๋‹น field ๊ฐ’์ด NULL ์ธ ํ–‰์€ ์ œ์™ธ
์ผ๋ฐ˜์ ์œผ๋กœ NOT NULL ์ธ id ๊ฐ’์„ count ํ•จ์ˆ˜ ์ธ์ž๋กœ ์“ด๋‹ค.

GROUP BY

SELECT addr, COUNT(*) AS ํšŒ์›์ˆ˜ FROM customer WHERE gender = 'man' GROUP BY addr; -- GROUP BY๋ฅผ ํ•˜๋ฉด ํ•ด๋‹น ํ•„๋“œ๋กœ ๊ทธ๋ฃนํ•‘์ด ๋˜๊ธฐ ๋•Œ๋ฌธ์—, SELECT ์‹œ ํ•ด๋‹น ํ•„๋“œ์™€ COUNT ๋ฅผ ๊ฐ™์ด ์“ธ ์ˆ˜ ์žˆ๋‹ค -- GROUP BY ๋ฅผ ํ•œ ํ•„๋“œ๋Š” SELECT ์—๋„ ์จ์ฃผ๋Š” ๊ฒƒ์ด ๋ฐ”๋žŒ์งํ•˜๋‹ค. -- COUNT() ํ•จ์ˆ˜ ์•ˆ์— ๋“ค์–ด์˜ค๋Š” ์ปฌ๋Ÿผ์€ GROUP BY ์ ˆ์— ์•ˆ ์จ์ค˜๋„ ๋œ๋‹ค -- ์ฆ‰, mem_no ์—ด ๋ฟ๋งŒ ์•„๋‹ˆ๋ผ ๋‹ค๋ฅธ ์—ด์ด ๋“ค์–ด์™€๋„ ๊ฒฐ๊ณผ๋Š” ๋˜‘๊ฐ™์Œ. -- ์ฃผ์˜ํ•  ์ ์€ ํŠน์ • ํ•„๋“œ๋ฅผ ๋ช…์‹œํ•  ๋•Œ NULL ์ด๋ฉด ํ•ด๋‹น ํ–‰์€ ์นด์šดํŒ…์ด ์•ˆ ๋จ. -- ๋”ฐ๋ผ์„œ NOT NULL ์ธ ํ•„๋“œ ํ˜น์€ ์ „์ฒดํ•„๋“œ(*) ๋ฅผ COUNT ํ•จ์ˆ˜์˜ ์ธ์ž๋กœ ๋„ฃ์–ด์ค˜์•ผ ํ•˜๋Š”๋ฐ, -- ์ฟผ๋ฆฌ์†๋„ ๋•Œ๋ฌธ์— NOT NULL ์ธ ํ•„๋“œ(ID) ๋ฅผ ๋ณดํ†ต ๋„ฃ๋Š”๋‹ค. -- ์—๋Ÿฌ ๋ฐœ์ƒ SELECT addr,COUNT(mem_no) AS ํšŒ์›์ˆ˜ FROM customer WHERE gender='man'; -- group by ๋ฅผ ์•ˆ ์จ์ฃผ๊ณ , count() ์™€ ํŠน์ • ํ•„๋“œ๋ฅผ ๊ฐ™์ด select ํ•  ์ˆ˜ ์—†์Œ.
SQL
๋ณต์‚ฌ
โ€ข
GROUP BY addr; : addr ํ•„๋“œ์˜ ๊ฐ’์ด ๊ฐ™์€ ํ–‰๋“ค์„ ๊ทธ๋ฃนํ•‘ํ•œ๋‹ค.
๊ทธ๋ฃนํ•‘ํ•œ ํ•„๋“œ๋Š” SELECT ์— ์ ์–ด์ฃผ๋Š” ๊ฒฝ์šฐ๊ฐ€ ๋งŽ๋‹ค.
โ€ข
COUNT() AS ํšŒ์›์ˆ˜ : ์ฟผ๋ฆฌ์‹คํ–‰ ์ˆœ์„œ๊ฐ€ FROM โ†’ WHERE โ†’ GROUP BY ์ด๋ฏ€๋กœ, SELECT ๋Š” ๋งจ ๋งˆ์ง€๋ง‰์— ์‹คํ–‰๋œ๋‹ค. ๋”ฐ๋ผ์„œ GROUP BY ์— ์˜ํ•ด ์ƒˆ๋กญ๊ฒŒ ๋ฐ”๋€ ํ…Œ์ด๋ธ”์—์„œ์˜ ๊ฐ ๋ฒ”์ฃผ(addr)์˜ ํ–‰ ๊ฐœ์ˆ˜๋ฅผ ๊ตฌํ•œ๋‹ค. count(*) ์™€ count(mem_no) ๋Š” ๋˜‘๊ฐ™์€ ๊ธฐ๋Šฅ์„ ํ•˜๊ณ , ์ฒ˜๋ฆฌ์†๋„๊ฐ€ ํ›„์ž๊ฐ€ ๋” ๋น ๋ฅด๊ธฐ ๋•Œ๋ฌธ์— ์ผ๋ฐ˜์ ์œผ๋กœ๋Š” ํ›„์ž๋ฐฉ์‹์„ ํƒํ•œ๋‹ค.

HAVING

WHERE ์€ ๊ทธ๋ฃนํ•‘์„ ํ•˜๊ธฐ์ „์— ์กฐ๊ฑด๊ฑธ์–ด์ฃผ๊ณ  , HAVING ์€ ๊ทธ๋ฃนํ•‘์„ ํ•œ ํ›„์— ์กฐ๊ฑด์„ ๊ฑธ์–ด์ค€๋‹ค.
๋‘˜ ๋‹ค ํ•„์š”ํ•œ ์ด์œ ๋Š” ๊ทธ๋ฃนํ•‘์„ ํ•˜๊ฒŒ ๋˜๋ฉด, ํ…Œ์ด๋ธ”์ด ๋ณ€๊ฒฝ๋˜๊ธฐ ๋•Œ๋ฌธ์ด๋‹ค.
-- HAVING์ ˆ์€ GROUP BY ์— ์˜ํ•ด ๋งŒ๋“ค์–ด์ง„ ๊ฒฐ๊ณผ์— ๋Œ€ํ•ด ์กฐ๊ฑด์„ ๊ฑธ ๋•Œ ์“ฐ์ž„ SELECT addr, COUNT(mem_no) AS ํšŒ์›์ˆ˜ FROM customer WHERE gender = 'man' GROUP BY addr HAVING COUNT(mem_no) < 100; -- ๊ทธ๋ฃนํ•‘ํ•œ ํ–‰์˜ ํšŒ์›์ˆ˜๊ฐ€ 100 ๋ฏธ๋งŒ์ผ๋•Œ๋งŒ
SQL
๋ณต์‚ฌ

ORDER BY

๋งจ ๋งˆ์ง€๋ง‰์— ์ž‘์„ฑํ•˜๊ณ , ์ •๋ ฌ ๊ธฐ๋Šฅ์œผ๋กœ ์‚ฌ์šฉํ•œ๋‹ค.
๋””ํดํŠธ๋Š” ์˜ค๋ฆ„์ฐจ์ˆœ(ASC)์ด๊ณ , ๋‚ด๋ฆผ์ฐจ์ˆœ์„ ํ•˜๋ ค๋ฉด DESC ๋ฅผ ์จ์ค€๋‹ค.
-- ORDER BY : ๋งจ ํ•˜๋‹จ์— ์ž‘์„ฑ. ์˜ค๋ฆ„์ฐจ์ˆœ: ASC / ๋‚ด๋žŒ์ฐจ์ˆœ : DESC SELECT addr, COUNT(mem_no) AS ํšŒ์›์ˆ˜ FROM customer WHERE gender = 'man' GROUP BY addr HAVING COUNT(mem_no) <100 ORDER BY COUNT(mem_no) DESC ;
SQL
๋ณต์‚ฌ

GROUP BY ์—ฌ๋Ÿฌ ํ•„๋“œ

์—ฌ๋Ÿฌ ํ•„๋“œ๋ฅผ ๋™์‹œ์— ๊ทธ๋ฃนํ•‘ํ•  ์ˆ˜ ์žˆ๋‹ค.
-- GROUP BY ์—ฌ๋Ÿฌ ํ•„๋“œ SELECT addr, gender, count(mem_no) AS ํšŒ์›์ˆ˜ FROM customer WHERE addr IN ('seoul', 'incheon') GROUP BY addr, gender ORDER BY addr DESC; -- IN ์—ฐ์‚ฐ์ž ์‚ฌ์šฉ -- addr, gender ํ•„๋“œ ๊ฐ’์œผ๋กœ ๊ทธ๋ฃนํ•‘์ด ๋จ
SQL
๋ณต์‚ฌ

ํ…Œ์ด๋ธ” ๊ฒฐํ•ฉ(JOIN)

ERM ๊ณผ ERD

ERM(Entity-Relationship Modelling)

๊ฐœ์ฒด-๊ด€๊ณ„ ๋ชจ๋ธ๋ง
โ€ข
๊ฐœ์ฒด(Entity) : ํ•˜๋‚˜ ์ด์ƒ์˜ ์†์„ฑ(Attribute)์œผ๋กœ ๊ตฌ์„ฑ๋œ ๊ฐœ์ฒด
์‰ฝ๊ฒŒ ์ƒ๊ฐํ•ด์„œ ๊ฐœ์ฒด == ํ…Œ์ด๋ธ” ์ด๊ณ  ์†์„ฑ == ํ•„๋“œ ์ด๋‹ค.
โ€ข
๊ด€๊ณ„(Realtionship) : ๊ฐœ์ฒด(ํ…Œ์ด๋ธ”)๊ฐ„์˜ ๊ด€๊ณ„

ERD(Entity-Relationship Diagram)

๊ฐœ์ฒด ๊ฐ„์˜ ๊ด€๊ณ„๋ฅผ ๋„ํ‘œ๋กœ ํ‘œํ˜„
FK ๋Š” ๋‹ค๋ฅธ ํ…Œ์ด๋ธ”์—์„œ์˜ PK ๋ฅผ ๋œปํ•œ๋‹ค.
PK ๋Š” ํ•œ ํ…Œ์ด๋ธ” ๋‹น ๋ฌด์กฐ๊ฑด ํ•˜๋‚˜์ด์ง€๋งŒ, FK ๋Š” ํ•œ ํ…Œ์ด๋ธ”์—์„œ ์—ฌ๋Ÿฌ ๊ฐœ๊ฐ€ ๋  ์ˆ˜ ์žˆ๋‹ค.

JOIN

1.
INNER ์กฐ์ธ
2.
LEFT OUTER ์กฐ์ธ (LEFT ์กฐ์ธ)
3.
RIGHT OUTER ์กฐ์ธ
4.
๊ทธ ์™ธ ๊ธฐํƒ€ (๋งŽ์ด ์•ˆ ์“ฐ๋Š” ๋“ฏ)
customer ํ…Œ์ด๋ธ”
sales ํ…Œ์ด๋ธ”
product ํ…Œ์ด๋ธ”

INNER JOIN

ON ์ ˆ๊ณผ ํ•จ๊ป˜ ์‚ฌ์šฉ๋˜๋ฉฐ, ON ์ ˆ์˜ ์กฐ๊ฑด์„ ๋งŒ์กฑํ•˜๋Š” ๋ฐ์ดํ„ฐ๋งŒ์„ ๊ฐ€์ ธ์˜จ๋‹ค.
์•ž์— INNER ๋ฅผ ์ƒ๋žตํ•ด๋„ ๋œ๋‹ค. ( INNER JOIN == JOIN )
์ฒซ๋ฒˆ์งธํ…Œ์ด๋ธ”์ด๋ฆ„ INNER JOIN ๋‘๋ฒˆ์งธํ…Œ์ด๋ธ”์ด๋ฆ„ ON ์กฐ๊ฑด -- INNER ์ƒ๋žต ๊ฐ€๋Šฅ ์ฒซ๋ฒˆ์งธํ…Œ์ด๋ธ”์ด๋ฆ„ JOIN ๋‘๋ฒˆ์งธํ…Œ์ด๋ธ”์ด๋ฆ„ ON ์กฐ๊ฑด
SQL
๋ณต์‚ฌ
-- customer์˜ mem_no ์™€ sales์˜ mem_no ๊ฐ€ ๊ฐ™์€ ํ–‰๋งŒ ๊ฐ€์ ธ์˜ด SELECT * FROM customer INNER JOIN sales ON customer.mem_no = sales.mem_no; -- ํ…Œ์ด๋ธ”์— ๋ณ„์นญ์„ ๋‹ฌ์•„์„œ ์‚ฌ์šฉํ•˜๋Š” ๊ฒƒ๋„ ๊ฐ€๋Šฅ SELECT * FROM customer AS a INNER JOIN sales AS b ON a.mem_no = b.mem_no;
SQL
๋ณต์‚ฌ
SELECT * FROM customer INNER JOIN sales ON customer.mem_no = sales.mem_no WHERE customer.mem_no = '1000970'; -- customer ์™€ sales ํ…Œ์ด๋ธ” ๋‘˜ ๋‹ค mem_no ํ•„๋“œ๊ฐ€ ์žˆ์œผ๋ฏ€๋กœ ์–ด๋–ค ํ…Œ์ด๋ธ”์˜ mem_no ์ธ์ง€ ๋ช…์‹œํ•ด์ค˜์•ผํ•จ
SQL
๋ณต์‚ฌ

LEFT JOIN (LEFT OUTER JOIN)

JOIN ์„ ๊ธฐ์ค€์œผ๋กœ ์™ผ์ชฝ ํ…Œ์ด๋ธ”์€ ๋ฌด์กฐ๊ฑด ๋‹ค ๋ถˆ๋Ÿฌ์˜จ๋‹ค.
๊ฒฐํ•ฉ์„ ํ•  ๋•Œ ์™ผ์ชฝํ…Œ์ด๋ธ”์„ ๊ธฐ์ค€์œผ๋กœ
ON ์ ˆ์— ๋งค์นญ๋˜๋ฉด, ์˜ค๋ฅธ์ชฝํ…Œ์ด๋ธ”๊ณผ ๊ฒฐํ•ฉ์„ ํ•˜๊ณ  (INNER JOIN ๊ณผ ๋™์ผ)
ON ์ ˆ์— ๋งค์นญ๋˜์ง€ ์•Š๋Š” ์˜ค๋ฅธ์ชฝ ํ…Œ์ด๋ธ”์˜ ํ•„๋“œ๋Š” NULL ๋กœ ์ฑ„์šด๋‹ค.
-- LEFT JOIN -- ์™ผ์ชฝํ…Œ์ด๋ธ”(customer)๋Š” ๋ฌด์กฐ๊ฑด ๋‹ค ๋ถˆ๋Ÿฌ์˜ค๊ณ , -- ์˜ค๋ฅธ์ชฝํ…Œ์ด๋ธ”(sales)์€ ON ์ ˆ์—์„œ ๋งค์นญ๋˜๋Š” ๋ฐ์ดํ„ฐ๋Š” ๊ฒฐํ•ฉ์„ ํ•˜๊ณ , ๋งค์นญ์ด ๋˜์ง€ ์•Š์œผ๋ฉด NULL ๋กœ ๊ฒฐํ•ฉํ•œ๋‹ค SELECT * FROM customer LEFT JOIN sales ON customer.mem_no = sales.mem_no;
SQL
๋ณต์‚ฌ
์™ผ์ชฝํ…Œ์ด๋ธ”(customer)์€ ๋ฌด์กฐ๊ฑด ๋‹ค ๋ถˆ๋Ÿฌ์˜ค๊ณ , ๋งŒ์•ฝ ON ์ ˆ์—์„œ ๋งค์นญ์ด ๋˜์ง€ ์•Š์œผ๋ฉด ์˜ค๋ฅธ์ชฝ ํ…Œ์ด๋ธ”์˜ ํ•„๋“œ๋ฅผ ๋‹ค NULL ๋กœ ์ฑ„์šฐ๋Š” ๊ฒƒ์„ ํ™•์ธํ•  ์ˆ˜ ์žˆ๋‹ค.

RIGHT JOIN (RIGHT OUTER JOIN)

JOIN ์„ ๊ธฐ์ค€์œผ๋กœ ์˜ค๋ฅธ์ชฝ ํ…Œ์ด๋ธ”์€ ๋ฌด์กฐ๊ฑด ๋‹ค ๋ถˆ๋Ÿฌ์˜จ๋‹ค.
๊ฒฐํ•ฉ์„ ํ•  ๋•Œ ์˜ค๋ฅธ์ชฝํ…Œ์ด๋ธ”์„ ๊ธฐ์ค€์œผ๋กœ
ON ์ ˆ์— ๋งค์นญ๋˜๋ฉด, ์™ผ์ชฝํ…Œ์ด๋ธ”๊ณผ ๊ฒฐํ•ฉ์„ ํ•˜๊ณ  (INNER JOIN ๊ณผ ๋™์ผ)
ON ์ ˆ์— ๋งค์นญ๋˜์ง€ ์•Š๋Š” ์™ผ์ชฝ ํ…Œ์ด๋ธ”์˜ ํ•„๋“œ๋Š” NULL ๋กœ ์ฑ„์šด๋‹ค.
-- RIGHT JOIN -- ์˜ค๋ฅธ์ชฝํ…Œ์ด๋ธ”(sales)๋Š” ๋ฌด์กฐ๊ฑด ๋‹ค ๋ถˆ๋Ÿฌ์˜ค๊ณ , -- ์™ผ์ชฝํ…Œ์ด๋ธ”(customer)์€ ON ์ ˆ์—์„œ ๋งค์นญ๋˜๋Š” ๋ฐ์ดํ„ฐ๋Š” ๊ฒฐํ•ฉ์„ ํ•˜๊ณ , ๋งค์นญ์ด ๋˜์ง€ ์•Š์œผ๋ฉด NULL ๋กœ ๊ฒฐํ•ฉํ•œ๋‹ค SELECT * FROM customer RIGHT JOIN sales ON customer.mem_no = sales.mem_no WHERE customer.mem_no IS NULL; -- NULL / IS NULL ์‚ฌ์šฉ -- IS NULL ์„ ์‚ฌ์šฉํ•ด์„œ ์™ผ์ชฝํ…Œ์ด๋ธ”์˜ ํ•„๋“œ๊ฐ€ NULL ์ธ ํ–‰๋งŒ ๊ฐ€์ ธ์˜ด
SQL
๋ณต์‚ฌ

ํ…Œ์ด๋ธ” 3๊ฐœ join

/* ํ…Œ์ด๋ธ” 3๊ฐœ join */ SELECT * FROM sales LEFT JOIN customer ON sales.mem_no = customer.mem_no LEFT JOIN product ON sales.product_code = product.product_code;
SQL
๋ณต์‚ฌ
sales ํ…Œ์ด๋ธ”์„ ๊ธฐ์ค€์œผ๋กœ customer ์™€ product ํ…Œ์ด๋ธ”์„ left join ํ–ˆ๋‹ค.

์„œ๋ธŒ์ฟผ๋ฆฌ (Sub Query)

๋ฉ”์ธ์ฟผ๋ฆฌ ์•ˆ์— ์žˆ๋Š” ๋˜ ๋‹ค๋ฅธ ์ฟผ๋ฆฌ๋ฅผ ์„œ๋ธŒ์ฟผ๋ฆฌ๋ผ๊ณ  ํ•œ๋‹ค.
๋ชจ๋“  ์„œ๋ธŒ์ฟผ๋ฆฌ๋Š” SELECT ๋ช…๋ น๋ฌธ์œผ๋กœ ์‹œ์ž‘ํ•œ๋‹ค. (๋ชจ๋“  ์ฟผ๋ฆฌ๋ฌธ์€ ๋‹ค SELECT๋กœ ์‹œ์ž‘)
1.
SELECT์ ˆ ์„œ๋ธŒ์ฟผ๋ฆฌ : ํ…Œ์ด๋ธ” ํ•„๋“œ(์—ด) ๊ตฌํ•˜๊ธฐ
2.
FROM์ ˆ ์„œ๋ธŒ์ฟผ๋ฆฌ : ํ…Œ์ด๋ธ” ๊ตฌํ•˜๊ธฐ(์—ด ๋ฐ ํ…Œ์ด๋ธ” ์ด๋ฆ„๋„ ์ง€์ •)
3.
WHERE์ ˆ ์„œ๋ธŒ์ฟผ๋ฆฌ : ๋ฆฌ์ŠคํŠธ ๊ตฌํ•˜๊ธฐ

SELECT์ ˆ ์„œ๋ธŒ์ฟผ๋ฆฌ

select์ ˆ ์„œ๋ธŒ์ฟผ๋ฆฌ : ํ•„๋“œ(์—ด) ๊ตฌํ•˜๊ธฐ
SELECT์ ˆ ์•ˆ์—์„œ ๋˜ ๋‹ค๋ฅธ SELECT์ ˆ์„ ์‚ฌ์šฉํ•œ๋‹ค.
JOIN ์„ ์‚ฌ์šฉํ•˜์ง€ ์•Š๊ณ  JOIN ์„ ํ‰๋‚ด๋‚ผ ์ˆ˜ ์žˆ๋Š”๋ฐ, ์„ฑ๋Šฅ์ด ์•ˆ ์ข‹์•„์„œ JOIN ์„ ์“ฐ๋Š” ๊ฒƒ์ด ๋” ๋‚ซ๋‹ค.
SELECT ์„œ๋ธŒ์ฟผ๋ฆฌ์‚ฌ์šฉ
-- SELECT์ ˆ ์„œ๋ธŒ์ฟผ๋ฆฌ SELECT *, (SELECT gender FROM customer WHERE sales.mem_no = mem_no) AS gender FROM sales;
SQL
๋ณต์‚ฌ
234ms
join ์‚ฌ์šฉ
-- SELECT์ ˆ ์„œ๋ธŒ์ฟผ๋ฆฌ vs. JOIN ์ฒ˜๋ฆฌ ์†๋„ ๋น„๊ต SELECT sales.*, customer.gender FROM sales LEFT JOIN customer ON sales.mem_no = customer.mem_no;
SQL
๋ณต์‚ฌ
4.7ms
join ์ด select ์„œ๋ธŒ์ฟผ๋ฆฌ๋ณด๋‹ค ๋ฌด๋ ค 50๋ฐฐ๊ฐ€๋Ÿ‰ ๋น ๋ฅด๋‹ค

FROM์ ˆ ์„œ๋ธŒ์ฟผ๋ฆฌ

from์ ˆ ์„œ๋ธŒ์ฟผ๋ฆฌ : ์—ด ์ด๋ฆ„ ๋ฐ ํ…Œ์ด๋ธ”๋ช…(๋ณ„์นญ) ์ง€์ •
ํšŒ์› ๋‹น ์ฃผ๋ฌธํšŸ์ˆ˜ ๊ตฌํ•˜๊ธฐ
-- FROM์ ˆ ๊ด„ํ˜ธ ์•ˆ์—๋Š” ํ…Œ์ด๋ธ”์ด ์™€์•ผํ•จ -- -> GROUP BY ์ ˆ๋กœ ์ƒˆ๋กœ ์ƒ์„ฑ๋œ ํ…Œ์ด๋ธ”์„ ์‚ฌ์šฉ๊ฐ€๋Šฅ SELECT * FROM ( SELECT mem_no, count(order_no) AS ์ฃผ๋ฌธํšŸ์ˆ˜ FROM sales GROUP BY mem_no ) AS A; -- FROM์ ˆ ์„œ๋ธŒ์ฟผ๋ฆฌ๋ฅผ ์“ธ ๋•Œ์—๋Š” ๋งˆ์ง€๋ง‰์— alias(๋ณ„์นญ)์„ ๊ผญ ๋ถ™ํ˜€์ค˜์•ผํ•จ.
SQL
๋ณต์‚ฌ

WHERE์ ˆ ์„œ๋ธŒ์ฟผ๋ฆฌ

where์ ˆ ์„œ๋ธŒ์ฟผ๋ฆฌ : ๋ฆฌ์ŠคํŠธ ํ˜•ํƒœ ๋ฐ˜ํ™˜
-- WHERE์ ˆ ์„œ๋ธŒ์ฟผ๋ฆฌ -- ๊ฐ€์ž…๋…„๋„๊ฐ€ 2019๋…„์ธ ํšŒ์›์˜ ์ฃผ๋ฌธํšŸ์ˆ˜๋ฅผ ๊ตฌํ•˜๋Š” ์ฟผ๋ฆฌ๋ฌธ SELECT count(order_no) AS ์ฃผ๋ฌธํšŸ์ˆ˜ FROM sales WHERE mem_no IN ( SELECT mem_no FROM customer WHERE YEAR(join_date) = 2019);
SQL
๋ณต์‚ฌ
ย IN ๋‹ค์Œ์—๋Š” ๋ฆฌ์ŠคํŠธ๊ฐ€ ์™€์•ผํ•œ๋‹ค.
ย SELECT mem_no FROM customer WHERE YEAR(join_date) = 2019 ๊ฒฐ๊ณผ๋Š” (โ€™100001โ€™, โ€˜100002โ€™, โ€˜100003โ€™, ..) ์™€ ๊ฐ™์€ ๋ฆฌ์ŠคํŠธ ํ˜•ํƒœ์ด๋‹ค.
join์œผ๋กœ ๋˜‘๊ฐ™์ด ๊ตฌํ˜„ํ•œ ๋ฒ„์ „
-- join ๋ฒ„์ „ SELECT count(order_no) AS ์ฃผ๋ฌธํšŸ์ˆ˜ FROM sales JOIN customer ON sales.mem_no = customer.mem_no WHERE YEAR(customer.join_date) = 2019;
SQL
๋ณต์‚ฌ

์„œ๋ธŒ์ฟผ๋ฆฌ ์ •๋ฆฌ

select,from,where ์„œ๋ธŒ์ฟผ๋ฆฌ ์ค‘ ๊ฐ€์žฅ ๋งŽ์ด ์‚ฌ์šฉํ•˜๋Š” ์„œ๋ธŒ์ฟผ๋ฆฌ๋Š” from์ ˆ ์„œ๋ธŒ์ฟผ๋ฆฌ ์ด๋‹ค.
ํšŒ์› ๋‹น ๊ตฌ๋งคํšŸ์ˆ˜ ๋ฐ ํšŒ์›์ •๋ณด๋ฅผ ๊ตฌํ•˜๋Š” ์ฟผ๋ฆฌ๋ฌธ (from์„œ๋ธŒ์ฟผ๋ฆฌ + join)
-- from์„œ๋ธŒ์ฟผ๋ฆฌ ์™€ join ๊ฒฐํ•ฉ CREATE TEMPORARY TABLE sales_sub_query SELECT A.๊ตฌ๋งคํšŸ์ˆ˜, B.* FROM ( SELECT mem_no ,count(order_no) AS ๊ตฌ๋งคํšŸ์ˆ˜ FROM sales GROUP BY mem_no ) AS A -- ํšŒ์› ๋‹น ๊ตฌ๋งคํšŸ์ˆ˜ ํ…Œ์ด๋ธ” ์ƒ์„ฑ JOIN customer AS B ON A.mem_no = B.mem_no; SELECT * FROM sales_sub_query;
SQL
๋ณต์‚ฌ
cf. CREATE TEMPORARY TABLE
1.
๋ฐฉ๋ฒ•1 : ์ผ๋ฐ˜์ ์ธ create table ๋ฐฉ์‹
CREATE TEMPORARY TABLE table_name( column_1_definition, column_2_definition, ..., table_constraints );
SQL
๋ณต์‚ฌ
2.
๋ฐฉ๋ฒ•2 : select ์ฟผ๋ฆฌ๋ฌธ ์‚ฌ์šฉ (select ๋กœ ๋งŒ๋“ค์–ด์ง„ ๊ฒฐ๊ณผ๋ฅผ ๊ทธ๋Œ€๋กœ ์ž„์‹œํ…Œ์ด๋ธ”๋กœ ์ƒ์„ฑ)
CREATE TEMPORARY TABLE top_costomers SELECT p.customerNumber, c.customerName, ROUND(SUM(p.aount), 2) sales FROM payments p INNETER JOIN customers c ON c.customerNumber = p.customerNumber GROUP BY p.customerNumber ORDER BY sales DESC LIMIT 10;
SQL
๋ณต์‚ฌ