[MySQL] DDL(create, alter, drop, truncate)

DDL

DDL(데이터정의어) : 객체(테이블, 뷰, 인덱스 등)를 생성, 수정, 삭제하는 명령어
- create, alter, drop, truncate
- 작업 단위 : 객체(테이블, 뷰, 인덱스 등)
- autocommit을 내포하고 있음.

 

 

* 테이블 table

테이블 생성 시 기본 정보 : 테이블명, 컬럼명, 데이터타입

옵션 : default값, 제약조건

데이터타입

• 숫자 - int, bigint, double, float
• 문자 - char(고정길이 문자형), varchar(가변길이 문자형)
• 날짜 - date(연/월/일), datetime(연/월/일/시/분/초) 

 

* View

하나 이상의 테이블을 기반으로 생성되었으나 물리적으로 존재하지않고 DB에 정의만 되어있는 논리적 가상 테이블
- 보안성, 편의성, 공간적 효율성의 목적으로 사용

- 계산과 조작 용도의 insert, update는 불가능하다.

- 뷰 생성 create view

create view 뷰명
as select 컬럼1, 컬럼2, 컬럼3 (서브쿼리 구문 형태) from 테이블
  [where 조건문];
  
create view empvu10
as select employee_id, last_name, salary, department_id
   from emp
   where department_id = 10;

 

 

1. 테이블 생성 create table

1) default 값이 포함된 테이블 생성

값을 입력하지 않을 시 default 값이 입력된다.

create table dept
(deptno int,
 dname varchar(10),
 create_date datetime default now( ));

 

2) 제약조건

• not null : 컬럼에 null값을 허용하지 않는다. 값이 반드시 있어야하는 필수 컬럼에 지정.
  varchar(가변길이 문자형) 사용 권장 - char일 경우 공간을 모두 차지하기 때문
• unique : 중복되지 않는 유일한 값을 입력해야 한다. null 값은 허용(여러 개 입력 가능)
  주로 중복되면 안되는 주민번호, 전화번호, 메일 등에서 활용

create table test
( id int not null unique, 
  name varchar(10) not null,
  jumin varchar(13) not null unique,
  email varchar(25) unique,
  job varchar(20) );

 

• primary key : 대표 컬럼(행을 고유하게 식별해줌)에 선언하는 기본 키. nn+uk
• foreign key : 두 테이블의 관계 선언(데이터의 무결성을 보장), 특정 컬럼을 참조.
  - pk, uk 선언된 컬럼만 참조 가능
  - fk 선언된 컬럼 : 자식 컬럼 / 참조하는 컬럼 : 부모 컬럼
  - 부모 컬럼에 있는 데이터 중 하나만 삽입, 수정 가능
  

create table test2
( t_num int primary key, 
  t_id int, title varchar(15) not null, 
  info varchar(100) not null, 		   -- 여기까지가 컬럼 레벨 정렬
  foreign key(t_id) references test(id) ); -- 테이블 레벨 정렬

 

• check : 해당 컬럼이 만족해야하는 조건을 지정

create table test3
( id int(10) primary key, 
  jumin varchar(13) not null unique check (length(jumin)=13),
  gender check (성별 in ('남', '여', '기타')),
  start_date date check (start_date >= '2020-01-01') );

 

테이블에 선언된 제약조건을 정보를 조회할 때

use information_schema;

-- test2 테이블에 선언된 제약조건 정보 조회
select * from table_constraints
where table_name = 'test2';

-- shop DB(스키마)에 존재하는 테이블에 선언된 모든 제약조건 정보 조회
select * from table_constraints
where constraint_schema = 'shop';

 

서브쿼리를 사용해 테이블 생성 시 구조와 데이터가 복사된 테이블이 생성된다고 했는데

제약조건은 not null만 복사된다.

2. 테이블 수정 alter table

데이터타입, 컬럼사이즈, default값 등 수정 가능하다.

 

1) 컬럼 추가

테이블 생성 후 컬럼을 추가하면 마지막 컬럼으로 추가된다.

null이 초기 값이나 default 값 지정 시 default 값이 삽입되어 있다.

alter table dept10
add  job_id varchar(5)
     mail varchar(20) default '미입력';

-- 가장 앞에 컬럼 추가
alter table dept10
      add emp_no int first

-- 특정 컬럼 뒤에 컬럼 추가
alter table dept10
      add salary int default 100 not null after name;

 

2) 컬럼 수정

-- 데이터 타입 수정
alter table dept10
      modify salary mint;

-- 컬럼 사이즈 수정
alter table dept10
      modify name varchar(20) not null;

-- default 값 수정
alter table dept10
      modify salary mint default 200 not null;
    
-- 컬럼명 수정
alter table dept10
      rename column hire_date to start_date;

 

3) 컬럼 삭제

부모 컬럼일 경우 제약조건을 먼저 삭제해야 한다!

alter table dept10
drop emp_no;

 

4) 제약조건 추가

-- pk 추가(uk와 동일)
alter table dept10
add primary key(employee_id);

-- nn 추가
alter table dept10
modify annsal double(22,0) not null; 

-- check 추가
alter table dept10
add check (salary > 100); 

-- fk 추가
alter table dept10
add mgr_id int default 100;

 

 

5) 테이블 삭제 drop table

테이블을 (구조, 데이터, 제약조건 등) 삭제한다.

drop table dept10;

 

제약조건 삭제

- pk 삭제 시 오류가 발생한다.

- 부모 컬럼에 선언된 pk, uk는 삭제할 수 없다(fk를 먼저 삭제해야함)

 

우선 제약조건 정보를 조회한다.

use information_schema;
select *
from table_constraints
where table_name = 'dept10';

-- fk 삭제 
alter table dept10
drop foreign key dept10_ibfk_1; 

-- check 삭제
alter table 테이블명
drop check check제약조건명;

-- uk 삭제
alter table 테이블명
drop index unique제약조건명;

-- nn 삭제
alter table dept10
modify annsal double(22,0) null;

 

6) 테이블 절단

테이블의 모든 행을 삭제한다.

truncate table dept10;

 

delete와 truncate 구분

delete	truncate
DML 데이터조작어	DDL 데이터정의어
commit, rollback 결정 가능	auto-commit 내포
테이블의 특정 행을 삭제	테이블의 모든 행 상제
delete from 테이블명; == truncate table 테이블명;