MySQL データベースの基本

データベースやSQLと聞くと難しく感じますが、単なる互いに紐づいたデータの集合です。

データベースの作成
テーブルの作成 CREATE TABLE
SELECTでデータを読み込む
データの追加 INSERT
UPDATEでデータ更新
VIEWの作成
関数を使う
- LENGTH 文字列の長さを返す
- LOWER 小文字に変換

データベースの作成

データベースは表のようなものです。データーベースは下のようなテーブルの集まりのことを指します。

マイナンバー	氏名	住所	性別
001	Taro	Tokyo	man
002	Hana	Hokkaido	women

国民個人情報データベース・個人情報テーブル

1行目のデータはTaroさんの個人情報、2行目はHanaさんの個人情報というように行ごとに紐づいています。

まずはkokumin_kojinという名前のデータベースを作成します。

mysql> CREATE DATABASE kokumin_kojin;
#SQLの基本は命令文(create database)入力と最後にセミコロン;をつけることです。
#命令文は大文字で書かれることが多いですが区別されません。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kokumin_kojin      |
| mysql              |
+--------------------+
3 rows in set (0.00 sec)

テーブルの作成 CREATE TABLE

実際のデータはテーブルの中に入力します。

個人情報を登録するテーブル(kojinzyouhou)をkokumin_kojinデータベースの中に作成します。

列のキーはmynumber, name, zyusyo, seibetsuです。

# CREATE TABLEでテーブルを作成します。書式は"データベース"."作成したいテーブル名”
mysql> create table kokumin_kojin.kojinzyouhou (
        mynumber INT auto_increment not null primary key,
        name VARCHAR(100),
        zyusyo VARCHAR(100),
        seibetsu INT(5),
        created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

#テーブルの作成を確認します。まずはuseでデータベースを選択します。
mysql> use kokumin_kojin
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-------------------------+
| Tables_in_kokumin_kojin |
+-------------------------+
| kojinzyohou             |
+-------------------------+
1 row in set (0.00 sec)

#descでkojinzyohouテーブルの中身を見る
mysql> desc kojinzyohou;
+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| mynumber   | int          | NO   | PRI | NULL              | auto_increment    |
| name       | varchar(100) | YES  |     | NULL              |                   |
| zyusyo     | varchar(100) | YES  |     | NULL              |                   |
| seibetsu   | int          | YES  |     | NULL              |                   |
| created_at | timestamp    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+
5 rows in set (0.00 sec)

データの型について

テーブルに入力するデータはデータ型を設定します。

データの型にはいくつかありますが代表的な型を紹介します

INT -> 数値
VARCHAR -> 可変長文字列
- VARCHAR(100)のように文字列の最大長を指定できる

テーブルの削除 DROP TABLE

テーブルの削除はdrop tableで行います。

mysql> DROP TABLE kojinzyohou;

テーブルに新しい列を追加 ALTER TABLE

作成したテーブルに新しく列を追加したい場合は alter tableを使います。

#まずは現在のテーブル情報を確認
mysql> desc kojinzyohou;
+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| mynumber   | int          | NO   | PRI | NULL              | auto_increment    |
| name       | varchar(100) | YES  |     | NULL              |                   |
| zyusyo     | varchar(100) | YES  |     | NULL              |                   |
| seibetsu   | int          | YES  |     | NULL              |                   |
| created_at | timestamp    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+
5 rows in set (0.00 sec)

#ここにageの列を追加する
mysql>  ALTER TABLE kojinzyohou ADD COLUMN age INT(3);
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 1

#ageが追加されていることを確認する
mysql> desc kojinzyohou;
+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| mynumber   | int          | NO   | PRI | NULL              | auto_increment    |
| name       | varchar(100) | YES  |     | NULL              |                   |
| zyusyo     | varchar(100) | YES  |     | NULL              |                   |
| seibetsu   | int          | YES  |     | NULL              |                   |
| created_at | timestamp    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| age        | int          | YES  |     | NULL              |                   |
+------------+--------------+------+-----+-------------------+-------------------+
6 rows in set (0.00 sec)

列名を変更 CHANGE COLUMN

列名を後から変更したい場合、ALTER TABLE “テーブル名” CHANGE COLUMN を使います。

mysql> ALTER TABLE kojinzyohou CHANGE COLUMN age nenrei INT;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc kojinzyohou;
+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| mynumber   | int          | NO   | PRI | NULL              | auto_increment    |
| name       | varchar(100) | YES  |     | NULL              |                   |
| zyusyo     | varchar(100) | YES  |     | NULL              |                   |
| seibetsu   | int          | YES  |     | NULL              |                   |
| created_at | timestamp    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| nenrei     | int          | YES  |     | NULL              |                   |
+------------+--------------+------+-----+-------------------+-------------------+
6 rows in set (0.00 sec)

SELECTでデータを読み込む

SELECT文はテーブルからデータを読むときに使います。

実際にテーブルデータが入ったものを使いたいので公式で配布しているデータベース例を使います。

サンプルデータベースの追加方法

mysqlの公式から配布されているデータベースを利用します

# wget https://downloads.mysql.com/docs/world-db.zip
# unzip world-db.zip 
# cd world-db/
# ls
world.sql

#ダウンロードしたsqlを実行する
# mysql < 'world.sql' 

# データベースの中身を確認する
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| world              |
+--------------------+

#データベースを切り替えて中身を確認する
mysql> use world
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |
| country         |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)

mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int      | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int      | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

cityテーブルにあるデータ全て(*)を参照

mysql> select * from city;
+------+------------------------------------+-------------+------------------------+------------+
| ID   | Name                               | CountryCode | District               | Population |
+------+------------------------------------+-------------+------------------------+------------+
|    1 | Kabul                              | AFG         | Kabol                  |    1780000 |
|    2 | Qandahar                           | AFG         | Qandahar               |     237500 |
|    3 | Herat                              | AFG         | Herat                  |     186800 |

NameとCountryCodeの列だけ表示

mysql> select Name,CountryCode from city;
+------------------------------------+-------------+
| Name                               | CountryCode |
+------------------------------------+-------------+
| Kabul                              | AFG         |
| Qandahar                           | AFG         |
| Herat                              | AFG         |
| Mazar-e-Sharif                     | AFG         |

WHEREを使った絞り込み ountryCodeがJPNのものを参照

単純な列表示だとたくさんデータがヒットするので絞り込むためにWHEREを使う

＃WHEREは列に対して条件を指定して検索できます。
mysql> select * from city where CountryCode = JPN;
ERROR 1054 (42S22): Unknown column 'JPN' in 'where clause'

#JPNは文字列なのでクオートで囲む必要がある
mysql> select * from city where CountryCode = 'JPN';
+------+---------------------+-------------+-----------+------------+
| ID   | Name                | CountryCode | District  | Population |
+------+---------------------+-------------+-----------+------------+
| 1532 | Tokyo               | JPN         | Tokyo-to  |    7980230 |
| 1533 | Jokohama [Yokohama] | JPN         | Kanagawa  |    3339594 |
| 1534 | Osaka               | JPN         | Osaka     |    2595674 |
| 1535 | Nagoya              | JPN         | Aichi     |    2154376 |
| 1536 | Sapporo             | JPN         | Hokkaido  |    1790886 |
| 1537 | Kioto               | JPN         | Kyoto     |    1461974 |
| 1538 | Kobe                | JPN         | Hyogo     |    1425139 |
| 1539 | Fukuoka             | JPN         | Fukuoka   |    1308379 |

#JPN以外のNOT検索も可能
mysql> select * from city where NOT CountryCode = 'JPN';
+------+------------------------------------+-------------+------------------------+------------+
| ID   | Name                               | CountryCode | District               | Population |
+------+------------------------------------+-------------+------------------------+------------+
|    1 | Kabul                              | AFG         | Kabol                  |    1780000 |
|    2 | Qandahar                           | AFG         | Qandahar               |     237500 |
|    3 | Herat                              | AFG         | Herat                  |     186800 |
|    4 | Mazar-e-Sharif                     | AFG         | Balkh                  |     127800 |
|    5 | Amsterdam                          | NLD         | Noord-Holland          |     731200 |
|    6 | Rotterdam                          | NLD         | Zuid-Holland           |     593321 |

