개요
저는 개발자는 아니지만 업무상 데이터베이스를 자주 다루는 일이 많습니다!
그래서 자주 사용하는 SQL 쿼리문들을 정리해두고 필요할 때 바로 활용할 수 있도록 정리해보도록 하겠습니다!
make Query!
1. 데이터 정렬하기
로그 데이터를 보거나 작업 이력을 확인할 때 가장 기본이 되는 기능이죠.
ORDER BY절을 사용하면 특정 컬럼을 기준으로 데이터 정렬이 가능합니다.
최신순 정렬 (내림차순: DESC)
보통 최신 데이터를 가장 먼저 확인하고 싶을 때 사용합니다. id나 날짜/시간(created_at 등) 컬럼을 기준으로 DESC 키워드를 붙여주면 됩니다.
-- 'created_at' 컬럼을 기준으로 가장 최신 데이터부터 출력함d
SELECT *FROM your_table
ORDER BY created_at DESC;
-- 최신 데이터를 볼건데 10개만 보고 싶으면 LIMIT 키워드 함께 사용하기.
-- 최신 데이터 10개만 보고 싶다면 LIMIT을 함께 사용하세요.SELECT *FROM your_table
ORDER BY created_at DESC LIMIT 10;오래된 순 정렬 (오름차순: ASC)
가장 먼저 쌓인 데이터부터 순서대로 보고 싶을 때 사용합니다.
ASC 키워드를 사용하며, 사실 ORDER BY의 기본값이기 때문에 생략하셔도 좋아요.
2. 오래된 데이터 삭제하기
로그나 불필요한 데이터가 지속해서 쌓이면 DB 성능에 영향을 끼칠 수 있습니다 ..!
이럴 때 오래된 데이터를 주기적으로 삭제해주는 작업이 필요해요.
DELETE쿼리는 되돌릴 수 없는 매우 위험한 작업이기 때문에 실제 운영 DB에서 실행하기 전에 반드시 SELECT 문으로 삭제 대상을 정확히 확인하고, 가능하다면 기존 데이터를 백업해두는 프로세스를 구축해두시는 것을 강력 권장합니다.
삭제할 데이터 확인하기 (SELECT)
먼저, 어떤 데이터를 삭제할지 반드시 SELECT 문을 활용해 직접 눈으로 확인하세요!!
아래 코드는 가장 오래된 데이터 100개를 확인하는 예시입니다.
SELECT *FROM your_table
ORDER BY created_at ASCLIMIT 100;위 쿼리를 실행했을 때 나오는 결과가 내가 정말 삭제하려는 데이터가 맞는지 꼼꼼하게 확인해주세요.
확인한 데이터 삭제하기 (DELETE)
SELECT *를 DELETE로 바꾸기만 하면 됩니다.
위에서 확인한 가장 오래된 데이터 100개를 삭제하는 예시입니다.
DELETE FROM your_table
ORDER BY created_at ASCLIMIT 100;3. 오늘 데이터만 뽑아서 보기 (WHERE + 날짜 함수)
오늘 발생한 에러 로그만 보고 싶어 또는 오늘 가입한 사용자 수만 확인하고 싶어 등과 같이 특정 날짜의 데이터를 조회하는 일이 매우 흔하죠?
날짜/시간 컬럼(datetime 또는 timestamp 타입)을 기준으로 오늘 날짜에 해당하는 데이터만 조회하는 방법은 DBMS 종류에 따라 조금씩 다를 수 있습니다.
날짜 형식으로 변환하여 비교
날짜/시간 컬럼에서 시간 정보를 제외하고 '날짜' 부분만 추출해서 오늘 날짜와 비교하는 방식입니다.
-- DATE() 함수로 날짜 부분만 추출, CURDATE()는 오늘 날짜를 반환
SELECT *FROM your_table
WHERE DATE(created_at) = CURDATE();날짜 범위로 비교
대용량 테이블에서는 함수를 사용해 컬럼을 가공하는 것보다,
컬럼 자체를 그대로 두고 범위를 지정하는 방식이 인덱스 활용이 가능해 성능에 더 유리하다고 합니다.
-- 오늘 00:00:00 이후 그리고 내일 00:00:00 이전 데이터
SELECT *FROM your_table
WHERE created_at >= CURDATE() AND created_at < CURDATE() + INTERVAL '1' DAY;4. 그룹별로 묶어서 통계내기 (GROUP BY)
종류별로 데이터 개수를 파악하고 싶을 때 유용합니다.
예를 들어서, 시간대별 에러 발생 건수를 확인해야 한다면 아래와 같이 쿼리문을 작성해주면 됩니다.
-- HTTP 상태 코드(status_code) 별로 각각 몇 개씩 있는지 확인
SELECT status_code, COUNT(*)
FROM web_access_log
GROUP BY status_code;
-- 날짜별로 가입한 사용자 수 확인
SELECT DATE(created_at) as join_date, COUNT(*)
FROM users
GROUP BY join_date
ORDER BY join_date DESC;5. 특정 문자열이 포함된 데이터 찾기 (LIKE)
로그 메시지에서 특정 키워드(error,failed 등)가 포함된 데이터를 찾을 때 매우 유용합니다.
%는 어떤 문자든, 몇 개든 상관없다는 의미의 와일드카드라고 보시면 됩니다.
-- message 컬럼에 'error'라는 단어가 포함된 모든 로그를 찾음.
SELECT *FROM log_table
WHERE message LIKE '%error%';6. 중복 데이터 다루기 (DISTINCT, GROUP BY)
DB를 보다보면 사용자 ID, 상품 카테고리처럼 중복된 값이 들어있는 컬럼을 자주 만나게 됩니다.
이때 이런 고민 한 번쯤 해보셨을 거에요.
그래서 담당자가 총 몇 명이지?담당자별 작업 이력을 좀 모아서 보고 싶은데 ..
이번 챕터에서는 중복 데이터를 다루는 3가지 방법에 대해 알아보겠습니다.
6-1. 그래서 총 몇명인데? - 고유값 보기 (DISTINCT)
특정 컬럼에서 중복을 전부 제거하고 순수하게 종류만 보고싶을 때 사용합니다.
마치 명단에서 동명이인을 하나로 합치는 것과 같다고 할까요?
고유한 값 목록 보기
planner 라고 하는 컬럼이 있다고 할 때 어떤 종류의 담당자가 있는지 목록을 확인하고 싶다면?
SELECT DISTINCT planner
FROM sales_data;실행하면 planner 컬럼에 ‘김철수’, ‘이영희’, ‘박지성’ 이라는 이름이 여러 번 반복되어도, 결과는 딱 세 개의 이름만 깔끔하게 보여줍니다.
고유한 값 개수 세기
그렇다면 고유한 담당자가 '총 몇 명'인지 숫자만 알고 싶을 땐 COUNT를 함께 사용하시면 됩니다.
SELECT COUNT(DISTINCT planner)
FROM sales_data;