201013 Oracle 집합연산자

연산자라 하면 + - * /같은 산술연산자를 떠올리기 마련인데 SQL에선 집합연산자가 많이 사용됨

 

3) 집합연산자

-union, union all, intersect, minus

 

- 합집합 :union

- 합집합에서 교집합(공통부분)을 중복함 : union all

- 교집합 : intersect

- A집합에서 B집합을 빼는것 : minus

- join연산 뒷부분에서 설명

 

 

4) 기타연산자

 

-In, any, some, all, between, exists

 

사용자가 특정 데이터를 주어졌을때 그 데이터중에 어느하나라도 일치하면 전체가 참 : in

 

(1) In

- WHERE 절의 조건식 구성에 사용

- 주어진 자료 중 어느 하나와 일치하면 전체가 참이되는 연산자

- 논리연산자 OR로 바꿔쓸 수 있다

 

*사용형식

컬럼명or수식 IN(값1, 값2,...)

 

예 ) 사원테이블에서 부서코드가 30, 70, 100에 속한 사원정보를 조회하시오

Alias는 사원번호, 사원명, 부서번호

 

WHERE DEPARTMENT_ID=30
    OR DEPARTMENT_ID=70
    OR DEPARTMENT_ID=100

이렇게 쓰는 대신에

SELECT EMPLOYEE_ID 사원번호,
        EMP_NAME 사원명, 
        DEPARTMENT_ID 부서번호
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN(30,70,100)
ORDER BY 3;

 

(2)ANY, SOME

- IN과 같다.

- IN을 ANY나 SOME으로 치환하는 경우 '연산자ANY|SOME'형식으로 표현됨

 

위의 예제를 SOME이나 ANY로 표현해보면,

WHERE DEPARTMENT_ID = ANY(30,70,100)

만약 

30,70,100 이외의것을 찾는다?

 

(IN의 경우)

WHERE DEPARTMENT_ID NOT IN(30,70,100)

(ANY나 SOME의 경우)

WHERE NOT DEPARTMENT_ID = SOME(30,70,100)

NOT을 WHERE 젤 앞에다 붙여서 전체를 부정해주기!

 

3) ALL

-주어진 조건을 모두 만족할 때만 전체가 참이되는 연산자

-논리연산자 'AND'로 치환가능

 

컬럼명|수식 ALL(값1, 값2,...)

 

(4) BETWEEN

-범위를 정할때 사용

-연속적인 데이터의 범위를 정할 때

-처리해주는 데이터의 속성이 같아야함 (연속적인 자료의 범위를 설정할 때)

 

*사용형식

컬럼명|수식 BETWEEN 값1 AND 값2

'컬럼명|수식'에 저장된 값이 '값1'에서 '값2'에 속한 값이면 참을 반환

논리연산자 AND로 치환가능

 

EX

회원테이블에서 마일리지가 2000~3000사이의 마일리지를 보유한 회원정보를 조회하시오

단, ALIAS는 회원번호, 회원명, 마일리지

 

논리연산자 AND를 이용할 경우

SELECT MEM_ID 회원번호, 
    MEM_NAME 회원명, 
    MEM_MILEAGE 마일리지, 
    MEM_JOB 직업
FROM MEMBER
WHERE MEM_MILEAGE >=2000 AND MEM_MILEAGE <=3000;

 

BETWEEN을 이용할 경우

SELECT MEM_ID 회원번호, 
    MEM_NAME 회원명, 
    MEM_MILEAGE 마일리지, 
    MEM_JOB 직업
FROM MEMBER
WHERE MEM_MILEAGE BETWEEN 2000 AND 3000;

EX

장바구니테이블(BUYPROD)에서 2005년 6월 판매된 상품을 조회하시오

ALIAS는 날짜, 상품코드, 수량, 단가, 금액

 

6월은 30일까지다

날짜는 타입이 날짜타입

BETWEEN A AND B에서 A와 B를 날짜형식으로 써줘야함

오라클에서는 날짜 (8)자리만 맞추면 문자열도 날짜로 자동 형변환이 된다

SELECT BUY_DATE 날짜,
    BUY_PROD 상품코드, 
    BUY_QTY 수량, 
    BUY_COST 단가, 
    BUY_COST*BUY_QTY 금액 