#VIRまたはPSEのOR検索も可能
mysql> select * from city where CountryCode = 'VIR' OR CountryCode = 'PSE';
+------+------------------+-------------+------------+------------+
| ID   | Name             | CountryCode | District   | Population |
+------+------------------+-------------+------------+------------+
| 4074 | Gaza             | PSE         | Gaza       |     353632 |
| 4075 | Khan Yunis       | PSE         | Khan Yunis |     123175 |
| 4076 | Hebron           | PSE         | Hebron     |     119401 |
| 4077 | Jabaliya         | PSE         | North Gaza |     113901 |
| 4078 | Nablus           | PSE         | Nablus     |     100231 |
| 4079 | Rafah            | PSE         | Rafah      |      92020 |
| 4067 | Charlotte Amalie | VIR         | St Thomas  |      13000 |
+------+------------------+-------------+------------+------------+
7 rows in set (0.00 sec)

# CountryCodeがJPNかつDistrictがHokkaidoを検索
mysql> select * from city where CountryCode = 'JPN' AND District = 'Hokkaido';
+------+-----------+-------------+----------+------------+
| ID   | Name      | CountryCode | District | Population |
+------+-----------+-------------+----------+------------+
| 1536 | Sapporo   | JPN         | Hokkaido |    1790886 |
| 1577 | Asahikawa | JPN         | Hokkaido |     364813 |
| 1599 | Hakodate  | JPN         | Hokkaido |     294788 |
| 1639 | Kushiro   | JPN         | Hokkaido |     197608 |
| 1650 | Obihiro   | JPN         | Hokkaido |     173685 |
| 1651 | Tomakomai | JPN         | Hokkaido |     171958 |
| 1667 | Otaru     | JPN         | Hokkaido |     155784 |
| 1720 | Ebetsu    | JPN         | Hokkaido |     118805 |
| 1730 | Kitami    | JPN         | Hokkaido |     111295 |
| 1735 | Muroran   | JPN         | Hokkaido |     108275 |
+------+-----------+-------------+----------+------------+
10 rows in set (0.00 sec)

#ORで複数条件を指定できる
mysql> select * from city where CountryCode = 'JPN' AND (District = 'Tochigi' OR District = 'Miyazaki');
+------+------------+-------------+----------+------------+
| ID   | Name       | CountryCode | District | Population |
+------+------------+-------------+----------+------------+
| 1565 | Utsunomiya | JPN         | Tochigi  |     440353 |
| 1595 | Miyazaki   | JPN         | Miyazaki |     303784 |
| 1659 | Ashikaga   | JPN         | Tochigi  |     165243 |
| 1670 | Oyama      | JPN         | Tochigi  |     152820 |
| 1692 | Miyakonojo | JPN         | Miyazaki |     133183 |
| 1704 | Nobeoka    | JPN         | Miyazaki |     125547 |
| 1773 | Kanuma     | JPN         | Tochigi  |      93053 |
+------+------------+-------------+----------+------------+
7 rows in set (0.00 sec)

#不等号も利用できる、人口100万人以上の都市を抽出する
mysql> select Name,District from city where CountryCode = 'JPN' AND Population >= 1000000;
+---------------------+-----------+
| Name                | District  |
+---------------------+-----------+
| Tokyo               | Tokyo-to  |
| Jokohama [Yokohama] | Kanagawa  |
| Osaka               | Osaka     |
| Nagoya              | Aichi     |
| Sapporo             | Hokkaido  |
| Kioto               | Kyoto     |
| Kobe                | Hyogo     |
| Fukuoka             | Fukuoka   |
| Kawasaki            | Kanagawa  |
| Hiroshima           | Hiroshima |
| Kitakyushu          | Fukuoka   |
+---------------------+-----------+
11 rows in set (0.01 sec)

## 列名を日本語に付け替えて表示
mysql> select Name AS '都市名',District AS '都道府県',Population AS '人口' from city where CountryCode = 'JPN' AND Population >= 1000000;
+---------------------+--------------+---------+
| 都市名              | 都道府県     | 人口    |
+---------------------+--------------+---------+
| Tokyo               | Tokyo-to     | 7980230 |
| Jokohama [Yokohama] | Kanagawa     | 3339594 |
| Osaka               | Osaka        | 2595674 |
| Nagoya              | Aichi        | 2154376 |
| Sapporo             | Hokkaido     | 1790886 |
| Kioto               | Kyoto        | 1461974 |
| Kobe                | Hyogo        | 1425139 |
| Fukuoka             | Fukuoka      | 1308379 |
| Kawasaki            | Kanagawa     | 1217359 |
| Hiroshima           | Hiroshima    | 1119117 |
| Kitakyushu          | Fukuoka      | 1016264 |
+---------------------+--------------+---------+
11 rows in set (0.00 sec)

