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 관계를 외래키 = 기본키 형식으로 위치에 맞게 써주어야 한다는 것을 알게 되었다.
'DB' 카테고리의 다른 글
(MariaDB) SQL - 조인(Join)/Inline View (3) | 2023.11.23 |
---|---|
(MariaDB) SQL - 그룹함수(집계함수) / GROUP BY / HAVING (6) | 2023.11.22 |
(MariaDB) SQL - Null 함수 / 날짜 함수 / 형 변환 함수 (1) | 2023.11.22 |
(MariaDB) SQL - 함수 (1) | 2023.11.22 |
Maria DB 설치 및 Heidi SQL 사용하기 (8) | 2023.11.20 |