데이터 조회

SELECT

• 데이터 조회

SELECT * FROM Customers;
-- Customers 테이블의 전체 데이터 조회

SELECT CustomerName FROM Customers;
-- Customers 테이블의 CustomerName column 조회

WHERE

• 데이터 필터링 : 원하는 조건의 ROW만 걸러서 보기

SELECT * FROM Orders WHERE EmployeeID = 3;
-- Orders 테이블에서 EmployeeID가 3인 데이터 조회

SELECT * FROM OrderDetails WHERE Quantity < 5; 
-- OrderDetails 테이블에서 Quantity가 5보다 작은 데이터 조회

ORDER BY

• 데이터 정렬 : 특정 COLUMN을 기준으로 데이터를 정렬
  • ASC : 오름차순(Ascending) - default
  • DESC : 내림차순(Descending)

SELECT * FROM Customers ORDER BY ContactName;
-- ContactName column 기준 오름차순으로 정렬

SELECT * FROM OrderDetails
ORDER BY ProductID ASC, Quantity DESC;
-- ProductID column기준으로 오름차순, 
-- ProductID가 같은 데이터끼리는 Quantity column기준 내림차순으로 정렬

LIMIT

• 원하는 개수만큼 데이터 조회
  • LIMIT {건너뛸 개수 (= 0)}, {가져올 개수}

SELECT * FROM Customers LIMIT 10;
-- 앞에 10개 조회

SELECT * FROM Customers LIMIT 30, 10;
-- 40번째 데이터부터 10개 조회

AS

• column 별명 지정

SELECT
  CustomerId AS ID,			-- CustomerId 	-> ID
  CustomerName AS NAME,		-- CustomerName -> NAME
  Address AS ADDR			-- Address 		-> ADDR
FROM Customers;

연산자

기본 연산자

연산자	의미
+ - * / %	더하기, 빼기, 곱하기, 나누기, 나머지
IS	양쪽 모두 TRUE거나 FALSE
IS NOT	한쪽은 TRUE, 다른 쪽은 FALSE
AND, &&	양쪽 모두 TRUE
OR, ||	한 쪽이라도 TRUE
=	양 쪽이 같음
!=, <>	양 쪽이 다름
<, >, <=, >=	부등호

BETWEEN AND

• 두 값 사이에 있음

SELECT * FROM OrderDetails
WHERE ProductID BETWEEN 1 AND 4;
-- ProductID가 1이상 4이하인 데이터 조회

SELECT * FROM Customers
WHERE CustomerName BETWEEN 'b' AND 'c';
-- CustomerName이 B로 시작하는 데이터 조회

IN, NOT IN

• 데이터의 존재유무

SELECT * FROM Customers
WHERE City IN ('Torino', 'Paris', 'Portland', 'Madrid') 
-- City값이 Torino, Paris, Portland, Madrid중에 있는 데이터 조회

LIKE '%', LIKE '_'

• 문자 패턴
  • % : 0~N개 문자
  • _ : _ 개수만큼의 문자

SELECT
  'HELLO' LIKE 'hel%', 		-- TRUE(hel로 시작하는 문자열)
  'HELLO' LIKE 'H%',		-- TRUE(H로 시작하는 문자열)
  'HELLO' LIKE 'H%O',		-- TRUE(H로 시작하고 O로 끝나는 문자열)
  'HELLO' LIKE '%O',		-- TRUE(O로 끝나는 문자열)
  'HELLO' LIKE '%HELLO%',	-- TRUE(HELLO를 포함하는 문자열)
  'HELLO' LIKE '%H',		-- FALSE(H로 끝나는 문자열)
  'HELLO' LIKE 'L%'			-- FALSE(L로 시작하는 문자열)
  
SELECT
  'HELLO' LIKE 'HEL__',		-- TRUE(HEL로 시작하고 뒤에 2글자)
  'HELLO' LIKE 'h___O',		-- TRUE(h로 시작하고 O로 끝, 사이에 3글자)
  'HELLO' LIKE '_____',		-- TRUE(5글자 문자열)
  'HELLO' LIKE '_HELLO',	-- FALSE(HELLO로 끝나고 앞에 1글자)
  'HELLO' LIKE 'HEL_',		-- FALSE(HEL로 시작하고 뒤에 1글자)
  'HELLO' LIKE 'H_O'		-- FALSE(H로 시작하고 O로 끝, 사이에 1글자)

• SQL은 대소문자 구분이 없음

함수

숫자 관련 함수

함수	설명
ROUND	반올림
CEIL	올림
FLOOR	내림
ABS	절댓값
GREATEST	(각 ROW에 대해) 가장 큰 값
LEAST	(각 ROW에 대해) 가장 작은 값
MAX	(각 그룹에서) 가장 큰 값
MIN	(각 그룹에서) 가장 작은 값
COUNT	(각 그룹에서) NULL을 제외한 개수
SUM	(각 그룹에서) 합
AVG	(각 그룹에서) 평균
POW(A,B)	A의 B제곱
SQRT	제곱근
TRUNCATE(N, n)	N을 소숫점 n자리까지 선택

