테이블과 뷰

테이블 만들기

테이블 은 표 형태로 구성된 2차원 구조로, 행과 열로 구성되어 있다.

행은 로우(row)나 레코드(record)라 부르며, 열은 컬럼(column)또는 필드라 부른다. 엑셀 시트와 비슷하다 생각하면 된다.

• CREATE TABLE은 테이블 생성하는 SQL로 , 열 이름, 테이블 이름, 데이터 형식 등을 지정한다.

• 열에 입력될 값이 1부터 자동 증가하도록 설정하려면 GUI에선 AI를 체크하고, SQL에서 AUTO_INCREMENT를 입력한다.

• 열에 빈 값을 허용하지 않으려면 GUI에선 NN을 체크하고, SQL에서는 NOT NULL을 입력한다.

• 열을 기본키로 지정하려면 GUI에선 PK를 체크하고 SQL에서는 PRIMARY KEY를 입력한다.

• 열을 외래키로 지정하려면 FOREIGN KEY 예약어를 입력한다.

제약조건

테이블에는 기본 키, 외래 키와 같은 제약조건을 설정할 수 있다. 제약조건을은 테이블을 구성하는 핵심 개념으로, 이를 잘 이해하고 활용시 데이터의 오류를 줄여 완전무결한 코드를 만들 수 있다.

기본 키, 외래 키가 대표적인 제약조건이다

기본 키는 학번, 아이디, 사번 등과 같은 고유한 번호를 의미하는 열에, 외래키는 기본키와 연결되는 열에 지정한다.

이메일,휴대폰과 같이 중복되지 않는 열엔 고유키(UNIQUE)를 지정할 수 있다. 만약 회원의 평균키는 200cm가 절대 넘지 않을 건데 실수로 200을 넣는 걸 방지하는 것을 체크라한다.

만약 한국에서 서비스를 한다 치면 대한민국이 디폴트일 것이다. 이걸 일일히 넣을수도 있지만

그냥 기본값을 설정할 수 도 있다. 또 값을 반드시 입력해야 되는 NOT NULL또한 존재한다.

제약 조건의 기본 개념과 종류

제약조건은 데이터의 무결성을 지키기 위해 제한하는 조건. 데이터의 무결성은 데이터에 결함이 없음이라는 의미이다.

만약 네이버 아이디,이메일이 중복이면? 개망하게 된다.

이러한 결함이 없는 것을 데이터의 무결성이라고 한다.

MYSQL에서 기본키 이외에 제공하는 제약조건에는

• PRIMARY KEY 제약조건
• FOREIGN KEY 제약조건
• UNIQUE KEY 제약조건
• CHECK 제약조건
• DEFAULT 정의
• NULL 값 허용 이 있다.

기본키 제약조건

데이터를 구분할 수 있는 식별자가 기본키이다.

기본키의 값은 중복될 수 없으며, NULL 값이 입력될 수 없다. 보통 아이디가 기본키가 된다.

또한 테이블은 기본 키를 1개만 가질 수 있다. 아이디 이메일 주민번호 등 뭘 기본키로 설정해도 되지만 가장 특성과 유일성을 가진 열을 기본키로 하는 것이 좋다.

USE naver_db;
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)	-- PRIMARY KEY 뒤에 열 이름 넣어주면 그게 기본키가 된다.
);

ALTER TABLE에서 설정하는 기본 키 제약 조건

제약조건을 설정하는 또 다른 방법은 이미 만들어진 테이블을 수하는 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  -- ALTER TABLE 로 기본키를 설정했다.
     ADD CONSTRAINT
     PRIMARY KEY (mem_id);

덤) 기본키에 이름 지정하기

기본키는 별도의 이름이 없으며, DESCRIBE 명령으로 확인시 그냥 PRI로만 나온다. 필요하다면 기본키의 이름도 직접 지어줄 수 있다. 예를 들어 PK_member_mem_id와 같은 이름 붙여주면, 이름만으로 PK가 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,
  CONSTRAINT PRIMARY KEY PK_member_mem_id (mem_id)	-- 기본키(mem_id)를 PK_member_mem_id로 바꿈
);

외래 키 제약조건

