3. MySQL 숫자와 문자열을 다루는 함수들 (초보자 MySQL 강좌)

엑셀이나 C언어를 다뤄보신 분들이라면 반올림 ROUND함수 같은 것들은 익숙하실 텐데요.
오늘은 MySQL에서 숫자와 문자열을 다루는 여러 함수들을 통해 실습을 해보겠습니다.

우선 숫자 관련 함수들에는

ROUND - 반올림
CEIL - 올림
FLOOR - 내림 

위와 같은 함수들이 있습니다. 

CEIL 같은 경우에는 '씰'로 발음하고 영어로는 '천장을 만들다'라는 뜻이 있습니다. 어떠한 숫자에 천장을 달아준다는 건 '올림'해준다는 의미와 상통하겠죠? 이런 식으로 이해하시면 용어를 암기하시는 데 조금이나마 도움이 될 거라 생각합니다 :)

Floor 같은 경우는 내려치다, 바닥에 눕히다와 같은 뜻이 있죠. 네 '내림'이라는 뜻입니다.
저만의 암기방식이긴 하지만 참고해보시길...ㅎㅎ

오늘도 아래 실습 사이트에 들어가서 직접 하나씩 입력해보면서 결괏값을 도출해봅시다!

https://www.w3schools.com/mysql/trymysql.asp?filename=trysql_select_all 

MySQL Tryit Editor v1.0

 

1. 반올림, 올림, 내림 함수 (ROUND, CEIL, FLOOR)

<0.5는 반올림, 0.4는 올림, 0.6은 내림하시오>

위 식을 쿼리를 짜서 실습해볼까요?

SELECT
  ROUND(0.5),
  CEIL(0.4),
  FLOOR(0.6);

과 같이 작성해주시면 됩니다 :) 여러 식을 동시에 계산하기 때문에 콤마(,)를 사이사이에 넣어주시는 것 꼭 잊지마시구요!

1, 0, 0이라는 결괏값이 나오셨다면 잘하신 겁니다.

다음 STEP으로 바로 넘어가죠.

<PRODUCTS 테이블에서 PRICE값과 그 값의 반올림, 올림, 내림한 값을 도출하시오>

쿼리를 작성해볼까요? SELECT, ROUND, CEIL, FLOOR, FROM 등을 사용해서요!

SELECT PRICE
  ROUND Price,
  CEIL Price,
  FLOOR Price
FROM Products;

와 같은 커리를 짜셨다면 잘하셨습니다. 하지만 ROUND, CEIL, FLOOR 뒤에 오는 PRICE를 괄호() 표시로 묶어주셔야 오류 없이 값이 도출됩니다. 

위와 같이 말이죠.

---

2. 절댓값(ABS)

절댓값을 구하는 함수에는 ABS라는 명령어를 사용합니다. ABS=앱솔루틀리입니다.

SELECT
  ABS(1), ABS(-9), ABS(10 - 21), ABS(5 + 11); 등 다양한 연산자와 수를 넣어서 직접 계산해보세요.

<응용하기>
OrderDetails 테이블에서 모든 값을 불러오되, 이중 Quantity에서 10을 뺀 값의 절댓값이 5보다 작은 데이터들을 추출하시오. (SELECT, FROM, WHERE, ABS 사용)

쿼리는 아래와 같이 짜주시면 됩니다.

SELECT * FROM OrderDetails
WHERE ABS(Quantity - 10) < 5;

---

3. 최댓값 최솟값(GREATEST, LEAST)

가장 큰 값과 가장 작은 값을 구하는 함수입니다. 

SELECT GREATEST(1, 10, 100),
         LEAST(1, 2, 4, 8, 16);

위 쿼리를 입력해보시고 그 값이 제대로 나오는지 직접 확인해보세요.

필자의 경우 앞서 내운 배용들을 활용해서 OrderDetails 테이블에서 각종 ID와 수량을 불러오고 그 값들 중 최댓값과 최소값을 구하는 쿼리를 짜보았다. 이때 최대값과 최솟값은 한국어로 출력되도록 AS문을 활용했다.

---

4. 그룹 함수: (MAX, MIN, COUNT, SUM, AVG)

