[DBMS/Oracle] 연산자

[DBMS/ORACLE] 연산자

연산자

1. 사칙연산자 : +, -, *, /

사용예)HR계정의 사원테이블(EMPLOYEES)에서 보너스를 계산하고 보너스를 포함한 지급액을 조회하시오.
      Alias는 사원번호,사원명,급여,보너스,지급액
      보너스 = 급여(SALARY) * 영업실적코드(COMMISSION_PCT)
      지금액 = 급여 + 보너스
      
  SELECT EMPLOYEE_ID As 사원번호, -- 복수, 단수 확인
         FIRST_NAME||' '||LAST_NAME AS 사원명,
         SALARY AS 급여,
         NVL(SALARY * COMMISSION_PCT,0) AS 보너스,
         SALARY + NVL(SALARY * COMMISSION_PCT,0) AS 지급액
         -- COMMISSION_PCT 값을 확인해보면 08부서(영업부서)를 제외하고 나머지 사원은 NULL 값을 가짐
         -- SALARY + NULL = NULL
         -- NULL 값이 하나라도 있다면 사칙연산에서 결과값은 NULL이 나옴
    FROM HR.EMPLOYEES -- 다른 계정의 테이블을 참조할 때는 반드시 계정명을 작성해야함

 

2. 비교연산잔(관계연산자) : 결과가 true/false 로 변환

  - >, <, =, >=,  <=, !=(<>) 중간에 공백이 오면 안된다.

  - 조건식에 사용

  - 조건식은 WHERE 절과 CASE WHEN - THEN(표현식: SELECT 절에 사용)의 조건식에 사용

사용예) 회원테이블에서 보유마일리지가 5000이상인 회원의 회원번호, 회원명, 직업, 마일리지를 조회하시오. 
       단, 마일리지가 많은 회원부터 출력하시오.
  SELECT MEM_ID AS 회원번호, 
         MEM_NAME AS 회원명, 
         MEM_JOB AS 직업, 
         MEM_MILEAGE AS 마일리지
    FROM MEMBER
   WHERE MEM_MILEAGE >= 5000
   ORDER BY MEM_MILEAGE DESC;

 

사용예) 매입테이블(BUYPROD)에서 2020년 1월에 매입된 상픔에서 매입수량의 합계가 50개 이상인 매입정보를 조회하시오
       Alias는 상품코드, 매입수량, 매입금액이며 매입금액이 많은 상품부터 출력하시오.
  SELECT BUY_PROD AS 상품코드, 
         SUM(BUY_QTY) AS 매입수량, 
         SUM(BUY_QTY * BUY_COST) AS 매입금액
    FROM BUYPROD
   WHERE BUY_DATE >= '20200101' AND BUY_DATE <= '20200131'
   GROUP BY BUY_PROD
  HAVING SUM(BUY_QTY) >= 50
   ORDER BY 3 DESC;

 

3. 논리 연산자 : NOT, AND, OR

  - ()를 사용해서 순서를 명확하게 제시하는 것이 좋다

사용예) 회원테이블(MEMBER)에서 여성회원의 회원번호,회원명,직업,주민번호2,마일리지를 조회하시오.
  SELECT MEM_ID AS 회원번호,
         MEM_NAME AS 회원명,
         MEM_JOB AS 직업,
         MEM_REGNO2 AS 주민번호2,
         MEM_MILEAGE AS 마일리지
    FROM MEMBER
        -- SUBSTR()시작글자, 자르는 갯수, 첫번째 자리에서 한 글자 자르기
   WHERE SUBSTR(MEM_REGNO2,1,1) = '2' OR SUBSTR(MEM_REGNO2,1,1) = '4'

 

사용예) 올해가 윤년인지 평년인지 구별하시오
  SELECT CASE WHEN (MOD(EXTRACT(YEAR FROM SYSDATE),4)=0 AND
                    MOD(EXTRACT(YEAR FROM SYSDATE),100)!=0) OR 
                    (MOD(EXTRACT(YEAR FROM SYSDATE),400)!=0) THEN
                    '올해는 윤년입니다'
              ELSE
                    '올해는 평년입니다.'
          END AS 결과
    FROM DUAL;

 

사용예) 사원들의 평균급여보다 적은급여를 받는 사원의 사원번호,사원명,급여를 
       조회하되 NOT 연산자를 사용하시오.
       
