DML과 JOIN

1. Insert/Update/Delete 설명

• MySQL에서 지원하는 컬럼타입

  • Numeric

    • INTEGER, INT, SMALLINT, TINYBIT, MEDIUMINT, BIGINT (정수)
    • DECIMAL(돈 관련할 때 유용), NUMERIC
    • FLOAT, DOUBLE, BIT (실수)

  • Date and Time

    • DATE, DATETIME, TIMESTAMP, TIME, YEAR

  • String

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

  • JSON

    • 다양한 JSON 조작 함수를 제공함

  • Spatial

    • 위도와 경도를 중심으로한 위치관련 타입

      
      

• Insert

  • -- 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 not null,
        user_id int
    );

    
    

  • -- INSERT
    INSERT INTO prod.vital(user_id, vital_id, date, weight) VALUES(100, 1, '2020-01-01', 75);
    INSERT INTO prod.vital(user_id, vital_id, date, weight) VALUES(100, 3, '2020-01-02', 78);
    INSERT INTO prod.vital(user_id, vital_id, date, weight) VALUES(101, 2, '2020-01-01', 90);
    INSERT INTO prod.vital(user_id, vital_id, date, weight) VALUES(101, 4, '2020-01-02', 95);
    INSERT INTO prod.vital(user_id, vital_id, date, weight) VALUES(999, 5, '2020-01-02', -1);
    INSERT INTO prod.vital(user_id, vital_id, date, weight) VALUES(999, 5, '2020-01-02', 10);
    
    INSERT INTO prod.alert VALUES(1, 4, 'WeightIncrease', '2020-01-02', 101);
    INSERT INTO prod.alert VALUES(2, NULL, 'MissingVital', '2020-01-04', 100);
    INSERT INTO prod.alert VALUES(3, NULL, 'MissingVital', '2020-01-04', 101);
    

    
    

• Delete

  • 조건을 기반으로 테이블에서 레코드 삭제

    • 테이블은 계속 존재

  • Truncate은 조건없이 모든 레코드를 삭제한다

    • 속도가 빠른 대신 트랜잭션 사용 시 roll back이 불가능

  • -- DELETE
    SELECT * FROM vital;
    DELETE FROM vital WHERE weight <= 0;
    DELETE FROM vital WHERE vital_id = 5;
    SELECT * FROM vital;
    DELETE FROM vital;
    SELECT * FROM vital;

• Update

  • 조건을 기반으로 테이블에서 특정 레코드의 필드값 수정

  • -- UPDATE
    SELECT * FROM vital WHERE vital_id = 4;
    UPDATE vital SET weight = 92 WHERE vital_id = 4;
    SELECT * FROM vital WHERE vital_id = 4;
    

  
  
  
  

2. Insert/Update/Delete 실습

• 전체 코드

  • -- prod는 guest 자격으로 수정이 불가능해서 
    -- test 데이터베이스에 jaeuk_vital, jaeuk_alert 테이블을 생성해서 썼다 
    
    show databases;
    use test;
    drop table if exists jaeuk_vital;
    CREATE TABLE jaeuk_vital(
        user_id int not null,
        vital_id int primary key,
        date timestamp not null,
        weight int not null
    );
    
    CREATE TABLE jaeuk_alert(
        alert_id int primary key,
        vital_id int,
        alert_type varchar(32),
        date timestamp not null,
        user_id int
    );
    
    -- INSERT
    INSERT INTO jaeuk_vital(user_id, vital_id, date, weight) VALUES(100, 1, '2020-01-01', 75);
    INSERT INTO jaeuk_vital(user_id, vital_id, date, weight) VALUES(100, 3, '2020-01-02', 78);
    INSERT INTO jaeuk_vital(user_id, vital_id, date, weight) VALUES(101, 2, '2020-01-01', 90);
    INSERT INTO jaeuk_vital(user_id, vital_id, date, weight) VALUES(101, 4, '2020-01-02', 95);
    INSERT INTO jaeuk_vital(user_id, vital_id, date, weight) VALUES(999, 5, '2020-01-02', -1);
    INSERT INTO jaeuk_vital(user_id, vital_id, date, weight) VALUES(999, 5, '2020-01-02', 10);
    
    INSERT INTO jaeuk_alert VALUES(1, 4, 'WeightIncrease', '2020-01-02', 101);
    INSERT INTO jaeuk_alert VALUES(2, NULL, 'MissingVital', '2020-01-04', 100);
    INSERT INTO jaeuk_alert VALUES(3, NULL, 'MissingVital', '2020-01-04', 101);
    
    
    -- DELETE
    SELECT * FROM jaeuk_vital;
    DELETE FROM jaeuk_vital WHERE weight <= 0;
    DELETE FROM jaeuk_vital WHERE vital_id = 5;
    DELETE FROM jaeuk_vital;
    SELECT * FROM jaeuk_vital;
    
    -- UPDATE
    SELECT * FROM jaeuk_vital WHERE vital_id = 4;
    UPDATE jaeuk_vital SET weight = 92 WHERE vital_id = 4;
    SELECT * FROM jaeuk_vital WHERE vital_id = 4;

    
    
    
    

