MySQL (MariaDB) で SQL を体験してみよう

オンラインハンズオンのためのテキストです。

• レベル はじめてSQLを触ってみる
• 想定時間 1時間

事前準備

今回使う情報を手元にメモしておいてください。

• サーバ名
• ssh ユーザ名
• ssh パスワード
• screen セッション名
• MySQL ユーザ名
• MySQL パスワード
• MySQL データベース名

サーバに接続

ssh で指定したサーバに接続してください。
接続できたら以下のように表示されます。

Welcome to Ubuntu 18.04.2 LTS (GNU/Linux 5.3.0-1030-aws x86_64)

 * Documentation:  https://help.ubuntu.com
 * Management:     https://landscape.canonical.com
 * Support:        https://ubuntu.com/advantage

  System information as of Wed Jul 15 06:15:17 UTC 2020

  System load:  0.07              Processes:           93
  Usage of /:   46.1% of 7.69GB   Users logged in:     0
  Memory usage: 23%               IP address for eth0: 172.31.8.62
  Swap usage:   0%

 * "If you've been waiting for the perfect Kubernetes dev solution for
   macOS, the wait is over. Learn how to install Microk8s on macOS."

   https://www.techrepublic.com/article/how-to-install-microk8s-on-macos/

  Get cloud support with Ubuntu Advantage Cloud Guest:
    http://www.ubuntu.com/business/services/cloud

 * Canonical Livepatch is available for installation.
   - Reduce system reboots and improve kernel security. Activate at:
     https://ubuntu.com/livepatch

115 packages can be updated.
0 updates are security updates.


Last login: Wed Jul 15 05:45:36 2020 from 223.135.97.92
ubuntu@ip-172-31-8-62:~$

今後、以下のように表示されているところを、

ubuntu@ip-172-31-8-62:~$ 

以下のように省略して表示しますので覚えていてください。$ のマークは入力可能であることを示していて、プロンプトと言います。プロンプトは状況により $ や # , > などがあります。

$

さて、$ の後ろに以下のように入力します。

$ screen -x hogehoge

この例では hogehoge が screen セッション名です。自分の screen セッション名に入れ替えてください。
$ の表示の後にキーボードから入力してエンターを押します。$ を入力する必要はありません。
この命令で screen セッションに接続します。 セッションを共有して、作業を講師側でモニターします。

MySQL に接続してみよう！

$ mysql -u root -p 

として、mysql に接続します。上の例では root が MySQL ユーザ名です。先にメモした MySQL ユーザ名に入れ替えてください。
パスワードを打ち込むと、MySQLに接続します。

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 42
Server version: 10.1.44-MariaDB-0ubuntu0.18.04.1 Ubuntu 18.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database test2;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> 

のように表示されます。 MariaDB と表示されていますね。これは MariaDB というデータベースが起動したところです。MariaDB は MySQL と互換を持つデータベースです。今回のワークショップでは MariaDB を使いますが、以降の説明は MySQL として説明をしていきます。
今後、以下のように表示されているところを、

MariaDB [(none)]> 

以下のように省略して表示します。

> 

先程、 $ をプロンプトと説明しましたが、 > は MySQLのプロンプトです。
この後、入力が必要な時は > の表示の後にキーボードから入力してエンターを押します。> を入力する必要はありません。

MySQL を操作してみよう！

データベースの選択

> SHOW DATABASES;

として、データベースの一覧を得ます。

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test2              |
+--------------------+
5 rows in set (0.00 sec)

test データベースを使うとすると、

> USE test;

として test データベースを選択します。

テーブル一覧

データベースには、テーブルが含まれています。どのテーブルがあるかは以下のようにして調べることができます。

> SHOW TABLES;

testというテーブルがあるのが見えますね。

show tables;
+----------------+
| Tables_in_test |
+----------------+
| albums         |
| artists        |
| musics         |
| test           |
+----------------+
4 rows in set (0.00 sec)

テーブルにアクセス

test テーブルに何が入っているか見てみましょう。

> SELECT * FROM test;

としてみます。

