MySQL Reference Manual-obtain the database and table information-MySQL

MySQL Reference Manual-obtain the database and table information to ensure that you are connected to the server, as discussed in the previous chapter. In this way, no database is selected for work, but that is good. From this point on, I know a little about how to make a query, which is more important than jumping to create a table, load data for them, and retrieve data from them. This section describes the basic principles of input commands. you can use several queries to try to make mysql work.
　　
This is a simple command that requires the server to tell you its version number and current date. Run the following command in mysql> prompt and press enter:
　　
Mysql> select version (), CURRENT_DATE;
+ -------------- +
| Version () | CURRENT_DATE |
+ -------------- +
| 3.22.20a-log | 2-03-19 |
+ -------------- +
1 row in set (0.01 sec)
Mysql>
　　
Here are several things about mysql:
A command is usually composed of SQL statements followed by a semicolon. (A semicolon is not required for some exceptions. The QUIT mentioned earlier is one of them. We will see others later .)
When you issue a command, mysql sends it to the server and displays the result. then, another mysql is displayed. it is ready to accept another command.
Mysql displays the query output in a table (row and column. The first row contains the label of the column, and the subsequent row is the result of the query. Generally, the column label is the name of the column from the database table. If you are retrieving the value of an expression rather than a table column (as in the preceding example), mysql uses the expression itself to mark the column.
Mysql shows how many rows are returned and how long the query takes to execute. It provides you with a general concept of server performance. Because they indicate the clock time (not the CPU or machine time), and because they are affected by server load and network latency, these values are not accurate. (For simplicity, "rows in the set" will not be displayed in the remaining examples in this chapter ".)
Keywords can be input in any case. The following queries are equivalent:
　　
Mysql> select version (), CURRENT_DATE;
Mysql> select version (), current_date;
Mysql> SeLeCt vErSiOn (), current_DATE;
　　
Here is another query, which indicates that you can use mysql as a simple calculator:
Mysql> select sin (PI ()/4), (4 + 1) * 5;
+ ------------- + --------- +
| SIN (PI ()/4) | (4 + 1) * 5 |
+ ------------- + --------- +
| 1, 0.707107 | 25 |
+ ------------- + --------- +
　　
The commands displayed so far are quite short, single-line statements. You can even enter multiple statements on a single row, but end each statement with a semicolon:
　　
Mysql> select version (); select now ();
+ -------------- +
| Version () |
+ -------------- +
| 3.22.20a-log |
+ -------------- +
　　
+ --------------------- +
| NOW () |
+ --------------------- +
| 00:15:33 |
+ --------------------- +
　　
A command does not have to be given in a single line, so it is not a problem to require long commands in multiple lines. Mysql determines where your statement ends by looking for a semicolon instead of the end of the input line. (In other words, mysql accepts input in free format: it collects input rows but executes them until it sees a semicolon .)
　　
Here is an example of a simple multi-line statement:
　　
Mysql> SELECT
-> USER ()
->,
-> CURRENT_DATE;
+ -------------------- + -------------- +
| USER () | CURRENT_DATE |
+ -------------------- + -------------- +
| Joesmith @ localhost |
+ -------------------- + -------------- +
In this example, after you enter the first line of a multi-row query, note how to change the prompt from mysql> to->, this is exactly how mysql points out that it does not see the complete statement and is waiting for the remaining part. The prompt is your friend because it provides valuable feedback. if you use this feedback, you will always know what mysql is waiting.
　　
If you decide, you do not want to execute a command you entered during the input process, and break in/c to cancel it:
　　
Mysql> SELECT
-> USER ()
->/C
Mysql>
　　
Note the prompt here. after you enter/c, it switches back to mysql> and provides feedback to indicate that mysql is ready to accept a new command.
　　
The following table shows the various prompts you can see and summarizes the statuses in which mysql means:
　　
Prompt meaning
Mysql> prepare to accept new commands
-> Wait for the next line of the multi-line command
'> Wait for the next row to collect strings starting with single quotation marks (')
"> Wait for the next row to collect strings starting with double quotation marks (" ")
　　
When you want to issue a command on a single line, multiple-line statements are usually "accidental", but you forget to terminate the semicolon. In this case, mysql waits for further input:
　　
Mysql> select user ()
->
　　
If this happens to you (you think you have finished the statement, but the only response is a-> prompt), it is very likely that mysql is waiting for a semicolon. If you don't notice what the prompt is telling you, you may spend some time sitting there 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 |
+ -------------------- +
　　
'> And "> The prompt appears during string collection. In MySQL, you can write a string (for example, 'hello' or "goodbye") that is enclosed by "'" or "), mysql also allows you to enter a string that spans multiple lines. When you see a '> or "> prompt, it means that you have entered a line containing a string starting with"' "or, however, no matching quotation marks are entered for the ending string. If you are indeed inputting a multi-line string, it is good, but is it true? Not all. More often, '> and "> The prompt shows that you accidentally removed a pair of quotation marks. For example:
　　
Mysql> SELECT * FROM my_table WHERE name = "Smith AND age <30;
">
If you enter the SELECT statement, press the Enter key, and wait for the result, nothing will appear. Don't be surprised, "Why is the query so long ?", Note: "> clues provided by the prompt. It tells you that mysql expects to see the rest of an unterminated string. (Do you see an error in the statement? The second quotation mark is missing for the string "Smith .)
In this step, what should you do? The simplest is to cancel the command. However, in this case, you cannot just break into/c, because mysql interprets it as part of the string it is collecting! On the contrary, enter the closed quotation mark character (so that mysql knows that you have completed the string) and then enter/c:
　　
Mysql> SELECT * FROM my_table WHERE name = "Smith AND age <30;
">"/C
Mysql>
　　
Prompt back to mysql>, it shows that mysql is ready to accept a new command.
　　
Knowing '> and "> What the prompt means is very important, because if you mistakenly enter an unterminated string, any row that is better than the one you enter next seems to be ignored by mysql-including rows that contain QUIT! This may be quite vague, especially if you do not know that you need to propose a termination quotation mark before you can cancel the current command.