MySQLを勉強し始めた頃に知っておきたかったことをいろいろ箇条書き

sqlを勉強し始めて(SELECT,INSERT,UPDATE,DELETE,CREATE文,left join,inner join,group by,order by,distinct,サブクエリを覚えたぐらい)の頃の自分が、その当時に次のステップとして知っておきたかったSQLのいろいろを書いていきます。

---

**注意**

私自身sqlについて勉強中のものですので、間違いや、もっといい書き方があるなどがあると思いますが、
私がsqlを勉強し始めた頃にこれを知っていれば、sqlってこんなに面白いものなんだともっと早く知ることができたのにと思い、投稿させていただきました。m(__)m
間違いなどあれば指摘いただければと思います。
テーブル・カラム名とかは大分適当ですが、やりたいことが伝わる内容には気をつけましたのでよろしくお願いいたします。

---

基本的にMySQL 5.6で書いて行きます。
MySQLでサポートしていない書き方などは別のsqlを使う感じです。

# バージョン確認

```sql
select version();
```

# テーブルバックアップ

```sql
create table sample_table_bk as select * from sample_table
```

# データが0件の場合だけINSERT

ググるとやり方がいろいろあるようですが、個人的には、これが一番見た目わかりやすいと思いました。

```sql
CREATE TABLE IF NOT EXISTS works(
    id           INTEGER PRIMARY KEY AUTO_INCREMENT,
    title        VARCHAR(50)
)
```

```sql
INSERT INTO works(
    title
)
SELECT title FROM (
    SELECT 'MyWork' title UNION ALL
    SELECT 'Todos'        UNION ALL
    SELECT 'Shopping'
) tmp
WHERE (select count(*) from works) = 0
```

# TRUNCATEで全データ削除

delete文だとオートコミットでなかった場合、`commit;`が必要ですが、
truncateだと`commit;`しなくても反映してくれる

以下2つは同じ意味です。

```sql
TRUNCATE TABLE sample_table;
```

```sql
DELETE FROM sample_table;
COMMIT;
```

# トリガーを設定する

参考

https://dev.mysql.com/doc/refman/5.6/ja/trigger-syntax.html

覚えると難しいことができそうですが、私がやってみたのは以下の簡単な例です。

---

例えばMySQLですと、CREATE文で`ON UPDATE`を指定すると、更新時に一緒に値を更新してくれますが、

```sql
CREATE TABLE IF NOT EXISTS test_table (
    id         INT(10) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
    content    VARCHAR(20) NOT NULL,
    createdate DATETIME DEFAULT CURRENT_TIMESTAMP,
    updatedate DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) DEFAULT CHARSET=utf8mb4;
```

SQLiteですと、このような`ON UPDATE`の機能はありませんので、**トリガー**使えばできる。

```sql:sqlite
CREATE TABLE IF NOT EXISTS sample_table (
    id         INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    name       VARCHAR NOT NULL,
    createdate DATE DEFAULT (DATETIME('now','localtime')),
    updatedate DATE DEFAULT (DATETIME('now','localtime'))
);
```

```sql:sqlite
CREATE TRIGGER IF NOT EXISTS updatedate AFTER UPDATE on sample_table  
BEGIN  
    UPDATE sample_table SET updatedate = DATETIME('now','localtime') WHERE id = old.id;  
END;
```

# 横つなぎにしてくれる集計関数
参考

https://dev.mysql.com/doc/refman/5.6/ja/group-by-functions.html#function_group-concat

