혼공 SQL - ch05(테이블과 뷰)

목록

1. 포스팅 개요
2. 본론
     2-1. 테이블 만들기
     2-2. 제약조건으로 테이블을 견고하게
     2-3. 가상의 테이블: 뷰
     2-4. 번외(눈에 띄는 것)
3. 요약

1. 포스팅 개요

혼자 공부하는 SQL 챕터 5를 요약 및 정리한 포스팅

2. 본론

2-1. 테이블 만들기

테이블의 구성

테이블 : 표 형태로 구성된 2차원 구조, 행과 열로 구성되어있다.
행 : 로우(row)나 레코드(record)라고 부른다.
열 : 컬럼(column) 또는 필드(feild)라고 부른다.

데이터베이스 생성하기

DROP DATABASE IF EXISTS 데이터베이스_이름; -- 해당 데이터베이스가 만약 있으면 삭제한다.
CREATE DATABASE 데이터베이스_이름; -- 데이터베이스를 생성한다.

테이블 생성하기

USE 데이터베이스_이름; -- 사용할 데이터베이스(=스키마)를 설정한다.
DROP TABLE IF EXISTS 테이블_이름; -- 만약 해당 테이블이 있으면 삭제한다.
CREATE TABLE 테이블_이름 (
    열_이름1 데이터_형식 (NOT NULL || NULL) (PRIMARY KEY) -- 기본키는 아래에서 따로 설정 가능하다. / 기본키는 당연히 NOT NULL이다.
    ..
    ..
    열_이름n 데이터_형식 (NOT NULL || NULL)
    ( PRIMARY KEY(기본키로_지정할_열_이름) ) -- 기본키 설정을 여기서도 할 수 있다. // 위에서 지정했기 떄문에 괄호()로 생략가능하다고 표시함.    
    FOREIGN KEY(외래키로_지정할_열_이름) REFERENCES 관계_테이블(관계_테이블에서_외래키로_지정할_열_이름) 
    -- 위 문장은 외래키 설정이다. 
    -- 후에 다루지만, 외래키로 지정할 열은 반드시 관계된 테이블에서 기본키로 설정되어있어야 한다. 
    -- 그러지 않으면, 다음과 같은 에러메세지가 발생한다.
    -- Error Code: 1822. Failed to add the foreign key constraint. Missing index for constraint '테이블_이름_ibfk_1' in the referenced table 관계_테이블
);

AUTO_INCREMENT
AUTO_INCREMENT로 지정한 열은 반드시 PRIMARY KEY 혹은 UNIQUE로 지정해야한다.

NOT NULL
Null 허용 여부.
반대는 NULL이다.

PRIMARY KEY
기본키를 설정하는 키워드

FOREIGN KEY(열) REFERENCES 테이블(열)

• CREATE문 안에서 외래 키를 지정한다.
• 이때 명시하는 관계된 테이블의 열은 기본 키여야 한다.

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

ALTER문으로 제약 조건 추가

제약 조건(CONSTRAINT)

• 데이터의 무결성을 지키기 위해 제한하는 조건
  • 데이터의 무결성이란?
    • 데이터에 결함이 없음을 의미함.
    • ex) 회원 아이디가 중복되는 것은 데이터의 무결성을 위배한 것이다.

기본키(PRIMARY KEY)

• 데이터를 구분할 수 있는 식별자
• 중복 X,
• NULL 허용 X,
• 기본 키로 생성한 것은 자동으로 클러스터형 인덱스가 생성된다. 클러스터형 인덱스에 대해서는 다음 장에 나온다.
• 테이블은 기본 키를 1개만 가질 수 있다.

기본키 설정 SQL문

-- 테이블 생성시 기본키 지정
CREATE TABLE 테이블_이름 (
기본_키_열_이름 데이터_형식 NOT NULL PRIMARY KEY,
...
...
);

-- 혹은

