Learning MySQL and MariaDB (2015)

Part I. The Software

Chapter 3. The Basics and the mysql Client

There are various methods of interacting with a MySQL or MariaDB server to develop or work with a database. A program that interfaces with the server is known as a MySQL client. There are many such clients, but this book focuses on one that best serves the need of interactive users, a text-based client known simply as mysql. It’s the most commonly used interface, recommended for beginners and preferred by advanced users.

There are alternative clients with GUIs, but in the long run they’re not as useful. First, you don’t learn as much while using them. Because they give you visual hints about what to do, you may be able to carry out some basic queries quickly, but you won’t be as well prepared for advanced work. The text-based mysql client causes you to think and remember more — and it’s not that difficult to use or confusing. More importantly, GUIs tend to change often. When they do, you will need to learn where to find what you want in the new version. If you change jobs or go to a customer’s site, or for whatever reason use someone else’s system, they may not use the same GUI with which you are familiar. However, they will always have the mysql client, because it’s installed with the MySQL server. So all examples in this book assume that this is the client you will use. I recommend that when examples are shown, that you try entering them on your computer with the mysql client so that you can reinforce what you’re learning.

The mysql Client

With the mysql client, you may interact with the MySQL or MariaDB server from either the command line or within an interface environment called the monitor. The command-line method of using mysql allows you to interact with the server without much overhead. It also allows you to enter MySQL commands in scripts and other programs. For instance, you can put lines in cron to perform maintenance tasks and make backups automatically of databases. The monitor is an ASCII display of mysql that makes the text a little more organized and provides more information about commands you execute. Almost all of the examples in this book are taken from the monitor display. If they’re not, I will note that they are from the command line.

If MySQL or MariaDB was installed properly on your server, mysql should be available for you to use. If not, see Post-Installation to make sure everything is configured correctly on your system and make sure you created the necessary symbolic links or aliases. The mysql client should be in the /bin/ or /usr/bin/ directory. Windows, Macs, and other operating systems with GUIs have file location utilities for finding a program. Look for the directory containing the mysql client and the other binary files for MySQL.

Assuming that everything is working, you will need a MySQL username and password to be able to connect to MySQL, even with the mysql client. If you’re not the administrator, you must obtain these credentials from the appointed person. If MySQL or MariaDB was just installed and theroot password is not set yet, its password is blank — that is to say, just press the Enter key when prompted for the password. To learn how to set the root password and to create new users and grant them privileges, see Post-Installation for starting pointers and Chapter 13 for more advanceddetails.

Connecting to the Server

Once you know your MySQL username and password, you can connect to the MySQL server with the mysql client. For instance, I gave myself the username russell so I can connect as follows from a command line:

mysql -u russell -p

It’s useful to understand each element of the previous line. The -u option is followed by your username. Notice that the option and name are separated by a space. You would replace russell here with whatever username you’ve created for yourself. This is the MySQL user, not the user for the operating system. Incidentally, it’s not a good security practice to use the root user, unless you have a specific administrative task to perform for which only root has the needed privileges. So if you haven’t created another user for yourself, go back and do that now. To log into MariaDB, you would enter the same command and options as for MySQL.

