#内部結合と外部結合
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