그룹함수
GROUP BY
GROUP BY : 데이터 값이 같은 행끼리 묶어서 그룹화 (~~별 데이터 출력)
/* 전공별 학생수 출력 */
SELECT major1, COUNT(*) FROM student
GROUP BY major1;ROULL UP : 하위 합계 생성
/* 부서별 전체 합계 */
SELECT deptno, SUM(salary)
FROM emp GROUP BY ROLLUP(deptno); -- DEPTNO 오름차순으로 정렬 후 해당 항목의 합계를 구함
GROUPING SET : 컬럼별 별도 처리
/* 부서별, 직업별 별도 합계 */
SELECT deptno, job, SUM(salary)
FROM emp GROUP BY GROUPING SETS(deptno, job);
CUBE : 결합 가능한 모든 그룹핑(모든 조합의 수)
/* 부서와 직급 조합별 합계 */
SELECT deptno, job, SUM(salary)
FROM emp GROUP BY CUBE(deptno, job);
HAVING
HAVING : GROUP BY의 조건을 지정할 때 사용
/* 교수 직급별 평균급여가 300이상인 부서코드와 평균 급여 출력 */
SELECT deptno, AVG(salary) FROM professor
GROUP BY deptno
HAVING AVG(salary) >= 300;SELECT 실행시 주의사항
SELECT 실행순서
실행순서에 유의하여 쿼리문을 작성할 것
JOIN
JOIN : 한 개 이상의 테이블과 테이블을 연결할여 데이터를 조회하는 기법
EQUI JOIN
조인 SQL문의 기본구조
SELECT
테이블명1.컬럼명, 테이블명2.컬러명, ...
FROM 테이블명1, 테이블명2
WHERE
테이블명1.컬럼명 = 테이블명2.컬럼명
( ORACLE과 MYSQL에서는 사용가능 )
조인 SQL문의 기본구조 (ANSI 방식)
SELECT
테이블명1.컬럼명, 테이블명2.컬러명, ...
FROM 테이블명1 JOIN 테이블명2
ON 테이블명1.컬럼명 = 테이블명2.컬럼명
( 국제표준 기법 )
OUTER JOIN
OUTER JOIN : 양쪽 테이블 모두 조건이 만족하지 않아도, 한쪽 테이블의 데이터를 모두 출력 해야 하는 경우 사용
LEFT OUTER JOIN (ORACLE, MYSQL)
/* 학생명, 지도교수명 출력 (지도교수가 없는 학생도 출력)
*/
SELECT s.name, p.name
FROM student s,professor p
WHERE s.profno = p.no(+);LEFT OUTER JOIN (ANSI)
SELECT s.name, p.name
FROM student s LEFT JOIN professor p
ON s.profno = p.no;CONNECT BY(계층형 조회)
트리구조로 조회 (ORACLE전용)
SELECT level, empno, mgr, ename
FROM emp
START WITH MGR IS NULL -- 시작조건
CONNECT BY PRIOR empno = mgr; -- 조인조건
LPAD 함수를 사용해서 출력
SELECT level, LPAD(' ', 4*(level-1)) || empno AS empno, mgr, ename
FROM emp
START WITH MGR IS NULL
CONNECT BY PRIOR empno = mgr;
서브쿼리(SUB-QUERY)
sub-query : select안에 select구문이 포함된 구문
WHERE절에 들어가는 서브쿼리
- 단일행 서브쿼리 : 서브쿼리의 결과가 1개인 경우
- 다중행 서브쿼리 : 서브쿼리의 결과가 2개 이상인 경우
IN : 모두 포함하는 조건
NOT IN : 모두 포함하지 않는 조건
ANY : 서브쿼리의 조건 중 하나라도 만족하면 조회 (OR과 같음)
ALL : 서브쿼리의 조건을 모두 만족되야 조회 (AND와 같음)
(단일, 다중 행)서브쿼리 예제
- 단일 행 서브쿼리
/* 양준혁 직원보다 급여가 많은 직원을 출력 */
SELECT ename, salary FROM emp
WHERE salary > (SELECT salary FROM emp WHERE ename = ‘양준혁’); - 다중 행 서브쿼리 (IN)
/* 1학년 학생들의 키와 같은 키를 가지고 있는 2학년 학생명과 학년, 키 출력 */
SELECT name, grade, height FROM student
WHERE height IN (SELECT height FROM student WHERE grade=1) AND grade=2;- 다중 행 서브쿼리 (ANY)
/* 2학년 학생들의 가장 작은 키보다 큰 2학년 학생명과 학년, 키 출력 */
SELECT name, grade, height FROM student
WHERE height >= ANY (SELECT height FROM student WHERE grade=2) AND grade=2;스칼라 서브쿼리
스칼라 서브쿼리 : 컬럼자리에 들어가는 서브쿼리
- 일반적인 JOIN문 대신에 사용하여 성능향상 효과를 얻을 수도 있음 (단 무분별한 서브쿼리 사용은 오히려 성능을 방해함)
스칼라 서브쿼리 예제
SELECT ename, d.deptno, dname
FROM emp e JOIN dept d
ON e.deptno = d.deptno; -- JOIN을 사용한 직원명, 부서코드, 부서명 출력
SELECT ename, deptno,
(SELECT dname FROM dept WHERE deptno=emp.deptno) dname
FROM emp; -- 스칼라 서브쿼리를 사용한 직원명, 부서코드, 부서명 출력INLINE VIEW 서브쿼리
inline view 서브쿼리 : FROM절 뒤에 들어감
- view : 실제 물리저인 테이블이 아닌 가상의 테이블
INLINE VIEW 서브쿼리 예제
/* 직원명, 급여, 직급별 평균 급여 출력 */
SELECT ename, e.deptno, e.salary, v.salary
FROM emp e JOIN
(SELECT deptno, AVG(salary) salary FROM emp GROUP BY deptno) v
ON e.deptno = v.deptno;페이징처리
ROWNUM 키워드를 통해 서브쿼리 사용
- 어플리케이션 목록 출력 시 해당 페이지 데이터 출력 용도
- 해당 페이지의 데이터 목록 조회 ex) 회원 목록 2페이지 조회
/* 페이지당 10개씩 출력되는 학생목록 첫 페이지 조회 */
SELECT * FROM (
SELECT ROWNUM as rnum, a.* FROM (
SELECT *
FROM student ORDER BY grade DESC
) a
) b
WHERE b.rnum BETWEEN 1 and 10;
/* 시작값 : (페이지-1) * 페이지당개수 + 1
끝값 : 페이지 * 페이지당개수 */서브쿼리 사용 가능 위치
- WHERE 조건문
- 컬럼
- HAVING 구문
- FROM 구문(inline view)
TCL
TCL(Transaction Control Language)
- COMMIT
- 트랜잭션(작업단위)를 정상적으로 데이터베이스에 적용
- 작업(INSERT, UPDATE, DELETE)내용을 DB에 저장 - ROLLBACK
- 작업 중 문제 발생 시 현재 트랜잭션의 변경 내역을 취소하고, 종료 트랜잭션 발생 이전 시점으로 되돌아감
- 작업(INSERT, UPDATE, DELETE)내용을 취소할 수 있음
