[Day18 - DB] 날짜 함수, 숫자 함수, GROUP BY, CASE, JOIN

날짜 함수

1. CURDATE(), CURRENT_DATE()

: 현재 날짜 반환

SELECT CURDATE();
SELECT CURRENT_DATE();

 

 

1-1. ADDDATE(기준일 INTERVAL 숫자 DAY/MONTH/YEAR), DATE_ADD(기준일 INTERVAL 숫자 DAY/MONTH/YEAR)

: 날짜 형식을 유지하면서 날짜 더하기

: 만약 CURDATE()+1과 같이 그냥 더할 경우 문자열로 출력(형식 유지X)

SELECT  ADDDATE(기준일 INTERVAL 숫자 DAY/MONTH/YEAR) FROM [테이블명];
SELECT  DATE_ADD(기준일 INTERVAL 숫자 DAY/MONTH/YEAR)  FROM [테이블명];

SELECT ADDDATE(CURDATE(), INTERVAL 1 DAY);
SELECT DATE_ADD(CURDATE(), INTERVAL 1 MONTH);
SELECT ADDDATE(CURDATE(), INTERVAL -1 YEAR);

: INTERVAL이 생략되어 숫자만 올 경우 디폴트 값은 DAY로 지정

// 둘 다 동일한 코드
SELECT ADDDATE(CURDATE(), 1);
SELECT ADDDATE(CURDATE(), INTERVAL 1 DAY);

 

 

1-2. DATE_SUB(기준일, INTERVAL 숫자 DAY/MONTH/YEAR)

: 기준일에서 숫자만큼 년/월/일 빼기

// 두 개가 동일한 의미
SELECT CURDATE(), DATE_SUB(CURDATE(), INTERVAL 10 DAY);
SELECT CURDATE(), ADDDATE(CURDATE(), INTERVAL -10 DAY);

 

 

2. CURTIME(), CURRENT_TIME()

: 현재 시간 반환

SELECT CURTIME();
SELECT CURRENT_TIME();

 

 

2-1. ADDTIME(기준 날짜, 더할 시간)

: 시간 형식을 유지하면서 시간 더하기

: 더할 시간을 문자형의 시간 형식으로 작성

SELECT CURTIME(), ADTIME(CURTIME(), '1:10:5');

 

3. NOW()

: 현재 날짜 및 시간 반환

SELECT NOW();

 

 

3-1. ADDTIME(기준일, 더할 날짜 및 시간)

: 날짜 및 시간 형식을 유지하면서 날짜 및 시간 더하기 가능

: 더할 시간을 문자형의 날짜 및 시간 형식으로 작성

SELECT NOW(), ADDTIME(NOW(), '2 1:10:5');

 

 

4. DATEDIFF(날짜, 기준일)

: 기준일로부터 날짜까지 얼마나 지났는지 day를 반환

SELECT hiredate, DATEDIFF(CURDATE(), hiredate) FROM emp;
SELECT DATEDIFF(CURDATE(), '1999-06-10');

 

 

5. DATE_FORMAT(날짜, 지정 날짜 형식)

: 날짜를 지정한 형식으로 출력

: 형식 지정 시 큰따옴표 사용하며, 대문자와 소문자의 출력 결과가 상이함

-- 날짜를 월/일/년 순으로 출력
SELECT DATE_FORMAT('2017-06-15', "%M %D %Y");

-- 현재 일시에서 월 만 출력
SELECT DATE_FORMAT(NOW(), "%M")

-- 현재 일시에서 월/일/년/시/분/초/요일 출력
SELECT DATE_FORMAT(NOW(), "%M %d %Y %H %i %s %W")

형식 : 

[연] : %Y(2023), %y(23)
[월] : %M(September), %m(09), %b(Sep), %c(9)

[일] : %d(05), %e(5)

[요일] : %W(Tuesday), %w(Tue)

[시] : %H(24시간 기준 : 13), %h(12시간 기준 : 1)

[분] : %i

[초] : %S, %s

[기타] %r : hh:mm:ss AM, PM

 

 

6. DAY(날짜), DAYOFMONTH(날짜)

: 날짜에서 일 추출

