[SQL] 기타 함수 정리

-- 제어 흐름 함수
-- 1) if(수식, 참, 거짓)
SELECT if(100>200, '참이군','거짓이군');

-- 2) ifnull(수식1, 수식2)
SELECT IFNULL(NULL, '널이군');
SELECT IFNULL('aaa','널이군');

-- 3) nullif(수식1, 수식2)
SELECT NULLIF(100,100); -- 수식1과 수식2가 같으면 null 반환
SELECT NULLIF(100,50); 

-- 4) case ~ when ~ else ~ end
SELECT case 10
		 when 1 then '일'
		 when 5 then '오'
		 when 10 then '십'
		 ELSE '모름'
	END;
	
	
-- 문자열 함수(중요!!!)
-- 1) ASCII(아스키코드), CHAR(숫자)
SELECT ASCII('A'); -- char --> ASCII CODE VALUE
SELECT CHAR(65); -- ASCII CODE VALUE --> CHAR

-- BIT_LENGTH(문자열), CHAR_LENGTH(문자열), LENGTH(문자열)
SELECT BIT_LENGTH('abcd'); -- 4 * 8
SELECT CHAR_LENGTH('ABCD');
SELECT LENGTH('ABCD'); -- 영문 1byte, 4글자 4byte
SELECT BIT_LENGTH('가나다');
SELECT CHAR_LENGTH('가나다');
select LENGTH('가나다라'); -- 한글3byte, 4글자 12byte

-- 3) CONCAT(문자열1, 문자열2 ...) CONCAT_WS(구분자, 문자열1, 문자열2,,,)
SELECT CONCAT('AAA','BBB','CCC');
SELECT CONCAT_WS('/', '2022','01','01');



-- 4) ELT(위치, 문자열1, 문자열2 ,,)
SELECT ELT(2, '하나','둘','셋');
-- FIELD(찾을 문자열, 문자열1, 문자열2,,)
SELECT FIELD('셋','하나','둘','셋');
-- FIND_IN_SET(찾을 문자열, 문자열 리스트)
SELECT FIND_IN_SET('둘','하나,둘,셋');
-- INSTR(기준문자열, 부분문자열)
SELECT INSTR('ABCDEF','CD');
SELECT LOCATE('C','ABCDE');


-- 5) FORMAT(숫자, 소수점자리수)
SELECT FORMAT(12345.1234567, 4); -- 반올림 수행함

-- 6) BIN(숫자), HEX(숫자), OCT(숫자)
SELECT BIN(7); -- 2진수
SELECT HEX(10);
SELECT OCT(8);

-- 7) INSERT (기준 문자열 위치, 길이, 삽입할 문자열)
SELECT INSERT('ABCDEFGHI',3,4,'@@@@');


-- 8) LEFT(문자열, 길이), RIGHT(문자열,길이)
SELECT LEFT('ABCDEFGHI',3);
SELECT RIGHT('ABCDEFGHI',3);


-- 9) UPPER(문자열), LOWER(문자열)
SELECT UPPER('abcdFGHijk');
SELECT LOWER ('abcdFGHijk');


-- 10) LPAD(문자열, 길이, 채울 문자열)
SELECT LPAD('AAA',5,'##');
SELECT RPAD('AAA',5,'##');


-- 11) LTRIM(문자열), RTRIM(문자열) : 공백제거용
SELECT LTRIM('     ABC');
SELECT RTRIM('ABC     ');

-- 12) TRIM(문자열), TRIM(방향 자를 문자열 FROM 문자열)
SELECT TRIM('   이것이   '); -- 앞뒤공백 모두 지운다.


-- 13) REPEAT(문자열, 횟수)
SELECT repeat('AAA',3);

-- 14) REPLACE(문자열, 원래문자열, 바꿀문자열)
SELECT REPLACE('This is mariaDB','This','이것이');


-- 15) REVERSE(문자열)
SELECT REVERSE('MariaDB');

-- 16) SPACE(길이)
SELECT CONCAT('이것이' , SPACE(10), 'MariaDB다');

-- 17) SUBSTRING(문자열, 시작위치, 길이)
SELECT SUBSTRING('ABCDEFG', 3, 2);



-- 날짜 시간 함수
-- 1) ADDDATE(날짜, 차이), SUBDATE(날짜, 차이)
SELECT ADDDATE('2022-01-01',INTERVAL 31 DAY);
SELECT ADDDATE('2022-01-01',INTERVAL 1 MONTH);
SELECT SUBDATE('2022-01-01',INTERVAL 1 MONTH);
SELECT SUBDATE('2022-01-01',INTERVAL 31 DAY);

