Database management File usage _ MySQL

Use bitsCN.com for files in database management

Use of files in database management

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]

Dir> D:/myCodes/redirect.txt

Dir> D:/myCodes/redirect.txt

Type d:/myCodes/redirect.txt

HELP

HELP TYPE

MySQL,

[SQL]

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]

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]

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

BitsCN.com