Shulou(Shulou.com)05/31 Report--

This article mainly explains "what are the MySQL commands that can save time". Friends who are interested may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what MySQL commands can save time"!

What are the time-saving MySQL commands?

1. Automation of the login process

A properly configured MySQL server requires you to provide a user name and password for authentication. Usually, we can add the user name directly after the mysql command. For security reasons, the password will not keep up. When you enter, the command prompt will remind you to enter the password.

% > mysql-uroot-pEnterpassword:WelcometotheMySQLmonitor.Commandsendwith;org....

With this small improvement, you can enter a user name thousands less times a year, save several hours in login time, create a .my.cnf file, put it in your home directory, if it is Windows, the file name is my.ini, and put it in the MySQL installation directory, in this file, add the following code, please use your login information to replace the placeholder.

[client] host=your_mysql_serveruser=your_usernamepassword=your_password

Be sure to set the permissions of this file correctly to prevent sensitive data from being peeped into.

2. Automatically switch databases

After logging in to the client, you need to switch to the target database. Usually we will use the following command to switch the database:

Mysql > usewjgilmore_dev

If you want to automatically switch to the target database after logging in, you can add the following command to the file described in the previous step, and note that the location should also be placed in the [client] section:

Database=your_database_name

3. Send commands from the script

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

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

% > mysql

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

4. Display the results vertically

Even a simple table schema contains several columns of fields. For example, the following table consists of 11 fields. When I execute a full-structure query, the input results are as follows:

Mysql > select*fromaccountswhereusername='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-1614-48-41-2010-10-2715-49-44 | +-+- -+ -+-+

Obviously, we cannot accept or read this display result, and use the G command to convert the ugly display result to vertical.

Mysql > select*fromaccountswhereusername='wjgilmore'G**1.row**id:7username:wjgilmoreemail:wj@wjgilmore.compassword:2b877b4b825b48a9a0950dd5bd1f264dzip_code:43201latitude:39.984577longitude:-83.018692confirmed:1recovery:8bnnwtqlt2289q2yp81tuge82fty501hcreated_on:2010-09-1614:48:41last_login:2010-10-2715 1614:48:41last_login:2010 4944

It looks more comfortable this way.

5. Enable the Tab key auto-completion function

Repeatedly entering table and field names is undoubtedly tedious, passing a-- auto-rehash parameter to the mysql client, or adding the following command to the my.ini file to enable Tab key autocompletion.

[mysql] auto-rehash

What are the time-saving MySQL commands?

6. Change the prompt

I often feel frightened when I want to view or modify a schema that is told that the table does not exist. Most of the time, I log in to the wrong database, resulting in mistakenly deleting the table that should not be deleted. By modifying the MySQL client prompt to display the database name of the current operation, so as to avoid misoperation. In order to make the mysql client have this kind of interaction, execute the following command after login:

Mysql > prompt [d] > [dev_wjgilmore_com] >

You may want to maintain this effect permanently, simply by adding the following command to your configuration file:

Prompt= [d] >

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

7. Use security updates to prevent disasters

I also mentioned earlier that fear may arise when a table is accidentally deleted. I believe I am not the only one who has had a similar experience. Apart from accidentally executing DROPTABLE, what is even more frustrating is the UPDATE command that ignores the WHERE clause. For example, suppose you want to use the following command to modify the user name:

Mysql > UPDATEuserssetUser='wjgilmore'WHEREUser='wjgilmore-temp'

But when you are in a hurry to eat out, you may forget to type in the following WHERE clause

Mysql > UPDATEuserssetUser='wjgilmore'

Once you press enter, the consequences will be very serious. The user names of all users in the users table have been changed to wjgilmore. To avoid this low-level but disastrous error, please add the following command to the configuration file:

Safe-updates

8. Use the command document

Many users know the built-in documentation for the mysql client, which displays a long list of commands when entering the help command.

Mysql > help...ListofallMySQLcommands:Notethatalltextcommandsmustbefirstonlineandendwith';' () Synergy for `help '.clear (c) Clearthecurrentinputstatement.connect (r) Reconnecttotheserver.Optionalargumentsaredbandhost.delimiter (d) Setstatementdelimiter....

If you don't know how to use the DESCRIBE command, just type

Mysql > helpdescribe;Name:'DESCRIBE'Description:Syntax: {DESCRIBE | DESC} tbl_ name [col _ name | wild] DESCRIBEprovidesinformationaboutthecolumnsinatable.ItisashortcutforSHOWCOLUMNSFROM.Thesestatementsalsodisplayinformationforviews. (see [HELPSHOWCOLUMNS].

9. Use Pager

Sometimes you may want to view a row of data in the table, but there will often be a full screen of data on the screen. In addition to using the LIMIT clause, open the client pager and set up your system paging utility:

Mysql > pagermorePAGERsetto'more'

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

10. Dump the output to a file

You may occasionally need to output the SQL execution results to a piece of this article, you can use the SELECTINTOOUTFILEcommand command to achieve this purpose, or directly enable the tee command in the mysql client, and develop an output file to achieve the same function, such as:

Mysql > teesales_report.txt

At this point, I believe you have a deeper understanding of "what time-saving MySQL commands". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.