2024.09.04. 테이블정의어(DDL) - table, 제약조건

* 데이터정의어(DDL) - Table

- 데이터 정의어(DDL)란??  테이블 자체를 생성,수정,삭제함.
- object(table, view, index 등등)를 생성, 수정, 삭제하는 명령어
- Table과 관련된 DDL구문 종류 : create table, alter table, drop table, truncate table

 

 (1) 테이블 생성(create table)

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

 

- 데이터타입 종류

  1) 숫자  -  정수형 : int, bigint
                   실수형 : float, double
  2) 문자  - 고정형 : char(n) : 공간효율이 별로지만 성능은 좋다(몇자리 차지하는지 알아보지 않아도 됨)
               - 가변형 : varchar(n) : 공간효율은 좋지만 성능이 별로다. 근데 막 디게 안좋은건 아니라고 한다.
               - 성능에 포커스를 둔 회사라면 char, 공간효율을 중시하는 회사면 varchar 사용하면 되는 거임.

                  근데 varchar를 많이쓴다. 내가 느끼는 성능의 차이는 별로 없다고 함.
  3) 날짜  - 년/월/일 : date
               - 년/월/일/시/분/초 : datetime

- 컬럼명의 특수문자는 _, #, $ 정도만 들어간다.

 

* default

● default값이 포함된 테이블 생성 예제

create table dept
(	deptno int,
	dname varchar(14),
	loc varchar(13),
	create_date datetime default now());
-- now() 대신 실제 기본값 지정도 가능하다. '2020-01-01 01:00:00'

● default 값이 포함된 컬럼 insert 예제

-- 명시적으로 기본값을 넣음
insert into dept values (30, 'CCC', 'C100', default); 

-- 명시적으로 null을 넣으면 빈값이 들어간다.
insert into dept values (40, 'DDD', 'D100', null);
-- [ 결과 ]
-- deptno, dname, loc, create_date
-- '40',   'DDD', 'D100', NULL -- create_date에 null 이 출력된다.

-- 세번째 컬럼은 default 없는데 넣어봤을 때 null 들어감. 디폴트값이 없을때 기본은 null이다.
insert into dept values (50, 'EEE', default, default); 
-- [ 결과 ]
-- # deptno, dname, loc,  create_date
--   '50',   'EEE', NULL, '2024-08-29 11:48:38' --디폴트값이 없는 loc에 default를 넣으니 null이 출력됨

● default 값이 포함된 컬럼 update 예제

update dept 
set create_date = default
where deptno = 40;
-- 변경전
-- # deptno, dname, loc, create_date
-- '40',     'DDD', 'D100', NULL
-- 변경후 : update도 디폴트 값으로 수정이 가능하다.
-- '40', 'DDD', 'D100', '2024-08-29 11:50:22'

 

 

* 제약조건이란?

- 테이블에 부적합한 데이터가 삽입/수정/삭제되는 것을 막아줌.
- 제약조건 종류 : not null, unique, primary key, foreign key, check

 

[제약조건1] not null 
- null값이 삽입/수정되는 것을 막아줌.
- 필수 컬럼에 많이 사용됨.
- 제약조건 선언 문법 1) 컬럼 레벨 문법  2) 테이블 레벨 문법

- char : null 들어와도 자리차지함. varchar : 공간을 잡지않음. null 값이 많이 입력되는 컬럼은 varchar 사용권장함.

create table test1
( 	id int not null,
	name varchar(30) not null,
	jumin varchar(13) not null,
	job varchar(20),
	email varchar(20),
	phone varchar(20) not null,
	start_date date);

 

[제약조건2] unique
- 컬럼에 중복값이 삽입/수정되는 것을 막아줌.
- 고유한 값이 들어가야 하는 컬럼에 활용됨.
- 유니크엔 null은 여러번 삽입되는 것이 허용된다. null은 모르는 것이기 때문에 같은 값이라고 할수없기 떄문이다.

create table test2
(	id int not null unique,
	name varchar(30) not null,
	jumin varchar(13) not null unique,
	job varchar(20),
	email varchar(20) unique,
	phone varchar(20) not null unique,
	start_date date
);

- 왜 id는 프라이머리키로 표시가 되는가? 워크벤치에서 그냥 고른거임

