4.OracleSQL DDL/뷰/시퀀스/동의어/인덱스

2022.07.25.월

---

가.DDL(Data Definition Language)

=>데이터베이스의 구조를 생성하거나 수정 및 삭제하는데 사용되는 SQL문

=>자동으로 커밋됨

 

 -오라클 객체

=>오라클 데이터 베이스의 데이터 구조

→테이블(table) : 기본적인 데이터 저장 단위로 행과 열로 구성된 객체

→인덱스(index) : 테이블에 저장된 데이터의 검색 성능 향상 목적을 위한 객체

→뷰(view) : 한 개 이상의 테이블의 논리적인 부분 집합을 표시할 수 있는 객체

→시퀀스(sequence) : 테이블의 특정 컬럼값에 숫자 값 자동 생성 목적을 위한 객체

→동의어(synonym) : 객체에 대한 동의어를 설정하기 위한 객체

 

 -테이블 생성

/*
문법
CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입 [DEFAULT 값 | 제약조건][,...] );
*/

SELECT deptno,dname,loc
FROM SCOTT.dept;

 

 -DEFAULT 옵션

=>INSERT문을 사용하여 테이블에 데이터를 저장할 때

    특정 컬럼에 값을 지정하지 않으면 자 동으로 널(null)값이 저장

=>DEFAULT 옵션을 사용하면 컬럼에 값을 지정하지 않아도 자동으로

     기본값이 입력되어 널(null)값이 저장되는 것을 방지

CREATE TABLE employee2
( empno NUMBER(4),
 ename VARCHAR2(20),
 --현재날짜를 디폴트 설정
 hiredate DATE DEFAULT SYSDATE,
 sal NUMBER(7,2));

 -제약조건

=>제약 조건은 테이블에 올바르지 않은 부적절한 데이터가 저장되는 것 방지하기 위해

    테이블을 생성할 때 각 컬럼에 대해서 정의하는 여러 가지 규칙

 

 -제약조건 타입

NOT NULL : 해당 컬럼 값으로 NULL을 허용하지 않음 컬럼 레벨 방식만 지원

/*
문법
CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입 [CONSTRAINT 제약조건명] NOT NULL,
컬럼명 데이터타입,
...
);
*/

--컬럼 레벨 형식으로 부서위치를 NOT NULL 제약조건으로 지정
CREATE TABLE department6
( deptno NUMBER(2) CONSTRAINT department6_deptno_pk PRIMARY KEY,
 dname VARCHAR2(15) CONSTRAINT department6_dname_uk UNIQUE,
 loc VARCHAR2(15) CONSTRAINT department6_loc_nn NOT NULL);

UNIQUE : 테이블 내에서 해당 컬럼 값은 항상 유일한 값 컬럼 레벨/테이블 레벨 방식 모두 지원

/*
문법
CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입 [CONSTRAINT 제약조건명] UNIQUE,
컬럼명 데이터타입,
...
);

-기본 키가 아닌 경우에도 컬럼의 모든 데이터가 유일해야 되는 경우
-기본 키 제약조건과의 차이점
 하나의 테이블에 UNIQUE 제약조건은 여러 개 지정 가능
 널(null)값도 저장 가능
 */
 
 --컬럼 레벨 형식으로 부서명을 UNIQUE 로 지정
 CREATE TABLE department4
( deptno NUMBER(2) CONSTRAINT department4_deptno_pk PRIMARY KEY,
 dname VARCHAR2(15) CONSTRAINT department4_dname_uk UNIQUE,
 loc VARCHAR2(15) );

PRIMARY KEY : 해당 컬럼 값은 반드시 존재해야 하고 유일해야 함

                            NOT NULL 과 UNIQUE 조건을 결합한 형태

                            컬럼 레벨/테이블 레벨 방식 모두 지원

