[SQL] 개발 환경 설정 및 문법

연·2024년 9월 1일

web

just공부

목록 보기

4/47

PHP 3분 요약 페이지가 이동하여 아래의 주소를 참고해 진행했다.

https://ysedeveloper.tistory.com/entry/401-MySQL-데이터베이스-생성하기?category=1083304

phpMyAdmin 접속 과정에서 애를 좀 먹었다.. 해결방법은 아래 블로그를 참고하여 해결했다.

https://olidang.tistory.com/83

phpMyAdmin 접속

데이터베이스 생성

테이블 생성

CREATE  TABLE  `tbl_person` ( 
    `person_id`  INT  NOT  NULL AUTO_INCREMENT , 
    `name`  VARCHAR(256) NOT  NULL , 
    `age`  INT NOT  NULL ,     
    PRIMARY  KEY (`person_id`)
) ENGINE = InnoDB;

테이블 : 동일한 특성을 가지는 행(row)의 모음
열(column) : 각 특성

테이블 생성 문법

CREATE TABLE 테이블이름 (열1이름, 열2이름, 인덱스) ENGINE=MySQL 엔진이름;.

열 : person_id, name, age

‘person_id’ INT NOT NULL AUTO_INCREMENT ,

INT : 정수 타입

데이터베이스 시스템에 데이터가 입력될 때 자동으로 숫자 증가

‘name’ VARCHAR(256) NOT NULL ,

VARCHAR : 문자열 타입, name 칼럼은 최대 256글자

비어 있으면 안됨

PRIMARY KEY (person_id)

PK : 각 열을 구분하는 유일한 식별자 (자동으로 생기는 행 번호)

인덱스 : 데이터베이스 성능을 위해 미리 참조를 만들어 놓는 것

PK는 primary key index로 인덱스 종류 중 하나

ENGINE = InnoDB;

InnoDB : MySQL 데이터베이스 엔진, 현대 바이너리 기반 엔진임

테이블에 값 가져오기

SELECT * FROM tbl_person where person_id = 1;

쿼리 : 데이터베이스에 질의 하는 구문
SELECT : 데이터베이스에 값을 가져오기 위한 쿼리
SELECT 컬럼들 from 테이블명 where 조건컬럼=조건값

테이블에 값 입력하기

INSERT INTO tbl_person (name, age) values ('yse', 50);

INSERT : 데이터베이스에 값을 입력하기 위한 쿼리
INSERT INTO 테이블이름 (컬럼1이름, 컬럼2이름) values (컬럼1값, 컬럼2값)

테이블 값 수정하기

UPDATE tbl_person set name='yse', age='30' where person_id = 1;

UPDATE : 데이터베이스에 값을 수정하기 위한 쿼리
UPDATE 테이블명 set 컬럼1이름=컬럼1값, 컬럼2이름=컬럼2값 where 조건

테이블 값 삭제하기

DELETE FROM tbl_person where person_id = 1;

DELETE : 데이터베이스에 값을 삭제하기 위한 쿼리
DELETE 테이블명 where 조건

PHP에서 MySQL 데이터베이스에 접속

MySQL 데이터베이스를 생성한 접속 정보를 PHP 파일에 기술함으로써 PHP에서 데이터베이스에 접속 가능함


<?php
function db_get_pdo()
{
    $host = 'localhost';
    $port = '3306';
    $dbname = 'php3min';
    $charset = 'utf8';
    $username = 'php3min';
    $db_pw = "1234";
    $dsn = "mysql:host=$host;port=$port;dbname=$dbname;charset=$charset";
    $pdo = new PDO($dsn, $username, $db_pw);
    return $pdo;
}

위의 코드를 db.php로 저장. DB 섹션 전반에 사용됨.
php에서 데이터베이스에 접속하기 위해선 PDO라는 내장 객체를 사용.
데이터베이스에 쿼리를 실행할 때마다 늘 데이터베이스에 연결하는 것이기 때문에 db_get_pdo() 함수를 분리했음
host : 데이터베이스 서버 주소, localhost : 본인의 컴퓨터
port : 데이터베이스 서버 포트, MySQL의 데이터가 오가는 통로
dbname : 데이터베이스의 이름
charset : 인코딩 방법, 대부분 utf8 사용
username : 데이터베이스 사용자 이름
db_pw : 데이터베이스 사용자 비밀번호
dsn : 데이터베이스 관련 정보를 바탕으로 만든 데이터베이스 연결 문자열
PDO 객체 : dsn, 사용자 이름, 사용자 비밀번호를 파라미터로 받아서 데이터베이스에 실제로 연결하는 역할을 함

