DB : SQLite (DQL) 명령어

이번 단원은 데이터를 조회하는 법을 배울 것이다. 물론 테이블을 만드는 방법을 안 후에 해야하지 않을까 했는데, 우리가 테이블 만들일이 얼마나 있을까? 일단은 조회하는 법을 알자. 그러면 어떻게 만들어야 하는지 알겠지!

---

 

DQL (Data Query Language) : 데이터 검색

핵심 SQL 키워드

• SELECT(+DISTINCT)
• FROM
• WHERE
• ORDER BY
• GROUP BY(+HAVING)
• LIMIT(+OFFSET)

vs code에서 sqlite3 관련 프로그램과 extention을 설치하면 db데이터 내부를 확인 가능하다

 

table 이름이 db 파일의 이름과 동일해서 헷갈릴 수 있으나, SELECT syntax에서는 데이터 베이스 파일자체를 탐색하는 것이 아니라 sql 파일에서 db파일을 연결 해둔다.

데이터 베이스 이름은 상관 없다.

 

탐색할 때 연결만 해두면 되는 것이고, 탐색은 table명으로 지정한다. 이후 SELECT로 column(field)를 지정하여 데이터를 가져온다.

---

SELECT

◆ 데이터 조회

가장 기본적인 조회 방법

 

위의 방식은 가장 일반적으로 데이터를 조회하는 방법이며, 해석하자면

'table_name 테이블에서 select_list 필드의 모든 데이터를 조회' 를 의미한다.

 

이번에 배우면서 제일 많이본 오류

 

위의 오류는 문법 오류로 SQL 문법 중 끝맺힘에 ' ; '(세미콜론)을 붙어야 하는데 붙이지 않았을 때 발생하게 된다.

column 혹은 table을 확인 할 수 없을 때 팝업되는 오류들이다. 주로 오타거나 없는 정보 검색, 혹은 db연결을 잘못했을 때 발생한다

 

다음의 예시들로 데이터를 조회하는 방법에는 어떠한 것들이 있는지 살펴보자.

 

◆ 데이터 다수 조회

해당 과정을 통해 데이터가 조회되는 것을 알았으니, 어떤 표현 방식들이 있는지도 살펴보자.

데이터를 Title뿐만 다른 column(field)도 원한다면? 같이 써주면 된다.

 

이정도면 어느정도 조회에 대한 감은 잡혔을 것 같으니, 데이터에 표현을 조금 간단히 하겠다.

 

◆ 데이터 전체 조회

그러면 데이터를 보려면 전부 Column(Field) 명을 알고 써야할까? 아니다. 한번에 나타낼 수 있는 만능 기호 ' * '(Asterisk) 가 있다!!

바로 써보자.

invoices table에 해당되는 모든 field 데이터를 가져온 것을 확인할 수 있다. 이걸 일일이 친다면.. 어우

 

◆ 데이터 Column(Field) 데이터 명 출력 변경

그러면 우리는 데이터를 불러들어올 때 해당 필드명으로만 해야할까? 아니다. AS + '(변경할 이름)' 를 통해임시로 바꿔줄 수 있다. 은근히 as도 여기저기 많이 쓰이는 문법인 것 같다. django할때도 as_p 등등 사용했었는데..

유용하게 쓰이므로 꼭 알아두자!

 

◆ 데이터 연산 출력

데이터 중에 시간, 혹은 금액 등 다양한 방식이 있을 텐데, 큰 수를 그대로 가져와야할까? '초' 단위를 '분' 단위로 바꿀 수 없을까? 놀랍게도 연산이 가능하다!

별도 처리 없이 기본 출력했을 경우 field에 입력된 데이터를 그대로들고온다.

연산 후 출력된 모습. 기본 정수 처리가 되며 소수점은 버림 처리된다.

 

크게 사용하는일은 없을 것 같지만 소수점을 살리는 방법도 있다.

 

