[PHP] MySQL 데이터베이스 테이블 생성하기

106_PHP MySQL 데이터베이스 테이블 생성하기

[요약]

• 테이블을 생성하는 방법에 대해 알아보자.

---

테이블은 데이터베이스 내에서 정보를 담는 역할을 한다.

우리가 여러 웹페이지들을 이용하면서 보는 정보들의 대부분은 이 테이블에 저장되어 있던 자료들이 웹페이지로 출력된다.

 

테이블은 크게 필드와 레코드로 구분된다.

테이블에서 번호, 이름, 직업, 성별을 '필드'라고 한다.

그리고 아래 열의 [1, 손흥민, 축구선수, 남] 을 '레코드'라고 한다.

번호	이름	직업	성별
1	손흥민	축구선수	남
2	김연아	피겨선수	여
3	박찬호	야구선수	남
4	김연경	배구선수	여

데이터베이스에서는 최적화가 중요하다.

테이블에 들어갈 값을 넣을 필드를 만들 때는 해당 필드에 어디에 어느 정도 용량의 값까지 입력할 수 있는지, 어떤값을 입력해야 하는지를 명시한다.

 

그럼 데이터를 입력할 수 있는 크기는 어느 정도이고, 어떤 데이터형이 들어가는지 지정하는 방법에 대해 알아보자.

테이블을 만들 때는 필드를 명시하고 명시한 필드에 어떠한 데이터가 들어가는지와 해당 데이터의 크기가 어느 정도 될 것인지를 명시해야 한다. 여기서 데이터라는 것은 짧은 텍스트인지, 긴 텍스트인지, 숫자인지 등을 의미한다.

 

텍스트를 입력할 때는 입력할 텍스트의 길이를 지정한다.

다섯 글자만 입력한다면 5를 지정하면 된다.(MySQL 4.0 이전 버전은 byte를 단위로 사용했지만 5.0 버전 이후 부터는 글자수로 입력)

하지만 숫자는 간단하지만은 않다.

 

데이터의 길이를 지정할 때는 byte(바이트)라는 단위를 사용한다.

일상생활에서 USB 메모리가 몇기가인지, SSD 또는 하드디스크 용량, 아이폰의 용량이 얼마인지 물어볼 때 사용하는 단위가 byte이다. 1byte는 8개의 bit(비트)로 이루어져 있다.

 

컴퓨터는 2진수를 사용한다. bit 또한 컴퓨터에서 사용하는 단위이미로 2진수(0과 1)를 사용한다.

즉, 하나의 bit에 들어가는 값은 0 아니면 1이다.

1+1을 하면 우리들의 계산으로는 2이지만, 컴퓨터의 2진법 계산으로는 10이 된다.

1byte는 총 256가지의 수를 표현할 수 있다.

1byte(8bit)가 256가지나 수를 표현할 수 있는 것은 비트의 자릿수에 표현할 수 있는 수가 정해져 있기 때문이다.

우리가 사용하는 10진수로 변환하려면 8 개의 비트 중 어떤 위치의 값이 1이면 그 값은 64, 어떠한 비트 값이 1이면 그 값은 128과 같은 방식으로 표현된다. 예를 들어, 비트를 이용하여 십진수 15를 표현하는 방법은 다음과 같다.

2진수로 10진수 15를 표현하면 1111이다.

10진수 236을 2진수로 표현하는 방법은 다음과 같다.

236을 2진수로하면 11101100이다.

 

숫자형 데이터형에는 어떠한 것이 있는지, 수를 집어넣을 때의 데이터형에 대해 알아보자.

다음 표에서 저장 가능한 수는 음의 정수를 포함한 범위이다.

해당 필드에 무조건 양의 정수만 넣는다고 가정했을 때 양의 정수만 입력 가능하게 하는 방법이 있으며,

이 방법을 사용하면 음의 정수 저장 공간은 필요하지 않으므로 그 수만큼 양의 정수 저장 공간이 늘어난다.

---

[숫자형 데이터형]

