[TIL] 2025-5-28 MySQL 내장 함수 / 조인

mysql 쓰면서도 항상 내장 함수를 많이 써보지는 않았어서 이번 시간이 매우 유용했습니다!

일주일 정도 진행된 db수업중에서 오늘이 가장 모르는게 많이 나온 날인거 같네요~

 

제어 흐름 함수

CASE ~ WHEN ~ ELSE ~ END

• 다중 분기 처리를 위한 연산자
• IF보다 복잡한 조건 분기에 유리

값 기반 예시

SELECT CASE 10
  WHEN 1 THEN '일'
  WHEN 5 THEN '오'
  WHEN 10 THEN '십'
  ELSE '글쎄요'
END;
-- 결과: '십'

 

조건 기반 예시

SELECT ename, sal,
  CASE
    WHEN sal >= 3000 THEN '상위급여'
    WHEN sal >= 2000 THEN '중위급여'
    ELSE '하위급여'
  END AS 급여등급
FROM emp;

---

문자열 함수

문자 길이 관련

CHAR_LENGTH()	문자의 개수
LENGTH()	바이트 수 (UTF-8 기준 한글은 3바이트)

 

SELECT CHAR_LENGTH('ABC'), LENGTH('ABC');
SELECT CHAR_LENGTH('가나다'), LENGTH('가나다');

 

문자열 연결

SELECT CONCAT('abc', 'ABC');
SELECT CONCAT_WS('/', '2022', '02', '22');

 

숫자 포맷팅

SELECT FORMAT(123456.7890, 4); -- 결과: '123,456.7890'

---

문자열 위치 함수

ELT(n, str1, str2, ...)	n번째 문자열 반환
FIELD(val, str1, str2, ...)	val의 위치 반환
FIND_IN_SET(val, 'str1,str2,...')	CSV 문자열에서 위치 찾기
INSTR(str, substr)	substr 시작 위치
LOCATE(substr, str)	INSTR과 동일, 매개변수 반대

SELECT ELT(2, '하나', '둘', '셋'),
       FIELD('둘', '하나','둘','셋'),
       FIND_IN_SET('둘','하나,둘,셋'),
       INSTR('하나둘셋','둘'),
       LOCATE('둘','하나둘셋');

---

문자열 조작 함수

문자열 일부 대체 및 추출

SELECT INSERT('abcdefghi', 3, 3, '@@@@'); -- 결과: ab@@@@fghi
SELECT LEFT('abcdefghi', 3), RIGHT('abcdefghi', 3);
SELECT LOWER('AbcD'), UPPER('AbcD');

문자열 채우기 및 공백 제거

SELECT LPAD('해달', 5, '&&'), RPAD('해달', 5, '##');
SELECT LTRIM(' 해달'), RTRIM(' 해달 ');
SELECT TRIM(' 해달 '), TRIM(BOTH 'ㅋ' FROM 'ㅋㅋㅋ사랑합니다ㅋㅋㅋ');

 

 

반복, 치환, 공백 생성

SELECT REPEAT('김만월', 3);
SELECT REPLACE('김만월은 1살입니다', '1살', '2살');
SELECT CONCAT('Trust', SPACE(10), 'your power');

 

부분 문자열 추출

SELECT SUBSTRING('Trust your power', 3, 2);
SELECT SUBSTRING('Trust your power' FROM 7 FOR 4); -- 'your'

 

구분자로 나누기

SELECT SUBSTRING_INDEX('www.naver.com', '.', 2); -- 'www.naver'
SELECT SUBSTRING_INDEX('www.naver.com', '.', -2); -- 'naver.com'

---

수학 함수

ABS(n)	절대값
CEILING(n)	올림
FLOOR(n)	내림
ROUND(n)	반올림
MOD(a, b) 또는 a % b	나머지 연산
RAND()	0 이상 1 미만의 난수
SIGN(n)	양/음/0 판별 (1, -1, 0)
TRUNCATE(n, d)	d 자릿수 이하 버림

 

