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


主に簡単にテーブル構造とデータが同じテーブルを作成する方法
は2パターンありますが、それぞのにも違いはあるので改めて確認。

その1
※テーブル構造+データ
create table ******* select ********


mysql> desc Y2008;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(50) | NO | PRI | | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> select * from Y2008;
+---------+
| comment |
+---------+
| TEST1 |
| TEST2 |
| TEST3 |
+---------+
3 rows in set (0.00 sec)

mysql> create table Y2009 select * from Y2008;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from Y2009;
+---------+
| comment |
+---------+
| TEST1 |
| TEST2 |
| TEST3 |
+---------+
3 rows in set (0.00 sec)

mysql> desc Y2009;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(50) | NO | | | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql>

create_select

その2
※テーブル構造+データ+インデックスなど
CREATE TABLE ****** LIKE **********


mysql> create table Y2010 like Y2008;
Query OK, 0 rows affected (0.01 sec)

mysql> desc Y2008;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(50) | NO | PRI | | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> desc Y2010;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| comment | varchar(50) | NO | PRI | | |
+---------+-------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> insert into Y2010 select * from Y2008;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from Y2010;
+---------+
| comment |
+---------+
| TEST1 |
| TEST2 |
| TEST3 |
+---------+
3 rows in set (0.00 sec)

mysql>


insert_table1