ํ˜ผ๊ณตS 3์ฃผ์ฐจ๐Ÿง

์ธ์†Œ๋ฆฌยท2022๋…„ 7์›” 24์ผ
0

ํ˜ผ๊ณตS

๋ชฉ๋ก ๋ณด๊ธฐ
3/6

3์ฃผ์ฐจ~๐Ÿ‘
1, 2์ฃผ์ฐจ ์ˆ˜์ •ํ•˜๊ณ  ์ด์ œ์„œ์•ผ 3์ฃผ์ฐจ ์”๋‹ˆ๋‹ค...^^
์˜ค๋Š˜๋„ ์–ด๊น€์—†์ด. SQL. ์กฐ๋งŒ๊ฐ„ ์ •๋ณตํ•  ๋“ฏ.


1. ๋ฐ์ดํ„ฐ ํ˜•์‹

๐Ÿ”ธ ์ •์ˆ˜ํ˜•

  • TINYINT: 1๋ฐ”์ดํŠธ(-128 ~ 127)
  • SMALLINT: 2๋ฐ”์ดํŠธ(-32,768 ~ 32,767)
  • INT: 4๋ฐ”์ดํŠธ(์•ฝ -21์–ต ~ +21์–ต)
  • BIGINT: 8๋ฐ”์ดํŠธ(์•ฝ -900๊ฒฝ ~ +900๊ฒฝ)

โ• Out of range: ์ž…๋ ฅ๊ฐ’์˜ ๋ฒ”์œ„๋ฅผ ๋ฒ—์–ด๋‚ฌ๋‹ค๋Š” ์˜๋ฏธ์ž…๋‹ˆ๋‹ค.

  • UNSIGNED: ์–‘์˜ ์ •์ˆ˜๋งŒ ํ‘œํ˜„ํ•ฉ๋‹ˆ๋‹ค.

๐Ÿ”ธ ๋ฌธ์žํ˜•

  • CHAR: ๊ณ ์ •๊ธธ์ด ๋ฌธ์žํ˜•
  • VARCHAR: ๊ฐ€๋ณ€๊ธธ์ด ๋ฌธ์žํ˜•

    CHAR์˜ ์žฅ์  - ์†๋„ ํ–ฅ์ƒ
    VARCHAR์˜ ์žฅ์  - ๊ณต๊ฐ„์„ ํšจ์œจ์ ์œผ๋กœ ์‚ฌ์šฉ ๊ฐ€๋Šฅ

๐Ÿ”ธ ๋Œ€๋Ÿ‰์˜ ๋ฐ์ดํ„ฐ ํ˜•์‹

  • LONGTEXT: ๋Œ€๋Ÿ‰์˜ ํ…์ŠคํŠธ(์ž๋ง‰, ๋Œ€๋ณธ ๋“ฑ)
  • LONGBLOB: ๋Œ€๋Ÿ‰์˜ ์ด์ง„ ๋ฐ์ดํ„ฐ(์‚ฌ์ง„์ด๋‚˜ ๋™์˜์ƒ ๋“ฑ)

๐Ÿ”ธ ์‹ค์ˆ˜ํ˜•

  • FLOAT: 4๋ฐ”์ดํŠธ(์†Œ์ˆ˜์  ์•„๋ž˜ 7์ž๋ฆฌ๊นŒ์ง€ ํ‘œํ˜„)
  • DOUBLE: 8๋ฐ”์ดํŠธ(์†Œ์ˆ˜์  ์•„๋ž˜ 15์ž๋ฆฌ๊นŒ์ง€ ํ‘œํ˜„)

๐Ÿ”ธ ๋‚ ์งœํ˜•

  • DATE: 3๋ฐ”์ดํŠธ(๋‚ ์งœ๋งŒ ์ €์žฅ. YYYY_MM_DD)
  • TIME: 3๋ฐ”์ดํŠธ(์‹œ๊ฐ„๋งŒ ์ €์žฅ. HH:MM:SS)
  • DATETIME: 8๋ฐ”์ดํŠธ(๋‚ ์งœ ๋ฐ ์‹œ๊ฐ„์„ ์ €์žฅ. YYYY_MM_DD HH:MM:SS)