---

ORDER BY

◆ 정렬된 데이터 조회

아무런 작업을 하지 않는다면 데이터 베이스의 입력된 순서를 지킨다

SELECT된 column에 ASC을 넣어준다면 특수기호-숫자-알파벳 등의 순서로 정렬된다.

 

DESC를 입력해주면 내림차순임을 알 수 있다.

 

ASC : 오름차순 (Ascending order)

DESC : 내림차순 (Descending order)

 

ORDER BY 의 기본값은 ASC로 오름차순이다. ASC를 기입하지 않아도 자동으로 오름차순이 입력되니 주의하자. 또, 중요한 부분이 있는데, 중복된 Column 값이 있다면 다른 Column 값을 통해 재정렬을 할 수 있다.

 

 

◆ 다수 Column(Field) 정렬된 데이터 조회

정렬은 앞쪽부터 차근차근 진행된다

 

추가로 NULL 값은 오름차순을 진행 했을 때 가장 위쪽에 위치하게 된다. 반대로 내림차순일 경우 가장 마지막에 위치한다.

 

' , '가 있다고 뒤의 DESC를 이어 받지 않는다. 기본값인 ASC를 진행한다는 것을 꼭 인지하자.

 

또한 2개이상의 정렬은 입력 순서가 달라져버리면 의도된 바와 다르게 출력 될 수 있으니, 유의하면서 진행해야 한다.

 

◆ 데이터 연산이 진행된 정렬

데이터가 연산된 정렬도 가능하다

 

60000으로 나누게 되면서 재생시간이 동일한 데이터가 생기게 되는데, 순서가 그러면 꼬일 수 있지 않을까? 라는 의문이 들겠지만, 나중에 데이터 순서를 배우면서 각 명령어 별 우선순위가 있기 때문에 연산된 데이터를 정렬하는 것이다.

지금은 간단하게만 알아 두자!

---

DISTINCT

◆ 중복 제거

Clause(절)에 해당되며 쓰이는 방법이 특이하고 데이터들의 중복을 제거할 수 있다. SELECT 바로 뒤에 쓰인다.

다른 방식으로도 중복을 제거할 수 있으므로 둘 다 알아둬야한다.(GROUP BY)

초기 데이터

DISTINCT 사용 후 ReportsTO와 Title에서 중복이 제거되었다.

 

여기서 조심해야 할 부분은 DISTINCT 가 각각 요소에 적용되는 것(OR)이 아니라 (AND)가 적용된다는 점이다. 예를들어 오른쪽 출력값을 확인해보면, 중복을 제거하는 DISTINCT ReportsTo가 적용 되었는데 1에 해당하는 값이 두개 출력되어있는 것을 확인 할 수 있다. 이를 토대로 다수로 중복을 제거하는 경우가 생기면, ReportsTo 와 Title이 모두 중복되어야 제거를 한다는 점을 유추할 수 있다.

---

WHERE

: FROM 뒤에 쓰인다.

Comparison Operatiors(비교 연산자)	Logical Operators(논리 연산자)
=, >=, <= !=, IS, LIKE, IN, BETWEEN, + NOT 등	AND, OR, NOT

 

WHERE과 같이 쓰이는 연산자들을 구분하자면 위와 같지만(논리 연산자는 비교 연산자에 모두 있는 연산자),  의미적으로 구분할 정도로 중요하지 않고 각 용어들이 어떤 것을 의미하는지가 중요하다. 

 

◆ 데이터 일치

' = '을 사용하여 해당 Column 값이 ' ' 와 동일한 데이터만 조회할 수 있다

 

◆ 데이터 불일치

일치하는 데이터가 = 라면 과연 반대는? 우리는 파이썬을 열심히 배웠으니 알것이다. ' != '인 것을.

Amsterdam이 아닌 BillingCity를 모두 조회할 수 있다

 

◆ NULL 데이터 조회

