[Database][SQL] 함수(replace, round, cast) / 조건문 / NULL

🍁 REPLACE

• Replace() : 치환하기

SELECT REPLACE('컬럼1에 찾을값이 있다', '찾을값', '바꿀값') AS msg;

REPLACE 문제

문제1

• 회원의 성씨 중에 이씨를 리씨로 바꾸기

SELECT mem_name,
REPLACE(mem_name , '이', '리') as rep1,
REPLACE(SUBSTRING(mem_name,1,1),'이','리') AS rep2,
SUBSTRING(mem_name,2,2) AS nm,
CONCAT(REPLACE(SUBSTRING(mem_name,1,1),'이','리'),SUBSTRING(mem_name,2,2)) AS rep_name
FROM member 
WHERE SUBSTRING(mem_name,1,1) = '이';

🍁 ROUND

• Round() : 반올림 함수

SELECT ROUND(123.567, 0) AS r1,
 ROUND(123.567, 1) AS r2,
 ROUND(123.567, 2) AS r3,
 ROUND(123.567, 3) AS r4,
 ROUND(123.567, 4) AS r5,
 ROUND(123.567, -1) AS r6,
 ROUND(123.567, -2) AS r7,
 ROUND(123.567, -3) AS r8,
 ROUND(123.567, -4) AS r9;

ROUND 문제

문제 1

• 상품정보에서 상품명, 원가율 조회하기
• 원가율 = 매입가/판매가의 백분율 값
• 원가율을 소숫점 2자리까지 표현

SELECT prod_name, ROUND((prod_cost/prod_sale)*100, 2) AS 원가율
FROM prod;

---

문제 2

• 회원 중에 취미가 수영인 회원이 구매한 상품들을 조회하기
• 회원 거주 지역이 서울, 대전, 광주인 회원
• 상품분류중에 피혁이라는 분류에 속해있는 상품을 구매한 회원
• 조회컬럼 : 상품명, 상품분류명, 원가(소숫점 2자리)

SELECT prod_name, 
			(SELECT lprod_nm 
				FROM lprod 
					WHERE lprod_gu = prod_lgu) AS lprod_nm,
			 ROUND((prod_cost/prod_sale)*100, 2) AS 원가율
FROM prod
WHERE prod_id IN(
		SELECT cart_prod 
		 FROM cart 
		 WHERE cart_member IN(
				SELECT mem_id 
				 FROM member 
				  where mem_like = '수영' 
				   AND substring(mem_add1,1,2) IN ('서울', '대전', '광주')))
	And prod_lgu IN(select lprod_gu
							from lprod 
			  					WHERE lprod_nm LIKE '%피혁%');

🍁 Case 조건문

• 조회 컬럼 대신 [조건문]으로 조회하기
• 작성 방법 1

Case 기준값
When 비교값 Then 처리할 값
Else 처리할 값(없어도 됨)
End

SELECT Case 1
			When 1 Then '1입니다'
			When 0 Then '0입니다'
			ELSE '음수'
		END AS case_data;

• 작성 방법 2

Case
When 비교연산자 Then 처리할 값
When 비교연산자 Then 처리할 값
Else 처리할 값
End 

SELECT Case 
			When 1<0 Then '1입니다'
			When 1>0 Then '0입니다'
			ELSE '음수'
		END AS case_data;

 

 

•  if 조건문 : mysql, mariadb에만 있음
  - true, false 결과 값을 무조건 넣어야 함

SELECT if(10 > 1, '크다',if(10 < 10, '같다', '작다')) AS if_test;

 

Case 조건문 문제

문제1

• 회원아이디, 회원이름, 성별 조회

SELECT mem_id, mem_name, 
	case SUBSTRING(mem_regno2,1,1)
    		when 1 then '남'
    		when 3 then '남'
            ELSE '여'
    		END AS gender
FROM member;

---

문제 2

• 회원 이름, 회원성별, 마일리지, 고객구분
• 고객구분은 마일리지의 값이 100이상이면 '우수고객' 100미만이면 '일반고객'

📍 코드 1  - Case 조건문 사용