#CountryCodeのユニークな値を確認 (uniq)
mysql> SELECT DISTINCT CountryCode from city;
+-------------+
| CountryCode |
+-------------+
| ABW         |
| AFG         |
| AGO         |
| AIA         |
| ALB         |
| AND         |

# CountryCodeごとの行数を数えたい場合(Group byを使用)
<!-- wp:loos-hcb/code-block -->
<div class="hcb_wrap"><pre class="prism line-numbers lang-plain"><code>＃WHEREは列に対して条件を指定して検索できます。
mysql> select * from city where CountryCode = JPN;
ERROR 1054 (42S22): Unknown column 'JPN' in 'where clause'

#JPNは文字列なのでクオートで囲む必要がある
mysql> select * from city where CountryCode = 'JPN';
+------+---------------------+-------------+-----------+------------+
| ID   | Name                | CountryCode | District  | Population |
+------+---------------------+-------------+-----------+------------+
| 1532 | Tokyo               | JPN         | Tokyo-to  |    7980230 |
| 1533 | Jokohama [Yokohama] | JPN         | Kanagawa  |    3339594 |
| 1534 | Osaka               | JPN         | Osaka     |    2595674 |
| 1535 | Nagoya              | JPN         | Aichi     |    2154376 |
| 1536 | Sapporo             | JPN         | Hokkaido  |    1790886 |
| 1537 | Kioto               | JPN         | Kyoto     |    1461974 |
| 1538 | Kobe                | JPN         | Hyogo     |    1425139 |
| 1539 | Fukuoka             | JPN         | Fukuoka   |    1308379 |

#JPN以外のNOT検索も可能
mysql> select * from city where NOT CountryCode = 'JPN';
+------+------------------------------------+-------------+------------------------+------------+
| ID   | Name                               | CountryCode | District               | Population |
+------+------------------------------------+-------------+------------------------+------------+
|    1 | Kabul                              | AFG         | Kabol                  |    1780000 |
|    2 | Qandahar                           | AFG         | Qandahar               |     237500 |
|    3 | Herat                              | AFG         | Herat                  |     186800 |
|    4 | Mazar-e-Sharif                     | AFG         | Balkh                  |     127800 |
|    5 | Amsterdam                          | NLD         | Noord-Holland          |     731200 |
|    6 | Rotterdam                          | NLD         | Zuid-Holland           |     593321 |

#VIRまたはPSEのOR検索も可能
mysql> select * from city where CountryCode = 'VIR' OR CountryCode = 'PSE';
+------+------------------+-------------+------------+------------+
| ID   | Name             | CountryCode | District   | Population |
+------+------------------+-------------+------------+------------+
| 4074 | Gaza             | PSE         | Gaza       |     353632 |
| 4075 | Khan Yunis       | PSE         | Khan Yunis |     123175 |
| 4076 | Hebron           | PSE         | Hebron     |     119401 |
| 4077 | Jabaliya         | PSE         | North Gaza |     113901 |
| 4078 | Nablus           | PSE         | Nablus     |     100231 |
| 4079 | Rafah            | PSE         | Rafah      |      92020 |
| 4067 | Charlotte Amalie | VIR         | St Thomas  |      13000 |
+------+------------------+-------------+------------+------------+
7 rows in set (0.00 sec)

