5. 테이블과 뷰-1

0. 학습 목표

• 테이블의 구조에 대해 완벽하게 이해
• 테이블의 핵심인 제약조건을 학습하고 적절하게 지정
• 뷰의 개념과 실제 작동하는 방법

0. 요약

• CREATE TABLE은 테이블을 생성하는 SQL로 테이블 이름, 열, 데이터 형식 등을 지정
• 열에 입력될 값이 1부터 자동 증가하도록 설정하려며 GUI에서는 AI를 체크하고 SQL에서는 AUTO_INCREMENT를 입력
• 열에 빈 값을 허용하지 않으려면 GUI에서는 NN, SQL에서는 NOT NULL
• 열을 기본 키로 지정하려면 GUI에서는 PK, SQL에서는 PRIMARY KEY
• 열을 외래 키로 지정하려면 FOREIGN KEY 예약어를 입력

---

1. 테이블 만들기

※핵심 키워드:  CREATE TABLE, AUTO_INCREMENT, NOT NULL, PRIMARY KEY, FOREGN KEY

테이블은 표 형태로 구성된 2차원 구조로 행과 열로 구성되어 있다. 그리고 행과 열은 아래와 같이 부르기도 한다.

• 행은 row, record라고도 부른다.
• 열은 column, field라고도 부른다.

---

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

쇼핑몰 데이터베이스인 네이버 쇼핑 데이터베이스를 설계하자. 

우선 회원 테이블인 member 테이블의 형식은 아래와 같다.

열 이름(한글)	열 이름(영문)	데이터 형식	널 허용 안함(NOT NULL)	기타
아이디	mem_id	CHAR(8)	YES	기본키(PK)
회원 이름	mem_name	VARCHAR(10)	YES
인원수	mem_number	TINYINT	YES
주소	addr	CHAR(2)	YES
연락처 국번	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(8)	YES
분류	group_name	CHAR(4)	NO
가격	price	INT	YES	UNSIGNED
수량	amount	SMALLINT	YES	UNSIGNED

---

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

GUI환경에서 테이블을 만들어 보자.

---

데이터베이스 생성하기

MySQL Workbrench에서 CREATE DATABASE naver_db; 를 통해 생성한다.

---

테이블 생성하기

먼저 member 테이블을 다음과 같이 생성하자.

• (<naver_db & Tables> 선택, 마우스 우클릭) -> <Create Table> -> <Table Name: member><위 member 테이블 형식으로 생성>

buy 테이블을 다음과 같이 생성하자.

• <naver_db & Tables> 선택 > 마우스 우클릭 > <Create Table> > <Table Name:buy><위 buy 테이블 형식으로 생성>

주의할 점은 GUI에서는 기본키-외래키 관계를 설정할 수 없다. 마지막에 SQL 스크립트를 약간 수정해 줘야 한다.

  FOREIGN KEY(mem_id) REFERENCES member(mem_id));

---

데이터 입력하기

<naver_db & member Tables> 마우스 우클릭 > <Select Rows - Limit 1000>을 하고 3개 행만 입력해 보자.

마찬가지로 buy 테이블에도 3개의 데이터를 입력해 보자.

적용 시 오류가 발생한다. 이유는 member와 buy 테이블은 기본키-외래키 관계이다. buy테이블의 mem_id는 반드시 member.mem_id에 존재해야하기 때문이다.

우선 APN행을 삭제하고 apply하자.

---

3-3. SQL로 테이블 만들기

데이터베이스 생성하기

기존 DB는 삭제하고 다시 naver_db를 생성해 보자.

DROP DATABASE IF EXISTS naver_db;
CREATE DATABASE naver_db;

---

테이블 생성하기

CREATE TABLE 구문으로 회원인 member 테이블의 스키마 부터 생성하자.

USE naver_db;
DROP TABLE IF EXISTS member;

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

이번에는 buy 테이블의 스키마도 생성하자.

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,
  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테이블에 데이터를 3건 입력해 보자. 동일하게 buy테이블에서 mem_id가 없기 때문에 에러가 발생한다.

INSERT INTO member VALUES ('TMC', '트와이스', 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');

SELECT * FROM member;

# mem_id, mem_name, mem_number, addr, phone1, phone2, height, debut_date
'BLK', '블랙핑크', '4', '경남', '055', '22222222', '163', '2016-08-08'
'TMC', '트와이스', '9', '서울', '02', '11111111', '167', '2015-10-19'
'WMN', '여자친구', '6', '경기', '031', '33333333', '166', '2015-01-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);

15:23:51	INSERT INTO buy VALUES (NULL, 'APN', '아이폰', '디지털', 200, 1)	Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`naver_db`.`buy`, CONSTRAINT `buy_ibfk_1` FOREIGN KEY (`mem_id`) REFERENCES `member` (`mem_id`))	0.000 sec

INSERT INTO buy VALUES (NULL, 'BLK', '지갑', NULL, 30, 2);
INSERT INTO buy VALUES (NULL, 'BLK', '맥북프로', '디지털', 1000, 1);

SELECT * FROM buy;

# num, mem_id, prod_name, group_name, price, amount
'1', 'BLK', '지갑', NULL, '30', '2'
'2', 'BLK', '맥북프로', '디지털', '1000', '1'