[데이터베이스] 75. 데이터 정의 (DDL)

1. create table (생성)

-- DDL (create, drop, alter)

-- 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 -- 참조키
)charset=utf8mb4;

select * from team_tb;
desc team_tb;

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

select * from team_tb;
select * from player_tb;

🍀테이블 생성 및 확인

🍀Row 생성 및 확인

2. alter table (수정)

-- 2. alter table (수정)
-- column명과 type 변경.
-- 근데 거의 안씀 변경될일이 잘 없기도 하고 변경되면 날리고 다시만드는 경우가 많음.
alter table player_tb change column prole ptype varchar(20);
select * from player_tb;

3. drop table (삭제)

-- 3. drop table (삭제)
-- 테이블 자체를 삭제함
drop table player_tb;
select * from player_tb;

4. truncate (테이블 비우기)

-- 4. truncate (테이블 비우기)
-- 테이블 안에 있는 데이터 날리기
truncate team_tb;
select * from team_tb;

5. 제약조건들

-- 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);
-- 값이 없어도 null로 넣어주는 편이 좋음
insert into player_tb(pname, prole, tno) values('이승엽', '1루수', 1);
insert into player_tb(pname, pnumber, prole, tno) values('박병호', 19, '1루수', 2);

select * from player_tb;

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_tno 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. 삭제 (성공)
-- 이름이 아닌 pno를 잡아서 삭제하는 이유,
-- 유일한 값이기 때문에 데이터를 잘못 지울 일이 없음,
-- 처리도 빠름. 풀스캔하지 않아도 되니까.
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_tno foreign key (tno) references team_tb (tno)
    on delete cascade
) charset=utf8mb4;

select * from player_tb;

delete from team_tb where tno = 3;

-- 2) 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_tno foreign key (tno) references team_tb (tno)
    on delete set null
) charset=utf8mb4;

select * from player_tb;

delete from team_tb where tno = 3;