mariaDB 그룹핑과 연산 명령어 모음

mariaDB에서 그룹핑을 하는 명령어 GROUP BY과 수학 연산을 처리해주는 다양한 명령어들의 사용법을 정리합니다.

부분 문자열 찾기

만약 특정 열에서 값 일부만 얻고 싶을 때에는 어떻게 해야할까? 바로 LEFT()와 RIGHT()를 사용하면 된다. 두 함수는 특정 열에서 값 일부만을 반환해준다.

먼저 LEFT()는 왼쪽을 기준으로 작동한다. 아래의 코드를 확인해보자.

1	SELECT LEFT('특정 열', '반환할 문자열 갯수') FROM '테이블 이름';

위 코드는 왼쪽을 기준으로 특정 열의 값들을 입력한 문자열 갯수만큼을 반환해준다. RIGHT()도 원리는 동일하지만 기준점이 오른쪽이다.

1	SELECT RIGHT('특정 열', '반환할 문자열 갯수') FROM '테이블 이름';

특정 패턴으로 문자열 찾기

만약 특정 열의 값이 콤마로 나눠져 있는 등의 측정 패턴으로 분리가 가능하다면 함수 SUBSTRING_INDEX()를 사용하면 된다. 해당 함수의 사용법은 아래와 같다.

1	SELECT SUBSTRING_INDEX('특정 열','기준점', '반환할 데이터 갯수') FROM '테이블 이름';

위 코드는 특정 열의 값들을 기준점을 기준으로 나눈 후에 앞에서부터 입력한 갯수만큼을 반환한다.

기존의 열의 값을 변경하기

기존에 있던 하나의 열을 2개로 분리한다고 가정해보자. 그러면 1. 새로운 열을 만들고 2. 기존의 열에 있던 값들 중 일부를 분리한 후에 3. 새로운 열에 넣으면 된다.
1,2 번 과정은 배웠기 때문에 3번을 살펴볼 것이다.

기존의 열값의 일부로 새로운 열을 채우는 방법은 다음과 같다.

1 2	UPDATE '테이블 이름' SET '새로운 열 이름' = RIGHT('기존의 열 이름', 2);

위 코드는 새로운 열을 기존 열의 일부로 채운다. 여기서 말하는 기존 열의 일부는 오른쪽에서 2개의 문자열이다. 이렇게 하면 특정 열이 원자적 데이터로 구성되지 않은 경우라도 테이블을 삭제하지 않고 원자적 데이터 형태로 수정할 수 있다.

조건문 명령어 CASE

만약 특정 열을 다른 열의 값에 따라서 따라서 다르게 변경하고 싶다면 어떻게 해야할까? 그럴 때는 명령어 CASE를 사용하면 된다.

명령어 CASE는 조건을 여러 개 설정하여 그에 따라 값을 변경할 수 있도록 해준다. 예를 들어 생각해보자.

만약 ‘성별’ 카테고리에 ‘M’의 값이 들어있다면 ‘한글’ 카테고리의 값을 ‘남성’으로 변경하고, ‘F’가 들어있다면 ‘여성’으로 변경해야 한다고 가정하자.

그러면 명령어 CASE를 아래와 같이 사용할 수 있다.

UPDATE '테이블 이름'
    SET 한글 =
        CASE
            WHEN 성별 = 'M' THEN '남성'
            WHEN 성별 = 'F' THEN '여성'
            ELSE '알 수 없음'
        END;

ELSE는 어떤 것에도 속하지 않는 경우에 뒤의 값을 넣어준다.

참고로 조건문 명령어인 CASE는 INSERT, DELETE에서도 사용할 수 있다.

데이터 정렬하기

만약 특정 열의 값 알파벳 순서를 기준으로 정렬하고 싶다고 생각해보자. 보통 열의 순서는 입력된 시간 순으로 되어 있다. 이럴 때 명령어 ORDER BY를 사용한다. 사용법은 다음과 같다.