+----+---------------------------------+---------+---------+---------+---------+---------+---------+---------+---------+
| id | area                            | ac1950  | ac1960  | ac1970  | ac1980  | ac1990  | ac2000  | ac2010  | ac2020  |
+----+---------------------------------+---------+---------+---------+---------+---------+---------+---------+---------+
| 18 | Africa                          |  227794 |  283361 |  363448 |  476386 |  630350 |  810984 | 1039304 | 1340598 |
| 19 | Asia                            | 1404909 | 1705041 | 2142480 | 2649578 | 3226099 | 3741263 | 4209594 | 4641055 |
| 20 | Europe                          |  549329 |  605407 |  656919 |  693567 |  720858 |  725558 |  736413 |  747636 |
| 21 | Latin America and the Caribbean |  168821 |  220470 |  286676 |  361253 |  442840 |  521836 |  591352 |  653962 |
| 22 | Northern America                |  172603 |  204649 |  230992 |  254007 |  279785 |  312427 |  343287 |  368870 |
| 23 | Oceania                         |   12976 |   16022 |   19922 |   23212 |   27299 |   31425 |   36873 |   42678 |
+----+---------------------------------+---------+---------+---------+---------+---------+---------+---------+---------+
6 rows in set (0.00 sec)

テーブルに入っていたのは世界の人口動態でした。
(WPP2019_POP_F01_1_TOTAL_POPULATION_BOTH_SEXES.xlsx Creative Commons license CC BY 3.0 より抜粋)

MySQLサーバ、データベース、テーブル

3つの関連性は、以下のように考えるとわかりやすいでしょう。

今までに行った、ssh でのサーバログインからの流れを、上記のような図で表すと以下のようになります。

SQLとは？

先程行った SELECT * FROM test;

というのは SQL の文です。SQLはデータベースを扱う言語です。

SELECT というのはSQLの命令です。SELECT 以外にも、

• INSERT INTO
• UPDATE
• DELETE

などの命令があります。SQL は他のコンピュータ言語と同じように、変数やIF文、LOOP文も使えます。しかしながら IF や LOOP は SQL から見るとオマケのような機能となります。

コンピュータを専門家でなくても扱うためには、コンピュータ言語を使うことがネックになります。特に IF や ループの使用が大きな問題となりました。SQL が生まれた背景には、ループをなくしてエンドユーザが使えることを目指すことがありました。

Relational database: a practical foundation for productivity
https://dl.acm.org/doi/10.1145/1283920.1283937

RDBMS とは？

1970年、当時 IBM の研究所勤務だった エドガー.F.コッド の論文 “A Relational Model of Data for Large Shared Data Banks” が一般向けの学術雑紙に公開されました。
この概念を元に作成されたデータベースシステムが RDBMS です。
世の中のデータはいろんな形式がありますが、RDBMSでは、「テーブル」という Excel の表のような形で全てを管理していきます。

データベースとは？

今使っている　MySQL は、リレーショナル・データーベース(RDB)という種類のデータベースです。
リレーショナル・データベースを含んだデータベースシステムを RDBMS と言います。
リレーショナル・データベース以外のデータベースはカード型データーベース、オブジェクト型データベースなどがあります。

MySQL とは？

RDBMSの機能として、データの保管や検索ができます。しかしながら、表のような形式でデータを管理するのなら、Excel を使ってもできますよね。

それに対するメリットとしては、以下のようなものがあります。

• 同時にアクセスができる
• 同時に更新を行ってもデータが壊れない
• 分散処理の機能がある
• SQL が使える

また、RDBMS の中で MySQL が持っている特徴としては、以下のようなものがあります。

• オープンソースである
• 互換性を持つ MariaDB などがある
• LAMP (Linux/Apache/MySQL/Perl or PHP or Python) という言葉が表しているように、非常によく使われている
• ノウハウなどがインターネット上に豊富

正規化

RDB の理論は全てのデータを表の形で管理するということになります。
しかし表の形式にして世の中のデータがうまく扱えるのでしょううか？ RDB は世の中のデータを正規化という方法で整理して扱います。

例えば、表といっても Excel でよく見られる以下のような表では、データ処理に馴染みません。

例えば、アルバム名で並べ変えるにはどうしたらいいでしょうか？ CDのリスト中に、特定のアーティストの曲が何曲あるかどうか調べるにはどうしたらいいでしょうか？

これを扱いやすくするために、正規化という処理を行って、RDBに即した形に直します。

なお、ここで使う例は独語版 WikiPedia のものを元にしています。 https://de.wikipedia.org/wiki/Normalisierung_(Datenbank)
(CC BY-SA 3.0)

第一正規化

元の形は赤くマークしたセルに、複数の情報が書かれてますよね。これを1つのセルに1つの情報に変更します。
Excelなどではセルの結合などで行ったりしますが、そうではなく、冗長になりますが下のような表に直します。

これで、特定のアーティストの曲が何曲あるか、などの調査や、アーティストでの並べ替えなどもやりやすくなりますね。

第二正規化

次に、表のうちのどのデータも、決まった場所のID(キー)によって識別できるようにします。

