SQLの基礎の基礎

## はじめに

SQLについて学習したので、これまでに学んだ知識をまとめました。
### 学習した事

- MySQLのインストール
- MySQL Workbenchの使用方法
- SQL構文の基本の使い方

今回は、SQL構文の基本の使い方について、自分用メモと知識定着の目的で書きました。訂正箇所などありましたら、ご指摘いただけると幸いです。

## SQLの基本

- SQLは大文字と小文字を区別しない
- 文字と日付はシングルクォーテーションで囲う

### データ型

テーブルを作成する時に、それぞれのカラムに指定した形式のデータしか入力できないように設定する。この時指定するデータの形式をデータ型という。

|データ型名|データ種別||
|---|:---:|---|
|int型|数値型|整数|
|tinyint型|数値型|-128〜127|
|char型|文字列|固定長の文字列255文字まで|
|varchar型|文字列|可変長の文字列255文字まで|
|text|文字列|長い文字列65535文字まで|
|date型|日付|1000-01-01〜9999-12-31まで|
|datetime型|日付・時刻| 00:00:00.000000〜23:59:59.999999|
|time型|時刻|838:59:59〜838:59:59|

### 集約関数

集約関数ではnullは基本的に無視されるが、`COUNT(*)`とする事でnullを含めたレコードを数える。

|関数|意味|
|---|:---:|
|COUNT|テーブルのレコード数を数える|
SUM|カラムの数値を合計する|
|AVG|カラムの数値を平均する|
|MAX|カラムの中の最大値を求める
|MIN|カラムの中の最小値を求める|

### SQL文の記述順序と実行順序

|記述順序|実行順序|
|---|:---:|
|SELECT|FROM|
|FROM|JOIN|
|JOIN|WHERE|
|WHERE|GROUP BY|
|GROUP BY|HAVING|
|HAVING|SELECT|
|ORDER BY|ORDER BY|
|LIMIT|LIMIT|

in演算子・・・　ある値が値セット内に含まれて**いる**かどうか。
not in演算子・・・　ある値が値セット内に含まれて**いない**かどうか。 

## テーブル操作
### CRUD  
CRUDとは(Create, Read, Update, Delete)のこと。<br>
SQLではこの4つの処理を

- Create = INSERT
- Read = SELECT
- Update = UPTATE
- Delete = DELETE

の構文で操作する。


### テーブルの作成
```
CREATE TABLE [テーブル名](
  列名 [データ型] [その他の記述],
  列名 [データ型] [その他の記述],
  列名 [データ型] [その他の記述]
);
```
### テーブルにデータの追加

```
INSERT INTO [テーブル名](列1,列2,列2) VALUES(値1,値2,値3);
```
この時、列と値の数が一致している必要がある。  
複数行を一度に追加する場合は、それぞれが括弧で囲まれ、カンマで区切られた、カラム値の複数のリストを記入していく。

```
INSERT INTO [テーブル名](列1,列2,列2) VALUES(値1,値2,値3),(値1,値2,値3),(値1,値2,値3);
```
### 列の追加
```
ALTER TABLE [テーブル名] ADD [追加する列名] [データ型] AFTER [列名];
```
### 列名の変更

```
ALTER TABLE [テーブル名] CHANGE [旧列名] [新列名] [データ型];
```

### データの取得(SELECT)

```
SLECT ・・・ カラムの指定
FROM ・・・ 対象テーブルの指定
JOIN ・・・ テーブルの結合
WHERE ・・・ 絞り込み条件の指定
GROUP BY ・・・ グループ化の条件の指定
HAVING ・・・ グループ化した後の絞り込み条件を指定
ORDER BY ・・・ 並び替え条件を指定
LIMIT ・・・ 取得する行数を指定
```

例 adventurerテーブル

