(4주차) Chapter 05

내용 정리

Chapter 05. 테이블과 뷰

05-1. 테이블 만들기

 

1) 데이터베이스 생성하기

DROP DATABASE IF EXISTS naver_db;
CREATE DATABASE naver_db;

 

2) 테이블 생성하기

 : 기본 키로 지정된 열에는 NOT NULL을 생략해도 당연히 NOT NULL로 취급함.

 : AUTO_INCREMENT로 지정한 열은 PRIMARY KEY나 UNIQUE로 지정해야 함.

 : 외래 키는 테이블을 만들 때 제일 마지막에 FOREIGN KEY 예약어로 지정함.

 : 기본 키 - 외래 키는 일대다로 연결되는 관계

 : 백틱(`)은 테이블 이름이나 열 이름을 묶을 때 사용

CREATE TABLE member(
mem_id		CHAR(8)				NOT NULL	PRIMARY KEY,
mem_name 	VARCHAR(10)			NOT NULL,
mem_number 	TINYINT				NOT NULL,
addr 		CHAR(2)				NOT NULL,
phone1 		CHAR(3)				NOT NULL,
phone2 		CHAR(8)				NOT NULL,
height 		TINYINT				UNSIGNED	NOT NULL,
debut_date 	DATE				NULL
);

DROP TABLE IF EXISTS buy;
CREATE TABLE(
num		INT 	AUTO_INCREMENT NOT NULL PRIMARY KEY,
mem_id		CHAR(8) NOT NULL,
prod_name	CHAR(8) 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)
);

 

3) 데이터 입력하기

INSERT INTO member VALUES('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015-10-19');

 

---

 

05-2. 제약조건으로 테이블을 견고하게

 

1) 기본키 제약조건

 : 중복될 수 없으며, NULL값이 허용되지 않음.

 : 테이블은 기본키를 하나만 가질 수 있음.

 : 대부분의 테이블은 기본키를 가지지만, 기본키가 없어도 테이블 구성은 가능함.

 : 기본키-외래키로 관계가 되어 있는 경우 외래키가 설정된 테이블을 먼저 삭제해야 함.

 

1)-1. 기본키 생성 방법(CREATE문)

 : 열 이름 뒤에 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
);

 : CREATE문 제일 마지막 행에 PRIMARY KEY(열_이름)을 붙여주면 그 열이 기본키로 설정됨.

DROP TABLE IF EXISTS member;
CREATE TABLE member(
mem_id CHAR(8) NOT NULL,
mem_name VARCHAR(10) NOT NULL,
height TINYINT UNSIGNED NULL,
PRIMARY KEY(mem_id)
);

 

1)-2. 기본키 생성 방법(ALTER TABLE문)

 : 이미 만들어진 테이블을 수정하는 ALTER TABLE문을 사용할 수 있음.

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);

 

1)-3. 기본키에 이름 지정하기

 : 기본키는 별도의 이름이 없으며, DESCRIBE문으로 확인하면 PRI로만 나옴. 필요하다면 기본키의 이름을 직접 지어줄 수도 있음.

DROP TABLE IF EXISTS member;
CREATE TABLE member(
mem_id CHAR(8) NOT NULL,
mem_name VARCHAR(10) NOT NULL,
height TINYINT UNSIGNED NULL,
CONSTRAINT PRIMARY KEY PK_member_mem_id(mem_id)
);

 

2) 외래키 제약조건

 : 외래키 제약조건은 두 테이블 사이의 관계를 연결해주고, 그 결과 데이터의 무결성을 보장해주는 역할을 함.

 : 외래키가 설정된 열은 꼭 다른 테이블의 기본키와 연결됨.

 : 기본테이블 - 기본키가 있는 테이블

 : 참조테이블 - 외래키가 있는 테이블

 : 참조테이블이 참조하는 기본테이블의 열은 반드시 기본키나 고유키로 설정되어 있어야 함.

 

2)-1. 외래키 생성방법(CREATE문)

 : CREATE문 제일 마지막 행에 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,
user_id CHAR(8) NOT NULL,
prod_name CHAR(6) NOT NULL,
FOREIGN KEY(user_id) REFERENCES member(mem_id)
);

 

2)-2. 외래키 생성방법(ALTER TABLE문)

DROP TABLE IF EXISTS buy;
CREATE TABLE(
num		INT 	AUTO_INCREMENT NOT NULL PRIMARY KEY,
mem_id		CHAR(8) NOT NULL,
prod_name	CHAR(8) NOT NULL,
);
ALTER TABLE buy
    ADD CONSTRAINT
    FOREIGN KEY(mem_id)
    REFERENCES member(mem_id);

 

2)-3. 기준 테이블의 열이 변경될 경우

 : 기본키-외래키로 맺어진 후에는 기준 테이블의 열 이름이 변경되지 않음. 열 이름이 변경되면 참조 테이블의 데이터에 문제가 발생하기 때문임.

 : 기본키-외래키로 맺어진 후에도 참조 테이블에 해당 데이터가 없다면 변경 가능함.

 : 기존 테이블의 열 이름이 변경될 때 참조 테이블의 열 이름이 자동으로 변경, 삭제되는 기능을 제공하는 것이 각각 ON UPDATE CASCADE, ON DELETE CASCADE문임.

DROP TABLE IF EXISTS buy;
CREATE TABLE(
num		INT 	AUTO_INCREMENT NOT NULL PRIMARY KEY,
mem_id		CHAR(8) NOT NULL,
prod_name	CHAR(8) NOT NULL,
);
ALTER TABLE buy
    ADD CONSTRAINT
    FOREIGN KEY(mem_id) REFERENCES member(mem_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE;

 

3) 기타 제약조건

3)-1. 고유키(unique) 제약 조건

 : 중복되지 않는 유일한 값을 입력해야 하는 조건

 : 기본키와 다르게 NULL값 허용

 : 기본키와 다르게 하나의 테이블에 여러 개 설정 가능

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
);

 

3)-2. 체크(check) 제약조건

 : 체크 제약조건을 설정한 후에 조건에 위배되는 값을 입력하면 오류가 발생함.

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)
);

 

3)-3. 기본값(default) 정의

 : 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해 놓는 방법.

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 100,
phone1 CHAR(3) NULL
);

 

 

---

05-3. 가상의 테이블: 뷰

 

1) 뷰의 개념

 : 뷰의 실체는 SELECT문

 : 뷰의 이름만 보고 뷰인지 알아볼 수 있도록 이름 앞에 v_를 붙이는 것이 일반적

 : 뷰를 사용하는 이유는 보안과 효율성에 있음.

 : 사용자마다 테이블에 접근하는 권한에 차별을 뒤서 처리하여 보안 강화 가능

 : 뷰로 만들어두면 매번 SELECT문을 사용할 필요가 없음.

 : 뷰는 기본적으로 '읽기 전용'이나 뷰를 통해서 원본 테이블의 데이터를 수정할 수 있음. 무조건 가능한 것은 아니고 몇가지 조건을 충족시켜야 함.

 

2) 뷰의 생성 : CREATE VIEW

CREATE VIEW 뷰_이름
AS
SELECT문;

 : 뷰에서 사용될 열 이름을 별칭을 사용하여 테이블과 다르게 지정할 수도 있음.

 : 또한 별칭으로 띄어쓰기가 가능하나 백틱으로 묶어줘야 함.

 

2) 뷰의 확인 : SELECT

: 필요한 열만 보거나 조건식을 넣을 수도 있음.

SELECT mem_name, addr 
FROM v_member
WHERE addr IN ('서울', '경기');

 

3) 뷰의 수정 : 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;

 

4) 뷰의 삭제 : DROP VIEW

DROP VIEW v_viewtest1;

 

5) 뷰 덮어쓰기 : CREATE OR REPLACE VIEW

: 뷰를 생성할 때 CREATE VIEW는 기존에 뷰가 있으면 오류가 발생하지만, CREATE OR REPLACE VIEW는 기존에 뷰가 있어도 덮어쓰는 효과를 내기 때문에 오류가 발생하지 않음.

USE market_db;
CREATE OR REPLACE VIEW v_viewtest2
AS
CREATE mem_id, mem_name, addr FROM member;

 

6) 뷰의 정보 확인 : DESCIRBE

 : 뷰도 테이블과 동일하게 정보를 보여주나 PRIMARY KEY등의 정보는 확인되지 않음.

DESCRIBE v_viewtest2;

 

7) 뷰의 소스코드 확인 : SHOW CREATE VIEW

SHOW CREATE VIEW v_viewtest2;

 

8) 뷰를 통한 데이터의 수정/삭제

 : 만약 테이블이 가진 조건식에 맞지 않은 데이터를 뷰에 입력하면 오류가 발생함.

 : 이를 해결하기 위해 (1) 뷰를 재정의 (2) 기존 테이블의 열의 속성을 변경 (3) Default값 활용

 : 뷰에 조건을 만족하지 않은 데이터가 입력 시 에러가 나지 않으며 기존 테이블에 데이터도 입력됨. 그래서 WITH CHECK OPTION을 사용하여 조건식에 맞지 않은 값은 입력되지 않도록 함.

ALTER VIEW v_height167
AS
SELECT * FROM member FROM height>=167
WITH CEHCK OPTION;

 

9) 뷰의 상태 확인 : CHECK TABLE

 : 테이블은 뷰가 참조하고 있어도 삭제됨

 : 그래서 뷰가 조회되지 않을 때 CHECK TABLE문으로 뷰의 상태를 확인해볼 수 있음.

CHECK TABLE v_height167;

 

10) 단순 뷰와 복합 뷰

 : 복합 뷰는 주로 두 테이블을 조인한 결과를 뷰로 만들 때 사용함.

 : 복합 뷰는 읽기 전용으로 복합 뷰를 통한 기존 테이블의 데이터 입력/수정/삭제는 불가함.

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;

 

기본 미션

 

p. 266의 market_db의 회원 테이블(member)을 생성하고, p.229 데이터 입력한 후 인증하기

 

 

선택 미션

 

p. 271 확인 문제 4번 풀고 인증하기

 

4. 다음은 기존에 뷰가 있으면 덮어쓰고, 없으면 새로 생성하는 SQL입니다. 빈칸에 들어갈 내용을 고르세요.

CREATE OR REPLACE VIEW 뷰_이름

AS

SELECT문;