SQLite User Guide for Android, iPhone - DML (SELECT/INSERT/UPDATE/DELETE) 등

SQLite Databae에 대한 네번째 포스트입니다. DML은 대부분의 개발자들이 기본 SELECT/INSERT/UPDATE/DELETE에 대해서 워낙 잘 들 알고 계시므로 이번 포스트에서는 DML 관련하여 몇가지만 다루도록 하겠습니다.

SQLite User Guide 포스트 목차

1. SQLite User Guide - 소개. GUI Tools 등
2. SQLite User Guide - PRAGMA, 시스템 카탈로그, DATA TYPE
3. SQLite User Guide - DDL(CREATE, DROP 등)
4. SQLite User Guide - DML (SELECT, INSERT, UPDATE, DELETE 등)
5. SQLite User Guide - Function (내장함수, Aggregation 함수)
6. SQLite User Guide - DateTime 함수, DateTime Formatting
7. SQLite User Guide - Trigger
8. SQLIte User Guide - Tranaction, Lock
9. SQLite User Guide - Performance, Optimizer
10. SQLite User Guide - VDBE(Virtual Database Engine)

1. EXPLAIN / EXPLAIN QUERY PLAN

실행계획을 볼 수 있는 명령어입니다.
EXPLAIN 명령어는 세부적인 VDBE의 operation code를 볼 수 있습니다.
EXPLAIN QUERY PLAN 은 실행계획을 보여 줍니다.

예제 테스트는 SQLite 첫번째 포스트에서 알려드린 SQLite Expert Personal 버전의 demodb를 이용해서 테스트하도록 하겠습니다.

예제)
EXPLAIN
SELECT K.NAME
      ,C.[CustNo]     
      ,C.[City]     
      ,O.OrderNo     
      ,O.[SaleDate]     
      ,E.[EmpNo]     
      ,i.[ItemNo]     
      ,i.PartNo
  FROM COUNTRY K JOIN CUSTOMER C ON K.[Name] = C.COUNTRY
       JOIN ORDERS O ON C.[CustNo] = O.CustNo
       JOIN ITEMS I ON O.[OrderNo] = I.OrderNo
       JOIN EMPLOYEE E ON O.[EmpNo] = E.EmpNo         
  WHERE K.NAME = 'Canada' 
    AND E.EMPNO = 145;

-- result
addr opcode p1 p2 p3 p4 p5 comment
0 Trace 0 0 0 "" 00 
1 String8 0 1 0 Canada 00 
2 Integer 145 2 0 "" 00 
3 Goto 0 53 0 "" 00 
4 OpenRead 5 5 0 keyinfo(1,BINARY) 00 
5 OpenRead 4 27 0 0 00 
6 OpenRead 1 9 0 8 00 
7 OpenRead 2 18 0 5 00 
8 OpenRead 6 41 0 keyinfo(1,BINARY) 00 
9 OpenRead 3 13 0 3 00 
10 OpenRead 7 39 0 keyinfo(1,BINARY) 00 
11 SeekGe 5 45 1 1 00 
12 IdxGE 5 45 1 1 01 
13 MustBeInt 2 44 0 "" 00 
14 NotExists 4 44 2 "" 00 
-- 중략.

EXPLAIN QUERY PLAN
SELECT K.NAME
      ,C.[CustNo]     
      ,C.[City]     
      ,O.OrderNo     
      ,O.[SaleDate]     
      ,E.[EmpNo]     
      ,i.[ItemNo]     
      ,i.PartNo
  FROM COUNTRY K JOIN CUSTOMER C ON K.[Name] = C.COUNTRY
       JOIN ORDERS O ON C.[CustNo] = O.CustNo
       JOIN ITEMS I ON O.[OrderNo] = I.OrderNo
       JOIN EMPLOYEE E ON O.[EmpNo] = E.EmpNo         
  WHERE K.NAME = 'Canada' 
    AND E.EMPNO = 145;

order from detail
0 0 TABLE COUNTRY AS K WITH INDEX sqlite_autoindex_country_1
1 4 TABLE EMPLOYEE AS E USING PRIMARY KEY
2 1 TABLE CUSTOMER AS C
3 2 TABLE ORDERS AS O WITH INDEX idx_orders_CustNo
4 3 TABLE ITEMS AS I WITH INDEX idx_items_ByOrderNo

VDBE의 OP코드 분석(EXPLAIN 결과) 및 실행계획에 대해서는 향후 포스트에서 상세하게 다루도록 하겠습니다.
맘이 급하십니까? 당장 알기 원하시면 다음의 링크를 참고 하시기 바랍니다.

실행계획/옵티마이저.
http://www.sqlite.org/optoverview.html
http://www.sqlite.org/queryplanner.html

VDBE의 OP코드
http://www.sqlite.org/opcode.html

2. SELECT

SELECT 는 워낙 잘 들 아시는 부분이라  한 두가지만 테스트 해 보도록 하겠습니다.
먼저, 오라클의 dual 테이블 같이 처리되는 구문은 MS-SQL 처럼 FROM 절 없이 사용하면 될 것 같습니다.

