MySQLでよく使うSQL構文をメモ

少し間が空くと忘れがちなSQL構文を備忘録として書き残しておきたいと思います。
 

SELECT文

基本

SELECT カラム1,カラム2,カラム3 FROM テーブル名;

データベースの指定のテーブルのレコードを抽出する構文。

カラム名をワイルドカード(*)にすれば全カラムを抽出するが、その分処理にコストがかかるため、できるだけ使うカラムに限定して取り出すのがよい。

ただ、カラム指定の場合は後にカラムを追加した場合に関連するSQL文にもカラムをすべて追加するという手間は出てくる。

以下記述で抽出したレコード数を結果として受け取ることもできる。

SELECT COUNT(*) FROM テーブル名;

 

INSERT文

INSERT INTO テーブル名 (カラム1, カラム2, カラム3) VALUES (値1, 値2, 値3);

データベース内の指定のテーブルにレコードを追加する構文。

カラムと値を対応する形で記述する。カラムに対して値の数は少なかったりしないように注意。
 

UPDATE文

UPDATE テーブル名 SET カラム1=値1, カラム2=値2, カラム3=値3 WHERE カラム4 = 値4

アップデート文はデータベースの指定のテーブルの指定のレコードの値を更新するための構文。

通常はWHERE句を併用して更新したいレコードを絞り込んで操作を行う。

ちなみに、以下のような記述を行うことでカラムの数値をカウントアップすることもできる。

UPDATE テーブル名 SET カラム1 = 値1 + 1;

 

結合

INNER JOIN

SELECT * FROM テーブル1 INNER JOIN テーブル2 ON テーブル1.カラム名 = テーブル2.カラム名 ;

INNER JOIN句を使うと、ふたつのテーブルを関連するカラム名で結合させてマージした結果を受け取ることができる。

例えば、投稿データを取得する際に投稿者のニックネームも一緒に取得したい場合などには、会員テーブルと投稿テーブルを結合させて情報を抽出するといったことが必要になる。
 

トランザクション処理

BEGIN;
(SQL文1)
(SQL文2)
(SQL文3)
COMMIT;

トランザクション処理は複数のSQL文をひとまとまりの処理として扱い、すべてが正常に完了しない限り処理を完了しない。

例えば、ポイントを使って購入後にコンテンツを付与するような処理で、ポイントだけ差し引いてコンテンツが付与されないことが起こると問題になる。

この場合、コンテンツ付与が失敗した時点でポイント消費処理を行わないようにしなければならない。

トランザクション処理はこういった場合に利用する。
 

bindParamを利用したSQL実装例

$db = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASS);
$db->query('SET NAMES utf8');
$sql = "INSERT INTO users (name) VALUES (:name);";
$stt = $db->prepare($sql);
$stt->bindParam(':name', $name);
$ret = $stt->execute();
return $ret;

bindParamはSQLインジェクションを防止するためのSQLの記述方法。

SQLインジェクションは、簡単に言うとデータベースへ不正アクセスするための操作。

例えば、検索フォームを使った会員検索において会員の個人情報(パスワードやメールアドレス等)が取得できたら困るが、プログラミングの知識がある人の場合検索フォームにSQL文を記述することで情報が取得できてしまう。

bindParamはこういった操作を無効化できる。

重要なデータを扱うアプリケーションではSQLインジェクション対策は必須。
 

PDOを使用したSELECT操作サンプル


// DB接続情報
define("CHARSET", "UTF-8");
define("DB_HOST", "");
define("DB_USER", "");
define("DB_PASS", "");
define("DB_NAME", "");
// DB操作
$db = new PDO('mysql:host='.DB_HOST.';dbname='.DB_NAME, DB_USER, DB_PASS);
$db->query('SET NAMES utf8');
$sql = "SELECT name FROM users WHERE id = :id ";
$stt = $db->prepare($sql);
$stt->bindParam(':email', $email);
$stt->bindParam(':password', $password);
$stt->execute();
while($row=$stt->fetch()){
  $result['name'] = $row['name'];
}
if(isset($result)){
  return $result;
}

会員IDから会員名を抽出する操作を行うときの実装サンプル。

Follow me!