Mysql exports the select statement results to an excel file, and mysqlselect

Mysql exports the select statement results to an excel file, and mysqlselect

I. Export data externally

1)Mysql connection + output query results to files. Execute in the command line (windows cmd command line, mac Terminal)

mysql -hxx -uxx -pxx -e "query statement" db > file 

-H: the host followed by the link)

-U: followed by the user name

-P: Password followed

Db: the database you want to query

File: the file to be written, absolute path

For example:

The SQL statement select * from edu_iclass_areas is output 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) connect to mysql and output the query results to the database for separate execution

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

-H/-u/-p parameters do not have the same content as above. xxx.txt is the file path to be output and its name.

For example:

-- Log on to mysqlmysql-h127.0.0.1-uroot-p123 -- output the query result to the file select * from edu_iclass_areas into outfile/Users/zhengcanrui/WORK/test/test.xls

The above two execution effects are equivalent.

Ii. Problems Encountered

1. mac command to start mysql on the terminal

1) after installing the MySQL service (for installation steps, refer to series Experience 1 ). Open "system preference settings" and click the "MySQL" icon at the bottom.

　　

2) In the "MySQL" dialog box, click "Start MySQL service ".

3) Click "application" in the navigation bar, and then double-click the "terminal" command in "utility.

4) enter the command to add the MySQL path in the terminal:

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

5) The command to log on to MySQL on the terminal is as follows:

mysql -u root -p

 

2. Access denied for user 'mysql username' @ 'host or ip' (using password: YES )'

Cause:

The mysql permission has not been granted access to localhost.
Run the following command to grant permissions ):
First, go to the mysql client command line.

Then enter:

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

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION; flush privileges;

2: You want to use the mysql username myuser to connect to the mysql server from the host with the ip address 192.168.1.3 and use mypassword as the password.

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;flush privileges;

You can execute these two commands.