MySQL client command line application tips

The command line of the MySQL client is widely used. The following describes the command line Application Techniques of the MySQL client in detail. We hope that you can learn more about the command line of the MySQL client.

Change client prompt

If you run show tables every time to remind yourself which database you have selected can get a dollar, it is estimated that I have become a millionaire now. In fact, when we switch between IDE, shell, and MySQL client prompts on a regular basis, it is easy to get lost without knowing where we are. To fundamentally eliminate this problem, you can use the following prompt command to change the MySQL prompt:

Mysql> prompt mysql (d)>

After the command is executed, the selected database is displayed at the prompt, as shown below:

Mysql (cmdate)>

In addition, we can easily lose the database server we log on to, not to mention the account in use. To solve this problem, use the u and h options to change the prompt settings:

Mysql> prompt mysql (u @ h)>

The command execution result is as follows:

Mysql (root@www.ctocio.com.cn)>

To permanently Save the changes, add the following command to the. my. cnf file:

[Mysql] prompt = mysql d>

The database, user, and host options are only a small part of many Optional options. For more information, see the MySQL instructions.

Use shell Alias (Alias) to execute frequently-used MySQL commands

Assume that your new web application is gradually entering the application stage, and new users are registered every hour. Just like IPO, you and your colleagues need to pay attention to the number of registered users. However, to execute a simple counting query, You need to constantly log on to and exit MySQL, which is obviously not very cost-effective. If you log on to the server, you can create a shell alias to log on to the MySQL server, select the appropriate database, and execute the Count command throughout the process:

%> Alias usrcount = "mysql-u appadmin-p myapplication
-E "select count (id) from users ""

In this example, you want to log on to the local MySQL server using the appadmin user account, SELECT the myapplication database, and then use the SELECT query to execute the Count query. After executing the preceding command, you only need to execute the following command and enter the appadmin user password to determine the number of users in the system:

%> Usrcount
Enterpassword:
+ ------------- +
| Count (id) |
+ ------------- +
| 1, 348 |
+ ------------- +

To permanently save this alias, you can add it to the. bashrc file or similar shell configuration file.

Query results in vertical format

When a table contains many columns, it is quite difficult to display the SELECT query results in a shell window. You can easily solve this inconvenience by attaching a G conversion to the query:

Mysql> SELECT * from users WHERE id = 1G

Executing this query will result in the following output:

Mysql test> select * from users where id = 1G
* ************************** 1. row *********
Id: 1
Name: Nancy
Email: Nancy@example.com
Telephone: 861088888888
City: Beijing
1 row in set (0.00 sec)

Create a CSV file from the query results

If you can use the database professionally, it is very likely that some colleagues will allow you to transfer data from the database to an Excel file for further analysis. We can perform all CSV formatting steps by modifying a SELECT query, and then place the data in a text file. All you need to do is to point out the ending fields and boundaries. For example, use the following command to convert a table named usersto a csv file named users.csv:

Mysql> SELECT * FROMusersINTOOUTFILE '/home/jason/users.csv'

FIELDSTERMINATEDBY ''-> LINESTERMINATEDBY '';

Insert batch data

Suppose you are writing an e-commerce application to sell a variety of products. It is wise to classify these products first. Therefore, you may need to use a table (named categories in this example) to manage these category names:

Mysqltest> createtablecategories (
-> Idintegernotnullauto_increment,
-> Namevarchar (35) notnull,
-> Primarykey (id ));

When writing a program, you have written a file containing a category (categories.txt ). This file is in the following format:

Candy

Fruit

Coffee

Tea

Vegetables

Add these categories to the categories table. You can add them manually, but this is time-consuming and error-prone. it is wise to use the MySQL load data infile command:

Mysql> LOADDATAINFILE '/home/nancy/categories.txt'
Using tablecategories-> LINESTERMINATEDBY'
'
If each row of your input file contains multiple projects, you can use tabs to separate these projects and insert each project into an independent column, use fields terminated '':

Mysql> LOADDATAINFILE '/home/jason/categories.txt'
Using tablecategories-> FIELDSTERMINATEDBY''
Linesterminatedby'
'

Disable annoying error prompts

You may hate the error message from the MySQL client. If you are unable to cope with such beeps, you can run a query from the client to cut off these midnight alarms. Although this does not affect the efficiency, you can disable this function. Temporarily disable the prompt sound. You can call -- no-beep while logging on to the server.

%> Mysql-u root-p -- no-beep

To permanently disable this function, add no-beep to the [client] section of the. my. cnf file.
 
 

Detailed description of how to name a shard using the mysql Command Line

Special usage of the SELECT command in MySQL

Mysql command line parameters

How to import SQL data through Mysql command lines

Create a temporary table in php mysql