[SQLite3] Appendix. SQLite3 커맨드 라인 도구, 관리도구

[SQLite3] Appendix. SQLite3 커맨드 라인 도구, 관리도구

이 글은 "빠르게 활용하는 모바일 데이터베이스 SQLite3” 이란 글을 보며 필요한 내용만 정리한 글입니다.

자세한 내용은 책을 구매해서 보세요

1. 셸 모드

-

SQLite3 의 셸 모드를 이용하면 윈도우의 명령 프롬프트나 리눅스의 셸과 같이 SQLite3 엔진과 대화식으로 명령어를 전달하고 그에 대한 결과를 받을 수 있다.

셸 모드는 개발 과정에서 SQL 구문을 작성하거나 디버깅 과정에서 DB 내용을 확인할 때 가장 많이 사용된다.

DB 생성

-

SQLite3 은 기본적으로 DB 파일 하나에 테이블, 레코드, 인덱스, 설정 정보 등 모든 데이터를 저장한다. 아래 명령을 통해 DB 파일을 생성하면서 Shell 에 진입할 수 있다. (존재하는 DB인 경우 생성 없이 Shell 에 진입한다. )

shell 모드에서 어떤 형태든 data 를 추가해야 (table 을 생성한다던지) 실제 db 파일이 생성된다.

> sqlite3 dbFileName

-

Shell 종료는 .exit 를 입력하면 된다.

( shell mode 에서 사용되는 명령어는 모두 마침표(‘.’) 로 시작하고 명령어 뒤에 세미콜론(‘;’)을 붙이지 않는다.

-

인자 없이 sqlite3 명령어만 실행하면 DB 내용이 파일에 저장되지 않고 메모리 내에서만 존재하게 된다.

shell 을 종료하면 모든 내용이 사라진다.

SQL 문 수행

-

SQL 문을 작성할 때 추후 유지보수를 위해 구문 중간에 주석을 입력해야 할 때가 있다.

한줄 주석인 경우에는 ‘—‘ 이후에 주석 내용을 입력하고, 여러 줄에 걸친 주석의 경우 C 와 동일하게 /* */ 사이에 주석 내용을 작성한다.

-

SQL 문은 세미콜론으로 끝나야 구문이 끝나기 때문에 가독성을 위해 enter 를 쳐서 new line 을 주면서 SQL 문을 작성할 수 있다.

테이블 조회

기본적으로 테이블 조회 결과는 | 구분자를 써서 각 필드를 구분하고 있다.

또한 NULL 값인 경우 출력할 때 아무것도 표시하지 않아 || 와 같이 구분자가 연이어 화면에 나타날 때도 있다.

.header ON 을 통해 필드의 이름을 표시하게 할 수 있고,

.mode column 을 통해 각 필드는 공백으로 구분하도록 할 수 있다.

.nullvalue NULL 을 통해 공백 대신 NULL 을 출력하도록 할 수 있다.

-

.width 명령어로 출력될 필드의 길이를 조정할 수도 있다.

.width 30 20 15 10 // 첫번째 column 이 30, 네번째 column 이 10 의 width 를 갖고 출력된다.

데이터베이스 및 테이블 스키마 정보

-

.tables 명령으로 DB 에 포함된 테이블들 이름을 조회할 수 있다.

.tables searchKeyword 으로 LIKE 연산과 같은 느낌으로 matching 되는 table 이름들을 출력할 수 있다.

.indices tableName 은 특정 테이블에 포함된 인덱스의 이름을 모두 출력한다.

.schema name 명령어는 테이블의 스키마, 인덱스와 같은 특정 테이블의 정보를 SQL 형태로 조회할 수 있다.

.schema 만 입력하면 DB 에 포함된 모든 테이블, 인덱스, 뷰, 트리거 정보가 출력된다.

-

DDL 구문으로 생성되는 모든 정보는 sqlite_master 라는 시스템 테이블에 저장된다.

이 테이블에는 타입(type), 이름(name), 생성한 테이블 이름(tbl_name), 루트 페이지 번호(rootpage), SQL 구문(sql)이 저장되며, 일반 SELECT 문으로 내용 조회가 가능하다.

type 에는 table, index 등이 입력되어 있다.

데이터 추출

dump 명령어를 이용하면 특정 테이블의 내용을 추출할 수 있다.

이 때 특정 테이블과 관련된 스키마, 트리거, 인덱스 정보를 비롯해 테이블에 저장된 레코드까지 추출할 수 있다.

또한 바이너리 포맷이 아닌 SQL 구문 형태로 추출하므로 SQLite3 뿐 아니라 다른 DBMS 에서 추출한 데이터를 가지고 동일한 DB 를 구축할 수도 있다.

이때 추출되는 SQL 구문은 명시적으로 트랜잭션을 사용해서 해당 DB 에 온전히 적용되게 한다.

.dump tableName — table 을 dump

.dump — 모든 내용을 dump

-

화면에 출력되는 내용을 특정 파일에 저장하려면 .output 명령어를 사용하면 된다.

> .output info.sql

> .dump bookInfo

화면 출력으로 다시 변경하려면

> .output stdout

파일에 저장된 SQL 구문의 실행

.read 명령어를 이용해서 파일에 저장된 SQL 구문을 수행할 수 있으며, 수행할 파일 이름을 인자로 전달하면 된다.

.read info.sql

출력 형식 변경

.mode html — HTML 형식

.mode csv — CSV 형식

.mode tabs — 탭으로 각 필드 구분

기타 설정

-

.echo 명령어를 이용하면 SQL 문을 수행할 때 명령어를 다시 한번 출력하는 기능을 활성화하거나(ON) 비활성화(OFF)할 수 있다.

이 기능이 활성화되면 SQL 구문을 수행하기 전에 해당 구문을 다시 한번 출력한다.

SELECT 문의 결과를 파일에 따로 남기는 경우 SELECT 문까지 함께 저장되므로 나중에 참고하는데 도움이 된다.

-

.separator 명령어는 SELECT 문에서 구분자를 변경할 때 사용한다.

.separator % — % 를 구분자로 구분한다

-

.prompt 명령어는 현재 출력되는 sqlite3 shell 모드의 프롬프트를 변경할 때 사용한다.

ex)

