はじめに
この記事はPostgreSQLの勉強のアウトプットにポケモンずかんをつくりながら学びを深める試みです。
作業環境はpgAdminで行っています。
過去の記事
現在までの操作を記事化してたら謎のシリーズ化し始めてます。
- 【PostgreSQL】ポケモンずかんをつくってSQLを学ぶ1（テーブルを作成）
- 【PostgreSQL】ポケモンずかんをつくってSQLを学ぶ2（データの登録・変更）
- → 現在の記事はここです
- 【PostgreSQL】ポケモンずかんをつくってSQLを学ぶ4（レコードの更新・削除）
楽しくSQLの理解を深めることが一番の目的なため、登録される順序などは実際のポケモン図鑑の内容と違う部分があるかとおもいますが、そこは全力を上げて見逃していただけると嬉しいです。
また、SQL初学者のため謎の動きもするかとおもいますが、それらの生々しい修正作業なども記録しながら作成していきますので、時々寄り道をしながら理解をより深めていきたいと思いますので、よろしくお願いします。
SELECT文
SELECT文とは
SQLにおいて非常に重要な文章で、データの一覧を抽出したり、条件を指定して必要な情報を抽出することができる。
SELECT文で以前作ったポケモン図鑑のデータを扱っていきます。
SELECT文の使い方
基本構文
SELECT文の基本的な構文ははこちらです。
SELECT 取得したいカラム名 FROM テーブル名 WHERE 条件;
記述の手順としては..
-
SELECTを宣言し、検索したいカラムを指定する。 -
FROMを宣言し、検索したいテーブルを指定する。 -
WHEREを宣言し、検索したい行の条件を指定する。
この３ステップで記述します。
FROM句なしで実行すると自分の好きなようにSELECT結果をカスタマイズできます。
ちなみになんですがSELECT 文だけ実行すると入れるデータの確認を行うことができます。
テーブル内からカラムを指定して取得する
pokemonテーブル内のポケモンの名前 ( nameカラムの内容 )をすべて抽出したいときはこのように記述します。
SELECT name FROM pokemon;
実行結果
ORDER BY （ソート）
テーブル内のすべてのカラムデータを取得する
すべてのデータを見たい場合は*( アスタリスク )で指定します。
SELECT * FROM テーブル名;
プログラミングで「すべて」を指定するときは、アスタリスクを使うことが多いので、ここでも「すべてのカラムで」という意味で使われます。
pokemonテーブル内の全データを見たい場合はこうなります。
SELECT * FROM pokemon;
実行結果
複数のデータを同時に取得する
２つ以上のカラムデータを同時に抽出したい場合は、列名をカンマで区切ります。
最後のカラムの後ろにはカンマをつけるとエラーが起きてしまうので注意しましょう。
SELECT カラム１, カラム2, カラム3 FROM テーブル名;
pokemonテーブル内のname(名前)、type1(タイプ１)、description(説明文)のカラムデータをいっぺんに見たい場合はこうなります。
SELECT name, type1, description FROM pokemon;
実行結果
カラム名に別名をつける
カラムに別の読み方をつけることもできます。書き方はこちらです。
SELECT カラム名 AS 別名 FROM staff
現在のカラムにそれぞれ別名を与えていこうと思います。
※DBMS（データベースの管理を行うシステム）環境によっては、日本語入力ができなかったり、クオーテーションが必要だったりするようです。
SELECT name AS "なまえ" FROM pokemon;
実行結果
「name」だったカラム名が、「なまえ」という表示になって抽出されました。
Distinct
Distinctをつかうことで、重複されたものを抽出せずにデータを引っ張ってきます。
視点をを変えれば、そのカラムに何種類の値があるかを調べることができます。
- 会社の部署はいくつあるのか
- ポケモンのタイプは何種類あるか
- 生徒はどこの出身者がいるか
などといった、オリジナルの種類の数を知りたいときに便利です。
【追加】ポケモン追加
ちょっと今のままだとポケモンの種類が少なすぎるため、少し追加します。
※ あくまでプログラミングの勉強を効率良くするための追加のため、実際のポケモン図鑑の順番とは一切関係ありませんので宜しくおねがいします。
INSERT INTO pokemon(
number, name, category, type1, type2, height, weight, description,created
) VALUES
('5','フリーザー','れいとうポケモン','こおり','ひこう','1.7','55.4','でんせつの とりポケモン。 くうきちゅうの すいぶんを こおらせふぶきを つくりだすことが できる。',current_timestamp),
('6','サンダー','でんげきポケモン','でんき','ひこう','1.6','52.6','くもの うえから きょだいないなずまを おとしながら あらわれるでんせつの とりポケモンである。',current_timestamp),
('7','ファイアー','かえんポケモン','ほのお','ひこう','2.0','60.0','むかしから ひのとりでんせつとしてしられる。 はばたくたびに はねがあかるく もえあがり うつくしい。',current_timestamp),
('8','カビゴン','いねむりポケモン','ノーマル','','2.1','460.0','1にちに たべものを 400キロ たべないと きが すまない。 たべおわると ねむってしまう。',current_timestamp),
('9','ミュウツー','いでんしポケモン','エスパー','','2.0','122.0','ひとりの かがくしゃが なんねんもおそろしい いでんし けんきゅうをつづけた けっか たんじょうした。',current_timestamp),
('10','ニャース','ばけねこポケモン','ノーマル','','0.4','4.2','ひかりものを あつめるのが すき。 きげんが いいときは トレーナーにも コレクションを みせてくれるぞ。',current_timestamp);
select * from pokemon;
実行結果
DISTINCTを使って重複分を省いて抽出する。
改めて、登録されているポケモンにはどんなタイプがあるか抽出してみます。
SELECT type1 FROM pokemon;
実行結果
これでいいようにみえますが、「でんき」や「ほのお」が重複していて、何種類あるかがぱっと見てわかりづらいです。
これから他のタイプが増えて、ポケモンの数が151匹とかになってくると確認が非常に大変になります。
そこでDISTINCTをつかって、重複分を省いて表示します。
基本構文
SELECT DISTINCT カラム名 FROM テーブル名;
これを使って、ポケモン図鑑のテーブル(pokemon)内の、ポケモンのタイプのカラム(type1)から重複分を省いて抽出します。
SELECT DISTINCT type1 FROM pokemon;
実行結果
これで重複された値は省かれ、登録されたポケモンのタイプは７種類あることがわかりました。
ORDER BY（ソート機能）
データをソート（データを一定の基準に従って並べかえること。）することも可能です。
ソートするにはORDER BY句を使用します。
昇順、降順はそれぞれ ASC、DESC で指定します。
- 昇順はASC (ascending=上昇の略）
- 降順はDESC(descending=降順の略）
select文で組み合わせた構文はこちらです。
SELECT カラム名 FROM テーブル名 ORDER BY ソートの基準にするカラム ASC （昇順）
SELECT カラム名 FROM テーブル名 ORDER BY ソートの基準にするカラム DESC （降順）
※ ちなみに、ORDER BY記述のあとに ASC, DESC を指定しない場合は ASC(昇順)と判断されます。
ポケモンの名前を昇順(あいうえお順)で並べ替えて出力する
テーブル全体のデータを、ポケモンの名前順に表示させたいと思います。
SELECT * FROM pokemon ORDER BY name ASC;
実行結果
ポケモンの名前を基準に昇順になりました。
ソート対象が文字列型の場合、文字コード順に並びます。
ポケモンの体重を降順（重い順）で並べ替えて出力する
数字の降順は値が大きいものが上に来るため、重いポケモン順に表示されると思います。
SELECT * FROM pokemon ORDER BY weight DESC;
実行結果
更に細かくソートする
ソート対象の列名をカンマで区切って複数指定することで、複数のソートキーによるソートを行うことができます。
「ポケモンのタイプ別でそれぞれ一番たかさの大きい順番をしりたい」
といった場合はタイプ(type)と たかさ(height)のカラムをカンマで区切ってあげます。
SELECT * FROM pokemon ORDER BY type1,height desc;
実行結果
それぞれのタイプのの高さ順でソートされました。
WHERE句（条件）
SELECT文だけだとカラムの中のすべての情報が抽出されてしまいます。
細かく検索するために、SELECT文のあとWHERE句を記述することで、条件を指定して必要な情報を抜き出すことができます。
WHERE句の基本構文
SELECT カラム名 FROM テーブル名 WHERE カラム名 = 値;
※ 値が文字列型(CHARやVARCHAR)のときは、シングルクオート'で囲みましょう。
値が~~であるものだけ抽出する
WHERE文をつかって、テーブル全体のなかから、でんきタイプ（type1が'でんき'）のポケモンだけを抽出してみます。
SELECT * FROM pokemon WHERE type1 = 'でんき';
実行結果
でんきタイプのポケモンだけを抽出することができました。
演算子
比較演算子
=（イコール）のほかにも、演算子（< > + - = * /）を使うことで、「○○より多い」、「○○より少ない」といった条件をつけて抽出することもできます。
| 比較演算子 | 内容 |
|---|---|
| = | 等しい |
| != もしくは <> | 等しくない |
| > | 大きい |
| < | 小さい |
| >= | 以上 |
| <= | 以下 |
全ポケモンの中から、体重が50.0ｋｇ以上のものだけ抽出する場合
SELECT * FROM pokemon WHERE weight > 50.0;
実行結果
体重が50.0ｋｇ以上のポケモンだけを抽出しました。
論理演算子
| 論理演算子 | 内容 |
|---|---|
| AND | かつ |
| OR | または |
| NOT | ではない |
条件はANDでつなげることで複数指定できます。
先程つかった「タイプがでんき」であり、「体重が50.0以上」のポケモンを抽出する場合はこのようになります。
SELECT * FROM pokemon WHERE type1 = 'でんき' AND weight > 50.0;
実行結果
今の所サンダーしか登録されていませんが、「でんきタイプ かつ 体重が50.0以上」のポケモンを抽出しました。
IN演算子
タイプがほのお、ノーマル、エスパーのポケモンを抽出したいとき、ORやイコールを使うとこのような書き方になります。
SELECT * FROM pokemon WHERE type1 = 'ほのお' OR type1 = 'ノーマル' OR type1 = 'エスパー';
少しいうえに同じような記述がおおくなります。
これを短く記述する方法で、IN演算子というものがあります。
IN演算子の構文
SELECT 表示するカラム名 FROM テーブル名 WHERE 抽出元のカラム名 IN(値,値,値,...);
IN演算子を使用して上のコードを書き直すとこうなります。
SELECT * FROM pokemon WHERE type1 IN('ほのお','ノーマル','エスパー');
実行結果
短いコードで同じ内容の実行結果を得ることができました。
BETWEEN演算子
「〇〇から〇〇までの間」といった表現には、BETWEEN演算子を使います。
BETWEEN演算子の構文
SELECT 表示するカラム名 FROM テーブル名 WHERE 抽出元のカラム名 BETWEEN 値 AND 値;
BETWEEN演算子をつかって、「身長が0.5mから1.8mのポケモン」だけを抽出します。
SELECT * FROM pokemon WHERE height BETWEEN 0.5 AND 1.8;
実行結果
身長が0.5mから1.8mのポケモンだけを引っ張ることができました。
LIKE演算子
説明文に「でんせつ」という言葉が入ったポケモンを知りたい場合がある場合、LIKE演算子を使います。
LIKE演算子を使う際に任意の文字列を表すワイルドカードという記号を使うことで、長文に紛れた文字であったり、「〇〇から始まる」といった文字列のパターンからマッチしたものを抽出することができます。
| ワイルドカード | 説明 | 使用例 |
|---|---|---|
| % | 任意の0文字以上の文字列 | 「サ%ダー」 の場合、以下のような文字列がマッチする。 ・サンダー ・サラバダー ・サスペンダー |
| _ | 任意の1文字 | 「サ_ダー」の場合、以下のような文字列がマッチする。 ・サンダー ・サイダー ・サケダー |
今回は説明文のなかに「'でんせつ'」が含まれるポケモンということで、「'%でんせつ%'」という表現になります。
SELECT * FROM pokemon WHERE description LIKE '%でんせつ%';
実行結果
ちなみに、'でんせつ%'という書き方をすると、「'でんせつ'の前には何も文字列が入らない」という表現になるため、表示結果がまた変わってきます。(この場合一番上のフリーザーの説明だけ。)
「田中○○さん」など、ファーストネームから名前を抽出したい場面でもつかえそうです。
IS NULL演算子
IS NULL算子はNULLを判定する演算子です。 DBにもNULL値が存在します。
概念的には「値がない」か「未知な値」を意味します。 NULLに対して比較演算子は使えません。
IS NULL演算子の構文
SELECT 表示するカラム名 FROM テーブル名 WHERE 抽出元のカラム名 IS NULL;
NOT演算子
WHEREのあとにNOTとつけることで、逆の結果にすることができます。
たとえば、さきほど使用した
「テーブル全体のなかから、でんきタイプ（type1が'でんき'）のポケモンだけを抽出」するコードもNOTとつけることで「でんきタイプ以外のポケモンを抽出する」という内容に変わります。
SELECT * FROM pokemon WHERE NOT type1 = 'でんき';
実行結果
でんきタイプ以外のポケモンを抽出することができました。
今まで使ったWHERE文のどれもNOTとつけることで「それ以外」という実行結果に変わってきますので、こういった表現方法も覚えておくと扱いの幅が広がってきそうです。