0. 학습 목표
- 테이블의 구조에 대해 완벽하게 이해
- 테이블의 핵심인 제약조건을 학습하고 적절하게 지정
- 뷰의 개념과 실제 작동하는 방법
0. 요약
- CREATE TABLE은 테이블을 생성하는 SQL로 테이블 이름, 열, 데이터 형식 등을 지정
- 열에 입력될 값이 1부터 자동 증가하도록 설정하려며 GUI에서는 AI를 체크하고 SQL에서는 AUTO_INCREMENT를 입력
- 열에 빈 값을 허용하지 않으려면 GUI에서는 NN, SQL에서는 NOT NULL
- 열을 기본 키로 지정하려면 GUI에서는 PK, SQL에서는 PRIMARY KEY
- 열을 외래 키로 지정하려면 FOREIGN KEY 예약어를 입력
- 기본 키는 행 데이터 중에서 데이터를 구분할 수 있는 식별자 역할을 함
- 외래 키는 두 테이블의 관계를 연결, 외래 키가 설정된 열은 꼭 다른 테이블의 기본키와 연결됨
- 고유 키는 중복되지 않은 유일한 값, 기본 키와 차이점은 NULL을 허용
- 체크는 입력되는 데이터를 점검하는 기능
- 기본값은 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정
- NULL값을 허용하려면 생략하거나 NULL을 사용, 허용하지 않으려면 NOT NULL을 사용
- 모든 데이터베이스 개체는 테이블과 관련있지만 특히 뷰는 테이블에 직접 접근하므로 밀접하게 연관됨
- 뷰는 가상의 테이블이며 SELECT문으로 구성
- 하나의 테이블과 관련되면 단순 뷰, 2개 이상은 복합뷰이며 복합뷰는 수정할 수 없음
- 뷰는 필요한 부분에만 접근하도록 하여 보안에 도움이 됨
| 용어 | 설명 |
| CREATE VIEW | 뷰를 생성하는 SQL |
| 별칭 | 뷰에서 사용할 열의 이름의 별칭 설정 |
| 백틱 | 뷰를 조회할 때 빈 칸이 있으면 붙여줘야 함 |
| ALTER VIEW | 뷰를 수정하는 SQL |
| DROP VIEW | 뷰를 삭제하는 SQL |
| CREATE OR REPLACE VIEW | 기존에 뷰가 있으면 덮어쓰고 없으면 생성 |
| DESCRIBE | 뷰 또는 테이블의 정보를 조회하는 SQL |
| SHOW CREATE VIEW | 뷰의 소스 코드를 보여주는 SQL |
| WITH CHECK OPTION | 뷰에 설정된 조건만 입력되도록 지정하는 SQL |
| CHECK TABLE | 뷰 또는 테이블의 상태를 확인하는 SQL |
3. 가상의 테이블: 뷰
※핵심 키워드: 데이터베이스 개체, 뷰, SELECT, 단순 뷰, 복합 뷰, 보안
뷰는 데이터베이스 개체 중 하나이다. 뷰는 한 번 생성해 놓으면 테이블이라고 생각하고 사용해도 될 정도로 사용자들의 입장에서는 테이블과 거의 동일한 개체로 취급한다.
뷰는 테이블처럼 데이터를 가지고 있지 않고 SELECT 문으로 만들어져 있기 때문에 뷰에 접근하는 순간 SELECT가 실행되고 그 결과가 화면에 출력되는 방식이다.
- 단순 뷰: 하나의 테이블과 연관된 뷰
- 복합 뷰: 2개 이상의 테이블과 연관된 뷰
3-1. 뷰의 개념
뷰 실습을 위해 인터넷 마켓 데이터 베이스를 생성한 후 진행하자.
DROP DATABASE IF EXISTS market_db; -- 만약 market_db가 존재하면 우선 삭제한다.
CREATE DATABASE market_db;
USE market_db;
CREATE TABLE member -- 회원 테이블
( mem_id CHAR(8) NOT NULL PRIMARY KEY, -- 사용자 아이디(PK)
mem_name VARCHAR(10) NOT NULL, -- 이름
mem_number INT NOT NULL, -- 인원수
addr CHAR(2) NOT NULL, -- 지역(경기,서울,경남 식으로 2글자만입력)
phone1 CHAR(3), -- 연락처의 국번(02, 031, 055 등)
phone2 CHAR(8), -- 연락처의 나머지 전화번호(하이픈제외)
height SMALLINT, -- 평균 키
debut_date DATE -- 데뷔 일자
);
CREATE TABLE buy -- 구매 테이블
( num INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 순번(PK)
mem_id CHAR(8) NOT NULL, -- 아이디(FK)
prod_name CHAR(6) NOT NULL, -- 제품이름
group_name CHAR(4) , -- 분류
price INT NOT NULL, -- 가격
amount SMALLINT NOT NULL, -- 수량
FOREIGN KEY (mem_id) REFERENCES member(mem_id)
);
INSERT INTO member VALUES('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015.10.19');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남', '055', '22222222', 163, '2016.08.08');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기', '031', '33333333', 166, '2015.01.15');
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울', NULL, NULL, 160, '2015.04.21');
INSERT INTO member VALUES('GRL', '소녀시대', 8, '서울', '02', '44444444', 168, '2007.08.02');
INSERT INTO member VALUES('ITZ', '잇지', 5, '경남', NULL, NULL, 167, '2019.02.12');
INSERT INTO member VALUES('RED', '레드벨벳', 4, '경북', '054', '55555555', 161, '2014.08.01');
INSERT INTO member VALUES('APN', '에이핑크', 6, '경기', '031', '77777777', 164, '2011.02.10');
INSERT INTO member VALUES('SPC', '우주소녀', 13, '서울', '02', '88888888', 162, '2016.02.25');
INSERT INTO member VALUES('MMU', '마마무', 4, '전남', '061', '99999999', 165, '2014.06.19');
INSERT INTO buy VALUES(NULL, 'BLK', '지갑', NULL, 30, 2);
INSERT INTO buy VALUES(NULL, 'BLK', '맥북프로', '디지털', 1000, 1);
INSERT INTO buy VALUES(NULL, 'APN', '아이폰', '디지털', 200, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '아이폰', '디지털', 200, 5);
INSERT INTO buy VALUES(NULL, 'BLK', '청바지', '패션', 50, 3);
INSERT INTO buy VALUES(NULL, 'MMU', '에어팟', '디지털', 80, 10);
INSERT INTO buy VALUES(NULL, 'GRL', '혼공SQL', '서적', 15, 5);
INSERT INTO buy VALUES(NULL, 'APN', '혼공SQL', '서적', 15, 2);
INSERT INTO buy VALUES(NULL, 'APN', '청바지', '패션', 50, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '지갑', NULL, 30, 1);
INSERT INTO buy VALUES(NULL, 'APN', '혼공SQL', '서적', 15, 1);
INSERT INTO buy VALUES(NULL, 'MMU', '지갑', NULL, 30, 4);
SELECT * FROM member;
SELECT * FROM buy;뷰의 기본 생성
간단한 회원 테이블을 조회하되, mem_id, mem_name, addr만 SELECT문으로 조회하자.
USE market_db;
SELECT mem_id, mem_name, addr FROM member;
# mem_id, mem_name, addr
'APN', '에이핑크', '경기'
'BLK', '블랙핑크', '경남'
'GRL', '소녀시대', '서울'
'ITZ', '잇지', '경남'
'MMU', '마마무', '전남'
'OMY', '오마이걸', '서울'
'RED', '레드벨벳', '경북'
'SPC', '우주소녀', '서울'
'TWC', '트와이스', '서울'
'WMN', '여자친구', '경기'뷰의 실체는 SELECT문이 된다. 실제 뷰를 만드는 기본적인 형식은 다음과 같다.
CREATE VIEW 뷰_이름
AS
SELECT 문;뷰를 만든 후에는 테이블을 검색하는 조건과 동일하게 SELECT를 사용한다. 조건식도 동일하게 사용할 수 있다.
SELECT 열_이름 FROM 뷰_이름
[WHERE 조건];위 회원 테이블을 조회하였던 것을 뷰로 만들어 보자.
USE market_db;
CREATE VIEW v_member
AS
SELECT mem_id, mem_name, addr FROM member;
SELECT * FROM v_member;
# mem_id, mem_name, addr
'APN', '에이핑크', '경기'
'BLK', '블랙핑크', '경남'
'GRL', '소녀시대', '서울'
'ITZ', '잇지', '경남'
'MMU', '마마무', '전남'
'OMY', '오마이걸', '서울'
'RED', '레드벨벳', '경북'
'SPC', '우주소녀', '서울'
'TWC', '트와이스', '서울'
'WMN', '여자친구', '경기'조건 식도 넣어보자.
SELECT mem_name, addr FROM member;
# mem_name, addr
'에이핑크', '경기'
'블랙핑크', '경남'
'소녀시대', '서울'
'잇지', '경남'
'마마무', '전남'
'오마이걸', '서울'
'레드벨벳', '경북'
'우주소녀', '서울'
'트와이스', '서울'
'여자친구', '경기'뷰의 작동
사용자는 뷰를 테이블이라고 생각하고 접근한다. 뷰는 기본적으로 읽기 전용이지만 몇 가지 조건을 만족한다면 수정도 가능하다.
뷰를 사용하는 이유
뷰는 테이블과 거의 동일하게 접근이 가능하다. 하지만 테이블 대신에 뷰를 사용하는 이유는 무엇일까?
- 보안에 도움이 된다.
: 개인정보에는 일반 사용자들이 접근할 필요가 없다. 하지만 위에서 만든 뷰는 일반적인 내용만을 포함하고 있어 이에 대한 접근이 필요할 수 있다. 이때 개인정보를 제외한 열만 뷰로 만들어서 접근하도록 한다면 보안에 도움이 된다. - 복잡한 SQL을 단순하게 만들 수 있다.
: 조인을 통해 물건을 구매한 회원들의 SQL을 예제로 살펴보자.
: 이것을 뷰로 생성하고 WHERE 절을 사용하여 블랙핑크 구매 기록을 조회해 보자.SELECT B.mem_id, M.mem_name, B.prod_name, M.addr, CONCAT(phone1, phone2) '연락처' FROM buy B INNER JOIN member M ON B.mem_id = M.mem_id;
CREATE VIEW v_memberbuy AS SELECT B.mem_id, M.mem_name, B.prod_name, M.addr, CONCAT(phone1, phone2) '연락처' FROM buy B INNER JOIN member M ON B.mem_id = M.mem_id; SELECT * FROM v_memberbuy WHERE mem_name = '블랙핑크'; # mem_id, mem_name, prod_name, addr, 연락처 'BLK', '블랙핑크', '지갑', '경남', '05522222222' 'BLK', '블랙핑크', '맥북프로', '경남', '05522222222' 'BLK', '블랙핑크', '청바지', '경남', '05522222222'
3-2. 뷰의 실제 작동
뷰의 실제 생성, 수정, 삭제
기본적인 뷰를 생성하면서 뷰에서 사용될 열 이름을 테이블과 다르게 지정 가능하다. 별칭은 띄어쓰기가 있는 경우 ', "로 묶어주면 되며 형식상 AS를 붙여주면 코드가 명확해 보인다. 책과는 다른 점은 별칭을 띄어쓰기로 진행하였을 때 정상적으로 출력되지 않아 띄어쓰기가 없는 별칭으로 수정했다.
USE market_db;
CREATE VIEW v_viewtest1
AS
SELECT B.mem_id member_id, M.mem_name AS member_name,
B.prod_name AS prod_name, CONCAT(M.phone1, M.phone2) AS office_phone
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id;
SELECT * FROM v_viewtest1;
# member_id, member_name, prod_name, office_phone
'BLK', '블랙핑크', '지갑', '05522222222'
'BLK', '블랙핑크', '맥북프로', '05522222222'
'APN', '에이핑크', '아이폰', '03177777777'
'MMU', '마마무', '아이폰', '06199999999'
'BLK', '블랙핑크', '청바지', '05522222222'
'MMU', '마마무', '에어팟', '06199999999'
'GRL', '소녀시대', '혼공SQL', '0244444444'
'APN', '에이핑크', '혼공SQL', '03177777777'
'APN', '에이핑크', '청바지', '03177777777'
'MMU', '마마무', '지갑', '06199999999'
'APN', '에이핑크', '혼공SQL', '03177777777'
'MMU', '마마무', '지갑', '06199999999'DISTINCT를 이용하여 구매 이력이 있는 member_id, member_name 별칭으로 찾아보자.
SELECT DISTINCT member_id, member_name FROM v_viewtest1;
# member_id, member_name
'APN', '에이핑크'
'BLK', '블랙핑크'
'GRL', '소녀시대'
'MMU', '마마무'열 이름/별칭을 한글로도 가능하지만 한글 운영 체제 외에는 인식되지 않을 수도 있으므로 권장하지는 않는다.
ALTER VIEW v_viewtest1
AS
SELECT B.mem_id 회원아이디, M.mem_name AS 회원이름,
B.prod_name AS 제품이름, CONCAT(M.phone1, M.phone2) AS 연락처
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id;
SELECT * FROM v_viewtest1;
# 회원아이디, 회원이름, 제품이름, 연락처
'APN', '에이핑크', '아이폰', '03177777777'
'APN', '에이핑크', '혼공SQL', '03177777777'
'APN', '에이핑크', '청바지', '03177777777'
'APN', '에이핑크', '혼공SQL', '03177777777'
'BLK', '블랙핑크', '지갑', '05522222222'
'BLK', '블랙핑크', '맥북프로', '05522222222'
'BLK', '블랙핑크', '청바지', '05522222222'
'GRL', '소녀시대', '혼공SQL', '0244444444'
'MMU', '마마무', '아이폰', '06199999999'
'MMU', '마마무', '에어팟', '06199999999'
'MMU', '마마무', '지갑', '06199999999'
'MMU', '마마무', '지갑', '06199999999'
SELECT DISTINCT 회원아이디, 회원이름 FROM v_viewtest1;
# 회원아이디, 회원이름
'APN', '에이핑크'
'BLK', '블랙핑크'
'GRL', '소녀시대'
'MMU', '마마무'뷰는 삭제도 역시 가능하다.
DROP VIEW v_viewtest1;뷰의 정보 확인
기존에 생성된 뷰에 대한 정보를 확인할 수 있다. 우선 간단한 뷰를 만들자. 여기서 CREATE OR REPLACE는 기존에 뷰가 있으면 덮어씌우고 없다면 생성한다.
CREATE OR REPLACE VIEW v_viewtest2
AS
SELECT mem_id, mem_name, addr FROM member;DESCRIBE로 뷰의 정보를 확인하자.
DESCRIBE v_viewtest2;
# Field, Type, Null, Key, Default, Extra
'mem_id', 'char(8)', 'NO', '', NULL, ''
'mem_name', 'varchar(10)', 'NO', '', NULL, ''
'addr', 'char(2)', 'NO', '', NULL, ''뷰도 테이블과 동일하게 정보를 보여주지만 PRIMARY KEY등의 정보는 확인되지 않는다. member 테이블과 비교하면 확실하게 확인 가능하다.
DESCRIBE member;
# Field, Type, Null, Key, Default, Extra
'mem_id', 'char(8)', 'NO', 'PRI', NULL, ''
'mem_name', 'varchar(10)', 'NO', '', NULL, ''
'mem_number', 'int', 'NO', '', NULL, ''
'addr', 'char(2)', 'NO', '', NULL, ''
'phone1', 'char(3)', 'YES', '', NULL, ''
'phone2', 'char(8)', 'YES', '', NULL, ''
'height', 'smallint', 'YES', '', NULL, ''
'debut_date', 'date', 'YES', '', NULL, ''SHOW CREATE VIEW문으로 뷰의 소스 코드를 확인할 수 있다. 더 자세한 정보를 확인할 수 있다.
SHOW CREATE VIEW v_viewtest2;
# View, Create View, character_set_client, collation_connection
'v_viewtest2', 'CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_viewtest2` AS select `member`.`mem_id` AS `mem_id`,`member`.`mem_name` AS `mem_name`,`member`.`addr` AS `addr` from `member`', 'utf8mb4', 'utf8mb4_0900_ai_ci'뷰를 통한 데이터의 수정/삭제
뷰를 통해 테이블의 데이터를 수정할 수 있다. v_member 뷰를 통해 데이를 수정해 보자. 정상적으로 수정된다.
UPDATE v_member SET addr = '부산' WHERE mem_id = 'BLK';
SELECT * FROM v_member WHERE mem_id = 'BLK';
# mem_id, mem_name, addr
'BLK', '블랙핑크', '부산'이번에는 데이터를 입력해 보자.
INSERT INTO v_member (mem_id, mem_name, addr) VALUES ('BTS', '방탄소년단', '경기');
18:29:24 INSERT INTO v_member (mem_id, mem_name, addr) VALUES ('BTS', '방탄소년단', '경기') Error Code: 1423. Field of view 'market_db.v_member' underlying table doesn't have a default value 0.000 secv_member가 참조하는 member 테이블 중 mem_number열은 NOT NULL로 되어 있기 때문에 반드시 입력해야 하나 v_member는 mem_number를 참조하지 않으므로 입력할 방법이 없다. 만약 입력해야 한다면 아래와 같이 설정해야 한다.
- v_member에 mem_number열을 포함하도록 열을 재 정의
- member의 mem_number열의 속성을 NULL로 변경
- member의 mem_number열에 DEFAULT 값 추가
이번에는 삭제를 확인하기 위해 평균 키 height가 167이상인 뷰를 생성하자.
CREATE VIEW v_height167
AS
SELECT * FROM member WHERE height >= 167;
SELECT * FROM v_height167;
# mem_id, mem_name, mem_number, addr, phone1, phone2, height, debut_date
'GRL', '소녀시대', '8', '서울', '02', '44444444', '168', '2007-08-02'
'ITZ', '잇지', '5', '경남', NULL, NULL, '167', '2019-02-12'
'TWC', '트와이스', '9', '서울', '02', '11111111', '167', '2015-10-19'2가지를 삭제해 보자. 키가 167미만인 데이터를 삭제 해보자. 데이터가 없기 때문에 삭제될 데이터도 없다.
DELETE FROM v_height167 WHERE height < 167;
18:35:44 DELETE FROM v_height167 WHERE height < 167 0 row(s) affected 0.000 sec그리고 키가 168인 데이터를 삭제해 보자. 해당 열은 외래키와 연결되어 있기 때문에 에러가 발생한다.
DELETE FROM v_height167 WHERE height = 168;
18:36:17 DELETE FROM v_height167 WHERE height = 168 Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`market_db`.`buy`, CONSTRAINT `buy_ibfk_1` FOREIGN KEY (`mem_id`) REFERENCES `member` (`mem_id`)) 0.016 sec뷰를 통한 데이터의 입력
이번에는 v_height167 뷰에서 키가 167 미만인 데이터를 입력해 보자.
INSERT INTO v_height167 VALUES('TRA', '티아라', 6, '서울', NULL, NULL, 159, '2005-01-01');뷰에서는 키가 159이기 때문에 당연이 보이지 않는다.
SELECT * FROM v_height167;
# mem_id, mem_name, mem_number, addr, phone1, phone2, height, debut_date
'GRL', '소녀시대', '8', '서울', '02', '44444444', '168', '2007-08-02'
'ITZ', '잇지', '5', '경남', NULL, NULL, '167', '2019-02-12'
'TWC', '트와이스', '9', '서울', '02', '11111111', '167', '2015-10-19'뷰는 167이상만 보여주는데 159가 입력된다는게 바람직하지는 않은 것 같다. 167 이상의 데이터만 입력 가능하도록 수정하는 것이 바람직한 것 같다.
이럴 때 예약어인 WITH CHECK OPTION을 통해 뷰에 설정된 값의 범위가 벗어나는 값은 입력되지 않도록 할 수 있다.
ALTER VIEW v_height167
AS
SELECT * FROM member WHERE height >= 167
WITH CHECK OPTION;
INSERT INTO v_height167 VALUES('TOB', '텔레토비', 4, '영국', NULL, NULL, 140, '1995-01-01');
19:29:34 INSERT INTO v_height167 VALUES('TOB', '텔레토비', 4, '영국', NULL, NULL, 140, '1995-01-01') Error Code: 1369. CHECK OPTION failed 'market_db.v_height167' 0.000 sec복합 뷰
두개 이상의 테이블로 만든 뷰를 복합 뷰라고 한다. 복합 뷰는 주로 두 테이블을 조인한 결과를 뷰로 만들 때 사용한다.
복합 뷰는 읽기 전용으로 데이터를 입력/수정/삭제할 수 없다.
CREATE VIEW v_complex
AS
SELECT B.mem_id, M.mem_name, B.prod_name, M.addr
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id;뷰가 참조하는 테이블의 삭제
뷰가 참조하는 테이블을 삭제해 보자. member, buy모두 삭제하자.
DROP TABLE IF EXISTS member, buy;당연이 뷰는 조회되지 않는다. 참조할 테이블이 없기 때문이다.
SELECT * FROM v_height167;
19:49:34 SELECT * FROM v_height167 LIMIT 0, 1000 Error Code: 1356. View 'market_db.v_height167' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them 0.000 sec뷰가 조회되지 않으면 CHECK TABLE 문으로 뷰의 상태를 확인할 수 있다. 뷰가 참조하는 테이블이 없어서 오류가 발생하는 것을 확인할 수 있다.
CHECK TABLE v_height167;
# Table, Op, Msg_type, Msg_text
'market_db.v_height167', 'check', 'Error', 'View \'market_db.v_height167\' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them'
'market_db.v_height167', 'check', 'error', 'Corrupt''혼자공부하는 SQL' 카테고리의 다른 글
| 6. 인덱스-2 (0) | 2022.02.10 |
|---|---|
| 6. 인덱스-1 (0) | 2022.02.09 |
| 5. 테이블과 뷰-2 (0) | 2022.02.03 |
| 5. 테이블과 뷰-1 (0) | 2022.02.03 |
| 4. SQL 고급 문법-3 (0) | 2022.01.29 |