다뤄야 할 함수가 많기에 각 함수명과 정의부터 훑고 실습해보도록 하자.
1) MAX: 가장 큰 값 도출
2) MIN: 가장 작은 값 도출
3) COUNT: (NULL을 제외한) 개수
4) SUM: 총합
5) AVG: 평균값(Average)

그렇다면 우리는 실습 사이트에서 OrderDetails 테이블의 수량(Quantity) 중 가장 큰 값과 가장 작은 값, 그리고 항목의 개수, 수량의 합, 평균수량까지 구해보자.

SELECT
  MAX(Quantity),
  MIN(Quantity),
  COUNT(Quantity),
  SUM(Quantity),
  AVG(Quantity)
FROM OrderDetails

바로 이렇게!

---

5. 제곱과 제곱근 구하기, 소수점 자릿수까지 불러오기

<2를 3 제곱하시오> 
<4의 2승은 무엇인지 구하시오>
<6을 16만큼 제곱하시오>
와 같은 제곱은 MySQL에서 어떤 함수를 사용할까요? 
바로 'POW(A, B)' 혹은 POWER(A, B) = A를 B만큼 제곱을 사용합니다.

SELECT
  POW(2, 3), POWER(4, 2), POWER(6, 16); 과 같이 말이죠.

반대로 제곱근(루트)은 어떻게 구할까요? 16의 제곱근을 구하라=루트 16을 구하라와 같이 말이죠.
바로 SQRT함수를 사용합니다. 
SELECT
  SQRT(16); 이렇게 말이죠. 4라는 값이 나오겠죠?

<응용하기>
실습 사이트에서, Products 테이블에서 Price값과 Price를 0.5 제곱한 값을 불러오시오. 단 조건은 Price의 제곱근 값이 4보다 작은 데이터들만 불러오시오.

직접 머리 싸매서 쿼리를 짜 봅시다! 아래와 같은 정답이 나왔다면 OK입니다.

SELECT Price, POW(Price, 1/2)
  FROM Products
  WHERE SQRT(Price) < 4;

또한 TRUNCATE 함수를 이용해서 소수점에서 원하는 자릿수까지 값을 불러올 수 있습니다.

TRUNCATE(A, B)라는 함수를 사용하면 A라는 숫자에서 소숫점 B번째 만큼의 자릿수까지만 값을 도출하라는 뜻입니다.
TRUCNATE(16.5960234, 4)를 입력하면 소숫점 네 번째 자리인 16.5960까지만 도출됩니다.

*B자리에 양수가 아닌 음수 ex) -3, 4등을 넣게 되면 소수점 자리를 넘어서 앞으로 0을 붙이게 됩니다. 쉽게 얘기해서

TRUNCATE(13592.3421, -1)을 입력하면 13590이라는 값이 도출됩니다. 직접 원하는 값을 넣어서 꼭 실험해보세요 ^^

만약 Products테이블에서 Price를 불러오는데 12.XX에 포함되는 값만 불러오고 싶다면 아래와 같은 쿼리를 짤 수 있겠죠?

SELECT Price FROM Products
WHERE TRUNCATE(Price, 0) = 12;

---

지금부터는 문자열을 다루는 함수에 대해 정리합니다.

6. 대문자로 표현하기 & 소문자로 표현하기

어떠한 문자열을 모두 '대문자로' 표현하고 싶을 때는 UPPER('    ') 혹은 UCASE('     ') 함수를 사용합니다.
모두 '소문자로' 표현하고 싶을 때는 LOWER('     ') 혹은 LCASE('      ')함수를 사용하시면 됩니다.

실습 사이트로 와서, CUSTOMERS테이블에서 CUSTOMERNAME은 대문자로, CONTACTNAME은 소문자로 데이터를 쭉 뽑고 싶다면 어떻게 쿼리를 작성하면 될까요? 직접 입력해보세요!

아래와 같이 작성하시면 됩니다.

SELECT
  UCASE(CustomerName),
  LCASE(ContactName)
FROM Customers;

---

7. 문자열 이어 붙이기 시리즈

2016, 02, 01, AM이라는 네 문자열을 이어서 출력하려면 어떤 함수를 써야 할까요? CONCAT이라는 함수를 사용해서

