SQL | MySQL | 내장 함수(1) - 숫자, 문자와 관련된 함수

 

 

 

MySQL에서는 다양한 기능의 내장 함수가 있음.

대표적인 내장함수는 아래와 같다. 

 

1. 문자열 함수 

2. 수학 함수 

3. 날짜와 시간 함수 

 

 

---

 

00. 사용 예제 테이블 

 

원래는 예제 테이블을 하나 하나 데이터 제공 사이트에서 찾았는데,,, 

요즘 ChatGPT가 많이 뜨길래 혹시 몰라서 사용해봤다^~^

 

https://chat.openai.com/chat 에 접속 후

Regenerate response 창에'MySQl에서 수학 함수,  문자열 함수를 모두 다룰 수 있는 20행짜리 예제 테이블 생성쿼리 알려줘' 라고 치면CREATE문과 INSERT INTO문을 날려준다...!

CREATE TABLE func_ex(
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50),
  age INT,
  salary FLOAT,
  created_date DATE,
  num1 INT,
  num2 INT,
  num3 DECIMAL(10, 2),
  string1 VARCHAR(50),
  string2 VARCHAR(50)
);

INSERT INTO func_ex (name, age, salary, created_date, num1, num2, num3, string1, string2) 
VALUES 
('John Doe', 28, 50000, '2021-01-01', 10, 20, 30.50, 'apple', 'orange'),
('Jane Smith', 32, 75000, '2021-01-02', 15, 25, 20.00, 'banana', 'grape'),
('Bob Johnson', 45, 100000, '2021-01-03', 20, 30, 10.25, 'peach', 'lemon'),
('Lisa Anderson', 22, 35000, '2021-01-04', 25, 35, 40.75, 'strawberry', 'blueberry'),
('Mike Brown', 37, 85000, '2021-01-05', 30, 40, 50.00, 'watermelon', 'kiwi'),
('Sarah Lee', 29, 65000, '2021-01-06', 35, 45, 15.25, 'pineapple', 'mango'),
('David Kim', 41, 90000, '2021-01-07', 40, 50, 35.50, 'apricot', 'peach'),
('Emily Davis', 26, 45000, '2021-01-08', 45, 55, 22.75, 'orange', 'pear'),
('Chris Lee', 33, 80000, '2021-01-09', 50, 60, 42.00, 'grapefruit', 'apple'),
('Ava Johnson', 27, 60000, '2021-01-10', 55, 65, 18.50, 'banana', 'orange'),
('William Jones', 39, 95000, '2021-01-11', 60, 70, 30.00, 'kiwi', 'strawberry'),
('Olivia Smith', 30, 55000, '2021-01-12', 65, 75, 55.25, 'mango', 'watermelon'),
('Ethan Kim', 36, 70000, '2021-01-13', 70, 80, 25.50, 'lemon', 'grapefruit'),
('Sophia Davis', 24, 40000, '2021-01-14', 75, 85, 47.00, 'pear', 'apricot'),
('Michael Lee', 43, 110000, '2021-01-15', 80, 90, 60.75, 'blueberry', 'pineapple');

 

뭐.... 20행짜리 써달라고 했는 데, 15행까지 밖에 안나왔지만 만족스럽당~!! 신기해...

---

 

01. 숫자와 관련된 함수들

 

 

①  ROUND(x)   

       :  x를 반올림

 

②  CEIL (x)       

       : x보다 크거나 가장 작은 정수를 반환(올림)

 

③  FLOOR(x)   

       :  x보다 작거나 같은 가장 큰 정수를 반환(내림)

SELECT 
     round(2.5),
     ceil(2.4),
     floor(2.4)
;

 

 

SELECT num3,
           round(num3) AS 반올림,
           ceil(num3) AS 올림,
           floor(num3) AS 내림 
FROM func_ex;

 

 

 

④  ABS(x) 

      :  x의 절대값 반환 

SELECT 
     ABS(3), ABS(-3), ABS(-3.4);

 

 

 

⑤  GREATEST( ) : ( ) 안에서 가장 큰 값 

⑥  LEAST( ) : ( ) 안에서 가장 작은 값 

SELECT num1, num2, num3,
       GREATEST(num1, num2, num3) AS GREATEST,
       LEAST(num1, num2, num3) AS LEAST
FROM func_ex;

 

 

 

⑦ 그룹함수 - 조건에 따라 집계된 값을 가져옴 

• MAX : 최대값 반환
• MIN  : 최소값 반환
• COUNT : 갯수(NULL 값은 제외)
• SUM : 총합
• AVG  : 평균값   

SELECT
  MAX(salary),
  MIN(salary),
  COUNT(salary),
  SUM(salary),
  AVG(salary)
FROM func_ex;

 

 

 

 

⑧ POW(a, b), POWER(a, b) : a의 b 제곱을 반환

⑨ SQRT(a) : a의 제곱근을 반환

SELECT pow(2,2), power(2,2), sqrt(16);

 

 

 

⑩ TRUNCATE(N, n) : N을 소수점 n자리까지 선택

SELECT
  TRUNCATE(1234.5678, 1) AS a,
  TRUNCATE(1234.5678, 2) AS b,
  TRUNCATE(1234.5678, 3) AS c,
  TRUNCATE(1234.5678, 0) AS d,
  TRUNCATE(1234.5678, -1) AS e,
  TRUNCATE(1234.5678, -2) AS f,
  TRUNCATE(1234.5678, -3) AS g;

 

 

 

 

⑪ RAND( ) : 0과 1 사이의 무작위 값을 반환.

