[SQL] 5. 테이블과 뷰
*해당 내용은 우재남 저자님의 <혼자 공부하는 SQL>을 보고 정리한 내용입니다.
테이블
표 형태로 구성된 2차원 구조로 행과 열로 구성된다.
- 행: 로우(row)나 레코드(record)라고 부른다.
- 열: 컬럼(column) 또는 필드(field)라고 부른다.
테이블은 마이크로소프트 엑셀의 시트와 거의 비슷한 구조로 이루어진다.
데이터 형식 지정
GUI 환경에서 테이블 만들기
*GUI: Graphical User Interface의 약자로, 윈도에서 진행하는 작업을 의미
데이터베이스(naver_db)와 테이블(buy, member) 생성
데이터 입력
SQL로 테이블 만들기
데이터베이스 생성
DROP DATABASE IF EXISTS naver_db;
CREATE DATABASE naver_db;
테이블 생성
-member 테이블
USE naver_db;
DROP TABLE IF EXISTS member;
CREATE TABLE member -- 회원 테이블
(mem_id CHAR(8) NOT NULL PRIMARY KEY, -- 회원 아이디(PK)
mem_name VARCHAR(10) NOT NULL, -- 이름
mem_number TINYINT NOT NULL, -- 인원수
addr CHAR(2) NOT NULL, -- 주소(경기, 서울, 경남 식으로 2글자만 입력)
phone1 CHAR(3) NULL, -- 연락처의 국번(02, 031, 055 등)
phone2 CHAR(8) NULL, -- 연락처의 나머지 전화번호(하이픈 제외)
height TINYINT UNSIGNED NULL, -- 평균 키
debut_date DATE NULL -- 데뷔 일자
);
-buy 테이블
DROP TABLE IF EXISTS buy;
CREATE TABLE buy -- 구매 테이블
(num INT AUTO_INCREMENT NOT NULL PRIMARY KEY, -- 순번(PK)
mem_id CHAR(10) NOT NULL, -- 아이디(FK)
prod_name CHAR(6) NOT NULL, -- 제품 이름
group_name CHAR(4) NULL, -- 분류
price INT UNSIGNED NOT NULL, -- 가격
amount SMALLINT UNSIGNED NOT NULL, -- 수량
FOREIGN KEY(mem_id) REFERENCES member(mem_id)
);- AUTO_INCREMENT로 지정한 열은 PRIMARY KEY나 UNIQUE로 꼭 지정해야 한다.
- mem_id 열은 buy, member 테이블 둘 다 존재해야 한다.
데이터 입력하기
-member 테이블
INSERT INTO member VALUES('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015-10-19');
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남', '055', '22222222', 163, '2016-8-8');
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기', '031', '33333333', 166, '2015-1-15');
-buy 테이블
INSERT INTO buy VALUES( NULL, 'BLK', '지갑', NULL, 30, 2);
INSERT INTO buy VALUES( NULL , 'BLK', '맥북프로', '디지털', 1000, 1);
제약조건의 기본 개념과 종류
제약조건은 데이터의 무결성을 지키기 위해 제한하는 조건이다. 데이터의 무결성이란 '데이터에 결함이 없음'을 의미한다.
결함을 미리 방지하기 위해 회원 테이블의 아이디를 기본 키로 지정한다. 이때 기본 키의 조건은 '중복되지 않고, 비어 있지도 않음'이다.
MySQL에서 제공하는 대표적인 제약조건
- PRIMARY KEY 제약조건
- FOREIGN KEY 제약조건
- UNIQUE 제약조건
- CHECK 제약조건
- DEFAULT 정의
- NULL 값 허용
기본 키 제약조건
테이블에는 많은 행 데이터가 있는데, 이 중에서 데이터를 구분할 수 있는 식별자는 기본 키(Primary Key)라고 부른다.
- ex) 회원 테이블 아이디, 학생 테이블의 학번, 직원 테이블의 사번 등
기본 키에 입력되는 값은 중복될 수 없으며, NULL 값이 입력될 수 없다. 기본 키로 생성한 것은 자동으로 클러스터형 인덱스가 생성되고 테이블은 기본 키를 1개만 가질 수 있다.
CREATE TABLE에서 설정하는 기본 키 제약조건
1) 열 이름 뒤에 PRIMARY KEY를 붙여준다.
USE naver_db;
DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
(mem_id CHAR(8) NOT NULL PRIMARY KEY,
mem_name VARCHAR(10) NOT NULL,
height TINYINT UNSIGNED NULL
);- DESCRIBE 문을 사용하여 테이블의 정보 살펴볼 수 있다.
DESCRIBE member;
2) 제일 마지막 행에 PRIMARY KEY(mem_id) 추가한다.
USE naver_db;
DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
(mem_id CHAR(8) NOT NULL,
mem_name VARCHAR(10) NOT NULL,
height TINYINT UNSIGNED NULL
PRIMARY KEY (mem_id)
);
ALTER TABLE에서 설정하는 기본 키 제약조건
- member를 변경
- 제약조건을 추가
- mem_id 열에 기본 키 제약조건 설정
DROP TABLE IF EXISTS member;
CREATE TABLE member
(mem_id CHAR(8) NOT NULL,
mem_name VARCHAR(10) NOT NULL,
height TINYINT UNSIGNED NULL
);
ALTER TABLE member
ADD CONSTRAINT
PRIMARY KEY(mem_id);
외래 키 제약조건
외래 키 제약조건은 두 테이블 사이의 관계를 연결해주고, 그 결과 데이터의 무결성을 보장해주는 역할을 한다. 외래 키가 설정된 열은 꼭 다른 테이블의 기본 키와 연결되어야 한다.
- 기본 키가 있는 회원 테이블: 기준 테이블
- 외래 키가 있는 구매 테이블: 참조 테이블
참조 테이블이 참조하는 기준 테이블의 열은 반드시 기본 키나, 고유 키로 설정되어 있어야 한다.
CREATE TABLE에서 설정하는 외래 키 제약조건
CREATE TABLE 끝에 FOREIGN KEY 키워드를 설정한다.
- 외래 키의 형식: FOREIGN KEY(열_이름) REFERENCES 기준_테이블(열_이름)
DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
(mem_id CHAR(8) NOT NULL PRIMARY KEY,
mem_name VARCHAR(10) NOT NULL,
height TINYINT UNSIGNED NULL
);
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)
);*기준 테이블의 열 이름과 참조 테이블의 열 이름이 꼭 같지 않아도 된다.
ALTER TABLE에서 설정하는 외래 키 제약조건
- buy 수정
- 제약 조건 추가
- 외래 키 제약조건을 buy 테이블의 mem_id에 설정
- 참조할 기준 테이블은 member 테이블의 mem_id 열임
DROP TABLE IF EXISTS buy;
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 문이다. (기준 테이블의 데이터가 삭제되면 참조 테이블의 데이터도 삭제 되는 기능이다.)
DROP TABLE IF EXISTS buy;
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;INSERT INTO buy VALUES (NULL, 'BLK', '지갑');
INSERT INTO buy VALUES (NULL, 'BLK', '맥북');
UPDATE member SET mem_id = 'PINK' WHERE mem_id = 'BLK'; -- BLK를 PINK로 변경
DELETE FROM member WHERE mem_id = 'PINK'; -- mem_id가 PINK인 행 삭제고유 키 제약조건
'중복되지 않는 유일한 값'을 입력해야 하는 조건으로 기본 키 제약조건과 거의 비슷하지만, 차이점은 고유 키 제약조건은 NULL값 허용한다. 기본 키는 테이블에 1개만 설정해야 하지만, 고유 키는 여러 개 설정 가능하다.
DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
(mem_id CHAR(8) NOT NULL PRIMARY KEY,
mem_name VARCHAR(10) NOT NULL,
height TINYINT UNSIGNED NULL,
email CHAR(30) NULL UNIQUE
);INSERT INTO member VALUES('BLK', '블랙핑크', 163, 'pink@gmail.com');
INSERT INTO member VALUES('TWC', '트와이스', 167, NULL);
INSERT INTO member VALUES('APN', '에이핑크', 164, 'pink@gmail.com');- 위 코드는 이메일이 중복되기에 오류가 뜬다.
체크 제약조건
입력되는 데이터를 점검하는 기능으로 열 정의 뒤에 CHECK(조건)을 추가하면 된다
- EX) 평균 키에 마이너스 값이 입력되지 않도록 하거나, 연락처의 국번에 02, 031, 041, 055 중 하나만 입력되도록 할 수 있다.
DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
(mem_id CHAR(8) NOT NULL PRIMARY KEY,
mem_name VARCHAR(10) NOT NULL,
height TINYINT UNSIGNED NULL CHECK(height >= 100),
phone CHAR(3) NULL
);
INSERT INTO member VALUES('BLK', '블랙핑크', 163, NULL);
INSERT INTO member VALUES('TWC', '트와이스', 99, NULL);- 위 코드는 체크 제약조건에서 설정한 값의 범위를 벗어났기 때문에 오류 발생한다.
필요하다면 테이블을 만든 후 ALTER TABLE 문으로 제약조건을 추가해도 된다.
ALTER TABLE member
ADD CONSTRAINT
CHECK (phone1 IN ('02', '031', '032', '054', '055', '061'));- 위 코드는 IN() 괄호 안에 있는 값 중 하나와 같아야 참이 된다.
기본값 정의
값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해 놓는 방법이다.
DROP TABLE IF EXISTS buy, member;
CREATE TABLE member
(mem_id CHAR(8) NOT NULL PRIMARY KEY,
mem_name VARCHAR(10) NOT NULL,
height TINYINT UNSIGNED NULL DEFAULT 160,
phone CHAR(3) NULL
);- 위 코드는 키를 입력하지 않고도 기본적으로 160이라고 입력된다.
- ALTER TABLE 사용 시 열에 DEFAULT를 지정하기 위해서는 ALTER COLUMN 문을 사용한다.
ALTER TABLE member
ALTER COLUMN phone1 SET DEFAULT '02';
INSERT INTO member VALUES('RED', '레드벨벳', 161, '054');
INSERT INTO member VALUES('SPC', '우주소녀', default, default);
널 값 허용
널 값 허용하려면 생략하거나 NULL 사용하고, 허용하지 않으려면 NOT NULL 사용한다. 다만 PRIMARY KET가 설정된 열에는 NULL 값이 있을 수 없으므로 생략하면 자동으로 NOT NULL로 인식한다. NULL 값은 '아무 것도 없다'라는 의미로 공백(' ')이나 0과는 다르다.
가상의 테이블: 뷰
뷰는 데이터베이스 개체 중 하나로 테이블과 아주 밀접하게 연관되어 있다. 한 번 생성해 놓으면 테이블이라고 생각하고 사용해도 될 정도로 사용자들 입장에서는 테이블과 거의 동일한 개체로 취급된다. 뷰는 테이블처럼 데이터를 가지고 있지 않고 뷰의 실체는 SELECT문으로 만들어져 있기 때문에 뷰에 접근하는 순간 SELECT가 실행되고 그 결과가 화면에 출력되는 방식이다.
단순 뷰와 복합 뷰로 나뉜다.
- 단순 뷰: 하나의 테이블과 연관된 뷰
- 복합 뷰: 2개 이상의 테이블과 연관된 뷰
뷰의 기본 생성
SELECT 문으로 아이디, 이름, 주소를 가져와 출력된 결과를 보면 결국 테이블의 모양을 가지고 있다. 즉 뷰의 실체기 SELECT 문이 된다.
위 예제의 실행 결과를 v_member라고 부르면, 앞으로는 v_member를 그냥 테이블이라고 생각하고 접근하면 된다.
*뷰의 이름만 보고도 뷰인지 알아볼 수 있도록 이름 앞에 v_를 붙이는 것이 일반적이다.
뷰를 만드는 형식
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;
뷰의 작동
사용자는 뷰를 테이블이라고 생각하고 접근한다. 그러면 MySQL이 뷰 안에 있는 SELECT를 실행해서 그 결과를 사용자에게 보내주므로 사용자 입장에서는 1번과 4번만, 즉 뷰에서 모두 처리된 것으로 이해한다. 뷰는 기본적으로 '읽기 전용'으로 사용되지만, 뷰를 통해서 원본 테이블의 데이터를 수정 가능하다.
뷰를 사용하는 이유
- 보안에 도움이 된다: 앞의 예에서 만든 v_member 뷰에는 사용자의 아이디, 이름, 주소만 있을 뿐 사용자의 중요한 개인 정보인 연락처, 평균 키, 데뷔 일자 등의 정보는 들어 있지 않다. 따라서 특정 사용자가 테이블에 접근하지 못하도록 막고, 필요한 부분에만 접근하도록 함으로써 보안에 도움이 된다.
- 복잡한 SQL을 단순하게 만들기 가능하다: 아래 코드는 내용이 길고 복잡하다.
SELECT B.mem_id, M.mem_name, B.prod_name, M.addr, CONCAT(M.phone1, M.phone2) '연락처'
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id;
위 코드를 아래와 같이 뷰로 생성해 놓고 사용자들은 해당 뷰에만 접근하도록 하면, 복잡한 SQL을 입력할 필요가 없어진다.
CREATE VIEW v_memberbuy AS
SELECT B.mem_id, M.mem_name, B.prod_name, M.addr, CONCAT(M.phone1, M.phone2) '연락처'
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id;SELECT * FROM v_memberbuy WHERE mem_name = '블랙핑크';
뷰의 실제 생성, 수정, 삭제
기본적인 뷰를 생성하면서 뷰에서 사용될 열 이름을 테이블과 다르게 지정 가능하다.. 별칭을 사용하면 되는데, 중간에 띄어쓰기를 사용할 수 있다. 별칭은 열 이름 뒤에 작은/큰 따옴표로 묶어주고, 형식상 AS를 붙여준다. 뷰를 조회할 때는 열 이름에 공백이 있으면 백틱(')으로 묶어줘야 한다.
USE market_db;
CREATE VIEW v_viewtest1
AS
SELECT B.mem_id 'Member ID', M.mem_name AS 'Member Name',
B.prod_name "Product Name",
CONCAT(M.phone1, M.phone2) AS "Office Phone"
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id;
SELECT DISTINCT `Member ID`, `Member Name` FROM v_viewtest1; -- 백틱을 사용
뷰의 수정은 ALTER VIEW 구문 사용하고, 열 이름에 한글을 사용해도 된다.
ALTER VIEW v_viewtest1
AS
SELECT B.mem_id '회원 아이디', M.mem_name AS '회원 이름',
B.prod_name "제품 이름",
CONCAT(M.phone1, M.phone2) AS "연락처"
FROM buy B
INNER JOIN member M
ON B.mem_id = M.mem_id;
SELECT DISTINCT `회원 아이디`, `회원 이름` FROM v_viewtest1; -- 백틱을 사용
뷰의 삭제는 DROP VIEW 사용한다.
DROP VIEW v_viewtest1;
뷰의 정보 확인
DESCRIBE 문으로 기존 뷰의 정보 확인 가능하다.
DESCRIBE v_viewtest2;
SHOW CREATE VIEW 문으로 뷰의 소스 코드도 확인 가능하다.
SHOW CREATE VIEW v_viewtest2;
뷰를 통한 데이터의 수정/삭제
아래 코드는 오류 없이 수정된다.
UPDATE v_member SET addr = '부산' WHERE mem_id='BLK' ;
하지만 아래 코드는 오류가 발생한다.
INSERT INTO v_member(mem_id, mem_name, addr) VALUES('BTS','방탄소년단','경기') ;- v_member(뷰)가 참조하는 member(테이블)의 열 중에서 mem_number 열은 NOT NULL로 설정되어서 반드시 입력해줘야 한다. 하지만 v_member에서는 mem_number 열을 참조하고 있지 않으므로 값을 입력할 방법이 없다.
- 만약 v_member 뷰를 통해서 member 테이블에 값을 입력하고 싶다면 v_member에 mem_number 열을 포함하도록 뷰를 재정의하거나, 아니면 member에서 mem_number 열의 속성을 NULL로 바꾸거나, 기본값(Default)을 지정해야 한다.
CREATE VIEW v_height167
AS
SELECT * FROM member WHERE height >= 167 ;
SELECT * FROM v_height167 ;
DELETE FROM v_height167 WHERE height < 167;
뷰를 통한 데이터의 입력
v_height167 뷰에서 키가 167 미만인 데이터 입력하면 코드는 실행되지만 조회를 하면 입력한 티아라는 보이지 않는다 → 바람직하지 않다.
INSERT INTO v_height167 VALUES('TRA','티아라', 6, '서울', NULL, NULL, 159, '2005-01-01') ;SELECT * FROM v_height167;
이럴 때 예약어 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')
복합 뷰
두 개 이상의 테이블로 만든 뷰로, 주로 두 테이블을 조인한 결과를 뷰로 만들 때 사용한다.
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;*복합 뷰는 읽기 전용으로 복합 뷰를 통해 테이블에 데이터를 입력/수정/삭제 불가능하다.
뷰가 참고하는 테이블의 삭제
DROP TABLE IF EXISTS buy, member;
현재 여러 개의 뷰가 두 테이블과 관련이 있는데도 테이블이 정상적으로 삭제 된다. 두 테이블 중 아무거나 연관되는 뷰를 다시 조회하면 조회할 수 없다는 메시지가 나온다.
뷰가 조회되지 않으면 CHECK TABLE 문으로 뷰의 상태를 확인 가능하다. → 뷰가 참조하는 테이블이 없어서 오류가 발생하는 것을 확인할 수 있다.
CHECK TABLE v_height167;