-- emp 테이블에서 입사일과 입사일의 일만 반환
SELECT hiredate, Day(hiredate) FROM emp;
SELECT hiredate, DAYOFMONTH(hiredate) FROM emp;

 

 

7. DAYNAME(날짜), DAYOFWEEK(날짜)

: 날짜에서 요일 추출

: DAYNAME은 문자열로, DAYOFWEEK는 일요일이 1인 기준 숫자로 반환

-- emp 테이블에서 hiredate의 요일 반환
SELECT hiredate, DAYNAME(hiredate) FROM emp;
-- emp 테이블에서 hiredate의 요일을 숫자로 반환
Select hiredate, DAYOFWEEK(hiredate) FROM emp;

 

 

8. HOUR()시 / MINUT() 분 / SECOND() 초 출력

: 일시에서 시/분/초 출력 가능

SELECT NOW(), HOUR(NOW());
SELECT NOW(), MINUTE(NOW());
SELECT NOW(), SECOND(NOW());

 

 

9. EXTRACT(년/월/일/요일 FROM 날짜)

: 시간 및 날짜에서 원하는 요소 추출

-- CURDATE()에서 월/연/일/주/분기 추출
SELECT CURDATE(), EXTRACT(MONTH FROM CURDATE()) AS MONTH;
SELECT CURDATE(), EXTRACT(YEAR FROM CURDATE()) AS YEAR;
SELECT CURDATE(), EXTRACT(DAY FROM CURDATE()) AS DAY;
SELECT CURDATE(), EXTRACT(WEEK FROM CURDATE()) AS WEEK;
SELECT CURDATE(), EXTRACT(QUARTER FROM CURDATE()) AS QUARTER;

-- NOW()에서 시/분/초 추출
SELECT NOW(), EXTRACT(HOUR FROM NOW()) AS HOUR;
SELECT NOW(), EXTRACT(MINUTE FROM CURDATE()) AS MINUTE;
SELECT NOW(), EXTRACT(SECOND FROM NOW()) AS SECOND;

-- CURDATE()에서 월과달을 같이 출력(형식 유지x)
SELECT CURDATE(), EXTRACT(YEAR_MONTH FROM CURDATE()) AS "YEAR_MONTH";

 

 

10. TIME_TO_SEC(시간)

: 시간을 초로 변환

-- CURTIME()를 초로 변환
SELECT CURTIME(), TIME_TO_SEC(CURTIME());

 

 

11. TIMEDIFF(시간1, 시간2)

: 시간1을 기준으로 시간2와 얼마나 차이가 나는지 시간 형식으로 출력

-- CURTIME()과 작성 시간의 차이
SELECT CURTIME(), TIMEDIFF(CURTIME(), '08:48:27');

 

---

숫자 함수

 

1. COUNT(칼럼명)

: 조건에 만족하는 레코드(행) 수

: 조건이 없을 경우 해당 칼럼 행의 수를 출력

: NULL 은 COUNT 되지 않음

-- emp 테이블의 모든 행 수 출력
SELECT COUNT(*) FROM emp;

-- emp 테이블의 comm 수 출력, NULL은 포함되지 않음
SELECT COUNT(comm) FROM emp;

-- emp 테이블의 deptno=10인 행 수
SELECT COUNT(*) FROM emp WHERE deptno=10;

 

 

2. SUM(칼럼명)

: 칼럼의 합계 출력

-- emp 테이블의 sal 합계 출력
SELECT SUM(sal) FROM emp;

-- emp 테이블의 deptno=10인 sal 합계 출력
SELECT SUM(sal) FROM emp WHERE deptno=10;

 

 

3. AVG(칼럼명)

: 칼럼의 평균 출력

: NULL은 포함되지 않기 NULL이 아닌 것 끼리의 평균이 되기 때문에 NULL을 포함하고자 한다면 NULL을 0으로 변환하여 출력해야 함

-- emp 테이블의 sal 평균 출력
SELECT AVG(sal) FROM emp;

-- emp 테이블의 deptno=10인 sal 평균 출력
SELECT AVG(sal) FROM emp WHERE deptno=10;

-- emp 테이블의 comm의 평균 출력(NULL은 0으로 변경 후 평균 계산)
SELECT AVG(IFNULL(comm, 0)) FROM emp;

 

 