예)
SELECT 'A' a

페이징 처리 (ROWNUM, TOP과 유사한 기능)

페이징 처리시 오라클에서는 ROWNUM을 사용하고 MS-SQL에서는 TOP을 주로 사용들 합니다. 물론 요즘은 ROW_NUMBER() 함수를 더 많이 사용하신다구요?
SQLITE에서는 페이징 처리하기가 더 편리합니다. LIMIT와 OFFSET이 그것입니다.

SELECT CUSTNO, COMPANY, CITY
  FROM CUSTOMER 
 ORDER BY CUSTNO
 LIMIT 5 OFFSET 5;

ORDER BY와 상관없이 사용이 가능합니다. LIMIT에 값을 지정하면 화면에 출력할 레코드 개수를 지정할 수 있습니다. OFFSET은 건너뛸 레코드 수를 나타냅니다. 즉, 위의 쿼리는 CUSTNO로 정렬해서 나온 순서에서 6번째부터 5개의 레코드를 가져오는 SELECT문입니다. OFFSET은 생략도 가능합니다.

페이징은 편리하나 오라클의 ROWNUM 처럼 결과에 수치값을 나타낼 방법이 있다면 여러모로 편리한데 그런 기능은 아쉽습니다. 필요시에는 별도의 NUMBER를 가진 테이블을 하나 만들어서 조인해서 사용해야 할 듯 합니다.

USING 구문

일반적으로 EQUAL JOIN 구문에서 ON 이하는 두 테이블간의 조인조건의 컬럼을 기술하는데, 두 컬럼의 컬럼명이 동일한 경우가 많습니다. 이때 간략하게 USING을 사용하여 쿼리를 줄일수 있습니다.

SELECT * FROM A INNER JOIN B ON A.COL1 = B.COL1;

-- 아래와 같이 위 문장을 USING을 사용하여 기술할 수 있음.
SELECT * FROM A INNER JOIN B USING(COL1);

-- 여러 테이블 조인에서의 USING 사용 예)
SELECT * FROM A JOIN B USING (c) JOIN C USING (e) JOIN D USING (g)

3. UPDATE (JOIN)

UPDATE 구문도 워낙 잘 아시니 여기서는 JOIN UPDATE에 대해서만 확인해 보도록 하겠습니다.
테스트를 위해 CUSTOMER 테이블에 컬럼을 2개 추가했습니다.

/* JOIN UPDATE TEST */
-- ALTER TABLE에서 한번여 여러 컬럼 추가도 안됩니다.

ALTER TABLE CUSTOMER
ADD CAPITAL TEXT;

ALTER TABLE CUSTOMER
ADD COLUMN CONTINENT TEXT;

-- 오라클 스타일 JOIN UPDATE TEST => ERROR
UPDATE CUSTOMER C
   SET (CAPITAL, CONTINENT) = (SELECT CAPTIAL, CONTINENT  
                                WHERE COUNTRY K                               
                                  AND K.[Name] = C.COUNTRY)                                 
 WHERE COUNTRY IN (SELECT NAME FROM COUNTRY);

-- MS-SQL 스타일 JOIN UPDATE => ERROR
UPDATE CUSTOMER
   SET CAPITAL = K.CAPITAL,  
       CONTINENT = K.CONTINENT      
FROM CUSTOMER C JOIN COUNTRY K ON C.Country = K.NAME;

찾아보니 JOIN UPDATE 자체를 지원하지 않는답니다. 에구, 이게 안되는 건 좀 치명적인듯. 업데이트시에 어플에서 처리하는 방식밖에 안될 것 같습니다.

4. DELETE

DELETE시에도 LIMIT와 OFFSET 구문을 함께 사용할 수 있습니다. 이 부분은 편리한 듯~ , 참 UPDATE 구문에도 LIMIT와 OFFSET 구문을 함께 사용할 수 있습니다.

DELETE문도 단순 스타일은 워낙 잘 들 아시니 중복 레코드 제거 쿼리 테스트만 해보도록 하겠습니다.

/* 중복 제거 DELETE 문 테스트 */

-- 임시 테이블 생성.
create table t1 (
id integer,
name varchar(10)
);

-- 테스트 데이터 삽입.
insert into t1 values(1,'a');
insert into t1 values(2,'a');
insert into t1 values(2,'b');
insert into t1 values(1,'b');
insert into t1 values(1,'C');

-- 오라클에서 주로 사용하는 방식으로 테스트.
-- ANY 키워드를 지원하지 않아서 에러가 발생함.
DELETE FROM t1 A
WHERE ROWID > ANY (SELECT ROWID
                                     FROM t1 B
                                    WHERE A.id = B.id);

-- DELETE문에서 서브쿼리로 조인을 지원안해서 이것도 에러가 발생함.
DELETE FROM t1 A
WHERE ROWID > (SELECT MIN(ROWID)
                              FROM    t1 B
                             WHERE A.id = B.id);