1 2	SELECT * FROM '테이블 이름' ORDER BY '기준 열 이름';

위 코드에서 사용한 ‘기준 열’은 순서를 변경하는데 사용할 값들을 지정한 것이다. 따라서 해당 명령을 입력했을 시 다른 열들의 순서는 알파벳 순서가 아닐 수 있으며, 오로지 기준 열을 기준으로 정렬된다.

그렇다면 명령어 ORDER BY는 어떤 순서로 정렬할까?

실험 결과 명령어 ORDER BY의 순서는 기호 - 숫자 - 알파벳이고, 알파벳의 경우 대소문자 구분을 하지 않는다.

여러 열로 정렬하기

만약에 하나의 열이 아니라 여러 개의 열을 기준으로 정리하고 싶다면 어떻게 쿼리를 보내야할까? 예를 들어서 ‘카테고리 1’을 가장 큰 기준으로 정렬하고 이후 세부적으로는 ‘카테고리 2’의 알파벳 순으로 정렬하고 싶다고 가정해보자.

그러면 아래와 같이 쿼리를 보내면 된다.

1 2	SELECT * FROM '테이블 이름' ORDER BY '카테고리 1', '카테고리 2';

위 코드는 첫 번째로 오는 열인 ‘카테고리 1’를 기준으로 레코드 순서를 정렬한다. 그 다음 ‘카테고리 1’순으로 정렬했을 때 겹치는 데이터들을 두 번째로 오는 ‘카테고리 2’을 기준으로 정렬한다.

역순으로 정렬하기

오름차순이 아니라 내림차순으로 정렬하고 싶을 때는 명령어 DESC를 ORDER BY에 오든 열 이름 뒤에 붙여주면 된다. 쿼리를 확인해보자.

1 2	SELECT * FROM '테이블 이름' ORDER BY '카테고리 1', '카테고리 2' DESC;

위의 쿼리는 ‘카테고리 1’는 알파벳 순서대로 정렬하고 같은 ‘카테고리 1’안의 데이터들은 ‘카테고리 2’ 역순으로 정렬하라고 명령한다. 그 결과는 다음과 같다.

만약 정렬하는 모든 열들을 내림차순으로 하고싶다면 어떻게 해야할까? 아래의 쿼리를 참고하자.

1 2	SELECT * FROM '테이블 이름' ORDER BY '카테고리 1' DESC, '카테고리 2' DESC;

덧셈 명령어 SUM

먼저 더하는 방법이다. 코드는 다음과 같다.

1	SELECT SUM('더하는 열 이름') FROM '테이블 이름';

위 코드는 지정할 열의 모든 값을 더해서 반환한다. 만약 어떤 조건을 추가하고 싶다면 WHERE을 사용할 수 있다.

1 2	SELECT SUM('더하는 열 이름') FROM '테이블 이름' WHERE '조건에 사용할 카테고리' = '조건';

만약 조건으로 사용할 카테고리의 모든 값들을 기준으로 결과값을 받고 싶을 때는 어떻게 해야할까? 위 코드를 해당 조건 갯수만큼 쿼리로 보낼 수도 있을 것이다.

SELECT SUM('더하는 열 이름') FROM '테이블 이름'
    WHERE '조건에 사용할 카테고리' = '조건 1';

SELECT SUM('더하는 열 이름') FROM '테이블 이름'
    WHERE '조건에 사용할 카테고리' = '조건 2';

SELECT SUM('더하는 열 이름') FROM '테이블 이름'
    WHERE '조건에 사용할 카테고리' = '조건 3';
.
.
.

하지만 이 방법은 너무 비효율적이다. 이럴 때는 명령어 GROUP BY를 사용해주면 한번에 결과를 받을 수 있다.

1 2	SELECT '조건에 사용할 카테고리' ,SUM('더하는 열 이름') FROM '테이블 이름' GROUP BY'조건에 사용할 카테고리';

