SQL文の基礎文法をまとめました

# なにこれ
SQL文の基礎文法を自分なりにまとめました。
頭の中にインデックスを作ることが目的です。

# データベース一覧を確認する時
#
```
show databases;
```

# データベース作成
#
```
create database データベース名
create database sqltest;
```

# データベースを選択
#
```
use データベース名
use sqltest;
```

# テーブルの確認
#
```
show tables;
```

# テーブル作成
#
```
create table テーブル名 (カラム名 カラム名の型, ……)
CREATE TABLE goods (id INT, name VARCHAR(255));
```

# カラムの確認（テーブル構造を見る）
#
```
SHOW columns FROM テーブル名
SHOW columns FROM goods;
```

# カラムの追加
#
```
一つだけ追加➡️ALTER TABLE テーブル名 ADD カラム名 カラムの型;
ALTER TABLE goods ADD apexLegends int;

複数追加➡️ALTER TABLE テーブル名 ADD (カラム名 カラムの型, ……);
ALTER TABLE goods ADD (price int, zaiko int);
```

# カラムの修正
カラムの型は変更しなくても記述してね。
#
```
ALTER TABLE テーブル名 CHANGE 古いカラム名 新しいカラム名 新しいカラムの型;
ALTER TABLE goods CHANGE zaiko stock int;
```

# カラムの削除
#
```
ALTER TABLE テーブル名 DROP カラム名
ALTER TABLE goods DROP stock;
```

# データの登録
#
```
全てのカラムに値を入れる➡️INSERT INTO テーブル名 VALUES(値1、値2、値3)
INSERT INTO goods VALUES(1, “ペン”, 120);

特定のカラムのみ➡️INSERT INTO テーブル名(カラム名1，カラム2)VALUES(値1，値2）
INSERT INTO goods(id, name) VALUES(2, “消しゴム”);
```

# データの更新
#
```
UPDATE テーブル名 SET 変更内容 WHERE 条件;
UPDATE goods SET price = 100 WHERE id = 2;
```

# データの削除
#
```
DELTE FROM テーブル名 WHERE 条件
DELETE FROM goods WHERE id =2;
```

# カラムの削除
#
```
ALTER TABLE テーブル名 DROP COLUMN カラム名
ALTER TABLE goods DROP COLUMN apexLegends;
```

# 指定したカラムの中身（レコード）を確認する
```
SHOW カラム名 FROM テーブル名
SHOW title FROM posts;
```

# テーブル全体のレコードを確認する
```
SHOW * FROM posts;
```

自分用メモ➡️ここからsql-curriculm_development

# 検索するテーブルを指定（ここから来る、fromしてるよ〜みたいな）
#
```
FROM テーブル名
```

# 取得するカラムを指定（こいつに決める！セレクトする！）
#
```
SELECT カラム名
```

# ワイルドカード
「*」のこと。SELECT句で使うと、全てのカラムを取得する。便利ぃ〜

# 取得するレコードを制限
#
```
WHERE 条件
WHERE id =1、WHERE name = “安倍”、WHERE id <= 5（idが5まで）
```

# 論理演算子
- AND（複数条件が全て一致してたらおk）
#
```
WHERE a AND b
SELECT * FROM users WHERE age <= 22 AND prefecture = “神奈川県”
```

- OR（複数条件のどれかが合ってればおk）
#
```
WHERE a OR b
SELECT * FROM users WHERE age <= 20 OR prefecture = "神奈川県"
```

