PHP MySQL 프로그래밍 (2) 데이터베이스 생성, 관리, 삭제

1. 데이터베이스 생성

MySQL 계정에서 데이터를 관리할 때 가장 기본적인 작업인 데이터베이스 생성 및 삭제 방법을 알아보자. phpMyAdmin을 이용하여 새로운 데이터베이스를 생성하기 위해 메인 화면 상단의 데이터베이스 메뉴를 클릭한다.

새 테이터베이스 만들기에서 데이터베이스 이름과 데이터 정렬방식을 다음과 같이 설정한 뒤 만들기를 클릭한다.

만들기 버튼을 클릭하면 새로운 데이터베이스 sample이 다음과 같이 생성된다.

2. 데이터베이스 테이블 관리

2.1) 데이터베이스 테이블 설계

데이터베이스는 테이블들의 집합으로 구성되어 있다. 테이블은 필드명 데이터형으로 구성되어 있고 몇가지 추가 옵션들이 존재한다. 일단 회원정보 관리를 위한 user 테이블을 다음과 같이 설계해보자.

필드명	데이터형	null	추가 사항	설명
num	int	not null	auto_increment, primary key	일련번호
id	char(20)	not null		아이디
pass	char(20)	not null		비밀번호
name	char(20)	not null		이름

필드명은 데이터베이스 테이블의 항목의 이름이고 데이터 형은 각 항목의 종류를 나타낸다. 데이터베이스의 각 항목값은 빈값(null값)이 존재할지 여부를 정할 수 있는데 not null로 지정하면 해당 필드는 반드시 존재하여야 한다는 뜻이다. 이중 num 필드의 경우 추가사항으로 auto_increment와 primary key가 있는데 primary key는 고유키라 불리며 테이블의 아이디 값으로 사용되는 중복이 존재하지 않는 값이라는 뜻이다. 예를들어 num이 1인 사용자가 존재하면 num이 1인 사용자 추가를 요청하면 primary key 에러가 나면서 요청이 거부된다. 이러한 아이디값으로 사용되는 primary key를 매 데이터 추가시마다 수동으로 변경해주기 어렵기 때문에 매 데이터 추가시마다 아이디 값을 1씩 자동 증가시키기 위해서는 auto_increment 설정을 해 주어야 한다. auto increment설정을 한 필드는 데이터 추가시 값을 수동으로 입력하면 에러가 난다.

 

2.2) 데이터베이스 테이블 생성

위와 같이 테이블을 설계했으면 테이블을 실제로 생성해 보자. phpMyAdmin에서는 두가지 방식으로 테이블을 생성할 수 있다. 하나는 admin에서 제공하는 UI를 이용한 방식이고 다른 하나는 명령문을 사용하는 방법이다. 

2.2.1) UI를 사용한 방법

제공하는 UI를 사용해서 테이블을 생성하려면 이름에 새로 생성할 테이블명과 컬럼수를 입력하고 실행 버튼을 선택한다.

새로운 테이블 생성 실행 버튼을 클릭하면 아래와 같이 필드를 생성할 수 있는 화면이 나온다. 여기에 내가 생성하고자 하는 필드의 이름을 입력하고 종류와 값을 위에 설계했던대로 입력한다. 위 필드들은 모두 not null(빈값 허용 불가)이기때문에 Null 항목에는 체크를 하지 않고 num 필드는 primary key에 auto_increment이기 때문에 AI 항목에 체크를 해준다. 마지막으로 저장 버튼을 선택하면 새로운 테이블이 생성된다.

새롭게 생성된 테이블의 상세 내용은 좌측 패널 sample 데이터베이스 밑에서 선택해 이동할 수 있으며 테이블 구조와 그 상세 내용인 primary key의 정보 등이 표시된다.

2.2.2) 명령어(SQL Query문)를 사용하는 방법

이번에는 테이블을 명령어로 생성해보자. 이미 user 테이블이 존재하기 때문에 user2로 이름을 바꿔서 생성한다. 테이블을 생성하기 위해서는 다음과 같은 형태의 명령어(이하 SQL문)를 사용해야 한다. 

create table 테이블명 (
필드명1 데이터형,
필드명2 데이터형,
...
primary key(필드명)
);

이번에 생성할 테이블의 SQL문은 다음과 같다.

create table user2 (
num int not null auto_increment,
id char(20) not null,
pass char(20) not null,
name char(20) not null,
primary key(num)
);

