[SQL] 20. DDL, DML

DDL

ALTER TABLE

• 테이블의 구조를 변형할 수 있는 명령어
• 작업 할 테이블에 대한 권한이 없다면 작업을 할 수 없다.

CREATE TABLE dept80 AS SELECT employee_id, last_name, salary*12 AS annsal, hire_date FROM employees WHERE department_id = 80;

 

ADD

• 추가적인 구조들을 더 할 때 사용하는 절
• ADD 뒤에는 온전한 테이블 레벨의 문법으로 내용을 정의한다.
• 컬럼을 정의하는 경우 컬럼명과 데이터타입은 필수이다.

ALTER TABLE dept80 ADD job_id varchar(9);

DESC dept80;

Field      |Type         |Null|Key|Default|Extra|

-----------+-------------+----+---+-------+-----+

employee_id|int unsigned |NO  |   |       |     |

last_name  |varchar(25)  |NO  |   |       |     |

annsal     |decimal(10,2)|NO  |   |0.00   |     |

hire_date  |date         |NO  |   |       |     |

job_id     |varchar(9)   |YES |   |       |     |

 

• 새롭게 추가된 컬럼 job_id는 기존 테이블의 데이터에서는 없었던 값이고 default도 없는 컬럼이기 때문에 별도의 설정이 없다면 다음과 같이 NULL값이 입력되어 있다.

SELECT * FROM dept80;

 

employee_id|last_name |annsal   |hire_date |job_id|

-----------+----------+---------+----------+------+

        145|Russell   |203280.00|1996-10-01|      |

        146|Partners  |196020.00|1997-01-05|      |

        147|Errazuriz |174240.00|1997-03-10|      |

        148|Cambrault |159720.00|1999-10-15|      |

        149|Zlotkey   |152460.00|2000-01-29|      |

…

 

ALTER TABLE dept80 ADD email varchar(30) DEFAULT '미입력';

DESC dept80;

Field      |Type         |Null|Key|Default|Extra|

-----------+-------------+----+---+-------+-----+

employee_id|int unsigned |NO  |   |       |     |

last_name  |varchar(25)  |NO  |   |       |     |

annsal     |decimal(10,2)|NO  |   |0.00   |     |

hire_date  |date         |NO  |   |       |     |

job_id     |varchar(9)   |YES |   |       |     |

email      |varchar(30)  |YES |   |미입력    |     |

 

SELECT * FROM dept80;

employee_id|last_name |annsal   |hire_date |job_id|email|

-----------+----------+---------+----------+------+-----+

        145|Russell   |203280.00|1996-10-01|      |미입력  |

        146|Partners  |196020.00|1997-01-05|      |미입력  |

        147|Errazuriz |174240.00|1997-03-10|      |미입력  |

        148|Cambrault |159720.00|1999-10-15|      |미입력  |

        149|Zlotkey   |152460.00|2000-01-29|      |미입력  |

 

컬럼 위치 지정하기

• 테이블 내에서 컬럼의 위치를 그대로 옮기는 것을 보통은 잘 지원하지 않으나 MySQL에서 일부분 사용가능하다.

FIRST

• 테이블의 첫번째 자리에 컬럼 추가하기
• 컬럼 정의의 마지막에 FIRST 키워드를 추가한다.

ALTER TABLE dept80 ADD emp_number int FIRST;

AFTER

• 특정 컬럼의 다음 순서로 컬럼을 추가하고 싶은 경우 사용한다.
• 다음의 경우 last_name 컬럼의 오른쪽 위치에 컬럼을 추가하게 된다.

ALTER TABLE dept80 ADD salary int DEFAULT 300 NOT NULL AFTER last_name;

DESC dept80;

 

Field      |Type         |Null|Key|Default|Extra|

-----------+-------------+----+---+-------+-----+

emp_number |int          |YES |   |       |     |

employee_id|int unsigned |NO  |   |       |     |

last_name  |varchar(25)  |NO  |   |       |     |

salary     |int          |NO  |   |300    |     |

annsal     |decimal(10,2)|NO  |   |0.00   |     |

hire_date  |date         |NO  |   |       |     |

job_id     |varchar(9)   |YES |   |       |     |

email      |varchar(30)  |YES |   |미입력    |     |

MODIFY

DESC dept80;

Field      |Type         |Null|Key|Default|Extra|

-----------+-------------+----+---+-------+-----+

emp_number |int          |YES |   |       |     |

employee_id|int unsigned |NO  |   |       |     |

last_name  |varchar(25)  |NO  |   |       |     |

salary     |int          |NO  |   |300    |     |

