[MySQL] 테이블과 뷰

본격적인 시작에 앞서

안녕하세요? 벌써 혼공학습단 4주 차네요.

사실 이걸 쓰고 있는 지금은 1월 17일입니다.

1월 30일부터 2월 11일까지 여행 일정이 많아서 지금 쓰는데 너무 힘들지만

그래도 열심히 공부해 볼게요

테이블 만들기

테이블은 표 형태로 구성된 2차원 구조로, 행과 열로 구성되어 있습니다. 행은 로우나 레코드라고 부르며, 열은 칼럼 또는 필드라고 부릅니다.

GUI 환경에서 테이블 만들기

먼저 MySQL 워크벤치에 새로운 데이터 베이스를 만듭니다.

create database naver_db;

만든 데이터베이스를 확장한 후 tables에 우클릭하여 Create table을 선택합니다.

테이블의 칼럼을 구성합니다. PK와 NN, Datatype은 아래 게시물을 참고해 주세요

이미지에는 보이지 않지만 우측 하단의 apply를 클릭해 테이블을 만들어 주세요.

https://hulrud.tistory.com/2

[혼공S] 데이터 베이스와 SQL

https://hulrud.tistory.com/6

[혼공S] SQL 고급 문법

 

create table 코드를 확인할 수 있습니다.

apply를 클릭하면 테이블이 생성된 것을 확인할 수 있습니다.

SQL로 테이블 만들기

워크벤치로 만든 테이블을 SQL문으로 만들어 보겠습니다.

위 문장을 입력하여 데이터베이스를 생성하였습니다.

위에서 본 Create Table 문과 같습니다.

자동으로 생성 된 SQL의 테이블 이름, 열 등은 백틱(`)으로 묶였지만, 묶지 않아도 상관없습니다.

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

테이블을 만들 때에는 테이블의 구조에 필요한 제약조건을 설정해야 합니다. PRIMARY KEY와 FOREIGN KEY 가 대표적인 제약조건입니다.

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

제약조건은 데이터의 무결성을 지키기 위해 제한하는 조건입니다. 중복되지 않아야 할 값이 중복되거나 존재해야 하지만 존재하지 않는 값이 있는 등 결함이 없는 것을 데이터의 무결성이라고 합니다.

기본 키 제약조건

PRIMARY KEY(기본 키)는 행 데이터 중에서 데이터를 구분할 수 있는 식별자 역할을 합니다.

기본 키는 다음과 같은 제약 조건을 가집니다

• 기본 키의 입력되는 값은 중복될 수 없다
• NULL 값이 입력될 수 없다

또한 테이블에는 기본 키를 1개만 가질 수 있습니다. 각 열마다 기본 키를 지정할 수 없으며 하나의 열에만 기본 키를 설정해야 합니다.

외래 키 제약조건

FOREIGN KEY(외래 키)는 두 테이블의 관계를 연결해 줍니다. 외래 키가 설정된 열은 꼭 다른 테이블의 기본 키와 연결됩니다.

기본 키가 있는 테이블을 기준 테이블, 외래 키가 있는 테이블을 참조 테이블이라고 합니다.

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

외래 키의 형식은 다음과 같습니다

FOREIGN KEY (열_이름) RFERENCES 기준 테이블(열_이름)

기타 제약 조건

기타 제약 조건에서 고유 키, 체크, 기본값 정의, 널 값 허용에 대해 알아보겠습니다.

Unique(고유 키)

• 중복되지 않는 유일한 값을 입력해야 한다

고유키와 기본키의 다른 점은 고유키에는 NULL 값을 입력할 수 있고, 한 테이블의 여러 개의 고유 키가 존재할 수 있습니다.

Check(조건)

• 조건에 해당하는 데이터를 입력해야 한다

체크는 입력되는 데이터를 점검하는 기능을 합니다

열의 정의 뒤에 Check(조건)을 추가하여 사용할 수 있습니다

height INT CHECK( height >= 100 )

100보다 작은 값을 height에 입력할 시 오류를 발생시킵니다

Default(기본 값) 정의

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

다음과 같이 정의할 수 있습니다

height INT DEFAULT 160

height에 값을 넣지 않은 경우 자동으로 160이 입력됩니다.

NULL(널 값 허용)

not null과 달리 null 값을 허용합니다.

null은 아무것도 없다는 의미로 0이나 공백과는 다릅니다

가상의 테이블: 뷰

데이터베이스 개체

뷰는 데이터베이스 개체 중 하나입니다. 모든 데이터베이스개체는 테이블과 관련이 있지만 특히 뷰는 테이블과 아주 밀접하게 연관되어 있습니다. 하지만 뷰는 테이블처럼 데이터를 가지고 있지는 않습니다. 뷰의 실체는 SELECT문으로 만들어져 있기 때문에 뷰에 접근하는 순간 SLELECT 가 실행되고 그 결과가 화면에 출력되는 형식입니다.

뷰는 단순 뷰와 복합 뷰로 나뉘는데, 단순 뷰는 하나의 테이블과 연관된 뷰, 복합 뷰는 2개 이상의 테이블과 연관된 뷰를 말합니다.

뷰의 개념

뷰를 만드는 형식은 다음과 같습니다.

CREATE VIEW 뷰_이름
AS
    SELECT 문;

뷰를 만든 후에 뷰에 접근하는 방식은 테이블과 동일하게 SELECT문을 사용합니다. 전체의 점급할 수도 있고, 필요하면 조건식도 테이블과 동일하게 사용할 수 있습니다.

SELECT 열_이름 FROM 뷰_이름
WHERE 조건; -- 생략가능

ex)

뷰를 만들 때 SELECT 한 열을 불러오는 뷰를 만들었습니다.

뷰를 사용하는 이유는 다음과 같습니다

• 보안에 도움이 된다: 데이터베이스는 사용자마다 테이블에 접근하는 권한에 차별을 뒤서 처리합니다. 사용자가 뷰에만 접근할 수 있도록 하고, 뷰에 중요한 개인정보를 SELECT 하지 않는 방식으로 중요한 개인정보에 사용자가 접근할 수 없도록 간접적으로 제어할 수 있습니다.
• 복잡한 SQL문을 단순하게 만들 수 있다: WHERE문이나 JOIN을 사용하는 SQL문을 뷰로 생성해 놓고 사용자들은 해당 뷰에만 접근하도록 하면 사용자가 복잡한 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;

SELECT * FROM v_memberbuy WHERE mem_name = '블랙핑크';

뷰의 실제 작동

사용자가 뷰에 접근하는 방식은 다음과 같습니다

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

기본적인 뷰를 생성하면서 뷰에서 사용될 열 이름을 테이블과 다르게 지정할 수도 있습니다.

별칭은 열 이름 뒤에 작은따옴표 또는 큰따옴표로 묶어주고, 형식상 AS를 붙여줍니다

뷰를 조회할 때에는 열 이름에 공백이 있으면 백틱(`)으로 묶어줘야 합니다.