sqlite> .prompt SQLITE3>

SQLITE3> 

-

리눅스의 경우 .timer 명령어를 통해 SQL문의 수행 시간 측정을 활성화(ON) 하거나 비활성화(OFF) 할 수 있다.

.timer ON

현재 설정된 상태 확인

.show 명령어는 현재 설정된 상태값을 확인하는 데 사용한다.

도움말 보기

기타 명령어들은 .help 를 통해 볼 수 있다.

2. 인자 전달 모드

-

shell 에 진입하지 않고 직접 SQL 문을 인자로 전달할 수 있다.

sqlite3 [options] fileName [SQL]

ex)

$ sqlite3 test.db ‘SELECT * FROM info;’

$ sqlite3 -header -column -nullvalue NULL test.db ‘SELECT * FROM info;’

$ sqlite3 test.db ‘.dump’

-

파일에 저장된 내용이나 키보드 입력을 이용해서 shell 의 입력 리다이렉트 기능을 활용할 수 있다.

$ sqlite3 test.db < info2.sql

-

-init 옵션을 설정해 sqlite3 shell 모드에 진입하기 전에 먼저 수행할 SQL 문을 지정하는 것도 가능하다.

이때 -init 인자로 수행할 SQL 문이 저장된 파일명을 전달한다.

$ sqlite3 -init info.sql test.db

-

-help 옵션을 사용해 sqlite3 명령어에 쓸 수 있는 옵션을 확인할 수 있다.

$ sqlite3 -help

3. SQLite Manager

-

파폭 웹 브라우저에서 동작하는 관리 도구이다.

.echo, .exit, .header, .header ON, .help, .indices, .mode, .mode column, .mode csv, .mode html, .mode tabs, .nullvalue, .nullvalue NULL, .output, .output stdout, .prompt, .read, .schema, .separator, .show, .tables, .timer, .width, /*, ;, DB 생성, db 파일, DDL, dump, dump transaction, dump 트랜잭션, index, init, Like, multi line 주석, Name, rootpage, shell mode, shell 종료, SQL, sql 구문, sqlite manager, sqlitemanager, sqlite_master, sql문 구행, sql문 주석, system table, table, table search, tbl_name, type, [SQLite3] Appendix. SQLite3 커맨드 라인 도구, 관리도구, 기타 설정, 대화식 명령, 데이터 추출, 데이터베이스 및 테이블 스키마 정보, 도움말 보기, 루트 페이지 번호, 마침표, 메모리 db, 생성한 테이블 이름, 세미콜론, 셸 모드, 시스템 테이블, 이름, 인자 전달 모드, 출력 형식 변경, 커맨드 라인, 타입, 테이블 조회, 파이어 폭스, 파이어 폭스 관리도구, 파일에 저장된 sql 구문의 실행, 파폭, 현재 설정된 상태 확인