4. MAX(칼럼명), MIN(칼럼명)

: 최대, 최소 구하기

-- emp 테이블에서 sal 중 최대값 출력
SELECT MAX(sal) FROM emp;

-- emp 테이블에서 sal 중 최소값 출력
SELECT MIN(sal) FROM emp;

 

---

GROUP BY

 

1. 칼럼 그룹화

SELECT 칼럼명 FROM [테이블명] GROUP BY 그룹화 할 칼럼명(alias로 지정한 별칭도 사용 가능);

-- emp 테이블의 deptno 별로 칼럼 출력
SELECT deptno, COUNT(*), SUM(sal) FROM emp GROUP BY deptno;

-- emp 테이블에서 deptno, job을 그룹화
SELECT deptno, job, COUNT(*), SUM(sal) FROM emp GROUP BY deptno, job;

-- student 테이블에서 메인 학과별 학생 수 조회
SELECT deptno1, COUNT(*) FROM student GROUP BY deptno1;

-- student 테이블에서 학년별 평균 키 조회
SELECT grade, format(AVG(height),1) FROM student GROUP BY grade;

 

 

2. 조건 처리 후 칼럼 그룹화

SELECT 칼럼명 FROM [테이블명]  WHERE 조건식 GROUP BY 그룹화 할 칼럼명;

 

 

3. 칼럼 그룹화 후 조건 처리

SELECT 칼럼명 FROM [테이블명] GROUP BY 그룹화 할 칼럼명 HAVING 조건;

-- emp 테이블에서 deptno 별 sal의 평균 중 평균이 2000 이상인것만 출력
SELECT deptno, avg(sal) FROM emp GROUP BY deptno HAVING AVG(sal) >=2000;

 

 

4. 조건 처리 후 칼럼 그룹화 한 후 조건 처리

SELECT 칼럼명 FROM [테이블명] WHERE 조건절 GROUP BY 그룹화 할 칼럼명 HAVING 조건;

 

 

5. ORDER BY가 존재하는 경우

SELECT 컬럼 FROM 테이블 [WHERE 조건식]
GROUP BY 그룹화할 컬럼 [HAVING 조건식] ORDER BY 컬럼1 [, 컬럼2, 컬럼3 ...];

-- student 테이블에서 각 학과와 학년별 평균 몸무게가 50 이상이고, 최대/최소 몸무게를 조회 후 정렬
SELECT deptno1, grade, COUNT(*), format(AVG(weight),1), MIN(weight), MAX(weight) 
FROM student 
GROUP BY deptno1, grade 
HAVING AVG(weight) > 50 
ORDER BY deptno1, grade;

 

---

CASE 문

 

SELECT 
CASE when 조건1 then 결과1
           when 조건2 then 결과2
           when 조건3 then 결과3
           else 결과4 // 생략 가능
END
FROM 테이블명;

 

[예제1]

-- emp 테이블에서 deptno에 따른 결과를 다르게 출력
SELECT empno, ename, deptno,
	CASE when deptno=10 then 'COOUNTINT'
		 when deptno=20 then 'RESEARCH'
		 when deptno=30 then 'SALSE'
		 when deptno=40 then 'OPERATIONS'
	END AS dname
FROM emp;

-- 비교 대상을 한 번만 작성 가능
SELECT empno, ename, deptno,
	CASE deptno 
    	 when 10 then 'COOUNTINT'
		 when 20 then 'RESEARCH'
		 when 30 then 'SALSE'
		 when 40 then 'OPERATIONS'
	END AS dname
FROM emp;

[예제2]

-- studnet 테이블에서 주민번호를 이용해 성별을 조회
-- 주민번호 7번째 숫자가 1이나 3이면 MAN, 2나 4면 WOMAN으로 조회
-- 이름, 주민번호, 성별(MAN, WOMAN)
SELECT name "이름", jumin "주민번호", 
	CASE when SUBSTR(jumin, 7, 1)=1 OR SUBSTR(jumin, 7, 1)=3 then 'MAN'
		when SUBSTR(jumin, 7, 1)=2 OR SUBSTR(jumin, 7, 1)=4 then 'WOMAN'
	END "성별"
FROM student;

