Chapter 1. SELECT 기초
🧐 3. 숫자와 문자열을 다루는 함수들
- 숫자관련
SELECT
ROUND(0.5),
CEIL(0.4),
FLOOR(0.6);
ROUND는 반올림이므로 1출력
CEIL는 올림이므로 1출력
FLOOR는 내림이므로 0출력
SELECT
Price,
ROUND(price),
CEIL(price),
FLOOR(price)
FROM Products;
Products테이블에서 금액, 반올림한 금액, 올림 금액, 내린 금액을 가져오기
SELECT ABS(1), ABS(-1), ABS(3 - 10);
ABS는 절대값을 내보내므로, 1, 1, 7을 출력한다.
SELECT * FROM OrderDetails
WHERE ABS(Quantity - 10) < 5;
OrderDetails테이블에서 6에서 14 사이의 Quantity를 가진 데이터를 가져온다.
SELECT
GREATEST(1, 2, 3),
LEAST(1, 2, 3, 4, 5);
GREATEST는 가장 큰 값인 3을 리턴,
LEAST는 가장 작은 값인 1을 리턴
주의할점은 괄호 안에서 제일 큰 값과 제일 작은 값을 출력한다. (max, min과 다름)
SELECT
OrderDetailID, ProductID, Quantity,
GREATEST(OrderDetailID, ProductID, Quantity),
LEAST(OrderDetailID, ProductID, Quantity)
FROM OrderDetails;
OrderDetails테이블에서 OrderDetailID, ProductID, Quantity 를 출력하고
그 값중 가장 큰 값과 가장 작은 값을 추가로 출력한다.
SELECT
MAX(Quantity),
MIN(Quantity),
COUNT(Quantity),
SUM(Quantity),
AVG(Quantity)
FROM OrderDetails
WHERE OrderDetailID BETWEEN 20 AND 30;
MAX는 컬럼 중에서 가장 큰 값
MIN은 컬럼 중에서 가장 작은 값
COUNT는 갯수
SUM은 총합
AVG는 평균값이다.
SELECT
POW(2, 3),
POWER(5, 2),
SQRT(16);
POW, POWER는 제곱이라서 8, 25를 출력
SQRT는 제곱근이라 4를 출력
SELECT Price, POW(Price, 1/2)
FROM Products
WHERE SQRT(Price) < 4;
Products테이블에서 Price와
POW(Price, 1/2) = SQRT(Price)
결국 4보다 작은 가격의 제곱근
SELECT
TRUNCATE(1234.5678, 1),
TRUNCATE(1234.5678, 2),
TRUNCATE(1234.5678, 3),
TRUNCATE(1234.5678, -1),
TRUNCATE(1234.5678, -2),
TRUNCATE(1234.5678, -3);
TRUNCATE는 두번째 파라미터에 양수가 오면 소수점자리를 해당 숫자만큼 놔두고 나머지는 자른다.
음수가 오면 소수점 앞으로 0을 붙인다.
자세한 예시는 아래 캡쳐로 확인할 수 있다.
SELECT Price FROM Products
WHERE TRUNCATE(Price, 0) = 12;
Products테이블에서 Price를 가져오는데 12.XX인 Price 데이터만 가져온다.
더 많은 숫자 함수 : mysql 공식문서
- 문자관련
SELECT
UPPER('abcDEF'),
LOWER('abcDEF');
대문자로
소문자로
SELECT
UCASE(CustomerName),
LCASE(ContactName)
FROM Customers;
Customers테이블에서 CustomerName은 대문자로, ContactName은 소문자로 가져오기
SELECT CONCAT('HELLO', ' ', 'THIS IS ', 2021)
괄호 안의 내용 이어붙임
HELLO THIS IS 2021 출력
SELECT CONCAT_WS('-', 2021, 8, 15, 'AM')
CONCAT_WS(S, ...) 괄호 안의 내용 S로 이어붙임
2021-8-15-AM 출력
SELECT CONCAT('O-ID: ', OrderID) FROM Orders;
결과값 앞에 O-ID: 를 붙여서 가져올 수 있다.
SELECT
CONCAT_WS(' ', FirstName, LastName) AS FullName
FROM Employees;
Employees테이블에서 FullName으로 컬럼명을 변경하고, FristName LastName 형식으로 값을 가져오기
SELECT
SUBSTR('ABCDEFG', 3),
SUBSTR('ABCDEFG', 3, 2),
SUBSTR('ABCDEFG', -4),
SUBSTR('ABCDEFG', -4, 2);
각각 CDEFG CD DEFG DE 를 출력한다.
mysql에서는 0이아닌 1부터 인덱스를 시작한다.
SELECT
LEFT('ABCDEFG', 3),
RIGHT('ABCDEFG', 3);
ABC EFG를 각각 출력한다.
SELECT
OrderDate,
LEFT(OrderDate, 4) AS Year,
SUBSTR(OrderDate, 6, 2) AS Month,
RIGHT(OrderDate, 2) AS Day
FROM Orders;
Orders테이블에서 OrderDate, LEFT로 왼쪽부터 4글자까지 자른 걸 Year, 중간을 Month, 오른쪽에서 2글자 자른걸 Day로 가져오기
SELECT
LENGTH('ABCDE'),
CHAR_LENGTH('ABCDE'),
CHARACTER_LENGTH('ABCDE');
LENGTH 문자열의 바이트 길이
CHAR_LENGTH, CHARACTER_LEGNTH 문자열의 문자 길이
각 5를 출력한다.
-- w3wchool 사이트에서는 한글이 제대로 동작하지 않습니다.
SELECT
LENGTH('안녕하세요'), -- 15
CHAR_LENGTH('안녕하세요'), -- 5
CHARACTER_LENGTH('안녕하세요'); -- 5
mysql에서 우리가 원하는 글자 수를 얻고 싶을 때, CHARACTER_LENGTH를 쓴다고 기억해두자
SELECT
CONCAT('|', ' HELLO ', '|'),
CONCAT('|', LTRIM(' HELLO '), '|'),
CONCAT('|', RTRIM(' HELLO '), '|'),
CONCAT('|', TRIM(' HELLO '), '|');
공백없애기
각각 | HELLO | |HELLO | | HELLO| |HELLO|를 출력한다.
SELECT * FROM Categories
WHERE CategoryName = ' Beverages '
공백때문에 결과로 나오는 게 없다.
SELECT * FROM Categories
WHERE CategoryName = TRIM(' Beverages ')
검색한 사람이 본인도 모르게 띄어쓰기를 할 수도 있으니
TRIM을 쓰면 해당 실수가 결과에 영향을 끼치지 않는다.
SELECT
LPAD('ABC', 5, '-'),
RPAD('ABC', 5, '-');
LPAD(S, N, P) S가 N글자가 될 때까지 P를 이어붙임
RPAD(S, N, P) S가 N글자가 될 때까지 P를 이어붙임
각각 --ABC, ABC--를 출력한다.
SELECT
LPAD(SupplierID, 5, 0),
RPAD(Price, 6, 0)
FROM Products;
Products테이블에서 SupplierID는 값 왼쪽에 0을 5자릿수가 되도록 붙인다.
Price는 자릿수가 6글자가 되도록 오른쪽에 0을 붙인다.
SELECT
REPLACE('맥도날드에서 맥도날드 햄버거를 먹었다.', '맥도날드', '버거킹');
REPLACE(S, A, B) S중 A를 B로 변경
버거킹에서 버거킹 햄버거를 먹었다. 출력
SELECT
REPLACE(Description, ', ', ' and ')
FROM Categories;
Categories테이블에서 Description에 카테고리들이 쉼표(,)로 구분되어 있는데 이걸 and로 변경해서 가져오기
SELECT
INSTR('ABCDE', 'ABC'),
INSTR('ABCDE', 'BCDE'),
INSTR('ABCDE', 'C'),
INSTR('ABCDE', 'DE'),
INSTR('ABCDE', 'F');
INSTR(S, s) S중 s의 첫 위치 반환, 없을 시 0
1, 2, 3, 4, 0이 출력된다.
SELECT * FROM Customers
WHERE INSTR(CustomerName, ' ') BETWEEN 1 AND 6;
-- < 6으로 하면?
Customers테이블에서 CustomerName중 1~6자리 내에 공백이 있는 경우 가져오기
< 6 하면 공백이 없는 경우도 가져온다.
SELECT
'01' = '1',
CONVERT('01', DECIMAL) = CONVERT('1', DECIMAL);
CAST(A, T) A를 T 자료형으로 변환
01 글자와 1 글자는 다르기 때문에 거짓(0)을 출력
이 글자들을 숫자자료형 중 하나인 DECIMAL로 변경해서 비교하면 참(1)로 출력한다.
더 많은 문자 함수 : mysql 공식문서
🧐 4. 시간/날짜 관련 및 기타 함수들
- 시간/날짜 관련 함수
SELECT CURDATE(), CURTIME(), NOW();
CURRENT_DATE, CURDATE 현재 날짜 반환
CURRENT_TIME, CURTIME 현재 시간 반환
CURRENT_TIMESTAMP, NOW 현재 시간과 날짜 반환
2021-09-21, 02:57:10, 2021-09-21 02:57:10 출력
SELECT
'2021-6-1' = '2021-06-01',
DATE('2021-6-1') = DATE('2021-06-01'),
'1:2:3' = '01:02:03',
TIME('1:2:3') = TIME('01:02:03');
DATE 문자열에 따라 날짜 생성
TIME 문자열에 따라 시간 생성
'2021-6-1' = '2021-06-01' 문자열이 같지 않아서 거짓(0) 출력
DATE('2021-6-1') = DATE('2021-06-01') 날짜를 비교하니까 참(1) 출력
'1:2:3' = '01:02:03' 문자열이 같지 않아서 거짓(0) 출력
TIME('1:2:3') = TIME('01:02:03') 시간을 비교하니까 참(1) 출력
SELECT
'2021-6-1 1:2:3' = '2021-06-01 01:02:03',
DATE('2021-6-1 1:2:3') = DATE('2021-06-01 01:02:03'),
TIME('2021-6-1 1:2:3') = TIME('2021-06-01 01:02:03'),
DATE('2021-6-1 1:2:3') = TIME('2021-06-01 01:02:03'),
DATE('2021-6-1') = DATE('2021-06-01 01:02:03'),
TIME('2021-6-1 1:2:3') = TIME('01:02:03');
문자열끼리 다르니 0
DATE끼리 비교하니 1
TIME끼리 비교하니 1
DATE와 TIME를 비교하니 0
DATE끼리 비교하니 1
TIME끼리 비교하니 1
SELECT * FROM Orders
WHERE
OrderDate BETWEEN DATE('1997-1-1') AND DATE('1997-1-31');
Orders테이블에서 OrderDate이 1997년 1월인 데이터만 가져오기
SELECT
OrderDate,
YEAR(OrderDate) AS YEAR,
MONTHNAME(OrderDate) AS MONTHNAME,
MONTH(OrderDate) AS MONTH,
WEEKDAY(OrderDate) AS WEEKDAY,
DAYNAME(OrderDate) AS DAYNAME,
DAY(OrderDate) AS DAY
FROM Orders;
YEAR 주어진 DATETIME값의 년도 반환
MONTHNAME 주어진 DATETIME값의 월(영문) 반환
MONTH 주어진 DATETIME값의 월 반환
WEEKDAY 주어진 DATETIME값의 요일값 반환(월요일: 0)
DAYNAME 주어진 DATETIME값의 요일명 반환
DAYOFMONTH, DAY 주어진 DATETIME값의 날짜(일) 반환
SELECT
OrderDate,
CONCAT(
CONCAT_WS(
'/',
YEAR(OrderDate), MONTH(OrderDate), DAY(OrderDate)
),
' ',
UPPER(LEFT(DAYNAME(OrderDate), 3))
)
FROM Orders;
이 함수들을 활용해서 원하는 데이터형식으로 출력할 수 있다.
SELECT * FROM Orders
WHERE WEEKDAY(OrderDate) = 0;
월요일인 데이터만 가져오기
SELECT
HOUR(NOW()), MINUTE(NOW()), SECOND(NOW());
HOUR 주어진 DATETIME의 시 반환
MINUTE 주어진 DATETIME의 분 반환
SECOND 주어진 DATETIME의 초 반환
3, 47, 6 출력
SELECT
ADDDATE('2021-06-20', INTERVAL 1 YEAR),
ADDDATE('2021-06-20', INTERVAL -2 MONTH),
ADDDATE('2021-06-20', INTERVAL 3 WEEK),
ADDDATE('2021-06-20', INTERVAL -4 DAY),
ADDDATE('2021-06-20', INTERVAL -5 MINUTE),
ADDDATE('2021-06-20 13:01:12', INTERVAL 6 SECOND);
ADDDATE, DATE_ADD 시간/날짜 더하기
SUBDATE, DATE_SUB 시간/날짜 빼기
SELECT
OrderDate,
ADDDATE(OrderDate, INTERVAL 1 YEAR),
ADDDATE(OrderDate, INTERVAL -2 MONTH),
ADDDATE(OrderDate, INTERVAL 3 WEEK),
ADDDATE(OrderDate, INTERVAL -4 DAY),
ADDDATE(OrderDate, INTERVAL -5 MINUTE)
FROM Orders;
Orders테이블에서 날짜데이터 가공해서 가져오기
SELECT
OrderDate,
NOW(),
DATEDIFF(OrderDate, NOW())
FROM Orders;
DATE_DIFF 두 시간/날짜 간 일수차
TIME_DIFF 두 시간/날짜 간 시간차
SELECT
TIMEDIFF('2021-06-21 15:20:35', '2021-06-21 16:34:41');
-01:14:06 출력
SELECT * FROM Orders
WHERE
ABS(DATEDIFF(OrderDate, '1996-10-10')) < 5;
Orders테이블에서 OrderDate가 1996-10-06 ~ 1996-10-14인 데이터 가져오기
SELECT
OrderDate,
LAST_DAY(OrderDate),
DAY(LAST_DAY(OrderDate)),
DATEDIFF(LAST_DAY(OrderDate), OrderDate)
FROM Orders;
해당 달의 마지막 날짜
SELECT
DATE_FORMAT(NOW(), '%M %D, %Y %T'),
DATE_FORMAT(NOW(), '%y-%m-%d %h:%i:%s %p'),
DATE_FORMAT(NOW(), '%Y년 %m월 %d일 %p %h시 %i분 %s초');
DATE_FORMAT 시간/날짜를 지정한 형식으로 반환
형식 설명
%Y 년도 4자리
%y 년도 2자리
%M 월 영문
%m 월 숫자
%D 일 영문(1st, 2nd, 3rd...)
%d, %e 일 숫자 (01 ~ 31)
%T hh:mm:ss
%r hh:mm:ss AM/PM
%H, %k 시 (~23)
%h, %l 시 (~12)
%i 분
%S, %s 초
%p AM/PM
SELECT REPLACE(
REPLACE(
DATE_FORMAT(NOW(), '%Y년 %m월 %d일 %p %h시 %i분 %s초'),
'AM', '오전'
),
'PM', '오후'
)
AM이 나오면 오전으로 변경
PM이 나오면 오후로 변경해서 시간을 출력한다.
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')
),
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')
);
STR _ TO _ DATE(S, F) S를 F형식으로 해석하여 시간/날짜 생성
SELECT
OrderDate,
DATEDIFF(
STR_TO_DATE('1997-01-01 13:24:35', '%Y-%m-%d %T'),
OrderDate
),
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')
)
FROM Orders;
더 많은 시간/날짜 함수 : mysql 공식문서
- 기타 함수
SELECT IF (1 > 2, '1는 2보다 크다.', '1은 2보다 작다.');
IF(조건, T, F) 조건이 참이라면 T, 거짓이면 F 반환
1은 2보다 작다. 출력
SELECT
CASE
WHEN -1 > 0 THEN '-1은 양수다.'
WHEN -1 = 0 THEN '-1은 0이다.'
ELSE '-1은 음수다.'
END;
좀더 복잡한 조건은 CASE 사용
-1은 음수다. 출력
SELECT
Price,
IF (Price > 30, 'Expensive', 'Cheap'),
CASE
WHEN Price < 20 THEN '저가'
WHEN Price BETWEEN 20 AND 30 THEN '일반'
ELSE '고가'
END
FROM Products;
만약 Price가 30보다 크면 Expensive, 같거나 적으면 Cheap 출력,
만약 20미만이면 저가, 20~30이면 일반, 초과면 고가로 출력
SELECT
IFNULL('A', 'B'),
IFNULL(NULL, 'B');
IFNULL(A, B) A가 NULL일 시 B 출력
각각 A, B 출력
복습완료