MySQL Export SELECT statement results to Excel files encountered problems and solutions _mysql

I. Export Data external

1 MySQL connection + to output the query results to the file. Execute on the command line (Windows cmd command line, Mac Terminal)

-H: followed by the linked host (host)

-U: The following is the user name

-P: followed by a password

DB: The database you want to query

File: You want to write the files, absolute path

For example:

The following query results from the SQL statement SELECT * from Edu_iclass_areas are exported to the/users/zhengcanrui/work/test/test.xls file.

Mysql-h127.0.0.1-uroot-p123-e "SELECT * from Edu_iclass_areas" Test >/users/zhengcanrui/work/test/test.xls

2 MySQL connection and the output of query results to the database separate execution

Mysql-hxxx-uxx-pxx
select * from table into outfile ' xxx.txt ';

-h/-u/-p parameters are not the same as the above, Xxx.txt is the file path to output and its name.

Such as:

--Login
to MySQL mysql-h127.0.0.1-uroot-p123- 
-Output query results to a file

The above two kinds of execution effect are equivalent.

Ii. problems encountered

1, the Mac in the terminal to start MySQL command

1 Install the MySQL service (installation steps can refer to the series of experience 1). Open System Preferences, and click the "MySQL" icon at the bottom.

2 in the MySQL dialog box, click the "Start MySQL Service" button

3 Click Application in the Finder's sidebar, and then, in utility, double-click to start the Terminal command.

4 Enter the command to add the MySQL path in the terminal:

Path= "$PATH":/usr/local/mysql/bin

5 at the terminal login to the MySQL command as follows:

2, Access denied for user ' MySQL user name ' @ ' host or IP ' (using Password:yes) '

Reason:

MySQL's access to localhost is not open.

You can use the following command to release permissions (authorization):

First enter the client command line to MySQL.

Then enter:

1: You want to use the MySQL username myuser from any host to connect to the MySQL server and use MyPassword as the password

Grant all privileges in *.* to ' myuser ' @ '% ' identified by ' MyPassword ' with GRANT OPTION;
Flush privileges;

2: You want to use the MySQL username myuser from IP for 192.168.1.3 host to connect to the MySQL server and use MyPassword as the password

Grant all privileges in *.* to ' myuser ' @ ' 192.168.1.3 ' identified by ' MyPassword ' with GRANT OPTION;
Flush privileges;

You can finish these two commands.

The above is a small series to introduce to you the MySQL export SELECT statement results to Excel files encountered problems and solutions, I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!