2. ๋ณ€์ˆ˜์˜ ์‚ฌ์šฉ

๐Ÿ”ธ ํ˜•์‹

SET @๋ณ€์ˆ˜์ด๋ฆ„ = ๋ณ€์ˆ˜์˜ ๊ฐ’;
SELECT @๋ณ€์ˆ˜์ด๋ฆ„;

์ฒซ ๋ฒˆ์งธ ์ค„์€ ๋ณ€์ˆ˜๋ฅผ ์„ ์–ธํ•˜๊ณ  ๊ฐ’์„ ๋Œ€์ž…ํ•ฉ๋‹ˆ๋‹ค.
๋‘ ๋ฒˆ์งธ ์ค„์€ ๋ณ€์ˆ˜์˜ ๊ฐ’์„ ์ถœ๋ ฅํ•ฉ๋‹ˆ๋‹ค.

โ• ๋ณ€์ˆ˜๋Š” ์›Œํฌ๋ฒค์น˜๋ฅผ ์žฌ์‹œ์ž‘ํ•  ๋•Œ๊นŒ์ง€๋Š” ์œ ์ง€๋˜์ง€๋งŒ, ์ข…๋ฃŒํ•˜๋ฉด ์—†์–ด์ง‘๋‹ˆ๋‹ค.


SET @count = 3;
SELECT mem_name, height FROM member ORDER BY height LIMIT @count;

์œ„ ๋ฌธ์žฅ์€ LIMIT์— ๋ณ€์ˆ˜๋ฅผ ์‚ฌ์šฉํ•  ์ˆ˜ ์—†๊ธฐ ๋•Œ๋ฌธ์— ์˜ค๋ฅ˜๊ฐ€ ๋ฐœ์ƒํ•ฉ๋‹ˆ๋‹ค.
PREPARE์™€ EXECUTE๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ์•„๋ž˜์™€ ๊ฐ™์ด ๋ฐ”๊ฟ€ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค.

PREPARE mySQL FROM 'SELECT mem_name, height FROM member ORDER BY height LIMIT ?';
EXECUTE mySQL USING @count;

mySQL์— SELECT ๋ฌธ์„ ์ €์žฅํ•˜๊ณ  EXECUTE๋ฅผ ์‹คํ–‰ํ•  ๋•Œ, USING์œผ๋กœ ๋ฌผ์Œํ‘œ(?)์— @count ๋ณ€์ˆ˜์˜ ๊ฐ’์„ ๋Œ€์ž…ํ•ฉ๋‹ˆ๋‹ค.

3. ๋ฐ์ดํ„ฐ ํ˜• ๋ณ€ํ™˜

๐Ÿ”ธ ๋ช…์‹œ์ ์ธ ๋ณ€ํ™˜

  • CAST()
  • CONVERT()

CAST(๊ฐ’ AS ๋ฐ์ดํ„ฐ ํ˜•์‹ [(๊ธธ์ด)] )
CONVERT(๊ฐ’, ๋ฐ์ดํ„ฐ ํ˜•์‹ [(๊ธธ์ด)] )

  • CONCAT(): ๋ฌธ์ž๋ฅผ ์ด์–ด์ค๋‹ˆ๋‹ค.

๐Ÿ”ธ ์•”์‹œ์ ์ธ ๋ณ€ํ™˜

SELECT '100'+'200';

๋ฌธ์ž๋Š” ๋”ํ•  ์ˆ˜ ์—†์œผ๋ฏ€๋กœ ์ž๋™์œผ๋กœ ์ˆซ์ž 100๊ณผ 200์œผ๋กœ ๋ณ€ํ™˜ํ•ด์„œ ๋ง์…ˆ์„ ์ˆ˜ํ–‰ํ–ˆ์Šต๋‹ˆ๋‹ค.

SELECT CONCAT(100, '200');

์ˆซ์ž 100์ด ๋ฌธ์ž '100'์œผ๋กœ ๋ณ€ํ™˜๋˜์–ด์„œ ์—ฐ๊ฒฐ๋ฉ๋‹ˆ๋‹ค.

SELECT 100+'200';