/*
문법
CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입 [CONSTRAINT 제약조건명] PRIMARY KEY,
컬럼명 데이터타입,
...
);

-테이블 레벌 방식 기본키 제약조건 추가 문법
-테이블에서 필요한 컬럼을 모두 정의하고 가장 마지막에 제약조건을 추가하는 방법
CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입, 컬럼명 데이터타입,
[CONSTRAINT 제약조건명] PRIMARY KEY(컬럼명[,컬럼명2)
);


-중복저장 불가
-null값 저장 불가
*/


--제약조건명을 ‘테이블명_컬럼명_pk' 형식 department_deptno_pk로 지정
--이후 PRIMARY KEY를 기술함으로써 deptno컬럼에 기본 키 제약조건이 설정
CREATE TABLE department
( deptno NUMBER(2) CONSTRAINT department_deptno_pk PRIMARY KEY,
 dname VARCHAR2(15),
 loc VARCHAR2(15) );
 
 --테이블 레벨 방식
 CREATE TABLE department3
( deptno NUMBER(2),
 dname VARCHAR2(15),
 loc VARCHAR2(15) ,
 CONSTRAINT department3_deptno_pk PRIMARY KEY(deptno, loc)
);

FOREIGN KEY : 해당 컬럼의 값이 다른 테이블의 컬럼의 값을 참조

                            참조되는 컬럼에 없는 값은 저장이 불가능

                             컬럼 레벨/테이블 레벨 방식 모두 지원

--해당 테이블에서 다른 테이블을 참조할 때 
--올바른 데이터값만 참조 가능하도록 제약하는 방법

--존재하는 데이터 및 null값만 저장가능 하도록 제약하는 방법

/*
문법
CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입 [CONSTRAINT 제약조건명] REFERENCES 부모테이
블명(컬럼명),
컬럼명 데이터타입,
...
);

-참조하는 부모 테이블의 컬럼은 반드시 기본 키(PRIMARY KEY) 
 또는 UNIQUE 키로 제약조건이 설정된 컬럼
*/

CHECK : 해당 컬럼에 가능한 데이터 값의 범위나 사용자 조건을 지정

               컬럼 레벨/테이블 레벨 방식 모두 지원

/*
문법
CREATE TABLE [스키마].테이블명
( 컬럼명 데이터타입 [CONSTRAINT 제약조건명] CHECK(조건식) , 컬럼명 데이터타입,
...
);
*/

--컬럼 레벨 형식으로 부서명 값에 ‘개발’과 ‘인사’ 만 저장 
--가능하도록 CHECK 제약 조건을 지정
CREATE TABLE department7
( deptno NUMBER(2) ,
 dname VARCHAR2(15)
 CONSTRAINT department7_dname_ck CHECK( dname IN('개발','인사')) ,
 loc VARCHAR2(15)
);

 -테이블 삭제

/*
문법
DROP TABLE 테이블명 [CASCADE CONSTRAINTS];

-테이블에 저장된 모든 데이터 및 관련된 인덱스와 
 FOREIGN KEY 제약조건을 제외한 모든 제약조건이 같이 삭제
-FOREIGN KEY 제약조건 CASCADE CONSTRAINTS 옵션을 지정해서 
 삭제하면 연쇄적으로 제약조건도 삭제
*/

 

 -테이블 변경

=>테이블의 컬럼의 추가,삭제 및 컬럼의 타입이나 길이 변경, 제약조건 추가(데이터가 없어야 함)

=>삭제 테이블에 대한 구조 변경은 기존에 저장되어 있던 데이터에 영향을 줌

--desc 테이블 : 컬럼보기

--1.컬럼 추가
/*
문법
ALTER TABLE 테이블명
ADD ( 컬럼명 데이터타입 [, 컬럼명 데이터타입]);

-데이터는 자동으로 null 초기화
*/

--이메일 컬럼과 주소 컬럼 추가
ALTER TABLE emp04
ADD ( email VARCHAR2(10) , address VARCHAR2(20) );

--2.컬럼 변경
/*
문법
ALTER TABLE 테이블명
MODIFY ( 컬럼명 데이터타입 [, 컬럼명 데이터타입]);

-데이터 타입, 크기, DEFAULT값 변경, 문자 컬럼의 전체 길이 변경 가능(행이 없을 경우)
*/

