Tuesday, July 23, 2013

MySQL Sample Databases

I saw a post on the forums.mysql.com site about the sample databases and I thought it might be worth a post to give a quick overview to them for others.

The Sample databases can be found here:  http://dev.mysql.com/doc/index-other.html
You can load these databases via the MySQL command line:

$ tar -vxf sakila-db.tar.gz
$cd sakila-db
$ mysql -u root -p < sakila-schema.sql
Enter password:
$ mysql -u root -p < sakila-data.sql
Enter password:

$ gzip -d world_innodb.sql.gz
$ mysql -u root -p -e "DROP SCHEMA IF EXISTS world";
Enter password:
$ mysql -u root -p -e "CREATE SCHEMA world";
Enter password:
$ mysql -u root -p world < world_innodb.sql
Enter password:

You get the idea. Sakila example database has the Drop SCHEMA and CREATE SCHEMA commands in the file so no need to do that step for that SCHEMA.

You can also use MySQL Workbench to load this data.
  • Create a connection handle to you database. 
  • Use this newly created connection handle to set up a Server Administration instance. 
  • Double click on your new instance.
  • Under Data Export / Restore you should see a Data import.
  • Import from a Self-Contained File
    • File path will be the location of your sakila-schema.sql then repeat for sakila-data.sql
    • You can select a schema or create a new one in the case of world.
    • Select start import and then you will be on the Import Progress view.
You now have access to the sample databases in your database. 

$ mysql -u root -p
> use sakila
> show tables;
> select * from actor limit 3;
| actor_id | first_name | last_name | last_update         |
|        1 | PENELOPE   | GUINESS   | 2006-02-15 04:34:33 |
|        2 | NICK       | WAHLBERG  | 2006-02-15 04:34:33 |
|        3 | ED         | CHASE     | 2006-02-15 04:34:33 |
Via workbench :
  • Close the admin tab
  • Select your connection handle under the SQL Development
  • You can either just type in select * from actor limit 3; and hit the lighting bolt.
  • Or you can type parts of the command and double click on table name or column names to have it populate the names for you. Then select the lighting bolt. 
Now you have data to start playing and learning with.

If you want to add tables to this you can use the MySQL command line or SQL Development and right click on "Tables" under the Schema of your choice and "create new table"