외래키 제약조건은 두 테이블 사이의 관계를 연결해준다. 그 결과 데이터 무결성을 보장해 주게 된다.

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

우리가 사용하는 회원 테이블과 구매 테이블의 가격

여기서 기본키가 있는 회원 테이블을 기준 테이블 이라고 부르며 외래키가 있는 구매 테이블을 참조 테이블이라고 부른다.

구매 테이블의 아이디(FK)는 반드시 회원 테이블의 아이디(PK)로 존재한다. “네이버 쇼핑” 데이터 베이스에 제품을 구매한 기록이 있는 사람은 네이버 회원이라는 의미다.

그래서 구매한 기록은 있으나 구매한 사람이 누군지는 모르는 치명적인 일은 절대 발생하지 않는다. 구매 테이블의 데이터는 모두 누가 구매했는지 확실히 알 수 있는 , 무결한(오류가 없는) 데이터가 된다.

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

CREATE TABLE에서 설정하는 외래키 제약 조건

외래키를 생성하는 방법은 CREATE TABLE 끝에 FOREIGN KEY 키워드를 설정하는 것이다.

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(열_이름) REFERENCES 기준_테이블(열_이름)이다.

위의 예에선 구매 테이블(buy)의 열 (mem_id)이 참조(REFERENCES)하는 기준 테이블(member)의 열 (mem_id)은 기본키로 설정 되어 있는 것을 알 수 있다.

만약 기준 테이블의 열이 Primary Key 또는 Unique가 아니라면 외래키 관계는 설정 되지 않는다.

기준 테이블의 열 이름과, 참조 테이블의 열 이름

네이버 쇼핑의 예에선, 기준 테이블의 열 이름(mem_id)와 참조 테이블의 열 이름(mem_id)이 동일하다. 하지만 반드시 같아야 하는 것은 아니다. 즉, 참조 테이블(buy)의 아이디 열 이름이 user_id와 같이 기준 테이블(member)의 mem_id와 달라도 상관이 없다.

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) -- 외래키를 설정하는 형식은 FOREIGN KEY(열_이름) REFERENCES 기준_테이블(열_이름); 이다.
);

ALTER TABLE 에서 설정하는 외래 키 제약조건을

외래키를 설정하는 다른 방법은 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 -- buy를 수정한다
    ADD CONSTRAINT  -- 제약 조건을 추가한다
    FOREIGN KEY(mem_id)  -- 외래키 제약조건을 buy 테이블의 mem_id에 설정한다.
    REFERENCES member(mem_id);  -- 참조할 기준 테이블은 member테이블의 mem_id열이다.

기준 테이블의 열이 변경 될 경우

예를 들어 회원 테이블의 BLK 가 물품을 2건 구매한 상태에서 회원 아이디를 PINK로 바꾸면? 두 테이블의 정보가 일치하지 않게 된다.

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

INSERT INTO member VALUES('BLK', '블랙핑크', 163);
INSERT INTO buy VALUES(NULL, 'BLK', '지갑');
INSERT INTO buy VALUES(NULL, 'BLK', '맥북');

SELECT M.mem_id, M.mem_name, B.prod_name
   FROM buy B
      INNER JOIN member M
      ON B.mem_id = M.mem_id;

여기서 BLK 아이디를 PINK로 바꾸면 에러가 난다.

UPDATE member SET mem_id = 'PINK' WHERE mem_id='BLK';


DELETE FROM member WHERE  mem_id='BLK';

그리고 삭제 또한 같은 오류로 삭제 되지 않는다.

기본 키 - 외래 키 관계가 설정되면 기준 테이블의 열은 변경되거나 삭제 되지 않는다.

기준 테이블의 열 이름이 변경될 시, 참조 테이블의 열 이름이 자동으로 변경되면 더 효율적일거 같다. 즉, 회원 테이블의 BLK가 PINK로 변경되면 자동으로 구매테이블의 BLK가 PINK로 변경 되는 것이다.

이런 기능을 지원 하는 것이 ON UPDATE CASCADE 문이다. 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;

명령어 시랳ㅇ 후 내부조인 사용해서 보면 물품 정보 및 사용자 정보를 확인 할 수 있고 기준테이블, 참조테이블 아이디가 모두 변경된다.

