Interacting with MySQL
Now you have got an idea of all the features of MySQL, it is time to start taking a look at them in action. There are two ways of working with MySQL that we will be covering: the MySQL monitor, and PHP. The MySQL monitor (often just called the MySQL client) is a command line-based tool that allows you to send queries direct to MySQL and receive the output in a formatted table - this is the best way to learn SQL, and also the best way to try out various new queries to make sure you have got things right.
The other way is PHP, naturally, and we will be looking at that later. To start off with we will be working solely with the MySQL monitor. Once you have the MySQL server installed, and started ready for connections, type "mysql -u root -p" at your command line. You will be prompted for the MySQL root password you selected during installation. Once you have entered the password, you will see a screen like the one below:
This program is the MySQL monitor. From here, as root, you can control all aspects of your database server, including adding users, creating new databases, changing data, and just trying out queries.
We'll be using the MySQL monitor for the whole of this section - I will let you know when you should switch to using PHP.
For the purpose of this chapter we will be using the database "phpdb", so we first need to create and select that database. To do this, type the following:
CREATE DATABASE phpdb;
The first line creates the database phpdb, and the second one says "all SQL queries use phpdb until further notification". You can create other databases if you like, then you can delete them using "DROP DATABASE phpdb". Note that both commands, and indeed all commands in SQL, end with a semicolon.
Author's Note: Do not delete the database "mysql". It contains all sorts of crucial information to your server, and should be left alone most of the time.