annsal     |decimal(10,2)|NO  |   |0.00   |     |

hire_date  |date         |NO  |   |       |     |

job_id     |varchar(9)   |YES |   |       |     |

email      |varchar(30)  |YES |   |미입력    |     |

 

• 기존 INT 데이터타입을 bigint로 변환

ALTER TABLE dept80 MODIFY salary bigint;

 

• 기존 varchar(25)에서 사이즈를 더 늘림.

ALTER TABLE dept80 MODIFY last_name varchar(30) NOT NULL;

DESC dept80;

Field      |Type         |Null|Key|Default|Extra|

-----------+-------------+----+---+-------+-----+

emp_number |int          |YES |   |       |     |

employee_id|int unsigned |NO  |   |       |     |

last_name  |varchar(30)  |NO  |   |       |     |

salary     |bigint       |YES |   |       |     |

annsal     |decimal(10,2)|NO  |   |0.00   |     |

hire_date  |date         |NO  |   |       |     |

job_id     |varchar(9)   |YES |   |       |     |

email      |varchar(30)  |YES |   |미입력    |     |

 

ALTER TABLE dept80 MODIFY salary bigint DEFAULT 500 NOT NULL;

Field      |Type         |Null|Key|Default|Extra|

-----------+-------------+----+---+-------+-----+

emp_number |int          |YES |   |       |     |

employee_id|int unsigned |NO  |   |       |     |

last_name  |varchar(30)  |NO  |   |       |     |

salary     |bigint       |NO  |   |500    |     |

annsal     |decimal(10,2)|NO  |   |0.00   |     |

hire_date  |date         |NO  |   |       |     |

job_id     |varchar(9)   |YES |   |       |     |

email      |varchar(30)  |YES |   |미입력    |     |

 

RENAME

• 구조의 이름을 다른 값으로 바꾼다.
• 테이블의 기본 구조는 바뀌는게 자동 적용되지만 프로그램과 연결된 데이터베이스인 경우 프로그램에서도 이름을 바꿔야 정상동작한다.
• column alias를 사용해도 되지만 가능하면 바꾸지 않고 처음에 잘 작성하는게 좋다.
• 컬럼명을 기존 hire_date에서 start_date로 변환하기

ALTER TABLE dept80 RENAME COLUMN hire_date TO start_date;

DESC dept80;

Field      |Type         |Null|Key|Default|Extra|

-----------+-------------+----+---+-------+-----+

emp_number |int          |YES |   |       |     |

employee_id|int unsigned |NO  |   |       |     |

last_name  |varchar(30)  |NO  |   |       |     |

salary     |bigint       |NO  |   |500    |     |

annsal     |decimal(10,2)|NO  |   |0.00   |     |

start_date |date         |NO  |   |       |     |

job_id     |varchar(9)   |YES |   |       |     |

email      |varchar(30)  |YES |   |미입력    |     |

DROP 

• 테이블의 구성하는 컬럼이나 제약조건을 삭제할 때 사용하는 절

DESC dept80;

Field      |Type         |Null|Key|Default|Extra|

-----------+-------------+----+---+-------+-----+

emp_number |int          |YES |   |       |     |

employee_id|int unsigned |NO  |   |       |     |

last_name  |varchar(30)  |NO  |   |       |     |

salary     |bigint       |NO  |   |500    |     |

annsal     |decimal(10,2)|NO  |   |0.00   |     |

start_date |date         |NO  |   |       |     |

job_id     |varchar(9)   |YES |   |       |     |

email      |varchar(30)  |YES |   |미입력    |     |

 

• emp_number 컬럼이 제거된 것을 확인 할 수 있음.
• 이 때, 삭제 된 컬럼의 데이터값은 같이 삭제되므로 주의!!

ALTER TABLE dept80 DROP emp_number;

Field      |Type         |Null|Key|Default|Extra|

-----------+-------------+----+---+-------+-----+

employee_id|int unsigned |NO  |   |       |     |

last_name  |varchar(30)  |NO  |   |       |     |

salary     |bigint       |NO  |   |500    |     |

annsal     |decimal(10,2)|NO  |   |0.00   |     |

start_date |date         |NO  |   |       |     |

job_id     |varchar(9)   |YES |   |       |     |

email      |varchar(30)  |YES |   |미입력    |     |

 

제약조건 조회

• 제약조건과 같은 테이블에 대한 명세, 정의 정보는 information_schema에 있으므로 필요한 경우 활용할 수 있어야 한다.

• 체크 제약조건은 나중에 추가된 제약조건으로 기존 제약조건 조회 테이블과 분리되어 별도의 check_constraints 테이블에서 조회가 가능하다.