-- 조금은 부하가 있지만, GROUP BY절을 이용한 NOT IN 서브쿼리로 중복 제거 가능.
DELETE FROM t1
WHERE ROWID NOT IN (SELECT MIN(ROWID)
                      FROM T1                     
                     GROUP BY ID);

5. COMMENT

SQLITE에서는 주석구문으로 블록주석인 /* */ 구문과 단일 행 주석인 -- 을 사용합니다.

6. NULL 처리

NULL값에 대한 처리는 Oracle과 거의 유사하게 동작합니다. SQLITE에서 NULL에 대한 상세한 정보를 얻으실려면 http://www.sqlite.org/nulls.html 를 참조하시기 바랍니다. 샘플코드 따라서 해보시면 금방 이해가 가실듯 합니다.

7. INDEXED BY

실행계획 편에서나 다룰 내용이지만, 여기서 INDEXED BY에 대해서 조금 다루도록 하겠습니다.

테이블명 뒤에 INDEXED BY 구문을 사용하면 지정된 인덱스를 쿼리가 무조건 사용합니다.
지정한 인덱스의 분포도나 이런거 상관없습니다. 무조건 사용합니다. 오라클의 INDEX 힌트와 유사해 보이지만 다릅니다. 오라클의 힌트는 힌트를 잘못 사용하더라도 에러가 발생하지 않지만, SQLITE에서는 SQL 구문이 FAIL 됩니다. 인덱스명을 잘못 사용하는 경우는 물론이고, 인덱스를 타지 않는 상황에서 인덱스를 지정해도 에러가 발생합니다.
아래 쿼리를 보겠습니다.

PRAGMA INDEX_LIST(COUNTRY); /* COUNTRY 테이블의 인덱스 현황입니다. */

seq name unique
0 sqlite_autoindex_country_1 1
--AUTOINCREMENT로 지정된 UNIQUE 인덱스가 하나 존재합니다.

--현재 상황에서 테스트할 쿼리의 실행계획을 보도록 하겠습니다.
EXPLAIN QUERY PLAN
SELECT *
  FROM COUNTRY
 WHERE NAME LIKE 'c%'
   AND CAPITAL LIKE 'a%'

order from detail
0 0 TABLE COUNTRY
--테이블 FULL SCAN하는 실행계획이 생성되었습니다.

-- 인덱스를 추가하겠습니다.
CREATE INDEX COUNTRY_IX1 ON COUNTRY (NAME);
CREATE INDEX COUNTRY_IX2 ON COUNTRY (CAPITAL);

PRAGMA INDEX_LIST(COUNTRY);

seq name unique
0 COUNTRY_IX2 0
1 COUNTRY_IX1 0
2 sqlite_autoindex_country_1 1
-- 추가된 인덱스가 보입니다.
-- 이전에 실행한 쿼리를 다시 실행해 보도록 하겠습니다.

EXPLAIN QUERY PLAN
SELECT *
  FROM COUNTRY
 WHERE NAME = 'c'
   AND CAPITAL = 'a';

order from detail
0 0 TABLE COUNTRY WITH INDEX sqlite_autoindex_country_1
-- 인덱스를 이용하지만, AUTOINCREMENT 컬럼에 대한 인덱스를 타고 검색합니다. 아마도 순차적으로 검색하려고 그런가 봅니다.

-- COUNTRY_IX2를 사용하도록 쿼리를 짠후 실행계획을 보면..
EXPLAIN QUERY PLAN
SELECT *
  FROM COUNTRY INDEXED BY COUNTRY_IX1
 WHERE NAME = 'c'
   AND CAPITAL = 'a'

-- 인덱스를 사용하는 쿼리로 변경이 되었습니다.
order from detail
0 0 TABLE COUNTRY WITH INDEX COUNTRY_IX1

-- 재미있는 상황이 있군요.
EXPLAIN QUERY PLAN
SELECT *
  FROM COUNTRY INDEXED BY COUNTRY_IX2
 WHERE NAME LIKE 'c%'
   AND CAPITAL LIKE 'a%'

/*
= 검색을 LIKE 검색으로 바꾸니 Cannot use index : COUNTRY_IX2란 에러가 발생했습니다. 일반적으로 오라클이나 MS-SQL은 단방향 LIKE 검색의 경우 인덱스를 태울 수 있습니다. SQLITE에서는 에러가 나는걸 보니 LIKE 검색은 인덱스를 이용하지 않나 봅니다. (이 부분은 향후 실행계획 부분에서 다시 자세히 보도록 하겠습니다.) */

본 포스트에서는 일반적인 DML에 대한 강좌 형식 보다는 SQLite에 대한 특징적인 면들을 살펴보았습니다. 그래서 insert 구문은 아예 다루지도 않았습니다. SQLite에 대한 기본 SQL 문법에 대해 더 궁금하시면 다음 사이트를 살펴보시기 바랍니다. http://www.sqlite.org/lang.html

다음 포스트에서는 SQLite Core Function 사용법에 대해 다뤄 보도록 하겠습니다.