mysql input query and exit query commands

Make sure you are connected to the server as discussed in the previous section. Connecting to the server does not select any database, but that's fine. Knowing the basics of how to query is more important than jumping right into creating tables, loading them with data, and retrieving data from them. This section describes the basic principles of entering commands. Using a few queries, you can try to understand how MySQL works.

This is a simple command that asks the server to tell it its version number and current date. Enter the following command at the mysql> prompt and press Enter:

mysql> SELECT VERSION(), CURRENT_DATE; 
+-----------------+--------------+
| VERSION()    | CURRENT_DATE |
+-----------------+--------------+
| 5.1.2-alpha-log | 2005-10-11  |
+-----------------+--------------+
1 row in set (0.01 sec)
mysql>

This question explains several aspects of mysql:

· A command usually consists of a SQL statement, followed by a semicolon. (There are some exceptions that do not require a semicolon. The QUIT mentioned earlier is an example. We will see other examples later.)

· When a command is issued, mysql sends it to the server and displays the execution results, then displays Another mysql> shows that it is ready to accept other commands.

· Mysql displays query output in a table (rows and columns). The first row contains the column labels, and subsequent rows are the query results. Typically, the column label is the name of the column you take from the database table. If you are retrieving an expression rather than the value of a table column (as in the previous example), MySQL tags the column with the expression itself.

· MySQL shows how many rows were returned and how long the query took, which gives you a general idea of ​​server performance. Because they represent clock time (not CPU or machine time), and because they are affected by factors such as server load and network latency, these values ​​are imprecise. (For the sake of brevity, "rows in collection" are no longer shown in other examples in this chapter.)

Ability to enter keywords in upper and lower case (case insensitive). The following queries are equivalent:

mysql> SELECT VERSION(), CURRENT_DATE; mysql> select version(), current_date; mysql> SeLeCt vErSiOn(), current_DATE; 
这是另外一个查询，它说明你能将mysql用作一个简单的计算器：
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 commands displayed so far are fairly short single-line statements. You can enter multiple statements on one line, just separate each statement with a semicolon:

mysql> SELECT VERSION(); SELECT NOW(); 
+-----------------+
| VERSION()       |
+-----------------+
| 5.1.2-alpha-log |
+-----------------+
1 row in set (0.00 sec)
+---------------------+
| NOW()               |
+---------------------+
| 2005-10-11 15:15:00 |
+---------------------+
1 row in set (0.00 sec)

It is not necessary to give a command all on one line, longer commands can be entered on multiple lines. MySQL determines where a statement ends by looking for the terminating semicolon rather than the end of the input line. (In other words, MySQL accepts free-form input: it collects the input lines but doesn't execute until it sees a semicolon.)

Here is an example of a simple multiline statement:

mysql> SELECT     -> USER()     -> , -> CURRENT_DATE; 
+---------------+--------------+
| USER()     | CURRENT_DATE |
+---------------+--------------+
| jon@localhost | 2005-10-11  |
+---------------+--------------+

In this example, after inputting multiple lines After the first line of the query, notice how the prompt changes from mysql> to ->, which is how mysql indicates that it has not seen the complete statement and is waiting for the remainder. The prompt is your friend because it provides valuable feedback, and if you use it, you will always know what mysql is waiting for.

If you decide you don’t want to execute a command while it is being entered, enter c to cancel it:

mysql> SELECT     -> USER()     -> \c mysql>

Also note the prompt here, after you enter c, it switches back to mysql>, providing feedback to indicate that mysql is ready to accept it A new command.

The following table shows the various prompts that can be seen and briefly describes the status of mysql they represent:

Prompt Meaning

mysql> Ready to accept new commands.

-> Wait for the next line of a multi-line command.

'> Wait for the next line, waiting for the end of the string starting with a single quote ("'").

"> Wait for the next line, waiting for the end of the string starting with double quotes (""").

`> Wait for the next line, waiting for the end of the identifier starting with a backslash dot (‘`’).

/*> Wait for the next line and the end of the comment starting with /*.

When you intend to issue a command on a single line, it is often "accidental" that a multi-line statement appears, but without a terminating semicolon. In this case, mysql is waiting for further input:

mysql> SELECT USER()     ->

If this happens (you think you have finished typing the statement, but only a -> prompt responds), it is likely that mysql is waiting for a semicolon. If you don't notice the prompt, 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:

mysql> SELECT USER()    -> ; 
+---------------+
| USER()    |
+---------------+
| jon@localhost |
+---------------+

During string collection, the '> and "> prompts will appear (prompting that MySQL is waiting for the end of the string). In MySQL, you can write by '' ' or '"' characters (for example, 'hello' or "goodbye"), and MySQL allows input strings that span multiple lines. When you see a '> or "> prompt, it means that a line containing a string starting with a ''' or '"' bracket character has been entered, but a matching quote that terminates the string has not been entered. This shows that you carelessly omitted a quote character. For example:

mysql> SELECT * FROM my_table WHERE name = &#39;Smith AND age < 30;     &#39;>

如果你输入SELECT语句，然后按Enter（回车）键并等待结果，什么都没有出现。不要惊讶，“为什么该查询这么长呢？”，注意">提示符提供的线索。它告诉你mysql期望见到一个未终止字符串的余下部分。（你看见语句中的错误吗？字符串"Smith丢掉了第二个引号。）

走到这一步，你该做什么？最简单的是取消命令。然而，在这种情况下，你不能只是输入\c，因为mysql作为它正在收集的字符串的一部分来解释它！相反，应输入关闭的引号字符(这样mysql知道你完成了字符串)，然后输入\c：

mysql> SELECT * FROM my_table WHERE name = &#39;Smith AND age < 30;     &#39;> '\c mysql>

提示符回到mysql>，显示mysql准备好接受一个新命令了。

`> 提示符类似于 '> 和"> 提示符，但表示你已经开始但没有结束以`> 开始的识别符。

知道'>和">提示符的含义很重要，因为如果你错误地输入一个未终止的字符串，任何后面输入的行将要被mysql忽略--包括包含QUIT的行！这可能令人相当困惑，特别是如果取消当前命令前还不知道你需要提供终止引号。