혼공학습단 11기 - 혼공S (4주차)

 

혼자 공부하는 SQL

#	진도	기본 미션	선택 미션
1주차 (1/2 ~ 1/7)	Chapter 01 ~ 02	p. 80의 shop_db의 회원 테이블(member)에서 아이유 회원에 대한 정보만 추출한 후 결과 화면 인증하기	데이터베이스 개체 3가지 설명하기
2주차 (1/8 ~ 1/14)	Chapter 03	p. 138의 확인 문제 2번 풀고 인증하기	데이터 입력, 삭제하는 기본 형식 작성하기
3주차 (1/15 ~ 1/21)	Chapter 04	p. 195의 확인 문제 4번 풀고 인증하기	p. 183 [좀 더 알아보기] 손코딩 실행하고 결과화면 인증하기
4주차 (1/22 ~ 1/28)	Chapter 05	p. 226의 market_db의 회원 테이블(member) 생성하고, p. 229 데이터 입력한 후 인증하기	p. 271 확인 문제 4번 풀고 인증하기
5주차 (1/29 ~ 2/4)	Chapter 06	p. 310 인덱스 생성하고 key_name이 PRIMARY로 출력된 결과 화면 캡처하기	인덱스 생성, 제거하는 기본 형식 작성하기
6주차 (2/5 ~ 2/12)	Chapter 07 ~ 08	p. 363 market_db의 고객 테이블(member)에 입력된 회원의 정보가 변경될 때 변경한 사용자, 시간, 변경 전의 데이터 등을 기록하는 트리거 작성하고 인증하기	p. 402 GUI 응용 프로그램 만들고 인증하기

 

 

이번 주 진도

#	진도	기본 미션	선택 미션
4주차 (1/22 ~ 1/28)	Chapter 05	p. 226의 market_db의 회원 테이블(member) 생성하고, p. 229 데이터 입력한 후 인증하기	p. 271 확인 문제 4번 풀고 인증하기

 

벌써 반환점을 돌아 4주 차!

책의 남은 부분도 점점 줄어들고 있지만, 매주 새로운 내용을 배운다기보다는 앞에서 훑어봤던 내용들을 하나씩 쪼개서 심화적으로 공부한다는 느낌이라 지금까지도 어렵지 않게 따라갈 수 있는 것 같다.

 

다음 주가 SQLD 접수던데 과연 내가 3월 9일까지 딸 수 있는 실력을 만들 수 있을지 잘 모르겠다...

일단 공부했으니 훈장을 하나 따고 싶기는 한데...

일단 기출문제집부터 사 와서 고민해 봐야겠다...

시험은 2~3달에 한 번 있는 것 같으니 문제 풀어보고 안 되겠으면 다음 기회에!

 

 

 

기본 미션

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

 

아래에 buy 테이블도 만들었지만 잠시 축소해 놓았다.

선택 미션

p. 271 확인 문제 4번 풀고 인증하기

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

[                      ] 뷰_이름
AS
	SELECT 문;

 

1) CREATE AND REPLACE VIEW

2) CREATE OR REPLACE VIEW

3) CREATE AND OVERWITE VIEW

4) CREATE OR OVERWRITE VIEW

A. 2

 

정리

 

05 - 1 테이블 만들기

 

1. 데이터베이스와 테이블 설계하기

  - 네이버 쇼핑 DB의 회원 테이블, 구매 테이블을 만들어보자.

  - 회원 테이블(member)

열 이름(한글)	열 이름(영문)	데이터 형식	널 허용 안함(Not Null)	기타
아이디	mem_id	CHAR(8)	Yes	기본 키(PK)
회원 이름	mem_name	VARCHAR(10)	Yes
인원수	mem_number	TINYINT	Yes
주소	addr	CHAR(2)	No
연락처 국번	phone1	CHAR(3)	No
전화번호	phone2	CHAR(8)	No
평균 키	height	TINYINT	No	UNSIGNED
데뷔 일자	debut_date	DATE	No

  - 구매 테이블(buy)

