初心者向け学習方法一覧はこちら
はじめに
この記事では、SQLの基本的な使い方について解説します。
実際にSQLを実行する方法についても記載していますが、ここに記載されているSQLは以下の記事で構築したMySQL環境での実行を想定しています。
SQLとは
SQL（エスキューエル、英語ではシークェルと発音されます）とは、RDBMSにおいて、データの操作や定義をするためのデータベース言語です。
SQLには標準SQL規格が存在していますが、その歴史的背景から、RDBMS（MySQL、Oracle、PostgreSQLなど）ベンダーごとに対応状況のバラつきがあり、それぞれのRDBMSで『方言』があります。
本記事で扱う内容は基本的にはどのRDBMSでも共通化されている内容となっています。ただし、一部においてはMySQL独自の表現・コマンドを扱いますので、その場合には「MySQLコマンド」と記載しています。
用語説明
SQLについて解説する前に、簡単に用語の解説をします。
- データベース: 複数のテーブルをひとまとめにしたもの
- テーブル: 表のこと
- レコード: テーブルにおける行のこと
- カラム: テーブルにおける列のこと
以下の画像のように、MySQLというサービスの中に複数のデータベースが存在し、その中に更に複数のテーブルが存在しているということに注意してください。
また、SQLは以下の3種類に分類されます。こちらもシステム開発においてはよく使われる言葉ですので、把握しておくと良いと思います。
- DDL（データ定義言語: Data Definition Language）: データベースやテーブルの作成・削除・構造の変更などを行う。
- DML（データ操作言語: Data Manipulation Language）: データベースに対してデータの検索・新規登録・削除・更新を行う。
- DCL（データ制御言語: Data Control Language）: データに対するアクセス制御を行う。
MySQLコマンドとSQLでデータベースを操作する
MySQLのコマンドやSQLを使って、一連の操作をします。操作内容は以下のとおりです。
ここで登場するMySQLのコマンド、SQLについては本記事の最後に表としてまとめていますのでそちらも参考にしてください。
なお、ここで解説するコマンド・SQLについてはごく一部です。より詳細を確認したい場合は公式ドキュメントを参照してください。
データベースを表示・作成・削除
まずはコンソール（Macの場合はターミナル、Windowsの場合はコマンドプロンプト）を用いてMySQLへアクセスします。
mysql --user=root --passwordデータベースの一覧を表示します。一覧表示のためのコマンドは以下の通りです。
SHOW DATABASES;なお、コマンドは大文字でも小文字でも構いません。ただし、Macの場合はデフォルトでデータベース名やテーブル名、カラム名などについては大文字・小文字が区別されますので注意してください。
また、コマンド・SQL文の最後にセミコロン（ ; ）が必要ですので忘れないようにしてください。
MySQLの環境を構築したばかりの場合、以下のような結果が返ってくるかと思います。これらはMySQLでデフォルトで用意されているデータベースです。
データベース追加
それでは、データベースを作成します。以下のMySQLコマンドを実行してください。なお、 -- で始まる行はコメント行であり、MySQLコマンド・SQLとして認識されません。
-- CREATE DATABASE データベース名;
CREATE DATABASE SAMPLE_DATABASE;一覧表示でデータベースが作成されたことを確認します。
SHOW DATABASES;
SAMPLE_DATABASE というデータベースが作成されていることが確認できます。
データベース削除
作成したデータベースを削除してみます。
-- DROP DATABASE データベース名;
DROP DATABASE SAMPLE_DATABASE;一覧表示でデータベースが削除されたことを確認します。
SHOW DATABASES;
SAMPLE_DATABASE というデータベースが削除されていることが確認できます。
テーブル・カラムの追加・変更・削除
次にテーブルを作成してみます。まずはテーブル作成をする対象のデータベースを改めて作成します。今回は小文字のデータベースを作成します。
CREATE DATABASE music;つづいて、このあとにSQLを実行していくデータベースを選択します。データベースの選択には USE というMySQLコマンドを利用します。
-- USE データベース名;
USE music;では、データベース music を選択している状態でテーブル一覧を表示してみます。
SHOW TABLES;
時点ではデータベースを作成したばかりなので Empty set （テーブルは存在しない）と表示されています。
テーブル追加
テーブルを作成してみます。ここで /* */ で囲まれているところもコメント行です。複数行にわたるコメントを書きたいときにはこちらを利用します（もちろん、 -- を複数行書いても構いません）。
/*
CREATE TABLE テーブル名 (
カラム名 型,
カラム名 型,
...
);
*/
CREATE TABLE music_data (
music_id INT,
title VARCHAR(255),
artist VARCHAR(255),
album VARCHAR(255),
label VARCHAR(255),
delivery_data DATE
);INT は整数の型、VARCHAR は指定バイト数以下の可変長の文字列の型、DATE は日付の型です。型については別の記事で詳しく解説します。
上記のSQLを実行したあと、テーブルが作成されたことを確認します。
SHOW TABLES;
また、テーブルの型の情報などを確認したいときには DESC コマンドを利用します。
-- DESC テーブル名;
DESC music_data;
テーブル変更
ここまででテーブルを作成しましたが、カラムを追加したい場合は ALTER TABLE ADD 文を使います。
-- ALTER TABLE テーブル名 ADD カラム名 型;
ALTER TABLE music_data ADD composer VARCHAR(255) COMMENT '作曲者' AFTER artist;ここで COMMENT はカラムのコメント文、 AFTER artist は「 artist カラムの後ろに追加する」ということを表現しています。
上記SQLを実行してから、再度テーブルの情報を確認してみます。
-- DESC テーブル名;
DESC music_data;
artist カラムの後ろに composer カラムが追加されていることがわかります。また、追加したコメントが DESC コマンドでは表示されていません。
コメントを表示したい場合は以下のコマンドを実行します。
-- SHOW FULL COLUMNS FROM テーブル名;
SHOW FULL COLUMNS FROM music_data;
composer カラムに「作曲者」というコメントが追加されていることが確認できます。
既存のカラムを変更したい場合には ALTER TABLE CHANGE COLUMN 文を使います。ここでは delivery_data カラムを delivery_date とリネームします。
-- ALTER TABLE テーブル名 CHANGE COLUMN 旧カラム名 新カラム名 新カラム型;
ALTER TABLE music_data CHANGE COLUMN delivery_data delivery_date DATE;DESC music_data;
カラム名が変更できたことを確認できました。
さらに、不要なカラムを削除したい場合には ALTER TABLE DROP 文を使います。
-- ALTER TABLE テーブル名 DROP カラム名;
ALTER TABLE music_data DROP composer;DESC music_data;
カラムが削除できたことを確認できました。
テーブル削除
テーブルを削除してみます。テーブルの削除には DROP TABLE 文を使います。
-- DROP TABLE テーブル名;
DROP TABLE music_data;SHOW TABLES;
テーブルがなくなったことを確認できます。
データを挿入・検索・更新・削除
データ挿入
テーブルにデータを挿入します。まずはテーブルを作成しておきましょう。
CREATE TABLE music_data (
music_id INT COMMENT '楽曲ID',
title VARCHAR(255) COMMENT '曲名',
artist VARCHAR(255) COMMENT 'アーティスト名',
album VARCHAR(255) COMMENT 'アルバム名',
label VARCHAR(255) COMMENT 'レーベル',
delivery_date DATE COMMENT '配信日'
);データを挿入するには INSERT INTO 文を使います。
/*
INSERT INTO テーブル名 (カラム1, カラム2, ...)
VALUES (値1, 値2, ...);
*/
INSERT INTO
music_data (
music_id,
title,
artist,
album,
label,
delivery_date
)
VALUES
(
1,
'ドライフラワー',
'優里',
'壱',
'ソニー・ミュージックエンタテインメント',
'2020-10-25'
);
続いて、もう2行データを追加しておきます。
INSERT INTO
music_data (
music_id,
title,
artist,
album,
label,
delivery_date
)
VALUES
(
2,
'群青',
'YOASOBI',
'THE BOOK',
'ソニー・ミュージックエンタテインメント',
'2020-09-01'
);
INSERT INTO
music_data (
music_id,
title,
artist,
album,
label,
delivery_date
)
VALUES
(
3,
'逆夢',
'King Gnu',
'一途／逆夢',
'ソニー・ミュージックエンタテインメント',
'2021-12-10'
);データ検索
データの検索には SELECT 文を使います。
-- パターン1: SELECT カラム1, カラム2, ... FROM テーブル名;
SELECT music_id, title FROM music_data;
-- パターン2: SELECT * FROM テーブル名;
SELECT * FROM music_data;SELECT と FROM の間には取得したいカラム名を指定します。カラム名を列挙する代わりに * （アスタリスク）とすることですべてのカラムを取得できます。
上記のSQLを実行すると以下のような結果が得られます。
特定の条件に合致したレコードだけを取得したい場合、 WHERE 句を使用します。
--SELECT * FROM テーブル名 WHERE 条件式;
SELECT * FROM music_data WHERE delivery_date between '2020-01-01' and '2020-12-31';上記のSQLでは delivery_date カラムが「2020-01-01〜2020-12-31の日付」となっているレコードだけを抽出できます。
また、WHERE句には複数の条件を入れることも可能です。その場合には WHERE のあとの条件式を AND または OR でつなぎます。
-- delivery_dateカラムが「2020-01-01〜2020-12-31の日付」 "かつ" artistカラムに「優里」と入っているもの
SELECT * FROM music_data
WHERE delivery_date between '2020-01-01' and '2020-12-31'
AND artist = '優里';-- delivery_dateカラムが「2020-01-01〜2020-12-31の日付」 "または" artistカラムに「King Gnu」と入っているもの
SELECT * FROM music_data
WHERE delivery_date between '2020-01-01' and '2020-12-31'
OR artist = 'King Gnu';
データ更新
データを更新する場合は、 UPDATE 文を使います。
-- UPDATE テーブル名 SET カラム名 = 更新値, ... [WHERE 条件式];
UPDATE music_data SET album = '一途/逆夢' WHERE music_id = 3;WHERE 句は必須ではありませんが、 WHERE 句なしだとすべてのレコードを更新してしまうため、通常は WHERE 句を併用します。
データ削除
データを削除する場合には DELETE 文を使います。
-- DELETE FROM テーブル名 [WHERE 条件式];
DELETE FROM music_data WHERE music_id = 3;こちらも WHERE 句は必須ではありませんが、 WHERE 句なしだとすべてのレコードを更新してしまうため、通常は WHERE 句を併用します。
また、テーブルを完全に空にしたい場合には TRUNCATE 文を使用することもできます。
--TRUNCATE テーブル名;
TRUNCATE music_data;
ユーザーの作成および権限付与・削除
最後に、ユーザーの作成及び作成したユーザーへの権限付与について解説します。
ここまでSQL実行するために使用したユーザーはデフォルトで作成される root ユーザーでした。 root ユーザーは非常に強い権限を持っているため、操作を誤るとデータベースの削除などの破壊的な操作ができてしまいます。
そのためシステムで利用するユーザーは別途作成し、必要な権限に絞った状態としておくのが一般的です（これを「最小権限の原則」と呼びます）。
ユーザー作成
それではユーザーを作成してみます。ユーザーを作成するには CREATE USER 文を使用します。
-- CREATE USER 'ユーザ名'@'ホスト名' IDENTIFIED BY 'パスワード';
CREATE USER 'user01'@'localhost' IDENTIFIED BY 'password';ユーザーはユーザー名と接続するクライアントのホスト名の組み合わせで指定します。指定したホストから指定したユーザー名でログインした場合だけ接続を行えます。
今回はローカル環境に構築したMySQLで操作しているため、ホスト名は localhost としています。
作成したユーザーを確認するには以下のSQLを実行します。
SELECT user, host FROM mysql.user;これは mysql というデフォルトで作成されたシステム用データベースにある user というテーブルから情報を取得するためのSQLです。これを実行すると以下のような結果が得られます。
user01 というユーザーが作成されたことを確認できました。
それでは、作成した user01 ユーザーでMySQLへログインしてみます。 root ユーザーでログインしている今のコンソールはこのあとも利用するため、新しいウィンドウを開いてそちらでログインしてみてください。
mysql --user=user01 --passwordログインしたあと、以下のSQLで今ログインしているユーザー名を確認できます。
SELECT user();
作成した user01 ユーザーでログインしていることが確認できました。
権限付与
ここで、 root ユーザーと user01 ユーザーとでそれぞれでデータベース一覧を表示してみます。
すると上記のように、 root ユーザーでは表示できているデータベースが user01 ユーザーでは表示できていないものがあることがわかります。これは user01 ユーザーが作成されたばかりでなんの権限もついていないためです。
それでは、 user01 ユーザーに music データベースを参照できるような権限を付与します。
/*
GRANT 権限 ON 適用対象のデータベース.適用対象のテーブル TO 'ユーザ名'@'ホスト名' IDENTIFIED BY 'パスワード';
FLUSH PRIVILEGES;
*/
GRANT SELECT ON music.* TO 'user01'@'localhost';
FLUSH PRIVILEGES;「権限」の部分には、設定したい権限を列挙します。
「適用対象のデータベース.適用対象のテーブル」には、権限を適用する対象を指定します。今回は music データベースのすべてのテーブルに SELECT 権限を与えるため、 music.* としています。
FLUSH PRIVILEGES を実行することで、権限の変更をデータベースに反映しています。
なお指定したユーザがデータベース上に存在しない場合、新たにユーザを作成した上で権限を付与します。そのためCREATE USER文を使用せずに、初めからGRANT文を用いてユーザを作成する場合もあります。今回は既存のユーザーへ権限を付与しているため、 IDENTIFIED BY 'パスワード' の部分は省略しています。
上記のSQLを実行したあと、 user01 ユーザーでログインしているコンソールでデータベース一覧を取得すると music データベースが表示できるようになったことが確認できます。
また、 music データベースの music_data テーブルに対して SELECT ができることを確認します。
USE music;
SELECT * FROM music_data;
一方で、データの挿入やテーブルの追加などはできないことも確認できます。
INSERT INTO
music_data (
music_id,
title,
artist,
album,
label,
delivery_date
)
VALUES
(
1,
'ドライフラワー',
'優里',
'壱',
'ソニー・ミュージックエンタテインメント',
'2020-10-25'
);
--> INSERT command denied と表示される
CREATE TABLE sample (col INT);
--> CREATE command denied と表示される
user01 ユーザーでテーブルを作成したりデータを挿入したりするには GRANT 文で別の権限を付与する必要があります。
付与できる権限については公式ドキュメントを参考にしてください。
また、付与されている権限については以下のコマンドで確認できます。
--SHOW GRANTS FOR 'ユーザ名'@'ホスト名';
SHOW GRANTS FOR 'user01'@'localhost';
なお、 FOR 'ユーザ名'@'ホスト名' を省略した場合にはコマンドを実行しているユーザーの権限を確認できます。
権限削除
権限を削除するには REVOKE 文を使います。
/*
REVOKE 権限 ON 適用対象のデータベース.適用対象のテーブル FROM 'ユーザ名'@'ホスト名';
FLUSH PRIVILEGES;
*/
REVOKE SELECT ON music.* FROM 'user01'@'localhost';
FLUSH PRIVILEGES;GRANT 文と同様に FLUSH PRIVILEGES を実行することで、権限の変更を反映しています。
上記のSQLを実行すると、 user01 ユーザーが music データベースへの SELECT 権限がなくなっていることが確認できます。
MySQLコマンド・SQLまとめ
本記事で紹介したMySQLコマンド・SQLについてのまとめです。ここで紹介したコマンド・SQLは一部ですので、紹介しきれなかったコマンド・SQLやコマンド・SQLの詳細などについては公式ドキュメントを参考にしてください。
MySQLコマンド
宣言 | 用途 |
SHOW DATABASES | データベース一覧を表示する |
CREATE DATABASE | データベースを作成する |
DROP DATABASE | データベースを削除する |
USE | 操作するデータベースを選択する |
SHOW TABLES | テーブル一覧を表示する |
DESC テーブル名 | テーブル定義を表示する |
SHOW FULL COLUMNS FROM テーブル名 | コメントを含めたテーブル定義を表示する |
SQL: DDL
宣言 | 用途 |
CREATE | テーブルなどを作成する |
DROP | テーブルなどを削除する |
ALTER | テーブルなどの定義を変更する |
TRUNCATE | テーブルを空にする |
SQL: DML
宣言 | 用途 |
SELECT | データを取得する |
INSERT | データを挿入する |
UPDATE | データを更新する |
DELETE | データを削除する |
SQL: DCL
宣言 | 用途 |
GRANT | ユーザーへ権限を付与する |
REVOKE | ユーザーの権限を剥奪する |
まとめ
MySQLの基本操作および基本的なSQLの構文について解説しました。
ここで紹介したものはほんのごく一部であり、コマンドやSQLを用いてできることはまだまだたくさんあります。
ぜひ公式ドキュメントを参考に実際に試してみてください。
次回は、SQLのデータの型・トランザクションについて解説します。
プログラミングスクールの選び方
転職を検討中の方向け
フリーランス・副業で活躍したい方向け
教養・キャリアアップしたい方向け
給付金について詳しく知りたい方向け
