chap05 테이블과 뷰

Chapter 05 테이블과 뷰

---

테이블 만들기

테이블은 표 형태로 구성된 2차원 구조로 행과 열로 구서오디어 있다.
MySQL 에서는 GUI 환경에서 테이블을 만드는 방식과 SQL 로 테이블을 만드는 방식이 있다.
GUI 로 만들기는 그냥 책을 보고 열심히 따라하면 되고 SQL 을 한번 알아보장

** SQL로 테이블 만들기.

• 테이블을 만들기

  CREATE TABLE sample_table (num INT)

  • 데이터베이스 생성하기

    dROP DATABASE IF EXISTS naver_db;
    CREATE DATABASE naver_db;

USE naver_db;
DROP TABLE IF EXISTS member;  -- 기존에 있으면 삭제
CREATE TABLE member -- 회원 테이블
( mem_id        CHAR(8), -- 회원 아이디(PK)
  mem_name      VARCHAR(10), -- 이름
  mem_number    TINYINT,  -- 인원수
  addr          CHAR(2), -- 주소(경기,서울,경남 식으로 2글자만입력)
  phone1        CHAR(3), -- 연락처의 국번(02, 031, 055 등)
  phone2        CHAR(8), -- 연락처의 나머지 전화번호(하이픈제외)
  height        TINYINT UNSIGNED,  -- 평균 키
  debut_date    DATE  -- 데뷔 일자
);

테이블을 만드는 구문을 살펴보면, 기본에 GUI 에서 데이터의 이름과 타입을 지정해 주었던 것을 동일하게 만들었음을 확인할 수 있다.

아직 PK, NOT NULL 등과 같은 옵션은 지정하지 않은 상태이다.

DROP TABLE IF EXISTS member;  -- 기존에 있으면 삭제
CREATE TABLE member -- 회원 테이블
( mem_id        CHAR(8) NOT NULL,
  mem_name      VARCHAR(10) NOT NULL, 
  mem_number    TINYINT NOT NULL, 
  addr          CHAR(2) NOT NULL,
  phone1        CHAR(3) NULL,
  phone2        CHAR(8) NULL,
  height        TINYINT UNSIGNED NULL, 
  debut_date    DATE NULL
);

NULL, NOT NULL 등을 지정하는 방법이다. 아무것도 지정하지 않으면 기본적으로 NULL 을 허용한다.
타입 (char, varchar) 등을 지정해준곳 뒤에 적어주면 된다.

이젠 기본 키 를 설정 해보자.

DROP TABLE IF EXISTS member;  -- 기존에 있으면 삭제
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) NULL,
  phone2        CHAR(8) NULL,
  height        TINYINT UNSIGNED NULL, 
  debut_date    DATE NULL
);

기본키로 설정하고자 하는 항목의 타입명 뒤에 PRIMARY KEY를 적어주면 된다.
사실 NOT NULL 은 없어도 무관하다. 기본키인데 아이디가 없으면 말이 안되기 때문이다.

DROP TABLE IF EXISTS buy;  -- 기존에 있으면 삭제
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) NULL , -- 분류
   price         INT UNSIGNED NOT NULL, -- 가격
   amount        SMALLINT UNSIGNED  NOT NULL -- 수량
);

이번에는 자동 증가 AUTO INCREMENT 도 사용해보자. 이번에는 살짝 다른게, AUTO_INCREMENT로 지정한 열은 primary key 나 UNIQUE 로 꼭 지정해 주어야한다.

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, 
   group_name     CHAR(4) NULL ,
   price         INT UNSIGNED NOT NULL,
   amount        SMALLINT UNSIGNED  NOT NULL ,
   FOREIGN KEY(mem_id) REFERENCES member(mem_id)
);

마지막으로 외래 키를 설정해 보자.

---

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

• 제약조건의 기본 개념 : 데이터의 무결성을 지키기 위해 제한하는 조건.

• 제약조건의 종류 :

  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • CHECK
  • DEFAULT
  • NULL

• 기본 키 제약 조건

  • 데이터를 구분할 수 있는 식별 자
  • 기본 키가 설정 되어 있어야 중복된 데이터가 입력되지 않는다.

• 기본 키 설정 방법 (1)

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 예약어를 붙여주면 mem_id 가 기본 키로 설정이 된다. 이제 중복, 비어있을 수 없다.

• 테이블을 삭제하는 순서.
  • 기본 키 - 외래 키 관계로 연결되어 있는 두개의 테이블이 있다. 만약 기본키가 설정되어있는 테이블을 삭제하면 외래키가 있는 테이블은 어떻게 될까???
  • 정답은 삭제가 안된다는것. 따라서 외래 키가 설정된 테이블을 먼저 삭제하고, 기본키가 있는 테이블을 삭제하는 순서로 해야 한다.

