はじめに
この記事で作成した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
以上です