|id|name|sex|lv|mp|job|
|:---:|:---:|:---:|:---:|:---:|:---:|
|1|山田|man|20|40|戦士|
|2|鈴木|woman|30|230|魔法使い|
|3|田中|man|40|280|僧侶|
|4|小野|woman|50|360|魔法戦士|
|5|佐藤|man|40|200|レンジャー|
|6|西野|woman|70|220|パラディン|


#### WHERE句


```
SELECT name, lv FROM adventurer WHERE name = '鈴木';
```
実行結果

```
name     lv
鈴木      25
```
##### LIKE 
ワイルドカード文字で文字列のパターンを指定する事ができる。

- '山%' → '山'で始まる文字列
- '%山' → '山'で終わる文字列
- '%山%' → '山'を含む文字列  
- '__山' → 何かしらの2文字で始まり、'山'で終わる文字列  

jobに戦士の文字列を含む情報を取得

```
SELECT * FROM adventurer WHERE job LIKE '%戦士%';
```
実行結果

```
id  name  sex   lv   mp    job
1   山田   man   20   40    戦士
4   小野  woman  50   360  魔法戦士
```

#### サブクエリ

鈴木さんよりもmpが高い人の名前を取得

```
SELECT name FROM adventurer WHERE mp > (SELECT mp FROM adventurer WHERE name = '鈴木');
```

実行結果

```
name
田中
小野
``` 

#### CASE

CASE式を使う事で条件分岐を指定する事ができる。 

lvが60以上なら'A' 
lvが40以上なら'B'
lvが40未満なら'C'

```
SELECT name,
       CASE WHEN lv >= 60 THEN 'A'
            WHEN lv >= 40 THEN 'B'
            ELSE 'C'
       END AS ranking
FROM adventurer;
```
実行結果

```
name   ranking
山田       C
鈴木       C
田中       B
小野       B
佐藤       B
西野       A
```

#### GROUP BY句

性別毎のレベルの平均を取得

```
SELECT sex, AVG(lv) FROM adventurer GROUP BY sex;
```

実行結果

```
sex     lv
man     50
woman   75
```
#### ORDER BY句

並び替え条件を指定する。複数条件を指定する時は、,区切りで指定する。
ASC・・・昇順（デフォルト）
DESC・・・降順

lvが高い順に並び替え。lvが同じ場合はid順。

```
SELECT * FROM adventurer ORDER BY lv DESC, id ASC;
```
実行結果

```
id  name  sex  lv  mp  job
6   西野  woman 70 220  パラディン
4   小野  woman 50 360  魔法戦士
3   田中   man  40 280  僧侶
5   佐藤   man  40 200  レンジャー
2   鈴木  woman 30 230  魔法使い
1   山田   man  20 40   戦士
```

#### LIMIT句
取得する行数を指定する事ができる。

```
SELECT * FROM adventurer ORDER BY lv DESC, id ASC LIMIT 3;
```

実行結果

```
id  name  sex  lv  mp  job
6   西野  woman 70 220  パラディン
4   小野  woman 50 360  魔法戦士
3   田中   man  40 280  僧侶
```

### データの更新(UPDATE)

```
UPDATE [テーブル名] SET [変更したい列名] = ['新しい値'] WHEREで条件指定;
```
### データを削除(DELETE)

```
DELETE FROM [テーブル名] WHERE [条件];
```
## データベース操作
### データベース接続

```
mysql -u root -p
```

### MySQL起動
```
mysql.server start
```

### MySQL終了
```
mysql.server stop
```

### MySQLの状態確認
```
mysql.server status
```

### データベースの作成
```
CREATE DATABASE [作成するデータベース名];
```

### データベースの命名ルール
- 半角のアルファベット a,b,cなど
- 半角の数字 1,2,3など
- アンダースコア _
- 名前の最初は半角のアルファベット

### データベースの削除
```
DROP DATABASE [削除したいデータベース名];
```

### データベースの一覧表示
```
SHOW DATABASES;
```
### 使用しているデータベースの確認

```
SELECT DATABASE();
```
### 使用するデータベースの選択
```
USE [データベース名];
```