- NOT（こいつ以外ならおk）
#
```
WHERE NOT a
SELECT * FROM users WHERE NOT prefecture = "東京都"


- BETWEEN（範囲指定するぞ〜）
#
```
WHERE カラム名 BETWEEN 下限 AND 上限
SELECT * FROM users WHERE age BETWEEN 21 AND 24
```

- IN（含まれてたらおk、場合によってはIFより便利かも）
#
```
WHERE カラム名 IN (値1，値2、値3、、、)
SELECT * FROM users WHERE prefecture IN ("東京都", "神奈川県")
```

# データの結合
- CONCAT関数（文字列をくっつける、対面3秒で合体いたします。）
#
```
CONAT（文字列1、文字列2、…） # 連結する文字列にNULLがあると、結果はNULL
SELECT CONCAT(family_name, first_name) FROM users
```

- AS句（カラムに別名をつけられる。名付け名人かよ）

```
SELECT CONCAT(family_name, first_name) AS “名前” FROM users

⬇️AS 省略できるけど分かりにくいから嫌い。
SELECT CONCAT(family_name, first_name) “名前” FROM users
```

# 重複する行の除外
- DISTINCT

ダブったやつを消す、デブは許さない。かぶるな

#
```
SELECT DISTINCT カラム名
SELECT DISTINCT user_id FROM shifts WHERE date = "2015-07-01"
```

# レコードのグループ化

- GROUP BY句

グループ化して一つにまとめるよ。グルーピングぅ。グループ単位で集計した結果を取得できる。DISTINCTとは似てるけど、用途が違いまっせ。

```
GROUP BY カラム名
SELECT user_id FROM shifts WHERE date = "2015-07-01" GROUP BY user_id
```

# レコードの数を数える
- COUNT関数 

文字通り数える。基本はNULL以外をね。「*」使ったら全全全部。カラムを指定して使用すると、新しいカラムを作って表示するよ。

#
```
SELECT COUNT（カラム名）
SELECT user_id, COUNT(*) FROM shifts WHERE date = "2015-07-01" GROUP BY user_id

⬇️COUNTにはASで名前もつけられるよ！ASにゃんすげえ
SELECT user_id, COUNT(*) AS "コマ数" FROM shifts WHERE date = "2015-07-01" GROUP BY user_id
```

# テーブルの結合
-  JOIN
 
指定したそれぞれのテーブル・カラムの値が一致するデータを結合できる。
JOIN句はFROM句の後に書け！対象のテーブルを指定する。カラムはONの後にイコールでな。

#
```説明.
FROM テーブル名1
JOIN テーブル名2 ON テーブル名1.カラム名1 = テーブル名2.カラム名2

FROM shifts
JOIN users ON shifts.user_id = users.id

⬇️省略もできるよ。説明無しでいきなり使うな！分かりにくいから使わない予定。
FROM shifts s
JOIN users u ON s.user_id = u.id
```

#
``` 具体例.
⬇️省略しないバージョン
SELECT
    user_id,
    COUNT(*) "コマ数",
    users.*
FROM shifts
JOIN users ON shifts.user_id = users.id
WHERE date = "2015-07-01"
GROUP BY user_id

⬇️省略しましたあ〜
SELECT
    user_id,
    COUNT(*) "コマ数",
    u.*
FROM shifts s
JOIN users u ON s.user_id = u.id
WHERE date = "2015-07-01"
GROUP BY user_id

⬇️遊んでみた
SELECT
    user_id,
    prefecture
FROM shifts s
JOIN users u ON prefecture = u.prefecture
WHERE date = "2015-07-01"

```

# 検索結果を用いた検索
シフトに入っていない人一覧を取得したい。➡️入ってる人を除くことで求められる➡️シフトに入ってる人を検索して、その検索結果を使ってさらに検索する。

- サブクエリ（今回の場合はNOT IN）

```
SELECT *
FROM users
WHERE id NOT IN (リスト)