๋ฌธ์ž '200'์ด ์ˆซ์ž 200์œผ๋กœ ์ž๋™ ๋ณ€ํ™˜๋˜์–ด 300์ด ์ถœ๋ ฅ๋ฉ๋‹ˆ๋‹ค.

4. ๋‘ ํ…Œ์ด๋ธ”์„ ๋ฌถ๋Š” ์กฐ์ธ

๐Ÿ”ธ ๋‚ด๋ถ€ ์กฐ์ธ(INNER JOIN)

์ผ๋Œ€๋‹ค(one to many) ๊ด€๊ณ„: ํ•œ์ชฝ ํ…Œ์ด๋ธ”์—๋Š” ํ•˜๋‚˜์˜ ๊ฐ’๋งŒ ์กด์žฌํ•ด์•ผ ํ•˜์ง€๋งŒ, ์—ฐ๊ฒฐ๋œ ๋‹ค๋ฅธ ํ…Œ์ด๋ธ”์—๋Š” ์—ฌ๋Ÿฌ ๊ฐœ์˜ ๊ฐ’์ด ์กด์žฌํ•  ์ˆ˜ ์žˆ๋Š” ๊ด€๊ณ„

SELECT <์—ด ๋ชฉ๋ก>
FROM <์ฒซ ๋ฒˆ์งธ ํ…Œ์ด๋ธ”>
โ €โ €โ €โ €โ € INNER JOIN <๋‘ ๋ฒˆ์งธ ํ…Œ์ด๋ธ”>
โ €โ €โ €โ €โ € ON <์กฐ์ธ๋  ์กฐ๊ฑด>
[WHERE ๊ฒ€์ƒ‰ ์กฐ๊ฑด]


๐Ÿ”ธ ์™ธ๋ถ€ ์กฐ์ธ

SELECT <์—ด ๋ชฉ๋ก>
FROM <์ฒซ ๋ฒˆ์งธ ํ…Œ์ด๋ธ”(LEFT ํ…Œ์ด๋ธ”)>
โ €โ €โ €โ €โ € <LEFT | RIGHT | FULL> OUTER JOIN <๋‘ ๋ฒˆ์งธ ํ…Œ์ด๋ธ”(RIGHT ํ…Œ์ด๋ธ”)>
โ €โ €โ €โ €โ € ON <์กฐ์ธ๋  ์กฐ๊ฑด>
[WHERE ๊ฒ€์ƒ‰ ์กฐ๊ฑด];

  • LEFT OUTER JOIN: ์™ผ์ชฝ ํ…Œ์ด๋ธ”์„ ๊ธฐ์ค€์œผ๋กœ ์™ธ๋ถ€ ์กฐ์ธ
  • RIGHT OUTER JOIN: ์˜ค๋ฅธ์ชฝ ํ…Œ์ด๋ธ”์„ ๊ธฐ์ค€์œผ๋กœ ์™ธ๋ถ€ ์กฐ์ธ
  • FULL OUTER JOIN: ์™ผ์ชฝ ์™ธ๋ถ€ ์กฐ์ธ๊ณผ ์˜ค๋ฅธ์ชฝ ์™ธ๋ถ€ ์กฐ์ธ์ด ํ•ฉ์ณ์ง„ ๊ฒƒ

๐Ÿ”ธ ๊ธฐํƒ€ ์กฐ์ธ

  • ์ƒํ˜ธ ์กฐ์ธ: ํ•œ์ชฝ ํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ํ–‰๊ณผ ๋‹ค๋ฅธ ์ชฝ ํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ํ–‰์„ ์กฐ์ธ์‹œํ‚ค๋Š” ๊ธฐ๋Šฅ
  • ํŠน์ง•
    -ON ๊ตฌ๋ฌธ์„ ์‚ฌ์šฉํ•  ์ˆ˜ ์—†์Šต๋‹ˆ๋‹ค.
    -๋žœ๋ค์œผ๋กœ ์กฐ์ธํ•˜๊ธฐ ๋•Œ๋ฌธ์— ๊ฒฐ๊ณผ์˜ ๋‚ด์šฉ์€ ์˜๋ฏธ๊ฐ€ ์—†์Šต๋‹ˆ๋‹ค.
    -์ƒํ˜ธ ์กฐ์ธ์˜ ์ฃผ ์šฉ๋„๋Š” ํ…Œ์ŠคํŠธํ•˜๊ธฐ ์œ„ํ•ด ๋Œ€์šฉ๋Ÿ‰์˜ ๋ฐ์ดํ„ฐ๋ฅผ ์ƒ์„ฑํ•  ๋•Œ์ž…๋‹ˆ๋‹ค.

    SELECT * FROM buy CROSS JOIN member;