Field	Type	Null	Key	Default	Extra
id	int	NO	PRI	NULL

 

 

[제약조건3] primary key
- 기본키 제약조건으로 not null + unique의 성격을 모두 가짐.
- null값 또는 중복값이 삽입/수정되는 것을 막아줌.
- 단, 테이블당 한번만 선언 가능함. 

●  컬럼레벨의 문법 사용하기

create table test3
(	id int primary key,
	name varchar(30) not null,
	jumin varchar(13) not null unique,
	job varchar(20),
	email varchar(20) unique,
	phone varchar(20) not null unique,
	start_date date
);

●  (==) 테이블레벨의 문법 사용해보기 / 컬럼생성과 제약조건 따로 선언한다.

create table test3
(	id int,
	name varchar(30) not null, -- 컬럼레벨의 문법(not null은 테이블레벨의 문법이 사용안됨. 무조건 컬럼레벨의 문법을 써야함.)
	jumin varchar(13) not null, -- 컬럼레벨의 문법
	job varchar(20),
	email varchar(20) unique, -- 컬럼레벨의 문법
	phone varchar(20) not null unique, -- 컬럼레벨의 문법
	start_date date,
	primary key(id), -- 테이블 레벨 문법
	unique(jumin) -- 테이블 레벨 문법
);

);

 

- mysql 에서는 fk만 테이블 레벨 문법을 애용하고, 나머지 제약조건들은 컬럼레벨의 문법을 많이 활용한다.
- pk : 단, 테이블당 한번만 선언 가능함.

 

[제약조건4] foreign key
- 외래키 제약조건으로 자기 자신 테이블이나 다른 테이블의 특정 컬럼(pk, uk)을 참조하는 제약조건
- fk 제약조건이 선언된 컬럼 : 자식 컬럼
- fk 제약조건이 참조하는 컬럼 : 부모 컬럼
- 자식 컬럼에는 부모 컬럼의 값 중 하나가 삽입/수정될 수 있다.
  (null 값은 허용함. 아직 결정안되었기 떄문에. 부모컬럼에 있는값만 허용하지만 null은 허용함.)

create table test4
(	t_num int primary key,
	t_id int,
	title varchar(20) not null,
	story varchar(100) not null,
	foreign key(t_id) references test3(id) -- test3 테이블이 있어야 작업이 가능하다.
);

 

[제약조건5] check
- 해당 컬럼이 만족해야 하는 조건문을 자유롭게 지정할 수 있는 제약조건
- (ex1) salary bigint check (salary > 0)
- (ex2) jumin char(13) check (length(jumin) = 13) -- 입력되는 값이 13자리가 안되면 입력이 안됨.
- (ex3) email varchar(30) check (email like '%@%') -- 메일주소에 @ 없으면 입력을 시키지 마라.
- (ex4) start_date date check (start_date >= '2022-05-01') -- 직원의 입사일은 회사 창립일(22.05.01)이후여야 한다.
- (ex5) 성별 varchar(10) check (성별 = '남' or 성별 ='여') (==) 성별 varchar(10) check (성별 in ('남','여'))

create table test5
(	id int(10) primary key,
	name varchar(30) not null,
	jumin varchar(13) not null unique check (length(jumin)=13),
	job varchar(20),
	email varchar(20) unique,
	phone varchar(20) not null unique,
	start_date date check (start_date >= '2005-01-01')
);

 

- check 제약조건은 desc에서 볼수가 없다.
   desc 명령어 역할 : 테이블 구조 조회(컬럼명, 데이터 타입, 컬럼사이즈, not null) + (pk, uk, fk, default 값) 제공됨 
- 상세 정보를 보려면 DB 사전을 조회해야 한다.

 

* DB 사전을 활용해서 테이블에 선언된 제약조건 정보 조회하기 

-- DB 사전으로 전환하기
show databases;
use information_schema; -- db에 내장된 database인데 사전역할을 한다고 함.
show tables;

-- table에 선언된 제약조건 정보조회
-- 'hr' DB(schema)에 포함된 테이블들의 제약조건 조회
select * from table_constraints
where table_schema = 'hr'; -- where 절이 없으면 무수히 많은 결과가 나옴. notnull 은 desc에서 나오니까 여기서는 안보임.

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

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

 

