PHP - 데이터베이스

데이터베이스는 회원제 시스템에서 없어서는 안될 중요한 것이다. 데이터의 저장 장소라고 생각하면 된다. 지금까지 코드 내부에 이름이나 주소같은 데이터를 포함해서 작성한 경우가 많았다. 하지만 프로그램이 실행 중일 때는 데이터를 처리할 수 있지만, 프로그램이 종료되면 데이터는 사라지게 된다.

PHP 코드는 Java나 JavaScript처럼 메모리 라는 장소에 데이터를 읽어 들여 프로그램을 실행한다. 그러나 프로그램 실행 시에는 데이터가 메모리 내부에 존재하지만, 프로그램이 종료되면 메모리에서 데이터가 모두 사라져 버린다. 프로그램이 종료되기 전에 데이터를 다른 장소에 저장해 둘 수 있다면 프로그램이 종료되어도 데이터가 남게 할 수 있다.

데이터베이스(DB, Database)

RDBMS(Relational Database Management System)은 데이터를 표 형식으로 저장하고 각 표의 관계도 저장할 수 있는 것이다. 데이터베이스에 데이터를 저장하면 데이터의 종류가 늘어나도 정해진 방식대로 데이터 저장, 검색, 수정, 삭제 처리를 할 수 있다.

스키마

스키마랑 데이터베이스의 구조를 말하며 RDBMS에서는 테이블을 포함한 데이터의 형이나 길이, 다른 테이블과의 관계를 말한다.

CRUD

데이터베이스에 필요한 네 가지 기능을 CRUD(크러드)라고 부른다. 이것은 Create(입력), Read(조회), Update(수정) 그리고 Delete(삭제)의 머릿글자를 조합한 것이다.

데이터베이스의 종류

데이터베이스명	설명
MySQL(MariaDB)	세계에서 가장 많이 이용하는 데이터베이스, 사용하기 쉽고 스토로지 엔진 변경 가능
PostgreSQL	MySQL과 같이 인기 많은 데이터베이스, 오픈 소스의 객체형 데이터베이스 관리 시스템(ORDBMS)
SQLite	서버가 아닌 라이브러리로서 동작하고, 가볍고 작으며 PHP에 포함되어 있음
Oracle	최초로 상용 RDBMS를 발표한 Oracle의 ORDBMS이고, 가격이 비싸고 다루기 어려움
Microsoft SQL Server	Microsoft의 RDBMS
Microsoft Access	Microsoft의 Office에 포함된 Windows용 RDBMS
DB2	최초로 관계형 데이터베이스를 만든 IBM의 상용 데이터베이스

MySQL

MySQL은 현재 Oracle의 소유로 오픈소스로 개발되고 있는 데이터베이스이다. 가장 많이 사용되는 데이터베이스인 MySQL은 데이터베이스를 저장하는 형식을 선택할 수 있는 특징이 있고 MyISAM 형식은 속도가 필요한 처리에 적합하다. MariaDB는 MySQL의 소스를 기반으로 하는 DBMS이다. 사용 방법과 구조는 MySQL과 동일하다.

MySQL은 XAMPP를 사용해서 설치할 수 있고(이 경우에는 MariaDB), MySQL Community Server의 홈페이지에서 다운을 받을 수 있다.

실행하고 서버에 접속하려면 클라이언트용 소프트웨어가 필요하다. 여기서는 명령행 클라이언트라는 mysql 명령행 도구를 이용한다. 명령 프롬프트나 터미널을 실행하고 Windows에 경우는 C:\xampp\mysql\bin\mysql.exe를 입력해 접속할 수 있다. MySQL 서버와 접속을 끊으려면 mysql> exit(또는 quit)을 입력한다.

<!-- Windows에 경우 -->
C:\xampp\mysql\bin\mysql.exe
 
<!-- Mac에 경우 -->
/Application/XAMPP/xamppfiles/bin/mysql
 