데이터형	byte	저장 가능 수	양의 정수 전환시 저장 가능 수 (unsigned)
tinyint	1byte	-128 ~ 127	0 ~ 255
smalint	2byte	-32768 ~ 32767	0 ~ 65535
mediumint	3byte	-8388608 ~ 8388607	0 ~ 16777215
int 또는 integer	4byte	-2147493648 ~ 2147483647	0 ~ 4294967295
bigint	5byte	-9223372036854775858 ~ 9223372036854775807	0 ~ 18446744073709551615

실수를 사용하려면 float 또는 double을 사용해야 한다.

---

문자형 데이터형은 어떠한 것이 있는지 알아보자.

문자형 데이터는 테이블에 텍스트를 저장하기 위한 데이터형으로 주소, 이름, 이메일 등의 정보를 테이블에 저장한다.

 

[문자형 데이터형]

데이터형	저장 가능 글자 수
char	255
varchar	255
tinytext	255
text	65535
mediumtext	16777215
longtext	4294967295
enum	지정된 값 중 한 가지만 사용 가능
set	지정된 값 중 여러 가지 값을 사용 가능

문자열의 데이터형을 사용하려면 데이터형을 적고 괄호를 연 후 사용할 글자 수를 지정해 주어야 한다.

---

[필드의 데이터형을 지정하는 방법]

사용할 데이터형(사용할 글자 수)
예를 들어 char형으로 10글자까지 작성한다면
char(10)

char 데이터형을 이용하여 네 글자까지 저장 공간을 지정했을 때 세 글자를 입력하면 한 글자를 입력할 공간이 불필요하게 남게 된다.

하지만 varchar(4)를 적용한 후 세 글자를 입력하면 자동으로 남는 공간을 없애준다.

즉, 불필요한 공간이 없어지므로 효율적이다.

그 대신 varchar은 char에 비해 속도 처리가 느리다는 단점이 있다.

tinytext, text, mediumtext, longtext는 지정된 크기에 한하여 사용할 수 있는 데이터형이다.

데이터형 char가 너무 작아서 사용하지 못한다면 tinytext를 사용하고, tinytext가 너무 작아서 사용하지 못한다면 text를 사용하면 된다.

 

앞에서 enum은 지정된 값 중 하나만 사용할 수 있다고 했다.

예를 들어, 성별의 정보를 받는다고 가정하면 남성 또는 여성으로만 값이 정해져 있기 때문에 이러한 값을 받는 필드를 만들 떄 유용하다. char를 사용하여 남성이라는 정보를 받거나 여성이라는 정보를 받으면 된다고 생각할 수 있지만, enum은 받는 값이 정해져 있기 때문에 해당 값을 좀 더 간단하게 저장한다. 그러므로 효육성 면에서 좋다.

또한 다른 값이 들어가는 사태를 원천적으로 막을 수 있다는 이점이 있다.

---

[enum 사용 방법]

남성을 m, 여성을 w라고 지정한다면
enum('m', 'w');

---

set은 지정된 값 중 여러 값을 저장할 수 있다.

예를 들어, 어떤 웹 서비스를 이용한 이후 이메일, 광고 문자, 푸시 알림 광고 등이 온다고 가정해보자.

이 중 광고 문자를 받고 싶지 않거나, 광고 문자나 푸시 알림이 받고 싶지 않거나 또는 3개 모두 받고 싶지 않다고 할 때 이정보들을 저장할 필드의 데이터형으로 사용하기 적합하다.

 

[set 사용 방법]

이메일을 e, 광고 문자를 s, 푸시 알림을 p라고 가정하면
set('e', 's', 'p');
enum은 e 또는 s 또는 p 중 한 가지만 저장 가능
set는 e 또는 s 또는 p 다수 저장 가능

---

날짜형 데이터형에 대해 알아보자.

날짜형 데이터는 어떤 글이 언제 작성 완료되었는지, 어떤 회원이 언제 가입했는지, 누가 언제 나의 웹사이트에 로그인 했는지 등과 같은 정보를 저장할 때 사용하는 데이터형이다.

