MySQL(MariaDB)문법 요약 및 PHP연동

MySQL에서 필요한 명령어들과 간략한 문법 설명이다.

자세한 내용은 SQL공부를 따로 할 때 다뤄보도록 하겠다.

참고로, 명령어 칠 때 세미콜론을 써줘야 한다.

 

---

데이터베이스 생성하기

create database DB명;

 

 

사용자 계정 생성하기

CREATE USER '사용자이름'@'접속범위' IDENTIFIED BY '비밀번호';

 

접속 범위는 localhost 또는 %중에 선택한다. (아니면 특정 IP를 적어줘도 된다.)

localhost는 서버 내부에서만 접속 가능하다는 뜻이다. 외부에서 DB에 접근이 불가능하다. (웹 서버와 DB가 같은 서버에 있을때 사용)

%는 외부에서도 이 계정으로 접속이 가능하다는 뜻이다. (특정 IP를 적으면 그 IP만 접속 가능하다.)

 

데이터베이스 권한 부여

GRANT 권한종류 ON 데이터베이스명. 테이블명 TO '사용자이름'@'접속범위';

 

권한 종류에는 다음 문장들이 들어갈 수 있다. 'ALL PRIVILEGES'(모든 권한), 'SELECT'(조회), 'INSERT'(삽입), 'UPDATE'(갱신), 'DELETE'(삭제)

테이블명에 *을 입력하면, 데이터베이스 내의 모든 테이블에 권한을 갖는다.

 

계정 권한 확인

SHOW GRANTS FOR '아이디'@'범위';

범위에는 localhost나 %를 쓴다.

 

권한 빼앗기

REVOKE 권한종류 ON 데이터베이스명.테이블명 FROM '아이디'@'접속범위';

 

계정 삭제

DROP USER '아이디'@'접속범위';

참고로 아이디가 중복되어도 접속범위가 다르면 다른 아이디로 인식된다. 

로그인할때 각자 상황에 맞는 아이디로 자동으로 접속된다고 한다.(해본적은 없다.)

---

데이터베이스 선택

이거를 해야 데이터베이스 안에 있는 테이블들을 상대로 명령어를 작성할 수 있다.

use 데이터베이스명;

 

테이블 생성

아래와 같이 한다. 필드명은 변수명정도로 생각해두자.

create table 테이블명(
	필드명1 자료형 [옵션],
    ...
)

옵션에는 primary key(고유값 지정), auto primary key등이 들어간다. 

자료형은 varchar(바이트수), int등..

 

테이블 목록 참조

데이터베이스 내부의 테이블들 목록 참조가 가능하다.

show tables;

 

특정 테이블의 구조 참조

특정 테이블이 어떠한 필드와 자료형인지 확인 가능하다.

desc 테이블명;

 

테이블 삭제

drop table 테이블명;

 

테이블에 데이터 삽입

두 가지 방법을 썼는데, 두 번째꺼는 모든 필드에 값을 넣을때이다.

특정 필드에만 값 작성할 예정이라면 첫 번째거를 써야한다.

insert into 테이블명 (필드명1, 필드명2, ...) values (필드값1, 필드값2, ...)

insert into 테이블명 values (필드값1, 필드값2, ...)

 

테이블 데이터 조회

조건식은 생략가능하다. 원하는 필드명만 출력할 경우 첫 번째 명령어를. 모든 필드를 출력하려면 *을 쓴다.

select 필드명1, 필드명2,... from 테이블명 where 조건식;

select * from 테이블명 where 조건식;

 

테이블 데이터 갱신

내가 수정하고 싶은 필드만 수정 가능함.

update 테이블명 set 필드명=필드값, 필드명=필드값,... where 조건식;

update score set kor=90 where num=1;

 

테이블 데이터 삭제

delete from 테이블명 where 조건식;

---

 

PHP에서 MySQL에 쿼리를 요청 보내기 위해서는 일단 MySQL에 로그인을 하는 코드가 필요하다.

아래의 코드처럼 PDO라는 클래스를 이용해서 객체를 생성한다. 이 때, 객체를 조종할 변수가 필요하니까 $DB객체명을 통해 조종한다.

$DB객체명 = new PDO("mysql:host=서버주소;dbname=사용할DB명","사용자ID","비밀번호");

그리고, 쿼리를 실행하는 건 아래와 같다. $db객체명->query나 $db객체명->exec도 있지만 SQL Injection을 일으키고 싶지 않다면 prepare를 쓰도록 하자. 

$num = 3;
$query = $DB객체명->prepare("select * from score where num=:num");
$query->bindValue(":num",$num,PDO::PARAM_INT);
$query->execute();

$row = $query->fetch(인자)

prepare는 쿼리의 뼈대를 고정시켜놓고, 입력받은 값은 쿼리가 아닌 문자열로만 처리하는 함수이다.

$num에 무슨 값이 들어오던 무조건 문자열로만 처리되어 SQL injection에서는 안전하다.

참고로 변하는 값부분은 :num과 같이 표현한다. 

 

bindValue는 값을 묶어주는 함수이다. ':num'에다가 $num의 값을 넣어준다.

값을 묶어준 후에는 execute로 실행한다.

 

실행 한 뒤에는 무언가 반환값이 도착해있는데 그거를 fetch함수를 통해 꺼낼 수 있다.

fetch함수에는 PDO::FETCH_ASSOC, PDO::FETCH_NUM이 들어갈 수 있는데 PDO::FETCH_ASSOC을 넣으면 연관배열로 반환되고 PDO::FETCH_NUM을 넣으면 숫자 인덱스인 배열이 반환된다.