(mysql.info) entering-queries
Info Catalog
(mysql.info) connecting-disconnecting
(mysql.info) tutorial
(mysql.info) database-use
3.2 Entering Queries
====================
Make sure that you are connected to the server, as discussed in the
previous section. Doing so does not in itself select any database to
work with, but that's okay. At this point, it's more important to find
out a little about how to issue queries than to jump right in creating
tables, loading data into them, and retrieving data from them. This
section describes the basic principles of entering commands, using
several queries you can try out to familiarize yourself with how `mysql'
works.
Here's a simple command that asks the server to tell you its version
number and the current date. Type it in as shown here following the
`mysql>' prompt and press Enter:
mysql> SELECT VERSION(), CURRENT_DATE;
+----------------+--------------+
| VERSION() | CURRENT_DATE |
+----------------+--------------+
| 5.0.7-beta-Max | 2005-07-11 |
+----------------+--------------+
1 row in set (0.01 sec)
mysql>
This query illustrates several things about `mysql':
* A command normally consists of an SQL statement followed by a
semicolon. (There are some exceptions where a semicolon may be
omitted. `QUIT', mentioned earlier, is one of them. We'll get to
others later.)
* When you issue a command, `mysql' sends it to the server for
execution and displays the results, then prints another `mysql>'
prompt to indicate that it is ready for another command.
* `mysql' displays query output in tabular form (rows and columns).
The first row contains labels for the columns. The rows following
are the query results. Normally, column labels are the names of
the columns you fetch from database tables. If you're retrieving
the value of an expression rather than a table column (as in the
example just shown), `mysql' labels the column using the
expression itself.
* `mysql' shows how many rows were returned and how long the query
took to execute, which gives you a rough idea of server
performance. These values are imprecise because they represent
wall clock time (not CPU or machine time), and because they are
affected by factors such as server load and network latency. (For
brevity, the `rows in set' line is sometimes not shown in the
remaining examples in this chapter.)
Keywords may be entered in any lettercase. The following queries are
equivalent:
mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE;
Here's another query. It demonstrates that you can use `mysql' as a
simple calculator:
mysql> SELECT SIN(PI()/4), (4+1)*5;
+------------------+---------+
| SIN(PI()/4) | (4+1)*5 |
+------------------+---------+
| 0.70710678118655 | 25 |
+------------------+---------+
1 row in set (0.02 sec)
The queries shown thus far have been relatively short, single-line
statements. You can even enter multiple statements on a single line.
Just end each one with a semicolon:
mysql> SELECT VERSION(); SELECT NOW();
+----------------+
| VERSION() |
+----------------+
| 5.0.7-beta-Max |
+----------------+
1 row in set (0.00 sec)
+---------------------+
| NOW() |
+---------------------+
| 2005-07-11 17:59:36 |
+---------------------+
1 row in set (0.00 sec)
A command need not be given all on a single line, so lengthy commands
that require several lines are not a problem. `mysql' determines where
your statement ends by looking for the terminating semicolon, not by
looking for the end of the input line. (In other words, `mysql' accepts
free-format input: it collects input lines but does not execute them
until it sees the semicolon.)
Here's a simple multiple-line statement:
mysql> SELECT
-> USER()
-> ,
-> CURRENT_DATE;
+---------------+--------------+
| USER() | CURRENT_DATE |
+---------------+--------------+
| jon@localhost | 2005-07-11 |
+---------------+--------------+
In this example, notice how the prompt changes from `mysql>' to `->'
after you enter the first line of a multiple-line query. This is how
`mysql' indicates that it has not yet seen a complete statement and is
waiting for the rest. The prompt is your friend, because it provides
valuable feedback. If you use that feedback, you can always be aware of
what `mysql' is waiting for.
If you decide you do not want to execute a command that you are in the
process of entering, cancel it by typing `\c':
mysql> SELECT
-> USER()
-> \c
mysql>
Here, too, notice the prompt. It switches back to `mysql>' after you
type `\c', providing feedback to indicate that `mysql' is ready for a
new command.
The following table shows each of the prompts you may see and
summarizes what they mean about the state that `mysql' is in:
*Prompt**Meaning*
`mysql>'Ready for new command.
`->' Waiting for next line of multiple-line command.
`'>' Waiting for next line, waiting for completion of a string
that began with a single quote (‘`''’).
`">' Waiting for next line, waiting for completion of a string
that began with a double quote (‘`"'’).
``>' Waiting for next line, waiting for completion of an
identifier that began with a backtick (‘``'’).
`/*>' Waiting for next line, waiting for completion of a
comment that began with `/*'.
In the MySQL 5.0 series, the `/*>' prompt was implemented in MySQL
5.0.6.
Multiple-line statements commonly occur by accident when you intend to
issue a command on a single line, but forget the terminating semicolon.
In this case, `mysql' waits for more input:
mysql> SELECT USER()
->
If this happens to you (you think you've entered a statement but the
only response is a `->' prompt), most likely `mysql' is waiting for the
semicolon. If you don't notice what the prompt is telling you, you
might sit there for a while before realizing what you need to do. Enter
a semicolon to complete the statement, and `mysql' executes it:
mysql> SELECT USER()
-> ;
+---------------+
| USER() |
+---------------+
| jon@localhost |
+---------------+
The `'>' and `">' prompts occur during string collection (another way
of saying that MySQL is waiting for completion of a string). In MySQL,
you can write strings surrounded by either ‘`''’ or ‘`"'’
characters (for example, `'hello'' or `"goodbye"'), and `mysql' lets
you enter strings that span multiple lines. When you see a `'>' or `">'
prompt, it means that you have entered a line containing a string that
begins with a ‘`''’ or ‘`"'’ quote character, but have not yet
entered the matching quote that terminates the string. This often
indicates that you have inadvertently left out a quote character. For
example:
mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
'>
If you enter this `SELECT' statement, then press Enter and wait for the
result, nothing happens. Instead of wondering why this query takes so
long, notice the clue provided by the `'>' prompt. It tells you that
`mysql' expects to see the rest of an unterminated string. (Do you see
the error in the statement? The string `'Smith' is missing the second
single quote mark.)
At this point, what do you do? The simplest thing is to cancel the
command. However, you cannot just type `\c' in this case, because
`mysql' interprets it as part of the string that it is collecting.
Instead, enter the closing quote character (so `mysql' knows you've
finished the string), then type `\c':
mysql> SELECT * FROM my_table WHERE name = 'Smith AND age < 30;
'> '\c
mysql>
The prompt changes back to `mysql>', indicating that `mysql' is ready
for a new command.
The ``>' prompt is similar to the `'>' and `">' prompts, but indicates
that you have begun but not completed a backtick-quoted identifier.
It is important to know what the `'>', `">', and ``>' prompts signify,
because if you mistakenly enter an unterminated string, any further
lines you type appear to be ignored by `mysql' -- including a line
containing `QUIT'. This can be quite confusing, especially if you do
not know that you need to supply the terminating quote before you can
cancel the current command.
Info Catalog
(mysql.info) connecting-disconnecting
(mysql.info) tutorial
(mysql.info) database-use
automatically generated byinfo2html