데이터 정의어 (DDL= Data Definition Language)

• 테이블의 구조 자체를 생성, 수정, 제거하도록 하는 명령문 집합

1. CREATE : DB 객체 생성
2. ALTER : 변경
3. DROP : 삭제
4. RENAME : 이름 변경
5. TRUNCATE : 데이터 및 저장 공간 삭제

1. create table

• 테이블 생성
• 생성하기 위해서는 테이블명을 정의하고,
  테이블을 구성하는 칼럼의 데이터 타입과 무결성 제약 조건을 정의해야 한다

create table 테이블명 (
	컬럼명 타입(크기),
    	컬럼명 타입(크기),
    	컬럼명 타입(크기));
        
        
ex) create table dept(
		dno number(2),
		dname varchar2(14),
		loc varchar(13));

< 테이블명 및 컬럼명 정의 규칙 >

• 문자(영어 대소문자)로 시작 , 30자 이내
• 문자(영어 대소문자) , 숫자 0~9 , 특수문자( _ $ # )만 사용 가능
• 대소문자 구별 없음 , 소문자로 저장하려면 ''로 묶어준다
• 동일 사용자의 다른 객체의 이름과 중복 안 됨
  ex) system이 만든 테이블명들은 다 달라야 함

2. 서브 쿼리문으로부터 테이블 복사 후 생성

• 서브 쿼리문으로 부서 테이블의 구조와 데이터 복사 -> 새로운 테이블 생성
• create table 테이블명 ( 컬럼명 o) : 지정한 컬럼 수와 데이터 타입이
  서브 쿼리문의 검색된 컬럼과 일치
  create table 테이블명 ( 컬럼명 x ) : 서브 쿼리의 컬럼 명이 그대로 복사
• 무결성 제약 조건 : not null 조건만 복사,
  기본키(=PK), 외래키(=FK)와 같은 무결성 제약 조건은 복사 x
• 디폴트 옵션에서 정의한 값은 복사 가능
• 서브 쿼리의 출력 결과가 테이블의 초기 데이터로 삽입 됨
• 서브 쿼리문 내의 산술식에 별칭 지정 필수

create table 테이블명 (컬럼명, 컬럼명2 ...)
as
서브쿼리;



ex)  

1. create table 테이블명(컬럼명 o)

- 서브 쿼리문으로 부서 테이블의 구조와 데이터 복사하기
create table dept1(dept_id) --컬럼수 1개 
AS
select dno --컬럼수 1개
from department;


2. create table 테이블명(컬럼명 명시 x)

- 산술식에 별칭 필수
create table dept2
AS
select eno, ename, salary*12 연봉 --별칭
from employee
where dno=20;

3. 기존 테이블의 구조만 복사하기 ( 데이터 x )

• 서브 쿼리의 where절에 항상 거짓이 되는 조건을 지정
  ex) where 0 = 1

ex) 부서 테이블의 구조만 복사하여 dept3 테이블 생성

create table dept3
AS
select *
from department
WHERE 0=1; --거짓 조건

🖐 테이블 구조 확인 명령어

DESC 테이블명;

이클립스에서는 실행되지 않음
(RUN SQL Command Line에서 실행 / conn system 1234 입력 👉 desc dept3 ;

2. alter table

• 테이블 구조 변경
• 컬럼 추가, 수정, 삭제

1) 컬럼 추가

• 새로운 칼럼 추가
• 추가된 컬럼은 테이블의 마지막 부분에 생성 -> 위치 지정 불가
• 새로 추가할 컬럼 값은 null

alter table 테이블명
add (컬럼명 타입);



ex) 

사원 테이블에 날짜 타입을 가지는 birth 컬럼 추가

alter table dept
add(birth date);

2) 컬럼 변경

• 컬럼의 타입, 크기, 기본 값 변경
• 기존 컬럼에 데이터가 없는 경우 : 타입, 크기 변경 자유
  기존 데이터가 존재하는 경우 : char, varchar2만 허용. 변경할 컬럼의 크기가 같거나 더 큰 경우에만 변경 가능

alter table 테이블명
modify 컬럼명 타입(크기);


ex) 


1. 사원 이름 컬럼 크기 변경하기

alter table dept
modify ename varchar2(30);


2. 사원 이름 컬럼 크기 변경 (30 -> 10)

alter table dept
modify ename varchar2(30);  => 오류 : 크기 작게 변경 불가


3. 사원 이름 컬럼 타입 변경 (varchar2 -> number)

alter table dept
MODIFY ename number(30);    => 오류 : 타입 변경은 char <-> varchar2만 가능


4. 컬럼 기본 값 지정