SELECT M.mem_id, M.mem_name, B.prod_name
   FROM buy B
      INNER JOIN member M
      ON B.mem_id = M.mem_id;

삭제하면 아무것도 없어지고 아래와 같다

DELETE FROM member WHERE  mem_id='PINK';

기타 제약 조건

중요 해김 제약조건 이외에도 실무에서 디비를 쓰다 보면 필요한 여러 제약 조건이 있다.

고유 키 제약조건

고유 키(Unique) 제약 조건은 중복되지 않는 유일한 값 을 입력해야 하는 조건이다.

이는 기본키 제약조건과 비슷하지만, 차이점은 고유 키 제약조건은 NULL 값을 허용한다는 것이다.

NULL 값은 여러개가 입력되어도 상관 없다. 또, 기본 키는 테이블에 1개만 생성되어야 하지만, 고유키는 여러개를 설정해도 된다.

만약 회원테이블에 E-mail 주소가 있다면, 중복되지 않으므로 고유키로 설정할 수 있다.

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	-- 고유키
);

고유 키로 설정할 열을 NOT NULL로 지정시 고유키도 기본키와 동일하게 중복도 안되고 비어있어도 안된다.

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

위 쿼리를 실행하면 이메일이 중복 되어서 실행 되지 않는다.

체크 제약 조건

체크 제약 조건은 입력되는 데이터를 점검하는 기능을 한다. 예를 들어 평균 키가 마이너스 값이 입력 되지 않게 하던가, 국번에 이상한 번호가 안 들어가게 하는 법이다.

평균키를 100 이상으로 반드시 두기 위해 check(조건)을 추가해 주면 된다.

DROP TABLE IF EXISTS 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),
  phone1      CHAR(3)  NULL
);

기본값 정의

기본값(Default) 정의는 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해 놓는 방법이다. 예를 들어 키를 입력하지 않고 기본적으로 160이라 입력되도록 하고 싶다면 다음과 같이 정의할 수 있다.

DROP TABLE IF EXISTS 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),
  phone1      CHAR(3)  NULL
);

ALTER TABLE 사용 시 열에 DEFAULT를 지정하기 위해서는 ALTER COLUMN 문을 사용한다. 예를 들어 다음과 같이 연락처의 국번을 입력하지 않으면 자동으로 02가 입력되도록 할 수 있다.

ALTER TABLE member
    ALTER COLUMN phone1 SET DEFAULT '02';

기본값이 설정된 열에 기본값을 입력하려면 default를 써주고 원하는 값을 입력하려면 해당 값을 써주면 된다.

ALTER TABLE member
    ALTER COLUMN phone1 SET DEFAULT '02';

INSERT INTO member VALUES('RED', '레드벨벳', 161, '054');
INSERT INTO member VALUES('SPC', '우주소녀', default, default);
SELECT * FROM member;

널 값 허용

널(NULL) 값을 허용 하려면 생략하거나 NULL 을 사용하고, 허용하지 않으려면 NOT NULL을 사용한다. 다만, PRIMARY KEY 가 설정된 열에는 NULL 값이 있을 수 없으므로 생략시 자동으로 NOT NULL 로 인식된다.

NULL 은 “아무 것도 없다”라는 의미다. 공백(‘‘)이나 0과는 다르다.

• 기본 키는 행 데이터 중에서 데이터를 구분할 수 있는 식별자 역할을 한다.

• 외래 키는 두 테이블 관계를 연결시켜 준다. 외래키가 설정된 열은 꼭 다른 테이블의 기본키와 연결이 된다.

• 고유키는 중복되지 않는 유일한 값이다. 기본키와 차이점은 NULL 값을 허용한다.
• 체크는 입력되는 데이터를 점검하는 기능이다.
• 기본 값은 값을 입력하지 않았을 떄 자동으로 입력값을 미리 지정한다.
• 널값을 허용하려면 생략하거나 NULL을 사용하고, 허용하지 않으려면 NOT NULL을 사용한다.

가상의 테이블 : 뷰

한마디로 “가상의 테이블”이다. 일반 사용자 입장에서는 테이블과 동일하게 보이기 떄문이다. 그렇다면 테이블이 있는데 왜 뷰를 사용하나? 뷰를 사용하면 사용자에게 테이블의 필요한 내용만 보이도록 할 수 있다.

