[SQL] Chapter 5. (12~14강)

12강.

테이블 생성

MySQL에서는 SCHEMA와 DATABASE가 동일한 의미로 사용된다.
- 그러나, Oracle같은 곳에서의 schema는 특정 database의 부분(단일 사용자에게 소유된 테이블이나 객체)를 의미한다.

member 테이블

CREATE DATABASE naver_db ;

USE naver_db ;
CREATE TABLE member     #회원 테이블
( mem_id        CHAR(8) NOT NULL PRIMARY KEY,    #회원 아이디(PK)
  mem_name      VARCHAR(10) NOT NULL,     #이름
  mem_number    TINYINT NOT NULL,     #인원수
  addr          CHAR(2) NOT NULL,     #주소(경기, 서울, 경남 식으로 2글자만 입력)
  phone1        CHAR(3) NULL,     #연락처의 국번(02, 031, 055 등)
  phone2        CHAR(8) NULL,     #연락처의 나머지 전화번호(하이픈 제외)
  height        TINYINT UNSIGNED NULL,     #평균 키, UNSIGNED는 범위를 양수로 옮김.
  debut_date    DATE NULL     #데뷔 일자
) ;

INSERT INTO member VALUES('TWC', '트와이스', 9, '서울', '02', '11111111', 167, '2015-10-19') ;
INSERT INTO member VALUES('BLK', '블랙핑크', 4, '경남', '055', '22222222', 163, '2016-8-8') ;
INSERT INTO member VALUES('WMN', '여자친구', 6, '경기', '031', '33333333', 166, '2015-1-15') ;
INSERT INTO member VALUES('OMY', '오마이걸', 7, '서울', NULL, NULL, 160, '2015-4-21') ;
INSERT INTO member VALUES('GRL', '소녀시대', 8, '서울', '02', '44444444', 168, '2007-8-2') ;
INSERT INTO member VALUES('ITZ', '잇지', 5, '경남', NULL, NULL, 167, '2019-2-12') ;
INSERT INTO member VALUES('RED', '레드벨벳', 4, '경북', '054', '55555555', 161, '2014-8-1') ;
INSERT INTO member VALUES('APN', '에이핑크', 6, '경기', '031', '77777777', 164, '2011-2-10') ;
INSERT INTO member VALUES('SPC', '우주소녀', 13, '서울', '02', '88888888', 162, '2016-2-25') ;
INSERT INTO member VALUES('MMU', '마마무', 4, '전남', '061', '99999999', 165, '2014-6-19')

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 ,     #수량
   FOREIGN KEY(mem_id) REFERENCES member(mem_id)
) ;

INSERT INTO buy VALUES( NULL, 'BLK', '지갑', NULL, 30, 2) ;
INSERT INTO buy VALUES( NULL, 'BLK', '맥북프로', '디지털', 1000, 1) ;
INSERT INTO buy VALUES( NULL, 'APN', '아이폰', '디지털', 200, 1) ;
INSERT INTO buy VALUES( NULL, 'MMU', '아이폰', '디지털', 200, 5) ;
INSERT INTO buy VALUES( NULL, 'BLK', '청바지', '패션', 50, 3) ;
INSERT INTO buy VALUES( NULL, 'MMU', '에어팟', '디지털', 80, 10) ;
INSERT INTO buy VALUES( NULL, 'GRL', '혼공SQL', '서적', 15, 5) ;
INSERT INTO buy VALUES( NULL, 'APN', '혼공SQL', '서적', 15, 2) ;
INSERT INTO buy VALUES( NULL, 'APN', '청바지', '패션', 50, 1) ;
INSERT INTO buy VALUES( NULL, 'MMU', '지갑', NULL, 30, 1) ;
INSERT INTO buy VALUES( NULL, 'APN', '혼공SQL', '서적', 15, 1) ;
INSERT INTO buy VALUES( NULL, 'MMU', '지갑', NULL, 30, 4) ;

13강.

제약 조건

제약조건은 데이터의 무결성을 지키기 위해 제한하는 조건이다.
- 데이터의 무결성이랑 '데이터에 결함이 없음'이란 의미이다.
- 간단한 예로 네이버 회원의 아이디가 중복되면 상단한 혼란이 일어날 것이다. 이런 것이 바로 데이터의 결함이고, 이런 결함이 없는 것을 데이터의 무결성이라고 한다.

MySQL에서 제공하는 대표적인 제약조건

