ProgateでSQLを学習

この記事は12分で読めます

自分用メモ

随時追記していく

※参考:Progateとは?魅力や欠点、学習の順序や心得など

SQLとは?


データベースに送る命令のこと。データベース専用の操作言語と言える。
JavaやC等のプログラミング言語で開発されたシステムやアプリでも、
DBのアクセスにはSQLを用いる。
DBの製品によってSQLの命令や記述方法には微細な違いがあるが、
基本的な構文や考え方は共通している。
SQL文は大文字と小文字を区別しない。文末にはセミコロン(;)を置く必要がある。

※参考:クリエイティブ、プログラミング用語まとめ

学習コース SQL I



①データを取得しよう

データベースとは
SQLとは
複数のカラムを選択する



SQLは分析でも使え、覚えておくと出来る事の幅が広がるスキルである

データ分析:「データベース」に保存されているデータを、統計などの知識を使って活用すること

データベース:テキストや数値などのデータを保存するためのツール
TwitterのようなSNSは「投稿データ」を、Progateでは「レッスンデータ」をDBに保存している


テーブルの構造:縦がカラム、横がレコード
データベースでは、必要に応じて複数のテーブルを作成する事が可能である


★SQLとは
データベースからデータを取得するためには「データを取得して下さい」といったように
データベースに送る命令が必要、これをSQLと呼ぶ

データベースから、データを取得するためには「選ぶ」という意味の”SELECT”というSQLを用いる
この「選ぶ」というSELCT文を用いて、「どのカラム(縦)のデータを取得するか」を選ぶ

データベースには複数のテーブルが存在する場合がある
そのため「FROM」というSQLを用いて、SELECT文で選んだカラムが
「どのテーブルのカラムか」を指定する必要がある


SELECT name   ②どのカラムのデータを?
FROM purchases ①どのテーブルの?


SELECT name
FROM purchases;


最後に「ここまでがSQL文です」ということをデータベースに伝えるための
セミコロン(;)を加えてSQLは完成
SQLは一行でも書けるが、今回は読みやすいように改行で書く
SQLは大文字と小文字を区別しないので、小文字で記述しても問題なし

Select name
From purchases;




一度に複数のカラムからデータを取得する方法もある



SELECT name, price



複数のカラムからデータを取得する場合は、カラム名をコンマ(,)で区切る


SELECT id, name, price, character_name, category, purchased_at
FROM purchases;


SELECT *
FROM purchases;


全カラムのデータを取得する場合は「*」の記号を用いる



②特定のデータを取得しよう

WHERE
比較演算子
LIKE演算子
NOT演算子
IS NULL・IS NOT NULL
AND・OR演算子




★特定のデータを取得しよう
「WHERE」というSQLを使う
「SELECT」と「FORM」で、
「どのテーブルのどのカラム(縦)のデータを取得するか」までは決まっているので、
WHEREで「どこのレコード(横)のデータを取得するか」を決めていく





WHERE category = ◯◯
(categoryカラム=縦が◯◯であるレコード=横を)



SELECT *     ←全てのカラム(縦)
FROM purchases
WHERE category =“食費”;



「食費」のようなテキストはクォーテーションで囲む必要がある(※参考:データ型)



SELECT *
FROM purchases
WHERE price = 1000;

「数値データ」は「文字データ」とは異なり、クォーテーションで囲んではいけない



2017-07-01といった「日付」のデータ型は、
クォーテーションで囲む必要がある:”2017-07-01”



セミコロン(;)は、最後に「ここまでがSQL文です」という意味があるので、
最後以外の箇所で書いてはいけない


SELECT *
FROM purchases
WHERE price >= 1000;


WHEREの条件では、「>=」といった大小比較の記号を使用する事もできる




SELECT *
FROM purchases
WHERE purchased_at <= “2017-08-01”;


「priceカラム」のような数値データ以外にも、
「purchased_atカラム」のような日付データにも、
比較演算子は用いる事が可能である




★「ある文字を含むデータ」を取得したい場合(LIKE演算子)
・例:「プリン」を含むデータのような条件で取得する
・意味:「指定したカラムが◯◯を含む(◯◯のような)レコード」という条件



SELECT *
FROM purchases
WHERE name LIKE “%プリン%”;


