はじめに
概要
この章では、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へのログイン方法
- phpMyAdminの基本操作
- ⚠️ phpMyAdminの章で作成したテーブルの削除が完了していること!!
- SQL文を実行する画面の確認(次で説明します。)
phpMyAdminでは、データベースを直接編集・管理する機能に加え、SQL文を実行して結果を表示する機能も備わっています。本章では、このSQL文実行機能を活用していきます。
以下の手順に従って、SQL文を実行する画面を確認しましょう。
SQL文を実行する画面を確認しよう
phpMyAdminにログインした後、SQL文を実行するための画面を確認しましょう。
まずは、phpMyAdminのメインページ上部にある「SQL」タブをクリックします。
phpMyAdminのメインページ
クリックすると、以下のようにSQL文を記述するためのエリアが表示されます。ここにSQL文を入力し、「実行」ボタンを押すことで、SQLが実行され、結果がテーブル形式で表示されます。
SQL文を記述するエリア
データベースを選択するまでは、このエリアでSQL文を実行します。データベース選択後は、そのデータベースのSQL文を記述するエリアを使用します。
それでは、SQL文の学習を始めましょう!
SQLとは
SQLとは、RDBMSに指示を伝えるためのデータベース言語のことです。
ユーザーやアプリケーションなどの「データベースを利用する側」のSQL文(SQLで記述された命令文)の指示を受けて、RDBMSがリレーショナルデータベースを操作します。
SQLには様々な命令が存在しますが、データベースやテーブルを作成できる「データ定義言語」とデータの操作ができる「データ操作言語」におおまかに分類することができます。
基本書式
SQLの書き方は後ほど手を動かしながら学習しますが、条件を付加してデータを抽出するSELECT文の基本書式は、以下の通りです。
1
SELECT カラム名 FROM テーブル名 WHERE 条件;
あらかじめ用意されているSELECT
やFROM
、WHERE
などのキーワード(SQLで命令のための語句)を利用し、テーブル名やカラム名、条件の値を指定することで、SQL文を完成させることができます。
文の最後には、セミコロン(;
)を付けます。
値はシングルクォート('
)で囲みます。
1
SELECT * FROM employees WHERE birthday > '1983-01-01';
あれ?phpMyAdminで自動生成されたSQL文には、シングルクォートだけじゃなくてバッククォートで囲まれる箇所があったような・・・。
良いところに気がついたね。2つの記号は、次の注意点で説明するよ!
SQL文を記述する際の注意点
phpMyAdminでデータを更新した際に自動生成されたSQL文には、条件の値を囲むシングルクォート('
)以外にも、バッククォート(`
)で囲まれる箇所がありましたね。
以下のように先ほどのSQL文とphpMyAdminで自動生成されるSQL文を比べてみると、条件の値をシングルクォート('
)で囲むという点は変わりません。
しかし、自動生成されるSQL文のテーブル名とカラム名は、バッククォート(`
)で囲まれています。
1
SELECT * FROM employees WHERE birthday > '1983-01-01';
1
SELECT * FROM `employees` WHERE `birthday` > '1986-01-01';
バッククォートは無くても問題ありませんが、「データベース名」「テーブル名」「カラム名」をバッククォートで囲うことによって、予約語をエスケープすることができます。
予約語とは
予約語とは「用途が決められた文字列」のことです。(詳細:MariaDBの予約語)
SELECT
やFROM
などのキーワードは予約されており、「データベース名」「テーブル名」「カラム名」として使用することができません。
例えばDATABASE
は予約語なので、データベース名に使用することができません。
1
CREATE DATABASE データベース名;
1
CREATE DATABASE DATABASE;
しかし、予約語であるDATABASE
をバッククォート(`
)で囲むことで、データベース名として使用可能になります。
1
CREATE DATABASE `DATABASE`;
使用可能にはなりますが、予約済のキーワードは用途が決められた文字列なので、上記のようなデータベース名は適切ではありません。今回は説明用として例を挙げています。
本章では、SQLで「データベース名」「テーブル名」「カラム名」を記述する際にバッククォートを使用しないので注意しましょう。
シングルクォート('
)とバッククォート(`
)は、よく間違えられやすい符号なので、「条件の値を書くときはシングルクォートで囲む」ということも覚えておきましょう。
SQL文のルールを確認しよう
SQLでは、一般的なスタイルガイド(ルールを集めたもの)が定まっていません。
例えば、SQLで命令文を記述する際に「大文字で書く」「小文字で書く」というルールは特にありません。
1
SELECT * FROM departments;
1
select * from departments;
しかし実際の開発現場では、「書き方で迷わない」「可読性を高める」「バグを発見しやすくする」といったメリットを享受するために、ルールを決めているところが大半です。
Pikawakaカリキュラムでも、いくつかのルールに従ってSQL文を記述して頂きます。
予約語は大文字で記述する
予約語とは「用途が決められた文字列」のことで、SQLではSELECT
やFROM
などが挙げられます。(詳細:MariaDBの予約語)
予約語は、大文字で記述しましょう。
1
SELECT * FROM departments;
SQL文を記述するエリアでは、入力補完(入力内容を予測して候補を表示する機能)があるので、大文字でも入力が楽になります。
インデントは半角スペース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コマンドによって、開発環境とテスト環境のデータベースを作成していましたね。
SQLでデータベース一覧を表示するには、SHOW DATABASES
構文を使用します。
1
SHOW DATABASES;
以下のようにSQL文を記述するエリアにSHOW DATABASES;
と記述し、下にある「実行」ボタンをクリックしてみましょう。
「実行」ボタンをクリックすると、SQL文が実行されます。実行結果は、以下のようにテーブル形式で表示されます。
一覧表示によって、以前railsコマンドで作成した「employee_management_development」と「employee_management_test」のデータベースも確認することができます。
このようにSHOW
文によって、データベース一覧を表示することができます。
以下のように「クエリボックスを表示」というボタンをクリックすると、再びSQL文を記述するエリアを表示することができます。
データベースを作成する
SQLで新しいデータベースを作成するには、CREATE DATABASE
構文を使用します。
1
CREATE DATABASE データベース名;
以下のSQL文を実行して、pikawakaという名前のデータベースを作成しましょう。
1
CREATE DATABASE pikawaka;
「実行」ボタンをクリックすると、SQL文が実行されます。データベースが作成されると、以下のように表示されます。
以下のSHOW DATABASES
構文でデータベースを一覧表示して、作成したデータベースがあるか確認しましょう。
1
SHOW DATABASES;
上記の実行結果によって、以下のように「pikawaka」という名前のデータベースを作成できたことが確認できます。
データベースを削除する
SQLでデータベースを削除するには、DROP DATABASE
構文を使用します。
1
DROP DATABASE データベース名;
以下のSQL文を実行して、先ほど作成した「pikawaka」という名前のデータベースを削除してみましょう。
1
DROP DATABASE pikawaka;
以下のSHOW DATABASES
構文でデータベースを一覧表示して、「pikawaka」という名前のデータベースが削除できていることを確認しましょう。
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 テーブル名;
以下のSQL文を実行してemployee_management_development
を選択しましょう。SELECT database();
で、現在使用しているデータベース名も表示させます。
1
2
USE employee_management_development;
SELECT database();
「実行」ボタンをクリックすると、SQL文が実行されます。指定したデータベースが選択されて、以下のように現在のデフォルトのデータベースとして表示されます。
セッションの終了
セッションが終了すると、デフォルトのデータベースではなくなるので注意しましょう。
phpMyAdminの場合は、以下のように新たにSQL文を実行するページを開くと、セッションが終了します。database関数を使用しても、現在選択しているデータベースはないので、NULL
が返ります。
phpMyAdminでは、データベース毎にSQL文を実行する画面があります。SQL文をemployee_management_development
上で確実に実行できるように画面を変更していきます。
まずは、メインページからデータベース一覧画面に移動して、以下のようにデータベース一覧からemployee_management_development
をクリックしましょう。
続いて、以下のようにemployee_management_development
内の画面上部にある「SQL」をクリックして、SQL文を実行する画面を開きましょう。
クリックすると、以下のようにSQL文を記述するエリアが表示されます。
現在の位置がlocalhost
ではなく、employee_management_development
を示していますね。これ以降は以下のページでSQL文を実行します。
上の画面を利用することで、使用するデータベースを選択している場合と同様に、テーブル名の指定をデータベース名.テーブル名
という形式ではなく、データベース名を省略してSQL文を記述することができます。
テーブル一覧を表示する
SQLでテーブル一覧を表示するには、SHOW TABLES
構文を使用します。
1
SHOW TABLES;
現在の位置がlocalhostではなく、employee_management_developmentであることを確認して、SHOW TABLES;
を実行してみましょう。
employee_management_development
のデータベース上に作成しているテーブルはないので、以下のようにSQL文を実行しても何も表示されません。
次は
テーブルを作成する
SQLでテーブルを作成するには、CREATE TABLE
構文を使用します。
テーブルを作成する際に、カラムのデータ型やオプションを指定することができます。カラムの名前、データ型、オプションは半角スペースで区切ります。カラムが複数ある場合は、カンマ(,
)で区切ります。
1
2
3
4
5
CREATE TABLE テーブル名 (
カラム名1 [データ型] [その他オプション],
カラム名2 [データ型] [その他オプション],
カラム名3 [データ型] [その他オプション],
);
usersテーブルには、name
という名前のカラムだけ作成します。カラムのデータ型はVARCHAR型、制約は非NULL制約(NOT NULL制約)を設定します。
以下のSQL文を実行してください。
1
2
3
CREATE TABLE users (
name varchar(30) NOT NULL
);
「実行」ボタンをクリックすると、SQL文が実行されます。
以下のSQL文も実行して、users
テーブルが作成されたことを確かめてみましょう。
1
SHOW TABLES;
上記のSQL文を実行すると、以下のようにemployee_management_development
のデータベース上に先ほど作成したusers
テーブルが表示されます。
カラムに関する情報を表示する
テーブル内のカラムに関する情報を表示するには、SHOW COLUMNS
構文を使用します。
1
SHOW COLUMNS FROM テーブル名;
以下のSQL文を実行して、先ほど作成した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
テーブルに必要なカラムはid
とname
です。
主キーであるid
のカラムには、主キー制約と非NULL制約を合わせて設定します。さらに自動的に一意の値を付与してくれるAUTO_INCREMENT
という機能も使用します。
その他のデータ型と制約は、以下の通りです。
カラム名 | データ型 | 制約 | その他 |
---|---|---|---|
id | INT型 | 主キー制約(PRIMARY KEY) 非NULL制約(NOT NULL制約) |
AUTO_INCREMENT |
name | VARCHAR型 | 非NULL制約(NOT NULL制約) |
上記の内容でdepartmentsテーブルを作成するSQL文は、以下の通りです。
PRIMARY KEY
でid
のカラムを指定していますね。そして、idにはNOT NULL
が指定される点にも注目してください。
1
2
3
4
5
CREATE TABLE departments (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(30) NOT NULL,
PRIMARY KEY(id)
);
「実行」ボタンをクリックすると、SQL文が実行されます。テーブルが作成されると、以下のように表示されます。
以下のSQL文も実行して、departments
テーブルがあるかを確かめてみましょう。
1
SHOW TABLES;
上記のSQL文を実行すると、以下のようにemployee_management_development
のデータベース上に先ほど作成したdepartments
テーブルが表示されます。
以下のSQL文を実行して、departmentsテーブル内のカラムに設定したデータ型や制約も確かめてみましょう。
1
SHOW COLUMNS FROM departments;
「実行」ボタンをクリックすると、SQL文が実行されます。実行結果は、以下のようにテーブル形式で表示されます。
主キー制約がid
カラムに設定されていることを確かめることができますね。
phpMyAdminでも確認すると、以下のようにidのカラムには「主キー制約」が設定されており、鍵のアイコンが表示されます。
次はemployeesテーブルを作成して、外部キー制約の設定を学びます。
外部キー制約を設定する
テーブル作成時に外部キー制約を設定することができます。子テーブル側で設定します。
SQLでは、FOREIGN KEY
とREFERENCES
を使用します。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 DELETE
とON UPDATE
でRESTRICT
を指定することで、親テーブルに対する削除・更新操作を拒否することができます。
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テーブルの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 KEY
でdepartment_id
を指定していますね。そして、REFERENCES
で参照先の親テーブル内にある主キーが設定される点にも注目してください。
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文が実行されます。テーブルが作成されると、以下のように表示されます。
以下のSQL文も実行して、employees
テーブルがあるかを確かめてみましょう。
1
SHOW TABLES;
上記のSQL文を実行すると、以下のようにemployee_management_development
のデータベース上に先ほど作成したemployees
テーブルが表示されます。
外部キー制約の場合はSHOW COLUMNS
構文で確かめることができません。
次に学習するSHOW CREATE TABLE
構文を使用することで、テーブル内容を網羅的に確認することができます。
CREATE TABLE構文を表示する
SHOW CREATE TABLE
構文を使用すると、テーブルを作成したCREATE TABLE
構文を表示することができます。
デフォルトで設定される内容も確認できます。
1
SHOW CREATE TABLE テーブル名;
以下のSQL文を実行してemployees
テーブルを作成したCREATE TABLE
構文を表示してみましょう。
1
SHOW CREATE TABLE employees;
「実行」ボタンをクリックすると、SQLが実行されます。実行結果のCreate Tableには、省略されたテキストしか表示していません。
以下のように「オプション」を開き、「部分テキスト」から「全文テキスト」に変更して「実行」ボタンをクリックしましょう。
「実行」ボタンをクリックすると、以下のように全文で表示されます。
外部キー制約がdepartment_id
に設定されていることが確認できますね。その他にもテーブル作成時には指定しなかったデフォルトの内容も確認することができます。
テーブルを削除する
SQLでテーブルを削除するには、DROP TABLE
構文を使用します。
1
DROP TABLE テーブル名;
以下のSQL文を実行して、「users」という名前のテーブルを削除してみましょう。
1
DROP TABLE users;
「実行ボタン」をクリックすると、以下のように確認ダイアログが表示されるので「ok」をクリックしてください。
以下のSHOW TABLES
構文でテーブルを一覧表示して、「users」という名前のデータベースが削除できていることを確認しましょう。
1
SHOW TABLES;
お疲れ様でした!以上でデータ定義言語は終わりです。次はデータ操作言語を学びます。
データ操作言語
データ操作言語とは、DML(Data Manipulation Language)とも呼ばれており、データ定義言語で定義されたデータ構造の中の各データを操作する言語のことです。主にデータの挿入、抽出、更新、削除などの命令ができます。
phpMyAdminのデータ操作でも少し触れましたが、データ操作では「CRUD」に対応する操作をおさえることが重要です。
CRUDに対応する操作をおさえよう
CRUDとは、Create(作成)・Read(読み込み)・Update(更新)・Delete(削除)の頭文字を繋げた用語で、データ操作の基本となる4つの処理のことでしたね。
SQLには、CRUDに対応するデータ操作言語のキーワードとしてINSERT
・UPDATE
・DELETE
・SELECT
が用意されています。
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テーブルにレコードを挿入するSQL文は、以下の通りです。
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テーブルに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テーブルにレコードを一括挿入してみます。
以下のデータを一括挿入します。テーブル作成時にid
にAUTO_INCREMENT
を設定しているので、id
に関しての指定は必要ありません。
id | name |
---|---|
1 |
開発 |
2 |
営業 |
以下のSQL文を実行して、データが何もないことを確かめてみましょう。
1
SELECT * FROM departments;
上記を実行すると、以下のようにdepartmentsテーブルのデータが何もない状態です。
あとで詳しく学習しますが、SELECT
構文を使用することで、テーブルからデータを抽出することができます。
以下のSQL文を実行して、departmentsテーブルにレコードを一括挿入しましょう。
1
2
3
INSERT INTO departments(name) VALUES
('開発'),
('営業');
実行ボタンをクリックすると、SQL文が実行されます。
以下のSQL文を実行して、departmentsテーブルにレコードを一括挿入できたかを確かめてみましょう。
1
SELECT * FROM departments;
INSERT
構文を使用したことで、以下のようにdepartmentsテーブルにレコードが一括挿入されます。そしてid
を指定しなくても、値が自動挿入される点にも注目してください。
employeesテーブルにレコードを挿入してみよう
次は、employeesテーブルにレコードを一括挿入してみます。
以下のデータを一括挿入します。テーブル作成時にid
にAUTO_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 |
employeesテーブルにレコードを挿入する前に、以下のSQL文を実行して、データが何もないことを確かめてみましょう。
1
SELECT * FROM employees;
以下の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);
実行ボタンをクリックすると、SQL文が実行されます。
以下のSQL文を実行して、employeesテーブルにレコードを一括挿入できたかを確かめてみましょう。
1
SELECT * FROM employees;
INSERT構文を使用したことで、以下のようにemployeesテーブルにレコードが一括挿入されます。そしてidを指定しなくても、値が自動挿入される点にも注目してください。
次は、既存のレコードのカラムを新しい値に更新してみましょう!
データを更新(UPDATE)
SQL文でテーブル内の既存のレコードのカラムを新しい値に更新するには、UPDATE
構文を使用します。SET
句には変更するカラム名と新しい値、WHERE
句にはどのレコードを更新するかを識別する条件を指定します。
WHERE
句がない場合は、すべてのレコードが更新されます。
1
2
3
UPDATE テーブル名
SET カラム名 = '新しい値'
WHERE 更新するレコードを特定する条件;
employees
テーブルにある山口冬子さんの生年月日を1993-05-12
から1993-12-12
に更新します。
更新するレコードのテーブル名はemployees
、カラム名はbirthday
で新しい値が'1993-12-12'
なので、以下のように指定することができます。
条件には、レコードを一意に識別するカラムである主キーのid
で6
と指定します。
1
2
3
UPDATE テーブル名
SET カラム名 = '新しい値'
WHERE 更新するレコードを特定する条件;
1
2
3
UPDATE employees
SET birthday = '1993-12-12'
WHERE id = 6;
上記のSQL文を実行して、employeesテーブル内にあるid
が6
のレコードのbirthday
を新しい値に更新してみましょう。
実行をクリックすると、以下のように表示されます。
以下のSQL文を実行して、テーブル内の既存のレコードのカラムを新しい値に更新できたかを確かめてみましょう。
1
SELECT * FROM employees;
employeesテーブルにある山口冬子さんの生年月日(birthday
)を1993-05-12
から1993-12-12
に更新することができましたね。
親テーブルのデータを更新する
親テーブル内の既存のレコードのカラムを新しい値に更新しようとすると、どのような挙動になるかを確かめてみましょう。
関連付けられたテーブルは、親子関係が成り立っています。
employeesテーブル作成時に外部キー制約をdepartment_id
に設定しましたが、その際に整合性を保つために、参照先の親テーブルに対する更新操作の指定も行いましたね。
以下のようにRESTRICT
で、更新操作の拒否を設定しています。
この設定により、親テーブル内の既存のレコードのid
を新しい値に更新することができません。他のカラムの値は更新することができます。
例えば、親テーブルであるdepartment
テーブルのname
の値は更新することができますが、主キーであるid
の値は更新することができません。
まずは以下のSQL文を実行して、departmentsテーブル内にあるid
が2
のレコードのname
の値を営業
から法人営業
に更新してみましょう。
1
2
3
UPDATE departments
SET name = '法人営業'
WHERE id = 2;
続いて、以下のSQL文を実行して、テーブル内の既存のレコードのカラムを新しい値に更新できたかを確かめてみましょう。
1
SELECT * FROM departments;
親テーブルだけど主キーの値じゃないから変更できているね!
次に親テーブル内の既存のレコードのid
を新しい値に更新してみましょう。
以下のSQL文を実行して、departmentsテーブル内にあるid
が2
のレコードのidの値を3
に更新してみましょう。
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 削除するレコードを特定する条件;
以下のSQL文を実行して、employeesテーブル内のidが6のレコードを削除してみましょう。
1
DELETE FROM employees WHERE id = 6;
「実行」ボタンをクリックして削除しようとすると、以下のように確認ダイアログが表示されるので「ok」をクリックします。
SQL文が実行されて「1 行変更しました」が表示されます。
以下のSQL文を実行して、employeesテーブル内のidが6のレコードを削除できていることを確かめてみましょう。
1
SELECT * FROM employees;
親テーブルのデータを削除する
親テーブル内の既存のレコードを削除しようとすると、どのような挙動になるかを確かめてみましょう。
関連付けられたテーブルは、親子関係が成り立っていますね。
employeesテーブル作成時に外部キー制約をdepartment_id
に設定しましたが、その際に整合性を保つために、参照先の親テーブルに対する削除操作の指定も行いましたね。
以下のようにRESTRICT
で、削除操作の拒否を設定しています。
この設定により、親テーブル内の既存のレコードを削除することができません。
以下のSQL文を実行して、departmentテーブル内にあるid
が1
のレコードを削除してみましょう。
1
DELETE FROM departments WHERE id = 1;
「実行」ボタンをクリックして削除しようとすると、確認ダイアログが表示されるので「ok」をクリックします。
「ok」をクリックして削除しようとすると、以下のようにエラーが起こります。
RESTRICT
の設定により、department_id
が参照するdepartment
テーブル内の既存レコードは、削除することができないので注意しましょう。
最後にデータベースに溜め込んだデータをSQL文で抽出する方法を学びましょう!
データを抽出(SELECT)
SQL文でテーブルからレコードを取得(抽出)するには、SELECT
構文を使用します。
SELECT
の後に取得するカラム名を指定します。WHERE
句で取得するレコードの条件を指定することができます。WHERE
句がない場合は全てのレコードを取得します。
1
SELECT カラム名 FROM テーブル名 WHERE 条件;
カラム名にアスタリスク(*
)を指定すると、全てのカラムを選択することができます。
1
SELECT * FROM テーブル名 WHERE 条件;
それでは実際に手を動かして、データを抽出してみましょう!
全てのカラムのデータを抽出する
以下のように指定することで、テーブルから全てのデータを抽出することができます。
SELECT
の後に指定するカラム名にアスタリスク(*
)を指定すると、全てのカラムを選択することができます。WHERE
句を指定してないので、全てのレコードを取得します。
1
SELECT * FROM テーブル名;
以下のSQL文を実行して、employeesテーブルから全てのカラムの全てのレコードを抽出してみましょう。
1
SELECT * FROM employees;
特定のカラムのデータを抽出する
SELECT
の後にカラム名を指定することで、特定のカラムのデータを抽出することができます。WHERE
句を指定してないので、特定のカラムの全てのレコードを取得します。
1
SELECT カラム名 FROM テーブル名;
以下のSQL文を実行して、employeesテーブル内のname
カラムの全てのレコードを抽出してみましょう。
1
SELECT name FROM employees;
実行ボタンをクリックすると、name
カラムの全てのレコードが抽出されており、他のカラムはありませんね。
複数のカラムを取得する場合は、次のようにSELECT
の後に指定するカラム名をカンマ(,
)で区切ります。
1
SELECT カラム名1, カラム名2 FROM テーブル名;
以下のSQL文を実行して、employeesテーブルからname
とbirthday
のデータを全て抽出してみましょう。
1
SELECT name, birthday FROM employees;
特定のレコードを抽出する
SELECT
構文で特定のレコードを抽出するには、WHERE
句を使用します。
WHERE
句には、抽出するレコードの条件を指定します。
1
SELECT * FROM テーブル名 WHERE 条件;
以下のSQL文を実行して、employeesテーブルからidが4のレコードを抽出しましょう。
1
SELECT * FROM employees WHERE id = 4;
抽出するデータを整列させる
SQL文で抽出するデータの整列は、SELECT
構文でORDER BY
句を使用します。
ORDER BY
句内のカラム名に順序を指定します。DESC
を指定すると、データは降順になります。デフォルトはASC
(昇順)ですが、明示的に指定することもできます。
1
2
SELECT カラム名
FROM テーブル名 ORDER BY 整列させるカラム名 ASC(またはDESC);
以下の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
を結合させて、データを抽出します。
1
2
3
SELECT *
FROM employees, departments
WHERE employees.department_id = departments.id;
実行ボタンをクリックすると、SQLが実行されます。実行結果は、以下のように2つのテーブルが結合して、データが抽出されます。
今回実行したSELECT
構文では、WHERE
句で結合するカラム同士の値が一致するレコードだけを抽出できるように指定しています。
このような結合方法を「内部結合」と呼びます。
1
2
3
SELECT *
FROM employees, departments
WHERE employees.department_id = departments.id;
内部結合はWHERE
句だけではなく、以下のようにINNER JOIN
句とON
句を使用して書き換えることができます。
1
2
3
SELECT *
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;
この章のまとめ
この章では、SQLについて詳しく学習しました。テーブルの作成、カラム情報の表示、主キー制約、外部キー制約、テーブルの削除などを実践し、CRUD操作(Create, Read, Update, Delete)において、データの挿入、更新、削除、抽出などの操作について深く理解しました。これらの操作はデータベースの管理や情報の取得に欠かせないスキルであり、アプリケーションの開発において非常に重要です。
この章で得た知識は、次のステップでのRailsのモデル(ActiveRecord)の学習に備えるための強固な基盤となります。また、この章を終える前に、以下の手順に従って今まで作成したテーブルを削除することを忘れないでください。これにより、次の章に進む前に環境を整えることができます。
以下のSQL文を実行して、employee_management_development
に作成したテーブルを全て削除してください。
1
DROP TABLE employees, departments;
上のSQLを実行すると、以下の画面が表示されてテーブルが削除されます。
この章のまとめ
- SQLは、RDBMSに指示を伝えるためのデータベース言語
- データ定義言語は、データベースの構造を定義する言語
- データ操作言語は、データ構造の中の各データを操作する言語
この記事で学んだことをTwitterに投稿して、アウトプットしよう!
Twitterの投稿画面に遷移します