[혼공S] CH5-(1). 테이블 만들기

테이블(table)은 표 형태로 구성된 2차원 구조로, 행과 열로 구성되어 있다.

여기서 행(row)은 레코드(record)라고도 불리며 열(column)은 필드(field)라고도 불린다.

 

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

테이블을 만들기 전에는 설계를 먼저해야한다. 테이블 설계는 테이블 이름, 열 이름, 데이터 형식, 기본 키 등을 설정하는 것이다. 

 

GUI 환경에서 테이블 만들기

데이터 베이스 생성하기

간단한 쿼리문을 통해 naver_db라는 데이터베이스를 만들어보기로 하자.

# 간단한 SQL 문으로 데이터베이스 만들기
CREATE DATABASE naver_db ;

SQL로 만든 데이터베이스는 화면에 바로 적용되지 않기 때문에 SCHEMAS 패널에 보이지 않는다. 

그때는 SCHEMAS 패널의 빈 곳에서 마우스 오른쪽 버튼을 클릭하고 'refresh all'을 누르면 된다.

테이블 생성하기

방금 만든 naver_db를 확장한 다음, table부분에서 마우스 오른쪽을 클릭하고 create table을 선택한다.

그리고 앞서 설계했던 테이블의 이름과 열, 데이터 타입을 입력한 후 apply-finish를 누르면 테이블이 생성된다. 

마찬가지로, 설계했던 buy 테이블도 만든 후에, member 테이블의 mem_id와 buy 테이블의 mem_id를 PK-FK 관계를 설정해주기 위해 다음과 같이 쿼리문을 수정해야 한다. GUI로는 PK-FK 관계를 선택할 수 없기 때문이다.

CREATE TABLE buy ( 
   ~~~
   ~~~
   amount SMALLINT UNSIGNED NULL, 
   PRIMARY KEY num, 
   FOREIGN KEY (mem_id) REFERENCES member(mem_id) 
 ) ;

생성된 FOREIGN KEY는 위와 같이 SCHEMAS 패널에서 확인할 수 있다.

 

데이터 입력하기

SCHEMAS 패널에서 tables의 member 테이블에 마우스 오르쪽을 클릭하여 select rows - limit 1000을 선택하면 비어져있는 테이블이 나타난다. 그리고 insert new row 아이콘을 클릭하고 다음과 같은 데이터를 입력한 후 apply-finish 버튼을 차례대로 누른다.

buy 테이블에도 마찬가지로 테이블을 입력해준다.

하지만 buy 테이블에 데이터를 입력하고 apply - finish 를 클릭하면 error가 발생하게 되는데 그 이유는, 

buy 테이블의 mem_id 값은 반드시 member 테이블의 mem_id로 존재해야 한다는 것 때문이다. (회원가입을 하고 나서 구매를 해야한다는 것)

따라서 'APN' 아이디에 해당되는 열을 buy 테이블에서 삭제한 후 테이블을 만들어보기로 하자.

 

SQL로 테이블 만들기

다시 처음부터 naver_db라는 데이터베이스를 먼저 만들어보자.

# SQL로 테이블 만들기
# 데이터베이스 만들기 
DROP DATABASE IF EXISTS naver_db ; 
CREATE DATABASE naver_db ;

테이블 생성하기

CREATE 구문으로 member 테이블을 만들어보기로 한다. 

# member 테이블 만들기
USE naver_db ; 
DROP TABLE IF EXISTS member ; 
CREATE TABLE member (
   mem_id       CHAR(8), 
   mem_name     VARCHAR(10),
   mem_number   TINYINT, 
   addr         CHAR(2),
   phone1       CHAR(3), 
   phone2       CHAR(8), 
   height       TINYINT   UNSIGNED, 
   debut_date   DATE
) ;

여기서 아직 PK, NN 등의 옵션을 추가해본다. 다음과 같이 빈 테이블 프레임이 만들어진 것을 확인할 수 있다.

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

SELECT * FROM member ;

이번에는 buy 테이블을 만들어보자. 여기서 buy 테이블에서 mem_id를 FOREIGN KEY로 하여 member 테이블의 PRIMARY KEY인 mem_id와 연결하도록 한다. 

즉, buy 테이블의 mem_id열은 혼자 존재하는 것이 아니라, 꼭 member 테이블의 mem_id에도 값이 있어야 한다는 것을 의미한다. 

# 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) -- 이 테이블의 mem_id 열을 member 테이블의 mem_id와 연결
) ;

데이터 입력하기

# 데이터 입력하기
DESC member ; 
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') ; 

DESC buy ; 
INSERT INTO buy VALUES(NULL, 'BLK', '지갑', NULL, 30, 2) ; 
INSERT INTO buy VALUES(NULL, 'BLK', '맥북프로', '디지털', 1000, 1) ; 
INSERT INTO buy VALUES(NULL, 'APK', '아이폰', '디지털', 200, 1) ; -- 에러발생

SELECT * FROM member ;
SELECT * FROM buy ;

 

Reference

혼자 공부하는 SQL, 우재남 지음