데이터형	저장 범위	표시 형식	크기
date	1000-01-01 ~ 9999-12-31	YYYY-MM-DD	3byte
datetime	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	YYYY-MM-DD HH:MM:SS	8byte
timestamp	1970-01-01 00:00:00 ~ 2038-01-10 03:14:07	YYYYMMDDHHMMSS	4byte
time	-838:59:59 ~ 838:59:59	HH:MM:SS	3byte
year	1901 ~ 2155	YYYY	1byte

date는 년도, 월, 일만 저장하는 데이터형이며, datetime은 년도, 월, 일, 시, 분, 초까지 저장하는 데이터형이다.

timestamp는 리눅스에서 사용하는 시간이며, 1970년 1월 1일 0시 0분 0초부터 시간을 세기 시작했다.

그러므로 저장 범위가 1070년 1월 1일 0시 0분 0초부터 시작된다.

time은 시, 분, 초만을 저장하는 데이터형이며, year는 연도만 저장하는 데이터형이다.

---

테이블 생성시 필요한 여러 데이터형에 대해 알아보았고, 이제 테이블을 한번 생성해보자.

테스트용으로 사용할 회원정보를 담고 있는 테이블을 만들어보자.

 

[테이블 생성 명령문]

CREATE TABLE 테이블명(
필드명 데이터형 옵션 필드 설명
)인코딩 설정 테이블 설명

어떠한 정보를 담을 것인지에 대한 정보는 다음과 같다.

회원의 일련번호,
회원의 아이디,
회원의 이름,
회읜의 비밀번호,
회원의 휴대전화번호,
회원의 이메일 주소,
회원의 생일,
회원의 성별,
회원의 가입 시기

회원의 일련번호는 회원을 구분하는 기준이 된다.

이름에 기준을 두면 동명이인이 있어서 사용할 수 없는 경우가 발생하며, 휴대전화번호는 번호가 바뀔 수 있어서 사용하기에 적합하지 않다. 따라서 모든 서비스에는 회원의 고유한 번호가 부여된다.

이 고유한 번호는 절대 다른 회원과 겹치는 일이 없다.

이러한 고유번호를 저장하는 필드를 생성할 때는 auto_increament를 사용한다.

auto_increament는 레코드가 발생할 때마다 1씩 더한 값을 저장하여 고유번호가 겹치지 않도록 해준다.

테이블 생성 시 사용하는 필드를 지정하는 방법에 대해 알아보자.

 

[필드 저장하기]

필드명 데이터형 기본값 옵션 코멘트

예를 들어, 고객의 고유번호 필드를 만든다면 다음과 같다.

필드의 이름 myMemberID
데이터형은 숫자 양의 정수를 입력하므로 옵션으로 unsigned를 지정
이 필드는 고유한 값이므로 auto_increment를 지정
이 필드의 설명을 '고객번호'로 지정
고유한 데이터를 담는 필드의 이름은 보통 테이블명에 ID를 붙여서 사용
그러므로 고유한 값을 담는 필드의 이름은 myMemberID

필드 생성문은
memberID int unsigned auto_increment coment '고객번호'

이와 같은 방법으로 고객 정보 테이블을 생성해보자.

테이블의 이름은 myMember이다.

CREATE TABLE myMember(
myMemberID int unsigned auto_increment comment '고객의 고유번호',
userId varchar(15) not null comment '고객의 아이디',
name varchar(10) not null comment '고객명',
password varchar(30) not null comment '고객의 비밀번호',
phone varchar(13) not null comment '고객의 휴대전화번호',
email varchar(30) not null comment '고객의 이메일 주소',
birthDay char(10) not null comment '고객의 생일',
gender enum('m', 'w', 'x') default 'x' comment '고객 성멸 m은 남성 w는 여성 x는 선택하지 않음',
regDate datetime not null comment '회원가입 일시',
primary key(myMenberID))
charset=utf8 comment='고객 정보 테이블';

