SQL : 10 DDL

_플레이데이터 빅데이터캠프 공부 내용 _ 6/2

MySQL : 10 DDL

1.  테이블 생성

1.1 구문

create table 테이블 이름(

  -- 컬럼 설정
  컬럼명   데이터타입    [default 값]    [제약조건] 

)

 

1.1.1 컬럼 설정

- 컬럼명   데이터타입    [default 값]    [제약조건] 

- 컬럼명

- 데이터타입 ex) int   varchar(30)
- default : 기본값. 값을 입력하지 않을 때 넣어줄 기본값. 값을 입력하면 입력한 값이 들어간다.

- 제약 조건

1.1.2 제약조건 설정 

not null은 컬럼레벨에서만 가능. 나머지는 컬럼레벨, 테이블레벨 둘다 가능.

 

- primary key (pk): 행식별 컬럼. NOT NULL, 유일값(Unique)
- unique Key (uk) : 유일값을 가지는 컬럼(중복x). null을 가질 수있다.
- not null (nn)  : 값이 없어서는 안되는 컬럼. null입력 불가.

                            값을 입력하지 않는 것도 불가(그러나, default 값 설정하면 default 값으로)
- check key (ck)  : 컬럼에 들어갈 수 있는 값의 조건을 직접 설정. check(컬럼명 조건)  

- foreign key (fk): 다른 테이블의 primary key 컬럼의 값만 가질 수 있는 컬럼. 
                              다른 테이블을 참조할 때 사용하는 컬럼.

 

ex) ck 

check(컬럼명 조건)

-- check : gender 컬럼은 m과 f만 올 수 있다는 제약 조건.
gender char(1) not null check(gender in ('M', 'F')),

-- check : age 컬럼의 값은 10-30사이의 정수만 가능하다는 제약 조건. 비교해서 트루가 되는 값만 반환.
age int check(age between 10 and 30)

 

(1) 제약 조건 설정하는 방법

- 컬럼 레벨 설정
    - 컬럼 설정에 같이 설정 .  not null의 경우 무조건 컬럼 레벨 설정에서 해줘야 한다.

    - 제약 조건 이름 지정 불가.
- 테이블 레벨 설정
    - 컬럼 설정 뒤에 따로 설정. 모든 컬럼 정의 후 constraint 으로 설정함.
    - 기본 문법 : constraint 제약조건이름 제약조건타입(지정할컬럼명) 

 

- 제약 조건 이름

   : 정의한 제약 조건을 어겼을 때 어떤 제약 조건을 어겼는지 제약조건이름으로 알 수 있다.

     <관례적인 표기> - 꼭 이렇게 해야 하는 것은 아니지만, 관례적으로 이렇게 표기한다.

       테이블명_컬럼명_제약조건타입  

       제약조건타입_테이블명_컬럼명  

        -- fk 설정

        자식테이블명_컬러명_부모테이블명_컬럼명_fk

 

   - mysql에서는 PK 제약조건이름 입력해도 primary으로 등록이 된다. 다른 db는 지정이름으로 된다.

     

(2) 테이블 제약 조건 조회

select * from information_schema.table_constraints   [where table_name = '테이블명'];

(3) 제약 조건 설정

-- 제약조건 해제
   SET FOREIGN_KEY_CHECKS = 0;
-- 제약조건 설정
   SET FOREIGN_KEY_CHECKS = 1;

  - 자식 테이블들을 먼저 삭제해야 부모테이블 삭제가 가능하다.

    Foreign key check 옵션을 끄고 해야 한다.

 

 

 

1.2 예시 _ 컬럼레벨, 테이블레벨

1.2.1 제약 조건 : 컬럼 레벨. 

create table parent_tb(
    no int primary key, -- no : number  -- pk 제약조건 컬럼레벨.
    name varchar(50) not null,  -- not null 제약 조건 컬럼레벨.
    -- 날짜/시간 타입의 컬럼에 insert 시점의 일시를 기본값으로 넣을 경우 
    join_date timestamp default current_timestamp, 	
    email varchar(100) unique, -- uk 제약조건 컬럼레벨
    gender char(1) not null check(gender in ('M', 'F')), -- ck. gender 컬럼은 m과 f만 올 수 있다는 제약 조건. 
    age int check(age between 10 and 30) -- ck. age 컬럼의 값은 10-30사이의 정수만 가능하다는 제약 조건.
);

 