열 이름(한글)	열 이름(영문)	데이터 형식	널 허용 안함(Not Null)	기타
순번	num	INT	Yes	기본 키(PK), 자동 증가
아이디	mem_id	CHAR(8)	Yes	외래 키(FK)
제품 이름	prod_name	CHAR(6)	Yes
분류	group_name	CHAR(4)	No
가격	price	INT	Yes
수량	amount	SMALLINT	Yes

 

 

2. GUI 환경에서 테이블 만들기

  - 스키마 탭 -> naver_db -> Tables 우클릭 -> Create Table

 

    테이블 만들기는 전에도 간단히 체험한 적이 있다.

    위의 회원 테이블부터 참고하여 Column Name에는 열 이름, DataType에 데이터 형식 그리고 PK 인지, Null 허용하는지, UNSIGNED로 사용하는지 등을 체크해 주자.

 

 

    Apply를 누르면 이렇게 테이블을 만드는 SQL 구문도 보여준다.

 

 

    구매 테이블도 작성한다.

    그런데 FK는 체크하는 곳이 안 보인다.

 

 

    일단 Apply를 누르면 나오는 창에서 SQL 구문을 수정할 수도 있다.

    FOREIGN KEY를 mem_id로 지정해 주는데, 기본 키 - 외래 키 관계를 만들기 위해 REFERENCES를 member테이블의 mem_id로 지정해 주자.

 

  - 데이터 입력하기

 

    셀렉트 문으로 테이블을 열고 칸을 클릭하면 직접 데이터를 넣을 수 있다.

 

    참고로 FK에 PK 데이터에 존재하지 않는 것을 넣는다면 오류가 발생한다.

    회원가입하지도 않는 사람이 구매를 했으니 당연한 오류이다!

 

 

3. SQL로 테이블 만들기

- 회원 테이블

DROP DATABASE IF EXISTS naver_db;
CREATE DATABASE naver_db;
USE naver_db;
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
);

 

    일단 전에 만든 DB는 삭제하고 처음부터 다시 만들어봤다.

    CREATE TABLE 테이블이름 ( ); 이 괄호 안에 GUI 환경에서 입력했던 그대로 타이핑한다.

    열_이름 데이터_형식 NULL을 허용하는지 아닌지, PK인지, 기타 등등의 옵션을 넣어주면 된다.

    궁금해서 NOT NULL과 PRIMARY KEY의 순서를 바꿔봤는데 문제없이 작동하는 걸 보면 옵션을 넣는 순서는 상관없는 것 같다.

 

  - 구매 테이블

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

     

    구매 테이블도 만들어준다. 여기서는 AUTO_INCREMENT라는 옵션도 추가됐다.

    FK를 지정할 때는 FOREIGN KEY라는 예약어로 지정해야 하는데, 방금 GUI로 테이블을 만들 때도 확인한 부분이다.

 

- 데이터 입력하기

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

 

멤버 테이블과 구매 테이블에 데이터를 입력하자.

여기서 마지막 문장은 오류가 나는데 앞에서 말했듯 PK에 없는 APN이라는 데이터를 FK로 등록하려고 했기 때문이다.

 

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

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

  - 제약조건(constraint)은 데이터의 무결성을 지키기 위해 제한하는 조건이다.

  - MySQL의 대표적인 제약조건

    - PRIMARY KEY 제약조건

    - FOREIGN KEY 제약조건

    - UNIQUE 제약조건

    - CHECK 제약조건

    - DEFAULT 정의

    - NULL 값 허용

 

 

2. 기본 키 제약조건

  - 데이터를 구분할 수 있는 식별자를 기본 키(Primary Key)라고 한다.

  - 기본 키는 중복되어서는 안 되며, 테이블 당 1개만 가질 수 있으며, NULL을 허용하지 않는다.

 

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

 

    기본 적으로 열 이름 뒤에 PRIMARY KEY를 써서 적용할 수 있다.

 

     성공적으로 생성이 되었다. 테이블 정보는 DESCRIBE 예약어를 통해서도 볼 수 있다.

 

