MySQL 요약 노트-4

soyeong·2022년 10월 9일
mysql뷰테이블
0

MySQL

목록 보기
4/7
post-thumbnail

MySQL 요약 노트 (2022년 1월 ~ 2022년 3월까지 공부했던 내용 정리)
혼자 공부하는 SQL책과 한빛미디어에서 제공하는 유튜브 강의로 공부함

📌 5강 테이블과 뷰

✔️ 05-1 테이블 만들기

  • 데이터베이스와 테이블 설계
    • MySQL 워크벤치를 이용하여 테이블을 만드는 방법
    • 테이블: 표 형태로 구성된 2차원 구조, 행과 열
      • 행(=로우 or 레코드)
      • 열(=컬럼 or 필드)
    • 예시: 쇼핑몰 데이터베이스인 '네이버 쇼핑' 데이터베이스 구조 ➡️ 회원 테이블(member), 구매 테이블(buy)
    • 각 열에 가장 적합한 데이터 형식 지정
      • 회원 테이블(member)
      • 구매 테이블(buy)
  • MySQL 워크벤치의 GUI 환경에서 테이블 만들기(p.219-p.224 참고)
    • GUI(=Graphical User Interface): 윈도에서 진행하는 작업을 의미
      1) 데이터베이스 생성하기
      2) 테이블 생성하기
      3) 데이터 입력하기
  • MySQL 워크벤치의 SQL로 테이블 만들기
    1) 데이터베이스 생성하기: CREATE DATABASE
    DROP DATABASE IF EXISTS 데이터베이스_이름; -- 기존 해당 데이터베이스가 있다면 삭제
CREATE DATABASE 데이터베이스_이름; - 데이터베이스 생성
    2) 테이블 생성하기: CREATE TABLE
    • 테이블에 열_이름과 데이터형식 지정
    • <테이블 옵션 추가>
      1. NULL(or NOT NULL) 지정
      2. PRIMARY KEY 설정(기본 키 설정)
      3. AUTO_INCREMENT 설정(자동 증가)
    • 외래 키는 테이블을 만들 때 제일 마지막에 FOREIGN KEY 예약어로 지정하기
    USE 데이터베이스_이름;
DROP TABLE IF EXISTS 테이블_이름; -- 기존 해당 테이블이 있다면 삭제
CREATE TABLE 테이블_이름
( 열_이름1 데이터형식 AUTO_INCREMENT(자동 증가 설정) NULL(or NOT NULL) PRIMARY KEY(기본 키 설정),
  열_이름2 데이터형식 NULL(or NOT NULL) ,
  열_이름3 데이터형식 NULL(or NOT NULL) ,
  ...
  FOREIGN KEY(열_이름) REFERENCES 테이블_이름(열_이름) -- 외래 키(FK)
);
    3) 데이터 입력하기: INSERT INTO
    INSERT INTO 테이블_이름 VALUES(데이터1, 데이터2, ..., 데이터n);
INSERT INTO 테이블_이름 VALUES(데이터1, 데이터2, ..., 데이터n);

✔️ 05-2 제약조건으로 테이블 만들기

  • 제약조건: 데이터의 무결성을 지키기 위해 제한하는 조건
    • 데이터의 무결성: 데이터에 결함이 없음
      • EX) 네이버 회원의 아이디가 중복되면...? 안됨 ➡️ 결함을 미리 방지하기 위해 회원 테이블의 아이디를 기본 키(PK: 중복되지 않고, 비어 있지도 않음)로 지정
    • 대표적인 제약조건
      기본 키(FK) 제약조건, 외래 키(FK) 제약조건, 고유 키(Unique) 제약조건, 체크(Check) 제약조건, 기본값(Default) 정의, NOT NULL 제약조건 등
  • 기본 키 제약조건: 테이블을 구분할 수 있는 식별자
    • 기본 키에 입력되는 값은 중복될 수 없고, NULL 값이 입력될 수 없음
      • 예) 회원 테이블의 아이디, 학생 테이블의 학생, 직원 테이블의 사번 등
    • 대부분의 테이블은 기본 키를 가져야 함
    • 테이블은 기본 키를 1개만 가질 수 있음
    • 열의 정의 뒤에 PRIMARY KEY 예약어 지정
    • ALTER TABLE에서 기본 키 설정
      DROP TABLE IF EXISTS 테이블_이름;
