SQLite는 iphone 이나 android 같은 모바일 환경 뿐 아니라 데스크탑 운영체제의 응용프로그램에서도 많이 사용되는 파일기반 RDB다. 2000년에 Richard Hipp 박사에 의해 개발된 무료 DB엔진이다. 복수의 사용자를 지원하지 않는 대신 C언어로 작성되어서 속도가 빠르다. 주요 특징으로는 Zero Configuration, Portabiliy, Compactness, Simplicity, Flexibility, Liberal Licensing, Reliability 라고 www.sqlite.org 사이트에 나와 있다. 다른 DBMS 사용경험이 있는 경우 삽질 예방을 위해 지원하지 않는 몇가지를 정리해 본다. SQLite 엔진의 버전이 올라가면 언제 그랬냐는 듯이 지원할지도 모른다.

 

  1) LEFT OUTER JOIN 만 지원 하며 "RIGHT OUTER JOIN"  이나 "FULL OUTER JOIN"은 사용불가

  2) 테이블 변경시 "RENAME TABLE" 과 "ADD COLUMN" 명령이 가능하며  

     "DROP COLUMN",  "ALTER COLUMN", "ADD CONSTRAINT" 등은  지원하지 않는다.

  3) SQLite 에서 View는 읽기 전용.

  4) Sqlite의 읽기/쓰기 권한은 OS 파일 시스템 권한을 사용한다.

 

 

 

 

 

1) 편리한 GUI Tool

   SQLite Expert 

        http://www.sqliteexpert.com/download.html

   Sqliteman 

        http://sqliteman.com/

   ODBC 드라이버

        http://www.ch-werner.de/sqliteodbc/ 

   sqlitebrowser 

        http://sourceforge.net/projects/sqlitebrowser/

 

2) 쉘 환경에서 데이터 조회 (sqlite3 사용)

 

   adb.exe를 사용하면 리눅스 운영체제의 쉘커맨드라인 환경을 사용할 수 있다. sqlite3 유틸리티를 사용하여 커맨드라인 모드로 간단한 관리와 데이터 조회가 가능하다.

 

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

D:\>adb push m.db /sdcard/
253 KB/s (836608 bytes in 3.224s)

D:\>adb shell
# sqlite3 /sdcard/m.db
sqlite3 /sdcard/m.db
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .table
.table
TBJBD43  TBJBD44  TBJBD46  TBJBD47  TBJBD48  TBJBD52  TBJBD55  TBJBD56
sqlite> select count(*) from tbjbd43;
select count(*) from tbjbd43;
6560
sqlite> .exit
.exit
# exit
exit

D:\>

 

 

--sqlite3 실행
#sqlite3 ..../xx.db
 

--종료법
sqlite>.exit
 

--테이블 확인
sqlite>.tables 

 

-- 열 헤더를 툴에서 보이게 설정 :

sqlite>.headers on

sqlite3 명령 참조 : http://www.sqlite.org/sqlite.html


 

 

 

 

 

테이블이나 인덱스 같은 DB객체의 정보 다른말로 메타데이터를 조회하는 방법을 먼저 정리해 본다.

위에서 소개한 관리툴을 사용하면 쉽고 비주얼하게 조회할 수 있다.

 

 

 

SQLITE_MASTER 테이블 : 시스템 카탈로그를 조회할 수 있는 READ-ONLY 테이블로 테이블, 인덱스, 트리거 등 모든 스키마 확인과 DDL문확인이 가능하다. 

SELECT * FROM SQLITE_MASTER;

 

PRAGMA   명령어

PRAGMA table_info( table-name );             -- 테이블 정보를 조회하는 명령어입니다

PRAGMA index_list( table-name );             -- 인덱스 리스트를 볼 수 있습니다

PRAGMA index_info( index-name );           -- 인덱스 정보를 조회할 수 있습니다

PRAGMA foreign_key_list( table-name );     -- fk 리스틀 볼 수 있습니다 .

 

 

Microsoft Windows [Version 6.0.6002]
(C) Copyright 1985-2005 Microsoft Corp.

 

C:\Users\root\Documents>adb push testlight3.db /sdcard/
140 KB/s (36864 bytes in 0.256s)

C:\Users\root\Documents>adb shell
# sqlite3 /sdcard/testlight3.db
sqlite3 /sdcard/testlight3.db
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .table
.table
HR_COUNTRIES    HR_JOBS         HR_REGIONS      援щℓ 二쇰Ц
HR_DEPARTMENTS  HR_JOB_HISTORY  SCOTT_EMP       ?쒗뭹
HR_EMPLOYEES    HR_LocationS    TEST2
sqlite> pragma table_info(hr_jobs);
pragma table_info(hr_jobs);
0|JOB_ID|varchar(10)|0||0
1|JOB_TITLE|varchar(35)|0||0
2|MIN_SALARY|float|0||0
3|MAX_SALARY|float|0||0
sqlite>

 

 

 

 

 

 

 

 

 

 

