Database/SQL

[SQL] DDL - CREATE, ALTER, DROP

Untitled_Blue 2023. 5. 29. 19:25
반응형

안녕하세요. 이번 글은 DDL에 대한 설명입니다.

- DDL이란?

DDL은 Data Definition Language 의 약자로서 데이터베이스 데이터를 관리하기 위한 제공되는 객체의 생성,관리,삭제,변경 관련 기능을 제공하는 언어를 의미한다. 이때 데이터베이스에서의 객체는 테이블을 의미한다. DDL의 언어로는 테이블을 생성하는 Create, 테이블을 수정하는 Alter, 테이블을 삭제하는 Delete가 있다.

- 테이블을 생성하는 Create

create table 테이블명 (
   칼럼명 자료형,
   칼럼명2 자료형,
   ...
);

Create는 테이블 또는 뷰를 생성하기 위한 언어로서 사용할 때는 테이블명을 지정해줘야 되며 내부에는 저장될 칼럼명과 자료형을 반드시 최소 한 개 이상 지정해야 한다.

create table PRACTICE01 (
    STU_NUMBER varchar2(100),
    STU_NAME varchar2(100),
    STU_DEPT varchar2(200),
    STU_START_DATE date
);

해당 코드를 입력하고 실행하면 'Table PRACTICE01이(가) 생성되었습니다.'라는 알림을 확인할 수 있을 것이다.

그리고 localhost를 접속 해제했다가 다시 비밀번호를 입력해서 재접속해서 테이블을 확인해보면 정상적으로 테이블이 생성된 점을 확인할 수 있다.

create table PRACTICE02 as select * from EMP;

또한 기존에 존재하는 테이블의 구조뿐만 아니라 내부에 있는 데이터까지 가져오면서 새 테이블을 생성하는 방법도 있다. 상단 코드처럼 테이블명 뒤에 as를 사용하고 select문을 사용해서 가져오고자 하는 테이블의 정보를 조회하는 방식을 통해 복사할 수 있다. 이때 뒤에 명령문을 입력하는데 명령문 안에 명령문을 작성하는 방식을 서브쿼리라고 한다.

select * from PRACTICE02;

다음과 같이 테이블이 정상적으로 생성됐음을 확인한 후 생성한 테이블의 데이터를 조회하면 다음과 같이 기존 EMP 테이블에 있던 구조와 데이터가 모두 정상적으로 복사됨과 동시에 생성됨을 확인할 수 있다. 필요한 정보만 가져오고 싶다면 select문에 where 조건문을 활용하는 방법과 select 뒤에 칼럼명을 원하는 것만 입력하는 방식으로 원하는 정보 및 구조만 가져올 수도 있다.

create table PRACTICE03 as select * from EMP where 0 = 1;

다음과 같이 단순히 테이블의 구조만 가져오고 싶을 때도 있을 것이다. 이때는 서브쿼리 뒤에 where 문을 사용하면서 0 = 1을 입력하는 방식을 통해 데이터는 가져오지 않으면서 구조만 복사해서 새 테이블을 생성하는 방법을 사용할 수 있다.

select * from PRACTICE03;
desc PRACTICE03;

다음과 같이 해당 테이블을 조회해보면 어떠한 데이터도 조회되지 않으면서 테이블 구조는 정상적으로 출력되는 점을 알 수 있다. 이는 where 0 = 1이라는 조건문을 사용해서 데이터는 복사되지 않고 구조만 가져오도록 했기 때문이다.

- 테이블의 구조를 변경하는 ALTER

ALTER는 테이블의 구조 및 제약조건 등 정보를 변경하는 명령어이다. 이때 변경에는 수정, 추가, 삭제가 있다.

1) 테이블 내 칼럼을 추가하는 ADD

alter table PRACTICE03
add PHONE_NUM varchar2(300);

다음 코드는 PRACTICE03 테이블에 PHONE_NUM이라는 varchar2(300) 형식의 칼럼을 추가하라는 뜻을 가지고 있다. 이를 실행하면 Table PRACTICE03이(가) 변경되었습니다. 라는 알림이 출력되는 것을 볼 수 있다.

또한 다시 테이블의 구조를 확인(DESC)해보면 추가한 칼럼이 정상적으로 반영되어있는 점을 확인할 수 있다.

2) 테이블 내 칼럼명을 변경하는 RENAME

alter table PRACTICE03
rename column SAL to SALARY;

다음 코드는 테이블 내 SAL이라는 칼럼명을 SALARY라고 변경하라는 뜻의 소스코드이다. 실행 후 구조를 확인해보자.

이름 변경 전 (좌측), 이름 변경 후 (우측)

다음과 같이 DESC 테이블명을 입력 후 실행하면 바꾸고자 했던 칼럼명이 정상적으로 변경되었음을 확인할 수 있다.

3) 칼럼의 자료형을 변경하는 MODIFY

