관계형 데이터베이스 관리 시스템(RDBMS)는 표를 기준으로 데이터를 저장 및 관리하는 시스템을 의미한다. 역사적으로 오래 사용되어 왔고 많은 곳에서 사용되는, 가장 기본이 되는 데이터베이스 시스템이다. 트랜잭션 취소도 쉬워 안정적이고, 또한 데이터의 구조를 일관적으로 가져갈 수 있다는 장점때문에 널리 이용된다.
SQL이라는 데이터베이스 관리용 언어를 사용하게 되며, RDBMS의 대표적인 제품으로는 MySQL, PostgreSQL, MSSQL 등이 있다. 본 글은 MySQL에서 사용되는 SQL의 문법을 정리한 글이다.
한편 일반적으로 SQL은 대소문자는 구분하지 않으나, 함수, 연산자 등은 일관되게 작성하는 것이 좋다. 예를 들어 SELECT * FROM Customers와 같이 연산자, 함수 등은 대문자로만 쓰는 형식이다.
Select - 원하는 정보 가져오기
1. Select 전반적인 기능
1. 테이블의 모든 정보 보기
*을 이용한다.
SELECT * FROM Customers;
CustomerID
CustomerName
ContactName
Address
City
PostalCode
Country
1
Alfreds Futterkiste
Maria Anders
Obere Str. 57
Berlin
12209
Germany
2
Ana Trujillo Emparedados y helados
Ana Trujillo
Avda. de la Constitución 2222
México D.F.
05021
Mexico
3
Antonio Moreno Taquería
Antonio Moreno
Mataderos 2312
México D.F.
05023
Mexico
4
Around the Horn
Thomas Hardy
120 Hanover Sq.
London
WA1 1DP
UK
5
Berglunds snabbköp
Christina Berglund
Berguvsvägen 8
Luleå
S-958 22
Sweden
2. 원하는 컬럼(열) 정보 갖고 오기
한편, 일반적으로는 *을 이용해서 모든 정보를 갖고 오는 것은 좋지 않은 습관이다. 이보다는 주로 원하는 컬럼만을 명시적으로 갖고 오는 방식이 더 선호된다.
SELECT CustomerID, CustomerName, ContactName FROM Customers;
CustomerID
CustomerName
ContactName
1
Alfreds Futterkiste
Maria Anders
2
Ana Trujillo Emparedados y helados
Ana Trujillo
3
Antonio Moreno Taquería
Antonio Moreno
4
Around the Horn
Thomas Hardy
5
Berglunds snabbköp
Christina Berglund
3. 원하는 조건의 행 갖고 오기
WHERE 문을 통해서 조건절을 추가할 수 있다.
SELECT CustomerId, CustomerName, ContactName, Country
FROM Customers
WHERE Country = 'Mexico' OR Country = 'UK'
CustomerId
CustomerName
ContactName
Country
2
Ana Trujillo Emparedados y helados
Ana Trujillo
Mexico
3
Antonio Moreno Taquería
Antonio Moreno
Mexico
4
Around the Horn
Thomas Hardy
UK
11
B's Beverages
Victoria Ashworth
UK
13
Centro comercial Moctezuma
Francisco Chang
Mexico
4. 원하는 순서대로 데이터 정렬하기
ORDER BY를 이용하여 정렬을 수행할 수 있다. 이때 기본값은 오름차순(ASC)이고, 내림차순으로 정렬하려면 DESC을 사용해야 한다.
구분
기준
기본
ASC
오름차순
O
DESC
내림차순
SELECT * FROM Products ORDER BY Price ASC
ProductID
ProductName
SupplierID
CategoryID
Unit
Price
33
Geitost
15
4
500 g
2.50
24
Guaraná Fantástica
10
1
12 - 355 ml cans
4.50
13
Konbu
6
8
2 kg box
6.00
52
Filo Mix
24
5
16 - 2 kg boxes
7.00
54
Tourtière
25
6
16 pies
7.45
SELECT * FROM Products ORDER BY Price DESC
ProductID
ProductName
SupplierID
CategoryID
Unit
Price
38
Côte de Blaye
18
1
12 - 75 cl bottles
263.50
29
Thüringer Rostbratwurst
12
6
50 bags x 30 sausgs.
123.79
9
Mishi Kobe Niku
4
6
18 - 500 g pkgs.
97.00
20
Sir Rodney's Marmalade
8
3
30 gift boxes
81.00
조합도 가능함
SELECT * FROM OrderDetails
ORDER BY ProductID ASC, Quantity DESC;
-- OrderDetail에서 우선 ProductID를 기준으로 오름차순 정렬 후
-- 같은 ProductID에 대해서는 Quantity를 기준으로 내림차순 정렬
OrderDetailID
OrderID
ProductID
Quantity
1570
10847
1
80
1741
10918
1
60
1271
10729
1
50
100
10285
1
45
2022
11031
1
45
5. 원하는 행 개수만 가져오기 (Pagination에 활용)
LIMIT을 통해서 원하는 행 개수만 가져올 수 있다.
SELECT ProductID, ProductName, Price FROM Products
LIMIT 3
-- 최대 3개까지만 갖고 오기
ProductID
ProductName
Price
1
Chais
18.00
2
Chang
19.00
3
Aniseed Syrup
10.00
SELECT ProductID, ProductName, Price FROM Products
LIMIT 3, 3
-- 3 초과부터 3개 더 갖고 오기
ProductID
ProductName
Price
4
Chef Anton's Cajun Seasoning
22.00
5
Chef Anton's Gumbo Mix
21.35
6
Grandma's Boysenberry Spread
25.00
보통 이 방식은 페이지네이션에서 자주 활용된다. 예를 들어서, 어떤 인터넷 포스트들을 보는데, 포스트가 1000개가 있다면 이를 한 번에 불러오는 것은 매우 비효율적이다. 1페이지에 50개, 2페이지에 50개, ... 이런 식으로 페이지를 나누는 게 훨씬 현명한 방법일 것이다. 이때 페이지네이션을 구현하기 위해 제한된 개수의 데이터만을 불러오는 LIMIT을 사용한다.
6. 원하는 컬럼 명으로 갖고 오기
가독성을 위해서 사용한다.
SELECT CustomerID AS Uid,
CustomerName AS Name,
Address AS Addr
FROM Customers
LIMIT 5
Uid
Name
Addr
1
Alfreds Futterkiste
Obere Str. 57
2
Ana Trujillo Emparedados y helados
Avda. de la Constitución 2222
3
Antonio Moreno Taquería
Mataderos 2312
4
Around the Horn
120 Hanover Sq.
5
Berglunds snabbköp
Berguvsvägen 8
한국어는 ‘’ 표로 문자열로 만들면 된다.
SELECT CustomerID AS Uid,
CustomerName AS '고객명',
Address AS '주소'
FROM Customers
LIMIT 5
Uid
고객명
주소
1
Alfreds Futterkiste
Obere Str. 57
2
Ana Trujillo Emparedados y helados
Avda. de la Constitución 2222
3
Antonio Moreno Taquería
Mataderos 2312
4
Around the Horn
120 Hanover Sq.
5
Berglunds snabbköp
Berguvsvägen 8
2. 각종 연산자들
연산자
의미
+, -, *, /
각각 더하기, 빼기, 곱하기, 나누기
%, MOD
나머지
IS
양쪽이 모두 TRUE 또는 FALSE
IS NOT
한쪽은 TRUE, 한쪽은 FALSE
AND, &&
양쪽이 모두 TRUE일 때만 TRUE
OR,
=
양쪽 값이 같음
!=, <>
양쪽 값이 다름
>, <
(왼쪽, 오른쪽) 값이 더 큼
>=, <=
(왼쪽, 오른쪽) 값이 같거나 더 큼
BETWEEN {MIN} AND {MAX}
두 값 사이에 있음
NOT BETWEEN {MIN} AND {MAX}
두 값 사이가 아닌 곳에 있음
IN (...)
괄호 안의 값들 가운데 있음
NOT IN (...)
괄호 안의 값들 가운데 없음
LIKE '... % ...'
0~N개 문자를 가진 패턴
LIKE '... _ ...'
_ 갯수만큼의 문자를 가진 패턴
LIKE는 문자열 속 포함 관계 혹은 패턴 검색을 위해 자주 사용한다.
SELECT * FROM Customers WHERE CustomerName LIKE 'b%'
-- b로 시작하는 이름의 Customer 갖고 오기
SELECT * FROM Customers WHERE CustomerName BETWEEN 'b' AND 'c'
-- 위와 같은 의미임
SELECT * FROM OrderDetails
WHERE ABS(Quantity - 10) < 5;
OrderDetailID
OrderID
ProductID
Quantity
1
10248
11
12
2
10248
42
10
4
10249
14
9
6
10250
41
10
함수
설명
GREATEST
(괄호 안에서) 가장 큰 값
LEAST
(괄호 안에서) 가장 작은 값
**MAX, MIN과 헷갈리지 말기** GREATEST, LEAST는 괄호(리스트) 안에서 가장 큰, 작은 값을 찾는 함수이고, MAX, MIN은 컬럼 중에서 max, min을 찾는 함수
SELECT
OrderDetailID, ProductID, Quantity,
GREATEST(OrderDetailID, ProductID, Quantity) AS Greatest,
LEAST(OrderDetailID, ProductID, Quantity) AS Least
FROM OrderDetails;
OrderDetailID
ProductID
Quantity
Greatest
Least
1
11
12
12
1
2
42
10
42
2
3
72
5
72
3
4
14
9
14
4
함수
설명
MAX
가장 큰 값
MIN
가장 작은 값
COUNT
갯수 (NULL값 제외)
SUM
총합
AVG
평균 값
SELECT
MAX(Quantity),
MIN(Quantity),
COUNT(Quantity),
SUM(Quantity),
AVG(Quantity)
FROM OrderDetails
WHERE OrderDetailID BETWEEN 20 AND 30;
SELECT REPLACE(
REPLACE(
DATE_FORMAT(NOW(), '%Y년 %m월 %d일 %p %h시 %i분 %초'),
'AM', '오전'
),
'PM', '오후'
)
-- 2023년 07월 04일 오전 05시 00분 초
-- 'AM'을 먼저 오전으로 바꾼 후, PM을 오후로 바꾼다.
-- 어차피 AM, PM 둘 중 하나만 존재하므로 이런 식으로 SQL을 짤 수 있다
함수
설명
STR _ TO _ DATE(S, F)
S를 F형식으로 해석하여 시간/날짜 생성
SELECT
DATEDIFF(
STR_TO_DATE('2021-06-04 07:48:52', '%Y-%m-%d %T'),
STR_TO_DATE('2021-06-01 12:30:05', '%Y-%m-%d %T')
), -- 3, DATE니깐 3일 차이남
TIMEDIFF(
STR_TO_DATE('2021-06-04 07:48:52', '%Y-%m-%d %T'),
STR_TO_DATE('2021-06-01 12:30:05', '%Y-%m-%d %T')
); -- 67:18:47, TIME 시간 차이는 이만큼 차이남
SELECT
OrderDate,
DATEDIFF(
STR_TO_DATE('1997-01-01 13:24:35', '%Y-%m-%d %T'),
OrderDate
) AS DateDiff,
TIMEDIFF(
STR_TO_DATE('1997-01-01 13:24:35', '%Y-%m-%d %T'),
STR_TO_DATE(CONCAT(OrderDate, ' ', '00:00:00'), '%Y-%m-%d %T')
) AS TimeDiff
FROM Orders;
OrderDate
DateDiff
TimeDiff
1996-07-04
181
838:59:59
1996-07-05
180
838:59:59
IF/IFNULL
형식
설명
IF(조건, T, F)
조건이 참이라면 T, 거짓이면 F 반환
IFNULL(A, B)
A가 NULL일 시 B 출력
보다 복잡한 조건문은 CASE를 사용한다
SELECT
Price,
IF (Price > 30, 'Expensive', 'Cheap') AS ExpensiveOrCheap,
CASE
WHEN Price < 20 THEN '저가'
WHEN Price BETWEEN 20 AND 30 THEN '일반'
ELSE '고가'
END AS '가격 구분'
FROM Products;
Price
ExpensiveOrCheap
가격 구분
18.00
Cheap
저가
19.00
Cheap
저가
10.00
Cheap
저가
22.00
Cheap
일반
5. 조건에 따라 그룹으로 묶기
Lesson 5. 조건에 따라 그룹으로 묶기
그룹 함수
함수
설명
MAX
가장 큰 값
MIN
가장 작은 값
COUNT
갯수 (NULL값 제외)
SUM
총합
AVG
평균 값
이 그룹 함수들은 GROUP BY 라는 키워드와 함께 자주 사용된다
GROUP BY 는 조건에 따라 집계된 값을 가져온다.
SELECT Country FROM Customers
GROUP BY Country;
21개 국가 명단 →
Country
Argentina
Austria
Belgium
Brazil
SELECT
Country, City,
CONCAT_WS(', ', City, Country)
FROM Customers
GROUP BY Country, City;
두 개의 컬럼을 기준으로 그룹도 가능함
Country
City
CONCAT_WS(', ', City, Country)
Argentina
Buenos Aires
Buenos Aires, Argentina
Austria
Graz
Graz, Austria
Austria
Salzburg
Salzburg, Austria
그룹 함수 활용하기
SELECT
COUNT(*), OrderDate
FROM Orders
GROUP BY OrderDate;
OrderDate로 그룹화해서, 각 OrderDate에 몇 개의 Order가 들어가있는지 집계하기
COUNT(*)
OrderDate
1
1996-07-04
1
1996-07-05
2
1996-07-08
1
1996-07-09
SELECT
ProductID,
SUM(Quantity) AS QuantitySum
FROM OrderDetails
GROUP BY ProductID
ORDER BY QuantitySum DESC;
각 ProductID 별로 몇 개의 Quantity가 있는지 SUM으로 집계 후, QuantitySum으로 내림차순 정렬
ProductID
QuantitySum
60
1577
59
1496
31
1397
SELECT
CategoryID,
MAX(Price) AS MaxPrice,
MIN(Price) AS MinPrice,
TRUNCATE((MAX(Price) + MIN(Price)) / 2, 2) AS MedianPrice,
TRUNCATE(AVG(Price), 2) AS AveragePrice
FROM Products
GROUP BY CategoryID
WITH ROLLUP -- 맨 밑에 집계용
-- 각 컬럼에 대해 전체 행 중 Max, Min, Median, Avg 값을 구한 것을 확인할 수 있음
각 CategoryID별로 그룹화해서, 각 카테고리 아이디별 Max, Min, Med, Avg 값을 구함
CategoryID
MaxPrice
MinPrice
MedianPrice
AveragePrice
1
263.50
4.50
134.00
37.97
2
43.90
10.00
26.95
23.06
3
81.00
9.20
45.10
25.16
4
55.00
2.50
28.75
28.73
5
38.00
7.00
22.50
20.25
6
123.79
7.45
65.62
54.00
7
53.00
10.00
31.50
32.37
8
62.50
6.00
34.25
20.68
263.50
2.50
133.00
28.86
SELECT
CONCAT_WS(', ', City, Country) AS Location,
COUNT(CustomerID)
FROM Customers
GROUP BY Country, City
WITH ROLLUP;
Location
COUNT(CustomerID)
Buenos Aires, Argentina
3
Graz, Austria
1
Salzburg, Austria
1
…
…
91
Country, City로 그룹화해서, 각 Location 당 몇 명의 Customer가 있는지 집계
HAVING 으로 그룹화된 데이터 걸러내기
SELECT
Country, COUNT(*) AS Count
FROM Suppliers
GROUP BY Country
HAVING Count >= 3;
-- Supplier 중에서 Count가 3 이상인 Country만 표시
Country
Count
France
3
Germany
3
USA
4
SELECT
COUNT(*) AS Count, OrderDate
FROM Orders
WHERE OrderDate > DATE('1996-12-31')
GROUP BY OrderDate
HAVING Count > 2;
-- 1996년 12월 31일 이후의 Order 중에서, Order 개수(Count)가 2 이상인 날짜만 Select
Count
OrderDate
3
1997-12-16
3
1997-12-18
3
1997-12-22
SELECT
CategoryID,
MAX(Price) AS MaxPrice,
MIN(Price) AS MinPrice,
TRUNCATE((MAX(Price) + MIN(Price)) / 2, 2) AS MedianPrice,
TRUNCATE(AVG(Price), 2) AS AveragePrice
FROM Products
WHERE CategoryID > 2
GROUP BY CategoryID
HAVING
AveragePrice BETWEEN 20 AND 30
AND MedianPrice < 40;
-- CategoryID가 2 이상인 Product로부터 Avg가 20부터 30 사이, Med가 40 이하인 Product만 Select
CategoryID
MaxPrice
MinPrice
MedianPrice
AveragePrice
4
55.00
2.50
28.75
28.73
5
38.00
7.00
22.50
20.25
8
62.50
6.00
34.25
20.68
DISTINCT: 중복된 값들 제거
SELECT DISTINCT CategoryID
FROM Products;
-- 위의 GROUP BY를 사용한 쿼리와 결과 비교 시
-- 정렬되지 않았음을 확인할 수 있다.
-- SELECT CategoryID FROM Products GROUP BY CategoryID -- 정렬됨
CategoryID
1
2
7
DISTINCT는 집계 함수와 사용 불가능하다. 즉 아래 문장은 오류가 발생한다. (단, ORDER BY 는 사용 가능하다)
SELECT COUNT DISTINCT CategoryID
FROM Products;
-- 오류 발생
SELECT DISTINCT Country, City
FROM Customers
ORDER BY Country, City;
Country
City
Argentina
Buenos Aires
Austria
Graz
Austria
Salzburg
DISTINCT 와 GROUP BY의 사용:
SELECT
Country,
COUNT(DISTINCT CITY)
FROM Customers
GROUP BY Country
WITH ROLLUP;
-- 각 Customer의 Country 중에서, 중복되지 않은 City의 수를 센다