만약 그룹 단위로 조건을 걸고 싶다면 WHERE 대신에 HAVING을 사용하면 된다.

1
2

SELECT '조건에 사용할 카테고리' ,SUM('더하는 열 이름') FROM '테이블 이름'
    GROUP BY'조건에 사용할 카테고리' HAVING COUNT('조건에 사용할 카테고리') = '조건';

평균 명렁어 AVG

명령어 AVG는 평균을 구해준다. 즉, SUM과 사용방법은 동일하지만 더하는 것이 아니라 평균을 구해주는 것이다. 사용법은 다음과 같다.

1 2	SELECT AVG('더하는 열 이름') FROM '테이블 이름' WHERE '조건에 사용할 카테고리' = '조건';

MIN과 MAX

최솟값을 구해주는 명령어 MIN과 최댓값을 구해주는 명령어인 MAX도 위의 연산 명령어들과 동일하다. 사용법은 다음과 같다.

SELECT MIN('더하는 열 이름') FROM '테이블 이름'
    WHERE '조건에 사용할 카테고리' = '조건';


SELECT MAX('더하는 열 이름') FROM '테이블 이름'
    WHERE '조건에 사용할 카테고리' = '조건';

개수를 세어주는 명령어 COUNT

명령어 COUNT는 해당 테이블의 레코드 개수를 확인할 수 있다. 사용법은 다음과 같다.

1	SELECT COUNT('열 이름') FROM '테이블 이름';

위 코드는 ‘열 이름’의 레코드 갯수를 반환한다 참고로 명령어 COUNT는 NULL값을 카운트하지 않는다.

중복 제거하는 명령어 DISTINCT

명령어 DISTINCT는 해당 데이터의 고유값들만 보여준다.

1	SELECT DISTINCT '열 이름' FROM '테이블 이름';

위 코드에서는 ‘열 이름’ 카테고리 값들 중에서 중복된 것을 제외한 고유값들만 반환한다. 참고로 명령어 DISTINCT는 NULL값도 포함해서 반환한다.

하지만 고유값 갯수를 세기 위해 COUNT를 추가한다면 COUNT는 NULL값을 세지 않는다. 즉, DISTINCT는 NULL값을 반환하지만 COUNT에서 무시하는 것이다.

1	SELECT COUNT(DISTINCT '열 이름') FROM '테이블 이름';

위 코드에서 만약 ‘열 이름’의 고유값이 NULL을 포함하여 6개였다면, ‘COUNT’는 5개라고 반환할 것이다.

결과 개수를 제한하는 명령어 LIMIT

만약 내게 학생들의 시험 점수 테이블이 있고 그 중 1,2등이 누군지 알고 싶다고 가정해보자. 그러면 아래와 같이 코드를 입력하면 된다.

1 2	SELECT '학생 이름 열', '시험 점수' FROM '테이블 이름' ORDER BY '시험 점수' DESC;

위 코드는 정렬된 모든 학생들의 시험 점수를 반환한다. 1,2 등을 찾을 수는 있지만 효율적이지는 않다. 1,2등만 반환하기 위해서는 위 코드에 명령어 LIMIT를 추가해보자.

1
2
3

SELECT '학생 이름 열', '시험 점수' FROM '테이블 이름'
    ORDER BY '시험 점수' DESC
    LIMIT 2;

그러면 원하는 정보만 바로 받아볼 수 있다.

만약 3등부터 5등까지만을 조회하고 싶다면 어떻게 해야 할까? 그 방법은 다음과 같다.

1
2
3

SELECT '학생 이름 열', '시험 점수' FROM '테이블 이름'
    ORDER BY '시험 점수' DESC
    LIMIT 2,3;

위 코드는 3번째부터 아래로 3개의 데이터를 보여달라는 의미이다. SQL에서는 0부터 순서로 시작하기 때문에 2가 세 번째 레코드를 의미한다. 결과는 다음과 같다.