• 기본 키 설정 방법 (2)
  • 또 다른 방법은 ALTER TABLE 구문을 사용하는 것이다.
  • 다음처럼 사용한다.

    ALTER TABLE member
     ADD CONSTRAINT 
     PRIMARY KEY (mem_id);
    

   - member 를 변경하고, 제약 조건을 추가하는데, mem_id 열에 기본 키 제약조건을 설정한다.

<br>

- 기본 키 설정 방법 (3)
  - 마지막 행에 PRIMARY KEY (열 이름) 을 추가하는 방법도 있다.
```SQL
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)
);

• 추가 - 기본 키에 이름 지정하기.
  • 기존의 기본키는 이름이 따로 없이 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)
);

CONSTRAINT PRIMARY KEY PK_member+,e,_id (mem_id) 로 적어주면 이름이 PK_member_mem_id 로 저장된다.

• 외래 키 제약 조건

  • 외래키 제약조건은 두 테이블 사이의 관계를 연결해주고, 데이터의 무결성을 보장해주는 역할을 한다.
  • 외래키로 설정된 열은 꼭 다른 테이블의 기본 키와 연결된다.
  • 기본키가 있는 테이블을 기준 테이블,
  • 외래키가 있는 테이블을 참조 테이블 이라 함.

• 외래키로 구매 테이블이 설정되어 있기 때문에 구매는 했는데 누가 구매했는지는 알 수 없는 일이 일어나지 않는다.

• 참조 테이블이 참조하는 기준 테이블의 열은 반드시 기본키 , 고유키 로 설정되어 있어야 한다.

  • (당연히 중복되는 값이 있는 열을 기준으로 외래키를 만들면 안되니깐..)

• 외래키 제약 조건 설정하기

  • CREATE TABLE 에서 설정하기.

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

테이블을 만들 때

FOREIGN KEY(mem_id) REFERENCES member(mem_id)

위 처럼 외래 키가 어느 기준 테이블의 열을 참조하는지 적어주면 된다.

• 외래키 제약 조건 설정하기
  • ALTER TABLE 에서 설정하기.

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

- buy 테이블을 수정하는데, CONSTRAINT 를 추가해 제약조건을 추가한다.
- 외래키 제약조건을 mem_id 열에 설정하고, 참조할 기준 테이블은 member 테이블의 mem_id 열이다.


- 기준 테이블의 열이 변경 될 경우
  - 만약 어느 테이블이 테이블A를 참조하고 있다고 하자. 
  - A의 열이 변경 된다면 참조테이블(B) 는 어떻게 될까?

- 답은, 기본 키 - 외래 키 관계가 된 이후로는 기준 테이블의 열 이름이 변경되지 않는 다는 것이다.
- 참조 테이블의 데이터에 문제가 발생하기 때문이다.
- 마찬 가지로, 삭제도 되지 않는다.

- 변경, 삭제를 가능하도록 하는 명령이
  - **ON UPDATE CASCADE**
  - **ON DELETE CASCADE**

- 이 두 명령은 기준 테이블의 데이터가 삭제, 또는 변경 되면, 참조 테이블의 데이터도 삭제, 변경 되는 기능이다.

```SQL
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;

• 문법은 다음과 같다. ALTER 맨 뒤에 적어준 후, 기준 테이블의 열을 변경하거나 삭제하면, 참조 테이블에도 잘 반영이 됨을 알 수 있다.

• 기타 제약 조건
  • 고유 키 제약 조건
  • 체크 제약 조건
  • 기본 값 정의
  • 널값 허용

• 고유 키
  • 고유 키 란, 중복되지 않는 유일한 값
  • 단 기본 키와의 차이는 NULL을 허용한다는 것이다.
  • 또, 기본 키와 달리 고유 키는 여러 개 설정 가능하다.

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

• NULL UNIQUE 로 설정을 해 email 열을 고유 키로 설정하였다.

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

• 위처럼 입력을 하면 에러가 난다. email 을 고유 키로 하였는데, 1,3 행의 이메일이 pink 로 같기 때문이다.

• 체크 제약조건

  • 체크 제약 조건은 입력되는 데이터를 점검하는 기능을 한다.
    • 예를 들어, 평균 키에 음수값이 입력되지 않게 한다거나,
    • 전화번호 앞자리를 02, 032, 등등 으로만 받을 수 있게 한다거나.

• 사용 예시

CREATE TABLE member 
( mem_id  CHAR(8) NOT NULL PRIMARY KEY, 
  mem_name    VARCHAR(10) NOT NULL, 
  height      TINYINT UNSIGNED NULL CHECK (height >= 100),
  phone1      CHAR(3)  NULL
);

• CHECK(조건) 을 추가한다.
• 이 예시의 경우 height >= 100, 즉 키가 100 이상이라는 조건을 달아준 것이다.

