(연재) SQLite3 강좌 – 테이블에서 조회하기 1

테이블에 저장된 데이터를 조회하기 위해서는 SELECT 구문을 사용한다. SELECT 구문운 SQL에서 가장 흔히 쓰이는 쿼리 구문인 동시에 가장 복잡한 구문이기도 하다. 테이블 조회와 관련하여 SQLite3는 표준 SQL에 정의된 거의 모든 기능을 제공한다. SQLite3의 SELECT 구문의 사용법에 대해서 살펴보자.

기본 컨셉

기본적으로 SELECT 는 DB 엔진으로부터 질의에 대한 답을 요청하는 명령이다. 가장 흔하게는 테이블 내의 레코드들을 조회하는 용도로 사용되지만, SELECT 명령의 본질은 DB가 수행할 수 있는 연산의 결과를 얻는 명령이다. 따라서 테이블 조회뿐만 아니라 다음과 같이 계산 결과를 얻도록 호출할 수도 있다.

SELECT 1 + 1;

위 쿼리는 단순히 2라는 결과를 얻게 된다. 테이블을 지정해서 데이터를 조회하지는 않지만 하나의 온전한 SELECT 구문이기도 하다.  물론 이것은 SELECT라는 명령이 DB엔진으로부터 어떠한 값을 요청한다는 개념을 보여주는 예이지, 실제로 이런 간단한 계산 때문에 데이터베이스를 사용하는 일은 드물다. SELECT 구문은 기본적으로 테이블의 내용을 조회하는 내용으로 알려져 있다.

하지만 실제로는 다음의 개념으로 SELECT 명령을 이해해야 한다.

  1. SELECT 명령은 어떤 “결과”를 요구하는 명령이다.
  2. SELECT 명령은 폭과 높이가 각각 0 이상인 2차원 그리드로 구성된 데이터에 대해서 수행된다.
  3. SELECT 명령의 결과 역시 폭과 높이가 각각 0 이상인 2차원 그리드로 구성될 수 있다.

이 관점에 따르면 SELECT 1 + 1; 은 첫째로 연산의 결과를 요구하고 있으며, 대상데이터는 가로가 0, 세로가 0인 그리드를 가지고 있다. 그리고 그 결과는 가로가 1, 세로가 1인 1X1 그리드로 단일값이 된다.  실제로 통상의 SELECT문이 대상으로 하는 테이블 역시 2차원 그리드이며, SELECT 문의 조회 결과 역시 여러 행일 수 있고, 한 행은 여러 칼럼으로 구분될 수 있다. 즉, 결과 역시 그리드가 될 수 있다.

SELECT 구문은 사실 매우 복잡하기 때문에, SQL에서도 가장 큰 비중을 차지한다. 일단 기본적인 SELECT 구문의 얼개를 살펴보면 대략 다음과 같다고 할 수 있다.

  1. SELECT [DISTINCT] {결과 칼럼 세트} : 결과에 표시될 칼럼을 선택한다. DISTINCT가 쓰이면 중복된 행을 제거한다.
  2. FROM 테이블|서브쿼리 : FROM 은 어디서 데이터를 조회할 것인지를 선택한다. 보통은 테이블 명을 쓴다. 이때 테이블 대신 서브 쿼리를 쓸 수도 있다. 즉 테이블이든 서브쿼리든 2차원 그리드라는 점을 기억하자.
  3. WHERE 조건 : 필터링 조건을 지정한다.
  4. ORDER BY 조건 [ASC|DESC] : 정렬조건을 선택한다.
  5. LIMIT {최대개수} [OFFSET {오프셋}] : 조회 결과의 범위와 양을 선택한다.

이것이 가장 심플한 SELECT 구문의 모양이다. 2, 3, 4, 5번은 각각 선택적으로 사용할 수 있다.

예를 들어 어떤 게시판에서 최근 글 순으로 50개를 표시하고 싶다면 다음과 같이 쿼리를 작성할 수 있을 것이다.

SELECT post_no, title, author, reg_date 
FROM board
ORDER BY reg_date DESC
LIMIT 50;