# CountryCodeがJPNかつDistrictがHokkaidoを検索
mysql> select * from city where CountryCode = 'JPN' AND District = 'Hokkaido';
+------+-----------+-------------+----------+------------+
| ID   | Name      | CountryCode | District | Population |
+------+-----------+-------------+----------+------------+
| 1536 | Sapporo   | JPN         | Hokkaido |    1790886 |
| 1577 | Asahikawa | JPN         | Hokkaido |     364813 |
| 1599 | Hakodate  | JPN         | Hokkaido |     294788 |
| 1639 | Kushiro   | JPN         | Hokkaido |     197608 |
| 1650 | Obihiro   | JPN         | Hokkaido |     173685 |
| 1651 | Tomakomai | JPN         | Hokkaido |     171958 |
| 1667 | Otaru     | JPN         | Hokkaido |     155784 |
| 1720 | Ebetsu    | JPN         | Hokkaido |     118805 |
| 1730 | Kitami    | JPN         | Hokkaido |     111295 |
| 1735 | Muroran   | JPN         | Hokkaido |     108275 |
+------+-----------+-------------+----------+------------+
10 rows in set (0.00 sec)

#ORで複数条件を指定できる
mysql> select * from city where CountryCode = 'JPN' AND (District = 'Tochigi' OR District = 'Miyazaki');
+------+------------+-------------+----------+------------+
| ID   | Name       | CountryCode | District | Population |
+------+------------+-------------+----------+------------+
| 1565 | Utsunomiya | JPN         | Tochigi  |     440353 |
| 1595 | Miyazaki   | JPN         | Miyazaki |     303784 |
| 1659 | Ashikaga   | JPN         | Tochigi  |     165243 |
| 1670 | Oyama      | JPN         | Tochigi  |     152820 |
| 1692 | Miyakonojo | JPN         | Miyazaki |     133183 |
| 1704 | Nobeoka    | JPN         | Miyazaki |     125547 |
| 1773 | Kanuma     | JPN         | Tochigi  |      93053 |
+------+------------+-------------+----------+------------+
7 rows in set (0.00 sec)

#不等号も利用できる、人口100万人以上の都市を抽出する
mysql> select Name,District from city where CountryCode = 'JPN' AND Population >= 1000000;
+---------------------+-----------+
| Name                | District  |
+---------------------+-----------+
| Tokyo               | Tokyo-to  |
| Jokohama [Yokohama] | Kanagawa  |
| Osaka               | Osaka     |
| Nagoya              | Aichi     |
| Sapporo             | Hokkaido  |
| Kioto               | Kyoto     |
| Kobe                | Hyogo     |
| Fukuoka             | Fukuoka   |
| Kawasaki            | Kanagawa  |
| Hiroshima           | Hiroshima |
| Kitakyushu          | Fukuoka   |
+---------------------+-----------+
11 rows in set (0.01 sec)

## 列名を日本語に付け替えて表示
mysql> select Name AS '都市名',District AS '都道府県',Population AS '人口' from city where CountryCode = 'JPN' AND Population >= 1000000;
+---------------------+--------------+---------+
| 都市名              | 都道府県     | 人口    |
+---------------------+--------------+---------+
| Tokyo               | Tokyo-to     | 7980230 |
| Jokohama [Yokohama] | Kanagawa     | 3339594 |
| Osaka               | Osaka        | 2595674 |
| Nagoya              | Aichi        | 2154376 |
| Sapporo             | Hokkaido     | 1790886 |
| Kioto               | Kyoto        | 1461974 |
| Kobe                | Hyogo        | 1425139 |
| Fukuoka             | Fukuoka      | 1308379 |
| Kawasaki            | Kanagawa     | 1217359 |
| Hiroshima           | Hiroshima    | 1119117 |
| Kitakyushu          | Fukuoka      | 1016264 |
+---------------------+--------------+---------+
11 rows in set (0.00 sec)

#CountryCodeのユニークな値を確認 (uniq)
mysql> SELECT DISTINCT CountryCode from city;
+-------------+
| CountryCode |
+-------------+
| ABW         |
| AFG         |
| AGO         |
| AIA         |
| ALB         |
| AND         |

COUNTを使って行数を数える

mysql> SELECT count(*) from city;
+----------+
| count(*) |
+----------+
|     4079 |
+----------+
1 row in set (0.02 sec)
テーブルには4079行があることがわかる

# Countryコードごとの行数を数える　(group byを利用)
mysql> SELECT CountryCode, COUNT(*) from city group by CountryCode;
+-------------+----------+
| CountryCode | COUNT(*) |
+-------------+----------+
| ABW         |        1 |
| AFG         |        4 |
| AGO         |        5 |
| AIA         |        2 |
| ALB         |        1 |
| AND         |        1 |

