MySQL - [데이터 정의어(DDL) - Create / Alter / Drop / Truncate ]

1. Create (테이블 생성)

테이블 생성 문법은 아래와 같다.

create table 테이블명
	( 컬럼명1 데이터타입(컬럼사이즈),
	 [컬럼명2 데이터타입(컬럼사이즈),
	  컬럼명3 데이터타입(컬럼사이즈)] );
 
# 컬럼명1 은 필수, 이후 컬럼명 2 ~~ 부터는 선택 사항이다.

 

혹은 기본값이나 제약조건을 줄 수도 있다. (NOT NULL은 무조건 이 방식으로 작성)

create table 테이블명
	( 컬럼명1 데이터타입(컬럼사이즈) default 기본값 (or) 제약조건,
	 [컬럼명2 데이터타입(컬럼사이즈),
	  컬럼명3 데이터타입(컬럼사이즈)] );

 

제약조건은 아래쪽에 따로 선언해줘도 된다. (FOREIGN KEY는 무조건 이 방식으로 작성)

create table 테이블명
	( 컬럼명1 데이터타입(컬럼사이즈),
	 [컬럼명2 데이터타입(컬럼사이즈),
	  컬럼명3 데이터타입(컬럼사이즈)]
	  제약조건(컬럼명),
	  FOREIGN KEY(컬럼명) REFERENCES 부모테이블명(부모컬럼명) );

 

PRIMARY KEY를 2개 (두 컬럼의 조합을 PRIMARY KEY로 사용할 때) 설정할 때는 아래와 같이 작성하면 된다.

create table TITLE_COPY (
	COPY_ID int,
	TITLE_ID int,
	primary key(copy_id, title_id),
);

 

shopdb에 products 테이블을 생성하고 테이블 구조 조회 결과 잘 나타남!

제약조건을 아래쪽에 선언해서 생성한 orders 테이블도 구조 조회 결과 잘 나타남!

 

---

테이블 생성 데이터 타입

데이터 타입	설명	설명
숫자 데이터 타입	SMALLINT	정수형 데이터 타입(2 byte) 32,768 ~ 32,767
	INT	정수형 데이터 타입(4 byte) 2,147,483,648 ~ 2,147,483,647 (최대 10자) 크기 지정 안해주면 11로 지정됨
	BIGINT	정수형 데이터 타입(8byte) 무제한 수 표현 가능
	FLOAT(p)	부동 소수형 데이터 타입(4 byte) 소수점 아래 7자리까지 표현 가능
문자 데이터 타입	CHAR(n)	고정 길이 문자형 n : 1~255 byte까지 지정 가능
	VARCHAR(n)	가변 길이 문자형 n : 1~65,535 byte까지 지정 가능
	LONGTEXT	대용량 문자열 데이터 타입
	LONGBLOB	대용량 바이너리 데이터 타입 (이미지, 음악 등)
날짜/시간 데이터 타입	DATE	날짜 데이터 타입 YYYY-MM-DD 형식으로 사용됨.
	DATETIME	날짜/시간 데이터 타입 YYYY-MM-DD HH:MI:SS 형식으로 사용됨.

char(10)와 varchar(10) -> 내부적으로 저장하는 방식이 다름.

 

- char : 데이터 바로 읽어옴. 무조건 10자리 잡음. -> 성능이 좋음. 데이터가 바로 들어옴

- varchar : 들어오는 문자열의 길이 확인하고. 해당 길이 만큼만 할당함. -> 공간적인 효율이 좋음. 데이터가 들어오는 데 시간이 걸림(길이를 확인해야 하므로)

 

* varchar 사용을 권장. 사람이 체감할 만한 성능 차이가 나지 않음!!

 

---

제약 조건

제약조건 유형	설명
NOT NULL	- 컬럼에 NULL값이 들어오는 것을 막기 위한 제약조건 - 사번, 이름, 주민번호, 전화번호 등의 필수 컬럼에 선언한다. ※ null값이란? 모르는 값, 정의되지 않은 값이라고 하며, 0와 공백과는 다른 특수한 값이다.
UNIQUE	- 중복되지 않는 유일한 값을 입력해야 한다는 제약조건 - 주민번호, 전화번호, 메일 등 고유한 값이 필요한 컬럼에 선언한다. - Unique 제약조건이 선언된 컬럼에 null값은 허용함.
PRIMARY KEY	- 기본키 제약조건으로 NOT NULL + UNIQUE의 성격을 모두 가지고 있는 제약조건 - PRIMARY KEY 제약조건은 테이블당 한번만 선언이 가능하다. - 사번, 학번, 회원번호, 제품번호 등의 컬럼에 사용된다.
FOREIGN KEY	- 외래키 제약조건으로 특정 테이블에서 다른 테이블을 참조하는 제약조건 - FOREIGN KEY 제약조건 때문에 테이블과 테이블이 연결된다. - FK가 걸려있는 쪽이 자식, 참조하는 쪽이 부모 - 참조할 키는 적어도 PRIMARY KEY, 혹은 UNIQUE 여야함!
CHECK	- 컬럼이 만족해야하는 조건문을 지정하는 제약조건 - ex) salary int check (salary > 0)        주민번호 varchar(13) check (length(주민번호) = 13)

- 제약조건은 여러개 줄 수 있음

- 전화번호는 문자열 데이터 타입으로 지정!!  -> 숫자 데이터로 01055553333 의 경우 앞에 0이 잘리고 1055553333으로 들어가기 때문

