Importing to MySQL
With MySQL workbench
us-cities.zipfile using the unique download link we emailed to you after purchase. This link looks like:
Once the download has finished double-click the
us-cities.zipfile and extract the
us-cities/sql/us-cities.sqlscript to your computer. Make a note of the location you extract the
us-cities.sqlfile to — you'll need it in step 5.
Open MySQL workbench.
Select your database from the
MySQL Connectionslist on the welcome screen and enter the password for your database user (if necessary). This will then open a new
Server > Data Importfrom the toolbar. Then under
Import from Self-Contained Fileand select the
us-cities.sqlfile you extracted in step 2. Under
Default Target Schemachoose the database schema you want to import the data to.
Start Importbutton at the bottom of the screen. The script will create a new table called
us_citiesand insert the data. This typically takes a few seconds to complete.
Once the import has finished you can query the
us_citiestable. Double-click the name of the database schema that you imported the data to under the
Schemassection of the sidebar, then select
File > New Query Taband type your query. For example:
SELECT * FROM us_cities LIMIT 50;
Query > Execute Current Statementfrom the toolbar to run your query and see the results.
With the Terminal (Mac & Linux)
CTRL+ALT+Tto open a new terminal window.
us-cities.zipfile using the unique download link we emailed to you after purchase. You can do this with the
$ wget -O us-cities.zip https://www.uscitieslist.org/download/xxxx-xxxx/
Once the download has finished decompress the
us-cities.zipfile. You can do this with the
$ unzip us-cities.zip
msqlcommand to run the
us-cities/sql/us-cities.sqlimport script, taking care to set the default character set to
UTF-8. For example to import the data into the
example_dbdatabase as the
rootuser you should use the command below.
$ mysql -u root -p --default-character-set=utf8 example_db < us-cities/sql/us-cities.sql
Once the import is complete you can query the data. Connect to your database using the
$ mysql -u root -p example_db
Then at the
mysqlprompt type your query and hit enter to run it. For example:
mysql> SELECT * FROM us_cities LIMIT 10;