09.13 날짜 함수, 세션 변수, JOIN

날짜 함수

 

adddate, subdate, addtime, subtime

SELECT 
	ADDDATE('2024-09-13', INTERVAL 28 DAY),
	ADDDATE('2024-09-13', INTERVAL 2 MONTH),
	SUBDATE('2024-09-13', INTERVAL 28 DAY),
	SUBDATE('2024-09-13', INTERVAL 2 MONTH),
	ADDTIME('2024-09-13 23:59:50', '0:1:0'),
	SUBTIME('2024-09-14 00:00:50', '0:1:0');

 

실행 결과

 

datediff

SELECT
	DATEDIFF(NOW(), '2025-02-12');

 

year, month, dayofweek

SELECT
	YEAR(CURDATE()), MONTH(CURDATE()), DAYOFWEEK(CURDATE());

 

date_format

SELECT
	DATE_FORMAT('2024-09-13', '%Y%m');

 

last_day

SELECT
	LAST_DAY('2023-2-1'),
	LAST_DAY('2024-2-10');

 

세션변수

연결된 상태까지만 유지되는 변수

SET @num1 := 100;
SET @num2 := 200;

SELECT @num1+@num2;

SELECT @num1 := @num1 + 100; 

SELECT @num1;

 

JOIN

 

inner join

SELECT 
	m.mbr_id,
	m.mbr_grd,
	cc.comm_nm,
	m.mbr_name,
	m.mbr_email
FROM
	members m INNER JOIN comm_code cc
	ON m.mbr_grd = cc.comm_cd;

 

outer join

-- 두 SELECT 문의 결과는 같다.
SELECT
	m.mbr_id,
	m.mbr_name,
	m.mbr_email,
	mll.login_no,
	mll.login_ip
FROM
	members m LEFT JOIN members_login_log mll
	ON m.mbr_id = mll.login_id
WHERE mll.login_no IS NULL;


SELECT
	m.mbr_id,
	m.mbr_name,
	m.mbr_email,
	mll.login_no,
	mll.login_ip
FROM
	members_login_log mll RIGHT JOIN members m
	ON m.mbr_id = mll.login_id
WHERE mll.login_no IS NULL;

 

self join

SELECT
	cco1.comm_cd,
	cco1.comm_cd_nm,
	cco2.comm_cd,
	cco2.comm_cd_nm
FROM
	comm_code_one cco1 JOIN comm_code_one cco2
	ON cco1.comm_cd = cco2.up_comm_cd;