1-10) MySQL기초 / PHP에 DB연동

테이블 생성 및 삭제

테이블을 생성한다? 

 - 내가 저장할 데이터의 구조를 정의하는것

정형화된 데이터 : 사용자 정보 와 같은것

관계형 DB는 정형화된 데이터를 관리하기 용이하다.

 - 아이디

 - 패스워드

비 정형화된 데이터 : 규칙이 없기 때문에 정형화 시킬수 없는 데이터

#)_ 참고

데이터 마이닝 : 규칙이 없어보이는 데이터를 규칙을 찾아내는 것.

데이터의 형태

# MySQL에서 기본적으로 제공해주는 데이터 타입이다.

# 스트링타입에서

시간복잡도 : CHAR > VARCHAR

공간복잡도 : VARCHAR > CHAR

실제로 대용량의 데이터를 관리하는부분에 있어서는 타입을 정하는것이 신중해야 할 상당히 중요한부분이다.

# BLOB 과 TEXT 타입

 - 대용량의 데이터를 저장할 때 주로 사용.

사람이 게시판의 글을 저장해야하는데, 얼만큼 입력해야할지 모르는경우 TEXT를 주로 사용.

그 외, 기본적인 다른 데이터의 형태는 https://dev.mysql.com/doc/refman/5.5/en/data-types.html

MySQL 공식 홈페이지를 참조하자.

따라서 우리는

 - no  : 식별번호 따라서 int

 - 아이디    : varchar 가 유리

 - 패스워드 : hash를쓴다면 항상 고정길이가 나오기 때문에, 따라서 char 가 유리

를 컬럼으로 하는 테이블을 생성해보도록 하자.

테이블 생성

# 테이블 생성 쿼리 : CREATE [테이블 이름]

사용할 DB를 지정해준 뒤 테이블을 생성한다.

mysql> CREATE TABLE [테이블 이름]( [컬럼 이름][컬럼 속성]들..., [컬럼 이름][컬럼 속성]들..., ...)

mysql> CREATE TABLE user_info( id varchar(10), pw char(50) );

 - 띄어쓰기로 컬럼이름과 컬럼속성 구분 주의

 - 컬럼 속성 : 데이터의 형태

그다음 확인해보면 내가 생성한 user_info 테이블을 확인할 수 있다.

# 테이블의 속성 확인 : DESC [테이블 이름]

만든 테이블의 속성을 확인한다. DESC (describe)

Field : 컬럼정보

 - Null : 이 컬럼에 Null값을 허용할것인지에 대한 여부. 즉, 데이터가 없어도 허용할 것인지

만약 no 이면 반드시 데이터 값이 들어가야 입력이 된다.

 - Key : 관계를 표현하는 수단. 이 키를 가지고 테이블간의 관계를 표현한다

 - Default : 데이터를 입력할 때, 해당컬럼의 값이 비어있을 경우의 디폴트 값.

 - Extra : 그 외 기타

Null, Key, Default, Extra 항목에 대해 특정 값을 지정해주려면 차례대로 입력해주면 된다.

뒷 예제에서 확인해보도록 하자.

# 테이블 삭제 : DROP TABLE [테이블 이름]

mysql> DROP TABLE [테이블이름];

하면 바로 지워진다. 주의해야할것은 안에 데이터가 있더라도 삭제된다.

# mysql> CREATE TABLE user_info( no int not null auto_increment primary key, id varchar(10), pw char(50) );

 - primary key 는 중복될 수 없다. 따라서 auto_increment 랑 같이사용한다. (왜냐면 자동증가하면 중복을 차단할 수 있기에)

따라서 디폴트 값 지정은 해줄 수 없다. 중복이 생길 여지가 있기때문에.

 - 위의 명령을 보면 알겠지만 설정들은 콤마없이 쭉 띄어쓰기만으로 구분하며 입력해주는 반면, 

컬럼과 컬럼은 콤마를 붙혀 경계를 확실하게 해 준다.

 - 설정들 중에 띄어쓰기로 구분한다고 해서 실수하는일이 없도록 익숙해져야한다.

# CREATE 쿼리의 형식. 

#)_ 참고

컬럼이름, 테이블이름 대소문자 모두 구분한다. 

표준 MySQL 문법에서만 대소문자를 구분하지 않는다고 생각하면 된다.

DML : 데이터를 넣고 뺴고 수정하고 하는것

 - [입력] : INSERT

mysql> INSERT INTO 테이블 이름[(컬럼이름1, ..)] VALUE(값1, ...);

# 컬럼이름은 지정할수도 안할수도 있다. 컬럼을 지정해주게되면 값과 일대일 매칭이 된다.

mysql> INSERT INTO user_info VALUE('user1', md5('1234'));         //하지만 이 경우 오류가 발생한다.

컬럼은 3개 (no, id, pw) 인데 입력 값을 두개만 지정해줬기 때문.

따라서 이럴땐 따로 지정을 해줘야한다.

mysql> INSERT INTO user_info(id, pw) VALUE('user1', md5('1234')); // 이런식으로 일대일 대응 

해쉬된 값으로 넣을 땐, md5() 함수를 이용한다.

* no field 에는 auto_increment가 설정된 PK 값이기때문에 VALUE 항목에 값을 집어넣으면 곤란해진다.

곤란해진다는 의미는, 자동지정된 것을 강제로 할당시켜도 되지만 주로 그렇게 하지 않는다는것이다.

 - [조회/검색] (= 출력) : SELECT

