[SQL 26] 컬럼 추가/수정/삭제와 서브 쿼리를 이용한 테이블 생성

샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.

No. 01
컬럼 추가/수정/삭제

테이블에 대한 수정은 특별한 경우를 제외하고는 전혀 사용하지 않아도 된다. 테이블의 구조가 운영 중에 바뀐다는 것은 사실상 모델링에 문제가 있다는 의미이다. 이런 작업을 하지 않도록 하는 것이 가장 좋은 방법이다. 그러나 실제로 현장에서 종종 일어나는 일이라고 한다. 이런 명령어도 있다는 정도만 알아두자.

컬럼 추가/수정

ALTER TABLE 테이블

[ADD | MODIFY] 컬럼 데이터타입;

• ADD : 컬럼을 추가한다.
• 추가된 컬럼에는 NULL값이 저장된다.
• MODIFY : 컬럼을 수정한다.
• CHAR와 VARCHAR2는 컬럼의 크기를 변경하지 않으면 서로 수정 교환 가능
• 컬럼의 크기는 늘리는 것만 가능하다.
• 대상 컬럼에 데이터가 없을 때만 크기를 줄이거나 타입을 수정하는 것이 가능

◈ 예제

▶ 컬럼 추가

create table example1 (

depno number(2),

dname varchar2(14)

);

Table EXAMPLE1이(가) 생성되었습니다.

alter table example1

add loc varchar2(13);

Table EXAMPLE1이(가) 변경되었습니다.

alter table example1

add start_date date default sysdate;

Table EXAMPLE1이(가) 변경되었습니다.

desc example1

▶ 컬럼 수정

alter table example1

modify loc char(13);

Table EXAMPLE1이(가) 변경되었습니다.

alter table example1

modify depno number(5);

Table EXAMPLE1이(가) 변경되었습니다.

desc example1

컬럼 삭제

ALTER TABLE 테이블

DROP COLUMN 컬럼;

• 컬럼을 삭제한다.
• SYS 유저가 소유한 테이블의 컬럼은 DROP 할 수 없다.

컬럼에 데이터가 있어도 삭제가 되므로 삭제시 주의 해야 한다.

◈ 예제

alter table example1

drop column loc;

Table EXAMPLE1이(가) 변경되었습니다.

alter table example1

drop column dname;

Table EXAMPLE1이(가) 변경되었습니다.

desc example1

No. 02
서브 쿼리를 사용한 테이블 생성

서브 쿼리의 결과를 테이블로 저장하는 방법이다. 주로 테스트용 테이블이나 임시 테이블 또는 분석용 테이블을 생성하는데 이용된다.

기본 구조

CREATE TABLE 테이블[(컬럼, 컬럼, …)]

AS (SELECT 문장 : subquery 문);

• 서브 쿼리문의 결과를 테이블로 생성한다.
• 데이터 타입과 길이는 서브 쿼리 문에 의해서 결정된다.
• 컬럼의 리스트와 서브 쿼리의 컬럼은 반드시 1:1대응돼야 한다.
• 컬럼명을 기술하지 않으면 서브 쿼리의 검색 결과 출력되는 헤더를 컬럼명으로 사용
• 서브 쿼리에는 ORDER BY절을 사용할 수 없다.
• 기존 테이블의 제약 조건 중 NOT NULL은 새로운 테이블에 상속된다.
• 다른 제약 조건은 상속되지 않는다.

◈ 예제

create table dept80

as ( select employee_id, last_name, salary*12 ANNUAL, hire_date

from employees

where department_id = 80);

Table DEPT80이(가) 생성되었습니다.

select * from dept80;

서브 쿼리에 수식이나 함수 등이 사용되는 경우 이들을 새롭게 생성되는 테이블의 컬럼명으로 부적절함으로 생성되는 테이블의 컬럼 명을 직접 지정해줘야 한다.

'CREATE TABLE'절에 직접 정의 하거나 서브 쿼리문의 SELECT절에 별명을 지정하면 된다.

create table emp_dept(emp_id, ename, dept_id, dept_name)

as ( select employee_id, first_name, e.department_id, department_name

from employees e, departments d

where e.department_id = d.department_id);

Table EMP_DEPT이(가) 생성되었습니다.

select * from emp_dept;