SQL - Query

데이터베이스에서 특정 원하는 데이터를 색인하기 위해 신호를 보내는 것을 쿼리(Query)라고한다.

이번 글에서는 SQL에서 사용되는 Query와 관련된 문법에 대해서 알아보자.

이전 글과 이어지는 내용이니, 데이터 준비를 위해 아래 글을 참조하기 바란다.

SQL - MySQL 데이터 준비

 

0. 사용 Schema 지정

USE classicmodels;

• classicmodels 스키마를 사용

 

 

 

 

1. SELECT

SELECT customernumber, phone FROM customers;

• customers 테이블에서 customernumber, phone 컬럼 선택해서 보여줌

 

※ 주의 사항

• 데이터의 개수가 엄청 많은 저장소의 경우
• 아래 코드를 실행하면 등짝 스매쉬 각이다…

SELECT * FROM [ table_name ];

• 테이블 정의서를 보고 테이블의 형태를 파악하고 사용할 자원과 시간을 최소화해야함

 

 

 

 

2. COUNT

SELECT COUNT(checknumber) FROM payments;

행의 개수, 각 칼럼의 값의 개수를 파악할 때 사용

• payments 테이블에서 checknumber 컬럼 카운트 후 선택해서 보여줌

 

 

 

 

3. 특정 컬럼명 할당

SELECT 
	COUNT(productcode) AS n_products
    , COUNT(productcode) n_products
    , COUNT(productcode)
    , COUNT(productcode) AS 개수
FROM 
	products
;

Python의 ‘import 00 as xx’ 처럼 MySQL에서는 원하는 이름으로 데이터 테이블의 컬럼명을 할당할 수 있다.

• productcode의 카운트를 n_products로,
• AS 생략 가능
• 원본 이름 그대로
• 한글도 가능
• products 테이블에서 가져옴

 

 

 

 

4. DISTINCT

중복을 제외하고 데이터를 조회하는 방법

SELECT 
	DISTINCT ordernumber
FROM 
	orderdetails
;

• orderdetails 테이블에서 ordernumber의 중복을 제외한 고유값들만 조회

 

SELECT 
	COUNT(ordernumber) AS 중복포함
    , COUNT(DISTINCT ordernumber) AS 중복제거
FROM 
	orderdetails
;

• 위 코드로 정말로 중복이 제거되는지 비교해볼 수 있다.

 

 

 

 

5. WHERE

SQL 쿼리에서 엄청 자주 쓰이는 중요한 문법

컬럼내 특정 값을 가진 행만 가져올 때 사용하며, 다른 문법과 결합되어 자주 사용된다

1. WHERE + BETWEEN

SELECT *
FROM orderdetails 
WHERE priceeach BETWEEN 30 AND 50;

• orderdetials 테이블에서 전체 컬럼 선택하는데 priceeach의 값이 30과 50사이인 행만 선택해서 보여줌

 

2. WHERE + 대소관계 연산자

SELECT *
FROM payments 
WHERE amount < 6066;

• payments 테이블에서 전체 컬럼 선택하는데 amount의 값이 6066미만인 행만 선택해서 보여줌

 

3. WHERE + IN

OR 연산자처럼 작동, 서브쿼리시에 자주 사용

• 먼저 OR 연산자를 사용했을 때,

SELECT country FROM customers;
SELECT 
	customernumber
    , country 
FROM customers
WHERE country = 'USA' OR country = 'Canada' OR country = 'France'
;

• 코드가 길어지며 가독성이 떨어짐
• WHERE + IN을 사용하여 깔끔하게 작성가능

SELECT 
	customernumber
    , country 
FROM customers
WHERE country IN ('USA', 'Canada', 'France')
;

• customers 테이블에서 customernumber, country 컬럼을 선택하는데, country의 값이 USA 또는 Canada 또는 France인 행만 선택

 

4. WHERE + NOT IN

• WHERE + IN과 달리 해당 값들이 없는 행만 선택해서 보여줌

SELECT 
	customernumber
    , country 
FROM customers
WHERE country NOT IN ('USA', 'Canada', 'France')
;

• customers 테이블에서 customernumber, country 컬럼을 선택하는데, country의 값이 USA 또는 Canada 또는 France이 아닌 행만 선택

 

5. WHERE + IS NULL

• 결측치가 있는 것들을 선택시 사용

SELECT employeenumber
FROM employees
WHERE reportsto IS NULL;

employees 테이블에서 employeenumber 컬럼을 선택하는데, reportsto 컬럼이 결측인 것만 보여줌

 

