[SQL] 20. DDL, DML

⛏️/Mysql | Oracle

[SQL] 20. DDL, DML

defyuil 2023. 11. 29. 13:56

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;

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

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

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

SELECT * FROM dept80;

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

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;

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

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

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

SELECT * FROM dept80;

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

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;

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

emp_number |int |YES | | | |

salary |int |NO | |300 | |

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

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

MODIFY

DESC dept80;

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

emp_number |int |YES | | | |

salary |int |NO | |300 | |

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

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;

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

emp_number |int |YES | | | |

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

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

ALTER TABLE dept80
MODIFY salary bigint DEFAULT 500 NOT NULL;

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

emp_number |int |YES | | | |

salary |bigint |NO | |500 | |

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

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

RENAME

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

ALTER TABLE dept80
RENAME COLUMN hire_date TO start_date;

DESC dept80;

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

emp_number |int |YES | | | |

salary |bigint |NO | |500 | |

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

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

DROP

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

DESC dept80;

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

emp_number |int |YES | | | |

salary |bigint |NO | |500 | |

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

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

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

ALTER TABLE dept80
DROP emp_number;

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

salary |bigint |NO | |500 | |

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

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;

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

employee_id|int |NO |PRI| |auto_increment|

salary |bigint |NO | |500 | |

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

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

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

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

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

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

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

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

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

ALTER TABLE hr.dept80
DROP PRIMARY KEY;

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

employee_id|int |NO | | | |

salary |bigint |NO | |500 | |

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

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