SQLについて [まとめ]

はじめに

SQLとは

データベースを操作するために使う言語

データベース

様々な情報の集合
たとえば、顧客情報や製品情報など

SQLはこのデータベースの情報を検索したり、追加したり、更新、削除など
操作するために使う

用語

データ分析

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

テーブル

データ管理している表のこと
また、縦の列をカラム、横の行をレコードと呼ぶ

データの取得

SELECT

データベースからデータを取得するために使う
SELECTを用いて、「どのカラムのデータを取得するか」を選ぶ

FROM

FROMを用いて、SELECTで選んだカラムが「どのテーブルのカラムか」を指定する必要がある

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

ここではpurchasesテーブルのnameカラムを選択している
セミコロンまで書いて１つのクエリ（命令）である

一気に取得する

SELECT name,price
FROM purchases;

これでカラムのnameとpriceを同時に取得できる

SELECT *
FROM purchases;

*でカラム全てを取得できる

特定のデータを取得する

WHERE

「どこのレコード（横の行）を取得するか」を指定する

SELECT *
FROM purchases
WHERE category="食費";

全てのカラムを選択し、表purchasesからcategoryカラムの食費を選ぶ
「食費」のようなデータには「データ型」と呼ばれるルールがあり,クォーテーションで囲む

データ型

データ型とは

テキストデータや数値データ、さらには日付データといったように「データの種類」を示すもの

種類	例
テキストデータ	"にんじゃわんこ"
数値データ	整数値 1000
日付データ	"2021-09-17"

※ 日付データはダブルクォーテーションまたはシングルクォーテーションで囲む

比較演算子

大小比較の記号
a < b、a <= b、a > b、a >= b　など

WHERE

比較演算子を用いることで「priceカラムが1000以上であるレコード」などを
取得することが可能となる

WHERE price>= 1000;

1000円以上のデータを取得

WHERE purchased_at <= "2017-08-01";

2017-08-01以前のデータを取得

ある文字を含むデータ

LIKE演算子

「〜のような」という意味を持つ
「指定したカラムが〇〇を含む（〇〇のような）レコード」という条件となる

ワイルドカード

どんな文字列にも一致することを指す記号
LIKE演算子では「％」をワイルドカードとして扱う

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

たとえばデータでプリン、牛乳プリン、プリンパフェ、焼きプリン大福があるとすると
全て該当する

「プリン」の前後のワイルドカード（%）により、「プリン」という文字を含むデータが取得できる

ワイルドカードの前方一致と後方一致

データにプリン、牛乳プリン、プリンパフェ、焼きプリン大福があるとすると

WHERE name LIKE "プリン%";

プリン、プリンパフェが該当する
つまりプリンで始まるデータが該当する

WHERE name LIKE "%プリン";

プリン、牛乳プリンが該当する
つまりプリンで終わるデータが該当する

NOT演算子

「〇〇を含まないデータ」や「〇〇に一致しないデータ」のような条件でデータを取得したい場合に使う

WHERE NOT name LIKE "%プリン%";

プリンを含まないデータ

WHERE NOT price > 1000;

1000円以上でないデータ

カラムに何も保存されていない

NULLとは

カラムに何も保存されていない場合はNULLと表示される

カラム名 IS NULL

「指定したカラムがNULLであるデータ」を取得することが可能

WHERE price IS NULL ;

指定したカラムのデータが「NULL」であるものを取得する

WHERE price IS NOT NULL ;

指定したカラムのデータが「NULL」でないものを取得する

複数条件を取得

WHERE 条件１ AND 条件２

条件１と条件２を同時に満たすデータを取得することができる

WHERE 条件１ OR 条件２

条件１または条件２のどちらかを満たすデータを検索することができる

取得したデータを並び替える

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

「〜順に並べる」という意味

並べ方

• ASC（昇順）
  小さい数から大きい数

• DESC（降順）
  大きい数から小さい数

SELECT *
FROM purchases
WHERE character_name = "にんじゃわんこ"
ORDER BY price ASC;

キャラクター名がにんじゃわんこのデータでプライスを昇順に並べる

必要な数だけデータを取得

LIMIT データの件数;

「最大で何件取得するか」を指定

SELECT *
FROM purchases
LIMIT 5 ;

WHEREを使う場合は

SELECT *
FROM purchases
WHERE 条件
LIMIT 5 ;

このように書く
purchasesテーブルの[条件]から最大５件取得する

ちなみに

SELECT *
FROM purchases
ORDER BY price DESC
LIMIT 5 ;

ORDERとLIMITどちらも使う場合はこのように書く
purchasesテーブルのpriceカラムのデータを降順で最大５件取得する
という意味

検索結果の加工

データの取得だけでなく、データ同士を計算したりして、新しいデータを作り出すこと
例えば日ごとに何円お金を使ったかなど、お金の使い方を分析できる

重複データを省く

DISTINCT(カラム名)

指定したカラムの重複したデータを除く

SELECT DISTINCT(name)
FROM purchases;

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

四則演算

