1. Sub Query(서브쿼리)
: 쿼리 안의 쿼리
2. 주의사항
1) SubQuery는 연산자 오른쪽에 위치해야 하며 반드시 괄호로 묶어야 한다
2) 특별한 경우를 제외하고는 SubQuery절에는 Order By가 올 수 없다
3) 단일행SubQuery , 다중행SubQuery에 따라 연산자를 잘 선택해야 한다
3. Sub Query 종류
1) 단일행
사용 가능한 연산자 : = != <> > >= < <=
2) 다중행
사용 가능한 연산자 : IN >ANY ALL =ANY <=ANY >=ALL <=ALL
3) 다중칼럼
4) 상호연관
4. Sub Query 위치별로 이름이 다르다
1) SELECT(Sub Query) : Scala Sub Query(스칼라 서브쿼리)
2) FROM(Sub Query) : Inline View(인라인 뷰)
3) WHERE(Sub Query) : Sub Query(서브쿼리)
5. View
: 가상테이블
[뷰를 사용하는 목적?] 보안, 편의성
6. 뷰 생성 구문
CREATE [OR REPLACE] VIEW 뷰명 [컬럼명]
AS
(Sub Query)
7. View 종류
1) 단순 View (Simple View)
: 서브쿼리에 join 조건 없이 만듦.
2) 복합 View (Complex View)
: 서브쿼리에 여러 개의 테이블이 Join 되어 만듦.
3) 인라인 View (INLINE View)
: FROM 절의 서브쿼리 형태로 만듦, 1회용으로만 사용
8. View를 생성하려면 계정에 권한 필요
[권한 확인] SELECT PRIVILEGE FROM DBA_SYS_PRIVS WHERE GRANTEE ='SCOTT0316';
[권한이 없는 경우] GRANT create view TO scott0316;
[주의!] 반드시 SYSTEM 계정으로 들어가기
9. Constraint(제약조건)
** DBMS는 데이터의 무결성을 보장하기 위해,
잘못된 데이터가 저장되는 것을 방지하기 위해 제약조건을 사용
** 제약 조건은 테이블의 '컬럼'에 설정하여 사용되며,
해당 '컬럼'에 설정된 제약조건에 위배된 데이터가 들어오는 경우 에러 발생
[데이터 무결성(Integrity)이란?] 저장된 데이터에는 '잘못된 데이터'가 없어야 한다는 의미
10. 제약조건 종류
1) NOT NULL
: 이 조건이 설정된 컬럼에는 NULL 입력 불가
2) UNIQUE
: 이 조건이 설정된 컬럼에는 중복값 입력 불가
3) PRIMARY KEY
: 테이블당 단 1개만 설정 가능,
데이터 유일성 보장,
NOT NULL + UNIQUE의 의미
4) FOREIGN KEY
: 다른 테이블의 컬럼을 참조해서 무결성 검사,
외래키 값은 NULL 혹은 참조할 수 있는 값이어야 한다.
5) CHECK
: 설정된 값만 입력 허용
11. 무결성 종류
1) 개체 무결성
: 릴레이션에서 기본키를 구성하는 속성은 널(Null)값이나 중복값을 가질 수 없다
PRIMARY KEY
2) 도메인 무결성
: 특정 속성의 값은 그 속성이 정의된 도메인에 속한 값이어야 한다.
CHECK, DEFAULT, NOT NULL
3) 참조 무결성
: 외래키 값은 NULL이거나, 참조할 수 없는 외래키 값을 가질 수 없다
FOREIGN KEY
4) 고유 무결성
: 특정 속성에 속한 값은 서로 달라야 한다
UNIQUE, NOT NULL, PRIMARY KEY
5) 널 무결성
: 릴레이션의 속성값이 NULL이 될 수 없다
NOT NULL
6) 관계 무결성
: 릴레이션
ON DELETE, NO UPDATE
12. 제약조건명의 관리를 위해 이름을 붙여주는 것이 좋음
[개발자들끼리 약속!!] 릴레이션명_컬렴명_제약조건명 으로 주로 실무에서 이런 식으로 많이 작성함
13. 제약조건 조회
: 테이블에 제약조건을 설정하면 그 내용은 ‘딕셔너리’ 에 저장
[사용자 딕셔너리] USER_CONSTRAINTS 와 USER_CONS_COLUMNS 사용
[DB 전체 딕셔너리] DBA_CONSTRAINTS 와 DBA_CONSTRAINT_COLUMNS 사용
[딕셔너리( Dictionary )란?] 스키마 · 사용자 · 객체 · 권한 · 롤 · 데이터베이스의 정보 등
오라클 데이터베이스를 운영하는데 필요한 정보를 관리하는 별도의 객체들
[CONSTRAINT_TYPE] P : Primary Key, U : Unique, C : Check, Not Null R : Foreign Key
14. 외래키 설정시 주의
: 참조되는 ‘부모테이블의 컬럼’은 반드시 Primary Key이거나 Unique
15. FOREIGN KEY + ON DELETE / ON UPDATE 옵션
데이터를 지울려고 한다..!!
지울려고 하는 데이터가 FK를 설정이 되어 있다...!!
거기다가 설상가상으로 삭제해야할 데이터가 누군가가 참조하고 있어...!!
이럴때는 설정한 옵션에 따라 지울수 있는 여부가 달라진다..!!
** ON DELETE / ON UPDATE 옵션 종류
NO ACTION : 참조테이블(부모테이블)에 변화가 있어도 자식테이블에 아무런 조치를 취하지 않음
CASCADE : 참조테이블의 데이터가 삭제 혹은 변경되면 자식테이블의 관련 데이터도 모두 삭제 혹은 변경
SET NULL : 참조테이블에 변화가 있으면 자식테이블의 관련데이터는 NULL로 변경
SET DEFAULT : 참조테이블의 변화가 있으면 자식테이블의 관련데이터는 기본데이터로 변경
16. 인덱스 생성 구문
CREATE UNIQUE INDEX 인덱스명
ON 테이블이름(컬럼명1 ASC | DESC, 컬럼명2 ... )
17. 지금까지 배운 CREATE로 생성한 것들(총 4개)
: CREATE USER, CREATE TABLE, CREATE SEQUENCE, CREATE VIEW
[실습코드]
1. [폴더] 07_SubQuery
1) SubQuery1.sql
-- 단일행 Sub Query : 결과가 한개 1행만 나오는 것
-- t_emp 테이블에서 scott 보다 급여를 많이 받는 사람의 이름과 급여 출력
SELECT * FROM t_emp;
SELECT sal FROM t_emp WHERE ename = 'SCOTT';
SELECT ename, sal
FROM t_emp
WHERE sal > (SELECT sal FROM t_emp WHERE ename = 'SCOTT');
-- t_student 테이블에서 가장 키 큰 학생의 '이름'과 '키'를 출력
SELECT * FROM t_student;
SELECT MAX(height) FROM t_student;
SELECT name, height
FROM t_student
WHERE height = (SELECT MAX(height) FROM t_student);
-- 1. 단일행 서브 쿼리
-- t_student, t_department 테이블 사용하여
-- 이윤나 학생과 1전공이 동일한 학생들의 이름과 1전공 이름을 출력하세요
SELECT * FROM t_student;
SELECT * FROM t_department;
SELECT deptno1 FROM t_student WHERE name = '이윤나';
SELECT s.name "학생이름", d.dname "1전공"
FROM t_student s, t_department d
WHERE s.deptno1 = d.deptno
AND s.deptno1 = (SELECT deptno1 FROM t_student WHERE name = '이윤나');
-- t_professor, t_department 테이블 :
-- 입사일이 송도권 교수보다 나중에 입사한 사람의
-- 이름과 입사일, 학과명을 출력하세요
SELECT * FROM t_professor;
SELECT * FROM t_department;
SELECT hiredate FROM t_professor WHERE name = '송도권';
SELECT name, hiredate
FROM t_professor
WHERE hiredate > (SELECT hiredate FROM t_professor WHERE name = '송도권');
SELECT p.name "이름", p.hiredate "입사일", d.dname"학과명"
FROM t_professor p, t_department d
WHERE p.deptno = d.deptno
AND hiredate > (SELECT hiredate FROM t_professor WHERE name = '송도권');
-- 쌤이 작성하신 코드
SELECT p.name "교수명", TO_CHAR(p.hiredate, 'YYYY-MM-DD') "입사일", d.dname "학과명"
FROM t_professor p, t_department d
WHERE p.deptno = d.deptno
AND
p.hiredate > (SELECT hiredate FROM t_professor WHERE name = '송도권');
-- t_student 테이블 :
-- 1전공이 101번인 학과의 평균 몸무게보다 몸무게가 많은 학생들의
-- 이름과 몸무게를 출력하세요
SELECT * FROM t_student;
SELECT AVG(weight)
FROM t_student
WHERE deptno1 = 101;
SELECT name "이름", weight "몸무게"
FROM t_student
WHERE weight > (SELECT AVG(weight) FROM t_student WHERE deptno1=101);
-- t_professor 테이블에서 심슨 교수와 같은 입사일에 입사한 교수 중,
-- 조인형 교수보다 월급을 적게 받는 교수의 이름과 급여, 입사일을 출력하세요
SELECT * FROM t_professor;
SELECT pay
FROM t_professor
WHERE name = '조인형';
SELECT hiredate
FROM t_professor
WHERE name = '심슨';
SELECT name, pay
FROM t_professor
WHERE hiredate = (SELECT hiredate FROM t_professor WHERE name = '심슨')
AND pay < (SELECT pay FROM t_professor WHERE name = '조인형');
-- 쌤이 작성하신 코드
SELECT name "이름", pay "급여", hiredate "입사일"
FROM t_professor
WHERE hiredate = (SELECT hiredate FROM t_professor WHERE name = '심슨')
AND
pay < (SELECT pay FROM t_professor WHERE name = '조인형');
-- 2. 다중행 쿼리
-- Sub Query 결과가 2건 이상 출력되는 것을 말합니다.
-- 다중행 Sub Query 와 함께 사용하는 연산자
-- IN 같은 값을 찾음
-- >ANY 최소값을 반환함 (서브쿼리 결과중 가장작은것보다 큰)
-- <ANY 최대값을 반환함 (서브쿼리 결과중 가장큰것보다 작은)
-- <ALL 최소값을 반환함 (서브쿼리 결과중 가장작은것보다 작은)
-- >ALL 최대값을 반환함 (서브쿼리 결과중 가장큰것보다 큰)
-- EXIST Sub Query 값이 있을 경우 반환
-- t_emp2, t_dept2 테이블 : 근무지역 (t_dept2.area)이
-- 서울 지사인 모든 사원들의 사번(empno)과 이름(name), 부서번호(deptno)를 출력하세요
SELECT * FROM t_emp2;
SELECT * FROM t_dept2;
SELECT dcode FROM t_dept2 WHERE area = '서울지사';
SELECT empno, name, deptno
FROM t_emp2
WHERE deptno IN (SELECT dcode FROM t_dept2 WHERE area = '서울지사');
-- t_emp2 테이블 : 전체직원중 과장 직급의 최소연봉자보다
-- 연봉이 높은 사람의 이름(name)과 직급(post), 연봉(pay)을 출력하세요.
-- 단, 연봉 출력 형식은 천 단위 구분 기호와 원 표시를 하세요
SELECT pay FROM t_emp2 WHERE post = '과장';
SELECT name "이름", post "직급", TO_CHAR(pay, '999,999,999') || '원' "연봉"
FROM t_emp2
WHERE pay >ANY (SELECT pay FROM t_emp2 WHERE post = '과장');
-- t_student 테이블 : 전체학생중에서 체중이 4학년 학생들의 체중에서
-- 가장 적게 나가는 학생보다 몸무게가 적은 학생의 이름과 학년과 몸무게를 출력하세요
SELECT * FROM t_student;
SELECT weight FROM t_student WHERE grade = 4;
SELECT name "이름", grade "학년", weight "몸무게"
FROM t_student
WHERE weight <ALL (SELECT weight FROM t_student WHERE grade = 4);
2) SubQuery2.sql
-- 다중칼럼 Sub Query : Sub Query 결과가 2건 이상 출력되는 것
-- Sub Query 결과가 여러 칼럼인 경우.
-- 주로 Primary Key 를 여러 칼럼을 합쳐서 만들었을 경우 한꺼번에
-- 비교하기 위해서 자주 사용.
-- t_student 테이블을 조회하여
-- 각 학년별로 최대 키를 가진 학생들의 학년과 이름과 키를 출력하세요,
-- 학년 오름차순으로 출력
SELECT grade, MAX(height)
FROM t_student
GROUP BY grade;
SELECT grade "학년", name "이름", height "키"
FROM t_student
WHERE (grade, height)
IN (SELECT grade, MAX(height) FROM t_student GROUP BY grade)
--ORDER BY grade ASC;
--ORDER BY 1 ASC;
ORDER BY "학년" ASC; -- 유일하게 ORDER BY 컬럼에다가 작성한 별명을 사용 할 수 있다..!!
-- t_professor , t_department 테이블 :
-- 각 학과별로 입사일이 가장 오래된 교수의 교수번호와 이름, 학과명을 출력하세요.
-- 단 학과이름 순으로 오름차순 정렬하세요
SELECT * FROM t_professor;
SELECT * FROM t_department;
SELECT p.profno "교수번호", p.name "교수명",
TO_CHAR(p.hiredate, 'YYYY-MM-DD') "입사일", d.dname "학과명"
FROM t_professor p, t_department d
WHERE p.deptno = d.deptno
AND (p.deptno, p.hiredate)
IN (SELECT deptno, MIN(hiredate) FROM t_professor GROUP BY deptno)
ORDER BY d.dname ASC;
-- t_emp2 테이블 :
-- 직급별로 해당직급에서 최대 연봉을 받는 직원의 이름과 직급, 연봉을 출력하세요,
-- 단, 연봉순으로 오름차순 정렬하세요
SELECT * FROM t_emp2;
SELECT name "사원명", post "직급", pay "연봉"
FROM t_emp2
WHERE (post, pay) IN (SELECT post, MAX(pay) FROM t_emp2 GROUP BY post)
ORDER BY pay;
-- t_emp2, t_dept2 테이블 :
-- 각 부서별 평균 연봉을 구하고 그 중에서 평균 연봉이 가장 적은 부서의 평균연봉보다
-- 적게 받는 직원들의 부서명, 직원명, 연봉을 출력 하세요
SELECT * FROM t_emp2;
SELECT * FROM t_dept2;
SELECT AVG(pay) FROM t_emp2 GROUP BY deptno;
SELECT d.dname "부서명", e.name "직원명", e.pay "연봉"
FROM t_emp2 e, t_dept2 d
WHERE e.deptno = d.dcode
AND e.pay <ALL (SELECT AVG(pay) FROM t_emp2 GROUP BY deptno)
ORDER BY e.pay;
-- 상호연관 Sub Query
-- t_emp2 테이블 :
-- 직원들 중에서 자신의 직급의 평균연봉과 같거나
-- 많이 받는 사람들의 이름과 직급, 현재 연봉을 출력하세요.
SELECT * FROM t_emp2;
SELECT a.name "사원이름", NVL(a.post, ' ') "직급", a.pay "급여"
FROM t_emp2 a
--WHERE a.pay >= (a.post 직급의 평균 연봉)
WHERE a.pay >= (SELECT AVG(b.pay) FROM t_emp2 b WHERE NVL(a.post, ' ') = NVL(b.post, ' '));
SELECT AVG(b.pay) FROM t_emp2 b WHERE '과장' = b.post;
-- Scalar Sub Query (스칼라 서브 쿼리)
-- JOIN 과 같은 결과 나옴.
-- 그러나 데이터 양이 적은 경우는 스칼라서브쿼리 방식이 Join 보다 나은 성능을 보여줌
-- t_emp2, t_dept2 테이블: 조회하여 사원들의 이름과 부서 이름을 출력
SELECT * FROM t_emp2;
SELECT * FROM t_dept2;
SELECT dname FROM t_dept2;
SELECT e.name "사원이름",
(SELECT d.dname FROM t_dept2 d WHERE e.deptno = d.dcode) "부서이름"
FROM t_emp2 e;
3) SubQuery3.sql
DELETE FROM test_emp_a;
DELETE FROM test_emp_b;
CREATE TABLE test_emp_a (
emp_id NUMBER,
emp_name VARCHAR2(100)
);
CREATE TABLE test_emp_b (
emp_id NUMBER,
emp_name VARCHAR2(100)
);
-- 한 row씩 INSERT
INSERT INTO test_emp_a VALUES (101, '장윤성');
INSERT INTO test_emp_b VALUES (201, '고유성');
SELECT * FROM test_emp_a;
SELECT * FROM test_emp_b;
-- 동시에 여러 개 테이블에 INSERT 하기
-- 다중 테이블 INSERT 구문에 sub query 필요
INSERT ALL
INTO test_emp_a VALUES(102, '조현주')
INTO test_emp_b VALUES(202, '조현주')
SELECT * FROM dual;
SELECT * FROM test_emp_a;
SELECT * FROM test_emp_b;
-- sub query로 INSERT 가능
INSERT INTO test_emp_a (SELECT 400, '안성원' FROM dual);
INSERT INTO test_emp_b (SELECT 500, '김성원' FROM dual);
SELECT * FROM test_emp_a;
SELECT * FROM test_emp_b;
-- 테이블 묻고 더블로!
INSERT INTO test_emp_a (SELECT * FROM test_emp_a);
SELECT * FROM test_emp_a;
INSERT INTO test_emp_b(emp_name) (SELECT emp_name FROM test_emp_a);
SELECT * FROM test_emp_b;
4) rownum.sql
-- SELECT 결과물중 맨 위의 5개만 출력해보고 싶으면 어케 해야 하나?
-- ex) 게시판.. 페이징
-- DBMS 마다 구현 방법 다름
-- MYSQL : LIMIT
-- MS SQL server : TOP
-- ORACLE : ROWNUM
SELECT empno, ename, sal FROM t_emp;
-- 자동적으로 오라클에서 붙여주는 행번호 객체(ROWNUM)
SELECT ROWNUM, empno, ename, sal FROM t_emp;
-- 직원번호 역순으로 출력했으나 ROWNUM은 여전히 1부터 출력됨
SELECT ROWNUM, empno, ename, sal
FROM t_emp
ORDER BY empno DESC;
-- 상위 5개만..!!
SELECT ROWNUM, empno, ename, sal
FROM t_emp
WHERE ROWNUM <= 5
ORDER BY empno DESC;
-- SELECT에 ROWNUM 없어도 동작
SELECT empno, ename, sal FROM T_EMP
WHERE ROWNUM <= 5 ORDER BY empno DESC;
-- ROWNUM > 5은 동작 안함, ROWNUM 범위가 1이 포함 안되면 동작 안함.
SELECT ROWNUM, empno, ename, sal FROM T_EMP
WHERE ROWNUM > 5 ORDER BY empno DESC;
-- 상위 5개 출력
-- row1 ~ row5 까지 출력(1 page)
SELECT ROWNUM, empno, ename, sal FROM T_EMP
WHERE ROWNUM >= 1 AND ROWNUM < 1 + 5
ORDER BY empno DESC;
-- (2page)
SELECT ROWNUM, empno, ename, sal FROM T_EMP
WHERE ROWNUM >= 6 AND ROWNUM < 6 + 5
ORDER BY empno DESC; -- 안나온다..!! 호에엥..!!
-- phonebook 뻥튀기...!!
SELECT * FROM phonebook;
--INSERT INTO phonebook (SELECT * FROM phonebook); -- 에러, Primary Key 중복!
INSERT INTO PHONEBOOK
(SELECT phonebook_seq.nextval, pb_name, pb_phonenum, pb_memo, SYSDATE FROM phonebook);
-- ROWNUM rev.
SELECT pb_uid, pb_name, pb_phonenum FROM phonebook ORDER BY pb_uid DESC;
SELECT t.*
FROM(SELECT pb_uid, pb_name, pb_phonenum FROM phonebook ORDER BY pb_uid DESC) t;
SELECT ROWNUM AS RNUM, t.*
FROM(SELECT pb_uid, pb_name, pb_phonenum FROM phonebook ORDER BY pb_uid DESC) t;
-- 한 페이지 당 5개 데이터
-- 두 번째 페이지
SELECT *
FROM (
SELECT ROWNUM AS RNUM, t.*
FROM(SELECT pb_uid, pb_name, pb_phonenum FROM phonebook ORDER BY pb_uid DESC) t
)
WHERE RNUM >= 6 AND RNUM < 6 + 5;
-- 네번째 페이지
SELECT *
FROM (
SELECT ROWNUM AS RNUM, t.*
FROM (SELECT pb_uid, pb_name, pb_phonenum FROM phonebook ORDER BY pb_uid DESC) t
)
WHERE RNUM >= 16 AND RNUM < 16 + 5;
-- 한 페이지 당 10개 테이터
-- 3번째 페이지
SELECT *
FROM (
SELECT ROWNUM AS RNUM, t.*
FROM(SELECT pb_uid, pb_name, pb_phonenum FROM phonebook ORDER BY pb_uid DESC) t
)
WHERE RNUM >= 21 AND RNUM < 21 + 10;
--자바 응용 가능
SELECT *
FROM (
SELECT ROWNUM AS RNUM, t.*
FROM(SELECT pb_uid, pb_name, pb_phonenum FROM phonebook ORDER BY pb_uid DESC) t
)
WHERE RNUM >= ? AND RNUM < ? + ?; -- 이런 식으로 자바에서 응용가능
2. [폴더] 08_View
1) View.sql
CREATE VIEW v_prof
AS
SELECT profno, name, email, hpage
FROM t_professor;
SELECT * FROM v_prof;
SELECT tname FROM tab; -- VIEW 확인 가능..!!
-- OR REPLACE 는 혹시 이전에 같은 이름의 view 가 있으면 삭제하고 새로 만들라는 의미
CREATE OR REPLACE VIEW v_prof
AS
SELECT profno, name, email, hpage
FROM t_professor;
SELECT * FROM v_prof;
-- VIEW 생성시 별도의 컬럼 이름을 지정해 줄 수 있다
CREATE OR REPLACE VIEW v_prof(pfno, nm, em, hp)
AS
SELECT profno, name, email, hpage
FROM t_professor;
SELECT * FROM v_prof;
-- t_professor, t_department 테이블을 join 하여
-- 교수번호와 교수이름과 소속학과 이름을 조회하는 view 를 생성하세요.
-- (이름: v_prof_dept)
SELECT * FROM t_professor;
SELECT * FROM t_department;
CREATE OR REPLACE VIEW v_prof_dept
AS
SELECT p.profno "교수번호", p.name "교수명", d.DNAME "소속학과명"
FROM t_professor p, t_department d
WHERE p.deptno = d.deptno;
-- 확인
SELECT * FROM v_prof_dept;
-- t_student, t_department 테이블 :
-- 학과별로 학생들의 최대키와 최대몸무게, 학과 이름을 출력하세요
SELECT * FROM t_student;
SELECT * FROM t_department;
SELECT
d.dname "학과명",
s.max_height "최대키",
s.max_weight "최대몸무게"
FROM
(SELECT deptno1, MAX(height) max_height, MAX(weight) max_weight
FROM t_student
GROUP BY deptno1 ) s ,
t_department d
WHERE
s.deptno1 = d.deptno;
-- t_student, t_department 테이블 :
-- 학과별(deptno1)로 가장 키가 큰 학생들의 이름과 키, 학과이름을
-- 인라인뷰 를 사용하여 다음과 같이 출력하세요
SELECT * FROM t_student;
SELECT * FROM t_department;
SELECT
d.dname "학과명", a.max_height "최대키", s.name "학생이름", s.height "키"
FROM
(SELECT deptno1, MAX(height) max_height FROM t_student GROUP BY deptno1) a,
t_student s, t_department d
WHERE
s.deptno1 = a.deptno1 AND s.height = a.max_height
AND s.deptno1 = d.deptno;
-- t_student 테이블 :
-- 학생의 키가 동일 학년의 평균 키보다 큰 학생들의 학년과 이름과 키, .
-- 해당 학년의 평균키를 출력하되, inline view 를 사용해서 아래와 같이 출력하세요.
-- 단 학년 칼럼은 오름 차순으로 정렬.
SELECT * FROM t_student;
SELECT
s.grade "학년", s.name "이름", s.height "키", a.avg_height "평균키"
FROM
(SELECT grade, AVG(height) avg_height FROM t_student GROUP BY grade) a,
t_student s
WHERE
a.grade = s.grade AND s.height > a.avg_height
ORDER BY 1;
3. [폴더] 09_Constraint
1) Constraint01.sql
-- 제약조건 (Constraint)
-- 테이블 생성시 동시에 설정하기
-- t_dept2.dcode 참조 예정
SELECT * FROM t_dept2; -- dcode : 0001, 1000 ... 1011
-- 제약조건명을 명시하지 않는 방법
DROP TABLE t_emp3 CASCADE CONSTRAINT;
CREATE TABLE t_emp3 (
no NUMBER(4) PRIMARY KEY,
name VARCHAR2(10) NOT NULL,
jumin VARCHAR2(13) NOT NULL UNIQUE,
area NUMBER(1) CHECK(area < 5),
deptno VARCHAR2(6) REFERENCES t_dept2(dcode)
);
-- 별도의 항목으로 제약조건 정의 가능
-- not null은 별도로 지정 불가
DROP TABLE t_emp4 CASCADE CONSTRAINT;
CREATE TABLE t_emp4 (
no NUMBER(4),
name VARCHAR2(10) NOT NULL,
jumin VARCHAR2(13) NOT NULL,
area NUMBER(1),
deptno VARCHAR2(6),
PRIMARY KEY(no),
UNIQUE(jumin),
CHECK(area < 5),
FOREIGN KEY(deptno) REFERENCES t_dept2(dcode)
);
-- 제약조건명을 명시하여 정의
DROP TABLE t_emp3 CASCADE CONSTRAINT;
CREATE TABLE t_emp3 (
no NUMBER(4) CONSTRAINT emp3_no_pk PRIMARY KEY,
name VARCHAR2(10) CONSTRAINT cmp3_name_nn NOT NULL,
jumin VARCHAR2(13)
CONSTRAINT emp3_jumin_nn NOT NULL
CONSTRAINT emp3_jumin_uk UNIQUE,
area NUMBER(1) CONSTRAINT emp3_area_dk CHECK(area < 5),
deptno VARCHAR2(6) CONSTRAINT emp3_deptno_fk REFERENCES t_dept2(dcode)
);
DROP TABLE t_emp3 CASCADE CONSTRAINT;
CREATE TABLE t_emp3 (
no NUMBER(4),
name VARCHAR2(10) CONSTRAINT emp3_name_nn NOT NULL,
jumin VARCHAR2(13) CONSTRAINT emp3_jumin_nn NOT NULL,
area NUMBER(1),
deptno VARCHAR2(6),
CONSTRAINT emp3_no_pk PRIMARY KEY(no),
CONSTRAINT emp3_jumin_uk UNIQUE(jumin),
CONSTRAINT emp3_area_ck CHECK(area < 5),
CONSTRAINT emp3_deptno_fk FOREIGN KEY(deptno) REFERENCES t_dept2(dcode)
);
-- 제약조건 조회하기
SELECT owner, constraint_name, constraint_type, status
FROM user_constraints
WHERE table_name = 'T_EMP4'; -- 테이블명 대문자로!
SELECT owner, constraint_name, constraint_type, status
FROM user_constraints
WHERE table_name = 'T_EMP3';
-- #9005 : 제약조건에 맞는 / 위배되는 DML 시도해보기
INSERT INTO t_emp3 VALUES(1, '오라클', '1234561234567', 4, 1000);
-- 두번 실행하면 오류!
-- ORA-00001: unique constraint (SCOTT0316.EMP3_NO_PK) violated
INSERT INTO t_emp3 VALUES(2, '오라클', '1234561234567', 4, 1000);
-- jumin UNIQUE 오류!
-- ORA-00001: unique constraint (SCOTT0316.EMP3_JUMIN_UK) violated
INSERT INTO t_emp3 VALUES(2, '오라클', '2222222222222222', 4, 1000);
-- VARCHAR2(13) 초과 오류!
-- ORA-12899: value too large for column "SCOTT0316"."T_EMP3"."JUMIN" (actual: 16, maximum: 13)
INSERT INTO t_emp3 VALUES(2, '오라클', '2222222222222', 10, 1000);
-- CHECK 오류!
-- ORA-01438: value larger than specified precision allowed for this column
INSERT INTO t_emp3 VALUES(2, '오라클', '2222222222222', 3, 2000);
-- FK오류!
-- ORA-02291: integrity constraint (SCOTT0316.EMP3_DEPTNO_FK) violated - parent key not found
INSERT INTO t_emp3 (NO, jumin, area, deptno) VALUES (2, '3333333333333', 4, 1001);
-- NN 오류! name 항목이 없음, 제약조건이 not null임...!!
-- ORA-01400: cannot insert NULL into ("SCOTT0316"."T_EMP3"."NAME")
-- INSERT뿐 아니라 UPDATE/DELETE 에서도 오류 발생 가능
UPDATE t_emp3 SET area = 10 WHERE NO = 1; -- CHECK 값 오류
SELECT * FROM t_emp3;
-- 위의 SELECT문을 통해 deptno, 즉 dcode 1000이 참조되어 있는 것을 확인..!!
DELETE FROM t_dept2 WHERE dcode = 1000;
-- 참조하고 있는 부모는 삭제 불가
-- ORA-02292: integrity constraint (SCOTT0316.EMP3_DEPTNO_FK) violated - child record found
-- 테이블 생성 후에 ALTER 명령 사용하여 제약조건 추가 가능!
-- t_emp4 의 name 컬럼 UNIQUE 제약 조건 추가
-- ALTER ~ ADD
-- ALTER ~ MODIFY
-- ALTER ~ DROP
ALTER TABLE t_emp4 ADD CONSTRAINT emp4_name_uk UNIQUE(name);
-- t_emp4 테이블의 area 컬럼에 NOT NULL 제약조건 추가해보세요
ALTER TABLE t_emp4 ADD CONSTRAINT emp4_area_nn NOT NULL(area); -- 에러
-- 이미 컬럼의 기본값인 NULL로 되어 있기 때문에 ADD가 아닌 MODIFY로 해야 한다.
-- 위의 112번째를 아래의 115번째줄처럼 바꿔서 처리해야 가능하다..!!
ALTER TABLE t_emp4 ADD CONSTRAINT emp4_name_uk UNIQUE(name);
-- 외래키 추가
-- t_emp4 테이블의 no 컬럼이 t_emp2 테이블의
-- empno 컬럼의 값을 참조하도록 참조키 제약조건을 설정하세요
ALTER TABLE t_emp4
ADD CONSTRAINT emp4_name_fk FOREIGN KEY(name) REFERENCES t_emp2(name);
-- 그냥 실행하면 에러다 ORA-02270: no matching unique or primary key for this column-list
-- 참조되는 부모테이블의 컬럼은 Primary Key 이거나 Unique 해야 한다!!!
-- 일단 부모테이블의 name을 unique로 바꾼뒤 위의 쿼리를 다시 실행하기..!!
-- 그럼 가능하다...
ALTER TABLE t_emp2
ADD CONSTRAINT emp2_name_uk UNIQUE(name);
-- t_emp3 테이블을 DROP 하고, 이전과 같은 조건으로 다시 만들되
-- t_dept2(dcode)를 참조하는 detpno 컬럼은
-- 부모테이블의 해당 데이터가 삭제되면 함께 삭제되도록 설정
DROP TABLE t_emp3 CASCADE CONSTRAINT;
CREATE TABLE t_emp3 (
no NUMBER(4) CONSTRAINT emp3_no_pk PRIMARY KEY,
name VARCHAR2(10) CONSTRAINT cmp3_name_nn NOT NULL,
jumin VARCHAR2(13)
CONSTRAINT emp3_jumin_nn NOT NULL
CONSTRAINT emp3_jumin_uk UNIQUE,
area NUMBER(1) CONSTRAINT emp3_area_dk CHECK(area < 5),
deptno VARCHAR2(6)
CONSTRAINT emp3_deptno_fk REFERENCES t_dept2(dcode)
ON DELETE CASCADE -- 부모 삭제되면 자식도 삭제
--ON DELETE SET NULL -- 부모 삭제되면 NULL 값으로
);
-- t_emp4 테이블의 name 필드의 제약조건 예
-- 부모테이블이 삭제되면 NULL이 되도록 설정하기 (ALTER 사용)
ALTER TABLE t_emp4 DROP CONSTRAINT emp4_name_fk; -- 일단 기존 제약조건 삭제
ALTER TABLE t_emp4
ADD CONSTRAINT emp4_name_fk FOREIGN KEY(name)
REFERENCES t_emp2(name)
ON DELETE SET NULL; -- 부모 삭제되면 자식은 null로 변환
------------------------------------------------------------------------------
-- DISABLE NOVALIDATE
-- t_validate, t_novalidate 테이블 사용하기
SELECT * FROM t_novalidate;
SELECT * FROM t_validate;
SELECT owner, constraint_name, CONSTRAINT_type, status
FROM user_constraints
WHERE table_name = 'T_VALIDATE';
INSERT INTO t_validate VALUES(1, 'DDD'); -- 처음에는 에러! PK이니깐..!!
-- ORA-00001: unique constraint (SCOTT0316.TV_NO_PK) violated
SELECT owner, constraint_name, constraint_type, status
FROM user_constraints
WHERE table_name = 'T-NOVALIDATE';
INSERT INTO t_novalidate VALUES(1, 'DDD'); -- 처음에는 에러! PK이니깐..!!
-- ORA-00001: unique constraint (SCOTT0316.SYS_C007028) violated
4. [폴더] 10_Index
1) Index.sql
-- 인덱스 (Index)
-- 데이터(레코드)를 빠르게 접근하기 위해 <키, 주소> 쌍으로 구성된 데이터 구조
--인덱스가 없으면 테이블에서 특정값을 찾기 위해 모든 데이터를 다 뒤지는 TABLE SCAN 발생
--기본키를 위한 인덱스를 기본 인덱스,
-- 일반적으론 테이블에 기본키가 있으면 기본키에 대한 기본 인덱스가 자동으로 생성된다.
-- 인덱스 종류
-- m-원 검색트리, B-트리, B*-트리, B+-트리 등이 있다.
-- 인덱스를 잘 사용하면 데이터베이스 성능이 좋아지게 할수도 있지만,
-- 잘못 설정하면 오히려 역효과가 날수도 있다.
-- 오라클
-- 기본적으로 INSERT 되는 순서대로 이입력됨. 정렬없이 저장됨.
-- 사원의 인덱스
SELECT empno, ROWID FROM t_emp; -- ROWID, ROW INSERT 될때마다 부여됨, SELECT 시 기본적으로 참조
SELECT empno, ROWID FROM t_emp ORDER BY empno DESC; -- ROWID도 내림차순이 됨
SELECT ename, ROWID
FROM t_emp ORDER BY ename DESC;
-- 인덱스 <key, rowid> 에 입력
-----------------------------------------------------------
-- (1) B-tree 인덱스
-- ① UNIQUE INDEX
-- ② Non Unique INDEX
-- ③ Function Based Index
-- ④ Descending Index
-- ⑤ 결합 인덱스 (Composite Index)
-----------------------------------------------------------
-- UNIQUE INDEX
-- key 값이 중복되는 데이터가 없다는 뜻
-- 이경우 인덱스 성능이 매우 좋아진다.
-- 당장은 중복된 값이 안들어오지만, 향후에 중복될 값이 입력될 가능성이 있는 칼럼에는
-- 절.대.로 인덱스 생성하면 안됨!!!!!!!!
-- 인덱스 생성 구문
-- CREATE UNIQUE INDEX 인덱스명
-- ON 테이블이름(컬럼명1 ASC | DESC, 컬럼명2 ... )
-- 연습
-- t_dept2 테이블의 dname 컬럼에 UNIQUE INDEX 생성
-- 인덱스의 이름은 idx_dept2_dname 으로 생성
SELECT * FROM t_dept2;
CREATE UNIQUE INDEX idx_dept2_dname ON t_dept2(dname);
INSERT INTO t_dept2 VALUES(9100, '임시매장', 1006, '서울지사');
INSERT INTO t_dept2 VALUES(9101, '임시매장', 1006, '부산지사'); -- 에러
-- ORA-00001: unique constraint (SCOTT0316.IDX_DEPT2_DNAME) violated
-- Non Unique INDEX
-- UNIQUE 인덱스가 성능은 좋으나 모든 컬럼에 다 만들수 있는 것은 아니다.
-- 중복된 데이터가 들어가야 하는 경우는 Non Unique INDEX 사용
-- 인덱스 생성 구문
-- CREATE INDEX 인덱스명
-- ON 테이블이름(컬럼명1 ASC | DESC, 컬럼명2 ... )
-- 예제
-- t_professor 테이블의 position 칼럼에 Non unique 인덱스를 내림차순 생성
CREATE INDEX idx_prof_position
ON t_professor(POSITION DESC);
-- ① DBeaver에서 확인해보기
/*
* 인덱스는 어느 칼럼에 만들어야 하나?
* 답은 WHERE절에 오는 조건 칼럼이나 조인 조건 칼럼에 만들어 두어야 함
* 가령 WHERE sal = 100 <-- sal 컬럼에 인덱스 만들어야 함.
* sal 칼럼에 인덱스가 있다면 오라클은 sal 칼럼의 인덱스를 활용할수 있다.
*
* 그런데!
* sal 컬럼에 인덱스 생성하고 나서..
* 정작 sql 문장에서는 WHERE sal + 100 = 200 조건으로 조회하면..
* 인덱스는 활용 못함.
* 이런 현상을 INDEX Suppressing Error 라고 함
* 인덱스는 잘 만들어 놓고.. SQL 를 잘 못 작성하여 인덱스 사용 못하는 경우.
*
* 명심! : 인덱스를 사용하려 했다면, WHERE 조건절에 절대로 다른 형태로 가공해서는 안됨.
*
* 그런데, 꼭 그렇게 사용해야 한다면?
* WHERE sal + 100 = 200.. ??
*
* 그러면 인덱스를 생성할때 저 형태로 , 즉 sal + 100 인덱스를 생성하면 됨.
* 이를 '함수 기반 인덱스' 라 함.
*
*/
CREATE INDEX idx_prof_pay_fbi
ON t_professor(pay + 100);
-- ② DBeaver에서 확인해보기
-- 위와 같이 하면 오라클은 인덱스를 만들때 저 연산을 수행해서 인덱스 저장 가능.
-- 함수기반인덱스(FBI) 는 근본 해결책은 아니다.
-- 조건이 변경되면 다시 만들어야 하고..
-- FBI 는 기존 인덱스를 활용할수 없는 단점도 있다.
-- ※ dbeaber 에서 생성 형태 확인해보자
--------------------------------------------
-- DESCENDING INDEX
-- 인덱스들을 생성할때 내림차순 (큰 값이 먼저 오게) 인덱스 생성
-- 큰값을 (큰값부터) 많이 조회하는 SQL에서 유리.
-- ex) 계좌조회. 최근날짜 (큰 날짜) 부터..
-- ex) 매출상위 매장순 .. 매출 큰 매장부터..
-- 예제
CREATE INDEX idx_prof_pay
ON t_professor(pay DESC);
-- ③ DBeaver에서 확인해보기
[추가] DBeaver에서 확인 하기 - ①
[추가] DBeaver에서 확인 하기 - ②
[추가] DBeaver에서 확인 하기 - ③
'웹_프론트_백엔드 > JAVA프레임윅기반_풀스택' 카테고리의 다른 글
| 2020.04.13 (0) | 2020.04.13 |
|---|---|
| 2020.04.10 (0) | 2020.04.10 |
| 2020.04.08 (0) | 2020.04.08 |
| 2020.04.07 (0) | 2020.04.07 |
| 2020.04.06 (0) | 2020.04.06 |