SELECT DATE_ADD('2022-01-01', INTERVAL 31 DAY);
SELECT DATE_SUB('2022-01-01', INTERVAL 31 DAY);


-- ADDTIME(날짜/시간, 시간), SUBTIME(날짜/ 시간, 시간)
SELECT ADDTIME('2022-01-01 23:59:59','1:1:1');
SELECT ADDTIME('2022-01-01 23:59:59','1:1:1');

-- CURDATE(), CURTIME(), NOW(), SYSDATE()
SELECT CURDATE();
SELECT CURRENT_DATE();
SELECT CURRENT_DATE;
SELECT CURTIME();
SELECT CURRENT_TIME();
SELECT CURRENT_TIME;
SELECT NOW();
SELECT LOCALTIME();
SELECT LOCALTIMESTAMP;
SELECT SYSDATE();

-- DATE(), TIME()
SELECT DATE(NOW());
SELECT TIME(NOW());


-- YEAR(날짜), MONTH(날짜), DAY(날짜)
-- HOUR(시간), MINUTE(시간), SECOND(시간), MICROSECOND(시간)
SELECT YEAR(NOW());
SELECT MONTH(CURRENT_DATE());
SELECT DAY(NOW());
SELECT DAYOFMONTH(CURRENT_DATE());
SELECT HOUR(CURTIME());
SELECT MINUTE(CURTIME());
SELECT SECOND(CURRENT_TIME);
SELECT MICROSECOND(CURRENT_TIME());

-- DATEDIFF(날짜1, 날짜2) , TIMEDIFF(날짜1 OR 시간1, 날짜2OR시간2)
SELECT DATEDIFF('2024-02-15', NOW());
SELECT TIMEDIFF('23:23:59', '12:11:10');


-- DAYOFWEEK(날짜), MONTHNAME(), DAYOFYEAR(날짜)
SELECT DAYOFWEEK('2024-03-17');
SELECT MONTHNAME(CURDATE());
SELECT DAYOFYEAR('2024-01-04');

-- LAST_DAY(날짜)
SELECT LAST_DAY('2024-03-14');

-- MAKEDATE(연도, 정수)
SELECT MAKEDATE(2024,74);


-- PERIOD_ADD(연월, 개월수), PERIOD_DIFF(연월1,연월2)
SELECT PERIOD_ADD(202402, 6);
SELECT PERIOD_DIFF(202201, 201812);


-- QUARTER(날짜) : 1~4분기
SELECT QUARTER('2024-03-15');

-- TIME_TO_SEC(시간)
SELECT TIME_TO_SEC('12:11:10');


-- 수학 관련 함수
-- ABS(숫자) 절댓값
SELECT ABS(-100);

-- CEILING(숫자) 올림, FLOOR(숫자)내림, ROUND(숫자)반올림
SELECT CEILING(4.1);
SELECT FLOOR(4.7);
SELECT ROUND(4.3);


-- CONV(숫자, 원래진수, 변환할 진수)
SELECT CONV('A',16,2);
SELECT CONV(8,10,8);

-- MOD(숫자1, 숫자2) or 숫자1% 숫자2 or 숫자1 MOD 숫자2
SELECT MOD(10,4);
SELECT 10%4;
SELECT 10 MOD 4;

-- POW(숫자1, 숫자2), SQRT(숫자)
SELECT POW(10,3);
SELECT SQRT(49);


SELECT POWER(10,4);

-- SIGN(숫자) 양수면1 음수면 -1
SELECT SIGN(100);
SELECT SIGN(0);
SELECT SIGN(-10);


-- TRUNCATE(숫자, 정수)
SELECT TRUNCATE(12345.12345, 3);
SELECT TRUNCATE(12345.12345, -3);


--시스템 정보 함수
-- USER(), DATABASE()
SELECT CURRENT_USER();
SELECT USER();
SELECT SESSION_USER();

SELECT DATABASE();


-- FOUND_ROWS()
SELECT * FROM usertbl;
SELECT FOUND_ROWS();


-- ROW_COUNT()
SELECT buytbl SET price = price *2;
SELECT ROW_COUNT();

-- 순위함수 
-- 회원테이블에서 키가 큰 순으로 정렬하시오.
SELECT * FROM usertbl ORDER BY height DESC;
-- 위 결과에 순위를 표시하시오
SELECT ROW_NUMBER() OVER(ORDER BY height DESC, NAME ASC ) rank, NAME, addr, height 
FROM usertbl;

SELECT DENSE_RANK() OVER(ORDER BY height DESC) RANK , NAME , height FROM usertbl;

