서브쿼리를 사용한 테이블 생성

• 서브쿼리의 출력 결과가 새로운 테이블로 복사되어 생성된다.
• 테이블 생성에서 사용되는 서브쿼리의 컬럼 리스트에는 컬럼, 표현식, 함수를 사용할 수 있다
• 새롭게 생성되는 테이블의 컬럼명은 서브쿼리가 출력해주는 컬럼의 이름값으로 사용하게된다.
• 다만, 표현식, 함수 경우에는 컬럼명으로는 사용할 수 없는 기호나 공백의 문자가 들어가게 되므로 column alias를 필수로 사용해야 한다.

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

• 생성된 테이블의 데이터타입과 크기값은 서브쿼리에서 출력에 사용된 테이블의 정보를 사용하여 설정된다.

SELECT * FROM dept80;

employee_id|last_name |annsal |hire_date |
-----------+----------+---------+----------+
145|Russell |168000.00|1996-10-01|
146|Partners |162000.00|1997-01-05|
147|Errazuriz |144000.00|1997-03-10|
148|Cambrault |132000.00|1999-10-15|
149|Zlotkey |126000.00|2000-01-29|
150|Tucker |120000.00|1997-01-30|

• 다만 제약조건은 NOT NULL을 제외하고는 복사되지 않는다.

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

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

테이블 수정(ALTER TABLE)

• 기존 테이블의 구조를 변형하는데 사용하는 문법

ADD 절

• 새로운 컬럼, 제약조건들을 추가할 때 사용하는 절

• dept80 테이블에 job_id 컬럼을 추가

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 | | | |

• 추가된 컬럼에 default의 설정이 없는 경우 기본적으로 각 행은 NULL값을 가진 상태로 추가된다.

SELECT * FROM dept80;

employee_id|last_name |annsal |hire_date |job_id|
-----------+----------+---------+----------+------+
145|Russell |168000.00|1996-10-01| |
146|Partners |162000.00|1997-01-05| |
147|Errazuriz |144000.00|1997-03-10| |
148|Cambrault |132000.00|1999-10-15| |
149|Zlotkey |126000.00|2000-01-29| |
150|Tucker |120000.00|1997-01-30| |
…

• 추가하는 컬럼에 default값이 있는 경우 NULL값 대신 default값을 채우게 된다.

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


SELECT * FROM dept80;

employee_id|last_name |annsal |hire_date |job_id|email|
-----------+----------+---------+----------+------+-----+
145|Russell |168000.00|1996-10-01| |미입력 |
146|Partners |162000.00|1997-01-05| |미입력 |
147|Errazuriz |144000.00|1997-03-10| |미입력 |
148|Cambrault |132000.00|1999-10-15| |미입력 |
149|Zlotkey |126000.00|2000-01-29| |미입력 |
…

• 추가될 컬럼의 위치 지정

FIRST

• 추가된 컬럼의 위치가 테이블의 첫번째자리가 된다.

ALTER TABLE dept80
ADD emp_number int FIRST;

AFTER

• 특정 컬럼의 다음 순서로 새로운 컬럼을 위치시킨다.

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 | |미입력 | |