(2주차 8일) SQL 함수, 조인, 서브쿼리

MariaDB

> 함수 실습(2)

• 문자열 함수

/*
* 문자열 함수
*/
-- ASCII(), CHAR()
SELECT ASCII('a'), CHAR(97);

-- BIT_LENGTH(), CHAR_LENGTH(), LENGTH() 함수
-- MariaDB는 기본적으로 UTF-8 코드를 사용하므로 영문은 1Byte, 한글은 3Byte를 할당함
SELECT BIT_LENGTH('abc'), CHAR_LENGTH('abc'), LENGTH('abc');
SELECT BIT_LENGTH('가나다'), CHAR_LENGTH('가나다'), LENGTH('가나다');

-- CONCAT(), CONCAT_WS()
SELECT CONCAT('2024', '05', '20');
SELECT CONCAT_WS('/', '2024', '05', '20');

-- usertbl 테이블에서 아이디, 이름, 전화번호를 조회
SELECT userID, NAME, 
--		 CONCAT(mobile1, mobile2) AS mobile FROM usertbl;
		 CONCAT_WS(')',mobile1, mobile2) AS mobile FROM usertbl;
		 
SELECT CONCAT(emp_name, '님의 급여는 ', salary, '입니다.') FROM employee;

-- 문자열 위치 반환: ELT(), FIELD(), FIND_IN_SET, INSTR(), LOCATE()
SELECT ELT(2, '하나', '둘', '셋'), -- 첫 인자의 위치값 리턴
		 FIELD('둘', '하나', '둘', '셋'), -- 첫 인자의 문자열 인자 위치 반환
		 FIND_IN_SET('둘', '하나,둘,셋'), -- 문자열을 콤마로 구분된 리스트에서 찾아 위치 반환
		 INSTR('하나둘셋', '둘'), -- 문자열에서 부분 문자열 시작 위치 반환
		 LOCATE('둘', '하나둘셋'); -- INSTR과 반대
		 
-- employee 테이블에서 이메일의 '@' 위치값 리턴
SELECT INSTR(email, '@') FROM employee;

-- INSERT(): 기준 문자열 위치부터 길이만큼 삭제하고 문자열 추가
SELECT INSERT('ABCDEFGHI', 3, 4, '@@@@'),
		 INSERT('ABCDEFGHI', 2, 3, '@@@@');
		 
SELECT emp_name, 
		 INSERT(emp_no, 8, 7, '*******') FROM employee;
		 
-- LEFT(), RIGHT()
SELECT LEFT('ABCDEFGHI', 3), RIGHT('ABCDEFGHI', 3);

SELECT emp_name, email, LEFT(email, INSTR(email, '@') - 1) AS id FROM employee;

-- UPPER(), LOWER()
SELECT UPPER('abcDEF'), LOWER('abcDEF');

-- LPAD(), RPAD(): 문자열을 길이만큼 늘린 후, 빈 곳에 문자열 추가
SELECT LPAD('HELLO', 4), LPAD('HELLO', 10, '#');

-- employee 테이블에서 사원명, 주민등록번호 출력
SELECT emp_name, RPAD(LPAD(emp_no, 8), 14, '*') FROM employee;

-- LTRIM(), RTRIM(), TRIM()
SELECT LTRIM('     HELLO     '),
		 RTRIM('     HELLO     '),
		 TRIM('     HELLO     ');
		 
SELECT TRIM(BOTH ' ' FROM '     HELLO     '),
		 TRIM(BOTH 'Z' FROM 'ZZZZZHEZLLO  ZZ'),
		 TRIM(LEADING 'Z' FROM 'ZZZZZHEZLLO  ZZ'),
		 TRIM(TRAILING 'Z' FROM 'ZZZZZHEZLLO  ZZ');
		 
-- REPEAT(), SPACE()
SELECT REPEAT('HELLO', 3),
		 SPACE(10),
		 CONCAT('Maria', SPACE(5), 'DB'),
		 REVERSE('HELLO');

-- REPLACE()
SELECT REPLACE('hong123@naver.com', 'naver.com', 'gmail.com');

SELECT emp_name, 
		 REPLACE(email, '@kh.or.kr', '') AS id,
		 REPLACE(email, 'kh.or.kr', 'naver.com') AS email FROM employee;
		 
-- SUBSTRING()
SELECT SUBSTRING('대한민국만세', 3), SUBSTRING('대한민국만세', 3, 2);

