[Database] Heidi SQL 문자열함수, 대소문자 변환, 공백 제거, 특정 자릿수까지의 문자, 문자열 내 위치 값, 서브쿼리

 

 

 

 

 

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

 

 

 

 

 

[ 대소문자 변환 ]

 

대문자 : Upper

소문자 : Lower

 

 

[ 작성 코드 ]

 

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

 

 

[ 결과 ]

 

 

 

 

 

 

 

[ 공백 제거하기 ]

 

LTRIM : 왼쪽 공백 제거

RTRIM : 오른쪽 공백 제거

TRIM : 좌우 공백 제거

 

 

 

[ 작성 코드 ]

 

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

 

 

 

[ 결과 ]

 

 

 

 

 

 

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

 

- mysql or mariaDB용

 

Left, Right

 

 

 

[ 작성 코드 ]

 

-- 특정 자릿수까지의 문자 추출하기
-- mysql or mariaDB용
SELECT LEFT(prod_id, 4), RIGHT(prod_id, 6)
FROM prod;

 

 

 

[ 결과 ]

 

 

 

 

 

 

[ 조건 ]

 

-- P101 상품분류에 대해 새로운 상품을 등록하려고 합니다.
-- P101000006이 현재 마지막 번호입니다.
-- 마지막 번호에 1 증가시키고자 합니다.
-- P101000007

 

 

 

[ 작성 코드 ]

 

 

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

 

 

[ 결과 ]

 

 

 

 

 

 

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

 

-- 표준 : In, Like, Between
-- 회사에서 만든 것 : concat, substring

 

 

[ 작성 코드 ]

 

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

 

 

[ 결과 ]

 

 

 

 

 

 

[ 조건 ]

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

 

 

[ 작성 코드 ]

 

SELECT Prod_id, Prod_name
FROM prod
WHERE substring(Prod_name, 4, 2) = '칼라';

 

 

[ 결과 ]

 

 

 

 

 

[ 조건 ]

 

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

 

 

 

[ 작성 코드 ]

 

SELECT Mem_name, SUBSTRING(Mem_add1, 1, 2), Mem_memorial
FROM member
WHERE SUBSTRING(mem_add1, 1, 2) IN ('서울', '대전')
	AND Mem_memorial LIKE '%결혼%';
	AND substring(Mem_name,1, 1) = '김';

 

 

 

[ 결과 ]

 

 

 

 

 

 

[ 서브쿼리 ]

 

-- 비교연산자를 이용하는 서브쿼리 특징
-- 하나의 컬럼에 하나의 값만 조회 가능해야 함
-- 단일 컬럼의 단일 행이라고 표현합니다.

 

 

 

[ 조건 ]

 

-- [문제]
-- a001인 회원이 가지고 있는 마일리지보다
-- 큰(이상)인 회원조회
-- 조회컬럼은 회원아이디, 회원마일리지

 

 

[ 작성 코드 ]

 

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

 

 

[ 실행 화면 ]

 

 

 

 

 

 

 

[ IN 함수를 이용해서 서브쿼리를 사용할 경우 규칙 ]
-- 하나의 컬럼에 여러 개의 값을 조회할 수 있음
-- 단일 컬럼에 다중 행이라고 칭합니다.

 

 

 

 

[ 조건 ]

 

-- [문제]
-- 한 번도 주문(장바구니)한 적이 없는 회원을 조회해 주세요
-- 조회컬럼 : 회원아이디, 회원이름

 

 

 

 

[ 작성 코드 ]

 

 

SELECT Mem_id, Mem_name
FROM member
WHERE Mem_id NOT IN(SELECT Cart_member
FROM cart);

 

 

 

[ 실행 화면 ]

 

 

 

 

 

[ 조건 ]

 

-- [문제]
-- 상품정보에 존재하지 않는 상품분류정보를 조회하려고 합니다.
-- 상품분류코드, 상품분류명 조회하기

 

 

[ 작성 코드 ]

 

SELECT Lprod_gu,Lprod_nm
FROM Lprod
WHERE Lprod_gu NOT IN(SELECT Prod_lgu
FROM prod);

 

 

 

[ 실행 화면 ]

 

 

 

 

 

 

 

 

[  조건  ]

 

-- [문제]
-- 주문번호, 주문자 아이디, 주문자이름, 주문상품코드, 주문수량을
-- 조회
-- Select 뒤에 조회할 컬럼명 대신해서 서브쿼리 작성 규칙
-- 하나의 컬럼에 하나의 값만 조회 가능
-- 단일 컬럼에 단일 행

 

 

 

[  작성 코드  ]

 

SELECT Cart_no, Cart_member, 
(SELECT mem_name
FROM member
WHERE mem_id=Cart_member) AS mem_name, 
Cart_prod, Cart_qty
FROM cart
;

 

 

 

[  실행 화면  ]

 

 

 

 

 

 

 

 

[  조건  ]

 

 

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

 

 

 

[  작성 코드  ]

 

 

SELECT Prod_name, Prod_sale, Prod_lgu, (SELECT lprod_nm FROM lprod WHERE lprod_gu=prod_lgu) AS lprod_nm
FROM prod;

 

 

 

[  실행 화면  ]

 

 

 

 

 

 

 

[  조건  ]

 

-- [문제]
-- 주문번호, 주문상품코드, 주문수량, 주문상품명, 상품분류명 조회하기
-- 단, 회원의 거주지역이 서울 또는 대전인 경우
-- 정렬은 주문번호를 기준으로 오름차순,
--		 주문수량을 기준으로 내림차순

 

 

 

 

[  작성 코드 ]

 

 

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;

 

 

 

 

[  실행 화면  ]

 

 

 

 

 

 

 

 

[  조건  ]

 

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

 

 

 

[  작성 코드  ]

 

 

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 (
									-- 회원의 마일리지 값이 100이상
									SELECT mem_id FROM member
										WHERE mem_mileage >= 100)
										
							 -- 주문수량이 5 이상
							 AND cart_qty >= 5)
							 
 -- 상품분류명에 "컴퓨터"가 포함
 AND prod_lgu IN (SELECT lprod_gu FROM lprod
 							WHERE lprod_nm LIKE '%컴퓨터%')
 							
 -- 거래처 주소지 지역이 서울or대전or광주
 AND prod_buyer IN (
	SELECT buyer_id FROM buyer
	 WHERE substring(buyer_add1, 1, 2) IN ('서울', '대전', '광주'));

 

 

[  결과  ]