뷰
하나의 가상테이블 이다. SELECT 문으로 조회된값으로 가상의 VIEW를 만들어내는것이다.
1
2
3
4
5
6
7
8
9
10
GRANT CREATE VIEW TO SCOTT; // icia_sys에서 입력
CREATE OR REPLACE VIEW v_emp as
SELECT EMPNO, ENAME, JOB, DEPTNO
FROM EMP
WHERE EMPNO = 7369;
SELECT *
FROM EMP_TEMP T, v_emp V
WHERE T.EMPNO = V.EMPNO;
인덱스
데이터베이스 테이블에 있는 데이터를 빨리 찾기 위한 용도의 데이터베이스
프라이머리 키는 반드시 UNIQUE이다.
유니크 인덱스 테이블에 하나 이상의 같은 데이터 값이 입력되는것을 막아줌
비유일 인덱스
인덱스 관리 (USER_INDEXES, USER_IND_COLUMNS, USER_COL_COMMENTS)
1
2
3
4
5
6
7
8
--인덱스
SELECT * FROM USER_INDEXES;
--인덱스 네임기준
SELECT * FROM USER_IND_COLUMNS;
CREATE INDEX IDX_EMP_MGR_GRADE ON
EMP(MGR, GRADE);
- 리빌드 해당 테이블이 빈번하게 일어날때 인덱스 리빌드를 해준다.
인덱스 사용시 주의사항 테이블 조회 속도를 향상시키기위한 용도로 너무 많은 인덱스는 피해야한다. 또한 인덱스 컬럼으로 컬럼의 중복을 최소화 해야함
시퀀스
여러 사용자에게 유일한 값을 생성해 주는 오라클객체
cycle : 최대값까지 증가후 최소값으로 돌아가는 옵션
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SELECT MAX(EMPNO) FROM EMP;
--시퀀스
CREATE SEQUENCE EMP_EMPNO_SEQ
START WITH 7935
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9999
NOCYCLE
NOCACHE;
SELECT EMP_EMPNO_SEQ.NEXTVAL --CURRVAL
FROM DUAL;
-- INSERT INTO EMP VALUES ((SELECT MAX(EMPNO)+1 FROM EMP))
-- 이방법이아닌
INSERT INTO EMP VALUES (EMP_EMPNO_SEQ.NEXTVAL, '김대경', '사원', 7902, TO_DATE('20210504', 'YYYYMMDD'), 1200, 0, 10, 1);
INSERT INTO EMP VALUES (EMP_EMPNO_SEQ.NEXTVAL, '윤예지', '사원', 7902, SYSDATE, 3000,0,20,5);
COMMIT;
BONUS (직원이름, 직업, 연봉, 보너스)
EMP (직원번호, 직원이름, 직업, 매니저번호, 고용일자, 연봉, 보너스, 부서번호, 등급) SALGRADE (등급, 최저금액, 최고금액) DEPT (부서번호, 부서이름, 지역) BONUS (직원이름, 직업, 연봉, 보너스)
EMP 테이블 보너스삭제, 부서번호삭제, 등급삭제 BONUS 직원이름, 직업,
EMP (직원번호, 직원이름, 직책, 매니저번호, 고용일자, 부서번호, 등급) BONUS (급여액(년(6자리), 월, 직원번호), 보너스(년, 월, 직원번호)) DEPT (부서번호, 부서이름, 지역) SALGRADE (등급, 최저금액, 최고금액)
테이블만들기
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
--고정길이 CHAR
--가변길이 VARCHAR2
CREATE TABLE EMP(
EMPNO NUMBER NOT NULL,
Ename VARCHAR2(10),
Job VARCHAR2(9),
Mgr NUMBER(4),
Hiredate DATE,
Deptno NUMBER(2) NOT NULL,
GRADE NUMBER(1)
);
// 프라이머리 키 만들어주기
ALTER TABLE EMP ADD CONSTRAINT PK_EMPNO PRIMARY KEY(EMPNO);
// COMMENT 달아주기
COMMENT ON TABLE EMP IS '사원정보';
COMMENT ON COLUMN EMP.EMPNO IS '사원번호';
COMMENT ON COLUMN EMP.ENAME IS '사원이름';
COMMENT ON COLUMN EMP.JOB IS '직책';
COMMENT ON COLUMN EMP.MGR IS '매니저';
COMMENT ON COLUMN EMP.HIREDATE IS '입사일';
COMMENT ON COLUMN EMP.DEPTNO IS '부서번호';
COMMENT ON COLUMN EMP.GRADE IS '등급';
// DEPT 테이블 만들어주기
CREATE TABLE DEPT(
DEPTNO NUMBER(2,0) NOT NULL,
DNAME VARCHAR2(14),
LOC VARCHAR2(13),
CONSTRAINT PK_DEPT PRIMARY KEY(DEPTNO)
);
// DEPT 테이블 COMMENT달아주기
COMMENT ON TABLE DEPT IS '부서코드';
COMMENT ON COLUMN DEPT.DEPTNO IS '부서코드';
COMMENT ON COLUMN DEPT.DNAME IS '부서명';
COMMENT ON COLUMN DEPT.LOC IS '부서위치';
// SALGRADE 생성
CREATE TABLE SALGRADE
(
GRADE NUMBER,
LOSAL NUMBER,
HISAL NUMBER
);
// SALGRADE 테이블 COMMENT 생성
COMMENT ON COLUMN SALGRADE.GRADE IS '등급';
COMMENT ON COLUMN SALGRADE.LOSAL IS '최저급여';
COMMENT ON COLUMN SALGRADE.HISAL IS '최고급여';
// SALGRADE PK 생성
ALTER TABLE SALGRADE ADD CONSTRAINT PK_GRADE PRIMARY KEY(GRADE);
// BONUS 테이블 생성
CREATE TABLE BONUS
(
PAYMONTH CHAR(6) NOT NULL,
EMPNO NUMBER(4) NOT NULL,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
CONSTRAINT PK_BONUS PRIMARY KEY(PAYMONTH, EMPNO)
)
// BONUS 테이블 COMMENT 생성
COMMENT ON TABLE BONUS IS '급여';
COMMENT ON COLUMN BONUS.PAYMONTH IS '급여년월';
COMMENT ON COLUMN BONUS.EMPNO IS '직원번호';
COMMENT ON COLUMN BONUS.SAL IS '급여액';
COMMENT ON COLUMN BONUS.COMM IS '보너스';
--FK
ALTER TABLE EMP
ADD CONSTRAINTS FK_MGR FOREIGN KEY(MGR)
REFERENCES EMP(EMPNO);
ALTER TABLE EMP
ADD CONSTRAINT FK_EMP_DEPTNO FOREIGN KEY(DEPTNO)
REFERENCES DEPT(DEPTNO);
ALTER TABLE EMP
ADD CONSTRAINT FK_EMP_GRADE FOREIGN KEY(GRADE)
REFERENCES SALGRADE(GRADE);
ALTER TABLE BONUS
ADD CONSTRAINT FK_BONUS_EMP FOREIGN KEY(EMPNO)
REFERENCES EMP(EMPNO);
외래키는 왜사용할까?
왜 외래키가 필요할까 알아보자
우리가 온라인 식료품 마트를 운영하고 있다고 가정해보자, 온라인 식료퓸 마트는 사용자로부터 주문을 받아서 배송해주는 시스템을 운영한다.
먼저, 사용자에 대한 정보를 저장하기위해 이런조건의 테이블이 필요할것이다.
1
아이디, 비밀번호, 이름, 성별, 배송주소
온라인마켓이다보니 위의 정보만으로 주문정보를 저장할수없으므로
1
주문 일자, 제품 id, 제품명, 수량
그렇다면 이렇게 테이블이 생길것입니다.
당장에는 문제가없을것이다. 하지만 한 user가 주문을 추가할때마다 데이터의 중복이 심해지기때문에 큰 비효율을 발생시키는 테이블 구조이다.
이렇게 빨간색으로 보이는 중복데이터가 많아진다는것이다. 그래서 이문제를 해결하기위한 방법으로 테이블을 사용자, 주문테이블로 나뉘어서 데이터를 저장하면된다.
분리된테이블
사용자 테이블
사용자 테이블은 사용자에 해당하는 정보만 가지고있으면 된다.
주문 테이블
주문테이블은 주문을 관리하는 테이블로 주문과 관련된 정보만 가지고 있으면 된다. 하지만, 이렇게 테이블을 만들면 큰 문제가 발생한다. 주문정보는 추가되는데 누가 주문을 했는지 알수있는 방법이 없다는 것이다.
이문제를 해결하기 위해서 사용자 테이블의 기본키 정보만 주문 테이블이 가지고있으면 사용자를 찾을수있다. 따라서 주문테이블의 위의 사용자테이블의 기본키를 추가하면, 중복된 데이터없이 데이터를 저장관리할수있다는것이다.
이게 바로 주문테이블에 존재하는 user_id를 우리는 외래키라고 부른다.
즉 요약한다면 외래키는 두개의 테이블을 연결하는 역활을하며, 참조하는 테이블에 무결성을 높여주는 역활을 한다.
테이블 분석하기
점선과 실선차이
점선은 부모는 PK 자식은 속성 실선은 부모는 pk 자식도 pk
보통 프라이머리키는 데이타타입이 CHAR TYPE, NUMBER TYPE 이 많다.
BONUS
현재 : (PAYMONTH, EMPNO, SAL, COMM) empno pk와
직원의 전 월급
시퀀스 생성
1
2
3
4
5
6
7
CREATE SEQUENCE EMP_EMPNO_SEQ
START WITH 7935
INCREMENT BY 1
MINVALUE 1
MAXVALUE 9999
NOCYCLE
NOCACHE;
데이터 추가
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
1. 첫번째순서
--GRADE 추가
--1 : 1000 ~ 2000
--2 : 2100 ~ 4000
--3 : 4100 ~ 6000
--4 : 6100 ~ 7500
--5 : 7600 ~ 9500
INSERT INTO SALGRADE VALUES (1, 1000, 2000);
INSERT INTO SALGRADE VALUES (2, 2100, 4000);
INSERT INTO SALGRADE VALUES (3, 4100, 6000);
INSERT INTO SALGRADE VALUES (4, 6100, 7500);
INSERT INTO SALGRADE VALUES (5, 7600, 9500);
COMMIT;
2. 두번째순서
--DEPT 추가
--10 : 총무팀 학익동
--20 : 인사팀 연수구
--30 : 전산팀 강남구
--40 : 영업팀 강북구
--50 : 경리팀 부평구
INSERT INTO DEPT VALUES (10, '총무팀', '학익동');
INSERT INTO DEPT VALUES (20, '인사팀', '연수구');
INSERT INTO DEPT VALUES (30, '전산팀', '강남구');
INSERT INTO DEPT VALUES (40, '영업팀', '강북구');
INSERT INTO DEPT VALUES (50, '경리팀', '부평구');
COMMIT;
3. 세번째순서
--EMP 추가
INSERT INTO EMP (EMPNO, ENAME, JOB, HIREDATE, DEPTNO, GRADE)
VALUES (EMP_EMPNO_SEQ.NEXTVAL, '홍길동', '사원', SYSDATE, 30, 1);
INSERT INTO EMP (EMPNO, ENAME, JOB, HIREDATE, DEPTNO, GRADE)
VALUES (EMP_EMPNO_SEQ.NEXTVAL, '장동건', '부장', TO_DATE('20100308','YYYYMMDD'), 10, 5);
INSERT INTO EMP (EMPNO, ENAME, JOB, HIREDATE, DEPTNO, GRADE)
VALUES (EMP_EMPNO_SEQ.NEXTVAL, '고소영', '차장', TO_DATE('20110504','YYYYMMDD'), 20, 4);
INSERT INTO EMP (EMPNO, ENAME, JOB, HIREDATE, DEPTNO, GRADE)
VALUES (EMP_EMPNO_SEQ.NEXTVAL, '설현', '과장', TO_DATE('20100308','YYYYMMDD'), 40, 3);
INSERT INTO EMP (EMPNO, ENAME, JOB, HIREDATE, DEPTNO, GRADE)
VALUES (EMP_EMPNO_SEQ.NEXTVAL, '나야1', '대리', TO_DATE('20110504','YYYYMMDD'), 50, 2);
INSERT INTO EMP (EMPNO, ENAME, JOB, HIREDATE, DEPTNO, GRADE)
VALUES (EMP_EMPNO_SEQ.NEXTVAL, '나야2', '사원', TO_DATE('20110308','YYYYMMDD'), 10, 1);
INSERT INTO EMP (EMPNO, ENAME, JOB, HIREDATE, DEPTNO, GRADE)
VALUES (EMP_EMPNO_SEQ.NEXTVAL, '나야3', '사원', TO_DATE('20111108','YYYYMMDD'), 10, 1);
INSERT INTO EMP (EMPNO, ENAME, JOB, HIREDATE, DEPTNO, GRADE)
VALUES (EMP_EMPNO_SEQ.NEXTVAL, '나야4', '사원', TO_DATE('20111208','YYYYMMDD'), 20, 1);
INSERT INTO EMP (EMPNO, ENAME, JOB, HIREDATE, DEPTNO, GRADE)
VALUES (EMP_EMPNO_SEQ.NEXTVAL, '나야5', '사원', TO_DATE('20110308','YYYYMMDD'), 10, 1);
INSERT INTO EMP (EMPNO, ENAME, JOB, HIREDATE, DEPTNO, GRADE)
VALUES (EMP_EMPNO_SEQ.NEXTVAL, '나야6', '사원', TO_DATE('20100308','YYYYMMDD'), 40, 1);
4. 네번째 순서
--BONUS 추가
INSERT INTO BONUS VALUES('202106', 7935, 1000, 0);
INSERT INTO BONUS VALUES('202106', 7936, 9200, 0);
INSERT INTO BONUS VALUES('202106', 7937, 7500, 0);
INSERT INTO BONUS VALUES('202106', 7945, 1000, 0);
INSERT INTO BONUS VALUES('202106', 7939, 1200, 0);
INSERT INTO BONUS VALUES('202106', 7940, 1300, 0);
INSERT INTO BONUS VALUES('202106', 7941, 1400, 0);
INSERT INTO BONUS VALUES('202106', 7942, 1200, 0);
INSERT INTO BONUS VALUES('202106', 7943, 2200, 0);
INSERT INTO BONUS VALUES('202106', 7944, 4200, 0);
--조회
--부서별 직원수
select deptno, count(*)
from emp
group by deptno
order by deptno ASC;
--부서코드 부서명 직원수
SELECT DEPTNO, DNAME, COUNT(*)
FROM DEPT D
GROUP BY DEPTNO, DNAME;
--인라인 뷰를 이용
--SUBQUERY를 이용해서 JOIN
SELECT D.DEPTNO 부서코드, D.DNAME 부서명, E.CNT 직원수
FROM DEPT D, (select deptno, count(DEPTNO) CNT
from emp
group by deptno
order by deptno ASC) E
WHERE D.DEPTNO = E.DEPTNO;
--EQUI JOIN
SELECT E.DEPTNO AS "부서 코드", D.DNAME 부서명, COUNT(D.DNAME) 직원수
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
GROUP BY E.DEPTNO, D.DNAME
ORDER BY E.DEPTNO ASC;
select deptno, count(*)
from emp
group by deptno
order by deptno ASC;
--직원별 이번달 급여액과 보너스를 합하여 조회 하고 받을수 있는 최저급여액과
-- 최대급여액 조회
--조회항목 : 직원번호, 직원명, 월 급여, 최저급여액, 최대급여액
SELECT *
FROM SALGRADE;
SELECT E.EMPNO 직원번호, E.ENAME 직원명, B.SAL + B.COMM "월 급여액",
S.LOSAL 최저급여액, S.HISAL 최대급여액
FROM EMP E, BONUS B, SALGRADE S
WHERE E.EMPNO = B.EMPNO
-- AND B.PAYMONTH = TO_CHAR(SYSDATE, 'YYYYMM')
AND E.GRADE = S.GRADE;
-- DUAL 테이블 사용해서 연습해보기
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD')
FROM DUAL;
--지난달 직원별 급여액과 보너스를 합하여 조회 하고 받을수 있는 최저급여액과
-- 최대급여액 조회
--조회항목 : 직원번호, 직원명, 월 급여, 최저급여액, 최대급여액
SELECT E.EMPNO 직원번호, E.ENAME 직원명, B.SAL + B.COMM "월 급여액",
S.LOSAL 최저급여액, S.HISAL 최대급여액, B.PAYMONTH
FROM EMP E, BONUS B, SALGRADE S
WHERE B.PAYMONTH = TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYYMM')
AND E.EMPNO = B.EMPNO
AND E.GRADE = S.GRADE;
SELECT TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYY-MM-DD')
FROM DUAL;
--직원들에 대한 지난달 직원별 급여액과 보너스를 합하여 조회하고 받을수 있는
--최저급여액과 최대급여액 조회 (단, 지난달 해당직원이 아닌경우는 0원처리)
--조회항목 : 직원번호, 직원명, 월 급여, 최저급여액, 최대급여액
--OUTER 조인이해
--OUTER 조인걸어준것은 다 걸어줘야한다.
SELECT E.EMPNO 직원번호, E.ENAME 직원명, NVL(B.SAL + B.COMM, 0) "월 급여액",
S.LOSAL 최저급여액, S.HISAL 최대급여액, B.PAYMONTH
FROM EMP E, BONUS B, SALGRADE S
WHERE E.EMPNO = B.EMPNO(+)
AND B.PAYMONTH(+) = TO_CHAR(ADD_MONTHS(SYSDATE, -1), 'YYYYMM')
AND E.GRADE = S.GRADE;
--급여 추가
INSERT INTO BONUS VALUES('202105', 7935, 1000, 0);
INSERT INTO BONUS VALUES('202105', 7936, 9200, 0);
INSERT INTO BONUS VALUES('202105', 7937, 7500, 0);
INSERT INTO BONUS VALUES('202105', 7945, 1000, 0);
INSERT INTO BONUS VALUES('202105', 7939, 1200, 0);
INSERT INTO BONUS VALUES('202105', 7940, 1300, 0);
INSERT INTO BONUS VALUES('202105', 7941, 1400, 0);
INSERT INTO BONUS VALUES('202105', 7942, 1200, 0);
INSERT INTO BONUS VALUES('202105', 7943, 2200, 0);
INSERT INTO BONUS VALUES('202105', 7944, 4200, 0);
SELECT *
FROM BONUS;
COMMIT;
1
2
3
4
5
6
7
8
9
10
11
12
-- 직원번호, 직원명, 총급여액, 총건수
--서브쿼리사용
-- 직원번호, 직원명, 총급여액, 총건수
SELECT E.EMPNO 직원번호, E.ENAME 직원명, B.SAL 총급여액, B.CNT 총건수
FROM EMP E, (
SELECT EMPNO, SUM(SAL) SAL, COUNT(EMPNO) CNT
FROM BONUS
GROUP BY EMPNO
) B
WHERE E.EMPNO = B.EMPNO;