혼자 공부하는 SQL / Chapter 05 테이블과 뷰

05-1 테이블 만들기

• 테이블(table)
  • 표 형태로 구성된 2차원 구조, 행과 열로 구성
    • 행: row, record라고 부른다.
    • 열: column, field라고 부른다.
• 테이블 설계하기(구조 정의)
  1. 열에 적합한 데이터 형식 지정
  2. 기본 키 지정
  3. 외래 키 지정
• 테이블 만들기
  • 데이터베이스 생성 (CREATE DATABASE)
  • 테이블 생성 (CREATE TABLE)
    • 빈 값 허용하지 않을 시 NOT NULL
    • 열 기본키로 지정시 PRIMARY KEY
    • 열 외래키로 지정시 FOREIGN KEY
    • 열 1부터 자동 증가시 AUTO_INCREMENT

 

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

• 제약조건(constraint)
  • 데이터의 무결성을 지키기 위해 제한하는 조건
  • 여기서 데이터의 무결성이란? 데이터에 결함이 없는 것을 의미
• 제약조건의 종류
  • PRIMARY KEY
    • 기본키
    • 값이 중복되지 않아야 하고 NULL 입력 불가, 1개의 테이블에서 기본키 1개씩만 입력가능!
    • 데이터를 구분할 수 있는 식별자 ex) 사번, 학번, 아이디…
    • 형식: PRIMARY KEY(열이름)
  • FOREIGN KEY
    • 외래키
    • 두 테이블 사이의 관계 연결
    • 외래키가 설정된 열은 다른 테이블의 기본키와 연결! ( 기본키(기준 테이블) - 외래키(참조 테이블) 관계 )
    • 연결 후에는 기준 테이블의 열 이름이 변경, 삭제되지 않는다.
    • 만약 기준 테이블의 열 이름이 변경될 때 참조 테이블의 열 이름이 자동으로 변경이나 삭제되게 하고 싶으면! ON UPDATE CASCADE나 ON DELETE CASCADE 사용
    • 형식: FOREIGN KEY(열이름) REFERENCES 기준테이블이름(열이름)
  • UNIQUE
    • 고유키
    • 중복되지 않는 유일한 값을 입력해야 한다.
    • NULL 값을 허용한다.
    • 한 테이블에 고유키를 여러 개 설정할 수 있다. (기본키는 1개자나)
  • CHECK
    • 조건을 줘서 입력되는 데이터를 점검한다.
    • 형식: CHECK(조건식)
  • DEFAULT
    • 값을 입력하지 않았을 때 자동으로 들어가는 기본값 지정
    • 형식: DEFAULT 기본값
  • NULL
    • 값을 꼭 입력해야 한다면 NOT NULL
    • NULL은 아무 것도 없다는 뜻! 공백, 0 아님. 없어요 그냥 없어요

 

05-3 가상의 테이블: 뷰

• 뷰(view)
  • 테이블 같지만 데이터는 가지고 있지 않은… 바로가기 아이콘과 같은 느낌
  • 단순뷰와 복합뷰로 나뉜다. 단순은 1개, 복합은 2개 이상의 테이블과 연관된 뷰
• 뷰 생성/삭제
  • CREATE VIEW 뷰이름 AS SELECT문;
  • 삭제는 DROP VIEW 뷰이름;
• 뷰 수정?
  • 기본적으로는 읽기 전용!!
  • 하지만 몇 가지 조건 만족 시 원본 테이블의 데이터를 수정 가능. (하지만 뷰의 사용 이유는 거의 읽기 전용이 아닐까… )
• 뷰 사용 이유?
  • 보안에 도움! 예로 다른 사용자에게 일을 시킬 때 개인정보 같이 전체 테이블을 보여줄 순 없으니, 필요한 정보만 쏙쏙 뽑아서 뷰를 생성하여 접근을 준다면 훨씬 안전.
  • 복잡한 SQL문을 단순하게 만들 수 있다. 자주 사용하는 데이터만 뽑아서 뷰로 만들어 두면 일할 때 편안.

 

---

미션

• 기본 미션
  • p. 226의 market_db의 회원 테이블(member) 생성하고, p. 229 데이터 입력한 후 인증하기
    • 회원 테이블(member) 생성
      • SQL 문

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

    • 회원(member), 구매(buy) 테이블 데이터 입력
      • SQL 문

      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 buy values(null, 'BLK', '지갑', null, 30, 2);
      insert into buy values(null, 'BLK', '맥븍프로', '디지털', 1000, 1);
      insert into buy values(null, 'APN', '아이폰', '디지털', 200, 1);
      

    • 출력 화면

• 선택 미션
  • p. 271 확인 문제 4번 풀고 인증하기
    • 다음은 기존에 뷰가 있으면 덮어쓰고, 없으면 새로 생성하는 SQL입니다. 빈칸에 들어갈 내용을 고르세요.
    •  
    • (빈칸) 뷰_이름 AS SELECT 문
      • 정답! 2번 CREATE OR REPLACE VIEW