【一网打尽】Raspberry Pi Course Week 6 – Day 2 (Understanding Databas...

木林森X

This tutorial will teach you the basics of installing MySQL for the Raspberry Pi and will teach you a little about databases.

What is a database?

A database is an organized collection of data.  A phone book for example is a database or the Google search engine is also a database, a very large database.  Databases are used to hold a large number of records such as patient’s health records, a list of blu-rays movies, a list of car registrations and owners and the government hold tax records every person.

Databases make it easy to find information and more than one person can retrieve these records.

There are a number of databases available for the Raspberry Pi but MySQL is a very common database in the open source community.  A database is a MySQL has the following features.

MySQL is scalable.

MySQL is designed for heavy traffic.

MySQL supports user management and permissions.

MySQL supports a client server architecture which means that you can log in from a remote computer to access the database.

For the purpose of this course you will perform all the database commands on the Raspberry Pi or the MySQL server.

A database contains tables that hold the data and many tables can exist within the database.  The example below shows a simple name and address table.

Install MySQL by entering the following commands

$ sudo apt-get install mysql-server

When prompted, enter a password for the MySQL root account.  This is different to your root account for the Raspberry Pi.  This password is specifically for the MySQL root user.  The MySQL root user has special privileges so that this user can create, delete and manipulate databases.

When the installation is complete you will need to log into the MySQL shell.  This will be used to communicate and issue commands to the MySQL server.

At the $ prompt enter the following.

$ mysql -u root -p

This command starts the MySql client with the user of root and will prompt you to enter your MySQL root password.

The above screen shot is the MySQL monitor interface.  It will be used to connect, create, delete and modify databases and tables within MySQL.  The interface can look a little daunting but when you have to connect to a database that is on the other side of the globe and speed is an issue, you will be glad that this basic looking yet powerful interface is available.  There are alternatives to the command line and one of these is called PHPMyAdmin.

The next command will create a database called scores.  This database will be used to store user game scores for the fictional game  called Obi On The Run.

At the mysql> prompt enter the following command.  Remember to complete the command by entering the ; at the end of the line.

mysql> CREATE DATABASE scores;

The database has been created but now we need to work on that database.  This is done by issuing the USE command.  Enter the following.

mysql> USE scores;

You will receive a confirmation that the database has changed.  All future commands will be performed on the scores database.

Rather than use the MySQL root account to access each database, it is far more secure to create a user with less privileges.  This limits any changes to this database only.  The following command will do just that.  The user will be called gamer and it is this user’s job to maintain the scores database.

mysql> CREATE USER ‘gamer’@’localhost’ IDENTIFIED BY ‘password’;

This creates the user called ‘game@localhost’ with the password of password.  Next you will want to setup the actions that this user can perform.

mysql> GRANT ALL PRIVILEGES ON scores.* TO ‘gamer’@'localhost’;

This allows all actions to be performed on the scores database for the gamer@localhost user.

The next command refreshes the database with the new privileges that you have just created and informs mysql to update its internal data with the new user privileges.

mysql> FLUSH PRIVILEGES;

If you do not issue the FLUSH PRIVILEGES command then you will not be able to login using the game user unless your reboot the Raspberry Pi.

And finally enter quit to quit.

mysql> quit

You can now log in with the new user that you just created.  Enter the following and enter the password when prompted.

mysql> -u gamer -p

Databases use tables to store information so the next step will be to create one.  A table will be created to store the scores and some additional text which will represent the player name.  The screenshot below demonstrates a Database called scores that contains a table called thescores.

Bonus information.

Databases normally contain multiple tables which are used to store different data depending on the application.  Even though this database is storing scores, it could also store a character table which could contain a number of characters to select from.

Select the database to use.

mysql> use scores;

Enter the following command to create the table.

CREATE TABLE thescores (player_name TEXT, score NUMERIC);

This will create a table called the_scores which will contain a field called player_name.

This will be a text field so we will let know MySQL know that this field can only store text.  This is commonly referred to as a DATA TYPE because you are enforcing the type of data that can be stored in these fields.   It can include numbers also such as Mike123 or just 123 but data in this field will always be treated as a text.

Finally the table will record the players score in the field scores.  As this type of data will be a number you can define this data type as a numeric.  As we will be declaring this as a numeric field, only numbers can be stored in this field.

Armed with this background knowledge you can now entered data into the correct fields without receiving an error.  Enter the following.

INSERT INTO thescores values(‘William Hay’, 250);

Each time your insert some data MySQL will respond with an acknowledgement.

Query OK, 1 row affected (0.01 sec)

Pay attention to the name that was just entered and notice that a text string has to be enclosed within ‘ but a numeric of number does not.

Enter the next line

INSERT INTO thescores values(‘Roger Johnson’, 200);

Finally enter

INSERT INTO thescores values(‘John123’, 400);

Now that there is some data in the table, we can retrieve this and manipulate it but first it would be nice to actually see which tables exists.

mysql>  SHOW TABLES;

Only 1 table is being displayed which is exactly what we have created.  To see what is in the table enter the following.

mysql> SELECT player_name, score FROM thescores;

The select command is known as a query because you are querying the database for information.

It can be a pain to type out every field name when it comes to displaying data within tables so the * symbol is the super hero in this case.

mysql> SELECT * FROM thescores;

This achieves the same result as typing every field name to display the data.  You can aslo isolate individual columns of data by just using a single field name.  For example you might only just want to know the names of the players using the game.

mysql> SELECT player_name FROM thescores;

The above command retrieved the player_name and displayed them in no particular order.  Adding some order to this table is just a matter of extending this query.  Enter the following.

mysql > SELECT  player_name, score FROM thescores ORDER BY score DESC;

You will notice that the order of the scores are being displayed with the highest numeric value first.  Add the following query to display the result with a lowest scores first.

mysql > SELECT  player_name, score from thescores ORDER BY score ASC;

The order clause can also be used for text fields.  Enter the following.

mysql > SELECT  player_name, score FROM thescores ORDER BY player_name;

You will see that the results are displayed in alphabetical [A to Z] order based on the player_name.  This can also be reverse [Z-A] by using DESC for descending order.

mysql > SELECT  player_name, score FROM thescores ORDER BY player_name DESC;