-- 비교 대상을 한 번만 작성 가능
SELECT name "이름", jumin "주민번호", 
	CASE SUBSTR(jumin, 7, 1) 
		when '1' then 'MAN'
		when '3' then 'MAN'
		when '2' then 'WOMAN'
		when '4' then 'WOMAN'
	END "성별"
FROM student;

[예제3]

-- student 테이블에서 1전공이 101번인 학생의 이름, 전화번호, 지역을 조회
-- 단, 지역은 전화번호의 지역번호로 구분
-- 02 : 서울, 031 : 경기. 051: 부산. 052:울산, 055:경남
SELECT name "이름", tel "전화번호",
	CASE SUBSTR(tel, 1, INSTR(tel, ')')-1) 
		when '02' then '서울'
		when '031' then '경기'
		when '051' then '부산'
		when '052' then '울산'
		when '055' then '경남'
	END "지역"
FROM student;

[예제4]

-- student 테이블에서 생년월일을 참조하여 태어난 달과 분리를 조회하여
-- 태어난 달이 1~3월이면 14분기, 4~ 6월이면 2/4분기, 7~9월이면 3/4분기, 10월~12월이면 4/4분기
-- 이름, 생년월일, 태어난 달, 분기 출력
SELECT name "이름", birthday "생년월일", MONTH(birthday),
	CASE when MONTH(birthday) BETWEEN 1 AND 3 then '1/4분기'
		when MONTH(birthday) IN(4,5,6) then '2/4분기'
		when MONTH(birthday) IN(7,8,9) then '3/4분기'
		when MONTH(birthday) IN(10,11,12) then '4/4분기'
	END "분기"
FROM student;

---

JOIN

 

1. JOIN이란?

: 두 개 이상의 테이블을 서로 연결하여 데이터를 검색할 때 사용하는 방법

: 테이블의 명을 그대로 가져가는 것이 아닌 중복 테이블명 고려를 위해 alias를 사용

: 기본 조인은 INNER JOIN에 해당

[JOIN 기본 형식] JOIN = INNER JOIN
SELECT 테이블1.칼럼, 테이블2.칼럼 ...
FROM 테이블1, 테이블2
WHERE 조건절;

SELECT 테이블1.칼럼, 테이블2.칼럼 ...
FROM 테이블1 (INNER) JOIN 테이블2
ON 조건
WHERE 추가 조건;

 

1. JOIN을 하기 위해 테이블을 2개 생성

-- CREATE TABLE : 테이블 생성
-- DROP TABLE : 테이블이 있으면 삭제
DROP TABLE test1;
CREATE TABLE test1(
	A VARCHAR(10),
	B VARCHAR(10)
);

DROP TABLE test2;
CREATE TABLE test2(
A VARCHAR(10),
C VARCHAR(20),
D VARCHAR(20));

-- TABLE에 데이터 삽입
INSERT INTO test1 VALUES('a1', 'b1');
INSERT INTO test1 VALUES('a2', 'b2');

INSERT INTO test2 VALUES('a3', 'c3', 'd3');
INSERT INTO test2 VALUES('a4', 'c4', 'd4');
INSERT INTO test2 VALUES('a5', 'c5', 'd5');

 

2. 테이블을 JOIN하여 테이블을 조회

: JOIN을 할 경우 많은 데이터가 생성됨(각 테이블의 데이터의 수를 곱한만큼 생성)

-- t1에 있는 모든 컬럼과 t2에 있는 모든 컬럼을 조회
SELECT t1.*, t2.*
FROM test1 t1, test2 t2;

-- 그냥 테이블을 조회할 경우 두 테이블의 행 수를 곱한만큼 경우의 수 테이블이 생성
/* 출력 결과
A.	B.	A.	B.	C
a1	b1	a3	c3	d3
a2	b2	a3	c3	d3
a1	b1	a4	c4	d4
a2	b2	a4	c4	d4
a1	b1	a5	c5	d5
a2	b2	a5	c5	d5
*/

 

[다른 예제]

-- emp 테이블의 deptno과 dept 테이블의 deptno가 동일한 dname을 출력
SELECT e.empno, e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno;

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

-- student, exam_01 테이블을 이용해 학번, 이름, 시험점수 조회
SELECT s.studno, s.name, e.total
FROM student s, exam_01 e
WHERE s.studno=e.studno;

