TIL - 7

ํƒœ๊ทธ
DB
๋‚ ์งœ
Mar 31, 2022
์†์„ฑ

INERT / UPDATE / DELETE

MYSQL์—์„œ ์ง€์›ํ•˜๋Š” ์ปฌ๋Ÿผ ํƒ€์ž… (1)

Numeric Type

  • INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT
  • DECIMAL, NUMBERIC
  • FLOAT, DOUBLE, BIT

Data and Time type

  • DATE, DATETIME< TIMESTAMP, TIME, YEAR

String Type

  • CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, SET

JSON type

  • ๋‹ค์–‘ํ•œ JSON ์กฐ์ž‘ํ•จ์ˆ˜๋ฅผ ์ œ๊ณตํ•จ

Spatial type

  • ์œ„๋„์™€ ๊ฒฝ๋„๋ฅผ ์ค‘์‹ฌ์œผ๋กœํ•œ ์œ„์น˜ ๊ด€๋ จ ํƒ€์ž…

INSERT

create table prod.vital( user_id int not null , vital_id int primary key , date timestamp not null , weight int not null ); create table prod.alert ( alert_id int primary key , vital_id int, alert_type varchar(32), date timestamp, user_id int );

๋ ˆ์ฝ”๋“œ ์ถ”๊ฐ€ํ•ด๋ณด๊ธฐ

insert into prod.vital(user_id, vital_id, date, weight) values(100, 1, '2020-01-01',75); ...

DELETE

  • ์กฐ๊ฑด์„ ๊ธฐ๋ฐ˜์œผ๋กœ ํ…Œ์ด๋ธ”์—์„œ ๋ ˆ์ฝ”๋“œ ์‚ญ์ œ ํ˜น์€ ๋ชจ๋“  ๋ ˆ์ฝ”๋“œ ์‚ญ์ œ
    • ํ›„์ž์˜ ๊ฒฝ์šฐ์—๋„ ํ…Œ์ด๋ธ”์€ ๊ณ„์† ์กด์žฌํ•œ๋‹ค.
  • DELETE FROM vs TRUNCATE
    • ์ฐจ์ด์ ์„ ์ดํ•ดํ•˜๋Š” ๊ฒƒ์ด ์ค‘์š”ํ•˜๋‹ค
    • TRUNCATE๋Š” ์กฐ๊ฑด ์—†์ด ๋ชจ๋“  ๋ ˆ์ฝ”๋“œ ์‚ญ์ œ / ์†๋„๊ฐ€ ๋น ๋ฅธ ๋Œ€์‹  ํŠธ๋žœ์žญ์…˜ ์‚ฌ์šฉ์‹œ ๋กค๋ฐฑ ๋ถˆ๊ฐ€
DELETE FROM prod.vital where weight <= 0; DELETE FROM prod.vital

UPDATE

  • ์กฐ๊ฑด์„ ๊ธฐ๋ฐ˜์œผ๋กœ ํ…Œ์ด๋ธ”์—์„œ ํŠน์ • ๋ ˆ์ฝ”๋“œ์˜ ํ•„๋“œ ๊ฐ’ ์ˆ˜์ • ๊ฐ€๋Šฅ

JOIN ์ด๋ž€?

  • SQL ์กฐ์ธ์€ ๋‘ ๊ฐœ ์ด์ƒ์˜ ํ…Œ์ด๋ธ”๋“ค์„ ๊ณตํ†ต ํ•„๋“œ๋ฅผ ๊ฐ€์ง€๊ณ  ํ†ตํ•ฉ
    • ์Šคํƒ€ ์Šคํ‚ค๋งˆ๋กœ ๊ตฌ์„ฑ๋œ ํ…Œ์ด๋ธ”๋“ค๋กœ ๋ถ„์‚ฐ๋˜์–ด ์žˆ๋˜ ์ •๋ณด๋ฅผ ํ†ตํ•ฉํ•˜๋Š”๋ฐ ์‚ฌ์šฉ
  • JOIN์˜ ๊ฒฐ๊ณผ๋กœ ์–‘์ชฝ์˜ ํ•„๋“œ๋ฅผ ๋ชจ๋‘ ๊ฐ€์ง„ ์ƒˆ๋กœ์šด ํ…Œ์ด๋ธ”์ด ๋งŒ๋“ค์–ด์ง
    • ์กฐ์ธ์˜ ๋ฐฉ์‹์— ๋”ฐ๋ผ ๋‘๊ฐ€์ง€๊ฐ€ ๋‹ฌ๋ผ์ง„๋‹ค.
      • ์–ด๋–ค ๋ ˆ์ฝ”๋“œ๋“ค์ด ์„ ํƒ๋˜๋Š”์ง€?
      • ์–ด๋–ค ํ•„๋“œ๋“ค์ด ์ฑ„์›Œ์ง€๋Š”์ง€?

JOIN ๋ฌธ๋ฒ•