取得したデータにかけ算をしたり、割り算をしたりする

記号	意味
+	足す
−	引く
*	かける
/	割る

SEELECT name,price * 1.1
FROM purchases;

これはpriceカラムに1.1をかけている
この形で四則演算が可能

関数を使う

SUM（カラム名）

指定したカラムに保存されたデータの合計を計算することが可能
SQLで数値の合計を計算する場合は、SUMを使う

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

にんじゃわんこのレコードを検索し、レコードの数値の合計を計算する

平均を計算する

AVG(カラム名)

指定したカラムに保存されたデータの平均を計算することが可能
SQLで数値の平均を計算する

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

にんじゃわんこのレコードを検索し、レコードの数値の平均を計算する

データの数を計算する

COUNT(カラム名)

指定したカラムのデータの数を計算
智まりデータがいくつ保存されているかを集計

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

SELECT COUNT(*)
FROM purchases;
WHERE charscter_name = "にんじゃわんこ";

にんじゃわんこのレコードを検索し、レコードの数を計算する
"*" を使った場合、特定のカラムのデータの数ではなく、
nullの数も含めたレコードの数を計算

最大・最小を求める

MAX(カラム名)

MAX関数を用いると、指定したカラムのデータの中から最大のデータを取得可能

MIN(カラム名)

MINと言う関数を用いることで、最小のデータを取得可能

SELECT MAX(price)
FROM purchases
WHERE character_name = "にんじゃわんこ"

にんじゃわんこのレコードを検索し、レコードの中で最も大きい数値を取得

データのグループ化

グループ化

例えば、日付が同じデータごとに集計関数を使うこと

GROUP BY カラム名

指定したカラムで、完全に同一のデータを持つレコードどうしをグループ化

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

purchased_atカラムの同じデータ同士をグループ化し、グループごとにpriceを集計する

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

複数のカラムを使ってグループ化

GROUP BY カラム１,カラム2,...

GROUP BYは複数のカラム名を適用させることができ、カラム名同士をコンマ(,)で繋げる

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

purchased_atとcharacter_nameの複数の組み合わせをグループ化しpriceカラムをそれぞれ集計する

このような形でたとえば

[にんじゃわんこの2017-07-01]
[にんじゃわんこの2017-07-02]
[ひつじ仙人の2017-07-01]
[ひつじ仙人の2017-07-02]

...などなど、データの組み合わせの数だけグループができる

SUMの部分をCOUNT関数に変えて集計も可能

細かい条件でデータをグループ化

GROUP BYはWHEREと一緒に使うことで例えば、日付ごとの食費に使ったお金の合計を取得できる

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

たとえば

SELECT SUM(price),purchased_at,character_name
FROM purchases
WHERE category="食費"
GROUP BY purchased_at,character_name;

順番としては、まずWHEREでカテゴリーが食費のレコードを検索し、日付とキャラクターでグループ化すれば、
日ごとの食費に使ったお金がわかる

検索される順番を理解しておくことが大事

グループ化したデータをさらに絞り込む

HAVING

GROUP BYでグループ化したデータを更に絞り込みたい場合に使う

GROPUP BY カラム名
HAVING 条件;

WHEREとHAVINGの違い

• WHERE
  グループ化される前のテーブル全体を検索対象とする

• HAVING
  GROUP BYによってグループ化されたデータを検索対象とする

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

purchased_atの同じレコード同士をグループ化し、それぞれ集計する
さらに集計したデータの1000円以上のデータを出力している

ここまでのおさらい

SELECT goals
FROM players
WHERE name="ウィル";

nameカラムのレコード（横の行）がウィルのゴール数の数値をgoalsカラムから取得

SELECT *
FROM players
WHERE goals >14;

goalsカラムのからゴール数値が１４以上のものを全カラムから探して取得

２つのクエリ（命令）を使う

サブクエリ

SQLでは、クエリの中に他のクエリを入れることができる

SELECT name
FROM players
WHERE goals > (
  SELECT goals
  FROM players
  WHERE name = "ウィル"
);

段落の部分がサブクエリでウィルの得点数を取得している
おさらいのコードを１つにまとめている
（）内はセミコロン不要である

サブクエリを含むクエリの場合、サブクエリが実行された後、外側にあるクエリが実行される

SELECT name,goals
FROM players
WHERE  goals > (
 SELECT AVG(goals)
 FROM players
)
;

goalsの平均得点数を計算し、それよりも高いgoalsの数値に該当する
nameカラムとgoalsカラムを取得している

データを読みやすくする

AS

ASを使うことでカラム名などに別名を定義することができる

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

ウィルの得点数を取得し、結果を"ウィルの得点数"として
数値を表示する

SELECT SUM(goals) AS "チームの合計得点"
FROM players
;

goalsカラムの合計得点を集計し、"チームの合計得点"として数値を表示する

複数テーブル

SELECT *
FROM countries
WHERE rank < (
 SELECT rank
 FROM countries
 WHERE name ="日本" 
)
;

