얼마전 SQLite의 새 버전인 3.37.0이 릴리즈되었습니다. 주로 안정성이나 성능 개선 위주의 업데이트만 있다가, 이번 버전에서는 STRICT 테이블이라는 새로운 기능이 추가되었습니다. 이 기능은 다른 DBMS 처럼 테이블 칼럼의 형식에 맞는 값만 저장하도록 제한하는 기능으로, 새로 생성하는 테이블의 옵션 형태로 설정하게 됩니다. SQLite3는 다른 DBMS와 달리 자료의 형(type)에 대해서 유연한 편입니다. 예를 들어 INTEGER 타입으로 정의된 칼럼에 “123” 같은 문자열을 저장하려 한다면 SQLite는 자동으로 이 문자열을 정수형태로 변환을 시도합니다. 따라서 저장되는 값은 문자열이 아닌 정수값 123이 저장됩니다. 만약 “xyz” 같은 변환이 불가능하거나,… 더 보기 »SQLite – STRICT 테이블
테이블을 다른 데이터베이스 파일로 이관하는 방법은 단순하다. 새로운 DB 파일을 새 데이터베이스로 연결해서, CREATE TABLE .. AS SELECT .. 구문을 사용해서 복사한다. 다음 예제는 외부 DB 파일을 반입한 다음, 특정 테이블 하나의 내용으로 새로운 테이블을 채우는 과정을 보여준다. ATTACH 'file-archives.db' AS other; CREATE TABLE other.images AS SELECT * FROM main.images; DETACH others; CREATE TABLE … AS … 를 사용하는 이 방법은 사실 한가지 함정을 가지고 있다. 이 쿼리는 테이블을 복제한다기보다는 한쪽의 데이터를 다른 테이블을 만들어서 밀어넣는 역할을 수행한다. 따라서 기존… 더 보기 »SQLite – 테이블을 다른 데이터베이스로 이관하기
INSERT 문은 테이블에 새로운 값을 추가할 때 사용하는 명령이다. 패턴에 따라서 몇 가지 형식으로 사용된다.
INSERT INTO table DEFAULT VALUES; : 테이블의 각 칼럼에 적절한 디폴트 값이 있다면 디폴트 값을 적용해서 새 Row를 추가한다. 디폴트 값에는 명시적으로 DEFAULT를 써서 선언한 값이나 자동으로 증가하는 인덱스 번호값 혹은 NULL이 사용될 수 있다. INSERT INTO table VALUES (val1, val2, val3, ... ); : 테이블의 모든 칼럼에 대해서 값을 던져주고 해당 값으로 새 레코드를 등록한다. 이 때 VALUES 뒤에 나오는 괄호의 값들은 테이블의 전체 칼럼 수와 같은 개수여야 하며, 테이블 스키마에 정의된 칼럼의 순서와 각각 매치되어야 한다. INSERT INTO table (col1, col3, col7) VALEUS (val1, val2, val3); : 1, 2의 혼합으로 칼럼의 일부에 대해서만 값을 제공하여 레코드를 생성한다. 여기에 명시되지 않은 칼럼들은 디폴트값으로 생성되기 때문에 NOT NULL이거나 디폴트가 정의되지 않았다면 에러가 난다. 트리거는 데이터 베이스 내에 특정한 동작이 수행될 때, 연관된 다른 쿼리를 자동으로 실행하는 기능이다. 예를 들어 어떤 레코드를 변경하거나 삭제할 때, 그와 관련된 다른 정보를 조작해야 한다거나 할 때 사용할 수 있다. 두 정보 사이의 관련성이 매우 밀접하다면, 클라이언트 코드에서 매번 쿼리를 두 번 실행하는 것보다 트리거를 통해서 관리하는 편이 더 나을 수 있는 것이다.
트리거를 사용하는 가장 일반적인 패턴은 카운트를 대체하기 위한 것이다. BOARD라는 테이블에 레코드가 몇 개인지 알기 위해서는 다음과 같은 쿼리를 실행할 수 있다.
SELECT count(*) FROM BOARD;문제는 count(*) 함수는 값을 계산하기 위해서 해당 쿼리의 모든 레코드 개수를 세어야 한다는 것이다. 테이블 크기가 작다면 별 문제가 되지 않겠지만, 수백만개의 레코드를 가지고 있는 테이블이라면 문제가 달라진다.
이번 글에서는 SELECT 구문 내에서 GROUP BY 절에 대해서 살펴보자. GROUP BY는 결과 행들을 특정한 하나 혹은 그 이상의 칼럼에 의한 그룹으로 묶어서 그룹을 요약하는 몇 가지 행들로 표현되도록 하는 것이다. 보통 MIN, MAX, SUM, AVG, COUNT 등과 같은 집합 함수와 같이 함께 사용된다. GROUP BY를 적용하는 주요 쿼리 구문의 형식은 다음과 같다. SELECT column1, aggregate_func(column1) FROM _table GROUP BY column1, column2, …. ; 다음의 예는 노래 정보 테이블에서 각 앨범당 수록곡의 개수를 산출하는 쿼리이다. SELECT albumid, COUNT(trackid) FROM tracks… 더 보기 »SELECT – GROUP BY (SQLite3)
SQLite에서 테이블을 생성하는 방법에 대해서 알아보자. 테이블을 생성할 때는 CREATE TABLE 구문을 사용한다. 이 구문에서는 테이블이 갖추어야 할 몇 가지 정보와 속성들을 정의할 수 있다. 새로운 테이블의 이름 테이블이 위치할 데이터베이스 테이블 내의 각 칼럼의 이름 각 칼럼의 데이터 타입 디폴트 값이 필요한 칼럼에는 디폴트 값 정의 collating sequece를 정의[^1] 필요에 따라 원시 키(PRIMARY KEY)를 정의할 수 있다. 테이블에 대한 제한을 걸 수 있다, UNIQUE, NOT NULL, CHECK, FOREIGN KEY 등이 있다. 이러한 내용들을 바탕으로 CREATE TABLE 문의 구조는 다음과 같다. [ ..… 더 보기 »테이블 생성하기 – SQLite3
지난 글에서 SQLite3에서 SELECT 명령의 사용방법에 대해서 살펴보았는데, FROM을 통해서 단일 테이블 혹은 단일 테이블 내의 범위를 부분적으로 얻어내는 서브 쿼리를 통해서 보다 정교한 범위의 데이터를 얻고, 또 WHERE절을 사용해서 결과를 필터링 하는 방법에 대해서도 살펴보았다. 그외에 GROUP BY나 그외 aggregation 연산을 통한 쿼리 방법에 대해서는 자세하게 다루지 않았는데, 그 전에 JOIN에 대해서 간단하게 짚고 넘어가고자 한다.
테이블에 저장된 데이터를 조회하기 위해서는 SELECT문을 사용한다. SELECT 구문운 SQL에서 가장 흔히 쓰이는 쿼리문인 동시에 가장 복잡한 구문이기도 하다. 테이블 조회와 관련하여 SQLite3는 표준 SQL에 정의된 거의 모든 기능을 제공한다. SQLite3의 SELECT 구문의 사용법에 대해서 살펴보자.
기본적으로 SELECT 는 DB 엔진으로부터 질의에 대한 답을 요청하는 명령이다. 일반적으로 테이블 내의 레코드들을 조회하는 용도로 사용되지만, SELECT 명령의 본질은 DB가 수행할 수 있는 연산의 결과를 얻는 명령이다. 따라서 테이블 조회뿐만 아니라 다음과 같이 계산 결과를 얻도록 호출할 수도 있다.
SELECT 1 + 1;위 쿼리는 단순히 2라는 결과를 얻게 된다. 테이블을 지정해서 데이터를 조회하지는 않지만 하나의 온전한 SELECT 구문이기도 하다. 물론 이것은 SELECT라는 명령이 DB엔진으로부터 어떠한 값을 요청한다는 개념을 보여주는 예이지, 실제로 이런 간단한 계산 때문에 데이터베이스를 사용하는 일은 드물다.
따라서 실제로는 다음의 개념으로 SELECT 명령을 이해해야 한다.
이 관점에 따르면 SELECT 1 + 1; 은 첫째로 연산의 결과를 요구하고 있으며, 가로가 0, 세로가 0인 (비어있는) 그리드를 대상으로 ‘조회’한다고 볼 수 있다. 그리고 그 결과는 1X1 그리드, 즉 단일값이 된다. 실제로 통상의 SELECT문이 대상으로 하는 테이블 역시 2차원 그리드이며, SELECT 문의 조회 결과 여러 열과 행으로 구성된 2차원 그리드가 된다.
더 보기 »(SQLite3) SELECT 문을 알아보자