(평균급여)
  SELECT AVG(SALARY)
    FROM HR.EMPLOYEES;
      
  SELECT EMPLOYEE_ID AS 사원번호,
         FIRST_NAME||' '||LAST_NAME AS 사원명,
         SALARY AS 급여
    FROM HR.EMPLOYEES
   WHERE NOT SALARY>=(SELECT AVG(SALARY)
                        FROM HR.EMPLOYEES)
   ORDER BY 3 DESC;

 

4. IN 연산자

  - 주어진 자료 중 어느 하나와 일치하면 true를 반환
  - 연산자에 '='의미가 내포되어 있음

(사용형식)
  expr IN(값1 [,값2,...])

  - 'expr'(표현식:컬럼이나 수식, 함수 등)의 값이 '값1' ~ '값n' 중 어느 하나와 일치하면 전체 결과는 true가 됨
  - 연속적이지 않고 불규칙한 값들과 비교할 때 주로 사용
  - 'OR'연산자로 바꾸어 쓸 수 있음
  - in연산자는 다른 연산자와 결합해서 쓸 수 없음, 범위가 정해지지 않은 연산
  - between a and b : a부터 b까지, 범위가 정해진 연산

 

사용예) 상품테이블(PROD)에서 분류코드 'P102','P201','P302'에 속한 상품의
       상품번호,상품명,매입가,매출가를 조회하시오.
       
(OR 연산자 사용)
  SELECT PROD_ID AS 상품번호,
         PROD_LGU AS 분류코드,
         PROD_NAME AS 상품명,
         PROD_COST AS 매입가,
         PROD_PRICE AS매출가
    FROM PROD
   WHERE PROD_LGU='P102'
      OR PROD_LGU='P201'
      OR PROD_LGU='P302'
    ORDER BY 1;

(IN 연산자 사용)
  SELECT PROD_ID AS 상품번호,
         PROD_LGU AS 분류코드,
         PROD_NAME AS 상품명,
         PROD_COST AS 매입가,
         PROD_PRICE AS매출가
    FROM PROD
   WHERE PROD_LGU IN('P102','P201','P302')
    ORDER BY 1;
    
(ANY(SOME) 연산자 사용)
  SELECT PROD_ID AS 상품번호,
         PROD_LGU AS 분류코드,
         PROD_NAME AS 상품명,
         PROD_COST AS 매입가,
         PROD_PRICE AS 매출가
    FROM PROD
   WHERE PROD_LGU =ANY('P102','P201','P302') -- =ANY, 붙여써야 함
    ORDER BY 1;

 

5. ANY(SOME) 연산자

  - ANY와 SOME은 동일 기능과 동일 사용방법을 가지고 있음

(사용형식)
  expr 관계연산자 ANY|SOME(값1, 값2 [,값3, ...])

- 'expr' 의 값이 ( )안의 어느 하나와 '관계연산자'의 조건을 만족하면 true반환
- 관계연산자 '>'와 같이 사용하면 ( )안의 값 중 최소값 보다 큰 자료를 검색할 수 있음

 

** ALTER 명령

  - DCL에 속한 명령으로 객체의 구조를 변경에 사용

  1) TABLE의 컬럼 추가, 변경, 삭제
    (1) 컬럼의 추가 
      ALTER TABLE 테이블명 ADD(컬럼명 데이터 타입[크기])

    (2) 컬럼 수정(데이터 타입 변경, 크기변경)
      -- 이미 데이터가 저장되어 있을 때 크기는 작은 사이즈로 변경 불가 
      ALTER TABLE 테이블명 MODIFY(컬럼명 데이터타입[크기])
        
    (3) 컬럼 삭제
      -- 다른 테이블에서 참조하는 기본키일 경우 삭제 불가
      ALTER TABLE 테이블명 DROP COLUMN 컬럼명
            
    (4) 컬럼명 변경
      ALTER TABLE 테이블명 RENAME COLUMN OLD_컬럼명 TO NEW_컬럼명
            
  2) 제약조건의 추가, 변경에 사용
    (1) 제약조건(기본키 or 외래키) 추가
      ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건이름 제약조건
        
    (2) 제약조건(기본키 or 외래키) 변경 (제약조건 이름변경)
      ALTER TABLE 테이블명 RENAME CONSTRAINT OLD_제약조건명 TO NEW_제약조건명 
        
    (3) 제약조건(기본키 or 외래키) 삭제
      ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건이름
        
  3) 테이블명의 변경에 사용
  ALTER TABLE OLD_테이블명 RENAME TO NEW_테이블명

