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)

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

12.8 String Functions and Operators Table 12.12 String Functions and Operators Name Description ASCII() Return numeric value of left-most character BIN() Return a string containing binary representation of a number BIT_LENGTH() Return length of argument

dev.mysql.com

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

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

'SQL' 카테고리의 다른 글

SQL \| MySQL \| 그룹에 따른 집계 - GROUP BY, HAVING (0)	2023.03.09
SQL \| MySQL \| 내장 함수(2) - 날짜, 시간 함수 (0)	2023.03.09
SQL \| MySQL \| SELECT(2) - 연산자(산술, 비교, 논리) (0)	2023.03.07
SQL \| MySQL \| SELECT(1) - 간단한 데이터 조작 (0)	2023.03.07
SQL \| MySQL \| 데이터 수정, 삭제 - Update, Delete (0)	2023.03.06

00. 사용 예제 테이블

01. 숫자와 관련된 함수들

02. 문자와 관련된 함수들

'SQL' 카테고리의 다른 글

티스토리툴바