<!-- Linux에 경우 -->
/opt/lamp/bin/mysql
 
<!-- MySQL 서버와 접속 끊기 -->
mysql> exit(또는 quit)

MySQL 서버의 모든 조작이 가능한 Root라는 권한을 얻으려면 관리자로 접속을 해야한다. 처음 실행을 하면 Root에 패스워드가 설정되어 있지 않다. 이전처럼 명령 프롬프트나 터미널에서 mysql -u 사용자명 -p자세하게 C:\xampp\bin>mysql -u root(사용자명을 지정) -p를 입력해 MySQL 서버에 접속을 하게되면 Enter password: 가 나오는데 처음이면 비밀번호가 설정되어 있지 않기 때문에 엔터로 넘어간다.

mysql -u 사용자명 -p
 
C:\xampp\bin>mysql -u root -p
Enter password:

패스워드를 설정하려면 SET PASSWORD 구문을 사용한다. SET PASSWORD FOR 사용자명@호스트명 = PASSWORD('패스워드');와 같이 사용한다. 여기서 호스트명은 네트워크에 접속된 기기에 알기 쉽게 이름을 붙인것이다. 현재 조작하고 있는 PC 또는 서버를 localhost라고 한다.

SET PASSWORD FOR 사용자명@호스트명 = PASSWORD('패스워드');

mysql 명령행 도구를 실행하고 root로 접속하고 아래와 같은 코드를 입력한다.

MariaDB [(none)]> SET PASSWORD FOR root@localhost = PASSWORD('password');
Query OK, 0 rows affected (0.00 sec)
 
MariaDB [(none)]> quit
Bye

사용자 지정은 root@localhost로 한다. 이때 password에는 임의의 패스워드를 입력한다. Quary OK가 출력이 되면 root에 패스워드가 설정된 것이다.

C:\xampp\mysql\bin>mysql -u root -p
Enter password: ********
 
Welcome to the MariaDB monitor. Commands end with ; or \g.

root로 mysql 명령행 도구를 실행후 앞서 설정한 패스워드를 Enter password:에 입력하면 MySQL 서버에 패스워드로 접속할 수 있다.

root에 패스워드를 설정을 해도 사용자를 지정하지 않고 mysql 명령행 도구로 실행하면 별다른 제재 없이 접속이되는 문제가 있다. 이것은 사용자명이 없는 '익명'의 사용자가 설정되어 있기 때문이다. 

use 데이터베이스명;
 
DELETE FROM user WHERE password = '';

따라서 위의 코드와 같이 use구문으로 접근한 데이터베이스를 선택후 DELETE문으로 user 테이블 안에 password가 비어 있는 테이블을 모두 삭제하는 명령을 실행한다.

MariaDB [(none)]> use mysql;
Database changed MariaDB [mysql]> DELETE FROM user WHERE password = '';
 
Query OK, 4 rows affected (0.00 sec)

use로 데이터베이스를 선택하지 않고 SQL 문을 실행하면 ERROR 1046(3D000): No database selected라는 오류가 발생한다. 실수로 root를 삭제하게 되면 XAMPP를 재설치 해야하니 패스워드를 설정한 뒤에 하도록 한다.

데이터 베이스를 작성하려면 CREATE DATABASE문을 사용한다.

CREATE DATABASE 데이터베이스명;
 
CREATE DATABASE 데이터베이스명;
CHARACTER SET 문자 코드명 COLLATE 콜레이션명;

한글로도 작성할 수 있지만 되도록 영문, 숫자 또는 기호로 작성하는게 좋다. 행의 맨 끝에는 ;를 입력해야 한다.

MariaDB [mysql]> CREATE DATABASE sampledb CHARACTER SET utf8 COLLATE utf8_general_ci;
 
Query OK, 1 rows affected (0.00 sec)

SHOW DATABASES는 데이터베이스가 제대로 작성 되었는지 확인하게 해준다.

SHOW DATABASES;
 
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
 
