[Database] HeidiSQL 함수 In, Replace, Select, nvl, 날짜 함수

 

 

 

 

 

 

프로그래밍 세계를 탐구합시다.

 

 

 

 

 

 

[  조건  ]

 

-- [문제]
-- 상품분류명에 컴퓨터가 포함되어 있고,
-- 주문이력이 있는 상품에 대한 데이터를 조회하려고 합니다.
-- 상품명, 상품분류명 조회하기

 

 

 

[  작성 코드  ]

 

SELECT Prod_name, 
(select Lprod_nm FROM lprod WHERE lprod_gu=prod_lgu) AS lprod_nm
FROM prod
WHERE prod_lgu IN (SELECT lprod_gu FROM lprod WHERE lprod_nm LIKE '%컴퓨터%')
 AND prod_id IN (SELECT cart_prod FROM cart);

 

 

 

 

[  결과  ]

 

 

 

 

 

 

1. Replace 함수

 

치환하기

replace(원본값, 찾을 값, 바꿀 값)

 

 

[  활용 예시  ]

 

 -- Replace() : 치환하기
 SELECT REPLACE ('컬럼1에 찾을 값이 없어요', '찾을값', '바꿀 값') AS msg;

 

 

[  결과  ]

 

 

 

 

 

 

[  조건  ]

 

-- 회원의 성씨 중에 이씨를 리씨로 바꿔주세요
 -- 성만 바꿔주시면 됩니다.
 -- 예시 : 이순신 -> 리순신

 

 

 

[  작성 코드1  ]

 

 

 SELECT
 concat(REPLACE(SUBSTRING(mem_name,1,1), '이', '리'), SUBSTRING(mem_name ,2))
 FROM member
 ;

 

 

[  결과  ]

 

 

 

 

[  작성 코드 2 ]

 

 

 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) = '이';

 

 

 

[  결과  ]

 

 

 

 

 

 

 

 

1. Round 함수

 

반올림 함수

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;

 

 

[  결과  ]

 

 

 

 

 

 

[  조건  ]

 

-- [ 문제 ]
-- 상품정보에서 상품명, 원가율을 조회하려고 합니다.
-- 원가율 = 매입가/판매가의 백분율 값입니다.
-- 원가율은 소숫점 2자리까지 표현

 

 

 

[  작성 코드  ]

 

 

SELECT prod_name, round(Prod_cost/Prod_sale*100, 2)
FROM prod
;

 

 

[  결과  ]

 

 

 

 

 

 

[  조건  ]

 

 

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

 

 

 

[  작성 코드  ]

 

SELECT prod_name, round(Prod_cost/Prod_sale*100, 2) AS mm,
(select Lprod_nm FROM lprod WHERE lprod_gu=prod_lgu) AS lprod_nm
FROM prod
WHERE
prod_id IN (SELECT cart_prod FROM cart 
WHERE cart_member IN(SELECT mem_id FROM member WHERE mem_like LIKE '%수영%'
AND
SUBSTRING(mem_add1, 1, 2) IN ('서울', '대전', '광주')))
AND
prod_lgu IN(SELECT lprod_gu FROM lprod WHERE lprod_nm LIKE '%피혁%');

 

 

 

[  결과  ]

 

 

 

 

 

 

 

[  CASE문 작성 방법  ]

 

 

-- 조건 컬럼 대신 [조건문]으로 조회하기

 

case 기준값
when 비교값 then 처리할 값
else 처리할 값
end

case 기준값
when 비교연산자 then 처리할 값
when 비교연산자 then 처리할 값
else 처리할 값
end

 

 

 

[  활용 예시  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;

 

 

 

 

[  결과  ]

 

 

 

 

 

 

[  조건  ]

 

 

-- [문제]
-- 회원아이디, 회원이름, 성별을 조회해 주세요

 

 

 

[  작성 코드  ]

 

 

-- [문제]
-- 회원아이디, 회원이름, 성별을 조회해 주세요
SELECT Mem_id, Mem_name,
		(case substring(Mem_regno2, 1, 1)
		when 1 then '남'
		when 3 then '남'
		when 2 then '여'
		when 4 then '여'
		ELSE '오류'
		END) AS 성별
FROM member;

 

 

 

[  결과  ]

 

 

 

 

 

 

 

 

[  조건  ]

 

 

-- [문제]
-- 회원이름, 회원성별, 마일리지, 고객구분
-- 고객구분은 마일리지의 값이 5000 이상이면 '우수고객'
--								  		5000 미만이면 '블랙리스트'

 

 

 

[  작성 코드  1  - case 문  ]

 

 

SELECT Mem_name, 
(case substring(Mem_regno2, 1, 1)
		when 1 then '남'
		when 3 then '남'
		when 2 then '여'
		when 4 then '여'
		ELSE '오류'
		END) AS 성별, Mem_mileage,
if (Mem_mileage >= 5000, '우수고객', '블랙리스트') AS 고객구분
FROM member;

 

 

 

[  작성 코드  2  -  if문  ]

 

 

SELECT Mem_name, 
		if(MOD(SUBSTRING(mem_regno2, 1, 1), 2)>0, '남', '여') AS 성별, Mem_mileage,
if (Mem_mileage >= 5000, '우수고객', '블랙리스트') AS 고객구분
FROM member;

 

 

 

[  결과  ]

 

 

 

 

 

 

 

 

[  조건  ]

 

 

-- [문제]
-- 회원이 구매한 상품명, 판매가격을 조회하려고 합니다.
-- 단, 여성인 회원들이 구매한 상품이고,
--      우수고객이 구매한 상품에 대해서만 조회합니다.
-- 우수고객은 마일리지값이 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 mod(substring(mem_regno2, 1, 1), 2)=0
		AND mem_mileage >= 5000));

 

 

 

[  결과  ]

 

 

 

 

 

 

 

[  nvl  ]

 

-- null, Nan, None : null 체크 -> nvl(null, 'null인경우값')

 

-- nvl(컬럼명, null인 경우 대체할 값)

 

 

[  활용 예시  ]

 

-- null, Nan, None : null 체크 -> nvl(null, 'null인경우값')
-- nvl(컬럼명, null인 경우 대체할 값)
SELECT nvl(NULL, 'null이네요'),
		 nvl(1, 'null이네요');

 

 

 

 

 

 

 

 

 

 

[  날짜 함수  ]

 

-- now() : 데이터 insert 시에 주로 사용
-- curdate() : 데이터 insert시에 년월일만 넣고자 할 때 주로 사용

 

 

[  활용 예시  ]

 

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

 

 

 

 

 

 

[  날짜 포맷 이용하기  ]

 

SELECT DATE_FORMAT('20231122144059','%Y-%m-%d %H:%i:%s') AS dt1,
		 DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS dt2;

 

 

 

 

 

 

 

 

[  조건  ]

 

 

-- 회원아이디, 회원이름, 회원생일, 주민번호 앞자리 조회하기
-- 생일, 주민번호는 '년-월-일' 형태로 조회

 

 

 

[  작성 코드  ]

 

 

SELECT Mem_id, Mem_name, 
			DATE_FORMAT(Mem_bir,'%Y-%m-%d') AS mem_bir,
			DATE_FORMAT(Mem_regno1,'%Y-%m-%d') AS mem_regno1
FROM member;

 

 

 

 

[  결과  ]