MySQL SQL 문법

1. SQL DDL(Structured Query Language Data Definition Language)

관계형 데이터베이스에 저장되어 있는 데이터를 처리하는 언어를 SQL이라고 하며 그중 하나인 DDL을 살펴보자.

 

우리가 저장하고 싶은 데이터가 무엇인지 정확히 알고 테이블을 설계해야하는데, 이를 데이터 모델링이라고 한다. 이 테이블을 설계했으면 테이블 구조를 생성해야 한다. 이를 DDL(데이터를 정의)이라고 한다.

---

테이블 필드의 중요 속성

 

- PRIMARY KEY

 

테이블에서 레코드의 유일성을 정의하는 필드이다. (ex : 이메일, 주민등록번호)

PRIMARY KEY로 지정된 필드가 있는 경우 데이터베이스단에서 중복된 값을 갖는 레코드가 생기는 것을 방지한다. (PRIMARY KEY UNIQUENESS CONSTRAINT)

Composite primary key : primary key가 두개 혹은 그 이상의 필드로 정의되는 경우다.

 

- FOREIGN KEY

테이블의 특정 필드가 다른 테이블의 필드에서 오는 값을 갖는 경우이다.

 

- NOT NULL

필드의 값이 항상 존재해야 한다. (NULL이 존재할 수 없다.)

 

- DEFAULT value

필드에 값이 주어지지 않은 경우에는 기본 값을 정의해 준다.

ex) timestamp 타입 : CURRENT_TIMESTAMP를 사용하면 현재시간으로 설정해줌.

---

아래 관계형 데이터베이스 예제를 살펴보자.

 

세션 테이블에는 유저 아이디와 세션생성날짜, 어느 채널에서 유입됬는지 저장된다.

채널 테이블은 유입된 채널의 목록이 저장된다.

 

MySQL에서 위와 같이 데이터를 정의 하는 방법은 다음과 같다.

CREATE TABLE session (
    id int not null auto_increment primary key,
    user_id int not null,
    created timestamp not null default current_timestamp,
    channel_id int not null,
    -- 채널 테이블의 channel_id 필드를 외래키로 지정
    foreign key(channel_id) references channel(id) 
);

CREATE TABLE channel (
    id int auto_increment primary key,
    channel varchar(32) not null
);

세션 테이블에 채널테이블의 id 컬럼과 연결시키기 위해 foreign key로 연결할 수 있다.

 

참고 : DESCRIBE 테이블 이름 -> 테이블의 구성 이름과 타입을 알려줌

 

2. SQL DML(Structured Query Language Data Manipulation Language)

테이블의 데이터 검색, 삽입, 수정, 삭제하는 데 사용한다.

 

🏅 SELECT

테이블에서 레코드(수)를 읽어오는데 사용한다.

 

SELECT 문법은 아래 틀을 숙지하자

SELECT 필드이름1, 필드이름2, ...
FROM 테이블이름
WHERE 선택조건
GROUP BY 필드이름1, 필드이름2, ...
ORDER BY 필드이름 [ASC|DESC] -- 필드 이름 대신에 숫자 사용 가능
LIMIT N;

 

예시 1.

SELECT channel_id, COUNT(1) 
FROM prod.session 
GROUP BY 1;

👉result

prod라는 데이터베이스의 session 테이블에서 channel_id 필드를 그루핑하고 그 channel_id가 몇개씩 있는지 카운팅하여 출력하면됨 (여기서 만약 GROUP BY 2이면 SELECT 뒤에 나오는 필드중, 2번째에 해당된느 필드로 그루핑하라는 의미임.), 여기서 count(1), 괄호안에 1은 신경쓰지말고 그루핑했을때 레코드 수가 몇갠지 세주는 기능으로 생각하면 됨.

 

SQL은 Declarative Language로, C, Python, Java 언어로 이 SQL을 짠다면 스텝바이 스텝으로 코드를 구현해야 되는데, SQL은 내가 이런 결과를 원해! 라고 2~3줄만 써주면 SQL이 알아서 수행해 준다.

 

예시 2.

SELECT COUNT(1) FROM prod.session;

테이블의 모든 레코드 수를 카운트함.

 

count 안에는 1을 쓰던 *을 쓰던 아무값을 쓰더라도 상관없다. ( 딱하나 예외로 null만 아니면 된다. 즉 count(null) 이렇게 쓰면 안된다.)

count 안의 값이 null 만 아니면 1씩 계속 카운트한다.

 

- CASE WHEN

필드 값의 변환을 위해 사용 할 수 있다.

CASE
    WHEN 조건1 THEN 값1
    WHEN 조건2 THEN 값2
    ELSE 값3
END 필드이름

 

실제 예제로 아래와 같이 사용한다.

