Introduction

Previously, I aquainted myself with managing my recently installed MySQL environment. Now to some of the fun stuff… exploring the power and utility of this wonderful tool !

The general tutorial lays down the ground work for me to start looking into data, but there are many indepth guides including ones at w3schools and tutorialspoint. Both are quite detailed, and I will be looking to them for inspiration as I learn how to use MySQL. The w3schools tutorial is particularly useful due to its interactivity, because users can try out SQL statements via their online interface. Additionally, the list of functions here is another good potential resource.

There are a number of test databases that we could use, but we will be exploring the Sakila database through its Sakila tutorial in combination with other resources such as the abovementioned. The source files contain instructions for database creation, and include the Sakila database design schema for use with MySQL workbench. Database design, including ERD modelling, is an important topic that I hope to explore further. Solid databases, are well designed ones !

Sample database setup

Install database

We will be setting up our test database as described here:

shell> mysql -u root -p

-- import database design schema
mysql> SOURCE C:/temp/sakila-db/sakila-schema.sql;
-- import data to populate database
mysql> SOURCE C:/temp/sakila-db/sakila-data.sql;
-- Note: replace "C:/temp/sakila-db/" with whatever your path is :)

-- having already connected to Sakila database..
mysql> SHOW TABLES;
mysql> SELECT COUNT(*) FROM film;

Setup tutorial user

The MySQL root user is powerful, and as such should be invoked with care! As has been said..

“With great power comes great responsibility.”

Thus we have our user, sophieG, through whom we will be commence our “datadventure” (sic). Now to create and empower or intrepid user !

mysql> CREATE USER 'sophieG'@'localhost' IDENTIFIED BY '2whizpoppingFrobscottle*!';
mysql> GRANT ALL ON sakila.* TO 'sophieG'@'localhost';

Once complete, we need to login as our new user and check the correct provision of datbase privileges (as above).

Conclusion

Once successful, we can move to the next step .