SELECT CONCAT(2016, 02, 01, 'AM');처럼 쿼리를 작성해주시면 됩니다. 그러면
2016 02 01 AM과 같이 표기가 되죠.

만약 이 문자열 사이에 - 혹은 _ 등과 같이 기호나 다른 문자열을 넣어서 2016-02-01-AM과 같이 표기하고 싶다면 어떻게 하면 될까요?

CONCAT_WS(A,....) 함수를 사용하면 됩니다. 함수를 해석하면.... 에 포함되는 문자열들 사이에 A라는 문자열(기호)을 넣어서 이어 붙여라는 뜻이 됩니다.

SELECT CONCAT_WS('-', 2016, 02, 01, 'AM'); 과 같이 쿼리를 작성하게 되면

2016-02-01-AM이라는 값이 도출되는 것이죠.

실습 사이트로 넘어와서,
Employees라는 테이블에서 Firstname과 LastName을 '     '(공백)으로 이어주고 그 값을 FullName이라는 별명으로 출력해 봅시다.

위와 같이 데이터를 추출하셨다면 잘 따라오신 겁니다!

---

8. 문자열 자르기, 왼쪽부터 N글자, 오른쪽부터 N글자 추출하기

SUBSTR 혹은 SUBSTRING이라는 함수를 사용하면 원하는 만큼 문자열을 자를 수 있습니다.

SELECT
  SUBSTR('ABCDEFG', 3),
  SUBSTR('ABCDEFG', 3, 2),
  SUBSTR('ABCDEFG', -4),
  SUBSTR('ABCDEFG', -4, 2);

자 위와 같은 쿼리가 있습니다.

SUBSTR('ABCDEFG', 3)의 경우 ABCDEFG에서 세 번째 문자열부터 쭈욱 불러오라는 말입니다. 'CDEFG'가 추출됩니다.

SUBSTR('ABCDEFG', 3, 2)의 경우 세 번째 문자열부터 '두 개'만 추출 해오라는 말입니다. 'CD'가 추출됩니다.

SUBSTR('ABCDEFG', -4)의 경우 음수가 들어갔기 때문에 역순으로 계산합니다. '뒤에서' 네 번째부터 추출하여 'DEFG'가 추출됩니다.

SUBSTR('ABCDEFG', -4, 2)는 뒤에서 네 번째 문자부터 두 문자만 추출 해오라는 뜻입니다. 'DE'가 추출되겠죠?

한 번 이해하면 어렵지 않으니 직접 값을 넣어보시고, '예상되는 결괏값을 생각해보신 후' 추출하여 결과를 확인해보시면 이해와 암기에 도움이 되실 것입니다. 

그렇다면
LEFT('ABCDEFG', 4)
RIGHT('ABCDEFG', 2)와 같은 함수는 무엇일까요?
영어 그대로 LEFT(A, n) 은 A라는 문자열의 '왼쪽부터' n만큼 데이터를 가져오라는 뜻이고 RIGHT는 오른쪽부터입니다.
위 쿼리의 값은 ABCD가 나올 것이고, 아래 쿼리의 값은 FG가 나오게 됩니다.

위와 같이 날짜 데이터를 불러올 때 연도와 월, 일을 따로 구분하여 뽑을 수도 있겠죠?

---

9. 바이트의 길이와 문자열 길이 구하기

LENGTH: 문자열의 바이트 길이
CHAR_LENGTH, CHARACTER_LENGTH: 문자열의 문자 길이

일반적으로 회사에서 SQL을 사용할 땐 아무래도 한국어를 많이 사용하시겠죠? 

CHAR_LENGTH를 써주셔야 여러분들이 원하는 '글자 개수'를 뽑아올 수 있습니다 :)
즉, CHAR_LENGTH('안녕하세요')를 입력하면 5라는 결괏값이 나오는 것이죠.

10. MySQL 공백 없애기 TRIM

I HELLO I라는 문자열에서 양쪽 공백, 혹은 왼쪽이나 오른쪽의 공백을 없애려면 어떤 함수를 사용해야 할까요?
바로 TRIM, LTRIM, RTRIM 함수를 사용하게 됩니다.
직접 아래 쿼리를 따라 실습 사이트에 입력해보고 결괏값을 보실게요.

