231222 DB - 데이터정의어 3

데이터정의어(DDL)

테이블 수정(alter table)

테이블 생성 후 컬럼 추가 시 기본적으로 마지막 컬럼으로 추가됨

초기 값은 null값이 삽입

SQL>
1) alter table 테이블명 add → 컬럼 추가, 제약 조건 추가(pk, uni, ck, fk)

2) alter table 테이블명 modify → 컬럼 수정, 제약조건 추가, 삭제(nn)

3) alter table 테이블명 drop → 컬럼 삭제, 제약조건 삭제(pk, uni, ck, fk)

4) alter table 테이블명 rename → 컬럼명 변경

 

컬럼 추가 : alter table 테이블명 add 컬럼명 제약 조건

 

예시)

dept80 테이블에 job_id 컬럼 추가

alter table dept80 add job_id varchar(10);

→ 마지막 컬럼으로 추가되면서 초기값 null

 

dept80 테이블에 email 컬럼 추가 + 기본값 '미입력'

alter table dept80 add email varchar(30) default '미입력';

→컬럼 추가 시 default값을 지정한 경우 초기값으로 default값이 삽입됨

 

컬럼 추가 시 특정 컬럼 뒤에 컬럼 추가(first, after)

alter table dept80 add emp_number int first; 

alter table dept80 add salary int default 300 not null after last_name;

 

그림 1. alter table 테이블명 add 예시 결과

 

컬럼 수정 : alter table 테이블명 modify 컬럼명 제약조건

데이터타입, 컬럼 사이즈, default값 변경, not null 제약조건

 

예시)

salary(int, not null, default 300) 수정
alter table dept80 modify salary bigint;
salary 수정 후 salary(int, null, default null)로 변경

 

last_name varchar(25) → varchar(30) not null
alter table dept80 modify last_name varchar(30) not null;

 

sarlary(bigint) → sarary (bigint default not null)
alter table dept80 modify salary bigint default 500 not null;

그림 2. alter table 테이블명 modify 예시 결과

 

컬럼 삭제 : alter table 테이블명 drop 컬럼

foreign key 제약조건이 참조하는 부모 컬럼인 경우에는 제약조건을 먼저 삭제해야함

 

예시)

alter table dept80 drop emp_number;

그림 3. alter table 테이블명 drop 예시 결과

 

제약조건 추가

primary key, check, unique, foreign key → alter table 테이블명 add;

 

예시)
not null → alter table 테이블명 modify;

alter table dept80 add primary key(employee_id);
alter table dept80 add unique(job_id);
alter table dept80 add check(salary>100);

※fk 제약조건 추가를 위해 컬럼 생성 alter table dept80 add mgr_id int default 150;

alter table dept80 add foreign key(mgr_id) references dept80(employee_id);

 

annsal(double(22,0), null) → annsal(double(22,0), not null)
alter table dept80 modify annsal double(22,0) not null;

그림 4. alter table 테이블명 add 제약조건 예시 결과

 

제약조건 삭제

not null → alter table 테이블명 modify

ex) annsal(double(22,0, not null) → annsal(double(22,0, null)

alter table dept80 modify annsal double(22,0);

 

primary key → alter table 테이블명 drop

제약조건 유형(primary key)으로 삭제가 가능함

alter table dept80 drop primary key;

그림 5. primary key 제약 조건 삭제

부모 컬럼에 선언된 pk를 삭제할 수 없기 때문이다. 즉, foreign key를 먼저 삭제해줘야 primary key를 삭제할 수 있다.

 

 

foreign key, unique, check → alter table 테이블명 drop

제약조건명으로 삭제가 가능

제약조건명 확인하기

use information_schema;

select * from table_constraints where table_name = 'dept80';

그림 6. 제약조건명 확인

dept80 테이블의 mgr_id 컬럼에 선언된 fk제약조건명 : dept80_ibfk_1
use hr;
alter table dept80 drop foreign key dept80_ibfk_1;

(이후 alter table dept80 drop primary key; 가능함. 즉, 작업 순서는 fk제거 → pk제거이다.)

 

컬럼명 변경 : alter table 테이블명 rename cloumn 기존컬럼명 to 바꿀컬럼명;

alter table dept80 rename column hire_date to start_date;

그림 7. 컬럼명 변경

 

DDL은 auto commit

 

테이블 삭제(drop table)

-데이터베이스로부터 테이블을 삭제함

-테이블 구조, 데이터 제약조건 등 모두 삭제

SQL> drop table 테이블명;

drop table dept80

그림 8. 테이블 삭제

 

테이블 절단(truncate table)

테이블로부터 모든 행을 삭제함

테이블로부터 구조만 남기고 모든 데이터를 삭제하는 작업

테이블을 비울 때 사용

SQL> truncate table 테이블명;

 

truncate table copy_emp;
desc copy_emp;
select * from copy_emp;

그림 9. 테이블 절단

 

cf) delete from 테이블명; 테이블의 데이터를 삭제하는 것은 같다.

하지만 truncate table은 DDL이므로 rollback이 불가능, delete from은 DML이므로 rollback이 가능하다.

---

연습문제 1
use shopdb;

create table title
(title_id int primary key,
title varchar(60) not null,
description varchar(400) not null,
rating varchar(4) check(rating in('G','PG','R','NC17','NR')),
category varchar(20) check(category in ('DRAMA', 'COMEDY', 'ACTION', 'CHILD', 'SCIFI', 'DOCUMENTARY')),
relase_date date);

desc title;

연습문제 2
create table title_copy
(copy_id int,
title_id int,
status varchar(15) not null check(status in('available','destroyed','rented','reserved')),
primary key(copy_id, title_id),
foreign key(title_id) references title(title_id));

 

데이터정의어(DLL) - View

view

하나 이상의 테이블을 기반으로 생성은 되었지만 물리적으로 존재하지 않고 DB에 정의만 되어 있는 가상의 논리적인 테이블

하나의 테이블로부터 view는 다수로 만들 수 있음.

ex) employees 테이블에서

급여 view(emp_id, last_name, salary, comm_pct),

연락처 view(emp_id, last_name, email, phone)

 

장점 : 보안성, 공간효율성, 다양성, 편의성

 

view 생성(create view)

SQL> create view 뷰이름
           as select 컬럼명1, 컬럼명2,....
           from 테이블명
           [where 조건]

as 이후 view의 베이스가 될 부분을 작성