ํšŒ์› ํ…Œ์ด๋ธ”(member)๊ณผ ๊ตฌ๋งค ํ…Œ์ด๋ธ”(buy)์˜ ์ƒํ˜ธ ์กฐ์ธ์ž…๋‹ˆ๋‹ค.


  • ์ž์ฒด ์กฐ์ธ: ์ž์‹ ์ด ์ž์‹ ๊ณผ ์กฐ์ธ

    SELECT <์—ด ๋ชฉ๋ก>
    FROM <ํ…Œ์ด๋ธ”> ๋ณ„์นญA
    โ €โ €โ €โ €โ € INNER JOIN <ํ…Œ์ด๋ธ”> ๋ณ„์นญB
    โ €โ €โ €โ €โ € ON <์กฐ์ธ๋  ์กฐ๊ฑด>
    [WHERE ๊ฒ€์ƒ‰ ์กฐ๊ฑด]

5. ์Šคํ† ์–ด๋“œ ํ”„๋กœ์‹œ์ €

ํ”„๋กœ๊ทธ๋ž˜๋ฐ ๊ธฐ๋Šฅ์ด ํ•„์š”ํ•  ๋•Œ ์‚ฌ์šฉํ•˜๋Š” ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ๊ฐœ์ฒด์ž…๋‹ˆ๋‹ค.

๐Ÿ”ธ ํ˜•์‹

DELIMITER $$
CREATE PROCEDURE ์Šคํ† ์–ด๋“œ ํ”„๋กœ์‹œ์ € ์ด๋ฆ„()
BEGIN

โ €โ €โ €โ €โ € ์ด ๋ถ€๋ถ„์— SQL ํ”„๋กœ๊ทธ๋ž˜๋ฐ ์ฝ”๋”ฉ

END $$
DELIMITER;
CALL ์Šคํ† ์–ด๋“œ ํ”„๋กœ์‹œ์ € ์ด๋ฆ„();

DELIMITER $$ ~ END $$ ์•ˆ์— ์ž‘์„ฑํ•˜๊ณ  CALL ๋กœ ํ˜ธ์ถœํ•ฉ๋‹ˆ๋‹ค.

6. IF

๐Ÿ”ธ ๊ธฐ๋ณธ ํ˜•์‹

IF <์กฐ๊ฑด์‹> THEN
โ €โ €โ €โ €โ € SQL ๋ฌธ์žฅ๋“ค
END IF;

โ• SQL ๋ฌธ์žฅ๋“ค์ด ๋‘ ๋ฌธ์žฅ ์ด์ƒ์ด๋ผ๋ฉด BEGIN~END๋กœ ๋ฌถ์–ด์ค˜์•ผ ํ•ฉ๋‹ˆ๋‹ค.

๐Ÿ”ธ IF~ELSE

์กฐ๊ฑด์— ๋”ฐ๋ผ ๋‹ค๋ฅธ ๋ถ€๋ถ„์„ ์ˆ˜ํ–‰ํ•ฉ๋‹ˆ๋‹ค.

7. ๋‚ ์งœ ๊ด€๋ จ ํ•จ์ˆ˜

  • CURRENT_DATE(): ์˜ค๋Š˜ ๋‚ ์งœ๋ฅผ ์•Œ๋ ค์ค๋‹ˆ๋‹ค.
  • CURRENT_TIMESTAMP(): ์˜ค๋Š˜ ๋‚ ์งœ ๋ฐ ์‹œ๊ฐ„์„ ํ•จ๊ป˜ ์•Œ๋ ค์ค๋‹ˆ๋‹ค.
  • DATEDIFF(๋‚ ์งœ1, ๋‚ ์งœ2): ๋‚ ์งœ2๋ถ€ํ„ฐ ๋‚ ์งœ1๊นŒ์ง€ ์ผ์ˆ˜๋กœ ๋ช‡์ผ์ธ์ง€ ์•Œ๋ ค์ค๋‹ˆ๋‹ค.

