MySql official handbook study notes 1 MySql easy to get started

Connect and disconnect servers

Connecting to the server usually requires an MySQL username and probably a password. If the server is running on a machine other than the login server, you also need to specify a host name:

shell > mysql -h host -u user -p

Enter password: ********

host stands for the hostname of the MySQL server running, user for the MySQL account username, and ******** ** your password.

If it works, you should see mysql > Some introductory information after the prompt:

shell > mysql -h host -u user -p

Enter password: ********

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection id is 25338 to server version: 5.1.2-alpha-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql >

mysql > The prompt tells you that mysql is ready to type the command for you.

Some MySQL installations allow users to connect as anonymous (unnamed) users to servers running on localhost. If this is the case with your machine, you should be able to call mysql to connect to the server without any options:

shell > mysql

After a successful connection can be made in mysql > Please enter QUIT (or \q) to exit at any time:

mysql > QUIT

Bye

In Unix, you can also disconnect the server by pressing the control-D key.

So let's get familiar with 1

Here is a simple command that asks the server to tell it its version number and current date. In mysql > Prompted to enter the following command and press enter:

mysql > SELECT VERSION (), CURRENT_DATE; //1 command usually consists of an SQL statement followed by a semicolon. < / pre >

+-----------------+--------------+

| VERSION() | CURRENT_DATE |

+-----------------+--------------+

| 5.1.2-alpha-log | 2005-10-11 |

+-----------------+--------------+

1 row in set (0.01 sec)

mysql >

In addition, mysql can enter keywords in case. The following queries are equivalent:

mysql > SELECT VERSION(), CURRENT_DATE;

mysql > select version(), current_date;

mysql > SeLeCt vErSiOn(), current_DATE;

Here's another query that says 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 command shown so far is a fairly short one-line statement. You can enter multiple statements on line 1, separated by 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)

Instead of giving one command in one line, a longer command can be typed into multiple lines. mysql determines where a statement ends by looking for a terminating semicolon. (in other words, mysql collects input lines but does not execute until it sees the semicolon.)

Here is an example of a simple multi-line statement:

mysql > SELECT

- > USER()

- > ,

- > CURRENT_DATE;

+---------------+--------------+

| USER() | CURRENT_DATE |

+---------------+--------------+

| jon@localhost | 2005-10-11 |

+---------------+--------------+

In this example, after entering the first line of a multi-row query, notice that the prompt is from mysql > A - > , which is exactly what mysql means when it says it doesn't see the full statement and is waiting for the rest. The prompt is your friend because it provides valuable feedback that, if used, will always know what mysql is waiting for.

If you decide you don't want to execute a command in the middle of typing, type \c to cancel it:

mysql > SELECT

- > USER()

- > \c

mysql >

Note also the prompt, which switches back to mysql after you type \c > , providing feedback to indicate that mysql is ready to accept a new command.

The following table shows the various visible prompts and a brief description of the state of mysql they represent:

prompt

meaning

mysql >

Be prepared for new orders.

- >

Wait for the next 1 line of the multiline command.

' >

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

" >

Wait for the next line, waiting for the end of the string that begins with a double quotation mark (" ").

` >

Wait for the next line, waiting for the end of the identifier that starts with an anticline (' ').

/* >

Wait for the next line, waiting for the end of the comment that starts with /*.

Will appear during string collection ' > And" > Prompt (indicates that MySQL is waiting for the end of the string). In MySQL, you can write strings enclosed by the '' or '' characters (for example, 'hello' or 'goodbye'), and mysql allows you to enter strings that span multiple lines. When you see 1 prime > Or" > At the prompt, this means that a line 1 containing a string starting with the '' or '' parenthesized character has been entered, but no matching quotation marks have been entered to terminate the string. This shows that you have inadvertently omitted 1 quote character. Such as:

mysql > SELECT * FROM my_table WHERE name = 'Smith AND age < 30;

' >

If you enter the SELECT statement, then press the Enter key and wait for the result, nothing happens. Don't be surprised, "why is the query so long?" , pay attention to" > The clues provided by the prompt. It tells you that mysql expects to see the rest of an unterminated string. Do you see the mistake in the statement? The second quote is missing from the string "Smith ".

At this point, what should you do? The simplest is to cancel the command. However, in this case, you can't just type \c, because mysql interprets it as part 1 of the string it is collecting! Instead, type the closed quote character (so mysql knows you've finished the string) and type \c:

mysql > SELECT * FROM my_table WHERE name = 'Smith AND age < 30;

' > '\c

mysql >

The prompt returns to mysql > , indicating that mysql is ready to accept a new command.

` > The prompt is like ' > And" > Prompt, but indicates that you have started but do not end with ' > The initial identifier.

Know ' > And" > The meaning of the prompt is important because if you enter an unterminated string incorrectly, any subsequent lines will be ignored by mysql - including those containing QUIT! This can be quite confusing, especially if you don't know you need to provide termination quotes before undoing the current command.