SELECT
  CONCAT('|', ' HELLO ', '|'),
  CONCAT('|', LTRIM(' HELLO '), '|'),
  CONCAT('|', RTRIM(' HELLO '), '|'),
  CONCAT('|', TRIM(' HELLO '), '|');

결과:

| HELLO |

|HELLO |

| HELLO|

|HELLO|

CONCAT으로 HELLO와 I를 이어 붙인 상태에서, 양쪽 공백, 왼쪽 공백, 오른쪽 공백을 없애는 쿼리를 눈으로 확인했습니다.

정리하면,
TRIM: 양쪽 공백 제거
LTRIM: 왼쪽 공백 제거
RTRIM: 오른쪽 공백 제거가 되겠죠 :)

카테고리에서 카테고리 이름을 검색하고 싶은데 저렇게 스페이스 바를 포함해서 검색하니 Result(결과)가 0으로 나타나지 않는 것을 확인할 수 있죠. 컴퓨터라 멍청해서 알아서 공백을 없애고 'Beverages'로 검색하지는 못합니다. 이를 방지하려면 ' Beverages '라는 문자열 앞에 TRIM만 붙여주시면 공백을 제거해서 검색하니까 어떤 공백이 들어가도 결괏값을 도출하겠죠?

---

A라는 데이터 목록 ex) 1, 2, 3, 4.... 이 5글자가 될 때까지 왼쪽이나 오른쪽에 0, *, -, _ 등 특별한 문자를 붙여주고 싶다면 어떤 함수를 사용해야 할까요? 바로 LPAD와 RPAD입니다.

LPAD('ABC', 5, '*') 혹은 RPAD('ABC', 10, '-') 이런 식으로 말이죠.

SELECT
LPAD('ABC', 5, '*')
RPAD('ABC', 10, '-');

​위와 같은 결과가 나오는 것을 확인하실 수 있습니다. 

REPLACE(A, B, C) 함수는 무엇일까요?
A라는 문자열에서 B라는 단어를 C로 바꿔라는 뜻입니다.

Categories라는 테이블에서 Description 목록을 추출하는데 그중 콤마(,) 표시된 것을 ' and '로 바꿔서 출력하고 싶다면

SELECT
  REPLACE(Description, ', ', ' and ')
FROM Categories;

위와 같은 쿼리를 작성해주시면

아래와 같이 and로 치환되어 표기되었음을 확인할 수 있습니다.
그런데, and가 두 번 중복되어 표기되는 값이 몇 개 보이죠.

왜 그러냐면 원래 데이터에서 마지막 항목을 표기할 때 이미 and라는 단어가 포함되어 있었기 때문입니다.

이 문제를 해결하려면 (집중해서 하나씩 이해하세요!)

1) 기존에 있던 and를 콤마(,)로 변경 
2) 변경된 데이터를 한 번 더 REPLACE 함수로 ' , '를 ' and'로 변경

즉 아래와 같은 쿼리가 완성되는 거죠.

SELECT
  DESCRIPTION,
  REPLACE(Description, ', ', ' and '),
  REPLACE(REPLACE(Description, ', and ', ', '), ', ', ' and')
FROM Categories;

---

11. 위치 찾기 함수 (INSTR)

'ABCDE'라는 문자열에서 'C' 혹은 ' DE'가 몇 번째 문자부터 시작되는지 확인하고 싶을 때 그 '몇 번째'를 찾아주는 함수입니다.

INSTR('ABCDE', 'C')
INSTR('ABCDE', 'DE')와 같이 활용할 수 있겠죠.

12. 변환 함수 (CAST) 원하는 자료형으로 바꾸다

CAST(A, B)와 CONVERT(A, B)는 같은 함수입니다.

즉, A라는 값을 B라는 값으로 변환해주는 함수입니다.

만약,

SELECT
  '01' = '1',
  CONVERT('01', DECIMAL) = CONVERT('1', DECIMAL);

위와 같은 카레가 있다면 

'01'이라는 글자와 '1'이라는 글자는 컴퓨터에서 '같지 않다'라고 판단합니다.
하지만 CONVERT함수와 DECIMAL을 활용하여 같은 숫자로 표기해주면
'01' = '1'이 같다고 컴퓨터가 처리하게 됩니다.