SYNONYM은 CREATE PUBLIC SYNONYM EMP FOR SCOTT.EMP; (다른 사용자의 테이블을 시노님으로 가져오기) 위처럼 쓰면 FROM EMP; 처럼 이용해도 가져와진다.
위처럼 쓰지 않으면 다른테이블을 가져올때 항상 FROM SCOTT.EMP; 처럼 사용해야 한다
---------------------------------
뷰
뷰의 핵심은 동기화
외부직원에게 테이블의 민감한 정보를 제외하고 제공할때 복제해서주면 동기화의 문제와 다시합칠때 문제가 있다
VIEW를 이용하면 정보를 은닉하여 보여주고 싶은 것만 보여주고 동기화도 자동으로 된다
---------------------------------
CRUD (필수 꼭외울 것)
INSERT - C (create)
SELECT - R (read)
UPDATE - U
DELETE - D
--------------------------------
SELECT * FROM EMP; 를 하면 DB에 검색을 바로 하는게 아니라 DBMS(오라클)를 검색한다 1. 파싱 ( 코드 구문분석) 2. 데이터딕셔너리에게 메타데이터를 물어본다 (EMP테이블이 있어? ENAME라는 컬럼이 있어?) 3. 있으면 DB(램을 검색하고 램에 없으면 하드에서 램으로 올려서 검색) (한번 수행했던 쿼리는 다시 수행하면 파싱이나 딕셔너리접근을 하지않아서 빠르게 출력된다)
--------------------------------
CPU - 캐시메모리 - 램
이면 캐시메모리가 캐시 역할
CPU - 램 - 하드디스크
이면 램이 캐시 역할
CPU가격은 캐시메모리가 정한다(속도가 매우 빠르기때문)
---------------------------------
최근 프로그래밍에서 속도증가는 자료구조 등 보다 하드디스크접근(I/O)을 줄이는 것에 집중되어 있다
디스크 접근(I/O)을 줄이는 방법 1.캐시메모리를 늘리기 / 메모리에 자주쓰는 데이터를 올리는 기법 2.시퀀셜 엑세스 기법 3.랜덤엑세스 기법
1번이 가장 중요
2.시퀀셜 엑세스 기법
순서대로 블록 전체를 다 읽어서 필요한 데이터를 찾아낸다.(풀스캔)
풀스캔 - 찾을 데이터가 많으면 많을수록 유리해진다
3.랜덤 엑세스 기법 (프라이머리 키)
필요한 블록만 하나씩 가져오는 것
데이터가 적으면 적을수록 유리해진다
통상 15%이내의 데이터를 검색할 때 시퀀셜보다 랜덤 엑세스가 유리하다
----------------------------
클러스터 - 군집화
클러스터링 팩터 전략 데이터를 아무곳에나 놓는 것이 아니라 비슷한것은 모아두는 것(검색시 속도향상)
SELECT *
FROM EMP
WHERE SAL >
(
SELECT SAL
FROM EMP
WHERE ENAME = 'SMITH'
);
조건이 1개, 비교값도 1개일 경우
SELECT *
FROM EMP
WHERE SAL >
(
SELECT SAL
FROM EMP
WHERE ENAME = 'SMITH'
);
조건이 1개, 비교값이 여러개일 경우
SELECT *
FROM EMP
WHERE SAL IN
(
SELECT SAL
FROM EMP
WHERE JOB = 'MANAGER'
);
--위 코드는 아래와 같다
SELECT *
FROM EMP
WHERE SAL IN (2975, 2850, 2450);
STUDENT 테이블과 DEPARTMENT 테이블을 사용하여 '서진수'학생과 1전공(DEPTNO1)이 동일한 학생들의 이름과 1전공 이름을 출력하세요
먼저 서진수 학생의 전공번호를 확인한다
SELECT DEPTNO1
FROM STUDENT
WHERE NAME = '서진수';
전공이 101인 학생의 이름과전공이름을 출력한다
SELECT S.NAME STUD_NAME, d.dname DEPT_NAME
FROM STUDENT S, DEPARTMENT D
WHERE D.DEPTNO = 101 AND S.DEPTNO1 = d.deptno;
101자리에 서브쿼리를 넣는다
SELECT S.NAME STUD_NAME, d.dname DEPT_NAME
FROM STUDENT S, DEPARTMENT D
WHERE D.DEPTNO =
(
SELECT DEPTNO1
FROM STUDENT
WHERE NAME = '서진수'
) AND S.DEPTNO1 = d.deptno;
PROFESSOR 테이블과 DEPARTMENT 테이블을 조회하여
'주승재'교수보다 나중에 입사한 사람의 이름과 입사일, 학과명을 출력하세요.
먼저 주승재 교수의 입사일을 확인한다
SELECT HIREDATE
FROM PROFESSOR
WHERE NAME = '주승재';
위의 테이블을 서브쿼리하여 이름과 입사일 학과명을 출력한다
SELECT P.NAME , P.HIREDATE, d.dname
FROM PROFESSOR P, DEPARTMENT D
WHERE HIREDATE >
(
SELECT HIREDATE
FROM PROFESSOR
WHERE NAME = '주승재'
)
AND p.deptno = d.deptno;
STUDENT 테이블에서 1전공(DEPTNO1)이 201번인 학과의
평균 몸무게보다 몸무게가 많은 학생들의 이름과 몸무게를 출력하세요.
먼저 201번 학과의 평균 몸무게를 검색한다
SELECT AVG(WEIGHT)
FROM STUDENT
WHERE DEPTNO1 = 201
GROUP BY DEPTNO1;
위의 테이블을 서브쿼리하여 학생들의 이름과 몸무게를 출력한다
SELECT NAME, WEIGHT
FROM STUDENT
WHERE WEIGHT >
(
SELECT AVG(WEIGHT)
FROM STUDENT
WHERE DEPTNO1 = 201
GROUP BY DEPTNO1
);
EMP2 테이블을 사용하여 전체 직원 중 '과장' 직급의 최소 연봉자보다 연봉이 높은 사람의 이름과 직급, 연봉을 출력하세요. 단, 연봉 출력 형식은 아래와 같이 천 단위 구분 기호화 $표시를 하세요.
먼저 EMP2 테이블의 과장직급의 최소 연봉을 구한다
SELECT MIN(PAY) -- MIN함수는 월급을 찾는다
FROM EMP2
WHERE POSITION = '과장'
4900만원이 최소치이므로 WHERE으로 그 이상인 사람들을 찾을 수 있다.
SELECT NAME, POSITION, to_char(pay, '$999,999,999')
FROM EMP2
WHERE pay > 49000000;
서브쿼리로 처음 코드를 넣으면
SELECT NAME, POSITION, to_char(pay, '$999,999,999')
FROM EMP2
WHERE pay >
(
SELECT MIN(PAY)
FROM EMP2
WHERE POSITION = '과장'
);
EMP2 테이블과 DEPT2 테이블을 조회하여 각 부서별 평균 연봉을 구하고 그중에서 평균 연봉이 가장 적은 부서의 평균 연봉보다 적게받는 직원들의 부서명, 직원명, 연봉을 출력하세요.
먼저 각부서별 평균연봉을 구한다
SELECT AVG(PAY)
FROM EMP2
GROUP BY DEPTNO
ORDER BY AVG(PAY);
위의 코드를 서브쿼리하여서 1번째 것을 출력
SELECT *
FROM
(
SELECT AVG(PAY)
FROM EMP2
GROUP BY DEPTNO
ORDER BY AVG(PAY)
)
WHERE ROWNUM = 1;
그리고 출력할 것들을 SELECT 정리하고 25000000자리에 위 코드를 서브쿼리한다
SELECT DEPT2.DNAME, EMP2.NAME, TO_CHAR(EMP2.PAY, '$999,999,999') SALARY
FROM EMP2, DEPT2
WHERE PAY < 25000000 AND EMP2.DEPTNO = DEPT2.DCODE;
SELECT DEPT2.DNAME, EMP2.NAME, TO_CHAR(EMP2.PAY, '$999,999,999') SALARY
FROM EMP2, DEPT2
WHERE PAY <
(
SELECT *
FROM
(
SELECT AVG(PAY)
FROM EMP2
GROUP BY DEPTNO
ORDER BY AVG(PAY)
)
WHERE ROWNUM = 1
)
AND EMP2.DEPTNO = DEPT2.DCODE;
IN 과 EXISTS의 차이
IN 은 서브쿼리 데이터를 WHERE 오른쪽의 DEPTNO 와 비교해서 같은 값인 경우에만 출력한다
SELECT DEPTNO
FROM DEPT
WHERE DEPTNO = 20;
20이 출력
DEPTNO가 20인 모든 데이터를 출력
SELECT * FROM DEPT
WHERE DEPTNO IN (
SELECT DEPTNO
FROM DEPT
WHERE DEPTNO = 20
);
EXISTS는 서브쿼리 데이터가 있으면 메인 쿼리를 모두 실행하고, 없으면 아예 실행하지 않는다
SELECT * FROM DEPT
WHERE EXISTS (
SELECT DEPTNO
FROM DEPT
WHERE DEPTNO = 20
);
SELECT * FROM DEPT
WHERE EXISTS (
SELECT DEPTNO
FROM DEPT
WHERE DEPTNO = 50
);
INSERT INTO t_minus VALUES(1, 1, 1);
INSERT INTO t_minus VALUES(1.1, 1.1, 1.1);
INSERT INTO t_minus VALUES(-1.1, -1.1, -1.1);
SELECT * FROM t_minus;
NUMBER(3) - 정수자리를 3자리 (999) 로 지정
NUMBER(3, 2) - 정수자리를 3자리 (999) , 소수자리를 2자리 (0.99) 로 지정
INSERT와 서브 쿼리를 사용하여 여러 행 입력하기
먼저 PROFESSOR 테이블을 복사하여 PROFESSOR3를 만든다
--INSERT와 서브쿼리를 사용하여 여러행 입력하기
CREATE TABLE professor3
AS
SELECT * FROM professor
WHERE 1 = 2; -- 아무데이터도 넣지 않기위해 무조건 틀린 조건이 나오도록 함
확인을 위해 select 해본다
SELECT *
FROM professor3;
의도 대로 데이터가 없는 테이블이 생성 되었다
서브쿼리로 여러건의 데이터를 한꺼번에 입력해본다
INSERT INTO professor3
SELECT * FROM professor;
professor3을 다시 확인해보면
SELECT *
FROM professor3;
자료들이 정상적으로 입력되어 있다
조건을 걸어서 입력하려면
INSERT INTO professor3
SELECT * FROM professor
WHERE PROFNO > 4000;
위와 같이 아래에 조건을 넣으면 된다 ( PROFNO가 4000번을 초과하는 행만 넣기)
INSERT ALL을 이용한 여러 테이블에 여러행 입력하기
먼저 테스트 테이블을 생성
CREATE TABLE prof_3
(
profno NUMBER,
name VARCHAR2(25)
);
CREATE TABLE prof_4
(
profno NUMBER,
name VARCHAR2(25)
);
다른 테이블의 데이터를 가져와서 입력
INSERT ALL
WHEN profno BETWEEN 1000 AND 1999 THEN INTO prof_3 VALUES(profno, name)
WHEN profno BETWEEN 2000 AND 2999 THEN INTO prof_4 VALUES(profno, name)
SELECT profno, name -- 모든 profno, name를 가져오기
FROM professor;
또는 아래와 같이 WHERE를 써도 된다
INSERT ALL
INTO prof_3 VALUES (profno, name)
INTO prof_3 VALUES (profno, name)
SELECT profno, name
FROM professor
WHERE profno BETWEEN 3000 AND 3999;
UPDATE
기존데이터를 다른 데이터로 변경 할때 사용
문법
UPDATE table
SET column = value
WHERE 조건;
직급이 조교수인 교수들의 BONUS를 200만원으로 인상
UPDATE professor
SET bonus = 200
WHERE position = '조교수';
확인해본다
SELECT *
FROM professor;
차범철 교수의 직급과 동일한 직급을 가진 교수들 중 현재 급여가 250만원이 안되는 교수들의 급여를 15%인상
UPDATE professor
SET pay = pay * 1.15
WHERE position =
(
SELECT position
FROM professor
WHERE name = '차범철'
)
and pay < 250;
UPDATE 조인
일반적인 UPDATE가 아닌 다른 테이블과 조인을 하는 UPDATE에 대해서 살펴본다
일반적인 UPDATE
UPDATE emp
SET sal = (sal + sal*0.1)
WHERE JOB = 'CLERK';
일반적인 UPDATE 조인
UPDATE emp E
SET sal = (sal + sal*0.1)
WHERE EXISTS
(
SELECT 1 FROM DEPT D
WHERE D.LOC = 'DALLAS' AND E.DEPTNO = D.DEPTNO
);
#여기서 서브쿼리안에 E.DEPTNO를 쓸 수 있는 것은 코드를 실행할 때 코드안의 모든 테이블을 가장 먼저 불러오기 때문이다 그렇기 때문에 서브쿼리 밖에 있는 emp E를 서브쿼리 안에서 쓸 수 있는 것
-- 오라클 조인
SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
-- ANSI 조인
SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME
FROM EMP E JOIN DEPT D --콤마를 JOIN으로
ON E.DEPTNO = D.DEPTNO; --WHERE를 ON으로( WHERE가 있으나 조인할때는 ON을 쓴다)
ANSI JOIN에서도 WHERE 사용 가능
-- ANSI 조인
SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO AND D.DEPTNO = 10; --AND 사용가능
SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME
FROM EMP E JOIN DEPT D
ON E.DEPTNO = D.DEPTNO
WHERE D.DEPTNO = 10; --ANSI 조인에도 ON 아래에 WHERE를 쓸 수 있다
오라클과 ANSI의 OUTER JOIN
-- 오라클 조인
SELECT *
FROM BOARD B, REPLY R
WHERE B.ID = R.BOARDID;
-- ANSI 조인
SELECT *
FROM BOARD B LEFT OUTER JOIN REPLY R
ON B.ID = R.BOARDID;
FULL OUTER JOIN
FULL OUTER JOIN은 오라클에서는 되지 않고 ANSI 를 써야한다
--오라클 조인
SELECT *
FROM BOARD B, REPLY R
WHERE B.ID(+) = R.BOARDID(+);
--ANSI 조인
SELECT *
FROM BOARD B FULL OUTER JOIN REPLY R
ON B.ID = R.BOARDID;
CREATE SEQUENCE users_seq
INCREMENT BY 1
START WITH 1;
CREATE SEQUENCE board_seq
INCREMENT BY 1
START WITH 1;
CREATE SEQUENCE reply_seq
INCREMENT BY 1
START WITH 1;
데이터 입력하기
-- Users, Board, Reply
-- DML (데이터 조작어)
INSERT INTO USERS(ID, USERNAME, EMAIL) -- (ID, USERNAME, EMAIL) 다넣을거면 안적어도되지만, 순서를 바꾸려면 넣어야한다
VALUES (USERS_SEQ.nextval, 'ssar', 'ssar@nate.com'); -- USERS_SEQ.nextval (시퀀스)를 쓰면 숫자가 자동으로 붙는다
INSERT INTO USERS(ID, USERNAME, EMAIL)
VALUES (USERS_SEQ.nextval, 'love', 'love@nate.com');
INSERT INTO USERS(ID, USERNAME, EMAIL)
VALUES (USERS_SEQ.nextval, 'cos', 'cos@nate.com');
commit;
-- 조인
SELECT *
FROM USERS U, BOARD B
WHERE U.ID = B.USERID;
-- 3번 유저가 나오게 조인
SELECT *
FROM USERS U, BOARD B
WHERE U.ID = B.USERID(+);
-- 댓글 적은사람 안적은사람 모두 찾기
SELECT *
FROM USERS U, REPLY R
WHERE U.ID = R.USERID(+);
-- 댓글 안적은 사람 찾기(WHERE이용)
SELECT *
FROM USERS U, REPLY R
WHERE U.ID = R.USERID(+)
AND R.ID IS NULL;
-- 댓글 안적은 사람 찾기(MINUS이용)
SELECT *
FROM USERS U, REPLY R
WHERE U.ID = R.USERID(+)
MINUS
SELECT *
FROM USERS U, REPLY R
WHERE U.ID = R.USERID;
-- 댓글 적은 사람만 찾기
SELECT *
FROM USERS U, REPLY R
WHERE U.ID = R.USERID;
-- 댓글 적은 사람만 찾기(INTERSECT이용)
SELECT *
FROM USERS U, REPLY R
WHERE U.ID = R.USERID(+)
INTERSECT
SELECT *
FROM USERS U, REPLY R
WHERE U.ID = R.USERID;
-- 그룹함수가 있으면 나머지 요소를 그룹바이에 넣어라
SELECT B.ID, B.TITLE, B.USERID, COUNT(R.ID) "댓글수"
FROM BOARD B, REPLY R
WHERE B.ID = R.BOARDID(+)
GROUP BY B.ID, B.TITLE, B.USERID;
-- 서브쿼리로
SELECT ID, TITLE, USERID, (SELECT COUNT(*) FROM REPLY WHERE BOARDID = B.ID) "댓글수"
FROM BOARD B;
-- 유저아이디를 유저네임으로 바꾸고 USERS 나오게
SELECT B.ID, B.TITLE, u.username, COUNT(R.ID) "댓글수"
FROM BOARD B, REPLY R, USERS U
WHERE B.ID = R.BOARDID(+) AND u.id(+) = b.userid
GROUP BY B.ID, B.TITLE, u.username
ORDER BY B.ID;
테이블 시퀀스 지우기
--테이블 지우기
DELETE FROM BOARD;
--시퀀스 지우기
DROP SEQUENCE BOARD_SEQ;
SELECT ename, sal, rownum
FROM
(
SELECT ename, sal
FROM emp
);
ROWNUM으로 순위 합치기
+ LAG OVER 함수는 순서를 미루는 함수
SELECT ename, s1, s2, DECODE(s1, s2, NO-1, NO)
FROM
(
SELECT ename, sal "S1", LAG(sal, 1, 0) OVER(ORDER BY sal DESC) "S2", ROWNUM "NO"
FROM
(
SELECT ename, sal
FROM emp -- 프롬으로 퍼올릴때 로우넘 번호가 매겨진다
ORDER BY Sal desc
)
);
RANK OVER를 이용한 순위합치기
SELECT ename, sal, RANK() OVER (ORDER BY sal DESC)
FROM emp;
RANK() OVER 를 이용해서 학생 키 순위 구하기
SELECT name, height, RANK() OVER (ORDER BY height DESC) "RANK"
FROM student;
학년별 키순위를 구하시오
union all 방식
SELECT name, grade, height, RANK() OVER (ORDER BY height DESC) "RANK"
FROM student
WHERE grade = 1
UNION ALL
SELECT name, grade, height, RANK() OVER (ORDER BY height DESC) "RANK"
FROM student
WHERE grade = 2
UNION ALL
SELECT name, grade, height, RANK() OVER (ORDER BY height DESC) "RANK"
FROM student
WHERE grade = 3
UNION ALL
SELECT name, grade, height, RANK() OVER (ORDER BY height DESC) "RANK"
FROM student
WHERE grade = 4;
PARTITION 방식
SELECT name, grade, height, RANK() OVER (PARTITION BY grade ORDER BY height DESC) "RANK"
FROM student;
RANK, DENSE_RANK, ROW_NUMBER 비교
SELECT empno, ename, job, sal,
RANK() OVER (ORDER BY sal DESC) sal_rank,
DENSE_RANK() OVER (ORDER BY sal DESC) sal_dense_rank,
ROW_NUMBER() OVER (ORDER BY sal DESC) sal_row_num
FROM emp;
