Reading data from text files (import)
Common text files: CSV (Comma Separated Values) files, that is, Values Separated by commas
The format is as follows::
[Plain]
M0001, Li Gang, 1976-01-
M0002, Wang Er, 1955-01-15,1
M0003, Li Si, 1967-03-
[SQL]
Load data infile 'd:/myCodes/test. cvs 'into TABLE member fields terminated by ','; -- note the encoding of the test. cvs file.
SELECT * into outfile 'd:/myCodes/out. cvs 'fields TERMINATED by', 'FROM member;
Execute the SQL commands saved in the file
[SQL]
Source d:/myCodes/test. SQL -- SOURCE is not an SQL command. Therefore, no extra points are added at the end;
Mysql test-uroot-p-e "source d:/myCodes/test. SQL" -- test is the database name
You can write the preceding SQL statement into batch processing files, for example:
[Plain]
Mysql test-uroot-p-e "source d:/myCodes/test. SQL"
Pause
Save the SQL Execution result to a file
The keyboard, mouse, and other input devices are referred to as standard input;
Displays and other devices are standard output devices.
Standard input and standard output devices can be changed. Such changes are called redirection ).
Command window
[SQL]
Copy codeThe Code is as follows:
Dir> D:/myCodes/redirect.txt
Dir> D: \ myCodes \ redirect.txt
Type d: \ myCodes \ redirect.txt
HELP
HELP TYPE
MySQL,
[SQL]
Copy codeThe Code is as follows:
Mysql-uroot-p> D: \ myCodes \ log.txt
Type d: \ myCodes \ log.txt
Mysql-uroot-p-e "source d:/myCodes/test. SQL"> D: \ myCodes \ log.txt
Run the tee command to save the SQL statement execution result to the file.
[SQL]
Copy codeThe Code is as follows:
Tee D:/myCodes/teeLog.txt
USE home;
SELECT * FROM customer;
Notee;
EXIT;
Type d: \ myCodes \ teeLog.txt
Database backup and recovery
The operation of saving the entire database to a file is called dump)
The operation of restoring a dump text file to a database is called restoration (restore)
[SQL]
Copy codeThe Code is as follows:
Mysqldump-u root-p home> D:/myCodes/home_back. SQL -- default-character-set = utf8
Mysqladmin-u root-p CREATE home1
Mysql-u root-p home1 <D:/myCodes/home_back. SQL -- default-character-set = utf8