// 생성 구문 맨 뒤에서 추가
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 구문을 사용해 추가
CREATE TABLE member
( mem_id CHAR(8) NOT NULL,
  mem_name VARCHAR(10) NOT NULL,
  height TINYINT UNSIGNED NULL
);
ALTER TABLE member
	ADD CONSTRAINT -- 제약 조건을 추가하겠다는 뜻
    PRIMARY KEY(mem_id);

 

    기본 키는 위와 같은 방법으로도 설정할 수 있다.

 

 

3. 외래 키 제약조건

  - 기본 키가 있는 테이블을 기본 테이블이라 부르고, 외래 키가 있는 테이블을 참조 테이블이라 부른다.

  - 외래 키가 참조하는 열은 반드시 기본 키나 고유 키로 설정되어 있어야 한다.

  - 참조하고 있는 PK 데이터가 있어야만 실행되어야 하는 경우 사용할 수 있다.

    예시: 회원 가입 한 사람만 구매 가능

  - PK와 FK의 이름이 달라도 문법적 오류는 아니지만, 되도록 동일하게 지정하는 것이 좋다.

 

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

   

    기본 적인 외래 키 설정 방법이다.

 

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

 

마찬가지로 ALTER TABLE을 사용하여 추가하기도 한다.

 

 

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

  - 만약 아이디를 변경해 버린다면 누가 구매했는지 조회가 안 될 텐데 어떡하지?

 

    테스트를 위해 데이터를 추가했다.

 

 

    그리고 데이터를 변경, 삭제하려고 하니 오류가 떠버렸다.

    열 이름이 변경되면 참조 테이블의 데이터에 문제가 발생하기 때문에 막아놓은 것이다.

    그럼 어떻게 아이디를 변경해야 하는 걸까? 이런 기능을 지원하는 것이 ON UPDATE CASCADE 문이다.

 

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)
    ON UPDATE CASCADE
    ON DELETE CASCADE;

 

ON UPDATE CASCADE 문은 기준 테이블의 데이터가 변경되면 참조 테이블의 데이터도 변경되게 하고,

ON DELETE CASCADE 문은 기존 테이블의 데이터가 삭제되면 참조 테이블의 데이터도 삭제되게 한다.

 

 

5. 기타 제약조건

  - 고유 키(Unique) 제약조건

    중복되지 않는 유일한 값을 입력해야 하는 조건이다. 기본 키와 비슷하나 고유 키는 NULL 값을 허용하며, NULL 값은 여러 개가 입력되어도 상관없다.

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

 

  - 체크(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)
);

 

키가 100 이상이어야만 입력되도록 하였다.

 

  - 기본값(Default) 정의

     값을 입력하지 않았을 때 자동으로 입력될 값을 미리 지정해 놓는 방법이다. 마치 프로그래밍 언어의 함수에서 파라미터 기본 값을 지정해 주는 것과 같다.

CREATE TABLE member
( mem_id CHAR(8) NOT NULL PRIMARY KEY,
  mem_name VARCHAR(10) NOT NULL,
  height TINYINT UNSIGNED NULL DEFAULT 160
);

 

  - 널 값 허용

    NULL 값을 허용하지 않으려면 NOT NULL을 입력한다.

    허용하려면 입력하지 않거나 NULL을 입력한다.

 

05 - 3 가상의 테이블: 뷰 

1. 뷰(view)

  - 뷰는 데이터베이스 개체 중 하나로 테이블의 필요한 내용만 보이게 만든 가상의 테이블이다. 개념이 쉬우면서도 말로 표현하기 어려운데, 나는 VIEW를 SELECT 문을 실제 테이블처럼 사용하게 하는 것이라고 정의할 수 있을 것 같다.

 

  - 뷰의 기본 생성

CREATE VIEW 뷰_이름
AS
	SELECT 문;

 

    SELECT 문을 그대로 가상의 테이블로 만들었다.

    생성한 뷰는 테이블과 동일하게 사용할 수 있다. SELECT 문도 물론 사용 가능하다.

    추가로 뷰의 이름은 보통 이름 앞에 v_를 붙여 식별한다.

 

 

  - 뷰는 내부에서 SELECT문을 사용해 원본 테이블에서 나온 결과를 옮겨 보여준다.

 

 

