今日学習する内容はリレーショナルデータベースに対する問い合わせ言語であるSQL（Structured Query Language）についてです。そしてデータベースに対する基本操作であるCRUDを学習していきます。CRUDとはデータベースへの操作クエリの頭文字をとったものです。
- Create（新規作成）
- Read（読み取り）
- Update（更新）
- Delete（削除）
本記事では、EC2インスタンスにMariaDBがインストールしてあり、phpMyAdminからMariaDBにインストールできていることを前提に解説していきます。設定がまだの方は先に「EC2インスタンスにMariaDBをインストールする」と「phpMyAdminのインストールと設定、そしてテーマを変えてみよう」の学習を終えてからこちらの記事に戻ってきてください。
SQLの種類
SQL言語内ではいくつかの分類があります。今回の基礎編ではDDLとDML+DQL（＝CURD）を取り上げていきます。
- DDL（Data Definition Language）・・・データベースの構成管理
- DML（Data Manipulation Language）・・・データの操作（INSERT、UPDATE、DELETE）
- DQL（Data Query Language）・・・データの検索（SELECT）
- DCL（ Data Control Language）・・・権限管理
- TCL（Transaction Control Language）・・・トランザクション管理
コレーション（Collation）とは
Collation (照合順序) とは、データベース内で文字列の比較や並び順を決めるルールになります。MariaDB/MySQLではCollationはデータベース、テーブル、フィールドの３レベルでCollationを設定することができます。それぞれ下位レベルにCollationの指定がなければ上位レベルの設定が流れ落ちて（カスケード）設定されることになります。
例えば、テーブルレベルにCollationの指定がなければデータベースレベルの設定が適用されます。フィールドにCollationの指定がなく、テーブルレベルにも指定がない場合はデータベースレベルの設定がフィールドに適用されることになります。
Collationはカスケード適用されますが、どのレベルでも明示的に設定する様にしましょう。
データベースエンジンとは
データベースエンジンにはいくつかの種類があります。代表的な３つを紹介しておきます。
- InnoDB
-
トランザクションや外部キー制約が可能でデータの整合性を保つことができます。
- MyISAM
-
InnoDBのようなトランザクションは外部キー制約はできないが、全文検索などの読み取りが高速。
- MEMORY
-
メモリ上にデータを持つのでシステム起動時にデータは失われますが、超高速なデータアクセスが可能。
クエリは基本縦書き
SQL文は横に長く書くこともできますが、可動性を上げるために適度な改行とインデントをいれるのが慣例です。以下のSQL文の例も縦書きにしてあります。
本番環境のクエリは改行やインデントを入れて縦書きで書きましょう。
データベースの作成
まずはデータベースの作成です。以下の例では文字コードは utf8mb4 で、その中でも unicode のCase Insensitive（大文字小文字区別なし）を指定しています。
CREATE DATABASE testdb
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;データベースの削除
以下のクエリでデータベースの削除ができます。データベースとデータベース内の全てのデータを削除してしまうので実行には注意が必要です。
DROP DATABASE testdb;テーブルの作成
testdbの中にuserテーブルを作成してみましょう。
以下の例の内容
- user_idフィールドを主キーとしている
- データベース作成時同様にCollationにはutf8mb4_unicode_ciを明示的に指定している
- データベースエンジンにはInnoDBを指定している
CREATE TABLE IF NOT EXISTS testdb.user (
user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY
, user_name VARCHAR(64)
, age TINYINT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci;テーブルの削除
以下のクエリでデータベース内のテーブルを削除できます。テーブルのデータはすべて失われるので実行の際は十分な注意が必要です。
DROP TABLE testdb.user;
INSERT
以下の例では１度に２件のデータを登録しています。user_idにはAUTO_INCREMENTが設定されているので未指定にすると自動的に1, 2, ..と番号が振られていきます。
INSERT INTO testdb.user
(`user_name`, `age`)
VALUES
('Mike', 18)
, ('Nancy', 19);SELECT
登録された２件のうち１件を選択してみましょう。
SELECT
user_id
, user_name
, age
FROM testdb.user
WHERE age = 19;SELECT * とすると全フィールドを取得しますが本番環境で使用するのはやめましょう。使用されている必要最低限のフィールドを明示的に記述しましょう。
DELETE
以下のクエリでデータの削除ができます。WHERE句を使用して削除対象のデータを絞り込みます。
DELETE FROM testdb.user WHERE age = 19;上記ではクエリは基本的に縦書きと言いましたが、管理画面上から実行する場合のDELETE文はWHERE句のコピペ漏れを防ぐために１行で書くのがよいでしょう。
UPDATE
以下のクエリでデータの更新を行います。WHERE句を使用して対象データを絞り込みます。
UPDATE testdb.user SET `user_name` = 'Mike Fox', `age` = 23 WHERE age = 18;管理画面上から実行する場合はUPDATE文もWHERE句のコピペ漏れを防ぐために１行で書くのがよいでしょう。
インデックスの作成
WHERE句で使用されるフィールドにはインデックスを作成しておきましょう。インデックスを設定しておくことで保存時にデータが検索に最適化された状態になり、検索時のパフォーマンスが向上します。
CREATE INDEX index_user_age ON testdb.user (age);インデックス名は接頭辞のindex + テーブル名＋フィールド名にすると重複なく作成できます。
作成されたインデックスの確認
インデックスが実際に作成されたことを以下のクエリで確認してみましょう。
SHOW CREATE TABLE user;検索結果の全体が表示されない場合は、検索結果近くのオプション部分から全体を表示する様に変更してください。以下のKEYが確認できればインデックスの作成は成功です。
KEY `index_user_age` (`age`)インデックスの削除
インデックスの削除は以下のクエリで行います。
ALTER TABLE testdb.user DROP INDEX site_id;まとめ
今回の記事では基礎的なSQLクエリとインデックスの作成について学習しました。CRUDはCreat、Read、Update、Deleteの頭文字でしたね。まずはCollationのカスケードについて理解をし、データベースエンジンの種類についても学びました。今回の例ではInnoDBエンジンを使用しました。そして、クエリは基本的に縦書きであることも知りました。クエリの例としてデータベースの作成・削除、テーブルの作成・削除、INSERT、SELECT、DELETE、UPDATE文を試しました。更にインデックスの作成と削除についても学びました。今回の学習は奥が深いSQLのほんの一歩目です。今後もSQLの学習を一緒に深めていきましょう。
コメント