This article will introduce you to MySQL by showing you how to create and use a simple data file using MySQL client instructions. Mysql (mysql command line tool) is an interactive program instruction tool that allows you to connect to the mysql database, perform queries, and browse query results. Mysql allows you to use batch mode: pre-store query instructions to a file and then tell mysql to execute the contents of the file. Both methods of using mysql are covered in this text.

MySQL 5.7 Reference Manual

directory

1. Establish and disconnect the database service

2, the query

Create and use the database

3.1. Create and select a database

Create a table

Add data to table

3.4. Retrieve data

4. Obtain database and table information

5. Use mysql’s batch mode

6. Command line query example

Sometimes you need to see what parameters mysql provides for us, just use the –help parameter to get the list of mysql parameters.

shell> mysql --help
Copy the code

Before this article begins, you need to make sure that mysql is properly installed on your machine and connected to mysql services. If your answer is no, go to:

MySQL website

1. Establish and disconnect the database service

In order to successfully connect to the MySQL service, you usually need to provide the MySQL username and password, and if the MySQL service is not on the host where the login command is executed, you also need to specify a remote host name.

shell> mysql -h host -u user -p
Enter password: ******
Copy the code

Host and user represent the host name of the MySQL service and the user name of the MySQL account. When MySQL prompts you to enter a password, enter the password.

If the previous step works, you will see some prompts and enter the mysql context.

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.7.21-standard Type 'help; ' or '\h' for help. Type '\c' to clear the buffer. mysql>Copy the code

Mysql >prompt indicates that mysql is ready for you to type SQL statements.

If you are logging in to MySQL on the same machine as the MySQL server, you can omit the host and simplify the code:

shell> mysql -u user -p
Copy the code

However, if you get an error message while trying to log in, something like:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
Copy the code

This means the MySQL service is not running. Chapter 2, Installing and Upgrading MySQL to get help.

If you encounter additional problems, go to Section B.5.2, “Common Errors When Using MySQL Programs” for help.

Some MySQL services allow users to connect to locally running services anonymously (with no user names). You can use the following command with no arguments:

shell> mysql
Copy the code

After you have successfully established a connection with the MySQL service, you can use QUIT (or \q) to disconnect the connection at any time:

mysql> QUIT
Bye
Copy the code

On Unix systems, you can also disconnect by typing Control+D.

In the following examples in this article, assume that you are already connected to the MySQL service.

2, the query

To ensure that you are connected to the MySQL service, here is a query for the MySQL service version number and current data:

In view of the above example, I would like to explain as follows:

A query statement consists of an SQL statement followed by a semicolon (although with some exceptions, semicolons are allowed to be omitted). Such as the QUIT command).

When you fire a query, mysql sends it to the server and executes it and displays the result of the query, and then prints another mysql> prompt indicating that you can continue to enter the query.

Mysql query results are presented in a table-like format (rows and columns). The first row contains the label for the column, and the subsequent rows are the query results. Normally, the column label is the name of the field in the database table that you are querying.

Mysql shows how many rows were queried and how long the query took. This information gives you advice on how to improve query performance. These performance figures are inexact because they represent only the time of the process (not CPU or machine time), which is affected by service loading and network latency.

The query you typed is case insensitive. The following query statements are equivalent.

mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE;
Copy the code

Here is another query statement, which demonstrates that you can do simple calculations with mysql:

So far, all queries have been short, single-line statements. You can type multiple queries in a single line. Just add a semicolon after each statement:

A query statement does not always need to be written on a single line, and a long query statement makes line breaking necessary. Mysql recognizes the semicolon as a separate query statement. If mysql does not recognize the semicolon, it will not execute the query statement.

Here is a multi-line statement:

In this example, notice how the prompt goes from mysql> to -> on the first of your multiple lines, with the indentation indicating an incomplete statement waiting for user input. This tip is your friend because it provides the callback interface available. If you want to use this callback, you need to know what mysql is waiting for user input.

If you decide that you do not want to execute an unfinished statement before typing it, type \c to cancel the statement.

The table below lists each of the prompts you will see in mysql.

Sometimes you forget to write a semicolon and accidentally produce multiple lines. Mysql will wait for you to continue typing:

If this happens to you, it’s probably because mysql is waiting for your semicolon. You just type a semicolon to end the statement and mysql will start executing it:

‘> and “> prompts often occur with characters in the character set (in other words, MySQL waits for you to complete a string entry). In MySQL, you can wrap strings with single and double quotation marks (such as’ hello ‘or’ goodbye ‘), And mysql allows you to write a string across multiple lines. When you see the ‘> and “> prompt, this means that which line of instruction you just typed has a string that begins with a single or double quote, but mysql did not find a pair of single or double quotes that end as a close. It’s just because you’re careless.

If you type a query, press Enter, and wait for MySQL to return nothing, you may wonder why the query is taking so long. You should notice the ‘> ‘prompt, which indicates that MySQL expects pairs of single or double quotes. The string ‘Smith is missing ‘completes the string).

This is, what should you do? The simplest way is to end the query, however, you don’t like to introduce the use of above \ c, because mysql understanding \ c this command as a still is part of the string, is the right thing, input closing quotation marks (mysql will know that you have completed the input string), followed by input \ c again, in this way, Then you can end the query:

At this point, the message changes to mysql>, which means mysql is ready for you to enter a new query.

It’s important to distinguish between ‘> ‘and ‘>’ and ‘>, because if you make a mistake by not entering a closing quote, any new line will wait for you to enter a closing quote, and all other special instructions will be considered part of the string and ignored for their purpose (for example, typing QUIT won’t work either).