SELECT s.studno, s.name, e.total
FROM student s JOIN exam_01 e
ON s.studno=e.studno
ORDER BY 3 desc;


-- student, exam_01, hakjum 테이블을 이용하여 학번, 이름, 시험 점수, 학점 조회
SELECT s.studno, s.name, e.total, h.grade
FROM student s, exam_01 e, hakjum h;
WHERE (s.studno=e.studno) AND (e.total BETWEEN h.min_point AND h.max_point);

SELECT s.studno, s.name, e.total, h.grade
FROM student s JOIN exam_01 e ON s.studno=e.studno 
JOIN hakjum h ON e.total BETWEEN h.min_point AND h.max_point;


-- gogakk, gift 테이블을 이용해 고객의 모든 정보와 고객이 본인의 포인트로 받을 수 있는 가장 좋은 상품 조회
SELECT g.*, t.gname
FROM gogak g JOIN gift t
ON g.point BETWEEN g_start AND g_end
ORDER BY g.point;

SELECT g.*, t.gname
FROM gogak g, gift t
WHERE g.point BETWEEN g_start AND g_end
ORDER BY g.point;


-- emp2, p_grade 테이블을 이용하여 이름, 직위, 급여, 같은 직급의 최소 급여, 최대 급여 조회
SELECT e.name, p.position, p.s_pay, p.e_pay
FROM emp2 e JOIN p_grade p
ON p.position = e.position


-- emp2 , p_grade 테이블을 이용하여 이름, 직위, 나이, 본인의 나이에 해당하는 예상 직급 조회
SELECT e.name, p.position, YEAR(CURDATE())-YEAR(e.birthday) "나이", p.position
FROM emp2 e, p_grade p
WHERE YEAR(CURDATE())-YEAR(e.birthday) BETWEEN p.s_age AND p.e_age;


-- gogak, gift 테이블을 이용하여 노트북을 받을 수 있는 고객긔 이름, 포인트, 상품명 조회
SELECT g.gname, g.point, t.gname
FROM gogak g, gift t
WHERE  g.point >= t.g_start AND t.gname='노트북';

SELECT g.gname, g.point, t.gname
FROM gogak g JOIN gift t ON g.point >= t.g_start
WHERE t.gname='노트북';

---

2. LEFT JOIN이란?

: 왼쪽 테이블을 기준으로 일치하는 행만 결합되고, 일치하지 않는 부분은 null 값으로 채워짐

: 즉, 조건절을 만족하지 않더라도 왼쪽 테이블에 있는 모든 행을 가져옴

SELECT 테이블1.칼럼, 테이블2.칼럼 ...
FROM 테이블1 LEFT JOIN 테이블2
ON 조건
WHERE 추가 조건;

SELECT s.studno, s.name, p.name
FROM student s LEFT JOIN professor p
ON s.profno=p.profno;

---

3. RIGHT JOIN이란?

: 오른쪽 테이블을 기준으로 일치하는 행만 결합되고, 일치하지 않는 부분은 null 값으로 채워짐

: 즉, 조건절을 만족하지 않더라도 오른쪽 테이블에 있는 모든 행을 가져옴

SELECT 테이블1.칼럼, 테이블2.칼럼 ...
FROM 테이블1
RIGHT JOIN 테이블2
ON 조건
WHERE 추가 조건;

SELECT s.studno, s.name, p.name
FROM student s RIGHT JOIN professor p
ON s.profno=p.profno;

---

4. FULL JOIN이란?

: FULL JOIN과 RIGHT JOIN의 테이블 값을 모두 유지

: MYSQL에서는 FULL JOIN을 지원하지 않아, UNION을 사용

SELECT 테이블1.칼럼, 테이블2.칼럼 ...
FROM 테이블1
FULL JOIN 테이블2
ON 조건
WHERE 추가 조건
--------------------------------
SELECT 테이블1.칼럼, 테이블2.칼럼 ...
FROM 테이블1
RIGHT JOIN 테이블2
ON 조건
WHERE 추가 조건
UNION
SELECT 테이블1.칼럼, 테이블2.칼럼 ...
FROM 테이블1
RIGHT JOIN 테이블2
ON 조건
WHERE 추가 조건;

