10 time-saving MySQL commands

Although many GUI-based MySQL clients exist, such as the famous phpMyAdmin and SQLYog, I have always liked the native MySQL command line client. Indeed, I am not familiar with these command line interfaces (CLI) previously, it took us some time to get familiar with them, especially when you do not often work in an operating system with a powerful CLI environment, but after some exercises, you can use the CLI to manage users, browse your database, and execute other tasks. The pleasure is beyond the reach of other humans.

In this article, I will introduce some of the MySQL command line client skills I have accumulated at work, whether you try one or all of them, I am sure you will save a lot of time.

By the way, the MySQL command line client is applicable to all operating systems, including Windows, but because the Windows native CLI environment is daunting, it is recommended that Windows users download and install the Console, it is an alternative solution for Windows Command Line and provides more powerful functions, such as convenient Text Selection and multi-tag Windows.

1. automated login process

The correct configuration of the MySQL server requires you to provide the user name and password for identity authentication. Generally, we can add the user name directly after the mysql Command. For security reasons, the password will not keep up, when you press enter to execute the command, the command prompt will remind you to enter the password.
Copy codeThe Code is as follows:
%> Mysql-u root-p Enter password: Welcome to the MySQL monitor. Commands end with; or g ....

With this small improvement, you can enter thousands of user names in a year, saving a few hours in the total Logon Time and creating one. my. the cnf file is stored in your home directory. For Windows, the file name is my. ini, and put it in the MySQL installation directory. In this file, add the following code. Use your login information to replace the placeholder.
Copy codeThe Code is as follows:
[Client] host = your_mysql_server user = your_username password = your_password

Be sure to correctly set the permission for this file to prevent sensitive data from being peeked.

2. Automatic database failover

After logging on to the client, you need to switch to the target database. Generally, we will use the following command to switch the database:
Copy codeThe Code is as follows:
Mysql> use wjgilmore_dev;

If you want to automatically switch to the target database after Logging On, you can add the following command in the file described in the previous step. Note that the location should also be in the [client] section:
Copy codeThe Code is as follows:
Database = your_database_name

3. Send commands from scripts

When designing a new database, I like to use the MySQL Workbench design mode and relationship. It is a very powerful tool that allows you to manage your mode on the GUI, then synchronize the data to the MySQL server, or export the SQL command to a file, so that you can import the data to MySQL later.

If you like handwriting code, such as creating a large number of stored procedures or executing a long connection, you can save the SQL statement as a file and then pass the file to the client for execution, such:
Copy codeThe Code is as follows:
%> Mysql <schema. SQL

Of course, you need to specify the connection string, or use the configuration file as before.

4. Vertical Display Results

Even in simple table mode, several columns of fields are contained. For example, the following table consists of 11 fields. When I perform a full-structure query, the input result is as follows:
Copy codeThe Code is as follows:
Mysql> select * from accounts where username = 'wjgilmore ';
+ ---- + ----------- + ------------------ + ------------------------------
---- + ---------- + ----------- + ------------ + ----------- + --------------
-------------------- + --------------------- +
| Id | username | email | password | zip_code | latitude | longpolling | confirmed | recovery | created_on | last_login |
+ ---- + ----------- + ------------------ + -------------------------------
--- + ---------- + ----------- + ------------ + ----------- + -----------------
----------------- + --------------------- +
| 7 | wjgilmore | wj@wjgilmore.com | small | 43201 | 39.984577 |-83.018692 | 1 | 8bnnwtqlt2289q2yp81tuge82fty501h | 14:48:41 | 15:49:44 |
+ ---- + ----------- + ------------------ + ----------------------------------
+ ---------- + ----------- + ------------ + ----------- + ----------------------
------------ + --------------------- +