위 쿼리 구문은 다음과 같은 구조로 이루어진다.

  1. SELECT 뒤에는 결과 그리드의 각 칼럼을 명시한다. 조회 대상에서 같은 이름의 칼럼이 있으면 그 값이 사용될 것이다.
  2. FROM 뒤에는 조회 대상이 되는 소스 그리드를 명시하는데, 여기서는 board라는 테이블이다.
  3. ORDER BY 절을 통해서 등록일의 역순, 즉 최근 등록순으로 정렬한다.
  4. LIMIT 50 은 출력의 양을 50개 행으로 제한한다.

LIMIT 다음에는 선택적으로 OFFSET이 올 수 있다. OFFSET 은 범위를 지정한다. 예를 들어 위 쿼리가 게시물 목록의 1 페이지라면 2페이지는 50개를 띄운 이후 범위에서부터 가져오면 된다. 따라서 OFFSET 50 을 LIMIT 50 뒤에 추가한다.

결과 정렬

ORDER BY 절은 특정한 칼럼의 값을 기준으로 결과를 정렬한다. 정렬의 기준이 될 칼럼명과 정렬의 방법(오름차순, 내림차순)을 쓴다. 정렬 방법을 명시하지 않는 경우 오름차순(ASC)으로 간주된다. 컴마(,)를 사용해서 정렬조건을 2개 이상 지정할 수 있고, 이 때에는 첫번째 정렬 조건에서 같은 순위를 갖는 행들이 두 번째 정렬조건에 의해서 다시 순서를 갖게 된다. 아무런 정렬조건이 없을 때 데이터는 테이블에 삽입된 순서대로 정렬된다.

필터링 – WHERE 절

WHERE 절은 결과를 필터링하기 위해 사용한다. 기본 모양은 WHERE 검색조건 이며, 이 검색 조건은 주로 비교식의 형태를 갖는다. 비교식은 좌변 연산자 우변 의 모양을 갖는다. 연산자는 기본적인 = , != , <> , < , > , <=, >= 의 비교 연산외에 IN , LIKE , BETWEEN , GLOB 등을 사용할 수 있다.

WHERE col_1 = 100
WHERE col2 IN (2, 7, 9)
WHERE col3 LIKE 'An%'
WHERE col4 BETWEEN 10 AND 20

WHERE 절의 내용은 참/거짓 값으로 평가된다. 따라서 NOT, AND, OR 의 논리 연산를 사용할 수 있다. 그외에 추가적으로 사용할 수 있는 연산자에는 이런 것들이 있다.

  • ALL : 주어진 모든 평가식이 참일 때 참
  • ANY : 주어진 모든 평가식 중 하나가 참이면 참
  • EXISTS : 서브쿼리를 만족하는 행이 하나 이상 포함되어 있는지 검사.
WHERE col1 = ANY (SELECT col1_2 FROM other_table WHERE colx > 99)
WHERE col1 = ALL (SELECT col1_2 FROM other_table WHERE colx = 99)
WHERE EXISTS (SELECT col1_2 FROM other_table WHERE coly <> 100)
  1. 서브 쿼리의 col1_2 칼럼중에서 현재 조회 결과 중에서 col1과 같은 것이 하나라도 있으면 그것을 포함한다.
  2. 서브 쿼리의 col1_2 칼럼이 모두 col1과 같은 것만 필터링한다.
  3. 서브 쿼리의 칼럼 결과가 있으면 만족하는 것으로 취급된다. 따라서 EXISTS 평가식의 서브 쿼리에는 현재 결과의 칼럼이 들어가거나 할 수 있다.

LIKE 비교

LIKE는 문자열의 전체가 아닌 일부가 맞는지를 평가한다. 보통 우변에 % 문자를 포함시키는데, 이는 유닉스 와일드카드와 비슷하게 기능한다.

WHERE name LIKE 'Ap%'  - Ap 혹은 Ap로 시작하는 문자열에 매칭
WHERE name LIKE '%ed'  - ed 혹은 ...ed로 끝나는 문자열에 매칭
WHERE name LIKE '%or%` - ...or... 이 들어가는 문자열에 매칭 

