SQL 문 예제 ( 11 / 21 )

 

SQL 문  예제

 

-- [문제]
/*
상품분류코드가 P201이고, 상품판매가격이 17만원인
상품코드, 상품명, 상품분류코드, 상품판매가격 조회
정렬은 상품명을 기준으로 오름차순
*/
SELECT prod_id , prod_name , prod_lgu , prod_sale
FROM prod
WHERE prod_lgu = 'P201' AND prod_sale = 170000
ORDER BY prod_name;

 

▶ 오름차순 ASC , 내림차순 DESC 오름차순은 기본값으로 되어있어 안적으면 자동으로 오름차순으로 정리됨

 

-- [문제]
/*
상품판매가격이 15만원 또는 17만원 또는 33만원인 상품을 조회
조회 컬럼은 상품명, 판매가격
*/
SELECT prod_name,prod_sale
FROM prod
WHERE prod_sale IN (150000, 170000 , 330000);
-- WHERE prod_sale = 150000 or prod_sale = 170000 OR prod_sale = 330000;

 

▶ 컬럼명 in ( 찾고자하는 데이터들)  로  특정 데이터들이 속한 행의 내용을 조회할 수 있다.

 

-- [문제]
/*
상품명에 삼성이라는 단어가 있는 데이터 조회하기
조회 컬럼 : 상품명 , 상품코드
*/
SELECT prod_name, prod_id
FROM prod
WHERE prod_name LIKE '%삼성%';

-- 상품명 중 두번째 단어가 '성'으로 시작하는 데이터
-- WHERE prod_name LIKE '_성%';
-- 상품명 중 마지막 단어가 '치'으로 끝나는 데이터
-- WHERE prod_name LIKE '%치';

▶ Like : 문자 내 특정 단어 포함 여부 검색

   ' % ' 는 다른 문자가 붙는 여부에 관계 없다는 뜻 , 즉 앞 뒤에 공백을 포함한 어떤 문자가 와도 조회 가능

     ' _ ' 는 문자(단어) 하나를 나타냄 

 

 

-- [문제]
/*
회원의 거주 지역이 서울이고 마일리지가 1000 이상인 회원의
아이디, 이름, 주소(앞), 마일리지 조회하기
*/

SELECT mem_id , mem_name , mem_add1 , mem_mileage
FROM member
WHERE mem_add1 LIKE '서울%' AND mem_mileage >= 1000;

 

 

SELECT mem_name, mem_bir
FROM member
-- WHERE mem_bir BETWEEN '1975-01-01' AND '1975-05-01';
-- WHERE mem_bir BETWEEN '19750101' AND '19750501';
-- WHERE mem_bir BETWEEN '1975.01.01' AND '1975.05.01';
WHERE mem_bir BETWEEN '1975/01/01' AND '1975/05/01';

 

▶ Between A and B : A이상 B이하의 범위 조건 , 날짜 타입 또는 숫자 타입에 대한 범위 연산에 주로 사용

    날짜 형식 (포맷형식) : 0000-00-00 , 0000.00.00 , 0000/00/00 , 000000000

 

 

-- [문제]
/*
회원의 출생년도가 1975년이 아닌 회원에 대한
조회컬럼 : 회원이름, 회원생일  
*/
SELECT mem_name , mem_bir
FROM member
WHERE NOT mem_bir LIKE '1975%';
-- WHERE mem_bir NOT LIKE '1975%';

 

▶ where 뒤에 not을 통해 뒤 조건에 만족하지 않는 경우의 데이터를 조회할 수 있다 

    like 앞에 not을 붙여도 동일하게 적용된다.

 

-- 회원이름 , 회원주민번호(앞-뒤) , 주소(앞 뒤) 조회
SELECT mem_name ,
 CONCAT(mem_regno1,'-',mem_regno2) AS mem_regno ,
  CONCAT(mem_add1 , mem_add2) AS mem_add
FROM member;

 

▶  컬럼데이터 합치기 : concat(값1,값2,...) 함수 사용 

     합친 데이터를 사용할 경우를 대비하여 별칭 사용 권장

 

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

 

▶  lower : 소문자로 변환 , upper : 대문자로 변환

     로그인 회원 아이디 조회 등에 사용된다. 별칭은 한글로 안쓰는 것을 더 권장

 

-- [문제]
/*
회원의 성씨가 김씨이고 지역이 서울 또는 대전에 거주하고 기념일에 결혼이 포함되어있는 회원정보 조회하기
조회컬럼 : 회원이름 , 지역(지역만),기념일명
*/
SELECT mem_name, LEFT(mem_add1,2) , mem_memorial
FROM member
WHERE mem_name LIKE '김%'
	AND LEFT(mem_add1,2) IN ('서울','대전')
	-- AND SUBSTRING(mem_add1,2) IN ('서울','대전') 위와 동일함
	AND mem_memorial LIKE '%결혼%';

▶  여러가지 함수를 사용하여 데이터 추출하기 예제 

 

-- [문제]
/*
아이디 a001인 회원이 가지고 있는 마일리지보다 큰(이상)회원들 조회
조회컬럼 : 회원아이디, 회원마일리지
*/
SELECT mem_id, mem_mileage
FROM member
WHERE mem_mileage >= (SELECT mem_mileage AS amil
			FROM member
			WHERE mem_id = 'a001' )	;

 

▶ 서브쿼리 사용

  

 