--이메일 컬럼의 크기를 40byte로 변경
ALTER TABLE emp04
MODIFY ( email VARCHAR2(40) );

--3.컬럼 삭제
/*
문법
ALTER TABLE 테이블명
DROP ( 컬럼명 [,컬럼명] );

-값의 존재 여부와 상관없이 무조건 삭제
*/

--이메일 컬럼삭제
ALTER TABLE emp04
DROP ( email );

--4.제약조건 추가
/*
문법
ALTER TABLE 테이블명
ADD [CONSTRAINT 제약조건명] 제약조건타입(컬럼명);

ALTER TABLE 테이블명
MODIFY ( 컬럼명 데이터타입 [CONSTRAINT 제약조건명] NOT NULL );

-NOT NULL 제외 나머지 제약조건 : ALTER TABLE ADD문 사용
-NOT NULL 제약조건 : ALTER TABLE MODIFY문 사용
*/

--기본키 제약조건 테이블에 추가
ALTER TABLE dept03
ADD CONSTRAINT dept03_deptno_pk PRIMARY KEY(deptno);

--NOT NULL 제약조건 추가
ALTER TABLE dept03
MODIFY ( dname VARCHAR2(15) CONSTRAINT dept03_dname_nn NOT NULL );

--5.제약조건 삭제
/*
문법
ALTER TABLE 테이블명
DROP PRIMARY KEY|UNIQUE(컬럼)|
CONSTRAINT 제약조건명 [CASCADE];
*/

--키 제약조건 삭제(2가지 방법)
ALTER TABLE dept03
DROP PRIMARY KEY;

ALTER TABLE dept03
DROP CONSTRAINT dept03_deptno_pk;

--6.제약조건 활성화/비활성화
/*
문법
ALTER TABLE 테이블명
DISABLE|ENABLE CONSTRAINT 제약조건명 [CASCADE];

-무결성이 보장된 데이터를 저장하려고 할 때
 키 제약조건을 비활성화 시켜 저장 성능을 향상 시킬 수 있음
*/

--기본키 비활성화
ALTER TABLE emp05
DISABLE CONSTRAINT emp05_empno_pk;
--기본키 활성화
ALTER TABLE emp05
ENABLE CONSTRAINT emp05_empno_pk;

---

나.뷰

=>물리적인 테이블 또는 다른 뷰(View)를 기반으로 하는 논리적인 테이블

=>논리적인 테이블이라고 부른 까닭은 물리적인 테이블처럼 실제 데이터를

    저장하고 있지 않으나 사용자는 마치 테이블을 사용하는 것과

     동일하게 뷰를 사용할 수 있기 때문

--1.뷰 생성
--관리자에서 grant create view to 로 뷰생성 권한 할당 해야함
/*
문법
CREATE [OR REPLACE] VIEW 뷰이름 [(alias[,alias] ...)]
AS
서브쿼리
[WITH CHECK OPTION [CONSTRAINT 제약조건명]]
[WITH READ ONLY [CONSTRAINT 제약조건명] ];

-일반적으로 읽기모드로 만듦
*/

--복잡한 SQL 간소화 목적
--복잡한 조인문장
SELECT empno,ename, d.dname, d.deptno
FROM emp e JOIN dept d
ON e.deptno = d.deptno
WHERE e.deptno = 20;

--SELECT 문을 하나의 VIEW로 정의
CREATE VIEW emp_view
AS
SELECT empno,ename, d.dname, d.deptno
FROM emp e JOIN dept d
ON e.deptno = d.deptno
WHERE e.deptno = 20;

--보안문제(민감한 정보 감추고 출력)
-월급을 제외한 뷰 작성
CREATE VIEW emp_view2
AS
SELECT empno,ename,job,mgr,hiredate,comm,deptno
FROM emp;

--2.뷰 수정
--CREATE OR REPLACE 문 사용

