DDL

yuju·2022년 10월 26일
0

SQL

목록 보기
8/11
post-thumbnail

🥨 DDL(DATA DEFNITION LANGUAGE) 데이타 정의어

: 데이타베이스의 객체(테이블, 뷰, 시퀀스, 인덱스, 동의어, 사용자 등) 을 저장하기 위한 가장 기본적인 객체

📝 테이블(TABLE) : 데이타베이스에서 데이타(행)을 저장하기 위한 가장 기본적인 객체

CREATE TABLE 테이블명(컬럼명 자료형[ (크기) ][DEFAULT 기본값] [컬럼제약조건], 
	컬럼명 자료형[(크기)] [DEFAULT 기본값] [컬럼제약조건],...[, 테이블제약조건])

✔ 식별자 작성 규칙 : 테이블명, 컬럼명, 별칭, 라벨명 등

  1. 영문자로 시작되며 1~30 범위의 문자들로 구성
  2. A~Z, 0~9,_ ,$, # 문자들을 조합하여 작성 - 대소문자 미구분 : 스네이크 표기법 사용 권장
  3. 영문자 외 다른 문자 사용가능 - 비권장
  4. 키워드로 식별자를 선언할 경우 에러 발생 - " "안에 표현 가능하지만 비권장

✔ 자료형 (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 기능 추가 가능

  1. ON DELETE CASCADE : 부모테이블의 행을 삭제할 경우 자식 테이블에 참조 컬럼값이 저장된 행도 같이 삭제하는 기능 제공
  2. 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 비활성화 처리 가능

0개의 댓글