LIKE演算子を用いる際には「ワイルドカード」という記号を覚えておく
どんな文字列にも一致することを指す記号、という意味
LIKE演算子では「%」をワイルドカードとしてつかう



SELECT *
FROM purchases
WHERE name LIKE “プリン%”;

ワイルドカードを文字列の前後どちらかのみに置くことも可能
「◯◯%」とした場合、「◯◯」以降はどんな文字列にも一致する
→「 プリン%」だと「プリンパフェ」は出てくるが、
 「牛乳プリン」や「焼きプリン大福」は出てこない
 →「%」は、ある種の”拡声器”的な役割を果たしていると言えそう



SELECT *
FROM purchases
WHERE name LIKE “%プリン”;


「%◯◯」の場合、「◯◯」より前はどんな文字列にも一致する
(例:牛乳プリン、プリン)
→この場合「%」は、ある種の「防波堤」の役割を果たしていると言えそう




★否定の条件でデータを取得する(NOT)
「◯◯を含まないデータ」や「◯◯に一致しないデータ」といった条件で
データを取得したい場合は「否定」を意味する「NOT演算子」を用いる


SELECT *
FROM purchases
WHERE NOT price > 1000;


SELECT *
FROM purchases
WHERE NOT name LIKE “%プリン%”;


「NOT演算子」は。条件を否定できる便利な演算子なので、しっかり覚えておく


★カラムに何も保存されてないデータ
こうしたデータは「NULL」と呼ぶ
「中身が何かわからない」ことを示す
例えば、何のデータも保存されてないばあいにNULLとなる



SELECT * FROM purchases
WHERE price IS NULL;


NULLのデータを取得するためには
「〜がNULLである」という意味になる「IS NULL」というSQLを用いる
「カラム名 IS NULL」とすることで、
「指定したカラムがNULLであるデータ」を取得する事が可能となる



SELECT * FROM purchases
WHERE price IS NOT NULL;


「NULLではないデータ」を取得する場合は
「〜がNULLではない」という意味になる「IS NOT NULL」を用いる

「IS NOT NULL」も「IS NULL」と同様に、
「カラム名 IS NOT NULL」のようにして使う

「NULLのデータ」や「NULLではないデータ」を取得したい場合、
「=」は利用できないので注意する



★「複数の条件」からデータを取得する


SELECT * FROM purchases
WHERE characeter_name = “ひつじ仙人”
AND category = “食費”;


AND演算子を使うと、WHERE文に複数の条件を指定できる
「WHERE 条件1 AND 条件2」のようにすることで、
条件1と条件2を共に満たすデータを検索できる



SELECT * FROM purchases
WHERE character_name = “ひつじ仙人”
OR character_name = “にんじゃわんこ”;



SELECT * FROM purchases
WHERE character_name = “ひつじ仙人”
OR character_name = “にんじゃわんこ”;



ANDもORも、最後にセミコロン(;)を忘れない



③取得結果を加工しよう

ORDER BY
LIMIT



★取得したデータを並び替え用(ORDER BY)


ORDER BY 並び替えたいカラム名 並べ方;



データを並び替えるためには「〜順に並べる」という意味の
「ORDER BY」というSQLを持ちる
またデータを並び替えるためには、
「(基準となる)並び替えたいカラム名」と「並べ方」を指定する必要がある




ASC(昇順): 1,2,3…100

DESC(降順): 100…3,2,1


「ORDER BY」の並べ方は「昇順」か「降順」を指定する
昇順は「小さい数から大きい数へ向かう順序」を
降順は「大きい数から小さい数へ向かう順序」を意味する

SQLでは昇順は「ASC」、降順は「DESC」と指定する



SELECT *
FROM purchases
ORDER BY price DESC;


「ORDER BY」はSQL文の末尾に記述することで、取得結果を並び替える



SELECT *
FROM purchases
WHERE 条件
ORDER BY price DESC;


「ORDER BY」はSQL文の末尾に記述すれば良いので、
「WHERE文」と併用することが可能


降順:大きい数から
昇順:小さい数から




★「必要な数だけ」データを取得する(LIMIT)
・「最大で何件取得するか」を指定する



LIMIT データの件数;


「最大で何件取得するか」を指定するには、
「制限する」という意味の「LIMIT」を用いる

検索結果の上から指定されたデータの件数だけ取得する



SELECT *
FROM purchases
LIMIT 5;