phpMyAdmin UI 콘솔과는 반대로 not null일 경우에만 데이터형 뒤에 not null이라고 표시해 준다.

쿼리를 실행시키기 위해 phpMyAdmin에 sample 데이터베이스를 클릭하면 데이터 베이스 상세 화면이 나타난다. 여기서 하단에 콘솔이라고 적힌 아이콘을 선택하면 아까 UI로 생성한 테이블에 쿼리 문이 보인다. 여기 새로 추가할 테이블을 위한 쿼리문을 입력하고 Ctrl + Enter를 눌러 실행한다.

그러면 아래 그림과 같이 새로운 테이블이 생성되었음을 알수 있다.

 

2.3) 테이블 수정

테이블도 UI SQL문 두가지 방법으로 수정이 가능하다. 하나씩 순서대로 배워보자.

2.3.1) UI를 이용한 수정

user 테이블에 age라는 int 형 nullable 데이터를 추가해보자. 좌측 패널에 sample db에 있는 user테이블 혹은 sample 상세 페이지에 있는 user 테이블을 클릭해서 user 테이블 상세 페이지로 가고 탭 상단에 구조 메뉴를 클릭한다. 그러면 아래와 같이 테이블 구조 상세 화면이 나타나고 여기서 각 항목을 변경, 삭제, 추가 할 수 있다. 변경과 삭제는 각각 우측에 실행 항목에 변경이나 삭제 버튼을 클릭하면 되고 우리는 항목 추가를 원하기 때문에 하단에 컬럼 추가 실행 버튼을 클릭한다.

그러면 다음과 같이 항목을 추가할 수 있는 창이 나타나는데 이름과 종류를 설정하고 Null 항목에 체크를 한 뒤 저장을 선택한다.

그리고 다시한번 구조 탭을 클릭해서 결과를 보면 age라는 이름을 가진 새로운 항목이 추가된 것을 확인 할 수 있다.

2.3.2) SQL문을 이용한 항목 추가

테이블 수정을 위한 SQL문은 다음과 같은 구조를 가진다. 여기서 대괄호를 친 부분은 적어도 되고 적지 않아도 딱히 상관없는 옵셔널한 부분이며 특정 컬럼 뒤에 새로운 필드를 넣기 위해서는 대괄호 부분에 필드의 이름을 적어주어야 한다. 여기서는 맨 뒤에 age 컬럼을 넣을 것이기 때문에 옵션 필드는 적지는 않겠다.

alter table 테이블명 add 새로운_필드명 필드_데이터형 [first 또는 after 필드명]

위 명령을 테이블에 맞게 바꾸면 다음과 같다.

alter table user2 add age int;

2.2.2 항목 처럼 콘솔창을 열어서 명령어를 입력하고 Ctrl + Enter를 친다.

명령어가 실행 된 뒤에 구조 탭에 들어가보면 age 항목이 잘 추가된 것을 확인할 수 있다.

항목을 수정하기 위해서는 다음 형식의 명령어를 입력해 주면된다.

alter table 테이블명 change 수정할_필드명 새로운_필드명 필드_데이터형;

예를들어 age라는 항목을 age2라는 항목으로 변경하기 위해서는 다음과 같은 SQL문을 사용하면 된다.

alter table user2 change age age2 int;

항목을 삭제하기 위해서는 다음 형태의 SQL문을 사용한다.

alter table 테이블명 drop 삭제할_필드명1, 삭제할_필드명2;

2.4) 테이블 삭제

우리는 user 테이블만을 사용할 것이기 때문에 user2 테이블을 삭제하기로 한다. 테이블삭제도 두가지 방식 모두 설명하겠다.

2.4.1) UI를 이용한 테이블 삭제

UI를 이용한 테이블 삭제는 간단하다. sample 데이터 베이스 상세 화면에서 삭제할 테이블인 user2에 삭제 버튼을 클릭하고 나타나는 경고창에 확인을 선택하면 된다.

2.4.2) SQL문을 이용한 테이블 삭제

테이블 삭제를 위한 SQL문은 다음구조를 가진다.

drop table 테이블명;

우리는 user2 테이블을 삭제 할 것이기 때문에 콘솔창에 다음 명령어를 입력한다.

drop table user2;

명령어를 실행하면 다음과 같이 테이블이 삭제된 것을 확인할 수 있다.