MySQL中单个表中的多个表

I've downloaded the Worlds-Country-State-and-City-Mysql-Database project from GitHub, it gives a database of countries, states, and cities.

Normally such databases are found in three tables for country, state, and city, but here it is only a single table, and it has all three pieces of data. It works fine but I can't understand how it works.

Here is snapshot of that table.

Somebody please explain this

What you have here is a parent-child relationship. The parent_ID column designates if a location belongs to another(is the child of another) Location_id.

For example to get the direct children (states) of Aruba, you would do a self join as such:

select * from mytable A
left join mytable B -- join same table
on b.parent_id=a.location_id -- with parent of the secondary being equal to the ID of the primary.
where a.location_id='Aruba'

If you want to get the children of the states(cities) , you would need one more self-join