> 숫자를 다루는 함수들
▶︎ 반올림, 올림, 내림
🌈 SELECT ROUND(0.5), CEIL(0.2), FLOOR(0.5);
- ROUND() : 반올림
- CEIL() : 올림
- FLOOR() : 내림
🌈 SELECT Price ROUND(Price), CEIL(Price), FLOOR(Price) FROM Products;
- 매개변수로 원하는 column을 넣기만 하면 알아서 연산해줍니다.
▶︎ 절대값(양수로 변환하기)
🌈 SELECT ABS(10), ABS(-10.5), ABS(10 - 110);
- 음수를 양수로 변환해줍니다.
🌈 SELECT OrderDetails WHERE ABS(Quantity - 5) < 5;
-
Quantity 컬럼의 값이 4 ~ 9 사이의 값인 데이터만 출력됩니다.
-
4-5=1, 9-5=4이기 때문에 5보다 작은 수가 되어 TRUE가 되기 때문입니다.
▶︎ 여러 colume중에 가장 큰/작은 값 가져오기
🌈 SELECT GREATEST(1, 5, 10), LEAST(1, 5, 10);
🌈 SELECT OrderDetailID, Quantity, GREATEST(Quantity, OrderDetailID), LEAST(Quantity, OrderDetailID) FROM OrderDetails;
- column끼리 비교하여 가장 큰/작은 값을 가져옵니다.
▶︎ 하나의 column에서 가장 큰/작은 값 가져오기
🌈 SELECT MAX(Quantity), MIN(Quantity) FROM OrderDetails WHERE OrderDetailID BETWEEN 1 AND 20;
- OrderDetails 테이블의 OrderDetailID가 1 ~ 20인 데이터들 중에 Quantity column에서 가장 큰 값과 가장 작은 값을 구하였습니다.
▶︎ 갯수, 총합, 평균값 구하기
🌈 SELECT COUNT(Quantity), SUM(Quantity), AVG(Quantity) FROM OrderDetails WHERE OrderDetailID BETWEEN 1 AND 20;
- 지정한 column의 갯수, 합계, 평균값을 구하였습니다.
▶︎ 제곱, 제곱근 구하기
🌈 SELECT POW(2, 3), POWER(2, 3), SQRT(16);
- POW(A, B), POWER(A, B)는 A를 B만큼 제곱해줍니다.
- SQRT()는 제곱근(루트)을 구해줍니다.
▶︎ 소숫점 자리수 선택하기
🌈 SELECT TRUNCATE(123.12345, 1), TRUNCATE(123.12345, 2), TRUNCATE(123.12345, 3);
-
두 번째 매개변수 숫자만큼 소숫점 자리수를 가져옵니다.
-
반올림을 해주지는않습니다.
-
두 번째 매개변수에 음수가 들어가면 소숫점은 사라지고 그 숫자만큼 소숫점의 앞쪽 숫자들이 0이 됩니다.
🌈 SELECT Price FROM Products WHERE TRUNCATE(Price, 0) = 12;
- Products의 Price 컬럼에서 TRUNCATE로 소숫점을 제거한 값이 12인 경우만 구해오는 식입니다.
> 문자열을 다루는 함수
▶︎ 대소문자로 변환하기
🌈 SELECT UPPER('abcDEF'), LOWER('abcDEF');
-
UPPER(), UCASE() : 대문자로 바꿔줍니다.
-
LOWER(), LCASE() : 소문자로 바꿔줍니다.
🌈 SELECT UPPER(CustomerName), LOWER(CustomerName) FROM Customers;
▶︎ 문자열들 이어 붙이기
🌈 SELECT CONCAT('반가웠어요!', ' ', '그럼', 20000);
-
CONCAT(..., ..., ...) : 괄호 안의 문자열들을 이어 붙여줍니다.
-
숫자가 끼어있어도 문자열로 변환되어 이어집니다.
🌈 SELECT CONCAT('주문번호: ', OrderID) FROM OrderDetails;
- 구하는 값을 우리가 원하는 형식으로 변환해줄 수 있습니다.
🌈 SELECT CONCAT_WS('~', '반가웠어요!', '그럼', 20000);
- CONCAT_WS(S, ..., ...) : 매개변수 첫번째 자리인 S에 넣은 문자가 합치려는 문자들 사이에 들어가게 됩니다.
🌈 SELECT CONCAT_WS('-', FirstName, LastName) AS 'Full name' FROM Employees;
- 두 컬럼의 내용을 합쳐 값을 가져와 보았습니다.
▶︎ 문자열 잘라서 가져오기
🌈 SELECT SUBSTR('123456', 3), SUBSTR('123456', 3, 2), SUBSTR('123456', -4), SUBSTR('123456', -4, 2);
-
SUBSTR()는 인수를 3개 받을 수 있습니다.
-
첫번째 인수를 대상으로 두번째 인수의 수 만큼 왼쪽에서 오른쪽으로 센 후 뒤의 문자를 가져옵니다.
-
두번째 인수가 음수인 경우 문자의 뒤(오른쪽) 끝에서부터 셈을 합니다.
-
세번째 인수는 가져올 글자 수를 지정합니다.
-
❗️MySQL은 숫자를 1부터 셉니다.
🌈 SELECT LEFT('123456', 3), RIGHT('123456', 3);
-
LEFT() : 왼쪽에서부터 두번째 인자에 적힌 수 만큼 글자를 가져옵니다.
-
RIGHT() : 오른쪽에서부터 글자를 가져옵니다.
🌈 SELECT OrderDate, LEFT(OrderDate, 4) AS 'Year', SUBSTR(OrderDate, 6, 2) AS 'Month', RIGHT(OrderDate, 2) AS 'Day' FROM Orders;
- SUBSTR(), LEFT(), RIGHT() 각각의 특징을 이용해 다음과 같이 날짜 컬럼을 나눌 수도 있습니다.
▶︎ 문자 길이 확인하기
🌈 SELECT LENGTH('반가워요'), CHAR_LENGTH('반가워요');
-
LENGTH() : 문자열의 바이트 길이를 출력합니다.
-
CHAR_LENGTH() : 문자열의 문자 갯수를 출력합니다.
▶︎ 글자 양쪽의 공백 제거하기
🌈 SELECT CONCAT('H', ' OLL ', 'A'), CONCAT('H', TRIM(' OLL '), 'A'), CONCAT('H', LTRIM(' OLL '), 'A'), CONCAT('H', RTRIM(' OLL '), 'A');
-
TRIM() : 문자 양쪽 끝의 공백을 모두 제거해줍니다.
-
LTRIM(), RTRIM() : 각각 문자의 왼쪽, 오른쪽 공백을 제거해줍니다.
🌈 SELECT * FROM Categories WHERE CategoryName = TRIM(' Condiments');
- 검색할 때 스페이스를 붙이고 검색할 수도 있는데 이럴 경우 공백에 의해 다른 문자로 인식되어 찾으려는 데이터가 검색되지 않게 되므로 이런 상황에서 사용합니다.
▶︎ 특정한 문자 이어붙이기
🌈 SELECT LPAD('ABC', 5, '^'), RPAD('ABC', 5, '^');
-
1번째 인자로 넣은 문자가 2번째 인자의 길이만큼 될 때 까지 3번째 인자에 넣은 문자를 이어붙여줍니다.
-
LPAD() : 1번 문자의 왼쪽에 붙여줍니다.
-
RPAD() : 1번 문자의 오른쪽에 붙여줍니다.
🌈 SELECT ProductID, LPAD(ProductID, 5, 0), Price, RPAD(Price, 6, 0) FROM Products;
- 서로 다른 길이의 문자 자릿수를 맞춰줄 수 있습니다.
▶︎ 특정 문자 대체하기
🌈 SELECT REPLACE('동대문에서 동대문닭한마리를 먹었습니다.', '동대문', '인사동');
- REPLACE() : 1번째 인자를 대상으로 2번째 인자의 문자와 같은 문자를 3번째 인자의 문자로 바꿔줍니다.
🌈 SELECT CustomerName, REPLACE(CustomerName, ' ', '⭐️') FROM Customers;
- 이름 사이 공백을 ⭐️로 대체하였습니다.
🌈 SELECT Description, REPLACE(Description, 'and', ','), REPLACE(Description, ',', 'and') FROM Categories;
-
함수를 여러번 사용하여 문제를 해결할 수도 있습니다.
-
Description 컬럼에서 콤마 대신 and를 사용하고 싶은데 기존에 입력된 값 중에 콤마 대신 and가 되어 있는 부분이 있는 경우
-
첫번째 REPLACE()로 and를 콤마로 바꾼 후 두번째 REPLACE()로 다시 콤마를 and로 바꿔서 문제를 해결할 수 있습니다.
▶︎ 찾는 문자의 자릿수 가져오기
🌈 SELECT INSTR('12345', '123'), INSTR('12345', '2345'), INSTR('12345', 3), INSTR('12345', '9');
-
첫번째 인자에 두번째 인자가 동일하게 존재하는 경우 첫번째 자릿수를 반환해줍니다.
-
존재하지 않는 경우 0을 반환합니다.
🌈 SELECT * FROM Customers WHERE INSTR(CustomerName, ' ') BETWEEN 1 AND 3;
- 자릿"수"를 반환하므로 BETWEEN을 이용하여 이름에 공백이 들어가는 자릿수가 3 이하인 데이터가져올 수도 있습니다.
▶︎ 자료형 변환하기
🌈 SELECT '01' = '1', CONVERT('01', DECIMAL) = CONVERT('1', DECIMAL);
-
CONVERT(A, T) : A를 T의 자료형으로 변환해줍니다.
-
문자 '01'과 '1'은 다르기때문에 FALSE가 됩니다.
-
이 둘을 DECIMAL형으로 바꾸면 둘 다 숫자 1이 되므로 TRUE가 됩니다.
💕 참고: 얄코님 유튜브 강의, SQL 첫걸음
