2.1. Using mysql Interactively

2.1. Using mysql Interactively

The mysql client program enables you to send queries to the MySQL server and receive their results. 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.

This section describes how to use mysql interactively. Batch mode is covered in Section 2.5, "Using Script Files with mysql."

To invoke mysql interactively from the command line, specify any necessary connection parameters after the command name:

 shell> mysql -u user_name -p -h host_name 

You can also provide a database name to select that database as the default database:

 shell> mysql -u user_name -p -h host_name db_name 

mysql understands the standard command-line options for specifying connection parameters. It also reads options from option files. Connection parameters and option files are discussed in Section 1.2, "Invoking Client Programs."

After mysql connects to the MySQL server, it prints a mysql> prompt to indicate that it's ready to accept queries. To issue a query, enter it at the prompt. Complete the query with a statement terminator (typically a semicolon). The terminator tells mysql that the statement has been entered completely and should be executed. When mysql sees the terminator, it sends the query to the server and then retrieves and displays the result. For example:

 mysql> SELECT DATABASE(); +------------+ | DATABASE() | +------------+ | world      | +------------+ 

A terminator is necessary after each statement because mysql allows several queries to be entered on a single input line. mysql uses the terminators to distinguish where each query ends, and then sends each one to the server in turn and displays its results:

 mysql> SELECT DATABASE(); SELECT VERSION(); +------------+ | DATABASE() | +------------+ | world      | +------------+ +-----------------+ | VERSION()       | +-----------------+ | 5.0.10-beta-log | +-----------------+ 

Statement terminators are necessary for another reason as well: mysql 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 Name, Population FROM City     -> WHERE CountryCode = 'IND'     -> AND Population > 3000000; +--------------------+------------+ | Name               | Population | +--------------------+------------+ | Mumbai (Bombay)    |   10500000 | | Delhi              |    7206704 | | Calcutta [Kolkata] |    4399819 | | Chennai (Madras)   |    3841396 | +--------------------+------------+ 

Further information about statement terminators can be found in Section 2.2, "Statement Terminators."

In the preceding example, notice what happens when you don't complete the statement on a single input line: mysql changes the prompt from mysql> to -> to give you feedback that it's still waiting to see the end of the statement. The full set of mysql prompts is discussed in Section 2.3, "The mysql Prompts."

If a statement results in an error, mysql displays the error message returned by the server:

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

If you change your mind about a statement that you're composing, enter \c and mysql will cancel the statement and return you to a new mysql> prompt:

 mysql> SELECT Name, Population FROM City     -> WHERE \c mysql> 

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

 mysql> \q 

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

 shell> mysql -e "SELECT VERSION()" +-----------------+ | VERSION()       | +-----------------+ | 5.0.10-beta-log | +--------------- -+ 

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