SQL을 사용하여 데이터베이스 읽기 및 쓰기

1. 시작하기 전에

사용되는 앱은 대부분 데이터를 기기에 직접 저장합니다. 시계 앱은 반복되는 알람을 저장하고, Google 지도 앱은 최근 검색 목록을 저장하며, 연락처 앱을 사용하면 연락처 정보를 추가, 수정, 삭제할 수 있습니다.

데이터 지속성(기기에 데이터를 저장하거나 유지)은 Android 개발에서 큰 부분을 차지합니다. 영구 데이터를 사용하면 앱을 닫을 때 사용자 제작 콘텐츠가 손실되지 않고, 인터넷에서 다운로드한 데이터가 저장되므로 나중에 다시 다운로드할 필요가 없습니다.

SQLite는 Android 앱에서 데이터를 유지하도록 Android SDK에서 제공하는 일반적인 방법입니다. SQLite는 Kotlin 클래스로 데이터를 구조화하는 것과 비슷한 방식으로 데이터를 표현할 수 있는 관계형 데이터베이스를 제공합니다. 이 Codelab에서는 실제 프로그래밍 언어는 아니지만 단 몇 줄의 코드로 SQLite 데이터베이스를 읽고 수정하는 간단하고 유연한 방법을 제공하는 SQL(구조화된 쿼리 언어)의 기본사항을 알아봅니다.

SQL에 관한 기본적인 지식을 습득하면 Room 라이브러리를 사용하여 이 단원의 뒷부분에서 앱에 지속성을 추가할 수 있습니다.

2. 관계형 데이터베이스의 주요 개념

데이터베이스란 무엇인가요?

Google Sheets와 같은 스프레드시트 프로그램에 익숙하다면 데이터베이스의 기본적인 비유에 익숙할 것입니다.

스프레드시트는 별도의 데이터 테이블 또는 동일한 통합문서에 있는 개별 스프레드시트로 구성됩니다.

1f2b00d3ca083c4a.png

각 테이블은 데이터가 나타내는 내용을 정의하는 열과 각 열의 값이 있는 개별 항목을 나타내는 행으로 구성됩니다. 예를 들어 학생의 ID, 이름, 전공, 성적에 관한 열을 정의할 수 있습니다.

a441da5cc7be346b.png

각 행에는 각 열의 값이 있는 한 학생의 데이터가 포함됩니다.

6131d8a59996f521.png

관계형 데이터베이스도 같은 방식으로 작동합니다.

  • 테이블은 학생, 교수와 같은 표시하려는 데이터의 상위 그룹을 정의합니다.
  • 열은 테이블의 각 행에 포함되는 데이터를 정의합니다.
  • 행에는 테이블에 있는 각 열의 값으로 구성된 실제 데이터가 포함됩니다.

관계형 데이터베이스의 구조에는 Kotlin의 클래스 및 객체에 관해 이미 알고 있는 내용이 반영됩니다.

data class Student(
    id: Int,
    name: String,
    major: String,
    gpa: Double
)
  • 클래스는 테이블과 같이 앱에서 나타내려는 데이터를 모델링합니다.
  • 속성은 열과 같이 클래스의 모든 인스턴스에 포함해야 하는 특정 데이터를 정의합니다.
  • 객체는 행과 마찬가지로 실제 데이터입니다. 객체에는 클래스에서 정의된 각 속성의 값이 포함되며 이는 행에 데이터 테이블에서 정의된 각 열의 값이 포함되는 것과 같습니다.

스프레드시트 하나에 시트를 여러 개 포함할 수 있고 앱 하나에 클래스를 여러 개 포함할 수 있는 것처럼 데이터베이스 하나에 테이블을 여러 개 포함할 수 있습니다. 데이터베이스는 테이블 간의 관계를 모델링할 수 있는 경우 관계형 데이터베이스라고 합니다. 예를 들어 대학원생 한 명은 교수 한 명을 박사 지도교수로 둘 수 있지만 해당 교수는 여러 학생의 박사 지도교수입니다.

7f1b56e05520dc3.png

관계형 데이터베이스의 모든 테이블에는 행의 고유 식별자가 포함됩니다(예: 각 행의 값이 자동으로 증가되는 정수인 열). 이 식별자를 기본 키라고 합니다.

테이블이 다른 테이블의 기본 키를 참조하는 경우 이를 외래 키라고 합니다. 외래 키가 있으면 테이블 간에 관계가 있음을 의미합니다.

