[데이터베이스 데이터 조작] 7. DDL

김건우

Feb 26, 2025

Contents

1. create table 2. alter table (수정)3. drop table (삭제) 4. truncate (테이블 비우기) 5. 제약조건들 !!6. FK 제약조건

💡

데이터 정의 언어 (Data Definition Language)

CREATE : 데이터 베이스, 테이블, 뷰, 인덱스 등을 생성합니다

ALTER : 데이터베이스 개체의 구조를 수정합니다. DROP : 데이터베이스 개체를 삭제합니다. TRUNCATE : 테이블의 모든 데이터를 삭제합니다.

1. create table


-- 테이블을 만드는 문법 
create table team_tb (
	tno int primary key,
    tname varchar(10) unique,
    tyear int,
    tloc varchar(10)
) charset = utf8mb4;

create table player_tb (
	pno int primary key,
    pname varchar(20),
    pnumber int,
    prole varchar(10),
    tno int  -- 참조키 FK (다른테이블을 참조 할 수 있다)
) charset = utf8mb4;

insert into team_tb(tno, tname, tyear, tloc) values(1, '삼성', 1982, '대구');
insert into team_tb(tno, tname, tyear, tloc) values(2, '넥센', 2000, '서울');
insert into team_tb(tno, tname, tyear, tloc) values(3, '롯데', 1990, '부산');

insert into player_tb(pno, pname, pnumber, prole, tno) values(1, '이대호', 20, '4번타자', 3); 
insert into player_tb(pno, pname, pnumber, prole, tno) values(2, '가득염', 10, '투수', 3); 
insert into player_tb(pno, pname, pnumber, prole, tno) values(3, '임수혁', 5, '포수', 3); 
insert into player_tb(pno, pname, pnumber, prole, tno) values(4, '이승엽', 3, '1루수', 1); 
insert into player_tb(pno, pname, pnumber, prole, tno) values(5, '박병호', 3, '1루수', 2); 

select *
from team_tb;

select *
from player_tb;

2. alter table (수정)


alter table player_tb change column prole ptype varchar(20);

3. drop table (삭제)


drop table player_tb;

4. truncate (테이블 비우기)


truncate team_tb;

5. 제약조건들 !!


drop table player_tb;

create table player_tb (
	pno int primary key auto_increment,
    pname varchar(20) not null,
    pnumber int,
    prole varchar(10) default '타자',
    tno int
) charset = utf8mb4;

insert into player_tb(pname, pnumber, tno) values('이대호', 20, 3); 
insert into player_tb(pname, pnumber, prole, tno) values('가득염', 10, '투수', 3); 
insert into player_tb(pname, pnumber, prole, tno) values('임수혁', null, '포수', 3); 
insert into player_tb(pname, prole, tno) values('이승엽', '1루수', 1); 
insert into player_tb(pname, pnumber, prole, tno) values('박병호', 3, '1루수', 2); 

select *
from player_tb;

auto_increment : 자동으로 pno가 ++ 되면서 생성된다.

6. FK 제약조건


-- DDL (create, drop, alter)


-- 모든 제약조건 잠시 해제
drop table if exists team_tb;
drop table if exists player_tb;

-- 1. create table (FK)
create table team_tb (
	tno int primary key,
    tname varchar(10) unique,
    tyear int,
    tloc varchar(10)
) charset=utf8mb4;

create table player_tb (
	pno int primary key auto_increment,
    pname varchar(20) not null,
    pnumber int,
    prole varchar(10) default '타자',
    tno int,
    constraint fk_player_tb foreign key(tno) references team_tb (tno)
) charset=utf8mb4;

-- 2. 더미데이터 세팅
insert into team_tb(tno, tname, tyear, tloc) values(1, '삼성', 1982, '대구');
insert into team_tb(tno, tname, tyear, tloc) values(2, '넥센', 2000, '서울');
insert into team_tb(tno, tname, tyear, tloc) values(3, '롯데', 1990, '부산');

insert into player_tb(pno, pname, pnumber, prole, tno) values(1, '이대호', 20, '1루수', 3);
insert into player_tb(pno, pname, pnumber, prole, tno) values(2, '가득염', 10, '투수', 3);
insert into player_tb(pno, pname, pnumber, prole, tno) values(3, '임수혁', 5, '포수', 3);
insert into player_tb(pno, pname, pnumber, prole, tno) values(4, '이승엽', 3, '1루수', 1);
insert into player_tb(pno, pname, pnumber, prole, tno) values(5, '박병호', 19, '1루수', 2);


-- 3. FK 제약조건으로 인해 insret 불가능
insert into player_tb(pname, pnumber, prole, tno) values('홍길동', 19, '1루수', 4);

-- 4. 삭제 (성공)
delete from player_tb where pno = 5;

-- 5. 삭제(실패)

-- (1) 참조하고 있는 이승엽의 tno를 null로 업데이트 후 삭제하면 잘됨
delete from team_tb where pno = 1;

-- (2) cascade -- on delete
drop table if exists team_tb;
drop table if exists player_tb;

create table team_tb (
	tno int primary key,
    tname varchar(10) unique,
    tyear int,
    tloc varchar(10)
) charset=utf8mb4;

create table player_tb (
	pno int primary key auto_increment,
    pname varchar(20) not null,
    pnumber int,
    prole varchar(10) default '타자',
    tno int,
) charset=utf8mb4;

select *
from player_tb;
delete from team_tb where tno = 3;


-- (3) cascade -- on delete set null
drop table if exists team_tb;
drop table if exists player_tb;

create table team_tb (
	tno int primary key,
    tname varchar(10) unique,
    tyear int,
    tloc varchar(10)
) charset=utf8mb4;

create table player_tb (
	pno int primary key auto_increment,
    pname varchar(20) not null,
    pnumber int,
    prole varchar(10) default '타자',
    tno int,
    constraint fk_player_tb foreign key(tno) references team_tb (tno)
    on delete set null -- update
) charset=utf8mb4;

select *
from player_tb;
delete from team_tb where tno = 3;

select *
from team_tb;