alter table PRACTICE03
modify DEPTNO VARCHAR2(50);

다음 코드는 테이블 내 DEPTNO의 자료형을 VARCHAR2(50)으로 변경하겠다는 뜻을 가지고 있다. 입력 후 실행하면 Table PRACTICE03이(가) 변경되었습니다. 라는 알림이 나오는 것을 볼 수 있으며 바로 구조를 확인해보자.

테이블의 구조를 확인해본 결과 기존 DEPTNO의 자료형이 NUMBER(2)이었던 것이 VARCHAR2(50)으로 새롭게 변경된 점을 확인할 수 있다.

4) 테이블 칼럼을 삭제하는 DROP

alter table PRACTICE03
drop column MGR;

다음 코드는 테이블 내 MGR이라는 열을 삭제하라는 명령어이다. 이때 칼럼을 삭제하면 해당 열에 있는 데이터도 함께 삭제되기 때문에 신중하게 사용해야 한다. 이제 테이블의 구조를 확인해보자.

다음과 같이 테이블의 구조를 조회한 결과 이전까지만 해도 존재했던 MGR이 정상적으로 삭제되었음을 확인 가능하다.

- 테이블의 이름을 변경하는 RENAME

먼저 변경하고자 하는 테이블의 이름을 먼저 확인해보자.

select * from PRACTICE02;

rename PRACTICE02 to PRACT_RENEWAL_02;

다음과 같이 RENAME [기존 테이블명] to [새로 변경할 테이블명]을 사용해서 테이블명을 변경할 수 있다.

select * from PRACTICE02;
select * from PRACT_RENEWAL_02;

이젠 제대로 변경되었는지를 확인하기 위해 기존 테이블명을 사용해서 조회 작업 (첫번째 코드 사용) 해보면 ORA-00942: 테이블 또는 뷰가 존재하지 않습니다 ("table or view does not exist") 라는 오류가 발생하는 점을 볼 수 있을 것이다.

이는 기존 테이블명이 새로운 이름으로 변경되었기 때문에 조회할 수 없다는 뜻이다.

이제 새로운 이름의 테이블을 조회하는 명령어를 실행해보면 다음과 같이 기존 테이블명에 있던 데이터들이 모두 정상적으로 출력되고 있음을 확인할 수 있다.

- 테이블의 데이터를 삭제하는 TRUNCATE, 테이블 자체를 제거하는 DROP

먼저 둘의 차이점을 먼저 말하자면 DROP은 테이블의 존재 자체를 삭제하는 명령어이다. 이는 곧 내부의 데이터뿐만 아니라 테이블명의 구조조차 조회할 수 없도록 완전히 삭제하는 명령어라는 뜻이다. 반면 TRUNCATE는 내부의 데이터만 모두 삭제될 뿐 테이블의 구조는 그대로 존재하는 명령어이다. 껍데기는 남아있다는 뜻으로 볼 수도 있다.

TRUNCATE TABLE PRACT_RENEWAL_02;

다음 코드는 테이블 내의 모든 데이터를 삭제하겠다는 뜻이다. 실행하면 Table PRACT_RENEWAL_02이(가) 잘렸습니다. 라는 알림을 확인할 수 있을 것이다. 이제 테이블 내 데이터를 조회해보자.

select * from PRACT_RENEWAL_02;

다음과 같이 테이블의 구조를 제외한 모든 데이터가 다 삭제되었음을 확인할 수 있다. 이때 Truncate는 delete와 다르게 데이터 정의어이기 때문에 rollback을 통한 복구 작업이 불가능한 점을 명심해야 한다.

drop table PRACT_RENEWAL_02;

다음과 같이 drop table을 통해 테이블을 삭제하는 작업을 진행 가능한 점을 확인할 수 있으며 실행하면 Table PRACT_RENEWAL_02이(가) 삭제되었습니다. 라는 알림을 볼 수 있을 것이다. SELECT문을 통해 지웠던 테이블명을 조회해보면 ORA-00942: 테이블 또는 뷰가 존재하지 않습니다 라는 알림이 나온다면 정상적으로 삭제된 것이다.

select * from tab;

삭제 후 tab 테이블을 조회해보면 BIN으로 시작하는 매우 긴 값의 이름을 볼 수 있을 것인데 이는 테이블이 존재했던 자리에 남아있는 찌꺼기(=쓰레기값)을 의미한다. 사실 조회가 안되서 별 의미는 없지만 앞으로 테이블의 목록을 계속 확인해야 될텐데 그대로 남겨둔다면 그것은 남겨두는대로 좋지 않을 것이다.

PURGE RECYCLEBIN;

해당 명령어를 통해 테이블을 삭제한 후에 남아있는 찌꺼기값을 모두 제거할 수 있으며 다시 tab을 조회해보면 정상적으로 삭제되었음을 확인할 수 있다.

 

다음 글은 DML 언어에 대한 설명입니다. 감사합니다.

반응형