SQLite란 무엇인가요?

SQLite는 흔히 사용되는 관계형 데이터베이스입니다. 특히 SQLite는 구조화된 쿼리 언어(SQL, 줄여서 'sequel'이라고도 함)를 사용하여 관계형 데이터베이스 관리를 위해 경량 C 라이브러리를 참조합니다.

C 또는 완전히 새로운 프로그래밍 언어를 학습하지 않아도 관계형 데이터베이스를 사용할 수 있습니다. SQL은 코드 몇 줄로 관계형 데이터베이스에서 데이터를 추가하고 검색하는 방법입니다.

SQLite로 데이터 표현

Kotlin을 사용하면 Int 및 Boolean과 같은 데이터 유형에 익숙해집니다. SQLite 데이터베이스도 데이터 유형을 사용합니다. 데이터 테이블 열에는 특정 데이터 유형이 있어야 합니다. 다음 표는 일반적인 Kotlin 데이터 유형을 상응하는 SQLite 데이터 유형에 매핑합니다.

Kotlin 데이터 유형

SQLite 데이터 유형

Int

INTEGER

String

VARCHAR 또는 TEXT

Boolean

부울

Float, Double

REAL

데이터베이스의 테이블과 각 테이블의 열을 총칭하여 스키마라고 합니다. 다음 섹션에서는 시작 데이터 세트를 다운로드하여 스키마에 관해 자세히 알아봅니다.

3. 시작 데이터 세트 다운로드

이 Codelab의 데이터베이스는 가상의 이메일 앱을 위한 것입니다. 이 Codelab에서는 메일을 정렬 및 필터링하거나 제목 텍스트 또는 발신자로 검색하는 등 익숙한 예를 사용하여 SQL로 할 수 있는 모든 강력한 작업을 보여줍니다. 또한 이 예를 통해 다음 개발자 과정에서 Room을 사용하기 전에 앱에서 발견할 수 있는 시나리오 유형을 경험해 봅니다.

SQL 기본사항 GitHub 저장소의 compose 브랜치에서 시작 프로젝트를 다운로드하려면 여기를 클릭하세요.

Database Inspector 사용

Database Inspector를 사용하려면 다음 단계를 따르세요.

  1. Android 스튜디오에서 SQL Basics 앱을 실행합니다. 앱이 실행되면 다음 화면이 표시됩니다.

76e94dfe2234c2b1.png

  1. Android 스튜디오에서 View > Tool Windows > App Inspection을 클릭합니다.

cd5dd859d31cbab3.png

이제 하단에 App Inspection 라벨이 지정된 새 탭이 표시되며 Database Inspector 탭이 선택되어 있습니다. 탭이 두 개 더 있지만 사용하지 않아도 됩니다. 로드하는 데 몇 초 정도 걸릴 수 있으며, 쿼리를 실행하는 데 선택할 수 있는 데이터 테이블이 포함된 목록이 왼쪽에 표시됩니다.

5ace24ac5cc15abc.png

  1. Open New Query Tab 버튼을 클릭하여 창을 열고 데이터베이스를 대상으로 쿼리를 실행합니다.

277ecff401ca5f1a.png

email 테이블에는 다음과 같이 열이 7개 있습니다.

  • id: 기본 키입니다.
  • subject: 이메일의 제목입니다.
  • sender: 이메일을 보낸 이메일 주소입니다.
  • folder: 받은편지함이나 스팸과 같이 메일을 찾을 수 있는 폴더입니다.
  • starred: 사용자가 이메일에 별표표시했는지 여부입니다.
  • read: 사용자가 이메일을 읽었는지 여부입니다.
  • received: 이메일이 수신된 시점의 타임스탬프입니다.

4. SELECT 문으로 데이터 읽기

SQL SELECT 문

SQL 문(쿼리라고도 함)은 데이터베이스를 읽거나 조작하는 데 사용됩니다.

SELECT 문을 사용하여 SQLite 데이터베이스에서 데이터를 읽습니다. 간단한 SELECT 문은 SELECT 키워드, 열 이름, FROM 키워드, 테이블 이름순으로 구성됩니다. 모든 SQL 문은 세미콜론(;)으로 끝납니다.

2d7ff99736b072b9.png

SELECT 문은 여러 열의 데이터를 반환할 수도 있습니다. 열 이름은 쉼표로 구분해야 합니다.

