[SQLite3] 03. SQL 고급 #1 |
이 글은 "빠르게 활용하는 모바일 데이터베이스 SQLite3” 이란 글을 보며 필요한 내용만 정리한 글입니다.
자세한 내용은 책을 구매해서 보세요
1. 관계형 데이터베이스 설계
-
한 개의 Table 에 모든 정보를 담는 경우 다음과 같은 단점이 있다.
중복되는 정보가 저장된다.
레코드 간 구분 가능한 필드가 명확히 없다. ( Property 가 거의 같은 경우 )
-
RDBMS 의 설계 원칙은 중복을 최소화하는 것이다.
데이터 중복성(redundancy)과 일관적이지 않은 데이터 종속성(dependency)를 제거하는 규칙에 따라 정보를 여러 테이블에 저장되도록 테이블을 설계하고, 테이블간 공통된 값으로 서로 연결되게 한다.
-
테이블의 각 행을 구분하는 하나 이상의 열을 primary key 라고 한다.
대개 이 기본키는 열 하나로 지정하지만 반드시 그렇게 해야 하는 것은 아니다.
각 열의 값은 고유할 필요는 없지만 이를 조합해서 만든 키의 값으로 각 행을 구분할 수 있다면 여러 열을 묶어서 기본키로 사용할 수 있다.
-
다른 테이블의 기본키를 참조하는 키를 외래키(foreign key)라고 한다.
2. 기본키
-
기본키는 테이블 내에서 유일하면서 최소한의 컬럼으로 구성돼야 한다.
( 유일성(uniqueness)과 최소성(minimality) )
테이블 내에서 레코드의 유일성을 보장하기 위한 제약조건을 개체 무결성(entity integrity)라고 한다.
-
유일키(unique key)는 설정된 칼럼에 입력되는 데이터가 테이블 내에서 유일하다는 것을 보장하기 위한 제약조건으로, 테이블 내에 하나 이상 설정할 수 있다.
UNIQUE 제약조건이나 UNIQUE 인덱스를 생성해서 설정할 수 있다.
유일성과 최소성을 만족하는 키들을 기본키로 사용될 수 있으므로 특별히 후보키(candidate key)라고 한다.
그 중 하나를 기본키로 선택하는 경우 나머지 후보키들은 대체키(alternate key)가 된다.
기본키 생성
-
syntax
CREATE TABLE [tableName] ( [columnName] [type] PRIMARY KEY );
-
다음과 같이 composite key 를 만들 수 있다.
CREATE TABLE employee ( name TEXT, job TEXT, PRIMARY KEY(name, job) );
-
SQLite3 에서는 명시적으로 기본키를 설정하지 않거나 기본키를 설정하더라도 INTEGER 자료형이 아닌 경우에는 내부적으로 숨겨진 기본키인 ROWID 를 추가한다.
이 컬럼은 INTEGER PRIMARY KEY AUTOINCREMENT 로 정의되어 있다.
숨겨진 컬럼이라 SELECT * 를 사용해서 가지고 올 수 없으며, 명시적으로 ROWID 라고 지정해야 조회할 수 있다.
기본키가 설정돼 있고, 자료형이 INTEGER 인 경우에는 ROWID 역시 INSERT 문에서 명시적으로 입력된 값으로 저장되며, ROWID 라는 컬럼명 대신 해당 기본키의 컬럼명으로 조회된다. ( ROWID 로 조회를 해도 primary key column name 으로 교체된 query 가 수행된다고 보면 된다. )
ROWID 는 OID, _ROWID 라는 별칭도 가지고 있다.
결국 그 별칭들은 display 될 때 모두 rowid 로 표시되긴 한다.
ROWID 가 있어도 내부구현이고, 다른 DBMS 와 호환성도 떨어지기 때문에 이 녀석을 로직에 이용하는 것은 권장하는 방법이 아니다.
기본키의 자동 증가
-
PRIMARY KEY 다음에 AUTOINCREMENT 를 써주면 된다.
-
SQLite3 에서는 내부적으로 기본키를 정의할 때 AUTOINCREMENT 를 사용하는 경우 입력된 기본키 중 가장 큰 값을 시스템 테이블인 sqlite_sequence 테이블에 저장해서 관리한다.
이러한 시스템 테이블은 보통 sqlite_ 라는 접두어로 시작하며, 사용자가 생성한 테이블과 동일하게 SELECT 문으로 조회할 수 있다.
기본키로 사용되기 위해 순차적으로 증가하는 값을 일반적으로 시퀀스(sequence)값이라고 부른다.
저장할 수 있는 2의 64승 -1 값이다.
-
기본키를 AUTOINCREMENT 로 설정하고, 그 값이 sqlite_sequence 테이블에서 관리할 수 있는 최대치까지 입력된 상태에서 새로운 레코드가 입력되면 SQLite3 은 SQLITE_FULL 에러를 반환하고, 해당 레코드 입력 연산은 실패한다.
-
테이블에서 기본키를 선정하는 방법은 크게 자연키와 인위적인 키로 나눌 수 있다.
자연키(Natural Key)는 테이블이 이미 존재하는 필드 가운데 기본키의 조건을 만족하는 필드를 선택하는 방식이다. 예를 들면 사람의 주민등록번호가 그것.
인위적인 키(Surrogate Key)는 데이터가 원래 가지고 있는 필드가 아닌 DB 에서 레코드 관리를 위해 기본키로 사용할 필드를 추가하는 방식으로 회사에 입사할 때 받는 사번이나 테이블의 publisher_id 가 그것이다.
기본적으로 업무상 기본키 역할을 하는 키가 있다면 해당 키를 DB 에서 기본키로, 즉 자연키를 기본키로 사용해야 한다.
자연키의 경우 데이터 자체가 가지고 있는 속성값을 이용하기 때문에 추가적인 작업이 필요하지 않다.
하지만 상황에 따라서는 변경되지 않을 것 같은 속성값이 변경되어 기본키의 조건을 만족하지 않을 수도 있다. 주민등록번호나 이름이 이에 해당한다.
또한 자연키에서 기본키가 두 개 이상의 필드를 조합한 복합키로 사용되는 경우 단일 필드의 기본키로 사용하는 경우와 비교해서 인덱스 관리 측면에서 효율성이 떨어진다.
이러한 경우 별도의 인위적인 키를 사용하면 저장 공간은 더 필요하지만 검색 성능 면에서 더 좋아질 수 있다.
3. 외래키
-
테이블의 한 필드가 다른 테이블의 기본키 필드를 참조하는 경우 이름 외래키(foreign key)라고 하며, 이 외래키를 통해 두 테이블 간의 연결 관계가 생성된다.
이때 하나 이상의 외래키를 포함하는 테이블을 종속 테이블, 종속 테이블에서 참조하는 기본키가 있는 테이블을 상위 테이블이라고 하며, 외래키는 상위 테이블의 기본키 컬럼과 이름이 달라도 된다.
-
외래키는 참조하고 있는 테이블에 존재하는 키이거나 NULL 이어야 하는데, 이를 위한 테이블 간의 데이터 일관성을 보장하기 위한 제약조건을 참조 무결성(referential integrity)라고 한다.
-
Oracle, MSSQL Server 와 같은 DBMS 는 참조키가 설정되면 자동적으로 참조 무결성 제약조건이 설정된다.
반면 SQLite3 의 3.6.19 이전 버전에서는 외래키를 설정하는 SQL 구문은 정상적으로 수행되나 DB 내부적으로 강제적인 참조 무결성 제약조건은 설정되지 않는다.
따라서 참조 무결성을 위반하는 레코드를 입력해도 아무런 에러 없이 수행되었다.
3.6.19 이후 버전에서는 참조 무결성 제약조건이 추가됐지만 이전 버전과의 하위 호환성을 위해 기본적으로 해당 기능은 비활성화돼 있다.
PRAGMA foreign_keys = 1;
위 명령을 통해 활성화할 수 있다. 0 값이 비활성화이다.
-
Foreign key syntax
CONTRAINT [contraintName] FOREIGN KEY ( [foreignKeyColumnName] ) REFERENCES [superTable]( [keyColumnName] )
여기서 CONTRAINT [contraintName] 은 생략 가능하다.
ex)
CREATE TABLE bookInfo(
book_id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT,
price INTEGER,
publisher_id INTEGER,
CONSTRAINT ctPublisherID_fk FOREIGN_KEY(publisher_id) REFERENCES publisherInfo(publisher_id));
-
SQLite3 3.6.19 이후 버전이더라도 외래키 제약조건이 비활성화되면 참조 무결성을 위반하는 내용도 정상적으로 입력된다
4. 중첩 질의
조건절의 중첩 질의
중첩 질의의 수행 순서는 항상 가장 안쪽부터 바깥쪽으로 수행된다.
중첩 질의를 너무 많이 쓰면 SQL 문의 가독성이 떨어지므로 오류가 발생해도 찾기가 어려워진다.
그러므로 들여쓰기를 적절히 사용해서 읽기 쉽게 만드는 것이 좋다.
ex)
SELECT title, price FROM bookInfo
WHERE publisher_id = ( SELECT publisher_id FROM publisherInfo
WHERE name=‘Apress’);
검색 컬럼의 중첩 질의
집계 함수와 같이 사용해서 SELECT 문의 검색 컬럼으로도 사용할 수 있다.
ex)
SELECT name, (SELECT count(*) FROM bookInfo WHERE bookInfo.publisher_id = publihserInfo.publisher_id) AS count)
FROM publisherInfo;
-
NULL 에 대한 모든 비교 연산의 결과는 NULL이 반환되며, 이 결과 조건은 거짓(false)로 판명된다.
5. 조인 연산
-
테이블을 연결하는 방식에 따라 내부 조인(INNER JOIN), 자연 조인(NATURAL JOIN), 외부 조인(OUTER JOIN), 자체 조인(SELF JOIN), 크로스 조인(CROSS JOIN) 등이 있다.
내부 조인 (INNER JOIN)
INNER JOIN 은 두 테이블을 연결하는 조건을 만족하는 결과만 반환하는 조인이다.
OUTER JOIN 과 정반대되는 개념이며 일반적으로 가장 많이 사용한다.
syntax
SELECT [tableName1].[columnName1], …
FROM [tableName1] INNER JOIN [tableName2]
ON [joinCondition]
ON 대신 WHERE 절을 사용해도 된다.
INNER JOIN 이 가장 많이 사용되므로 INNER JOIN 대신 , 를 사용해도 된다.
-
join을 할 때 tableName.columnName 의 형태로 쓰는 것이 좋다.
두 개의 table 에 동일 columnName 을 가진 경우 문제가 될 수 있기 때문이다
현재는 unique name 이라고 해도 추후에 다른 table 에 동일이름이 생길 수 있기 때문에 유지보수상 tableName.columnName 의 형태로 쓰는 것이 추천된다.
-
컬럼 이름에 별칭을 지정했던 것처럼 테이블 이름 뒤에 AS 키워드를 사용해서 테이블 별칭을 지정할 수 있다.
AS 키워드를 생략할 수도 있다.
SELECT B.title, B.price. P.name FROM bookInfo B, publisher P WHERE B.publisher_id = P.publisher_id
자체 조인
SELF JOIN 이란 동일한 테이블을 INNER JOIN 연결해서 조회하는 방법이다.
SELECT title, price FROM bookInfo WHERE price IN
( SELECT price FROM bookInfo WHERE title=‘SQLite3’ );
위의 중첩 질의를 self join 으로 변경할 수 있다.
SELECT B1.title, B1.price
FROM bookInfo B1, bookInfo B2
WHERE B1.price = B2.price
AND B2.title=‘SQLite3’;
일반적으로 중첩 질의를 사용하는 것보다 위와 같이 조인 연산을 사용하는 편이 조금 더 빠르다.
자연 조인
NATURAL JOIN 은 내부 조인의 일종으로 조인 조건을 명시적으로 작성하는 대신 테이블 사이에서 공통적인 컬럼 이름을 매개체로 연결하는 방식이다.
FROM 절에서 NATURAL JOIN 이라는 키워드를 사용하며, 이미 공통된 컬럼 이름으로 연결되기 때문에 조인 조건(WHERE, ON 절)은 작성하지 않는다.
또한 같은 이름의 컬럼을 바탕으로 연결됐기 때문에 SELECT 문의 조회 필드에 와일드 카드(*)를 사용하는 경우에도 중복된 컬럼은 한 번만 조회된다.
외부 조인
-
대부분의 조인은 중속 테이블의 참조키가 상위 테이블의 기본키를 참조하는 형태이다.
종속 테이블의 참조키가 NULL 인 경우에는 조인 연산의 조건절과 결과가 NULL 이 되어 결과 집합에 포함되지 않는다.
OUTER JOIN 으로 두 테이블을 연결하면 지정된 테이블의 연결 정보가 없는 레코드도 결과집합에 포함된다.
FROM 절에 OUTER JOIN 이라는 키워드를 사용해서 지정해야 한다.
-
왼쪽 테이블의 모든 행이 검색되게 하는 LEFT OUTER JOIN, 오른쪽 테이블의 모든 행이 검색되게 하는 RIGHT OUTER JOIN, 외부 조인 양쪽 테이블의 모든 행이 검색되게 하는 FULL OUTER JOIN 이 있다.
하지만 SQLite3 은 LEFT OUTER JOIN 만 지원한다.
RIGHT OUTER JOIN 은 LEFT OUTER JOIN 에서 table 의 위치만 조정해서 할 수 있다.
-
조인 연산에서 조인 조건절이 생략되면 테이블 간의 관계를 유추할 수 없으므로 종속 테이블의 모든 행이 상위 테이블의 모든 행과 관계를 맺게 된다.
이러한 형태의 조인 방법을 크로스 조인(CROSS JOIN)이라고 한다.
일반적으로 INNER JOIN 에서 JOIN 조건만 생략하면 크로스 조인이 된다.
하지만 이런 경우는 대부분 무의미한 정보라서 실제 업무에서는 거의 사용하지 않는다.
6. 인덱스
-
인덱스(Index)는 일반적으로 DBMS 에서 데이터를 기준에 맞춰 정렬해서 검색과 정렬 속도를 높이는 데 사용된다.
인덱스를 정의하면 DB 내부적으로 도서의 색인과 같은 자료구조 형태로 해당 열을 정렬해서 관리하게 된다.
따라서 테이블 전체를 순차적으로 검색하는 것에 비해 인덱스를 이용해 검색하는 편이 빠른 성능을 발휘한다.
반면 인덱스 갱신을 위한 추가적인 시간이나 공간이 필요해진다.
-
인덱스를 사용할 때의 장단점은..
장점 : 일치 검색(“=“), 범위 검색(“<“) 의 성능이 향상됨
필터링이나 정렬 연산의 성능이 향상됨
단점 : 레코드 삽입/삭제/갱신 시 인덱스를 갱신하는 시간이 추가되어 성능 저하가 발생
인덱스를 저장할 추가적인 공간이 필요
인덱스 생성
-
syntax
CREATE INDEX [indexName] ON [tableName]( [fieldName] [orderingType], … );
정렬방법은 생략할 수 있다.
둘 이상의 필드에 인덱스를 설정하려면 콤마를 구분자로 사용하면 된다.
테이블 내에 레코드 저장여부와 상관없이 인덱스를 생성할 수 있고, 하나의 테이블에 생성할 수 있는 인덱스의 개수는 제한이 없다.
ex)
CREATE INDEX idxBookInfoTitle ON bookInfo(title ASC);
( 오름차순이 기본이라 ASC 은 생략해도 된다. )
-
동일한 결과집합을 반환하는 SQL 문이라도 결과를 얻는 데 걸리는 시간은 인덱스의 사용 여부에 따라 큰 차이가 날 수 있다.
질의문에 따라 해당 인덱스를 사용할지 여부는 DBMS 내부적으로 판단하므로 개발자는 질의문을 작성할 때 최적의 성능을 내도록 작성해야 한다.
이렇게 DBMS 내부적으로 질의를 수행하는 단계를 실행 계획(Query Plan)이라고 하며, SQLite3 에서는 이러한 내부 동작을 확인하는 명령어가 있다.
질의문 앞에 EXPLAIN QUERY PLAN 이라는 키워드를 사용하면 해당 질의문이 인덱스를 활용하는지 확인할 수 있다.
-
INDEX 생성도 기존재하는 Index 를 재생성하려고 하면 에러가 나기 때문에 IF NOT EXISTS 를 함게 쓰는 것이 좋다.
UNIQUE 인덱스
테이블의 특정 컬럼에 중복된 데이터가 입력되지 않게끔 CREATE 와 INDEX 키워드 사이에 UNIQUE 키워드를 입력해서 UNIQUE INDEX 를 생성할 수 있다.
CREATE UNIQUE INDEX idxBookInfoTitle_uk ON bookInfo(title);
이미 테이블에 레코드가 존재하는 상태에서 UNIQUE INDEX 를 생성하려면 해당 필드에 저장된 데이터는 중복값이 없어야 한다. 그렇지 않으면 에러가 발생한다.
다중 칼럼 인덱스
두 개 이상의 컬럼에 하나의 인덱스를 생성하는 것을 다중 컬럼 인덱스(multi-column index)라고 한다.
다중 컬럼 인덱스를 사용하는 이유는 WHERE 절과 같은 조건절이나 정렬 조건이 하나 이상으로 복잡한 경우 이를 빠르게 검색하기 위해서이다.
ex)
CREATE INDEX idxBookInfoTitleISBN ON bookInfo(title ASC, isbn DESC);
인덱스 삭제
-
syntax
DROP INDEX [indexName];
IF EXISTS 를 함께 써주는 것이 좋다.
-
일반적으로 DBMS 에서는 B-Tree 계열의 인덱스를 사용하지만 해시나 다른 종류의 인덱스는 정렬과는 무관하다.
7. 테이블 제약 조건
-
DB 작업 중 자주 발생하는 오류 중 하나는 데이터 값이 반드시 입력되어야 하는 컬럼을 누락해서 NULL 이 되는 경우이다.
특히 이 문제는 테이블 스키마를 작성한 사용자와 데이터를 입력하는 사용자가 서로 다른 경우에 자주 발생한다.
이 경우 INSERT 구문을 수행할 때 누락된 컬럼을 알려주면서 에러가 발생하게 한다면 사용자가 실수를 쉽게 알아차릴 수 있을 것이다.
테이블 제약조건은 특정 컬럼이 설정된 조건을 만족하도록 강제한다.
NOT NULL
CREATE TABLE contact( id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL );
DEFAULT 제약 조건
-
컬럼 값을 입력하지 않았을 경우 들어가는 기본 값을 설정한다.
CRETE TABLE contact( id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
mobile TEXT DEFAULT ‘ignored’);
default 값을 지정했어도 insert 나 update 할 때 명시적으로 NULL 을 넣을 수는 있다. 즉, default 값이 NOT NULL 을 보장하는 것은 아니다.
-
CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP 를 이용해 DEFAULT 를 설정할 수도 있다.
CREATE TABLE dataLog( id INTEGER,
date NOT NULL DEFAULT CURRENT_DATE,
time NOT NULL DEFAULT CURRENT_TIME,
timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP);
형태는 아래와 같다.
date 는 YYYY-MM-DD
time 은 HH:MM:SS
timestamp 는 YYYY-MM-DD HH:MM:SS
UNIQUE 제약 조건
-
특정 컬럼에 대해 입력되는 데이터가 중복 없이 유일해야 하는 경우가 있다.
이런 경우 UNIQUE 제약조건을 걸어주면 된다.
CREATE TABLE contact(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL UNIQUE);
-
UNIQUE 조건을 별도로 정의할 수도 있다.
CREATE TABLE contact(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
UNIQUE(name) );
-
NULL 값은 UNIQUE 제약조건에 영향을 받지 않고 중복해서 입력할 수 있다.
반면 다른 DBMS 인 MS SQL Server 에서는 NULL 값 역시 UNIQUE 속성의 영향을 받아서 1개만 입력할 수 있다.
-
둘 이상의 컬럼을 묶어서 UNIQUE 제약 조건을 설정할 수도 있다.
CREATE TABLE contact(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
mobile TEXT DEFAULT ‘생략’,
UNIQUE(name, mobile)
);
CHECK 제약 조건
-
예를 들어 gender 의 경우 보통 M, F 만 받길 원한다.
이렇게 해두면 데이터를 조회할 때 조건이 단순해져 SQL 문을 작성하기도 쉽고, 검색 성능도 상대적으로 좋다.
특정 컬럼의 데이터의 값을 제약하거나 어떤 범위로 한정하려면 CHECK 제약 조건을 사용하면 된다.
CHECK 제약조건은 조건식을 인자로 받아서 해당 조건이 참인 경우에만 테이블에 레코드를 입력/수정할 수 있다.
CREATE TABLE contact(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
gender TEXT CHECK(gender IN (‘M’, 'F’))
);
-
CHECK 조건 역시 UNIQUE 처럼 절을 따로 분리해서 독립적으로 사용할 수 있다.
CREATE TABLE contact(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
gender TEXT,
CHECK(gender IN (‘M’, “F’))
);
8. 뷰
-
뷰(view)는 하나 이상의 테이블 혹은 다른 뷰로부터 유도되는 일종의 가상 테이블이다.
syntax
CREATE VIEW [viewName] AS [selectQuery]
ex)
CREATE VIEW vwAllBookInfo
AS
SELECT bookInfo.title, bookInfo.price, publisherInfo.name
FROM bookInfo, publisherInfo
WHERE bookInfo.publisher_id = publihserInfo.publisher_id;
-
view table 도 .tables 명령어를 통해 똑같이 볼 수 있다.
그리고 일반 table 처럼 SELECT 으로 조회할 수 있다.
뷰는 읽기 전용으로 조회만 가능하다. CUD 를 수행하면 에러가 난다.
뷰를 이용하면 복잡한 질의를 단순화할 수 있고 손쉽게 SQL 문을 재사용할 수 있다.
-
CREATE TABLE 과 동일하게 CREATE VIEW 도 IF NOT EXISTS 를 함께 사용하는 것이 좋다.
-
Trigger 를 이용해 뷰에 INSERT, DELETE, UPDATE 와 같은 연산이 수행되는 경우 원본 테이블에 해당 수정 사항이 반영되게 할 수도 있다.
이는 추후 trigger 에서 다룬다.
-
뷰를 생성할 때 두 개의 필드를 합쳐 하나의 필드로 만들 수도 있다.
ex)
CREATE VIEW vwPrettyBookInfo
AS
SELECT bookInfo.title || ‘(‘ || publisherInfo.name || ‘)’ AS NAME, price AS PRICE
FROM bookInfo, publisherInfo
WHERE bookInfo.publisher_id = publisherInfo.publisher_id;
|| 는 concat 하는 데 사용된다.
-
아래 명령으로 뷰를 삭제할 수도 있다.
DROP VIEW IF EXISTS [viewName];
-
CREATE 와 VIEW 사이에 TEMPORARY 나 TEMP 키워드를 사용해서 임시뷰를 생성할 수 있다.
이 뷰는 db 를 open 한 프로세스 내에서만 유효하고, 해당 프로세스가 DB 를 close 하면 자동으로 삭제된다.
ex)
CREATE TEMPORARY VIEW vwTempBookInfo
AS
…
'프로그래밍 놀이터 > Database' 카테고리의 다른 글
| [SQLite3] 04. SQLite3 C API 기초 (4) | 2018.05.20 |
|---|---|
| [SQLite3] 03. SQL 고급 #2 (0) | 2018.05.19 |
| [SQLite3] 02. SQLite3 설치 (0) | 2018.05.17 |
| [SQLite3] 01. SQLite 소개 (0) | 2018.05.16 |
| sqlite 의 transaction mode 와 lock 의 종류 특성. (0) | 2014.08.31 |
댓글