すでにメンバーの場合は

無料会員登録

GitHubアカウントで登録 Pikawakaが許可なくTwitterやFacebookに投稿することはありません。

登録がまだの方はこちらから

Pikawakaにログイン

GitHubアカウントでログイン Pikawakaが許可なくTwitterやFacebookに投稿することはありません。

SQLを使ってリレーショナルデータベースを操作してみよう

この記事で出来るようになること

はじめに

概要

この章では、phpMyAdminを用いてSQL文を実行する方法を学びます。phpMyAdminは、データベースの直接編集・管理だけでなく、SQL文を使用してデータを操作する機能を備えています。これまでに学んだリレーショナルデータベースの用語やphpMyAdminの基本操作を踏まえ、SQL文を活用してデータベースをより深く理解しましょう。

Railsでは便利なメソッドが用意されているので、SQLを直接的に記述しなくてもデータベースを操作できます。しかし、SQLの知識を持つことで、裏側で何が行われているのかや、複雑なデータの取り扱いを理解できるようになります。

目標

この章の目標
  • phpMyAdminを使用して、SQL文の基本を理解する
  • SQL文を実行し、データベースの操作に慣れる
  • データ定義言語(DDL)とデータ操作言語(DML)の違いを把握する

この章で完成するもの

この章を通じて、phpMyAdminでSQL文を実行し、データベースの基本的な操作をマスターします。具体的には、テーブルの作成・編集、データの挿入・更新・削除などの操作を行い、SQL文の基本を身につけます。

使用するデータベースはemployee_management_developmentです。phpMyAdminの機能を利用してSQL文を記述し、実行して指示を出します。

必要な前提条件・事前準備

この章を始める前に、リレーショナルデータベースに関する基本用語と概念、phpMyAdminへのログイン方法、およびその基本操作についての理解が必要です。また、phpMyAdminを使った前章で作成したテーブルの削除が完了していることを確認してください。

必要なもの・知識

phpMyAdminでは、データベースを直接編集・管理する機能に加え、SQL文を実行して結果を表示する機能も備わっています。本章では、このSQL文実行機能を活用していきます。

以下の手順に従って、SQL文を実行する画面を確認しましょう。

SQL文を実行する画面を確認しよう

phpMyAdminにログインした後、SQL文を実行するための画面を確認しましょう。

SQL文を記述するエリアの表示

まずは、phpMyAdminのメインページ上部にある「SQL」タブをクリックします。

メインページからSQLページへの移動phpMyAdminのメインページ

クリックすると、以下のようにSQL文を記述するためのエリアが表示されます。ここにSQL文を入力し、「実行」ボタンを押すことで、SQLが実行され、結果がテーブル形式で表示されます。

SQL文を記述するエリアSQL文を記述するエリア

データベースを選択するまでは、このエリアでSQL文を実行します。データベース選択後は、そのデータベースのSQL文を記述するエリアを使用します。

それでは、SQL文の学習を始めましょう!

ぴかわかさん

SQLとは

SQLとは、RDBMSに指示を伝えるためのデータベース言語のことです。

ユーザーやアプリケーションなどの「データベースを利用する側」のSQL文(SQLで記述された命令文)の指示を受けて、RDBMSがリレーショナルデータベースを操作します。

データベース言語

SQLには様々な命令が存在しますが、データベースやテーブルを作成できる「データ定義言語」とデータの操作ができる「データ操作言語」におおまかに分類することができます。

基本書式

SQLの書き方は後ほど手を動かしながら学習しますが、条件を付加してデータを抽出するSELECT文の基本書式は、以下の通りです。

基本書式 | 条件を付加してデータを抽出する
1
SELECT カラム名 FROM テーブル名 WHERE 条件;

あらかじめ用意されているSELECTFROMWHEREなどのキーワード(SQLで命令のための語句)を利用し、テーブル名やカラム名、条件の値を指定することで、SQL文を完成させることができます。

文の最後には、セミコロン(;)を付けます。

基本書式

