MySQL – MariaDB – The Basics and the mysql Client

How to configure nginx for wordpress

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

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

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 the root 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 advanced

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

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.


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

Copyright (c) 2000, 2013, Oracle, Monty Program Ab and others.

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:


This command provides help on using the mysql

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


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
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

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 the mysql 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
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:


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

| 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

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

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,


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

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:


| 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


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


| 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:


| Field   | Type    | Null | Key | Default | Extra |
| book_id | int(11) | YES  |     | NULL    |       |
| title   | text    | 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

Inserting and Manipulating Data

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

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
. 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

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

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 and status
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

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 rows where
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

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

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 selects book_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.


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.


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

  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

  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

  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:

    FROM contacts JOIN relation_types
    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.

Comments are closed.