SELECT RANK() OVER(ORDER BY height DESC) RANK , NAME , height FROM usertbl;


SELECT NTILE(3) OVER(ORDER BY height DESC) '반번호' , NAME , height FROM usertbl;



-- PIVOT

CREATE TABLE pivotTest(
	uName CHAR(3),
	season CHAR(3),
	amount INT
);

INSERT INTO pivotTest VALUES
('김범수','겨울',10),('윤종신','여름',15),('김범수','가을',25),
('김범수','봄',3),('김범수','봄',37),('윤종신','겨울',40),
('김범수','여름',14),('김범수','겨울',22),('윤종신','여름',64);


SELECT * FROM pivotTest;


SELECT uName,
	SUM(IF(season = '봄', amount, 0)) AS '봄', 
	SUM(IF(season = '여름', amount, 0)) AS '여름', 
	SUM(IF(season = '가을', amount, 0)) AS '가을', 
	SUM(IF(season = '겨울', amount, 0)) AS '겨울',
	SUM(amount) AS '합계' 
FROM pivotTest
GROUP BY uName; 




-- TRIGGER
-- 테이블에 삽입, 수정, 삭제 등의 작업(이벤트)이 발생할 때
-- 자동으로 작동되는 개체이다.
-- 테이블에 부착되는 프로그램 코드이다.
-- 직접 실행시킬 수는 없고 오직 해당 테이블에 이벤트가 발생할 경우에만 실행된다.

USE sqldb;
CREATE TABLE if NOT EXISTS testTBL(
	Id INT,
	txt VARCHAR(10)
);

INSERT INTO testTBL VALUES (1, '이엑스아이디');
INSERT INTO testTBL VALUES (2, '애프터스쿨');
INSERT INTO testTBL VALUES (3, '에이오에이');


-- testTBL에 trigger 부착하기
DROP TRIGGER if EXISTS testTrg;
delimiter //
CREATE TRIGGER testTrg -- 트리거이름
	AFTER DELETE  -- 트리거 동작 시점 지정
	ON testTBL -- 트리거가 부착되는 테이블 지정
	FOR EACH ROW -- 각 행마다 적용
BEGIN 
	SET @msg = '가수 그룹이 삭제됨'; -- 트리거 실행 시 작동되는 코드
END //
delimiter;

SET @msg = '';
INSERT INTO testTBL VALUES (4, '나인뮤지스');
SELECT @msg;

UPDATE testTBL SET txt = '에이핑크' WHERE id = 3;
SELECT @msg;

DELETE FROM testTBL WHERE id = 4; 
SELECT @msg;



-- 회원 테이블에 update나 delete를 시도하면, 수정 또는 삭제된 데이터를
-- 별도의 테이블에 보관하고 변경된 일자와 변경한 사람을 기록하자.

CREATE TABLE backup_userTBL(
	userID CHAR(8) PRIMARY KEY,
	NAME VARCHAR(10) NOT NULL,
	birthYear INT NOT NULL,
	addr CHAR(2) NOT NULL,
	mobile1 CHAR(3),
	mobile2 CHAR(8),
	height SMALLINT,
	mDate DATE,
	modType CHAR(2), -- 변경된 타입, '수정' 또는 '삭제'
	modDate DATE, -- 변경된 날짜
	modUser VARCHAR(256) -- 변경한 사용자
);



-- Update 트리거 작성
DROP TRIGGER if EXISTS backUserTbl_UpdateTrg;
DELIMITER //
CREATE TRIGGER backUserTbl_UpdateTrg
	AFTER update
	ON userTBL
	FOR EACH row
BEGIN
	INSERT INTO backup_userTBL VALUES (OLD.userID, OLD.name, OLD.birthYear,
			OLD.addr, OLD.mobile1, OLD.mobile2, OLD.height, OLD.mDate,
			'수정',CURDATE(), CURRENT_USER());
END //
DELIMITER;




-- Delete 트리거 작성
DROP TRIGGER if EXISTS backUserTbl_DeleteTrg;
delimiter //
CREATE TRIGGER backUserTbl_UpdateTrg
	AFTER delete
	ON userTBL
	FOR EACH row
BEGIN
	INSERT INTO backup_userTBL VALUES (OLD.userID, OLD.name, OLD.birthYear,
			OLD.addr, OLD.mobile1, OLD.mobile2, OLD.height, OLD.mDate,
			'삭제',CURDATE(), CURRENT_USER());
END //
delimiter;

UPDATE usertbl SET addr = '몽고' where userID = 'JKW';
DELETE FROM usertbl WHERE height >= 177;
SELECT * FROM usertbl;
SELECT * FROM backup_UserTBL;