The mysql Client

The mysql Client

          This section discusses mysql, a general-purpose client program for issuing queries and retrieving their
results. It can be used interactively or in batch mode to read queries from a file.

Using mysql Interactively

     The mysql client program enables the sending of queries to the MySQL server with any results being
displayed in the same interface. It can be used interactively or it can read query input from a file in batch
mode:

• Interactive mode is useful for day-to-day usage, for quick one-time queries, and for testing how
queries work.

• Batch mode is useful for running queries that have been prewritten and stored in a file. It's especially valuable for issuing a complex series of queries that's difficult to enter manually, or queries that need to be run automatically by a job scheduler without user intervention. MySQL statements such as the version query, shown below as executed within the mysql client, can also be run from the shell command prompt as part of the mysql client startup;

mysql> SELECT VERSION();


+-----------------+
| VERSION()       |
+-----------------+
| 5.0.41-log           |
+-----------------+

You can execute a statement directly from the command line by using the -e or --execute option:

shell> mysql -u user_name -ppassword -e "SELECT VERSION()"


+-----------------+
| VERSION()       |
+-----------------+
| 5.0.41-log           |
+-----------------+

No statement terminator is necessary unless the string following -e consists of multiple statements. In that case, separate the statements by semicolon characters.





Statement Terminators


There are numerous terminators that can be used in the mysql client to end a statement. Two terminators
are the semicolon character (';') and the \g sequence. They're equivalent and may be used
interchangeably:

mysql> SELECT VERSION(), DATABASE();


+-----------------+--------------------+
| VERSION()       | DATABASE()        |
+-----------------+--------------------+
| 5.0.40-log | INFORMATION_SCHEMA |
+-----------------+--------------------+

The \G sequence also terminates queries, but causes mysql to display query results in a vertical style that
shows each output row with each column value on a separate line:

mysql> SELECT VERSION(), DATABASE()\G

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

VERSION(): 5.0.40-log
DATABASE(): INFORMATION_SCHEMA

The \G terminator is especially useful if a query produces very wide output lines because vertical format
can make the result much easier to read.

The mysql client allows a single query to be entered using multiple input lines. This makes it easier to
issue a long query because you can enter it over the course of several lines. mysql will wait until it sees
the statement terminator before sending the query to the server to be executed. For example:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
-> FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_NAME LIKE 'E%';
 +--------------------+-----------------+-----------------+
| TABLE_SCHEMA | TABLE_NAME | TABLE_TYPE   |
+--------------------+-----------------+------------------+
| information_schema | ENGINES          | SYSTEM VIEW |
| information_schema | EVENTS            | SYSTEM VIEW |
| mysql                      | event                   | BASE TABLE    |
+--------------------+-----------------+------------------+
3 rows in set (#.## sec)

In the preceding example, mysql changes the prompt from mysql> to -> to provide feedback that it's still waiting to see the end of the statement.

If a statement results in an error, mysql displays the following error message:

mysql> This is an invalid statement;
ERROR 1064 (42000): You have an error in your SQL syntax.

If it is necessary to cancel a statement once mysql has already received some of the input, enter \c and

mysql will cancel the statement and return the mysql> prompt:

mysql> SELECT * FROM INFORMATION_SCHEMA > WHERE \c
mysql>

To quit mysql, use \q, QUIT, or EXIT: mysql> \q