--3.뷰 종류
/*
1)단순뷰 : 하나의 기본 테이블에 대해서 정의한 뷰 DML문 실행이 가능
2)복합뷰 : 두 개 이상의 기본 테이블에 대해서 정의한 뷰(두개 이상 테이블 조인)
*/

--4.뷰 삭제
/*
문법
DROP VIEW 뷰이름;
*/

---

다.시퀀스

=>테이블의 특정 컬럼값을 넘버링(numbering)하기 위해서 사용

=>Oracle SQL 전용

--1.시퀀스 생성
/*
문법
CREATE SEQUENCE 시퀀스명
[ START WITH n] : 시작번호(기본값 1)
[ INCREMENT BY n] : 증가값(기본값 1)
[ MAXVALUE n | NOMAXVALUE ] : 시퀀스의 최대값(기본값 +무한대)
[ MINVALUE n | NOMINVALUE ] : 시퀀스의 최소값(기본값 -무한대)
[ CYCLE | NOCYCLE ] : 최대값까지 증가하면 MINVALUE부터 다시 증가(기본값 NOCYCLE)
[ CACHE n | NOCACHE ] : n개를 자동으로 메모리에 할당(기본값 20)
*/

--부서번호를 자동으로 부여해주는 시퀀스 객체 생성
CREATE SEQUENCE dept_deptno_seq
START WITH 10 --10시작
INCREMENT BY 10 --10증가
MAXVALUE 100 --최대값 100
MINVALUE 5 --최대값 도달시 5부터 시작
CYCLE
NOCACHE;

--2.NEXTVAL/CURRVAL
/*
문법
시퀀스명.NEXTVAL/CURRVAL

NEXTVAL 호출 : 시퀀스 값 증가
CURRVAL 호출 : 현재 시퀀스 값 반환
*/

--2.시퀀스 수정
/*
문법
ALTER SEQUENCE 시퀀스명
[ INCREMENT BY n]
[ MAXVALUE n | NOMAXVALUE ]
[ MINVALUE n | NOMINVALUE ]
[ CYCLE | NOCYCLE ]
[ CACHE n | NOCACHE ]
*/

--3.시퀀스 삭제
/*
문법
DROP SEQUENCE 시퀀스명;
*/

---

라.동의어

=>데이터베이스 객체에 대한 별칭(스키마.객체에 대한 별칭)

--1.동의어 생성
/*
문법
CREATE [PUBLIC] SYNONYM 동의어
FOR 스키마.객체;
*/

-- SYS
GRANT create synonym
TO scott; -- SCOTT 계정
CREATE SYNONYM emp_synonym
FOR hr.employees;

--2.동의어 삭제
/*
문법
DROP SYNONYM 시노님명;
*/

---

마.인덱스

=>테이블의 검색 속도 향상 목적

=>Oracle SQL 전용

=>컬럼값(num) + 행주소(rowid)를 가지고 있음

=>명시적으로 생성하지 않아도  기본 키나 UNIQUE키와

    같은 제약조건을 지정하면자동으로 인덱스가 생성

 

 -인덱스 작동방식

  1)num 컬럼에  index 설정(unique, non_unique)

  2)num 컬럼 오름차순 정렬

  3)중간 컬럼(ROOT)을 기준(<, >)으로  B_tree 생성

  4)이진검색

    ∴모든 테이블을 찾는 것보다 검색 속도 향상

 

--rowid  확인
select rowid
from emp;

--rowid 8자리 의미
/*
6 : table 정보
3 : def 파일 정보
6 : def 파일안의 테이블이 적혀있는 블럭id
3 : 블럭의 행 번호
*/

--인덱스 생성
/*
문법
CREATE [UNIQUE] INDEX 인덱스명
ON 테이블( 컬럼[,컬럼] );
*/

//사원이름에 인덱스 생성
CREATE INDEX emp_ename_idx
ON emp(ename);

--인덱스 삭제
--DROP INDEX 인덱스명;