가장 복잡하고 가장 사용빈도가 많은 쿼리이다.

# SELECT가 어떻게 보면 주된 Database의 사용 이유이기때문에 가장 형식이 길다.  (https://dev.mysql.com/doc/refman/5.5/en/select.html)

# mysql> SELECT 컬럼, ... FROM 테이블 이름;

mysql> SELECT no, id, pw FROM user_info;

와일드카드를 사용하여

mysql> SELECT * FROM user_info;

를 입력하여도 동일한 결과가 출력된다.

# SELECT 는 출력 도 가능하다. 

원하는 내용을 출력이 가능하고, 연산된 결과도 출력해준다.

id 만을 선택하여 출력할 수는 없다 왜냐면 user_info 테이블 안에 포함된 컬럼이기 때문이다.

따라서 from 을 써서 같이 써 줘야한다.

특정 사용자의 데이터만 조회하고자 한다면

# SELECT 쿼리에 WHERE 표현식 에 해당하는 조건을 설정하여 조회(출력)할 수 있다.

mysql> SELECT 컬럼, ... FROM 테이블 이름 WHERE 표현식 ...;

단, 주의해야할점은 WHERE 뒤에 같다 또는 다르다 를 쓸때 = 를 하나만 써준다. (WHERE 에서 사용할 때만)

# WHERE 조건을 잘못 입력했을때와, 정확히 입력했을 경우.

SELECT 컬럼 FROM 테이블 이름 WHERE 조건식 ;

이 구문은 DB를 사용하는 주된 목적이라 말 할수 있을정도로 사용빈도가 높다.

반드시 능숙하게 사용할 수 있어야 한다.

 - [수정] : UPDATE

UPDATE는 주의해야할 것이 WHERE 조건으로 특정 행을 갱신하겠다 라고 지정해주지 않으면, 전체 컬럼이 UPDATE 된다.

조건이 안들어가는경우에는 모든것이 참이라고 본다는 것이다.

# 위와같이 WHERE 조건을 지정해주지않으면 해당 컬럼의 전체 값들이 모두 UPDATE 된다.

# 이처럼 WHERE 조건을 써서 특정값만 변경할 수 있다.

mysql> UPDATE 테이블 이름 SET 할당 리스트;

mysql> UPDATE 테이블 이름 SET 할당 리스트 WHERE 조건;

 - [삭제] : DELETE

 행 하나를 삭제한다. WHERE가 없으면 모두 참이되므로 모두 지워진다.

특정 컬럼의 값만 삭제하려면 UPDATE를 해야한다.

DELETE는 행 자체를 지워버리는것이다.

따라서 DELETE와 FROM 사이에 따로 표현식이 들어가지않는다.

# WHERE 조건에 맞는 행을 전체 삭제

이제 PHP 에서 직접 제어해보도록 하자.

PHP에 DB연동

PHP에서는 mysql 라이브러리를 제공해준다. 우선 실습 환경을 구축하도록 하자.

#> yum install php

#> yum list installed php* 명령을통해 php 설치된 리스트를 보면

 php-mysql.i686 이 설치가 되어있어야한다.

# 기존에 설정해 둔 인증을 풀어주고 진행하자.

풀어주지 않을경우 진입시 인증을 요구한다.

(사용자 인증에 관련된 파트는 http://kevinthegrey.tistory.com/20 참고)

# sql.php 파일을 작성한다.

물론 경로는 아파치 html 루트디렉토리인 /var/www/html/ 이다.

# sql.php 파일이다. 

MySQL에 연결하기위한 php의 mysql라이브러리로 mysql_conncet( [server], [username], [password] ) 를 사용하였다.

이 함수는 MySQL 링크 식별자(Link identifier)를 리턴하는데, 실패할경우 false를 리턴한다.

따라서 접속에 실패할경우 접속실패를 알리고 종료하고자 die() 함수를 사용했다.

mysql_error() 함수는 가장 마지막으로 출력된 에러를 가져온다.

성공적으로 접속한 경우 'Connect Successfully' 라는 문자열을 출력한다.

연결이 성공적으로 됐다면 찾고자하는 DB를 선택하여야 한다.

이때 사용할 라이브러리가 mysql_select_db( [DATABASE_NAME], [LINK_IDENTIFIER] ) 함수다.

즉, mysql> use user; 의 과정을 의미한다.

# 이전에 일반변수를 super globals로 사용하기위해 켜놓은 설정때문에 Warning이 뜨는것이다.

무시하면 되지만 거슬린다면 http://kevinthegrey.tistory.com/18 참고하여 설정을 꺼주면 되겠다.

# 성공적으로 접속되었다. 

DB를 가져오는 작업을 아직 해둔것이 아니라서 따로 데이터가 출력되지는 않았다.

https://dev.mysql.com/doc/apis-php/en/apis-php-ref.mysql.html

PHP에서 제공하는 MySQL라이브러리 중 함수들 매뉴얼이다.

각기 내용들의 깊이가 깊어서 링크를 첨부한다. 참고하며 사용하면 될 것같다.

DB의 쿼리에 대한 이해가 없으면 SQL Injection 과 같은 가장 대표적인 공격기법을 전혀 이해할 수가 없다.

모든 프로그래밍적 지식을 머릿속에 암기하여 프로그램을 설계하는것은 불가능하다.

정확한 레퍼런스를 두고 적재적소에 사용할 줄 알면 된다.