sqlite的：查询结果创建新表，并保持原有架构 [英] Sqlite: Create new table for query results and keep the original schema

问题描述

我有以下的数据库文件（DB1.db）只包含一个表：

  sqlite的＆GT; 。倾倒
PRAGMA foreign_keys = OFF;
BEGIN TRANSACTION;
CREATE TABLE CARS（ID INT KEY NOT NULL，公司CHAR（20）NOT NULL，颜色CHAR（10）NOT NULL）;
INSERT INTO汽车总动员VALUES（111，'现代'，'白'）;
INSERT INTO汽车总动员VALUES（222，'宝马'，'黑'）;
INSERT INTO汽车总动员VALUES（333，'丰田'，'蓝'）;
承诺;
 

为了在两个不同的数据库中两个表之间的加入，我附上DB1.db（和其他DB）到DB2.db：

  sqlite的＆GT;附加数据库DB1.db'为'DB1';
 

在'车'表信息如下：

  sqlite的＆GT; PRAGMA TABLE_INFO（CARS）;
0 | ID | INT KEY | 1 || 0
1 |公司| CHAR（20）| 1 || 0
2 |彩| CHAR（10）| 1 || 0
 

Next和决赛，我想上运行所附表格查询，结果在新表中保存DB2.db：

  sqlite的＆GT; CREATE TABLE RES1 AS SELECT ID，公司从CARS WHERE ID ='333';
 

和如我所料，结果是在一个新的表：

  sqlite的＆GT; SELECT * FROM RES1;
333 |丰田
 

但新表信息如下：

  sqlite的＆GT; PRAGMA TABLE_INFO（RES1）;
0 | ID | INT | 0 || 0
1 |公司| TEXT | 0 || 0
 

正如你所看到的，我失去了公司列类型 - 它不再是'CHAR（20）和列可能是空的（'NOT NULL'为0而不是1如前）

我的问题：
我该怎么办上述操作（附加和查询结果插入到新表），仍然不会失去原表信息？

解决方案

这有没有关系连接。

CREATE TABLE ... AS ...不保留表的定义;它创建具有的类型的信息的最低限度的表，以便能够存储值

如果你想有什么不同表的定义，您必须手动创建表，然后用的 INSERT INTO ..​​. SELECT ... 。

I have the following db file (DB1.db) contains only one table:

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE CARS (ID INT KEY NOT NULL, COMPANY CHAR(20) NOT NULL, COLOR CHAR(10) NOT NULL);
INSERT INTO "CARS" VALUES(111,'Hyundai','White');
INSERT INTO "CARS" VALUES(222,'BMW','Black');
INSERT INTO "CARS" VALUES(333,'Toyota','Blue');
COMMIT;

In order to join between two tables in two different databases, I attach the DB1.db (and the other db) into DB2.db:

sqlite> ATTACH DATABASE 'DB1.db' AS 'db1' ;

The 'CARS' table info looks like:

sqlite> PRAGMA TABLE_INFO(CARS);
0|ID|INT KEY|1||0
1|COMPANY|CHAR(20)|1||0
2|COLOR|CHAR(10)|1||0

Next and final, I want to run query on the attached tables and save the result in new table in DB2.db:

sqlite> CREATE TABLE RES1 AS SELECT ID,COMPANY FROM CARS WHERE ID='333';

And as I expected, the result is in a new table:

sqlite> SELECT * FROM RES1 ;
333|Toyota

But the new table info looks like:

sqlite> PRAGMA TABLE_INFO(RES1);
0|ID|INT|0||0
1|COMPANY|TEXT|0||0

And as you can see, I lost the 'COMPANY' column type - it is no longer 'CHAR(20)' and the column might be empty ('NOT NULL' is 0 and not 1 as before).

My question: How can I do the operations above (attach and query result into new table) and still not lose the original table information?

解决方案

This has nothing to do with ATTACHing.

CREATE TABLE … AS … does not keep the table definition; it creates a table with the bare minimum of type information to be able to store the values.

If you want to have any different table definition, you have to create the table manually, and then insert the data with INSERT INTO … SELECT ….

这篇关于sqlite的：查询结果创建新表，并保持原有架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！