先程のデータでは、Titleや白い箇所はTrackとCD_IDの組み合わせで識別されます。しかしながら赤い場所の識別はどうしたらいいでしょう？ 赤い場所はCD_IDで識別することになり、決まった場所のIDという原則から外れてしまいます。

そこで、このようにテーブルを分離します。
分離したテーブルは、共通する CD_ID で参照することにします。

アルバムとアーティストのテーブル

曲テーブル

第三正規化

ここでは、テーブルのどのデータも、キーに従属するものになるようにします。

先程第二正規化した表の、赤いところに着目します。赤いところはキーである CD_ID に従属しませんよね。むしろ CD_ID はアーティストに従属しますよね。アーティストのStart年もアーティストに従属します。このままだと Anastacia のアルバムが増えたらアーティストの Start 年が増えてしまいます。1999年に Start したアーティスストは何人いるかを集計できなくなってしまいますね。

そこで、Artist_IDというキーを新たに導入し、テーブルを分離します。

アルバムテーブル

アーティストテーブル

第三正規化まで終わったテーブルおよび関係性は以下のようになります。

正規化の実際

扱いやすくするために、実際は多少崩してデータを使うことがあります。

データを操作してみる

ちょっと復習

先に、

• USE TEST;
• SHOW DATABASES;
• SHOW TABLES;

を操作しましたよね。もう一度思い出してみましょう。キーボードの上矢印で昔の履歴が出てきます。覚えておきましょう(重要)。

SELECT

先程の例のデータが、 musicsテーブルなどに入っています。
以下のようにすれば、内容を見ることができます。

> SELECT CD_ID,Truck,Title FROM musics;
+-------+-------+----------------------------+
| CD_ID | Truck | Title                      |
+-------+-------+----------------------------+
|  4711 |     1 | Not That Kind              |
|  4711 |     2 | I'm Outta Love             |
|  4711 |     3 | Cowboys & Kisses           |
|  4712 |     1 | Shine On You Crazy Diamond |
|  4713 |     1 | Paid my Dues               |
+-------+-------+----------------------------+
5 rows in set (0.00 sec)

なお、表示の順番を変えるにはこうします。

> SELECT Title,CD_ID FROM musics;
+----------------------------+-------+
| Title                      | CD_ID |
+----------------------------+-------+
| Not That Kind              |  4711 |
| I'm Outta Love             |  4711 |
| Cowboys & Kisses           |  4711 |
| Shine On You Crazy Diamond |  4712 |
| Paid my Dues               |  4713 |
+----------------------------+-------+
5 rows in set (0.00 sec)

全部を抽出するにはこうします。

> SELECT * FROM musics;
+----------+-------+-------+----------------------------+
| music_id | CD_ID | Truck | Title                      |
+----------+-------+-------+----------------------------+
|        1 |  4711 |     1 | Not That Kind              |
|        2 |  4711 |     2 | I'm Outta Love             |
|        3 |  4711 |     3 | Cowboys & Kisses           |
|        4 |  4712 |     1 | Shine On You Crazy Diamond |
|        5 |  4713 |     1 | Paid my Dues               |
+----------+-------+-------+----------------------------+
5 rows in set (0.00 sec)

ここで表示されるカラムの順番は、テーブルの構造の通りの順番ですが、データの管理には関係なく、順番の操作は SELECT 分で行うか、受信した後の表示で行うことになります。先の例から、misic_idを追加しています。
他のテーブルも見てみましょう。

> SELECT * FROM artists;
+-----------+------------+-------+
| Artist_ID | Artist     | Start |
+-----------+------------+-------+
|       311 | Anastacia  |  1999 |
|       312 | Pink Floyd |  1965 |
+-----------+------------+-------+
2 rows in set (0.00 sec)

> SELECT * from albums;
+-------+--------------------+-----------+--------------+
| CD_ID | Albumtitle         | Artist_ID | Release_Year |
+-------+--------------------+-----------+--------------+
|  4711 | Not That Kind      |       311 |         2000 |
|  4712 | Wish You Ware Here |       312 |         1975 |
|  4713 | Freak of Nature    |       311 |         2001 |
+-------+--------------------+-----------+--------------+
3 rows in set (0.00 sec)

SELECT 文の活用

リレーション

先に分割したいくつかの表をいっしょにして、データを表示するにはリレーションという昨日を使います。

SELECT 文の FROM 句にテーブル名を並べれば、複数の表を扱うことができます。

