[혼공SQL] chapter 5. 테이블과 뷰(1)

여정이·2024년 7월 24일

혼자 공부하는 SQL

목록 보기

14/28

📒 요약 : 테이블은 표 형태로 표된 2차원 구조로, 데이터베이스의 기본 구조이다. MySQL 워크벤치에서는 gui 환경으로도 테이블을 생성할 수 있고, SQL문을 직접 입력해서도 테이블을 생성할 수 있다.

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

2장에서 배웠던 데이터베이스 모델링에 대한 개념을 복습하는 느낌으로 아래의 과정을 따라가보자. 우선 익숙한 테이블 구조를 사용하는 것이 이해하는 데 도움이 될 것이니, 앞에서 사용했던 회원 테이블과 구매 테이블을 이용하여 네이버 쇼핑 DB를 만ㄷ르어 볼 것이다.

그림1. 회원 테이블(member)

그림2. 구매 테이블(buy)

각각의 테이블은 아래의 표와 같이 설계된다. 속성과 열 이름, 데이터 형식, 그리고 not null과 같은 제약 조건을 확인하자. 구매 테이블의 경우 아이디를 외래 키로 설정한다. 외래 키로 설정하려면 구매 테이블의 아이디와 회원 테이블의 아이디를 연결해야 한다.

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

표1. 회원 테이블 설계(member)

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

표2. 구매 테이블 설계(buy)

위와 같이 데이터베이스 설계가 완료되었으면, 이제 다음에는 테이블을 직접 생성하고 데이터를 삽입해보겠다.

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

그림3. naver_db 데이터베이스 생성

먼저 MySQL 워크벤치의 왼쪽 페널에서 위와 같이 naver_db라는 이름의 데이터베이스를 생성한다. 데이터베이스를 생성하면 naver_db 데이터베이스가 만들어지는데, 해당 패널을 열고, Tables를 클릭 후 우클릭, Create Table을 클릭한다. 그러면 아래와 같이 테이블을 생성할 수 있는 UI가 출력된다. 이제 위에서 테이블을 설계한 대로 아래와 같이 값을 집어넣으면 된다. 이 때 Not Null과 같은 제약 조건을 꼭 확인해주자. 마지막으로 apply 버튼을 클릭하면 끝. 이렇게 member와 buy 테이블을 모두 생성해준다. 참고로, FK와 PK의 관계는 gui 화면에서 설정할 수 없다. 그래서 apply버튼을 클릭하면 나오는 SQL 창에서 임의로 설정해주어야 함을 유의하자. 생성된 외래 키는 SCHEMAS 패널에서 확인할 수 있다.

그림4. 테이블 생성하기

그림5. 테이블 생성 gui

이제 생성된 테이블에 데이터를 입력해 볼 차례다.

데이터 입력하기

MySQL 워크벤치의 왼쪽 패널의 조금 전 만든 테이블을 선택하고 우클릭하여 Select Rows-Limit 1000을 클릭하면 아래와 같이 SELECT 문이 자동으로 생성된다. 그리고 Result Grid 창에는 비어 있는 테이블을 확인할 수 있는데, 이는 buy 테이블을 생성한 뒤 아무런 데이터를 넣지 않았기 때문이다. 이제 buy와 member 테이블 안에 아래 그림과 같이 값을 하나하나 넣어주고, 우측 하단의 apply 버튼을 눌러 데이터 입력을 마무리하면 된다. 이 때, buy 테이블에서 지정했던 FK때문에 오류가 발생할 수 있다. 이는 buy 테이블의 mem_id 값은 반드시 member 테이블의 mem_id로 존재해야 하기 때문인데, member 테이블에 해당 아이디 값이 없으면 데이터를 삽입할 수 없기 때문에 발생하는 오류이다. 따라서 member 테이블에 해당 데이터를 입력해주면 문제를 해결할 수 있다.

그림6. 생성된 buy 테이블

그림7. 테이블에 데이터 입력하기

그림8. 데이터 입력 완료

이제 테이블에 데이터 입력을 완료하였다. 다음에는 SQL문을 직접 입력하여 테이블을 생성하고, 데이터를 입력해보도록 하겠다.

2. SQL로 테이블 만들기

SQL을 사용하여 데이터베이스를 생성하고 테이블을 관리하는 법은 3장에서 배운 적이 있지만, 복습한다는 생각으로 한 번 더 살펴보자.

데이터베이스 생성하기

아래의 SQL문을 이용하여 간단하게 데이터베이스를 생성할 수 있다.

DROP DATABASE IF EXISTS naver_db; 			#naver_db 라는 데이터베이스가 존재하면 해당데이터베이스를 삭제한다.
CREATE DATABASE naver_db;

아래의 CREATE TABLE 구문으로 member 테이블과 buy 테이블을 생성한다. 이 때, 위에서 지정했던 제약조건과 데이터의 형식, 컬럼 명 등을 잘 확인하도록 하자.

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

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

위의 SQL문을 입력하고 실행하면 두 개의 테이블이 생성된다. 이제 아래에서는 각 테이블에 데이터를 입력할 것이다.

데이터 입력하기

INSERT문을 이용하여 데이터를 넣을 차례다. member 테이블과 buy 테이블에 아래처럼 각각의 제약조건을 만족하는 데이터를 입력하고 실행하면 된다. 아주 간단하다!

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