mysql备份恢复表中的数据

2024-10-22 17:01:33

1、登录mysql，在test数据库新建一个表chengshi: Create Table 潮贾篡绐CREATETABLE`chengshi`(`id`int(11)NOTNULLAUTO_INCREMENT,`shengfen`varchar(10)DEFAULTNULL,`chengshi`varchar(10)DEFAULTNULL,`shijian`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=10DEFAULTCHARSET=utf8

2、在表中输入数据：insert into `chengshi` (`id`, `shengfen`, `che荏鱿胫协ngshi`, `shijian`) values('1','河南','安阳','2019-03-05 10:14:27');insert into `chengshi` (`id`, `shengfen`, `chengshi`, `shijian`) values('2','河南','郑州','2019-03-05 10:14:27');insert into `chengshi` (`id`, `shengfen`, `chengshi`, `shijian`) values('3','河南','洛阳','2019-03-05 10:14:27');insert into `chengshi` (`id`, `shengfen`, `chengshi`, `shijian`) values('4','河南','漯河','2019-03-05 10:14:27');insert into `chengshi` (`id`, `shengfen`, `chengshi`, `shijian`) values('5','河南','新乡','2019-03-05 10:14:27');insert into `chengshi` (`id`, `shengfen`, `chengshi`, `shijian`) values('6','河南','濮阳','2019-03-05 10:14:27');insert into `chengshi` (`id`, `shengfen`, `chengshi`, `shijian`) values('7','河南','焦作','2019-03-05 10:14:27');insert into `chengshi` (`id`, `shengfen`, `chengshi`, `shijian`) values('8','河南','鹤壁','2019-03-05 10:14:27');insert into `chengshi` (`id`, `shengfen`, `chengshi`, `shijian`) values('9','河南','开封','2019-03-05 10:14:27');

3、把表的数据备份c:\chengshi.sql文件中：SELECT * INTO OUTFILE 'c:/chengshi.sql' FROM chengshi

4、这个文件备份的表的数据，把备份的数据恢复的表中。

5、备份的主键和表的主键重复，所有替换数据：LOAD DATA INFILE 'c:/chengshi.sql' REPLACE INTO TABLE chengshi

6、备份的主键和表的主键重复，把没有的数据恢复到表中：DELETE FROM chengshi WHERE id>=3;LOAD DATA INFILE 'c:/chengshi.sql' IGNORE INTO TABLE chengshi;

7、查询所有数据：SELECT * FROM chengshi;