사용예) HR계정의 EMPLOYEES 테이블에 새로운 컬럼 EMP_NAME VARCHAR2(50)를 추가하시오
  ALTER TABLE HR.EMPLOYEES ADD (EMP_NAME VARCHAR2(50));

 

** UPDATE문
  - DML(INSERT,UPDATE, DELETE, MERGE)에 속한 명령
  - 존재하는 자료의 내용의 변경처리

(사용형식)
    UPDATE 테이블명
       SET 컬럼명1=값1[,]
           [컬럼명2=값2[,]]
                :
           [컬럼명n=값n]
    [WHERE 조건]

사용예) 사원테이블에서 EMP_NAME컬럼에 FIRST_NAME과 LAST_NAME에 저장된 자료를 결합하여 저장하시오
  UPDATE HR.EMPLOYEES
     SET EMP_NAME=FIRST_NAME||' '||LAST_NAME;
       
  SELECT FIRST_NAME,LAST_NAME,EMP_NAME
    FROM HR.EMPLOYEES;
  COMMIT;

 

사용예) 사원테이블에서 100번 부서에 속한 사원의 최소 급여보다 많은 급여를 받는 사원의
       사원번호,사원명,부서코드,급여를 조회하시오.
  SELECT EMPLOYEE_ID AS 사원번호,
         EMP_NAME AS 사원명,
         DEPARTMENT_ID AS 부서코드,
         SALARY AS 급여
    FROM HR.EMPLOYEES
   WHERE SALARY > ANY(12008,9000,8200,7700,7800,6900)
     AND DEPARTMENT_ID != 100
   ORDER BY 4 DESC;

(SUBQUERY 사용)
  SELECT EMPLOYEE_ID AS 사원번호,
         EMP_NAME AS 사원명,
         DEPARTMENT_ID AS 부서코드,
         SALARY AS 급여
    FROM HR.EMPLOYEES
   WHERE SALARY > ANY(SELECT SALARY
                        FROM HR.EMPLOYEES
                       WHERE DEPARTMENT_ID = 100)
     AND DEPARTMENT_ID != 100
   ORDER BY 4 DESC;

 

6. ALL

(사용형식)
  expr 관계연산자 ALL(값1,값2[,값3,...])

  - expr에 저장된 값이 ( )안의 모든 값과 관계연산자 조건을 만족하면 true 반환
  - 사용하는 관계연산자 중 '='는 사용할 수 없음
  - '>'를 사용한 ALL은 '값1' ~ '값n'사이의 값 중 최댓값보다 큰 경우의 결과 출력

 

사용예) 사원테이블에서 100번 부서에 속한 사원의 최소 급여보다 많은 급여를 받는 사원의
       사원번호,사원명,부서코드,급여를 조회하시오.
  SELECT EMPLOYEE_ID AS 사원번호,
         EMP_NAME AS 사원명,
         DEPARTMENT_ID AS 부서코드,
         SALARY AS 급여   
    FROM HR.EMPLOYEES
   WHERE SALARY > ALL(SELECT SALARY
                        FROM HR.EMPLOYEES
                       WHERE DEPARTMENT_ID=100)
   ORDER BY 4 DESC;

 

7. BETWEEN 연산자

  - 연속적이거나 규칙성을 가지고 있는 값들을 비교할 때 사용
  - 주로 범위 설정에 사용
  - AND 연산자를 대신할 수 있음

(사용형식)
  expr BETWEEN 값1 AND 값2

  - expr의 값이 '값1' ~ '값2' 사이의 값이면 참(true)을 반환
  - expr >= 값1 AND expr <= 값2로 바꾸어 사용할 수 있음
  - 모든 데이터 타입에서 사용 가능

 

사용예) 매입테이블 (BUYPROD)에서 4월 1일 ~ 4월 20일까지 날짜순으로 매입정보를 조회
       Alias는 날짜, 상품코드, 수량, 금액
(AND 연산자 사용)
  SELECT BUY_DATE AS 날짜, 
         BUY_PROD AS 상품코드, 
         BUY_QTY AS 수량, 
         BUY_QTY * BUY_COST  AS 금액
    FROM BUYPROD
   WHERE BUY_DATE >= TO_DATE('20200401') AND BUY_DATE <= TO_DATE('20200420')
   ORDER BY 1;
      
