やったこと備忘_20190309

#内部結合と外部結合

mysql> show columns from customer;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| ID        | int(11)     | NO   | PRI | NULL    |       |
| FirstName | varchar(32) | NO   |     | NULL    |       |
| LastName  | varchar(32) | NO   |     | NULL    |       |
| Sex       | char(1)     | YES  |     | NULL    |       |
| Age       | int(11)     | YES  |     | NULL    |       |
+-----------+-------------+------+-----+---------+-------+


mysql> show columns from address;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| ID       | int(11)      | NO   | PRI | NULL    |       |
| Zip      | char(9)      | YES  |     | NULL    |       |
| District | varchar(10)  | YES  |     | NULL    |       |
| Addr     | varchar(200) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

mysql> select * from customer;
+----+-----------+----------+------+------+
| ID | FirstName | LastName | Sex  | Age  |
+----+-----------+----------+------+------+
|  1 | 花子      | 鈴木     | f    |   21 |
|  2 | 太郎      | 田中     | m    |   30 |
|  3 | 一郎      | 鈴木     | m    |   34 |
|  4 | 奈々      | 香川     | f    |   21 |
|  5 | 孝之      | 佐藤     | m    |   38 |
|  6 | 史郎      | 伊藤     | m    |   45 |
+----+-----------+----------+------+------+
6 rows in set (0.00 sec)

mysql> select * from address;
+----+----------+----------+--------------------+
| ID | Zip      | District | Addr               |
+----+----------+----------+--------------------+
|  1 | 155-0001 | 東京都   | 世田谷区下北1-1-1  |
|  2 | 160-0023 | 東京都   | 新宿区西新宿9-8-7  |
|  3 | 079-1274 | 北海道   | 石狩市樽川3-1-1    |
|  5 | 155-0031 | 神奈川県 | 横浜市港南区6-8-87 |
+----+----------+----------+--------------------+

mysql> select c.ID, c.FirstName, c.Age, a.District
    -> from address a
    -> inner join customer c on c.ID = a.ID;
+----+-----------+------+----------+
| ID | FirstName | Age  | District |
+----+-----------+------+----------+
|  1 | 花子      |   21 | 東京都   |
|  2 | 太郎      |   30 | 東京都   |
|  3 | 一郎      |   34 | 北海道   |
|  5 | 孝之      |   38 | 神奈川県 |
+----+-----------+------+----------+

mysql> select c.ID, c.FirstName, c.Age, a.District
    -> from address a
    -> left outer join customer c on c.ID = a.ID;
+------+-----------+------+----------+
| ID   | FirstName | Age  | District |
+------+-----------+------+----------+
|    1 | 花子      |   21 | 東京都   |
|    2 | 太郎      |   30 | 東京都   |
|    3 | 一郎      |   34 | 北海道   |
|    5 | 孝之      |   38 | 神奈川県 |
+------+-----------+------+----------+

mysql> select c.ID, c.FirstName, c.Age, a.District
    -> from address a
    -> right outer join customer c on c.ID = a.ID;
+----+-----------+------+----------+
| ID | FirstName | Age  | District |
+----+-----------+------+----------+
|  1 | 花子      |   21 | 東京都   |
|  2 | 太郎      |   30 | 東京都   |
|  3 | 一郎      |   34 | 北海道   |
|  4 | 奈々      |   21 | NULL     |
|  5 | 孝之      |   38 | 神奈川県 |
|  6 | 史郎      |   45 | NULL     |
+----+-----------+------+----------+

感覚として
左側を軸とする = fromで指定したテーブル
つまり内部結合でもとになってるテーブル

右側を軸とする = 結合先のテーブル

ということが分かった

#json型の使い方
mysql> show columns from countryinfo;
+-------+-------------+------+-----+---------+------------------+
| Field | Type        | Null | Key | Default | Extra            |
+-------+-------------+------+-----+---------+------------------+
| doc   | json        | YES  |     | NULL    |                  |
| _id   | varchar(32) | NO   | PRI | NULL    | STORED GENERATED |
+-------+-------------+------+-----+---------+------------------+

上記テーブルを2レコードだけ

mysql> select * from countryinfo limit 2;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+
| doc                                                                                                                                                                                                                                                                                                                                            | _id |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+
| {"GNP": 828, "_id": "ABW", "Name": "Aruba", "IndepYear": null, "geography": {"Region": "Caribbean", "Continent": "North America", "SurfaceArea": 193}, "government": {"HeadOfState": "Beatrix", "GovernmentForm": "Nonmetropolitan Territory of The Netherlands"}, "demographics": {"Population": 103000, "LifeExpectancy": 78.4000015258789}} | ABW |
| {"GNP": 5976, "_id": "AFG", "Name": "Afghanistan", "IndepYear": 1919, "geography": {"Region": "Southern and Central Asia", "Continent": "Asia", "SurfaceArea": 652090}, "government": {"HeadOfState": "Mohammad Omar", "GovernmentForm": "Islamic Emirate"}, "demographics": {"Population": 22720000, "LifeExpectancy": 45.900001525878906}}   | AFG |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----+

んで、これを見やすくするjson関数

mysql> select json_pretty(doc) as doc from countryinfo limit 2;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| doc                                                                                                                                                                                                                                                                                                                                                                                                    |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {
  "GNP": 828,
  "_id": "ABW",
  "Name": "Aruba",
  "IndepYear": null,
  "geography": {
    "Region": "Caribbean",
    "Continent": "North America",
    "SurfaceArea": 193
  },
  "government": {
    "HeadOfState": "Beatrix",
    "GovernmentForm": "Nonmetropolitan Territory of The Netherlands"
  },
  "demographics": {
    "Population": 103000,
    "LifeExpectancy": 78.4000015258789
  }
} |
| {
  "GNP": 5976,
  "_id": "AFG",
  "Name": "Afghanistan",
  "IndepYear": 1919,
  "geography": {
    "Region": "Southern and Central Asia",
    "Continent": "Asia",
    "SurfaceArea": 652090
  },
  "government": {
    "HeadOfState": "Mohammad Omar",
    "GovernmentForm": "Islamic Emirate"
  },
  "demographics": {
    "Population": 22720000,
    "LifeExpectancy": 45.900001525878906
  }
}   |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

本日はここまで

来週はjson型の要素を取り出す方法を調査、やってみる

参考URL:
[title]http://phpjavascriptroom.com/?t=mysql&p=join

[title]https://hit.hateblo.jp/entry/MYSQL/8.0/JSON