이전 예시에서 NULL 값을 본적이 있다. 오름차순을 진행했을 때, 가장 위에 출력되는 것을 확인 할 수 있었는데, 이번에는 해당 값을 조회하는 방법이다.

다만 위에서 사용한 ' = ' 이 아닌 ' IS '를 사용하니 주의하자

NULL 값을 조회하는 것을 확인할 수 있다.

반대로 IS NOT 도 가능하다

 

사용 방식이 사실 'IS'는 ' = ' 와, 'IS NOT' 은 ' != '으로 표현이 가능하긴 하다. 하지만 명백한 용도의 의미를 띄고 있기 때문에 혼용하지 말고 정확하게 사용하는 것을 권장한다.

 

◆ 'AND' 와 'OR'을 이용한 조건 추가

여러가지 조건도 추가가 가능하다. 예를들어 한국인이면서 여자인 경우, 혹은 외국인이면서 여자가 아닌 경우 등 여러가지로 묶어서 가능하다. 이 역시 파이썬과 비슷한 구문을 갖고 있다.

ReportsTo가 NULL 값이 아니면서 Title이 Sales Support Agent를 조회할 경우이다.

 

물론 OR도 가능하다

 

ReportsTo이 1이거나 2일경우 모두 조회한다.

 

◆ 비교 연산자를 통한 조건 조회

파이썬과 동일하게 '>=',  '>',  '<',  '<=' 모두 사용 가능하며 한번에 작성도 물론 가능하다.

minute가 14미만이면서 10이상인 경우를 모두 조회할 수 있다.

더 심화해서 나타내면 minute가 14미만, 12이상인 데이터를 오름차순 정렬 조회한다

 

◆ IN 을 이용한 조건 조회

이와 유사한 방식이 ' = ' 을 OR로 나열 했을 때와 동일하다.

minute에 3 혹은 4 혹은 5 이 있는지 확인 조회한다. OR 을 사용했을 때와 동일한 결과를 가져온다.

 

그렇다면 반대도 이렇게 진행하면 될까??

---

 

아쉽게도 아니다. 왜냐면 IN 인 기본적으로 OR를 의미하고 있으니 그에 반대를 사용하게 된다면 WHERE OR 이 아닌 WHERE AND를 사용해야한다!

해당 구문이 동일한 구문이다.

 

◆ ' % ', ' _ ' 를 이용한 데이터에 대한 필터 조회

이는 필요한 데이터를 얻기 위해 데이터에 대한 직접적인 필터를 넣어 해당하는 조건을 만족하는 데이터를 조회하는 것이다. (직접적인 표현, 필터 표현을 했지만 이는 이해하기 쉽게 하려는 워딩이며 실질적으로 의미 있는 워드는 아니다)

이때 특이하게 LIKE 절이 있으며 두가지 주요 와일드 카드가 있다.

 

1. ' % ' (퍼센트) : 임의의 문자열을 의미하며 글자수에 대한 제한이 없다.

2. ' _ ' (언더스코어) : 임의의 단일 문자를 의미하며 글자수에 맞추어 제한된다.

%on을 사용하므로써 가장 마지막에 on이 들어간 Name을 모두 포함한다.

양 옆&nbsp; %% 도 사용이 가능하며 ti가 들어간 데이터를 모두 골라올 수 있다.

언더 스코어의 경우 %와다르게 글자 수가 동일하며 _해당하는 위치에는 어떤 글자가 와도 가져온다.

 

◆ 조회하는 레코드 수 제한 (LIMIT + OFFSET)

모든 데이터를 조회할 필요 없이 일부 개수만 가져올 수 있다. 예를들어 조회하는 데이터가 100개라면 LIMIT을 통해 출력을 10개, 20개 .. 만 출력할 수 있다. 또, 10개를 가져오되 중간부터 시작해서 10개를 조회할 것이라면 OFFSET 을 통해 일부 정보를 뛰어넘어서 출력할 수 있다.