SELECT A.*, B.* FROM raw_data table A __ JOIN raw_data.table2 B ON A.key1 = B.key1 and A.key2 = B.key2 where A.ts >= '2019-01-01';

JOIN์‹œ ๊ณ ๋ คํ•ด์•ผ ํ•  ์ 

  • ๋จผ์ € ์ค‘๋ณต ๋ ˆ์ฝ”๋“œ๊ฐ€ ์—†๊ณ  ๊ธฐ๋ณธํ‚ค์˜ ์œ ์ผ์„ฑ์ด ๋ณด์žฅ๋จ์„ ์ฒดํฌํ•ด์•ผํ•œ๋‹ค
    • ์•„์ฃผ์ค‘์š”.!!
  • ์กฐ์ธํ•˜๋Š” ํ…Œ์ด๋ธ”๊ฐ„์˜ ๊ด€๊ณ„๋ฅผ ๋ช…ํ™•ํ•˜๊ฒŒ ์ •์˜
    • One to One
      • ์™„์ „ํ•œ one to one : session & session_channel
      • ํ•œ์ชฝ์ด ๋ถ€๋ถ„์ง‘ํ•ฉ์ด ๋˜๋Š” one to one
    • Ont to Many(order vs order_items)
      • ํ•˜๋‚˜์˜ ์ฃผ๋ฌธ์—๋Š” ์—ฌ๋Ÿฌ๊ฐœ์˜ ์ƒํ’ˆ
      • ์ด ๊ฒฝ์šฐ ์ค‘๋ณต์ด ๋” ํฐ ๋ฌธ์ œ๊ฐ€๋จ -> ์ฆํญ!
    • Many to One
      • ๋ฐฉํ–ฅ๋งŒ ๋ฐ”๊พธ๋งŒ one to many๋กœ ๋ณด๋Š”๊ฒƒ๊ณผ ์‚ฌ์‹ค์ƒ ๋™์ผ
    • Many to many
      • ์ชผ๊ฐœ์ž.
  • ์–ด๋А ํ…Œ์ด๋ธ”์„ ๋ฒ ์ด์Šค๋กœ ์žก์„์ง€ ๊ฒฐ์ •ํ•ด์•ผํ•จ

JOIN์˜ ์ข…๋ฅ˜

  • INNER ์กฐ์ธ
  • LEFT ์กฐ์ธ
  • RIGHT ์กฐ์ธ
  • FULL OUTER ์กฐ์ธ > mysql์€ ์ด๋ฅผ ์ง€์›ํ•˜์ง€ ์•Š์Œ
  • SELF ์กฐ์ธ
  • CROSS ์กฐ์ธ

INNER ์กฐ์ธ

  • ์–‘์ชฝ ํ…Œ์ด๋ธ”์—์„œ ๋งค์น˜๊ฐ€ ๋˜๋Š” ๋ ˆ์ฝ”๋“œ๋“ค๋งŒ ๋ฆฌํ„ดํ•จ
  • ์–‘์ชฝ ํ…Œ์ด๋ธ”์˜ ํ•„๋“œ๊ฐ€ ๋ชจ๋“œ ์ฑ„์›Œ์ง„ ์ƒํƒœ๋กœ ๋ฆฌํ„ด๋จ
select * from prod.vital v join prod.alert a on v.vital_id = a.vital_id;

FULL ์กฐ์ธ

  • ์™ผ์ชฝ ํ…Œ์ด๋ธ”๊ณผ ์˜ค๋ฅธ์ชฝ ํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ๋ ˆ์ฝ”๋“œ๋“ค์„ ๋ฆฌํ„ดํ•จ
  • ๋งค์นญ๋˜๋Š” ๊ฒฝ์šฐ์—๋งŒ ์–‘์ชฝ ํ…Œ์ด๋ธ”๋“ค์˜ ๋ชจ๋“  ํ•„๋“œ๋“ค์ด ์ฑ„์›Œ์ง„ ์ƒํƒœ๋กœ ๋ฆฌํ„ด๋จ
select * from prod.vital v LEFT join prod.alert a on v.vital_id = a.vital_id; UNION select * from prod.vital v RIGHT join prod.alert a on v.vital_id = a.vital_id;

CROSS ์กฐ์ธ

  • ์™ผ์ชฝ ํ…Œ์ด๋ธ”๊ณผ ์˜ค๋ฅธ์ชฝ ํ…Œ์ด๋ธ”์˜ ๋ชจ๋“  ๋ ˆ์ฝ”๋“œ๋“ค์˜ ์กฐํ•ฉ์„ ๋ฆฌํ„ดํ•จ
select * from prod.vital v cross join prod.alert a

self ์กฐ์ธ

  • ๋™์ผํ•œ ํ…Œ์ด๋ธ”์„ alias๋ฅผ ๋‹ฌ๋ฆฌํ•ด์„œ ์ž๊ธฐ ์ž์‹ ๊ณผ ์กฐ์ธ
select * from prod.vital v1 join prod.vital v2 on v1.vital_id = v2.vital_id;