Importing to MySQL

With MySQL workbench

  1. Download the us-cities.zip file using the unique download link we emailed to you after purchase. This link looks like:

    https://www.uscitieslist.org/download/xxxx-xxxx/

  2. Once the download has finished double-click the us-cities.zip file and extract the us-cities/sql/us-cities.sql script to your computer. Make a note of the location you extract the us-cities.sql file to — you'll need it in step 5.

  3. Open MySQL workbench.

  4. Select your database from the MySQL Connections list on the welcome screen and enter the password for your database user (if necessary). This will then open a new SQL Editor window.

  5. Select Server > Data Import from the toolbar. Then under Import Options choose Import from Self-Contained File and select the us-cities.sql file you extracted in step 2. Under Default Target Schema choose the database schema you want to import the data to.

  6. Click the Start Import button at the bottom of the screen. The script will create a new table called us_cities and insert the data. This typically takes a few seconds to complete.

  7. Once the import has finished you can query the us_cities table. Double-click the name of the database schema that you imported the data to under the Schemas section of the sidebar, then select File > New Query Tab and type your query. For example:

    SELECT * FROM us_cities LIMIT 50;

  8. Select Query > Execute Current Statement from the toolbar to run your query and see the results.

With the Terminal (Mac & Linux)

  1. Press CTRL+ALT+T to open a new terminal window.

  2. Download the us-cities.zip file using the unique download link we emailed to you after purchase. You can do this with the wget command:

    $ wget -O us-cities.zip https://www.uscitieslist.org/download/xxxx-xxxx/

  3. Once the download has finished decompress the us-cities.zip file. You can do this with the unzip command:

    $ unzip us-cities.zip

  4. Use the msql command to run the us-cities/sql/us-cities.sql import script, taking care to set the default character set to UTF-8. For example to import the data into the example_db database as the root user you should use the command below.

    $ mysql -u root -p --default-character-set=utf8 example_db < us-cities/sql/us-cities.sql

  5. Once the import is complete you can query the data. Connect to your database using the mysql command again:

    $ mysql -u root -p example_db

  6. Then at the mysql prompt type your query and hit enter to run it. For example:

    mysql> SELECT * FROM us_cities LIMIT 10;