* table_constraints 의 문제 : check 제약조건의 경우 조건문이 뭔지를 알 수가 없음.
* check 제약 조건인 경우 조건문 조회 (check_constraints 테이블 활용)

select * 
from check_constraints
where constraint_schema = 'hr';

 

 

* 서브쿼리를 사용한 테이블 생성
- 서브쿼리 구문을 사용해서 테이블 생성 시 원본 테이블의 구조와 데이터를 그대로 가지는 복사본의 테이블이 생성됨.
- 단, 제약조건은 not null 제약조건만 복사됨.
- 테이블 백업 또는 테스트용 테이블 생성 시 활용됨.

- 서브쿼리 활용 테이블 생성 예제1

create table dept80
as select employee_id, last_name, salary * 12 as annsal, hire_date
from employees
where DEPARTMENT_ID = 80; -- 테이블 구조와 데이터 모두 복사해서 만듦.
-- not null 은 넘어왔는데 pk, check 조건은 안넘어옴.

desc dept80; -- dept80 구조
-- # Field, Type, Null, Key, Default, Extra
-- 'annsal', 'double(22,0)', 'YES', '', NULL, ''
-- 'employee_id', 'int', 'NO', '', NULL, ''
-- 'hire_date', 'date', 'NO', '', NULL, ''
-- 'last_name', 'varchar(25)', 'NO', '', '', ''

 

- 서브쿼리 활용 테이블 생성 예제2

create table emp_bk
as select *
from employees; -- employees 테이블 전체 구조와 데이터를 복사해 놓음(백업기능)

 

(2) 테이블 수정(alter table)

1) 컬럼추가: alter table 테이블명 add --- ;
2) 컬럼수정: alter table 테이블명 modify --- ;
3) 컬럼삭제: alter table 테이블명 drop --- ;
4) 컬럼명수정: alter table 테이블명 rename --- ;

 

 

1) 컬럼 추가
- 마지막 컬럼으로 추가되고 초기값은 null이 삽입됨.

-- 마지막 컬럼으로 추가되고 초기값은 0이 삽입됨.
alter table dept80
add salary int default 0;
-- 새롭게 추가된 컬럼(salary)의 데이터가 default 값인 0이 자동으로 입력된다.

- 첫번째 컬럼으로 추가하려면

alter table dept80
add emp_number int first; 
-- 이 컬럼에서 not null을 못넣는다. 이곳은 디폴트 선언을 안했는데 null을 못넣게하면 아예 생성을 못함.

- 특정 컬럼뒤에 추가하려면?

alter table dept80
add email varchar(30) default '미입력' not null after last_name; 
-- last_name 컬럼 뒤에 email컬럼을 추가해달라

 

 2) 컬럼 수정(데이터타입, 컬럼사이즈, default, not null)

- 컬럼타입변경: salary(int) -> salary(bigint)

alter table dept80 modify salary bigint;

- last_name : (varchar(20), not null -> (varchar(30)) 
: 사이즈를 늘리는건 무조건 되는데 줄이는 거는 그럴 수고 안될 수도 있다.

  예를 들어 줄이려는 길이가 10인데 컬럼속에 저장된 데이터가 10이상인 것이 있으면 수정불가.

- 컬럼타입변경: email : (varchar(20), default '미입력', not null) -> (varchar(20), default '미정', not null)

alter table dept80
modify email varchar(20) default '미정' not null; -- 안바뀌는 것도 조건이 있으면 다 적어줘야 한다.
-- 이미 들어가 있는 '미입력' 데이터는 바뀌는게 아니고 이제부터 들어가는 컬럼부터 '미정'으로 바뀌는 것이다.

 

- 제약조건 변경

alter table dept80
modify last_name varchar(30);
-- 원래 last_name은 not null 이었는데 지금은 아니도록 바꿨다. 

-- not null을 명시해야 없어지지 않고 그대로 살아있다. 명시하지않으면 조건이 없어짐.
-- employee_id: (변경 전) int, not null -> (변경 후) bigint, not null
alter table dept80
modify employee_id bigint not null;

-- email : 변경조건 - (varchar(30), default '미입력', not null)
alter table dept80
modify email varchar(20) default '미입력' not null;

 

< 컬럼 수정 modify 주의사항 >
- 데이터 타입도 모두가 바뀌는 것은 아니다. employee_id의 데이터타입은 int인데 date로 바꾼다면 역할자체를 못하니까 안되고, char 같은거는 된다. 반대로 email컬럼을 char -> int 이러면 컬럼 자체를 못사용함.

 

3) 컬럼 삭제