SQLite DML & DDL
http://www.sqlite.org/lang.html

 

 

    테스트를 위해서 첨부한 파일의 DDL을 예를 소개해 본다. 이 테이블은 MS의 NorthWind 테스트 db 중  "구매 주문" 테이블과 오라클의 HR.EMPLOYEES 테이블을 ODBS로 내보내기 한 테이블이다. 테이블명이나 칼럼명에 공백이 들어가는 경우와 예약어인 경우 반드시  ""나 []를 사용해야 한다.

 

CREATE TABLE "구매 주문"

 ("PO 번호" integer,

  "공급업체 번호" integer,

  "작성자" integer,

  "제출일" timestamp,

  "작성일" timestamp,

  "상태 ID" integer,

  "예상일" timestamp,

  "운송 비용" float,

  "세금" float,

  "대금 지급일" timestamp,

  "대금 지급액" float,

  "지불 방법" varchar(50),

   "참고 사항" longvarchar(65536),

   "승인자" integer,

   "승인일" timestamp,

   "제출자" integer);

 

CREATE TABLE "HR_EMPLOYEES"

 ("EMPLOYEE_ID" float,

  "FIRST_NAME" varchar(20),

  "LAST_NAME" varchar(25),

  "EMAIL" varchar(25),

  "PHONE_NUMBER" varchar(20),

  "HIRE_DATE" timestamp,

  "JOB_ID" varchar(10),

  "SALARY" float,

  "COMMISSION_PCT" float,

   "MANAGER_ID" float,

   "DEPARTMENT_ID" float );

 

예제 데이터는 access 에서 테이블 가져오기를 한후 SQLite DB파일로 내보내기 한것이다. 일단 위 링크에서 ODBC드라이버를 다운받아서 설치 한다. 다음으로 다른 SQLite툴을 이용하여 db파일을 생성한 후 다음처럼 ODBC 원본을 만들면된다. 주의할 점은 SQLite3 ODBC Driver를 선택해야만 한다.

 

마침 버튼을 누른후 세부적인 정보를 입력해 준다.

 

 

 테이블생성  예제 1)

   CREATE TABLE TEST2

   (

       _ID INTEGER NOT NULL, 
       CLASS TEXT NOT NULL,

       VALUE TEXT,

        CONSTRAINT TEST2_PK PRIMARY KEY (_ID, CLASS)

    );

SQLite 에서는 ALTER TABLE 문에서 ADD CONSTRAINT 구문이 지원되지 않기 때문에 PRIMARY KEY, UNIQUE, CHECK 등의 TABLE LEVEL 의 CONSTRAINT는 위 문장처럼 CREATE TABLE 문 제일 하단에 기술해야 한다. SQLExpert 3.3 툴에서 Pkey, Fkey, Uniquekey를 추가하는 기능이 있는데 툴 자체의 기능인지 SQLite의 엔진 업그레이드 때문인지 잘 모르겠다.

 

 

   테이블 생성 예제 2)

    CREATE TABLE TEST1

    (

         _ID INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,

          TYPE TEXT NOT NULL DEFAULT 'HIGH',

          NAME TEX

     );

 

자동채번이 가능한 일련번호 칼럼을 추가하는 예다. AUTOINCREMENT 문을 가진 DDL이 최초 실행되면 SQLITE는 내부적으로 SQLITE_SEQUENCE 테이블을 생성한다. 이 테이블은 NAME, SEQ 컬럼을 가진 테이블로 테이블마다 자동 증가되는 최종 값을 가진다. 재미있게 도 SQLITE_SEQUENCE 테이블값을 직접 핸들링도 가능하다. "ALTER TABLE" 명령으로 칼럼 추가시 한 칼럼 씩만 추가 할 수 있다.

 

 

 

 

 

 

5. SELECT 문
 

∘ 테이블명, 칼럼명에 공백이 들어가면 []나 ""를 사용하면 되는데 []를 사용하는게 좋을듯 하다.

 

   SELECT * FROM [구매 주문]; 

   SELECT * FROM "구매 주문";

 

∘ 주석은 '--'를 사용하고 알리아스는 as 문을 사용하거나 생략할 수 있다.

   SELECT a.id, --a.[설명],
             a."공급업체 번호"  as field1, a.[제품 이름]  field2
   FROM "제품" a;

 

∘ 대소문자 구분없이 비교

   SELECT * FROM MyTable WHERE t_Name = ‘Alice’ COLLATE NOCASE; 

 

