#はじめに
この記事で作成したWorldDatabase on Docker(for Mac)のデータ閲覧がてら操作系のSQLをメモ化しました。
SQLの出展は「スッキリわかる SQL 入門ドリル215問付き! (スッキリシリーズ)」。
このシリーズすごい好きですwわかりやすさは全てに勝る！

#やってみる
##データベース, テーブル, カラムの確認

> SHOW databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| world              |
+--------------------+

> use world
Database changed

mysql> SHOW tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+

> SHOW columns FROM city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+

##INSERT, SELECT(WHERE), UPDATE, DELETE


> INSERT INTO city VALUES (99999, 'Okome', 'USA', 'Okome Chan', 99999);

> SELECT * FROM city WHERE ID = 99999;
+-------+-------+-------------+------------+------------+
| ID    | Name  | CountryCode | District   | Population |
+-------+-------+-------------+------------+------------+
| 99999 | Okome | USA         | Okome Chan |      99999 |
+-------+-------+-------------+------------+------------+

> UPDATE city SET Name = 'Okome-chan' WHERE ID = 99999;
Rows matched: 1  Changed: 1  Warnings: 0

> SELECT * FROM city WHERE Name = 'Okome-chan';
+-------+------------+-------------+------------+------------+
| ID    | Name       | CountryCode | District   | Population |
+-------+------------+-------------+------------+------------+
| 99999 | Okome-chan | USA         | Okome Chan |      99999 |
+-------+------------+-------------+------------+------------+

> DELETE FROM city WHERE ID = 99999;

> SELECT * FROM city WHERE ID = 99999;
Empty set (0.00 sec)

##コメントの書き方×2

-- コメント（行末まで有効）
/* コメント */

##ASで別名の定義

> SELECT ID, Name FROM city WHERE ID = 99999;
+-------+-------+
| ID    | Name  |
+-------+-------+
| 99999 | Okome |
+-------+-------+

> SELECT ID AS id, Name AS name FROM city WHERE ID = 99999;
+-------+-------+
| id    | name  |
+-------+-------+
| 99999 | Okome |
+-------+-------+

##DISTINCT, ORDER BY, LIMIT

> SELECT DISTINCT ID from city;
4081 rows in set (0.01 sec)

# 昇順
> SELECT DISTINCT ID FROM city ORDER BY ID ASC;

# 降順
> SELECT DISTINCT ID FROM city ORDER BY ID DESC;

# 上位3行の出力
> SELECT DISTINCT ID FROM city ORDER BY ID desc LIMIT 3;

# 列番号でカラムの代替が可能
> SELECT DISTINCT ID FROM city ORDER BY 1 ASC;

# 例：主キーを列番号でasc
> SELECT * FROM city ORDER BY 1 DESC;

##集合演算子(UNION, EXCEPT, INTERSECT)
適した列がないですねw

# UNION(和)
> SELECT Name FROM city UNION SELECT Name FROM country;

# EXCEPT(差)
> SELECT Name FROM city EXCEPT SELECT Name FROM country;

# INTERSECT(積)
> SELECT Name FROM city INTERSECT SELECT Name FROM country;

##計算式

# SELECT文の選択列リストで計算する
> SELECT Name, Population FROM country WHERE Population > 22720000 LIMIT 3;
+------------+------------+
| Name       | Population |
+------------+------------+
| Argentina  |   37032000 |
| Bangladesh |  129155000 |
| Brazil     |  170115000 |
+------------+------------+

> SELECT Name, Population, Population + 50000000 AS Population2 FROM country WHERE Population > 22720000 LIMIT 3;
+------------+------------+-------------+
| Name       | Population | Population2 |
+------------+------------+-------------+
| Argentina  |   37032000 |    87032000 |
| Bangladesh |  129155000 |   179155000 |
| Brazil     |  170115000 |   220115000 |
+------------+------------+-------------+

# CASE演算子。BashとかのCASEと使い方は同じですね。長いので改行してます。
# when〜thenには演算子で計算式を使うこともできる。
mysql> SELECT Name, Population,
    -> CASE Name
    -> WHEN 'Argentina' THEN 'Argentina2'
    -> WHEN 'Bangladesh' THEN 'Bangladesh2'
    -> WHEN 'Brazil' THEN 'Brazil2';
    -> END AS Population3
    -> FROM country WHERE Population > 22720000 LIMIT 3
    -> ;