CREATE TABLE 테이블_이름 (
기본_키_열_이름 데이터_형식 NOT NULL,
...
...
...,
PRIMARY KEY(기본_키_열_이름) -- 
CONSTRAINT (제약조건_이름) PRIMARY KEY (기본_키로_지정할_이름) -- 제약조건_이름 생략가능.
);

ALTER문으로 기본 키 설정
만약 CREATE문으로 테이블을 생성했지만, 기본 키를 지정하지 않았을 경우.
ALTER문으로 지정할 수 있다.

ALTER TABLE 테이블_이름 
    ADD CONSTRAINT        -- 제약조건 추가
    PRIMARY KEY(테이블에서_기본키로_저장할 열_이름); -- 기본 키 지정

외래키(FOREIGN KEY)

• 두 테이블 사이를 연결해준다.
• 데이터의 무결성을 보장한다.
• 외래 키가 설정된 열은 꼭 다른 테이블의 기본 키와 연결해야 한다.
• 기본 키가 있는 테이블을 기준 테이블이라고 하고, 외래 키가 있는 테이블을 참조 테이블이라고 부른다.
• 참조 테이블이 참조하는 기준 테이블의 열은 반드시 기본 키이거나, 고유 키(UNIQUE)로 설정되어있어야 한다.

외래 키 설정 SQL문

CREAT TABLE 테이블_이름 
(
열_이름
...
...
FOREIGN KEY(외래_키로_지정할_열_이름) REFERENCES 기준_테이블(기준_테이블의_기본_키) -- 외래키 지정
);

ALTER문으로 외래 키 설정

ALTER TABLE 테이블_이름
    ADD CONSTRAINT -- 제약조건 추가
    FOREIGN KEY(외래_키로_지정할_열_이름)
    REFERENCES 기준_테이블(기준_테이블의_기본_키);

ON UPDATE CASCADE 과 ON DELETE CASCADE

만약 회원 테이블과 구매내역 테이블이 있을 때,
회원테이블이 기준테이블이고, 구매내역 테이블이 참조테이블일 때,
기준 테이블에서 회원 이름이 기본 키이고, 참조테이블에서는 회원 이름이 외래 키라고 가정해보자.

기준테이블에서 회원이름을 변경을 하면 어떤 일이 발생할까?
만약 변경하게되면, 기본 키와 외래 키가 달라져서 이후에 참조테이블에서 기준테이블의 기본키를 이용하여 데이터를 찾으려고 하면 문제가 발생할 수 있다고 예상할 수 있을 것이다.

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


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; 

위의 SQL문의 결과를 보면 다음과 같다.

이제 다음 SQL문으로 기본 키에 해당하는 회원 아이디를 바꿔보자.

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

어떤 일이 벌어질까?

결론은 실패다.

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`naver_db`.`buy`, CONSTRAINT `buy_ibfk_1` FOREIGN KEY (`mem_id`) REFERENCES `member` (`mem_id`))

애초에 기본 키-외래 키로 맺어진 후에는 기준 테이블의 열 이름이 변경되지 않는다.
열 이름이 변경되면 참조 테이블의 데이터에 문제가 생기기 때문이다.
이렇게 무결성을 보장하도록 해준다.

만약 참조 테이블에 기준 테이블과 관련된 데이터가 없다면, 변경이 가능하다.

truncate buy;
UPDATE member SET mem_id = 'PINK' where mem_id = 'BLK';

서론이 길었지만, 이 사용할 수 있는 것이 ON UPDATE CASCADE 과 ON DELETE CASCADE이다.

ON UPDATE CASCADE는 기준테이블의 데이터가 수정되면, 참조 테이블의 데이터도 수정된다.
ON DELETE 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  -- UDATE CASCADE
    ON DELETE CASCADE; -- DELETE CASCADE

데이터를 넣고, UPDATE문을 실행해보면,

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

UPDATE member SET mem_id = 'PINK' where mem_id = '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;

삭제도 확인해보자

DELETE FROM member where mem_id = 'PINK'; -- 데이터 삭제
select * from buy; -- 확인

결과는 아래와 같다.

---

