파트 6-2. 테이블 압축, 임시테이블, 삭제 및 수정
1. 테이블 압축
테이블 압축 기능은 대용량 테이블의 공간을 절약하는 효과를 갖는다.
-- 형식
row_format = compressed;
CREATE DATABASE IF NOT EXISTS compressDB;
USE compressDB;
CREATE TABLE normalTBL( emp_no int , first_name varchar(14));
CREATE TABLE compressTBL( emp_no int , first_name varchar(14))
ROW_FORMAT=COMPRESSED ;
-- 앞으로 이 테이블에 입력될 데이터는 '내부적'으로 압축
-- 이후의 사용에 있어서는 아무 신경 안써도 된다. 알아서 압축되어 저장된다.
INSERT INTO normalTbl
SELECT emp_no, first_name FROM employees.employees;
INSERT INTO compressTBL
SELECT emp_no, first_name FROM employees.employees;
SHOW TABLE STATUS FROM compressDB; -- 해당 데이터베이스의 테이블 정보를 보여준다.
2. 임시 테이블
임시 테이블은 이름처럼 임시로 잠깐 사용되는 테이블이다. 사실 그냥 테이블을 만들고 사용하고 drop을 해도 된다. 하지만 이럴 경우 계속 누적이 되고, 나중에는 어느것이 임시로 사용했던 테이블인지 인식하기 어렵다.
임시 테이블이 삭제되는 시점은 다음과 같다.
- 사용자가 drop table로 직접 삭제
- workbench를 종료하거나 mysql 클라이언트를 종료하면 삭제
- mysql 서비스가 재시작되면 삭제
임시 테이블은 세션 내에서만 존재하며, 세션이 닫히면 자동으로 삭제된다. mysql은 여러 명이 접속 가능한데 현재 내가 임시 테이블을 만들면 나한테만 보인다는 의미이다. 또한, 임시 테이블은 데이터베이스 내의 다른 테이블과 동일한 이름을 사용할 수 있다. 이럴 경우에는 기존 테이블은 임시 테이블이 있는 동안에는 접근이 불가능하고, 무조건 임시 테이블로 접근할 수 있다. 따라서, 임시 테이블은 다른 이름으로 주는 것이 좋다. 보통은 이름 앞에 temp를 붙여서 사용한다.
-- 형식
-- 그냥 테이블 만드는 것에 temporary만 추가
-- 테이블 이름앞에는 임시 테이블임을 명시하기 위해 temp를 붙이는게 좋다.
create temporary table [if not exists] 테이블이름
(열 정의 ...)
-- 예시
CREATE TEMPORARY TABLE IF NOT EXISTS temptbl (id INT, name CHAR(5));
3. 테이블 삭제
테이블 삭제는 아래와 같이 간단하다. 주의할 사항은 외래 키(foreign key) 제약 조건의 기준 테이블을 삭제하고 싶은 경우, 먼저 외래 키가 생성된 외래 키 테이블을 삭제하고 기준 테이블을 삭제해야 한다.
drop table 테이블이름;
-- 여러 테이블 삭제 가능
drop table 테이블1, 테이블2, .... ;
4. 테이블 수정
테이블 수정은 alter table문을 사용한다. 앞서 제약 조건을 추가할 경우에 alter table구문을 사용해 왔는데, 이미 생성된 테이블에 무언가를 추가/변경/삭제하는 것은 모두 alter table을 사용한다.
열의 추가
열 추가는 기본적으로 가장 뒤에 열로 추가된다. 순서를 지정하려면 제일 뒤에 ‘first’ 또는 ‘after 열이름’을 지정하면 된다. first는 제일 앞에 열이 추가되고, after 열이름은 열이름 뒤에 추가된다.
alter table usertbl -- usertbl 수정
add homepage varchar(30) -- 열 추가
default 'http:...' -- 디폴트값
null; -- null 허용
열의 삭제
제약 조건이 걸린 열을 삭제할 경우에는 제약 조건을 먼저 삭제한 후에 열을 삭제해야 한다.
alter table usertbl
drop column mobile1;
열 이름 및 데이터 형식 변경
alter table usertbl
-- 열이름 name을 uname으로 변경하고 데이터 형식 varchar로 변경하고 null허용
change column name uname varchar(20) null;
열의 제약 조건 추가 및 삭제
-- 먼저 외래 키 삭제
alter table buytbl
drop foreign key buytbl_ibfk_1;
-- 연결된 기본 키 삭제
alter table usertbl
drop primary key;
5. 전체 실습
지금까지 배운 기능을 전체적으로 실습해보자.
-- 실습을 위한 테이블 정보 입력
USE tabledb;
DROP TABLE IF EXISTS buytbl, usertbl;
CREATE TABLE usertbl
( userID CHAR(8),
name VARCHAR(10),
birthYear INT,
addr CHAR(2),
mobile1 CHAR(3),
mobile2 CHAR(8),
height SMALLINT,
mDate DATE
);
CREATE TABLE buytbl
( num int AUTO_INCREMENT PRIMARY KEY,
userid CHAR(8),
prodName CHAR(6),
groupName CHAR(4),
price INT ,
amount SMALLINT
);
INSERT INTO usertbl VALUES('LSG', '이승기', 1987, '서울', '011', '1111111', 182, '2008-8-8');
INSERT INTO usertbl VALUES('KBS', '김범수', NULL, '경남', '011', '2222222', 173, '2012-4-4');
INSERT INTO usertbl VALUES('KKH', '김경호', 1871, '전남', '019', '3333333', 177, '2007-7-7');
INSERT INTO usertbl VALUES('JYP', '조용필', 1950, '경기', '011', '4444444', 166, '2009-4-4');
INSERT INTO buytbl VALUES(NULL, 'KBS', '운동화', NULL , 30, 2);
INSERT INTO buytbl VALUES(NULL,'KBS', '노트북', '전자', 1000, 1);
INSERT INTO buytbl VALUES(NULL,'JYP', '모니터', '전자', 200, 1);
INSERT INTO buytbl VALUES(NULL,'BBK', '모니터', '전자', 200, 5);
-- 여기까지 실습 정보 입력 완료
-- primary key 추가
ALTER TABLE usertbl
ADD CONSTRAINT PK_usertbl_userID
PRIMARY KEY (userID);
DESC usertbl;
-- 외래 키 추가 -> 오류 발생 -> buytbl의 userid가 usertbl에 없는 경우
ALTER TABLE buytbl
ADD CONSTRAINT FK_usertbl_buytbl
FOREIGN KEY (userID)
REFERENCES usertbl (userID);
-- 문제가 되는 행 삭제
DELETE FROM buytbl WHERE userid = 'BBK';
-- 다시 외래 키 연결
ALTER TABLE buytbl
ADD CONSTRAINT FK_usertbl_buytbl
FOREIGN KEY (userID)
REFERENCES usertbl (userID);
-- 오류 발생 -> 지금 외래 키가 연결되어 있는데 삽입되는 정보가 usertable userid에 없음
INSERT INTO buytbl VALUES(NULL,'BBK', '모니터', '전자', 200, 5);
-- 외래 키가 연결되있는 상태에서 일단 구매 테이블에 정보를 다 넣고 나중에 만들고 싶은 경우
-- 방법은 외래 키를 비활성화하고 다 넣은 다음 다시 활성화
SET foreign_key_checks = 0; -- 외래 키 비활성화
INSERT INTO buytbl VALUES(NULL, 'BBK', '모니터', '전자', 200, 5);
INSERT INTO buytbl VALUES(NULL, 'KBS', '청바지', '의류', 50, 3);
INSERT INTO buytbl VALUES(NULL, 'BBK', '메모리', '전자', 80, 10);
INSERT INTO buytbl VALUES(NULL, 'SSK', '책' , '서적', 15, 5);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책' , '서적', 15, 2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '청바지', '의류', 50, 1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화', NULL , 30, 2);
INSERT INTO buytbl VALUES(NULL, 'EJW', '책' , '서적', 15, 1);
INSERT INTO buytbl VALUES(NULL, 'BBK', '운동화', NULL , 30, 2);
SET foreign_key_checks = 1; -- 외래 키 활성화
-- 열 제약 조건 추가 -> 오류 발생 -> 이미 조건에 부합하지 않는 데이터가 저장되어 있음
ALTER TABLE userTBL
ADD CONSTRAINT CK_birthYear
CHECK ( (birthYear >= 1900 AND birthYear <= 2023) AND (birthYear IS NOT NULL) );
-- 정보 수정
UPDATE usertbl SET birthYear=1979 WHERE userID='KBS';
UPDATE usertbl SET birthYear=1971 WHERE userID='KKH';
-- 다시 열 제약 조건 추가
ALTER TABLE userTBL
ADD CONSTRAINT CK_birthYear
CHECK ( (birthYear >= 1900 AND birthYear <= 2023) AND (birthYear IS NOT NULL) );
-- 오류 발생-> check 제약 조건에 안맞기 때문
INSERT INTO usertbl VALUES('TKV', '태권뷔', 2999, '우주', NULL , NULL , 186, '2023-12-12');
-- insert 성공
INSERT INTO usertbl VALUES('SSK', '성시경', 1979, '서울', NULL , NULL , 186, '2013-12-12');
INSERT INTO usertbl VALUES('LJB', '임재범', 1963, '서울', '016', '6666666', 182, '2009-9-9');
INSERT INTO usertbl VALUES('YJS', '윤종신', 1969, '경남', NULL , NULL , 170, '2005-5-5');
INSERT INTO usertbl VALUES('EJW', '은지원', 1972, '경북', '011', '8888888', 174, '2014-3-3');
INSERT INTO usertbl VALUES('JKW', '조관우', 1965, '경기', '018', '9999999', 172, '2010-10-10');
INSERT INTO usertbl VALUES('BBK', '바비킴', 1973, '서울', '010', '0000000', 176, '2013-5-5');
-- userid 수정 -> 오류 발생 -> 외래 키와 연결되어 있어서 바꿀 수 없음
UPDATE usertbl SET userID = 'VVK' WHERE userID='BBK';
-- 외래 키 비활성화 후 수정하고 다시 활성화
SET foreign_key_checks = 0;
UPDATE usertbl SET userID = 'VVK' WHERE userID='BBK';
SET foreign_key_checks = 1;
-- 조건대로 조회
SELECT B.userid, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
FROM buytbl B
INNER JOIN usertbl U
ON B.userid = U.userid ;
-- buytbl의 행 개수 조회 -> 바로 위 select문이랑 조회 개수가 다름
-- 위에서 vvk를 bbk로 바꿨는데 구매테이블은 여전히 bbk이기에 연결이 안되었기 때문
SELECT COUNT(*) FROM buytbl;
-- 기존 foreign key를 삭제하고
-- 재설정하여 update cascade 사용해서 기준되는 테이블 수정시 연결된 외래 키 테이블도 같이 수정되도록
ALTER TABLE buytbl
DROP FOREIGN KEY FK_usertbl_buytbl;
ALTER TABLE buytbl
ADD CONSTRAINT FK_usertbl_buytbl
FOREIGN KEY (userID)
REFERENCES usertbl (userID)
ON UPDATE CASCADE;
-- 이제 수정시 같이 수정됨
UPDATE usertbl SET userID = 'VVK' WHERE userID='BBK';
SELECT B.userid, U.name, B.prodName, U.addr, CONCAT(U.mobile1, U.mobile2) AS '연락처'
FROM buytbl B
INNER JOIN usertbl U
ON B.userid = U.userid
ORDER BY B.userid;
-- 삭제불가능 -> 외래 키와 연결되있기 때문
DELETE FROM usertbl WHERE userID = 'VVK';
-- 외래 키 정보를 삭제하고
-- 외래키를 재설정해주면서 기준 테이블 정보가 삭제되면 연결된 외래 테이블 정보도 같이 삭제되도록
-- on delete cascade 사용
ALTER TABLE buytbl
DROP FOREIGN KEY FK_usertbl_buytbl;
ALTER TABLE buytbl
ADD CONSTRAINT FK_usertbl_buytbl
FOREIGN KEY (userID)
REFERENCES usertbl (userID)
ON UPDATE CASCADE
ON DELETE CASCADE;
-- 이제 같이 삭제됨
DELETE FROM usertbl WHERE userID = 'VVK';
SELECT * FROM buytbl ;
-- check 제약 조건이 걸린 열은 그냥 삭제 가능
-- 즉, pk fk관계만 삭제할 때 고려해주면 된다.
ALTER TABLE usertbl
DROP COLUMN birthYear ;
