혼자 공부하는 SQL_4주차 정리 및 미션

혼자 공부하는 SQL 4주차

※ 순서

Chp 5. 정리

미션

 

Chp 5. 테이블과 뷰

■ 테이블 만들기

-테이블: 표 형태로 구성된 2차원 구조, 행(로우, 레코드)과 열(컬럼, 필드)로 구성

• 데이터베이스와 테이블 설계하기
• GUI 환경에서 테이블 만들기
  • 데이터베이스 생성하기
    • CREATE DATABASE ex_db;
    • 잘 못 생성한 경우(삭제): DROP DATABASE ex_db;
  • 테이블 생성하기
    • 자동으로 생성된 SQL의 테이블 이름, 열 등은 백틱(`)으로 묶여 있지만 그렇지 않아도 상관 없음
  • 데이터 입력하기
    • 입력 시 Tab 키 누르면 다음 칸으로 이동
• SQL로 테이블 만들기
  • 데이터베이스 생성하기
    • CREATE DATABASE ex_db;
      • 기존에 ex_db가 있다면 삭제 후 생성: DROP DATABASE IF EXISTS ex_db;
  • 테이블 생성하기
    • NULL&NOT NULL
      • 지정하지 않으면 기본값으로 NULL
      • 직접 모두 지정해주는 것이 좋음
    • PRIMARY KEY
      • 기본 키 설정
      • NULL 값 허용 X → 생략해도 NOT NULL로 취급
    • AUTO_INCREMENT
      • PRIMARY KEY나 UNIQUE로 꼭 지정해야 함
    • FOREIGN KEY
      • 외래 키
      • 테이블 생성 시 제일 마지막에 지정
      • FOREIGN KEY(ex_id) REFERENCES extable(ex_id)
  • 데이터 입력하기

 

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

• 제약조건의 기본 개념과 종류
  • 제약조건: 데이터의 무결성을 지키기 위해 제한하는 조건
    • 데이터의 무결성 = 데이터에 결함이 없음
    • MySQL에서 제공하는 대표적인 제약조건
      • PRIMARY KEY 제약조건
      • FOREIGN KEY 제약조건
      • UNIQUE 제약조건
      • CHECK 제약조건
      • DEFAULT 정의
      • NULL 값 허용
• 기본 키 제약조건
  • 테이블의 많은 행 데이터 중에서 데이터를 구분할 수 있는 식별자 역할
  • 중복 X, NULL X
  • 자동으로 클러스터형 인덱스 생성
  • 테이블 당 기본 키 1개
  • CREATE TABLE에서 설정하는 기본 키 제약 조건(코드블럭 참고)
    • +테이블 삭제 순서
      • 기본 키-외래 키 연결 경우 외래 키 설정된 테이블 먼저 삭제 후 기 본키 설정된 테이블 삭제
  • ALTER TABLE에서 설정하는 기본 키 제약 조건(코드블럭 참고)
    • 이미 만들어진 테이블 수정
  • +기본 키에 이름 지정하기(코드블럭 참고)

-- CREATE TABLE에서 설정하는 기본 키 제약조건
-- 열 이름 뒤에 PRIMARY KEY 붙이기
USE naver_db;
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에서 설정하는 기본 키 제약조건
-- 마지막 행에 PRIMARY KEY 추가
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)
);

-- ALTER TABLE에서 설정하는 기본 키 제약조건
DROP TABLE IF EXISTS member;
CREATE TABLE member
(mem_id CHAR(8) NOT NULL,
 mem_name VARCHAR(10) NOT NULL,
 height TINYTINT UNSIGNED NULL
);
ALTER TABLE member
    ADD CONSTRAINT
    PRIMARY KEY (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)
);

• 외래 키 제약조건
  • 두 테이블 사이 관계 연결 → 데이터의 무결성 보장
  • 외래 키가 설정된 열은 다른 테이블의 기본 키와 꼭 연결
  • 기준 테이블: 기본 키 있는 테이블
  • 참조 테이블: 외래 키 있는 테이블
    • 참조하는 기준 테이블의 열은 반드시 기본 키 or 고유 키로 설정되어 있어야 함
  • CREATE TABLE에서 설정하는 외래 키 제약조건(코드블럭 참고)
  • ALTER TABLE에서 설정하는 외래 키 제약조건(코드블럭 참고)
  • 기준 테이블의 열이 변경될 경우
    • 기본 키-외래 키 관계 설정 후 기준 테이블의 열은 변경 or 삭제 X
    • 기준 테이블 열 이름 변경 시 참조 테이블 열 이름 자동으로 변경 → ON UPDATE CASCADE
    • 기준 테이블의 데이터 삭제 시 참조 테이블의 데이터도 삭제 → ON DELETE CASCADE

-- CREATE TABLE에서 설정하는 외래 키 제약조건
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)
);

-- 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
    ADD CONSTRAINT
    FOREIGN KEY(mem_id)
    REFERENCES member(mem_id);

• 기타 제약조건
  • 고유 키 제약조건(코드블럭 참고)
    • 중복되지 않는 유일한 값
    • NULL 값 허용(기본 키 제약조건과의 차이점)
    • 테이블에 여러 개 설정 가능(기본 키 제약조건과의 차이점)
  • 체크 제약조건(코드블럭 참고)
    • 입력되는 데이터 점검하는 기능
    • ALTER TABLE 문으로 제약조건 추가 가능
  • 기본값 정의(코드블럭 참고)
    • 값을 입력하지 않았을 때 자동으로 입력될 값 미리 지정
    • ALTER TABLE 사용 시 열에 DEFAULT 지정 위해 ALTER COLUMN 문 사용
  • NULL 값 허용
    • 생략 or NULL 사용
    • 허용 X: NOT NULL 사용
    • PRIMARY KEY 설정된 열은 자동 NOT NULL 인식
    • NULL = 아무 것도 없다 ≠ 공백 or 0

-- 고유 키 제약조건
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
);

-- 체크 제약조건
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 문 제약조건 추가
ALTER TABLE member
    ADD CONSTRAINT
    CHECK (phone1 IN ('02', '031', '032', '054', '055', '061'));

-- 기본값 정의
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 사용 시
ALTER TABLE member
    ALTER COLUMN phone1 SET DEFAULT '02';

 

■ 가상의 테이블: 뷰

-뷰: 데이터베이스 개체 중 하나, 테이블과 아주 밀접하게 연관

    -'가상의 테이블', '바로 가기 아이콘'

    -테이블처럼 데이터를 가지고 있지 X

    -단순 뷰: 하나의 테이블과 연관된 뷰

    -복합 뷰: 2개 이상의 테이블과 연관된 뷰, 테이블의 데이터 수정 X

        -두 테이블을 조인한 결과를 뷰로 만들 때 사용

        -읽기 전용, 데이터를 입력/수정/삭제 X

• 뷰의 개념
  • 뷰의 기본 생성(코드블럭 참고)
    • 뷰의 실체 = SELECT 문
      • 뷰를 만든 후에 테이블과 동일하게 사용하면 됨
  • 뷰의 작동
    • (사용자) → 조회 또는 변경 → (뷰; SELECT 문) → 쿼리 실행 → (테이블) → 쿼리 결과값 → (뷰; SELECT 문) → 결과 → (사용자)
    • 뷰는 기본적으로 '읽기 전용'
      • 뷰를 통해 원본 데이터 수정 가능(몇 가지 조건 만 족 시)
  • 뷰를 사용하는 이유
    • 보안(security)에 도움
      • 특정 사용자가 테이블에 접근 못하도록 함, 필요 부분에만 접근 가능
    • 복잡한 SQL 단순화
      
      • 복잡한 쿼리를 자주 사용해야함 → 뷰 생성
      • 해당 뷰에만 접근하면 됨

-- 뷰의 기본 생성
-- 뷰를 만드는 형식
CREATE VIEW 뷰_이름
AS
	SELECT 문;

-- 뷰에 접근하는 방식
SELECT 열_이름 FROM 뷰_이름
	[WHERE 조건];

• 뷰의 실제 작동
  • 뷰의 실제 생성, 수정, 삭제
    • 뷰에 사용될 열 이름을 테이블과 다르게 지정 가능
      • 별칭 사용
        
        • 열 이름 뒤에 작은 따옴표 or 큰 따옴표로 묶어줌
        • 형식상 AS 붙임(코드가 명확해 보이는 장점)
        • 중간에 띄어쓰기&한글 사용 가능
          • 뷰 조회 시 이름에 공백 있으면 백틱(`)으로 묶어줘야 함
    • 뷰의 수정
      • ALTER VIEW 사용
      • 열 이름에 한글 사용 가능(권장 X)
    • 뷰의 삭제
      • DROP VIEW 사용
    • + 데이터베이스 개체의 생성/수정/삭제
      • 데이터베이스 개체는 서로 완전히 다른 기능을 하지만 생성/수정/삭제 문법 거의 동일
      • 생성
        • CREATE 개체_종류
      • 수정
        • ALTER 개체_종류
      • 삭제
        • DROP 개체_종류
  • 뷰의 정보 확인
    • DESCRIBE 문 사용
      
      • PRIMARY KEY 등 정보 확인 X
    • SHOW CREATE VIEW
      • 뷰의 소스 코드 확인
    • + CREATE OR REPLACE VIEW
      • CREATE VIEW: 뷰 생성 시 기존에 뷰 있으면 오류 발생
      • CREATE OR REPLACE VIEW: 기존에 뷰 있어도 덮어쓰는 효과 내기 때문에 오류 발생 X
        • DROP VIEW와 CREATE VIEW를 연속으로 작성한 효과
  • 뷰를 통한 데이터의 수정/삭제
    • UPDATE
      • 뷰를 통해 데이터 입력하려면, 뷰에서 보이지 않는 테이블의 열에 NOT NULL이 없어야 함
    • DELETE
  • 뷰를 통한 데이터의 입력
    • WITH CHECK OPTION: 뷰에 설정된 값의 범위가 벗어나는 값은 입력되지 않도록 함
  • 뷰가 참조하는 테이블의 삭제
    • DROP
    • 테이블은 뷰가 참조하고 있어도 삭제됨

 

 

▶ 4주차 미션

    ▷ 기본 미션

<p.226 market_db의 회원 테이블(member) 생성 & p.229 데이터 입력 후 인증>