๐Ÿ’พ MySQL -VIEW

suRanยท2022๋…„ 7์›” 16์ผ
0

๐Ÿ’พ MySQL

๋ชฉ๋ก ๋ณด๊ธฐ
7/9
post-thumbnail

๋ณธ ํฌ์ŠคํŒ…์€ ํ”„๋กœ๊ทธ๋ž˜๋จธ์Šค ๋ฏธ๋‹ˆ ๋ฐ๋ธŒ ์ฝ”์Šค๋ฅผ ๊ณต๋ถ€ํ•˜๋ฉฐ
ํ•™์Šต์„ ๊ธฐ๋กํ•˜๊ธฐ ์œ„ํ•œ ๋ชฉ์ ์œผ๋กœ ์ž‘์„ฑ๋œ ๊ธ€์ž…๋‹ˆ๋‹ค.

View๋ž€?

์ž์ฃผ selectํ•˜๋Š” ์ฟผ๋ฆฌ๊ฐ€ ์žˆ์„ ๋•Œ, ํ•ด๋‹น select์ฟผ๋ฆฌ๋กœ ๋งŒ๋“ค์–ด์ง„
๊ฐ€์ƒ ํ…Œ์ด๋ธ”์— ์ด๋ฆ„์„ ์ฃผ๊ณ  ๊ทธ๊ฒƒ์„ ์‚ฌ์šฉํ•˜์—ฌ ์งˆ์˜ํ•˜๋Š” ๊ฒƒ์ด๋‹ค.

๊ฐ€์ƒ ํ…Œ์ด๋ธ”์— ์ด๋ฆ„์„ ์ฃผ๋ฉด ๊ทธ๊ฒƒ์ด View๋กœ์„œ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ๋‹จ์— ์ €์žฅ๋œ๋‹ค.
์ฃผ์˜ํ•ด์•ผํ•  ์ ์€ View๋Š” ํ…Œ์ด๋ธ”๋กœ์„œ ์ €์žฅ๋˜๋Š” ๊ฒƒ์ด ์•„๋‹ˆ๋ผ๋Š” ๊ฒƒ์ด๋‹ค.

view๋Š” select์ฟผ๋ฆฌ๊ฐ€ view๋ผ๋Š” ์ด๋ฆ„์œผ๋กœ ์ €์žฅ๋˜๋Š” ๊ฒƒ์ด๋‹ค.
๋”ฐ๋ผ์„œ view๊ฐ€ ์‚ฌ์šฉ๋  ๋•Œ๋งˆ๋‹ค select๊ฐ€ ์‹คํ–‰๋œ๋‹ค.

view๋„ ๋งˆ์น˜ ํ…Œ์ด๋ธ”์ฒ˜๋Ÿผ ํŠน์ • ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์•„๋ž˜์— ์ €์žฅํ•  ์ˆ˜ ์žˆ๋‹ค.




๐Ÿ’ฟ View ์ƒ์„ฑ๋ฐฉ๋ฒ•

view ํ˜•์‹

CREATE OR REPLACE VIEW ๋ทฐ์ด๋ฆ„ AS SELECT ์งˆ์˜ ์ฟผ๋ฆฌ;

์˜ˆ์ œ

-- ์ž์ฃผ ์‚ฌ์šฉํ•˜๋Š” ์ฟผ๋ฆฌ
SELECT s.id, s.user_id, s.created, s.channel_id, c.channel
FROM session s
JOIN channel c ON c.id = s.channel_id;
  • ์•„๋ž˜ ์ฝ”๋“œ๋Š” ์œ„์˜ select๋ฌธ ์ฟผ๋ฆฌ๋ฅผ view๋กœ ์ƒ์„ฑํ•˜๋Š” ์ฝ”๋“œ์ด๋‹ค.

CREATE OR REPLACE VIEW test.suranSessionDetails AS
SELECT s.id, s.user_id, s.created, s.channel_id, c.channel
FROM session s
JOIN channel c ON c.id = s.channel_id;
  • ์ด์ œ test.suranSessionDetails๋ฅผ ๊ฐ€์ƒํ…Œ์ด๋ธ”์ฒ˜๋Ÿผ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๋‹ค.


SELECT * 
FROM test.suranSessionDetails
WHERE id = 7;

  • sql๋ฌธ์ด ๊ฐ„๊ฒฐํ•ด์ง„๋‹ค.



์ •๋ฆฌ

view๋Š” ์ž์ฃผ ์“ฐ๋Š” select์ฟผ๋ฆฌ๋ฅผ ๋งˆ์น˜ ํ…Œ์ด๋ธ”์ฒ˜๋Ÿผ DB์— ๋“ฑ๋กํ•˜๊ณ , ์‰ฝ๊ฒŒ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๊ฒŒ ํ•ด์ฃผ๋Š” ๋ฐฉ๋ฒ•์ด๋‹ค.
view๋„ ํ…Œ์ด๋ธ”์ฒ˜๋Ÿผ ํŠน์ • ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์•„๋ž˜์— ์ €์žฅํ•  ์ˆ˜ ์žˆ๊ณ ,
๋ทฐ ์ƒ์„ฑ ์‹œ์—๋Š” CREATE OR REPLACE VIEW ๋ทฐ์ด๋ฆ„ AS SELECT ์งˆ์˜ ์ฟผ๋ฆฌ; ๋ฅผ ์‚ฌ์šฉํ•œ๋‹ค.

profile
๊ฐœ๋ฐœ ๊ณต๋ถ€๋ฅผ ํ•ด๋ผ

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