「LIMIT」も「ORDER BY」の同様にSQL文の末尾に記述することで
取得するデータの数を制限する


SELECT *
FROM purchases
WHERE 条件
LIMIT 5;



「LIMIT」は「ORDER BY」と同様に
SQL分の末尾に記述すれば良いだけなので、
「WHERE文」と併用することが可能


④総合演習



★ORDER BYとLIMITの組み合わせ

SELECT *
FROM purchases
ORDER BY price DESC
LIMIT 5;


「ORDER BY」と「LIMIT」はどちらもSQLの末尾に記述するが、
2つ一緒に使うことも可能
但し、この2つを併用する際は、「LIMIT」を末尾にする必要がある


★SQLの演算子
http://otndnld.oracle.co.jp/document/products/lite10g/10.2.0/html/B19286-02/sqopr.htm
等しくない場合は「!=」を用いる
等しい場合は「=」を用いる(2つ重ねる「==」ではないので注意)


SELECT id,name
FROM user
WHERE 3 < id
ORDER BY id DESC;


学習コース SQL Ⅱ


①検索結果を加工しよう


★実践的なSQL
取得したデータを加工して分析しやすくしたり、
便利な関数を使って少し高度な分析を行ったりする

★検索結果を加工する
加工:DBのデータを取得するだけでなく、そのデータ同士を計算して
新しいデータを作り出すこと

★DISTINCT

DISTINCT(カラム名)

DISTINCTを用いると、検索結果から重複するデータを除く事が可能
「DISTINCT(カラム名)」とすることで、検索結果から
指定したカラムの重複するデータを除くことが出来る


SELECT DISTINCT(name)
FROM purchases;

→purchasesテーブルからnameカラムの重複したデータを省いてる

DISTINCTは、SELECT文の後に使用することで、
重複を省いたデータを取得できる


★四則演算
SQLには取得したデータにかけ算や割り算をする「四則演算」という機能がある
これを使うと、消費税を含んだ値にできる

「カラム名 * 1.08」
「カラム名 – 1000」


SQLでは四則演算(+ – * /)が可能


SELECT name, price * 1.08
FROM purchases;


四則演算をSELECT文の後に使うことで、計算後のデータを取得できる

②集計関数を使おう

★関数を使ってみよう
WHEREで検索したデータ→集計関数→集計結果(データの合計、平均など)

集計関数は、テーブルに保存されている
数値データの合計や平均などを求めてくれる


★SUM関数
SUM(カラム名)

SQLで数値の合計を計算する場合は、SUMを用いる
「SUM(カラム名)」のようにすることで、
指定したカラムに保存されたデータの合計を計算できる

SELECT SUM(price)
FROM purchases;

SUM関数はSELECTの後に使用し、集計結果を取得することが出来る


★WHERE文とSUM関数

SELECT SUM(price)
FROM purchases
WHERE character_name = “にんじゃわんこ”;

SUM関数はWHERE文と併用できる
上記のコードは、WHERE文を使う事で、
にんじゃわんこが今まで使ったお金の合計金額を取得している


★平均を計算しよう
priceカラムに保存されてるデータの平均を計算できれば、
1回あたりに使うお金の平均が求められる

AVG(カラム名)

SQLで数値の平均を計算する場合は「AVG」を用いる
「AVG(カラム名)」にすることで、指定したカラムに
保存されたデータの平均を計算することが可能となる

SELECT AVG(pride)
FROM purchases;

AVG関数はSELECTの後に使用し、計算結果を取得することが出来る


★WHERE文とAVG関数

SELECT AVG(price)
FROM purchases
WHERE character_name = “にんじゃわんこ”;

AVG関数はWHERE文と併用する事ができる


★データの数を計算しよう
保存されてるデータの数を数える集計関数を解説

COUNT(カラム名)

COUNT関数は、指定したカラムのデータの合計値を計算してくれる関数
「COUNT(カラム名)」とすることで、指定したカラムのデータの数を計算する

COUNT関数でカラム名を指定した場合、
nullになってるデータの数は計算されない

SELECT COUNT(*)
FROM purchases;

nullの数も含めてデータの数を計算したい場合は、
COUNT関数で「*(全てのカラム)」を指定する
「*」を使った場合「特定のカラムのデータ数」ではなく「レコードの数」を計算する

SELECT COUNT(*)
FROM purchases
WHERE character_name = “にんじゃわんこ”;