# 100行以上の行数を持つCountryCodeを抽出
mysql> SELECT CountryCode, COUNT(*) from city group by CountryCode HAVING COUNT(*) >= 100;
+-------------+----------+
| CountryCode | COUNT(*) |
+-------------+----------+
| BRA         |      250 |
| CHN         |      363 |
| IND         |      341 |
| JPN         |      248 |
| MEX         |      173 |
| PHL         |      136 |
| RUS         |      189 |
| USA         |      274 |
+-------------+----------+
8 rows in set (0.00 sec)

# Count行を並び替えて表示する
#昇順
mysql> SELECT CountryCode, COUNT(*) from city group by CountryCode HAVING COUNT(*) >= 100 ORDER BY COUNT(*);
+-------------+----------+
| CountryCode | COUNT(*) |
+-------------+----------+
| PHL         |      136 |
| MEX         |      173 |
| RUS         |      189 |
| JPN         |      248 |
| BRA         |      250 |
| USA         |      274 |
| IND         |      341 |
| CHN         |      363 |
+-------------+----------+
8 rows in set (0.00 sec)

#降順
mysql> SELECT CountryCode, COUNT(*) from city group by CountryCode HAVING COUNT(*) >= 100 ORDER BY COUNT(*) DESC;
+-------------+----------+
| CountryCode | COUNT(*) |
+-------------+----------+
| CHN         |      363 |
| IND         |      341 |
| USA         |      274 |
| BRA         |      250 |
| JPN         |      248 |
| RUS         |      189 |
| MEX         |      173 |
| PHL         |      136 |
+-------------+----------+
8 rows in set (0.00 sec)

SUMで人口を集計

mysql> select sum(Population) from city;
+-----------------+
| sum(Population) |
+-----------------+
|      1429559884 |
+-----------------+
1 row in set (0.00 sec)

#2本だけ数えてみる
mysql> select sum(Population) from city WHERE CountryCode = 'JPN';
+-----------------+
| sum(Population) |
+-----------------+
|        77965107 |
+-----------------+
1 row in set (0.00 sec)

#ついでに最小(MIN)、最大(MAX)、平均(AVG)を求める
mysql> select MIN(Population) from city WHERE CountryCode = 'JPN';
+-----------------+
| MIN(Population) |
+-----------------+
|           91170 |
+-----------------+
1 row in set (0.00 sec)

mysql> select MAX(Population) from city WHERE CountryCode = 'JPN';
+-----------------+
| MAX(Population) |
+-----------------+
|         7980230 |
+-----------------+
1 row in set (0.00 sec)

mysql> select AVG(Population) from city WHERE CountryCode = 'JPN';
+-----------------+
| AVG(Population) |
+-----------------+
|     314375.4315 |
+-----------------+
1 row in set (0.00 sec)

データの追加 INSERT

追加したテーブルにデータを追加数にはINSERTを使います。追加するデータは列で指定したデータ型で入れます。

#データを追加するテーブルの情報を確認する
mysql> desc kojinzyohou;
+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| mynumber   | int          | NO   | PRI | NULL              | auto_increment    |
| name       | varchar(100) | YES  |     | NULL              |                   |
| zyusyo     | varchar(100) | YES  |     | NULL              |                   |
| seibetsu   | int          | YES  |     | NULL              |                   |
| created_at | timestamp    | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| nenrei     | int          | YES  |     | NULL              |                   |
+------------+--------------+------+-----+-------------------+-------------------+
6 rows in set (0.00 sec)

#何も入っていない
mysql> select * from kojinzyohou;
Empty set (0.00 sec)

# INSERT INTO "テーブル名" ("列名") VALUES ("値"); でデータを追加する
mysql> INSERT INTO kojinzyohou  (name, zyusyo, seibetsu) VALUES ('pogo', 'Aomori', 1);
Query OK, 1 row affected (0.00 sec)

#中身を確認
mysql> select * from kojinzyohou;
+----------+------+--------+----------+---------------------+--------+
| mynumber | name | zyusyo | seibetsu | created_at          | nenrei |
+----------+------+--------+----------+---------------------+--------+
|        1 | pogo | Aomori |        1 | 2022-08-21 09:33:33 |   NULL |
+----------+------+--------+----------+---------------------+--------+
1 row in set (0.00 sec)