8. CASE

๐Ÿ”ธ ํ˜•์‹

CASE
โ €โ €โ €โ €โ € WHEN ์กฐ๊ฑด1 THEN
โ €โ €โ €โ €โ € โ €โ €โ €โ €โ € SQL ๋ฌธ์žฅ๋“ค1
โ €โ €โ €โ €โ € WHEN ์กฐ๊ฑด2 THEN
โ €โ €โ €โ €โ € โ €โ €โ €โ €โ € SQL ๋ฌธ์žฅ๋“ค2
โ €โ €โ €โ €โ € WHEN ์กฐ๊ฑด3 THEN
โ €โ €โ €โ €โ € โ €โ €โ €โ €โ € SQL ๋ฌธ์žฅ๋“ค3
โ €โ €โ €โ €โ € ELSE
โ €โ €โ €โ €โ € โ €โ €โ €โ €โ € SQL ๋ฌธ์žฅ๋“ค4
END CASE;

9. WHILE

๐Ÿ”ธ ํ˜•์‹

WHILE <์กฐ๊ฑด์‹> DO
โ €โ €โ €โ €โ € SQL ๋ฌธ์žฅ๋“ค
END WHILE;

๐Ÿ”ธ ITERATE

์ง€์ •ํ•œ ๋ ˆ์ด๋ธ”๋กœ ๊ฐ€์„œ ๊ณ„์† ์ง„ํ–‰ํ•ฉ๋‹ˆ๋‹ค.

๐Ÿ”ธ LEAVE

์ง€์ •ํ•œ ๋ ˆ์ด๋ธ”์„ ๋น ์ ธ๋‚˜๊ฐ‘๋‹ˆ๋‹ค. ์ฆ‰ WHILE๋ฌธ์ด ์ข…๋ฃŒ๋ฉ๋‹ˆ๋‹ค.

10. ๋™์  SQL

use market_db;
PREPARE myQuery FROM 'SELECT * FROM member WHERE mem_id = "BLK";
EXECUTE myQuery;
DEALLOCATE PREPARE myQuery;

PREPARE ๋ฌธ์—์„œ SELECT * FROM member WHERE mem_id = "BLK"๋ฅผ myQuery์— ์ž…๋ ฅ ์‹œ์ผœ๋†“๊ณ  EXECUTE myQuery ๋ฌธ์œผ๋กœ ์‹คํ–‰ํ•ฉ๋‹ˆ๋‹ค.
โ• ์‹คํ–‰ ํ›„์—๋Š” DEALLOCATE PREPARE๋กœ ๋ฌธ์žฅ์„ ํ•ด์ œํ•ด์ฃผ๋Š” ๊ฒƒ์ด ์ข‹์Šต๋‹ˆ๋‹ค.

๊ธฐ๋ณธ ๋ฏธ์…˜๐Ÿ”โœจ

p. 195 - ํ™•์ธ๋ฌธ์ œ 4๋ฒˆ ํ’€๊ณ  ์ธ์ฆ์ƒท!

์„ ํƒ ๋ฏธ์…˜๐Ÿ’ก๐ŸŒŸ

p. 183 - [์ข€ ๋” ์•Œ์•„๋ณด๊ธฐ] ์†์ฝ”๋”ฉ ์‹คํ–‰ํ•˜๊ณ  ๊ฒฐ๊ณผํ™”๋ฉด ์ธ์ฆ์ƒท!

profile
์ฝ”๋”ฉ์ด๋ผ๋Š” ๊ฐ์˜ฅ์— ๊ฐ‡ํ˜€ ์‚ฌ๋Š” ์‚ถ

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

comment-user-thumbnail
2022๋…„ 7์›” 24์ผ

์™€.. ์ฉ”์–ด์š”

๋‹ต๊ธ€ ๋‹ฌ๊ธฐ