테이블 생성시 제약조건 넣기
1. CHECK
CHECK (조건); 과 같은 식으로 사용한다.
만약 조건에 미달하는 데이터를 넣고자 하면 오류가 발생한다.
CREATE TABLE member (
mem_id CHAR(8) NOT NULL PRIMARY KEY,
height TINYINT UNSIGNED NULL CHECK (height >= 100)
);
INSERT INTO member VALUES('BLK', 163);
INSERT INTO member VALUES('APN', 83); -- 체크 제약조건 오류!2. UNIQUE
고유값 지정. 중복된 값을 넣으면 오류가 발생함.
CREATE TABLE member (
mem_id CHAR(8) NOT NULL PRIMARY KEY,
email CHAR(30) NULL UNIQUE
);
DESCRIBE member;
INSERT INTO member VALUES('BLK', 'pink@gmail.com');
INSERT INTO member VALUES('APN', 'pink@gmail.com'); -- 중복오류!3. DEFAULT
기본값 지정. 구체적인 값을 넣는 대신 DEFAULT값을 넣으면 자동적으로 미리 설정된 값이 넣어진다.
CREATE TABLE member (
mem_id CHAR(8) NOT NULL PRIMARY KEY,
dance_grade CHAR(1) NULL DEFAULT 'C'
);
INSERT INTO member VALUES('BLK', NULL);
INSERT INTO member VALUES('BLK', ); -- 입력값이 없으면 에러!
INSERT INTO member VALUES('TWC', default);
INSERT INTO member VALUES('SPC', 'A');
SELECT * FROM member;
완성된 테이블 수정하기 (ALTER)
1. PRIMARY KEY 지정하기
기존에 있는 컬럼을 PRIMARY KEY 로 지정하고 싶을때 사용하는 방법
ALTER '테이블 이름' ADD CONSTRAINT PRIMARY KEY ('컬럼 이름');
ALTER TABLE member
ADD CONSTRAINT -- 강제로 지정함
PRIMARY KEY (mem_id);
DESCRIBE member; -- 테이블의 컬럼을 설명하도록 하는 명령어2. FOREIGN KEY
ALTER TABLE buy
ADD CONSTRAINT
FOREIGN KEY(mem_id) REFERENCES member(mem_id);기존의 선언은 아래와 같다.
CREATE TABLE buy (
num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
mem_id CHAR(8) NOT NULL,
prod_name CHAR(6) NOT NULL,
FOREIGN KEY(mem_id) REFERENCES member(mem_id)
);
FOREIGN KEY 수정/삭제시 자동 변경 명령어 : CASCADE
만약 외래키로 사용하고 있던 컬럼 값이, 본래 테이블에서 수정되면, 외래키로 사용하고 있던 테이블에서는 값이 변경되지 않는다. 그렇게 되면 사용자가 의도했던 연계가 끊어진것이나 다름없게 된다.
이를 해결하기 위해 사용하는 명령어가 CASCADE(폭포) 다.
이 명령어가 적용된 컬럼값에 수정/ 삭제가 발생하면, 자동적으로 이 컬럼값을 외래키로 사용하고 있던 테이블에서도 값이 수정/ 삭제되는 것이다.
CREATE TABLE buy (
num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
mem_id CHAR(8) NOT NULL,
prod_name CHAR(6) NOT NULL
);
ALTER TABLE buy
ADD CONSTRAINT
FOREIGN KEY(mem_id) REFERENCES member(mem_id)
ON UPDATE CASCADE
ON DELETE CASCADE;member 테이블의 mem_id 컬럼의 값을 buy 테이블의 mem_id란 이름의 외래키로 받아오는데, 업데이트, 삭제시 자동 적용되도록 cascade를 적용한 것이다.
추가/ 수정/ 삭제 시 작동하는 코드: TRIGGER(트리거)
테이블에 데이터가 추가/ 수정/ 삭제가 일어날시, 정해진 쿼리문이 실행되도록 하는 예약어이다.
데이터가 삭제(수정)될때 삭제될 데이터를 다른 테이블에 옮겨놓고 싶다거나, 추가될때마다 특정 쿼리가 실행되기를 원하면 사용한다.
테이블에 부착하고자 하는 트리거를 먼저 선언해야 한다.
프로시저와 형식이 비슷하다.
형식:
DELIMITER $$
CREATER TRIGGER '트리거 이름'
AFTER DELETE( INSERT/ UPDATE) -- 삭제/ 수정/ 추가 정하기
ON '테이블 이름'
FOR EACH ROW -- 각 행마다 적용
BEGIN
'실행 쿼리문'
END $$
DELIMITER;
CREATE TABLE IF NOT EXISTS trigger_table (
id INT,
txt VARCHAR(10)
);
INSERT INTO trigger_table VALUES(1, '홍길동');
INSERT INTO trigger_table VALUES(2, '변사또');
INSERT INTO trigger_table VALUES(3, '춘향이');
SELECT * FROM trigger_table;
-- 트리거 선언 : 테이블에 트리거를 부착한다.
DROP TRIGGER IF EXISTS exitTrigger;
DELIMITER $$
CREATE TRIGGER exitTrigger -- 트리거이름
AFTER DELETE -- 삭제후 작동하도록 지정(DELETE,UPDATE,INSERT)
ON trigger_table -- 트리거를 부착할 테이블지정
FOR EACH ROW -- 각 행마다 적용함
BEGIN
SET @msg = '회원이 탈퇴하였습니다.';
END $$
DELIMITER ;다양한 사용법 예시
1. 쿼리문을 통한 테이블 생성
CREATE TABLE singer
AS
(SELECT mem_id, mem_name, mem_number, addr FROM member);2. 트리거를 통해 업데이트시 값 추가
old는 수정, 삭제가 이뤄지기 전의 값을 의미하는 예약어이다.
----------- 추가할 테이블 선언---------------
DROP TABLE IF EXISTS backup_singer;
CREATE TABLE backup_singer (
mem_id CHAR(8) NOT NULL,
mem_name VARCHAR(10) NOT NULL,
mem_number INT NOT NULL,
addr CHAR(2) NOT NULL,
modType CHAR(2), -- 변경된 타입. '수정' 또는 '삭제'
modDate DATE, -- 변경된 날짜
modUser VARCHAR(30) -- 변경한 사용자. '관리자' 'user_id'
);
----------- 트리거를 통해 추가 --------------
DROP TRIGGER IF EXISTS singer_updateTrigger;
DELIMITER $$
CREATE TRIGGER singer_updateTrigger
AFTER UPDATE
ON singer
FOR EACH ROW
BEGIN
INSERT INTO backup_singer VALUES( OLD.mem_id, OLD.mem_name,
OLD.mem_number, OLD.addr, '수정', CURDATE(), CURRENT_USER() );
END $$
DELIMITER ;3. 트리거를 통해 삭제시 추가
DELIMITER $$
CREATE TRIGGER singer_deleteTrigger
AFTER DELETE
ON singer
FOR EACH ROW
BEGIN
INSERT INTO backup_singer VALUES( OLD.mem_id, OLD.mem_name,
OLD.mem_number, OLD.addr, '삭제', CURDATE(), CURRENT_USER() );
-- 오늘 날짜 -- mySQL계정이름
END $$
DELIMITER ;TRUNCATE: 테이블을 비우는 명령어
가상 테이블 - view
기본적으로 읽기 파일이기에 수정, 삭제가 힘들다(불가능은 아님). 또한 복잡한 sql을 필요한 부분만 취사 선택해 간단하게 만들수 있기에 편하다.
형식
CREATE VIEW '뷰 이름'
AS
SELECT '컬럼 이름1', '컬럼이름2' ..... FROM '테이블 이름';
자주 사용하는 복잡한 쿼리문을 미리 VIEW로 설정해둔다면, 필요할때마다 쉽게 뷰를 통해 값을 얻어올수 있다.
CREATE VIEW v_memberbuy
AS
SELECT b.mem_id, m.mem_name, b.prod_name, m.addr,
CONCAT(m.phone1, m.phone2) '연락처' -- 5개의 컬럼
FROM buy b
INNER JOIN member m
ON b.mem_id = m.mem_id;
SELECT * FROM v_memberbuy WHERE mem_name = '블랙핑크';미리 자주 사용하는 컬럼값을 뷰로 설정해두면 편하다.