-  제약 조건 DEFAULT

  : 컬럼 데이터 삽입 기본값 설정.  (예시. 5줄)
    -- > type : timestamp, default값 : current_timestamp  

    만약 join_date에 값을 넣지 않는다면, default 값이 들어가게 만들어주는 것이다.

    위 예시에는 current_timestamp 현재 날짜 시간이 들어가도록 설정해줌.

    - default 설정은 dbms 마다 다른데 mysql은 이렇게 작성해주면 됨 
    - 생일 등 이러한 등록은 사용자의 입력에 따르지만, 가입한 날짜의 경우 사용자의 입력으로 이뤄지지 않을 것이다.

      등록 버튼을 누른 그 순간을 자동으로 해야 한다. 그때 이렇게 작성한다.

 

- insert시 설정한 제약 조건을 어긴다면 에러가 날 것이다.  

-- 제약 조건 어겼을 때 error--

insert into parent_tb (no, name, email, gender, age) values (104, '유관순','a@a.com','F', 29);
-- Error Code: 1062. Duplicate entry 'a@a.com' for key 'parent_tb.email'
-- 이메일은 uk 제약조건. 중복된 값이 들어 갈 수 없다.

insert into parent_tb (no, name, email, gender, age) values (105, '강감찬','abd@a.com','남', 29);
-- Error Code: 3819. Check constraint 'parent_tb_chk_1' is violated.	
-- gender check 제약 조건.

 

1.2.2  제약 조건 : 테이블 레벨. 

 - 제약 조건 이름 설정 할 수 있음.  not null 빼고 테이블 레벨로 설정 가능.

create table child_tb(
	no int auto_increment,  -- PK.  auto_increment.
    jumin_num char(14),  -- UK  -- 주민번호는 자리수 14 고정('-' 포함 가능성까지)이니 char로 입력
    age int not null, -- CK
    p_no int not null, -- FK (parent_tb.no 참조)    
    constraint child_tb_no_pk primary key(no), -- mysql은 제약조건이름 입력해도 primary으로 등록이 된다. 다른 db는 지정이름으로 됨.
    constraint child_tb_jumin_num_uk unique(jumin_num),
    constraint child_tb_age_ck check(age > 0 and age <= 100),
    constraint child_tb_p_no_parent_tb_no_fk foreign key(p_no) references parent_tb(no) on delete cascade 
    -- 부모삭제 시 자식 해당 행도 같이 삭제 -- on 설정 안 하면 부모 테이블 삭제 불가. -- p_no가 not null이라 delete null 설정 못함.  
);

 

-  제약 조건 auto_increment

   : 자동증가 컬럼. insert시 1씩 증가하는 정수를 가진다. PK에서 주로 사용.

    - 해당 컬럼은 insert 구문 실패에도 count되기 때문에 에러도 포함하여 insert 쿼리 실행시킨 만큼 인덱스가 증가한다.

-  on delete cascade

   : 부모삭제 시 자식 해당 행도 같이 삭제.

      p_no가 not null이라 delete set null 설정 못함.  

     이를 아무 설정 안 하면 부모 테이블 삭제 불가하다.

 

- insert시 설정한 제약 조건을 어긴다면 에러가 날 것이다.  

insert into child_tb(jumin_num, age, p_no) values ('333333-2222222', 25, 201);
-- Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`testdb`.`child_tb`, CONSTRAINT `child_tb_p_no_parent_tb_no_fk` FOREIGN KEY (`p_no`) REFERENCES `parent_tb` (`no`) ON DELETE CASCADE)	0.000 sec

   - p_no는 fk 로 부모테이블을 참조하는 컬럼이다. 부모테이블에 없는 값을 넣었다면 오류가 날 것이다.
     부모테이블에서 참조할 행 데이터에 맞춰 적어줘야 한다.

2. 테이블 삭제

DROP TABLE 테이블이름;

 

delete from parent_tb where no = 101;

3. 테이블 수정 ALTER

[ 컬럼 관련 수정 ]

3.1 컬럼 추가

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

 

  ex) 컬럼 레벨 제약조건 지정하는 것도 테이블 생성할 때와 똑같이 해주면 된다.

alter table cust add column age int default 0 not null;
alter table cust add column age2 int default 0 not null check(age2 > 10);

alter table emp2 add column email varchar(100) not null, 
                 add column jumin_num char(14) unique;

 