The -p option instructs the mysql client to prompt you for the password. You could add the password to the end of the -p option (e.g., -pRover#My_1st_Dog&Not_Yours!, where the text after -p is the password). If you do this, leave no space between -p and the password. However, entering the password on the command line is not a good security practice either, because it displays the password on the screen (which others standing behind you may see), and it transmits the password as clear text through the network, as well as making it visible whenever someone gets a list of processes that are running on the server. It’s better to give the -p option without the password and then enter the password when asked by the server. Then the password won’t be displayed on the screen or saved anywhere.

If you’re logged into the server filesystem with the same username as you created for MySQL, you won’t need the -u option; the -p is all you’ll need. You could then just enter this:

mysql -p

Once you’ve entered the proper mysql command to connect to the server, along with the password when prompted, you will be logged into MySQL or MariaDB through the client. You will see something that looks like this:

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 1419341

Server version: 5.5.29 MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

If MariaDB is installed on your server, you will see something like the following:

Welcome to the MariaDB monitor. Commands end with ; or \g.

Your MariaDB connection id is 360511

Server version: 5.5.33a-MariaDB MariaDB Server, wsrep_23.7.6.rXXXX

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>>

The first line, after “Welcome to the MySQL/MariaDB monitor,” says that commands end with a semicolon (;) or a slash-g (\g). When you enter a command, or rather an SQL statement, you can press Enter at any point to go to the next line and continue entering more text. Until you enter either ; or \g, the mysql client will not transmit what you’ve entered to the MySQL server. If you use \G, with an uppercase G, you’ll get a different format. We’ll cover that format later. For now, just use the semicolon.

The second line in the output shown tells you the identification number for your connection to the server. One day you may get in trouble and need to know that. For now you can ignore it.

The third line tells you which version of MySQL or MariaDB is installed on the server. That can be useful when you have problems and discover in reading the online documentation that the problem is in a particular version, or when you want to upgrade the server but need to know which version you have now before upgrading.

The next line talks about getting online help. It provides help for all of the SQL statements and functions. Try entering these commands to see what the client returns:

help

This command provides help on using the mysql client.

help contents

This command shows you a list of categories for help on major aspects of MySQL or MariaDB. In that list, you will see one of the categories is called Data Manipulation. These are SQL statements related to inserting, updating, and deleting data.

hep Data Manipulation

This command will display all of those statements for which help is available from the client. One of those SQL statements is SHOW DATABASES.

help SHOW DATABASES

This command shows how to retrieve the help information related to that SQL statement. As you can see, there is plenty of useful information accessible within the client. If you can’t quite remember the syntax of an SQL statement, it’s a quick way to retrieve the information.

The first help command provides help on using the mysql client. The second help command shows you a list of categories for help on major aspects of MySQL or MariaDB. In that list, you will see one of the categories is called, Data Manipulation. These are SQL statements related to inserting, updating, and deleting data. The third help command will display all of those statements for which help is available from the client. One of those SQL statements is SHOW DATABASES. The last help command shows how to retrieve the help information related to that SQL statement. As you can see, there is plenty of useful information accessible within the client. If you can’t quite remember the syntax of an SQL statement, it’s a quick way to retrieve the information.

A minor but sometimes useful tip is included in the third line of the opening results: to cancel an SQL statement once you’ve started typing it, enter \c and press Enter without a closing semicolon. It will clear whatever you have been entering, even on previous lines, from the buffer of themysql client, and return you to the mysql> prompt.

The very last line, the mysql>, is known as the prompt. It’s prompting you to enter a command, and is where you’ll operate during most of this book. If you press Enter without finishing a command, the prompt will change to -> to indicate that the client hasn’t yet sent the SQL statement to the server. On MariaDB, the default prompt is different. It shows MariaDB [(none)]>> to start. When you later set the default database to be used, the none will be changed to the name of the current default database.

Incidentally, it is possible to change the prompt to something else. To do so, enter the client command prompt followed by the text you want to display for the prompt. There are a few special settings (e.g., \d for default database). Here’s how you might change the prompt:

prompt SQL Command \d>\_

And here’s how the prompt will look after you run the preceding command to change it:

SQL Command (none)>

Right now you have no default database. So now that you have the mysql client started, let’s start exploring databases.

Starting to Explore Databases

The next few chapters cover how to create databases, add data to them, and run queries to find interesting relationships. In this chapter, while you’re logged into MySQL or MariaDB with the mysql client, let’s get familiar with the core aspects of the database system. We’ll consider a few basic concepts of databases so that you may enter a few commands within the mysql monitor. This will help you get comfortable with the mysql client. Because you may be in a very early stage of learning, we’ll keep it simple for now.

In SQL terminology, data is always stored in a table, a term that reflects the way a user generally views the data. In a table about movies, for example, you might see a horizontal row about each movie, with the title as one column, and other columns to indicate more information on each movie:

+----------+--------------------+--------+

| movie_id | title | rating |

+----------+---------------------+--------+

| 1 | Casablanca | PG |

| 2 | The Impostors | R |

| 3 | The Bourne Identity | PG-13 |

+----------+--------------------+--------+

That’s just a simple example. Don’t try to create that table. Let’s first take a look at what you already have on your server, to see these elements. From the mysql> prompt, enter the following and press the Enter key:

SHOW DATABASES;

The following output (or something similar) should be displayed in response:

+--------------------+

| Database |

+--------------------+

| information_schema |

| mysql |

| test |

+--------------------+

First, let me mention a book convention. MySQL is not case sensitive when you enter keywords such as SHOW. You could just as well enter show or even sHoW. However, the names of databases, tables, and columns may be case sensitive, especially on an operating system that is case sensitive, such as Mac OS X or Linux. Most books and documentation use all upper case letters to indicate keywords while respecting the case of the things that you can change. We use all lower case letters for database, table, and column names because it’s easier on the eyes and easier to type, and mostly because it’s easier for the reader to distinguish between what is set by the SQL convention and what is flexible.

The list just displayed shows that you have three databases at the start of using MySQL, created automatically during installation. The information_schema database contains information about the server. The next database in the list is mysql, which stores usernames, passwords, and user privileges. When you created a user for yourself at the end of Chapter 2, this is where that information was stored. You may have noticed that some commands shown in Chapter 2 referenced this database. Don’t try to change the mysql database directly. Later, I’ll show you commands for manipulating this database. At least for now, access the mysql database only through administrative functions and utilities. The last database listed is called test. That’s there for you to test things and to use when learning. Let’s use that for a bit in this chapter.

First SQL Commands

The test database is initially empty; it contains no tables. So let’s create one. Don’t worry about understanding what you’re doing in detail. I’ll introduce concepts gradually as we go along.

So enter the following in the mysql client (remember the terminating semicolon):

CREATE TABLE test.books (book_id INT, title TEXT, status INT);

This is your first SQL statement. It creates a table in the test database and names it books. We specified the name of the database and table with test.books (i.e., the format is database.table). We also defined, within the parentheses, three columns for the table. We’ll talk about that in more depth later.

If you correctly type that SQL statement, you’ll receive a reply like this:

Query OK, 0 rows affected (0.19 sec)

This is a message from the server reporting how things went with the SQL statement you sent. What you need to take from the message is that everything is OK. With that, let’s see the results of what we did. To see a list of tables within the test database, enter:

SHOW TABLES FROM test;

The output should be:

+----------------+

| Tables_in_test |

+----------------+

| books |

+----------------+

1 row in set (0.01 sec)

You now have one table, books. Notice that the results are enclosed with ASCII text to look like a table of data, as you might draw it on a piece of paper. Notice also the message after the table. It says that one row is in the set, meaning that books is the only table in the database. The time in parentheses that you will see after running every SQL statement indicates how long it took for the server to process the request. In this case, it took my server 0.01 seconds. I ran that statement from my home computer in Milan, Italy, but using my server in Tampa, Florida in the U.S. That’s a pretty quick response. Sometimes it’s even faster and shows 0.00 seconds, because the lapse in time was not enough to register.

From this point forward, I will leave out these lines of status to save space and to keep the clutter down, unless there’s something relevant to discuss. For the same reason, I’m not including the mysql> prompts. You’ll have to learn when something is entered from the mysql client versus the operating system shell — although I will usually indicate when to enter something from the operating system shell. So from now on, I’ll combine input and output like this:

SHOW TABLES FROM test;

+----------------+

| Tables_in_test |

+----------------+

| books |

+----------------+

You can tell what you’re supposed to enter because it’s bold, whereas the output is not.

For each of these SQL statements, we have to specify the database name. If you will be working mainly in one database (you usually will be), you can set the default database so that you don’t have to specify the database each time. To do this, enter a USE command:

USE test

NOTE

Incidentally, if your server doesn’t have the test database, you can create it by just entering CREATE DATABASE test; on the server first.

Because this is an instruction for the mysql client and not the server, the usual ending semicolon is not needed. The client will change the default database on the server for the client to the one given, making it unnecessary to specify table names without a preceding database name — unless you want to execute an SQL statement for a table in another database. After entering the USE command, you can re-enter the earlier SQL statement to list the tables in the database without specifying that you want test. It’s taken for granted:

SHOW TABLES;

+----------------+

| Tables_in_test |

+----------------+

| books |

+----------------+

Now that we’ve peeked at a database, which is not much more than a grouping of tables (in this example, only one table), and created a table, let’s look inside the table that we created. To do that, we’ll use the SQL statement DESCRIBE, like so:

DESCRIBE books;

+---------+---------+------+-----+---------+-------+

+---------+---------+------+-----+---------+-------+

| book_id | int(11) | YES | | NULL | |

| status | int(11) | YES | | NULL | |

+---------+---------+------+-----+---------+-------+

In these results you can see that we created three fields for entering data, named book_id, title, and status. That’s pretty limited, but we’re keeping things simple in this chapter. The first and third fields, book_id and status, are integer types, meaning they can contain only numbers. We stipulated that when we created the table by adding the INT keyword when specifying those columns. The other field, title, can contain text, which includes anything you can type at the keyboard. We set that earlier with the TEXT keyword. Don’t worry about remembering any of this now. We’re just looking around to get a feel for the system and the mysql client.

Inserting and Manipulating Data

Let’s put some data in this table. Enter the following three SQL statements within the mysql client:

INSERT INTO books VALUES(100, 'Heart of Darkness', 0);

INSERT INTO books VALUES(101, 'The Catcher of the Rye', 1);

INSERT INTO books VALUES(102, 'My Antonia', 0);

All three lines use the SQL statement INSERT to insert, or add data, to the books table. Each line will be followed by a status message (or an error message if you mistype something), but I didn’t bother to include those messages here. Notice that numbers don’t need to be within quotes, but text does. The syntax of SQL statements like this one is pretty structured — hence the name Structured Query Language. You can be casual about spacing between elements of the statements, but you must enter everything in the right order and use the parentheses, commas, and semicolons as shown. Keeping SQL statements structured makes queries predictable and the database faster.

The previous examples insert the values given in parentheses into the table. The values are given in the same order and format as we told MySQL to expect when we created the table: three fields, of which the first and third will be numbers, and the second will be any kind of text. Let’s ask MySQL to display the data we just gave it to see how it looks:

SELECT * FROM books;

+---------+------------------------+--------+

| book_id | title | status |

+---------+------------------------+--------+

| 100 | Heart of Darkness | 0 |

| 101 | The Catcher of the Rye | 1 |

| 102 | My Antonia | 0 |

+---------+------------------------+--------+

In this table, you can see more easily why they call records rows and fields columns. We used the SELECT statement to select all columns — the asterisk (*) means “everything” — from the table named. In this example, book_id functions as a record identification number, while title andstatus contain the text and numbers we want to store. I purposely gave status values of 0 or 1 to indicate status: 0 means inactive and 1 means active. These are arbitrary designations and mean nothing to MySQL or MariaDB. Incidentally, the title of the second book is not correct, but we’ll use it later as an example of how to change data.

Let’s play with these values and the SELECT statement to see how it works. Let’s add a WHERE clause to the SQL statement:

SELECT * FROM books WHERE status = 1;

+---------+------------------------+--------+

| book_id | title | status |

+---------+------------------------+--------+

| 101 | The Catcher of the Rye | 1 |

+---------+------------------------+--------+

In these results, we’ve selected only rows in which status equals 1 (i.e., only records that are active). We did this using the WHERE clause. It’s part of the SELECT statement and not an SQL statement on its own. Let’s try another SQL statement like this one, but ask for the inactive records:

SELECT * FROM books WHERE status = 0 \G

*************************** 1. row ***************************

book_id: 100

title: Heart of Darkness

status: 0

*************************** 2. row ***************************

book_id: 102

title: My Antonia

status: 0

Notice that this time we changed the ending of the SQL statement from a semicolon to \G. This was mentioned earlier in this chapter as an option. It shows the results not in a table format, but as a batch of lines for each record. Sometimes this is easier to read, usually when the fields are so long that a tabular format would be too wide for your screen and would wrap around. It’s a matter of preference for each situation.

We’ve added data to this minimal table. Now let’s change the data a little. Let’s change the status of one of the rows. To do this, we will use the UPDATE statement. It produces two lines of status output:

UPDATE books SET status = 1 WHERE book_id = 102;

Query OK, 1 row affected (0.18 sec)

Rows matched: 1 Changed: 1 Warnings: 0

You can learn how to read and remember SQL statement syntax better if you read and interpret them in the way and order they’re written. Let’s do that with this SQL statement, the first line in the preceding code block. It says to update books by setting the value of status to 1 for all rowswhere book_id equals 102. In this case, there is only one record with that value, so the message that follows says that one row was affected, and only one was changed or updated — however you want to say that. To see the results, run the SELECT statement shown earlier, the one where we check for active status:

SELECT * FROM books WHERE status = 1;

+---------+------------------------+--------+

| book_id | title | status |

+---------+------------------------+--------+

| 101 | The Catcher of the Rye | 1 |

| 102 | My Antonia | 1 |

+---------+------------------------+--------+

Thanks to our update, we get two rows back this time, where the rows have a status of active. If we execute the UPDATE statement again, but for a different book_id, we can change the book, The Catcher in the Rye to inactive:

UPDATE books SET status = 0 WHERE book_id = 101;

SELECT * FROM books WHERE status = 0;

+---------+------------------------+--------+

| book_id | title | status |

+---------+------------------------+--------+

| 100 | Heart of Darkness | 0 |

| 101 | The Catcher of the Rye | 0 |

+---------+------------------------+--------+

Let’s enter one more UPDATE statement so you can see how to do more with just one statement. As I mentioned earlier, the title of this book is not correct. It’s not The Catcher of the Rye. The correct title is The Catcher in the Rye. Let’s change that text in the title column, while simultaneously setting the value of status back to 1. We could do this with two SQL statements, but let’s do it in one like so:

UPDATE books

SET title = 'The Catcher in the Rye', status = 1

WHERE book_id = 101;

Notice that we’ve given the same syntax as before with the UPDATE statement, but we’ve given two pairs of columns and values to set. That’s easier than entering the UPDATE statement twice. It also saves some network traffic when communicating with a server on another continent.

A Little Complexity

Let’s increase the pace a little. Let’s create another table and insert a couple of rows of data in it. Enter these two SQL statements from within the mysql client:

CREATE TABLE status_names (status_id INT, status_name CHAR(8));

INSERT INTO status_names VALUES(0, 'Inactive'), (1,'Active');

Now we’ve created the table status_names, but with only two columns. The CREATE TABLE statement is similar to the one we used to create the first table. There’s one difference I’d like you to notice: instead of using the column type of TEXT, we’re using the column type of CHAR, which stands for “character.” We can add text to this column, but its size is limited: each row can have only a maximum of eight characters in this column. That makes a smaller field and therefore a smaller and faster table. It doesn’t matter in our examples here, as we’re not entering much data, but little specifications like this will make a huge performance difference in large databases. It’s good for you to start thinking this way from the beginning.

The second SQL statement added two sets of values. Doing multiple sets of values in one INSERT is allowed, and is easier than entering a separate line for each. Here’s how the data looks in that table:

SELECT * FROM status_names;

+-----------+-------------+

| status_id | status_name |

+-----------+-------------+

| 0 | Inactive |

| 1 | Active |

+-----------+-------------+

That’s probably a seemingly useless table of data. But let’s combine this table with the first table, books, to see a glimpse of the potential of database system like MariaDB. We’ll use the SELECT statement to join both tables together to get nicer results, and we’ll be selective about which data is displayed. Try this on your computer:

SELECT book_id, title, status_name

FROM books JOIN status_names

WHERE status = status_id;

+---------+------------------------+-------------+

| book_id | title | status_name |

+---------+------------------------+-------------+

| 100 | Heart of Darkness | Inactive |

| 101 | The Catcher in the Rye | Active |

| 102 | My Antonia | Active |

+---------+------------------------+-------------+

First, notice that I broke this SQL statement over three lines. That’s allowed. Nothing is processed until you type a semicolon and then press the Enter key. Breaking apart a statement like this makes it easier to read, but has no effect on MySQL. In this SQL statement, the first line selectsbook_id and title, which are both in books, and status_name, which is in the status_names table. Notice that we didn’t use an asterisk to select all of the columns, but named the specific ones we want. We also chose columns from two tables.

On the second line, we say to select these columns listed from books and from status_names. The JOIN clause is where we named the second table.

In the WHERE clause, on the third line, we tell MySQL to match the values of the status column from books to the values of the status_id column from the status_names table. This is the point in which the rows from each will be joined. If the idea of joining tables seems difficult, don’t worry about it at this point. I’ve included it just to show you what can be done with MySQL and MariaDB. I’ll explain joins more fully later.

When we created books, we could have made status a text or character field and entered the words Active or Inactive for each row. But if you have a table with thousands or maybe millions of rows of data, entering 0 or 1 is much easier and you’re less likely to make typos (e.g., you might enter Actve sometimes). Databases are tedious, but creating tables with better structures and using better written SQL statements makes them less tedious and helps you to leverage your time and resources.

Summary

There’s plenty more you can do to explore the simple tables we’ve created, but in this chapter I wanted just to give you an overview of MySQL and MariaDB, and to show you around. The chapters in Part II will delve into details, starting with Chapter 4, which will cover creating tables in detail.

Before jumping ahead, you might want to reinforce what you just learned from this chapter. A few exercises follow for you to play some more on your own with the test database and the mysql client. When you’re finished, to exit mysql, type quit or exit, and press the Enter key.

Exercises

In addition to logging into MySQL or MariaDB with the mysql client and entering the SQL statements shown already in this chapter, here are a few exercises to get some more practice playing with the mysql client and to help you better understand the basics. Rather than use generic names like books and book_id, you’re asked to use more realistic names. In that same spirit, use fairly realistic data (e.g., “John Smith” for a person’s name) when entering data in these exercises.

1. Log into MySQL or MariaDB using the mysql client and switch the default database to the database, test. Create two tables called contacts and relation_types. For both tables, use column type INT for number columns and CHAR for character columns. Specify the maximum number of characters you want with CHAR — otherwise MySQL wills set a maximum of one character, which is not very useful. Make sure that you allow for enough characters to fit the data you will enter later. If you want to allow characters between numbers (e.g., hyphens for a telephone number), use CHAR. For the contacts, you will need six columns: name, phone_work, phone_mobile, email, relation_id. For the relation_types table, there should be only two columns: relation_id and relationship.
When you’re finished creating both tables, use the DESCRIBE statement to see how they look.

2. Enter data in the two tables created in the previous exercise. Enter data in the second table, relation_types first. Enter three rows of data in it. Use single-digit, sequential numbers for the first column, but the following text for the second column: Family, Friend, Colleague. Now enter data in the table named contacts. Enter at least five fictitious names, telephone numbers, and email addresses. For the last column, relation_id, enter single digits to correspond with the relation_id numbers in the table, relation_types. Make sure you have at least one row for each of the three potential values for relation_id.

3. Execute two SELECT statements to retrieve all of the columns of data from both tables that you created and filled with data from the previous two exercises. Then run a SELECT statement that retrieves only the person’s name and email address from the table named contacts.

4. Change some of the data entered in the previous exercises, using the UPDATE statement. If you don’t remember how to do that, refer back to the examples in this chapter on how to change data in a table. First, change someone’s name or telephone number. Next, change someone’s email address and his or her relationship to you (i.e., relation_id). Do this in one UPDATE statement.

5. Run a SELECT statement that joins both tables created in the first exercise. Use the JOIN clause to do this (the JOIN clause was covered in this chapter, so look back at the example if you don’t remember how to use it). Join the tables on the common column named relation_id — this will go in the WHERE clause. To help you with this, here’s how the clauses for the tables should look:

6. ...

7. FROM contacts JOIN relation_types

8. WHERE contacts.relation_id = relation_types.relation_id

...

Select the columns name and phone_mobile, but only for contacts who are marked as a Friend — you’ll have to add this to the WHERE with AND. Try doing this based on the value of relation_id and then again based on the value of the relationship column.