[DB] 데이터베이스 명령어3 (MariaDB) - concat(), upper(), trim(), substring(), left(), 서브쿼리

CONCAT

* 병합

    - 컬럼 데이터 합치기(병합) : concat()함수 사용 ( 중요! )
    - 병합후 별명 지어줘야함!.  

 

- 회원이름, 회원주민번호(앞 - 뒤), 주소(앞 뒤)

SELECT mem_name, 
CONCAT(mem_regno1, '-', mem_regno2) AS 'regno',
CONCAT(mem_add1, ' ', mem_add2) AS 'add'
FROM member;

 

---

대소문자

* 대소문자 

    - 대소문자 변환 : lower(), upper()

    - 회원가입할 때, 오류 찾을 때 사용

 

SELECT LOWER(mem_id) AS '소문자로', 
               UPPER(mem_id) AS '대문자로'
FROM member;

 

---

TRIM

* 공백제거

    - 공백 제거하기 : TRIM(), LTRIM(), RTRIM()

  

 [ 공백 제거 ] (3가지)

• LTRIM : 왼쪽공백제거
• RTRIM : 오른쪽 공백제거
• TRIM : 좌우 공백 제거

SELECT '         왼쪽공백', LTRIM('          왼쪽공백제거'),
               '오른쪽 공백      ', RTRIM('오른쪽 공백제거       '),
               '    좌우 공백    ', TRIM('      좌우 공백 제거   ');

 

---

LEFT, RIGHT

* 특정 자릿수까지의 문자 추출하기

    - 특정 자릿수까지의 문자 추출 : LEFT(컬럼명, 자릿수), RIGHT(컬럼명, 자릿수)

    - 자릿수는 1부터
    - mysql or mariaDB용

 

SELECT LEFT(prod_id, 4), RIGHT(prod_id, 6)
FROM prod;

 

[문제7]
P101 상품분류에 대해 새로운 상품을 등록하려고 한다.
P101000006이 현재 마지막 번호이다.
마지막 번호에 1 증가시키고자 한다.
최종값 P101000007을 만들어보기

SELECT CONCAT(LEFT('P101000006', 9) , RIGHT('P101000006', 1) + 1 );

문제7

---

SUBSTRING

* 문자열 내 특정 위치값 추출하기 

    - SUBSTRING(문자열, 시작위치값, 개수)

 

SELECT SUBSTRING('Java Program', 7, 3),
               SUBSTRING('Java Program', 1, 3);

 

[문제8]
상품명의 4번째 자리부터 2개의 문자가 '칼라'인 것에 대한
상품코드, 상품명 조회

- 사용할 테이블 : prod
- 사용할 컬럼 : prod_id, prod_name
- 일반 조건 : SUBSTRING(prod_name, 4, 2) = '칼라'

SELECT prod_id, prod_name
FROM prod
WHERE SUBSTRING(prod_name, 4, 2) = '칼라';

문제8

 

[문제9]
회원의 성씨가 김씨이고,
지역이 서울 또는 대전에 거주하고,
기념일에 결혼이 포함되어 있는 회원 정보 조회하기
조회컬럼 : 회원이름, 지역(지역 앞 2자리만), 기념일명

SELECT mem_name, mem_add1, mem_memorial
FROM member
WHERE mem_name LIKE '김%'
AND SUBSTRING(mem_add1, 1, 2) IN ('서울', '대전')
AND mem_memorial LIKE '%결혼%';

문제9

 

---

SUBQUERY

*  서브 쿼리

    - SELECT 안  SELECT

 

    - 비교연산자를 이용하는 서브쿼리 특징
       "단일 컬럼의 단일 행" : 하나의 컬럼에 하나의 값만 조회가능해야함

        ex. (SELECT mem_mileage, mem_id FROM member WHERE mem_id = 'a001');

                 => row오류 : 비교하는 값이 맞지 않다.

 

    - IN 함수를 이용해서 서브쿼리를 사용할 경우 규칙
       "단일 컬럼에 다중 행" : 하나의 컬럼에 여러개의 행을 조회 할 수 있음

 

    - SELECT 뒤에 조회할 컬럼명 대신해서 서브쿼리 작성 규칙
       "단일 컬럼에 단일 행 " : 하나의 컬럼에 하나의 값만 조회 가능

 

 

[문제10]
아이디 a001인 회원이 가지고 있는 마일리지보다 이상인 회원조회
조회컬럼 : 회원아이디, 회원마일리지

SELECT mem_id, mem_mileage 
FROM member
WHERE mem_mileage >= (SELECT mem_mileage FROM member WHERE mem_id = 'a001');

문제10

 

[문제11]
한번도 주문한적이 없는 회원을 조회하기
조회컬럼 : 회원아이디, 회원이름

SELECT mem_id, mem_name
FROM member
WHERE mem_id NOT IN (SELECT cart_member FROM cart);

문제11

 

[문제12]
상품정보에 존재하지 않는 상품분류정보를 조회
상품분류코드, 상품분류명 조회하기

SELECT lprod_gu, lprod_nm
FROM lprod
WHERE lprod_gu NOT IN (SELECT prod_lgu FROM prod);

문제12

[문제13]
주문번호, 주문자 아이디, 주문자 이름, 주문상품코드, 주문수량을 조회