(BETWEEN 연산자 사용)
  SELECT BUY_DATE AS 날짜, 
         BUY_PROD AS 상품코드, 
         BUY_QTY AS 수량, 
         BUY_QTY * BUY_COST  AS 금액
    FROM BUYPROD
   WHERE BUY_DATE BETWEEN TO_DATE('20200401') AND TO_DATE('20200420')
   ORDER BY 1;

 

사용예) 상품테이블에서 분류코드 'P200'번대에 분류에 속한 상품의 상품번호,상품명,판매가,매입가를 조회하시오.
(AND 연산자)
  SELECT PROD_ID  AS 상품번호,
         PROD_NAME AS 상품명,
         PROD_LGU AS 분류코드,
         PROD_PRICE AS 판매가,
         PROD_COST AS 매입가
    FROM PROD
   WHERE PROD_LGU >= 'P200' AND PROD_LGU <= 'P299'
       
(BETWEEN 연산자)
  SELECT PROD_ID  AS 상품번호,
         PROD_NAME AS 상품명,
         PROD_LGU AS 분류코드,
         PROD_PRICE AS 판매가,
         PROD_COST AS 매입가
    FROM PROD
   WHERE UPPER(PROD_LGU) BETWEEN 'P200' AND 'P299'

 

사용예) 회원테이블에서 보유마일리지가 2000-3000사이인 회원들의 회원번호,회원명,나이,성별,마일리지를 조회하시오
  SELECT MEM_ID AS 회원번호, 
         MEM_NAME AS 회원명,
         EXTRACT(YEAR FROM SYSDATE)-EXTRACT(YEAR FROM MEM_BIR) AS 나이,
         CASE WHEN SUBSTR(MEM_REGNO2,1,1) IN ('2','4') THEN
                   '여성'
              ELSE
                   '남성'
              END AS 성별,
          MEM_MILEAGE AS 마일리지
     FROM MEMBER
    WHERE MEM_MILEAGE BETWEEN 2000 AND 3000;

 

8. LIKE 연산자

  - 패턴비교 연산자
  - 문자열 비교 연산자(숫자나 날짜 비교에 사용을 자제할 것)
  - 패턴을 구성하는 문자로 '%','_'(와일드카드) 사용

 

  . '%' : '%'가 사용된 이후의 모든 문자열과 대응(공백도 대응됨)
  . ex) '김%' : '김'자로 시작하는 모든 문자열은 참(true)을 반환
        '%김' : '김'자로 끝나는 모든 문자열은 참(true)을 반환
        '%김%' : 문자열 내부에 '김'이 있으면 참(true)을 반환
  . '_' : '_'가 사용된 위치의 하나의 문자열과 대응
  . ex) '김_' : '김'자로 시작하는 2개의 문자로 구성된 문자열은 참(true)을 반환
        '_김' : '김'자로 끝나고 2개의 문자로 구성된 문자열은 참(true)을 반환
        '_김_' : 3개의 문자로 구성되고 문자열 내부에 '김'이 있으면 참(true)을 반환

(사용형식)
  컬럼명 LIKE '패턴문자열'

사용예) 회원테이블에서 거주지가 충남인 회원들의 회원번호,회원명,주소를 조회하시오.
  SELECT MEM_ID AS 회원번호,
         MEM_NAME AS 회원명,
         MEM_ADD1||' '||MEM_ADD2 AS 주소
    FROM MEMBER    
   WHERE MEM_ADD1 LIKE '충남%'
      OR MEM_ADD1 LIKE '충청남%'

 

사용예) 장바구니 테이블에서 2020년 6월 매출정보를 조회하시오
       Alias는 날짜,상품번호,판매수량   
  SELECT TO_DATE(SUBSTR(CART_NO,1,8)) AS 날짜,
         CART_PROD AS 상품번호,
         CART_QTY AS 판매수량
    FROM CART
   WHERE CART_NO LIKE '202006%'
   ORDER BY 1;

 

사용예) 매입테이블에서 2020년 6월 매입정보를 조회하시오
       Alias는 날짜,상품번호,매입수량
  SELECT BUY_DATE AS 날짜,
         BUY_PROD AS 상품번호,
         BUY_QTY AS 판매수량
    FROM BUYPROD 
   WHERE BUY_DATE BETWEEN TO_DATE('20200601') AND TO_DATE('20200630')      
   ORDER BY 1;