[テーブル作成](#001)

```sql
select 地方,GROUP_CONCAT(都道府県)
from japan
group by 地方;

select 地方,GROUP_CONCAT(都道府県 ORDER BY シーケンス DESC SEPARATOR':')
from japan
group by 地方;

select GROUP_CONCAT(DISTINCT 地方)
from japan;
```

![WS000024.JPG](https://qiita-image-store.s3.amazonaws.com/0/142910/c7f37f5e-e969-14bd-0934-3bf1319b8682.jpeg)


# information_schemaからテーブル名・カラム名一覧取得

MySQLですと、information_schemaでテーブルのいろいろな情報が取れますね

```sql
SELECT *
FROM information_schema.tables
WHERE table_schema = database()
```

```sql
SELECT *
FROM information_schema.columns
WHERE table_schema = database()
AND table_name = 'hogehoge'
ORDER BY ordinal_position
```

# 最大値/最小値に対する全カラムを取得
[テーブル作成](#002)

- 支店ごとの来客数最大に対する他のカラムも取得

やり方はいろいろあるとのことです。

以下3つとも同じ結果になる

```sql
SELECT *
FROM guest
WHERE ((支店,来客数) IN (
    SELECT 支店,MAX(来客数)
    FROM guest
    GROUP BY 支店
))
```

---

```sql
SELECT guest.*
FROM guest
INNER JOIN (
    SELECT 支店,MAX(来客数) 来客数
    FROM guest
    GROUP BY 支店
) tmp
USING(支店,来客数)
```

![WS000025.JPG](https://qiita-image-store.s3.amazonaws.com/0/142910/beca5c8f-8a65-4581-3a63-1090e7e59411.jpeg)

---

row_number()関数などでも同じ結果を取得できる

※MySQL5.6では使えないので、postgresqlを使用

```sql:postgresql
SELECT * FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY 支店 ORDER BY 来客数 DESC) NUM
    FROM guest
) a
WHERE NUM = 1
```

![スクリーンショット 2018-02-11 15.49.27.png](https://qiita-image-store.s3.amazonaws.com/0/142910/0856cf50-7f7d-0612-c9e8-e4e389cacbb2.png)

# ファンクションを作成する

ほとんど使ったことがないため、とりあえずfizzbuzzを作りました

```sql
CREATE FUNCTION fizzbuzz (i INT)
RETURNS VARCHAR(20) DETERMINISTIC
RETURN case
            when i % 3 = 0 and i % 5 = 0 then 'fizzbuzz'
            when (i % 3 = 0) then 'fizz'
            when (i % 5 = 0) then 'buzz'
            else i
       end
```

```sql
select num,fizzbuzz(num) from (
    select 1 num
    union all select 2
    union all select 3
    union all select 4
    union all select 5
    union all select 6
    union all select 7
    union all select 8
    union all select 9
    union all select 10
    union all select 11
    union all select 12
    union all select 13
    union all select 14
    union all select 15
) a
```

![WS000026.JPG](https://qiita-image-store.s3.amazonaws.com/0/142910/31acbbd5-3664-8b74-751f-6f2a7bc70dc8.jpeg)

# 縦持ちのデータを横持ちにする
[テーブル作成](#003)

※やり方はいろいろ人によります

```sql
   select a.syohin_mei
         ,l.size size_l
         ,m.size size_m
         ,s.size size_s
     from (select distinct fuku_1.syohin_mei from fuku_1) a
left join (select syohin_mei,size
           from fuku_1
           where size='L') l
       on a.syohin_mei=l.syohin_mei
left join (select syohin_mei,size
           from fuku_1
           where size='M') m
       on a.syohin_mei=m.syohin_mei
left join (select syohin_mei,size
           from fuku_1
           where size='S') s
       on a.syohin_mei=s.syohin_mei
```

![WS000027.JPG](https://qiita-image-store.s3.amazonaws.com/0/142910/000ad010-6176-f588-419d-c2d1e0785443.jpeg)

# 横持ちのデータを縦持ちにする
[テーブル作成](#004)

※やり方はいろいろ人によります

```sql
select * from (
   (select syohin_mei,'L' size
      from fuku_2
     where size_l=1)
     union all
   (select syohin_mei,'M'
      from fuku_2
     where size_m=1)
     union all
   (select syohin_mei,'S'
      from fuku_2
     where size_s=1)
) a
order by syohin_mei desc,size
```

![WS000028.JPG](https://qiita-image-store.s3.amazonaws.com/0/142910/9c7135ee-589c-63e0-5d6d-4a614ffb2752.jpeg)

# WITH句で重複条件などをまとめる
[テーブル作成](#005)


with句を使用することで、条件を使い回す、長いsqlだと見やすくなる、と行ったメリットがあります。

※with句はMySQL5.6ではサポートしていないため、postgresql 9.2を使用しています。
MySQLだとwith句の代わりにVIEWを作成するなどします。

**出力内容**

キャンペーン対象の商品について、先月と今月の売り上げを比較したデータを出力する
出力項目:ショップID、商品コード、先月トータル売上、今月トータル売上、売上アップ/ダウン

**抽出条件**

* syohin_mstテーブルのcampaign_flgが1

または、
* 以下のショップID・商品コードの組み合わせの場合は、campaign_flgに関係なく抽出

| ショップID shop_id | 商品コード code | 備考 |
|---|---|---|
|11001|01S||
|11001|01M||
|11003|01S|※先月トータル売上には適用しない|
|11003|01M|※先月トータル売上には適用しない|
|11005|01X|※先月トータル売上には適用しない|

```sql:postgresql
WITH campaign_code AS (
   (select '11001' shop_id, '01S' code, 1 sengetsu_flg union
    select '11001','01M',1  union
    select '11003','01S',0  union
    select '11003','01M',0  union
    select '11005','01X',0) union
    SELECT shop_id,code,1
    FROM syohin_mst
    WHERE campaign_flg=1
),
先月売上金額 AS (
    SELECT A.shop_id,A.code,SUM(B.amount) total
    FROM sales A
    LEFT JOIN syohin_mst B
    ON A.shop_id=B.shop_id AND A.code=B.code
    WHERE TO_CHAR(A.sales_date,'yyyymm') = TO_CHAR(now()+'-1 months','yyyymm')
    AND ((A.shop_id,A.code)IN(SELECT DISTINCT shop_id,code FROM campaign_code WHERE sengetsu_flg=1))
    GROUP BY A.shop_id,A.code
),
今月売上金額 AS (
    SELECT A.shop_id,A.code ,SUM(B.amount) total
    FROM sales A
    LEFT JOIN syohin_mst B
    ON A.shop_id=B.shop_id AND A.code=B.code
    WHERE TO_CHAR(A.sales_date,'yyyymm') = TO_CHAR(now(),'yyyymm')
    AND ((A.shop_id,A.code)IN(SELECT DISTINCT shop_id,code FROM campaign_code))
    GROUP BY A.shop_id,A.code
)
SELECT A.shop_id
      ,A.code
      ,COALESCE(B.total,0) 先月
      ,COALESCE(C.total,0) 今月
      ,CASE WHEN COALESCE(B.total,0) > COALESCE(C.total,0) THEN '売上ダウン'
            WHEN COALESCE(B.total,0) < COALESCE(C.total,0) THEN '売上アップ'
            ELSE '同'
       END
FROM (SELECT DISTINCT shop_id,code FROM campaign_code) A
LEFT JOIN 先月売上金額 B ON A.shop_id=B.shop_id AND A.code=B.code
LEFT JOIN 今月売上金額 C ON A.shop_id=C.shop_id AND A.code=C.code
ORDER BY A.shop_id,A.code
```

こういう、ちょっとややこしい条件になってくると、with句を使うことで見やすくなりました

![スクリーンショット 2018-02-11 16.05.55.png](https://qiita-image-store.s3.amazonaws.com/0/142910/ca7ba4a9-e086-19f5-9c73-6970a367ca06.png)

# ビューでselect文を簡潔にする

[テーブル・ビュー作成](#006)

- 全テーブルに削除フラグのカラムがあり、全テーブルレコードを論理削除している
- それに加えて、memberテーブルからははstatusが`'加入中'`のデータのみを常に持ってくる
- といった条件でテーブルを5つ結合する

普通にsqlを書くと以下のようなselect文になる

```sql
   select *
     from cust c
left join member m
       on c.cust_id=m.cust_id
      and m.delete_flg=0
      and m.status='加入中'
left join food_order o
       on c.cust_id=o.cust_id
      and o.delete_flg=0
left join food f
       on o.food_id=f.food_id
      and f.delete_flg=0
left join food_category fc
       on f.food_cat_id=fc.food_cat_id
      and fc.delete_flg=0
    where c.delete_flg=0
 order by c.cust_id,fc.food_cat_id,f.food_id;

-- またはサブクエリにする

   select *
     from cust c
left join (select * from member where delete_flg=0 and status='加入中') m
       on c.cust_id=m.cust_id
left join (select * from food_order where delete_flg=0) o
       on c.cust_id=o.cust_id
left join (select * from food where delete_flg=0) f
       on o.food_id=f.food_id
left join (select * from food_category where delete_flg=0) fc
       on f.food_cat_id=fc.food_cat_id
    where c.delete_flg=0
 order by c.cust_id,fc.food_cat_id,f.food_id;
```

好みの問題になりますが、自分的には上の書き方よりは、ビューにして簡潔にした方がわかりやすいかなと思います。

```sql
-- ビューから取得
select *
from customer_view c
left join member_view        m  on c.cust_id=m.cust_id
left join food_order_view    o  on c.cust_id=o.cust_id
left join food_view          f  on o.food_id=f.food_id
left join food_category_view fc on f.food_cat_id=fc.food_cat_id
order by c.cust_id,fc.food_cat_id,f.food_id;
 ```

![WS000029.JPG](https://qiita-image-store.s3.amazonaws.com/0/142910/d1107a80-d83f-0e48-76dd-4157cd675e70.jpeg)

# csvを加工して取り込み

http://www.data.jma.go.jp/obd/stats/data/mdrr/docs/csv_dl_format_mxtem.html

気象庁のホームページから、全国最高気温情報のcsvを持ってきて、それを取り込んで見る

[テーブル作成](#007)

※加工と言っても、日付変換とcase文使ってるぐらいです

```sql
LOAD DATA LOCAL INFILE 'c:/high.csv' -- Cドライブ直下にcsvを置いた場合
INTO TABLE high
FIELDS TERMINATED BY ','
IGNORE 1 LINES
(
     @field1
    ,@field2
    ,@field3
    ,@field4
    ,@field5
    ,@field6
    ,@field7
    ,@field8
    ,@field9
    ,@field10
    ,@field11
    ,@field12
    ,@field13
    ,@field14
    ,@field15
    ,@field16
    ,@field17
    ,@field18
    ,@field19
    ,@field20
    ,@field21
    ,@field22
    ,@field23
    ,@field24
    ,@field25
    ,@field26
    ,@field27
    ,@field28
    ,@field29
    ,@field30
    ,@field31
    ,@field32
    ,@field33
    ,@field34
    ,@field35
    ,@field36
    ,@field37
)
SET 
     `観測所番号` = @field1
    ,`都道府県` = case when @field2 like '北海道%' then '北海道' else @field2 end
    ,`地点` = @field3
    ,`国際地点番号` = @field4
    ,`現在時刻` = STR_TO_DATE(CONCAT(@field5,@field6,@field7,@field8,@field9),'%Y%m%d%H%i')
    ,`今日の最高気温` = @field10
    ,`今日の最高気温の品質情報` = @field11
    ,`今日の最高気温起時` = STR_TO_DATE(CONCAT(@field12,@field13),'%d%H%i')
    ,`今日の最高気温起時の品質情報` = @field14
    ,`平年差` = @field15
    ,`前日差` = @field16
    ,`該当旬（月）` = @field17
    ,`該当旬（旬）` = @field18
    ,`極値更新` = @field19
    ,`10年未満での極値更新` = @field20
    ,`今季最高` = @field21
    ,`今年の最高気温（昨日まで）` = @field22
    ,`今年の最高気温（昨日まで）の品質情報` = @field23
    ,`今年の最高気温（昨日まで）を観測した起日` = STR_TO_DATE(CONCAT(@field24,@field25,@field26),'%Y%m%d')
    ,`昨日までの観測史上1位の値` = @field27
    ,`昨日までの観測史上1位の値の品質情報` = @field28
    ,`昨日までの観測史上1位の値を観測した起日` = STR_TO_DATE(CONCAT(@field29,@field30,@field31),'%Y%m%d')
    ,`昨日までの1月の1位の値` = @field32
    ,`昨日までの1月の1位の値の品質情報` = @field33
    ,`昨日までの1月の1位の値の起日` = STR_TO_DATE(CONCAT(@field34,@field35,@field36),'%Y%m%d')
    ,`統計開始年` = @field37;
```

![WS000030.JPG](https://qiita-image-store.s3.amazonaws.com/0/142910/b389fede-b8e4-f375-812b-29bf63301da8.jpeg)

# csv出力

https://dev.mysql.com/doc/refman/5.6/ja/select-into.html

公式に書いてあること、そのままです

```sql
SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM test_table;
```

# INDEXを作成してみる

先ほど作った最高気温を保存しているhighテーブルにindexを作成して見ます。

※本来ならもっと大量のデータに対してINDEXを作成するものでしょうが、
今回は私がSQLを勉強し始めた頃に知っておきたかったことという内容で書いてますので、とりあえず作成だけ実施してみました

```sql
ALTER TABLE high ADD INDEX 都道府県_idx(都道府県)
```

EXPLAINを先頭につけると、indexが使用されたことがわかる

```sql
explain select *
from high
where 都道府県='沖縄県'
```

![WS000031.JPG](https://qiita-image-store.s3.amazonaws.com/0/142910/4b8ea41b-758d-64b5-2d3e-176848558845.jpeg)

# TRANSACTION、COMMIT、ROLLBACK

TRANSACTIONを開始し、COMMITして決定するか、ROLLBACKで前の状態に戻すかをできる便利な機能

この話はよくデッドロックの問題と一緒に話されていることが多いので、
実際にデッドロックを起こしてみました。

```sql
CREATE TABLE `tests` (
  `id`  int(11) NOT NULL AUTO_INCREMENT,
  `col` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) DEFAULT CHARSET=utf8;

INSERT INTO tests VALUES(null,'111'),(null,'222');
```

**デッドロック手順**


セッション①：`START TRANSACTION`
↓
セッション②：`START TRANSACTION`
↓
セッション①：`update tests set col = 'aaa' where id =1`
↓
セッション②：`update tests set col = 'bbb' where id =2`
↓
セッション①：`update tests set col = 'ccc' where id =2`

![20171214230222.gif](https://qiita-image-store.s3.amazonaws.com/0/142910/76abf986-5c1e-ee6f-b165-3cf5cff17ea8.gif)

実行中のまま動かなくなるデッドロックがおきました

# 表結合で私がわかりやすいと思った説明

※そもそも表結合がどういう動きか感覚が掴めなかった頃の自分がわかりやすいと思った説明です。
なので、sqlが内部的にこうやって表結合している、という説明ではありません

[テーブル作成](#008)

まず結合キーを書かず、表を2つselectした場合どうなるかを確認

```sql
select *
from animal,bunrui
```

![WS000032.JPG](https://qiita-image-store.s3.amazonaws.com/0/142910/824652bc-1ddd-7307-001d-05c7bab32ebd.jpeg)

テーブルanimal(レコード3行)とbunrui(レコード5行)が倍掛けになって15行結果が来る
### 内部結合(INNER JOIN)

そこに対して、結合キーを指定する

```sql
select *
from animal,bunrui
where animal.bunrui_id=bunrui.id;
-- または
select *
from animal
inner join bunrui on animal.bunrui_id=bunrui.id;
```

同一レコードごとに色分けするとこのような感じです。
倍掛けで増えたレコードの中で、結合キーが一致するものだけが選択される

![スクリーンショット 2018-02-11 16.31.06.png](https://qiita-image-store.s3.amazonaws.com/0/142910/116988cd-2720-5707-32f6-575e31a6bac6.png)

![WS000033.JPG](https://qiita-image-store.s3.amazonaws.com/0/142910/7e6e5ce9-419b-cd27-207b-d227662e8049.jpeg)

### 外部結合(LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN)

- LEFT JOIN(LEFT OUTER JOIN)

```sql
select *
from animal
left join bunrui on animal.bunrui_id=bunrui.id;
```

![WS000034.JPG](https://qiita-image-store.s3.amazonaws.com/0/142910/a8fd053b-d0cb-99e8-98c9-268bc0e02100.jpeg)

![スクリーンショット 2018-02-11 16.39.22.png](https://qiita-image-store.s3.amazonaws.com/0/142910/18e9080f-993a-29f7-caa3-9efb5db3f9ad.png)

---

外部結合する際は、結合キーで条件を指定する場合と、
WHEREで条件を指定する場合では、結果が変わってしまうため注意。

```sql
select *
from animal
left join bunrui
on animal.bunrui_id=bunrui.id
and bunrui.name='両生類';
```

![WS000035.JPG](https://qiita-image-store.s3.amazonaws.com/0/142910/18ea4120-6258-3052-45f6-a192d7a8462f.jpeg)

```sql
select *
from animal
left join bunrui
on animal.bunrui_id=bunrui.id
where bunrui.name='両生類';
```

![WS000036.JPG](https://qiita-image-store.s3.amazonaws.com/0/142910/a179968a-122b-92fd-0422-6a1ab2fb5543.jpeg)

![スクリーンショット 2018-02-11 16.56.02.png](https://qiita-image-store.s3.amazonaws.com/0/142910/bc446b95-402f-78da-41a7-5de8d594bf0e.png)


---

複数テーブル結合

```sql
select *
from syubetsu
left join bunrui on syubetsu.id=bunrui.syubetsu_id
left join animal on bunrui.id=animal.bunrui_id
```
![WS000039.JPG](https://qiita-image-store.s3.amazonaws.com/0/142910/a8059da5-a46d-2025-913e-78bcd16c2160.jpeg)

![スクリーンショット 2018-02-11 17.37.43.png](https://qiita-image-store.s3.amazonaws.com/0/142910/989b2f7f-643f-93fd-71e9-1e9b99e2fabe.png)

- RIGHT JOIN(RIGHT OUTER JOIN)

```sql
select *
from animal
right join bunrui on animal.bunrui_id=bunrui.id;
```

![WS000037.JPG](https://qiita-image-store.s3.amazonaws.com/0/142910/218af384-25cf-c93c-fe55-c7ea6b93922c.jpeg)

![スクリーンショット 2018-02-11 17.02.47.png](https://qiita-image-store.s3.amazonaws.com/0/142910/9e3a31b2-cc76-bbd4-1a38-89d87b4fea14.png)

- FULL OUTER JOIN

LEFT JOINとRIGHT JOINを同時にやっているようなことのようです

※MySQLではFULL OUTER JOINをサポートしていないため、postgresqlで実行しています

```sql:postgresql
select *
from animal
full outer join bunrui on animal.bunrui_id=bunrui.id;
```

![スクリーンショット 2018-02-11 17.09.07.png](https://qiita-image-store.s3.amazonaws.com/0/142910/c8a28624-1579-42a9-88c0-0b2f4f59412e.png)

![スクリーンショット 2018-02-11 17.11.18.png](https://qiita-image-store.s3.amazonaws.com/0/142910/55cb9694-4e3e-7689-c7ec-4d1805ae621c.png)


最後まで読んでいただいてありがとうございました:tired_face:

# SQLサンプル
[ページトップへ](#)

---

##### 001

[↑ **横つなぎにしてくれる集計関数** に戻る](#横つなぎにしてくれる集計関数)

```sql
-- DROP TABLE IF EXISTS japan;
CREATE TABLE IF NOT EXISTS japan (
    地方      VARCHAR(20),
    都道府県   VARCHAR(20) unique,
    シーケンス TINYINT(1) unsigned
) DEFAULT CHARSET=utf8mb4;

INSERT INTO japan(地方,都道府県,シーケンス) VALUES
('北海道','北海道',1),
('東北','青森県',1),
('東北','岩手県',2),
('東北','宮城県',3),
('東北','秋田県',4),
('東北','山形県',5),
('東北','福島県',6),
('関東','茨城県',1),
('関東','栃木県',2),
('関東','群馬県',3),
('関東','埼玉県',4),
('関東','千葉県',5),
('関東','東京都',6),
('関東','神奈川県',7),
('中部','新潟県',1),
('中部','富山県',2),
('中部','石川県',3),
('中部','福井県',4),
('中部','山梨県',5),
('中部','長野県',6),
('中部','岐阜県',7),
('中部','静岡県',8),
('中部','愛知県',9),
('近畿','三重県',1),
('近畿','滋賀県',2),
('近畿','京都府',3),
('近畿','大阪府',4),
('近畿','兵庫県',5),
('近畿','奈良県',6),
('近畿','和歌山県',7),
('中国','鳥取県',1),
('中国','島根県',2),
('中国','岡山県',3),
('中国','広島県',4),
('中国','山口県',5),
('四国','徳島県',1),
('四国','香川県',2),
('四国','愛媛県',3),
('四国','高知県',4),
('九州','福岡県',1),
('九州','佐賀県',2),
('九州','長崎県',3),
('九州','熊本県',4),
('九州','大分県',5),
('九州','宮崎県',6),
('九州','鹿児島県',7),
('九州','沖縄県',8);

commit;
```

[↑ **横つなぎにしてくれる集計関数** に戻る](#横つなぎにしてくれる集計関数)

---

##### 002

[↑ **最大値/最小値に対する全カラムを取得** に戻る](#%E6%9C%80%E5%A4%A7%E5%80%A4%E6%9C%80%E5%B0%8F%E5%80%A4%E3%81%AB%E5%AF%BE%E3%81%99%E3%82%8B%E5%85%A8%E3%82%AB%E3%83%A9%E3%83%A0%E3%82%92%E5%8F%96%E5%BE%97)

```sql
-- DROP TABLE IF EXISTS guest;
CREATE TABLE IF NOT EXISTS guest (
    支店        VARCHAR(10),
    来客数      INT(5),
    日付        DATETIME,
    売り上げ合計 INT(10),
    現場責任者   VARCHAR(10)
);

INSERT INTO guest VALUES
('札幌',20,NOW(),100000,'佐藤'),
('札幌',25,ADDDATE(NOW(),-1),130000,'佐藤'),
('札幌',18,ADDDATE(NOW(),-2),90000,'鈴木'),
('東京',52,NOW(),350000,'田中'),
('東京',53,ADDDATE(NOW(),-1),370000,'斉藤'),
('東京',37,ADDDATE(NOW(),-2),250000,'田中'),
('大阪',37,NOW(),210000,'村田'),
('大阪',25,ADDDATE(NOW(),-1),170000,'村田'),
('大阪',30,ADDDATE(NOW(),-2),190000,'村田');

commit;
```

[↑ **最大値/最小値に対する全カラムを取得** に戻る](#%E6%9C%80%E5%A4%A7%E5%80%A4%E6%9C%80%E5%B0%8F%E5%80%A4%E3%81%AB%E5%AF%BE%E3%81%99%E3%82%8B%E5%85%A8%E3%82%AB%E3%83%A9%E3%83%A0%E3%82%92%E5%8F%96%E5%BE%97)

---

##### 003

[↑ **縦持ちのデータを横持ちにする** に戻る](#縦持ちのデータを横持ちにする)

```sql
-- DROP TABLE IF EXISTS fuku_1;
CREATE TABLE IF NOT EXISTS fuku_1 (
    syohin_mei VARCHAR(30),
    size       VARCHAR(6),
    PRIMARY KEY(syohin_mei,size)
);

INSERT INTO fuku_1 VALUES
('上着','L'),
('上着','M'),
('上着','S'),
('シャツ','L'),
('シャツ','M'),
('ズボン','M'),
('ズボン','S');

commit;
```

[↑ **縦持ちのデータを横持ちにする** に戻る](#縦持ちのデータを横持ちにする)

---

##### 004

[↑ **横持ちのデータを縦持ちにする** に戻る](#横持ちのデータを縦持ちにする)

```sql
-- DROP TABLE IF EXISTS fuku_2;
CREATE TABLE IF NOT EXISTS fuku_2 (
    syohin_mei VARCHAR(30),
    size_l     TINYINT(1),
    size_m     TINYINT(1),
    size_s     TINYINT(1)
);

INSERT INTO fuku_2 VALUES
('上着',1,1,0),
('シャツ',1,1,1),
('ズボン',0,0,1);

commit;
```

[↑ **横持ちのデータを縦持ちにする** に戻る](#横持ちのデータを縦持ちにする)

---

##### 005

[↑ **WITH区、VIEWで重複条件などをまとめる** に戻る](#with%E5%8F%A5%E3%81%A7%E9%87%8D%E8%A4%87%E6%9D%A1%E4%BB%B6%E3%81%AA%E3%81%A9%E3%82%92%E3%81%BE%E3%81%A8%E3%82%81%E3%82%8B)

```sql:postgresql
-- DROP TABLE IF EXISTS syohin_mst;
-- DROP TABLE IF EXISTS sales;

-- 商品マスタ
CREATE TABLE IF NOT EXISTS syohin_mst (
    shop_id      VARCHAR(10),
    code         VARCHAR(100),
    name         VARCHAR(100),
    amount       INT,
    campaign_flg SMALLINT,
    PRIMARY KEY(shop_id,code)
);

-- 1個の売り上げに対し、1レコードずつ保存されていくテーブル
CREATE TABLE IF NOT EXISTS sales (
    shop_id      VARCHAR(10),
    code         VARCHAR(100),
    sales_date   TIMESTAMP
);

INSERT INTO syohin_mst(shop_id,code,name,amount,campaign_flg) VALUES
('11001','01S','消しゴム小',250,0),
('11001','01M','消しゴム中',300,0),
('11001','01L','消しゴム大',300,0),
('11001','02A','えんぴつ1本',100,0),
('11001','02B','えんぴつ5本',500,0),
('11001','03A','ボールペン',110,1),
('11002','01S','消しゴム小',300,1),
('11002','01M','消しゴム中',350,1),
('11002','01L','消しゴム大',400,1),
('11002','02A','えんぴつ1本',100,0),
('11002','02B','えんぴつ5本',500,0),
('11003','01S','消しゴム小',300,0),
('11003','01M','消しゴム中',350,1),
('11003','01L','消しゴム大',400,0),
('11003','02A','えんぴつ1本',100,0),
('11003','02B','えんぴつ5本',500,0),
('11004','01M','消しゴム中',350,1),
('11004','02A','えんぴつ1本',100,1),
('11004','03A','ボールペン',120,0),
('11005','01S','消しゴム小',250,0),
('11005','01M','消しゴム中',300,0),
('11005','01L','消しゴム大',350,0),
('11005','02A','えんぴつ1本',80,1),
('11005','02B','えんぴつ5本',400,1),
('11005','03A','ボールペン',110,1),
('11005','01X','ねり消しゴム',500,0),
('11006','01S','消しゴム小',260,1),
('11006','01M','消しゴム中',310,1),
('11006','01L','消しゴム大',360,1),
('11006','02A','えんぴつ1本',100,1),
('11006','02B','えんぴつ5本',500,1),
('11007','01S','消しゴム小',260,0),
('11007','01M','消しゴム中',310,0),
('11007','01L','消しゴム大',360,0),
('11007','02A','えんぴつ1本',110,1),
('11007','02B','えんぴつ5本',510,1);

INSERT INTO sales(shop_id,code,sales_date) VALUES
('11001','01S',now()+'-2 months'),
('11001','01S',now()+'-1 months'),
('11001','01S',now()),
('11001','01S',now()+'-1 months'),
('11001','01S',now()),
('11001','01S',now()),
('11001','01S',now()),
('11001','01S',now()),
('11001','01S',now()),
('11001','01M',now()),
('11001','01M',now()),
('11001','01M',now()),
('11001','01L',now()+'-2 months'),
('11001','02A',now()+'-2 months'),
('11001','02A',now()+'-2 months'),
('11001','02A',now()+'-1 months'),
('11001','02A',now()),
('11001','02B',now()+'-1 months'),
('11001','02B',now()+'-1 months'),
('11001','02B',now()+'-1 months'),
('11001','02B',now()),
('11001','03A',now()+'-2 months'),
('11001','03A',now()+'-1 months'),
('11002','01S',now()+'-2 months'),
('11002','01S',now()+'-1 months'),
('11002','01S',now()),
('11002','01M',now()+'-2 months'),
('11002','01M',now()+'-1 months'),
('11002','01M',now()+'-1 months'),
('11002','01M',now()),
('11002','01L',now()+'-2 months'),
('11002','01L',now()+'-1 months'),
('11002','01L',now()+'-1 months'),
('11002','01L',now()),
('11002','01L',now()),
('11002','02A',now()+'-2 months'),
('11002','02A',now()+'-1 months'),
('11002','02A',now()),
('11002','02B',now()+'-2 months'),
('11002','02B',now()+'-1 months'),
('11002','02B',now()),
('11003','01S',now()+'-1 months'),
('11003','01S',now()),
('11003','01S',now()),
('11003','01M',now()+'-2 months'),
('11003','01M',now()+'-1 months'),
('11003','01M',now()+'-1 months'),
('11003','01M',now()+'-1 months'),
('11003','01M',now()),
('11003','01L',now()+'-2 months'),
('11003','01L',now()+'-1 months'),
('11003','01L',now()),
('11003','02A',now()+'-2 months'),
('11003','02A',now()+'-1 months'),
('11003','02A',now()),
('11003','02B',now()+'-2 months'),
('11003','02B',now()+'-1 months'),
('11003','02B',now()),
('11004','01M',now()+'-2 months'),
('11004','01M',now()+'-1 months'),
('11004','01M',now()+'-1 months'),
('11004','01M',now()+'-1 months'),
('11004','01M',now()),
('11004','02A',now()+'-2 months'),
('11004','02A',now()),
('11004','03A',now()+'-1 months'),
('11004','03A',now()+'-1 months'),
('11004','03A',now()+'-1 months'),
('11004','03A',now()+'-1 months'),
('11004','03A',now()+'-1 months'),
('11005','01S',now()+'-2 months'),
('11005','01S',now()+'-1 months'),
('11005','01S',now()),
('11005','01S',now()),
('11005','01S',now()),
('11005','01M',now()+'-2 months'),
('11005','01M',now()+'-1 months'),
('11005','01M',now()),
('11005','01L',now()+'-2 months'),
('11005','01L',now()+'-1 months'),
('11005','01L',now()+'-1 months'),
('11005','01L',now()+'-1 months'),
('11005','01L',now()),
('11005','01L',now()),
('11005','01L',now()),
('11005','01L',now()),
('11005','02A',now()+'-1 months'),
('11005','02A',now()),
('11005','02B',now()+'-2 months'),
('11005','02B',now()+'-1 months'),
('11005','02B',now()),
('11005','03A',now()+'-2 months'),
('11005','03A',now()+'-2 months'),
('11005','03A',now()+'-2 months'),
('11005','03A',now()+'-1 months'),
('11005','03A',now()+'-1 months'),
('11005','03A',now()),
('11005','03A',now()),
('11005','03A',now()),
('11005','01X',now()+'-2 months'),
('11005','01X',now()+'-1 months'),
('11005','01X',now()),
('11006','01S',now()+'-2 months'),
('11006','01S',now()+'-1 months'),
('11006','01S',now()),
('11006','01S',now()),
('11006','01M',now()+'-1 months'),
('11006','01M',now()),
('11006','01L',now()+'-2 months'),
('11006','01L',now()+'-1 months'),
('11006','01L',now()),
('11006','02A',now()+'-2 months'),
('11006','02A',now()+'-1 months'),
('11006','02A',now()),
('11006','02B',now()+'-1 months'),
('11006','02B',now()),
('11006','02B',now()),
('11006','02B',now()),
('11007','01S',now()+'-1 months'),
('11007','01S',now()),
('11007','01S',now()),
('11007','01M',now()+'-2 months'),
('11007','01M',now()+'-1 months'),
('11007','01M',now()),
('11007','01L',now()+'-2 months'),
('11007','01L',now()+'-1 months'),
('11007','01L',now()),
('11007','02A',now()+'-2 months'),
('11007','02A',now()+'-1 months'),
('11007','02A',now()),
('11007','02A',now()),
('11007','02A',now()),
('11007','02A',now()),
('11007','02A',now()),
('11007','02B',now());

commit;
```

[↑ **WITH区、VIEWで重複条件などをまとめる** に戻る](#with%E5%8F%A5%E3%81%A7%E9%87%8D%E8%A4%87%E6%9D%A1%E4%BB%B6%E3%81%AA%E3%81%A9%E3%82%92%E3%81%BE%E3%81%A8%E3%82%81%E3%82%8B)

---

##### 006

[↑ **ビューでselect文を簡潔にする** に戻る](#ビューでselect文を簡潔にする)

```sql
-- DROP TABLE IF EXISTS cust;
-- DROP TABLE IF EXISTS member;
-- DROP TABLE IF EXISTS food;
-- DROP TABLE IF EXISTS food_category;
-- DROP TABLE IF EXISTS drink;

-- 来店したお客さん
CREATE TABLE IF NOT EXISTS cust(
    cust_id     INT(11),
    name        VARCHAR(20),
    delete_flg  TINYINT(1) DEFAULT 0
);
-- 会員登録したお客さん
CREATE TABLE IF NOT EXISTS member(
    member_id   INT(11),
    cust_id     INT(11),
    status      VARCHAR(10),
    delete_flg  TINYINT(1) DEFAULT 0
);
-- 注文した料理
CREATE TABLE IF NOT EXISTS food_order(
    cust_id    INT(11),
    food_id    INT(11),
    delete_flg TINYINT(1) DEFAULT 0
);
-- 料理一覧
CREATE TABLE IF NOT EXISTS food(
    food_id     INT(11),
    food_cat_id INT(11),
    name        VARCHAR(20),
    delete_flg  TINYINT(1) DEFAULT 0
);
-- 料理のカテゴリー
CREATE TABLE IF NOT EXISTS food_category(
    food_cat_id INT(11),
    name        VARCHAR(20),
    delete_flg  TINYINT(1) DEFAULT 0
);

CREATE OR REPLACE VIEW customer_view(
    cust_id,
    name
) AS
SELECT cust_id,name
FROM   cust
WHERE  delete_flg=0;

CREATE OR REPLACE VIEW member_view(
    member_id,
    cust_id,
    status
) AS
SELECT member_id,cust_id,status
FROM   member
WHERE  delete_flg=0
AND    status='加入中';

CREATE OR REPLACE VIEW food_order_view(
    cust_id,
    food_id
) AS
SELECT cust_id,food_id
FROM   food_order
WHERE  delete_flg=0;

CREATE OR REPLACE VIEW food_view(
    food_id,
    food_cat_id,
    name
) AS
SELECT food_id,food_cat_id,name
FROM   food
WHERE  delete_flg=0;

CREATE OR REPLACE VIEW food_category_view(
    food_cat_id,
    name
) AS
SELECT food_cat_id,name
FROM   food_category
WHERE  delete_flg=0;

INSERT INTO cust VALUES
(1,'佐藤',0),
(2,'鈴木',0),
(3,'田中',1),
(4,'木村',1),
(5,'村田',0);

INSERT INTO member VALUES
(1,1,'加入中',0),
(2,2,'加入中',0),
(3,3,'登録ミス',1),
(4,3,'登録ミス',1),
(5,3,'加入中',0),
(6,5,'退会済',0);

INSERT INTO food_order VALUES
(1,1,0),
(1,9,0),
(1,6,1),
(2,7,0),
(2,7,0),
(3,2,0),
(3,3,1),
(4,5,0),
(5,10,0),
(5,8,0),
(5,7,0),
(5,1,0),
(5,5,1),
(5,6,1);

INSERT INTO food VALUES
(1,1,'枝豆',0),
(2,1,'サラダ',0),
(3,1,'唐揚げ',0),
(4,1,'鍋',0),
(5,1,'寿司',0),
(6,2,'水',0),
(7,2,'ビール',0),
(8,2,'ハイボール',0),
(9,2,'焼酎',0),
(10,2,'ワイン',0);

INSERT INTO food_category VALUES
(1,'食べ物',0),
(2,'飲み物',0);

commit;
```

[↑ **ビューでselect文を簡潔にする** に戻る](#ビューでselect文を簡潔にする)

---

##### 007

[↑ **csvを加工して取り込み** に戻る](#csvを加工して取り込み)

```sql
-- DROP TABLE IF EXISTS high;
CREATE TABLE IF NOT EXISTS high(
     `観測所番号` INT(11) NOT NULL PRIMARY KEY
    ,`都道府県` VARCHAR(50)
    ,`地点` VARCHAR(50)
    ,`国際地点番号` INT(11)
    ,`現在時刻` DATETIME
    ,`今日の最高気温` FLOAT
    ,`今日の最高気温の品質情報` INT(2)
    ,`今日の最高気温起時` TIME
    ,`今日の最高気温起時の品質情報` INT(2)
    ,`平年差` FLOAT
    ,`前日差` FLOAT
    ,`該当旬（月）` INT(2)
    ,`該当旬（旬）` INT(2)
    ,`極値更新` INT(2)
    ,`10年未満での極値更新` INT(2)
    ,`今季最高` INT(2)
    ,`今年の最高気温（昨日まで）` FLOAT
    ,`今年の最高気温（昨日まで）の品質情報` INT(2)
    ,`今年の最高気温（昨日まで）を観測した起日` DATE
    ,`昨日までの観測史上1位の値` FLOAT
    ,`昨日までの観測史上1位の値の品質情報` INT(2)
    ,`昨日までの観測史上1位の値を観測した起日` DATE
    ,`昨日までの1月の1位の値` FLOAT
    ,`昨日までの1月の1位の値の品質情報` INT(2)
    ,`昨日までの1月の1位の値の起日` DATE
    ,`統計開始年` INT(11)
) DEFAULT CHARSET=utf8;
```

[↑ **csvを加工して取り込み** に戻る](#csvを加工して取り込み)

---

##### 008

[↑ **表結合で私がわかりやすいと思った説明** に戻る](#表結合で私がわかりやすいと思った説明)

```sql
-- DROP TABLE IF EXISTS animal;
-- DROP TABLE IF EXISTS bunrui;
-- DROP TABLE IF EXISTS syubetsu;

-- 動物
CREATE TABLE IF NOT EXISTS animal(
    id   INT(11),
    bunrui_id INT(11),
    name VARCHAR(20)
);
-- 分類
CREATE TABLE IF NOT EXISTS bunrui(
    id          INT(11),
    syubetsu_id INT(11),
    name        VARCHAR(20)
);
-- 種別
CREATE TABLE IF NOT EXISTS syubetsu(
    id        INT(11),
    name      VARCHAR(20)
);

INSERT INTO animal VALUES
(1,1,'犬'),
(2,3,'カエル'),
(3,10,'イワシ');

INSERT INTO bunrui VALUES
(1,1,'哺乳類'),
(2,1,'鳥類'),
(3,1,'両生類'),
(4,1,'爬虫類'),
(5,2,'節足動物');

INSERT INTO syubetsu VALUES
(1,'脊椎動物'),
(2,'無脊椎動物');

commit;
```

[↑ **表結合で私がわかりやすいと思った説明** に戻る](#表結合で私がわかりやすいと思った説明)