一、实验内容
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 FilesMySQLMySQL Server 8.0bin>mysqldump -hlocalhost -uroot -pqwe123123 dbem employee>C:UserspcDesktopemployee.sql
4、C:Program FilesMySQLMySQL Server 8.0bin>mysqldump -hlocalhost -uroot -pqwe123123 dbem >C:UserspcDesktopdbem.sql
5、drop table employee;
C:Program FilesMySQLMySQL Server 8.0bin>mysqlimport -uroot -pqwe123123 dbem C:UserspcDesktopemployee.sql
6、delete from salary where employeeID=504209;
C:Program FilesMySQLMySQL Server 8.0bin>mysqlimport -uroot -pqwe123123 -replace dbem C:ProgramDataMySQLMySQL Server 8.0Uploadssalary.txt
三、实验分析
1、用sql语句备份文件时，路径用的导致报错，改为/后成功运行
2、用sql语句备份文件时，一开始随意写了一个路径，发现不行，用show variables like ‘%secure%’;语句找到安全路径后解决问题
3、一开始在mysql里输入mysqldump命令，一直都不能执行，改为在cmd中方可执行