SELECT emp_name, 
		 SUBSTRING(email, 1, INSTR(email, '@') - 1) AS 'id',
		 IF(SUBSTRING(emp_no, 8, 1) = 1, '남자', '여자') AS 'gender' FROM employee;
		 
SELECT SUBSTRING_INDEX('cafe.naver.com', '.', 2),
		 SUBSTRING_INDEX('cafe.naver.com', '.', -2);
		 
SELECT SUBSTRING_INDEX(email, '@', 1) FROM employee;

 

• 수학 함수

/*
* 수학 함수
*/
-- ABS()
SELECT ABS(100), ABS(-100), ABS(10.9), ABS(-10.9);

-- CEILING(), FLOOR(), ROUND(), TRUNCATE()
SELECT CEILING(4.3), FLOOR(4.7), ROUND(4.53, 1), TRUNCATE(16.13, -1);

-- MOD()
SELECT MOD(10, 3), 7 % 8, 157 MOD 14;

-- RAND()
SELECT RAND();

-- POW(), SQRT(), SIGN()
SELECT POW(2, 3), SQRT(9), SIGN(15), SIGN(0), SIGN(-33);

• 날짜 및 시간 함수

/*
* 날짜 및 시간 함수
*/
-- ADDDATE(), SUBDATE(), ADDTIME(), SUBTIME()
SELECT ADDDATE('2024-05-20', INTERVAL 31 DAY),
		 ADDDATE('2024-05-20', INTERVAL 31 MONTH),
		 ADDDATE('2024-05-20', INTERVAL 31 YEAR);
		 
SELECT SUBDATE('2024-05-20', INTERVAL 31 DAY);

SELECT ADDTIME('2024-05-10 09:00:00', '01:10:30'),
		 ADDTIME('09:00:00', '02:20:50');
		 
SELECT SUBTIME('2024-05-10 09:00:00', '01:10:30'),
		 SUBTIME('09:00:00', '02:20:50');
		 
SELECT emp_name, hire_date,
		 ADDDATE(hire_date, INTERVAL 3 MONTH) FROM employee;
		 
-- CURDATE(), CURTIME(), NOW(), SYSDATE(): 현재 날짜 및 시간 출력
SELECT CURDATE(), CURTIME(), NOW(), SYSDATE();

-- YEAR(), MONTH(), DAY(), HOUR(), MINUTE(), SECOND(), MICROSECOND(): 특정 정보만 출력
SELECT YEAR(NOW()), MONTH(NOW()), DAY(NOW()), HOUR(NOW()),
		 MINUTE(NOW()), SECOND(NOW()), MICROSECOND(NOW());
		 
-- DAYDIFF(), TIMEDIFF(): 날짜 및 시간 인자간의 차이 반환
SELECT DATEDIFF(CURDATE(), '2022-05-20'),
		 TIMEDIFF(CURTIME(), '09:00:00');
		 
SELECT emp_name, hire_date, 
		 DATEDIFF(IF(ent_date IS NULL, CURDATE(), ent_date), hire_date) FROM employee;

-- DAYOFWEEK(), MONTHNAME(), DAYOFYEAR(), LAST_DAY()		 
SELECT DAYOFWEEK(CURDATE()),
		 MONTHNAME('2024-04-20'),
		 DAYOFYEAR(CURDATE()),
		 LAST_DAY(CURDATE());
		 
SELECT emp_name, hire_date, LAST_DAY(CURDATE()) FROM employee;

-- MAKEDATE(), MAKETIME(), PERIOD_ADD(), PERIOD_DIFF(), QUARTER(), TIME_TO_SEC()
SELECT MAKEDATE(2024, 100), MAKETIME(22, 58, 25);

SELECT PERIOD_ADD(202403, 11), PERIOD_DIFF(202408, 202305);

SELECT QUARTER(CURDATE());

SELECT TIME_TO_SEC(CURTIME());

• 윈도우 함수

/*
* 윈도우 함수
*/
-- #순위 함수 ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()
-- ROW_NUMBER: uesrtbl 테이블에서 키가 큰 순으로 행넘버 부여
SELECT ROW_NUMBER() OVER(ORDER BY height DESC, NAME ASC) AS 'num',
		 NAME, addr, height FROM usertbl;
		 
-- 지역별로 키를 기준으로 행넘버 부여하여 주소, 순위, 이름, 키를 조회
SELECT ROW_NUMBER() OVER(PARTITION BY addr ORDER BY height DESC) AS num,
		 addr, NAME, height FROM usertbl ORDER BY addr;
		 