SELECT s.studno, s.name, p.name
FROM student s LEFT JOIN professor p
ON s.profno=p.profno
UNION
SELECT s.studno, s.name, p.name
FROM student s RIGHT JOIN professor p
ON s.profno=p.profno;

---

5. SELF JOIN이란?

: 하나의 테이블을 가지고 조인을 진행

: 하나의 테이블을 가지고 조인을 진행하기 때문에 각 테이블에 alias를 지정하여 이름을 다르게 설정해야 함

: ON 조건절에서 조인하는 칼럼명이 동일할 경우 USING(칼럼명)으로 나타낼 수 있음

SELECT 테이블1.칼럼, 테이블1.칼럼
FROM 테이블1
SELF JOIN 테이블1
ON 조건
WHERE 추가 조건

-- dept2 테이블을 이용하여 부서의 모든 정보와 각 부서의 상위 부서명을 조회
SELECT d.*, de.area
FROM dept2 d LEFT JOIN dept2 de
ON d.pdept=de.dcode;

SELECT d.*, de.*
FROM dept2 d, dept2 de
ORDER BY 1;

-- emp 테이블을 이용하여 직원의 사번, 이름, 담당 매니저(MGR)사번과 이름 조회
SELECT e1.empno, e1.ename, e1.mgr, e2.ename
FROM emp e1, emp e2
WHERE e1.mgr=e2.mgr;

---

6. NATURL JOIN이란?

: 두 테이블에서 동일한 컬럼명( 두 테이블 간의 동일한 이름, 타입을 가진 컬럼)을 갖는 컬럼은 모두 조인

: 위 SELF JOIN에서 USING을 사용하는 대신 NATURAL JOIN으로 변경 가능

SELECT 테이블명1.칼럼명1, 테이블명2.칼럼명2
FROM 테이블명1 NATURAL JOIN 테이블명2

SELECT e.*, d.*
FROM emp e JOIN dept d ON e.deptno=d.deptno;

-- 동일한 칼럼명이기 때문에 NATURAL JOIN으로 변경 가능
SELECT e.*, d.dname
FROM emp e  NATURAL JOIN dept d;

---

[심화 예제]

-- student, department 테이블을 이용하여 학번, 이름, 제1전공명, 제2전공명 조회
-- 제2전공이 NULL인 학생들은 NULL인 상태로 나오도록 출력
SELECT s.studno, s.name, d1.dname, d2.dname
FROM student s JOIN department d1 ON s.deptno1=d1.deptno 
LEFT JOIN department d2 ON s.deptno2=d2.deptno;

-- student, department 테이블을 이용하여 컴퓨터정보학부에 속한 학생들의 학번, 이름, 학과 조회
SELECT s.studno, s.name, d.dname
FROM student s JOIN department d
ON s.deptno1=d.deptno AND d.part=100;

SELECT s.studno, s.name, d2.dname, d1.dname
FROM student s JOIN department d1 ON s.deptno1=d1.deptno 
JOIN department d2 ON (d1.part=d2.deptno AND d2.dname='컴퓨터정보학부');

-- student, department 테이블에서 제1전공이나 제2전공으로 인해 '전자제어관'에서 수업을 듣는 학생들 조회
SELECT s.studno, s.name, s.deptno1, s.deptno2, d1.build
FROM student s JOIN department d1
ON s.deptno1=d1.deptno OR s.deptno2=d1.deptno
WHERE d1.build='전자제어관';

SELECT s.studno, s.name, s.deptno1, s.deptno2, d1.build, d2.build
FROM student s JOIN department d1 ON s.deptno1=d1.deptno 
LEFT JOIN department d2 ON s.deptno2=d2.deptno
WHERE d1.build='전자제어관' OR d2.build='전자제어관';

-- emp 테이블에서 자신보다 입사일이 빠른 사람의 수 조회 (NULL 값을 포함하지 않음)
SELECT e1.empno, e1.ename, e1.hiredate, COUNT(e2.hiredate)
FROM emp e1 LEFT JOIN emp e2
ON e1.hiredate > e2.hiredate
GROUP BY e1.empno, e1.ename
ORDER BY 1;