値はシングルクォート(')で囲みます。

SQL | birthdayが1983-01-01よりも後のレコードを抽出する
1
SELECT * FROM employees WHERE birthday > '1983-01-01';
ぴっかちゃん

あれ?phpMyAdminで自動生成されたSQL文には、シングルクォートだけじゃなくてバッククォートで囲まれる箇所があったような・・・。

良いところに気がついたね。2つの記号は、次の注意点で説明するよ!

ぴかわかさん

SQL文を記述する際の注意点

phpMyAdminでデータを更新した際に自動生成されたSQL文には、条件の値を囲むシングルクォート(')以外にも、バッククォート(`)で囲まれる箇所がありましたね。

phpMyAdminでデータを更新

以下のように先ほどのSQL文とphpMyAdminで自動生成されるSQL文を比べてみると、条件の値をシングルクォート(')で囲むという点は変わりません。

しかし、自動生成されるSQL文のテーブル名カラム名は、バッククォート(`)で囲まれています。

先ほどの書き方で説明したSQL文
1
SELECT * FROM employees WHERE birthday > '1983-01-01';
phpMyAdminで自動生成されたSQL文
1
SELECT * FROM `employees` WHERE `birthday` > '1986-01-01';

バッククォートは無くても問題ありませんが、「データベース名」「テーブル名」「カラム名」をバッククォートで囲うことによって、予約語をエスケープすることができます。

予約語とは

予約語とは「用途が決められた文字列」のことです。(詳細:MariaDBの予約語

SELECTFROMなどのキーワードは予約されており、「データベース名」「テーブル名」「カラム名」として使用することができません。

例えばDATABASEは予約語なので、データベース名に使用することができません。

データベースを作成するSQL文
1
CREATE DATABASE データベース名;
データベース名にDATABASEを使用できない
1
CREATE DATABASE DATABASE;

しかし、予約語であるDATABASEをバッククォート(`)で囲むことで、データベース名として使用可能になります。

バッククォートによって、データベース名にDATABASEを使用できる
1
CREATE DATABASE `DATABASE`;

使用可能にはなりますが、予約済のキーワードは用途が決められた文字列なので、上記のようなデータベース名は適切ではありません。今回は説明用として例を挙げています。

本章では、SQLで「データベース名」「テーブル名」「カラム名」を記述する際にバッククォートを使用しないので注意しましょう。

シングルクォート(')とバッククォート(`)は、よく間違えられやすい符号なので、「条件の値を書くときはシングルクォートで囲む」ということも覚えておきましょう。

ポイント
  1. 予約語とは、用途が決められた文字列のこと
  2. 予約済のキーワードは、バッククォート(`)で囲うと「テーブル名」や「カラム名」などの識別子として使用できる
  3. SQL文で値を囲む記号は、シングルクォート(')である

SQL文のルールを確認しよう

SQLでは、一般的なスタイルガイド(ルールを集めたもの)が定まっていません。

例えば、SQLで命令文を記述する際に「大文字で書く」「小文字で書く」というルールは特にありません。

大文字で書く場合の例
1
SELECT * FROM departments;
子文字で書く場合の例
1
select * from departments;

しかし実際の開発現場では、「書き方で迷わない」「可読性を高める」「バグを発見しやすくする」といったメリットを享受するために、ルールを決めているところが大半です。

Pikawakaカリキュラムでも、いくつかのルールに従ってSQL文を記述して頂きます。

予約語は大文字で記述する

予約語とは「用途が決められた文字列」のことで、SQLではSELECTFROMなどが挙げられます。(詳細:MariaDBの予約語

予約語は、大文字で記述しましょう。

SQL | 予約語は大文字で記述すること
1
SELECT * FROM departments;

SQL文を記述するエリアでは、入力補完(入力内容を予測して候補を表示する機能)があるので、大文字でも入力が楽になります。

入力補完

インデントは半角スペース2つ分

インデント(字下げ)は「半角スペース2つ分」で揃えましょう。

インデントは半角スペース2つ分

特定の予約語の後には改行を入れる

以下の予約語の後に改行を入れましょう。

改行を入れる予約語一覧
1
2
3
4
5
SELECT
FROM
WHERE
GROUP BY
ORDER BY

全ての予約語の後に改行を入れてしまうと、かえって読みづらくなります。そのため、処理の大区分となる予約語だけ1段目に記述します。

特定の予約語だけに改行を入れる
1
2
3
4
5
6
SELECT
  name
FROM
  employees
WHERE
  department_id = 1;

データ定義言語

データ定義言語は、DDL(Data Definition Language)とも呼ばれて、データベースの構造を定義する言語のことです。主にデータベースやテーブルの作成、削除、変更を加える際に使用します。

あらかじめ用意されているデータ定義言語の主なキーワードは、以下の通りです。

  • CREATE - データベースやテーブルを作成する
  • DROP - データベースやテーブルを削除する
  • ALTER - データベースやテーブルに変更を加える

データ定義言語で定義された構造の中の個々のデータを操作する言語を「データ操作言語」と呼びます。

まずは、データ定義言語でデータベースやテーブルを作成してみます。

ぴかわかさん

データベース一覧を表示する

SQL文でデータベース一覧を表示してみます。
railsコマンドによって、開発環境とテスト環境のデータベースを作成していましたね。

2つのデータベースを作成

SQLでデータベース一覧を表示するには、SHOW DATABASES構文を使用します。

SQL文 | 全てのデータベース一覧を表示する
1
SHOW DATABASES;
SHOW DATABASES構文を使用して、データベース一覧を表示しましょう

以下のようにSQL文を記述するエリアSHOW DATABASES;と記述し、下にある「実行」ボタンをクリックしてみましょう。

データベースを一覧表示する

「実行」ボタンをクリックすると、SQL文が実行されます。実行結果は、以下のようにテーブル形式で表示されます。

一覧表示によって、以前railsコマンドで作成した「employee_management_development」と「employee_management_test」のデータベースも確認することができます。

実行結果

このようにSHOW文によって、データベース一覧を表示することができます。

クエリボックスを表示させましょう

以下のように「クエリボックスを表示」というボタンをクリックすると、再びSQL文を記述するエリアを表示することができます。

クエリボックスを表示

データベースを作成する

SQLで新しいデータベースを作成するには、CREATE DATABASE構文を使用します。

データベースを作成する
1
CREATE DATABASE データベース名;
CREATE DATABASE構文を使用して、データベースを作成してみましょう

以下のSQL文を実行して、pikawakaという名前のデータベースを作成しましょう。

SQL | pikawakaという名前のデータベースを作成する
1
CREATE DATABASE pikawaka;

データベース作成

「実行」ボタンをクリックすると、SQL文が実行されます。データベースが作成されると、以下のように表示されます。

実行結果

SHOW DATABASES構文を使用して、データベースを一覧表示させましょう

以下のSHOW DATABASES構文でデータベースを一覧表示して、作成したデータベースがあるか確認しましょう。

SQL文 | 全てのデータベース一覧を表示する
1
SHOW DATABASES;

上記の実行結果によって、以下のように「pikawaka」という名前のデータベースを作成できたことが確認できます。

データベースを作成

データベースを削除する

SQLでデータベースを削除するには、DROP DATABASE構文を使用します。

データベース削除
1
DROP DATABASE データベース名;
DROP DATABASE構文を使用して、データベースを削除してみましょう

以下のSQL文を実行して、先ほど作成した「pikawaka」という名前のデータベースを削除してみましょう。

SQL | pikawakaという名前のデータベースを削除する
1
DROP DATABASE pikawaka;
SHOW DATABASES構文を使用して、データベースを一覧表示させましょう

以下のSHOW DATABASES構文でデータベースを一覧表示して、「pikawaka」という名前のデータベースが削除できていることを確認しましょう。

SQL文 | 全てのデータベースを一覧表示する
1
SHOW DATABASES;

実行結果

使用中のデータベース名を表示する

現在使用しているデフォルトのデータベース名の表示は、database関数を使用します。

現在使用中のデータベースを表示する
1
SELECT database();
SELECT database();を実行して、使用中のデータベース名を表示させましょう

現在使用しているデータベースはないのでSELECT database();を実行すると、以下のようにNULLが返ります。

現在使用しているデータベースはない

テーブルを作成する前に使用するデータベースを選択します。

ぴかわかさん

データベースを選択する

SQLでデータベースを選択するには、USE構文を使用します。

使用するデータベースを選択する
1
USE データベース名;

使用するデータベースを選択していない場合は、テーブル名の指定をデータベース名.テーブル名という形式で記述する必要があります。

使用するデータベースを選択していない場合
1
SELECT * FROM データベース名.テーブル名;

しかし、USE構文を使ってデータベースを選択していれば、データベース名を省略してテーブル名を指定することができます。

使用するデータベースを選択している場合
1
2
USE データベース名;
SELECT * FROM テーブル名;
USE構文を使用して、データベースを選択しましょう

以下のSQL文を実行してemployee_management_developmentを選択しましょう。SELECT database();で、現在使用しているデータベース名も表示させます。

使用するデータベースを選択する
1
2
USE employee_management_development;
SELECT database();

使用するデータベースを選択する

「実行」ボタンをクリックすると、SQL文が実行されます。指定したデータベースが選択されて、以下のように現在のデフォルトのデータベースとして表示されます。

実行結果

セッションの終了

セッションが終了すると、デフォルトのデータベースではなくなるので注意しましょう。

phpMyAdminの場合は、以下のように新たにSQL文を実行するページを開くと、セッションが終了します。database関数を使用しても、現在選択しているデータベースはないので、NULLが返ります。

NULLが返る

phpMyAdminでは、データベース毎にSQL文を実行する画面があります。SQL文をemployee_management_development上で確実に実行できるように画面を変更していきます。

employee_management_developmentのSQL文を実行する画面を開きましょう

まずは、メインページからデータベース一覧画面に移動して、以下のようにデータベース一覧からemployee_management_developmentをクリックしましょう。

データベースを選択する

続いて、以下のようにemployee_management_development内の画面上部にある「SQL」をクリックして、SQL文を実行する画面を開きましょう。

SQL文を実行する画面

クリックすると、以下のようにSQL文を記述するエリアが表示されます。

現在の位置がlocalhostではなく、employee_management_developmentを示していますね。これ以降は以下のページでSQL文を実行します。

SQL文を実行する画面

上の画面を利用することで、使用するデータベースを選択している場合と同様に、テーブル名の指定をデータベース名.テーブル名という形式ではなく、データベース名を省略してSQL文を記述することができます。

テーブル一覧を表示する

SQLでテーブル一覧を表示するには、SHOW TABLES構文を使用します。

テーブル一覧を表示する
1
SHOW TABLES;
SHOW TABLES構文を使用して、テーブル一覧を表示してみましょう

現在の位置がlocalhostではなく、employee_management_developmentであることを確認して、SHOW TABLES;を実行してみましょう。

テーブル一覧を表示する

employee_management_developmentのデータベース上に作成しているテーブルはないので、以下のようにSQL文を実行しても何も表示されません。

テーブル一覧を表示する

ぴっかちゃん

次はemployee_management_developmentのデータベース上にテーブルを作成するよ!

テーブルを作成する

SQLでテーブルを作成するには、CREATE TABLE構文を使用します。

テーブルを作成する際に、カラムのデータ型やオプションを指定することができます。カラムの名前、データ型、オプションは半角スペースで区切ります。カラムが複数ある場合は、カンマ(,)で区切ります。

テーブルを作成する
1
2
3
4
5
CREATE TABLE テーブル名 (
  カラム名1 [データ型] [その他オプション],
  カラム名2 [データ型] [その他オプション],
  カラム名3 [データ型] [その他オプション],
);
CREATE TABLE構文を使用して、簡易的なusersテーブルを作成してみましょう

usersテーブルには、nameという名前のカラムだけ作成します。カラムのデータ型はVARCHAR型、制約は非NULL制約(NOT NULL制約)を設定します。

以下のSQL文を実行してください。

usersテーブルを作成する
1
2
3
CREATE TABLE users (
  name varchar(30) NOT NULL
);

usersテーブルを作成する

「実行」ボタンをクリックすると、SQL文が実行されます。

SQL文が実行

SHOW TABLES構文を使用して、テーブル一覧を表示してみましょう

以下のSQL文も実行して、usersテーブルが作成されたことを確かめてみましょう。

テーブル一覧を表示する
1
SHOW TABLES;

上記のSQL文を実行すると、以下のようにemployee_management_developmentのデータベース上に先ほど作成したusersテーブルが表示されます。

usersテーブル

カラムに関する情報を表示する

テーブル内のカラムに関する情報を表示するには、SHOW COLUMNS構文を使用します。

特定のテーブル内のカラムに関する情報を表示する
1
SHOW COLUMNS FROM テーブル名;
SHOW COLUMNS構文を使用して、カラムに関する情報を表示しましょう

以下のSQL文を実行して、先ほど作成したusersテーブル内のカラムに関する情報を表示してみましょう。

usersテーブル内のカラムに関する情報を表示する
1
SHOW COLUMNS FROM users;

「実行」ボタンをクリックすると、SQL文が実行されます。実行結果は、以下のようにテーブル形式で表示されます。

usersテーブル内のnameカラムに指定した型と制約を確かめることができましたね。

カラムの情報

次はSQLで主キー制約を設定する方法を学習します!

ぴかわかさん

主キー制約を設定する

主キー制約の設定は、基本的にテーブル作成時に行います。

SQLでは、PRIMARY KEY(カラム名)で指定した名前のカラムに主キー制約を設定することができます。最後にカンマ(,)を付ける必要はありません。

テーブルを作成する
1
2
3
4
5
6
CREATE TABLE テーブル名 (
  カラム名1 [データ型] [その他オプション],
  カラム名2 [データ型] [その他オプション],
  カラム名3 [データ型] [その他オプション],
PRIMARY KEY(カラム名)
);

主キー制約を設定するカラムには、非NULL制約(NOT NULL制約)も合わせて設定する必要があります。主キー制約の設定が必要なdepartmentsテーブルを作成して書き方を学びましょう。

departmentsテーブルの詳細情報を確認しましょう

departmentsテーブルに必要なカラムはidnameです。

作成するテーブル

主キーであるidのカラムには、主キー制約と非NULL制約を合わせて設定します。さらに自動的に一意の値を付与してくれるAUTO_INCREMENTという機能も使用します。

その他のデータ型と制約は、以下の通りです。

カラム名 データ型 制約 その他
id INT型 主キー制約(PRIMARY KEY)
非NULL制約(NOT NULL制約)
AUTO_INCREMENT
name VARCHAR型 非NULL制約(NOT NULL制約)

上記の内容でdepartmentsテーブルを作成するSQL文は、以下の通りです。

PRIMARY KEYidのカラムを指定していますね。そして、idにはNOT NULLが指定される点にも注目してください。

departmentsテーブルを作成する
1
2
3
4
5
CREATE TABLE departments (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(30) NOT NULL,
PRIMARY KEY(id)
);
上記のSQL文を実行して、departmentsテーブルを作成しましょう

departmentsテーブルを作成する

「実行」ボタンをクリックすると、SQL文が実行されます。テーブルが作成されると、以下のように表示されます。

テーブル作成後

SHOW TABLES構文を使用して、テーブル一覧を表示してみましょう

以下のSQL文も実行して、departmentsテーブルがあるかを確かめてみましょう。

テーブル一覧を表示する
1
SHOW TABLES;

上記のSQL文を実行すると、以下のようにemployee_management_developmentのデータベース上に先ほど作成したdepartmentsテーブルが表示されます。

departmentsテーブルが表示される

SHOW COLUMNS構文を使用して、カラムに関する情報を表示しましょう

以下のSQL文を実行して、departmentsテーブル内のカラムに設定したデータ型や制約も確かめてみましょう。

departmentsテーブル内のカラムに関する情報を表示する
1
SHOW COLUMNS FROM departments;

「実行」ボタンをクリックすると、SQL文が実行されます。実行結果は、以下のようにテーブル形式で表示されます。

主キー制約がidカラムに設定されていることを確かめることができますね。

departmentsテーブル内のカラムに関する情報を表示する

phpMyAdminでも確認すると、以下のようにidのカラムには「主キー制約」が設定されており、鍵のアイコンが表示されます。

主キー制約

次はemployeesテーブルを作成して、外部キー制約の設定を学びます。

ぴかわかさん

外部キー制約を設定する

テーブル作成時に外部キー制約を設定することができます。子テーブル側で設定します。

SQLでは、FOREIGN KEYREFERENCESを使用します。FOREIGN KEYでは、外部キー制約を設定する子テーブルのカラム名、REFERENCESでは基本的に参照先の親テーブルと親テーブルの主キーとなるカラム名を指定します。

FOREIGN KEY(カラム名)の後にカンマ(,)を付ける必要はありません。

テーブルを作成する
1
2
3
4
5
6
7
8
CREATE TABLE 子テーブル名 (
  カラム名1 [データ型] [その他オプション],
  カラム名2 [データ型] [その他オプション],
  カラム名3 [データ型] [その他オプション],
  PRIMARY KEY(カラム名),
FOREIGN KEY(カラム名)
REFERENCES 親テーブル名(カラム名)
);

前回のphpMyAdminの「親テーブルに対する削除」や「親テーブルに対する更新」では、整合性を保つために親テーブルのレコードの削除や主キーの値を更新することができませんでしたね。

SQLではON DELETEON UPDATERESTRICTを指定することで、親テーブルに対する削除・更新操作を拒否することができます。

テーブルを作成する
1
2
3
4
5
6
7
8
9
10
CREATE TABLE 子テーブル名 (
  カラム名1 [データ型] [その他オプション],
  カラム名2 [データ型] [その他オプション],
  カラム名3 [データ型] [その他オプション],
  PRIMARY KEY(カラム名),
  FOREIGN KEY(カラム名)
    REFERENCES 親テーブル名(カラム名)
ON DELETE RESTRICT -- 親テーブルに対する削除操作を拒否する
ON UPDATE RESTRICT -- 親テーブルに対する更新操作を拒否する
);

外部キー制約の設定が必要なemployeesテーブルを作成して、書き方を学びましょう。

employeesテーブルの詳細情報を確認しましょう

外部キー制約は、子テーブルであるemployeesテーブルのdepartment_idに設定します。department_idは、departmentsテーブルの主キーであるidを参照します。

外部キー制約の設定

ほとんどのカラムには、非NULL制約を設定しますが、birthdayのカラムだけは、デフォルト値をNULLにできるよう制約を設定しません。

その他のデータ型と制約は、以下の通りです。

カラム名 データ型 制約 その他
id INT型 主キー制約(PRIMARY KEY)
非NULL制約(NOT NULL制約)
AUTO_INCREMENT
name VARCHAR型 非NULL制約(NOT NULL制約)
birthday DATE型 デフォルト値:
NULL
department_id INT型 外部キー制約(FOREIGN KEY)
非NULL制約(NOT NULL制約)

上記の内容でemployeesテーブルを作成するSQL文は、以下の通りです。

FOREIGN KEYdepartment_idを指定していますね。そして、REFERENCESで参照先の親テーブル内にある主キーが設定される点にも注目してください。

employeesテーブルを作成する
1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE employees (
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(30) NOT NULL,
  birthday date DEFAULT NULL,
  department_id int(11) NOT NULL,
  PRIMARY KEY(id),
FOREIGN KEY(department_id)
REFERENCES departments(id)
ON DELETE RESTRICT
ON UPDATE RESTRICT
);
上記のSQL文を実行して、employeesテーブルを作成しましょう

employeesテーブル作成

「実行」ボタンをクリックすると、SQL文が実行されます。テーブルが作成されると、以下のように表示されます。

SQL文実行結果

SHOW TABLES構文を使用して、テーブル一覧を表示してみましょう

以下のSQL文も実行して、employeesテーブルがあるかを確かめてみましょう。

テーブル一覧を表示する
1
SHOW TABLES;

上記のSQL文を実行すると、以下のようにemployee_management_developmentのデータベース上に先ほど作成したemployeesテーブルが表示されます。

employeesテーブルを表示

外部キー制約の場合はSHOW COLUMNS構文で確かめることができません。

次に学習するSHOW CREATE TABLE構文を使用することで、テーブル内容を網羅的に確認することができます。

CREATE TABLE構文を表示する

SHOW CREATE TABLE構文を使用すると、テーブルを作成したCREATE TABLE構文を表示することができます。

デフォルトで設定される内容も確認できます。

CREATE TABLE構文を表示する
1
SHOW CREATE TABLE テーブル名;
SHOW CREATE TABLE構文を使用して、CREATE TABLE構文を表示させましょう

以下のSQL文を実行してemployeesテーブルを作成したCREATE TABLE構文を表示してみましょう。

employeesテーブルのCREATE TABLE構文を表示する
1
SHOW CREATE TABLE employees;

「実行」ボタンをクリックすると、SQLが実行されます。実行結果のCreate Tableには、省略されたテキストしか表示していません。

部分テキスト表示

以下のように「オプション」を開き、「部分テキスト」から「全文テキスト」に変更して「実行」ボタンをクリックしましょう。

全文表示方法

「実行」ボタンをクリックすると、以下のように全文で表示されます。

外部キー制約がdepartment_idに設定されていることが確認できますね。その他にもテーブル作成時には指定しなかったデフォルトの内容も確認することができます。

全文表示

テーブルを削除する

SQLでテーブルを削除するには、DROP TABLE構文を使用します。

テーブルを削除する
1
DROP TABLE テーブル名;
DROP TABLE構文を使用して、テーブルを削除しましょう

以下のSQL文を実行して、「users」という名前のテーブルを削除してみましょう。

usersテーブルを削除する
1
DROP TABLE users;

テーブル削除

「実行ボタン」をクリックすると、以下のように確認ダイアログが表示されるので「ok」をクリックしてください。

ダイアログ表示

SHOW TABLES構文を使用して、テーブル一覧を表示してみましょう

以下のSHOW TABLES構文でテーブルを一覧表示して、「users」という名前のデータベースが削除できていることを確認しましょう。

SQL | テーブルを一覧表示する
1
SHOW TABLES;

テーブル一覧表示

お疲れ様でした!以上でデータ定義言語は終わりです。次はデータ操作言語を学びます。

ぴかわかさん

データ操作言語

データ操作言語とは、DML(Data Manipulation Language)とも呼ばれており、データ定義言語で定義されたデータ構造の中の各データを操作する言語のことです。主にデータの挿入、抽出、更新、削除などの命令ができます。

phpMyAdminのデータ操作でも少し触れましたが、データ操作では「CRUD」に対応する操作をおさえることが重要です。

CRUDに対応する操作をおさえよう

CRUDとは、Create(作成)・Read(読み込み)・Update(更新)・Delete(削除)の頭文字を繋げた用語で、データ操作の基本となる4つの処理のことでしたね。

SQLには、CRUDに対応するデータ操作言語のキーワードとしてINSERTUPDATEDELETESELECTが用意されています。

CRUD SQL文 SQL文の説明
Create(作成) INSERT文 テーブルにレコードを挿入する
Read(読み込み) SELECT文 テーブルのレコードを抽出する
Update(更新) UPDATE文 テーブルのレコードを更新する
Delete(削除) DELETE文 テーブルのレコードを削除する

前回はphpMyAdminを利用してCRUDのデータ操作を行いましたが、今回はSQLで命令を記述してCRUDに対応する操作をマスターしていきます。

それでは、実際にSQL文でデータを操作してみましょう!

ぴかわかさん

データを挿入(INSERT)

SQL文で既存のテーブルに新しいレコードを挿入するには、INSERT構文を使用します。

既存のテーブルに新しい単一のレコードを挿入する
1
INSERT INTO テーブル名(カラム名) VALUES()

例えば、nameカラムの値が'田中太郎'のレコードをusersテーブルに挿入したい場合で考えてみましょう。(前提:idにAUTO_INCREMENTを設定)

usersテーブルに新しいレコードを挿入する

上記の内容でusersテーブルにレコードを挿入するSQL文は、以下の通りです。

例 | usersテーブルに新しいレコードを挿入する
1
INSERT INTO users(name) VALUES('田中太郎');

主キーであるidには、自動的に一意の値を付与してくれるAUTO_INCREMENTが設定されるので、特にidについて指定する必要はありません。

レコードのカラムが複数ある場合

レコードのカラムが複数ある場合は、カラム名や値をカンマで区切り指定します。

既存のテーブルに単一の新しいレコードを挿入する
1
INSERT INTO テーブル名(カラム名1, カラム名2) VALUES(1, 2);

例えば、usersテーブルにレコードを挿入する際に、nameだけではなくageにも値を指定する場合で考えてみましょう。

複数のカラムがある場合

上記の内容でusersテーブルにレコードを挿入するSQL文は、以下の通りです。

VALUESの括弧内に指定する値は、テーブル名(users)の括弧内に指定されるカラムの並び順の通りにします。

usersテーブルに新しいレコードを挿入する

usersテーブルに新しいレコードを挿入する2

複数のレコードを挿入する場合

単一のレコードを挿入するだけではなく、複数のレコードを挿入する場合の指定方法も確認しましょう。

usersテーブルに2件のレコードを挿入する場合で確かめてみます。

複数のレコードを挿入する場合

これまでに学習したINSERT構文を使用すると、以下のように指定することができます。

例 | 既存のテーブルに単一の新しいレコードを挿入する
1
2
INSERT INTO users(name, age) VALUES('田中太郎', 18);
INSERT INTO users(name, age) VALUES('山田花子', 30);

上記のSQL文でもレコードを挿入することができますが、以下のようにVALUESの値をカンマ(,)で区切り、まとめて指定することで一括挿入することができます。

例 | 既存のテーブルに複数のレコードを一括挿入する
1
INSERT INTO users(name, age) VALUES('田中太郎', 18), ('山田花子', 30);

さらに以下のようにVALUESの後に改行を入れて、半角スペース2つ分のインデントで整えることで読みやすくなります。

例 | 既存のテーブルに複数のレコードを一括挿入する
1
2
3
INSERT INTO users(name, age) VALUES
  ('田中太郎', 18), -- idが1のレコードとして挿入される
  ('山田花子', 30);  -- idが2のレコードとして挿入される

それでは実際にemployees とdepartmentsのテーブルにデータを挿入してみましょう!

ぴかわかさん

departmentsテーブルにレコードを挿入してみよう

まずは、departmentsテーブルにレコードを一括挿入してみます。

作成するテーブル

以下のデータを一括挿入します。テーブル作成時にidAUTO_INCREMENTを設定しているので、idに関しての指定は必要ありません。

id name
1 開発
2 営業
SELECT構文を使用して、departmentsテーブルのデータがないことを確認しましょう

以下のSQL文を実行して、データが何もないことを確かめてみましょう。

departmentsテーブルのデータを抽出
1
SELECT * FROM departments;

上記を実行すると、以下のようにdepartmentsテーブルのデータが何もない状態です。

departmentsテーブルのデータを抽出

あとで詳しく学習しますが、SELECT構文を使用することで、テーブルからデータを抽出することができます。

INSERT構文を使用して、既存のテーブルに新しいレコードを挿入しましょう

以下のSQL文を実行して、departmentsテーブルにレコードを一括挿入しましょう。

departmentsテーブルにレコードを一括挿入する
1
2
3
INSERT INTO departments(name) VALUES
  ('開発'),
  ('営業');

departmentsテーブルにレコードを一括挿入する

実行ボタンをクリックすると、SQL文が実行されます。

SELECT構文を使用して、departmentsテーブルのデータを確認しましょう

以下のSQL文を実行して、departmentsテーブルにレコードを一括挿入できたかを確かめてみましょう。

departmentsテーブルのデータを抽出
1
SELECT * FROM departments;

INSERT構文を使用したことで、以下のようにdepartmentsテーブルにレコードが一括挿入されます。そしてidを指定しなくても、値が自動挿入される点にも注目してください。

departmentsテーブルのデータを抽出

employeesテーブルにレコードを挿入してみよう

次は、employeesテーブルにレコードを一括挿入してみます。

employeesテーブル

以下のデータを一括挿入します。テーブル作成時にidAUTO_INCREMENTを設定しているので、idに関しての指定は必要ありません。

id name birthday department_id
1 田中太郎 1980-10-22 1
2 山田花子 1983-08-20 1
3 高橋一朗 1986-06-16 2
4 伊藤晴子 1987-10-01 1
5 鈴木二郎 1990-01-17 2
6 山口冬子 1993-05-12 2
SELECT構文を使用して、employeesテーブルのデータがないことを確認しましょう

employeesテーブルにレコードを挿入する前に、以下のSQL文を実行して、データが何もないことを確かめてみましょう。

employeesテーブルのデータを抽出する
1
SELECT * FROM employees;

employeesテーブルのデータが挿入されていない状態

INSERT構文を使用して、既存のテーブルに新しいレコードを挿入しましょう

以下のSQL文を実行して、employeesテーブルに6件レコードを一括挿入しましょう。

SQL | employeesテーブルに6件のレコードを一括挿入する
1
2
3
4
5
6
7
INSERT INTO employees(name, birthday, department_id) VALUES
  ('田中太郎', '1980-10-22', 1),
  ('山田花子', '1983-08-20', 1),
  ('高橋一朗', '1986-06-16', 2),
  ('伊藤晴子', '1987-10-01', 1),
  ('鈴木二郎', '1990-01-17', 2),
  ('山口冬子', '1993-05-12', 2);

departmentsテーブルに6件のレコードを一括挿入する

実行ボタンをクリックすると、SQL文が実行されます。

employeesテーブルにデータを6件挿入

SELECT構文を使用して、employeesテーブルのデータを確認しましょう

以下のSQL文を実行して、employeesテーブルにレコードを一括挿入できたかを確かめてみましょう。

employeesテーブルのデータを抽出する
1
SELECT * FROM employees;

INSERT構文を使用したことで、以下のようにemployeesテーブルにレコードが一括挿入されます。そしてidを指定しなくても、値が自動挿入される点にも注目してください。

 employeesテーブルのデータを抽出する

次は、既存のレコードのカラムを新しい値に更新してみましょう!

ぴかわかさん

データを更新(UPDATE)

SQL文でテーブル内の既存のレコードのカラムを新しい値に更新するには、UPDATE構文を使用します。SET句には変更するカラム名と新しい値、WHERE句にはどのレコードを更新するかを識別する条件を指定します。

WHERE句がない場合は、すべてのレコードが更新されます。

テーブル内の既存のレコードのカラムを新しい値に更新する
1
2
3
UPDATE テーブル名
  SET カラム名 = '新しい値'
  WHERE 更新するレコードを特定する条件;
employeesテーブルのデータを更新するSQL文を確認しましょう

employeesテーブルにある山口冬子さんの生年月日を1993-05-12から1993-12-12に更新します。

更新するレコードのデータを特定する

更新するレコードのテーブル名はemployees、カラム名はbirthdayで新しい値が'1993-12-12'なので、以下のように指定することができます。

条件には、レコードを一意に識別するカラムである主キーid6と指定します。

テーブル内の既存のレコードのカラムを新しい値に更新する
1
2
3
UPDATE テーブル名
  SET カラム名 = '新しい値'
  WHERE 更新するレコードを特定する条件;
employeesテーブル内のidが6のレコードのbirthdayカラムの値を更新する
1
2
3
UPDATE employees
  SET birthday = '1993-12-12'
WHERE id = 6;
上記のSQL文を実行して、レコードのカラムを新しい値に更新しましょう

上記のSQL文を実行して、employeesテーブル内にあるid6のレコードのbirthdayを新しい値に更新してみましょう。

実行をクリックすると、以下のように表示されます。

birthdayの値を更新1

SELECT構文を使用して、employeesテーブルのデータが更新されたか確認しましょう

以下のSQL文を実行して、テーブル内の既存のレコードのカラムを新しい値に更新できたかを確かめてみましょう。

employeesテーブルのデータを抽出する
1
SELECT * FROM employees;

employeesテーブルにある山口冬子さんの生年月日(birthday)を1993-05-12から1993-12-12に更新することができましたね。

データ更新後の確認

親テーブルのデータを更新する

親テーブル内の既存のレコードのカラムを新しい値に更新しようとすると、どのような挙動になるかを確かめてみましょう。

関連付けられたテーブルは、親子関係が成り立っています。

外部キー制約の設定

employeesテーブル作成時に外部キー制約をdepartment_idに設定しましたが、その際に整合性を保つために、参照先の親テーブルに対する更新操作の指定も行いましたね。

以下のようにRESTRICTで、更新操作の拒否を設定しています。

更新操作の拒否を設定

この設定により、親テーブル内の既存のレコードのidを新しい値に更新することができません。他のカラムの値は更新することができます。

例えば、親テーブルであるdepartmentテーブルのnameの値は更新することができますが、主キーであるidの値は更新することができません。

departmentsテーブル

親テーブルのid以外は新しい値に更新できることを確かめてみましょう

まずは以下のSQL文を実行して、departmentsテーブル内にあるid2のレコードのnameの値を営業から法人営業に更新してみましょう。

SQL | departmentsテーブルのデータを更新する
1
2
3
UPDATE departments
  SET name = '法人営業'
WHERE id = 2;

続いて、以下のSQL文を実行して、テーブル内の既存のレコードのカラムを新しい値に更新できたかを確かめてみましょう。

departmentsテーブルのレコードを抽出する
1
SELECT * FROM departments;

departmentsテーブルのレコードを抽出する

ぴっかちゃん

親テーブルだけど主キーの値じゃないから変更できているね!

親テーブルのidを新しい値に更新できないことを確かめてみましょう

次に親テーブル内の既存のレコードのidを新しい値に更新してみましょう。

以下のSQL文を実行して、departmentsテーブル内にあるid2のレコードのidの値を3に更新してみましょう。

SQL | departmentsテーブルのデータを更新する
1
2
3
UPDATE departments
  SET id = 3
WHERE id = 2;

「実行」ボタンをクリックして更新しようとすると、以下のようにエラーが起こります。

エラー

RESTRICTの設定により、department_idが参照するdepartmentテーブルの主キーであるidの値は、更新することができないので注意しましょう。

データを削除(DELETE)

SQL文でテーブル内の既存のレコードを削除するには、DELETE構文を使用します。DELETE構文を実行すると、削除されたレコードの行数が返ります。

WHERE句にはどのレコードを削除するかを識別する条件を指定します。

テーブルのデータを削除する
1
DELETE FROM テーブル名 WHERE 削除するレコードを特定する条件;
DELETE構文を使用して、テーブルのデータを削除しましょう

以下のSQL文を実行して、employeesテーブル内のidが6のレコードを削除してみましょう。

employeesテーブルのデータを削除する
1
DELETE FROM employees WHERE id = 6;

「実行」ボタンをクリックして削除しようとすると、以下のように確認ダイアログが表示されるので「ok」をクリックします。

SQL文が実行されて「1 行変更しました」が表示されます。

employeesテーブルのデータを削除する

SELECT構文を使用して、employeesテーブルのデータを確認しましょう

以下のSQL文を実行して、employeesテーブル内のidが6のレコードを削除できていることを確かめてみましょう。

データ削除後の確認
1
SELECT * FROM employees;

データ削除後の確認

親テーブルのデータを削除する

親テーブル内の既存のレコードを削除しようとすると、どのような挙動になるかを確かめてみましょう。

関連付けられたテーブルは、親子関係が成り立っていますね。

外部キー制約の設定

employeesテーブル作成時に外部キー制約をdepartment_idに設定しましたが、その際に整合性を保つために、参照先の親テーブルに対する削除操作の指定も行いましたね。

以下のようにRESTRICTで、削除操作の拒否を設定しています。

削除操作の拒否を設定

この設定により、親テーブル内の既存のレコードを削除することができません。

親テーブル内の既存のレコードを削除できないことを確認しましょう

以下のSQL文を実行して、departmentテーブル内にあるid1のレコードを削除してみましょう。

departmentsテーブルのidが1のレコードを削除する
1
DELETE FROM departments WHERE id = 1;

「実行」ボタンをクリックして削除しようとすると、確認ダイアログが表示されるので「ok」をクリックします。

「ok」をクリックして削除しようとすると、以下のようにエラーが起こります。

レコードを削除する

RESTRICTの設定により、department_idが参照するdepartmentテーブル内の既存レコードは、削除することができないので注意しましょう。

最後にデータベースに溜め込んだデータをSQL文で抽出する方法を学びましょう!

ぴかわかさん

データを抽出(SELECT)

SQL文でテーブルからレコードを取得(抽出)するには、SELECT構文を使用します。

SELECTの後に取得するカラム名を指定します。WHERE句で取得するレコードの条件を指定することができます。WHERE句がない場合は全てのレコードを取得します。

SELECT文の基本書式
1
SELECT カラム名 FROM テーブル名 WHERE 条件;

SELECT文の基本書式

カラム名にアスタリスク(*)を指定すると、全てのカラムを選択することができます。

全てのカラムを選択してデータを抽出する
1
SELECT * FROM テーブル名 WHERE 条件;

それでは実際に手を動かして、データを抽出してみましょう!

ぴかわかさん

全てのカラムのデータを抽出する

以下のように指定することで、テーブルから全てのデータを抽出することができます。

SELECTの後に指定するカラム名にアスタリスク(*)を指定すると、全てのカラムを選択することができます。WHERE句を指定してないので、全てのレコードを取得します。

テーブルから全てのデータを抽出する
1
SELECT * FROM テーブル名;
SELECT構文を使用して、全てのカラムのデータを抽出しましょう

以下のSQL文を実行して、employeesテーブルから全てのカラムの全てのレコードを抽出してみましょう。

employeesテーブルのすべてのカラムの全てのレコードを抽出する
1
SELECT * FROM employees;

employeesテーブルのすべてのカラムのデータを抽出する

特定のカラムのデータを抽出する

SELECTの後にカラム名を指定することで、特定のカラムのデータを抽出することができます。WHERE句を指定してないので、特定のカラムの全てのレコードを取得します。

特定のカラムのデータを抽出する
1
SELECT カラム名 FROM テーブル名;
SELECT構文を使用して、特定のカラムのデータを抽出しましょう

以下のSQL文を実行して、employeesテーブル内のnameカラムの全てのレコードを抽出してみましょう。

employeesテーブルからnameカラムのデータを抽出する
1
SELECT name FROM employees;

実行ボタンをクリックすると、nameカラムの全てのレコードが抽出されており、他のカラムはありませんね。

employeesテーブルからnameカラムのデータを抽出する

複数のカラムを取得する場合は、次のようにSELECTの後に指定するカラム名をカンマ(,)で区切ります。

複数のカラムのデータを抽出する
1
SELECT カラム名1, カラム名2 FROM テーブル名;
SELECT構文を使用して、複数のカラムのデータを抽出しましょう

以下のSQL文を実行して、employeesテーブルからnamebirthdayのデータを全て抽出してみましょう。

employeesテーブルからnameカラム、birthdayカラムのデータを抽出する
1
SELECT name, birthday FROM employees;

複数のカラムのデータを抽出する

特定のレコードを抽出する

SELECT構文で特定のレコードを抽出するには、WHERE句を使用します。
WHERE句には、抽出するレコードの条件を指定します。

特定のレコードを抽出する
1
SELECT * FROM テーブル名 WHERE 条件;
SELECT構文を使用して、特定のレコードを抽出しましょう

以下のSQL文を実行して、employeesテーブルからidが4のレコードを抽出しましょう。

employeesテーブルからidが4のレコードを抽出する
1
SELECT * FROM employees WHERE id = 4;

employeesテーブルからidが4のレコードを抽出する

抽出するデータを整列させる

SQL文で抽出するデータの整列は、SELECT構文でORDER BY句を使用します。

ORDER BY句内のカラム名に順序を指定します。DESCを指定すると、データは降順になります。デフォルトはASC(昇順)ですが、明示的に指定することもできます。

抽出するデータを整列させる
1
2
SELECT カラム名 
  FROM テーブル名 ORDER BY 整列させるカラム名 ASC(またはDESC);
SELECT構文でORDER BY句を使用して、抽出するデータを整列させましょう

以下のSQL文を実行して、idが降順になるようにデータを抽出しましょう。

抽出するデータを整列させる
1
2
SELECT *
  FROM employees ORDER BY id DESC;

抽出するデータを整列させる

関連するテーブル同士の結合

SELECT構文で関連するテーブル同士を結合して、データを抽出することができます。

FROM句に結合させるテーブル名を羅列して、WHERE句でどのカラムを結合させるかを指定します。テーブル名.カラム名は、どのテーブルに属するカラムかを示します。

テーブル同士を結合する基本書式
1
2
3
SELECT * 
  FROM テーブル名1, テーブル名2
  WHERE テーブル名1.結合させるカラム名 = テーブル名2.結合させるカラム名;

employeesテーブルとdepartmentsテーブルを結合して、データを抽出してみましょう。

employeesテーブル作成時にdepartment_idに外部キー制約を設定したことで、2つのテーブルは関連づけられています。

外部キー制約の設定

employeesテーブルのdepartment_idとdepartmentsテーブルのidを結合させて、データを抽出します。

以下のSQL文を実行してください
テーブル同士を結合する基本書式
1
2
3
SELECT *
  FROM employees, departments
  WHERE employees.department_id = departments.id;

実行ボタンをクリックすると、SQLが実行されます。実行結果は、以下のように2つのテーブルが結合して、データが抽出されます。

テーブル同士を結合する基本書式

今回実行したSELECT構文では、WHERE句で結合するカラム同士の値が一致するレコードだけを抽出できるように指定しています。

このような結合方法を「内部結合」と呼びます。

WHERE句でテーブル同士を結合する
1
2
3
SELECT *
  FROM employees, departments
WHERE employees.department_id = departments.id;

内部結合はWHERE句だけではなく、以下のようにINNER JOIN句とON句を使用して書き換えることができます。

INNER JOIN句でテーブル同士を結合する
1
2
3
SELECT *
  FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

NNER JOIN句でテーブル同士を結合する

この章のまとめ

この章では、SQLについて詳しく学習しました。テーブルの作成、カラム情報の表示、主キー制約、外部キー制約、テーブルの削除などを実践し、CRUD操作(Create, Read, Update, Delete)において、データの挿入、更新、削除、抽出などの操作について深く理解しました。これらの操作はデータベースの管理や情報の取得に欠かせないスキルであり、アプリケーションの開発において非常に重要です。

この章で得た知識は、次のステップでのRailsのモデル(ActiveRecord)の学習に備えるための強固な基盤となります。また、この章を終える前に、以下の手順に従って今まで作成したテーブルを削除することを忘れないでください。これにより、次の章に進む前に環境を整えることができます。

次の章に進む前に、SQLを実行してテーブルを削除しておきましょう

以下のSQL文を実行して、employee_management_developmentに作成したテーブルを全て削除してください。

employeesテーブルとdepartmentsテーブルを削除する
1
DROP TABLE employees, departments;

上のSQLを実行すると、以下の画面が表示されてテーブルが削除されます。

employeesテーブルとdepartmentsテーブルを削除する

この章のまとめ

  • SQLは、RDBMSに指示を伝えるためのデータベース言語
  • データ定義言語は、データベースの構造を定義する言語
  • データ操作言語は、データ構造の中の各データを操作する言語