SQL 함수(FUNCTION)

목차

---

-- 함수: 미리 만들어 놓은 특별한 기능을 사용하는 방법
-- 단일행 함수 : 특별한 기능을 하나의 데이터에 적용하여 출력
-- 다중행 함수 : 특별한 기능을 여러개의 데이터에 적용하여 출력

-- - CEIL(), ROUND(), TRUNCATE(), CONCAT(), DATE_FORMAT() ...
-- 다중행(결합,집계) 함수 : 특별한 기능을 여러개의 데이터에 적용하여 출력
-- - SUM(), AVG(), COUNT(), MIN(), MAX(), VAR(), MEDIAN() ...

 

간편계산시에는 SELECT 12.3 FROM DUAL; 로 해서 FROM 은 언제나 꼭 써줘야함 회사세팅에서는. (MYSQL에서는 기본으로 FROM DUAL이 써지게 되어있어서, 코드쓸때는 FROM ~~ 부분은 안써도 되는 것 처럼 보이는 것 뿐임)

 

concat, group_concat

-- CONCAT() : 문자열을 결합하여 결과 출력
-- name_code : 국가이름(국가코드) 포멧의 컬럼 추가
SELECT name, code, CONCAT(name, code) AS name_code
FROM country;

SELECT staff_id, first_name, last_name, CONCAT(first_name, ' ', last_name) AS full_name
FROM staff; -- Mike, Hillyer => Mike Hillyer

-- concat 해서 full name 출력후, 영화아이디로 그룹핑해서, 한 영화에 해당되었던 여러 full_name 들이 한 열에 쭉 보이게 concat 한번 더 하기(group_concat)
select f.film_id, f.title, group_concat(' ', concat(a.first_name, ' ', a.last_name)) AS full_name
from film f, film_actor fa, actor a
where f.film_id = fa.film_id AND fa.actor_id = a.actor_id
GROUP BY f.film_id
ORDER BY f.film_id ASC;

 

STRING FUNCTION

-- STRING FUNCTION
-- -SUBSTRING / SUBSTR (기능 동일)
SELECT SUBSTRING ('뭐라구머라구어쩌라구', 1,6) FROM DUAL; --1번째자리-15째자리 (둘다 포함), 첫 인덱스는 1이라고 보면됨
--뭐라구머라구
SELECT SUBSTRING ('뭐라구머라구어쩌라구', 7) FROM DUAL; -- 7번째 자리부터 끝까지 반환

--LENGTH/CONCAT/UPPER/LOWER
SELECT LENGTH('AAAAA') FROM DUAL; --LENGTH는 BYTE수 반환, 한글은 하나당 3바이트
SELECT CONCAT('DDD','AAA') FROM DUAL;
SELECT UPPER ('Fast Campus') FROM DUAL;
SELECT LOWER ('Fast Campus') FROM DUAL;

SELECT TRIM('   AAA  AA  A   ') FROM DUAL; -- PYTHON STRIP과 동일
SELECT INSTR('AAAAAAADDD', 'AAD') FROM DUAL; -- PYTHON FIND와 동일, 첫번째문자열 안에서의 두번째 문자열의 위치 반환
SELECT REPLACE('ASSDDFD','F','G') FROM DUAL; -- PYTHON REPLACE
SELECT LPAD('AADDDFF',10,'!') FROM DUAL; -- 10이라는 길이를 채우고싶은데 첫번째 문자열로 채우고 남았다면 왼쪽에('L'PAD) '!'를 채워줘

NUMBER FUNCTION

-- NUMBER FUNCTIONS
SELECT CEIL(12.345)  --13 올림, 자리수 설정안됨 
FROM DUAL;

SELECT CEIL(12.345*10) / 10  --12.4, 올림
FROM DUAL;

SELECT ROUND(12.345) FROM DUAL; -- 12, 반올림
SELECT ROUND(12.345,1) --12.3  
FROM DUAL;

SELECT TRUNCATE(12.567,1); --버림(절삭), 12.3, 반드시 자리수 설정해야함
SELECT FLOOR (12.567) FROM DUAL; -- 12, 버림(내림)

--예시, 연령대 데이터 출력하기(ages로) (예, 22살은 20으로 출력)
SELECT passengerid, name, age, truncate(age,-1) AS ages
FROM titanic;

SELECT ABS(82.8) FROM DUAL; 
SELECT SIGN(82.8) FROM DUAL; -- 양수값이 들어가있으면 1, 음수면 -1 리턴하는 함수
SELECT MOD(82 , 5) FROM DUAL; -- 둘째값으로 첫째값 나누었을때 나머지 리턴

DATE

date_format()

날짜표현식 링크: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

date_format 함수 상세 설명 페이지: https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format

-- DATE
SELECT NOW() FROM DUAL;  -- 현재시간 리턴(쿼리 실행시간)
SELECT SYSDATE() FROM DUAL; -- NOW와 거의동일  (쿼리내에서 해당 함수가 실행되는 시간)
SELECT CURRENT_DATE() FROM DUAL; --오늘날짜
SELECT ADDDATE(NOW(), 10) FROM DUAL; -- NOW에서 10일 더한것 ADDDATE('20230901',3) 이렇게쓰기도함
SELECT LAST_DAY('20231225') FROM DUAL; -- 날짜가 포함된 월의 마지막 날을 리턴해주는 함수

SELECT YEAR(NOW()) FROM DUAL; 주어진날짜에서 연/월/일 날짜만 리턴 YEAR/MONTH/DAY

--DATE FORMAT
USE sakila;
SELECT payment_id, amount, payment_date, DATE_FORMAT(payment_date, '%Y-%m') AS monthly
		, DATE_FORMAT(payment_date, '%p %h') AS hour12
FROM payment;

-- 요일별 총 매출 출력 : DATE_FORMAT() : '%a'
-- 조건 : 주중중에서 (주말제외)
SELECT DATE_FORMAT(payment_date, '%a') AS dow, SUM(amount) AS ts
FROM payment
-- WHERE DATE_FORMAT(payment_date, '%a') NOT IN ('Sat', 'Sun')
GROUP BY dow
HAVING dow NOT IN ('Sat', 'Sun');

-- where 이 낫나 HAVING이 낫나. WHERE 로 먼저 거르고나서 하는게 더 메모리를 적게 먹으니, WHERE로 하자.
-- 2005년도 5월 데이터 출력
SELECT amount, payment_date
FROM payment
WHERE (payment_date >= '2005-05-01') AND (payment_date <= '2005-06-01');
-- 2005 05 31말고 5월 다 출력하려면 2005 06 01로 해야함.

-- 하지만 위 코드 보다 요 코드가 더 낫죠
SELECT amount, payment_date
FROM payment
WHERE date_format(payment_date, '%Y-%m') = '2005-05';

 

NULL FUNCTION

SELECT IFNULL('실제데이터', '대체값') FROM DUAL; -- 실제데이터 값이 NULL이면 대체값을 대입하는 함수

SELECT COALESCE('data1', 'data2', 'data3') FROM DUAL; -- NULL이 아닌 최초의 값을 리턴하는 함수, data1이 null이면 데이터2값을 리턴

SELECT NULLIF('데이터1','데이터2') FROM DUAL; -- 주어진 데이터 두개가 동일하면 NULL리턴, 아니면 데이터 1 리턴

SELECT ISNULL('데이터') FROM DUAL; --데이터가 NULL인지 확인하는 함수. 데이터가 존재하면 0, NULL이면 1 리턴.