SELECT * FROM check_constraints;

CONSTRAINT_CATALOG|CONSTRAINT_SCHEMA|CONSTRAINT_NAME |CHECK_CLAUSE                                                                                                                         |

------------------+-----------------+----------------+-------------------------------------------------------------------------------------------------------------------------------------+

def               |shopdb           |test5_chk_1     |(length(`jumin`) = 13)                                                                                                               |

def               |shopdb           |test5_chk_2     |(`start_date` >= _utf8mb4\'2005-01-01\')                                                                                             |

def               |hr               |title_chk_1     |(`rating` in (_utf8mb4\'G\',_utf8mb4\'PG\',_utf8mb4\'R\',_utf8mb4\'NC17\',_utf8mb4\'NR\'))                                           |

def               |hr               |title_chk_2     |(`category` in (_utf8mb4\'DRAMA\',_utf8mb4\'COMEDY\',_utf8mb4\'ACTION\',_utf8mb4\'CHILD\',_utf8mb4\'SCIFI\',_utf8mb4\'DOCUMENTARY\'))|

def               |hr               |title_copy_chk_1|(`status` in (_utf8mb4\'AVAILABLE\',_utf8mb4\'DESTROYED\',_utf8mb4\'RENTED\',_utf8mb4\'RESERVED\'))                                  |

def               |class7_2307_sjt  |menu_items_chk_1|(`category` in (_utf8mb4\'Appetizer\',_utf8mb4\'Main Course\',_utf8mb4\'Dessert\',_utf8mb4\'Beverage\'))                             |

ALTER TABLE dept80 MODIFY employee_id int PRIMARY KEY AUTO_INCREMENT;

 

DESC dept80;

Field      |Type         |Null|Key|Default|Extra         |

-----------+-------------+----+---+-------+--------------+

employee_id|int          |NO  |PRI|       |auto_increment|

last_name  |varchar(30)  |NO  |   |       |              |

salary     |bigint       |NO  |   |500    |              |

annsal     |decimal(10,2)|NO  |   |0.00   |              |

start_date |date         |NO  |   |       |              |

job_id     |varchar(9)   |YES |   |       |              |

email      |varchar(30)  |YES |   |미입력    |              |

 

SELECT * FROM information_schema.table_constraints WHERE constraint_schema = 'hr' AND table_name = 'dept80';

CONSTRAINT_CATALOG|CONSTRAINT_SCHEMA|CONSTRAINT_NAME|TABLE_SCHEMA|TABLE_NAME|CONSTRAINT_TYPE|ENFORCED|

------------------+-----------------+---------------+------------+----------+---------------+--------+

def               |hr               |PRIMARY        |hr          |dept80    |PRIMARY KEY    |YES     |

 

SELECT * FROM information_schema.KEY_COLUMN_USAGE WHERE constraint_schema = 'hr' AND table_name = 'dept80';

CONSTRAINT_CATALOG|CONSTRAINT_SCHEMA|CONSTRAINT_NAME|TABLE_CATALOG|TABLE_SCHEMA|TABLE_NAME|COLUMN_NAME|ORDINAL_POSITION|POSITION_IN_UNIQUE_CONSTRAINT|REFERENCED_TABLE_SCHEMA|REFERENCED_TABLE_NAME|REFERENCED_COLUMN_NAME|

------------------+-----------------+---------------+-------------+------------+----------+-----------+----------------+-----------------------------+-----------------------+---------------------+----------------------+

def               |hr               |PRIMARY        |def          |hr          |dept80    |employee_id|               1|                             |                       |                     |                      |

 

• primary key 제약조건의 경우 테이블의 고유한 제약조건으로 제약조건명은 무조건 primary로 고정이다.

ALTER TABLE hr.dept80 DROP PRIMARY KEY;

Field      |Type         |Null|Key|Default|Extra|

-----------+-------------+----+---+-------+-----+

employee_id|int          |NO  |   |       |     |

last_name  |varchar(30)  |NO  |   |       |     |

salary     |bigint       |NO  |   |500    |     |

annsal     |decimal(10,2)|NO  |   |0.00   |     |

start_date |date         |NO  |   |       |     |

job_id     |varchar(9)   |YES |   |       |     |

email      |varchar(30)  |YES |   |미입력    |     |

 

• primary key 제약조건을 제외한 나머지 제약조건들은
  ALTER …
  DROP 제약조건 종류 제약조건명;
  위의 형태로 작성되어야 한다.

 

ALTER TABLE orders DROP FOREIGN KEY orders_ibfk_1;

 

외래키 제약조건 비활성화 / 활성화

• 다음 명령어들은 현재 접속 세션에서만 적용된다. 다른 사용자에게는 영향을 미치지 않으며 실행한 사용자가 접속 중일 때만 적용되는 옵션값이다. 
• 제약조건을 비활성화하려면 아래의 설정값을 변경한다.

SET foreign_key_checks=0;

• 다시 외래키 제약조건을 활성화한다.

SET foreign_key_checks=1;

 

CREATE TABLE

default

• DEFAULT 값으로는 비확정적인 값을 출력하는 함수, 표현식, 서브쿼리를 사용할 수 없다.
• 일반적으로 리터럴 값들만 허용하며, 예외적으로 DATETIME의 데이터타입인 경우 CURRENT_TIMESTAMP를 활용하여 기본값으로 함수의 출력값을 설정하는 것을 허용한다.

CREATE TABLE test_date( id INT PRIMARY KEY AUTO_INCREMENT, order_time DATETIME DEFAULT current_timestamp );

 

• 아래와 같이 입력값들을 모두 비워두더라도 ID 컬럼은 AI 옵션으로 값이 자동으로 채워지며, order_time컬럼은 default옵션으로 기본값이 입력값으로 등록된다.

INSERT INTO test_date VALUES ();

SELECT * FROM test_date;

 

ON UPDATE 옵션절

• 행의 값을 새롭게 갱신 할 때의 기본값을 설정하는 옵션

CREATE TABLE test_date2( id INT PRIMARY KEY AUTO_INCREMENT, order_time DATETIME DEFAULT current_timestamp ON UPDATE current_timestamp );

 

FOREIGN KEY 옵션

ON DELETE

• 외래키관계에서 부모 데이터가 삭제 될 때 동작하는 옵션 설정

ON UPDATE

• 외래키관계에서 부모 데이터가 갱신 될 때 동작하는 옵션 설정

ALTER TABLE students ADD FOREIGN KEY (grade) REFERENCES grades(id) ON DELETE CASCADE ← 부모가 삭제 시 자식 행도 같이 삭제 ON UPDATE CASCADE; ← 부모가 갱신 될 시 자식 행도 같이 갱신

 

옵션값

• NO ACTION : 참조하던 값이 삭제되거나 변경되어도 아무런 작업을 하지 않는다. 이 경우 값이 바뀌는 것을 허용하지 않기 때문에 제약조건을 위배하는 작업을 하게되면 에러가 발생하게 된다. 기본값.
• CASCADE : 부모의 값이 변경되거나 삭제되는 경우, 자식의 값도 종속적으로 부모의 변경된 값으로 바뀌거나 행이 삭제된다. 부모행에 대한 작업을 하게되면 자식 행들도 동일하게 영향을 받게되므로 주의해서 작업 할 것! 정확하게 알고 사용한다면 테이블들의 무결성 유지에 많은 도움을 준다.
• SET NULL : 부모의 값이 변경되거나 삭제되는 경우, 자식의 값은 NULL값으로 바뀌게 된다. 자식컬럼에 NOT NULL이 걸린 경우에는 사용할 수 없으므로 주의!

 

DML

INSERT

입력하는 데이터가 중복일 경우 처리

기존 값을 갱신

• 옵션절 ON DUPLICATE KEY 사용
• 주로 동기화 작업을 할 때 유용하게 사용할 수 있다.

CREATE TABLE insert_test (id int PRIMARY KEY AUTO_INCREMENT, name varchar(30));

 

SELECT * FROM insert_test;

id|name|

--+----+

 1|hi  |

 2|name|

 

• id가 2인 행은 이미 존재하므로 에러가 발생한다.

INSERT INTO insert_test (id, name) VALUES (2, 'name2');

SQL Error [1062] [23000]: Duplicate entry '2' for key 'insert_test.PRIMARY'

 

• 다음의 옵션절을 추가하면 중복된 키값이 발견되면 insert 대신 update로 동작하게 된다.
• 기존 키값 행의 name 컬럼의 값을 values에 작성된 name 컬럼의 입력값으로 갱신한다.

INSERT INTO insert_test (id, name) VALUES (2, 'name2') ON DUPLICATE KEY UPDATE name = VALUES(name);

SELECT * FROM insert_test;

id|name |

--+-----+

 1|hi   |

 2|name2|

입력을 무시

• 경고는 뜨지만 에러코드를 출력하지는 않는다.
• INSERT IGNORE INTO … 사용

INSERT IGNORE INTO insert_test (id, name) VALUES (2, 'name2');

 

백업

복구

 

https://survey.stackoverflow.co/2023