sqlite-( TABLE == FILE )
———————————————————
1、导入 .import命令 ( FILE ==> TABLE)
———————————————————
sqlite>.import FILE TABLE //Import data from FILE into TABLE
----------例：
目地：将data.txt 中的数据内容导入到test_database.db数据库的table_im表中。
- 我先查看data.txt文本中的内容
- 打开数据库文件，将data.txt文本中的内容导入某table
我的操作：在目标表table_im没有被创建的情况下，
结果：执行 sqlite>.import data.txt table_im，会将data.txt的第一行数据当作table_im的字段名col_name，并将后面的2~5行的数据作为字段列内容导入table_im。详情如下(看注释)：
root@vm-ubuntu:~/sqlite_C# sqlite3 test_database.db
SQLite version 3.8.7.4 2014-12-09 01:34:36
Enter ".help" for usage hints.
sqlite> .table
COMPANY //开始只有COMPANY这一个table
sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS TEXT , SALARY REAL );
INSERT INTO "COMPANY" VALUES(1,'LiA',20,'China',100000.0);
INSERT INTO "COMPANY" VALUES(2,'LiB',25,'America',200000.0);
INSERT INTO "COMPANY" VALUES(3,'LiC',30,'earth',300000.0);
INSERT INTO "COMPANY" VALUES(5,'LIE',40,'Ground',500000.0);
INSERT INTO "COMPANY" VALUES(6,'LiF',45,'Home',600000.0);
COMMIT;
sqlite> .import data.txt table_im //导入操作 (在这之前table_im没有被create)
sqlite> .table
COMPANY table_im //在这里可以看出来， table_im被上一步操作新建了，
sqlite> .dump //然后我查看数据库中table_im是怎么被创建的，表结构是怎么样的
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE COMPANY( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS TEXT , SALARY REAL );
INSERT INTO "COMPANY" VALUES(1,'LiA',20,'China',100000.0);
INSERT INTO "COMPANY" VALUES(2,'LiB',25,'America',200000.0);
INSERT INTO "COMPANY" VALUES(3,'LiC',30,'earth',300000.0);
INSERT INTO "COMPANY" VALUES(5,'LIE',40,'Ground',500000.0);
INSERT INTO "COMPANY" VALUES(6,'LiF',45,'Home',600000.0);
CREATE TABLE table_im(
"1" TEXT,
"LiA" TEXT,
"20" TEXT,
"China" TEXT,
"100000.0" TEXT //可看到，data.txt的第一行数据被当作了table_im的字段名，并且，表结构的字段类型也被自动生成了，清一色的TEXT类型 。
);
INSERT INTO "table_im" VALUES('2','LiB','25','America','200000.0');
INSERT INTO "table_im" VALUES('3','LiC','30','earth','300000.0');
INSERT INTO "table_im" VALUES('5','LIE','40','Ground','500000.0');
INSERT INTO "table_im" VALUES('6','LiF','45','Home','600000.0');
COMMIT;
sqlite> select * from table_im; //于是table_im的字段内容就从data.txt的第二行开始
2|LiB|25|America|200000.0
3|LiC|30|earth|300000.0
5|LIE|40|Ground|500000.0
6|LiF|45|Home|600000.0
所以说，建议：先创建表，定义表结构，再将文本文件导入到表中
正确导入方法:
sqlite> CREATE TABLE TABLE_TEST( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS TEXT , SALARY REAL );
sqlite> .TABLE
Error: unknown command or invalid arguments: "TABLE". Enter ".help" for help
sqlite> .table
COMPANY TABLE_TEST
sqlite> .import data.txt TABLE_TEST
sqlite> .dump TABLE_TEST
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE TABLE_TEST( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS TEXT , SALARY REAL );
INSERT INTO "TABLE_TEST" VALUES(1,'LiA',20,'China',100000.0);
INSERT INTO "TABLE_TEST" VALUES(2,'LiB',25,'America',200000.0);
INSERT INTO "TABLE_TEST" VALUES(3,'LiC',30,'earth',300000.0);
INSERT INTO "TABLE_TEST" VALUES(5,'LIE',40,'Ground',500000.0);
INSERT INTO "TABLE_TEST" VALUES(6,'LiF',45,'Home',600000.0);
COMMIT;
_________________________________________
2、导出 .output命令 (TABLE==>FILE）
_________________________________________
sqlite>.output FILE_NAME //Send outputto FILENAME
----------例：
root@vm-ubuntu:~/sqlite_C# sqlite3 test_database.db
SQLite version 3.8.7.4 2014-12-09 01:34:36
Enter ".help" for usage hints.
sqlite> .output output.txt //将结果集输出定位到output.txt
sqlite> select * from COMPANY; //查寻输出
sqlite> .exit
root@vm-ubuntu:~/sqlite_C# ls
backupdb.db gettable mydata.db selectdata.c sqlite3.h.gch
connect_db.c gettable.c output.txt selectdata_pro.c sqlite_test.c
createtable.c insertdata.c README.md sqlite3.h test_database.db
root@vm-ubuntu:~/sqlite_C# vim output.txt
从得到的文件来看，导出的数据包含各字段的内容，不包含字段名！