고객의 고유번호를 입력하는 필드이며 숫자를 사용하므로 데이터형에 int를 사용하고, 양의 정수를 사용하므로 unsigned를 사용하여 사용할 수 있는 양의 정수 용량을 2배로 늘렸다.

 

고객의 이름을 받는 필드이다.

한국인의 경우 이름이 세 글자인 경우가 많지만, 성을 포함하여 두 글자인 사람도 있으며, 네 글자, 다섯 글자인 경우도 있기 때무에 열 글자까지 허용하고 varchar 데이터형을 사용했다. 옵션으로 쓰인not null은 이 필드에 빈 값이 들어오더라도 null 이라는 값을 쓰지 않겠다는 뜻이다. null은 없는 값을 뜻한다. not null이 지정된 필드에 값을 넣지 않으면 아무것도 입력되지 않지만 필수적으로 값이 들어가야 하는 필드에 적용한다.

name, password, phone, emial의 경우 길이가 모두 다르기 때문에 데이터형으로 varchar를 사용했지만 birthDay의 경우 YYYY-mm-dd와 같이 10자로 정해져 있으므로 char를 사용했다.

 

성별을 받는 필드는 enum을 사용했다.

성별은 여성 아니면 남성이기 때문에 2개로만 받을 수 있다. 무억을 받을 것인지 정해 있는 부분이라면 enum을 사용한다. 

컴퓨터의 입장에서는 남성, 여성이라는 값을 숫자로 인지해 지정하므로 속도가 빨라진다.

m과 w외에 x도 있는데, 이것은 성별을 선택하지 않았을 때 사용할 값이다. Default는 기본값을 뜻한다.

데이터 입력시 아무 값도 없을 경우 default 값이 들어간다. 만약 default 'x'가 지정되어 아무 값도 받지 않은 경우 x가 값으로 입력된다.

 

학교에서는 학생들에게 번호를 부여한다.

대학에 가면 자신만의 학번을 부여받게 되고, 군대에 가면 자신만의 군번을 부여받게 된다.

같은 반에 손흥민이라는 사람이 3명이 있다고 가정해 보자. 선생님이 '손흥민 일어나'라고 말할 경우 3명이 모두 일어나지만, 번호를 부를 경우 그 번호를 부여 받은 1명만 일어나게 된다. 이러한 번호가 데이터베이스에서는 primary key이다.

primary key는 테이블의 레코드를 식별하는 대표 번호이다.

따라서 myMemberID라는 필드를 primary key로 지정하면 myMemberID 필드는 고유한 값이 되어 겹치는 값이 없게 된다. 이 primary key는 한 테이블에 하나만 지정해야 하며, 2개 이상 지정해서는 안된다. primary key를 지정하면 고객 정보를 고객번호로 찾을 때 빠른 속도로 찾을 수 있다. primary key를 지정하지 않으면 지정할 때보다 느린 속도로 찾게 되며, 서비스 속도도 느려진다.

 

charset=utf8는 티이블의 문자셋을 유니코드로 지정한다는 것이며, 유니코드를 쓰지 않으면 한글을 입력할 경우 깨져서 입력되는 현상이 초래되므로 반드시 지정해야 한다. comment='고객 정보 테이블'은 어떤 테이블인지에 대한 설명을 적는다.

 

그럼 phpExample 데이터베이스에 myMember 테이블을 생성하자.

[예제: 109_createMyMember.php ]

<?php
    include $_SERVER['DOCUMENT_ROOT'] . '/php/108-2_connentDB.php';

    $sql = "CREATE TABLE myMember(";
    $sql .= "myMemberID int unsigned auto_increment comment '고객의 고유번호',";
    $sql .= "userId varchar(15) not null comment '고객의 아이디',";
    $sql .= "name varchar(10) not null comment '고객명',";
    $sql .= "password varchar(30) not null comment '고객의 비밀번호',";
    $sql .= "phone varchar(13) not null comment '고객의 휴대전화번호',";
    $sql .= "email varchar(30) not null comment '고객의 이메일 주소',";
    $sql .= "birthDay char(10) not null comment '고객의 생일',";
    $sql .= "gender enum('m', 'w', 'x') default 'x' comment '고객 성멸 m은 남성 w는 여성 x는 선택하지 않음',";
    $sql .= "regDate datetime not null comment '회원가입 일시',";
    $sql .= "primary key(myMenberID))";
    $sql .= "charset=utf8 comment='고객 정보 테이블';";

    $result = $dbConnect->query($sql);

    if ($result) {
      echo "테이블 생성 완료";
    } else {
      echo "테이블 생성 실패";
    }