뷰(view) 데이터베이스 개체중 하나다. 모든 데이터 베이스 개체는 테이블과 관련이 있지만 뷰는 테이블과 아주 밀접히 관련이 있다.

뷰는 한번 생성해두면 테이블이라 생각하고 사용해도 될 정도로 사용자의 입장에선 테이블과 거의 동일한 개체로 취급한다.

뷰는 테이블처럼 데이터를 가지고 있지는 않다.뷰의 실체는 SELECT 문으로 만들어져 있기 떄문에 뷰에 접근하는 순간 SELECT가 실행되고 그 결과가 화면에 출력되는 방식이다.

말하자면 바로가기 아이콘과 비슷하다.

뷰는 단순 뷰 와 복합 뷰로 나뉜다.

단순 뷰는 하나 테이블과 연관된 뷰고, 복합뷰는 2개 이상의 테이블과 연관된 뷰이다.

뷰를 실행하면 테이블과 완전 동일하게 나온다. 뷰의 실체가 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;

처럼 뷰를 새로운 테이블로 생각하고 접근한다.

뷰 안에 조건식도 넣어서 실행이 가능하다.

뷰의 작동

그림에서 사용자는 뷰를 테이블이라 생각하고 접근한다. 그러면 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;

뷰의 실제 작동

사실 실무에서 뷰는 좀 더 복잡하게 사용한다.

뷰의 실제 생성, 수정, 삭제

기본적인 뷰를 생성하면서 뷰에서 사용될 열 이름을 테이블과 다르게 지정할 수 있다. 기존에 배운 별칭을 사용하면 되는데 중간에 띄어쓰기 사용이 가능하다. 별칭은 열 이름 뒤에 작은 따옴표 또는 큰 따옴표로 묶고 형식상 AS 로 붙여준다. AS를 붙이면 코드가 명확해보이는 장점이 있다.

단 뷰를 조회시에 열 이름에 공백이 있으면 백틱으로 묶어줘야 한다.

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;

바꿀떄도 마찬가지로 ALTER TABLE,

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를 사용한다.

데이터 베이스 개체의 생성/수정/삭제

데이터 베이스 개체는 서로 완전히 다른 기능을 하지만, 생성/수정/삭제 문법은 거의 동일하다.

• 생성 모든 데이터베이스 개체(테이블, 뷰, 인덱스, 스토어드 프로시저, 스토어드 함수, 트리거 등)을 생성할 때는 CREATE 개체_종류를 사용한다. 예로 뷰를 생성할 때는 CREATE VIEW를 사용한다.

• 수정

이미 생성된 데이터베이스 개체를 수정할 떄는 ALTER 개체_종류 를 사용한다. 예로 테이블을 수정할 떄는 ALTER TABLE을 사용한다.

• 삭제 기존의 데이터베이스 개체를 삭제시엔 DROP 개체_종류를 사용한다. 예로 스토어드 프로시저를 삭제할 때는 DROP PROCEDURE를 사용한다.

####뷰의 정보 확인

USE market_db;
CREATE OR REPLACE VIEW v_viewtest2	-- 기존에 뷰가 있어도 덮어쓰는 효과
AS
    SELECT mem_id, mem_name, addr FROM member;

DESCRIBE 문으로 기존 뷰의 정보를 확인할 수 있다.

SHOW CREATE VIEW 문으로 뷰의 소스코드도 확인할 수 있다.

• 모든 데이터베이스 개체는 테이블과 관련이 있지만 특히 뷰는 테이블에 직접 접근하므로 밀접하게 연관된다.

• 뷰를 한마디로 정의하면 “가상의 테이블”이다. 뷰의 실체는 SELECT 문으로 구성된다.

• 하나의 테이블과 관련되면 단순 뷰 , 2개 이상의 테이블과 관련되면 복합 뷰라고 부른다. 복합 뷰로는 테이블의 데이터를 수정할 수 없다.

• 뷰는 특정 사용자가 테이블에 접근하지 못하도록 하고 필요한 부분에만 접근하도록 함으로써 보안에 도움이 된다.