| Database           |
 
+--------------------+
 
| information_schema |
 
| mysql              |
 
| performance_schema |
 
| phpmyadmin         |
 
| sampledb           |
 
| test               |
 
+--------------------+
 
6 rows in set (0.00 sec)

DROP DATABASE는 불필요한 데이터베이스를 삭제할 수 있다. 데이터를 포함하고 있는 데이터베이스를 삭제하면 데이터도 함께 삭제가 되니 삭제하기전에 확인을 해야한다. 행의 끝에는 ;를 입력해야 한다.

DROP DATABASE 데이터베이스명;
 
MariaDB [(none)]> DROP DATABASE sampledb;
Query OK, 0 rows affected (0.02 sec)

use문은 root로 mysql 명령행 도구에 접속한 뒤 데이터베이스에 접속할 때 사용한다. use문을 사용하면 특정 데이터베이스를 이용한다는 것을 MySQL 서버에 알린다. use 데이터베이스명;을 입력하고 엔터를 누르면 된다.

사용자 작성과 사용자 삭제는 각각 CREATE USER문과 DROP USER문을 사용한다.

<!-- 사용자 작성 -->
CREATE USER '사용자명'@'호스트명'
IDENTIFIED BY '패스워드';
 
MariaDB [(none)]> CREATE user
'smaple'@'localhost' IDENTIFIED BY 'password';
 
Query OK, 0 rows affected (0.00 sec)
 
<!-- 사용자 삭제 -->
MariaDB [(none)]> DROP user 'sample'@'localhost';
 
Query OK, 0 rows affected (0.00 sec)

추가한 사용자로 접속하는 방법은 아래와 같다.

MariaDB [(none)]> use sampledb;
 
ERROR 1044 (42000): Access denied for user 'smaple'@'localhost' to database 'sampledb'

에러가 출력되는 이유는 추가된 사용자가 sampledb에 대한 권한이 없기 때문이다. 권한을 부여하려면 GRANT문을 사용한다.

GRANT 권한 ON 데이터베이스명.* TO '사용자명'@'호스트명'
 
<!-- 권한을 부여 -->
MariaDB [(none)]> GRANT ALL PRIVILEGES ON sampledb.* TO 'sample'@'localhost';
 
Query OK, 0 rows affected (0.00 sec)
 
<!-- 권한을 리로드 -->
MariaDB [(none)]> flush privileges;
 
Query OK, 0 rows affected (0.00 sec)

ALL PRIVILEGES는 모든 권한을 의미한다. 권한을 변경하려면 FLUSH 구문을 사용해 내부에 캐시되어 있는 권한으로 클리어하고 새로운 권한을 다시 읽어 들인다.

명칭	형태	설명
root	mysql -u root -p	관리자로 서버 접속
SET PASSWORD	SET PASSWORD FOR 사용자명@호스트명 = PASSWORD('패스워드')	패스워드 설정
DELETE FROM	DELETE FROM user WHERE password = '';	익명 사용자 삭제
CREATE DATABASE	CREATE DATABASE 데이터베이스명;	데이터베이스 생성
문자 코드, 콜레이션	CHARACTER SET 문자 코드명 COLLATE 콜레이션명;	문자 코드, 콜레이션 설정
SHOW DATABASES	SHOW DATABASES;	데이터베이스 확인
DROP DATABASE	DROP DATABASE 데이터베이스명;	데이터베이스 삭제
use	use 데이터베이스명;	특성 데이터베이스를 사용
CREATE USER	CREATE USER '사용자명'@'호스트명' IDENTIFIED BY '패스워드';	사용자 생성
DROP USER	DROP USER '사용자명'@'호스트명';	사용자 삭제
GRANT	GRANT 권한 ON 데이터베이스명.* TO '사용자명'@'호스트명'	권한 설정
ALL PRIVILEGES		모든 권한
FLUSH	flush privilege;	권한을 리로드