∘ 더미테이블 (오라클의 dual 테이블) :  MS -SQL 처럼 FROM 절 없이 사용

 

   SELECT 'A' dummycol1, 2 commycol2, CURRENT_TIME, CURRENT_DATE;

 

 

∘ 페이징 처리 (ROWNUM, TOP 과 유사한 기능) 
    SELECT employee_id, first_name,  phone_number, hire_date
    FROM hr_employees
    ORDER BY employee_id
    LIMIT 5 OFFSET 5;

 

ORDER BY와 상관없이 사용이 가능하다. LIMIT에 지정한 값이 출력할 레코드 수를 나타내며 OFFSET은 건너 뛸 레코드 수를 나타낸다. 위의 쿼리는 CUSTNO로 정렬해서 나온 순서에서 6 번째부터 5 개의 레코드를 가져오는 SELECT 문이다. OFFSET은 생략도 가능하다. 오라클의 ROWNUM 처럼 일련번호를 제공하는 기능은 없다. 

 

 

 

 

 

 

INSERT INTO smaster select * from master2;;

INSERT INTO mytable VALUES (null, 'GoodDay', '좋은날');

 

 

 

 

 

  (JOIN UPDATE를 지원하지 않음)

  

  --오라클 스타일 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;

 

 

 

 

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

 LIMIT 와 OFFSET 구문을 함께 사용할 수 있습니다 .

  

 /- 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. d);

 

 --DELETE 문에서 서브쿼리로 조인을 지원안해서 이것도 에러가 발생함 .

 DELETE FROM t1 A

 WHERE ROWID > (SELECT MIN(ROWID) 
 FROM

 WHERE A.id = B.id ;

 

 --조금은 부하가 있지만,  GROUP BY 절을 이용한 NOT IN 서브쿼리로 중복 제거 가능.

 DELETE FROM t1

 WHERE ROWID NOT IN

 (SELECT MIN(ROWID) FROM T1 GROUP BY ID)

 

 

 

 

 

 

 

 

SQLite Core Functions

 

 

coalesce(X,Y,Z, ...)

함수는 중에서 첫번째로 Null 아닌 값을 리턴하는 함수. 모든 인자가 이면 Null 을 리턴한다.

첫번째로 Not Null인 인자값을 리턴합니다. 둘다 null 이면 null을 리턴. 

 

length(X) 

길이값을 리턴하는 함수

 

upper(X), lower(Y)

대/소 문자로 변환 하는 함수.

 

ltrim(X)

X 값 중 왼쪽편의 공백을 제거하는 함수

 

ltrim(X,Y)

X 문자열중에서 Y에 나타난 값을 제일 좌측부터 제거하는 함수

SELECT ltrim(" ZZZZabcZZ ", " aZ")  문의 결과는  

좌측부터 공백,a,Z 까지를 제거한 후  "bcZZ”가 반환된다.

 

max(X,Y,Z....)

인자값들 중 최대값을 리턴.

 

min(X,Y,Z....)

인자값들 중 최소값을 리턴합니다

nullif(X,Y)

두 인자가 서로 같으면 nul 다르면 X 값을 리턴

nullif('x','y') 는 ‘x’ 반환    nullif('x','x') nul 반환

Quote()

 함수는 single quotation을 escape (‘’로 변경) 한다. Insert 나 update 시에 사용하면 유용.

select quote("girl's mouse") => 'girl''s mouse'

 

random(*)

-9223372036854775808 부터 + 9223372036854775807 숫자 사이의 임의의 수를 리턴

 

replace(X, Y, Z)

X 문자열 중에서 Y문자열을 Z로 변경

select replace('1/12/2009','1','x') => "x/x2/2009"

 

round(X)

반올림 함수. Round(3.5) => 4

 

round(X, Y)

Y는 소수점 자리수 round(2.555, 2) => 2.56

substr(X, Y, Z)

X문자열 중에서 Y 번째부터 시작해서 Z개수만큼 문자열을 가져오는 함수. Z 가 생략되면 Y 번째 문자열부터 문자열 끝까지 리턴한다. Y의 최소 값은 1 (자바에서는 0)

select substr("string", 1, 3) => str

select substr("string", 0, 3) => st

select substr("string", -1, 3) => g

select substr("string", -3, 3) => ing

select substr("string", 2) => tring

 

typeof(X)

X 표현식에 대한 데이터 타입 (“null”, “integer”, “real”,“text ”, “blob”)을 반환

 

 

 

 

10. Aggregation Function (집합 함수)

 

 

avg(X)
Not Null 값의 평균값을 리턴한다. X 컬럼값이 문자열이나 BLOB 이면 0 으로 간주하고 처리 함.

count(X)

X칼럼이 Not Null 값을 가지는 레코드의 개수 반환한다.

 

group_concat(X)

X가 not null인 경우 그룹내의 모든 문자열을 콤마 (,)를 구분자로 해서 문자열을 합쳐서 반환한다.

 

group_concat(X,Y)

Y 값이 구분자로 사용하여 문자열을 합한후 리턴한다.

 

max(X)

그룹내의 값들 중 최대값을 리턴.

 

min(X)

그룹내의 값들중 최소값을 리턴한다. 모든값이 Null이면 Null을 리턴.

sum(X),   total(X)

그룹내의 값의 합계를 리턴한다. 모든 값이 Null 인 경우 Sum()은 Null Total()은 0.0을 반환한다.

 

 

 

 

 

SQLite Date And Time Functions
http://www.sqlite.org/lang_datefunc.html

 

  1) Keyword 

 

   CURRENT_TIME  : 현재 시간 (형식 : 03:22:56)  UTC 기준

   CURRENT_DATE  : 현재 날짜  (형식 :2010 -08 25) UTC 기준

   SELECT CURRENT_TIME, CURRENT_DATE;

 

 

   2)  DateTime 관련 함수

  

    date(timestring, modifier, modifier, ...)

    time(timestring, modifier, modifier, ...)

    datetime(timestring, modifier, modifier, ...)            날짜 /시간 함수

    julianday (timestring, modifier, modifier, ...)          율리우스력 함수

    strftime(format, timestring, modifier, modifier, ...)    날짜 포맷팅 함수

 

 

 timestring .
 위 다섯개의 함수 인자 중 파라미터에로 들어갈 수 있는 날짜 형식은 아래와 같습니다

 1. YYYY-MM-DD

 2. YYYY-MM-DD HH:MM

 3. YYYY-MM-DD HH:MM:SS 
 4. YYYY-MM-DD HH:MM:SS.SSS

 5. YYYY-MM-DDTHH:MM

 6. YYYY-MM-DDTHH:MM:SS

 7. YYYY-MM DDTHH:MM:SS.SSS

 8. HH:MM

 9. HH:MM:SS 
 10. HH:MM:SS.SSS

 11. now

 12. DDDDDDDDDD

 

 strftime format .
 함수에 사용되는 에 사용할 수 있는 값들은 다음과 같습니다

 % d day of month: 00

 %f fractional seconds: SS.SSS 
 %H hour: 00 -24

 %j day of year: 001 -366

 %J Julian day number

 %m month: 01 12

 %M minute: 00 -59

 %s seconds since 1970 -01 -01 
 %S seconds: 00 -59

 %w day of week 0 6 with sunday==0

 %W week of year: 00 -53

 %Y year: 0000 -9999

 %% %

 

 

 modifer .
 함수의 에 들어갈 수 있는 값들은 다음과 같습니다

 

 1. NNN days

 2. NNN hours

 3. NNN minutes

 4. NNN.NNNN seconds 
 5. NNN months

 6. NNN years

 7. start of month

 8. start of year

 9. start of day

 10. weekday N 
 11. unixepoch

 12. localtime

 13. utc

 

 

