SQL (1) SQL의 기본

SQL이란?

SQL이란 Structured Query Language라고 구조화 질의어라고 하며, 관계형 데이터베이스 관리 시스템(RDBMS)의 데이터를 관리하기 위해 설계된 특수 목적의 프로그래밍 언어이다. 관계형 데이터베이스 관리 시스템에서 자료의 검색과 관리, 데이터베이스 스키마 생성과 수정, 데이터베이스 객체 접근 조정 관리를 위해 고안되었다.

단문을 구성하는 SQL 언어 요소 및 몇 가지를 보여준다.

출처: 위키백과

DB(DATABASE)란?

데이터베이스는 여러 사람이 공유하여 사용할 목적으로 체계화해 통합, 관리하는 데이터의 집합입니다. 작성된 목록으로써 여러 응용 시스템들의 통합된 정보들을 저장하여 운영할 수 있는 공용 데이터들의 묶음이다.

출처: 위키백과

위의 DB에서 특정 테이블 (도서대출내약)을 조회하고 그것을 가지고 올 때 'SELECT * FROM 도서대출내역'이 바로 SQL입니다.

쿼리의 기본 구조

• SELECT ~ : 조회할 컬럼명 선택
• FROM ~ : 조회할 테이블명 지정 (위치와 테이블명 입력)
• WHERE ~ : 질의할 때 필요한 조건을 설정
• GROUP BY ~ : 특정 컬럼을 기준으로 그룹을 지어 출력
• ORDER BY ~ : SELECT 다음에 오는 컬럼 중 정렬이 필요한 부분을 정렬 (기본 설적: 오름차순)
• LIMIT 숫자 : Display하고자 하는 행의 수를 결정

<테이블명: 도서대출내역>

이미지를 보았을 때, 이는 도서관에서 관리하고 있는 DB라는 점을 알 수 있습니다.

• ID: 대출을 한 사람의 ID
• 이름: 대출자의 이름
• 도서 ID: 대출한 도서의 ID
• 대출일: 도서를 대출한 날짜
• 반납일: 도서를 반납한 날짜

5개의 열:컬럼(column)과 가로 줄은 행:로우(row)라고 부릅니다.

[ID 컬럼(column)]

[첫 번째 행(row)]

기본 구조를 테이블을 활용해 검증해 보겠습니다.

import os
db_path = os.getenv('HOME')+'/mydb.db'
conn = sqlite3.connect(db_path)
c = conn.cursor()

for row in c.execute('SELECT * FROM 도서대출내역'):
	print(row)
    
>>>
('101', '문강태', 'aaa', '2020-06-01', '2020-06-05')
('101', '문강태', 'ccc', '2020-06-20', '2020-06-25')
('102', '고문영', 'bbb', '2020-06-01', None)
('102', '고문영', 'ddd', '2020-06-08', None)
('103', '문상태', 'ccc', '2020-06-01', '2020-06-05')
('104', '강기둥', None, None, None)

전체 조회

SELECT * FROM 도서대출내역;

for row in c.execute('SELECT * FROM 도서대출내역'):
	print(row)
    
>>>
('101', '문강태', 'aaa', '2020-06-01', '2020-06-05')
('101', '문강태', 'ccc', '2020-06-20', '2020-06-25')
('102', '고문영', 'bbb', '2020-06-01', None)
('102', '고문영', 'ddd', '2020-06-08', None)
('103', '문상태', 'ccc', '2020-06-01', '2020-06-05')
('104', '강기둥', None, None, None)

SELECT와 FROM 사이에는 특정 컬럼을 넣어 출력하고는 합니다. 위의 쿼리처럼 별(*)을 입력하게 되면 테이블 전체를 가져오라는 명령어가 됩니다.

특정 컬럼 지정

SELECT ID FROM 도서대출내역:

for row in c.execute('SELECT ID FROM 도서대출내역'):
	print(row)
    
>>>
('101',)
('101',)
('102',)
('102',)
('103',)
('104',)

SELECT와 FROM 사이에 ‘ID’를 넣으면 '전체 테이블 중에 ID 컬럼만 가져와라'라는 명령어가 됩니다.

조건 입력

SELECT * FROM 도서대출내역
WHERE 이름 = "문강태";

for row in c.execute('SELECT * FROM 도서대출내역 WHERE 이름 = "문강태";'):
	print(row)
    
>>>
('101', '문강태', 'aaa', '2020-06-01', '2020-06-05')
('101', '문강태', 'ccc', '2020-06-20', '2020-06-25')

