Oracle 8강 - ANY,ALL,IN,LIKE,BETWEEN연산자

2)ANY(SOME)연산자

• 주어진 데이터 중 어느 하나와 일치하면 전체 결과가 참으로 반환
• IN, SOME과 함께 다중 행 연산자로 역활 수행
• IN 연산자 또는 OR 연산자로 대치 가능
  (사용형식)
  espr 관계연산자ANY|SOME(값1[,값2...])

**모르는 자료를 가지고 비교할 때, 확정되지 않은 값을 가지고 비교할 때 서브쿼리 사용

사용예) 회원테이블에서 모든 남성회원들보다 마일리지가 많은 여성회원을 조회하시오

(서브쿼리 : 모든 남성회원들의 마일리지)

SELECT MEM_MILEAGE
  FROM MEMBER
 WHERE SUBSTR(MEM_REGNO2,1,1) IN('1','3');
 --SUBSTR(컬럼명,숫자1,숫자2) : 컬럼의 '숫자1'의 자리부터 시작해서 '숫자2'개 만큼의 문자열 추출

(메인쿼리 : 서브쿼리 결과보다 많은 마일리지를 보유한 여성회원의 정보)
-- 조건 : 여성, 서브쿼리 결과보다 많은 마일리지

SELECT MEM_ID AS 회원번호
       MEM_NAME AS 회원명
       MEM_REGNO1||'-'||MEM_REGNO2 AS 주민번호
       MEM_MILEAGE AS 마일리지
  FROM MEMBER
 WHERE SUBSTR(MEM_REGNO2,1,1) IN('2','4')
   AND MEM_MILEAGE > ALL(SELECT MEM_MILEAGE
 			   FROM MEMBER
			  WHERE SUBSTR(MEM_REGNO2,1,1) IN('1','3'));
 

--ANY일 때는 남성회원의 마일리지 중에 가장 작은 사람보다는 큰 값,
ALL을 쓸 때는 모든 남성회원들 중 가장 큰 수 보다 큰 값을 구함.

사용예)사원테이블에서 10,90,110번 부서에 속한 사원번호,사원명,급여를 출력하시오

(OR 연산자)
SELECT EMPLOYEE_ID AS 사원번호,
       FIRST_NAME||' '||LAST_NAME AS 사원명,
       DEPARTMENT_ID AS 부서코드,
       SALARY AS 급여
  FROM HR.EMPLOYEES
 WHERE DEPARTMENT_ID = 10
    OR DEPARTMENT_ID = 90
    OR DEPARTMENT_ID = 110
    
 (IN 연산자)   
    SELECT EMPLOYEE_ID AS 사원번호,
       FIRST_NAME||' '||LAST_NAME AS 사원명,
       DEPARTMENT_ID AS 부서코드,
       SALARY AS 급여
  FROM HR.EMPLOYEES
 WHERE DEPARTMENT_ID IN(10,90,110);

(ANY,SOME 연산자)   
    SELECT EMPLOYEE_ID AS 사원번호,
       FIRST_NAME||' '||LAST_NAME AS 사원명,
       DEPARTMENT_ID AS 부서코드,
       SALARY AS 급여
  FROM HR.EMPLOYEES
 WHERE DEPARTMENT_ID =ANY(10,90,110);
--WHERE DEPARTMENT_ID =SOME(10,90,110);

3)ALL 연산자

• 주어진 데이터 모두와 일치해야 결과가 참이 되는 연산자
• IN과 함께 다중 행 연산자로 역활 수행
• AND 연산자로 대치 가능
  (사용형식)
  expr 관계연산자ALL(값1,[,값2...,])
  . 사용되는 관계연산자 중 '='은 사용되는 경우가 없음 (부등호와 함께 사용됨)

사용예)회원테이블에서 거주지가 '충남'인 모든 회원들의 마일리지보다 더 많은 마일리지를 보유하고 있는 회원번호, 회원명, 거주지, 마일리지를 조회
거주지는 광역시도임

 (서브쿼리 : 거주지가 '충남'인 모든 회원들의 마일리지)
    SELECT MEM_MILEAGE
      FROM MEMBER
     WHERE SUBSTR(MEM_ADD1,1,2) = '충남'; 
  
  (서브쿼리의 결과보다 많은 마일리지 보유회원정보 조회)
  SELECT MEM_ID AS 회원번호, 
         MEM_NAME AS 회원명, 
         SUBSTR(MEM_ADD1,1,2) AS 거주지, 
         MEM_MILEAGE AS 마일리지
    FROM MEMBER
   WHERE MEM_MILEAGE >ALL(SELECT MEM_MILEAGE
                            FROM MEMBER
                           WHERE SUBSTR(MEM_ADD1,1,2)= '충남');
                           

4)LIKE 연산자

• 패턴을 비교하는 연산자
• 패턴을 구성하는 패턴구성 문자(와일드카드)로 '%','_'가 사용
• 문자열 비교연산자(숫자 및 날짜 비교에 사용하지 말 것)
• 자주 사용하면 성능저하의 원인
  (사용형식)
  expr LIKE '패턴문자열'
• '%' : '%'가 기술된 위치에서 이후의 모든 문자열과 대응
  ex) '김%' -> 첫 글자가 '김'으로 시작하는 모든 문장과 대응
  '%다' -> 마지막 글자가 '다'인 모든 문장과 대응
  '%전%' -> 문장 중 '전'이라는 글자가 존재하면 일치하는 문장으로 취급
• '_' : '_'가 기술된 위치에서 이후의 모든 문자열과 대응
  ex) '김_' -> 두 글자로 구성되며 첫 글자가 '김'이어야 결과가 참
  '_다' -> 두 글자로 구성되며 두 번째 글자가 '다'이어야 결과가 참
  '_전_' -> 세 글자로 구성되며 가운데 글자가 '전'이라는 글자여야 결과가 참

