다중행 서브쿼리 → 애매해서 잘 사용 안 함.
SELECT 컬럼 ...
FROM 테이블
WHERE 컬럼 다중_행_연산자 (SELECT 문장 : Sub query문)......;
서브쿼리에 여러 행이 검색되는 경우 사용한다.
‐ 다중행 연산자를 이용한다.
‐ 다중행 연산자의 종류
． IN : 나열된(검색된) 값 중에 하나만 일치하면 된다.
． ANY : 나열된(검색된) 값 일부와 일치하면 된다.
． ALL : 나열된(검색된) 값 전체와 일치해야 한다.
IN 연산자를 이용한 다중행 서브쿼리
• 화학과교수의 부임일과동일한 날 입사한 사원의 명단을 검색한다.
• 20번 부서원과 동 일한관리자로부터 관리받는 사원을 검색한다.
SQL> SELECT eno 사번, ename 이름
2 FROM emp
3 WHERE hdate IN (SELECT hiredate
4 FROM professor
5 WHERE section = '화학');
SQL> SELECT * FROM emp
2 WHERE mgr IN (SELECT mgr FROM emp
3 WHERE dno = '20')
4 AND dno != 20; ← 매우 많은 실수!!
ANY, ALL을 이용한 다중행 서브쿼리
• 10번 부서원들보다 급여가 낮은 사원을 검색한다.
SQL> SELECT eno 사번, ename 이름, dno 부서번호
2 FROM emp
3 WHERE sal < ALL (SELECT sal
4 FROM emp
5 WHERE dno = '10');
- 컬럼 > ALL → 컬럼> MAX() : 가장 큰 값보다 크다
- 컬럼 < ALL → 컬럼< MIN() : 가장 작은 값보다 작다.
- 컬럼 > ANY → 컬럼> MIN() : 가장 작은 값보다 크다.
- 컬럼 < ANY → 컬럼< MAX() : 가장 큰 값보다 작다
다중열서브쿼리 → in 서야 하는 경우가 많다.
SELECT 컬럼 ...
FROM 테이블
WHERE (컬럼1, 컬럼2, ...)
IN (SELECT 문장 : Sub query문) ......;
‐ 서브쿼리의 SELECT문에 여러 개의 컬럼을 검색한다.
‐ 여러 개의 컬럼을 검색하는 서브쿼리문을 이용할 때는 반드시 비교대상 컬럼과 1:1 대응돼야 한다.
‐ 다중열 서브쿼리에서 서브쿼리의 검색결과가 단지하나의 행이라면'=' 연산자사용 이 가능하지만 되도록'IN' 연산자를 이용한다.
• 손하늘과 동일 관리자의 관리를 받으면서 업무도 같은 사원을 검색한다.
SQL> SELECT eno 사번, ename 이름, mgr 관리자, job 업무
2 FROM emp
3 WHERE (mgr, job) IN (SELECT mgr, job
4 FROM emp
5 WHERE ename = '손하늘')
6 AND ename != '손하늘';
이건 쓰지 말자.(같은 결과 나온다는 보장 못함.)
SQL> SELECT eno 사번, ename 이름, mgr 관리자, job 업무
2 FROM emp
3 WHERE mgr= (SELECT mgr FROM emp WHERE ename = '손하늘')
4 AND job = (SELECT job FROM emp WHERE ename = '손하늘')
5 AND ename!= '손하늘';
• 김선유와 부서 및 업무가 동일한 같은 사원을 검색한다.
SQL> SELECT eno 사번, ename 이름, dno 부서번호, job 업무
2 FROM emp
3 WHERE (dno, job) IN (SELECT dno, job
4 FROM emp
5 WHERE
6 AND ename != '김선유';
(이름이 같은 사람이 있어서 결과가 다르게 나온다.)
SQL> SELECT eno 사번, ename 이름, dno 부서번호, job 업무
2 FROM emp
3 WHERE dnoIN (SELECT dno FROM emp WHERE ename = '김선유')
4 AND job IN (SELECT job FROM emp WHERE ename = '김선유')
5 AND ename!= '김선유'
집합연산자
SELECT ...
[UNION ALL | UNION | INTERSECT | MINUS]
SELECT ...
‐ 두 SELECT 문의결과를 집합연산한다.
‐ 검색결과의 헤더는 앞쪽 SELECT문에 의해 결정된다.
‐ 두 SELECT 문의 컬럼개수와 데이터타입이 일치해야 한다.
UNION 합집합 검색된 결과의 합으로 중복이 제거된다.
UNION ALL 검색된 결과의 합으로 중복을 허용한다 → 보통 사용 안 하고 유니온만 사용함.
INTERSECT 교집합 양쪽모두에 포함된 결과만 검색한다.
MINUS 차집합 첫 번째 검색결과에서 두 번째 검색결과를 제외한 나머지만 검색한다.
집합연산자를 이용한 검색
• 2000년 이후에 입사한 사원과부 임한 교수의 명단을 부임일순으로 검색한다.
SQL> SELECT pno 번호, pname 이름, hiredate 입사일_부임일
2 FROM professor
3 WHERE hiredate >= '2000/01/01'
4 UNION ALL
5 SELECT eno, ename, hdate
6 FROM emp
7 WHERE hdate >= '2000/01/01'
8 ORDER BY 3;
‐ 집합연산자를 이용한 출력에서 헤더는 첫 번째 SELECT문에 의해 결정된다.
‐ ORDER BY절은 SELECT문 끝에 추가한다.
． pno나 eno가 아니라 반드시 번호를 이용해서 정렬한다.
• 화학, 물리학과학생들 중에 학점이 3.0 이상인학생을 검색한다.
• 제갈씨성을가진 사원 중에 지원업무를 하지 않는 사원을 검색한다.
SQL> SELECT sno 학번, sname 이름, major 학과, avr 학점 FROM student
2 WHERE major IN ('화학', '물리')
3 INTERSECT
4 SELECT sno, sname, major, avr FROM student
5 WHERE avr >= 3;
SQL> SELECT eno, ename, job FROM emp
2 WHERE ename LIKE '제갈%'
3 MINUS
4 SELECT eno, ename, job FROM emp
5 WHERE job = '지원';
단일행 함수문자함수
• 단일행 함수를 이용하면 검색된 데이터에 대해서 각행 별로 연산된 결과값을 얻을 수 있으며 중첩이 가능하다.
• 단일행 함수는 응용프로그램의 오류를 줄이 고개발시간을 단축할 수 있다.
• 인수로는 칼럼명, 수식, 변수, 상수 등을 사용할 수 있다. 함수명(인수 1, 인수 2,...)
• 단일행 함수는 SELECT 절이나 WHERE절 또는 HAVING 절등값이필요한 거의 모든 구문에 사용이 가능하다.
• 단일행 함수의 종류‐ 문자함수, 숫자함수, 날짜함수, 변환함수, 일반함수..
문자함수
| 함수명 | 기능 | 함수명 | 기능 |
| LOWER | 문자열을소문자로변환한다. LOWER(문자열) | INSTR | 문자의위치반환 INSTR(문자열, 검색문자, 시작위치, 횟수) |
| LOWER('ORACLE') → oracle | INSTR('ORACLE', 'A') → 3 | ||
| UPPER | 문자열을대문자로변환한다. UPPER(문자열) | TRIM | 접두(접미) 글자를잘라낸다 TRIM([leading | trailing | both] 제외문자 FROM 문자열) |
| UPPER('oracle') → ORACLE | TRIM('o' FROM 'oracle') → racle | ||
| INITCAP | 첫번째문자는대문자로, 나머지는소문자로변 환한다. INITCAP(문자열) | LENGTH LENGTHB |
문자열의길이나BYTE를반환한다. LENGTH(문자열) LENGTHB(문자열) |
| INITCAP('oracle') → Oracle | LENGTH('디비') → 2 LENGTHB('디비') → 4 | ||
| SUBSTR | 문자열내지정된위치의문자열을검색 한다. SUBSTR(문자열, STR,CNT) | LPAD,RPAD | 지정된문자열의길만큼빈부분에문자를 채운다. LPAD(문자열, 출력폭, 채움문자) |
| SUBSTR('oracle', 1, 2) → or | LPAD('20000', 10, '*') → *****20000 |
공백 자동으로 없애는 거 → TRIM
문자치환함수
| 단일행 | 기능 |
| TRANSLATE | 문자단위로치환된값을반환한다. TRANSLATE(문자열, 검색문자, 치환문자) |
| TRANSLATE('oracle', 'a','#') → or#cle | |
| REPLACE | 문자열단위로치환된값을반환한다. REPLACE(문자열, 검색문자열, 치환문자열) |
| REPLACE('oracle', 'or', '##') → ##acle |
• 형식이 비슷한 TRANSLATE와 REPLACE를 동일한 치환을 통해 비교해 보자.
‐ 원문의'Wo'를'--'로 변환한다.
SQL> SELECT TRANSLATE('World of Warcraft', 'Wo', '--') Translate,
2 REPLACE('World of Warcraft', 'Wo', '--') Replace
3 FROM dual;
‐ TRANSLATE는 각각의 글자를 지정된 문자로 변환하는 것에 주의한다