SQLの基本知識

SQL学習の基本


SQL学習の構築の手順

手順概要

1.Amazon RDSを用いてMySQLが稼働するデータベースサーバーの初期設定を行う
2.PCにDBServerをインストールする
3.DBeverからデータベースサーバーへ接続する


>DBeaverとは

パソコンにインストールして利用するSQLクライアントの一種で、マウスなどで簡単に操作できるソフト（GUI）

>SQLクライアントとは

ネットワークを通じてデータベースサーバーに接続するためのソフトウェア。
SQLの実行や記述をサポートしているだけでなく、GUIで設定や操作を行えるため、SQLの実務経験量に関わらず、開発の現場で使用されている。

>Amazon RDSとは

AmazonRDSとはクラウド上でRDBを簡単に構築・運用できる便利なサービス。

>RDBとは

Relational DataBaseの略称。
関係データベースと訳され、データを複数の表で管理し表と表の関係を定義することで複雑なデータの関係性を表現できる



#### DBeaverからDBサーバーへ接続する
1.インストールしたDBeaverを起動する
2.「新しい接続」アイコンをクリック
3.MySqlを選択
4.一般タブを開く
・Server Host
RDSのDBサーバーのエンドポイント

以下AWSのRDSで作成したデータベースを参照。
![4ca3cfbc-ed87-4c77-9929-b3658332d4e6.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1314502/770fdbbe-0cb8-89d4-70ba-dce150f4db3a.png)