원래 있던 데이터들을 나눠서 출력했고, 합쳐져있을 때를 확인해보면 제대로 나뉘었다.

 

헷갈리지 않게 그림으로도 살펴보자

OFFSET 는 생략할 수 있는데, 명시하지 않았다면 앞이 OFFSET이므로 유의하자.

---

GROUP BY

: 레코드를 그룹화한다.

 

◆ 일반적인 그룹화

특히 데이터를 조회하는데 중요한 역할을 한다. 다른 키워드 없이 GROUP BY만 사용한다면 SELECT 의 중복제거인 DISTINCT와 동일한 결과를 가져올 수 있다.

그룹화되어 출력된 요소는 CA, MA, WA만 존재함을 알 수 있다.

 

◆ '집계함수 (Aggregation Functions)를 이용한  조회

: 집계 함수와 함께 사용가능하며 해당 값들에 대한 계산을 수행할 수 있다. 물론 SELECT에서도 사용 가능하다.

: 값에 대한 계산을 수행하고 단일한 값을 반환하는 함수이며 주로 아래 5개를 사용한다.

• SUM : 그룹된 요소를 모두 더하여 반환
• AVG : 그룹된 요소의 평균을 반환
• MAX : 그룹된 요소들 중 최댓값을 반환
• MIN : 그룹된 요소들 중 최소값을 반환
• COUNT : 그룹된 요소들의 개수를 반환

COUNT를 사용하여 만들어진 그룹에 해당되는 요소 개수를 카운트 하여 표현할 수 있다.

 

◆ HAVING을 사용한 조건 조회

그러면 평균을 활용하고 싶다면 어떡할까? WHERE 절로 걸러내거나 GROUP BY를 통해 거를 수 있지 않을까???

하지만 내 생각대로 되지않는 일이란.. 슬프다

 

머리속으로는 이해가 되는 과정이지만 SQLite에서는 해당 방식은 허락해주지 않더라. 다른 SQL은 어떨지 모르겠다.

일단 SQLite에서는 해당 조건을 만족하는 데이터들을 그룹화 하려면 어떻게 해야할까? 이 때, HAVING을 사용하여 필터링할 수 있다.

문제없이 출력되는 것을 확인할 수 있다.

 

쓰고보니 극단적인 예시라 AVG를 써서 데이터 조회,출력 의미가 애매하긴한데, GROUP BY와 WHERE에는 사용이 불가하고 HAVING에서는 가능하다는 점이 중요하다.

---

구문 쌓는 순서는 어느정도 숙지 되겠지만, 실행순서에 따라 데이터 조회가 달라질 수 있으니 꼭 인지하면서 조회를 해야한다. 

• FROM : 테이블을 불러온다
• WHERE : 특정 조건을 맞춘다
• GROUP BY : 그룹화 한다
• HAVING : 그룹화 조건을 적용한다
• SELECT : 위의 조건을 만족시킨 데이터를 조회한다
• ORDER BY : 조회된 데이터를 정렬한다
• LIMIT : 특정 위치의 데이터 값을 가져온다

한개의 statement라면 문제 없지만 조금 더 심화되면 중첩 쿼리(Nested Query) 혹은 서브쿼리 (Subquery)와 같은 조회를 해야할 수 있다. 아직 기초라서 다루진 않겠지만, 가능하다는 점만 먼저 알아두자!

 

---

SQL하면 잔뜩 쫄아서 어떻게 데이터를 만지지.. 했는데 생각보다 알고리즘 푸는거 같아서 재미있었다. 마치 조건문 if와 변수 할당과 출력하는 느낌이랄까? 이런거만 하고싶어~~ 물론 조회할 세부 사항이 늘어날 때마다 머리는 터질거 같긴 하더라.. 다음 시간은 데이터 테이블을 직접 생성하는 법을 배워보자!!