CItyテーブルから首都のみを選んでCapitalテーブルを作成。

city

1) テーブル作成
CREATE TABLE Capitals LIKE City;

create_table_like

2) 首都データの投入

INSERT INTO Capitals SELECT * FROM City
where ID IN(select Capital from Country where Capital is not null);

insert_into

select * from Capitals where name like 'to%' order by name;

tokyo

=================
その他の方法(全てのデータをいれてから削除
=================


mysql> CREATE TABLE Capitals LIKE City;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO Capitals SELECT * FROM City;
Query OK, 4079 rows affected (0.17 sec)
Records: 4079 Duplicates: 0 Warnings: 0

mysql> delete from Capitals where ID NOT IN
-> (select Capital from Country where Capital is not null);
Query OK, 3847 rows affected (4.86 sec)

mysql> select count(*) from Capitals;
+----------+
| count(*) |
+----------+
| 232 |
+----------+
1 row in set (0.00 sec)

mysql> select * from Capitals where name like 'to%' order by name;
+------+---------+-------------+----------------+------------+
| ID | Name | CountryCode | District | Population |
+------+---------+-------------+----------------+------------+
| 1532 | Tokyo | JPN | Tokyo-to | 7980230 |
| 3503 | Toskent | UZB | Toskent Shahri | 2117500 |
+------+---------+-------------+----------------+------------+
2 rows in set (0.01 sec)

mysql>

delete

Comments are closed.

Post Navigation