?>

실행결과

결과를 보면 테이블 생성 여부만 확인할 수 있고 실제 데이터베이스 안을 볼 수 없는 상태이다.

 

다음은 실제 데이터베이스를 보기 위해서 터미널에서 mysql에 접속할 수 있고, 더 편리하게 이용하는 방법으로는 웹에서 사용하는 phpMyAdmin 프로그램을 이용할 수 있다.

 

우선 먼저 터미널로 접속하여 데이터베이스를 확인해도록 하자.

터미널에서 접속하기 위해 다음의 경로로 이동한다.

C:\xampp\mysql\bin

경로를 이동하는 명령문인 cd를 사용하면 다음과 같다.

cd \xampp\mysql\bin

 

터미널에서 MySQL 접속 명령문은 다음과 같다.

mysql -uroot -p1234

./는 현재 폴더를 의미한다.

-u의 u는 user를 의미하며 -p의 p는 password를 의미한다.

 

앞의 명령문을 입력하면 mysql에 접속하게 된다.

 

데이터베이스의 작업을 하려면 사용하려는 데이터베이스를 선택해야 한다.

데이터베이스의 목록을 보는 명령문은 다음과 같다.

show databases;

본인의 데이터베이스 목록을 보면 앞에서 생성한 phpExample 외에도 이전에 만들어놓았던 다른 데이터베이스의 목록도 확인할 수 있다. 위의 데이터베이스 목록중에서 phpExample 데이터베이스를 사용하려면 use 명령어를 사용한다.

 

[use 명령문 사용 방법]

use 데이터베이스명

phpExample 데이터베이스를 사용하기 위해서는 다음과 같이 사용한다.

use phpExample;

 

터미널에서 데이터베이스 명령문은 끝에 ;(세미콜론)을 사용한다.

데이터베이스를 종료하는 명령어는 터미널과 마찬가지로 exit;이다.

---

터미널에서 MySQL에 접속하는 방법은 여기까지 하고, 다음은 phpMyAdmin 프로그램을 통해서 접속하는 방법에 대해 알아보자.

 

[phpMyAdmin 프로그램 접속 URL]

http://localhost/phpmyadmin/

주의할 점은 터미널에서 MySQL에 접속한 상태이면 접속이 되지 않는 경우가 있으므로 접속이 안될 시 터미널에서의 MySQL 접속을 종료 후 사용해야한다.

위 그림의 왼쪽에 있는 트리뷰에 데이터베이스 목록이 있고, 데이터베이스를 선택하면 테이블 목록이 펼쳐진다.

 

위 그림을 보면 [코드 109]에서 생성한 테이블인 myMember 테이블을 확인할 수 있다.

지금부터 사용하는 쿼리문은 오른쪽의 탭에 있는 SQL에서 실행할 수 있다.

 

여기에서는 PHP와 MySQL의 연동을 예로 만들기 위해 php 파일로 저장하여 실행하지만 phpMyAdmin이나 터미널로 MySQL에 접속하여 쿼리문을 실행해도 무방하다.

---

PHP 예제를 사용하지 않고 myMember 테이블을 만든다면 다음과 같이 사용할 수 있다.

[터미널에서의 사용]

 

[phpMyAdmin에서의 사용]

쿼리문을 작성하고 우측하단에 보이는 [실행] 버튼을 누르면 쿼리문이 실행된다.

 

 

 

---

 

 

 

초보자를 위한 PHP 200제
김태영 지음 | 정보문화사