Copyright (C) 2000-2012 |
GNU Info (mysql.info)Entering queriesEntering Queries ================ Make sure you are connected to the server, as discussed in the previous section. Doing so will 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 below following the `mysql>' prompt and hit the RETURN key: mysql> SELECT VERSION(), CURRENT_DATE; +--------------+--------------+ | version() | CURRENT_DATE | +--------------+--------------+ | 3.22.20a-log | 1999-03-19 | +--------------+--------------+ 1 row in set (0.01 sec) mysql> This query illustrates several things about `mysql': * A command normally consists of a SQL statement followed by a semicolon. (There are some exceptions where a semicolon is not needed. `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>' to indicate that it is ready for another command. * `mysql' displays query output as a table (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 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.707107 | 25 | +-------------+---------+ The commands 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() | +--------------+ | 3.22.20a-log | +--------------+ +---------------------+ | NOW() | +---------------------+ | 1999-03-19 00:15:33 | +---------------------+ 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 | +--------------------+--------------+ | joesmith@localhost | 1999-03-18 | +--------------------+--------------+ 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 hasn't 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 will always be aware of what `mysql' is waiting for. If you decide you don't 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, collecting a string that begins with a '>' single quote (`''). ` Waiting for next line, collecting a string that begins with a ">' double quote (`"'). 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' will execute it: mysql> SELECT USER() -> ; +--------------------+ | USER() | +--------------------+ | joesmith@localhost | +--------------------+ The `'>' and `">' prompts occur during string collection. 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've 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. That's fine if you really are entering a multiple-line string, but how likely is that? Not very. More often, the `'>' and `">' prompts indicate that you've inadvertantly 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 hit RETURN and wait for the result, nothing will happen. 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 quote.) 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. It's important to know what the `'>' and `">' prompts signify, because if you mistakenly enter an unterminated string, any further lines you type will appear to be ignored by `mysql' -- including a line containing `QUIT'! This can be quite confusing, especially if you don't know that you need to supply the terminating quote before you can cancel the current command. automatically generated by info2www version 1.2.2.9 |