기타연산자
(IN,ANY,SOME,EXISTS,ALL,BETWEEN,LIKE)
1) IN 연산자
비교대상이 여러개이고 비연속적일때 많이사용
-
하나의 컬럼과 다수개의 데이터를 비교하여 그중 어느 하나라도 일치하면 전체 결과가 참(TRUE)이 됨
-
=ANY, =SOME 으로 치환될 수 있음
-
OR 연산자로 치환 가능
(사용형식)
expr IN(값1[,값2,...]). 연속적이지 않은 값들을 비교하는 경우 주로 사용
-- OR 연산자로 바꿔쓸수있음
사용예) 사원 테이블에서 부서번호 10-30번부서의 사원정보를 조회하시오.
Alias는 사워번호, 사원명, 부서번호, 직무코드
=>이 경우 IN 연산자보다 BETWEEN 또는 AND 연산자가 효율적임
SELECT EMPLOYEE_ID AS 사원번호,
FIRST_NAME||' '||LAST_NAME AS 사원명,
DEPARTMENT_ID AS 부서번호,
JOB_ID AS 직무코드
FROM HR.EMPLOYEES
WHERE DEPARTMENT_ID >= 10 AND DEPARTMENT_ID <=30
-- AND 연산자 쓴경우
-- BETWEEN 연산자를 쓰는 경우:
WHERE DEPARTMENT_ID BETWEEN 10 AND 30;
-- IN 연산자를 쓰는경우: WHERE DEPARTMENT_ID(10,20,30);
-- OR 연산자를 쓰는경우: WHERE DEPARTMENT_ID
= 10 OR DEPARTMENT_ID = 20 OR DEPARTMENT_ID = 30;
ORDER BY 3;사용예) 사원 테이블에서 부서번호 30, 70, 90, 100번부서의 사원정보를 조회하시오.
Alias는 사워번호, 사원명, 부서번호, 직무코드
=>이 경우 IN 연산자가 다른 연산자보다 더 효율적임
SELECT EMPLOYEE_ID AS 사원번호,
FIRST_NAME||' '||LAST_NAME AS 사원명,
DEPARTMENT_ID AS 부서번호,
JOB_ID AS 직무코드
FROM HR.EMPLOYEES
WHERE DEPARTMENT_ID IN(30,70,90,100)
-- OR 연산자를 쓰는 경우:
WHERE DEPARTMENT_ID = 30 OR DEPARTMENT_ID
= 70 OR DEPARTMENT_ID = 90 OR DEPARTMENT_ID = 100
-- =SOME, =ANY 연산자를 쓰는경우:
WHERE DEPARTMENT_ID =ANY(30,70,90,100)
ORDER BY 3;사용예) 사원의 수가 5명 이상인 부서에 속한 사원들을 조회하시오
Alias는 사원번호, 사원명, 부서코드
SELECT EMPLOYEE_ID AS 사원번호,
FIRST_NAME || ''|| LAST_NAME AS사원명,
DEPARTMENT_ID AS 부서코드
FROM HR.EMPLOYEES
WHERE DEPARTMENT_ID IN (SELECT A.DID
FROM (SELECT DEPARTMENT_ID AS DID,
COUNT(*) AS CNT
FROM HR.EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(*) >=5) A)
ORDER BY 3;2) ANY(SOME) 연산자
ANY=SOME 서로 다른게 1도없음
-
주어진 데이터 중 어느 하나와 일치하면 전체 결과가 참으로 반환
-
IN 과 함께 다중 행 연산자로 역할 수행 --IN은 동등성만 평가. ANY(SOME)은 나머지 대소관계도 평가.
-
IN 연산자 또는 OR 연산자로 대치 가능
(사용형식)
expr(수식이나컬럼) 관계연산자ANY|SOME(값1[,값2,...])
확정되지않은, 알지못하는 값을 가지고 연산이 수행될때 사용되는 방법이 서브쿼리
**사용예) 회원테이블에서 모든 남성회원들 보다 마일리지가 많은 여성회원을 조회하시오.
--여성회원들이 가지고 있는 MM_MILEGE > ANY 모든 남성회원의 마일리지**
(서브쿼리 :모든 남성회원들의 마일리지 구하기)
SELECT MEM_MILEAGE
FROM MEMBER
WHERE SUBSTR(MEM_REGNO2,1,1) IN('1','3');
-- 맨왼쪽 첫번째(1)에서 (1)개를 떼어라. 그게 1이나 3이면 추출.
추출한건 문자열
-- 회원들의 주민번호 뒷자리7자리중 첫째자리로 1이거나 3일때
-- WHERE절(줄수)없음 = 전체다구함(메인쿼리: 서브쿼리 결과 보다 많은 마일리지를 보유한 여성회원의 정보)
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
-- 서브쿼리보다 값이 커야한다.
-- 남성마일리지 최대 5300이고 최소가 1000임.
-- ANY를 쓰면 서브쿼리에 해당하는 마일리지가 OR로 연결됨.
-- 한 여성의 마일리지가 1300이다라고 하면 남성 1000보다 커서
참이되고 OR이라 전체 참됨.
-- 즉, 남성마일리지의 데이터중 아무거나에서 비교했을때
하나라도 크면 참이됨.
FROM MEMBER
WHERE SUBSTR(MEM_REGNO2,1,1) IN('1','3'));
**
사용예) 사원테이블에서 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);3) ALL 연산자
-
주어진 데이터 모두와 일치해야 전체 결과가 참이죄는 연산자
-
IN과 함께 다중 행 연산자로 역할 수행
-
AND 연산자로 대치 가능
(사용형식)
expr 관계연산자 ALL (값1[,값2,...]). 사용되는 관계연산자 중 '='은 사용되는 경우가 없음
-- 위에 경우를 예로 들면 WHERE DEPARTMENT_ID =ALL(10,90,110); 이면 부서코드가 10이면서 90이면서 110인이됨.
-- = 다가속성 -> 관계형데이터베이스에서는 허용하지않음
**사용예) 회원 테이블에서 거주지가 '충남'인 모든 회원들의 마일리지보다 더 많은 마일리지를 보유하고 있는 회원의
회원번호, 회원명, 거주지, 마일리지를 조회. 거주지는 광역시도임.**
(서브쿼리: 거주지가 '충남'-> 기본주소 인 모든 회원들의 마일리지)
SELECT MEM_MILEAGE
FROM MEMBER
WHERE SUBSTR(MEM_ADD1, 1,2)= '충남';
-- 첫번째에서 두글자를 뺀것이= 충남
(메인쿼리: 서브쿼리의 결과보다 많은 마일리지 보유회원정보 조회)
--5300보다 마일리지 많아야함.
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%';사용예) 상품테이블에서 '컴퓨터제품'(분류코드 'P101')에 속한 제품정보를 상품 코드를 이용하여 조회하시오
Alias는 상품코드, 상품명, 거래처코드, 매입가격
SELECT PROD_ID AS 상품코드,
PROD_NAME AS 상품명,
PROD_BUYER AS 거래처코드,
PROD_COST AS 매입가격
FROM PROD
WHERE PROD_ID LIKE 'P101%';
-- 기본키는 보통 CHAR 고정길이.5) BETWEEN 연산자
-
범위를 지정할 때 사용
-
논리 연산자 AND로 치환 가능
-
모든 데이터 타입 사용 가능
(사용형식)
expr BETWEEN 값1 AND 값2;. expr의 값이 '값1' 에서 '값2' 사이의 값을 가지면 참
. '값1'과 '값'의 데이터 타입은 같아야 함.
사용예) 사원테이블에서 30-70번 부서에 속한 사원정보를 조회하시오
Alias는 사원번호, 사원명, 부서번호, 직무코드이며 부서코드 순으로 출력하시오.
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
-- AND를 쓰면 WHERE DEPARTMENT_ID >=30
AND WHERE DEPARTMENT_ID<=70
-- IN을 쓰면 WHERE DEPARTMENT_ID IN(30,40,50,60,70)
-- SOME/ANY를 쓰면 WHERE DEPARTMENT_ID =ANY(30,40,50,60,70)
ORDER BY 3;
사용예) 2005년 1월-2월 매입정보를 조회하시오.
Alias는 날짜, 매입상품, 매입수량, 매입금액이며 날짜순으로 출력하시오
SELECT BUY_DATE AS 날짜, --날짜타입
BUY_PROD AS 매입상품,
BUY_QTY AS 매입수량,
BUY_COST * BUY_QTY AS 매입금액 --매입금액이랑 매입단가 다름.
FROM BUYPROD
WHERE BUY_DATE BETWEEN '2005/01/01' AND '2005/02/28'
-- 날짜타입으로 자동 형변환됨. BUT 맹신 NO
-- TO_DATE(20050101)사용하여 형변환
-- LAST_DAY(TO_DATE('20050204'))
-> TODATE로 바뀐 날짜타입에 데이터에서 월을 뽑아서
그 월의 맨 마지막 날을 되돌려줌.
-- WHERE BUY_DATE BETWEEN '20050101' AND
LAST_DAY(TO_DATE('20050204'))로 날짜타입으로 변환시켜서 쓰는게좋음
-- BETWEEN과 %, _ 과같은 패턴문자열과 쓸수없음
ORDER BY 1;