cf94edd5de825043.png

테이블의 모든 열을 선택하려면 열 이름 대신 와일드 카드 문자(*)를 사용합니다.

fb75d3033c59949a.png

두 경우 모두 이와 같은 간단한 SELECT 문이 테이블의 모든 행을 반환합니다. 반환하려는 열 이름만 지정하면 됩니다.

SELECT 문을 사용하여 이메일 데이터 읽기

이메일 앱에서 실행해야 하는 주요 작업 중 하나는 메일 목록을 표시하는 것입니다. SQL 데이터베이스를 사용하면 SELECT 문으로 이 정보를 가져올 수 있습니다.

  1. Database Inspector에서 email 테이블이 선택되어 있는지 확인합니다.

ffc77f938ea09071.png

  1. 먼저 email 테이블의 모든 행에서 모든 열을 선택해 봅니다.
SELECT * FROM email;
  1. 텍스트 상자 오른쪽 하단에 있는 Run 버튼을 클릭합니다. 전체 email 테이블이 반환되는지 확인합니다.

4c3ea237c6ed2b57.png

  1. 이제 모든 행의 제목만 선택해 봅니다.
SELECT subject FROM email;
  1. 이번에도 쿼리를 통해 모든 행이 반환되지만 해당하는 단일 열에 관한 결과만 반환됩니다.

69a20935721dcc2.png

  1. 여러 열을 선택할 수도 있습니다. 제목과 발신자를 선택해 보세요.
SELECT subject, sender FROM email;
  1. 쿼리를 통해 email 테이블의 모든 행이 반환되지만 subject 및 sender 열의 값만 반환됩니다.

4ae739dad54397ea.png

축하합니다. 첫 번째 쿼리를 실행했습니다. 무사히 완료했지만 이것은 시작에 불과합니다. SQL의 'Hello World'라고 보면 됩니다.

절을 추가하여 데이터의 하위 집합을 지정하고 출력 형식이 지정되는 방식을 변경해 SELECT 문을 훨씬 구체적으로 사용할 수 있습니다. 다음 섹션에서는 흔히 사용되는 SELECT 문의 절과 데이터의 형식을 지정하는 방법을 알아봅니다.

5. 집계 함수 및 고유 값과 함께 SELECT 문 사용

집계 함수로 열 줄이기

SQL 문은 행을 반환하는 것으로만 제한되지 않습니다. SQL은 최댓값을 찾거나 특정 열에 가능한 고유한 값의 수를 계산하는 등 특정 열에서 연산이나 계산을 실행할 수 있는 다양한 함수를 제공합니다. 이러한 함수를 집계 함수라고 합니다. 특정 열의 데이터를 모두 반환하는 대신 특정 열의 단일 값을 반환할 수 있습니다.

SQL 집계 함수의 예는 다음과 같습니다.

  • COUNT(): 쿼리와 일치하는 총 행 개수를 반환합니다.
  • SUM(): 선택된 열에 있는 모든 행 값의 합계를 반환합니다.
  • AVG(): 선택된 열에 있는 모든 값의 평균값을 반환합니다.
  • MIN(): 선택된 열에서 가장 작은 값을 반환합니다.
  • MAX(): 선택된 열에서 가장 큰 값을 반환합니다.

열 이름 대신 집계 함수를 호출하고 괄호 안의 인수로 열 이름을 전달할 수 있습니다.

6730a62d583a0d9.png

테이블의 모든 행에 대해 해당 열의 값을 반환하는 대신 집계 함수를 호출하면 단일 값이 반환됩니다.

집계 함수는 데이터베이스의 모든 데이터를 읽을 필요가 없을 때 값에 관해 계산하는 효율적인 방법입니다. 예를 들어 전체 데이터베이스를 목록에 로드하여 수동으로 실행하지 않고 열의 평균값을 구하려고 할 수 있습니다.

email 테이블에서 실제로 사용하는 집계 함수를 살펴보겠습니다.

  1. 앱은 수신된 총 이메일 수를 가져오려고 할 수 있습니다. COUNT() 함수와 와일드 카드 문자(*)를 사용하면 됩니다.
SELECT COUNT(*) FROM email;
  1. 쿼리를 통해 단일 값이 반환됩니다. 이 작업은 행을 수동으로 계산하는 Kotlin 코드 없이 SQL 쿼리만으로 할 수 있습니다.