6. WHERE + IS NOT NULL

• 결측치가 아닌 것들을 선택시 사용

SELECT employeenumber
FROM employees
WHERE reportsto IS NOT NULL;

employees 테이블에서 employeenumber 컬럼을 선택하는데, reportsto 컬럼이 결측이 아닌 것만 보여줌

 

※ 참고

• COUNT()는 결측치를 세지 않음!

SELECT 
	COUNT(employeenumber)
	, COUNT(reportsto) 
FROM employees;

• COUNT를 통해 특정 관심 컬럼만 결측여부 확인 가능

 

7. WHERE + LIKE %TEXT%

• 관심있는 문자나 단어를 찾을 때 유용
• %A 는 A로 끝나는 문자를 의미
• A% 는 A로 시작하는 문자를 의미
• %A% 는 중간에 A가 들어가는 문자를 의미

SELECT 
	addressline1
FROM customers
WHERE addressline1 LIKE '%ST.';

• customers 테이블에서 addressline1 컬럼을 선택하는데, addressline1의 값이 ST.으로 끝나는 것만 선택해서 보여줌

 

 

 

 

6. GROUP BY

SELECT 
	customernumber
    , checknumber
    , SUM(amount)
FROM 
	payments
GROUP BY customernumber, checknumber;

• payments 테이블에서 customernumber, checknumber, SUM(amount) 컬럼을 선택하는데, customernumber, checknumber를 기준으로 테이블을 재구성

 

 

 

 

7. CASE WHEN

• IF 조건문처럼 사용되는 문법, TRUE일 때 THEN 이하를, FALSE일 때 ELSE 이하를 할당하고 END로 끝냄  

SELECT 
	SUM(CASE WHEN country = 'USA' THEN 1 ELSE 0 END) N_USA
    , COUNT(*)
    , SUM(CASE WHEN country = 'USA' THEN 1 ELSE 0 END) / COUNT(*) AS USA_PORTION
FROM 
	customers;

• country 컬럼의 값이 USA인 값만 카운트하여 N_USA 컬럼으로 보여줌
• 전체 컬럼의 개수 카운트하여 보여줌
• 비율을 계산하여 USA_PORTION 컬럼으로 보여줌
• customers 테이블에서 선택

 

 

 

 

8. JOIN

데이터 테이블을 서로 연동하여 데이터를 조회할 때 사용

실제 데이터베이스를 다룰 때는, 데이터 정의서와 ERD그림을 보면서, 어떻게 JOIN할 것인지 계획을 짠다.

• orders 테이블을 A로, customers 테이블을 B로 두고, 겹치는 컬럼명인 customernumber를 기준으로 여러 JOIN방식을 시행해보자
• A, B는 alias이며 임의의 이름 가능

 

1. LEFT JOIN

• A 기준으로 JOIN

SELECT 
	A.ordernumber
    , B.country
FROM orders A
LEFT 
JOIN customers B
ON A.customernumber = B.customernumber;

 

2. RIGHT JOIN

• B 기준으로 JOIN

SELECT 
	A.ordernumber
    , B.country
FROM orders A
RIGHT
JOIN customers B
ON A.customernumber = B.customernumber;

 

3. INNER JOIN

• A,B 교집합으로 JOIN

SELECT 
	A.ordernumber
    , B.country
FROM orders A
INNER 
JOIN customers B
ON A.customernumber = B.customernumber;

 

4. FULL JOIN 

• A,B 합집합으로 JOIN
• 데이터 개수가 많을 때는 비효율적
• MySQL에서는 FULL JOIN을 지원하지 않음!

SELECT 
	*
FROM orders A
FULL 
JOIN customers B
ON A.customernumber = B.customernumber;

• 대신 LEFT JOIN과 RIGHT JOIN을 UNION하여 FULL JOIN을 흉내낼 수 있다.

(SELECT 
	*
FROM orders A
LEFT 
JOIN customers B
ON A.customernumber = B.customernumber
)
UNION
(SELECT 
	*
FROM orders A
RIGHT
JOIN customers B
ON A.customernumber = B.customernumber
);

 

 

 

 

9. CASE WHEN + GROUP BY

• 두개를 응용하여 쿼리를 해보자

SELECT 
	CASE WHEN country IN ('USA', 'Canada') THEN 'North America' ELSE 'OTHERS' END AS region
	, COUNT(customernumber) N_customers
FROM customers
GROUP BY CASE WHEN country IN ('USA', 'Canada') THEN 'North America' ELSE 'OTHERS' END;

