231220 DB - 데이터 정의어 2

데이터정의어(DDL)

Table

객체(테이블, 뷰, 인덱스 등)를 생성, 수정, 삭제하는 명령

 

테이블 관련 DDL 종류 : create table, alter table, drop table, truncate table(테이블 절단)

 

테이블 생성(create table)

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

()안에 테이블을 구성할 컬럼
테이블명, 컬럼명은 한글을 사용하지 않음.

 

데이터 타입 종류

1) 문자

char : 고정형 (생년월일, 전화번호 등에 사용)

varchar : 가변 (이메일, 주소 등에 사용)

공간 효율 : char < varchar, 성능(속도) : char > varchar

2) 숫자

int, bigint : 정수형

float, double : 실수형

3) 날짜

date : 년/월/일

datetime : 년/월/일/시/분/

 

실습1) dept 테이블 생성

create table dept
(deptno int,
dname varchar(14),
loc varchar(13),
create_date datetime default now());

※ 날짜나 숫자는 컬럼 사이즈를 지정할 필요없음. 문자는 지정해줘야함

그림 1. 실습 1 결과

 

데이터 삽입

insert into dept values(10, 'AAA','A100','2023-12-20');

insert into dept values(11, 'AAB','A101',default);

insert into dept(deptno, dname) values(20, 'BBB');

insert into dept values(30, 'CCC', null, null);

insert into dept values(40, 'DDD', default, default);

그림 2. 실습 1 데이터 삽입

update dept 
set create_date = default
where deptno = 30;

그림 3. 실습 1 데이터 수정

 

제약 조건

• 사용 목적 : 테이블의 부적합한 데이터가 들어오는 것을 방지하기 위해서 제약 조건을 사용함.
• 제약조건 종류 : primary key, foreign key, not null, unique, check

 

1) not null

• 컬럼에 null값을 허용하지 않는(삽입/수정 되는 것을 막아주는) 제약 조건
• 반드시 값이 있어야하는 필수 컬럼에 지정
• 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);

그림 4. not null 예시

 

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);

그림 5. unique 예시

 

3) primary key

• 테이블의 행을 고유하게 식별해 줄 수 있는 컬럼(대표 컬럼)에 선언하는 기본키 제약조건
• not null, unique의 성격을 모두 가지는 제약조건

 

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);

그림 6. primary key 예시

 

4) foreign key

• 두 테이블 사이의 관계를 선언함으로써 데이터의 무결성을 보장해주는 외래키 제약조건
• foreign key 제약조건 때문에 테이블과 테이블이 연계되어 운영될 수 있음
• 자기 자신 테이블이나 다른 테이블의 특정 컬럼(PK, unique)을 참조하는 제약조건
• 단, primary key 또는 unique 제약조건이 선언된 컬럼만 참조할 수 있음
• foreign key 제약조건이 선언된 컬럼 = 자식 컬럼
  foreign key 제약조건이 참조하는 컬럼 = 부모 컬럼
• 자식 컬럼에는 부모 컬럼에 있는 데이터 중 하나만 삽입, 수정될 수 있음

 

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));

그림 7. foreign key 예시

foreign key은 따로 아래에 작성해야함.
t_num int primary key, t_id int, title varchar(20) not null, story varchar(100) not null을 column level 문법으로 작성함
foreign key(t_id) references test3(id));을 table level 문법. 즉, foreign keys는 table level 문법으로 작성해야함

※desc의 경우 테이블에 선언된 제약조건을 전체적으로 알 수 없음.

 

5) check

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

 

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'));

그림 8. check 예시

 

6) 테이블에 선언된 제약조건 정보 조회

DB 사전(information_schema)을 사용

show databases;

그림 9. show databases

information_schema가 DB 사전의 역할을함

 

use information_schema;

show tables;

그림 10. show tables

 

select * from table_constraints where table_name = 'test5';

그림 11. test5 테이블에 선언된 제약조건 정보 조회

 

select * from table_constraints where constraint_schema = 'shopdb';

그림 12. shopdb 데이터베이스(스키마)에 존재하는 테이블에 선언된 제약조건 정보 조회

 

select * from check_constraints where constraint_schema = 'hr';

그림 13. check 제약조건의 조건문 조회

 

select * from key_column_usage where table_name = 'test4';

그림 14. test4 테이블에 선언된 PK, FK 제약조건 정보 상세조회

 

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

예제1)

create table dept80
as select employee_id, last_name, salary*12 'annsal', hire_date
      from employees
      where department_id = 80;

그림 15. 예제 1

서브쿼리를 사용해서 테이블 생성 시 원본 테이블의 구조와 데이터를 그대로 가지는 복사본의 테이블이 생성됨

단, 제약조건은 not null만 복사.

나머지 제약조건은 직접 추가해줘야함. 제약조건은 데이터 입력 후 추후에 추가/삭제해 줄 수 있음.

백업 테이블 또는 테이블 테이블 생성 시 많이 활용됨.

 

예제2)

create table emp_backup
as select * from employees;

그림 16. 예제 2

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

→ employees 테이블과 구조만 동일한 빈 테이블 생성

insert into copy_emp select * from employees;

→ employees 테이블로부터 107개 행 복사해서 삽입

employees  = copy_emp

==

create table emp_backup
as select * from employees;

employees = emp_backup

 

※as연산자 역할

1. 열에 대한 별칭(alias)

2. 기존 테이블의 내용을 복사하여 집어넣기

 

insert, update, delete → 데이터조작어(DML), 작업단위 : 행. commit, rollback을 결정해줘야함.

create table, alter table, drop table, truncate table → 데이터정의어(DDL), 작업단위 : 객체(테이블). auto-commit 내포

DDL은 한 번 실행하면 곧장 저장됨