Getting started with the mysql client

Now that we've installed MariaDB, and we know how to start it, we are ready to open the client and start running our first SQL statements!

In this book, we will use the mysql command-line client to run SQL statements. Many open source graphical clients are available for free. They provide an easy way to perform most operations without the need for remembering the syntax of all the SQL statements. The actions performed on the graphic interface are internally converted to SQL statements. Most of these clients also provide the ability to manually type the statements for execution. However, by manually typing the SQL statements into mysql we will always exactly know what we are doing. Additionally, once we accumulate some experience, this method will probably be the faster one in most situations.

In this section, we will learn to start and quit the command-line client, run statements, and to use the client commands.

Starting and quitting the client

To start the client, we need to specify at least the username and the password that we want to use for this session. The options to do this are the same as we used earlier for mysqladmin. The following example shows how to start mysql and the typical output that appears on the screen:

Now the client is running in an interactive mode, which means that we can enter queries and it will show us an output. When we want to quit the client, we can type the \quit command or its brief version, \q.

MariaDB [test]> \quit
Bye

There are two non-interactive ways to use the command-line client as well. One of them is to pass it a single statement. The client will send the query to the server, which will show us the output and then terminate. This is useful if we do not have other statements to execute. The -e option can be used to pass a query to mysql, like in the following example:

federico@this:~$ /usr/local/mysql/bin/mysql -uroot -psaoirse -e "SELECT VERSION()"
+---------------------+
| VERSION()           |
+---------------------+
| 10.0.13-MariaDB-log |
+---------------------+

It is also possible to pass the path of a text file containing the SQL statements to MySQL for execution. The client will read the file, execute all the commands, and show us the output. We can do this with a system-independent syntax:

mysql -uroot -proot < 1.sql

Running queries

Let's look at the prompt, the final line in the preceding example. It starts with the words: MariaDB. This is useful, because the mysql client also allows us to connect to the MySQL databases. But this string informs us that we are connected to MariaDB. Then we see none, which means that no default database is selected.

This means that, in our SQL statements, we always have to specify a database name. For example, if we want to list all the tables in a database, we will type the following:

MariaDB [(none)]> SHOW TABLES FROM test;
+-----------------+
| Tables_in_test  |
+-----------------+
| _xy             |
+-----------------+
1 rows in set (0.00 sec)

With a view to typing less verbose statements, when working with a database we can select it with the USE command. Consider the following example:

MariaDB [(none)]> USE test;
Database changed
MariaDB [test]> SHOW TABLES;
+-----------------+
| Tables_in_test  |
+-----------------+
| _xy             |
+-----------------+
1 rows in set (0.00 sec)

As we can see from these examples, to run an SQL statement, we just need to type it into the mysql command line. We can separate the words with any number of spaces, tabs, and new line characters. The client knows that a statement is finished when it finds a delimiter, which is, by default, the semicolon character (;). It is even possible to write more than one statement in one line. Take a look at the following two examples:

MariaDB [test]> SELECT
    -> VERSION();
+---------------------+
| VERSION()           |
+---------------------+
| 10.0.13-MariaDB-log |
+---------------------+
1 row in set (0.00 sec)
MariaDB [test]> SELECT VERSION(); SELECT PI();
+---------------------+
| VERSION()           |
+---------------------+
| 10.0.13-MariaDB-log |
+---------------------+
1 row in set (0.00 sec)
+----------+
| PI()     |
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)

In all the preceding examples, the output of the queries is shown in tabular form. But when an output contains many columns, it can be useful to print it vertically. This can be done by terminating a statement with the character, \G instead of a semicolon. For example:

MariaDB [test]> SHOW CHARACTER SET LIKE 'ascii' \G
*************************** 1. row ***************************
          Charset: ascii
      Description: US ASCII
Default collation: ascii_general_ci
           Maxlen: 1
1 row in set (0.00 sec)

Even if the client is running in interactive mode, we can still execute the SQL statements from a test file by using the SOURCE command:

SOURCE my_file.sql;

In the Linux and UNIX systems, mysql maintains a history of the statements that we execute. We can recall such statements and execute them again. To move backwards and forward through the statement history, we can use the arrow up and arrow down keys. You can also use Ctrl + R, as in any standard shell, to do a reverse search in the history.

Client commands

In the previous examples, we used some client commands, such as \q (or \quit) to leave the client, and \G to get the output displayed vertically. A client command is a statement that affects the behavior of mysql in some way. These commands are never sent to the server. Most client commands are brief strings starting with the backslash character (\), though USE and SOURCE are client commands too. Here we will see the most useful commands.

The \h command, or \help, shows a list of the available client commands.

Sometimes, we want to completely delete the statement we are typing because of an error. A faster way to achieve the same result is by using the \c command and pressing Enter. mysql will simply ignore the line, and will not send it to the server.

The \W command (upper case) causes server warnings to be shown in the command line along with fatal errors. The \w causes warnings to be hidden, but errors will still appear. While the default behavior hides the warnings, examining them could be important to find out if a problem occurs during the execution of statements.

On Linux, \P can be used to set a pager. A pager is a program used to see statement results. For example, if a query produces large results, we can use less as a pager to be able to scroll through the results. Then, \n can be used to unset the pager. For example:

MariaDB [(none)]> \P less
PAGER set to 'less'
MariaDB [(none)]> SELECT * FROM information_schema.COLUMNS \G
2042 rows in set (0.68 sec)
MariaDB [(none)]> \n
PAGER set to stdout

On the Linux and UNIX systems, we can use the \e command to compose a statement in an external editor such as Vim or GNU Emacs. The choice of the editor depends on the $EDITOR system variable.

With the system or \! command, we can execute a system command, and see its output on the screen. This can be useful in several situations. For example, if we want to install a plugin but we do not remember the file name, we can use one of the following commands to list the contents of the plugin directory:

system ls /usr/local/mysql/lib/plugin/
\! ls /usr/local/mysql/lib/plugin/