🥨 DDL(DATA DEFNITION LANGUAGE) 데이타 정의어
: 데이타베이스의 객체(테이블, 뷰, 시퀀스, 인덱스, 동의어, 사용자 등) 을 저장하기 위한 가장 기본적인 객체
📝 테이블(TABLE) : 데이타베이스에서 데이타(행)을 저장하기 위한 가장 기본적인 객체
CREATE TABLE 테이블명(컬럼명 자료형[ (크기) ][DEFAULT 기본값] [컬럼제약조건],
컬럼명 자료형[(크기)] [DEFAULT 기본값] [컬럼제약조건],...[, 테이블제약조건])✔ 식별자 작성 규칙 : 테이블명, 컬럼명, 별칭, 라벨명 등
- 영문자로 시작되며 1~30 범위의 문자들로 구성
- A~Z, 0~9,_ ,$, # 문자들을 조합하여 작성 - 대소문자 미구분 : 스네이크 표기법 사용 권장
- 영문자 외 다른 문자 사용가능 - 비권장
- 키워드로 식별자를 선언할 경우 에러 발생 - " "안에 표현 가능하지만 비권장
✔ 자료형 (DATATYPE) : 컬럼에 저장 가능한 값을 표현하기 위한 키워드
1. 숫자형
: NUMBER [ (전체자리수, 소숫점자릿수) ]
2. 문자형
: CHAR (크기) - 크기: 1~2000 (BYTE) >> 고정형 길이
: VARCHAR2 (크기) - 크기 : 1~4000 (BYTE) >> 가변길이 (최대)
: LONG (크기) - 크기 : 최대 2BYTE >> 가변길이 : 테이블에 하나의 컬럼에만 부여 가능하며 정렬 불가능
: CLOB : 최대 4BYTE >> 가변길이 : 인코딩 처리된 문자값이 저장된 텍스트 파일을 저장하기 위한 자료형
: BLOB : 최대 4BYTE >> 가변길이 : 원시값이 저장된 일반(이진) 파일을 저장하기 위한 자료형
3. 날짜형
: DATE - 날짜와 시간
: TIMESTAMP - 초(MS) 단위 시간
📝딕셔너리(DICTIONARY) : 시스템 정보를 제공하기 위한 가상의 테이블(뷰)
USER_ DICTIONARY(일반 사용자) , DBA_DICTIONARY (관리자) , ALL DICTIONARY (모든 사용자)
✔ USER_OBJECTS : 현재 접속 사용자의 객체 정보를 제공하는 딕셔너리
SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE='TABLE';✔ USER_TABLES : 현재 접속 사용자의 테이블 정보를 제공하는 딕셔너리
SELECT TABLE_NAME FROM USER_TABLES;>> USER_TABLES 딕셔너리 대신 동의어로 TABS 제공
SELECT TABLE_NAME FROM TABS;✔ USER_TAB_COLUMNS : 테이블의 컬럼 정보를 제공하는 딕셔너리
SELECT COLUMN_NAME, DATA_DEFAULT FROM USER_TAB_COLUMNS WHERE TABLE_NAME='TABLE NAME';
- 생략된 컬럼에는 기본값이 전달되어 삽입 처리
- DEFAULT 키워드를 사용하여 기본값을 제공받아 삽입 처리
✔ USER_CONSTRAINTS : 테이블에 설정된 제약조건을 제공하는 딕셔너리
✔ CONSTRAINT_NAME : 제약조건을 구분하기 위한 이름(식별자)
- 제약조건의 이름을 설정하지 않으면 SYS_XXXXXXX 형식으로 자동 설정
💡 테이블의 구조 확인
DESC TABLENAME;
❕❕ 테이블 생성시 제약조건을 설정하지 않은 경우 컬럼에 어떤 값을 전달하든 삽입 가능 - DATA 무결성 위반 가능
💡 기본값 설정하는 방법
CREATE TABLE MANAGER(NO NUMBER(4), NAME VARCHAR2(20), STARTDATE DATE DEFAULT SYSDATE , PAY NUMBER DEFAULT 1000);💡 테이블의 목록 및 구조 확인
SELECT TABLE_NAME FROM USER_TABLES;
DESC TABLENAME;
📝 제약조건(CONSTRAINT) : 컬럼에 비정상적인 값이 저장되는 것을 방지하기 위한 기능 >> 데이타 무결성 유지
1. 컬럼 수준의 제약조건 : 테이블의 속성 선언시 컬럼에 제약조건을 설정
2. 테이블 수준의 제약조건 : 테이블 선언시 테이블의 특정 컬럼에 제약조건을 설정
➰ CONSTRAINT_TYPE : 제약조건의 종류
C(CHECK) , P(PRIMARY KEY), R(REFERENCE), U(UNIQUE)
- SEARCH_CONDITION
📌 CHECK : 컬럼값에 대한 조건을 제공하여 조건에 맞는 값만 저장되도록 설정하는 제약조건
- 컬럼 수준의 제약조건 또는 테이블 수준 제약조건으로 설정 가능
✔ CHECK 제약조건을 설정하는 컬럼만으로 CHECK 제약조건의 조건식 작성
CREATE TABLE SAWON2 (NO NUMBER(4), NAME VARCHAR2(20), PAY NUMBER CHECK(PAY>=5000000)); ==> 컬럼수준의 제약조건 ✔ 테이블 수준의 제약조건은 모든 컬럼을 사용하여 CHECK 제약조건의 조건식 작성 가능
EX) CREATE TABLE SAWON4 (NO NUMBER(4), NAME VARCHAR2(20), PAY NUMBER, CONSTRAINT SAWON4_PAY_CHECK CHECK(PAY>=5000000));✔ 제약조건을 설정할 경우 제약조건 관리를 효율적으로 하기위해 제약조건의 이름을 명시하는 것을 권장
> 형식) 컬럼명 자료형 [ (크기) ] CONSTRAINT 제약조건명 제약조건;
EX> CREATE TABLE TRAINEE2 (TNO NUMBER(4) CONSTRAINT TRAINEE2_TNO_PK PRIMARY KEY, TNAME VARCHAR2(20), SCODE NUMBER(2), CONSTRAINT TRAINEE2_SCODE_FK FOREIGN KEY(SCODE) REFERENCES SUBJECT1(SNO)); 📌 NOT NULL : NULL을 허용하지 않는 제약조건 - 컬럼에 반드시 값이 저장되도록 설정하는 제약조건
- 컬럼 수준의 제약조건만 가능📌 UNIQUE : 중복된 컬럼값 저장을 방지하기 위한 제약조건
- 컬럼 수준의 제약조건 또는 테이블 수준의 제약조건으로 설정 가능
- 테이블의 여러 컬럼에 UNIQUE 제약조건 설정이 가능하며 NULL 허용
- UNIQUE 제약조건은 테이블 수준의 제약조건을 사용하여 컬럼을 그룹하여 중복 방지 처리 가능 ➰ PRIMARY KEY 제약조건
- 컬럼 수준의 제약조건 또는 테이블 수준의 제약조건 설정 가능
- 테이블에서 하나의 컬럼에만 설정 가능
- 테이블에서 행을 구분할 수 있는 고유값이 저장된 컬럼에 PRIMARY KEY 제약조건 설정
- PRIMARY KEY 제약조건은 테이블의 관계를 구체화하기 위해 반드시 설정해야 되는 제약조건
➰ FOREIGN KEY 제약조건
- 테이블 수준의 제약조건만 설정가능
- 부모테이블의 PRIMARY KEY 제약조건이 설정된 컬럼을 참조하여 자식 테이블의 컬럼에 FOREIGN KEY 제약조건 설정
✔ FK 설정할 경우 ON DELETE CASCADE 또는 ON DELETE SET NULL 기능 추가 가능
- ON DELETE CASCADE : 부모테이블의 행을 삭제할 경우 자식 테이블에 참조 컬럼값이 저장된 행도 같이 삭제하는 기능 제공
- ON DELETE SET NULL : 부모테이블의 행을 삭제할 경우 자식 테이블에 참조 컬럼값을 NULL로 변경하는 기능 제공
❕❕ INNER JOIN은 결합조건이 맞는 행만 결합하여 검색 - 결합조건이 맞지 않는 행 미검색
💡 제약조건확인
R_CONSTRAINT_NAME : 참조하는 부모 테이블의 컬럼에 설정된 PRIMARY KEY 제약조건의 이름
📚 서브쿼리를 사용하여 테이블 생성가능 - 기존 테이블을 이용하여 새로운 테이블 생성: 행복사
형식) CREATE TABLE 테이블명( [컬럼명, 컬럼명,...] ) AS SELECT 검색대상, 검색대상,... FROM 테이블명 [WHERE 조건식]
✔ 테이블 생성: 서브쿼리의 검색결과를 사용하여 테이블을 생성하고 검색된 행을 생성된 테이블에 삽입 처리
- 서브쿼리의 검색대상의 속성을 전달받아 타겟테이블을 생성하며 타겟테이블의 컬럼명은 변경 가능 - 검색대상의 자료형 및 크기 변경 불가능
- 서브쿼리에서 사용된 원본테이블의 제약조건은 새로운 타겟테이블에 미적용
EX) CREATE TABLE EMP2 AS SELECT * FROM EMP;- 서브쿼리의 검색행의 조건식을 무조건 거짓으로 설정하여 검색하면 원본테이블의 행은 복사하지 않도록 설정
EX) CREATE TABLE EMP6 AS SELECT * FROM EMP WHERE 1=0 ;📚 테이블삭제 : 테이블에 저장된 모든 행 삭제
형식) DROP TABLE 테이블명
- 오라클은 테이블을 삭제할 경우 테이블을 휴지통(RECYCLEBIN)으로 이동하여 삭제처리 > 삭제 테이블 복구 가능
- TNAME 컬럼에 BIN으로 시작되는 테이블은 오라클 휴지통에 존재하는 삭제 테이블
✔ 오라클 휴지통에 존재하는 객체 목록 확인
SHOW RECYCLEBIN;
오라클 휴지통에는 테이블뿐만 아니라 테이블과 관계있는 INDEX 객체도 같이 존재
✔ 오라클 휴지통에 존재하는 삭제 테이블 복구 : 테이블과 관계있는 INDEX 객체도 같이 복구 처리
FLASHBACK TABLE 테이블명 TO BEFORE DROP; ✔ 오라클 휴지통의 테이블 제거 : 테이블과 종속관계에 있는 INDEX 객체도 같이 삭제 처리
PURGE TABLE 테이블명;✔ 오라클 휴지통의 모든 테이블 제거
PURGE RECYCLEBIN; ✔ 오라클 휴지통을 사용하지 않고 삭제 처리
DROP 테이블 테이블명 PURGE; ✔ 테이블 초기화 : 테이블 생성 직후의 상태로 초기화 처리하는 명령 - 테이블에 저장된 모든 행 삭제
TRUNCATE TABLE 테이블명;
** ROLLBACK 처리 불가능📚 테이블 이름 변경
RENAME 기존테이블명 TO 변경테이블명 📚 테이블 속성변경
ALTER TABLE 테이블명 변경옵션
> 변경옵션에 의해 테이블 속성에 대한 추가, 삭제, 변경 및 제약조건에 대한 추가, 삭제 가능
> 컬럼 기본값 및 컬럼 수준의 제약조건 설정가능📚 테이블 속성 추가
ALTER TABLE 테이블명 ADD (컬럼명 자료형 [ (크기)] ][DEFAULT 기본값] [제약조건]) ;
> 컬럼 기본값 및 컬럼 수준의 제약조건 설정가능
> 테이블에 행이 저장되어 있어도 테이블 속성 추가 가능 - 기존 저장행의 추가된 속성에는 컬럼 기본값이 자동 저장 ✔ 테이블의 컬럼 자료형과 크기 변경
ALTER TABLE 테이블명 MODIFY(컬럼명 자료형[( 크기 )] [DEFAULT 기본값] [제약조건]) ;
> 테이블에 행이 저장되어 있으면 컬럼의 자료형 변경 불가능
> 테이블에 행이 저장되어 있으면 컬럼의 자료형 크기 변경 가능 // 저장된 컬럼값 보다 변경할 컬럼의 크기가 작은 경우 에러 발생
> 컬럼 기본값 및 컬럼 수준의 제약조건 설정 가능 ✔ 테이블의 컬럼명 변경
ALTER TABLE 테이블명 RENAME COLUMN 기존컬럼명 TO 변경컬럼명📚 테이블 속성 삭제 - 테이블 컬럼값도 같이 삭제
ALTER TABLE 테이블명 DROP COLUMN 컬럼명📚 제약조건 추가
-
컬럼 수준의 제약조건은 테이블 속성 추가 및 테이블 속성 변경시 설정 가능 (ADD & MODIFY)
-
테이블 수준의 제약조건은 ADD 옵션을 사용하여 설정 가능
ALTER TABLE 테이블명 ADD [CONSTRAINT 제약조건명] 제약조건
EX> ALTER TABLE USER1 ADD CONSTRAINT USER1_NO_PK PRIMARY KEY(NO);✔ 제약조건 삭제 - 테이블 속성 변경시 제약조건 삭제 가능
ALTER TABLE 테이블명 DROP {PRIMARY KEY | CONSTRAINT 제약조건명} [CASCADE] ✔ 제약조건의 비활성화 - 컬럼에 설정된 제약조건을 일시적으로 중지하는 기능
ALTER TABLE 테이블명 DISABLE {PRIMARY KEY | CONSTRAINT 제약조건명} [CASCADE] ✔ 제약조건의 활성화 - 비활성화 처리된 제약조건을 다시 동작되도록 설정하는 기능
ALTER TABLE 테이블명 ENABLE {PRIMARY KEY | CONSTRAINT 제약조건명}
> 활성화 처리될 제약조건에 제약조건을 위반하는 컬럼값이 저장되어 있는 경우 활성화 처리 불가능
> 제약조건을 위반하는 컬럼값을 변경하거나 컬럼값이 저장된 행을 삭제해야만 제약조건 활성화 가능
FK 제약조건에 의해 참조되는 부모테이블의 PK 제약조건은 비활성화 처리 불가능
--> CASCADE 키워드를 사용하여 부모테이블의 PK 제약조건을 비활성화 처리하면 자식테이블에 설정된 FK 제약조건도 자동으로 DROP 비활성화 처리 가능