2. 뷰를 사용하는 이유

  - 보안상의 이유

    비밀번호, 주민등록번호와 같은 보안을 중요로 하는 데이터들이 보이지 않도록, 다른 요소들만 보이는 뷰를 만든다. 예를 들어 은행에서는 직급, 직책에 따라 볼 수 있는 정보가 다르도록 뷰를 생성한다.

 

  - 단순하게 만들 수 있다

    자주 사용해야 하는 복잡한 SELECT 문을 미리 뷰로 만들어 놓는다면, 두세 번 복잡한 작업을 할 필요가 없다.

 

 

3. 뷰의 실제 작동

  - 실제 생성

    뷰의 실제 생성 예시이다.

    뷰를 생성하면서 뷰에서 사용될 열 이름을 다르게 지정할 수 있는데 기존에 배운 별칭을 사용하면 된다.

    별칭에서는 한글이나 띄어쓰기 사용이 가능하지만 주의할 점이 있는데 뷰를 조회할 때는 백틱(`: 키보드 1 왼쪽에 있음)으로 묶어줘야 한다는 것이다.

    별칭을 붙일 때 AS는 형식상 넣는 것으로 코드가 명확해 보인다.

 

  - 실제 수정

    뷰의 수정은 ALTER VIEW를 사용한다.

    예시에서는 열 별칭을 한글로 바꾸고 있다.

 

  - 실제 삭제

    DROP VIEW 뷰_이름으로 손쉽게 사용 가능하다.

 

    데이터베이스 개체에서 생성, 수정, 삭제하는 방법은 거의 동일하다.

    CREATE 개체_종류 / ALTER 개체_종류 / DROP 개체_종류

 

  - 정보 확인

    뷰도 DESCRIBE 문을 통해 정보를 확인할 수 있다.

    그러나 테이블과는 달리 PK 등 나타나지 않는 정보도 존재한다.

 

  - 뷰를 통한 데이터의 수정

    UPDATE 문을 사용하여 테이블을 수정하듯 뷰를 수정할 수 있다.

    주의점이 있는데, 뷰에 있는 데이터만 수정할 수 있다는 것이다.

    원본 테이블에는 있지만 뷰에는 없는 열까지는 손을 댈 수 없다.

 

  - 뷰를 통한 데이터의 삭제

    삭제도 DELETE문으로 삭제가 가능하지만, 수정과 마찬가지로 테이블에는 있지만 뷰에는 없는 데이터 까지 손을 댈 수는 없다.

    예를 들어 키가 167 이상인 데이터만 갖는 뷰를 생성해보자.

    여기서 167 미만인 데이터를 지우려 한다면

 

     당연히 오류가 발생한다.

 

  - 뷰를 통한 데이터의 입력

    입력도 수정 삭제와 비슷하다.

    일단 모든 열을 참조하는 뷰가 아니라면 입력에 문제가 생길 수 있다.

   

v_member 뷰는 모든 열을 참조하는 뷰가 아니다.

    게다가 원래 member 테이블의 열 중에서 mem_number 열은 NOT NULL이기 때문에 데이터가 입력되어야 한다.

    그런데 뷰에는 이 열이 없으니 데이터가 입력되지 않는 것이다.

    만약 뷰를 통해서 입력하고 싶다면, NULL을 허용하거나, 기본값을 지정해야 한다.

 

    조금 전에 만든 v_height167 뷰를 통해 더 알아보자.

    

    167 이상만 조회하는 뷰인데 167 미만인 데이터도 입력이 됐다고 뜬다.

     

    그러나 뷰에서는 보이지 않고

 

 

    참조 테이블에서만 확인할 수 있게 된다.

 

    이럴 때 WITH CHECK OPTION을 통해 뷰에 설정된 범위를 벗어나는 데이터는 입력되지 않도록 할 수 있다.

 

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

    뷰와 관계없이 참조하는 테이블은 삭제 가능하다!

    테이블을 삭제하고 뷰를 조회하려고 하면 오류가 발생한다.

 

 

 

 

 

 

이전 글 보기

 

1주 차

https://mountain-noroo.tistory.com/94

혼공학습단 11기 - 혼공S (1주차)

 

2주 차

https://mountain-noroo.tistory.com/97

혼공학습단 11기 - 혼공S (2주차)