Day34 Database

22/12/12

📌 9. 데이터정의어(DDL) - Table

• <SQL구문의 유형>
  데이터질의어(DQL) : select
  데이터조작어(DML) : insert, update, delete
  데이터정의어(DDL) : create, alter, drop, truncate
  데이터제어어(DCL) : grant, revoke
  트랜잭션제어어(TCL) : commit, rollback, savepoint

---

📂 테이블 생성 : create table

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

• 테이블명, 컬럼명 : 문자로 시작, 문자/숫자/특수문자(_, #, $만 사용 가능)혼합 가능
• 데이터타입 - 문자 : char(고정), varchar(가변)
                  - 숫자 - 정수 : int, bigint
                           - 실수 : double
                  - 날짜 - 년/월/일 : date
                           - 년/월/일/시/분/초 : datetime
• 옵션 : 제약조건, default값

✅ 테이블 생성

🔸 default값이 포함된 테이블 생성 및 활용

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

💥 자동커밋 설정하기 : [Query] - [Auto-Commit Transactions] 체크하기

insert into dept
values (10,'AAA','A100','2022-10-25 13:51:05');

default값이 선언되어 있지 않은 컬럼은 생략시 null값 삽입됨.
default값이 선언된 컬럼은 생략시 default값 삽입됨.

🔸 암시적(자동)으로 default값 삽입하는 방법

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

🔸 명시적(수동)으로 default값 삽입하는 방법

insert into dept
values (30,'CCC','C100',default);

insert into dept
values (40,'DDD','D100',null);

insert into dept
values (50,'EEE',default, default);

update dept
set create_date = default
where deptno = 40;

select*
from dept;

✅ 제약조건 : 테이블에 부적합한 데이터가 삽입/수정/삭제되는 것을 막아줌.

• 제약조건 유형 : not null, unique, primary key, foreign key, check
• 제약조건 선언 시기 : 테이블 생성 시 - create table ---;
                              테이블 생성 후 - alter table ---;
• 제약조건 선언 문법 : 컬럼 레벨 문법 - nn, uk, pk, ck
                              테이블 레벨 문법 - fk, uk, pk, ck

🔸 [제약조건1] not null
    null값이 삽입/수정될 수 없는 제약조건
    필수 컬럼에 선언함.
    (예) 회원명, 직원이름, 주민번호, 생년월일, 전화번호 등

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

🔸 [제약조건2] unique
    중복값이 삽입/수정되는 것을 막아주는 제약조건
    (단, not 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);
 
desc test2;

🔸 [제약조건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);
 
desc test3;

🔸 [제약조건4] foreign key(외래키)
    자기 자신 테이블이나 다른 테이블의 특정 컬럼을 참조하는 제약조건
    FK 제약조건이 선언된 컬럼 : 자식컬럼
    FK 제약조건이 참조하는 컬럼 : 부모컬럼
    자식 컬럼에는 부모 컬럼에 있는 값 중 하나만 삽입/수정될 수 있음!
    (단, not 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));
 
desc test4;

🔸 [제약조건5] check
    해당 컬럼이 만족해야 하는 조건문을 자유롭게 지정하는 제약조건
    (예) salary int check (salary > 0)
         성별 varchar(10) check (성별 in ('남','여'))
         jumin varchar(13) check (length(jumin)=13)
         email varchar(50) check (email like '%@%')

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

• Data Dictionary(DB 사전) 사용하기

show databases;

• DB 사전인 information_schema로 전환하기

use information_schema;

show tables;

desc table_constraints;

select table_schema, table_name, constraint_type
from table_constraints
where table_schema = 'hr'
order by table_name;

desc check_constraints;

select *
from check_constraints
where constraint_schema = 'hr';

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

use hr;

🔸 (예제1) 기존 테이블(서브쿼리 테이블)에 복사본 테이블이 생성됨.

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

select*
from dept80;

🔸 (예제2) 백업용 또는 테스트용으로 복사본 테이블 생성 많이함.

create table copy_dept
as select*
   from departments;
desc copy_dept;

---

✏️ <연습문제>

shopdb 데이터베이스(스키마)에서 테이블 생성 연습문제 작업하기!

use shopdb;

1. 테이블 이름 : TITLE
   

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','PR','R','NC17','NR')),
 CATEGORY varchar(20) check (CATEGORY in ('DRAMA','COMEDY','ACTION',
										'CHILD','SCIFI','DOCUMENTARY')),
 RELEASE_DATE date);
desc title;

2. 테이블 이름 : TITLE_COPY
   

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

• check 제약조건에 대한 정보를 더 디테일하게 보고싶을때

-- DB 사전 조회
use information_schema;

select*
from check_constraints
where constraint_schema = 'shopdb';