10 Save time Mysql Command Summary _mysql

Although there are many GUI based MySQL clients that exist, such as the famous phpMyAdmin and SQLyog, but I have always liked native MySQL command-line clients, it is true that we need to take some time to familiarize ourselves with these command-line interfaces (CLI) before they are known, Especially if you don't usually work under an operating system with a powerful CLI environment, but with some practice, you can manage users through the CLI, browse your database, and perform other tasks that other people don't realize.

In this article, I'll introduce some of the MySQL command-line client tips I've accumulated at work, whether you try one or all of them, and I'm sure you'll save a lot of time.

Incidentally, the MySQL command-line client adapts to all operating systems , including windows, but because of the fear of the Windows native CLI environment, it is recommended that Windows users download and install the console. It is an alternative solution to the Windows command line, providing more powerful features such as easy text selection, multiple label windows, and more.

1, Login process automation

Properly configured MySQL server requires you to provide user name and password authentication, usually, we can directly after the MySQL command plus user name, for security reasons, the password will not follow, enter the execution, command prompt will remind you to enter the password.

Copy Code code 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 the user name less than thousands of times a year, which can save a few hours in the login time, Create a. my.cnf file, put it in your home directory, if it is windows, the filename is My.ini, and you want to put it in the MySQL installation directory, in this file, add the following code, please replace the placeholder with your login information.

Copy Code code as follows:

[Client] host = Your_mysql_server user = your_username Password = Your_password

Be sure to set permissions on this file correctly to prevent sensitive data from being peeping.

2. Automatically switch database

Once you have logged in to the client, you need to switch to the target database, which we will normally use to switch the database:

Copy Code code as follows:

Mysql>use Wjgilmore_dev;

If you want to automatically switch to the target database after you log in, you can add the following command to the file described in the previous step, and note that the location is also in the [Client] section:

Copy Code code as follows:

Database = Your_database_name

3. Send commands from Script

When designing a new database, I like to use the MySQL Workbench (MySQL Workbench) design patterns and relationships, it is a particularly powerful tool, you can in the graphical interface to manage your model, and then sync to the MySQL server , or export SQL commands to a file for easy import to MySQL later.

If you like handwriting code, such as creating a large number of stored procedures, or performing a long connection, you can save the SQL as a file and then pass the file to the client for execution, such as:

Copy Code code as follows:

%>mysql < Schema.sql

Of course you need to specify the connection string, or you can specify it by using the configuration file as before.

4, Vertical display results

Even a very simple table pattern can contain several columns of fields, such as the following table consists of 11 fields, and when I execute a full structure query, the results are as follows:

Copy Code code as follows:

Mysql> SELECT * from accounts where username = ' Wjgilmore ';
+----+-----------+------------------+------------------------------
----+----------+-----------+------------+-----------+--------------
--------------------+---------------------+---------------------+
| ID | Username | email | password | Zip_Code | Latitude | Longitude | Confirmed | Recovery | created_on | Last_login |
+----+-----------+------------------+-------------------------------
---+----------+-----------+------------+-----------+-----------------
-----------------+---------------------+---------------------+
| 7 | Wjgilmore | wj@wjgilmore.com | 2b877b4b825b48a9a0950dd5bd1f264d | 43201 | 39.984577 | -83.018692 | 1 | 8bnnwtqlt2289q2yp81tuge82fty501h | 2010-09-16 14:48:41 | 2010-10-27 15:49:44 |
+----+-----------+------------------+----------------------------------
+----------+-----------+------------+-----------+----------------------
------------+---------------------+---------------------+

Obviously we can't accept this and we can't read the display and use the G command to convert the ugly display result to the vertical type.

Copy Code code as follows:

Mysql> SELECT * from accounts where username = ' Wjgilmore ' G
1. Row ***************************
Id:7 Username:wjgilmore email:wj@wjgilmore.com
PASSWORD:2B877B4B825B48A9A0950DD5BD1F264D zip_code:43201
latitude:39.984577 Longitude:-83.018692
Confirmed:1 recovery:8bnnwtqlt2289q2yp81tuge82fty501h
Created_on:2010-09-16 14:48:41 last_login:2010-10-27 15:49:44

It looks a little more comfortable.

5. Enable Tab key to complete the function automatically

It is certainly tedious to repeat the table name and field name, either by passing a--auto-rehash parameter to the MySQL client or by adding the following command to the My.ini file to enable the TAB key AutoComplete feature.

Copy Code code as follows:

[MySQL] Auto-rehash

6, change the prompt

I have more than once wanted to view or modify a pattern that is told that the table does not exist, and I am often afraid of it, most of the time I log in the wrong database, causing the wrong deletion of the table, by modifying the MySQL client prompt to display the database name of the current operation, thus avoiding misoperation, In order for the MySQL client to have this kind of interactivity, log on and execute the following command:

Copy Code code as follows:

Mysql>prompt [d]> [dev_wjgilmore_com]>

You may want to keep this effect permanently, simply by adding the following command to your profile:

Copy Code code as follows:

prompt = [d]>

Of course not limited to the hint database name, also can display the current date and time, host name and username, please read the MySQL manual for more comprehensive information.

7. Use security update to prevent disaster

I also mentioned earlier that it can be scary when a table is accidentally deleted, and I believe I'm not the only one with a similar experience, except accidentally executing the drop table, the more crashes are the ignoring of the WHERE clause's update command, for example, assuming you want to modify the user's user name by using the following command:

Copy Code code as follows:

Mysql>update users Set user = ' Wjgilmore ' WHERE user = ' wjgilmore-temp ';

But when you rush to come up to eat, you may forget to typed the following WHERE clause

Copy Code code as follows:

Mysql>update users Set User = ' Wjgilmore ';

Once you press ENTER, the consequences are serious, the user name of all users in the user table has been modified to wjgilmore, in order to avoid this low-level, but also disastrous consequences of the error, please add the following command in the configuration file:

Copy Code code as follows:

Safe-updates

8. Use command documents

Many users know the built-in documentation for the MySQL client, and when you enter the Help command, it displays a long list of commands.

Copy Code code as follows:

Mysql>help
... List of all MySQL commands:note so all text commands must is
The "." (?) 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 the describe command is used, you only need to enter

Copy Code code 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 shortcut for show COLUMNS from. These statements also display information for views. ([Help show COLUMNS].) ...

9. Use pager

Sometimes you might want to look at a row of data in a table, but you will often see a full screen of data on the screen, in addition to using the LIMIT clause, open the client pager, set your system paging utility:

Copy Code code as follows:

Mysql>pager more pager set to ' more '

If you want to disable pager, execute the nopager command.

10. Dump the output to a file

You may occasionally need to output SQL execution results to a text file, either by using the SELECT INTO outfile command commands, or by enabling the tee command directly on the MySQL client, and by developing an output file that implements the same functionality, such as:

Copy Code code as follows:

Mysql>tee Sales_report.txt

Summary

Whether you choose a part of it or try these 10 tips, they will save you a lot of time and effort and will ease your pain, if you know other MySQL command line tips, share in the comments!

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