3.2 컬럼 수정

  ALTER TABLE 테이블이름 MODIFY COLUMN 수정할컬럼명 변경설정 [, MODIFY COLUMN 수정할컬럼명  변경설정]

- 데이터타입 관련 수정

     - 숫자/문자열 컬럼은 크기를 늘릴 수 있다.

           - 크기를 줄일 수 있는 경우 : 열에 값이 없거나 모든 값이 줄이려는 크기보다 작은 경우

              만약, 열에 있는 데이터 값보다 크기를 작게 설정하면 에러남.

 

    - 데이터가 모두 NULL이면 데이터타입을 변경할 수 있다. (단 CHAR<->VARCHAR2 는 가능.)

 

- NULL 설정 관련 수정

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

 

     - 컬럼명 타입;                      => nollable   # null 생략 가능하기에 안 적어주면 기본값 null 허용됨.
     - 컬럼명 타입 not null ;        => not null 

 

ex) datatype, null 허용 여부 

alter table cust modify column age tinyint not null; -- not null을 지정하지 않으면 기본값 null 허용으로 변경됨.
alter table cust modify column age2 int null; -- 데이터 타입은 그대로 둔다면 그대로 또 적어줌.


-- 데이터 타입 크기를 줄이는 경우에 담겨있는 데이터들의 크기보다 작은 크기를 설정할 수 없다. 에러.
alter table cust modify column address varchar(2) not null;
alter table cust2 modify column address varchar(2) not null; -- 데이터 값들이 이미 2 이상이다. 에러남.

 

3.3 컬럼 삭제

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

- CASCADE CONSTRAINTS

    : 삭제하는 컬럼이 Primary Key인 경우 그 컬럼을 참조하는 다른 테이블의 Foreign key 설정을 모두 삭제한다.
- 한번에 하나의 컬럼만 삭제 가능.

 

ex)

alter table cust drop column cust_age;

 

3.4 컬럼 이름 바꾸기

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

- not null을 제외한 제약 조건이 있다면, 이름 변경이 안 된다.

alter table cust rename column age to cust_age; -- age2를 cust_age로 바꿈.

 

 

[ 제약조건 관련 수정 ]

   제약조건은 수정 불가. 삭제하고 다시 만들어야 함.

3.5 제약조건 추가

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

  ex) pk, fk 제약조건 추가

-- pk 설정 추가
alter table cust add constraint cust_pk primary key(cust_id);
-- alter table cust add primary key(cust_id) -- primary는 이 구문도 가능. 

-- fk 설정 추가
alter table ord add constraint ord_cust_fk foreign key(cust_id) references cust(csut_id)

-- ck 설정 추가
alter table emp2 add constraint emp2_gender_ck check(gender in ('M','F'));

-- uk 설정 추가
alter table emp2 add constraint emp2_email_uk unique(email);

- 사실 primary key는 제약조건 이름 지정해도 primary으로 지정됨.

 

3.6 제약 조건 삭제

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

만약, PK를 제거한다면? 

ALTER TABLE 테이블명 DROP PRIMARY KEY 
    ==> CASECADE : 제거하는 Primary Key를 Foreign key 가진 다른 테이블의 Foreign key 설정을 모두 삭제한다.

 ex) 

alter table emp2 drop constraint emp2_email_uk;

 

3.7 NOT NULL <-> NULL 변환

- 위 컬럼 수정에서 보기

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

 

** 테이블 복제

- NOT NULL 조건을 제외한 제약 조건은 복사 되지 않는다.

create table 이름
as
select 문 -- > subquery

-- 복사한 테이블 생성(not null을 제외한 제약 조건은 copy가 안됨)
create table cust2
as
select * from customers;

- 테이블의 구조만 복사

    where 조건 성립 안 하니 데이터는 복사하지 않는다.

    데이터 값은 복사되지 않고 테이블의 구조만(컬럼 설정 ....) 복사됨.  (NOT NULL 조건 빼고 다른 제약조건은 복사안됨.)

create table cust
as
select * from customers where 1 = 0;

 

** 테이블관련 설정 조회 (DDL관련)

1) 해당 DB 내 테이블 조회

show tables; -- 해당 DB 내 테이블 조회

2) 생성한 테이블에 대한 정보

desc 테이블명;

3) 테이블 제약 조건 조회

select * from information_schema.table_constraints;
select * from information_schema.table_constraints where table_name = '테이블명';