SQL

本章ではSQLの文法を学習していきます。

データベースやSQLがどのようなものだったか忘れてしまった方は、
「学習前に知っておくべきこと」のデータベースとはに戻って復習しましょう。

昨今、SQLを書くことなくデータベースの構造を操作できるアプリが登場しています。
しかし、アプリからデータベースを操作する場合はSQLを知っていないとデータを扱うことができないので、少しずつ慣れていきましょう！

データベースを作成しよう

データベースを作成する方法は2つあります。

1つ目は、アプリで直感的に操作する方法。
2つ目は、ターミナル上で操作する方法。

今回はターミナルを利用して、データベースの作成、操作を行っていきます。

SQLを実行するにはしようしているデータベースソフトにログインしなければなりません。
本講座で使用しているソフトはMySQL(MariaDB)です。

MySQLにログインするには以下のコマンドをターミナルで実行してください。
このコマンドはrootというユーザー名でログインするというものです。

mysql -u root

コマンド実行後、ターミナルが以下のようになっていれば正常にログインが完了しています。

ログアウトする場合は、「Ctrl + D」を押してください。
すると、今まで通りのターミナルに戻れます。

MySQLにログインし、SQLを実行できるようになったのでデータベースを作成していきます。
まずはデータベースを作成する構文を確認しておきましょう。

CREATE DATABASE データベース名;

「データベース名」の箇所を作成するデータベース毎に置き換えて使用しましょう。

今回はblogというデータベースを作成します。
以下のコマンドをターミナルで実行してください。

CREATE DATABASE blog;

SQLを実行して Query OK のメッセージが表示されていれば正常に実行されています。

blogというデータベースを作成することができたので、そのデータベースを使用することを宣言します。
以下のコマンドをターミナルで実行してください。

USE blog;

テーブルに関連する用語を知ろう

テーブルを作成する前に、テーブルに関わる用語を抑えておきましょう。

カラム

列のことをカラム、列毎の名前をカラム名と呼びます。

縦の赤枠がカラム、idやnameがカラム名になります。

レコード

行のことをレコードと呼びます。

横の赤枠がレコードです。

データ型

テーブルのセル(マス)に格納されるデータの種類のことを、データ型と呼びます。
セル毎にデータ型を指定するのではなく、カラム毎にデータ型を設定します。

データ型は使用するデータベースソフトによって多少異なる場合があります。
ですので、データ型を設定するときは都度調べるようにしましょう。

主要なデータ型

データ型の種類は非常に多いので覚える必要はありません。
しかし、よく使うデータ型の名前は抑えておきましょう。

INT

0~4294967295までの整数のみ格納できるようになります。

TINYINT

0~255までの整数のみ格納できるようになります。

VARCHAR

文字を格納できるようになります。
格納できる文字数は0~255文字以内なので、それ以上の文字を格納したい場合は別のデータ型をしようします。

DATETIME

日付と時刻を格納できるようになります。

属性

テーブルはカラム毎に属性を設定することができます。
データ型はデータの種類でしたが、属性は少し異なります。

主要な属性

データ型に比べ属性は少ないですが、初めてSQLを学ぶ場合は全てを覚える必要はありません。
使用頻度の高い属性の意味を抑えておきましょう。

NOT NULL

私たちは日常生活で、「何もない」や「存在しない」ことを「0(ゼロ)」などと表したりしますが、
ほとんどのプログラミング言語ではデータが存在しないことをNULLで表します。
NOT NULLはデータが存在しないことを許可しない属性です。

UNIQUE KEY

重複したデータを禁止する属性です。

PRIMARY KEY

データを一意に識別する属性です。
データの重複とNULLを禁止します。

AUTO_INCREMENT

データを挿入すると連番にしてくれる属性です。

テーブルを作成しよう

テーブルもデータベースを作成した時同様にターミナルでSQLを実行して作成します。

まずは構文を確認しておきましょう。
列が複数ある場合は「,(カンマ)」で区切ります。

CREATE TABLE テーブル名(カラム名 データ型 制約, カラム名 データ型 制約);

以下のSQLを実行し、postsテーブルを作成しましょう。
見やすくするために改行していますが、改行せずに実行しても同じ結果になります。

CREATE TABLE posts(
id INT AUTO_INCREMENT PRIMARY KEY,
body VARCHAR(255) NOT NULL
);

テーブルの構造を確認するには以下の構文でSQLを実行してください。

DESC テーブル名;