- annsal 컬럼삭제하기

alter table dept80
drop annsal;

 

4) 제약조건 추가 문법 2가지
[문법1] pk, uk, fk, ck 제약조건 추가
(1) pk 제약조건 추가

alter table dept80 
add primary key(employee_id); -- 테이블레벨의 문법과 같다.

(2) uk,ck,fk 제약조건 추가
- uk: 유니크 제약조건에 null 들어가는 것은 괜찮다. 어차피 모르는 값이기 때문에

- fk: 데이터 타입이 안맞아도 참조가 안된다.

-- uk

alter table dept80
add unique(job_id);

-- ck
alter table dept80
add check(salary >= 0);

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

 

[문법2] not null 제약조건 추가

alter table dept80
-- modify salary not null; 
-- Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'not null' at line 2 0.000 sec
-- 에러이유: 타입을 적지 않았기때문 
-- 데이터 타입이 안바뀌어도 꼭 적어줘야 한다.
modify salary bigint not null;

 

 5) 제약조건 삭제문법 3가지

 [문법1] pk 제약조건 삭제 -> 제약조건 유형으로 삭제 가능함. 테이블당 한번밖에 선언이 안되기떄문이다.(제약조건 명으로도 삭제가능함)

alter table dept80
drop primary key;

 

[문법2] uk, ck, fk 제약조건 삭제 -> 제약조건명으로 삭제 가능함.
- table 에 여러번 선언될 수 있는 제약조건이다. 그래서 제약조건 유형으로 삭제가 안되고 제약조건 명을 사용해서 삭제해야 한다.
- db 사전에서 제약조건명을 확인해서 삭제해야한다.

-- 1) DB 사전으로부터 제약조건명 확인
use information_schema; -- db 전환하기
select *
from table_constraints
where table_name = 'dept80'; -- fk제약조건명 : dept80_ibfk_1, dept80_ibfk_2
-- 2) 제약조건명으로 제약조건 삭제 가능함.
use hr; -- 다시 사용하는 db로 전환하기
alter table dept80
drop foreign key dept80_ibfk_2;

 

[문법3] not null 제약조건 삭제

alter table dept80
-- modify hire_date date null; -- null 명시
-- (==)
modify hire_date date; -- null 없으면 기본값으로 null 조건 들어감.

 

6) 컬럼명 수정 및 테이블명 수정

-- 컬럼명 수정
-- alter table 테이블명 rename column 기존컬럼명 to 새컬럼명;
alter table dept80
rename column hire_date to start_date;

-- 테이블명 수정.
ALTER TABLE hr.dept80
RENAME TO hr.dept88 ;

 

<<정리>>
- 테이블 수정(alter table)
- alter table 테이블명 add --- ; - 컬럼추가, 제약조건 추가(pk, fk, uk,ck)
- alter table 테이블명 modify --- ; - 컬럼수정, 제약조건 추가/삭제(not null)
- alter table 테이블명 drop --- ; - 컬럼삭제, 제약조건 삭제(pk, fk, uk,ck)
- alter table 테이블명 rename --- ; - 컬럼명 수정, 테이블명 수정.

 

(3) 테이블 삭제(drop table)

[문법] drop table 테이블명;
drop table dept88;

- DML은 commit 안하면 롤백 가능함.

- DDL은 롤백이 안된다. 백업이 안되면 drop하고나면 복구가 안되니 신중히 작업하라.
- DDL : autocommit을 내포하고 있는 명령어.

 

(4) 테이블 절단(truncate table)

- 테이블 구조는 남고, 데이터만 삭제. = 테이블을 싹 비우는 작업.  테이블 구조는 남고, 테이블의 모든 행이 삭제(절단)됨.

-- [문법] truncate table 테이블명; -- DDL 구문이기떄문에 autocommit이 바로됨.
truncate table copy_emp; 
-- (==) delete from copy_emp; -- commit전이라면 rollback이 가능함. 원래는 특정행을 삭제하는 명령어이다.