SELECT
  TRUNCATE(1234.5678, 1),	-- 1234.5
  TRUNCATE(1234.5678, 2),	-- 1234.56
  TRUNCATE(1234.5678, 3),	-- 1234.567
  TRUNCATE(1234.5678, -1),	-- 1230
  TRUNCATE(1234.5678, -2),	-- 1200
  TRUNCATE(1234.5678, -3);	-- 1000

• Numeric Functions and Operators

문자열 관련 함수

함수	설명
UCASE, UPPER	모두 대문자로
LCASE, LOWER	모두 소문자로
CONCAT(...)	문자열 이어붙이기
CONCAT_WS(S, ...)	문자열 사이에 S를 넣어서 이어붙이기
SUBSTR, SUBSTRING(str, idx, ofs)	idx부터 ofs만큼 문자열을 자름
LEFT	왼쪽부터 N글자
RIGHT	오른쪽부터 N글자
LENGTH	문자열의 문자 바이트 길이
CHAR_LENGTH, CHARACTER_LENGTH	문자열의 문자 길이
TRIM	양쪽 공백 제거
LTRIM	왼쪽 공백 제거
RTRIM	오른쪽 공백 제거
LPAD(S,N,P)	S가 N글자가 될 때까지 왼쪽에 P를 이어붙임
RPAD(S,N,P)	S가 N글자가 될 때까지 오른쪽에 P를 이어붙임
REPLACE(S,A,B)	S에 있는 A를 B로 치환
INSTR(S,s)	S중 s의 첫번째 위치 반환, 없으면 0
CONVERT	자료형 변환

SELECT CONCAT_WS('-', 2021, 8, 15, 'AM');	-- 2021-8-15-AM

SELECT
  SUBSTR('ABCDEFG', 3),				-- CDEFG
  SUBSTR('ABCDEFG', 3, 2),			-- CD
  SUBSTR('ABCDEFG', -4),			-- DEFG
  SUBSTR('ABCDEFG', -4, 2);			-- DE

SELECT
  LENGTH('안녕하세요'), 				-- 15
  CHAR_LENGTH('안녕하세요'), 		-- 5
  CHARACTER_LENGTH('안녕하세요'); 	-- 5
  
SELECT * FROM Categories
WHERE CategoryName = TRIM(' Beverages ');
-- 검색할 때 앞뒤 공백이 있으면 정확한 값이 나오지 않을 수 있음

SELECT
  LPAD('ABC', 5, '_'),				-- __ABC
  RPAD('ABC', 5, '_');				-- ABC__

SELECT
  INSTR('ABCDE', 'ABC'),			-- 1
  INSTR('ABCDE', 'BCDE'),			-- 2
  INSTR('ABCDE', 'C'),				-- 3
  INSTR('ABCDE', 'DE'),				-- 4
  INSTR('ABCDE', 'F');				-- 0

• MYSQL은 1부터 인덱스를 셈
• String Functions and Operators

시간, 날짜 관련 함수

함수	설명
CURRENT_DATE, CURDATE	현재 날짜
CURRENT_TIME, CURTIME	현재 시간
CURRENT_TIMESTAMP, NOW	현재 시간과 날짜
DATE	문자열을 받아 날짜생성
TIME	문자열을 받아 시간 생성
YEAR	년도
MONTHNAME	월(영문)
MONTH	월(숫자)
WEEKDAY	요일(숫자)
DAYNAME	요일명
DAYOFMONTH, DAY	날짜
HOUR	시
MINUTE	분
SECOND	초
ADDDATE, DATE_ADD	시간, 날짜 더하기
SUBDATE, DATE_SUB	시간, 날짜 빼기
DATEDIFF	두 날짜 사이 일 수
TIMEDIFF	두 시간 사이 시간차
LAST_DAY	그 달의 마지막 날짜

SELECT 
  ADDDATE('2021-06-20', INTERVAL 1 YEAR),				-- 1년 뒤
  ADDDATE('2021-06-20', INTERVAL -2 MONTH),				-- 2달 전
  ADDDATE('2021-06-20', INTERVAL 3 WEEK),				-- 3주 뒤
  ADDDATE('2021-06-20', INTERVAL -4 DAY),				-- 4주 전
  ADDDATE('2021-06-20', INTERVAL -5 MINUTE),			-- 5분 전
  ADDDATE('2021-06-20 13:01:12', INTERVAL 6 SECOND);	-- 6초 뒤

SELECT 
  SUBDATE('2021-06-20', INTERVAL 1 YEAR),				-- 1년 전
  SUBDATE('2021-06-20', INTERVAL -2 MONTH),				-- 2달 뒤
  SUBDATE('2021-06-20', INTERVAL 3 WEEK),				-- 3주 전
  SUBDATE('2021-06-20', INTERVAL -4 DAY),				-- 4주 뒤
  SUBDATE('2021-06-20', INTERVAL -5 MINUTE),			-- 5분 뒤
  SUBDATE('2021-06-20 13:01:12', INTERVAL 6 SECOND);	-- 6초 전
  
SELECT * FROM Orders
WHERE
  ABS(DATEDIFF(OrderDate, '1996-10-10')) < 5;