> SELECT Albumtitle,Truck,Title FROM albums,musics;
+--------------------+-------+----------------------------+
| Albumtitle         | Truck | Title                      |
+--------------------+-------+----------------------------+
| Not That Kind      |     1 | Not That Kind              |
| Wish You Ware Here |     1 | Not That Kind              |
| Freak of Nature    |     1 | Not That Kind              |
| Not That Kind      |     2 | I'm Outta Love             |
| Wish You Ware Here |     2 | I'm Outta Love             |
| Freak of Nature    |     2 | I'm Outta Love             |
| Not That Kind      |     3 | Cowboys & Kisses           |
| Wish You Ware Here |     3 | Cowboys & Kisses           |
| Freak of Nature    |     3 | Cowboys & Kisses           |
| Not That Kind      |     1 | Shine On You Crazy Diamond |
| Wish You Ware Here |     1 | Shine On You Crazy Diamond |
| Freak of Nature    |     1 | Shine On You Crazy Diamond |
| Not That Kind      |     1 | Paid my Dues               |
| Wish You Ware Here |     1 | Paid my Dues               |
| Freak of Nature    |     1 | Paid my Dues               |
+--------------------+-------+----------------------------+
15 rows in set (0.00 sec)

あれれ、なんだかいっぱい出ましたね。これは、albums の CD_ID と musics の CD_ID が同じということが MySQL にはわからないためにこうなります。albums の CD_ID と musics の CD_ID が同じということを教えるには以下のようにします。

> SELECT Albumtitle,Truck,Title FROM albums,musics WHERE albums.CD_ID=musics.CD_ID;
+--------------------+-------+----------------------------+
| Albumtitle         | Truck | Title                      |
+--------------------+-------+----------------------------+
| Not That Kind      |     1 | Not That Kind              |
| Not That Kind      |     2 | I'm Outta Love             |
| Not That Kind      |     3 | Cowboys & Kisses           |
| Wish You Ware Here |     1 | Shine On You Crazy Diamond |
| Freak of Nature    |     1 | Paid my Dues               |
+--------------------+-------+----------------------------+
5 rows in set (0.00 sec)

同じようにして、アーティストも表示してみましょう。

> SELECT Artist,Albumtitle,Truck,Title FROM albums,musics,artists WHERE albums.CD_ID=musics.CD_ID and albums.Artist_ID=artists.Artist_ID;
+------------+--------------------+-------+----------------------------+
| Artist     | Albumtitle         | Truck | Title                      |
+------------+--------------------+-------+----------------------------+
| Anastacia  | Not That Kind      |     1 | Not That Kind              |
| Anastacia  | Not That Kind      |     2 | I'm Outta Love             |
| Anastacia  | Not That Kind      |     3 | Cowboys & Kisses           |
| Pink Floyd | Wish You Ware Here |     1 | Shine On You Crazy Diamond |
| Anastacia  | Freak of Nature    |     1 | Paid my Dues               |
+------------+--------------------+-------+----------------------------+

WHERE

上のSQLを上矢印で呼び出して、WHEREにつける情報を追加します。

> SELECT Artist,Albumtitle,Truck,Title FROM albums,musics,artists WHERE albums.CD_ID=musics.CD_ID and albums.Artist_ID=artists.ARTIST_iD and Artist="Anastacia";
+-----------+-----------------+-------+------------------+
| Artist    | Albumtitle      | Truck | Title            |
+-----------+-----------------+-------+------------------+
| Anastacia | Not That Kind   |     1 | Not That Kind    |
| Anastacia | Not That Kind   |     2 | I'm Outta Love   |
| Anastacia | Not That Kind   |     3 | Cowboys & Kisses |
| Anastacia | Freak of Nature |     1 | Paid my Dues     |
+-----------+-----------------+-------+------------------+
4 rows in set (0.00 sec)

この例では、 Anastacia のみのデータを抽出しています。

ORDER BY

データの順番は、そのままでは SQLでは全く意味がありません。順番を思い通りにしたい場合は、ソート方法を指定します。

> SELECT Artist,Albumtitle,Truck,Title FROM albums,musics,artists WHERE albums.CD_ID=musics.CD_ID and albums.Artist_ID=artists.ARTIST_iD ORDER BY  Artist;
+------------+--------------------+-------+----------------------------+
| Artist     | Albumtitle         | Truck | Title                      |
+------------+--------------------+-------+----------------------------+
| Anastacia  | Not That Kind      |     1 | Not That Kind              |
| Anastacia  | Not That Kind      |     2 | I'm Outta Love             |
| Anastacia  | Freak of Nature    |     1 | Paid my Dues               |
| Anastacia  | Not That Kind      |     3 | Cowboys & Kisses           |
| Pink Floyd | Wish You Ware Here |     1 | Shine On You Crazy Diamond |
+------------+--------------------+-------+----------------------------+
5 rows in set (0.00 sec)

