一、实验内容
1、用不同的存放格式(自由设计)备份DBEM数据库中的 employee, salary两个基本表,其中employee表要求只备份 employeeID, name, education等三个字段
2、根据上述任务所保存的文件,将相关数据恢复到基本表中,其中 要求employee表在恢复之前事先随机删除几条记录,SQL语句中要求指定replace功能
3、使用mysqldump命令备份DBEM数据库中的employee表
4、使用mysqldump命令备份整个DBEM数据库
5、删除employee表,然后使用mysql命令,利用上述保存的文件 恢复employee表
6、删除salary表的部分数据,然后使用mysqlimport命令及-- replace功能恢复salary表,其中salary表的数据备份文件使用实验4.1中的文件
二、实验过程
1、use dbem;
select * from salary into outfile ‘C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/salary.txt’
fields terminated by ‘\t’
enclosed by ‘’
escaped by ‘,’
lines terminated by ‘\r’;
select employeeID,name,education from employee into outfile ‘C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee.txt’
fields terminated by ‘\t’
enclosed by ‘’
escaped by ‘,’
lines terminated by ‘\r’;
2、delete from employee where employeeID=‘000001’;
load data infile ‘C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/employee.txt’ replace into table employee
FIELDS TERMINATED BY ‘\t’
enclosed by ‘’
escaped by ‘,’
LINES TERMINATED BY ‘\r’;
load data infile ‘C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/salary.txt’ replace into table salary
FIELDS TERMINATED BY ‘\t’
enclosed by ‘’
escaped by ‘,’
LINES TERMINATED BY ‘\r’;
删除一条记录后的employee表:
恢复后的salary表:
恢复后的employee表:
3、C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump -hlocalhost -uroot -pqwe123123 dbem employee>C:\Users\pc\Desktop\employee.sql
4、C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqldump -hlocalhost -uroot -pqwe123123 dbem >C:\Users\pc\Desktop\dbem.sql
5、drop table employee;
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqlimport -uroot -pqwe123123 dbem C:\Users\pc\Desktop\employee.sql
6、delete from salary where employeeID=504209;
C:\Program Files\MySQL\MySQL Server 8.0\bin>mysqlimport -uroot -pqwe123123 -replace dbem C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\salary.txt
三、实验分析
1、用sql语句备份文件时,路径用的\导致报错,改为/后成功运行
2、用sql语句备份文件时,一开始随意写了一个路径,发现不行,用show variables like ‘%secure%’;语句找到安全路径后解决问题
3、一开始在mysql里输入mysqldump命令,一直都不能执行,改为在cmd中方可执行