基本情報技術者試験　データベース編②


#初めに
[前回](https://qiita.com/reo0612/items/0d9bb0635e62b4891e85)の続きを書いていきます。

#SQLでDBを操作する
`SQL`というのは、`DBMS(データベース管理システム)へ命令するために用いられる言語`のことで
以下のような流れでユーザーがDBMSへ命令し、データを抽出したりなどを行います。
![SQLの例.001.jpeg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/b6823e21-b936-d0ed-f785-ee7210632fa5.jpeg)
様々な命令文がSQLには用意されており、

・表(テーブル)を定義するCREATE文
・行(レコード)を挿入するINSERT文
・削除するDELETE文
・行(レコード)の一部を更新するUPDATE文

などなど。

これらの命令は、`スキーマの定義や表の作成といった定義を担当するデータ定義言語DDL`と
`データの抽出や挿入、更新、削除といった操作を担当するデータ操作言語DML`に分けることができます。

SQLはこの２つの言語によって構成されています。

命令文の中で最も特徴的なのが、`SELECT文`です。
これは様々な条件を付与することで、柔軟にデータを抽出することができます。

データというのはただ貯め込むだけでは意味を持ちません。

何らかの条件付け(売り上げ上位の商品など)を行い
抽出することで初めてデータに意味がくっついてきます。

#SELECT文の基本的な書式
SELECT文によるデータ抽出の基本として

・`どのような条件で?`
・`どの表から?`
・`どの列を取り出す?`

これらを指定することによってDBから様々なデータを取得できます。

例えば、以下のような表があったとしてDBMSに命令したとします。

・商品表
![スクリーンショット 2021-07-13 12.55.55.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/7ed3056a-c358-85b4-30d5-d54a9e59e0b3.png)
・`どのような条件で?`
単価50以上のもの

・`どの表から?`
商品表から

・`どの列を取り出す?`
商品名と単価

結果は以下のようになりました↓
![スクリーンショット 2021-07-14 15.36.25.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/af24e9df-cedd-7b41-ce39-2f5bab732d47.png)
このように命令に合ったデータを取り出せます。

実際の書式はこんな感じで↓

```sql
SELECT 列名 FROM 表名 WHERE 条件
```

先ほどの命令をSQLで書くとこんな感じです↓

```sql
SELECT 商品名, 単価 FROM 商品表 WHERE 単価>=50
```

#関係演算とビュー表
前回の記事で正規化が出てきました。

DBに貯め込んだデータに矛盾や重複が発生しないように
表を最適化するといったものでしたね。

しかし、どんどん分割されていった正規化済みの表を見てみると
番号が並んであるだけだったり正直、分かりにくかったりします。
![スクリーンショット 2021-07-14 15.52.15.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/aaf6f2a5-4308-9f92-8625-6c75801bd9bb.png)
ここまでの話では「`データをいかに効率的に貯め込むか`」でしたが
そのデータを活用できなきゃ意味がありません。

そういう時に`関係演算`が役に立ちます。

関係演算は、表と表をくっつけて新しい表を作ったり
表の中から特定の行や列を取り出したりなどが出来ます。

というわけで、関係演算はDBに貯め込んだデータを使って様々な表を生成できます。
このような、仮想的に作る一時的な表のことを`ビュー表`と呼びます。

###・特定の列を抽出する(射影)
`射影`は、`表の中から特定の列を取り出す関係演算`です。

SQLでは以下のように記述し、射影を行います。

```sql
SELECT 商品名 FROM 商品表
```
・商品表
![スクリーンショット 2021-07-13 12.55.55.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/7ed3056a-c358-85b4-30d5-d54a9e59e0b3.png)
射影を行う事によって、「商品名」が取り出されました↓
![スクリーンショット 2021-07-14 16.03.01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/d9508af0-aa3e-4675-f49c-bd8d27ba8309.png)

###・特定の行を抽出する(選択)
`選択`は、`表の中から特定の行を取り出す関係演算`です。

SQLでは以下のように記述し、選択を行います。

```sql
SELECT * FROM 商品表 WHERE 単価>70
```
SELECT文で選択を行うには、`WHERE句`を用いて
取り出したい行の条件を指定します。

・商品表
![スクリーンショット 2021-07-13 12.55.55.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/7ed3056a-c358-85b4-30d5-d54a9e59e0b3.png)
選択を行う事によって、条件に合った行が取り出されました↓
![スクリーンショット 2021-07-14 16.12.10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/d286806c-e664-a347-92a3-545b9b557a08.png)
先ほどもチラッと出てきましたね。

因みに、

```sql
SELECT * FROM ~
```
のように列名のところに*(アスタリスク)を使うと`全ての列が抽出対象`になります。
これは表の構造をそのままで、特定の行のみを取り出したい時に便利です。

WHERE句は`比較演算子`を用いて条件を指定していきます。
![スクリーンショット 2021-07-14 16.29.26.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/7790054b-fd70-4616-ebbd-34648e29844f.png)

###・条件を組み合わせて抽出する
例えば、「50以上100未満の商品」など複数の条件を
組み合わせて抽出したいとします。

その場合は`論理演算子`の

・AND
・OR
・NOT

の３つを用います。

以下の表から抽出していきたいと思います。

・商品表
![スクリーンショット 2021-07-13 12.55.55.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/7ed3056a-c358-85b4-30d5-d54a9e59e0b3.png)
#####○AND

```sql
SELECT * FROM 商品表 WHERE 単価>30 AND 単価<60
```
単価が30より大きく、かつ60未満のデータが取り出されました↓
![スクリーンショット 2021-07-14 16.46.05.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/589ef1a5-dd41-37ed-4e5b-8ceb51c3b79b.png)
#####○OR
```sql
SELECT * FROM 商品表 WHERE 単価<30 OR 単価>60
```
単価が30未満または60より大きいデータが取り出されました↓
![スクリーンショット 2021-07-14 16.49.22.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/8b81e896-63aa-ac8e-f45f-6a020ac4f788.png)
#####○NOT
```sql
SELECT * FROM 商品表 WHERE NOT(単価<30 OR 単価>60)
```
単価が30未満でもなく、60より大きくないデータが取り出されました↓
![スクリーンショット 2021-07-14 16.50.38.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/07238ddf-cba3-0eb0-939f-7d0b8ed33c80.png)

###・表と表を結合する(結合)
`結合`は、`表と表を結合させる関係演算`です。

結合を行うには、FROM句の中にくっつけたい表の名前を羅列して
WHERE句で結合させたい列を指定します。

```sql
SELECT * FROM 表名, 表名 WHERE 表名.列名=表名.列名
```
ここで注目するのが「`表名.列名`」の記述です。
これは`どの表に所属する列か`を表しています。

列を特定するために必要なだけで重複しない列名であれば
「`表名.`」の部分は`省略してもOK`です。

では実際に以下の表を結合していきましょう。

・受注表
![スクリーンショット 2021-07-14 17.13.53.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/3cbc8717-286c-9d69-e82d-6ec59028f2db.png)
・顧客表
![スクリーンショット 2021-07-13 14.24.04.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/b5969ce0-d492-c7bc-366b-1e08617c3569.png)

```sql
SELECT * FROM 受注表, 顧客表 WHERE 受注表.顧客コード=顧客表.顧客コード
```
「顧客コード」を使って各行が結びつけられました↓
![スクリーンショット 2021-07-14 17.13.55.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/e5a38ca9-1467-b1df-3d11-9240c032e85d.png)
ちなみに結合させる列名が同じ名前同士である必要はありません。

###・データを整列させる
抽出結果を整列させたい場合は`ORDER BY句`を用います。

```sql
ORDER BY 列名 ASC(またはDESC)
```
昇順で並べたい場合は`ASC(省略可)`で
降順で並べるには`DESC`を記述します。

実際に以下の表の「単価」を並び替えてみます。

・商品表
![スクリーンショット 2021-07-13 12.55.55.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/7ed3056a-c358-85b4-30d5-d54a9e59e0b3.png)

```sql
SELECT * FROM 商品表 ORDER BY 単価
```
「単価」が昇順で並べられました↓
![スクリーンショット 2021-07-15 9.13.44.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/ffc5e6ef-b828-d599-f332-94e034d52f5d.png)
記述する時は、ASCは省略できるので
わざわざ記述していません。

因みに複数の列を並び替えることも可能です。

以下の表で複数の列を整列させてみましょう。

・受注表
![スクリーンショット 2021-07-14 17.13.53.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/3cbc8717-286c-9d69-e82d-6ec59028f2db.png)

```sql
SELECT 顧客コード, 受注No, 受注日時 FROM 受注表 ORDER BY 顧客コード, 受注日時 DESC
```
「顧客コード」は昇順に、「受注日時」は降順に並べ替えられました↓
![スクリーンショット 2021-07-15 9.22.30.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/e173f0fa-c0b0-3953-ebe3-5f7f805f5619.png)

###・集計を行う
データを取り出す際に集計を行うことができ、その為の様々な関数(`集合関数`)が用意されています。

これを用いると、列の合計値や最大値、行数(レコード件数)などを求めることが出来ます。
![スクリーンショット 2021-07-15 9.38.57.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/69745375-bf5c-d25a-69bf-80c6e0ad9b16.png)
例えば、扱っている商品の平均単価を求めたいなら以下のように記述します。

・商品表
![スクリーンショット 2021-07-13 12.55.55.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/7ed3056a-c358-85b4-30d5-d54a9e59e0b3.png)

```sql
SELECT AVG(単価) FROM 商品表
```
「単価」の平均を求めることが出来ました↓
![スクリーンショット 2021-07-15 9.49.37.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/932e57fb-15bd-2259-e13b-6134ae8a469f.png)

###・データをグループ化する
`グループ化`というのは、`特定の列を指定し、その中身が一致する項目をまとめて扱うこと`です。

これは集合関数と組み合わせることによって更に強力なものとなります。

グループ化には、`GROUP BY句`を用います。

```sql
GROUP BY 列名
```
実際に以下の表で、グループ化と集合関数を用いていきましょう。

・商品分類表
![スクリーンショット 2021-07-15 10.14.01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/ae30995b-a316-ad49-a9e3-642bb711b523.png)

```sql
SELECT 分類, AVG(単価) FROM 商品分類表 GROUP BY 分類
```
分類ごとの平均単価が求められました↓
![スクリーンショット 2021-07-15 10.17.10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/592a4e7d-205f-1eac-5e5a-613feaed4bae.png)

###・グループに条件を付けて絞り込んでいく
グループ化を行なった際に、これに条件を付けて取り出すグループを絞り込むことが出来ます。

条件を付けて絞り込むには`HAVING句`を用います。

```sql
GROUP BY 列名 HAVING 絞り込み条件
```
以下の表から絞り込み条件を記述して取り出していきます。

・商品分類表
![スクリーンショット 2021-07-15 10.14.01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/ae30995b-a316-ad49-a9e3-642bb711b523.png)

```sql
SELECT 分類, AVG(単価) FROM 商品分類表 GROUP BY 分類 HAVING AVG(単価)>70
```
分類ごとの平均単価を求め、その中から平均単価が70より大きいグループを取り出せました↓
![スクリーンショット 2021-07-15 10.31.28.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/743730/ae6bb490-b2ba-74a6-13a3-80fec95149df.png)
#おわりに
今回はここまでです。

間違っている部分や気になる部分があれば
コメントして下さると有り難いです。

[次の記事](https://qiita.com/reo0612/items/6fa0429606427b40ef15)です。