SELECT DISTINCT user_id
FROM shifts
WHERE date = "2015-07-01"
⬇️上記に一致しなかったやつらを出す！
SELECT *
FROM users
WHERE id NOT IN (
    SELECT DISTINCT user_id
    FROM shifts
    WHERE date = "2015-07-01"
)
```

SQLメモ

# 10.30　下記を追記
Udemyの講座に出てきたSQL文を自分用にメモしただけなので、詳しいテーブルの内容は乗せることができません。ご了承ください

# like句を使ったあいまい検索

## %（パーセント）
0文字以上の任意の文字列

### 例
```
‘中%’ // 中で始まる文字列
‘%中%’ // 中を含む文字列
‘%中’ // 中で終わる文字列
```

## ＿（アンダースコア）
任意の一文字

### 例
```
＿子 // 何かしらの2文字から始まり、「子」で終わる文字列	
```

# limitを使った上限設定

## 余談
RailsでSQL文を発行する時、find_byとかすると自動的にlimit(1)がついてる。

### 例
```
SELEC　T * from products limit 10;
SELECT * from products limit 0, 10;  // 明示的に0から始まることを示す
```

# CSVとは
Comma Separated Valuesの略
区切り文字のコンマ「,」で区切ったテキストデータ。
CommaだからC。Tabで区切るT(Tab)SVというのもある。

# distinct ユニークユーザーを求める
distinctを使うと重複した値を削除できる。

```
select count(distinct user_id) from access_logs
```

# group by 月別のユニークユーザー数
集計した後に処理する

```
select request_month, count(distinct user_id) from access_logs
where request_month >= '2017-01-01'
and request_month < '2018-01-01'
group by request_month;
```

# 記述順序と実行順序
|  | 記述順序 | 実行順序 |
| ---- | ---- | ---- |
| 1 | select | from |
| 2 | from | where |
| 3 | where | group by |
| 4 | group by | having |
| 5 | having | select |
| 6 | order by | order by |
| 7 | limit | limit |

inner joinなど結合処理はfromの次。2.5番目みたいな感じ。

# having 抽出条件を指定する
whereはselectの結果から抽出
havingはgroupByでグルーピングした結果から、条件指定。この中の10以上！みたいな？

```
group by request_month
having count(*) >= 1000
```

# round 四捨五入 
```
構文：round (四捨五入する数値、桁数）

select id, name, round(price * 1.08, 0) from users;
```

# concat 文字列の連結
```
select concat(last_name, ' ', first_name, 'さん') as 名前 from users;
```

# 日付と時刻の関数や演算子
| 種類 | 記述 |
| ---- | ---- |
| 現在の日付 | current_date() |
| 現在の時刻 | current_time() |
| 現在の時刻（何日の何時何分まで表示） | current_timestamp() |
| n日後の日付 | + interval 3(n) day |
| n日前の日付 | - interval 3(n) day |
| x時間後の時刻 | + interval 6(n) hour |
| x時間前の時刻 | - interval 6(n) hour |
| extract | 日付や時刻の特定の部分（年や月）までを取り出す |

## 例文
```
select current_date();
select current_date() - interval 3 day;
select current_time() - interval 7 hour;

// extractを使ってみた
select * from orders where extract(year_month from order_time) = 201701;
select * from orders where extract(year from order_time) = 2017;
select * from orders where extract(month from order_time) = 1;
```

# inner join 内部で結合！
テーブル名を指定して、がっちゃんこする。nullがない。両方のテーブルの情報がある。
outer joinはそれがないのでnullが出る。

```
select users.id, users.last_name, users.first_name, prefectures.name 
from users 
inner join prefectures on users.prefecture_id = prefectures.id

// 下記は省略バージョン
select u.id, u.last_name, u.first_name, p.name from users as u
inner join prefectures as p on u.prefecture_id = p.id

// 応用問題
select o.id, o.order_time, o.amount, u.id as ユーザーID, u.last_name, u.first_name from orders as o
inner join users as u on o.user_id = u.id
where u.prefecture_id = 13
and extract(year_month from order_time) = 201701
order by o.id
```

# outer join
nullが生まれるのがこっち。left outer joinとright outer joinがある。outerを省略してleft joinと書ける。

"LEFT OUTER JOIN"は主とするテーブルの情報を全て出すのに対し、
"RIGHT OUTER JOIN"は結合させる副とするテーブルの情報を全て出します。

```
select u.last_name, u.first_name, o.user_id order_user_id, o.id order_id
from users u 
left outer join orders o on u.id = o.user_id
order by u.id
```

# 3つ以上のテーブル結合
```
select 
    o.id,
    o.user_id,
    concat(u.last_name, ' ', u.first_name) as ユーザー名,
    o.amount,
    o.order_time,
    p.name product_name,
    od.product_qty,
    p.price
from orders o
inner join 
	order_details od
on o.id = od.order_id
inner join
	products p
on od.product_id = p.id
inner join
	users u
on o.user_id = u.id
```

# 多対多の結合
```
SELECT 
	p.id product_id,
    p.name product_name,
    c.name category_name
FROM 
	products p
inner join products_categories pc on p.id = pc.product_id
inner join categories c on pc.category_id = c.id
where
	p.id = 3
```

# union 集合演算子
テーブルの足し算ができる。unionは重複なし、union allは重複有り

```
select
	id,
	email,
    last_name,
    first_name,
    gender
from 
	users
union
select
	id,
	email,
    last_name,
    first_name,
    gender
from 
	admin_users
```

# ビュー
SQL文を保存して、from で呼び出して実行できる。orderが使えなかったり色々と制約有り

```
// 都道府県ごとのユーザー数を求めた
create view prefecture_user_counts(name, count)
as
select
	p.name name,
    count(*) count
from 
	users u
inner join prefectures p on u.prefecture_id = p.id
group by u.prefecture_id;
```

# サブクエリ
問い合わせ結果に基づいて、異なる問い合わせを行う仕組み。複雑なお問い合わせができる。
## not inを使って、12月に注文してない人をあぶり出す
```
select 
	id,
    last_name,
    email
from users
where id not in(
	select user_id from orders
	where extract(year_month from order_time) = 201712
);
```

## スカラサブクエリ　単一の戻り値を返す
```
// priceが5000以上の商品を表示
select * from products
where 
	price > (select avg(price) from products);
```

# case式　条件分岐
rubyとかと一緒。caseとwhenで条件分岐できる。

```
select 
	u.id as user_id,
    count(*) as num, 
	case 
		when count(*) >= 5 then 'A'
        when count(*) >= 2 then 'B'
        else 'C'
	end as user_rank
from users as u
inner join orders as o on u.id = o.user_id
group by u.id
```

演習問題

```
select 
	p.id as product_id,
    p.name as product_name,
    sum(od.product_qty) as 販売個数,
    case
		when sum(od.product_qty) >= 20 then 'A'
        when sum(od.product_qty) >= 10 then 'B'
        else 'C'
    end as ランク
from products as p
left outer join order_details as od on p.id = od.order_id
group by p.id
order by ランク asc
```

# データの更新
列数と値数は一致する必要あり。ただし省略した場合は別。全部埋めれば良い。
## insert into 新規データの追加
```
insert into テーブル名(列1, 2..) values (値1, 2..)
insert into products(name, price) values ('新商品A', 1000);

// 省略した場合(Railsはこっちで書かれてる）
insert products values(1002, ‘新商品B’, 2000);

// 複数行追加(idは省略可能）
insert into products (id, name, price) values(1005, '新商品C', 3000), (1006, '新商品D', 4000);
```

## update データの更新
```
update products set name = ‘hoge’ where id = 1;
update products set name = ‘fuga’, price = 114 where id = 1;

// いっぱい更新する。safe_updatesを0にしてね。
update products set price = price * 1.1 where id in(
	select product_id
	from order_details
	group by product_id
	having sum(product_qty) >= 10
);
```

## delete　データの削除
```
delete from products_categories;
delete from products where id =1001;
delete from products where id not in(select product_id from order_details group by product_id);
```