제약조건	설명
PRIMARY KEY 제약조건	· 데이터를 구분할 수 있는 식별자, · 테이블 당 기본 키는 1개의 열에만 설정 가능. · 중복될 수 없고, NULL 값을 허용하지 않는다. · 기본 키로 생성한 것은 자동으로 클러스터형 인덱스가 생성된다.
FOREIGN KEY 제약조건	· 두 테이블 사이의 관계를 연결해주고, 그 결과 데이터의 무결성을 보장해주는 역할. · 외래 키가 설정된 열은 반드시 다른 테이블의 기본 키와 연결된다. · 기본 키 or 고유 키가 있는 테이블(=기준 테이블), 외래 키가 있는 테이블(=참조 테이블)
UNIQUE 제약조건	· 기본 키 제약조건과 비슷하지만, 고유 키 제약조건은 NULL 값을 허용. · 테이블 당 고유 키는 여러 개 설정 가능.
CHECK 제약조건	· 입력되는 데이터를 점검하는 기능을 한다. · 예를 들어, 평균 키에 음수 값이 입력되지 않도록 하거나, 연락처의 국번에 02, 031, 041, 055 중 하나만 입력되도록 할 수 있다.
DEFAULT 정의	· 값을 입력하지 않았을 때, 자동으로 입력될 값을 미리 지정해 놓는 방법이다.
NULL 값 허용	· NULL 값을 허용하려면 생략 혹은 NULL을 사용, 허용하지 않으려면 NOT NULL을 사용. · NULL 값은 '아무것도 없다.'라는 의미이다. 공백이나 0과는 엄연히 다르다. · 기본 키가 설정된 열에는 NULL 값이 있을 수 없기에, 자동으로 NOT NULL을 사용.

PRIMARY KEY(기본 키) 제약조건

테이블을 만들 때 기본 키 설정.

CREATE TABLE member 
( mem_id  CHAR(8) NOT NULL PRIMARY KEY,     # mem_id 뒤에 PRIMARY KEY를 통해 기본키로 설정
  mem_name    VARCHAR(10) NOT NULL, 
  height      TINYINT UNSIGNED NULL
) ;

# mem_id를 PRIMARY KEY로 설정하는 방법 위, 아래 동일.

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(mem_id)를 통해 기본키로 설정
) ;

테이블을 만든 후에 기본 키 설정

CREATE TABLE member 
( mem_id  CHAR(8) NOT NULL, 
  mem_name    VARCHAR(10) NOT NULL, 
  height      TINYINT UNSIGNED NULL
) ;     # 이미 만들어진 member 테이블

ALTER TABLE member    # member 테이블을 수정
     ADD CONSTRAINT     # 제약조건을 추가
     PRIMARY KEY (mem_id) ;     # mem_id는 PRIMARY KEY

FOREIGN KEY(외래 키) 제약조건

테이블을 만들 때 외래 키 설정

# 기준 테이블
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)  # mem_id는 member 테이블의 mem_id를 참조한 외래키
) ;

테이블을 만든 후에 외래 키 설정

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

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

기준 테이블(member)	참조 테이블(buy)

만약, 기준 테이블인 member의 mem_id가 아이디 변경(수정), 회원 탈퇴(삭제) 등으로 변동이 있으면, 참조 테이블인 buy의 mem_id는 변동 이전의 상태로 남아있기에 데이터 무결성에 문제가 생긴다.

UPDATE member SET mem_id = 'PINK' WHERE mem_id='BLK' ;     #아이디 변경
DELETE FROM member WHERE  mem_id='BLK' ;     #회원탈퇴

# 위의 두 가지 경우 모두 오류 발생.

위 같은 문제는 기준 테이블의 PK의 변동 사항이 참조 테이블의 FK에 반영이 된다면 해결될 것이다.
- 참조 테이블에 ON UPDATE CASCADE, ON DELETE CASCADE를 추가하면 된다.