-- RANK: 동순위 이후 rank를 동순위만큼 증가
SELECT RANK() OVER(ORDER BY height DESC) AS rank,
		 addr, NAME, height FROM usertbl ORDER BY addr;
		 
SELECT RANK() OVER(PARTITION BY addr ORDER BY height DESC) AS num,
		 addr, NAME, height FROM usertbl ORDER BY addr;
		 
-- DENSE_RANK: 동순위 이후 rank를 1 증가
SELECT DENSE_RANK() OVER(ORDER BY height DESC) AS rank,
		 addr, NAME, height FROM usertbl ORDER BY addr;
		 
-- NTILE: 정렬 후 데이터를 n분할
SELECT NTILE(2) OVER(ORDER BY height DESC) AS 'group',
		 addr, NAME, height FROM usertbl ORDER BY addr;
		 
-- employee 테이블에서 급여가 높은 순서대로 rank 부여하고 순위, 직원명, 급여 조회
SELECT RANK() OVER(ORDER BY salary DESC) AS 'rank',
		 emp_name, salary FROM employee ORDER BY salary DESC;
		 
SELECT RANK() OVER(PARTITION BY job_code ORDER BY salary DESC) AS 'rank',
		 emp_name, job_code, salary FROM employee ORDER BY salary DESC;
		 
-- #분석 함수 LEAD(), LAG(), FIRST_VALUE(), LAST_VALUE(), CUME_DIST()
-- 키 순서대로 내림차순 정렬 후 다음 사람과 키 차이를 조회
SELECT NAME, addr, height,
		 height - LEAD(height, 1) OVER(ORDER BY height DESC) FROM usertbl;
		 
-- 키 순서대로 내림차순 정렬 후 이전 사람과 키 차이를 조회
SELECT NAME, addr, height,
		 height - LAG(height, 1) OVER(ORDER BY height DESC) FROM usertbl;
		 
-- 지역별로 가장 키가 큰 사람과의 키 차이를 조회
SELECT addr, NAME, height,
		 FIRST_VALUE(height) OVER(PARTITION BY addr ORDER BY height DESC) - height AS 'diff' 
         FROM usertbl;

 

> 조인(JOIN)과 UNION 연산자(1)

/*
* 조인(JOIN)
*/
-- #내부 조인(INNER JOIN)
-- 각 사원들의 사번, 직원명, 부서 코드, 부서명을 조회
SELECT emp_id, emp_name, dept_code, b.dept_title
FROM employee a INNER JOIN department b ON a.dept_code = b.dept_id;

-- 각 사원들의 사번, 직원명, 직급 코드, 직급명을 조회
SELECT emp_id, emp_name, a.job_code, b.job_name
FROM employee a INNER JOIN job b ON a.job_code = b.job_code
WHERE b.job_name = '대리';

-- #자연 조인(NATURAL JOIN) - 참고!

SELECT emp_id, emp_name, job_code, job_name
FROM employee NATURAL JOIN job;

/*
* 연습문제
*/
-- usertbl 테이블과 buytbl 테이블을 조인하여
-- JPY라는 아이디를 가진 회원의 이름, 주소, 연락처, 주문 상품 이름을 조회
SELECT NAME, addr, CONCAT(mobile1, mobile2) AS mobile, b.prodName
FROM usertbl a INNER JOIN buytbl b ON a.userID = b.userID
WHERE a.userID = 'JYP';

-- employee 테이블과 department 테이블을 조인하여
-- 보너스를 받는 사원들의 사번, 직원명, 보너스, 부서명을 조회
SELECT emp_id, emp_name, bonus, b.dept_title
FROM employee a LEFT OUTER JOIN department b ON a.dept_code = b.dept_id
WHERE bonus IS NOT NULL;

-- employee 테이블과 department 테이블을 조인하여
-- 인사관리부가 아닌 사원들의 직원명, 부서명, 급여를 조회
SELECT emp_name, b.dept_title, salary
FROM employee a LEFT OUTER JOIN department b ON a.dept_code = b.dept_id
WHERE dept_code != 'D1' OR dept_code IS NULL;
-- where문에서는 NULL <> 1이 true로 평가되지 않으므로 where문의 결과값에서 필터링됨

-- employee 테이블과 department 테이블, job 테이블을 조인하여
-- 사번, 직원명, 부서명, 직급명 조회
SELECT emp_id, emp_name, b.dept_title, c.job_name
FROM employee a LEFT OUTER JOIN department b ON a.dept_code = b.dept_id
					 LEFT OUTER JOIN job c ON a.job_code = c.job_code;