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カラムの食費を選ぶ
「食費」のようなデータには「データ型」と呼ばれるルールがあり,<font color="red">クォーテーション</font>で囲む


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

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

<font color="red">※</font> 日付データはダブルクォーテーションまたはシングルクォーテーションで囲む


## 比較演算子

大小比較の記号
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でカテゴリーが食費のレコードを検索し、日付とキャラクターでグループ化すれば、
日ごとの食費に使ったお金がわかる


<font color="red">検索される順番を理解しておくことが大事</font>

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


#### 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」を参考にしています。