COUNT関数はWHERE文と併用できる

★最大、最小を求めよう
保存されてるデータの中で最大、最小のものを検索する集計関数

MAX(カラム名)
MIN(カラム名)

SQLで「MAX」という関数を用いると、
指定したカラムのデータから最大のデータを取得できる
また「MIN」という関数を用いることで、最小のデータを取得できる

SELECT MAX(price)
FROM purchases;

MAX,MINも他の集計関数と同様にSELECTの後に使用できる

SELECT MAX(price)
FROM purchases
WHERE charcter_name = “にんじゃわんこ”;

MAX,MIXも他の集計関数と同様にWHERE文と併用できる


③データをグループ化しよう

日付ごとの金額の合計を一度に出して分析したい場合は
グループ化」という機能を使う
日付が同じデータごとに集計関数を使うことを「グループ化」と言う
→日付ごとにお金の合計を取得したい!

GROUP BY カラム名

「GROUP BY」を用いると、データをグループ化する事ができる
「GROUP BY カラム名」とすることで、指定したカラムで、
完全に同一のデータを持つレコード同士が同じグループになる

SELECT SUM(price),purchased_at
FROM purchases
GROUP BY purchased_at;

グループ化するには、今までの集計関数を
取得するSQLの語尾に「GROUP BY カラム名」を追加する
集計関数(SUM等?)により、各グループごとデータが集計される

SELECT SUM(price), purchased_at
FROM purchases
GROUP BY purchased_at;

→これやると、日毎の合計が表示される!

「GROUP BY」を用いる場合、SELECT文で使えるには
「GROUP BYに指定しているカラム名」と「集計関数」のみ
上では、SELECTでpriceカラムを指定している


★GROUP BY(複数)
複数のカラムでもグループ化ができる
→複数の要素に分割して、それぞれの合計を出す感じ?

SELECT SUM(price),purchased_at, character_name
FROM purchases
GROUP by purchased_at, character_name;

「GROUP BY」は複数のカラム名を適用させる事ができ、
その場合は、カラム名同士をコンマ(,)で繋げる

「GROUP BY」に複数のカラム名でグループ化すると、
データの組み合わせの数だけグループが出来る


★細かい条件でデータをグループ化する
例:日付ごとの食費に使ったお金の合計
→この場合はWHERE文を一緒に使うと便利
→GROUP BYはWHERE文と一緒に使う事もできる

SELECT 集計関数
FROM テーブル名
WHERE 条件
GROUP BY カラム名, カラム名;

「GROUP BY」はWHERE文とも併用できる、この場合はWHERE文の後に書く
実行の順番は以下の通り:
①検索:WHERE
→WHERE文でカテゴリーが「食費」であるレコードを検索する
②グループ化:GROUP BY
→日付とキャラクターでグループ化する
③関数:COUNT SUM AVG MAX MIN
→集計関数で計算する


★WHEREとGROUP BYの書き方

SELECT SUM(pride), purchased_at, character_name
FROM purchases
WHERE category = “食費”
GROUP BY purchased_at, character_name;

「WHERE」と「GROUP BY」の順番には気を付ける


★グループ化したデータを更に絞り込もう(HAVING)
GROUP BYでグループ化したデータから、
更に得智慧のグループのみを取得したい場合は
HAVINGというSQLを用いる必要がある

GROUP BY カラム名
HAVING 条件;

「GROUP BY カラム名 HAVING 条件」とすることで、
条件を満たすグループを取得することが出来る


★WHEREとHAVING
SQLのコマンドは以下の順番で実行される
①検索:WHERE
②グループ化:GROUP BY
③関数:COUNT SUM AVG MAX MIN
④HAVING

グループ化した後のデータを絞り込む際、
WHEREでなくHAVINGを使うのは、
上記のSQLの各コマンドが実行される順番と関係している

実行順序によって、WHEREとHAVINGは検索対象に違いがある
WHEREはグループ化される前のテーブル全体を検索対象とする
HAVINGはGROUP BYによってグループ化されたデータを検索対象とする


★HAVINGの注意点
条件式で使うカラムは、必ずグループ化されたテーブルのカラムを使う

SELECT SUM(price), purchased_at
FROM purchases
GROUP BY purchased_at
HAVING SUM(price) > 1000;

④総合演習


学習コース SQL Ⅲ