※참고
기본 키 - 외래 키로 설정된 테이블에서 데이터를 삭제할 경우,
기준테이블의 데이터를 먼저 삭제하면, 참조테이블에서 외래 키를 통해 A테이블의 데이터를 조회할 수 없다.
떄문에 데이터를 삭제할 때,
참조 테이블에서 데이터를 삭제한 이후,
기준테이블의 데이터를 삭제하도록 한다.
즉 외래 키의 데이터를 먼저 삭제한 이후, 기본 키의 데이터를 삭제하는 것이 보다 바람직하다.
이를 간편하게 해주는 것이 앞서 보았던 ON UPDATE CASCADE, ON DELETE CASCADE이다. 잘 활용하자!

고유키(UNIQUE)

• 중복되지 않는 유일 한 값을 입력해야 하는 조건
• 기본 키 제약조건과 비슷하지만, NULL을 허용하는 차이가 있다.
• NULL이 여러개 입력되어도 상관 없다.
• 기본 키는 테이블에 하나만 설정해야 하지만, 고유 키는 여러 개를 설정할 수 있다.

CREATE TABLE 테이블_이름 (
...
열_이름 데이터_형식 UNIQUE -- UNIQUE 키워드로 고유 키 설정이 이루어진다.
);

체크(CHECK)

• 입력 되는 데이터를 점검한다.

CREATE TABLE 테이블_이름 (
...
열_이름 데이터_형식 CHECK(조건) -- 데이터를 입력되는 조건을 설정할 수 있다. 
);

ALTER TABLE 테이블
    ADD CONSTRAINT
    CHECK (조건);

-- 예시 전화번호 국번은 02, 031, 032만 허용할 때
    CHECK ('국번' IN('02','031','032'));
-- IN()은 괄호 안에 있는 값 중 하나와 같아야 한다.

기본값(DEFAULT)

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

CREATE TABLE 테이블_이름 (
...
열_이름 데이터_형식 ... DEFAULT 값,
...
);

ALTER문으로 DEFAULT값을 설정할 때는 ALTER COLUMN문을 사용한다.

ALTER TABLE 테이블_이름 
    ALTER COLUMN 열_이름 SET DEFAULT 값;    

기본값을 설정하고, 데이터를 입력할 떄, 기본값을 사용하고 싶으면, default라고 입력해주면된다.

다음은 예시 SQL문이다.

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 DEFAULT 160,
 phone1 char(3) null
);

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;

결과는 다음과 같다.

NOT NULL

• NULL을 허용하지 않는다는 의미
• NULL 값을 허용하려면, 생략하거나, NULL을 사용하면된다.
• 기본 키는 NULL을 허용하지 않기 때문에, 기본 키에는 생략해도 NOT NULL로 인식된다.

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

• 가상의 테이블이라고도 불린다.
• 데이터베이스 개체 중의 하나이다.
• 뷰는 데이터를 가지고 있지 않다.
• 뷰의 실체는 SELECT 문으로 만들어져있고, 뷰에 접근하는 순간 SELECT가 실행되어 결과가 화면으로 출력된다.
• 뷰의 이름만 보고도 뷰인지 알아볼 수 있도록 앞에 v_를 붙이는 것이 일반적이다.
• 뷰는 읽기 전용이기때문에, 수정이 불가능하다.
  하지만, 특정한 조건이 만족했을 때, 뷰를 통해서 원본 테이블의 데이터를 수정할 수도 있다.

단순 뷰

• 하나의 테이블과 연관된 뷰를 의미
• -데이터의 수정/삭제/입력이 가능하다.

복합 뷰

• 2개 이상의 테이블과 연관된 뷰를 의미
• 데이터의 수정/삭제/입력이 불가능한 읽기 전용이다.

뷰 생성

CREATE VIEW 뷰_이름
as 
    SELECT 문;

뷰 접근

SELECT 열_이름 FROM 뷰_이름
(WHERE 조건);

뷰는 테이블에 접근한 것과 동일한 결과를 얻을 수 있다.

뷰를 사용하는 이유

