DB、SQL 勉強まとめ

##### 投稿の目的
- 個人的な勉強のためのメモとして上げているので暖かい目で見守ってください。

## RDBMS(リレーショナルデータベースマネージメントシステム)
- RDBを管理するためのソフトウェアの総称。
- RDBMSの種類
	- Oracle
	- Mysql
	- Microsoft SQL server
	- PostgreSQL
	- SQLite


## MySQL 操作　

### mysql -h ホスト名 -u ユーザー名 -D データベース名
- mysqlでデータベースに接続する。





## SQL(ストラクチャードクエリランゲージ)
- SQLとはデータベースの操作などを行うための言語です。

### データを操作するもの

#### show 
- テーブルやカラムの情報を確認できる。

```sql
show tables;  /* 現在のDBのテーブル一覧を表示 */

show columns from テーブル名; /* 指定したテーブルのカラム情報を表示 */
```

#### SELECT
- データの取得する際に行う。

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

#### INSERT
- データを新規で追加する際に行う。
- カラム名は複数指定することができ、カラム数と同じ数をVALUESに指定します。

```sql
INSERT INTO テーブル名(カラム名) VALUES(追加するデータ);
```

#### UPDATE
- データ更新する際に行う。
- 条件を指定しないと指定したカラムのデータすべてに変更が適用されます。

```sql
UPDATE テーブル名 SET カラム名 = 'データ' WHERE 条件
```

#### DELETE
- データを削除する際に行う。
- DELETEもUPDATE同様条件を指定しないと指定したテーブルのデータすべてが削除されます。

```sql
DELETE FROM テーブル名 WHERE 条件
```

### データを絞り込む

#### WHERE
- 条件を付けてデータを絞り込む。
- 「=(イコール)」で値を指定できる。

```sql
SELECT * FROM shop WHERE id = 20;
```

#### LIKE
- %は何でも何文字でも曖昧検索が可能。
- _は一文字曖昧検索が可能。
- 「NOT」を付ける事で不要なデータを除去ができる。

```sql
/* 指定された文字が含まれているのを表示 */
SELECT * FROM shop WHERE shop_name LIKE '%さん%'; 
SELECT * FROM shop WHERE shop_name LIKE '%屋さん%'; 
```

```sql
/* 末尾に指定された文字が含まれている */
SELECT * FROM shop WHERE shop_name LIKE '%屋さん';
```

```sql
/* 先頭に指定された文字が含まれている */
SELECT * FROM shop WHERE shop_name LIKE 'ピザ%';
```

```sql
/* 先頭と末尾に指定された文字が含まれている */
SELECT * FROM shop WHERE shop_name LIKE 'ピザ%さん';
SELECT * FROM shop WHERE shop_name LIKE 'お%さん';
SELECT * FROM shop WHERE shop_name LIKE 'お%屋さん';
```

```sql
/* 指定されている文字が含まれていたら除去 */
SELECT * FROM shop WHERE shop_name NOT LIKE '%ピザ%';
```

```sql
/* この間は何文字かを指定 */
SELECT * FROM shop WHERE shop_name LIKE 'お__屋さん';
```

#### IN
- 複数の値を指定できる。

```sql
SELECT * FROM shop WHERE shop_name IN('おそば屋さん', 'おうどん屋さん');
```

#### or
– 結果は同じだけど、書き方が複雑になるので同一カラムで複数の値を検索する場合は「or」では検索しない

```sql
SELECT * FROM shop where shop_name = 'おそば屋さん' or shop_name = 'おうどん屋さん';
```

#### between
- 指定した範囲でデータを検索する。
- NOTは否定を指しています。

```sql
SELECT * FROM shop where id between 2 AND 7;

SELECT * FROM shop where id NOT between 2 AND 7;
```

#### 、 <、 >=、 <= (不等号)

```sql
SELECT * FROM shop where id >= 10;
```

#### and、or、!
- andはかつ、両方という意味です。
- orはまたは、どっちかという意味です。
- !は否定を指しています。

```sql
SELECT * FROM shop where id = 2 AND shop_name = 'お寿司屋さん';

SELECT * FROM shop where id = 2 or shop_name = 'お寿司屋さん';

SELECT * FROM shop where id != 2 AND shop_name = 'お寿司屋さん';
```

#### -- != は別の書き方もあります。

```sql
SELECT * FROM shop where id <> 2;
```

### データを並び替える