SELECT cart_no, cart_member, (SELECT mem_name FROM member WHERE mem_id = cart_member ) AS mem_name, cart_prod, cart_qty
FROM cart;

문제13

 

[문제14]
상품명, 상품판매가격, 상품분류코드, 상품분류명 조회

SELECT prod_name, prod_sale, prod_lgu, (SELECT lprod_nm FROM lprod WHERE lprod_gu = prod_lgu) AS lprod_nm
FROM prod;

문제14

 

[문제15]
주문번호, 주문상품코드, 주문수량, 주문상품명, 상품분류명 조회하기
단, 회원의 거주지역이 서울 또는 대전인 경우
정렬은 주문번호 기준으로 오름차순, 주문수량을 기준으로 내림차순
(Hint)상품코드의 앞자리4자리는 상품분류명랑 같다

<내 코드>

SELECT cart_no, cart_prod, cart_qty,
              (SELECT prod_name FROM prod WHERE prod_id = cart_prod) AS prod_name,
              (SELECT lprod_nm FROM prod, lprod WHERE (lprod_gu = prod_lgu) AND (prod_id = cart_prod)) AS lprod_nm
FROM cart
WHERE (SELECT SUBSTRING(mem_add1, 1, 2) from member where mem_id = cart_member) IN ('서울', '대전')
ORDER BY cart_no ASC, cart_qty DESC;

 

<교수 코드>

SELECT cart_no, cart_prod, cart_qty,
              (SELECT prod_name FROM prod WHERE prod_id = cart_prod) AS prod_name,
              (SELECT lprod_nm FROM lprod WHERE lprod_gu = SUBSTRING(cart_prod, 1, 4)) AS lprod_nm
FROM cart
where cart_member IN (SELECT mem_id FROM member WHERE SUBSTRING(mem_add1, 1, 2) IN ('서울', '대전'))
ORDER BY cart_no ASC, cart_qty DESC;

 

※ 내가 한 코드

 "나는 cart_member 랑 일치하는 mem_id를 조건으로 줘서 회원주소를 앞에서 두 글자 가져왔다. 그리고 그 회원주소가 서울 또는 대전을 만족하는 경우의 조회컬럼들을 가져왔다. "

 

※ 교수님 코드

"mem_add1 앞 두글자가 서울이나 대전인 조건을 만족하는 mem_id에 해당하는 cart_member를 만족하는 조회컬럼들을 가져옴"

 

문제15

 

 

[문제16]
상품코드, 상품명, 상품판매가격, 거래처명 조회하기
단, 회원의 마일리지 값이 100이상이고,
      주문수량이 5이상이고, 
      상품분류명에 "컴퓨터"가 포함되어 있고,
      거래처 주소지 지역이 서울, 대전, 광주인 경우 

<내 코드>

SELECT (SELECT prod_id FROM prod WHERE cart_prod = prod_id) AS prod_id,
               (SELECT prod_name FROM prod WHERE cart_prod = prod_id) AS prod_name,
               (SELECT prod_sale FROM prod WHERE cart_prod = prod_id) AS prod_sale,
               (SELECT buyer_name FROM buyer WHERE buyer_id IN (SELECT prod_buyer FROM prod WHERE                        cart_prod = prod_id )) AS buyer_name
FROM cart
WHERE (SELECT Mem_mileage FROM member WHERE mem_id = cart_member ) >= 100
               AND Cart_qty >= 5
               AND (SELECT lprod_nm FROM lprod WHERE Lprod_gu IN (SELECT Prod_lgu from prod WHERE                                     prod_id = cart_prod )) LIKE '%컴퓨터%'
               AND (SELECT SUBSTRING(Buyer_add1, 1, 2) FROM buyer WHERE buyer_id IN (SELECT prod_buyer                           FROM prod WHERE prod_id = cart_prod)) IN ('서울', '대전', '광주');

 

<교수 코드>

SELECT prod_id, prod_name, prod_sale, (SELECT buyer_name FROM buyer WHERE buyer_id = prod_buyer )
                                                                    AS buyer_name
FROM prod
WHERE prod_id IN (SELECT cart_prod FROM cart WHERE cart_member IN (SELECT mem_id FROM member 
                                WHERE mem_mileage >= 100)
                             AND cart_qty >= 5)
AND prod_lgu IN (SELECT lprod_gu FROM lprod WHERE lprod_nm LIKE '%컴퓨터%')
AND prod_buyer IN (SELECT buyer_id FROM buyer WHERE SUBSTRING(buyer_add1, 1, 2) IN ('서울', '대전', '광주'));

 

※ 내가 한 코드

 "나는 cart를 메인으로 두고 정보들을 가져왔는데 그렇게 하니까 조회컬럼 설정하는게 어려웠고 이중 쿼리문이 3개나 들어가야 했다. 구매를 했다는 가정하에 두려고 선택했으나 코드가 복잡해져서 옳지 않은 선택인것 같다!  "

 

※ 교수님 코드

: 이중 서브쿼리는 뒤에서 부터 차례대로 하나씩 써보는게  이해하기가 쉬웠다

예를 들어 cart테이블을 통해 member테이블의 정보를 가져올 때 

"mem조건→where → from member → select PK → IN → where FK → from cart → select PK → IN →where FK" 

결과 : where FK IN (select PK from cart where FK in (select PK from member where mem_mileage >= 100))

 

문제16