DDL _Table

DDL: 정의하고자 하는 데이터에 과녀련된 무언가를 만드는 것

DML : I (생성) / U(수정) /D(삭제)/ S(조회)
DDL : CREATE / ALTER / DROP + TRNNCATE TABLE

---

테이블 생성

• 구문

create table 테이블 이름(
	컬럼 설정)

컬럼 설정

• 컬럼명 데이터타입 [default 값] [제약조건]
  → 데이터타입
  → default : 기본값. 값을 입력하지 않을 때 넣어줄 기본값

제약조건 설정

• primary key (PK): 행식별 컬럼. NOT NULL, 유일값(Unique)
• unique Key (uk) : 유일값을 가지는 컬럼 null을 가질 수 있음
• not null (nn) : 값이 없어서는 안되는 컬럼
• check key (ck) : 컬럼에 들어갈 수 있는 값의 조건을 직접 설정
• foreign key (fk): 다른 테이블의 primary key 컬럼의 값만 가질 수 있는 컬럼
  다른 테이블을 참조할 때 사용하는 컬럼

• 컬럼 레벨 설정
  → 컬럼 설정에 같이 설정
• 테이블 레벨 설정
  → 컬럼 설정 뒤에 따로 설정
• 기본 문법
  : constraint 제약조건이름 제약조건타입(지정할 컬럼명)
• 테이블 제약 조건 조회
  : select * from information_schema.table_constraints;

테이블 삭제

• 구분
  DROP TABLE 테이블이름;

• 제약조건 해제
  SET FOREIGN_KEY_CHECKS = 0;

• 제약조건 설정
  SET FOREIGN_KEY_CHECKS = 1;

ex)

• 이 조건을 가진 book 이라는 table을 만들고자 할 때

책(book) 테이블
-- 컬럼명 | 데이터타입 | 제약 조건 |기타
-- isbn | varchar(13), | primary key
-- title | varchar(50) | not null
-- author | varchar(50) | not null
-- page | int | not null, check key-0이상값
-- price | int | not null, check key-0이상값
-- publish_date | timestamp | not null, default-current_timestamp(등록시점 일시)
-- publisher_no | int | not null, Foreign key-publisher

drop table if exists book;  -- if exists 안 넣으면 없을 경우 error뜸

create table book(  
	isbn varchar(13),  
    title varchar(50) not null, 
    author varchar(50) not null, 
    page int not null, 
    price int not null,  
    publish_date timestamp default current_timestamp not null,
    publisher_no int not null,
    primary key(isbn),  -- 이름 지정할 거 아니면 'constriant + 이름' 지워도 됨
    check(page>0),
    check(price > 0),
    foreign key(publisher_no) references publisher(publisher_no)
    );

show tables;

---

ALTER : 테이블 수정

컬럼 관련 수정

• 컬럼 추가

  : ALTER TABLE 테이블이름 ADD COLUMN 추가할 컬럼설정 [,ADD COLUMN 추가할 컬럼설정]

• 컬럼 수정

  ALTER TABLE 테이블이름 MODIFY COLUMN 수정할컬럼명 타입 null설정 [, MODIFY COLUMN 수정할컬럼명 타입 null설정]

→ 숫자/문자열 컬럼은 크기를 늘릴 수 있음 - 줄이는것은 불가
(크기를 줄일 수 있는 경우 : 열에 값이 없거나 모든 값이 줄이려는 크기보다 작은 경우)
→ 데이터가 모두 NULL이면 데이터타입을 변경할 수 있음 (단 CHAR<->VARCHAR 는 가능)
→ null 설정을 생략하면 nullable이 됨

• 컬럼 삭제

  ALTER TABLE 테이블이름 DROP COLUMN 컬럼이름 [CASCADE CONSTRAINTS]

  • CASCADE CONSTRAINTS
    : 삭제하는 컬럼이 Primary Key인 경우 그 컬럼을 참조하는 다른 테이블의 Foreign key 설정을 모두 삭제

    → 한번에 하나의 컬럼만 삭제 가능

ALTER TABLE 테이블이름 SET UNUSED (컬럼명 [, ..]) → 괄호 안의 column은 사용하지 않음 (나중에 한번에 지우기 위해)
ALTER TABLE 테이블이름 DROP UNUSED COLUMNS

• SET UNUSED 설정시 컬럼을 바로 삭제하지 않고 삭제 표시를 함
  → 설정된 컬럼은 사용할 수 없으나 실제 디스크에는 저장되어 있음으로 속도가 빠름
  → DROP UNUSED COLUMNS 로 SET UNUSED된 컬럼을 디스크에서 삭제

• 컬럼 이름 바꾸기

  ALTER TABLE 테이블이름 RENAME COLUMN 원래이름 TO 바꿀이름;

ex)

use hr_join;

-- customers/orders 테이블의 구조만 복사한 테이블 생성(not null을 제외한 제약 조건은 copy가 안됨)
show tables;

drop table if exists cust;
create table cust
as 
select * from customers;

show tables;

desc cust;
select * from cust;
select * from information_schema.table_constraints 
where table_name = 'customers'; -- table column의 구조만 복사하고 제약조건은 not null 제외하면 전부 복사가 안됨

drop table if exists cust;
select * from customers where 1 = 0;  -- where 절을 넣으면 구조만 copy 하는 것이고 where절을 넣지 않으면 데이터까지 copy


drop table if exists orders_copy;
create table orders_copy
as
select * from orders where 1 = 0;

show tables;
desc cust;

---

제약 조건 관련 수정

• 제약조건 추가

  ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건 설정

• 제약조건 삭제

  ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건이름

  PRIMARY KEY 제거
  : ALTER TABLE 테이블명 DROP PRIMARY KEY

• CASECADE : 제거하는 Primary Key를 Foreign key 가진 다른 테이블의 Foreign key 설정을 모두 삭제함

• NOT NULL <-> NULL 변환은 컬럼 수정을 통해 함

  • ALTER TABLE 테이블명 MODIFY COLUMN 컬럼명 타입 NOT NULL
  • ALTER TABLE 테이블명 MODIFY COLUMN 컬럼명 NULL

ex)

• 제약조건 추가

-- cust PK 추가
alter table cust add constraint cust_pk primary key(cust_id);
-- orders_copy(cust_id) - cust (cust_id) FK
alter table orders_copy add constraint fk_order_copy_cust
			foreign key(cust_id) references cust(cust_id);

-- cust.address를 null 허용 컬럼
alter table cust modify column address varchar(40);  -- 생략 또는 null (null 허용)
	-- 다시 not null로 바꿀라면
alter table cust modify column address varchar(40) not null;    -- null 허용 x 

• 제약 조건 제거

alter table orders_copy drop constraint fk_order_copy_cust;

select * from information_schema.table_constraints 
where table_name = 'customers';

desc cust;

• 컬럼 추가

alter table cust add column age int default 0 not null; -- 구분을 뮈해 column명 (age)부터 묶어주기도 함

desc cust;

• 컬럼 수정

alter table cust modify column cust_name varchar(30) not null, -- not null인것은 그대로 not null 해줘야 함
				 modify column address varchar(100);

desc cust;

• 컬럼 이름 변경

alter table cust rename column age to cust_age;
desc cust;

• 컬럼 삭제

alter table cust drop column cust_age;
desc cust;