逆順である必要がある場合には DESC をつけます。

> SELECT Artist,Albumtitle,Truck,Title FROM albums,musics,artists WHERE albums.CD_ID=musics.CD_ID and albums.Artist_ID=artists.ARTIST_iD ORDER BY Artist DESC; 
+------------+--------------------+-------+----------------------------+
| Artist     | Albumtitle         | Truck | Title                      |
+------------+--------------------+-------+----------------------------+
| Pink Floyd | Wish You Ware Here |     1 | Shine On You Crazy Diamond |
| Anastacia  | Not That Kind      |     1 | Not That Kind              |
| Anastacia  | Not That Kind      |     2 | I'm Outta Love             |
| Anastacia  | Freak of Nature    |     1 | Paid my Dues               |
| Anastacia  | Not That Kind      |     3 | Cowboys & Kisses           |
+------------+--------------------+-------+----------------------------+
5 rows in set (0.00 sec)

COUNT

集計してみましょう。

> SELECT Artist,COUNT(Truck) FROM albums,musics,artists WHERE albums.CD_ID=musics.CD_ID and albums.Artist_ID=artists.ARTIST_ID GROUP BY Artist;
+------------+--------------+
| Artist     | COUNT(Truck) |
+------------+--------------+
| Anastacia  |            4 |
| Pink Floyd |            1 |
+------------+--------------+
2 rows in set (0.00 sec)

人口情勢を使う

ここから先は test テーブルを使ってみましょう。

> SELECT * FROM test;
+----+---------------------------------+---------+---------+---------+---------+---------+---------+---------+---------+
| id | area                            | ac1950  | ac1960  | ac1970  | ac1980  | ac1990  | ac2000  | ac2010  | ac2020  |
+----+---------------------------------+---------+---------+---------+---------+---------+---------+---------+---------+
| 18 | Africa                          |  227794 |  283361 |  363448 |  476386 |  630350 |  810984 | 1039304 | 1340598 |
| 19 | Asia                            | 1404909 | 1705041 | 2142480 | 2649578 | 3226099 | 3741263 | 4209594 | 4641055 |
| 20 | Europe                          |  549329 |  605407 |  656919 |  693567 |  720858 |  725558 |  736413 |  747636 |
| 21 | Latin America and the Caribbean |  168821 |  220470 |  286676 |  361253 |  442840 |  521836 |  591352 |  653962 |
| 22 | Northern America                |  172603 |  204649 |  230992 |  254007 |  279785 |  312427 |  343287 |  368870 |
| 23 | Oceania                         |   12976 |   16022 |   19922 |   23212 |   27299 |   31425 |   36873 |   42678 |
+----+---------------------------------+---------+---------+---------+---------+---------+---------+---------+---------+
6 rows in set (0.00 sec)

sum

AC2020の世界人口合計を出してみます。

> SELECT SUM(ac2020) FROM test;
+-------------+
| SUM(ac2020) |
+-------------+
|     7794799 |
+-------------+
1 row in set (0.00 sec)

779万と出てきました。実はこの単位は千人なので、1000倍してみましょう。

> SELECT SUM(ac2020)*1000 AS WORLD FROM test;
+------------+
| WORLD      |
+------------+
| 7794799000 |
+------------+
1 row in set (0.00 sec)

77億人となりましたね。ここでは、SUM(ac2020)*1000 の名前を AS 句で WORLD という名前に付け替えもしています。

LIMIT

今のデータは少ないのでいいですが、何十万件もあるようなデータだといっぱい出ると大変ですね。とりあえずの表示するには、LIMIT句で画面に収まるようにします。

> SELECT area,ac2020 FROM test ORDER BY ac2020 LIMIT 1;
+---------+--------+
| area    | ac2020 |
+---------+--------+
| Oceania |  42678 |
+---------+--------+
1 row in set (0.00 sec)

ORDERでソートしましたが、その一番最初のデータが表示されました。

データの登録

ここから先は本日は行いませんが、紹介だけ行います。

テーブルを作ってみましょう。

 > CREATE TABLE mydata( id int(11) , name varchar(50),age int,  PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT

データを登録してみます。

> INSERT INTO mydata VALUES (1,"Anonymous",100);

UPDATE

SELECT の応用で、このようにします。

> UPDATE mydata set age = ‘25’ where name'Anonymous';

データを登録します。

自分のデータベースを作る

> CREATE DATABASE テストデータベース;

(権限が必要です)