FROM BUYPROD
WHERE BUY_DATE BETWEEN '2005/06/01' AND '2005/06/30';

WHERE BUY_DATE BETWEEN '20050601' AND '20050630';

WHERE BUY_DATE BETWEEN TO_DATE('20050601') AND TO_DATE('20050630');

다 가능

 

EX

상품테이블PROD에서 상품의 분류코드가 'P300'대에 속한 제품정보를 조회하시오

ALIAS는 상품명,거래처코드, 분류코드,매입가격,판매이익 

 

SELECT PROD_NAME 상품명,
    PROD_BUYER 거래처코드, 
    PROD_LGU 분류코드,
    PROD_COST 매입가격,
    PROD_PRICE-PROD_COST 판매이익
FROM PROD
WHERE LOWER(PROD_LGU) BETWEEN 'p300' AND 'p399';    

 

 

(5) LIKE

- 패턴비교에 사용 (일정한 형식을 줘서 그 형식에 맞는지 안맞는지)

- EX)이메일 형식에 맞게 입력하시오

- 와일드카드 (매칭문자열) '%','_'가 사용된다

- % : 이 문자열이 사용된 위치 이하의 모든 문자열과 대응

   EX ) '홍%' : 첫 글자가 '홍'으로 시작하는 모든 문자열과 대응

        '%' : 모든 문자열과 대응

       '대전%', '%다' , '박%'

       **이건 날짜에 못씀! 숫자도 못씀 오로지 문자열 비교에만 쓴다!!!  

       패턴비교하면 속도가 느려진다.

- '_' : 해당 문자열이 사용된 위치에서 한글자와 대응

  EX ) '홍_동' : 세글자로 구성되어있으면서 첫글자와 마지막글자가 홍,동인 경우

 

EX

회원테이블에서 '김'씨 성을 가진 회원을 조회하시오

ALIAS는 회원번호, 회원명, 생년월일 ,취미

SELECT MEM_ID 회원번호, 
    MEM_NAME 회원명, 
    MEM_BIR 생년월일 ,
    MEM_LIKE 취미
FROM MEMBER
WHERE MEM_NAME LIKE '김%';

EX

회원테이블에서 '김'씨 성을 가진 회원을 조회하시오

ALIAS는 회원번호, 회원명, 주소

SELECT MEM_ID 회원번호, 
    MEM_NAME 회원명,
    MEM_ADD1 || ' ' || MEM_ADD2 주소
FROM MEMBER
WHERE MEM_ADD1 LIKE '충남%';

 

 

EX 장바구니테이블에서 2005년도 7월에 판매된 상품정보를 조회하시오

ALIAS는 상품코드, 수량

 

SELECT CART_PROD 상품코드, 
        CART_QTY 수량
FROM CART
WHERE CART_NO LIKE '200507%';

EX

SELECT  TO_DATE(SUBSTR(A.CART_NO,1,8)) 날짜,
        B.PROD_NAME 상품명,
        A.CART_PROD 상품코드, 
        A.CART_QTY 수량,
        B.PROD_PRICE 단가,
        A.CART_QTY*B.PROD_PRICE 금액
FROM CART A, PROD B
WHERE CART_NO LIKE '200507%'
        AND B.PROD_ID = A.CART_PROD;

JOIN을 이용함 (두개의 테이블을 썼다)

이떄 두개의 테이블 사이에는 관계가 맺어져 있어야함 (똑같은 자료가 양쪽테이블에 저장이 되어있어야한다)

 

 

EX

2005년 1월 매입현황을 조회하시오

ALIAS는 날짜, 상품코드, 매입수량

SELECT BUY_DATE 날짜,
        BUY_PROD 상품코드,
        BUY_QTY 매입수량
FROM BUYPROD
WHERE BUY_DATE BETWEEN '20050101' AND '20050131';

LIKE는 절대 날짜에 쓸수없다.

 

WHERE BUY_DATE LIKE '200501%';

이렇게 쓰면 오류는 안나지만 데이터가 안나옴

 

 날짜비교에는 %를 쓰지말고 BETWEEN을 써야한다 

 

%를 꼭 쓰고싶다??? 구별자 확인해서 구별자를 꼭 집어넣어야한다.

WHERE BUY_DATE LIKE '2005/01/%';