SELECT와 FROM 사이에 *이 있으니 전체 컬럼을 모두 가져온다는 뜻이고, WHERE 절이 추가되었습니다. WHERE 절 이후에는 특정 조건을 입력할 수 있습니다. 위에는 '이름 = "문강태"' 라고 적어 놓았는데, 이름이 문강태인 사람을 가져오게 됩니다.

GROUP BY로 중복 제거

SELECT 이름 FROM 도서대출내역
GROUP BY 이름;

for row in c.execute('SELECT 이름 FROM 도서대출내역 GROUP BY 이름;'):
	print(row)
    
('강기둥',)
('고문영',)
('문강태',)
('문상태',)

GROUP BY는 이름 그대로 데이터를 그룹화 시키는 역할을 합니다. GROUP BY는 보통 집계성 함수와 함께 사용됩니다.

DISTINCT로 중복을 제거

SELECT DISTINCT 이름 FROM 도서대출내역;

for row in c.execute('SELECT DISTINCT 이름 FROM 도서대출내역;'):
	print(row)
    
>>>
('문강태',)
('고문영',)
('문상태',)
('강기둥',)

ORDER BY로 정렬

SELECT * FROM 도서대출내역
ORDER BY ID ;

for row in c.execute('SELECT * FROM 도서대출내역 ORDER BY ID ;'):
	print(row)
    
>>>
('101', '문강태', 'aaa', '2020-06-01', '2020-06-05')
('101', '문강태', 'ccc', '2020-06-20', '2020-06-25')
('102', '고문영', 'bbb', '2020-06-01', None)
('102', '고문영', 'ddd', '2020-06-08', None)
('103', '문상태', 'ccc', '2020-06-01', '2020-06-05')
('104', '강기둥', None, None, None)

ORDER BY 뒤에 특정 컬럼명을 적으면, 그 컬럼을 기준 값으로 정렬을 해서 보여줍니다. 위 쿼리문에서 보시면 SELECT와 FROM 사이에 별(*)이 들어 있으므로 '전체 컬럼을 가져오되 ID를 기준으로 정렬해달라'라는 뜻이 됩니다.

일반적으로 ORDER BY 뒤에는 (ASC)가 생략돼 있습니다. 오름차순이 기본적으로 설정되어 있다는 것이지요. 반대는 내림차순으로 DESC라고 씁니다.

SELECT * FROM 도서대출내역
ORDER BY ID DESC ;

for row in c.execute('SELECT * FROM 도서대출내역 ORDER BY ID DESC ;'):
	print(row)
    
>>>
('104', '강기둥', None, None, None)
('103', '문상태', 'ccc', '2020-06-01', '2020-06-05')
('102', '고문영', 'bbb', '2020-06-01', None)
('102', '고문영', 'ddd', '2020-06-08', None)
('101', '문강태', 'aaa', '2020-06-01', '2020-06-05')
('101', '문강태', 'ccc', '2020-06-20', '2020-06-25')

ID를 기준으로 내림차순 정렬이 됩니다.

특정 row 조회

SELECT * FROM 도서대출내역 LIMIT 5;

for row in c.execute('SELECT * FROM 도서대출내역 LIMIT 5 ;'):
	print(row)
    
>>>
('101', '문강태', 'aaa', '2020-06-01', '2020-06-05')
('101', '문강태', 'ccc', '2020-06-20', '2020-06-25')
('102', '고문영', 'bbb', '2020-06-01', None)
('102', '고문영', 'ddd', '2020-06-08', None)
('103', '문상태', 'ccc', '2020-06-01', '2020-06-05')

LIMIT 구문 뒤 숫자를 적으면 그 숫자만큼의 행(row)만 출력됩니다. LIMIT 구문은 주로 처음 테이블들을 조회할 때 그 테이블들의 구조를 파악하고, 어떤 값이 존재하는지 샘플로 파악하고자 할 때 사용됩니다.

SELECT 이름, 대출일, 반납일
FROM 도서대출내역
ORDER BY 대출일 DESC
LIMIT 1;

for row in c.execute('SELECT 이름, 대출일, 반납일 FROM 도서대출내역 ORDER BY 대출일 DESC LIMIT 1;'):
    print(row)
   
>>> ('문강태', '2020-06-20', '2020-06-25')

DISTINCT와 GROUP BY

만약 기준 별로 중복없이 집계를 하고 싶다면, 집계함수와 DISTINCT, GROUP BY를 모두 활용해야 합니다.

Q&A

Q. '도서대출내역' 테이블에서 |이름|대출건수| 를 출력하는 쿼리를 작성해 보세요. 'GROUP BY 이름' 구문을 활용해야 하며, 'COUNT(*) AS 대출건수' 라고 alias를 활용할 수 있습니다.

A. SELECT 이름, COUNT(*) AS 대출건수 FROM 도서대출내역 GROUP BY 이름