+------------+------------+-------------+
| Name       | Population | Population3 |
+------------+------------+-------------+
| Argentina  |   37032000 | Argentina2  |
| Bangladesh |  129155000 | Bangladesh2 |
| Brazil     |  170115000 | Brazil2     |
+------------+------------+-------------+

##関数
よく使いそうな気がする関数だけ試しました。
あとは都度調べる。~~めんどうやしね。~~

# LENGH関数
> SELECT Name, LENGTH(Name) AS Name2 FROM country WHERE Population > 22720000 LIMIT 3;
+------------+-------+
| Name       | Name2 |
+------------+-------+
| Argentina  |     9 |
| Bangladesh |    10 |
| Brazil     |     6 |
+------------+-------+

# ROUND関数（指定行で四捨五入する）
> SELECT Name, ROUND(Population, -7) AS Population4 FROM country WHERE Population > 22720000 LIMIT 3;
+------------+-------------+
| Name       | Population4 |
+------------+-------------+
| Argentina  |    40000000 |
| Bangladesh |   130000000 |
| Brazil     |   170000000 |
+------------+-------------+

> SELECT Name, ROUND(Population, -8) AS Population4 FROM country WHERE Population > 22720000 LIMIT 3;
+------------+-------------+
| Name       | Population4 |
+------------+-------------+
| Argentina  |           0 |
| Bangladesh |   100000000 |
| Brazil     |   200000000 |
+------------+-------------+

# CURRENT_DATE, CURRENT_TIME
> SELECT Name, Population, CURRENT_DATE AS Population5 FROM country WHERE Population > 22720000 LIMIT 3;
+------------+------------+-------------+
| Name       | Population | Population5 |
+------------+------------+-------------+
| Argentina  |   37032000 | 2019-12-14  |
| Bangladesh |  129155000 | 2019-12-14  |
| Brazil     |  170115000 | 2019-12-14  |
+------------+------------+-------------+

mysql> SELECT Name, Population, CURRENT_TIME AS Population5 FROM country WHERE Population > 22720000 LIMIT 3;
+------------+------------+-------------+
| Name       | Population | Population5 |
+------------+------------+-------------+
| Argentina  |   37032000 | 07:05:49    |
| Bangladesh |  129155000 | 07:05:49    |
| Brazil     |  170115000 | 07:05:49    |
+------------+------------+-------------+

##集計関数

# SUM, AVG, MAX, MIN, COUNT
> SELECT SUM(Population) AS Population5 FROM country;
+-------------+
| Population6 |
+-------------+
|  6078749450 |
+-------------+

> SELECT AVG(Population) AS Population5 FROM country;
+---------------+
| Population6   |
+---------------+
| 25434098.1172 |
+---------------+

> SELECT MAX(Population) AS Population5 FROM country;
+-------------+
| Population6 |
+-------------+
|  1277558000 |
+-------------+

> SELECT MIN(Population) AS Population5 FROM country;
+-------------+
| Population6 |
+-------------+
|           0 |
+-------------+

> SELECT COUNT(*) AS Population5 FROM country;
+-------------+
| Population6 |
+-------------+
|         239 |
+-------------+

> SELECT COUNT(Name) AS Population5 FROM country;
+-------------+
| Population6 |
+-------------+
|         239 |
+-------------+

##グループ化

> SELECT Continent, SUM(Population) AS total FROM country GROUP BY Continent;
+---------------+------------+
| Continent     | total      |
+---------------+------------+
| Asia          | 3705025700 |
| Europe        |  730074600 |
| North America |  482993000 |
| Africa        |  784475000 |
| Oceania       |   30401150 |
| Antarctica    |          0 |
| South America |  345780000 |
+---------------+------------+

# 集計結果で絞り込むにはHAVINGを使う
> SELECT Continent, SUM(Population) AS total FROM country GROUP BY Continent HAVING SUM(Population) > 0;
+---------------+------------+
| Continent     | total      |
+---------------+------------+
| Asia          | 3705025700 |
| Europe        |  730074600 |
| North America |  482993000 |
| Africa        |  784475000 |
| Oceania       |   30401150 |
| South America |  345780000 |
+---------------+------------+

#おわりに
金額とかの集計しやすい列はないので、そこは別でクリアすれば文句なしですね。
好き放題＆使い捨てできるDBがサッと起動する、っていう可搬性のほうが優ってます。

SQLは小文字派だったのですが、字面にすると大文字のほうが断然読みやすいですねw
以上です