SELECT rand();

 

 

 

---

 

02. 문자와 관련된 함수들

 

 

① UPPER(string) : 문자열을 모두 대문자로 변환

② LOWER(string) : 문자열을 모두 소문자로 변환 

SELECT
          name,
          upper(name) AS 대문자,
          lower(name) AS 소문자
FROM func_ex;

 

 

 

③ LENGTH(string) :  문자열의 바이트 길이를 반환

④ CHAR_LENGTH, CHARACTER_LEGNTH : 문자열의 문자 길이를 반환 

SELECT 
  length("김보리"),
  char_length("김보리");

 

 

SELECT 
           name,
           length(name),
           char_length(name)
FROM func_ex;

 

 

 

⑤ 문자열 추출 함수 

• SUBSTR, SUBSTRING(string, 시작위치, 길이) : 문자열의 범위를 지정해서 추출
• LEFT(string, n) : 왼쪽부터 n개의 글자수를 추출
• RIGHT(string, n) : 오른쪽부터 n개의 글자수를 추출

SELECT
  SUBSTR('peach', 3), /* length를 지정안하면 끝까지 추출*/
  SUBSTR('peach', 3, 2),
  SUBSTR('peach', -4),
  SUBSTR('peach', -4, 2),
  LEFT ('peach', 2),
  RIGHT('peach',3);

 

 

 SELECT created_date ,
        LEFT(created_date, 4) AS 'YEAR',
        substr(created_date, 6, 2) AS 'MONTH',
        right(created_date, 2) AS 'DAY'
FROM func_ex;

 

 

 

⑥ LOCATE(찾을 문자, string, 시작인덱스)

• 인수로 전달받은 문자열이 특정 문자열에서 처음으로 나타나는 위치를 반환
• 전달받은 문자열이 특정 문자열 내에 존재하지 않으면 0을 반환     

※ 대부분의 프로그래밍 언어가 문자열의 첫 번째 문자의 인덱스를 0부터 시작하지만

      MySQL에서는 문자열의 첫 번째 문자의 인덱스를 1부터 시작

SELECT name, locate("a",name) FROM func_ex;

 

 

· LOCATE 함수를 사용하여 공백의 위치를 찾은 후 fullname을 firstname과 lastname으로 분리

CREATE TABLE func_ex2
AS 
SELECT name ,
           substr(name, 1, locate(" ",name)-1) AS firstname,
           substr(name, locate(" ",name)+1) AS lastname
FROM func_ex;

 

 

 

⑦ 문자열 결합 함수

• CONCAT(string1, string2,...) : 두 개 이상의 문자열을 연결하여 반환
• CONCAT_WS(separator, string1, string2, ...) :  두 개 이상의 문자열을 separator로 연결하여 반환

SELECT 
    concat("오후 " , "3", ":","14"),
    concat("오후 ", concat_ws(":","3","14"))
 ;

 

 

SELECT 
          concat(firstname, " " , lastname ) AS fullname
FROM func_ex2;

 

 

 

⑧ 문자열 공백 제거 함수 

• TRIM([BOTH|LEADING|TRAILING] [제거할 문자] [FROM] string)   

            - BOTH: 전달받은 문자열의 양 끝에 존재하는 특정 문자를 제거(default)

            - LEADING : 전달받은 문자열 앞에 존재하는 특정 문자 제거 

            - TRAILING : 전달받은 문자열 뒤에 존재하는 특정 문자 제거 

 

        제거할 문자를 명시하지 않으면, 자동으로 공백 제거 

 

• LTRIM(string)   : 왼쪽 공백 제거
• RTRIM(string)  : 오른쪽 공백 제거 

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

 

 

 

⑨ 문자열 자릿수 맞춰 주는 함수 

• LPAD(str , len, padstr) : 지정된 문자나 문자열로 문자열을 왼쪽으로 채우는 함수 
• RPAD(str , len, padstr) : 지정된 문자나 문자열로 문자열을 오른쪽으로 채우는 함수 

SELECT LPAD('hello', 10, '*'),
       RPAD('hello', 10, '*');

 

 

 

⑩ REPLACE(string, old_val, new_val) : string에서 old_val 문자열을 찾은 후에, 찾은 문자열을 new_val로 대체

SELECT created_date,
       REPLACE(created_date,"-", "/") 
FROM func_ex;

 

 

· REPLACE 함수를 공백 제거에서 사용할 수 있음

SELECT name,
       REPLACE(name," ", "") 
FROM func_ex;

 

 

 

 

⑪ 문자열 자료형 변환 함수 

• CAST(A as T) : A를 T 자료형으로 변환
• CONVERT(A, T) : A를 T 자료형으로 변환

SELECT 
     "01" = "1",
     -- UNSIGNED 부호 없는 정수로 변환
     CAST("01" AS UNSIGNED) ='1',
     CONVERT("01", UNSIGNED) = '1';

 

 

 

⑫ FORMAT(number, decimals) : 숫자 값을 지정된 형식으로 서식을 지정하여 문자열로 변환하는 데 사용

• 숫자타입의 데이터를 세 자리마다 쉼표(,)를 사용하는 '#,###,###.##' 형식으로 변환

SELECT FORMAT(1234567.89, 2),
       FORMAT(1234567.89, 0)
;

 

 

 

 

 

 

 

---

출처 및 참고 문헌(reference)

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html

MySQL :: MySQL 8.0 Reference Manual :: 12.8 String Functions and Operators

http://www.tcpschool.com/mysql/mysql_builtInFunction_string

 

 

https://www.yalco.kr/@sql/1-3/