뷰의 수정은 ALTER VIEW 구문을 사용하며, 열 이름에 한글을 사용해도 됩니다

뷰를 통한 데이터의 입력

뷰를 통해 데이터를 입력할 수 있습니다. 테이블과 마찬가지로 INSERT 문을 통해 데이터를 입력할 수 있습니다.

INSERT INTO v_viewtest VALUES('TRA', '티아라', '도서', null);

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

DROP TABLE IF EXISTS buy, member;

뷰가 참조하는 테이블을 삭제할 시, 뷰를 조회할 수 없습니다.

미션

기본미션: market_db의 회원 테이블(member)을 생성하고, p229 데이터 입력한 후 인증하기

insert문을 통해 테이블에 행을 추가할 수 있습니다

선택미션: p271 확인문제 4번 풀고 인증하기

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

--                    -- 뷰_이름
AS
    SELECT 문;

1. CREATE AND REPLACE VIEW
2. CREATE OR REPLACE VIEW
3. CREATE AND OVERWRITE VIEW
4. CREATEE OR OVERWRITE VIEW

제가 생각한 정답은 4번이었는데, 실제 정답은 2번입니다. 직접 실행하여 살펴보겠습니다.

키워드 자체가 존재 X

마무리

이번 챕터에서는 테이블의 생성과 가상의 테이블 '뷰'에 대해 공부하였습니다.

저번 SQL고급 문법보다는 확실히 편안하네요...

이 글을 읽고 계신 혼공 S 참여자 분들은

SQL문법들은 아주아주 중요하니 2, 3차시를 꼭 복습하면서 진도 나가시면 좋을 거 같아요!