CREATE TABLE 테이블_이름
( 열_이름1 데이터형식 NULL(or NOT NULL),
  열_이름2 데이터형식 NULL(or NOT NULL),
  ...
);
ALTER TABLE 테이블_이름
  ADD CONSTRAINT
  PRIMARY KEY (열_이름);
  • 외래 키 제약조건: 두 테이블 사이의 관계를 연결해주고, 그 결과 데이터의 무결성을 보장해주는 역할
    • 기본 키-외래 키 관계: 기본 키가 있는 테이블은 기준 테이블, 외래 키가 있는 테이블은 참조 테이블
    • 참조 테이블이 참조하는 기준 테이블의 열은 반드시 기본 키나, 고유 키로 설정되어 있어야 함
    • CREATE TABLE에서 외래 키 설정: FOREIGN KEY(열이름) REFERENCES 기준테이블(열이름)
      DROP TABLE IF EXISTS 테이블_이름1, 테이블_이름2;
CREATE TABLE 테이블_이름1 -- 기준 테이블
( 열_이름 데이터형식 NULL(or NOT NULL) PRIMARY KEY,
  열_이름2 데이터형식 NULL(or NOT NULL),
  ...
);
CREATE TABLE 테이블_이름2 -- 참조 테이블
( 열_이름1 데이터형식 NULL(or NOT NULL) PRIMARY KEY,
  열_이름 데이터형식 NULL(or NOT NULL),
  ...
  FOREIGN KEY(열_이름) REFERENCES 기준_테이블(열_이름);
);
    • ALTER TABLE에서 외래 키 설정
      DROP TABLE IF EXISTS 테이블_이름;
CREATE TABLE 테이블_이름 -- 참조 테이블
( 열_이름1 데이터형식 NULL(or NOT NULL) PRIMARY KEY,
  열_이름 데이터형식 NULL(or NOT NULL),
  ...
);
ALTER TABLE 참조테이블
  ADD CONSTRAINT
  FOREIGN KEY(열_이름)
  REFERENCES 기준_테이블(열_이름);
    • 기준 테이블의 열이 변결될 경우
      • 기본 키-외래 키로 맺어진 후에는 기준 테이블의 열 이름이 변경되지 않음(열 이름이 변경되면 참조 테이블의 데이터에 문제 발생)
      • ALTER TABLE에서 ON UPDATE CASCADE 문, ON DELETE CASCADE 문 설정: 기준 테이블의 열 이름이 변경될 때 참조 테이블의 열 이름이 자동으로 변경되도록 하는 기능
      DROP TABLE IF EXISTS 테이블_이름;
CREATE TABLE 테이블_이름 -- 참조 테이블
( 열_이름1 데이터형식 NULL(or NOT NULL) PRIMARY KEY,
  열_이름 데이터형식 NULL(or NOT NULL),
  ...
);
ALTER TABLE 참조_테이블
  ADD CONSTRAINT
  FOREIGN KEY(열_이름) REFERENCES 기준_테이블(열_이름)
  ON UPDATE CASCADE
  ON DELETE CASCADE;
  • 기타 제약조건(고유 키 제약조건, 체크 제약조건,기본값 정의
    ,널 값 허용)
  • 1) 고유 키 제약조건
    • 고유 키 제약조건: 중복되지 않는 유일한 값을 입력해야 하는 조건
    • 기본 키 제약조건과 거의 비슷하지만, 고유 키 제약조건은 NULL 값을 허용한다
      • 예) 회원 테이블 Email 주소
      • 열의 정의 뒤에 NULL, UNIQUE로 지정
  • 2) 체크 제약조건
    • 체크 제약조건: 입력되는 데이터를 점검하는 기능
      • 예) 평균 키에 마이너스 값이 입력되지 않게 함: CHECK(height>=100)
      • 열의 정의 뒤에 CHECK(조건) 추가
      • ALTER TABLE 문으로 CHECK(조건) 제약조건 추가
      ALTER TABLE 테이블_이름
  ADD CONSTRAINT
  CHECK (조건);
  • 3) 기본값 정의
    • 기본값 정의: 값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해 놓은 방법
      • 예) 키를 입력하지 않고 기본적으로 160이라고 입력되게 함: DEFAULT 160
      • 열의 정의 뒤에 DEFAULT '값' 추가
      • ALTER TABLE 문으로 DEFAULT '값' 제약조건 추가
      ALTER TABLE 테이블_이름
    ALTER COLUMN 열_이름 SET DEFAULT '값';
      • 기본값이 설정된 열에 기본값을 입력하려면 default라고 씀
      INSERT INTO 테이블_이름 VALUES(데이터, 데이터, 데이터, default);
  • 4) 널 값 허용
    • 널 값 허용: 널(Null) 값을 허용하려면 생략하거나 NULL을 사용, 허용하지 않으려면 NOT NULL을 사용함
      • NULL: 빈 값 허용
      • NOT NULL: 빈 값을 허용하지 않는다
      • 기본 키(PK)가 설정된 열에는 NULL 값이 있을 수 없음(NOT NULL)
      • NULL 값=아무 것도 없다라는 의미(공백('')이나 0과 다름)

