[Database] 12. DDL (데이터 정의)

-- DDL (CREATE, DROP, ALTER)
-- NFD (엔포드) 외워라 !!!!!!!

-- 1. CREATE table(테이블 만들기)
CREATE TABLE team_tb(
	tno int primary key, 
    tname varchar(10) unique,
    tyear int, 
    tloc varchar(10)) 
char set utf8mb4;

select * from team_tb;
desc team_tb;

CREATE TABLE player_tb(
	pno int primary key,
    pname varchar(10),
    pnumber int,
    prole varchar(10),
    tno int)    
char set utf8mb4;

select * from player_tb;
desc player_tb;

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, '박병호', 18, '1루수', 2);

select * from team_tb;
select * from player_tb;

-- 2. ALTER TABLE (테이블 수정 : 컬럼 명과 타입 수정)
-- 테이블을 우클릭 -> 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. 제약 조건들
CREATE TABLE player_tb(
	pno int primary key auto_increment, -- pno가 1씩 자동 증가
    pname varchar(10) not null,
    pnumber int,
    prole varchar(10) default '타자',
    tno int)    
char set 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 ('임수혁', 5, '포수', 3);
insert into player_tb(pname, prole, tno) values ('이승엽', '1루수', 1);
insert into player_tb(pname, pnumber, prole, tno) values ('박병호', 18, '1루수', 2);

select *
	from  player_tb;

2. 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 제약 조건으로 인해 insert 불가능
insert into player_tb(pname, pnumber, prole, tno) values('홍길동', 19, '1루수', 4);
select * from player_tb;

-- 4. 삭제
delete from player_tb where pno = 5;

-- 5. 삭제 실패
-- (1) 참조하고 있는 이승엽의 tno를 null로 업데이트 후 삭제하면 잘 됨
delete from team_tb where tno = 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,
    constraint fk_player_tb foreign key(tno) references team_tb (tno)
    on delete cascade -- update
) 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
) charset=utf8mb4;

delete from team_tb where tno = 3;

select * from player_tb;