PHP에서 MySQL 데이터베이스 데이터 가져오기

function db_select($query, $param=array()){
    $pdo = db_get_pdo();
    try {
        $st = $pdo->prepare($query); // 실제 쿼리 실행
        $st->execute($param); // 쿼리 실행
        $result =$st->fetchAll(PDO::FETCH_ASSOC); // 데이터 전부 가져옴
        $pdo = null;
        return $result;
    } catch (PDOException $ex) {
        return false;
    } finally {
        $pdo = null; // 초기화해야 데이터베이스 커넥션 끊김
    }
}
// 결과 확인 후, 아래 코드 삭제
var_dump(db_select("select * from tbl_person"));
echo "<br />";
var_dump(db_select("select * from tbl_person where person_id = ?", array(1)));
echo "<br />";
var_dump(db_select("select * from tbl_person where person_id = :person_id", array('person_id' => 1)));

db_get_pdo() 함수를 사용하므로 db.php 파일 아래 붙임
$pdo = MySQL에 접속하는 객체
prepare statement : 플레이스 홀더를 통해 전달된 파라미터들의 SQL Injection 방지

fetch_style

	설명	예시
FETCH_ASSOC	연관배열로 데이터를 가지고 온다.	fetchAll(PDO::FETCH_ASSOC)
FETCH_CLASS	객체 형태로 데이터를 가지고 온다. 뒤에	기호로 객체 타입을 지정한다.
FETCH_OBJ	익명 객체 형태로 데이터를 가지고 온다. 자동으로 컬럼이 멤버변수화된다.	fetchAll(PDO::FETCH_OBJ)
FETCH_NUM	순차배열 형태로 데이터를 가지고 온다.	fetchAll(PDO::FETCH_NUM)
FETCH_BOTH	FETCH_ASSOC + FETCH_NUM	fetchAll(PDO::FETCH_BOTH)

db_select 함수 사용법

// 파라미터 없는 경우
db_select("select * from tbl_person");
// 쿼리에 전달할 파라미터가 있는 경우
db_select("select * from tbl_person where person_id = ?", array(1))
db_select("select * from tbl_person where person_id = :person_id", array('person_id' => 1))

쿼리에 파라미터를 전달하는 방법

// 1. 쿼리 문자열 안 ? 넣기
db_select("select * from tbl_person where person_id = ?", array(1))
// 2. 쿼리 문자열 안 :키 넣기
// 파라미터 배열은 연관 배열로 전달, 각 키에 바인딩됨
db_select("select * from tbl_person where person_id = :person_id", array('person_id' => 1))

PHP에서 MySQL 값 입력하기

function db_insert($query, $param = array())
{
    $pdo = db_get_pdo();
    try {
        $st = $pdo->prepare($query);
        $result = $st->execute($param);
        $last_id = $pdo->lastInsertId();
        $pdo = null;
        if ($result) {
            return $last_id;
        } else {
            return false;
        }
    } catch (PDOException $ex) {
        return false;
    } finally {
        $pdo = null;
    }
}
 
var_dump(
    db_insert(
        "insert into tbl_person (name, age) values (?,?)",
        array("php", 25)
    )
);
 
// 실행 확인 후 삭제
var_dump(
    db_insert(
        "insert into tbl_person (name, age) values (:name,:age)",
        array("name" =>"php7", "age" => 5)
    )
);

db.php파일 아래 붙임
$pdo→lastInsertID() 메소드: 자동으로 설정되는 PK 가져옴
db_insert 함수는 성공할 경우 PK, 실패할 경우 false 반환

PHP에서 MySQL 데이터베이스 데이터 수정/삭제하기

function db_update_delete($query, $param = array())
{
    $pdo = db_get_pdo();
    try {
        $st = $pdo->prepare($query);
        $result = $st->execute($param);
        $pdo = null;
        return $result;
    } catch (PDOException $ex) {
        return false;
    } finally {
        $pdo = null;
    }
}
 
// 확인 후 삭제
var_dump(
    db_update_delete(
        "update tbl_person set age=:age where person_id = :person_id", 
        array("age" => 4, "person_id" => 1) 
    )
);
 
var_dump(
    db_update_delete(
        "delete from tbl_person where person_id = ?", 
        array(3) 
    )
);