✔️ 05-3 가상의 테이블: 뷰(View)

  • 뷰(View)

    • 뷰: 데이터베이스 개체 중 하나임, 바탕 화면의 바로 가기 아이콘과 비슷
      • 단순 뷰: 하나의 테이블과 연관된 뷰
      • 복잡 뷰: 2개 이상의 테이블과 연관된 뷰
    • 뷰의 기본 생성: CREATE VIEW
      CREATE VIEW 뷰_이름
AS
  SELECT 문;
    • 뷰에 접근하는 방식: SELECT
      SELECT 열_이름 FROM 뷰_이름
  [WHERE 조건];
    • 뷰를 사용하는 이유
      1. 보안에 도움이 됨
      2. 복잡한 SQL을 단순하게 만들 수 있음

  • 뷰의 실제 작동

    • 뷰의 실제 생성: CREATE VIEW
      • AS 별칭 사용(작은따옴표 또는 큰따옴표로 묶음, 띄어쓰기 가능)
      • 뷰를 조회할 때 열 이름에 공백이 있으면 백틱(`) 사용
      USE 데이터베이스_이름;
CREATE VIEW 뷰_이름
AS
  SELECT 문;
SELECT DISTINCT `뷰_열이름1`, `뷰_열이름2`, ... FROM 뷰_이름; -- 백틱(`)을 사용
    • 뷰의 수정: ALTER VIEW
      ALTER VIEW 뷰_이름
AS
  SELECT 문;
SELECT DISTINCT `뷰_열이름1`, `뷰_열이름2`, ... FROM 뷰_이름; -- 백틱(`)을 사용
    • 뷰의 삭제: DROP VIEW
      DROP VIEW 뷰_이름;
    • 뷰의 정보 확인: DESCRIBE, SHOW CREATE VIEW
      DESCRIBE 뷰_이름;
      SHOW CREATE VIEW 뷰_이름;
    • 뷰를 통한 데이터의 수정/삭제: UPDATE, DELETE
      UPDATE 뷰_이름 SET ... WHERE ...; -- 수정
      DELETE FROM 뷰_이름 WHERE...; -- 삭제
    • 뷰를 통한 데이터의 입력: INSERT
      • WITH CHECK OPTION 예약어: 뷰에 설정된 값의 범위가 벗어나는 값은 입력되지 않도록 함
      INSERT INTO 뷰_이름 VALUES(데이터1,데이터2,...,); -- 수정
      ALTER VIEW 뷰_이름
AS
  SELECT 문
  WITH CHECK OPTION;
INSERT INTO 뷰_이름 VALUES(데이터1,데이터2,...,); -- 수정
    • 뷰가 참조하는 테이블의 삭제: DROP TABLE
      • CHECK TABLE 문으로 뷰의 상태를 확인할 수 있음
      DROP TABLE IF EXISTS 테이블_이름;
      SELECT * FROM 뷰_이름; -- 에러 발생
      CHECK TABLE 뷰_이름; -- 뷰의 상태 확인

📌 참고문헌

우재남. (2021). 혼자 공부하는 SQL. 한빛미디어. p216-p270.

profile
soyeong
The ultimate goal is to be a Data Scientist.
이전 포스트

MySQL 요약 노트-3

다음 포스트

MySQL 요약 노트-5

0개의 댓글