interplanetary detour

Redis 클라이언트 접속

root@db2:~# redis-cli

127.0.0.1:6379>

데이터 저장

127.0.0.1:6379> set 1111 "JM JOO"

OK

127.0.0.1:6379> set 1112 "YH JOO"

OK

127.0.0.1:6379> set 1113 "KO HONG"

OK

127.0.0.1:6379> set 1114 "DJ HONG"

OK

데이터 검색

127.0.0.1:6379> get 1111

"JM JOO"

127.0.0.1:6379> get 1112

"YH JOO"

Key값 출력

127.0.0.1:6379> keys *

1) "1113"

2) "1111"

3) "1112"

4) "1114"

127.0.0.1:6379> keys *2

1) "1112"

Key 삭제

127.0.0.1:6379> del 1112

(integer) 1

127.0.0.1:6379> keys *

1) "1113"

2) "1111"

3) "1114"

Key 이름 변경

127.0.0.1:6379> rename 1113 1116

OK

127.0.0.1:6379> keys *

1) "1111"

2) "1116"

3) "1114"

랜덤 Key 검색

127.0.0.1:6379> randomkey

"1116"

127.0.0.1:6379> randomkey

"1114"

존재 여부 검색

127.0.0.1:6379> exists 1111

(integer) 1

127.0.0.1:6379> exists 1112

(integer) 0

Value 길이 확인

127.0.0.1:6379> strlen 1111

(integer) 6

전체 Key 삭제

127.0.0.1:6379> flushall

OK

127.0.0.1:6379> keys *

(empty array)

일정 시간 동안만 저장

127.0.0.1:6379> setex 1111 30 "JM JOO"

OK

127.0.0.1:6379> ttl 1111

(integer) 18

127.0.0.1:6379> ttl 1111

(integer) 5

127.0.0.1:6379> ttl 1111

(integer) 0

127.0.0.1:6379> ttl 1111

(integer) -2

127.0.0.1:6379> get 1111

(nil)

데이터 다중 일괄 저장 및 검색

127.0.0.1:6379> mset 1113 "NoSQL User Group" 1115 "PIT"

OK

127.0.0.1:6379> mget 1113 1115

1) "NoSQL User Group"

2) "PIT"

TABLE STRUCTURE

QUERY

EXPLAIN

 UNION

특징

• 여러 개의 테이블을 세로로 합치는 개념이다(추가적인 레코드 출력).
• UNION하는 두 테이블의 컬럼명이 다른 경우에는 첫 번째 테이블의 컬럼명으로 출력된다.
• 중복되는 데이터는 자동으로 DISTINCT해서 출력한다(UNION ALL 사용 시 전부 출력).
• LIMIT을 걸면 UNION을 통해 테이블을 합친 후에 수행한다. 괄호로 SELECT문을 묶는다면 해당 단일 쿼리 안에서 LIMIT을 수행하도록 할 수 있다.

JOIN

특징

• 여러 개의 테이블을 가로로 합치는 개념이다(레코드 당 추가적인 정보).
• 각 테이블에 같은 컬럼이 있을 때만 JOIN을 할 수 있으며, 그 컬럼이 '붙이기 위한 키워드'가 된다.
• JOIN은 INNER JOIN을 기본으로 사용하며, 일치하지 않는 값은 출력하지 않는다(곱집합).
• LEFT OUTER JOIN (LEFT JOIN) = 왼쪽 테이블을 우선시하는 합집합.
• RIGHT OUTER JOIN (RIGHT JOIN) = 오른쪽 테이블을 우선시하는 합집합.
• CROSS JOIN = 두 테이블의 레코드들의 모든 조합 (모든 경우의 수)
• INNER JOIN과 OUTER JOIN은 결합 조건을 ON 구에 적어야 한다. 비교 연산자 뿐만 아니라 논리 연산자 등 다양한 연산자로 조건을 설정할 수 있다.
• 컬럼명 간 등호를 사용하여 결합 조건을 설정할 경우 ON 대신 USING 구를 사용할 수도 있다.

서브 쿼리란 쿼리 안의 쿼리를 말한다. 서브 쿼리를 감싸고 있는 원래의 쿼리는 메인 쿼리라고 부른다.

SELECT [컬럼명] FROM [테이블명]

    WHERE [컬럼명] [연산자] (SELECT ~);

서브 쿼리는 위의 형식과 같이 괄호로 감싸며, 서브 쿼리에는 세미콜론을 사용하지 않는다.

아래의 예제들을 살펴보자.

WHERE절에서의 사용