LIKE 매칭에서는 % 외에도 _ 도 쓰인다. 이는 임의의 한 글자라는 의미이다. '__pple' 는 ##pple 에 매칭되기 때문에 apple 에는 매칭되지 않는다. (%pple 에는 apple이 매칭되겠지만)  만약 찾고자 하는 문자열이 순수하게 _ 를 담고 있다면 이를 이스케이프해야 한다. 쿼리 내 문자열 리터럴에는 백슬래시를 사용한 이스케이프를 사용하지 않고 ESCAPE '_' 를 뒤에 붙여서 알려준다.

WHERE col1 LIKE 'get_value' ESCAPE '_'
                            ~~~~~~~~~~

GLOB 연산

GLOB 연산자는 LIKE와 비슷한데, 유닉스 와일드카드를 사용하게 해준다. 이는 *, ?, [a-z], [A-Z], [0-9] 와 같이 LIKE 연산보다는 조금 세부적인 필터링이 가능하다.

결과에서 중복된 행을 제거하기 DISTINCT

SELECT DISTINCT col1, col2,... 과 같이 DISTINCT 를 사용해서 쿼리를 보내면 중복되는 행을 제거한 결과를 얻을 수 있다. 이는 모든 조회, 필터링, 범위 선택을 마친 출력될 결과 그리드에 대해서 “완전히 중복되는 행”을 필터링한다. (특정한 칼럼만 중복되는 경우는 제외되지 않는다.) 만약 특정한 칼럼이 중복되어 있는 데이터에서 그 칼럼의 중복을 없애고자 한다면 GROUP BY를 사용해야 한다.

SELECT의 결과와 서브 쿼리

FROM 절이 없는 경우(앞서 1+1의 계산에서 보았듯이), 데이터베이스가 데이터를 조회할 대상은 0행, 0칼럼의 크기가 없는 가상의 테이블이 된다. 또 SELECT 구분의 결과는 결과 칼럼 셋에서 지정한 너비의 칼럼 수와 전체 결과 수의 행수를 갖는 그리드가 된다. 따라서 맨 처음 소개했던 SELECT 1 + 1; 은 크기가 없는 데이터셋을 사용하며 그 결과는 1 X 1 크기의 그리드가 된다. 만약 SELECT 1 + 1, 2 * 2, 3 + 8; 이라는 쿼리라면 1행 3열의 그리드가 그 결과가 될 것이다.

SELECT의 결과가 행과 열로 이루어진 그리드이고, 이러한 사각형 모양의 데이터 셋은 테이블의 모양과도 매우 비슷하다. 따라서 SELECT 의 결과 자체는 일종의 임시테이블로 볼 수 있으며, 따라서 SELECT 구문의 FROM 절은 테이블이 아닌 다른 SELECT 구문의 결과에서부터 데이터를 조회하는 것도 가능하다. 이를 서브 쿼리라고 한다.

굳이 예를 들자면, SELECT * FROM board; 라는 쿼리는 board 테이블의 전체 내용을 그대로 결과로 만드는 쿼리이다. 즉 이 쿼리문이 그대로 FROM 에 쓰이면 FROM board와 동치가 된다. 이 말은 최근 글 50개를 다음과 같이 작성하는 것도 가능하다는 의미이다.

SELECT title, author, reg_date
FROM (
    SELECT * FROM board
)
ORDER BY reg_date DESC
LIMIT 50, 50; -- LIMIT 50 OFFSET 50을 이렇게 줄여 쓸 수 있다.

서브 쿼리를 활용하면 단일 쿼리에서는 구성하기 어려운 정렬, 그룹핑 조건을 비교적 쉽게 하나의 쿼리로 구성할 수 있다. 그저 다른 SELECT 문의 결과를 테이블처럼 사용하면 되는 것이기 때문에 유용하게 사용할 수 있을 것이다.

 

 

[Python] 초간단 Sqlite3 사용 예제

다음 코드는 SQLite3를 통해 테이블을 만들고 (그전에 있으면 없애버리고) INSERT, SELECT, UPDATE, DELETE를 모두 한 번씩 해보는 예제이다. 쿼리를 만들고 실행하는 방법에 대해 보기 편하라고 만든 예제라 별도의 함수화 같은 건 하지 않았다. [Python] 초간단 Sqlite3 사용 예제 더보기