SQL 기본 지침서

SQLite 기본 지침서

Kotlin으로 Android 앱 개발 과정에서는 다음 주제에 대해 잘 알고 있다고 가정합니다.

  • 일반적인 데이터베이스
  • 특히 SQL 데이터베이스
  • 데이터베이스와 상호작용하는 데 사용하는 SQL 언어

이 페이지는 복습 및 참고용으로 활용할 수 있습니다.

SQL 데이터베이스

SQL 데이터베이스는 다음과 같이 행 및 열 테이블에 데이터를 저장합니다.

  • 행과 열의 교차 부분을 필드라고 합니다.
  • 필드에는 데이터, 다른 필드에 대한 참조 또는 다른 테이블에 대한 참조가 포함됩니다.
  • 각 행에는 하나의 항목이 포함됩니다. 항목은 일반적으로 기본 키로 사용되는 고유 ID로 식별됩니다.
  • 각 열은 테이블별로 고유한 이름으로 식별됩니다.

SQLite

SQLite는 다음과 같은 특성을 가진 SQL 데이터베이스 엔진을 구현합니다.

  • 자체 포함(다른 구성요소가 필요하지 않음)
  • 서버리스(서버 백엔드가 필요하지 않음)
  • 구성 없음(앱에 대해 구성할 필요가 없음)
  • 트랜잭션(SQLite의 단일 트랜잭션 내 변경이 완전히 발생하거나 전혀 발생하지 않음)

SQLite는 전 세계에서 가장 광범위하게 배포된 데이터베이스 엔진입니다. SQLite의 소스 코드는 공개 도메인에 있습니다. SQLite 데이터베이스에 관한 자세한 내용은 SQLite 웹사이트를 참고하세요.

테이블 예

  • 이름이 DATABASE_NAME인 데이터베이스
  • 이름이 WORD_LIST_TABLE인 테이블
  • _id, word, definition 열

alpha 및 beta라는 단어를 삽입하면(이때 alpha에는 정의가 두 개 있음) 테이블은 다음과 같을 수 있습니다.

DATABASE_NAME

WORD_LIST_TABLE

_id

word

definition

1

'alpha'

'first letter'

2

'beta'

'second letter'

3

'alpha'

'particle'

특정 행의 내용을 찾으려면 _id를 사용하거나, 제약 조건을 지정해 테이블에서 행을 선택하는 쿼리를 작성하여 행을 검색합니다.

트랜잭션

트랜잭션은 작업의 단일 논리 단위로 실행되는 작업 시퀀스입니다. 트랜잭션으로 승인받으려면 작업의 논리 단위가 네 가지 속성인 원자성, 일관성, 격리성, 내구성을 나타내야 합니다.

  • 원자성: 트랜잭션의 모든 데이터 수정이 실행되거나 수정이 전혀 실행되지 않습니다. 프로그램 비정상 종료, 운영체제 비정상 종료 또는 정전으로 인해 변경사항을 디스크에 기록하는 작업이 중단되더라도 원자성은 true입니다.
  • 일관성: 트랜잭션이 완료될 때 트랜잭션은 모든 데이터를 일관된 상태로 유지해야 합니다.
  • 격리성: 동시 트랜잭션에 의한 수정은 다른 동시 트랜잭션에 의한 수정과 격리되어야 합니다. 트랜잭션은 다른 동시 트랜잭션이 데이터를 수정하기 전에 데이터가 있었던 상태의 데이터를 인식하거나 두 번째 트랜잭션이 완료된 후의 데이터를 인식합니다. 트랜잭션은 중간 상태를 인식하지 못합니다.
  • 내구성: 트랜잭션이 완료된 후의 효과는 시스템에 영구적으로 적용됩니다. 시스템 오류가 발생하더라도 수정사항이 유지됩니다.

트랜잭션의 예:

  • 저축예금 계좌에서 당좌예금 계좌로 자금을 이체합니다.
  • 사전에 용어 및 정의를 입력합니다.
  • 마스터 브랜치에 변경 목록을 커밋합니다.

트랜잭션에 관한 자세한 내용은 SQLite의 원자 커밋을 참고하세요.

쿼리 언어

SQL 쿼리 언어를 사용하여 데이터베이스와 상호작용할 수 있습니다. 쿼리는 매우 복잡할 수 있지만 다음과 같은 네 가지 기본 작업이 있습니다.

  • 행 삽입
  • 행 삭제
  • 행의 값 업데이트
  • 지정된 기준을 충족하는 행 검색

Android에서 데이터 액세스 객체(DAO)는 데이터베이스를 삽입하고 삭제하며 업데이트하기 위한 편의 메서드를 제공합니다. 쿼리 언어에 관한 자세한 설명은 SQLite에서 이해하는 SQL을 참고하세요.

쿼리 구조

SQL 쿼리는 고도로 구조화되어 있습니다. 샘플 쿼리:

  • SELECT word, definition FROM WORD_LIST_TABLE WHERE word="alpha"