• country 컬럼에서 USA 또는 Canada가 있을 경우 North America로, 다른 경우는 OTHERS로 선택하고 조건문 끝냄, 그 결과를 region이라는 컬럼명으로 출력
• customernumber를 카운트하여 N_customers 컬럼명으로 출력
• customers 테이블로부터 선택함
• 선택결과를 첫번째 컬럼 기준으로 정렬

※ GROUP BY 1

• 방금 위 코드에서 GROUP BY 줄을 Query 시의 첫번째 컬럼으로 정렬하라는 의미인 GROUP BY 1으로 표시 가능하다!

SELECT 
	CASE WHEN country IN ('USA', 'Canada') THEN 'North America' ELSE 'OTHERS' END AS region, 
    COUNT(customernumber) N_customers
FROM customers
GROUP BY 1;

• Query문이 길어질 때 정말로 유용하다!

 

 

 

 

10. ROW_NUMBER, RANK, DENSE_RANK

• 데이터의 순위를 매기는데 사용되는 함수
• MySQL 8.0 버전 이후부터 지원
• 세가지 함수 등수를 매기는 것은 동일하나 동점일 때의 처리가 다르다
• ROW_NUMBER: 동점일 경우에도 데이터 순서대로 다른 순위 매김
• RANK: 동점을 같은 등수로 취급, 동점인 데이터의 수만큼 고려해 다음 순위 매김
• DENSE_RANK: 동점을 같은 등수로 취급, 동점인 데이터의 수와 관계없이 동점의 순위 다음 수로 순위 매김

SELECT 
	buyprice
    , ROW_NUMBER() OVER(ORDER BY buyprice) ROWNUMBER
    , RANK() OVER(ORDER BY buyprice) RNK
    , DENSE_RANK() OVER(ORDER BY buyprice) DENSERANK
FROM products;

• buyprice 컬럼 선택
• buyprice 컬럼내의 값들로 정렬한 뒤 ROW_NUMBER 함수 적용 후 ROWNUMBER 컬럼명으로 선택
• buyprice 컬럼내의 값들로 정렬한 뒤 RANK 함수 적용 후 RNK 컬럼명으로 선택
• buyprice 컬럼내의 값들로 정렬한 뒤 DENSE_RANK 함수 적용 후 DENSERANK 컬럼명으로 선택
• products 테이블로부터 선택함

 

 

 

 

11. PARTITION BY

• 컬럼내의 같은 값들로 묶어서 선택하는 방식

SELECT 
	productline
	, buyprice 
    , ROW_NUMBER() OVER(PARTITION BY productline ORDER BY buyprice) ROWNUMBER
    , RANK() OVER(PARTITION BY productline ORDER BY buyprice) RNK
    , DENSE_RANK() OVER(PARTITION BY productline ORDER BY buyprice) DENSERANK
FROM products;

• productline 컬럼 선택
• buyprice 컬럼 선택
• productline 컬럼내의 같은 값들로 묶은 후, buyprice 컬럼내의 값들로 정렬한 뒤 ROW_NUMBER 함수 적용 후 ROWNUMBER 컬럼명으로 선택
• productline 컬럼내의 같은 값들로 묶은 후, buyprice 컬럼내의 값들로 정렬한 뒤 RANK 함수 적용 후 RNK 컬럼명으로 선택
• productline 컬럼내의 같은 값들로 묶은 후, buyprice 컬럼내의 값들로 정렬한 뒤 DENSE_RANK 함수 적용 후 DENSERANK 컬럼명으로 선택
• products 테이블로부터 선택함

 

 

 

 

12. Subquery (서브 쿼리)

• 쿼리문 안에서 중첩해서 쿼리를 실행하는 방식
• 필요한 데이터 범위를 좁혀 들어갈 때 자주 사용

 

1. Subquery FROM

• 서브쿼리 FROM은 하나의 테이블처럼 사용됨
• 컬럼이름과 테이블명을 명시해야함 '(괄호:서브쿼리) 테이블명'

SELECT A.customernumber
FROM (
	SELECT customernumber
    FROM customers
    WHERE city = 'NYC'
) A;

• customernumber 컬럼에서 city가 NYC인 것들만 선택
• customers 테이블로부터 선택함

 

2. Subquery WHERE

SELECT ordernumber
FROM orders
where customerNumber in (
	SELECT customernumber
    FROM customers 
    WHERE city = 'NYC'
);

• customernumber 컬럼에서 city가 NYC인 것들만 ordernumber를 선택
• orders 테이블로부터 선택함