📝SQL 기본 다지기(Insert,Update,Delete,Join)

📝9일차 데이터베이스 실습

1. MySQL에서 지원하는 컬럼 타입

• Numeric Type
  • INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT
  • DECIMAL, NUMERIC
  • FLOAT, DOUBLE, BIT
• Date and Time Type
  • DATE, DATETIME, TIMESTAMP,TIME, YEAR
• String Type
  • CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, SET
• JSON Type
  • 다양한 JSON 조작함수를 제공함
• Spatial Type
  • 위도와 경도를 중심으로한 위치 관련 타입

1. INSERT,DELETE,UPDATE

1️⃣INSERT

INSERT INTO 문을 사용하여 테이블에 새로운 레코드를 추가할 수 있다.

• 기본 문법 2가지

-- 1. 필드 이름을 넣어준다.
INSERT INTO 테이블이름(필드이름1, 필드이름2, 필드이름3, ...) VALUES (데이터값1, 데이터값2, 데이터값3, ...)

-- 2. 필드 이름을 생략.
INSERT INTO 테이블이름
   VALUES (데이터값1, 데이터값2, 데이터값3, ...)
--  필드의 이름을 생략할 수 있으며, 이 경우에는 데이터베이스의 스키마와 같은 순서대로 필드의 값이 자동 대입

2의 방법의 경우 생략할 수 있는 필드는 3가지가 있다.

• NULL을 저장할 수 있도록 설정된 필드
  
• DEFAULT 제약 조건이 설정된 필드
  
• AUTO_INCREMENT 키워드가 설정된 필드

• 2가지 테이블로 실습

-- prod.vital 생성 
create table prod.vital(
	user_id int not null,
    vital_id int primary key,
    date timestamp,
    weight int not null    
);

-- prod.alert 생성 
create table prod.alert(
	alert_id int primary key,
    vital_id int,
    alert_type varchar(32),
    date timestamp,
    user_id int     
);

-- INSERT 예시
insert into prod.vital(user_id,vital_id,date,weight) values(100,4,'2021-01-01',100);

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);


SELECT * FROM prod.alert WHERE vital_id = NULL; -- = 으로 비교하면 찾을 수가 없다.  
SELECT * FROM prod.alert WHERE vital_id IS NULL; -- is NULL 로 비교해야 찾을 수 있다.

SELECT * FROM prod.alert WHERE vital_id != NULL; -- != 는 null 로 비교가 불가능하다.
SELECT * FROM prod.alert WHERE vital_id IS NOT NULL; 

---

2️⃣DELETE

조건을 기반으로 테이블에서 레코드 삭제 혹은 모든 레코드 삭제한다.

• DELETE FROM vs. TRUNCATE
  • 차이점을 이해하는 것이 중요하다.
  • TRUNCATE은 조건없이 모든 레코드 삭제. 속도가 빠른 대신 트랜잭션 사용시 롤백(rollback) 이 불가하다.
  • TRUNCATE는 WHERE, 트랜잭션을 지원하지 않는다.
  • TRUNCATE는 전체 테이블의 내용 삭제시에는 여러모로 유리하다.
• DELETE를 실행해도 테이블은 남아있다.

• 기본 문법

-- WHERE 절의 조건을 만족하는 레코드만을 삭제
DELETE FROM 테이블이름
WHERE 필드이름=데이터값;

-- 해당 테이블에 저장된 모든 데이터 삭제
DELETE FROM 테이블이름;

-- DELETE 예시
SELECT * FROM prod.vital;
DELETE FROM prod.vital WHERE weight <= 0;
DELETE FROM prod.vital WHERE vital_id = 5;
SELECT * FROM prod.vital;
DELETE FROM prod.vital;
SELECT * FROM prod.vital;

---

3️⃣UPDATE

조건을 기반으로 테이블에서 특정 레코드(들)의 필드 값 수정 가능하다.

• 기본 문법

UPDATE 테이블이름
SET 필드이름1=데이터값1, 필드이름2=데이터값2, ...
WHERE 필드이름=데이터값

-- UPDATE 예시
update prod.vital set weight=1000 where vital_id = 4;
-- vital_id가 4인 값의 weight 값을 수정한다.

---