#### Order by(オーダーバイ)
- Order byは並び替えを行うことができます。
- ORDER BYはカラムの後にasc(昇順), desc(降順)を指定できる。デフォルトはasc

```sql
SELECT * FROM shop ORDER BY id;

SELECT * FROM shop ORDER BY id DESC;
```

- WHEREで絞った後に並べ替えも可能

```sql
SELECT * FROM shop WHERE id > 2 ORDER BY id DESC;
```

### データを集計

#### count
- 件数を算出する

```sql
SELECT COUNT(*) FROM shop;

SELECT COUNT(shop_name) FROM shop;
```

- このSQLお店の名前で重複したものをカウントしない。

```sql
SELECT COUNT(distinct shop_name) FROM shop;
```

### グループ化

#### Group by
- データを集計する軸となるカラムを指定する。

```sql
SELECT shop_name, COUNT(*) FROM shop GROUP BY shop_name;

SELECT shop_name, COUNT(*) FROM shop WHERE shop_name != 'ピザ屋さん' GROUP BY shop_name;
```

#### having
- Group byで集計したものを絞り込む。

```sql
SELECT shop_name, COUNT(*) FROM shop GROUP BY shop_name HAVING count(*) >= 2; 
```

#### as
- AS(アズ(読み方):エイリアス)を使ってシンプルにすることが可能。

```sql
SELECT
    shop_name, 
    COUNT(*) AS cnt
FROM 
    shop 
GROUP BY 
    shop_name 
HAVING cnt >= 2; 
```

#### SUM
- 合計値を取得

```sql
SELECT SUM(id) FROM shop;
```

- グループ化も可能

```sql
SELECT SUM(id) FROM shop GROUP BY category_id;
```

#### MAX
- 最大値を算出する

```sql
SELECT MAX(id) FROM shop;
```

- グループ化も可能

```sql
SELECT MAX(id) FROM shop GROUP BY category_id;
```

#### MIN
- 最小値を算出する

```sql
SELECT MIN(id) FROM shop;
```

- グループ化も可能

```sql
SELECT MIN(id) FROM shop GROUP BY category_id;
```

#### AVG
- 平均値を算出する

```sql
SELECT AVG(id) FROM shop;
```

- グループ化も可能

```sql
SELECT AVG(id) FROM shop GROUP BY category_id;
```

### テーブルを結合する

#### join
- テーブル同士を結合すること。めっちゃ頻出。

- joinの種類は大きく分けて2つ
	- 内部結合(INNER JOIN)
		- 互いの条件に一致するレコードをのみを抽出する
		- 片方のテーブルにしか存在しない値は間引かれる。
	- 外部結合(OUTER JOIN)
		- 結合元と結合先で値が一致しないものも抽出する。
	- LEFT OUTER JOIN
		- 左側のテーブルを軸に結合する。
	- RIGHT OUTER JOIN
		- 右側のテーブルを軸に結合する。
	- FULL OUTER JOIN
		- 結合の優先順位が存在しない。

#### INNER JOIN
- 一致するものだけを取得する

```sql
SELECT * FROM users AS u INNER JOIN microposts AS m ON u.id = m.user_id;  

select
    *
from
    users as u
    inner join microposts as m
        on u.id = m.user_id;
```

- 名前、順番なども変更が可能 

```sql
select
    u.id as 'ユーザーID',
    m.user_id as '結合ID',
    m.id as 'ポストID',
    u.name as '氏名',
    u.email as 'メールアドレス',
    m.content as '投稿'
from
    users as u
    inner join microposts as m
        on u.id = m.user_id;
```

- テーブル名を反対にしても結果は同じ

```sql
select
    u.id as 'ユーザーID',
    m.user_id as '結合ID',
    m.id as 'ポストID',
    u.name as '氏名',
    u.email as 'メールアドレス',
    m.content as '投稿'
from
    microposts as m
    inner join users as u
        on u.id = m.user_id;
```

#### LEFT JOIN
- usersを軸にして結合して値が存在しない場合はNULL表示

```sql
SELECT 
    *
FROM
    users AS u
    LEFT JOIN microposts AS m
        ON u.id = m.user_id;
```

#### RIGHT JOIN
- micropostsを軸にして結合して値が存在しない場合はNULL表示

```sql
SELECT
    *
FROM
    users AS u
    RIGHT JOIN microposts AS m
        ON u.id = m.user_id;
```