テーブルの構造が以下の画像のようになっていれば問題ありません。

データを挿入しよう

これまでの過程でテーブルを作成することができましたが、まだデータが登録されていません。
データを登録するにはINSERT文を使用します。

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

bodyカラムに対して「初めての投稿」という文を入れた状態でデータを挿入してみましょう。

INSERT INTO posts (body) VALUES ("初めての投稿");

今度は「2回目の投稿」という文を入れてデータを挿入してみましょう。

INSERT INTO posts (body) VALUES ("2回目の投稿");

データを取得しよう

先ほど挿入したデータをテーブルから取り出してみます。
テーブルからデータを取得するにはSELECT文を使用します。

SELECT カラム名 FROM テーブル名;

まずはpostsテーブルからbodyカラムのデータを取り出してみます。

SELECT body FROM posts;

データを正常に取得できると表形式になって表示されます。

複数のカラムのデータを取得したい場合は、カラム名をカンマで区切ります。

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

idカラムとbodyカラムのデータを取得する場合は以下のようなSQLになります。

SELECT id, body FROM posts;

テーブルにある全てのカラムのデータを取得する場合は、アスタリスクを使用します。

SELECT * FROM posts;

条件を付けてデータを取得しよう

特定のデータを取得したい場合、WHERE句を使用します。
まずは例文を見てみましょう。

SELECT * FROM posts WHERE id = 1;

このSQLは、
postsテーブルからidカラムのデータが1のレコードを取得せよ
という意味になります。

AND

条件を複数設定し、その条件全てに合致したデータを対象にする場合はANDを使用します。

SELECT * FROM posts WHERE id = 2 AND body = "2回目の投稿";

postsテーブルからidカラムのデータが2 かつ bodyカラムのデータが2回目の投稿のデータを取得せよ
という意味になります。

OR

条件を複数設定し、その条件のどれかに合致したデータを対象にする場合はORを使用します。

SELECT * FROM posts WHERE id = 1 OR body = "2回目の投稿";

postsテーブルからidカラムのデータが1 または bodyカラムのデータが2回目の投稿のデータを取得せよ
という意味になります。

SELECT * FROM テーブル名 WHERE の後に必ず成り立つ条件を設定すると、
テーブルにある全てのデータを取得します。

SELECT * FROM posts WHERE id = 100 OR 1 = 1;

postsテーブルにidカラムのデータが100のものがなくても、1 = 1は成り立つので全てのデータを取得してしまうのです。

データを更新しよう

データを更新するにはUPDATE文を使用します。

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

基本的な文法はこのようになりますが、このまま使ってしまうとSETで指定したカラム全てのデータが変更されてしまいます。
UPDATE文を使用するときは通常、WHERE句で更新の対象を決めます。

UPDATE posts SET body = "updated" WHERE id = 2;

postsテーブルのidカラムが2のレコードのbodyカラムのデータをupdatedに更新せよ
という意味になります。

データを削除しよう

データを削除するにはDELETE文を使用します。

DELETE FROM テーブル名;

DELETE文もUPDATE文同様に、WHERE句で削除の対象を決めて実行します。
そうしないと、テーブルのデータが全て削除されるので注意が必要です。

DELETE FROM posts WHERE id = 1;

SQLインジェクション

SQLインジェクションはアプリのセキュリティ不備を意図的に利用し、想定しないSQL文を実行させることにより、
データベースを不正に操作する攻撃手法のことをいいます。

以下のように、社員番号を入力するとその社員情報が取得できるSQL文があると想定します。

SELECT * FROM employees WHERE code = '入力内容';

社員番号が1234の人を検索する場合は以下のようになります。

SELECT * FROM employees WHERE code = '1234';

もし、a’ OR ‘a’ = ‘a’ と入力されると実行されるSQLは以下のようになります。

SELECT * FROM employees WHERE code = 'a' OR 'a' = 'a';

code = ‘a’ が成り立たなくても、’a’ = ‘a’は成り立ちますよね。
このように入力内容をなんでも受け付けるようにしているとデータが流出してしまったり、
データを削除されてしまう恐れがあります。

作ったばかりの知名度がないWebアプリでも色々なところから攻撃を受けます。
文法を覚えることも大事ですが、このような攻撃手法を知っておくことで対策を考えることができます。
この章では対策は紹介しませんが、PHPとデータベースを連携させるときに代表的な方法で
SQLインジェクションの対策を施します。