countriesテーブルより、nameカラムの日本のrankカラムよりランクの低い国
を取得する

テーブルを紐づける

テーブル同士を一緒に使うためには、外部キーが設定されたカラムと主キーが設定されたカラムを用意する
外部キーで他のテーブルにある主キーを指定することで、テーブル同士を紐付けることができる

テーブルを紐づけるメリット

カラムを追加するのではなく、紐づけることでデータの管理のしやすさがある
変更点が出たときに、１箇所だけ修正すれば良くなるなど

テーブルの結合

JOIN

複数のテーブルを1つに結合したいときに使う

ON

条件を指定して、テーブルAにテーブルBを結合する

結合したテーブルは1つのテーブルとしてデータを取得することができる

SELECT *
FROM テーブル１
JOIN テーブル２
ON テーブルA.カラム名 = テーブルB.カラム名

カラム名はそれぞれ外部キーと主キーとなっている

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

実行順序はまずテーブルが結合され、次に結合されたSELECTが実行される

複数テーブルでのカラムの指定

「テーブル名.カラム名」

複数のテーブルに同じカラム名が存在するときに指定する方法

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

これでそれぞれのnameカラムを指定できる

SELECT *
FROM players
JOIN countries
ON players.country_id = countries.id
WHERE players.name = "ウィル";

WHEREでも同じように「テーブル名.カラム名」で指定できる

全体実行順序の確認

1.テーブルの指定
FROM

2.結合
ON・JOIN

3.取得条件
WHERE

4.グループ化
GROUP BY

5.関数
COUNT、SUM、AVG、MIN

6.HAVING
HAVING

7.検索
SELECT・DISTINCT

8.順序
ORDER BY

9.LIMIT
LIMIT

複数テーブルの活用

SELECT players.name AS "選手名",teams.name AS "前年所属していたチーム"
FROM players
JOIN teams
ON players.previous_team_id = teams.id
;

複数テーブルでもそれぞれ取得したカラムにASで名前を変更できる

NULLの場合の取得

NULLを含んだ場合の結合

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

LEFT JOIN

FROMで指定したテーブルのレコードを全て取得する
外部キーがNULLのレコードもNULLのまま実行結果に表示される

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

これでNULLを含めたデータを取得できる

３つ以上のテーブル結合

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

JOINは1つのクエリで、複数回使用できるが、FROMは1度だけ書けば大丈夫

ここまでのおさらい

SELECT name AS "選手名" , height AS "身長"
FROM players
WHERE height >(
SELECT AVG(height)
FROM players
)
; 

まず平均身長をplayersテーブルから出力して、それよりも身長が高い
nameカラムとheightカラムを出して名前を変えている

SELECT *
FROM players
JOIN countries
ON players.country_id = countries.id
WHERE players.height >=180 
AND
 countries.name ="日本"
;

playersテーブルとcountriesテーブルを結合し
身長180以上かつnameカラムのレコードが日本のデータを出力

応用

SELECT countries.name AS "国名",AVG(goals) AS"平均得点" 
FROM players
JOIN countries
ON players.country_id = countries.id
GROUP BY countries.name
;

playersテーブルとcountriesテーブルを結合し
countriesテーブルのnameカラムでグループ化し
名前を変更した

データの追加

INSERT

テーブルにレコードを挿入

AUTO INCREMENT

多くの場合、idカラムにはAUTO INCREMENTという機能が使われる
データが作成されるときに自動で値が割り当てられる

INSERT INTO students (id,name,course)
VALUES (4,"Kate","Java");

それぞれのカラムに順番に挿入される

INSERT INTO students (name,course)
VALUES ("Kate","Java");

idカラムと値の4は、AUTO INCREMENT機能があるため省略可能

データの更新

UPDETE

データを更新したいときに使う
複数変更したいカラムがある場合、コンマ(,)で区切る

UPDATE students
SET name = "Jordan",course = "HTML"
WHERE id = 6;

指定したカラムに新しい値を設定し、更新するレコードをWHEREで指定する

WHEREで更新するレコードを指定しないとカラム内の全データが更新されてしまう

UPDATE実行後はデータを戻すことができないので、実行前に一度SELECTを実行して、
操作するデータを確認することが大事

データの削除

DELETE

レコードを削除するときはDELETEを使う
UPDATEと同様にクエリ実行後はレコードを元に戻すことができないので、
SELECTでレコードを確認してから削除を行う

DELETE FROM students
WHERE id = 7;

削除するレコードをWHEREで指定

WHEREで削除するレコードを指定しないとテーブル内の全レコードが削除されてしまう

４つの構文

• 取得
  SELECT カラムA,カラムB
  FROM テーブル名;

• 挿入
  INSERT INTO テーブル名（カラムA,カラムB）
  VALUES(値1,値2);

• 更新
  UPDATE テーブル名
  SET カラムA = 値1,カラムB = 値2
  WHERE 条件;

• 削除
  DELETE FROM テーブル名
  WHERE 条件;

参考書籍

Progate 学習コース「SQL」を参考にしています。