SELECT channel_id, CASE
    WHEN channel_id in (1, 5, 6) THEN 'Social-Media'
    WHEN channel_id in (2, 4) THEN 'Search-Engine'
    ELSE 'Something-Else'
END channel_type
FROM prod.session;

 

- NULL

• 값이 존재하지 않음을 나타내는 상수. 0이나 ""과는 다르다.
• 필드 지정시 값이 없는 경우 NULL로 지정 가능하다. (테이블 정의시 디폴트 값으로도 지정 가능)
• 어떤 필드의 값이 NULL인지 아닌지 비교는 특수 문법을 필요로 함(field1 is NULL 혹은 filed1 is not NULL)
• NULL이 사칙연산에 사용되면 항상 그 결과는 NULL 이다 (0+NULL = NULL, 0-NULL = NULL, 0*NULL = NULL, 0/NULL = NULL)

 

- COUNT

 

테이블 : prod.count_test

 

SELECT COUNT(1) FROM prod.count_test
SELECT COUNT(0) FROM prod.count_test
SELECT COUNT(NULL) FROM prod.count_test
SELECT COUNT(value) FROM prod.count_test
SELECT COUNT(DISTINCT value) FROM prod.count_test

👉result

7
7
0
6
4

카운트 안에 값이 null만 아니면 테이블의 레코드 수를 전부 카운팅한다.

카운트 안에 null이 있으면 카운트를 하지 않아서 0이 나온다.

COUNT(value)는 매번 카운트 안 값이 바뀌므로 NULL만 카운팅하지 않아서 6이 된다.

COUNT(DISTINCT value)로 중복을 제거하면 NULL, 1, 0, 4, 3 이렇게 5개가 있으므로 NULL을 제외한 4가 나온다.

 

- WHERE

 

• IN

WHERE channel_id in (3, 4) -- 반대로 not in으로 쓸 수도 있음
WHERE channel_id = 3 OR channel_id = 4
-- 위 두코드는 동일한 코드

• LIKE

대소문자 구별 없이 문자열 매칭 기능을 제공함

WHERE channel LIKE 'G%' -- G로 시작하는 문자열 찾음
WHERE channel LIKE '%o%' -- o가 들어있는 문자열 찾음
-- NOT LIKE 키워드로 반대로 사용 가능

• BETWEEN

날짜 범위에 사용 가능

 

 

- ORDER BY

 

디폴트 순서는 오름 차순

내림 차순을 원하면 DESC를 써줘야함.

여러개의 필드를 사용해서 정렬하려면 ORDER BY 1 DESC, 2, 3 이런식으로 콤마로 필드를 구분하면 됨

 

※ NULL 값이 들어있을 경우 순서

오름차순일 경우 NULL 값이 처음에 위치함.

내림차순일 경우 NULL 값이 마지막에 위치함.

 

 

이 외에도 다양한 STRING FUNCTIONS, 타입변환 함수들이 존재하는데 그때그때 찾아가면서 쓰자.

 

👨‍💻 GROUP BY

테이블의 레코드를 그룹핑하여 그룹별로 다양한 정보를 계산한다.

 

1. 먼저 그룹핑할 필드를 결정한다. (하나 이상의 필드가 될 수 있음)

2. 그룹별로 계산할 내용을 결정한다. (이때 Aggregate 함수를 사용한다 : COUNT, SUM, AVG, MIN, MAX, GROUP_CONCAT .. 등등)

 

이 함수를 사용할때는 보통 필드이름을 alias로 지정하는 것이 일반적이다.

 

예시 1.

SELECT
    LEFT(created, 7) AS mon, -- 왼쪽에서 7개까지 읽어서 연-월만 추출
    COUNT(DISTINCT user_id) AS user_count -- 유일한 유저를 카운팅
FROM prod.session
GROUP BY 1 -- GROUP By mon, GROUP BY LEFT(created, 7)과 동치
ORDER BY 1;

흔히 위 방식처럼 하는 것을 MAU(Monthly Active User) 를 구한 것이다.

월별로 유일한 사용자 수를 체크하는 것이다.

 

예시 2.

SELECT
    LEFT(s.created, 7) AS mon,
    c.channel,
    COUNT(DISTINCT user_id) AS mau
FROM session s
JOIN channel c ON c.id = s.channel_id
GROUP BY 1,2
ORDER BY 1 DESC, 2;

이 방식은 월별로 먼저 그루핑한뒤 다시 채널별로 그루핑한다. (예를들면 1월-페이스북, 2월-페이스북, ... 12월-페이스북, 1월-인스타그램 , 2월-인스타그램, ... 12월-인스타그램, 1월-트위터, .....)

 

이런식으로 월별, 채널별 유일한 사용자 수를 체크한다.

