(MariaDB) SQL - 함수

 

서브쿼리 사용 예제 

-- [문제]
/*
상품분류명에 컴퓨터가 포함되어있고 주문이력이 있는 상품에 대한 데이터 조회
조회컬럼 : 상품명 , 상품분류명
*/
-- join 미완성
SELECT prod_name , lprod_nm
FROM prod , lprod , cart
WHERE prod_lgu = lprod_gu
AND lprod_nm LIKE '%컴퓨터%'
AND cart_prod = prod_id;

-- 서브쿼리 사용
SELECT prod_name , 
     (SELECT lprod_nm FROM lprod WHERE lprod_gu = prod_lgu ) AS lprod_nm
FROM prod
WHERE prod_id in (SELECT cart_prod FROM cart)
AND (select lprod_nm from lprod where lprod_gu = prod_lgu) LIKE '%컴퓨터%' ;

 

▶  총 6개의 행이 나오는 서브쿼리 사용 구문과 달리 join의 경우에는 12개의 행이 나왔다. 

    

SQL 함수 for mariaDB(mysql)
REPLACE('찾을값이 있는 컬럼' , '찾을값' , '바꿀값')	찾은 값을 바꿀 값으로 바꾸어(치환)하여 준다.  실제 데이터가 바뀌는것은 아님
ROUND(숫자 , 소수점자리수)	소수점 자리수까지 값이 나오도록 반올림 해줌
Case 문	조건에 맞는 값을 추출(if else와 비슷)
if ( 조건 , true일때 반환 값 , false일때 반환값)	조건이 참일때 거짓일때 값 출
MOD(a , b)	a 나누기 b 의 나머지 출력

 

 

REPLACE

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

-- 회원의 성씨 중에 이씨를 리씨로 바꾸기
SELECT CONCAT (REPLACE(left(mem_name,1) , '이' , '리') , substring(mem_name ,2)) AS msg
FROM member;

 

▶  처음에는 replace만 사용하였는데 이름에있는 '이'도 '리'로 바뀌었다. 이를 해결하기 위해 substring으로 나눈 뒤 성씨 하나와 그 뒤의 나머지 이름(이름이 4글자여도 가능)을 concat으로 붙였다.

 

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자리까지)
*/
-- join 사용
SELECT prod_name , lprod_nm , ROUND(prod_cost / prod_sale * 100 , 2) AS'원가율'
FROM prod, lprod , member , cart
WHERE prod_lgu = lprod_gu
AND lprod_nm LIKE '%피혁%'
AND cart_prod = prod_id
AND cart_member = mem_id
AND mem_like = '수영'
AND left(mem_add1,2) IN ('서울', '대전','광주');
-- 서브쿼리 사용
SELECT prod_name ,
     (SELECT lprod_nm FROM lprod WHERE lprod_gu = prod_lgu) as lprod_name ,
	  ROUND(prod_cost / prod_sale * 100 , 2) AS'원가율'
FROM prod
WHERE prod_lgu in (SELECT lprod_gu FROM lprod WHERE lprod_nm LIKE '%피혁%')
AND prod_id in (SELECT cart_prod FROM cart
					WHERE cart_member IN (SELECT mem_id FROM member 
													WHERE left(mem_add1,2) IN ('서울', '대전','광주') 
													AND mem_like = '수영')) ;

 

▶ member - cart - prod 순으로 참조하고있는 관계가 있고 member와 prod 사이의 관계가 없는 상태이기 때문에 cart까지 연결해서 join해주어야한다.

 

CASE

 

- 사용방법

Case 기준값
     When 비교값 Then 처리할값
      Else 처리할값
End

또는
Case 
     When 조건  Then 처리할값
      Else 처리할값
End

-- [문제]
/*
회원아이디, 회원이름 , 성별을 조회해주세요
*/
SELECT mem_id , mem_name ,
		 Case 
		 When left(mem_regno2,1) IN (1 , 3) Then '남'
		 ELSE '여'
		END AS mem_gender
FROM member;

 

▶ 주민등록번호 뒷자리인 mem_regno2 의 첫자리를 사용하여 성별을 판별한다. 

 

 

IF

 

mysql , mariadb에만 있음

if ( 조건 , true일때 반환 값 , false일때 반환값) 

else라는 개념이 없으므로 true, false일때 반환값이 무조건 있어야함

 

-- [문제]
/*
회원이름 , 회원성별 , 마일리지 , 고객구분
고객구분은 마일리지의 값이 5000이상이면 '우수고객' 5000 미만이면 '일반고객'
*/
-- if문 사용
SELECT mem_name , 
      if (mod(left(mem_regno2,1),2),'남','여') AS '회원성별' ,
      mem_mileage,
	  if(mem_mileage >= 5000 ,'우수고객' , '일반고객') AS '고객구분'
FROM member;
-- Case문 사용
SELECT mem_name ,
      case when mod(left(mem_regno2,1),2) = 1 then '남' ELSE '여'END AS '회원성별' ,
      mem_mileage,
	  case when mem_mileage >= 5000 then '우수고객' ELSE '일반고객' END 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 mem_mileage >= 5000
			AND mod(left(mem_regno2,1),2) = 0));
-- join 이용
SELECT prod_name , prod_sale
FROM prod , member , cart
WHERE prod_id = cart_prod
AND cart_member = mem_id 
AND mem_mileage >= 5000
AND mod(left(mem_regno2,1),2) = 0;