5d49b987545184bb.png

  1. 가장 최근 메일의 시간을 가져오려면 received 열에서 MAX() 함수를 사용하면 됩니다. 가장 높은 숫자가 최근 Unix 타임스탬프이기 때문입니다.
SELECT MAX(received) FROM email;
  1. 이 쿼리는 단일 결과, 즉 recieved 열에서 가장 높은(가장 최근) 타임스탬프를 반환합니다.

d0241dce845c3955.png

DISTINCT로 중복 결과 필터링

열을 선택할 때 앞에 DISTINCT 키워드를 붙일 수 있습니다. 이 접근 방식은 쿼리 결과에서 중복 항목을 삭제하려는 경우 유용할 수 있습니다.

4f02533256302f26.png

예를 들어 많은 이메일 앱에는 주소 자동 완성 기능이 있습니다. 이메일 발신자의 주소를 모두 포함하고 목록으로 표시할 수도 있습니다.

  1. 다음 쿼리를 실행하여 모든 행의 sender 열을 반환합니다.
SELECT sender FROM email;
  1. 결과에 다수의 중복 항목이 포함되어 있습니다. 이는 바람직하지 않습니다.

4f0489d1668dbede.png

  1. sender 열 앞에 DISTINCT 키워드를 추가하고 쿼리를 다시 실행합니다.
SELECT DISTINCT sender FROM email;
  1. 이제 결과가 훨씬 작고 모든 값이 고유한 것을 볼 수 있습니다.

43a47ad8d18fee6e.png

집계 함수에서 열 이름 앞에 DISTINCT 키워드를 붙일 수도 있습니다.

55c45cb9c258e882.png

데이터베이스에서 고유한 발신자 수를 알고 싶다고 가정해 보겠습니다. COUNT() 집계 함수와 sender 열의 DISTINCT 키워드를 사용하여 고유한 발신자 수를 계산할 수 있습니다.

  1. SELECT 문을 실행하여 DISTINCT sender를 COUNT() 함수에 전달합니다.
SELECT COUNT(DISTINCT sender) FROM email;
  1. 쿼리를 통해 고유한 발신자가 14명임을 알 수 있습니다.

19ae43b0bc9a927e.png

6. WHERE 절로 쿼리 필터링

많은 이메일 앱에서는 데이터, 검색어, 폴더, 발신자 등 특정 기준에 따라 표시된 메일을 필터링하는 기능을 제공합니다. 이러한 유형의 사용 사례에서는 WHERE 절을 SELECT 쿼리에 추가할 수 있습니다.

테이블 이름 뒤에 새 줄에서 WHERE 키워드와 표현식을 차례로 추가할 수 있습니다. 좀 더 복잡한 SQL 쿼리를 작성할 때는 가독성을 위해 각 절을 새 줄에 배치하는 것이 일반적입니다.

707b0641aa2de0f.png

이 쿼리는 선택된 각 행에서 불리언 검사를 실행합니다. true가 반환되는 경우 쿼리 결과에 행이 포함됩니다. 쿼리가 false를 반환하는 행은 결과에 포함되지 않습니다.

예를 들어 이메일 앱에는 스팸, 휴지통, 임시보관함 필터나 사용자가 만든 필터가 있을 수 있습니다. 다음 안내에서는 WHERE 절을 사용하여 이 작업을 실행합니다.

  1. folder = 'inbox' 조건을 검사하는 WHERE 절을 포함하여 email 테이블에서 모든 열(*)을 반환하도록 SELECT 문을 실행합니다. 오타가 아닙니다. SQL에서는 등호를 하나만 사용하여 서로 같은지 검사하고 큰따옴표가 아닌 작은따옴표를 사용하여 문자열 값을 나타냅니다.
SELECT * FROM email
WHERE folder = 'inbox';
  1. 사용자의 받은편지함에 있는 메일 행만 결과로 반환됩니다.

6e9f2a17186d7faa.png

WHERE 절을 사용하는 논리 연산자

SQL WHERE 절은 단일 표현식으로 제한되지 않습니다. Kotlin의 and 연산자(&&)와 동등한 AND 키워드를 사용하여 두 조건을 모두 충족하는 결과만 포함할 수 있습니다.

d8a698416e55d11b.png

또는 Kotlin의 or 연산자(||)와 동등한 OR 키워드를 사용하여 두 조건 중 하나를 충족하는 행을 결과에 포함할 수 있습니다.