• ALTER 문을 이용해서도 CHECK 제약조건을 사용할 수 있다.

  ALTER TABLE member
    ADD CONSTRAINT 
    CHECK  (phone1 IN ('02', '031', '032', '054', '055', '061' )) ;
  

• 기본값 정의

  • 기본값 정의는, 값이 입력되지 않았을 때 자동으로 입력 될 값을 미리 지정해 놓는 방법이다.
  • 사용 예시

 CREATE TABLE member 
( mem_id  CHAR(8) NOT NULL PRIMARY KEY, 
  mem_name    VARCHAR(10) NOT NULL, 
  height      TINYINT UNSIGNED NULL DEFAULT 160,
  phone1      CHAR(3)  NULL
);

DEFAULT 를 지정해줌으로써 add 에서 저 height 의 값이 비어있는 채로 들어왔다면 디폴트 값 160으로 설정이 된다.

• 널값 허용
  • 허용하려면 NULL
  • 허용 안하려면 NOT NULL
  • PRIMARY KEY로 설정된 열의 경우에 NULL이 있을 수 없다는 사실!

---

05 - 3 가상의 테이블 : 뷰

• 뷰는 데이터베이스 개체 중 하나이다.

• '가상의 테이블' 로 불리며, 사용자에게 테이블의 필요한 내용만 보이도록 할 수 있다.

• SELECT 문으로 뷰가 만들어 지고, '바로가기' 아이콘이랑 유사하다고 생각하면 된다.

• SELECT 시에 나온 결과가 하나의 테이블과 같아 보인다. 이를 그냥 하나의 테이블처럼 사용자에게 보여주겠다는 것이다. 따라서 뷰의 실체가 SELECT 문 이라는 것.

• 뷰 생성법은 다음과 같다.

CREATE VIEW v_member
AS
    SELECT mem_id, mem_name, addr FROM member;

SELECT 문으로 뷰로 보여줄 열들을 선택한다.

• 뷰를 사용하는 이유!
  • 보안에 도움이 된다.
    • 뷰를 통해 보게되는 사용자는 원래 테이블의 숨겨진 내용, 즉 연락처 , 데뷔 일자 등과같은 정보를 보지 못한다.
  • 복잡한 SQL을 단순하게 만들 수 있습니다.
    • 주로 사용하는 SELECT 문의 경우 그때 그때 입력하기에 매우 길 수 있다.

• 뷰의 실제 작동
  • 실제로 뷰가 사용되는 방식을 알아보자.
  • 별칭을 사용해 뷰에서 실제 보여질 열의 이름을 설정해 줄 수 있고, 형식상 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; -- 백틱을 사용

• AS 로 열 이름을 설정해 주었고, 맨 마지막 줄에서 백틱을 사용하였다.
• 뷰의 수정은 ALTER VIEW 를 사용한다.
• 뷰의 정보 확인

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

• 기존에 이미 뷰가 존재한다면 create VIEW 시 오류가 생긴다.
• CREATE OR REPLACE VIEW 를 하면, 이미 뷰가 존재한다면 덮어쓰고, 없다면 새로 생성해주기 때문에 오류가 생기지 않는다.
• 즉, DROP VIEW 와 CREATE VIEW 를 동시에 쓴 효과가 난다.

• 뷰를 통한 데이터의 수정/삭제
  • 뷰를 통해서 원래 테이블의 데이터를 수정할 수 있다.
  • UPDATE 구문을 사용하여 수정해보면, 문제없이 수정됨을 알 수 있다.
  • 이번엔 데이터를 입력해보자.

INSERT INTO v_member(mem_id, mem_name, addr) VALUES('BTS','방탄소년단','경기') ;

• 이 때는 에러가 발생한다. 기존 테이블에 NOT NULL 인 데이터가 있는데, 뷰에서 입력한 데이터에 이 데이터가 없기 때문에 에러가 생기는 것이다.
• 이를 해결하기 위해선 뷰에 NOT NULL 인 열을 포함시키는 방법이 있다.

• 이번엔 뷰를 통해 데이터를 입력해보자.

INSERT INTO v_height167 VALUES('TRA','티아라', 6, '서울', NULL, NULL, 159, '2005-01-01') ;

• 정상적으로 입력이 되었다.
• 하지만 키가 160 이하인 입력은 받지 않도록 테이블이 제약조건이 있다면 저 입력은 입력이 되지 않아야 할 것이 입력이 된 것이 된다.
• 이를 막기 위한 것이 WITH CHECK OPTION 이다.

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

• 이렇게 with check 옵션을 해주면, 만약 167 이하인 입력이 들어왔을 경우 에러가 발생한다.

• 마지막으로 뷰가 참조하는 테이블의 삭제를 해보자.

• 뷰가 생성되어있는 테이블을 DROP 을 이용해 삭제해보았다.

• 이 후 뷰를 조회하면, 에러가 발생한다.