샘플 쿼리의 일반 버전:

  • SELECT columns FROM table WHERE column="value"

샘플 쿼리의 부분:

  • SELECT columns: 반환할 열을 선택합니다. 모든 열을 반환하려면 *를 사용합니다.
  • FROM table: 결과를 가져올 테이블을 지정합니다.
  • WHERE: 충족해야 하는 조건(예: column="value") 앞에 오는 선택적 키워드입니다. 일반적인 연산자는 =, LIKE, <, >입니다. 여러 조건을 연결하려면 AND 또는 OR를 사용합니다.

쿼리의 다른 부분:

  • ORDER BY: 열을 기준으로 결과를 정렬하기 위한 핵심 구문입니다(선택사항). 오름차순의 경우 ASC를, 내림차순의 경우 DESC를 지정합니다. 순서를 지정하지 않으면 기본 순서가 적용되며, 이에 따라 정렬되지 않은 상태가 될 수 있습니다.
  • LIMIT: 제한된 수의 결과를 지정하는 키워드입니다.

샘플 쿼리 및 결과

다음 쿼리는 앞서 정의한 테이블을 사용합니다.

SELECT * FROM WORD_LIST_TABLE

WORD_LIST_TABLE 테이블의 모든 행을 가져옵니다.
SELECT word, definition FROM WORD_LIST_TABLE WHERE _id > 2 id가 2보다 큰 모든 항목의 word 및 definition 열을 선택합니다.
반환 값

[["alpha", "particle"]]

SELECT _id FROM WORD_LIST_TABLE WHERE word="alpha" AND definition LIKE "%art%"

정의에 하위 문자열 art가 있는 단어 alpha의 id를 반환합니다.

[["3"]]

SELECT definition FROM WORD_LIST_TABLE ORDER BY word DESC LIMIT 1

모든 정의를 선택합니다. 역순으로 정렬하고 목록이 정렬된 후 첫 번째 행을 가져옵니다. 지정된 열(word)을 기준으로 정렬됩니다. 반환하지 않는 열을 기준으로 정렬할 수 있습니다.

[["second letter"]]

SELECT * FROM WORD_LIST_TABLE LIMIT 2,1

위치 2에서 시작하는 항목 1개를 반환합니다. 위치 계산은 1부터 시작됩니다(0이 아님). 반환 값 [["2", "beta", "second letter"]]

이 SQL Fiddle 웹사이트에서 데이터베이스 생성 및 쿼리를 연습할 수 있습니다.

Android SQLite 쿼리

Android 시스템의 SQLite 데이터베이스에 원시 쿼리 또는 매개변수로 쿼리를 보낼 수 있습니다.

rawQuery(String sql, String[] selectionArgs) 메서드는 제공된 SQL을 실행합니다. 이 메서드는 결과 세트의 Cursor를 반환합니다. 다음 표는 위의 처음 두 샘플 쿼리를 어떻게 원시 쿼리로 표현하는지 보여줍니다.

1

String query = "SELECT * FROM WORD_LIST_TABLE";
rawQuery(query, null);

2

query = "SELECT word, definition FROM WORD_LIST_TABLE WHERE _id> ? ";
String[] selectionArgs = new String[]{"2"}
rawQuery(query, selectionArgs) ;

query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) 메서드는 지정된 테이블을 쿼리합니다. 이 메서드는 결과 세트의 Cursor를 반환합니다. 다음은 인수를 입력하는 방법을 보여주는 쿼리입니다.

SELECT * FROM WORD_LIST_TABLE
WHERE word="alpha"
ORDER BY word ASC LIMIT 2,1;

쿼리는 다음을 반환합니다.

[["alpha", "particle"]]

사용할 수 있는 인수의 예:

String table = "WORD_LIST_TABLE"
String[] columns = new String[]{"*"};
String selection = "word = ?"
String[] selectionArgs = new String[]{"alpha"};
String groupBy = null;
String having = null;
String orderBy = "word ASC"
String limit = "2,1"

query(table, columns, selection, selectionArgs, groupBy, having, orderBy, limit);

참고: 실제 코드에서는 null 값의 변수를 만들지 않습니다. 이 메서드의 다른 매개변수 사용 버전은 Android SQLiteDatabase 문서를 참고하세요.

커서

커서는 구조화된 데이터 행에 대한 포인터입니다. 커서를 테이블 행에 대한 포인터로 생각할 수 있습니다.

쿼리는 쿼리 결과의 첫 번째 요소를 가리키는 Cursor 객체를 반환합니다. Cursor 클래스는 쿼리 결과를 통해 커서를 이동하는 메서드와 결과의 각 행의 열에서 데이터를 가져오기 위한 메서드를 제공합니다.

메서드가 Cursor 객체를 반환하면 결과를 반복하고 데이터를 추출하며 데이터를 사용하여 작업하고 커서를 닫아 메모리를 해제합니다.

자세히 알아보기