2. JOIN

JOIN은 데이터베이스 내의 여러 테이블에서 가져온 레코드를 조합하여 하나의 테이블이나 결과 집합으로 표현해 준다.

주로 SELECT문과 함께 쓰인다.

⚠️JOIN시 고려해야 할점

• 먼저 중복 레코드가 없고 Primary Key의 uniqueness가 보장됨을 체크해야한다.
• 조인하는 테이블들간의 관계를 명확하게 정의한다.
  • One to one
    • 완전한 one to one: session & session_channel
    • 한쪽이 부분집합이 되는 one to one
  • One to many? (order vs order_items)
    • 이 경우 중복이 더 큰 문제됨 -> 증폭!!
  • Many to one?
    • 방향만 바꾸면 One to many로 보는 것과 사실상 동일.
  • Many to many?
    • 이는 one to one이나 one to many로 바꾸는 것이 가능하다면 변환하여 조인하는 것이 덜 위험하다.
  • 어느 테이블을 베이스로 잡을지 (From에 사용할지) 결정해야한다.
    
    

💿JOIN의 종류

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN
  • MySQL은 이를 지원하지 않음 LEFT JOIN과 RIGHT JOIN을 UNION하는 것으로 이를 대신할 수 있음
• SELF JOIN
• CROSS JOIN

1️⃣INNER JOIN

INNER JOIN은 ON 절과 함께 사용되며, ON 절의 조건을 만족하는 데이터만을 가져옵니다.

양쪽 테이블에서 매치가 되는 레코드들만 리턴한다.

양쪽 테이블의 필드가 모두 채워진 상태로 리턴된다.

• 기본 문법

FROM 첫번째 테이블이름
INNER JOIN 두번째테이블이름
ON 조건

FROM 첫번째 테이블이름
JOIN 두번째테이블이름
ON 조건

• ON 절에서는 WHERE 절에서 사용할 수 있는 모든 조건을 사용할 수 있다.
• MySQL에서는 JOIN, INNER JOIN, CROSS JOIN이 모두 같은 의미로 사용된다.

-- inner join
select * from prod.vital v
join prod.alert a on a.vital_id = v.vital_id;
 -- 값이 있는 것들만을 출력한다. (공통된 부분)

---

2️⃣LEFT JOIN

LEFT JOIN은 첫 번째 테이블을 기준으로, 두 번째 테이블을 조합하는 JOIN이다.

왼쪽 테이블(Base)의 모든 레코드들을 리턴한다.

오른쪽 테이블의 필드는 왼쪽 레코드와 매칭되는 경우에만 채워진 상태로 리턴된다.

• 기본 문법

FROM 첫번째테이블이름
LEFT JOIN 두번째테이블이름
ON 조건

-- left join
select * from prod.vital v
left join prod.alert a on a.vital_id = v.vital_id; -- inner와의 차이점은 오른쪽에 해당하는 쪽들은 null 값으로 채워져서 나온다.

---

3️⃣FULL JOIN

왼쪽 테이블과 오른쪽 테이블의 모든 레코드들을 리턴함

매칭되는 경우에만 양쪽 테이블들의 모든 필드들이 채워진 상태로 리턴된다.

• 코드 예시

-- outter join => 총 6개의 값을 return  한다.
select * from prod.vital v
left join prod.alert a on a.vital_id = v.vital_id
union -- union all 을 하게되면 중복되는것 까지 같이 출력한다. 
select * from prod.vital v
right join prod.alert a on a.vital_id = v.vital_id; 

• 결과
  

---

4️⃣CROSS JOIN

왼쪽 테이블과 오른쪽 테이블의 모든 레코드들의 조합을 리턴함

• 코드 예시

-- cross join => 각각 3, 4개의 레코드를 가지고 있는데 모든 경우의 수를 조합하여 출력한다. (3*4 = 12 개) 
select * from prod.vital v cross join prod.alert a;

• 결과

---

5️⃣SELF JOIN

동일한 테이블을 alias(별명)를 달리해서 자기 자신과 조인한다.

-- self join 
select * from prod.vital v
join prod.vital v2 on v.vital_id = v2.vital_id;

• 결과
  • 같은 값을 두번 출력한다.(같은 테이블)