ALTER TABLE buy
    ADD CONSTRAINT 
    FOREIGN KEY(mem_id) REFERENCES member(mem_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE ;

위처럼 참조 테이블(buy)를 수정해주면 수정, 삭제 모두 가능

UNIQUE(고유 키) 제약조건

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

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 값을 동일하게 넣으면 오류가 발생한다.

CHECK 제약조건

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

평균 키가 100 이상인 값만 입력되도록 체크 제약조건을 설정.
- 100 미만인 값은 입력, 출력 모두 되지 않는다.

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

member 테이블에 phone1은 02, 031, 032, 054, 055, 061만 들어갈 수 있도록 체크 제약조건을 추가.

DEFAULT(기본 값) 정의

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를 160으로 설정.

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

phone1의 DEFAULT를 02로 추가 정의.

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

출력 결과

우주소녀는 정의된 DEFAULT 값으로 출력되는 것을 확인할 수 있다.

14강.

VIEW(뷰)

View는 실질적으로 데이터가 존재하지 않는 테이블이다.
- 이 테이블을 가상 테이블이라고도 부른다.
- 실제 정보를 가지고 있는 테이블을 물질 테이블이라고 하고, 그 테이블에서 필요한 정보만 가상의 테이블을 만들어서 읽어 오는 것이다.

v_member VIEW

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

출력 결과

v_memberbuy VIEW

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 ;

출력 결과

v_viewtest1 VIEW

CREATE VIEW v_viewtest1
AS
    SELECT B.mem_id 'Member ID', M.mem_name 'Member Name', 
            B.prod_name 'Product Name', 
            CONCAT(M.phone1, M.phone2) '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

출력 결과

v_viewtest1에서 `Member ID`, `Member Name`을 중복을 제외하고 출력

v_viewtest2 VIEW

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

출력 결과

CREATE OR REPLACE VIEW는 새롭게 뷰를 생성하거나, 동일한 뷰가 존재하면 덮어씌어 뷰를 갱신한다.
- 위 결과는 v_viewtest2의 속성

v_viewtest2의 속성과 member의 속성의 차이점으로는 뷰는 PK가 표시 되지 않는다.
- 즉, 뷰에서 PK가 아니라고, 실제 테이블의 PK가 아닌 것은 아님.

v_member 수정/입력

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

출력 결과

경남에서 부산으로 수정된 것을 확인할 수 있다.
- 이때 실제 테이블인 member도 수정됨.

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

위와 같이 뷰에 값을 입력하려 하면 오류가 발생한다.
- 입력받은 값은 실제 테이블인 member에 입력된다.
- 그러나 v_member에서 입력할 수 있는 값은, NOT NULL인 mem_number를 포함하지 않으므로 입력이 불가능.

v_height167 VIEW

CREATE VIEW v_height167
AS
    SELECT * FROM member WHERE height >= 167

height가 167 이상인 값만 출력하는 view

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

출력 결과

member에 값은 입력이 되지만, 뷰의 조건이 height가 167 이상만 출력하기에 v_height167에서는 보이지 않는다.

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

애초에 v_height167에서 height가 167 이상인 값만 입력받을 수 있도록 WITH CHECK OPTION로 설정 가능하다.

'Study' 카테고리의 다른 글

[SQL] Chapter 6. (15~17강) (0)	2025.07.07
[SQL] Chapter 4. (9~11강) (0)	2025.07.07
[SQL] Chapter 3. (6~8강) (0)	2025.07.07
[SQL] Chapter 2. (3~5강) (4)	2025.07.07
[Computer Vision] Dolphin, Shark, Whale을 분류하는 ResNet50 파이프라인 구축 (0)	2024.08.29

Hwan's AI

[SQL] Chapter 5. (12~14강)

12강.

테이블 생성

member 테이블

buy 테이블

13강.

제약 조건

PRIMARY KEY(기본 키) 제약조건

FOREIGN KEY(외래 키) 제약조건

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

UNIQUE(고유 키) 제약조건

CHECK 제약조건

DEFAULT(기본 값) 정의

14강.

VIEW(뷰)

v_member VIEW

v_memberbuy VIEW

v_viewtest1 VIEW

v_viewtest2 VIEW

v_member 수정/입력

v_height167 VIEW

'Study' 카테고리의 다른 글

티스토리툴바

[SQL] Chapter 5. (12~14강)

12강.

테이블 생성

member 테이블

buy 테이블

13강.

제약 조건

PRIMARY KEY(기본 키) 제약조건

FOREIGN KEY(외래 키) 제약조건

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

UNIQUE(고유 키) 제약조건

CHECK 제약조건

DEFAULT(기본 값) 정의

14강.

VIEW(뷰)

v_member VIEW

v_memberbuy VIEW

v_viewtest1 VIEW

v_viewtest2 VIEW

v_member 수정/입력

v_height167 VIEW

'Study' 카테고리의 다른 글

관련글

티스토리툴바