・ユーザー名
RDSのDBサーバーのマスタユーザ名(RDSで設定した名前）
・パスワード
RDSのDBサーバーのマスタパスワード（RDSで設定したもの）

5.テスト接続を確認



・データベース
　・データベースの作成（指定した名前のデータベースを作成）
　・データベースの一覧の取得（作成済みのデータベースの一覧を取得）
　・データベースの削除（指定した名前のデータベースを削除）
・テーブル
　・テーブルの作成（指定した名前のテーブルを作成）
　・テーブルの一覧の取得
　・テーブルの削除
・データ
　・データの追加
　・データの削除

### データベースの操作

１.データベース
DBeaverでの操作

![95db49fa-c8b4-429f-a977-8e81ac0e16b8.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1314502/79040f06-9653-3305-d106-c5c35ed3324a.png)

>データベースの作成

#### CREATE DATABASE DB名;
指定した名前でデータベースの作成


>データベース一覧の取得

#### SHOW DATABASE;
データベース一覧の取得

![7818b501-e48e-4051-ab3b-7b2a170599e3.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1314502/793073f3-b641-b9c1-bc98-e52f5290ff1d.png)

>データベースの削除

#### DROP DATABASE DB名;

データベースの削除

### テーブルの操作

・テーブル
データを保存するための「表」を指す
・カラム
テーブルの縦一列
・レコード
テーブルの横一列
・フィールド
各々の入力項目を指す。
「セル」のイメージ

>テーブルの作成

#### CREATE TABLE DB名.テーブル名（カラム名　データ型, カラム名　データ型)

```
create table testdb.users(id int, name varchar(10));
```

>テーブル一覧の取得

#### SHOW TABLES FROM DB名;
```
show tables from testdb;
```

対象DBの中から、作成済みのテーブルの一覧を取得
![スクリーンショット 2022-06-18 11.47.10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1314502/73e1b3de-3d69-acbc-ae11-9d2515471ce9.png)

>テーブルの削除

#### DROP TABLE DB名.テーブル名;

```
drop table testdb.users;
```

### データの操作

>データの追加

#### INSERT INTO DB名.テーブル名 VALUES (値①, 値②, ...)

テーブルに含まれるすべてのカラムに値を指定して、データの追加する。記述する値の順序は、カラムの順序と同じ。

![スクリーンショット 2022-06-18 11.59.55.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1314502/a5c8e00a-28f8-b074-76f7-769ae527f9c4.png)

>データの取得

```
select * from testdb.users;!
```
![スクリーンショット 2022-06-18 12.02.30.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1314502/1a224a98-b068-c5d6-b63c-ea78dbc66fc2.png)

>複数のデータを追加

#### INSERT INTO DB名.テーブル名 VALUES (value1, value2, ...), ・・・
テーブルに含まれる全てのカラムに値を指定して、複数のデータをまとめて追加します。記述する値の順序は、カラム名の順序と同じにする。
```
insert into testdb.users values (3, 'スズキ'),(4, 'John'), (5, 'メアリー');
```

>データの削除

#### DELETE FROM DB名.テーブル名 WHERE 条件式;

指定したDBの対象テーブルに格納されている全てのデータを削除する
```
delete from testdb.users where id=2;
```
![スクリーンショット 2022-06-18 12.11.22.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1314502/45513ce3-c071-86e6-d5f3-f18329cbf077.png)

>格納されているデータ全て削除
#### DELETE FROM DB名.テーブル名
```
delete from testdb.users;
```
![スクリーンショット 2022-06-18 12.14.41.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1314502/13725bcf-8c77-3c29-aa30-fbd0252e8df2.png)

## データの取得

> 一つのカラムのデータの取得
#### SELECT カラム名① FROM DB名.テーブル名
```
select email from training1.users;
```
>全てのカラムの値を取得
#### SELECT * FROM DB名.テーブル名
```
select * from training1.users;
```
## コメントの書き方
#### #コメント
#### --コメント
#### /*コメント*/


>特定のデータを取得
#### SELECT * FROM DB名.テーブル名　WHERE 条件式
指定した条件式をみたす<b>全て</b>のデータを取得する
WHEREの次に記述する条件式には、カラム名、<b>演算子</b>、関数などを記述
```
select * from training1.users where gender='男';
```
#### SELECT カラム①, カラム②, ... FROM DB名.テーブル名 WHERE 条件式
指定した条件式をみたす<b>特定</b>のデータを取得する
```
select name, email, tel from training1.users where email='jiro.tanaka@ccc.com';
```

##演算子
条件式に入れる演算子は以下のように存在する。
取り出したいデータによって条件を変えよう！

>演算子の種類

演算子とは、加算、減算、乗算、除算、剰余などを計算すること。
＜演算子一覧＞

|演算子	|使用例	| 意味 |
|----|----|----|
|+|A + B|AにBを加える (加算)|
|-|A - B|AからBを引く(減算)|
|*|A * B|AにBをかける (乗算)|
|/|A / B|AをBで割る (除算)|
|DIV|A DIV B|AをBで割る(整数除算)|
|%|A % B|AをBで割った余り (剰余)|
|MOD|A MOD B|AをBで割った余り (剰余)|

（例）
#### select A + B;
#### select A - B;
```
select height + weight from training1.users;
select weight - height from training1.users;
```

>比較演算子
```
select 5 between 2 and 10, 1 not between 2 and 10;
```
>論理演算子

>データの並び替え

SELECT 文の中で、ORDER BY を使用することで、指定したカラムの値を並び替えたデータを取得することができる。

#### SELECT カラム名①, カラム名②, ... FROM DB名.テーブル名 ORDER BY カラム名③ 並び替え条件

```
select id, name, height from training1.users order by height asc;
```
・ascとは、昇順に並び替える。
・descとは、降順に並び替える。

>複数のカラムを並び替える
#### ORDER BY カラム名① 並び替え条件①, カラム名② 並び替え条件②

'''
select * from training1.users order by gender desc, age asc;
'''
最初にgenderカラムの降順で並び替えて、さらにageカラムの昇順で並び替える。

>whereとorderとの組み合わせ
#### WHERE 条件式 ORDER BY カラム名 並び替え条件
```
select * from training1.users where age >= 20 order by gender asc, age desc;
```

## より複雑なデータを取得する


>csvデータをインポートする

![スクリーンショット 2022-06-18 14.54.27.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1314502/0a1ebb27-93c7-7999-6186-abd5ee0c0e98.png)

![スクリーンショット 2022-06-18 14.55.43.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1314502/15fbf213-c661-670e-ecb7-75fc069dc160.png)


>AND ORで条件を指定し、データを抽出
#### SELECT カラム名①, カラム名②, ... FROM DB名.テーブル名 WHERE 条件式① AND/OR 条件式② AND/OR ...

WHEREの条件式を、ANDまたはORを使って組み合わることができる。
条件式をみなしたい場合は、括弧で囲う。

例
```
select * from training1.transactions where purchaseDate >= '2020-05-05' and (item='Beef' or item='Pork');
```

![スクリーンショット 2022-06-18 15.07.35.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1314502/da63e7fc-a386-0137-1edf-111af749cc34.png)

> BETWEEN で指定の範囲を条件としてデータを抽出

#### SELECT カラム名①, カラム名②, ... FROM DB名.テーブル名 WHERE カラム名③ BETWEEN 最小値 AND 最大値

最小値以上、最大値以下の範囲に含まれるデータを取得する
（例）
purchaseDataカラムの値が、2020-05-01 〜 2020-05-08 の範囲のデータを取得する

```
select * from training1.transactions where purchaseDate between '2020-05-01' and '2020-05-08';
```

>IN 

値を指定してデータを抽出する

#### SELECT カラム名①, カラム名②, ... FROM DB名.テーブル名 WHERE カラム名③ IN (値①, 値②,...)

（例）item カラムの値が、Coffee(S), Coffee(M), Coffee(L) のいずれかに等しいデータを取得する。

```
select * from training1.transactions where item in ('Coffee(S)', 'Coffee(M)', 'Coffee(L)');
```
![スクリーンショット 2022-06-18 15.21.53.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1314502/b3c13f5e-8b85-48cf-20aa-fda8ea9aeff9.png)

>NOT IN
それに該当しないデータを抽出する
```
select * from training1.transactions where item not in ('Coffee(S)', 'Coffee(M)', 'Coffee(L)');
```
![スクリーンショット 2022-06-18 15.22.19.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1314502/16212060-0a1f-1505-ebfa-5d58235171a2.png)

>LIKE

該当するものが<b>含まれる</b>データを抽出する
#### SELECT カラム名①, カラム名②, ... FROM DB名.テーブル名 WHERE カラム名③ LIKE パターン

（例）
item カラムの値が、Coffeeという文字列から始まるデータを取得する
```
select * from training1.transactions where item like 'Coffee%';
```
![スクリーンショット 2022-06-18 15.24.22.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1314502/5f873068-45bf-d3c0-afb0-11ba3a3550cd.png)

>LIMIT 
レコード数の<b>上限</b>を設定してデータを抽出する
#### SELECT カラム名①, カラム名②, ... FROM DB名.テーブル名 LIMIT 最大件数
（例）
最大10件までの購入履歴を取得する
```
select * from training1.transactions limit 10;
```
![スクリーンショット 2022-06-18 15.26.59.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1314502/d388d9f4-07af-6675-8245-9959aa9c194f.png)

>GROPU BY

カラム名のの値を基準にグループ化して、データを取得する
#### SELECT カラム名①, カラム名②, ... FROM DB名.テーブル名 GROUP BY カラム名①, カラム名②,...

（例）
buyerごとのsalesの合計値を取得する
```
select buyer from training1.transactions group by buyer;
```
![スクリーンショット 2022-06-18 15.33.41.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1314502/f2af4a1a-3dd2-6110-3585-27ab0b9d6e22.png)


```
select buyer, sum(sales) from training1.transactions group by buyer;
```
![スクリーンショット 2022-06-18 15.34.14.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1314502/1a010b24-0962-1460-bc1b-8cc3c0079ff9.png)

>HAVING

グループ化したデータに対してさらに条件を指定してデータを絞り込みたい時にHAVINGを使用する

#### SELECT カラム名①, カラム名②, ... FROM DB名.テーブル名 GROUP BY カラム名①, カラム名②,... HAVING 条件式
#### SELECT カラム名①, カラム名②, ... FROM DB名.テーブル名 WHERE 条件式① GROUP BY カラム名①, カラム名②,... HAVING 条件式②

（例）
buyerごとのsales の合計値が1500円以上のデータを取得する
```
select buyer, sum(sales) from training1.transactions group by buyer having sum(sales) >= 1500;
```
![スクリーンショット 2022-06-18 15.41.41.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1314502/afab3aac-e8e0-b474-2faf-5dcdacdb1489.png)


## SQLの関数

>SUM
合計値を取得する
（例）
商品ごとの購入金額の合計値を降順で取得する
```
select item, sum(sales) from training1.transactions group by item order by sum(sales) desc;
```
![スクリーンショット 2022-06-18 15.50.31.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1314502/3cc506f7-eea6-ee60-5e73-8c730d11f14a.png)

>COUNT
指定したカラムの行数を抽出する
（例）
購入者の延べ人数を取得する
```
select count(buyer) from training1.transactions;
```
![スクリーンショット 2022-06-18 15.50.06.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1314502/8ea4b0d9-c0a8-6172-0c7b-c91730394640.png)

>AVG
指定したカラムの平均値を抽出する

（例）
購入者ごとの購入金額平均値を昇順で取得する
```
select buyer, avg(sales) from training1.transactions group by buyer order by avg(sales) asc;
```
![スクリーンショット 2022-06-18 15.50.52.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1314502/420fe76a-29e1-faf6-6848-927ce657a9c2.png)

>MAX, MIN

指定したカラムの値の最大値と最小値を抽出する
（例）
購入履歴の最大値と最小値を抽出
```
select max(sales), min(sales) from training1.transactions;
```

![スクリーンショット 2022-06-18 15.52.41.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1314502/2557c7d6-d736-5a44-7e60-632ff834825b.png)