f3cecac289e7650d.png

가독성을 위해 NOT 키워드를 사용하여 표현식을 무효화할 수도 있습니다.

27300a0a38ef0343.png

많은 이메일 앱에서는 여러 필터를 사용할 수 있습니다(예: 읽지 않은 메일만 표시).

email 테이블에서 다음과 같이 더 복잡한 WHERE 절을 사용해 보세요.

  1. 사용자의 받은편지함에 있는 메일만 반환하는 것 외에도 읽지 않은 메일로 결과를 제한해 봅니다. 여기서 read 열의 값은 false입니다.
SELECT * FROM email
WHERE folder = 'inbox' AND read = false;
  1. 쿼리를 실행하면 사용자의 받은편지함에 있는 읽지 않은 메일만 결과에 포함됨을 알 수 있습니다.

d9ebd307a146d320.png

  1. 중요 폴더에 있는 메일 OR 별표표시된(starred = true) 메일 모두를 반환합니다. 즉, 다양한 폴더의 이메일 중 별표표시가 되어 있는 이메일이 결과에 포함됩니다.
SELECT * FROM email
WHERE folder = 'important' OR starred = true;
  1. 결과를 확인합니다.

fd2f0dc7b6444956.png

LIKE를 사용하여 텍스트 검색

WHERE 절을 사용하여 할 수 있는 매우 유용한 작업 하나는 특정 열에서 텍스트를 검색하는 것입니다. 열 이름, LIKE 키워드, 검색 문자열을 차례로 지정하면 이 결과를 얻을 수 있습니다.

6692c0d491b6f9af.png

검색 문자열은 퍼센트 기호(%)로 시작하고 그 뒤에 검색할 텍스트(검색어), 퍼센트 기호(%)가 차례로 나옵니다.

c69c15f654645ee2.png

지정된 텍스트로 시작하는 결과인 접두사를 검색하는 경우 첫 번째 퍼센트 기호(%)를 생략합니다.

fbe6a94daaf173ae.png

또는 접미사를 검색하는 경우 마지막 퍼센트 기호(%)를 생략합니다.

141f567c9cbc4029.png

앱이 텍스트 검색을 사용할 수 있는 사용 사례는 다양합니다. 제목에 특정 텍스트가 포함된 이메일을 검색하거나 사용자가 입력할 때 자동 완성 추천 용어를 업데이트하는 것을 예로 들 수 있습니다.

다음 안내에 따라 email 테이블을 쿼리할 때 텍스트 검색을 사용할 수 있습니다.

  1. 데이터베이스에 있는 문자와 마찬가지로, 셰익스피어의 캐릭터들은 바보에 관해 이야기하는 것을 좋아했습니다. 다음 쿼리를 실행하여 제목에 'fool' 텍스트가 있는 총 이메일 수를 가져옵니다.
SELECT COUNT(*) FROM email
WHERE subject LIKE '%fool%';
  1. 결과를 확인합니다.

fd2ff96969824b0d.png

  1. 다음 쿼리를 실행하여 제목이 fool로 끝나는 모든 행의 모든 열을 반환합니다.
SELECT * FROM email
WHERE subject LIKE '%fool';
  1. 두 행이 반환되는 것을 확인합니다.

a23379e507e39c0b.png

  1. 다음 쿼리를 실행하여 문자 h로 시작하는 sender 열의 고유한 값을 반환합니다.
SELECT DISTINCT sender FROM email
WHERE sender LIKE 'h%';
  1. 쿼리에서 helena@example.com, hyppolytus@example.com, hermia@example.com, 이렇게 세 가지 값을 반환합니다.

47ada07aee5cd8d9.png

7. 결과 그룹화, 정렬, 제한

GROUP BY로 결과 그룹화

집계 함수와 WHERE 절을 사용하여 결과를 필터링하고 줄이는 방법을 알아봤습니다. SQL은 쿼리 결과의 형식을 지정하는 데 도움이 되는 다른 여러 절을 제공합니다. 이러한 절 중에는 결과를 그룹화하고 정렬하고 제한하는 것이 있습니다.

GROUP BY 절을 사용하여 결과를 그룹화하면 주어진 열에 동일한 값을 가진 모든 행이 결과에서 나란히 그룹화될 수 있습니다. 이 절은 결과를 변경하