【SQL】基礎総まとめ

SQLの基礎についてまとめています。

SQLでできること

データの分析ができる。

SQLとは

SQLとは、クエリを書くための言語のこと

クエリとは・・・データベースに送る命令のこと

クエリ

SELECT

カラムの指定をする。

FROM

テーブルの指定をする。

あるテーブルのあるカラムからデータを取得するときの例

SELECT カラム名
FROM テーブル名;

複数カラムからデータを取得する場合

以下のようにカンマで区切るのみ！

SELECT カラム名, カラム名

全カラムのデータを取得する場合

*記号を使用する。

SELECT *

WHERE

レコードの指定をする。

〇〇カラムの値が指定した値のデータを取得する。

WHERE カラム名 = 値;

値が文字の場合は"〇〇"ダブルクウォートで囲う

WHEREでは比較演算子を使う事もできる。

WHERE price >= 1000;

LIKE演算子

ある文字を含むデータを取得する。

WHERE name LIKE 文字列;

ワイルドカード使用例
WHERE name LIKE "%コーヒー%";

LIKE演算子では=が不要になる

文字列にはワイルドカードを使用する。

ワイルドカードとは

例えば、「%コーヒー%」とすると

コーヒーを含めば、前後にどんな文字が存在していても抽出できる。

ブレンドコーヒー
コーヒーカップ
スターバックス・コーヒー・ジャパン

%を後ろだけにつけて「コーヒー%」とすると、抽出結果は以下となる。

コーヒーカップ

%を前だけにつけて「%コーヒー」とすると、抽出結果は以下となる。

ブレンドコーヒー

“”ダブルクウォートは%の外！

NOT演算子

WHEREの後、条件の前にNOTを付けることで、その条件を満たさないデータの取得ができる。

WHERE NOT name = "コーヒー";

WHERE NOT name LIKE "%コーヒー%";

NULLのデータを取得する

WHERE name IS NULL;

NULLでないデータを取得する
WHERE name IS NOT NULL;

普通に英語！w
でも、NULLとそうでないときとでNOTの付く位置がちがうので注意！

NULLのデータ取得は、以下のように=は使用できない。

WHERE name = NULL;

これはNG

「IS」を使う必要がある。

複数条件を指定する方法

AND演算子

WHERE 条件1
AND 条件2;

WHERE name = "コーヒー"
AND category = "食費";

OR演算子

WHERE 条件1
OR 条件2;

データの並び替え

ORDER BY

SELECT カラム名
FROM テーブル名
ORDER BY カラム名 並べ方

昇順
ORDER BY カラム名 ASC;

降順
ORDER BY カラム名 DESC;

取得データ件数制限

LIMIT

SELECT *
FROM name
LIMIT 3;

重複データを省く

DISTINCT

検索結果から、指定したカラムの重複するデータを除くことができる

DISTINCT(カラム名)

例
SELECT DISTINCT(name)
FROM purchases;

purchasesテーブルからnameの重複を除いてデータを取得する事ができる。

四則演算

データに四則演算して、取得する方法


SELECT name, price * 2
FROM purchases;

集計関数

SUM関数

合計を計算する。

SUM(カラム名)

例
SELECT SUM(price)
FROM purchases;

priceカラムの合計を取得できる。

WHEREと組み合わせることで、絞り込んだカラムの合計を取得することもできる。

SELECT SUM(price)
FROM purchases
WHERE name = "punks";

nameカラムをpunksに絞ったデータのpriceの合計を取得できる。

SUMでSELECTが必要なのは少し不思議な感じがするけど、
SELECTで取得したカラムにSUMを適用していると考えれば納得！

AVG関数

平均を計算する。

AVG(カラム名)

例
SELECT AVG(price)
FROM purchases;

AVGもまたSUMと同様にWHEREと組み合わせることができる。

SELECT AVG(price)
FROM purchases
WHERE name = "ソルサ";

COUNT関数

データの合計でなくて、数を計算する。

COUNT(カラム名)

COUNT関数では、対象のカラムがNULLだった場合、カウントされない。

NULLも含めてカウントする場合は以下のように、*とする。

COUNT(*)

こうすることでレコードの数を取得できる。

COUNTもまた、SUM、AVGと同じようにWHEREと組み合わせて使用が可能。

SELECT COUNT(*)
FROM purchases
WHERE name = "ソルサ";

最大・最小を抽出する

MAX・MIN関数

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

レコードのグループ化

GROUP BY

FROMの後ろに追加することでレコードをグループ化できる。