-- 가장 많이 써먹을 듯

select * from hr_employees
where hire_date >= date('1997-07-20', 'localtime')
  and hire_date < date('1997-11-12', 'localtime');

 

-- UTC 기준의 현재 날짜 /시간

select datetime('now');

2011-07-21 01:09:50

 

-- 로컬 기준의 현재 날짜 /시간
select datetime('now','localtime');

2011-07-21 10:10:19

 

-- 현재 로컬 기준 시간에서 10 분 3.5 초를 더한 시간

SELECT datetime('now', 'localtime','+3.5 seconds', '+10 minutes');

2011-07-21 10:23:22
 

--현재 로컬 시간에 3.5 초를 더하고 날짜는 돌아오는 화요일 (weekday == 0 이 일요일)

select datetime('now', 'localtime', '+3.5 seconds', 'weekday 2');

2011-07-26 10:14:54

 

--현재 달의 시작/마지막 날짜

SELECT date('now', 'start of month');

2011-07-01
SELECT date('now', 'start of month', '+1 month','-1 day', 'localtime');

2011-07-31

 

--2011-01-01 02:34:56 초부터 현재까지의 총 초

SELECT strftime('%s', 'now') - strftime('%s','2011-01-01 02:34:56');

17361941

 

--현재날짜 /시간 기준에서 올해 9 번째달의 첫번째 화요일

SELECT date('now', 'start of year', '+9 months', 'weekday 2');

2011-10-04

 

-- 날짜 포맷 스타일 변경
select strftime("%Y/%m/%d %H:%M:%S",'now','localtime');

2011/07/21 10:21:59