-- 1996년 10월 10일 전후 5일 이내의 데이터를 조회

DATE_FORMAT

• 시간, 날짜를 지정한 형식으로 변환
• STR_TO_DATE(S, F) : 문자열을 F형식으로 해서 시간, 날짜 생성

형식	설명
%Y	년도 4자리
%y	년도 2자리
%M	월(영문)
%m	월(숫자)
%D	일(영문)
%d, %e	일(숫자)
%T	hh:mm:ss
%r	hh:mm:ss AM/PM
%H, %k	시(~23)
%h, %l	시(~12)
%i	분
%S, %s	초
%p	AM/PM

SELECT
  DATE_FORMAT(NOW(), '%M %D, %Y %T'),					-- January 5th, 2023 13:31:10
  DATE_FORMAT(NOW(), '%y-%m-%d %h:%i:%s %p'),			-- 23-01-05 01:31:10 PM
  DATE_FORMAT(NOW(), '%Y년 %m월 %d일 %p %h시 %i분 %s초');	
  -- 2023년 01월 05일 PM 01시 31분 10초

기타 함수

함수	설명
IF(조건, T, F)	조건이 참이면 T, 거짓이면 F
CASE-WHEN 조건-THEN 실행문-ELSE 실행문-END	WHEN의 조건이 참이면 THEN, 맞는 조건이 없으면 ELSE
IFNULL(A,B)	A가 NULL이면 B출력

SELECT
  Price,
  IF (Price > 30, 'Expensive', 'Cheap'),		-- Price가 30보다 크면 Expensive, 작으면 Cheap
  CASE
    WHEN Price < 20 THEN '저가'					-- Price가 20보다 작으면 저가
    WHEN Price BETWEEN 20 AND 30 THEN '일반'	-- Price가 20 ~ 30사이면 일반
    ELSE '고가'									-- Price가 30보다 크면 고가
  END
FROM Products;

데이터 그룹화

• 그룹함수 : 그룹단위로 실행되는 함수
  • MAX, MIN, COUNT, SUM, AVG

GROUP BY

• 조건에 따라 중복없이 특정 column을 기준으로 데이터를 묶음

SELECT Country FROM Customers
GROUP BY Country;
-- Customers 테이블에 있는 Country의 종류 조회

SELECT 
  Country, City,
  CONCAT_WS(', ', City, Country)
FROM Customers
GROUP BY Country, City;
-- Customers 테이블에 중복되지 않는 (City, Country)쌍 조회

SELECT
  COUNT(*), OrderDate
FROM Orders
GROUP BY OrderDate;
-- 날짜별 행의 개수

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;
-- CategoryID별 Price의 통계치

SELECT 
  CONCAT_WS(', ', City, Country) AS Location,
  COUNT(CustomerID)
FROM Customers
GROUP BY Country, City;
-- (City, Country) 조합 당 CustomerID의 수

WITH ROLLUP

• 전체 column의 총 합
• ORDER BY 와 함께 사용 불가

SELECT
  Country, COUNT(*)
FROM Suppliers
GROUP BY Country
WITH ROLLUP;
-- 마지막 행에 count의 총 합이 추가됨

HAVING

• 그룹 안에서 필터링
• WHERE은 그룹화 전 데이터, HAVING은 그룹화 한 데이터에 대해 적용

SELECT
  COUNT(*) AS Count, OrderDate
FROM Orders
WHERE OrderDate > DATE('1996-12-31')	-- 1996년12월31일 이후 데이터 중
GROUP BY OrderDate						-- 날짜별로 묶었을 때
HAVING Count > 2;						-- Count가 2보다 큰 데이터 조회

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				-- CategoryID가 2보다 큰 데이터 중
GROUP BY CategoryID					-- CategoryID가 같은 것끼리 묶었을 때
HAVING
  AveragePrice BETWEEN 20 AND 30	-- 평균 가격이 20 ~ 30사이이고
  AND MedianPrice < 40;				-- 중간값이 40보다 작은 데이터 조회

DISTINCT

• 중복을 제거하여 조회(SELECT문에 사용)
• 그룹 함수 사용 불가(MAX, MIN, COUNT, SUM, AVG)
• GROUP BY는 자동으로 정렬이 되어 나오지만, DISTINCT는 원본 데이터 순서 그대로 나옴

SELECT DISTINCT CategoryID
FROM Products;
-- CategoryID의 중복을 제거하여 조회

SELECT COUNT DISTINCT CategoryID
FROM Products;
-- 오류 발생(COUNT 사용 불가)

SELECT DISTINCT Country
FROM Customers
ORDER BY Country; 
-- 수동으로 정렬

SELECT DISTINCT Country, City
FROM Customers
ORDER BY Country, City;
-- (Country, City)쌍의 중복을 제거하여 조회

• GROUP BY 와 DISTINCT 를 함께 사용

SELECT
  Country,
  COUNT(DISTINCT CITY)
FROM Customers
GROUP BY Country;
-- Country당 City의 종류를 카운트

참고자료

• 왕초보용! 갖고 노는 MySQL 데이터베이스 강좌