[혼공S] 4주차 - 테이블, 뷰

과제:

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

1. member 테이블 생성: 자동 대문자가 안되어서 그냥 소문자로 와르르 썼다... mysql 설정을 다시 해봐야할듯

2. 데이터 입력하기

3. 결과

Chapter.5 테이블과 뷰

테이블 제약조건(Constraint)

• 데이터의 무결성을 지키기 위해 제한하는 조건
• 데이터의 무결성 = 데이터에 결함이 없음

기본 키 제약조건

• 기본 키(Primary Key): 데이터를 구분할 수 있는 식별자
• 중복될 수 없고, NULL 값이 입력될 수 없다
• 기본 키로 생성한 것은 자동으로 클러스터형 인덱스가 생성(추후 학습 예정)
• 테이블은 기본 키를 1개만 가질 수 있다

-- CREATE TABLE에서 기본 키 제약조건 걸기
mem_id    CHAR(8) PRIMARY KEY,
-- 혹은 CREATE TABLE 맨 아래에 작성
PRIMARY KEY (mem_id)

-- ALTER TALBE에서 기본 키 제약조건 걸기
ALTER TABLE member
    ADD CONSTRAINT
    PRIMARY KEY(mem_id);

외래 키 제약조건

• 두 테이블 사이의 관계를 연결해주고, 그 결과 데이터의 무결성을 보장해주는 역할을 한다
• 외래 키가 설정된 열은 꼭 다른 테이블의 기본 키와 연결됨
• 기본 키가 있는 회원 테이블 = 기준 테이블, 외래 키가 있는 구매 테이블 = 참조 테이블
• 참조 테이블의 FK는 반드시 기준 테이블의 PK로 존재한다
• 기준 테이블의 열이 PK 또는 Unique가 아니라면 외래 키 관계는 설정되지 않는다
• 기준 테이블의 열 이름이 변경/삭제될 때 참조 테이블의 열 이름이 자동으로 변경되게 하기: ON UPDATE/DELETE CASCADE

-- CREATE TABLE에서 외래 키 제약조건 걸기: 끝에 FOREIGN KEY 키워드 설정하기
FOREIGN KEY(mem_id) REFERENCES member(mem_id)

-- ALTER TALBE에서 외래 키 제약조건 걸기
ALTER TABLE buy
    ADD CONSTRAINT
    FOREIGN KEY(mem_id)
    REFERENCES member(mem_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE;

기타 제약조건

• 고유 키 제약조건(Unique): 중복되지 않는 유일한 값을 입력해야 하는 조건. NULL 값 허용
• 체크 제약조건(Check): 입력되는 데이터를 점검하는 기능
  • height TINYINT UNSIGNED NULL CHECK (height >= 100)
• 기본값 정의(Default): 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해 놓는 방법
  • height TINYINT UNSIGNED NULL DEFAULT 160

뷰

CREATE VIEW 뷰_이름
AS
    SELECT 문;

• 뷰의 실체는 SELECT 문이다. 뷰를 만든 후에는 테이블과 동일하게 사용하면 된다
• 뷰를 사용하는 이유
  • 보안: 특정 행만 보이는 뷰에만 접근할 수 있도록 권한을 준다.
  • 단순화: 자주 사용하는 복잡한 SQL을 뷰로 생성해 놓고 해당 뷰에 접근한다.

뷰의 생성, 수정, 삭제

-- 생성
CREATE VIEW v viewtest1
AS
    SELECT B.mem_id 'Member ID', M.mem name AS 'Member Name', 
        B.prod_name "Product Name", CONCAT (M. phonel, M.phone2) As "Office Phone"
    FROM buy B
        INNER JOIN member M
        ON B.mem_id = M.mem_id;

-- 수정
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;

-- 삭제
DROP VIEW v_viewtest1;

• VIEW에 WITH CHECK OPTION을 사용하면 뷰에 설정된 값의 범위가 벗어나는 값의 입력을 막을 수 있다
• 뷰가 참조하고 있어도 테이블은 삭제된다(CHECK TABLE 뷰_이름으로 테이블 상태 확인 가능)
• 단순 뷰: 하나의 테이블로 만듦. 복합 뷰: 두 개 이상의 테이블로 만듦, 읽기 전용!!