SELECT ABS(-100);
SELECT CEILING(4.7), FLOOR(4.7), ROUND(4.7);
SELECT MOD(157, 10), 157 % 10;
SELECT RAND();
SELECT SIGN(100), SIGN(0), SIGN(-100.123);
SELECT TRUNCATE(12345.12345, 2), TRUNCATE(12345.12345, -2);

---

날짜 및 시간 함수

현재 날짜/시간 관련

sql

 

SELECT CURDATE(), CURTIME(), NOW(), SYSDATE();
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW());
SELECT DAYOFWEEK(NOW()), MONTHNAME(NOW()), DAYOFYEAR(NOW());

 

문자열 → 날짜 변환

SELECT STR_TO_DATE('2023-04-01 12:30:45', '%Y-%m-%d %H:%i:%s');

 

날짜 연산 및 포맷

SELECT DATE_ADD('2022-01-01', INTERVAL 31 DAY);
SELECT DATE_SUB('2022-01-01', INTERVAL 1 MONTH);
SELECT DATE_FORMAT(NOW(), '%Y년 %m월 %d일 %H시 %i분 %s초');

---

시스템 정보 함수

조회된 행/처리된 행 개수

SELECT SQL_CALC_FOUND_ROWS * FROM userTbl LIMIT 3;
SELECT FOUND_ROWS();

UPDATE userTbl SET addr = '서울' WHERE userID = 'JYP';
SELECT ROW_COUNT();

 

환경 정보 조회

SELECT VERSION();
SELECT DATABASE(), USER(), SESSION_USER();

 

자동 증가 ID 확인

INSERT INTO tbl (name) VALUES ('test');
SELECT LAST_INSERT_ID();

 

조인의 종류

INNER JOIN	양쪽 테이블에 모두 존재하는 데이터만 반환
LEFT OUTER JOIN (LEFT JOIN)	왼쪽 테이블은 모두 표시, 오른쪽에 없으면 NULL
RIGHT OUTER JOIN (RIGHT JOIN)	오른쪽 테이블은 모두 표시, 왼쪽에 없으면 NULL
FULL OUTER JOIN	양쪽 모두 표시, 없는 쪽은 NULL (MySQL은 직접 지원 안 함)
CROSS JOIN	가능한 모든 조합 (카티션 곱)
SELF JOIN	자기 자신과 조인

INNER JOIN (내부 조인)

• 가장 기본적인 조인 방식
• 양쪽 테이블 모두에서 조건을 만족하는 행만 반환

 

SELECT e.ename, d.dname
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno;

LEFT JOIN (왼쪽 외부 조인)

• 왼쪽 테이블의 모든 행을 기준으로 오른쪽 테이블과 조인
• 오른쪽에 일치하는 값이 없으면 NULL로 표시

 

SELECT e.ename, d.dname
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno;

RIGHT JOIN (오른쪽 외부 조인)

• RIGHT JOIN은 LEFT JOIN과 반대
• 오른쪽 테이블의 모든 행을 기준으로 왼쪽 테이블과 조인

 

SELECT e.ename, d.dname
FROM emp e
RIGHT JOIN dept d ON e.deptno = d.deptno;

 

 

FULL OUTER JOIN

• 양쪽 테이블의 모든 행을 반환
• 어느 한쪽에 값이 없으면 NULL로 표시
• MySQL은 FULL OUTER JOIN을 직접 지원하지 않음 → UNION으로 구현 가능

-- MySQL에서 FULL OUTER JOIN을 흉내 내는 방식
SELECT e.ename, d.dname
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno

UNION

SELECT e.ename, d.dname
FROM emp e
RIGHT JOIN dept d ON e.deptno = d.deptno;

CROSS JOIN

• 조건 없이 두 테이블의 모든 조합(카티션 곱)을 반환

 

SELECT e.ename, d.dname
FROM emp e
CROSS JOIN dept d;

• 결과 행 수 = 테이블1 행 수 × 테이블2 행 수

 

SELF JOIN (자기 자신과의 조인)

• 같은 테이블을 두 번 사용하여 조인

SELECT e1.ename AS 사원명, e2.ename AS 상급자명
FROM emp e1
LEFT JOIN emp e2 ON e1.mgr = e2.empno;