DB회사들마다 종속함수라는 것이 있다.
종속함수는 '다른'DB에서는 함수 이름이 다른 함수인데
우리는 MariaDB의 종속함수를 알아보자.
문제1
내용
1. 상품분류명에 컴퓨터가 포함되어 있고, 주문이력이 있는 상품에 대한 데이터를 조회.
2. 상품명, 상품분류명 조회하기
< 내 코드 >
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 WHERE cart_prod = prod_id);※ 맨 아래 주문이력이 있는 상품에 대한 데이터 정보 불러 올 때 cart에 cart_prod에 해당하는 prod_id 있다면 주문이력이 있다는 뜻이다.
REPLACE
* 치환 : replace( 컬럼, '찾을값', '바꿀값' )
SELECT REPLACE('컬럼1에 찾을값이 있어요.', '찾을값', '바꿀값') AS msg;
문제2
내용
1. 회원의 성씨 중에 이씨를 리씨로 바꾸기
2. 성만 바꾸기 (예시 : 이순신 -> 리순신)
< 내 코드 >
SELECT CONCAT(REPLACE(SUBSTRING(mem_name, 1, 1), '이', '리'), SUBSTRING(mem_name, 2, 2)) AS name
FROM member;※ 성만 바꾸기 위해서 substring을 써서 성 부분과 이름 부분으로 나눈다음 replace해주고 concat으로 합쳤다.
< 교수님 코드 >
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,
-- 결과 : 124 // 해석 : 소수점 0번째까지, 아래에서 반올림 됨
ROUND(123.567, 1) AS r2,
-- 결과 : 123.6 // 해석 : 소수점 1번째까지, 아래에서 반올림 됨
ROUND(123.567, 2) AS r3,
-- 결과 : 123.57 // 해석 : 소수점 2번째까지, 아래에서 반올림 됨
ROUND(123.567, 3) AS r4;
-- 결과 : 123.567 // 해석 : 소수점 3번째까지, 아래에서 반올림 됨
SELECT ROUND(123.567, -1) AS r5,
-- 결과 : 120 // 해석 : 소수점 기준(0)으로 -1번째에서 반올림
ROUND(123.567, -2) AS r6,
-- 결과 : 100 // 해석 : 소수점 기준(0)으로 -2번째에서 반올림
ROUND(123.567, -3) AS r7,
-- 결과 : 0 // 해석 : 소수점 기준(0)으로 -3번째에서 반올림
ROUND(765.432, -2) AS r8;
-- 결과 : 0 // 해석 : 소수점 기준(0)으로 -2번째에서 반올림
문제3
내용
1. 상품정보에서 상품명, 원가율을 조회 ( 원가율 = 매입가/판매가의 백분율 값 )
2. 원가율은 소숫점 2자리까지 표현
< 내 코드 >
SELECT prod_name, ROUND((Prod_cost/Prod_sale)*100, 2) AS percent
FROM prod;
문제4
내용
1. 회원중에 취미가 수영인 회원이 구매한 상품들을 조회
2. 회원 지역이 서울, 대전, 광주에 거주하는 회원들
3. 상품분류명 중에 피혁이라는 분류에 속해있는 상품을 구매한 회원
4. 조회컬럼 : 상품명, 상품분류명, 원가(소숫점 2자리까지)
※ 원가율 = (매입가/판매가) * 100
< 내 코드 >
SELECT prod_name,
(SELECT lprod_nm FROM lprod WHERE lprod_gu = prod_lgu ) as lprod_nm,
ROUND((Prod_cost/Prod_sale)*100, 2) AS cost
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문 : 조회 컬럼 대신 [조건문]으로 조회하기
- 작성방법
① 방법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;
문제5
내용
1. 회원아이디, 회원이름, 성별을 조회해 주세요.
< 내 코드 >
SELECT mem_id, mem_name,
Case
when substring(Mem_regno2,1,1) = 1 then '남자'
when substring(Mem_regno2,1,1) = 2 then '여자'
ELSE '성별이 없습니다.'
END AS gender
FROM member;
< 교수 코드 >
SELECT mem_id, mem_name,
(Case SUBSTR(mem_regno2, 1, 1)
When 1 Then '남자'
When 3 Then '남자'
ELSE '여자'
END) AS gender
FROM member;
IF
- if문 : MySQL, MariaDB에만 있음
- WHERE 안에서는 IF문 안씀!!
- 작성방법 : if ( 조건식, 참일 경우 실행값 , 거짓일 경우 실행값 )
SELECT if(10 > 1, '크다', '작다') AS if_test;
- 출력 : 크다
SELECT if(10 > 100, '크다', if(10 < 10, '같다', '작다')) AS if_test;
- 출력 : 작다
MOD
- mod( ) : 나눈 나머지의 값 함수
- WHERE 안에서 사용 많이함!!
SELECT MOD(10, 2) AS mod_test;
- 출력 : 0
문제6
내용
1. 회원이름, 회원성별, 마일리지, 고객구분
2. 고객구분은 마일리지의 값이 5000이상이면 '우수고객'
5000미만이면 '일반고객'
< 내 코드 >
SELECT mem_name,
(Case SUBSTRING(mem_regno2, 1, 1)
when 1 then '남자'
when 3 then '남자'
ELSE '여자'
END) AS gender,
mem_mileage,
if( mem_mileage >= 5000, '우수고객', '일반고객' ) AS mem_best
FROM member;
< 교수님 코드 >
SELECT mem_name,
if(MOD(SUBSTRING(mem_regno2, 1, 1), 2)>0 , '남자', '여자') AS gender,
mem_mileage,
if( mem_mileage >= 5000, '우수고객', '일반고객' ) AS mem_best
FROM member;
문제7
내용
1. 회원이 구매한 상품명, 판매가격을 조회
2. 단, 여성인 회원들이 구매한 상품이고,
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 MOD(substring(mem_regno2, 1, 1), 2) = 0 AND mem_mileage >= 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 if(mem_mileage >= 5000, '우수고객', '일반고객')= '우수고객'));
NULL
- null, NaN, Nane : 메모리 공간이 존재하지 않는 값
VS ' '인경우(white space) : 메모리는 존재하지만 값은 없는 것(비어있는 것)
- null 체크 → nvl 함수 , IFNULL함수(거의 안씀)
- 작성방법 : nvl (null, 'null인 경우값') , IFNULL(컬럼명, 'null인경우 대체할 값' )
SELECT nvl(NULL, 'null이네요'),
-- 첫번째가 널이면 뒤에 값 출력
nvl(1, 'null이네요');
-- 첫번째가 널이아니면 그대로 출력
1. null값 찾기 : is null(where절 내에서만 사용가능)
SELECT *
FROM prod
WHERE prod_mileage IS NULL;
2. nvl하기 : 마일리지값이 null인 경우에 0으로 출력시키기(메모리 공간에 대체시키기)
SELECT prod_name, nvl(prod_mileage, 0) AS prod_mileage,
IFNULL(prod_mileage, 1) AS if_mileage
FROM prod
WHERE prod_mileage IS NULL;
3. 이건 값이 안나옴 왜? null과 같으려면 prod_mileage에 메모리 공간이 없어야 함.
SELECT *
FROM prod
WHERE prod_mileage = NULL;
날짜함수
- MySQL, MariaDB에서 사용하는 날짜 함수
- NOW() : 데이터 INSERT시에 주로 사용
- CURDATE() : 데이터 INSERT시에 년월일만 넣고자 할 때 주로 사용
- CURTIME() : 시간은 자주 사용되지는 않음
SELECT NOW() AS '오늘날짜',
CURDATE() AS '오늘 년월일',
CURTIME() AS '오늘 시분초';
날짜포맷
- DATE_FORMAT() : 날짜 포맷
SELECT DATE_FORMAT('20231122144059', '%Y-%M-%D') AS DATE1,
DATE_FORMAT('20231122144059', '%Y-%m-%d %H:%I:%S') AS DATE2,
DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS DATE3;
| %Y | 년, 2023 | %y | 년, 23 |
| %M | 월, November | %m | 월, 11 |
| %D | 일, 22nd | %d | 일, 22 |
| %H | 시, 14 | %h | 시, 14 |
| %I | 분, 02 | %i | 분, 02 |
| %S | 초, 59 | %s | 초, 59 |
문제8
내용
1. 회원아이디, 회원이름, 회원생일 조회하기
2. 생일은 '년-월-일' 형태로 조회
< 내 코드 >
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;※ 기준이 1970년 1월 1일 이후부터 적용되므로 그 전 날짜들은 다른 값으로 나온다.
ex. 1946-04-09 인 사람은 2046-04-09로 출력된다.
형 변환 함수
- Cast() : 형(타입)변환 함수 ; 표준
- Convert() : 형(타입)변환 함수 ; MYSQL, MariaDB에만
- 형변환을 주로 사용하는 경우 : 문자타입의 날짜값을 날짜타입으로 바꾸고자 할 때 주로 사용됨
SELECT CAST('20231122' AS DATE), CONVERT('20231122', DATE);
'Back-End > 데이터베이스' 카테고리의 다른 글
| [DB]데이터베이스 명령어5 (MariaDB) - 조인(Join), 가상테이블(inline view) (8) | 2023.11.23 |
|---|---|
| [DB]데이터베이스 명령어5 (MariaDB) - 그룹함수(집합함수) (3) | 2023.11.22 |
| [DB] 데이터베이스 명령어3 (MariaDB) - concat(), upper(), trim(), substring(), left(), 서브쿼리 (11) | 2023.11.21 |
| [DB] 데이터베이스 명령어2 (MariaDB) - AS, IN, LIKE, BETWEEN (0) | 2023.11.21 |
| [DB] 데이터베이스 명령어1(MariaDB, HeidiSQL) (2) | 2023.11.20 |
