MySQL：使用查询中的信息创建新表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6595252/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: Creating a new table with information from a query
提问by Saikios
In MySQL, I would like to create a new table with all the information in this query:
在 MySQL 中，我想创建一个包含此查询中所有信息的新表：
select * into consultaa2 from SELECT
CONCAT( 'UPDATE customers SET
customers_default_address_id= ',
(SELECT a.address_book_id FROM
address_book a where
c.customers_id=a.customers_id order by
address_book_id desc limit 1), '
WHERE customers_id = ', customers_id,
';') AS sql_statement FROM customers c
where c.customers_id > 3894;
The query is too long for the browser to show the concat and I need this to make this updates.
查询太长，浏览器无法显示 concat，我需要它来进行此更新。
采纳答案by Sukumar
*Note that this method does not createa table(as per OP title). To do that see this answer.*
*请注意，此方法不会创建表格（根据 OP 标题）。要做到这一点，请参阅此答案。*
Inserting into a table with information from a query is of the format
将查询中的信息插入到表中的格式为
INSERT INTO <TABLE-1>
SELECT * FROM <TABLE-2>
In your case, it would be
在你的情况下，这将是
insert into consultaa2
SELECT CONCAT( 'UPDATE customers SET customers_default_address_id= ',
(SELECT a.address_book_id FROM address_book a where c.customers_id=a.customers_id order by address_book_id desc limit 1), ' WHERE customers_id = ', customers_id, ';') AS sql_statement FROM customers c where c.customers_id > 3894;
Just make sure the columns in the table you are inserting into and the columns returned from the select query match.
只需确保您插入的表中的列与选择查询返回的列匹配。
回答by Daniel Hilgarth
You can do it like this:
你可以这样做：
CREATE TABLE tablename SELECT * FROM othertable;
tablenameis the name of the new table you want to create, SELECT * FROM othertableis the query that returns the data the table should be created from.
tablename是您要创建的新表的名称，SELECT * FROM othertable是返回创建表的数据的查询。
回答by Eric Leschinski
mysql create new table
mysql 创建新表
Example from mysql commandline.
来自 mysql 命令行的示例。
mysql> create table foo(id int, vorta text);
Query OK, 0 rows affected (0.02 sec)
Insert rows
插入行
mysql> insert into foo values(1, 'for the hoarde');
Query OK, 1 row affected (0.00 sec)
look what's in there
看看里面有什么
mysql> select * from foo;
+------+----------------+
| id | vorta |
+------+----------------+
| 1 | for the horde |
+------+----------------+
1 row in set (0.00 sec)
Create a new table with information from a query
使用查询中的信息创建一个新表
mysql> create table foo2 select * from foo;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
Check if the data moved
检查数据是否移动
mysql> select * from foo2;
+------+----------------+
| id | vorta |
+------+----------------+
| 1 | for the horde |
+------+----------------+
1 row in set (0.00 sec)
