뷰는 기본적으로 읽기전용으로 많이 사용되지만 뷰를 통해서 원 테이블의 데이터를 수정할 수도 있습니다.
뷰의 장점으로는 보안에 도움이 된다는 점이 있습니다.
특정사용자에게 어떤 테이블의 모든 정보가 아닌 공개가능한 정보만을 뷰로 생성하여 제공할 수 있기 때문입니다.
그리고 복잡한 조인쿼리등을 단순화 시켜주기도 합니다.
매번 조인을 해서 검색하던 자료를 뷰로 한번 생성해 놓으면 그 후로는 그 뷰를 select로 조회하기만 하면 되기 때문입니다.
뷰 생성하기
USE DB명; -- 사용할 DB명
CREATE VIEW v_userbuyTBL -- v_userbyTBL 뷰 생성
AS
SELECT U.userid AS 'USER ID', U.name AS 'USER NAME', B.prodName AS 'PRODUCT NAME',
U.addr, CONCAT(U.mobile1, U.mobile2) AS 'MOBILE PHONE'
FROM userTBL U
INNER JOIN buyTBL B -- userTBL 과 buyTBL 내부조인
ON U.userid = B.userid; -- userid로 조인
SELECT `USER ID`, `USER NAME` FROM v_userbuyTBL; -- 주의! 백틱을 사용한다. 만들어진 뷰에서 select
뷰 수정하기
ALTER VIEW v_userbuyTBL -- v_userbuyTBL 뷰 수정
AS
SELECT U.userid AS '사용자 아이디', U.name AS '이름', B.prodName AS '제품 이름',
U.addr, CONCAT(U.mobile1, U.mobile2) AS '전화 번호' -- as(별칭)을 한글로 수정
FROM userTBL U
INNER JOIN buyTBL B -- 내부조인 (userTBL-buyTBL)
ON U.userid = B.userid ; -- userid를 기준으로
SELECT `이름`,`전화 번호` FROM v_userbuyTBL; -- `(백틱)주의. 수정된 뷰를 select
뷰 삭제하기
DROP VIEW 뷰이름;
뷰 정보확인
DESCRIBE 뷰이름;
뷰 소스코드확인
SHOW CREATE VIEW 뷰이름;
뷰로 원본 테이블 수정, 입력이 불가능한 경우
원본에 NOT NULL인 값이 뷰에 생성되어 있지 않다면 입력 불가능. 뷰를 통해 NOT NULL 열을 입력할 수 없음.
집계함수를 사용한 뷰(SUM값 등)
UNION ALL, JOIN 등을 사용한 뷰
DISTINCT, GROUP BY 등을 사용한 뷰
조건으로 만들어진 뷰에 INSERT하기 및 WITH CHECK OPTION
운동선수 테이블에서 180cm 이상인 선수만 뷰로 생성합니다.
CREATE VIEW v_height180 -- v_height180 뷰 생성
AS
SELECT * FROM userTBL WHERE height >= 180 ; -- userTBL(원테이블)에서 180 이상만 선택
SELECT * FROM v_height180 ;
위와 같이 입력 시 입력은 되지만 뷰에서는 조회가 안되고 원본 테이블에서는 조회가 되는 상황이 발생합니다.
이를 방지하고자 조건으로 만들어진 뷰에 조건에 맞지 않는 데이터가 인서트 될 경우 에러를 만드는 방법이 "WITH CHECK OPTION" 입니다.
CREATE VIEW v_height180 -- v_height180 뷰 생성
AS
SELECT * FROM userTBL WHERE height >= 180 -- userTBL(원테이블)에서 180 이상만 선택
WITH CHECK OPTION; -- 인서트시 조건에 맞는지 체크하기
이 옵션을 추가해서 뷰를 생성 시 180cm 미만의 선수가 입력될 경우 에러가 발생합니다.
뷰가 참조하는 테이블 삭제 시
뷰가 참조하는 원본 테이블들을 삭제합니다.
DROP TABLE IF EXISTS 테이블명1, 테이블명2; -- (테이블이 존재한다면) 테이블명1, 테이블명2 삭제
뷰를 조회 할 경우 에러가 납니다. 참조하는 테이블이 없기 때문에 조회 할 수 없다는 에러가 발생합니다.
임시테이블은 현재 접속한 세션내에서만 존재할 수 있는 테이블입니다. 세션이 닫히면 자동으로 삭제됩니다.
또한 임시 테이블은 생성한 클라이언트에서만 접근이 가능합니다.
임시테이블은 데이터베이스안의 기존의 테이블과 동일한 이름으로 생성할 수 있습니다.
동일한 이름의 임시테이블이 존재하는 동안에는 기존 테이블에는 접근이 불가능합니다.
임시테이블이 삭제되는 시점은 아래와 같습니다.
사용자가 DROP TABLE로 직접삭제
HeidiSQL을 종료 or 클라이언트 프로그램을 종료하면 삭제됨
MariaDB 서비스를 재시작하면 삭제됨
임시 테이블을 만드는 방법은 아래와 같습니다.
USE DB명; -- DB사용
CREATE TEMPORARY TABLE IF NOT EXISTS 임시테이블명 -- 임시테이블(이 존재하면 삭제하고) 생성
(
id INT, name CHAR(7)
);
DESCRIBE 임시테이블명; -- 임시테이블 상세
INSERT INTO 임시테이블명 VALUES (1, 'This'); -- 임시테이블에 값 입력
SELECT * FROM 임시테이블명; -- 임시테이블 조회
DROP TABLE 임시테이블명 ; -- 임시테이블 삭제
테이블 삭제
테이블 삭제는 아래와 같이 간단한 명령어로 가능합니다.
DROP TABLE 테이블명 ; -- 테이블 삭제
주의할 사항은 외래키 제약조건의 기준 테이블은 삭제할 수가 없습니다.
먼저, 왜래키가 생성된 외래키 테이블을 삭제해야만 합니다.
여러 테이블을 동시에 삭제하는 방법은 아래와 같습니다.
DROP TABLE 테이블1, 테이블2, 테이블3 ; -- 테이블1~3 삭제
테이블 수정
테이블의 수정은 ALTER TABLE 문으로 사용합니다. 이미 생성되 테이블을 추가, 변경, 수정, 삭제 가능합니다.
열의 추가
USE DB명; -- DB명 사용
ALTER TABLE userTBL -- 테이블 수정
ADD homepage VARCHAR(30) -- 열추가(홈페이지)
DEFAULT 'http://www.hanbit.co.kr' -- 디폴트값
NULL; -- Null 허용함
열은 기본적으로 제일 뒤에 추가됩니다.
순서를 지정하고 싶다면 제일 뒤에 'FIRST' 또는 'AFTER 열이름'을 지정하면 됩니다.
FIRST는 제일앞에 추가되고, AFTER 열이름은 열이름 다음에 추가됩니다.
열의 삭제
ALTER TABLE userTBL -- 테이블 수정
DROP COLUMN mobile1; -- 모바일1 열을 삭제
만약 제약조건이 걸린 연을 삭제할 경우는 제약조건을 먼저 삭제한 후에 열을 삭제해야합니다.
열의 이름 및 데이터 형식 변경
ALTER TABLE userTBL -- 테이블 수정
CHANGE COLUMN name uName VARCHAR(20) NULL ; -- name열 변경, 열이름을 uName으로, 데이터형식을 VARCHAR(20)으로, NULL허용으로 변경
제약조건이 걸려있는 열은 문제가 발생할 수 있습니다.
열의 제약 조건 추가 및 삭제
ALTER TABLE userTBL -- userTBL 테이블 변경
ADD CONSTRAINT PRIMARY KEY (userID); -- 추가 강제 기본키 (열이름)
ALTER TABLE userTBL -- 테이블 수정
DROP PRIMARY KEY; -- 기본키 제약조건 삭제
ALTER TABLE buyTBL
DROP FOREIGN KEY FK_userTBL_buyTBL; -- 외래키 제약조건 삭제
기본키를 삭제할 경우 기본키가 다른 테이블의 외래키와 연결되어 있다면 외래키를 먼저 삭제하고 기본키를 삭제해야 합니다.
CHECK 제약조건(MariaDB 10.2.1부터 지원, MySQL 8.0.16부터 지원)
DEFAULT 정의
NULL 값 허용
이번 포스팅에서는 UNIQUE, CHECK, DEFAULT NULL 제약조건에 대해서 설명하겠습니다.
UNIQUE 제약조건
UNIQUE 제약조건은 중복되지 않는 유일한 값을 입력해야 하는 조건 입니다.
이것은 기본키와 거의 비슷하지만 차이점은 UNIQUE는 NULL 값을 허용합니다.
대표적으로 E-MAIL 열을 UNIQUE로 많이 사용합니다.
아래는 테이블 생성시 UNIQUE 제약조건을 넣는 방법 2가지 예 입니다.
USE DB명; -- 사용할 DB선택
-- UNIQUE 제약 방법1
DROP TABLE IF EXISTS buyTBL, userTBL; -- 테이블이 존재하면 삭제
CREATE TABLE userTBL -- 테이블 생성
( userID CHAR(8) NOT NULL PRIMARY KEY, -- 기본키, NOT NULL
name VARCHAR(10) NOT NULL,
birthYear INT NOT NULL,
email CHAR(30) NULL UNIQUE -- UNIQUE, NULL 허용
);
-- UNIQUE 제약 방법2
DROP TABLE IF EXISTS userTBL; -- 테이블이 존재하면 삭제
CREATE TABLE userTBL -- 테이블 생성
( userID CHAR(8) NOT NULL PRIMARY KEY, -- 기본키, NOT NULL
name VARCHAR(10) NOT NULL,
birthYear INT NOT NULL,
email CHAR(30) NULL , -- NULL 허용
CONSTRAINT AK_email UNIQUE (email) -- 이름을 AK_email로 변경하고 UNIQUE로 제약(email을)
);
CHECK 제약조건
CHECK 제약 조건은 데이트럴 점검하는 기능입니다.
열 값이 입력될 때 특정 수치 이상은 입력을 못하게 막거나, 특정 시점 이후의 날짜만 입력가능하게 하거나 하는 조건을 걸수 있습니다.
아래는 출생년도의 제약조건과, 이름의 공란을 허용하지 않도록 하는 예제입니다.
DROP TABLE IF EXISTS userTBL; -- 테이블이 존재하면 삭제
CREATE TABLE userTBL -- 테이블 생성
( userID CHAR(8) PRIMARY KEY, -- 기본키
name VARCHAR(10) ,
birthYear INT CHECK (birthYear >= 1900 AND birthYear <= 2020), -- CHECK 제약조건 (생년이 1900이상 2020이하)
mobile1 char(3) NULL,
CONSTRAINT CK_name CHECK ( name IS NOT NULL) -- name의 이름을 CK_name으로 변경, NOT NULL로 변경
);
다음은 위에서 만든 mobile1(국번)에 CHECK 제약조건으로 수정하는 방법입니다.
ALTER TABLE userTbl -- 테이블 변경
ADD CONSTRAINT CK_mobile1 -- 추가, 이름을 CK_mobile1로 변경
CHECK (mobile1 IN ('010','011','016',,'019')) ; -- mobile1 안에 값은 010, 011, 016, 019만 입력되도록 제약
CHECK 제약 조건을 설정한 후에는, 제약조건에 위배되는 값은 입력이 안됩니다.
CHECK 제약 조건을 무시하려면 시스템 변수 중 check_constraint_checks 값을 0으로 설정해야 합니다.
SET check_constraint_checks = 0; -- CHECK 제약조건 비활성화
SET check_constraint_checks = 1; -- CHECK 제약조건 활성화
DEFAULT 정의
DEFAULT는 값을 입력하지 않을 경우, 자동으로 입력되는 기본값을 정의 하는 방법입니다.
아래 예는 출생연도를 입력하지 않으면 -1을 입력,
주소를 입력하지 않으면 "경기도"을 입력,
키를 입력하지 않으면 "175"가 입력되도록 한 DEFAULT 정의 입니다.
DROP TABLE IF EXISTS userTBL; -- 테이블이 존재하면삭제
CREATE TABLE userTBL -- 테이블 생성
( userID char(8) NOT NULL PRIMARY KEY,
name varchar(10) NOT NULL,
birthYear int NOT NULL DEFAULT -1, -- NOT NULL, 디폴트값 -1
addr char(2) NOT NULL DEFAULT '경기도', -- NOT NULL, 디폴트값 경기도
mobile1 char(3) NULL,
mobile2 char(8) NULL,
height smallint NULL DEFAULT 175, -- NULL 허용, 디폴트값 175
mDate date NULL
);
다른 방법으로는 ALTER 테이블로 변경하는 방법도 있습니다.
DROP TABLE IF EXISTS userTBL;
CREATE TABLE userTBL
( userID char(8) NOT NULL PRIMARY KEY,
name varchar(10) NOT NULL,
birthYear int NOT NULL ,
addr char(2) NOT NULL,
mobile1 char(3) NULL,
mobile2 char(8) NULL,
height smallint NULL,
mDate date NULL
);
ALTER TABLE userTBL -- 테이블 변경
ALTER COLUMN birthYear SET DEFAULT -1; -- 출생연도 디폴트값 -1
ALTER TABLE userTBL
ALTER COLUMN addr SET DEFAULT '경기도'; -- 주소 디폴트값 경기도
ALTER TABLE userTBL
ALTER COLUMN height SET DEFAULT 175; -- 키 디폴트값 175
디폴트값이 설정된 열에는 아래와 같은 방법으로 데이터 입력이 가능합니다.
-- default 문은 DEFAULT로 설정된 값을 자동 입력됩니다.
INSERT INTO userTBL VALUES ('KHJ', '김혜정', default, default, '011', '1234567', default, '2021.11.11');
-- 열이름이 명시되지 않으면 DEFAULT로 설정된 값을 자동 입력됩니다.
INSERT INTO userTBL(userID, name) VALUES('LKJ', '이경진');
-- 모든 값을 직접 입력하면 DEFAULT로 설정된 값은 무시됩니다.
INSERT INTO userTBL VALUES ('PSH', '박수환', 1981, '서울', '019', '9876543', 176, '2022.4.2');
NULL 값 허용
열 값에 NULL을 허용할지 허용하지 않을 지를 정할 수 있습니다. (NULL, NOT NULL)
PRIMARY KEY(기본키)의 경우는 반드시 NOT NULL 이어야 합니다.
PRIMARY KEY(기본키)열에 NOT NULL을 생략해도 NOT NULL로 인식됩니다.
CHECK 제약조건(MariaDB 10.2.1부터 지원, MySQL 8.0.16부터 지원)
DEFAULT 정의
NULL 값 허용
이번 포스팅에서는 외래키 제약조건에 대해서 설명하겠습니다.
외래키 제약조건
외래키 제약조건은 두 테이블 사이의 관계를 선언함으로써, 데이터의 무결성을 보장해 주는 역할을 합니다.
외래 키 관계를 설정하면 하나의 테이블이 다른 테이블에 의존하게 됩니다.
외래키 테이블에 데이터를 입력할 때는 꼭 기준 테이블을 참조해서 입력하므로, 기준 테이블에 이미 데이터가 존재해야 합니다.
기준 테이블의 기본키 열에 있는 값들만 현재테이블의 외래키 값으로 사용할 수 있고 다른 값을 넣으면 에러가 발생합니다.
외래키 테이블이 참조하는 기준 테이블의 열은 반드시 PRIMARY KEY(기본키)이거나, UNIQUE 제약 조건이 설정되어 있어야 합니다.
외래키 설정 방법은 아래 코드처럼 외래키 테이블에서 설정을 합니다.
DROP TABLE IF EXISTS buyTBL, userTBL; -- 테이블이 존재할 경우 삭제(DROP)
CREATE TABLE userTBL
( userID CHAR(8) NOT NULL PRIMARY KEY, -- 기본키로 설정, 기본키는 반드시 NOT NULL
name VARCHAR(10) NOT NULL,
birthYear INT NOT NULL
);
CREATE TABLE buyTBL
( num INT AUTO_INCREMENT NOT NULL PRIMARY KEY , -- 기본키, NOT NULL, 자동증가 설정
userID CHAR(8) NOT NULL,
prodName CHAR(6) NOT NULL,
FOREIGN KEY(userID) REFERENCES userTBL(userID) -- 이 테이블의 외래키는 userID이고, userTBL 테이블의 userID 와 연결
);
외래키의 이름을 "FK_기준테이블명_외래키있는테이블명"으로 설정해두면 나중에 이름만 보고도 어떤 관계인지 알 수가 있습니다.
이렇게 외래키의 이름을 입력하고 싶다면 아래 처럼 "CONSTRAINT 외래키이름"을 추가해주면 됩니다.
(입력을 안할 경우 자동으로 이름이 랜덤하게 생성됨)
DROP TABLE IF EXISTS buyTBL;
CREATE TABLE buyTBL
( num INT AUTO_INCREMENT NOT NULL PRIMARY KEY ,
userID CHAR(8) NOT NULL,
prodName CHAR(6) NOT NULL,
CONSTRAINT FK_userTBL_buyTBL FOREIGN KEY(userID) REFERENCES userTBL(userID) -- CONSTRAINT 외래키이름
);
생성된 외래키 이름이 잘 들어갔는제 SELECT문으로 확인해 봅니다.
select * from information_schema.table_constraints where table_name = 'buyTBL'; -- 테이블 정보확인
외래키 이름 확인
이미 생성된 테이블에 외래키를 지정하는 방법도 존재합니다.
ALTER TABLE 구문을 이용하는 방법입니다.
DROP TABLE IF EXISTS buyTBL, userTBL;
CREATE TABLE userTBL
( userID CHAR(8) NOT NULL PRIMARY KEY,
name VARCHAR(10) NOT NULL,
birthYear INT NOT NULL
);
CREATE TABLE buyTBL
( num INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
userID CHAR(8) NOT NULL,
prodName CHAR(6) NOT NULL
);
ALTER TABLE buyTBL -- 테이블 수정
ADD CONSTRAINT FK_userTBL_buyTBL -- 제약조건 추가. 외래키 이름을 설정
FOREIGN KEY (userID) -- 외래키 제약조건을 userID에 설정
REFERENCES userTBL(userID); -- 참조할 기준 테이블은 userTBL 테이블의 userID 열로 설정
외래키 옵션중에 ON DELETE CASCADE 또는 ON UPDATE CASCADE가 있습니다.
이는 기준 테이블의 데이터 값이 변경되었을때 외래키 테이블의 데이터 값도 자동으로 변경되도록 설정해줍니다.
예를들어 ON UPDATE CASCADE로 설정했을경우 기준테이블의 참조된 기본키값이 "AAA"에서 "BBB"로 수정된다면 외래키가 설정된 테이블의 외래키값도 "BBB"로 자동으로 수정됩니다.
설정방법은 아래 코드 처럼 값을 넣어 줍니다.
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; -- 기준 테이블의 참조된 기본키값이 변경 시 buyTBL의 userID 값도 함께 변경
