DDL (Data Definition Language)
- 데이터 베이스의 구조(오라클 객체)를 생성하거나 수정 및 삭제하는데 사용되는 SQL문
- 자동으로 COMMIT; 되기 때문에 데이터베이스에 즉각 영향을 미치며, 데이터 베이스 사전에 정보가 저장된다.
- 오라클 데이터 베이스는 다음과 같은 여러개의 데이터 구조를 갖고 있으며 '오라클 객체'라고 부른다
| 객체명 | 설명 |
| 테이블(table) | 기본적인 데이터 저장 단위로 row와 column로 구성된 객체 |
| 인덱스(index) | 테이블에 저장된 데이터의 검색 성능 향상 목적을 위한 객체 |
| 뷰(view) | 한 개 이상의 테이블의 논리적인 부분 집합을 표시할 수 있는 객체 |
| 시퀀스(sequence) | 테이블의 특정 컬럼값에 숫자 값 자동 생성 목적을 위한 객체 |
| 동의어(synonym) | 객체에 대한 동의어를 설정하기 위한 객체 |
- DDL 명령어
| SQL 종류 | 명령문 |
|
Data Definition Language (DDL: 데이터 정의어) |
CREATE(데이터베이스 객체 생성) |
TABLE 객체
TABLE 객체 생성
CREATE TABLE 테이블명(column명 datatype 제약조건, ...);
오라클의 data type
| Data Type | 설명 |
| CHAR(size) |
- 고정 길이의 문자 데이터를 저장하기 위한 자료형 (1byte~2000byte) - 지정된 길이보다 작은 데이터가 입력되면 나머지 공간은 공백으로 채워짐. - 일반적으로 데이터 길이가 항상 고정된 크기를 갖는 |
| VARCHAR2(size) |
- 가변 길이의 문자데이터를 저장하기 위한 자료형 (1byte~4000byte) - 지정된 길이보다 작은 데이터가 입력되면 입력된 문자열의 길이만큼만 기억공간이 할당됨. - 일반적으로 입력되는 데이터 길이가 유동적인 경우에 사용. |
| NVARCHAR2(size) | - 국가별 국가집합에 따른 크기의 문자 또는 바이트의 가변 길이 문자 (1byte~4000byte) |
| NUMBER(p, s) |
- 숫자갑승ㄹ -38자리수 부터 +38자리수를 저장 - 가변 길이의 숫자 저장 - p : 전체 자릿수 / s : 표시할 소수점 자리수 |
| DATE |
- 날짜 및 시간 - JDBC에서 getDate()로 불러올수도 있지만 TO_CHAR 함수를 이용해서 문자열로 바꿔서 읽어와야한다. |
| ROWID | 안배움 |
| BLOB |
- 파일 자체를 DB에 저장할때 사용. - 2 진데이터. 즉, 바이너리 데이터를 저장할때 사용 |
| CLOB |
- 문자 데이터 최대 4GB 까지 저장 가능하다. - JDBC에서 읽어올때 getClob()로 읽어와야한다. |
| BFILE | 안배움 |
Default 옵션
- Default 옵션을 넣으면 null값 대신 지정한 default값이 저장된다.
- 고정된 값만을 가지는 컬럼에 대해서 유용하게 사용할 수 있다.
ex) 현재시간, 성별
CREAT TABLE employee2
(empno NUMBER(4),
ename VARCHAR2(20),
hiredate DATE DEFAULT SYSDATE,
sal NUMBER(7,2));
제약 조건(Constraints Rule)
- 테이블을 생성할때 각 컬럼에 대해서 정의하는 어려가지 규칙
- 테이블에 부적절한 데이터가 저장되는 것을 방지하기위해서 사용함
- 데이터 베이스 설계 단계에서 무결성을 보장 받기 위한 방법
제약 조건 설정하는 법
<!--Column level로 제약조건 설정하기-->
CREATE TABLE 테이블명(컬럼명 데이터타입 CONSTRAINT 제약조건명 제약조건, ...);
<!--예시-->
CREATE TABLE DEPT2(
DEPTNO NUMBER,
DNAME VARCHAR2(12) CONSTRAINT DEPT2_DNAME_NN NOT NULL,
LOC VARCHAR2(12)
);
<!--Table level로 제약조건 설정하기-->
CREATE TABLE 테이블명(
컬럼명 데이터타입
CONSTRAINT 제약조건명 제약조건(컬럼명)
);
<!--예시-->
CREATE TABLE DEPT2(
DEPTNO NUMER,
LOC VARCHAR(20),
CONSTRAINT DEPT2_DEPTNO_PK PRIMARY KEY(DEPTNO),
CONSTRAINT DEPT2_LOC_CK CHECK(LOC IN('SEOUL','BUSAN'))
);| 제약 조건 설정하는 법 | 설명 |
| Column Level (칼럼 레벨) |
- TABLE 생성시 각각의 컬럼을 정의하면서 같이 제약조건을 지정하는 방법 - 한개의 칼럼에 한 개의 제약조건만 정의 가능. 모든 제약 조건 사용 가능 |
|
Table Level |
- 모든 칼럼을 정의 하고 맨 마지막에 재약조건을 추가하는 방법 - 칼럼 정의와 분리하여 정의 - 한개이상의 칼럼에 한개의 제약조건을 정의 할 수 있음 - 하나의 칼럼에 여러개의 제약조건을 부여할 경우에도 사용된다.
|
데이터 사전 조회(제약조건 조회)
DESC USER_CONSTRAINTS
DESC USER_CONS_COLUMNS
제약조건 이름 짓기
제약조건 타입
- UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK 제양조건은 기본적으로 컬럼에 없던 새로운 제약조건을 추가하는 것이기 때문에 컬럼레벨 방식이나 테이블 레벨 방식 모두 사용가능하다
- 기본적으로 모든 컬럼은 NULL 값을 허용한다. NOT NULL
<!--컬럼레벨로 지정-->
CREATE TABLE 테이블명(컬럼명 데이터타입 CONSTRAINT 제약조건명 제약조건, ...);
<!--테이블레벨로 지정-->
CREATE TABLE 테이블명(
컬럼명 데이터타입
CONSTRAINT 제약조건명 제약조건(컬럼명)
);
| CONSTRAINTS TYPE | 설명 |
| NOT NULL |
- 해당 컬럼 값으로 NULL을 허용하지 않는다. - 컬럼 레벨 방식만 지원한다. |
| UNIQUE |
- 테이블 내애서 해당 칼럼 값은 항상 유일한 값을 갖는다 - 칼럼 레벨/테이블 레벨 방식 모두 지원한다 - 하나의 테이블에 UNIQUE 제약조건을 여러개 지정할 수 있고, NULL값도 저장할 수 있다. |
| PRIMARY KEY |
- 값이 유일(UNIQUE)해야되고, NULL 값은 포함할 수 없음을 보증하는 제약 조건 - 컬럼 레벨 / 테이블 레벨 방식을 모두 지원한다. |
| FOREIGN KEY |
- 해당 컬럼의 값이 다른 테이블의 컬럼 값을 참조한다. - 참조되는 컬럼에 없는 값은 저장 불가 - 컬럼 레벨 / 테이블 렐벨 방식 모두지원 |
| CHECK |
- 해당 컬럼에 저장되는 데이터를 검사해서 조건과 일치하는 데이터만 저장이 가능하도록 처리하는 제약조건 |
TABLE 객체 삭제
DROP TABLE 테이블 [CASCADE CONSTRAINTS];
//CASCADE CONSTRAINTS는 생략 가능- 테이블에 저장된 모든 데이터, 관련된 INDEX, 제약조건(FOREIGN KEY 제외)이 같이 삭제된다.
- FOREIGN KEY자동으로 삭제되지 않음.
따라서 자식테이블이 부모테이블을 참조하고 있는 상황에서 부모테이블을 삭제하면 에러가 발생됨.
이때 CASCADE CONSTRAINTS 옵션을 지정한다.
FLASHBACK DROP 명령어
안배움
TABLE 객체 변경
컬럼 추가 / 변경 / 삭제
컬럼 추가
ALTER TABLE 테이블명
ADD(컬럼명 테이터 타입,...);
컬럼 변경
- 모든 ROW의 COLUMN이 NULL이거나 ROW가 없는 경우에만
컬럼길이 축소와 데이터 타입 변경이 가능하다. - DEFAULT 값을 변경하는 경우에는 변경 이후부터 입력되는 행에 대해서만 적용 가능하다.
ALTER TABLE 테이블명
MODIFY(컬럼명 테이터 타입,...);
컬럼 삭제
- 컬럼은 값의 존재여부와 상관없이 무조건 삭제된다.
- 한번에 여러개의 컬럼들을 동시에 삭제할 수도 있지만 반드시 최소한 하나의 컬럼은 존재해야한다.
ALTER TABLE 컬럼명
DROP COLUMN 컬럼명,....;
컬럼 이름 바꾸기
ALTER TABLE 테이블명
RENAME COLUMN 컬럼명 TO 변경할컬럼명;
제약조건 추가 / 조건 삭제 / 활성화 / 비활성화
제약조건 추가
- 기본적으로 모든 컬럼은 NULL 값을 허용하는데, 이런 기본 동작을 허용하지 않는 것이 NOTNULL 제약조건이다.
결국 NOT NULL 제약조건은 NULL값을 허용하는 기본적인 동작을 허용하지 않도록 추가하는 것이 아니고, 수정하는 것이다.
<!--UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK-->
ALTER TABLE 테이블명
ADD CONSTRAINT 제약조건명 제약조건(컬럼명);
<!--NOT NULL-->
ALTER TABLE 테이블명
MODIFY(컬럼명 테이터타입 CONSTRAINT 제약조건명 NOT NULL);
제약 조건 삭제
<!--삭제하는 방법(공통)-->
ALTER TABLE 테이블명
DROP CONSTRAINT 제약조건명 [CASADE];
<!--PRIMARY KEY 삭제하기-->
ALTER TABLE 테이블명
DROP PRIMARY KEY;
<!--UNIQUE 삭제하기-->
ALTER TABLE 테이블명
DROP UNIQUE(컬럼명);
제약 조건 활성화 / 비활성화
<!--제약조건 비활성화:CASCADE | 활성화:ENABLE-->
ALTER TABLE 테이블명
DISABLE|ENABLE CONSTRAINT 제약조건명 [CASCADE];
ROWNUM / ROWID COLUMN
- 오라클에서 테이블을 생성하면 기본적으로 제공되는 칼럼
<!--예시-->
SQL> SELECT EMPNO, ENAME, ROWNUM, ROWID
2 FROM EMP;
EMPNO ENAME ROWNUM ROWID
---------- -------------------- ---------- ------------------
7369 SMITH 1 AAADVZAAEAAAADlAAA
7499 ALLEN 2 AAADVZAAEAAAADlAAB
7521 WARD 3 AAADVZAAEAAAADlAAC
7566 JONES 4 AAADVZAAEAAAADlAAD
7654 MARTIN 5 AAADVZAAEAAAADlAAE
7698 BLAKE 6 AAADVZAAEAAAADlAAF
7782 CLARK 7 AAADVZAAEAAAADlAAG
7839 KING 8 AAADVZAAEAAAADlAAH
7844 TURNER 9 AAADVZAAEAAAADlAAI
7900 JAMES 10 AAADVZAAEAAAADlAAJ
7902 FORD 11 AAADVZAAEAAAADlAAK
EMPNO ENAME ROWNUM ROWID
---------- -------------------- ---------- ------------------
7934 MILLER 12 AAADVZAAEAAAADlAALROWNUM
- ROW 고유의 아이디 (ROW 를 수정해도 변하지 않음
- SELECT하는 시점에 ROWNUM이 생김
- ROWNUM은 반드시 별칭을 붙여줘야함.
이유 : 예약어 이므로 별칭을 붙이지 않으면 어디에 있는 ROWNUM인지 알기 어렵다. - ROWUM 사용 목적
(1) ROW의 개수를 알고 싶을때
(2) 페이징 처리를 위해 사용한다.
- 글을 작성한 날짜 순서대로 나열하고, 나열한 날짜 대로 ROWNUM을 붙여
PAGE마다 몇번부터 몇번까지 ROWNUM을 불러올지.
문제
<!--ROWNUM은 SELECT하는 시점에 생성됨
FROM절 -> SELECT문 -> ORDER BY 절
SELECT 후 정렬 하기 때문에 ROWNUM의 순서가 섞인다.-->
SELECT EMPNO, ENAME, ROWNUM, ROWID
FROM EMP
ORDER BY ENAME ASC;
EMPNO ENAME ROWNUM ROWID
---------- -------------------- ---------- ------------------
7499 ALLEN 2 AAADVZAAEAAAADlAAB
7698 BLAKE 6 AAADVZAAEAAAADlAAF
7782 CLARK 7 AAADVZAAEAAAADlAAG
7902 FORD 11 AAADVZAAEAAAADlAAK
7900 JAMES 10 AAADVZAAEAAAADlAAJ
7566 JONES 4 AAADVZAAEAAAADlAAD
7839 KING 8 AAADVZAAEAAAADlAAH
7654 MARTIN 5 AAADVZAAEAAAADlAAE
7934 MILLER 12 AAADVZAAEAAAADlAAL
7369 SMITH 1 AAADVZAAEAAAADlAAA
7844 TURNER 9 AAADVZAAEAAAADlAAI
EMPNO ENAME ROWNUM ROWID
---------- -------------------- ---------- ------------------
7521 WARD 3 AAADVZAAEAAAADlAAC
<!--문제1. 이름으로 오름 차순 정렬한 순서대로 ROWNUM을 매겨보세요-->
<!--이름을 오름차순으로 정렬 후 ROWNUM을 순차적으로 주려면 서브쿼리 사용하면 됨.
서브쿼리로 생성된 DATA도 하나의 테이블이다.-->
SELECT EMPNO, ENAME, ROWNUM
FROM (SELECT EMPNO, ENAME
FROM EMP
ORDER BY ENAME ASC);
EMPNO ENAME ROWNUM
---------- -------------------- ----------
7499 ALLEN 1
7698 BLAKE 2
7782 CLARK 3
7902 FORD 4
7900 JAMES 5
7566 JONES 6
7839 KING 7
7654 MARTIN 8
7934 MILLER 9
7369 SMITH 10
7844 TURNER 11
EMPNO ENAME ROWNUM
---------- -------------------- ----------
7521 WARD 12
<!--문제2. ROWNUM 중 6번~5번 사이의 EMPNO, ENAMEM ROWNUM을 갖고와보세요.-->
1 SELECT EMPNO, ENAME, RNUM
2 FROM (SELECT EMPNO, ENAME, ROWNUM AS RNUM
3 FROM (SELECT EMPNO, ENAME
4 FROM EMP
5 ORDER BY ENAME ASC)
6 )
7* WHERE RNUM BETWEEN 6 AND 10
SQL> /
EMPNO ENAME RNUM
---------- -------------------- ----------
7566 JONES 6
7839 KING 7
7654 MARTIN 8
7934 MILLER 9
7369 SMITH 10
ROWID
- ROW 고유의 아이디 (ROW 를 수정해도 변하지 않음)
ROW를 생성하는 시점에 생김
'ORACLE' 카테고리의 다른 글
| SQL함수 / 단일행 함수 (0) | 2019.11.15 |
|---|---|
| DML / TCL (0) | 2019.11.15 |
| Data Base (0) | 2019.11.14 |
| SELECT 문 / WHERE 절 (0) | 2019.11.14 |
| 명령프롬프트 명령어 (0) | 2019.11.14 |