Obviously, we cannot accept or read this display result. Use the G command to convert the ugly display result to a vertical one.
Copy codeThe Code is as follows:
Mysql> select * from accounts where username = 'wjgilmore 'G
* *************************** 1. row ***************************
Id: 7 username: wjgilmore mail: wj@wjgilmore.com
Password: 2b877b4b825b48a9a0950dd5bd1f264d zip_code: 43201
Latitude: 39.984577 longpolling:-83.018692
Confirmed: 1 recovery: 8bnnwtqlt2289q2yp81tuge82fty501h
Created_on: 2010-09-16 14:48:41 last_login: 2010-10-27 15:49:44

It looks more comfortable.
 

5. Enable the Tab key auto-completion Function

Repeated input table names and field names are undoubtedly boring. You can pass the -- auto-rehash parameter to the mysql client, or add the following command to the my. ini file to enable the Automatic completion function of the Tab key.
Copy codeThe Code is as follows:
[Mysql] auto-rehash

6. Change the prompt

More than once I want to view or modify the mode in which the notified table does not exist. In this case, I am often afraid. Most of the time I log on to the wrong database, leading to the accidental deletion of tables that should not be deleted, modify the MySQL client prompt to display the Database Name of the current operation to avoid misoperation. To make the mysql client interactive, log on and execute the following command:
Copy codeThe Code is as follows:
Mysql> prompt [d]> [dev_wjgilmore_com]>

You may want to maintain this effect permanently, which is simple. You only need to add the following command to your configuration file:
Copy codeThe Code is as follows:
Prompt = [d]>

Of course, it is not limited to prompting the database name, but also displaying the current date and time, host name and user name. Please read the MySQL manual for more comprehensive information.

7. Use security updates to prevent disasters

As mentioned above, accidental deletion of a TABLE may lead to fear. I believe that more than one of me has a similar experience, except for the accidental execution of the DROP TABLE, even worse, the UPDATE command of the WHERE clause is ignored. For example, if you want to use the following command to modify the User Name:
Copy codeThe Code is as follows:
Mysql> UPDATE users set User = 'wjgilmore' WHERE User = 'wjgilmore-temp ';

However, when you are eager to go out for dinner, you may forget to input the WHERE clause
Copy codeThe Code is as follows:
Mysql> UPDATE users set User = 'wjgilmore ';

Once you press enter, the consequences will be very serious. All user names in the users table will be changed to wjgilmore. To avoid such low-level errors that may cause catastrophic consequences, add the following command to the configuration file:
Copy codeThe Code is as follows:
Safe-updates

8. Use command documentation

Many users know the mysql client's built-in documentation. When you enter the help command, it displays a long list of commands.
Copy codeThe Code is as follows:
Mysql> help
... List of all MySQL commands: Note that all text commands must be
First on line and end ';'? (?) Synonym
For 'help'. clear (c) Clear the current input statement.
Connect (r) Reconnect to the server. Optional arguments are db and host. delimiter (d)
Set statement delimiter ....

If you do not know how to use the DESCRIBE command, you only need to enter
Copy codeThe Code is as follows:
Mysql> help describe;
Name: 'describe' Description: Syntax:
{DESCRIBE | DESC} tbl_name [col_name | wild] DESCRIBE provides information about the columns in a table.
It is a short cut for show columns from. These statements also display information for views. (See [help show columns] ..)...

9. Use Pager

Sometimes you may want to view a certain row of data in the table, but the screen will usually display full screen data, in addition to using the LIMIT clause, enable the pager client, set your system paging utility:
Copy codeThe Code is as follows:
Mysql> pager more PAGER set to 'more'

To disable pager, run the nopager command.

10. Dump the output content to a file

You may occasionally need to output the SQL Execution results to a text file. You can use the SELECT INTO OUTFILE command to achieve this purpose, or enable the tee command directly on the mysql client, and develop output files to implement the same functions, such:
Copy codeThe Code is as follows:
Mysql> tee sales_report.txt

Summary

Whether you select some of these skills or try these 10 skills, they will save you a lot of time and energy and ease your pain. If you know other MySQL command line skills, share it in the comments!

Source: http://www.developer.com/db/10-command-line-timesavers-for-mysql-tasks.html