SELECT -- (3)
    order_id, price
FROM -- (1)
    productorder
WHERE -- (2)
    price >= (
        SELECT -- (2-2)
            AVG(price)
        FROM -- (2-1)
            productorder
    );

WHERE절에서 서브 쿼리를 날리는 쿼리문이다. 서브 쿼리로 가져온 결과를 메인 쿼리의 조건으로 사용하고 있다.

SELECT절에서의 사용

SELECT -- (2)
    order_id, price,
    (
        SELECT -- (2-2)
            COUNT(*)
        FROM -- (2-1)
            productorder
    ) AS order_count
FROM -- (1)
    productorder
ORDER BY -- (3)
    price
LIMIT -- (4)
    3;

SELECT절에서 서브 쿼리를 사용했다. 원래라면 집약 함수와 일반 컬럼을 동시에 가져올 수 없기 때문에

SELECT
    order_id, price, COUNT(*)
~

이런 식의 쿼리를 작성하면 에러가 발생해 결과값을 도출할 수 없지만 서브 쿼리를 사용함으로써 가능케 하는 것이다.

HAVING절에서의 사용

SELECT
    customer_id, AVG(price)
FROM
    productorder
GROUP BY
    customer_id
HAVING
    AVG(price) < (
        SELECT
            AVG(price)
        FROM
            productorder
    );

WHERE절에서 사용한 것과 비슷하게 HAVING절에서도 서브 쿼리로 가져온 값으로 조건을 걸어줄 수 있다.

서로 다른 테이블

SELECT
    customer_id, customer_name
FROM
    customer
WHERE
    membertype_id = (
        SELECT
            membertype_id
        FROM
            membertype
        WHERE
            membertype = "할인 회원"
    );

메인 쿼리의 customer 테이블과는 다른 membertype 테이블에서 membertype 컬럼이 할인 회원인 멤버의 아이디만 가져와서 조건을 걸 수도 있다.

복수행 서브 쿼리

SELECT
    order_id, product_id
FROM
    productorder
WHERE
    product_id IN (
        SELECT
            product_id
        FROM
            product
        WHERE
            price >= 150
    );

위와 같이 서브 쿼리의 결과가 단일행이 아닌 복수행으로 반환되는 경우 일반적인 연산자(<, >, = 등...)는 사용할 수 없다. 그 대신 IN, ANY, ALL 등의 연산자를 통해 비교할 수 있다.

IN : 리스트에 이 값이 하나라도 있으면 1 반환.

ANY : 조건이 일치하는 값이 리스트에 하나라도 있으면 1 반환.

ALL : 리스트에 있는 값이 전부 조건에 일치하면 1 반환.

ANY, ALL 연산자

SELECT
    *
FROM
    product
WHERE
    stock < ANY (
        SELECT
            SUM(quantity)
        FROM
            productorder
        GROUP BY
            product_id
    );

위와 같은 방식으로 사용할 수 있다.

서브 쿼리에서의 LIMIT 오류 해결법

SELECT
    customer_id, customer_name
FROM
    customer
WHERE
    customer_id IN (
        SELECT
            customer_id
        FROM (
            SELECT
                customer_id
            FROM
                productorder
            ORDER BY
                price DESC
            LIMIT 3
        ) AS tmp
    );

서브 쿼리를 IN, ANY, ALL 등의 연산자를 통해 처리할 때 해당 서브 쿼리 내부에서 LIMIT 예약어 사용 시 MySQL 버전에 따라 오류가 발생할 수 있다. 이 때 서브 쿼리를 이중으로 중첩시켜서 제일 내부에 있는 서브 쿼리에서 LIMIT을 걸어주면 문제없이 LIMIT을 사용할 수 있다.

상관 서브 쿼리

SELECT
    product_id,
    product_name
FROM
    product
WHERE
    3 < (
        SELECT
            SUM(quantity)
        FROM
            productorder
        WHERE
            product.product_id = productorder.product_id
    );

상관 서브 쿼리의 실행 구조

상관 서브 쿼리란 기존의 메인 쿼리 이전에 서브 쿼리를 완전히 끝내고 그 결과값을 통해 메인 쿼리를 하는 방식과는 달리 메인 쿼리를 수행하는 중에 서브 쿼리도 수행하는 것이다. 서브 쿼리의 결과를 매번 비교해서 메인 쿼리에게 전달한다.

외래 키(Foreign Key)

어떤 테이블의 PRIMARY KEY 값이 다른 테이블의 컬럼 중에 포함될 경우 이것을 외래 키라고 한다.