보안(Security)에 도움이 된다.
어떤 회사에 회원의 개인정보가 담겨있는 테이블이 있을 때, 사원에게 주민등록번호, 연락처 등 회원의 민감한 개인정보를 보여줄 필요는 없다.

필요한 데이터만 보여주도록 하면 된다.

이 때 원본 테이블을 건드리는 것이 아니라, 필요한 데이터만 보여줄 수 있도록 뷰를 생성하여 뷰에 접근해서 확할 수 있도록 하면 된다.

복잡한 SQL을 단순하게 만들 수 있다.
예를 들어 다음과 같이 쪼큼 복잡한 쿼리가 있다고 해보자.

SELECT b.mem_id, m.mem_name, b.prod_name, m.addr, concat(m.phone1, m.phone2) '연락처'
from buy b 
    inner join memebr m
    on b.mem_id = m.mem_id;

이 쿼리를 자주 사용한다면, 매번 이 코드를 입력해야하는데, 이 때 뷰를 사용하면 간단히 접근할 수 있다.

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 memㅠr m
    on b.mem_id = m.mem_id; 

이처럼 뷰로 지정해놓으면 복잡한 쿼리도 훨씬 간단히 접근할 수 있다.
필요하면 WHERE절도 사용할 수 있다.

뷰를 생성할 때

• 원본 테이블의 열이름과 다르게 하기위해 별칭을 사용할 수 있다.
  • 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;

    SELECT DISTINCT `Member ID`, `Member Name` FROM v_viewtest1; -- 백틱 사용

뷰를 수정할 때

ALTER VIEW 구문을 사용한다.

ALTER VIEW 뷰_이름
AS SELECT;

뷰를 삭제할 때

DROP VIEW를 사용한다.

DROP VIEW v_viewtest1;

뷰 덮어쓰기

CREATE OR REPLACE VIEW를 사용하여 기존에 뷰가 있으면 덮어쓸 수 있다.

CREATE OR REPLACE VIEW 뷰_이름
AS 
SELECT 문;

뷰 정보확인

DESCRIBE로 뷰의 정보를 확인할 수 있다.
테이블과 동일하게 정보를 보여주지만, PRIMARY KEY 등의 정보는 확인되지 않는다.

DESCRIBE 뷰_이름;

뷰의 소스코드 확인

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

SHOW CREATE VIEW 뷰_이름;

뷰를 통한 데이터 수정

update문을 이용해서 뷰를 통해서 데이터를 수정할 수 있다.

예시

UPDATE v_member SET addr = '부산' WHERE mem_id = 'BLK';

뷰를 통한 데이터 삭제

DELETE문을 이용해서 뷰를 통해서 데이터를 삭제할 수 있다.

뷰를 통한 데이터 입력

입력이 가능하긴 하나, 조건이 만족되어야 한다.
예를 들면 원본 데이터에는 컬럼이 4개이고, 뷰에는 원본 테이블에서 3개의 컬럼만 추출했다.
그리고 뷰를 통해서 데이터를 입력하려고 하는데, 하필 추출하지 않은 하나의 컬럼에 NOT NULL 옵션이 있으면, 실행과정에서 오류가 발생한다.

그래서 추출하지 않은 하나의 컬럼까지 포함시키도록 뷰를 재정의하거나,
혹은 원본 테이블에서 추출하지 않은 하나의 컬럼에 default값을 줘서 자동으로 값이 들어가게 하거나, 해서 값이 입력될 수 있는 조건을 만족해야한다.

WITH CHECK OPTION
뷰의 조건에는 맞지 않음에도 불구하고, 이상한 경로로 데이터가 입력될 때가 있다.
예를 들어,
원본 테이블에서 키가 167 이상인 회원만 추출한 뷰가 있다고 가정했을 때,
뷰를 통해서 키가 167미만으로 하여 데이터를 입력하면 입력할 때 오류가 발생해야 한다.
그런데, 값이 제대로 입력되었고 뷰에서는 확인이 되지 않는다.
이때 WITH CHECK OPTION 키워드를 통해서 뷰에 설정된 값의 범위가 벗어나는 경우 입력되지 않도록 할 수 있다.

