[혼공SQL] 4주차

05-1 테이블 만들기

테이블은 MySQL Workbench에서도 만들 수 있고, SQL로도 만들 수 있다. 그럼 SQL로 member 테이블을 만들어보자.

DROP TABLE IF EXISTS member;	-- 기존에 member 테이블이 있다면 삭제
CREATE TABLE member
(	mem_id		CHAR(8) NOT NULL, -- not null-> 빈 값 허용하지 않음
	mem_name	VARCHAR(10) NOT NULL PRIMARY KEY,	-- primary key-> 기본키 설정
    mem_number	TINYINT NOT NULL,
    addr		CHAR(2) NOT NULL,
    phone1		CHAR(3) NULL,
    phone2		CHAR(8) NULL,
    height 		TINYINT UNSIGNED NULL, -- unsigned-> 0부터 시작, 음수 사용 X
    debut_date	DATE NULL
);

이제 회원 테이블과 연결되는 구매 테이블을 만들어보자.

DROP TABLE IF EXISTS buy;	-- 기존에 buy 테이블이 있다면 삭제
CREATE TABLE buy
(	num			INT AUTO_INCREMENT NOT NULL PRIMARY KEY, 
-- auto_increment-> 숫자 자동 증가, primary key 또는 unique와 함께 사용
	mem_id		CHAR(8) NOT NULL,
    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)
    -- buy 테이블의 mem_id 열을 member 테이블의 mem_id 열과 외래 키 관계로 연결
);

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

1. 기본키와 외래키

• 기본키 - 외래키 관계에서 기본키가 있는 테이블을 '기준 테이블'이라 부르고, 외래키가 있는 테이블을 '참조 테이블'이라 한다.
• 기준 테이블의 기본키 열을 변경하면 참조 테이블 데이터에 문제가 발생하기 때문에 에러가 나며 변경되지 않는다. 이러한 문제를 해결하기 위해 기준 테이블의 열 이름이 변경될 때 참조 테이블의 열도 같이 변경하게 하는 기능이 있다.

ALTER TABLE buy
	ADD CONSTRAINT
    FOREIGN KEY(mem_id) REFERENCES member(mem_id)
    ON UPDATE CASCADE -- 기준 테이블의 열 이름이 변경될 때 참조 테이블 열 이름도 변경됨
    ON DELETE CASCADE; -- 기준 테이블의 데이터가 삭제될 때 참조 테이블의 데이터도 삭제됨

2. 고유키(UNIQUE)

기본키(PRIMARY KEY)와 비슷하지만 고유키(UINQUE)는 NULL값을 허용한다. 또, 기본키는 하나만 설정해야 하지만 고유키는 여러개를 설정할 수 있다.

create table memer
(	mem_id		CHAR(8) NOT NULL PRIMARY KEY,
	mem_name	VARCHAR(10) NULL UNIQUE,
    email		CHAR(30) NULL UNIQUE
);

3. 체크(CHECK)

입력되는 데이터를 점검하는 기능. 예를 들어 평균 키에 마이너스 값이 입력되지 않도록 하거나, 연락처의 국번에 02,031,041,055 중 하나만 입력되도록 할 수 있다.

create table member
(	mem_id		CHAR(8) NOT NULL PRIMARY KEY,
	mem_name	VARCHAR(10) NOT NULL,
    height		TINYINT UNSIGNED NULL CHECK (height >= 100)
);

4. 기본값(DEFAULT)

값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해 놓는 기능. 키의 기본값을 160으로 지정해보자.

create table member
(	mem_id		CHAR(8) NOT NULL PRIMARY KEY,
	mem_name	VARCHAR(10) NOT NULL,
    height		TINYINT UNSIGNED NULL DEFAULT 160
);

05-3 가상의 테이블 : 뷰

뷰(view)는 데이터베이스 개체 중에 하나이다. 사용자들의 입장에서는 테입르과 거의 동일한 개체로 취급된다. 뷰는 SELECT 문으로 만들어져 있기 때문에 뷰에 접하는 순간 SELECT가 실행되고 그 결과가 화면에 출력된다. '바로 가기 아이콘'과 비슷하다고 생각하면 쉽다. 뷰는 단순 뷰와 복합 뷰로 나뉘며 단순 뷰는 하나의 테이블과 연관된 뷰를 말하고 복합 뷰는 2개 이상의 테이블과 연관된 뷰를 말한다.

뷰 만들기

CREATE VIEW v_member
AS
	SELECT mem_id, mem_name, addr FROM member; 
SELECT * FROM v_member; -- 뷰 사용

뷰를 사용하는 이유

• 보안에 도움이 된다 - 공개할 수 있는 데이터만 뷰에 넣고 뷰에만 접근할 수 있도록 권한을 주면 보안에 도움이 된다.
• 복잡한 SQL을 단순하게 만들 수 있다 - 복잡한 쿼리를 뷰로 만들어놓고 사용하면 편리하게 사용할 수 있다.

CREATE OR REPLACE VIEW v_member

drop view와 create view를 연속으로 실행하여 기존에 v_member라는 뷰가 있어도 오류가 발생하지 않는다.

SHOW CREATE VIEW v_member;

뷰의 소스코드를 확인할 수 있다.

WITH CHECK OPTION

뷰에 설정된 값의 범위가 벗어나는 값은 입력할 수 없도록 함.

🐤기본미션

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

😎선택미션

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

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

[			빈칸			] 뷰_이름
AS
	SELECT 문;

① CREATE AND REPLACE VIEW
② CREATE OR REPLACE VIEW
③ CREATE AND OVERWRITE VEIW
④ CREATE OR OVERWRITE VIEW

정답 : 2

CREATE OR REPLACE VIEW는 DROP VIEW와 CREATE VIEW를 연속으로 실행한다.

이번 주차에서는 앞에 잠깐 나왔던 view에 대해서 자세히 배울 수 있어 좋았다. 다음 주차는 여름방학이다!! 비록 학교 방학은 끝났지만 혼공단 여름방학 동안 잘 쉬다 와야겠다😊