- primary key는 개인정보인 주민번호 전화번호 보다는 학번, ID, 사번 컬럼 등 을 권장.

 

- Foreign Key

      자식입장에서는 부모가 하나만 있어야 함 -> 참조할 키는 PRI, 혹은 UNIQUE 여야함!

 

 

---

AUTO_INCREMENT 속성

 

- 테이블 생성(또는 수정) 시 특정 컬럼에 auto_increment 속성을 부여할 경우 insert 시 자동으로 1부터 시작해서 1씩 증가하는 값을 반환해주는 속성

- 시작 값 또는 증가 값을 변경하고 싶은 경우에는 테이블 생성 후 테이블 수정 구문(alter table)으로 변경 가능

- primary key 또는 unique 제약조건이 지정된 컬럼만 auto_increment 속성을 활용할 수 있다.

- auto_increment 속성은 숫자 형식의 데이터 타입에만 활용 가능

- auto_increment 속성이 지정된 컬럼은 insert 작업 시 null값을 지정하면 자동으로 값이 입력

 

# 테이블 생성 시
create table 테이블명
	( 컬럼1 int auto_increment primary key,
	  컬럼2 데이터타입,
	  컬럼3 데이터타입);

# 입력값(시작값) 변경 (default = 1)
alter table 테이블명 auto_increment=입력값;

# 증가값(증가 사이즈) 변경 (default = 1)
set @@auto_increment_increment=증가값;

# 현재 몇 번까지 사용되었는지 확인하는 방법
select last_insert_id();

 

 

---

ex) 서브쿼리가 사용된 create 구문 
- employees 테이블과 구조는 동일하고 비어 있는 테이블 생성

- 서브쿼리를 사용해서 테이블 생성 시 원본 테이블의 구조 및 데이터 모두 복사된 테이블이 생성됨. 제약조건은 not null만 복사됨.

- 테이블 백업 또는 테스트용 테이블 생성 시 주로 활용함!

create table copy_emp
as select *
   from employees
   where 1=2;

 

ex) 테이블과 구조는 동일하지만 데이터는 없는 복사본 테이블 생성

create table copy_emp
as select *
   from employees
   where 1=2;

---

2. Alter (테이블 수정)

1) 컬럼 추가

- 기본적으로 마지막 컬럼으로 추가됨.

- 초기 값은 null값이 삽입되어 있음.

alter table 테이블명
add 컬럼명 데이터타입 [제약조건];

ex) default값 지정 시에는 초기 값으로 default값이 삽입되어 있음.

alter table 테이블명
add 컬럼명 데이터타입 default '기본값';

ex) 가장 앞에 또는 특정 컬럼 뒤에 컬럼 추가하는 방법

-- 가장 앞에 추가
alter table 테이블명
add 컬럼명 데이터타입 first;

-- 특정 컬럼 뒤에 추가
alter table 테이블명
add 컬럼명 데이터타입 [default 300 not null] after 컬럼명;

 

 

2) 컬럼 수정

- 데이터타입, 컬럼 사이즈, default값 변경가능

-- 데이터 타입 번경
alter table 테이블명
modify 컬럼명 변경할데이터타입(컬럼 사이즈);

-- 제약 조건도 다시 설정
alter table 테이블명
modify 컬럼명 변경할데이터타입 변경할제약조건;

-- default 값 변경
alter table 테이블명
modify 컬럼명 변경할데이터타입 default 기본값 [not null]; 
=> 저장된 default값이 변경되는게 아니라 변경 이후 데이터 삽입 시부터 적용됨.

- 컬럼명 변경

alter table 테이블명
change 원래컬럼명 변경할컬럼명 데이터타입;

 

 

3) 컬럼 삭제

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

alter table 테이블명
drop 컬럼명;

 

 

4) 제약조건 추가

- pk, fk, uk ,ch 는 drop / not null 은 modify

-- primary key, unique, check, foreign key 제약조건 추가 문법 동일함. 
alter table 테이블명
add primary key(컬럼명);

alter table 테이블명
add foreign key(컬럼명) references 부모테이블명(컬럼명);


-- not null 제약조건 추가 문법
alter table 테이블명
modify 컬럼명 데이터타입 not null;

 

 

5) 제약조건 삭제

- 제약조건 정보 조회

use information_schema;

select *
from table_constraints
where table_name = '테이블명';

제약조건 조회 결과

 

- 테이블 있는 DB로 변경 후 작업해야함!

- foreign key 제약조건이 선언된 컬럼이 참조하고 있는 부모 컬럼에 선언된 primary key 또는 unique 제약조건은 삭제할 수 없음.  삭제를 원하는 경우 foreign key 제약조건을 먼저 삭제해야함. 

- pk, fk, uk ,ch 는 drop / not null 은 modify

alter table 테이블명 
drop primary key;

-- foreign key 제약조건 삭제 문법
alter table 테이블명
drop foreign key dept80_ibfk_1(제약조건명-위 테이블 참조); 

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

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

-- not null 제약조건 삭제 문법
alter table 테이블명
modify annsal double(22,0) null;

 

 

 

---

3. DROP (테이블 삭제)

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

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

drop table 테이블명;

 

 

---

4. Trundate (테이블 절단)

- 테이블로부터 모든 행을 삭제함. 즉 구조는 그대로 두고 안에 데이터만 삭제

truncate table 테이블명; -- DDL: autocommit 내포

-- 아래 구문도 테이블의 모든 행을 삭제함
delete from 테이블명; -- DML: 작업 후 commit/rollback 필요

 

 

---