예를 들어 customer 테이블과 order 테이블이 있을 때 customer 테이블에 있는 customer_id는 PK, order 테이블에 있는 customer_id는 FK이다.

customer 테이블에 존재하지 않는 값은 order 테이블에서도 외래 키로 사용할 수 없다.

ex) customer_id = 100 은 존재하지 않기 때문에 order 테이블에도 넣을 수 없음.

EXISTS

SELECT
    product_id, product_name
FROM
    product AS p
WHERE
    EXISTS (
        SELECT
            *
        FROM
            productorder AS po
        WHERE
            p.product_id = po.product_id
    );

EXISTS 뒤에 오는 서브 쿼리의 결과값이 존재하면 1을 반환(출력), 하나의 레코드도 리턴되지 않는다면 0을 반환한다.

존재하지 않으면 출력하는 NOT EXISTS도 사용 가능하다.

SELECT
    CAST("123" AS SIGNED) + 1;

위와 같이 CAST 함수를 이용하여 문자열 타입이었던 "123"을 정수형 123으로 치환해서 1을 더할 수 있게 된다.

SIGNED 대신 UNSIGNED 사용 시 출력값이 음수가 되면 out of range가 뜬다. 절대값 출력일 것이라 생각했으나 단순히 양수만 처리 가능한 것 같다.

SIGNED 대신 DECIMAL 사용 시 실수 처리 가능.

IFNULL(인수1, 인수2)

1. 인수1이 NULL이 아니면 인수1 반환, NULL이면 (2)번 과정으로.
2. 인수2가 NULL이 아니면 인수2 반환, NULL이면 NULL 반환.

SELECT
    *
FROM
    product
ORDER BY
    IFNULL(price, 999999) ASC;

price가 NULL일 경우에 값을 999999로 만들어서 맨 마지막에 위치하도록 정렬한다.

NULLIF(인수1, 인수2)

1. 인수1 = 인수2 → NULL 반환
2. 인수1 ≠ 인수2 → 인수1 반환

데이터가 NULL일 경우에 0이 아닌 NULL 그대로 나오게 해야할 경우 사용한다.

ex) 나눗셈의 분모 지정 시

First Match 기법을 사용하기 때문에 위의 조건부터 체크하며 하나의 조건이 만족하면 해당 처리를 수행 후 그 아래는 확인하지 않고 바로 END로 이동한다. 어떤 것에도 들어맞지 않으면 ELSE의 처리를 수행한다.

조건 CASE

SELECT
    customer, quantity,
    CASE
        WHEN quantity <= 3 THEN 1000
        WHEN quantity <= 7 THEN 1200
        WHEN quantity <= 10 THEN 1500
        ELSE 2000
    END AS delivery_fee
FROM
    delivery;

WHEN 뒤에 어떤 조건을 주고, THEN 뒤에 해당 조건을 만족한다면 실행할 동작을 작성한다.

만약 "WHEN quantity <= 10 THEN 1500"이 맨 위로 갈 경우 전부 맨 위에서 걸릴 것이기 때문에 항상 내려갈수록 조건의 범위가 좁아지도록 설정해주어야 한다(피라미드 형태).

값 CASE

SELECT
    customer,
    CASE delivery_time
        WHEN 1 THEN "오전"
        WHEN 2 THEN "오후"
        WHEN 3 THEN "야간"
        ELSE "지정 없음"
    END AS delivery_time2
FROM
    delivery;

WHEN 뒤에 어떤 값을 주고, CASE 뒤의 Column이 해당 값이라면 THEN 뒤의 값을 END AS 뒤의 별명으로 출력한다.

SELECT
    customer, quantity,
    IF(
        quantity > 5, "Yes", "No"
    ) AS novelty
FROM
    delivery;

IF 함수 안의 인수

• 첫 번째 : 조건
• 두 번째 : TRUE
• 세 번째 : FALSE

IF 함수 중첩

SELECT
    customer, quantity,
    IF(
        quantity > 5, "Yes",
        IF(
            quantity > 3, "Next Time", "No"
        )
    ) AS novelty
FROM
    delivery;

첫 번째 IF 함수의 FALSE 자리에 또 다른 IF 함수를 작성함으로써 중첩시킬 수 있다.

첫 번째 IF 함수가 TRUE일 경우 해당 값으로 진행되고, FALSE일 경우 안 쪽의 IF 함수를 확인한다.

CASE 또는 IF 함수는 주로 SELECT절에서 사용하지만,

WHERE절(조건 분기)과 ORDER BY절(분류 기준 재정의)에서도 사용할 수 있다.