mysql>

UPDATEでデータ更新

登録済みのデータを更新するにはUPDATE文を使います

mysql> select * from kojinzyohou;
+----------+------+--------+----------+---------------------+--------+
| mynumber | name | zyusyo | seibetsu | created_at          | nenrei |
+----------+------+--------+----------+---------------------+--------+
|        1 | pogo | Aomori |        1 | 2022-08-21 09:33:33 |   NULL |
+----------+------+--------+----------+---------------------+--------+
1 row in set (0.00 sec)

# nameがpogoになっている行の住所を'Hokkaido'に変更します
mysql> UPDATE kojinzyohou set zyusyo = 'Hokkaido' WHERE name = 'pogo';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from kojinzyohou;
+----------+------+----------+----------+---------------------+--------+
| mynumber | name | zyusyo   | seibetsu | created_at          | nenrei |
+----------+------+----------+----------+---------------------+--------+
|        1 | pogo | Hokkaido |        1 | 2022-08-21 09:33:33 |   NULL |
+----------+------+----------+----------+---------------------+--------+
1 row in set (0.00 sec)

VIEWの作成

VIEWというSELECTの結果を保存して一時的なテーブルとして利用する方法があります。

mysql> SELECT CountryCode, COUNT(*) from world.city group by CountryCode HAVING COUNT(*) >= 100 ORDER BY COUNT(*);
+-------------+----------+
| CountryCode | COUNT(*) |
+-------------+----------+
| PHL         |      136 |
| MEX         |      173 |
| RUS         |      189 |
| JPN         |      248 |
| BRA         |      250 |
| USA         |      274 |
| IND         |      341 |
| CHN         |      363 |
+-------------+----------+
8 rows in set (0.00 sec)

# CREATE VIEWでcountry_sumという名前のビューを作成する。

mysql> CREATE VIEW country_sum (Country, sum) AS SELECT CountryCode, COUNT(*) from world.city group by CountryCode HAVING COUNT(*) >= 100
ORDER BY COUNT(*);
Query OK, 0 rows affected (0.00 sec)

#作成したビューはテーブルのように指定して検索できる
mysql> select * from country_sum;
+---------+-----+
| Country | sum |
+---------+-----+
| PHL     | 136 |
| MEX     | 173 |
| RUS     | 189 |
| JPN     | 248 |
| BRA     | 250 |
| USA     | 274 |
| IND     | 341 |
| CHN     | 363 |
+---------+-----+
8 rows in set (0.01 sec)

関数を使う

エクセルに関数があるようにSQLにも関数がたくさんあります。

LENGTH 文字列の長さを返す

mysql> select Name, LENGTH(Name) FROM world.city HAVING LENGTH(Name) >= 30;
+------------------------------------+--------------+
| Name                               | LENGTH(Name) |
+------------------------------------+--------------+
| San Fernando del Valle de Cata     |           30 |
| Santo Domingo de los Colorados     |           30 |
| Castellón de la Plana [Castell     |           31 |
| Thiruvananthapuram (Trivandrum     |           30 |
| Luxembourg [Luxemburg/Lëtzebuerg]  |           34 |
+------------------------------------+--------------+
5 rows in set (0.00 sec)

LOWER 小文字に変換

mysql> select Name, length, LOWER(Name) from (select Name, LENGTH(Name) AS length FROM world.city HAVING LENGTH(Name) >= 30) AS LOWER;
+------------------------------------+--------+------------------------------------+
| Name                               | length | LOWER(Name)                        |
+------------------------------------+--------+------------------------------------+
| San Fernando del Valle de Cata     |     30 | san fernando del valle de cata     |
| Santo Domingo de los Colorados     |     30 | santo domingo de los colorados     |
| Castellón de la Plana [Castell     |     31 | castellón de la plana [castell     |
| Thiruvananthapuram (Trivandrum     |     30 | thiruvananthapuram (trivandrum     |
| Luxembourg [Luxemburg/Lëtzebuerg]  |     34 | luxembourg [luxemburg/lëtzebuerg]  |
+------------------------------------+--------+------------------------------------+
5 rows in set (0.00 sec)