複数テーブルを利用したデータ取得を学ぶ
サブクエリとJOINを覚えてSQLを使いこなすのが目標

①サブクエリを使ってみよう

★SQLを使いこなそう
複数テーブルと新しいSQL文の書き方を用いて
これまでは取得できなかったデータを取得する

★サブクエリ
1個のSQL文でデータが取得できるようになる
SQLでは、SQL文の中に他のSQL文を入れる事ができる
この「他のSQL文」をサブクエリと言う
2つ以上のSQL文を1つにまとめることが出来るので、
より複雑なデータを取得する際に用いられる

→以下は2つのSQL文を書く場合
SELECT goals
FROM players
WHERE name = “ウィル”;

SELECT name
FROM players
WHERE goals > 14;

→以下はサブクエリを使う場合
SELECT name
FROM players
WHERE goals > (
SELECT goals
FROM players
WHERE name = “ウィル”
);

上記の図では「ウィルより得点数の多い選手名」を調べている
サブクエリの書き方としては、()で囲むことでサブクエリを使用できる
サブクエリの書き方は基本的に通常のSQL文と同じだが、
()内にセミコロン(;)は不要:セミコロン(;)はSQL文の最後に書けばOK

★サブクエリの実行順序
サブクエリを含むSQL文の場合、サブクエリが実行された後、
外部にあるSQL文が実行される

ーーーーーーーーーーーーーーーーーー

★データを読みやすくしよう(AS)
取得したカラム名の表示を変更できる

SELECT goals AS “ウィルの得点数”
FROM players
WHERE name = “ウィル”;

ASを使うことでカラム名などに別名を定義できる
「カラム名 AS “名前”」で、カラム名に定義する名前を指定する

②複数テーブルを活用してみよう

テーブルを紐付ける
JOIN
teamsテーブル
LEFT JOIN
3つのテーブル結合

★テーブルを紐付ける
あるデータと別のデータを一緒に使う
テーブル同士を一緒に使うには、
外部キーと主キーというカラム(列)を用意する
(主キーが「id」なら、外部キーは「◯◯_id」となる?)

テーブルを紐付けるメリットとして、
データを管理しやすくなる点が挙げられる

★JOIN
紐付いたテーブルはJOINを使うことで合体できる
これを「テーブルの結合」と呼ぶ
結合したテーブルは、1つのテーブルと同じようにデータを取得できる

SELECt *
FROM テーブルA
JOIN テーブルB
ON 結合条件

ONで条件を指定して、テーブルAにテーブルBを結合する
結合したテーブルは1つのテーブルとしてデータを取得できる

SELECT *
FROM players
JOIN countries
ON players.country_id = countries.id;

結合条件は「ON テーブル名.カラム名 = テーブル名.カラム名」で指定する
最初のカラム名が外部キーで、後のカラム名が主キーに該当

実行順序としては、まずJOINが実行され、
その上で、結合されたテーブルに対してSELECTが実行される


★複数テーブルでカラムを指定する方法
複数のテーブルに同じカラムがある場合は、
「テーブル名.カラム名」で指定する必要がある

SELECT players.name, countries.name
FROM Players
JOIN countries
ON players.country_id = countries.id:


★全体実行順序の確認
①テーブルの指定 FROM
②結合 ON・JOIN
③取得条件 WHERE
④グループ化 GROUP BY
⑤関数 COUNT SUM AVG MIN
⑥HAVING
⑦検索 SELECT・DISTINCT(意外と後の方…)
⑧順序 ORDER BY
⑨LIMIT

※HAVINGは「抽出条件の指定」
※DISTINCTは「重複行の除外」

ーーーーーーーーーーーーーーーーーー

★3つのテーブル
テーブルは2つだけでなく複数個作れる

SELECT *
FROM players
JOIN teams
ON players.previous_team_id = teams.id;

JOINを使った結合は、FROMで指定したテーブルを基準に実行される
しかし、外部キーがNULLのレコードは、実行結果には反映されない


備忘録メモ


以下の記事も参照
※参考:データベース(DB)とは?CRUDやSQL、作成方法も解説
※参考:リレーショナルデータベース(RDB)とは?四大命令や主キー外部キーも解説

  • このエントリーをはてなブックマークに追加
  • LINEで送る

関連記事

  1. この記事へのコメントはありません。

  1. この記事へのトラックバックはありません。