alter table dept
MODIFY ename varchar2(40) DEFAULT '기본';

3) 컬럼 제거

• 컬럼, 컬럼의 데이터 제거
• 삭제된 컬럼은 복구 불가

alter table 테이블명
drop column 컬럼명;


ex)

사원 테이블에서 사원 이름 컬럼 제거

alter table dept
drop column ename;

4) 컬럼 제거 - set unused

• 시스템의 요구가 적을 때 컬럼을 제거할 수 있도록 unused로 표시
• 실제로 테이블에서 해당 컬럼이 제거되지는 않음
• unused로 표시된 컬럼은 select절로 조회 불가
• descrebe문으로도 표시되지 않음
  ex) desc 테이블명; (테이블 구조 확인)

alter table 테이블명
set unused (컬럼명);



ex)


사원 테이블에서 사원번호 제거

alter table dept
set unused (eno);

🤔 사용하는 이유?

1. 사용자에게 보이지 않게 하기 위해
2. unused로 미사용 상태로 표시한 후 한번에 drop으로 제거하기 위해
   운영 중에 컬럼을 삭제하는 것은 시간이 오래 걸릴 수 있음

unused로 표시된 모든 컬럼을 한번에 제거

alter table dept2
DROP unused columns;    => s:복수

3. rename

• 테이블을 포함한 객체의 이름을 변경

rename 기존 테이블명 to 변경 테이블명;



ex) 테이블명 변경하기

rename dept2 to dept3;

4. drop table

• 기존 테이블과 데이터 제거
• 삭제할 테이블의 기본 키나 고유 키를 다른 테이블에서 참조하고 있는 경우 삭제 불가 -> 자식테이블 먼저 제거

drop table 테이블명;



ex) 테이블 제거하기

drop table dept

• 혹은 참조키 제약조건을 제거하는 방법 (참조하는 상황을 제거)

drop table 테이블명 cascade constraints; --s : 제약조건'들'

5. truncate table

• 테이블의 구조는 유지, 데이터와 할당된 공간 해제 (제거)

truncate table 테이블명;


ex)

테이블의 모든 데이터 제거하기

truncate table dept;   => but, 테이블의 구조는 남아있음 (컬럼명, 데이터 타입)

6. 데이터 사전

• 사용자와 DB 자원을 효율적으로 관리하기 위해 다양한 정보를 저장하는 시스템 테이블 집합
• 사용자가 테이블 생성, 사용자 변경 등의 작업을 할 때 DB 서버에 의해 자동 갱신되는 테이블
• 사용자 직접 수정, 삭제 불가 👉 읽기 전용 뷰

1) USER__데이터 사전

USER_ 로시작 ~ S(복수)로 끝남

• 사용자와 가장 밀접하게 관련된 뷰로 자신이 생성한 테이블, 뷰, 인덱스, 동의어 등의 객체나 해당 사용자에게 권한 정보 제공

ex) 

USER_tables로 사용자가 소유한 '테이블'에 대한 정보 조회

select *
from USER_tables;

2) ALL__데이터 사전

• 전체 사용자와 관련된 뷰, 사용자가 접근할 수 있는 모든 객체 정보 조회

• 객체 : 테이블, 시퀀스, 인덱스, 뷰 등
  owner : 조회 중인 객체가 누구의 소유인지 확인

ex) 

ALL_tables로 테이블에 대한 정보 조회

사용자  : system 일 때 - 500레코드 (레코드:한줄), SYS와 SYSTEM만 : 사용자(HR:교육용) 제외된 상태로 결과가 나옴
       :   hr 	일 때 - 78레코드 (사용자 hr과 다른 사용자를 포함한 결과 나옴)

select owner, table_name 
from ALL_tables; 
--where owner in ('SYSTEM') or table_name in ('EMPLOYEE', 'DEPARTMENT');
--where table_name in ('EMPLOYEE', 'DEPARTMENT');

2) DBA__데이터 사전

• 시스템 관리와 관련된 뷰, DBA나 시스템 권한을 가진 사용자만 접근 가능
• 사용자가 hr(교육용 계정)이라면 'DBA_데이터 사전'을 조회할 권한 없음
• DBA 권한 가진 system 계정으로 접속해야 테스트 가능

ex)

DBA_tables로 테이블에 대한 정보 조회

사용자  : system 일 때 - 500레코드 (레코드:한줄), SYS와 SYSTEM만 : 사용자(HR:교육용) 제외된 상태로 결과가 나옴
    		        ALL_tables로 조회한 정보와 같은 결과
       :   hr 	일 때 - 실패(table or view does not exist) : DBA 권한 없음

select owner, table_name 
from DBA_tables;