SQL 함수
CONCAT( 값1,값2,...)	컬럼데이터 합치기 >> 값1값2..
LOWER(값)	소문자로 변환
UPPER(값)	대문자로 변환
LTRIM(값)	왼쪽 공백 제거
RTRIM(값)	오른쪽 공백 제거
TRIM(값)	좌우 공백 제거
LEFT(값, 자리수)	왼쪽부터 특정 자리수까지의 값 추출
RIGHT (값, 자리수)	오른쪽부터 특정 자리수까지의 값 추출
SUBSTRING(값, 시작자리수, 추출할 개수)	문자열 내 특정 위치값 추출 예시) SUBSTRING('Java Program',7,3) >> 'rog'

 

 

관련 예제 

-- [문제]
/*
한번도 주문한적이 없는 회원 조회하기
조회컬럼 : 회원 아이디 , 회원 이름
*/
SELECT mem_id, mem_name
FROM member
WHERE mem_id NOT IN (select cart_member FROM cart);

 

▶  서브쿼리에서 하나의 컬럼(단일 컬럼) 에 하나의  값(단일행)과 비교할때는 비교연산자( = , != ...) 사용하여 같은지를 비교 한다. 하나의 컬럼에 여러개의 값들(다중행) 중 일치하는 값이 있는지 비교할 때는 in , not in  으로 비교한다. 

 

-- [문제]
/*
주문번호, 주문자 아이디, 주문자 이름, 주문상품코드 조회
*/
SELECT cart_no, cart_member , cart_prod , mem_name
FROM cart , member
WHERE cart_member = mem_id;
-- select 뒤에 조회할 컬럼명 대신해서 서브쿼리 작성 규칙
-- 하나의 컬럼에 하나의 값만 조회가능
-- 단일 컬럼에 단일행 
SELECT cart_no , cart_member , 
(SELECT mem_name FROM member WHERE mem_id = cart_member) AS mem_name 
, cart_prod 
FROM cart;

 

▶  위의 코드는 join을 사용해서 다른 테이블의 컬럼을 가져오는 방식, 아래는 서브쿼리를 이용한 방식

 

-- [문제]
/*
상품명 , 상품판매가격 , 상품분류코드 , 상품분류명 조회
*/
-- 1 join
SELECT prod_name , prod_sale , prod_lgu , lprod_nm
FROM prod , lprod
WHERE prod_lgu = lprod_gu;
-- 2 서브쿼리
SELECT prod_name , prod_sale , prod_lgu,
	(SELECT lprod_nm FROM lprod WHERE lprod_gu = prod_lgu) AS lprod_nm
FROM prod;

 

▶ 위와 비슷한 예제  이러한 경우에는 join을 사용하면 조금 더 간결하게 값을 조회할수 있다.

 

-- [문제]
/*
주문번호 , 주문상품코드 , 주문 수량 , 주문상품명 , 상품분류명 조회
단 , 회원의 거주지역이 서울 또는 대전인 경우
정렬은 주문번호를 기준으로 오름차순, 주문수량을 기준으로 내림차순
*/
-- 1. join 사용
SELECT cart_no , cart_prod , cart_qty , prod_name , lprod_nm 
FROM cart, prod , lprod 
WHERE cart_prod = prod_id
	AND prod_lgu = lprod_gu
	AND cart_member IN (SELECT mem_id from member where left(mem_add1,2) IN ('서울', '대전'))
ORDER BY cart_no , cart_qty DESC ;

-- 2. 서브쿼리 사용
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 , cart_qty DESC ;

 

▶ 2. 서브쿼리에서 lprod_nm을 가져오기 위해 lprod_gu 와 cart_prod의 첫 4자리가 동일함을 이용하였다. 

 

 

-- [문제]
/*
상품코드, 상품명 , 상품판매가격 조회하기
단, 회원의 마일리지 값이 100 이상이고, 주문수량이 5 이상이고, 상품분류명에 '컴퓨터'가 포함되어있는 경우
거래처 주소지 지역이 서울 or 대전 or 광주인 경우
*/
SELECT prod_id , prod_name , prod_sale ,
		(SELECT buyer_name FROM buyer 
		WHERE buyer_id = prod_buyer and left(buyer_add1 , 2) IN ('서울' , '대전' , '광주')) 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 left(prod_id,4) IN (SELECT lprod_gu FROM lprod
									WHERE lprod_nm LIKE '%컴퓨터%')  ;

-- member 조건
SELECT mem_id 
FROM member
WHERE mem_mileage >= 100;

-- cart 조건 + member조건
SELECT cart_prod
FROM cart 
WHERE cart_member in (SELECT mem_id FROM member 
								WHERE mem_mileage >= 100)
	AND cart_qty >= 5);

-- lprod 조건
SELECT lprod_gu
FROM lprod
WHERE lprod_nm LIKE '%컴퓨터%';

-- buyer 조건
SELECT buyer_id
FROM buyer
WHERE left(buyer_add1 , 2) IN ('서울' , '대전' , '광주') ;

 

▶ 4가지의 조건을 각각 실행하고 잘 실행이 되는지 확인한 다음 메인 쿼리에 붙이는 식으로 진행하였다.

거래처명의 경우에는 where에 조건을 따로 추가하지 않고 select 절에 where 조건을 추가하였다. 

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

 

▶ 위 문제를 join을 사용해서 풀어본 것이다.

이때 where 뒤에 join 관계를 외래키 =  기본키 형식으로 위치에 맞게 써주어야 한다는 것을 알게 되었다.