3. 다양한 JOIN 살펴보기

• JOIN이란?

  • SQL 조인은 두 개 이상의 테이블들을 공통 필드를 가지고 통합하는 방법

    • 스타 스키마로 구성된 테이블들로 분산되어 있던 정보를 통합하는데 사용

  • JOIN의 결과로 양쪽의 필드를 모두 가진 테이블이 만들어짐

    • 어떤 레코드들이 선택되는지

    • 어떤 필드가 포함되는지

      
      

• INNER JOIN

  • 두 개 이상의 테이블들이 공통적으로 갖고 있는 칼럼만 통합

  • 새로 생긴 테이블은 다 채워져있음

    
    

• LEFT/RIGHT JOIN

  • 한 쪽을 기준으로 통합하는 방법

  • 주로 LEFT랑 INNER를 많이씀

  • base에 있는 칼럼은 다 있지만, base에 없는 칼럼은 null로 채워짐

    
    

• FULL OUTER JOIN

  • MySQL은 공식적으로 지원하지 않음
  • LEFT/RIGHT JOIN 둘다 해서 UNION하면 가능은 함 근데 굳이 해야할까
  • 양쪽의 공통 칼럼만 나오고 나머지는 null 처리
  
  

• CROSS JOIN

  • 모든 가능한 조합을 다 통합하는 방법

    
    

• SELF JOIN

  • 내 자신의 칼럼과 어떤 칼럼을 통합해서 쓰는 방법

  • SELECT * 
    FROM vital v1 JOIN vital v2 ON v1.vital_id = v2.vital_id; 

    
    

• JOIN 시 고려해야할 점

  • 중복 레코드가 없고 Primary key의 유일성이 보장됨을 체크
  • 조인하는 테이블 간의 관계를 명확하게 정의해야함
  • 어느 테이블을 베이스로 잡을지 결정해야함
  
  
  
  

4. JOIN 실습

   • 전체 코드

     • select * from jaeuk_vital;
       select * from jaeuk_alert;
       
       -- INNER JOIN
       SELECT * FROM jaeuk_vital v
       JOIN jaeuk_alert a ON v.vital_id = a.vital_id;
       
       -- LEFT JOIN
       SELECT * FROM jaeuk_vital v
       LEFT JOIN jaeuk_alert a ON v.vital_id = a.vital_id;
       
       -- RIGHT JOIN
       SELECT * FROM jaeuk_vital v
       RIGHT JOIN jaeuk_alert a ON v.vital_id = a.vital_id;
       
       -- OUTER JOIN
       SELECT * FROM jaeuk_vital v
       LEFT JOIN jaeuk_alert a ON v.vital_id = a.vital_id
       UNION
       SELECT * FROM jaeuk_vital v
       RIGHT JOIN jaeuk_alert a ON v.vital_id = a.vital_id;
       
       
       -- CROSS JOIN
       SELECT * FROM jaeuk_vital v CROSS JOIN prod.alert a;
       
       -- SELF JOIN
       SELECT * FROM jaeuk_vital v1
       JOIN prod.vital v2 ON v1.vital_id = v2.vital_id;