[Sqlite]-->数据迁移备份--从低版本3.6.2到高版本3.8.6
数据迁移一， 使用.dump命令命令帮助提示.dump ?TABLE? ... Dump the database in an SQL text format If TABLE specified, only dump tables matching LIKE pattern TABLE.理解分析：使用.dump命令可以将数据库对象导出成SQL格式。不带任何参数时，.dump将整个数据库导出为数据库定义语言(DDL)和数据库操作语言(DML)命令，适合重新创建数据库对象和其中的数据。如果提供了参数，Shell将参数解析作为表名或视图，导出任何匹配给定参数的表或视图，那些不匹配的将被忽略。默认情况下.dump 命令的输出定向到屏幕。如：.dump 如果要将输出重定向到文件，请使用.dump命令，此命令将所有的输出重定向到指定的文件中。若要恢复到屏幕的输出，只需要执行.output stdout命令就OK了。sqlite>.output file.sqlsqlite>.dumpsqlite>.output stdout注：如果file.sql不存在，将在当前工作目录中创建该文件。如果文件存在，它将被覆盖。 二，准备测试数据：CREATE TABLE COMPANY(ID INT NOT NULL, NAME VARCHAR(20),AGE INT,ADDRESS VARCHAR(20),SALARY DECIMAL(7,2));INSERT INTO COMPANYSELECT 1, 'Paul', 32, 'California',20000.0 UNION ALLSELECT 2, 'Allen', 25, 'Texas' ,15000.0 UNION ALLSELECT 3, 'Teddy', 23, 'Norway' ,20000.0 UNION ALLSELECT 4, 'Mark', 25, 'Rich-Mond' ,65000.0 UNION ALLSELECT 5, 'David', 27, 'Texas' ,85000.0 UNION ALLSELECT 6, 'Kim', 22, 'South-Hall',45000.0 UNION ALLSELECT 7, 'James', 24, 'Houston' ,10000.0 ;SELECT * FROM COMPANY;CREATE TABLE t1(id INT,NAME VARCHAR(20));INSERT INTO t1 SELECT 1,'a' UNION ALL SELECT 2,'b' UNION ALL SELECT 3,'c';将低版本的表修改表名为临时表COMPANY_TMP。ALTER TABLE COMPANY RENAME TO COMPANY_TMP; 三，开始备份操作，在低版本3.6.2上面做备份：# /usr/bin/sqlite3.bak.3.6.2 timSQLite version 3.6.20Enter ".help" for instructionsEnter SQL statements terminated with a ";"sqlite>sqlite> .headers onsqlite> .mode columnssqlite> .output alltables.sqlsqlite> .dumpsqlite>.exit 四，查看生成的备份文件：# more alltables.sqlPRAGMA foreign_keys=OFF;BEGIN TRANSACTION;CREATE TABLE "COMPANY_TMP"(ID INT NOT NULL, NAME VARCHAR(20),AGE INT,ADDRESS VARCHAR(20),SALARY DECIMAL(7,2));INSERT INTO "COMPANY_TMP" VALUES(1,'Paul',32,'California',20000);INSERT INTO "COMPANY_TMP" VALUES(2,'Allen',25,'Texas',15000);INSERT INTO "COMPANY_TMP" VALUES(3,'Teddy',23,'Norway',20000);INSERT INTO "COMPANY_TMP" VALUES(4,'Mark',25,'Rich-Mond',65000);INSERT INTO "COMPANY_TMP" VALUES(5,'David',27,'Texas',85000);INSERT INTO "COMPANY_TMP" VALUES(6,'Kim',22,'South-Hall',45000);INSERT INTO "COMPANY_TMP" VALUES(7,'James',24,'Houston',10000);CREATE TABLE t1(id INT,NAME VARCHAR(20));INSERT INTO "t1" VALUES(1,'a');INSERT INTO "t1" VALUES(2,'b');INSERT INTO "t1" VALUES(3,'c');COMMIT;#
看到都是一条条备份成的dml、ddl的sql语句。 五，在高版本3.8.6上面恢复数据1，在3.8.6上面建立相同的COMPANY表，不过多添加了2个字段IPHONE和LOGIN_DATE，如下所示：DROP TABLE IF EXISTS COMPANY;CREATE TABLE COMPANY(ID INT NOT NULL, NAME VARCHAR(20),AGE INT,ADDRESS VARCHAR(20),SALARY DECIMAL(7,2),IPHONE VARCHAR(16) NOT NULL,LOGIN_DATE DATETIME);INSERT INTO COMPANYSELECT 1, 'Paul', 32, 'California',20000.0,'14782121412',DATETIME('NOW') UNION ALLSELECT 2, 'Allen', 25, 'Texas' ,15000.0,'13982121412',DATETIME('NOW') ;SELECT * FROM COMPANY; 2， 开始恢复导入原来备份的数据sql脚本# sqlite3 tiSQLite VERSION 3.8.6 2014-08-15 11:46:33Enter ".help" FOR USAGE hints.sqlite> .headers ONsqlite> .MODE COLUMNSsqlite> .READ alltables.sql sqlite> .TABLECOMPANY COMPANY_TMPt1 sqlite>
3，在数据迁移从旧临时表迁移到新表之前，查下现有表的数据sqlite>SELECT * FROM COMPANY;ID NAME AGE ADDRESS SALARY IPHONE LOGIN_DATE --------------------------------------------------------------------------------1 Paul 32 California20000 147821214122014-08-29 11:14:072 Allen 25 Texas 15000 139821214122014-08-29 11:14:07sqlite>
4，开始使用INSERT迁移数据到新添加字段的表COMPANYsqlite> INSERT INTO COMPANY(ID,NAME,AGE,ADDRESS,SALARY,IPHONE) SELECT ID,NAME,AGE,ADDRESS,SALARY,'' FROM COMPANY_TMP ;sqlite> SELECT * FROM COMPANY;ID NAME AGE ADDRESS SALARY IPHONE LOGIN_DATE --------------------------------------------------------------------------------1 Paul 32 California20000 147821214122014-08-29 11:14:072 Allen 25 Texas 15000 139821214122014-08-29 11:14:071 Paul 32 California20000 2 Allen 25 Texas 15000 3 Teddy 23 Norway 20000 4 Mark 25 Rich-Mond 65000 5 David 27 Texas 85000 6 Kim 22 South-Hall45000 7 James 24 Houston 10000 sqlite> 看到迁移数据之后的显示，旧表数据已经迁移到新表了。 六，最后删除旧的临时表sqlite> DROP TABLE IF EXISTS COMPANY_TMP;sqlite> 至此，整个数据迁移工作顺利结束。
页:
[1]