SELECT mem_name,
		case SUBSTRING(mem_regno2,1,1)
    			when 1 then '남'
    			when 3 then '남'
         ELSE '여'
    	END AS gender,
		mem_mileage, 
		case 
			when mem_mileage >=100 then '우수고객'
			when mem_mileage < 100 then '일반고객'
		END AS grade
FROM member;

📍 코드 2 - If 조건문 사용

SELECT mem_name,
		if(MOD(SUBSTRING(mem_regno2, 1, 1), 2)=1, '남자', '여자') AS MF,
		mem_mileage,
		if(mem_mileage >= 5000, '우수고객', '일반고객') AS mem_best
FROM member;

mod() : 나머지의 값을 가져오는 함수
작성 방법 : SELECT MOD(10, 2) AS mod_test

---

문제 3

•  회원이 구매한 상품명, 판매가격을 조회
• 여성인 회원들이 구매한 상품이고, 우수고객이 구매한 상품에 대해서만 조회
• 우수고객은 마일리지값이 5000점 이상인 회원

SELECT prod_name, prod_sale
FROM prod
WHERE prod_id IN(
SELECT cart_prod 
FROM cart 
WHERE cart_member in(
select mem_id FROM member where SUBSTRING(mem_regno2,1,1) = 2 AND mem_mileage >= 5000));

🍁 NULL

• NULL, NAN, NONE
• NULL 체크 →  NVL(컬럼명, NULL인 경우 대체할 값)
• NULL인 경우 : 메모리가 존재하지 않는 값
• ' '인 경우 : 메모리는 존재하지만 값은 없는 것(비어있음), white space

SELECT NVL(NULL, 'NULL이다'),
		NVL(1, 'NULL이다');

• NULL값 찾기 : IS NULL (WHERE절 내에서만 사용가능)
• 조건에서 NULL을 비교할 때는 비교연산자를 쓸 수 없고 ISNULL이나 IS NOT NULL을 사용해야 한다.

SELECT PROD_NAME, NVL(PROD_MILEAGE,0) AS PROD_MILEAGE
FROM prod
WHERE prod_mileage IS NULL;

• IFNULL vs NULLIF
  - IFNULL(A, B) : A가 NULL이면 B를 리턴하고, NULL이 아니면 A 리턴한다.
  - NULLIF(A, B) : A = B가 True이면 NULL을 리턴하고, 그렇지 않으면 A을 리턴한다.
•  

🍁 날짜 함수

• NOW() : 데이터 INSERT시에 주로 사용
• CURDATE() : 데이터 INSERT시에 년월일만 넣고자 할 때 주로 사용
• 시간은 자주 사용되지는 않음

SELECT NOW() AS '오늘날짜', CURDATE() AS '오늘 년월일', CURTIME() AS '오늘 시분초';

• 날짜 포맷 이용하기
  - DATE_FORMAT(컬럼명, 날짜형식)

SELECT DATE_FORMAT('20231122144059', '%Y-%M-%D %H:%I:%S') AS dt1,
			DATE_FORMAT(NOW(), '%Y-%M-%D %H:%I:%S') AS dt2;

날짜 함수 문제

문제 1

• 회원아이디, 회원이름, 회원생일 조회
• 생일은 '년-월-일' 형태로 조회
• 조회 되는 날짜의 가장 최솟값은 1970년 1월 1일이다. 그렇기 때문에 성윤미의 경우 mem_regno1에 460409로 데이터 값이 저장되어 있지만 %Y 형태로 연도를 가져오면 1946년으로 조회되지 못하고 2046년으로 나타나는 것이다.

SELECT mem_id, mem_name, 
	DATE_FORMAT(mem_bir,'%Y년-%m월-%d일') AS birthday,
	DATE_FORMAT(mem_regno1,'%Y-%m-%d') AS mem_regno1
FROM member;

🍁 형(타입) 변환 함수

•  Cast() or Convert()
• 형 변환을 주로 사용하는 경우 : 문자타입의 날짜값을 날짜타입으로 바꾸고자 할 때 주로 사용된다.

SELECT CAST('20231122' AS DATE), CONVERT('20231122', DATE);