create view v_height167
as 
select * from member where height >= 167; -- 키가 167이상인 회원 추출

select * from v_height167; -- 조회

DELETE FROM v_height167 where height < 167; -- 167이상인 사람만 추출했으므로, 167미만인 회원은 지워지지 않는다.

insert into v_height167 values('TRA', '티아라', 6, '서울', NULL, NULL, 159, '2005-01-01'); -- 167 미만인데 잘 들어감..
insert into v_height167 values('TRA', '티아라', 6, '서울', NULL, NULL, 159, '2005-01-01'); -- 167 미만인데 잘 들어감..

-- 13:51:14    insert into v_height167 values('TRA', '티아라', 6, '서울', NULL, NULL, 159, '2005-01-01')    1 row(s) affected    0.015 sec
-- 0.015 sec

select * from v_height167; -- 조회
-- GRL    소녀시대    8    서울    02    44444444    168    2007-08-02
-- ITZ    잇지    5    경남            167    2019-02-12
-- TWC    트와이스    9    서울    02    11111111    167    2015-10-19

ALTER VIEW v_height167
as 
    SELECT * from member where height >= 167
    WITH CHECK OPTION;

insert into v_height167 values('TRA', '티아라', 6, '서울', NULL, NULL, 159, '2005-01-01');  -- WITH CHECK OPTION 이후 오류 발생
-- 13:53:45    insert into v_height167 values('TRA', '티아라', 6, '서울', NULL, NULL, 159, '2005-01-01')    Error Code: 1369. CHECK OPTION failed 'market_db.v_height167'    0.000 sec

뷰를 통한 데이터 입력/수정/삭제는 단순 뷰(하나의 테이블로 만든 뷰)에만 적용된다.
복합 뷰(두 테이블을 조인한 결과)는 읽기 전용이므로, 뷰를 통한 데이터 입력/수정/삭제를 할 수 없다!

뷰가 참조하는 테이블의 삭제

• 뷰가 참조하는 테이블을 삭제하면, 조회할 수 없다는 메세지가 나온다.

DROP TABLE IF EXISTS buy, member; -- 뷰와 연관된 테이블 삭제
select * from v_height167; -- 뷰 조회
-- 13:57:53    select * from v_height167 LIMIT 0, 5000    Error Code: 1356. View 'market_db.v_height167' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them    0.000 sec

CHECK TABLE로 뷰의 상태를 확인할 수 있다.

CHECK TABLE 뷰_이름;

CHECK TABLE v_height167;
-- 'market_db.v_height167', 'check', 'Error', 'View \'market_db.v_height167\' references invalid table(s) -- 참조하는 테이블이 유효하지 않다는 메세지

2-4. 번외(눈에 띄는 것)

나는 포스팅할 때, Obsidian이라는 프로그램으로 마크다운을 작성해서 티스토리에 바로 올리는데, 이미지같은 경우는 벨로그에 복붙하면, 알아서 URL이 생성되고 편해서 벨로그를 가끔씩 켜두면서 블로그포스팅을 한다.
그런데, DB를 학습하는 중이라 그런지,, 눈에 갑자기 들어오는 게 있다.

바로 업로드 중이라고 나오면서, blob이라고 나온다.

동영상 같은 데이터는 이진데이터 형식의 BLOB 형식의 데이터가 적절하다는 것을 배웠던 게 엊그제인데, 이게 눈에 띈다. 허허허

3.요약

테이블을 생성하면서 제약조건을 넣어보고,
각종 키에 대한 제약조건 사항들을 알아보았다.

그리고 조금 어려운 개념이고 지루했었지만, 가상의 테이블 뷰를 생성하고, 뷰의 이점이 무엇인지, 뷰를 통해서 데이터 입력, 수정, 삭제가 가능하다는 것을 알아보았다.

REAL MYSQL을 위해서... 얼렁 다음 챕터로..!!