GROUP BY カラム名

GROUP BYを使用する時のルール

GROUP BYを用いる場合は、SELECTで使えるのは以下2点のみ

GROUP BYで指定しているカラム名
集計関数（SUM, AVG, COUNT）

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

以下はNG
SELECT price, name
FROM purchases
GROUP BY name;

複数のグループ化をする場合

GROUP BY カラム名1, カラム名2, カラム名3

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

関数の記述の順番

WHEREとGROUP BYを組み合わせて使用する場合は、GROUP BYはWHEREの後に記述する。

すると、処理の順番は以下のようになる。

検索：　WHERE
グループ化：　GROUP BY
関数：　COUNT・SUM・AVG・MAX・MIN

先に絞り込んでから、グループ化して、集計される。

グルーブ化した中で(GROUP BY)、条件を絞って抽出したデータ(WHERE)から更に絞り込む

HANING

GROUP BY カラム名
HAVING 条件;

例
HAVING SUM(price) > 1000;

WHEREとHAVINGの違い

WHERE：検索対象は、グループ化される前のテーブル全体

HAVING：検索対象は、GROUP BYによってグループ化されたデータ

サブクエリ

クエリを２つに別けなくても、１つにまとめることができる。

以下のテーブルを基に例を考えていきます。

drinksテーブル

カラム名	データ
id	データの番号
name	ドリンク名
size	サイズ
price	価格

SELECT price
FROM drinks
WHERE name = "カフェアメリカーノ"

これで、カフェアメリカーノのprice一覧を取得できる。

SELECT name
FROM drinks
WHERE price > 300;

これで、priceが300以上のドリンク一覧を取得できる

これを１つにまとめることができる。

SELECT name
FROM drinks
WHERE price > (
  SELECT price
  FROM drinks
  WHERE name = "カフェアメリカーノ"
);

これで、カフェアメリカーノのpriceを取得後、そのprice以上のドリンク一覧を取得できる。

サブクエリは、サブクエリ内の処理が先に実行される。

サブクエリの中に関数を使用

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

サブクエリを使うと、後に出てくるテーブル結合をしなくても、別テーブルからのデータ取得ができる。

AS

カラム名に別名を定義することができる。

SELECT カラム名 AS "定義したい名前"

複数テーブルを紐付ける

まず、親となるテーブルに子の外部キーを追加する。

テーブルの結合

JOINを使うと複数のテーブルを結合させ、データを取得することができる。

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

結合条件って何だ？

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

例
ON players.county_id = countries.id
キーで結合する。

ここで指定したカラム同士をひも付けることができる。

ちなみに、テーブルAとテーブルBの関係（順番）はSELECT条件と関係ない。
SELECT条件でテーブルAの絞り込みをしてもいいし、テーブルBの絞り込みをしてもいい

JOINを含んだクエリの処理の流れ

初めにJOINが実行され、その後結合されたテーブルに対しSELECTが実行される。

JOIN, ONはクエリの中でFROMの次に優先度が高い

結合したテーブルに同じ名前のカラムが存在する場合、

「テーブル名.カラム名」として指定する。

例えばnameのようなカラムの場合

SELECT players.name, countries.name

外部キーがNULLだとレコードの結合ができない。

JOINはNULLのレコードを取得できない。

NULLのレコードも取得したい場合

LEFT JOIN

LEFT JOINを使うと外部キーがNULLでも、NULLのままレコードを結合できる。

結合したテーブルのNULLレコードができる。

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

3つ以上のテーブル結合

3つ以上のテーブルを結合する場合は以下のように1つのクエリにJOINを複数使用する。

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

データ（レコード）の追加

INSERT INTO テーブル名 (カラム名)
VALUES(カラムに追加する文字)

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

多くの場合、idカラムはAUTO INCREMENTという、自動で番号を割り当てる機能があるため省略して以下のように書くのが一般的。

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

データの更新

UPDATE テーブル名
SET 変更したい値
WHERE 更新するレコード

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

WHEREでレコードを指定しなかった場合、全てが変更されてしまう。

データの削除

DELETE FROM テーブル名
WHERE 更新するレコード

例
DELETE FROM students
WHERE id = 7;

DELETEもWHEREと同様に

WHEREでレコードを指定しなかった場合、全てが変更されてしまう。

四則演算

SELECTやORDET BYに四則演算を組み込む事もできる。

利益を求めたいとき、price – costで算出ができる。

SELECT name, price - cost
FROM items
ORDER BY price - cost DESC;