사용예) 회원테이블에서 서울에 살고 있는 회원의 회원번호, 회원명, 주소, 핸드폰번호를 조회하시오

SELECT MEM_ID AS 회원번호,
       MEM_NAME AS 회원명,
       MEM_ADD1||' '||MEM_ADD2 AS 주소,
       MEM_HP AS 핸드폰번호
  FROM MEMBER
 WHERE MEM_ADD1 LIKE '서울%';

사용예)장바구니테이블에서(CART) 2005년 4월 판매정보를 조회하시오
Alias는 구매회원 번호, 상품번호, 구매수량이다.

   SELECT CART_MEMBER AS "구매회원 번호", 
         CART_PROD AS 상품번호, 
         CART_QTY AS 구매수량
    FROM CART
   WHERE CART_NO LIKE '200504%';

-- CART_NO에서 날짜 추출해야함. / 회원별로 상품수량, 상품금액을 곱하면 총 회원 구매금액나옴.

사용예)상품테이블에서 '컴퓨터제품'(분류코드 'P101')에 속한 제품정보를
상품코드를 이용하여 조회하시오.
Alias는 상품코드,상품명,거래처코드,매입가격

  SELECT PROD_ID AS 상품코드,
         PROD_NAME AS 상품명,
         PROD_BUYER AS 거래처코드,
         PROD_COST AS 매입가격
    FROM PROD 
   WHERE PROD_ID LIKE 'P101%'; --기본키의 특징 : 글자의 길이가 같다

5)BETWEEN 연산자

• 범위를 지정할 때 사용
• 논리연산자 AND로 치환 가능
• 모든 데이터 타입 사용 가능
  (사용형식)
  expr BETWEEN 값1 AND 값2;
  . expr의 값이 '값1'에서 '값2' 사이의 값을 가지면 참
  . '값1'과 '값2'의 데이터 타입은 같아야 함
  

사용예) 사원테이블에서 30-70번 부서에 속한 사원정보를 조회하시오
Alias는 사원번호,사원명,부서번호,직무코드이며
부서코드 순으로 출력하시오

  (AND 연산자 사용) 
  SELECT EMPLOYEE_ID AS 사원번호,
         FIRST_NAME||' '||LAST_NAME AS 사원명,
         DEPARTMENT_ID AS 부서번호,
         JOB_ID AS 직무코드
    FROM HR.EMPLOYEES
   WHERE DEPARTMENT_ID >=30 AND DEPARTMENT_ID<=70
   ORDER BY 3;
   
   (IN 연산자 사용)
   SELECT EMPLOYEE_ID AS 사원번호,
         FIRST_NAME||' '||LAST_NAME AS 사원명,
         DEPARTMENT_ID AS 부서번호,
         JOB_ID AS 직무코드
    FROM HR.EMPLOYEES
   WHERE DEPARTMENT_ID IN(30,40,50,60,70)
   ORDER BY 3;

   (=SOME, =ANY 연산자 사용)
   SELECT EMPLOYEE_ID AS 사원번호,
         FIRST_NAME||' '||LAST_NAME AS 사원명,
         DEPARTMENT_ID AS 부서번호,
         JOB_ID AS 직무코드
    FROM HR.EMPLOYEES
   WHERE DEPARTMENT_ID =ANY(30,40,50,60,70)
   ORDER BY 3;
  
   (BETWEEN 연산자 사용) 
   SELECT EMPLOYEE_ID AS 사원번호,
         FIRST_NAME||' '||LAST_NAME AS 사원명,
         DEPARTMENT_ID AS 부서번호,
         JOB_ID AS 직무코드
    FROM HR.EMPLOYEES
   WHERE DEPARTMENT_ID BETWEEN 30 AND 70 
   ORDER BY 3;
   

사용예)2005년 1월-2월 매입정보를 조회하시오.
Alias는 날짜,매입상품,매입수량,매입금액이며 날짜순으로 출력하시오.

  SELECT BUY_DATE AS 날짜,
         BUY_PROD AS 매입상품,
         BUY_QTY AS 매입수량,
         BUY_COST AS 매입금액
    FROM BUYPROD
   WHERE BUY_DATE BETWEEN TO_DATE('20050101') AND LAST_DAY(TO_DATE('20050204'))
   ORDER BY 1;

--날짜 타입이 없어서 8자리의 문자열로 날짜타입을 표현할 수 있는 함수를 써줌

• TO_DATE : 문자열을 날짜타입으로 바꿔주는 함수(날짜를 나타낼 때는 날짜타입으로 변환해줘야 오류가 안나옴.)
• LAST_DAY : 해당하는 달의 마지막 날을 나타내주는 함수
• BETWEEN에는 고정된 값이 와야함(와일드카드 쓸 수 없음)

1. 문자열 함수
   1)CONCAT(C1,C2) (||와 같은 역할)
   - 주어진 문자열 C1, C2를 결합하여 새로운 문자열 반환
   - '||'연산자와 동일 기능
   

사용예)회원테이블에서 회원명과 주민번호를 출력하시오
단 주민번호는 'XXXXXX-XXXXXXX'형식으로 출력하며 CONCAT함수를 사용

  SELECT MEM_NAME AS 회원명, 
         CONCAT(CONCAT(MEM_REGNO1,'-'),MEM_REGNO2) AS 주민번호
    FROM MEMBER;
 

함수(매개변수,매개변수) - 매개변수(Argument/Parameter) : 함수가 실행되기 위해 필요한 데이터의 통로
C: 문자
N: 숫자
D: DATE