이때 잠깐 JOIN을 사용했는데, 기본적으로 INNER JOIN을 수행한다. 또한 alias 사용할 때 AS를 꼭 써줄 필요는 없다.

 

🏅 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하여 레코드를 추가할 수 있다.

-- 테이블이름 옆에 괄호열고 필드를 지정하면 거기에 맞게 값을 대입하면 된다.
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

조건을 기반으로 테이블에서 레코드를 삭제하거나 모든 레코드를 삭제(DELETE FROM)한다.

모든 레코드를 삭제하더라도 테이블은 계속 존재한다.

DELETE FROM prod.vital WHERE weight <= 0; -- 체중이 0 이하인 레코드 삭제
DELETE FROM prod.vital; -- 모든 레코드 삭제 (테이블은 남아 있음)

 

TRUNCATE는 DELETE FROM 처럼 조건 없이 모든 레코드를 삭제하지만 속도가 빠르다. 하지만 트랜잭션을 사용하게 되면 롤백할 수 없다.

 

🏅 UPDATE

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

UPDATE prod.vital
SET weight = 92
WHERE vital_id = 4;

prod.vital 테이블에 있는 vital_id가 4인 레코드의 모든 weight를 92로 set 한다. (update 시킴)

 

 

👨‍💻 JOIN

SQL의 조인은 두 개 이상의 테이블의 공통 필드를 가지고 통합할 수 있다.

앞서 배운 스타 스키마로 분산 되어있던 정보를 통합하는데 사용한다.

 

JOIN의 결과로 양쪽의 필드를 모두 가진 새로운 테이블이 만들어지게 된다.

 

- JOIN 문법

SELECT A.*, B.*
FROM raw_data.table1 A
____ JOIN raw_data.table2 B ON A.key1 = B.key1 and A.key2 = B.key2
WHERE A.ts >= '2019-01-01';

____ 에는 INNER, LEFT, RIGHT, CROSS 키워드가 들어간다. 

MySQL은 FULL 조인을 지원하지 않는다.

 

※ JOIN시 고려해야할 점

• 먼저 중복 레코드가 없어야 하고 PRIMARY KEY의 UNIQUENESS가 보장됨을 체크해야 한다.
• 조인하는 테이블 간의 관계를 명확히 파악해야 한다.
• 어느 테이블을 베이스로 잡을지(From에 들어갈 테이블) 결정해야 한다.

테이블 간의 관계는 다음과 같다.

1. One To One

예를들어 세션 테이블과 세션 채널 테이블은 일대일 관계이다. (특정 세션 1개에는 특정 채널로부터의 유입이 1개이기 때문)

 

2. One To Many

예를들어 주문서와 주문한 아이템의 관계이다. 주문서에는 여러 주문 아이템들이 들어갈 수 있다. (이때 중복이 발생하면 배로 문제가 발생하게 됨)

 

3. Many To One

방향만 바꾸면 One To Many와 동일함.

 

4. Many To Many

다대다 관계는 복잡하고 위험한 상황이 많이 발생하므로 되도록 일대일 관계나 다대일 관계로 바꿀수만 있다면 바꿔서 조인하는 것이 덜 위험하다.

---

JOIN에는 여러 방식이 있는데, 이 방식에 따라 어떤 레코드 들이 선택되고 어떤 필드들이 채워지는지 결정된다.

 

- JOIN 종류

 

출처 : https://theartofpostgresql.com/blog/2019-09-sql-joins/

1. INNER JOIN

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

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

 

SELECT * FROM prod.vital v
JOIN prod.alert a ON v.vital_id = a.vital_id;

👉result

 

2. LEFT JOIN

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

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

 

SELECT * FROM prod.vital v
LEFT JOIN prod.alert a ON v.vital_id = a.vital_id;

👉result

RIGHT JOIN은 LEFT JOIN과 위치만 반대일 뿐 동일하다.

 

3. FULL JOIN

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

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

 

SELECT * FROM prod.vital v
LEFT JOIN prod.alert a ON v.vital_id = a.vital_id
-- MySQL은 FULL JOIN을 지원해주지 않아서 UNION을 사용해야 함
UNION -- vs. UNION ALL은 중복없이 레코드가 추가됨
SELECT * FROM prod.vital v
RIGHT JOIN prod.alert a ON v.vital_id = a.vital_id;

👉result

4. CROSS JOIN

왼쪽 테이블과 오른쪽 테이블의 모든 레코드들의 조합으로 리턴한다.

 

SELECT * FROM prod.vital v CROSS JOIN prod.alert a;

👉result

5. SELF JOIN

동일한 테이블을 alias를 다르게 해서 자기 자신과 조인한다.

 

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

👉result