SQL 기법

개요

이 짧은 문서에서는 초보자를 위한 SQL 기법을 몇 가지 소개하도록 하겠습니다. SQL이 무엇인지 알고 싶은 분은 아래의 링크를 먼저 확인해주세요.

개발 환경 준비하기

SQL이 작동하기 위해서 특정 종류의 데이터베이스가 필요합니다. SQLite는 모바일 앱 저장소와 개발에 주로 사용되는 유명한 경량 데이터베이스입니다. Sequel Pro, install MySQL, fire up a Laravel application 등의 도구를 사용해서 쿼리를 실행할 수 있는 데이터베이스 환경을 생성해보세요.

테이블 생성하기

첫 번째로 알아야 할 것은 관계형 데이터베이스(MySQL, SQLite, PostgreSQL)가 테이블들로 이루어졌다는 것입니다. 하나의 데이터베이스는 많은 테이블을 가지고 있고 각 테이블은 레코드의 특정 카테고리로 이루어져 있습니다. 예를 들어, e커머스 사이트의 경우에는 주문, 사용자, 거래, 제품의 정보가 담긴 테이블이 있습니다. 많은 웹 애플리케이션에서 보통 마이그레이션(migrations)을 통해 테이블을 생성하지만, SQL의 create문을 읽고 작성할 수 있으면 도움이 될 것입니다. 

create table exercise_logs ( 
  id integer primary key autoincrement,  
  type text, 
  minutes integer,  
  calories integer,  
  heart_rate integer
);

위에 있는 SQL문은 5개의 열(id, type, minutes, calories, heart_rate)을 가진 exercise_logs라는 테이블을 생성합니다. 각 열은 정수나 텍스트처럼 특정 데이터 타입(data type)을 갖습니다.

create문의 두 번째 줄에서 새로운 레코드가 고유한 id를 갖는다고 명시했습니다. 이것을 테이블의 기본키(primary key)라고 합니다. 

데이터베이스 테이블의 구조와 정의를 데이터베이스 스키마라고 합니다.

테이블에 데이터 삽입하기

테이블에 운동 기록을 추가하기 위해 SQL insert문을 작성합니다.

insert into exercise_logs(type, minutes, calories, heart_rate) values ('biking', 30, 100, 110);

이 예시에서 테이블의 이름인 exercise_logs와 데이터를 삽입하고 싶은 특정 열을 명시했습니다. values 키워드 다음에 데이터베이스에 삽입하고 싶은 데이터를 명시합니다.

Select 문

여러 전문 직무에서 SQL select문을 사용해서 데이터 쿼리를 날리는 것은 아주 중요합니다.

데이터베이스의 모든 레코드를 선택하기 위해서는:

select * from exercise_logs;

사용자가 참가하고 있는 모든 활동과 그 활동을 위해 소모한 전체 칼로리를 찾기 위해서 다음과 같은 select문을 작성합니다:

select type, sum(calories) as total_calories 
from exercise_logs 
group by type;

예를 들면 어떤 등급의 학생이 몇 명인지를 결정하는 것과 같은 여러 종류의 일을 select문으로 처리할 수 있습니다.

심박 수에 따라 각각의 운동을 그룹화할 수 있습니다.

/* Group the count of exercises by each heart rate zone */
select count(*),    
  case         
    when heart_rate > 220 - 30 then 'above max'        
    when heart_rate > round(.9 * (220 - 30)) then 'above target'
    when heart_rate > round(.5 * (220 - 30)) then 'within target'
    else 'below target'    
end as 'heart_rate_zone'
from exercise_logs
group by heart_rate_zone;

SQL은 목적에 맞게 데이터를 설명할 때 아주 유용합니다. 마케팅 결과 필터링, 고객 정보 찾기, 서버 로그 찾기, 보고용 대시보드 생성하기 등 여러 작업에 활용될 수 있습니다.

서브쿼리와 Like

가끔은 쿼리를 이용해 동적으로 데이터를 얻어 그 결과를 다른 쿼리에 적용해야 하는 일이 있습니다. 이 경우에 우리는 서브쿼리를 사용합니다. 예를 들어 drs_favorites 라는 테이블은 의사의 추천 활동 정보를 담고 있습니다:

/* This is a comment */
create table drs_favorites ( 
  id integer primary key,    /* Unique identifier */
  type text,                 /* Type of activity */
  reason text                /* Why the doctor recommends it */
);
insert into drs_favorites
  (type, reason) 
  values ('running', 'improves cardiovascular health.');

메인 쿼리를 통해 activity_log 테이블에서 심혈관 건강 개선을 위해 의사가 추천한 모든 활동을 찾을 수 있습니다.

SELECT * FROM exercise_logs WHERE type IN (SELECT type FROM drs_favorites WHERE reason LIKE "%cardiovascular%");

괄호 사이에 있는 서브쿼리는 의사의 추천 이유 중 “심혈관"이라는 단어가 포함된 모든 레코드를 선택합니다. LIKE operator에 대한 더 많은 설명을 참고하세요.  SQL은 대문자나 소문자 모두 사용할 수 있다는 것을 명심하세요.

조인과 여러 데이터베이스에 질의 가능한 SQL 구문이 포함된 예시 코드는 아래의 깃허브 저장소에서 확인 가능합니다.